Can not convert database and data to UTF-8

Started by BlackPowderSmoke, May 20, 2020, 11:06:40 AM

Previous topic - Next topic

BlackPowderSmoke

I am working on a task in which I need advice for. I am trying to convert the database and data to UTF-8. I am using a SMF test forum in order to minimize risk to to the actual forum. It is located here...

https://1858remington.com/test/index.php

"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key key varbinary(255) NOT NULL default ''' at line 10
File: /home/eartjylo/1858remington.com/test/Sources/ManageMaintenance.php
Line: 664

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.17, while your database is at version 2.0.6. The above error might possibly go away if you execute the latest version of upgrade.php. "

I have the most current version of SMF, plus I have run the upgrade.php again, as suggested by the last line of the above message.

I contacted my host and got this back...


We have checked the settings for your application and the error logs, please check it below:

[19-May-2020 18:50:00 America/New_York] PHP Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/eartjylo/1858remington.com/test/Sources/Subs.php on line 3825
It seems that the issue is caused by the file Subs.php. Please double-check the code in this file with the website developer.


What should I do?

Arantor

So your later error is caused by upgrading to PHP 7.4, which is not yet supported.

Your former error is caused by having a column in the database called "key" which needs backticks in the query to support it.

Not sure which mod added it though.

BlackPowderSmoke

Thank you. Which php version is  the newest  supported by SMF?

Arantor


BlackPowderSmoke

Thank you. I tried 7.3 and 7.2, then  7.0 with the same result.

Kindred

Quote from: Arantor on May 20, 2020, 11:08:45 AM
Your former error is caused by having a column in the database called "key" which needs backticks in the query to support it.

Not sure which mod added it though.
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

BlackPowderSmoke

Quote from: Kindred on May 20, 2020, 12:10:14 PM
Quote from: Arantor on May 20, 2020, 11:08:45 AM
Your former error is caused by having a column in the database called "key" which needs backticks in the query to support it.

Not sure which mod added it though.
My oversight. Thank you.

cicstim

Quote from: Kindred on May 20, 2020, 12:10:14 PM
Quote from: Arantor on May 20, 2020, 11:08:45 AM
Your former error is caused by having a column in the database called "key" which needs backticks in the query to support it.

Not sure which mod added it though.

Thank you. I have deleted all mods, ran cache. No change.  I have been unable to  find a key column in database so far...

Sir Osis of Liver

Run query -



SELECT table_name,table_schema
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name='key'


Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

BlackPowderSmoke

Quote from: Sir Osis of Liver on May 20, 2020, 01:02:18 PM
Run query -



SELECT table_name,table_schema
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name='key'


Thank you for the help. I sure need it.

Ok so I ran the query...


SELECT table_name,table_schema
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name='key'

This was the result...

table_name
table_schema

INNODB_FT_CONFIG
information_schema

smf_log_badbehavior
eartjylo_1858

smf_log_badbehavior
eartjylo_58test


When I attempt to open INNODB FT CONFIG, I get #1227 – access denied. You need (at least one of) the PROCESS privileges for this operation.

How do I set these privileges?

Kindred

don't mess with that one.

your problem is in the table smf_log_badbehavior
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

BlackPowderSmoke

Quote from: Kindred on May 20, 2020, 06:04:37 PM
don't mess with that one.

your problem is in the table smf_log_badbehavior

Thank you. I deleted the key column in the  table smf_log_badbehavior

Now I have a new database error message...

QuoteColumn 'body' cannot be part of FULLTEXT index
File: /home/eartjylo/1858remington.com/test/Sources/ManageMaintenance.php
Line: 664

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.17, while your database is at version 2.0.6. The above error might possibly go away if you execute the latest version of upgrade.php.

Kindred

Well, for one, you can't just delete columns from tables

If you are still using bad behavior, you've now broken it
If you are not using it, then you should remove the entire set of tables, not just the column

Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

BlackPowderSmoke

Quote from: Kindred on May 20, 2020, 08:41:01 PM
Well, for one, you can't just delete columns from tables

If you are still using bad behavior, you've now broken it
If you are not using it, then you should remove the entire set of tables, not just the column
Thank you, the mod was previously removed. The table is now removed.
unfortunately, the problem persists.

BlackPowderSmoke

Quote from: BlackPowderSmoke on May 20, 2020, 06:20:48 PM
Quote from: Kindred on May 20, 2020, 06:04:37 PM
don't mess with that one.

your problem is in the table smf_log_badbehavior

Thank you. I deleted the key column in the  table smf_log_badbehavior

Now I have a new database error message...

QuoteColumn 'body' cannot be part of FULLTEXT index
File: /home/eartjylo/1858remington.com/test/Sources/ManageMaintenance.php
Line: 664

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.17, while your database is at version 2.0.6. The above error might possibly go away if you execute the latest version of upgrade.php.

Lines 659-665...

   // Change the columns to binary form.
         $smcFunc['db_query']('', '
            ALTER TABLE {raw:table_name}{raw:updates_blob}',
            array(
               'table_name' => $table_info['Name'],
               'updates_blob' => substr($updates_blob, 0, -1),
            )

BlackPowderSmoke

Thank you to all that advised me in this manner. I think I have properly followed the given advice, but no resolution.

Sir Osis of Liver

Remove the fulltext index in Admin -> Search -> Search Method.  If it clears the error, you can recreate it.
 
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

BlackPowderSmoke

Quote from: Sir Osis of Liver on May 22, 2020, 08:57:26 PM
Remove the fulltext index in Admin -> Search -> Search Method.  If it clears the error, you can recreate it.

Thank you for the information. I removed the index and the problem persists.

I do want to reiterate that it only affect some threads. I was able to recover the text by opening and saving the post as in the first post here...     https://1858remington.com/test/index.php?topic=12230.0

Doug Heffernan

Quote from: BlackPowderSmoke on May 20, 2020, 06:20:48 PM
Quote from: Kindred on May 20, 2020, 06:04:37 PM
don't mess with that one.

your problem is in the table smf_log_badbehavior

Thank you. I deleted the key column in the  table smf_log_badbehavior

Now I have a new database error message...

QuoteColumn 'body' cannot be part of FULLTEXT index
File: /home/eartjylo/1858remington.com/test/Sources/ManageMaintenance.php
Line: 664

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.17, while your database is at version 2.0.6. The above error might possibly go away if you execute the latest version of upgrade.php.

Most likely your fulltext index had utf8_unicode_ci on one of its columns, and utf8_general_ci on its other columns.

All columns of a fulltext index must have not only the same character set, but also the same collation.

BlackPowderSmoke

Quote from: doug_ips on May 23, 2020, 11:58:18 AM
Quote from: BlackPowderSmoke on May 20, 2020, 06:20:48 PM
Quote from: Kindred on May 20, 2020, 06:04:37 PM
don't mess with that one.

your problem is in the table smf_log_badbehavior

Thank you. I deleted the key column in the  table smf_log_badbehavior

Now I have a new database error message...

QuoteColumn 'body' cannot be part of FULLTEXT index
File: /home/eartjylo/1858remington.com/test/Sources/ManageMaintenance.php
Line: 664

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.17, while your database is at version 2.0.6. The above error might possibly go away if you execute the latest version of upgrade.php.

Most likely your fulltext index had utf8_unicode_ci on one of its columns, and utf8_general_ci on its other columns.

All columns of a fulltext index must have not only the same character set, but also the same collation.

Thank you. This is a test site so I can always refer to the original site or update the test site as needed. How do I correct this or can I?

Advertisement: