Invalid sql with export import

[bazzaxiv]

Steps to reproduce

1.Export database
2.Import database
3.

Expected behaviour

The exported .sql should be imported without error

Actual behaviour

Errors are generated during import. The errors occur because the exported data is incorrect. See here -

CREATE TABLE `a3gj6_categories` (
  `id` int(11) NOT NULL,
  `asset_id` int(10) UNSIGNED NOT NULL DEFAULT '0'COMMENT
) ;

. The problem is there is no space between the '0' and COMMENT and the rest of the table is not exported. Not all commented rows were affected and I could see no connection between the ones that were. By a trial and test method I removed all offending comments from the database until it exported the DB with all CREATE TABLE entries complete.
When I imported this DB it errored again when filling a table.
The data was
(7, 1, 15, 20, 1, 'com_contact', 'com_contact', '{"core.admin":{"7":1},"core.manage":{"6":1},"core.create":[],"core.delete":[],"core.edit":[],"core.edit.state":[],"core.edit.own":[]}'),
but the error message says it is
(7, 1, 15, 20, 1, 'com_contact', 'com_contact', '{"core.admin":{"7":1},"core.manage":{"6":1},"core.create":[],"core.delete":[...]

Server configuration

Operating system:
Fedora 22
Web server:
Apache 2.4.18
Database:
MariaDB 10.0.23
PHP version:
5.6.20
phpMyAdmin version:
4.6.1

Client configuration

Browser:
Firefox 46.0.1
Operating system:
Fedora 23

[nijel]

Was the export done with 4.6.1? It exports just fine for me with this version. Previous version had bug on this though, see #12041.

[bazzaxiv]

Yes both 4.6.1. I have just built a new work station and am transferring all my assets to it. The only difference in builds is the new machine has Fedora 23 and PHP 5.6.21.
I have read the other now closed bug reports and this does look like the same issue. The problem is confined to those DBs that have comments but not all commented rows display the issue. Many tables with comments exported properly. I am at a complete loss re the second problem the data looks ok but errors on import.

[nijel]

Can you please export this table with mysqldump so that I can take deeper look at it?

[ibennetch]

I can't reproduce this, either, with 4.7.0-dev and the updated (3.4.4) parser or with phpMyAdmin 4.6.1.

[pvanbavel]

I also encountered problems with import / export.
I've done a lot of things in order to get everything working.
Alas, I haven't saved all steps,
Following is what I did and found out.
I was on phpMyAdmin 4.6.1, and MySQL-5.6.17, Apache 2.4.9 on Windows 7, 64 bit.
I made a backup using phpMyadmin export of all databases.
Because of PHP time restrictions I've split up the process in a number of zip-files.
Then I upgraded to MySQL-5.7.12
It turned out that I had to initialize my "data" directory due to all kinds of errors (among others):

• problems connecting MySQL Workbench to the databases,
• performance_schema.session_variables’ table doesn’t exist
  After successfully initializing the "data" directory I regained access for my root and pma users.
  Refilling my databases using phpMyadmin import went OK. Except for a number of cases. All of them stopped, reporting some error in a serialized variable.

Changing basedir and datadir in the my.ini file I went back and forth to the old data directory and tried to make new exports (of the databases that reported the error). The result was exactly the same: an error in a serialized variable.

Finally I didn't select the databases to start the export, instead I selected the the database that gave problems and selected all tables in that database to export to the zip file. In the new environment I created an empty database and successfully imported the zip-file.

It turns out that creating an empty database before the import function results in everything going OK. Even with the file that gave an error when trying to import if there was no database and the database was created in the importfile

[bazzaxiv]

Why is this marked as closed? Is it because I didn't respond quickly enough. If so, sorry but I didn't know how to use mysqldump and I do have other things occupying my time as, I am sure you all do.
Using mysqldump to export and import the database worked so my immediate problem is solved. I do want to stress that there a two, I believe, connected issues here. One is the exported data getting corrupted due to spaces not being inserted consistently after a quote (") and the other, now I have seen the dump file, is probably an escape ( \ ) not being inserted before a quote (") during import.
Here is the dump.
joomback.txt

[nijel]

It's not marked as closed and never was. You probably confused it with reference from closed issue above...

[bazzaxiv]

Sorry, yes I did.

[nijel]

Thanks, the query is also great reproducer for #12219 :-).

[nijel]

However I'm unable to reproduce your problem with 4.6.1, the generated SQL seems perfectly correct:

CREATE TABLE `a3gj6_categories` (
  `id` int(11) NOT NULL,
  `asset_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `parent_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `lft` int(11) NOT NULL DEFAULT '0',
  `rgt` int(11) NOT NULL DEFAULT '0',
  `level` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `path` varchar(255) NOT NULL DEFAULT '',
  `extension` varchar(50) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL,
  `alias` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `note` varchar(255) NOT NULL DEFAULT '',
  `description` mediumtext NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '0',
  `checked_out` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `access` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `params` text NOT NULL,
  `metadesc` varchar(1024) NOT NULL COMMENT 'The meta description for the page.',
  `metakey` varchar(1024) NOT NULL COMMENT 'The meta keywords for the page.',
  `metadata` varchar(2048) NOT NULL COMMENT 'JSON encoded metadata properties.',
  `created_user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `created_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `modified_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `hits` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `language` char(7) NOT NULL,
  `version` int(10) UNSIGNED NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So are you sure the wrong dump was not generated with previous version? You can see the version in headers of the file. I really think this is duplicate for #12041.

[bazzaxiv]

You will not get any error exporting from Phpmyadmin with that dump. As stated earlier I removed all the offending comments from the database. However if you now try to import that export it should fail.
I will see if I can find a copy or get a dump from the live site.

[nijel]

Importing your export works just fine (when fixed/workarounded #12219 if you are using configuration storage). Exporting it works also fine and importing it back as well. I really need something to reproduce it as it really seems to work just fine.

I've tried to add comments to mentioned field and it also exports fine:

CREATE TABLE `a3gj6_categories` (
  `id` int(11) NOT NULL,
  `asset_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'test comm',
  `parent_id` int(10) UNSIGNED NOT NULL DEFAULT '0',

[imabug]

I've run into the same issue trying to export one of my databases with phpMyAdmin. The problem only happened in tables where there was a COMMENT set on a field.

This is the dump of one of the tables I got from exporting with phpMyAdmin

CREATE TABLE `Machines` (
  `MachineID` int(11) UNSIGNED NOT NULL,
  `ModalityID` int(11) UNSIGNED DEFAULT '0',
  `Description` varchar(60) DEFAULT NULL,
  `ManufacturerID` int(11) UNSIGNED DEFAULT '0',
  `VendSiteID` varchar(25) DEFAULT NULL,
  `Model` varchar(20) DEFAULT NULL,
  `SerialNumber` varchar(20) DEFAULT NULL,
  `ManufDate` date NOT NULL DEFAULT '0000-00-00',
  `InstallDate` date NOT NULL DEFAULT '0000-00-00',
  `RemoveDate` date NOT NULL DEFAULT '0000-00-00',
  `LocationID` int(11) UNSIGNED DEFAULT '0',
  `Room` varchar(20) DEFAULT NULL,
  `Active` tinyint(4) NOT NULL DEFAULT '0'COMMENT
) ;

This is the same table dumped with mysqldump

CREATE TABLE `Machines` (
  `MachineID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ModalityID` int(11) unsigned DEFAULT '0',
  `Description` varchar(60) DEFAULT NULL,
  `ManufacturerID` int(11) unsigned DEFAULT '0',
  `VendSiteID` varchar(25) DEFAULT NULL,
  `Model` varchar(20) DEFAULT NULL,
  `SerialNumber` varchar(20) DEFAULT NULL,
  `ManufDate` date NOT NULL DEFAULT '0000-00-00',
  `InstallDate` date NOT NULL DEFAULT '0000-00-00',
  `RemoveDate` date NOT NULL DEFAULT '0000-00-00',
  `LocationID` int(11) unsigned DEFAULT '0',
  `Room` varchar(20) DEFAULT NULL,
  `Active` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Not used anymore',
  `Status` enum('Active','Inactive','Removed') NOT NULL DEFAULT 'Active',
  `Notes` text,
  `Photo` varchar(50) NOT NULL DEFAULT '',
  `SurveyFreq` enum('Annual','Semi-annual','Quarterly','Monthly','Weekly','NA') NOT NULL,
  PRIMARY KEY (`MachineID`),
  KEY `ModalityID` (`ModalityID`),
  KEY `LocationID` (`LocationID`),
  KEY `ManufacturerID` (`ManufacturerID`),
  KEY `MachDesc` (`Description`,`SerialNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=325 DEFAULT CHARSET=utf8;

Using
Fedora 23
Apache 2.4.18
PHP 5.6.22
MariaDB 10.0.23
phpMyAdmin 4.6.1 (4.6.2 hasn't hit the Fedora repos yet)

[nijel]

Thanks for pointer to Fedora! The problem is that Fedora packages do not use sqlparser shipped with phpMyAdmin, but some older version, which does have this bug (and many others).

The problem is that the package uses obsolete package:

Requires:   php-composer(udan11/sql-parser) >= 3.4.0

It should use phpmyadmin/sql-parser instead.phpmyadmin/sql-parser (see https://packagist.org/packages/udan11/sql-parser).

I've reported this on Fedora bug tracker https://bugzilla.redhat.com/show_bug.cgi?id=1342512 (most likely for @robert-scheck).