Rebuild database without reinstalling everything

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.

Hi, define everything :slight_smile:
It’s starts to be tricky if you have companies, if you want to preserve clicks and email visit information.

1 Like

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?

Was database exported from MySQL 8?

From my experience once I went past MySQL 5 and anything I exported from it give me issue when trying to import it into MariaDB.

1 Like

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.

Thanks for the suggestion, @techbill. No it wasn’t. I’ve been on MariaDB since forever/long time as far as I know.

@joeyk, I don’t know how it got damaged; I’ve never done anything unusual as far as I know. I do get foreign key constraint errors.

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.

OK, I posted on the other forum thread, you mentioned. Do you have special characters in the Mautic user names?

Thanks for response @joeyk. There is only one user on the system, no special characters in username.

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:

1 Like

what is the username_mautic field for?

Thanks @Nick_J - good suggestion. When I run mysqlcheck my_mautic_database, the result is OK for every table. Should I dig deeper?

@joeyk referred 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.

A post was split to a new topic: How to work with campaigns

@cheska It’s disrespectful ! you have to create your own thread.

1 Like

@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 :slight_smile:

Empathy or hypocrisy?

I will also troll the discussion by answering you @rcheesley (excuse me @modifiedcontent)

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.


1 Like

do you have any errors in mariadb logs?

I tried mariadb-check, but it wasn’t available on my server and is apparently a symlink to mysqlcheck anyway. Where can I find the mariadb logs?

Thanks for reviving my “old thread” - 3 days…

here /var/log/mariadb/mariadb.log

1 Like

@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.

1 Like