Return to Question

added 2587 characters in body

Source Link

edited Feb 15 at 14:51

Added 1:

MariaDB [(none)]> SHOW VARIABLES LIKE '%timeout';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| connect_timeout             | 60000 |
| delayed_insert_timeout      | 300   |
| innodb_flush_log_at_timeout | 1     |
| innodb_lock_wait_timeout    | 360   |
| innodb_rollback_on_timeout  | OFF   |
| interactive_timeout         | 28800 |
| lock_wait_timeout           | 86400 |
| net_read_timeout            | 120   |
| net_write_timeout           | 900   |
| slave_net_timeout           | 60    |
| thread_pool_idle_timeout    | 60    |
| wait_timeout                | 28800 |
+-----------------------------+-------+

product table structure:

CREATE TABLE `product` (
  `id` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `comment` longtext DEFAULT NULL,
  `parentId` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `countryId` int(11) NOT NULL,
  `stateId` int(11) DEFAULT NULL,
  `expireDate` bigint(20) NOT NULL,
  `supervisorName` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `planDate` bigint(20) DEFAULT NULL,
  `canceledDate` bigint(20) NOT NULL,
  `cancelReason` int(11) NOT NULL,
  `warranty` longtext DEFAULT NULL,
  `productEdition` int(11) NOT NULL,
  `productCode` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `condition` int(11) NOT NULL,
  `productAlias` varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `productName` varchar(400) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `productAliasId` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `tag` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `type` int(11) NOT NULL,
  `timeCreated` bigint(20) NOT NULL,
  `operator` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `productStoreId` int(11) DEFAULT NULL,
  `productApplication` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_idx2` (`operator`),
  KEY `product_idx4` (`subjectDN`(255)),
  KEY `product_idx5` (`type`),
  KEY `product_idx6` (`supervisorName`,`condition`),
  KEY `product_idx7` (`countryId`),
  KEY `product_idx11` (`productAliasId`),
  KEY `product_idx12` (`productCode`,`productAliasId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

What can I do for this case?

Added 1:

MariaDB [(none)]> SHOW VARIABLES LIKE '%timeout';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| connect_timeout             | 60000 |
| delayed_insert_timeout      | 300   |
| innodb_flush_log_at_timeout | 1     |
| innodb_lock_wait_timeout    | 360   |
| innodb_rollback_on_timeout  | OFF   |
| interactive_timeout         | 28800 |
| lock_wait_timeout           | 86400 |
| net_read_timeout            | 120   |
| net_write_timeout           | 900   |
| slave_net_timeout           | 60    |
| thread_pool_idle_timeout    | 60    |
| wait_timeout                | 28800 |
+-----------------------------+-------+

product table structure:

CREATE TABLE `product` (
  `id` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `comment` longtext DEFAULT NULL,
  `parentId` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `countryId` int(11) NOT NULL,
  `stateId` int(11) DEFAULT NULL,
  `expireDate` bigint(20) NOT NULL,
  `supervisorName` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `planDate` bigint(20) DEFAULT NULL,
  `canceledDate` bigint(20) NOT NULL,
  `cancelReason` int(11) NOT NULL,
  `warranty` longtext DEFAULT NULL,
  `productEdition` int(11) NOT NULL,
  `productCode` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `condition` int(11) NOT NULL,
  `productAlias` varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `productName` varchar(400) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `productAliasId` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `tag` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `type` int(11) NOT NULL,
  `timeCreated` bigint(20) NOT NULL,
  `operator` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `productStoreId` int(11) DEFAULT NULL,
  `productApplication` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_idx2` (`operator`),
  KEY `product_idx4` (`subjectDN`(255)),
  KEY `product_idx5` (`type`),
  KEY `product_idx6` (`supervisorName`,`condition`),
  KEY `product_idx7` (`countryId`),
  KEY `product_idx11` (`productAliasId`),
  KEY `product_idx12` (`productCode`,`productAliasId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

What can I do for this case?

Source Link

asked Feb 14 at 14:55

user3637971

Can't export MariaDB large table by mysqldump when row_format is compressed

I have a MariaDB 10.2.13 server. This server contains a database called 'factory'. This database contains a table called 'client'. This is a large table.

client table structure:

CREATE TABLE `client` (
  `id` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `name` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `lastname` longtext DEFAULT NULL,
  `age` int(11) NOT NULL,
  `address1` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `time` bigint(20) NOT NULL,
  `comment` longtext NOT NULL,
  `address2` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_idx1` (`address1`),
  KEY `product_idx3` (`address2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

I wanted to export the table by the mysqldump instruction as follows:

mysqldump factory --quick --complete-insert --no-create-db --no-create-info --skip-extended-insert --compact --tables client --max_allowed_packet="3G" > dump_client.sql

Then I got the error message as follows:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `client` at row: 1002046

I was trying to check the record by the SELECT Statement as follows:

SELECT * from client limit 1002046,1'

Even though, I didn't see anything irregular in data. I think it is happening when ROW_FORMAT is COMPRESSED. I can't change the ROW_FORMAT because it's a requeriment.

What can I do for this case?