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

Multi-value insert on MyISAM table that makes slaves crash

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5.40
    • 5.5.41
    • None
    • Debian 7, MariaDB 5.5.40 from your repository

    Description

      This insert will make all slaves crash if run on a MyISAM table. If the table is InnoDB it works fine. Statement based or row based replication doesn't seem to matter.

      # Master
      MariaDB [test]> CREATE TABLE `test` (
          ->   `col1` int(11) NOT NULL,
          ->   `col2` int(11) NOT NULL,
          ->   PRIMARY KEY (`col1`,`col2`),
          ->   KEY `col2` (`col2`)
          -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
      Query OK, 0 rows affected (0.00 sec)
       
      # Both
      MariaDB [test]> show global variables like '%packet%';
      +--------------------------+------------+
      | Variable_name            | Value      |
      +--------------------------+------------+
      | max_allowed_packet       | 1073741824 |
      | slave_max_allowed_packet | 1073741824 |
      +--------------------------+------------+
      2 rows in set (0.00 sec)
       
      INSERT INTO `test` VALUES (2775,974),(2775,975),(2775,976),(2778,977),(2778,978),(2782,979),(2790,986),(2790,1139),(2792,840),(2792,984),(2792,989),(2793,982),(2793,992),(2793,993),(2793,994),(2795,323),(2795,332),(2797,980),(2797,997),(2797,998),(2798,1103),(2798,1104),(2799,841),(2799,985),(2799,988),(2833,983),(2833,990),(2833,991),(2834,981),(2834,995),(2834,996),(2835,316),(2835,317),(3007,854),(3007,856),(3008,855),(3008,857),(3009,823),(3009,824),(3014,1),(3015,1),(3016,2),(3017,2),(3018,3),(3019,3),(3024,842),(3024,843),(3024,844),(3025,845),(3025,846),(3025,847),(3040,31),(3041,32),(3042,52),(3042,55),(3043,53),(3043,54),(3044,278),(3044,279),(3044,280),(3044,281),(3044,282),(3044,283),(3044,284),(3044,285),(3045,1),(3046,1),(3049,220),(3050,221),(3050,222),(3051,2),(3052,2),(3053,223),(3054,224),(3055,225),(3056,226),(3057,227),(3058,228),(3059,229),(3060,327),(3066,236),(3067,237),(3068,238),(3069,239),(3070,240),(3080,241),(3081,242),(3082,247),(3083,248),(3084,249),(3085,250),(3086,251),(3087,252),(3088,253),(3089,254),(3090,255),(3091,256),(3092,257),(3093,258),(3094,259),(3096,263),(3097,264),(3100,273),(3100,302),(3101,266),(3102,267),(3103,268),(3104,269),(3105,270),(3111,275),(3112,238),(3113,272),(3115,286),(3116,318),(3116,319),(3117,290),(3117,292),(3118,238),(3119,291),(3119,293),(3120,304),(3121,305),(3122,306),(3123,307),(3124,308),(3125,309),(3126,310),(3127,311),(3128,312),(3128,336),(3129,313),(3129,350),(3130,314),(3131,315),(3131,351),(3132,325),(3132,328),(3134,502),(3138,334),(3139,338),(3139,339),(3140,340),(3140,341),(3141,344),(3141,345),(3142,346),(3142,347),(3149,351),(3149,354),(3150,351),(3150,356),(3152,358),(3152,359),(3153,361),(3153,370),(3154,363),(3154,369),(3156,350),(3156,371),(3159,376),(3160,377),(3160,379),(3160,384),(3161,378),(3161,380),(3161,383),(3162,388),(3162,389),(3162,390),(3169,392),(3169,393),(3169,394),(3170,395),(3170,396),(3170,397),(3171,398),(3171,399),(3171,400),(3172,401),(3172,402),(3172,403),(3173,404),(3173,405),(3173,406),(3178,351),(3178,421),(3190,411),(3190,412),(3191,413),(3191,414),(3192,415),(3192,416),(3193,417),(3193,418),(3194,419),(3194,420),(3195,353),(3195,424),(3196,425),(3196,426),(3197,427),(3197,428),(3198,429),(3198,430),(3199,431),(3199,432),(3200,433),(3200,434),(3201,435),(3201,436),(3202,437),(3202,438),(3203,439),(3203,440),(3204,441),(3204,442),(3205,443),(3205,444),(3206,445),(3206,446),(3207,447),(3207,448),(3208,449),(3208,450),(3209,451),(3209,452),(3210,453),(3210,454),(3211,455),(3211,456),(3212,457),(3212,458),(3213,459),(3213,460),(3214,461),(3214,462),(3215,463),(3215,464),(3218,466),(3218,467),(3218,468),(3219,469),(3219,470),(3219,471),(3220,474),(3220,475),(3220,476),(3221,477),(3221,478),(3221,479),(3222,480),(3222,481),(3223,482),(3223,483),(3224,484),(3224,485),(3225,486),(3225,487),(3227,503),(3227,505),(3228,506),(3228,507),(3230,508),(3230,509),(3231,510),(3231,511),(3232,512),(3232,513),(3233,514),(3233,515),(3234,516),(3234,517),(3235,518),(3235,519),(3237,521),(3237,522),(3239,524),(3239,525),(3240,526),(3240,527),(3241,528),(3241,529),(3242,530),(3242,531),(3243,532),(3243,533),(3244,534),(3244,535),(3245,536),(3245,537),(3246,538),(3246,539),(3252,540),(3252,541),(3254,543),(3254,544),(3254,545),(3255,547),(3255,548),(3255,571),(3256,550),(3256,551),(3256,572),(3257,553),(3257,554),(3257,573),(3258,556),(3258,557),(3258,574),(3259,559),(3259,560),(3259,575),(3260,561),(3260,562),(3260,563),(3261,565),(3261,576),(3262,566),(3262,567),(3263,568),(3263,569),(3263,570),(3264,577),(3264,578),(3265,579),(3265,580),(3266,581),(3266,582),(3266,591),(3267,583),(3267,584),(3267,592),(3268,585),(3268,586),(3268,593),(3269,587),(3269,588),(3269,594),(3270,589),(3270,590),(3271,595),(3271,596),(3271,597),(3272,598),(3272,599),(3273,600),(3273,601),(3273,602),(3274,603),(3274,604),(3274,605),(3275,606),(3275,607),(3275,608),(3276,609),(3276,610),(3276,611),(3277,612),(3277,613),(3277,614),(3278,615),(3278,616),(3279,617),(3279,618),(3279,619),(3279,628),(3279,629),(3280,620),(3280,621),(3280,622),(3281,623),(3281,624),(3281,625),(3282,626),(3282,825),(3283,630),(3283,631),(3284,632),(3284,633),(3284,634),(3285,635),(3285,940),(3286,638),(3286,639),(3286,640),(3287,641),(3287,642),(3287,643),(3288,644),(3288,645),(3288,646),(3289,647),(3289,648),(3289,649),(3290,650),(3290,651),(3290,652),(3291,653),(3291,654),(3291,655),(3292,656),(3292,657),(3292,658),(3293,659),(3293,660),(3293,661),(3294,662),(3294,663),(3294,664),(3295,665),(3295,666),(3295,667),(3296,668),(3296,669),(3296,670),(3297,671),(3297,672),(3297,673),(3298,674),(3298,675),(3298,676),(3299,677),(3299,678),(3299,679),(3300,680),(3300,681),(3300,682),(3301,683),(3301,684),(3301,685),(3302,686),(3302,687),(3302,688),(3303,689),(3303,690),(3303,691),(3304,692),(3304,693),(3304,694),(3305,695),(3305,696),(3305,697),(3306,698),(3306,699),(3306,700),(3307,701),(3307,702),(3307,703),(3308,704),(3308,705),(3308,706),(3309,707),(3309,708),(3310,709),(3310,710),(3311,711),(3311,712),(3311,713),(3312,714),(3312,715),(3312,716),(3313,717),(3313,1167),(3314,720),(3314,721),(3314,722),(3315,723),(3315,724),(3315,725),(3316,726),(3316,727),(3316,728),(3317,729),(3317,730),(3317,731),(3318,732),(3318,733),(3318,734),(3319,735),(3319,736),(3319,737),(3320,738),(3320,739),(3320,740),(3321,741),(3321,742),(3322,743),(3322,744),(3323,745),(3323,746),(3323,747),(3324,748),(3324,749),(3324,750),(3325,751),(3325,752),(3325,753),(3326,754),(3326,755),(3327,756),(3327,757),(3328,758),(3328,789),(3329,761),(3329,790),(3330,762),(3330,763),(3331,768),(3331,785),(3331,786),(3332,769),(3332,783),(3332,784),(3335,766),(3336,767),(3343,770),(3343,771),(3344,772),(3344,773),(3345,774),(3345,775),(3347,776),(3347,777),(3347,987),(3348,778),(3348,779),(3349,780),(3372,781),(3372,782),(3373,787),(3373,788),(3376,791),(3376,792),(3377,793),(3377,794),(3378,799),(3378,800),(3379,801),(3379,802),(3380,795),(3380,796),(3381,797),(3381,798),(3383,805),(3384,806),(3384,807),(3385,808),(3385,809),(3386,810),(3386,811),(3387,812),(3387,814),(3388,815),(3388,816),(3391,817),(3391,818),(3391,819),(3392,820),(3392,821),(3392,822),(3393,826),(3393,827),(3394,828),(3394,829),(3395,830),(3395,831),(3396,834),(3396,835),(3397,832),(3397,833),(3398,836),(3398,837),(3399,838),(3399,839),(3410,850),(3410,851),(3411,852),(3411,853),(3412,848),(3412,849),(3419,860),(3419,951),(3420,859),(3420,861),(3422,862),(3422,863),(3423,864),(3423,865),(3424,866),(3424,867),(3424,872),(3424,873),(3425,868),(3425,869),(3425,874),(3425,875),(3426,878),(3426,879),(3427,876),(3427,877),(3428,880),(3432,884),(3432,885),(3432,886),(3434,887),(3434,888),(3434,889),(3441,894),(3441,895),(3442,896),(3442,897),(3444,904),(3445,905),(3449,906),(3449,907),(3450,908),(3450,909),(3453,910),(3458,915),(3458,916),(3459,917),(3459,918),(3463,919),(3463,920),(3485,929),(3486,930),(3487,931),(3488,932),(3489,933),(3493,2),(3494,2),(3501,934),(3502,936),(3503,938),(3504,939),(3505,941),(3506,942),(3507,943),(3508,944),(3509,945),(3510,946),(3511,947),(3512,948),(3514,949),(3514,950),(3515,953),(3516,954),(3517,955),(3518,956),(3519,957),(3520,958),(3521,959),(3527,960),(3527,965),(3528,961),(3528,962),(3529,963),(3529,964),(3530,966),(3530,967),(3531,968),(3531,969),(3535,970),(3535,971),(3536,972),(3536,973),(3540,999),(3540,1000),(3541,1001),(8888,9999);
       
      # Results in
      141014 20:28:09 [ERROR] mysqld got signal 11 ;
      This could be because you hit a bug. It is also possible that this binary
      or one of the libraries it was linked against is corrupt, improperly built,
      or misconfigured. This error can also be caused by malfunctioning hardware.
       
      To report this bug, see http://kb.askmonty.org/en/reporting-bugs
       
      We will try our best to scrape up some info that will hopefully help
      diagnose the problem, but since we have already crashed, 
      something is definitely wrong and this may fail.
       
      Server version: 5.5.40-MariaDB-1~wheezy-log
      key_buffer_size=33554432
      read_buffer_size=2097152
      max_used_connections=2
      max_threads=1502
      thread_count=1
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 15440031 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x0x7f20a2c12000
      Attempting backtrace. You can use the following information to find out
      where mysqld died. If you see no messages after this, something went
      terribly wrong...
      stack_bottom = 0x7f20cec7f970 thread_stack 0x40000
      /usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7f20cf5d4f3b]
      /usr/sbin/mysqld(handle_fatal_signal+0x422)[0x7f20cf20dc12]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0xf030)[0x7f20ce906030]
      /lib/x86_64-linux-gnu/libc.so.6(+0x1235c8)[0x7f20cd2545c8]
      /usr/sbin/mysqld(+0x8507e5)[0x7f20cf5857e5]
      /usr/sbin/mysqld(+0x851a51)[0x7f20cf586a51]
      /usr/sbin/mysqld(+0x851cca)[0x7f20cf586cca]
      /usr/sbin/mysqld(+0x851c78)[0x7f20cf586c78]
      /usr/sbin/mysqld(+0x852121)[0x7f20cf587121]
      /usr/sbin/mysqld(+0x850bbd)[0x7f20cf585bbd]
      /usr/sbin/mysqld(_ZN7handler12ha_write_rowEPh+0xb1)[0x7f20cf215541]
      /usr/sbin/mysqld(_ZN14Rows_log_event9write_rowEPK14Relay_log_infob+0x145)[0x7f20cf2ccbf5]
      /usr/sbin/mysqld(_ZN20Write_rows_log_event11do_exec_rowEPK14Relay_log_info+0x22)[0x7f20cf2ccfd2]
      /usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x252)[0x7f20cf2c2952]
      /usr/sbin/mysqld(_Z26apply_event_and_update_posP9Log_eventP3THDP14Relay_log_info+0x17d)[0x7f20cf06eedd]
      /usr/sbin/mysqld(handle_slave_sql+0x830)[0x7f20cf06fd40]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x6b50)[0x7f20ce8fdb50]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f20cd20ce6d]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x0): is an invalid pointer
      Connection ID (thread ID): 1
      Status: NOT_KILLED

      Remove any one record to make it 669 total and it works fine. However, it doesn't seem to be just any data. An insert generated with something like:

      for I in $(seq 1000 1670); do echo -n "($I,$I),"; done

      Works just fine. However, the statement I've provided above will crash it without fail. Remove the secondary index and it also works just fine. The insert was taken from a mysqldump of another 5.5 host. I backed out the amount it was inserting until I found where it didn't crash.

      Attachments

        Activity

          sophomeric Eric Webster added a comment -

          I do get that error yes. I wonder if when mysqld_safe restarts it if it overwrites the debug file or appends to it? I did the same test and crash with --no-auto-restart on mysqld_safe so that the trace file MDEV-6871-2014-11-19-2.trace.bz2 should be the final crash.

          sophomeric Eric Webster added a comment - I do get that error yes. I wonder if when mysqld_safe restarts it if it overwrites the debug file or appends to it? I did the same test and crash with --no-auto-restart on mysqld_safe so that the trace file MDEV-6871 -2014-11-19-2.trace.bz2 should be the final crash.

          The last trace captures "almost" the error, but it may be good enough for me to get an idea of what's going on.
          I will investigate this on Friday.

          When I say almost, the problem is that the last part of the log was not flushed before it crashed, so I lose a few K of data at the end.
          Hopefully I will not need the last part. I will know soon.

          If you have a time over you could fix this by running the slave with
          --debug=:d:t:i:O,/tmp/mysqld.trace

          This will flush the trace after each write; It will make the slave a bit slower but it will ensure that everything is in the log.

          Thanks a lot for the help so far!

          monty Michael Widenius added a comment - The last trace captures "almost" the error, but it may be good enough for me to get an idea of what's going on. I will investigate this on Friday. When I say almost, the problem is that the last part of the log was not flushed before it crashed, so I lose a few K of data at the end. Hopefully I will not need the last part. I will know soon. If you have a time over you could fix this by running the slave with --debug=:d:t:i:O,/tmp/mysqld.trace This will flush the trace after each write; It will make the slave a bit slower but it will ensure that everything is in the log. Thanks a lot for the help so far!
          sophomeric Eric Webster added a comment -

          MDEV-6871-dtio-trace.bz2 has been uploaded as you requested.

          sophomeric Eric Webster added a comment - MDEV-6871 -dtio-trace.bz2 has been uploaded as you requested.

          Found the bug, thanks to the latest trace file!

          You have enabled the, not needed and not recommended option, --skip-external-locking=0. When this option is enabled, you get a core dump. If not, then things works.

          This option was before used to protect against multiple servers using the same MyISAM files, something we supported a LONG time ago, but not really anymore.

          Of course this should not cause a core dump; I will fix this ASAP.

          However, in any case you should remove this option from your config files as it's not likely to be useful and it actually causes a notable performance degradation.

          Thanks a lot for all the trace files!

          monty Michael Widenius added a comment - Found the bug, thanks to the latest trace file! You have enabled the, not needed and not recommended option, --skip-external-locking=0. When this option is enabled, you get a core dump. If not, then things works. This option was before used to protect against multiple servers using the same MyISAM files, something we supported a LONG time ago, but not really anymore. Of course this should not cause a core dump; I will fix this ASAP. However, in any case you should remove this option from your config files as it's not likely to be useful and it actually causes a notable performance degradation. Thanks a lot for all the trace files!

          Problem was that repair() did lock and unlock tables, which leaved already locked tables in wrong state.
          This affected both MyISAM and Aria.
          Fix pushed into 5.5 tree

          monty Michael Widenius added a comment - Problem was that repair() did lock and unlock tables, which leaved already locked tables in wrong state. This affected both MyISAM and Aria. Fix pushed into 5.5 tree

          People

            monty Michael Widenius
            sophomeric Eric Webster
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.