Since there seems to be no way to fix constant database errors (and here) (?), is there a way to manually rebuild the database without having to reinstall everything else or do any Composer crap etc.? In the most surgical, non-invasive way.
Something like > export/backup existing data > delete the database > “install” new database tables > import backed up data in the fresh tables
That process has probably already been described somewhere, but haven’t found it yet. Will do a more thorough search later.
Thanks for response @joeyk. I want to preserve all data, without having to reinstall any application files; just rebuild the database tables, then put back the data - of course including companies and visit data etc.
If that’s not possible, what is the most surgical process to fix/repair a Mautic database?
It is not hard if you know what is damaged and how it got damaged. (Overwritten for example.)
The hardest is the preserve email opens for example.
If the ID of specific data is matching, then you can nicely place back. The problem is with tables referring to eachother and solving key constrain issues.
At this point I am less interested in figuring out “how it got damaged” - there are apparently no answers or solutions anyway. I am not even entirely sure which problems are connected. I am looking for the least invasive solution to restore and be able to move forward.
Start by analyzing the tables and try to repair the indexes.
mysqlcheck is a maintenance tool that allows you to check, repair, analyze and optimize multiple tables from the command line. Read more: https://mariadb.com/kb/en/mysqlcheck/
Thanks @Nick_J - good suggestion. When I run mysqlcheck my_mautic_database, the result is OK for every table. Should I dig deeper?
@joeykreferred to this here. When I post code/error message here I usually replace actual usernames, server paths, my own custom prefix, etc. with something like that.
Since the database is OK according to mysqlcheck, I am going to try update to 4.4.3 first to see if that fixes database errors I see elsewhere. Edit: It didn’t.
@Nick_J new users don’t have the permission to create a new post initially but they can reply - so that is probably why they responded to an old thread.
I have broken it out as a new post, maybe consider a slightly more empathetic response in future
I received a notification for the problem of @modifiercontent, by clicking on the link to come read and help it I come across someone who is participating in the discussion to ask for help for another problem.
I am a new user and I knew how to read the rules and apply them without problem because we are well guided with information popups displayed throughout the forum to guide us through the steps. So no real error, especially since the response form can’t really be confused with the form to create a new message.
In short, it’s just a matter of respect. If he wants to have access to the functionality then who follows the steps as we have all done or you may have to delete the steps to allow the new member to participate in the forum as soon as they register.
My answer was not excessive but fair. It’s a pity that I have to troll the discussion to answer you on the other hand.
@Nick_J - I don’t seem to have that path on my server. My Bluehost VPS is configured via WHM/cPanel/EasyApache etc. I don’t think mariadb is used anywhere; it’s all mysql even if it presumably links to mariadb stuff.
I have /var/lib/mysql/username_mautic/ - CentOS default - with lots of .opt, .frm and .ibd files that Bluefish editor can’t read and in Notepad++ it’s lots of NULL NULL with gibberish sprinkled in. Googling now what this is…
FRM file is used by MySQL Server to store table schema and .IBD file is used to store data and indexes of a MySQL database .
You can only read these .frm and .ibd files “with MySQL”, so I guess somehow via command line or PHPMyAdmin? There is no Windows desktop MySQL client that would be able to read these files I guess?
@joeyk, you asked earlier what username_mautic referred to, something I had in my error message on the other thread. It is my mautic database name, as above in /var/lib/mysql/username_mautic/.
Could there be a mismatch between how CentOS stores table schema and indexes and how/where Mautic expects it to be done?
We run CentOS 7 / cPanel / MySQL 5 on Bluehost. We are not having any issue with Mautic on it database related.
If I recalled correctly that NULL is normal to see in columns with empty string if MySQL is running in strict mode. There can’t be empty data in column instead it have to have a string “NULL” in it.
Again, I am not sure but I think it’s what I remember about the NULL and strict mode in MySQL.
