I’m having problems with some customers, after the database was updated to version 10.2.35. Customers using Laravel (older versions) have reported errors on their website and many have stopped working after this upgrade. Has any website presented this problem?
Sorry for the late reply, I’ve been researching but have not found much on this other than code issues. Most cases that error means that an empty array is being returned. Too generic an error to know for sure if its related to the mariadb upgrade.
I could not find a mariadb \ mysql compatibility matrix for laravel versions either. So I cannot confirm if that is the issue, but this is likely if the data is intact. Would have to put the site back on the original mariadb version with the same data to confirm.
I would run this by an experienced Laravel developer first, if possible.
If not my next step would be to downgrade mariadb, which can be a bit of a pain.
To downgrade you would mysqldump (backup) all dbs and privilege’s then reinstall the original mariadb verion with an empty datadir, restore the dumps and privilege’s.
If you took a backup of the original versions datadir before upgrade you could reinstall mariadb with an empty datadir then stop mariadb and restore the original datadir then start it up again. Don’t try to start up an older mariadb version using an upgraded datadir.
If you need more specifics on how to fully remove the existing mariadb version and install the old one let me know.
Hi inventdigital,
I had two cases wherein I moved a website to a newly setup Interworx server and then that website wasn’t working properly, no values were being written, turns out that Mariadb’s sql_mode defaults to strict.
Ex: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Can just set it to none
in /etc/my.cnf
Add: sql_mode=""
then restart mysql
After I did that, the websites worked.
1 Like
Thanks @dcoeli and Welcome!
The sql_mode settings would normally persist with an upgrade as yum does not over-write those conf files. Depends on the version @inventdigital upgraded from. If the original version was prior to strict mode becoming default this could be the issue.
Hi
We do not use laravel but @nico does and has very recently tested moving a laravel site from cpanel to IW (centos 7 - IW-CP 6.9)
The reported blank pages (website not working or displaying) and as well as the MariaDB my.cnf setting, had to change PHP to 7.3 as it is a bug in MariaDB for PHP earlier re connections.
So, if still not working, change PHP to 7.3 and it should start to display if it is same issues as seen by Nico
Kudos to @Nico
Many thanks
John
Hi whatever you do, DO NOT downgrade MariaDB this will give you more issues. The problem is with MariadDB update causing this, there is a patch and good bug report about this. One solution is to upgrade to phpv7.3 or higher. But please do realise your clients using ‘old’ software so best would be to realy advise them to upgrade everything [ Laravel and PHP coding ] When the patch will be applied by Interworx I can’t say, but cPanel has already patched it. Friday the 13th was my lucky day to be confronted with it 
Here are some details: Bug: MDEV-24121
https://jira.mariadb.org/browse/MDEV-24121
Note: enable debug in .env file, this will give you much more information.
1 Like
Hi
Just a word of warning re setting my.cnf detail as posted earlier
Can just set it to none
in /etc/my.cnf
Add: sql_mode=""
then restart mysql
After I did that, the websites worked.
This will stop the import of databases using Phpmyadmin and also import from another server (cpanel and I guess interworx, plesk etc…)
It creates the database(s) but does not populate them, so the databases have no tables
also you cannot run mysql_upgrade after a MariaDB update
Kudos to Nico for reporting this issue and resolving this issue over many Hours yesterday
Many thanks
John
@d2d4j Just disabling strict mode by setting sql_mode="" broke IW and phpmyadmin imports?
@d2d4j @IWorx-Joseph, sadly YES, none of the databases got imported. Did 4 imports and randomly users where NOT assigned to the databases had to manual assign user to DB or correct the rights/priviliges. We also noticed that the import tool is stil not importing folders above cPanel public_html.
Both cPanel and Iworx where having same MariaDB version 10.2.36
Nico
Hi
@IWorx-Joseph @Nico many thanks
I am sorry I do not think I was very clear
Please see pictures showing the failure (this was IW to IW import, both latrest IW-CP v6.9 but from centos6 to centos7)
This is with the posted fix active and as you can see, the import stated successful but the database is zero bits and it has created the database, but not imported any tables/data into the database
If the posted fix is not active, the database is created and fully populated.
It also shows same issues when importing a DB through MyphpAdmin and Cpanel
@nico sorry I think you may confuse IW as the issues you are posting as separate to the posted fix.
However, they are valid points so to save confusion, the posted fix is as above and Nico additional points are:
Correct rights/priviliges to user DB
Cpanel import not importing folders above Cpanel public_html
Nico import was from a Cpanel to IW-CP v6.9
Database was same on both - MariaDB 10.2.36 and the site been imported was a laravel site
I hope that helps a little in explaining the issue
@IWorx-Joseph - If you want, I can let you on the servers and let you know a domain to test or you can create a test yourself on a test server
Many thanks
John
We probably should have a ticket for this and post the access info in that ticket, if we need to take a look. Disabling strict mode via sql_mode="" is not uncommon. I suspect there might be a work around that allows for disabling strict but does not break phpmyadmin or our importer.
Default sql_mode for mariadb 10.2:
"STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
So try setting the following, this keeps all the other defaults but removes STRICT_TRANS_TABLES, enabling it.
sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Looping support in, in case a ticket is needed @IWorx-Jenna @iworx-brandon
Hi
@IWorx-Joseph @IWorx-Jenna @iworx-brandon - many thanks
I will try that over this weekend if alright, and let you know or if you prefer, I can open a support ticket and let you test yourself.
To be clear, it affects all importing, IW Phpmyadmin and cpanel. The other imports say from plesk etc… I cannot test
I cannot test it to see if Laravel though still works with the new settings unless @Nico has left his test on 7hi. If not, perhaps @Nico could test to see if it works on Laravel. @Nico has better understanding of Laravel then myself as I have never used Laravel
Many thanks
John
Hi
@IWorx-Joseph @IWorx-Jenna @iworx-brandon I have tested and sorry it did not import database as expected. 0kb and no tables imported
I have opened a support ticket with all details needed to test and set to no rush, so whenever you have spare time
Hope that’s alright and have a lovely weekend
Many thanks
John
I saw the ticket you submitted–I’ll do some digging for you, tomorrow. Thanks for testing! 
Hi @IWorx-Jenna
Many thanks, your a star but there’s no rush so enjoy your weekend.
Also, which I do not think was mention sorry, when @Nico tried to import from Cpanel, this also stopped the import of SSL but thinking perhaps the import script upon failure of MySQL (which does not show any failure and shows completed) stops the next import action.
With MySQL mode="" the Cpanel imported did not work fine but with it hashed out did and with SSL (apart from the known issue over folders abovepublic_html)
I think that was everything we noted and Nico was using 7hi as a test for cpanel account import, so his servers would not have any issues, so this means all settings were default
Please remember 7hi is the server to use, and import from haveworx into 7hi. Then you can change my.cnf as you need on 7hi.
As I said though, no rush and enjoy your weekend
Many thanks
John
@d2d4j It looks like the sql_mode declaration is not happening under the right ini header. Running the test that you ran (with just a bare sql_mode declaration in my.cnf) output the following in ~iworx/var/log/iworx.log as I imported:
2020-11-29 11:48:07.83420 [3cxt3g-2rpk-z7dm-CLI] [INFO] : /usr/bin/mysql --defaults-file=/home/brandon/brandon.com/defaults56GjrV --user=brandon_import --socket=/var/lib/mysql/mysql.sock --compress brandon_sample : /usr/bin/mysql: unknown variable 'sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' : import.php
(Forgive my lack of creative names. Ha ha)
Looking this error up elsewhere, I came across (https://stackoverflow.com/questions/59575541/mysql-error-unknown-variable-sql-mode-strict-trans-tables-no-zero-in-date-no) which showed that one potential reason was defining the sql_mode in the wrong place. In their case it was under [mysql] and not [mysqld]. Since just adding that directive to my.cnf, adds it under the [client-server] heading, it’s possible we’re essentially doing the same thing. Consequently, I was able to get the import working adding the following to my my.cnf file and restarting mariaDB:
[mysqld]
sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Since that worked, I tried a further step to just get back to the empty sql_mode that was original mentioned. The import worked with the following section in my my.cnf:
[mysqld]
sql_mode=""
It looks like there is already a section defined for [mysqld] in /etc/my.cnf.d/server.cnf so that might be the best place for the sql_mode declaration. I tried it from this location, and the above worked, as expected.
Please give that a shot and let me know if you still face issues with the db contents showing up. This does seem like something that should be handled more gracefully, so I will make a note with the devs.
1 Like
It is really much more easier, what I do is I go in Nodeworx Control Panel, MySQL server, Press the save button in the MySQL Server Options section, once pressed save, it creates/add with those [default] or modified by you the settings in /etc/my.cnf with the [mysqld] section in it. That [mysqld] is with a default installation not there… the cPanel import was OK after that, I had to add the extra user on the DB manually but tables where imported fine. But I made sure both on cPanel and Nodeworx server that the MariaDB was the same version. Hope that helps.
P.S. the SSL was imported ok, but had some of the cPanel directives in it, so I always re-generate a new one with LE.
More P.S. the bug with the name servers and localhost in the SSL was still there in v6.9.0 
Nico
Hi Nico, is this issue referenced somewhere else? I must have missed it. Can you explain the issue that you are having?
Sure, this one: https://appendix.interworx.com/6.x/changelog/6.9.x/version-6-9-0-build-1810.html
When I renewed the LE SSL I noticed it had localhost, the name servers and server name in the list.
No problem for me I only selected what i wanted
Kind regards,
Nico
My Fault, I am not on Beta… sorry.
1 Like
Hi
@iworx-brandon - your a star and completely correct. I am sorry, I did place the code in client side and not mysqld section.
I must have looked at that a lot and never realised sorry. Kudos to you and yes, all working as expected.
@Nico - Kudos to you and no need to apologise, we all make mistakes especially me but you raise excellent points
Many thanks
John