Koozali.org: home of the SME Server
Contribs.org Forums => Koozali SME Server 10.x => Topic started by: jameswilson on February 03, 2022, 05:03:46 PM
-
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
-
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.
-
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
-
Cool. Must be time.... :pint:
-
Lol definetly
James
-
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
-
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.
-
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.
-
Oh i dont think its a sme issue with this one. I think its the version number of mariadb being older than the mysql57.
-
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.
-
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?
-
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
-
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
-
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
-
Ignore above, i hadnt opend port 3309 etc.
Continuing tests!
James
-
hum the reason it was not showing first in phpmyadmin is i forgot to expand the template of phpmyadmin config. will fix that latter.
yes the first step for the repo to config need a small fix too. reboot should not be asked, the update event is not named correctly.
-
Id say this contrib is a go, works exactly as the old one, rebooted etc all seems great.
Brilliant work once again!
Thankyou
James