Backup of MySQL table data not working correctly

[trevorjwood]

I recently tried using the SQL backup created by wordpress-backup-to-dropbox to recreate my Wordpress database. It failed

One of the tables - wp_ps_groups(created by Page Security by Contexture) wouldn't import the data because there was a duplicate key in the table. On further investigation this key was set to 'NULL'. In the SQL from your plugin the table create & insert looks like this

-- Table structure for table wp_ps_groups

CREATE TABLE wp_ps_groups (
ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
group_title varchar(40) NOT NULL COMMENT 'The name of the group',
group_description text COMMENT 'A description of or notes about the group',
group_creator bigint(20) unsigned DEFAULT NULL COMMENT 'The id of the user who created the group',
group_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The datetime the group was created',
group_system_id varchar(5) DEFAULT NULL COMMENT 'A unique system id for system groups',
group_site_access varchar(20) DEFAULT 'none' COMMENT 'If site security is enabled, this dictates how much access this group has. Values: none,limited,full',
PRIMARY KEY (ID),
UNIQUE KEY group_system_id (group_system_id)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- Dumping data for table wp_ps_groups

INSERT INTO wp_ps_groups (ID, group_title, group_description, group_creator, group_date, group_system_id, group_site_access) VALUES
('1', 'Registered Users', 'This group automatically applies to all authenticated users.', '0', '2013-11-06 09:32:28', 'CPS01', 'none'),
('2', 'kitchen', 'Kitchens', '1', '2013-11-20 19:56:37', '', 'none'),
('3', 'catering manager', 'Catering managers', '1', '2013-11-20 19:56:41', '', 'none');

whereas using the Dump from PHPMyAdmin, the create & insert looks like this

-- Table structure for table wp_ps_groups

CREATE TABLE wp_ps_groups (
ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
group_title varchar(40) NOT NULL COMMENT 'The name of the group',
group_description text COMMENT 'A description of or notes about the group',
group_creator bigint(20) unsigned DEFAULT NULL COMMENT 'The id of the user who created the group',
group_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The datetime the group was created',
group_system_id varchar(5) DEFAULT NULL COMMENT 'A unique system id for system groups',
group_site_access varchar(20) DEFAULT 'none' COMMENT 'If site security is enabled, this dictates how much access this group has. Values: none,limited,full',
PRIMARY KEY (ID),
UNIQUE KEY group_system_id (group_system_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- Dumping data for table wp_ps_groups

INSERT INTO wp_ps_groups (ID, group_title, group_description, group_creator, group_date, group_system_id, group_site_access) VALUES
(1, 'Registered Users', 'This group automatically applies to all authenticated users.', 0, '2013-11-06 09:32:28', 'CPS01', 'none'),
(2, 'kitchen', 'Kitchens', 1, '2013-11-20 19:56:37', NULL, 'none'),
(3, 'catering manager', 'Catering managers', 1, '2013-11-20 19:56:41', NULL, 'none');

In the Insert the value 'group_system_id' for both IDs 1 & 2 should be NULL whereas the SQL generated from backup-to-dropbox has them as '' (which is not null) and it is this that is causing the error.

Looking through the rest of the SQL dump, I can see everywhere that PHPMyAdmin dumps NULL, backup-to-dropbox puts ''

Please investigate.

Cheers