Description:
Upon attempting to execute:
INSERT INTO acct_falcon SELECT * FROM acct_innodb LIMIT 2147483648
execution appears to progress normally. After about 45 seconds (about the time it takes to execute the same command with an InnoDB target), MySQL reports error 1022 (duplicate key in table acct_falcon). The source table contains just over 13 million rows.
The table has the following schema:
CREATE TABLE `pmacct`.`acct_falcon` (
`agent_id` int(2) unsigned NOT NULL,
`mac_src` char(17) CHARACTER SET latin1 NOT NULL,
`mac_dst` char(17) CHARACTER SET latin1 NOT NULL,
`vlan` int(2) unsigned NOT NULL,
`ip_src` char(15) CHARACTER SET latin1 NOT NULL,
`ip_dst` char(15) CHARACTER SET latin1 NOT NULL,
`src_port` int(2) unsigned NOT NULL,
`dst_port` int(2) unsigned NOT NULL,
`ip_proto` char(6) CHARACTER SET latin1 NOT NULL,
`tos` int(4) unsigned NOT NULL,
`packets` int(10) unsigned NOT NULL,
`bytes` bigint(20) unsigned NOT NULL,
`flows` int(10) unsigned NOT NULL,
`stamp_inserted` datetime NOT NULL,
`stamp_updated` datetime DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=utf8;
The same error occurs if attempting to use CREATE TABLE SELECT ... .
Additionally, after attempting to populate the table, attempting to drop the table fails and attempting to issue SELECT statements causes MySQL to crash, as does attempting to query STATUS information for the database containing the Falcon table.
The MySQL log contains the following entries at completion of the INSERT ... SELECT:
database open failed: can't open file "C:\Program Files\MySQL\MySQL Server 5.2\Data\pm.fts": No such file or directory (2)
database open failed: can't open file "C:\Program Files\MySQL\MySQL Server 5.2\Data\pm.fts": No such file or directory (2)
database open failed: can't open file "C:\Program Files\MySQL\MySQL Server 5.2\Data\pm.fts": No such file or directory (2)
database open failed: can't open file "C:\Program Files\MySQL\MySQL Server 5.2\Data\pm.fts": No such file or directory (2)
database open failed: can't open file "C:\Program Files\MySQL\MySQL Server 5.2\Data\pm.fts": No such file or directory (2)
database open failed: can't open file "C:\Program Files\MySQL\MySQL Server 5.2\Data\pm.fts": No such file or directory (2)
database open failed: memory is exhausted
How to repeat:
Execute the above INSERT .. SELECT statement with a sufficiently large dataset.
The actual data used is available (about a 70MB .bz2 archive).
Suggested fix:
Correct Falcon behaviour.
