[MDEV-9454] mysqldump creating malformed PERSISTENT SQL Created: 2016-01-23  Updated: 2016-02-23  Resolved: 2016-02-23

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.1.8
Fix Version/s: 10.1.11

Type: Bug Priority: Major
Reporter: Jan Steinman Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: COLLATE, mysqldump, virtual_columns
Environment:

Mac OS X 10.6.8


 Description   

Attempting to upgrade Mac OS 10.6.8 to 10.10 for various reasons. Have MariaDB 10.1.8 on the old machine and 10.1.9 on the new one.

Did "mysqldump --all-databases --add-drop-database --allow-keywords --comments --dump-date --log-error=mysqldump.err --password=*************** --user=root --max_allowed_packet=1073741824" on the MariaDB 10.1.8 machine, used scp() to move the resulting 11GB dump to the upgraded machine, then attempted to use "echo "SET FOREIGN_KEY_CHECKS=0;" | cat - dump.sql | mysql --max_allowed_packet=2G" to import the data into a fresh install of MariaDB 10.1.9.

Restore fails with "ERROR 1064 (42000) at line 13749: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS (INET_NTOA(IP)) PERSISTENT,"

Doing "fgrep ' PERSISTENT,' dump.sql" gives:

  `start_day` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') AS (DATE_FORMAT(`time_start`, '%a')) PERSISTENT,
 
  `end_day` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') AS (DATE_FORMAT(`time_end`, '%a')) PERSISTENT,
 
  `IP_dotted` varchar(255) COLLATE utf8_bin AS (INET_NTOA(IP)) PERSISTENT,
 
  ...

Note that the first two PERSISTENT statements were successfully imported. It stopped at the third, which includes "COLLATE utf8_bin" in the column definition, which doesn't seem to be <a href="https://mariadb.com/kb/en/mariadb/virtual-computed-columns/">included in the documentation for virtual columns</a>.

I may attempt to make a simpler test case, but am short of time right now...

Would like to have a work-around. Using emacs on an 11 GB file to nuke eight instances of COLLATE in PERSISTENT data def statements is not an option.



 Comments   
Comment by Elena Stepanova [ 2016-01-25 ]

Good news is that the bug has already been fixed in 10.1.10, it's MDEV-7655.
Bad news is that it's fixed not by accepting this kind of syntax, but by not creating it, which means your dump from 10.1.8 will still be wrong.

Is using sed on the 11G dump an acceptable workaround?

Comment by Jan Steinman [ 2016-01-25 ]

"Is using sed on the 11G dump an acceptable workaround?"

I thought of that, but my first attempt at this ("sed 's/ COLLATE utf8_bin AS / AS /' dump.sql | fgrep PERSISTENT") did not return expected results. I'll plug away at it...

As you note, a fix in MariaDB 10.1.10 won't help me get data out of MariaDB 10.1.8. Before I start another hours-long dump, do you think doing so with --skip-set-charset will keep the COLLATE statements out of the dump? Or should I try adding --default-character-set=utf8 instead? I don't see any other mysqldump options that might create a clean dump from 10.1.8.

Thanks!

Another question: any idea when 10.1.11 might be out? I am unable to use 10.1.10 because of MDEV-9322.

Comment by Elena Stepanova [ 2016-01-25 ]

This sed call seems to work on the fragment you quoted, maybe other ones just have a different collation.

--skip-set-charset won't help at all, it relates to SET statements in executable comments. Same goes to --default-character-set, it's unrelated.

Second thing after sed that comes to mind is creating two dump files: one dump with --no-data option, and another with --no-create-info option. Then the first file should be quite small, and you'll be able to edit it in emacs (or anywhere else) easily.

10.1.11 is expected to be out this week, the current ETA is 2016-01-28, but sometimes there can be a delay for a day or two.

Comment by Jan Steinman [ 2016-01-26 ]

I was able to work around this because 90% of the data (about 10 GB) was in a database without the problem. The one database that had the problem was fairly small (204 MB), was dumped separately, and was successfully edited and restored.

So I'm up and running on 10.1.9, battling privileges, changing "localhost" to a LAN "10.x.y.z" address in about a dozen places...

Looking forward to 10.1.11...

Generated at Thu Feb 08 07:34:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.