Rebuild database without reinstalling everything

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: https://mariadb.com/kb/en/mysqlcheck/

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.

Regards

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

@techbill, problems apparently start when you (try to) convert the database from MYISAM to InnoDB or/and change collation - utf8_unicode_ci or utf8mb4_sumthing_?

I think I’ve solved my original issue related to that, but my database is now a mix of InnoDB and MYISAM - some MYISAM tables won’t convert, not sure if I’d only cause more problems if I forced it somehow. Also not sure what recommended collation etc. is.

I am still curious to see an answer to the original question of this thread, how to do a clean surgical rebuild of the database without losing data and without having to reinstall everything.

We recently converted our database from Latin to utf8 as well too.

What we did was to set up in my.cnf that it defaults to urf8 and InnoDB then export all the database from Latin. (my.cnf is only accessible by root / server admin)

We deleted and cleaned up all old database then we imported it back into which get stored as utf8 in InnoDB.

1 Like

Thanks. Good info. I had set up those defaults in my.cnf, haven’t tried exporting and importing the whole database yet - no data loss?

Wasn’t utf8mb4 recommended for Mautic?

And I think converting MYISAM to InnoDB is what causes most problems.

Would simply exporting, importing a mixed MYISAM/InnoDB database into default InnoDB still work?

No data loss as far we could see but we do have a back-up and a back-up of back-up before attempting this.

If I recalled correctly, I exported into SQL then delete the whole database completely.

I edited the my.cfg setting the default to utf8 and InnoDB then import the “SQL” back into the newly created database.

That was a typo in my earlier post. Sorry about that.

Yes, utf8mb4 is what I went with.

Here a partial screenshot of my parital DB after importing it back in.

1 Like