Advertisement:

Author Topic: Can not convert database and data to UTF-8  (Read 988 times)

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Can not convert database and data to UTF-8
« on: May 20, 2020, 11:06:40 AM »
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?

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 72,639
    • StoryBB/StoryBB on GitHub
Re: Can not convert database and data to UTF-8
« Reply #1 on: May 20, 2020, 11:08:45 AM »
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.

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #2 on: May 20, 2020, 11:27:19 AM »
Thank you. Which php version is  the newest  supported by SMF?

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 72,639
    • StoryBB/StoryBB on GitHub
Re: Can not convert database and data to UTF-8
« Reply #3 on: May 20, 2020, 11:37:33 AM »
7.2 or 7.3.

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #4 on: May 20, 2020, 11:56:04 AM »
Thank you. I tried 7.3 and 7.2, then  7.0 with the same result.

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 59,299
  • Gender: Male
    • Kindred-999 on GitHub
Re: Can not convert database and data to UTF-8
« Reply #5 on: May 20, 2020, 12:10:14 PM »
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.
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #6 on: May 20, 2020, 12:15:13 PM »
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.

Offline cicstim

  • Semi-Newbie
  • *
  • Posts: 24
Re: Can not convert database and data to UTF-8
« Reply #7 on: May 20, 2020, 12:58:07 PM »
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...

Offline Sir Osis of Liver

  • SMF Super Hero
  • *******
  • Posts: 10,231
  • Hoarding Budweiser in NY
Re: Can not convert database and data to UTF-8
« Reply #8 on: May 20, 2020, 01:02:18 PM »
Run query -

Code: [Select]

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


Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #9 on: May 20, 2020, 05:41:17 PM »
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?

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 59,299
  • Gender: Male
    • Kindred-999 on GitHub
Re: Can not convert database and data to UTF-8
« Reply #10 on: May 20, 2020, 06:04:37 PM »
don't mess with that one.

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

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #11 on: May 20, 2020, 06:20:48 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...

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

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 59,299
  • Gender: Male
    • Kindred-999 on GitHub
Re: Can not convert database and data to UTF-8
« Reply #12 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

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

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #13 on: May 20, 2020, 10:43:38 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.

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #14 on: May 20, 2020, 10:44:53 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...

Quote
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),
            )

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #15 on: May 22, 2020, 08:17:36 PM »
Thank you to all that advised me in this manner. I think I have properly followed the given advice, but no resolution.

Offline Sir Osis of Liver

  • SMF Super Hero
  • *******
  • Posts: 10,231
  • Hoarding Budweiser in NY
Re: Can not convert database and data to UTF-8
« Reply #16 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.
 

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #17 on: May 23, 2020, 11:40:03 AM »
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

Offline Doug Heffernan

  • Full Member
  • ***
  • Posts: 567
    • An Unofficial King of Queens Forum
Re: Can not convert database and data to UTF-8
« Reply #18 on: May 23, 2020, 11:58:18 AM »
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...

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

Offline BlackPowderSmoke

  • Semi-Newbie
  • *
  • Posts: 34
Re: Can not convert database and data to UTF-8
« Reply #19 on: May 23, 2020, 12:27:08 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...

Quote
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?