MySql split table on insert

MySQLsplit

I am new so please go easy on me 🙂

I have the following table

CREATE TABLE `send_sms` (
    `sql_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `momt` ENUM('MO','MT') NULL DEFAULT NULL,
    `sender` VARCHAR(20) NULL DEFAULT NULL,
    `receiver` VARCHAR(20) NULL DEFAULT NULL,
    `udhdata` BLOB NULL,
    `msgdata` TEXT NULL,
    `time` BIGINT(20) NULL DEFAULT NULL,
    `smsc_id` VARCHAR(255) NULL DEFAULT NULL,
    `service` VARCHAR(255) NULL DEFAULT NULL,
    `account` VARCHAR(255) NULL DEFAULT NULL,
    `id` BIGINT(20) NULL DEFAULT NULL,
    `sms_type` BIGINT(20) NULL DEFAULT NULL,
    `mclass` BIGINT(20) NULL DEFAULT NULL,
    `mwi` BIGINT(20) NULL DEFAULT NULL,
    `coding` BIGINT(20) NULL DEFAULT NULL,
    `compress` BIGINT(20) NULL DEFAULT NULL,
    `validity` BIGINT(20) NULL DEFAULT NULL,
    `deferred` BIGINT(20) NULL DEFAULT NULL,
    `dlr_mask` BIGINT(20) NULL DEFAULT NULL,
    `dlr_url` VARCHAR(255) NULL DEFAULT NULL,
    `pid` BIGINT(20) NULL DEFAULT NULL,
    `alt_dcs` BIGINT(20) NULL DEFAULT NULL,
    `rpi` BIGINT(20) NULL DEFAULT NULL,
    `charset` VARCHAR(255) NULL DEFAULT NULL,
    `boxc_id` VARCHAR(255) NULL DEFAULT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `binfo` VARCHAR(255) NULL DEFAULT NULL,
    `meta_data` TEXT NULL,
    `priority` BIGINT(20) NULL DEFAULT NULL,
    `foreign_id` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`sql_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

I have two different applications talking to each other via these tables.

I would like to count the inserted rows (only 3 in below example, but could be 1000s at a time) and separate then into 3 other existing tables (same format)

So for Example:

INSERT INTO send_sms
( momt, sender, receiver, msgdata, sms_type, dlr_mask, dlr_url ) 
VALUES ( 'MT','1234', '447XXXXXXXX', 'Hello world4', 2, 27, 'test1' ),
( 'MT','Sender', '447XXXXXXXY', 'Hello world4', 2, 27, 'test2' ),
( 'MT','Sender', '447XXXXXXXY', 'Hello world4', 2, 27, 'test3' );

send_sms1 (would have test1)
send_sms2 (would have test2)
send_sms3 (would have test3)

Would need to be able to support if its not multiples of 3 example if its 10,000 rows send_sms1, send_sms2 should get 3,333 and send_sms3 should get 3,334 (doesn't matter which order). As this is a live system the table needs to be accessed at the same time (so during the move the table must be writable for other insert commands

I have tried things like like

INSERT INTO send_sms2(SELECT * FROM send_sms WHERE sql_id >= (SELECT COUNT(*) FROM send_sms_dump)/2);

the above was just a test to split data- this worked however there isn't any delete so the data wasn't moved. Just 50K copied from the table to the other table

Please point me in the right direction so I can do some more research 🙂

Update thank you Rick James for the solution.

I am now trying to create an update trigger

DELIMITER $$

CREATE TRIGGER my_trigger AFTER INSERT ON send_sms_dump
FOR EACH ROW
BEGIN
    -- Statement one
    INSERT INTO send_sms_dump2
    SELECT * FROM send_sms_dump
             WHERE sql_id % 3 = 0;
    -- Statement two
    INSERT INTO send_sms_dump3
    SELECT * FROM send_sms_dump
             WHERE sql_id % 3 = 1;
    -- More UPDATE statements
    INSERT INTO send_sms_dump4
    SELECT * FROM send_sms_dump
             WHERE sql_id % 3 = 2;
    -- More UPDATE statements
    DELETE FROM send_sms_dump;
END$$

I have the above the error is … /* SQL Error (1442): Can't update table 'send_sms_dump' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. */ …

I am guessing its because of the delete command, i didn't want to drop the table as the third party application will be writing again to that table – let me know if i should post a new question 🙂

thanks guys

Best Answer

You have an AUTO_INCREMENT sql_id, so you have the rows numbered 1,2,3, 4,5,6, ... (unless you are using a clustering or Multi-Master setup that sets auto_increment_increment to something other than 1).

So, load the data into a table, then copy thirds into the three desired tables, and finally DROP the initial table.

INSERT INTO table_1
    SELECT * FROM table_all
             WHERE sql_id % 3 = 0;   -- 0 for table_1. 1 for table_2, 2 for table_3

If you are adding to tables1/2/3, and they have an AUTO_INCREMENT, you can get unique ids by _skipping sql_id in the transfer:

INSERT INTO table_1 (b,c,d,e)
    SELECT b,c,d,e FROM table_all
             WHERE sql_id % 3 = 0;

This assumes the columns in table_all are

sql_id,b,c,d,e

And table_1/2/3 have another column that is AUTO_INCREMENT.

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Drop auto increment hack w/o alter table

Related Question