Special characters issue

[gatesco]

I'm struggling with my forum in 2.0.19.

I updated MySQL 8. However, as a side effect, I'm having problems with special characters in the forum.

As a non-technical person, I looked through the various parameters and noticed that the character encoding of MySQL has changed to "UTF-8 Unicode (utf8mb4)" while the forum db encoding is "latin1_swedish_ci"

Again as a non-technical person, I assume that I have to convert the forum db to UTF8.

I downloaded the language package in UTF8 from the official site and uploaded it via ftp, overwriting the files when requested.

Then I tried to convert the database to UTF8 via forum maintenance but I get this error message:

Code Select Expand

Invalid default value for 'created_date'
File: /web/htdocs/www.********.it/home/Sources/ManageMaintenance.php
Riga: 699
I also tried to change the encoding of the db through MySQL, selecting the "utf8mb3_general_ci" encoding because I noticed that it is the same one adopted by SMF 2.1 when populating the db in a new installation.

Unfortunately it gives me this error:

Code Select Expand

Missing comma before start of a new alter operation. (near "DEFAULT CHARSET" at position 50)
Operazione di modifica non riconosciuta. (near "DEFAULT CHARSET" at position 50)
Query SQL: Copia Modifica Modifica

ALTER TABLE `Sql*****_4`.`backup_my_log_online` DEFAULT CHARSET=utf8mb3 COLLATE utf8mb3_general_ci

Messaggio di MySQL: Documentazione

#1067 - Invalid default value for 'logTime'

Can someone help me? 

Thanks a lot

[Kindred]

Well, the missing default is easy. Give it a default  (usually NULL)

[gatesco]

Well, the missing default is easy. Give it a default  (usually NULL)

Can you kindly describe to me how to do it? I'm sorry but I'm not very expert

[Doug Heffernan]

Can you kindly describe to me how to do it? I'm sorry but I'm not very expert

Go to your phpmyadmin and select the database that you have used for your Smf forum. Find the table that holds the aforementioned field and click the Structure tab. Select said field, click the Edit button, assign it a NULL value as default and then save the changes.

[gatesco]

Thank you so much!

Done! I tried again to convert the database to UTF8 via forum maintenance. Now I have this error:

Code Select Expand

Data too long for column 'reason' at row 5
File: /web/htdocs/www.*******.it/home/Sources/ManageMaintenance.php
Riga: 660
How can I repair it?

Thanks again for your support

[Doug Heffernan]

Data too long for column 'reason' at row 5

This type of error happens when the amount of data that is being inserted to a column that does not have the capability to store that data. i.e. the data is bigger than the defined/allowed data type of the column.

Anyways, you will have to change the datatype of that field. Most likely it is defined as VARCHAR. Try to  change it to TEXT and see if that would help.

Another way to go about this is to disable the strict mode as that is an extra control of mysql to avoid inserting invalid data. Hope it helps.

[gatesco]

I manually found - and repaired - all the "data too long". Then, I tried the database conversion and it worked.

Now I want to create a clone of the db in order to test the migration to SMF 2.1. So I exported the db but I can't import it due to "502 bad gateway" error. I think db is too heavy (350mb)...

How can I clone the db via PhpMyAdmin instead of export&import it?

[Chen Zhen]

It's likely a timeout error. You can attempt to increase PHP limits in your php.ini or via your server control panel (upload_max_filesize and post_max_size).
Prior to exporting the DB, clear all the logs. You can do export/import of larger tables separately like the messages table.

[gatesco]

PHP limits increased! Thanks again for the support.

It's strange now... I tested the migration to 2.1 of the "clone forum" in its subdirectory (obviously linked to the "clone db" previously imported) but, when I opened the subdirectory/upgrade.php it changed the directories of the main forum 

How is it possible?

Fortunately I noticed it in time and took action: I closed upgrade.php and I used repair_settings.php to repair the directories of the main forum.

What could I have done wrong?

[Illori]

you need to run What is repair_settings.php?

[gatesco]

Yes, I already run it and fixed the problem.

Now I'm a little bit scared of trying again the migration. Do you think it could change again the directories?

edit:
I've just tried. When I open upgrade.php, page is almost empty. There's only the blank list of the upgrade steps and nothing else. It's so strange, isn't it?

[Illori]

if you are moving your files/database for testing you need to follow https://wiki.simplemachines.org/smf/Hosting_-_How_do_I_move_my_SMF_forum_to_a_different_host