0

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

  • No -- just insert the one row, not 1/3 of the table. The Trigger is called for each row, in spite of what the FOR EACH ROW may imply. – Rick James May 6 at 18:13
  • Furthermore, you have the current row in OLD or NEW, so you should not touch the table. – Rick James May 6 at 18:14
0

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.

  • Brilliant that works thank you! - Please could you help me setup a trigger on insert? ///DELIMITER $$ CREATE TRIGGER After_Insert AFTER INSERT ON send_sms_dump FOR EACH ROW BEGIN INSERT INTO send_sms_dump2 SELECT * FROM send_sms_dump WHERE sql_id % 3 = 0; END $$ DELIMITER ;/// – mcgster May 6 at 6:09
  • 1
    @mcgster - No. A Trigger is invoked once for each row inserted, not once for the entire INSERT. (The "FOR EACH ROW" is confusing.) – Rick James May 6 at 18:12
  • Thanks again Rick and sorry to bug you again... I think I am going to do things a little differently. As I can see that we will get duplicated sql_ids as well The send_sms_dump will be cleared down on every move so it might have the same sql_id on the next insert and will cause problems when moving the rows Can you let me know if this sounds like it might work? Create another column on send_sms default value = no something like “imported” – mcgster May 6 at 20:29
  • Create an event to select imported = “no” on send_sms_dummy every 60 seconds and run the insert queuery to move the rows with selected columns (no sql_id or imported) to send_sms_dump2/3/4 and update the imported to “yes” and the. run delete where imported is “yes”? – mcgster May 6 at 20:29
  • Perhaps what I added to my Answer works better than imported? Caveat: If you need the original sql_id values, then my suggestion is flawed. – Rick James May 6 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.