Topic: Can not convert database and data to UTF-8

[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]

7.2 or 7.3.

[BlackPowderSmoke]

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

[Kindred]

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]

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]

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 -

Code: [Select]


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



[BlackPowderSmoke]

Run query -

Code: [Select]


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

[BlackPowderSmoke]

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...

Column '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

[BlackPowderSmoke]

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]

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...

Column '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.
 

[BlackPowderSmoke]

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]

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...

Column '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]

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...

Column '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?