Topic: Mysql57 to mariadb restore

[jameswilson]

Hi All
Moving some stuff from 9 to a new sme 10.
I have done a mysql dump (from mysql57 on the v9 machine) all fine

mysqldump57 -u root cxxxxxxxxxx3 > cxxxxxxxxx3-1.sql

all fine

moved the file over to the new machine

 mysql -u root cxxxxxxxx3 < cxxxxxxxx3-1.sql

then I get this error

ERROR 1273 (HY000) at line 129: Unknown collation: 'utf8mb4_unicode_520_ci'

The reading of dome looks like it maybe due to the version of maria?
never having worked with mariadb before im unsure where to start.
Im moving a lettle website first then moving up to bigger things

Thanks

James

[ReetP]

What was the application using Mysql 57?

Wordpress at a guess

https://duckduckgo.com/?t=ffab&q=mariadb+Unknown+collation%3A+%27utf8mb4_unicode_520_ci%27&ia=web

Inevitable Stack:

https://stackoverflow.com/questions/42385099/1273-unknown-collation-utf8mb4-unicode-520-ci

Have a thorough read, and then test on a TEST machine.

[jameswilson]

Hi Reet

Yes it is wordpress

I followed the guides to edit the sql dump file and it imported it via the cli.

Many thanks

James

[ReetP]

Cool. Must be time.... 

[jameswilson]

Lol definetly

James

[jameswilson]

Hi all
Still trying to move stuff over to new sme10 servers, im not doing a backup restore on these just moving files and db's

anyhow ive resolved most of the db issues with the following commands

sed -i 's/utf8mb4unicode520_ci/utf8mb4_unicode_ci/g' sqlfile.sql

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' sqlfile.sql

this allowed the mysql dump files from mysql57 to mariadb on sme10

but im testing one of the larger site and have a new issue

 

mysql -u root wordpress123 < mysqldump.sql

ERROR 1064 (42000) at line 736: 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 'json NOT NULL,
  PRIMARY KEY (`cache_id`),
  UNIQUE KEY `cache_key` (`cache_key`' at line 4

Ive noticed in phpmysql i cant repair a database but doing an optimise results in some errors. I did the optimise and repair then made a new dump file in the hope that would help.

The db is working on the current site via mysql57 so isnt totally corrupted and im unsure if these errors are relevant

Or can I add mysql57 to sme 10 or upgrade mariadb.
I remember upgraded the db a bad idea from sme 7 days

Thanks

[jameswilson]

Found from google that my error was due to a db structure stating the value cannot be null but didnt have a default value set.
So ive added default values on the assumption that the value is set on creation by a plugin.

So passed that error now have a new one

ERROR 1709 (HY000) at line 736: Index column size too large. The maximum column size is 767 bytes.

I have found the following fix but dont want to apply it as i dont understand if it will cause issues

When you run a command to create a MySQL database table, you might see an error message saying “Error 1709: Index column size too large. The maximum column size is 767 bytes”.

This happens if you create a ROW_FORMAT=COMPRESSED table with utf8 encoding; it makes the table’s row too large.

You can avoid this problem by using ROW_FORMAT=DYNAMIC for that table instead.

I assume this is something caused by moving from a newer version and changing the type.

[ReetP]

I think that these are more likely issues from the app responsible rather than SME itself (which doesn't touch internal DB structure)

I would bet it is due to misbehaving Wordpress plugins or similar.

Treat it as a DB/Wordpress issue rather than an SME issue.

Have a read on mysql 57 -> maria Db to check if there are any specific issues.

Wondering if mysql 5.7 -> maria 5.5 is an issue?

Might be you need a higher version of Maria DB. Hmmmm.

JP might know.

[jameswilson]

Oh i dont think its a sme issue with this one. I think its the version number of mariadb being older than the mysql57.

[Jean-Philippe Pialasse]

you will need a mysql57 contrib portage or one for mariadb 10.x based on sclo

easier way would be to start porting the sme9 mysql57 contribs based on sclo
 i can port it, but i will need systemd adaptation.

would need feedback if i do that, and help from anyone with build rights as i am quite busy currently. 

sclo allows to have a second sql server aside to the initial mariadb server. 

sclo has
rh-mysql57
rh-mysql80
rh-mariadb101
rh-mariadb 102
rh-mariadb 103
rh-mariadb 105


as pointed by John in a bug direct upgrade of mariadb in place is a dangerous path.

[jameswilson]

as pointed by John in a bug direct upgrade of mariadb in place is a dangerous path.

yes i remember it being bad from years ago.

Is there a performance hit using sclo?

Ill help where I can, i can certainly test it. Id assume going for latest mariadb would make sense?

[Jean-Philippe Pialasse]

Is there a performance hit using sclo?

not more than having few other services running on a machine
for sure there is a few MB of memory used per service, but CPU should not be more used by calling 500 request to one service or 250 to one and 250 to the second.

Ill help where I can, i can certainly test it. Id assume going for latest mariadb would make sense?

prepare a test VM

[Jean-Philippe Pialasse]

bug : https://bugs.koozali.org/show_bug.cgi?id=11877

wiki entry on how to install : https://wiki.koozali.org/Mysql57#Installation
as it is

report to the bug any issue, or any success

should work, was able to get the service starting without reboot, however not tested if phpsqladmin works out of the box with it. report if any issue also there

[jameswilson]

Great thats absolutely brilliant

Ran the commands. After the first it wanted a reconfig, reboot. Did that all ok

can login to mysql57 command line.

But not showing in phpmyadmin as far as i can see?

Thanks

[jameswilson]

Ignore above, i hadnt opend port 3309 etc.

Continuing tests!

James