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?
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.
Thank you. Which php version is the newest supported by SMF?
7.2 or 7.3.
Thank you. I tried 7.3 and 7.2, then 7.0 with the same result.
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.
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.
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...
Run query -
SELECT table_name,table_schema
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name='key'
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?
don't mess with that one.
your problem is in the table smf_log_badbehavior
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.
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
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.
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),
)
Thank you to all that advised me in this manner. I think I have properly followed the given advice, but no resolution.
Remove the fulltext index in Admin -> Search -> Search Method. If it clears the error, you can recreate it.
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
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.
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?
If you've removed fulltext index and search is set for no index, collation of index columns is not the problem. You should not still be getting the same error. If you've broken the badbehavior mod, you'll need to remove it manually, or start over with a clean set of files.
BlackPowderSmoke, as youi not reeplied in a few weeks im guessing this is solved and marking it as such, if you still need help let us know and we can help further, we will need to know what you have done since you last visited and updated us,