Stuck on restore of data on backup server

Support
1

Situation
I am testing my backup server and I have attempted to restore emr_backup.tar after I enter the path and answer all question as default answer including the utf type , I get the following error;

Restoring OpenEMR database …
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CHARACTER SET utf8 COLLATE utf8_general_ci’ at line 1
Error creating MySQL database with ‘CREATE DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci’.
I then created the database in mysql and the ran restore script again and received the same error. Am i missing something ?

OpenEMR Version
I’m using OpenEMR version 7.0.0

Browser:
I’m using: Chrome

Operating System
I’m using: Putty on windows 10

Search
Did you search the forum for similar questions?
yes
Logs
Did you check the logs? yes
Was there anything pertinent in them? yes
Please paste them here (surround with three backticks (```) for readability.
'tyran@119w:~$ sudo less /var/log/mysql/error.log
2024-06-18T17:19:39.222646Z 14 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ‘‘mysql_native_password’ is deprecated and will be removed in a future release. Please use caching_sha2_password instead’

You can also turn on User Debugging under Administration->Globals->Logging User Debugging Options=>All

2

try Use:

CREATE DATABASE YourDatabaseName;

instead of
CREATE DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;

3

Ok. The classification of the database takes place during the restore operation, so I just chose default as instructed. I will try this. Thanks.

4

The didn’t work but changing the utf8 code to mb3 is apparently needed as it has been updated and utf8 is deprecated; however, I still get an error and the error log is empty; Please see below;

'Extracting /home/tyran/emr_backup.tar …
Extracting /tmp/emr_backup/openemr.tar.gz …
ls: cannot access ‘sites’: No such file or directory
grep: sites//sqlconf.php: No such file or directory
grep: sites//sqlconf.php: No such file or directory
grep: sites//sqlconf.php: No such file or directory

Do you want to specify site ID, locations or database names for the restore? [N/y]’

5

Hi
how big is your database?

if tis a test database, you can send me a download link and let me try on my server.

6

Hi it is the actual tarball. 8g . Just trying to make sure it is working. Also you should know that that database was created using mysql 5.7 and the new server is running 8.0. The use of native passwords have been deprecated and not used in 8.0. So i was going to just roll back to 5.7 and give is a shot if I ma able to still download it. I will give that a try first if you think that is a good idean?

7

The 5.7 rollback did not work. What is the easiest way to send you the link. I have never done this before will my database?

Thanks

8

Honestly, MySQL8 is crap.
If your original host was MySQL 5.7, and you’re upgrading, just use MariaDB >= 10.5, and be done with it. Oracle is likely to kill off community MySQL at some point anyway, so it’s easier to make the jump now and have one less thing to worry about later.
All that said, if your backup is missing the ‘sites’ directory, then you may have issues.
First things first, you need to verify if you actually have an intact SQL dump file to restore your database from. If not, then you may want to explore a more robust method of backup/restore or data replication.

9

Well. I have attempted to open the file openemr.sql.gz on the backup server. When imported it was empty. it says the file itself is 10 bytes? Makes no sense to me. The tarball is almost 8gigs. I am happy to use Maria. I have never used it but I will try with that. Before we were using openemr built in GUI backup but that has stopped working. I think the file may be too large. So I set up the backup server and just want to make sure it works as the production system needs be updated but before I do that I need to have a confirmed backup.

10

I have also on the backup setup mariadb and I get the same error when using emr restore. When I try to import the database directly into mariadb I get “unknown database - openemr”

11

8Gb… this coul db causing the issue too.
Have you edited the php.ini file to increase the file upload size? by default its small.
You will have to manually change the values in php.ini file.
if your hosting has provided u a graphical way to edit php.ini file then good. else you will have to do in terminal.
also there might be 2 php.ini files , one is for cli and the other for graphical interface.
Pl do lemme know if you need help in editing these files. You can connect on whatsapp if you need to.
Sent you a s pm also.
regards

12

I could be wrong here, but you’re probably going to need to create the (empty) openemr database, and grant access to it for the user credentials defined in your sqlconf.php file.
The restore script is trying to populate the tables back into the database, but it has no target database and no access to pipe those table contents into it.
Are your application and database living on the same same host, or are they separate ? That will affect how your user grants are defined and what the contents of your sqlconf.php need to be.