CODEWITHSUNDEEP
mysqlcreate-table
o139
o139

Reputation: 874

MySQL: Create table if not exist always appending data instead of skip creating table

I have a table 'table1' and want to create another table, but store it in memory (engine=memory), with data from table1.

In my test server SQL works as expected - create the table once, put data in it from 'table1' and next time just skips insertion, because table 'mem_table' already exists.

In production server SQL is unexpected - it's create table 'mem_table', insert data from 'table1'. Every run it's inserting data again and again.

SQL:

CREATE TABLE IF NOT EXISTS `mem_table` (
`f1` mediumint(9) NOT NULL AUTO_INCREMENT,
`f2` mediumint(9) NOT NULL,
`f3` varchar(100) NOT NULL
PRIMARY KEY (`f1`),
KEY `f3` (`f3`)
) ENGINE=MEMORY DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1 SELECT NULL f1, f2, f3 FROM table1;

Upvotes: 3

Views: 224

Answers (2)

o139
o139

Reputation: 874

This behavior, as documentation says, is version dependent.

So, the solution is not using "IF NOT EXISTS" in CREATE TABLE. And if the table is exists, mysql just return error and didn't insert data again and again.

CREATE TABLE `mem_table` (
`f1` mediumint(9) NOT NULL AUTO_INCREMENT,
`f2` mediumint(9) NOT NULL,
`f3` varchar(100) NOT NULL
PRIMARY KEY (`f1`),
KEY `f3` (`f3`)
) ENGINE=MEMORY DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1 SELECT NULL f1, f2, f3 FROM table1;

Upvotes: 1

Bryan Zwicker
Bryan Zwicker

Reputation: 652

Discovered through our comments to your question:

It seems to be an issue with the memory not being allocated for the table.

Take a look through the common issues for memory-based table storage for MySQL: https://dev.mysql.com/doc/refman/4.1/en/full-table.html

Upvotes: 0

Related Questions