importer.import crashing because of server variables in dump file

[johndanek]

Describe the bug
I am attempting to feed the output of the npm library mysqldump into mysql-import in an AWS SAM function. The error I receive is:

2023-03-06T17:34:03.171Z	2e91bc57-df7f-410c-9452-76acd78a52ae	INFO	 - Import 0% Completed
2023-03-06T17:34:03.175Z	2e91bc57-df7f-410c-9452-76acd78a52ae	ERROR	Invoke Error 	{"errorType":"Error","errorMessage":"Encoding not recognized: 'undefined' (searched as: 'undefined')","stack":[
  "Error: Encoding not recognized: 'undefined' (searched as: 'undefined')","
    at Object.getCodec (/var/task/node_modules/iconv-lite/lib/index.js:104:23)","
    at Object.getEncoder (/var/task/node_modules/iconv-lite/lib/index.js:115:23)","
    at exports.encode (/var/task/node_modules/mysql-import/node_modules/mysql2/lib/parsers/string.js:23:25)","
    at Query.toPacket (/var/task/node_modules/mysql-import/node_modules/mysql2/lib/packets/query.js:16:30)","
    at Query.start (/var/task/node_modules/mysql-import/node_modules/mysql2/lib/commands/query.js:60:38)","
    at Query.execute (/var/task/node_modules/mysql-import/node_modules/mysql2/lib/commands/command.js:45:22)","
    at Connection.handlePacket (/var/task/node_modules/mysql-import/node_modules/mysql2/lib/connection.js:456:32)","
    at Connection.addCommand (/var/task/node_modules/mysql-import/node_modules/mysql2/lib/connection.js:478:12)","
    at Connection.query (/var/task/node_modules/mysql-import/node_modules/mysql2/lib/connection.js:546:17)","
    at /var/task/node_modules/mysql-import/mysql-import.js:466:23"
]}

I have a hunch that the issue is due to the dump file beginning with the following lines:

/*!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 utf8 */
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */

If I manually delete these lines from the file, it seems to work. But the error is definitely a little confusing. Is this expected behavior?

[fedevezedulia]

Exact same problem here, did you find a solution @johndanek ?

[johndanek]

Exact same problem here, did you find a solution @johndanek ?

Sorry for the delay @fedevezedulia but nope- I ended up using the mysql CLI instead of this library.

[mikstur]

A bit hacky, but essentially you can remove the first 7 and last 7 lines of the file (the comments containing the comments) and then it should work.

  let content = fs.readFileSync(dumpFileName).toString().split('\n');;
  content.shift();
  content.shift();
  content.shift();
  content.shift();
  content.shift();
  content.shift();
  content.shift();
  content.pop();
  content.pop();
  content.pop();
  content.pop();
  content.pop();
  content.pop();
  content.pop();
  content = content.join('\n');
  fs.writeFileSync(dumpFileName, content);

[rafalmyszynski]

It's a MySQL client issue, not this package. I was using outdated node-mysql2 and got the same error. After upgrading, it works fine. In my case, it was solved in this PR sidorares/node-mysql2#3551.

As you can see, it's caused by new encodings used in MySQL v8. Same queries work fine in MySQL 5.7.