Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The slave is not working due to primary key error as shown below:

      Last_SQL_Errno: 1062
       Last_SQL_Error: Error 'Duplicate entry '550696860' for key 'PRIMARY'' on query.
 Default database: 'vserv'. 
 Query: 
  'INSERT INTO ox_data_intermediate_ad
        (date_time, ad_id, zone_id, clicks, impressions, 
         requests, operation_interval, operation_interval_id, 
         interval_start, interval_end, creative_id, updated)
    SELECT
        date_time, ad_id, zone_id, SUM(clicks) AS clicks, 
        SUM(impressions) AS impressions, SUM(requests) AS requests, 
        5 AS operation_interval, 1871 AS operation_interval_id, 
        '2012-05-26 11:55:00' AS interval_start, 
        '2012-05-26 11:59:59' AS interval_end, 
        0 AS creative_id, '2012-05-26 12:04:01' AS updated
    FROM
        (
        SELECT
            interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id,
            count AS clicks, 0 AS impressions, 0 AS requests
        FROM
            ox_data_bkt_c
        WHERE
            interval_start >= '2012-05-26 11:55:00'
            AN

The qeury mentioned does not try to insert the auto-incremented column data_intermediate_ad_id. It expects mysql to automatically add the next ID. When I checked the slave, I found a record for primary key 550696860.

Master is 5.0 and slave is 5.5.24

The second slave with version 5.5.21 is working fine. Is this a bug related to 5.5.24 version?

The query that has an issue is as follows:

INSERT INTO
                ox_data_intermediate_ad
                (date_time, ad_id, zone_id, clicks, impressions, requests, operation_interval, operation_interval_id, interval_start, interval_end, creative_id, updated)
            SELECT
                date_time, ad_id, zone_id, SUM(clicks) AS clicks, SUM(impressions) AS impressions, SUM(requests) AS requests, 5 AS operation_interval, 1591 AS operation_interval_id, '2012-05-25 12:35:00' AS interval_start, '2012-05-25 12:39:59' AS interval_end, 0 AS creative_id, '2012-05-25 12:43:01' AS updated
            FROM
                (
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, count AS clicks, 0 AS impressions, 0 AS requests
                FROM
                    ox_data_bkt_c
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59' UNION ALL 
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, count AS impressions, 0 AS requests
                FROM
                    ox_data_bkt_m
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59' UNION ALL 
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, 0 AS impressions, count AS requests
                FROM
                    ox_data_bkt_r
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59'
                ) AS virtual_table
            GROUP BY
                date_time, ad_id, zone_id

I will like to know why does the slave has an issue with the insert into ... select statement.

share|improve this question
    
Chances are, the ox_data_intermediate_ad table is simply out of sync between the master and slave which can be addressed with pt-table-checksum and pt-table-sync. Do you have an auto_increment_increment or auto_increment_offset set on any of the servers? There is an open bug that can cause this type of behavior. –  Aaron Brown May 28 '12 at 15:01
add comment

1 Answer

up vote 1 down vote accepted

Duplicate key error should never happen. When it does, then probably

  • The Master and Slave were not in sync (perhaps for a long time)
  • You are writing to the Slave.

Never write to a Slave. Recommend setting readonly=ON on the Slave. This will (mostly) prevent accidental writes. However, user 'root' (or any SUPER user) will bypass the readonly check.

As Aaron mentioned, percona.com has a tool for checking consistency between Master and Slave. They have another tool for syncing them.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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