I installed 6.0.0 on a Fresh Ubuntu 20 install. I am trying to import the mysqldump from a working 5.0.2 into the 6.0. Import is interrupted by an ascii ‘\0’ error and some binary mode issue. I have looked on the internet to see how to resolve this error but to no avail. So, I decided to use mySQLAdmin. I exported from the 5.0.2 and try to import it into the 6.0. It gets an error without any clue of what kind of error it is. Has anyone had similar problem importing data from a dump into the new 6.0? I have searched here but haven’t seen anyone posting the error.
yes, I renamed the original OpenEMR then create a new OpenEMR database. The other difference is that the new v6 requires a new password strength so that password is different from v5.
The error is
ERROR: ASCII ‘\0’ appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII ‘\0’ is expected. Query: ‘’.
I have searched online to troubleshoot. The backup is not tar/zipped.
I tried to open the dump from PHPMyAdmin in Notepad ++ and below is the file property. It is not a binary file but I am still getting an error.
– phpMyAdmin SQL Dump
– version 4.6.6deb5ubuntu0.5
– https://www.phpmyadmin.net/
– Host: localhost:3306
– Generation Time: Mar 08, 2021 at 04:00 PM
– Server version: 5.7.33-0ubuntu0.18.04.1
– PHP Version: 7.2.24-0ubuntu0.18.04.7
SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8mb4 */;
You need to be talking apple to apples. Old dump DB is using UTF-8 and utf8_unicode_ci collation.
What is your new DBs encoding and collation? Should be what you’ve dumped but the encoding NAME is being set to utf8mb4. That means there will be a one byte difference on import where some data may get offset causing the error.
Goto your sites/default/sqlconf.php and change encoding from utf8mb4 to utf8.
$db_encoding = 'utf8';
or just comment out
//$sqlconf["db_encoding"] = $db_encoding;
Do I do that on the live system before the dump or on the new system before importing it in?
I have also tried this and I still get the same error.
mysqldump -u [username] –p[password] --default-character-set=utf8 -N --routines --skip-triggers --databases [database_name] > [dump_file.sql]
What’s going on?
@CalvinTy you have described all other tools you tried. But what was the error when you followed both hints offered in the original error message?
I tried looking for instructions on mysql running in binary mode or non-interactive mode on google. What I have found so far is not helpful nor clear on how other people resolved this error. I am wondering if other users here have this same problem. I am on 5.0.2 (4) and mysql collation utf8_general_ci. But when I do the 2 mysqldump methods, I get the above issues described above.
This is not an openemr issue. Collation/character set is specified in output of mysqldump. As long as it is valid on target system, problem is most likely created by operating system in either writing or reading the dump file. Suggest the following:
- Look at the accepted solution discussed in first google hit. I think they end up going through zip/unzip cycle to let target OS fix encoding issues.
- If size of your db is small, you can consider pt-table-sync to avoid issues introduced by files. There is bit of learning involved but pt-* tools are useful in multiple situations.
Best.
Thank you Jerry. As I stated earlier in the post, mine is definitely not zipped when I backup. A second proof that it is not zipped is that I can open it with Notepad+. So those solutions do not seem to apply to me.
That being said, may I ask what are the mysql settings that everyone is using. According to PHPmyAdmin, my mysql is set to utf8mb4 but my openemr database is utf8_unicode_ci. Would that be the source of the problem?
Another possibility is should I install Ubuntu 18 and OpenEmr 5.0.2 again and upgrade everything to Ubuntu 20 and OpenEMR 6.0 and see if that will resolve the problem?
Ok, I have finally solved the back up issue. I solved it my back up and restore problem by changing back up CLI format to mysqldump -u root -p -N --routines --databases --single-transaction [database] > openemr.sql.
With the above format, I can restore the openemr v5.0.4 into my new 6.0.2. After that, I did the sqlupgrade.php without a hitch. However, when I log in, the load time is slower and why I try to save a note, The error below pops up.
Query Error
ERROR: query failed: INSERT INTO keys (name, value) VALUES (?, ?)
Error: Duplicate entry ‘sixa’ for key ‘keys.name’
/var/www/openemr/src/Common/Crypto/CryptoGen.php at 433:sqlStatementNoLog
/var/www/openemr/src/Common/Crypto/CryptoGen.php at 231:collectCryptoKey(six,a,database)
/var/www/openemr/src/Common/Crypto/CryptoGen.php at 100:coreDecrypt(uzWb9x0mkpsW2M5ny2FnFX3GKh7ttIff2Rcfal2YJeWu+0jnaseFJSC+D/t59umSG7NjS+PoLO8F2uQtg07CzBMk2prLIJy0lOZjhbWDQNSQMs5F5cBDkeN+QHuxBuG15O0/Il0SSF5Tn6DvPYl8/A==,database,six)
/var/www/openemr/src/Common/Crypto/CryptoGen.php at 462:decryptStandard(006uzWb9x0mkpsW2M5ny2FnFX3GKh7ttIff2Rcfal2YJeWu+0jnaseFJSC+D/t59umSG7NjS+PoLO8F2uQtg07CzBMk2prLIJy0lOZjhbWDQNSQMs5F5cBDkeN+QHuxBuG15O0/Il0SSF5Tn6DvPYl8/A==,database)
/var/www/openemr/src/Common/Crypto/CryptoGen.php at 153:collectCryptoKey(six,a,drive)
/var/www/openemr/src/Common/Crypto/CryptoGen.php at 66:coreEncrypt(SHOW COLUMNS FROM form_soap,drive,six)
/var/www/openemr/src/Common/Logging/EventAuditLogger.php at 739:encryptStandard(SHOW COLUMNS FROM form_soap)
/var/www/openemr/src/Common/Logging/EventAuditLogger.php at 641:recordLogItem(1,patient-record-select,TT,Default,SHOW COLUMNS FROM form_soap,3230,Billing)
/var/www/openemr/library/ADODB_mysqli_log.php at 45:auditSQLEvent(SHOW COLUMNS FROM form_soap,1,)
/var/www/openemr/vendor/adodb/adodb-php/drivers/adodb-mysqli.inc.php at 632:Execute(SHOW COLUMNS FROM form_soap)
/var/www/openemr/vendor/adodb/adodb-php/adodb.inc.php at 1460:MetaColumns(form_soap)
/var/www/openemr/src/Common/ORDataObject/ORDataObject.php at 27:MetaPrimaryKeys(form_soap)
/var/www/openemr/interface/forms/soap/FormSOAP.class.php at 182:persist()
/var/www/openemr/interface/forms/soap/C_FormSOAP.class.php at 65:persist()
/var/www/openemr/interface/forms/soap/save.php at 24:default_action_process(Array)
The question now is what could cause this error?
Any thought? Anyone?
I’ve finally solved this issue. The problem was that by default, Ubuntu was backing up in a different UTF format, not UTF8. So to back up in UTF8 format, I had to change mysql dump statement to… mysqldump -u root -p -R database > /file/location/*.sql . That solved the problem and I can finally proceed to import the database into my new EMR v6.0.0 . I hope that will help others on Ubuntu.
2 Likes