mysql.infoschema'@'localhost' does not exist

[EdderShredder]

Hi guys

Sorry for the bad subject line.

I'm having an issue on a fresh install after coming from a 2.4 installation and percona.

Followed all of the instructions to the letter.

I dropped the default database and imported my old one.

Now, when I go to backup the current database using

sudo mysqldump -u root -p -f --all-databases > 11.04.2020.sql

It is saying

mysqldump: Error: 'The user specified as a definer ('mysql.infoschema'@'localhost') does not exist' when trying to dump tablespaces
mysqldump: Couldn't execute 'SHOW DATABASES': The user specified as a definer ('mysql.infoschema'@'localhost') does not exist (1449)

Otherwise site is working perfectly. But I need to back it up. At the moment I have the archivist on strict instruction not to add accessions or any data until this is sorted. Any ideas guys?

[Dan Gillean]

Hi there, 

I've not seen this error before, and am not aware of such a user needed in AtoM. 

If your server has more than one database on it, then running the mysqldump command as you've shown it in your email may be causing problems. Rather than specifying the name of your AtoM database, you've used --all-databases, which we do not recommend anywhere in our documentation. You're also using -f (force), meaning if there are errors, you're not seeing them at the time you create the dump!

I recommend just running the dump command like so: 

• mysqldump -u myusername -p mydbname > /path/to/mybackupfile.sql
  

So, for example, if your MySQL username is root, and the database name is atom, this might look like: 

• mysqldump -u root -p atom > 11.04.2020.sql

If you can't recall your database name, user, and password used in MySQL during installation, you can either check in /root/.my.cnf, or config/config.php to see the credentials. 

See: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#accessing-the-mysql-command-prompt
  
• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#backing-up-the-database

The other thing I noticed - you mention dropping the old database, but did you remember to also recreate it after dropping, before loading your data? See steps 3-5 in the "Copy your old data" section of the upgrading docs: 

3. If you’re upgrading from 2.5.x or lower to 2.6.x or higher make sure your data is on utf8mb3 or utf8 (the default if you followed the installation documentation) and that you are using MySQL 8.0 as that’s a requirement since AtoM 2.6. During the upgrade task, your data will be transformed to the utf8mb4 charset and the utf8mb4_0900_ai_ci collation.

4. Re-create the database with the new charset and collation:

mysql -u username -p -e "DROP DATABASE IF EXISTS atom;"
mysql -u username -p -e "CREATE DATABASE atom CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;"

5. Now, load the contents into the new database:

mysql -u username -p atom < /tmp/atom_db.sql

• https://www.accesstomemory.org/docs/latest/admin-manual/installation/upgrading/#copy-your-old-data
  

Let us know if this helps! 

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/54e3e7de-dea0-4f16-9d03-2f8c347f67d9n%40googlegroups.com.

[EdderShredder]

Yes the mysqldump -u root -p atom > backup.sql gives the same error.

There must be an issue with the way the DB was imported.

I used the -f to try and diagnose the issue but the same error was thrown.

I will not use --alldatabases anymore.

I may have to recreate the upgrading steps you link above.