Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7268

Column of table cannot be converted from type 'decimal(0,?)' to type ' 'decimal(10,7)'

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.40
    • 5.5.44
    • Replication
    • None
    • centos-6.5
      x86_64
    • 5.5.44

    Description

      If the middle of a pt-online-schema change using --set-vars binlogformat=ROW the following replication error.

      The table was created on 5.0.34 and has since been upgraded over time to 5.5.29. Could it be the column is the old decimal format?

      http://forums.mysql.com/read.php?26,619649,619718#msg-619718 references (Bug #16416302).

      Server version: 5.5.29-MariaDB-log MariaDB Server
      Same effects where observed on 5.5.40 slave.

      MariaDB [weather]> show slave status\G 
      *************************** 1. row ***************************
                     Slave_IO_State: Waiting for master to send event
                        Master_Host: 192.168.124.35
                        Master_User: replication
                        Master_Port: 3306
                      Connect_Retry: 60
                    Master_Log_File: mysql-bin.046692
                Read_Master_Log_Pos: 67654425
                     Relay_Log_File: mysqld-relay-bin.930329
                      Relay_Log_Pos: 30748354
              Relay_Master_Log_File: mysql-bin.046557
                   Slave_IO_Running: Yes
                  Slave_SQL_Running: No
                    Replicate_Do_DB: 
                Replicate_Ignore_DB: 
                 Replicate_Do_Table: 
             Replicate_Ignore_Table: 
            Replicate_Wild_Do_Table: 
        Replicate_Wild_Ignore_Table: 
                         Last_Errno: 1677
                         Last_Error: Column 3 of table 'weather.lightning' cannot be converted from type 'decimal(0,?)' to type 'decimal(10,7)'
                       Skip_Counter: 0
                Exec_Master_Log_Pos: 30748070
                    Relay_Log_Space: 14291648459
                    Until_Condition: None

      on master:

      mysqlbinlog --start-position 30748070  mysql-bin.046557 | more
       
      BEGIN
      /*!*/;
      # at 30748141
      # at 30748202
      # at 30748274
      #141204 10:12:43 server id 1  end_log_pos 30748202      Table_map: `weather`.`lightning` mapped to number 177647
      #141204 10:12:43 server id 1  end_log_pos 30748274      Table_map: `weather`.`_lightning_new` mapped to number 177646
      #141204 10:12:43 server id 1  end_log_pos 30748356      Delete_rows: table id 177647 flags: STMT_END_F
       
      BINLOG '
      65h/VBMBAAAAPQAAACou1QEAAO+1AgAAAAEAB3dlYXRoZXIACWxpZ2h0bmluZwAHAw8MAAAADwQK
      AAoAYA==
      65h/VBMBAAAASAAAAHIu1QEAAO61AgAAAAEAB3dlYXRoZXIADl9saWdodG5pbmdfbmV3AAcDDwz2
      9vYPCgoACgcKBwUBCgBg
      65h/VBkBAAAAUgAAAMQu1QEAAO+1AgAAAAEAB/+AG4GOBAVHUEFUU5OG1tNFEgAAIC0zMy42MzA3
      NDc5IDE1Mi44MzIwNjk3ICAtNDEuMgJHUw==
      '/*!*/;
      # at 30748356
      #141204 10:12:43 server id 1  end_log_pos 30748383      Xid = 12786901894
      COMMIT/*!*/;

      decoded version

      mysqlbinlog --start-position 30748070  --verbose --base64-output=decode-rows mysql-bin.046557 | more
       
      BEGIN
      /*!*/;
      # at 30748141
      # at 30748202
      # at 30748274
      #141204 10:12:43 server id 1  end_log_pos 30748202      Table_map: `weather`.`lightning` mapped to number 177647
      #141204 10:12:43 server id 1  end_log_pos 30748274      Table_map: `weather`.`_lightning_new` mapped to number 177646
      #141204 10:12:43 server id 1  end_log_pos 30748356      Delete_rows: table id 177647 flags: STMT_END_F
      ### DELETE FROM `weather`.`lightning`
      ### WHERE
      ###   @1=76448027
      ###   @2='GPATS'
      ###   @3=2009-11-16 09:51:23
      ###   @4=!! Don't know how to handle column type=0 meta=0 (0000)# at 30748356
      #141204 10:12:43 server id 1  end_log_pos 30748383      Xid = 12786901894
      COMMIT/*!*/;
       

      | lightning | CREATE TABLE `lightning` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `source` varchar(10) NOT NULL DEFAULT '',
        `timestamp_utc` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `latitude` decimal(10,7) NOT NULL DEFAULT '0.0000000',
        `longitude` decimal(10,7) NOT NULL DEFAULT '0.0000000',
        `amperage` decimal(5,1) DEFAULT NULL,
        `event_type` varchar(10) DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `idx_lightning_1` (`source`,`timestamp_utc`,`latitude`,`longitude`)
      ) ENGINE=InnoDB AUTO_INCREMENT=484282187 DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 ROW_FORMAT=DYNAMIC |
       
       
      | _lightning_new | CREATE TABLE `_lightning_new` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `source` varchar(10) NOT NULL DEFAULT '',
        `timestamp_utc` datetime(3) NOT NULL COMMENT 'UTC time (millisecond precision)',
        `latitude` decimal(10,7) NOT NULL DEFAULT '0.0000000',
        `longitude` decimal(10,7) NOT NULL DEFAULT '0.0000000',
        `amperage` decimal(5,1) DEFAULT NULL,
        `event_type` varchar(10) DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `idx_lightning_1` (`source`,`timestamp_utc`,`latitude`,`longitude`)
      ) ENGINE=InnoDB AUTO_INCREMENT=484282186 DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 ROW_FORMAT=DYNAMIC |

      After changing the format to doubles I had different conversion error at the same position.

      alter table  _lightning_new modify latitude double  NOT NULL DEFAULT 0.0000000,modify  longitude  double  NOT NULL DEFAULT 0.0000000, modify amperage double DEFAULT NULL;
       
      slart slave;
       
      MariaDB [weather]> show slave status\G
      *************************** 1. row ***************************
                     Slave_IO_State: Waiting for master to send event
                        Master_Host: 192.168.124.35
                        Master_User: replication
                        Master_Port: 3306
                      Connect_Retry: 60
                    Master_Log_File: mysql-bin.046693
                Read_Master_Log_Pos: 34510815
                     Relay_Log_File: mysqld-relay-bin.930329
                      Relay_Log_Pos: 30748354
              Relay_Master_Log_File: mysql-bin.046557
                   Slave_IO_Running: Yes
                  Slave_SQL_Running: No
                    Replicate_Do_DB: 
                Replicate_Ignore_DB: 
                 Replicate_Do_Table: 
             Replicate_Ignore_Table: 
            Replicate_Wild_Do_Table: 
        Replicate_Wild_Ignore_Table: 
                         Last_Errno: 1677
                         Last_Error: Column 3 of table 'weather._lightning_new' cannot be converted from type 'decimal(10,7)' to type 'double'
                       Skip_Counter: 0
                Exec_Master_Log_Pos: 30748070
                    Relay_Log_Space: 14363364234
                    Until_Condition: None

      Attachments

        1. lightning.bin
          1 kB
        2. master-lightning.frm
          9 kB
        3. slave-_lightning_old.frm
          9 kB
        4. slave-lightning.frm
          9 kB

        Issue Links

          Activity

            People

              bar Alexander Barkov
              danblack Daniel Black
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.