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

ERROR 1194 or server crashes after a SELECT with UNION and TRIM on TEXT fields

    Details

      Description

      1/ SUMMARY of the problem :
      When performing SELECT with UNION on small amount of data, with TRIM() function applied on TEXT fields IN the "select_expr" list part of the SELECT(s), we get MariaDB server crash with "ERROR 1194 (HY000): Table '(temporary)' is marked as crashed and should be repaired"

      2/ cnf file /etc/my.cnf.d/server.cnf (note that /etc/my.cnf is empty : contains only include directive)
      I changed the default settings as defined below to increase some stack, heap and buffer resources with NO success.

      #
      # These groups are read by MariaDB server.
      # Use it for options that only the server (but not clients) should see
      #
      # See the examples of server my.cnf files in /usr/share/mysql/
      #
       
      # this is read by the standalone daemon and embedded servers
      [server]
      datadir=/data/mysql
      socket=/var/lib/mysql/mysql.sock
      symbolic-links=0
      bind-address=::
      log-error=/var/log/mariadb/mariadb.log
      max_heap_table_size=256M
      tmp_table_size=256M
      tmp_memory_table_size=256M
      big_tables=ON
      thread_stack=512M
      aria_pagecache_buffer_size=1024M
      #  aria_pagecache_file_hash_size
      aria_sort_buffer_size=512M
      innodb_temp_data_file_path = ibtmp1:48M:autoextend
       
       
      # this is only for the mysqld standalone daemon
      [mysqld]
       
      #
      # * Galera-related settings
      #
      [galera]
      # Mandatory settings
      #wsrep_on=ON
      #wsrep_provider=
      #wsrep_cluster_address=
      #binlog_format=row
      #default_storage_engine=InnoDB
      #innodb_autoinc_lock_mode=2
      #
      # Allow server to accept connections on all interfaces.
      #
      #bind-address=0.0.0.0
      #
      # Optional setting
      #wsrep_slave_threads=1
      #innodb_flush_log_at_trx_commit=0
       
      # this is only for embedded server
      [embedded]
       
      # This group is only read by MariaDB servers, not by MySQL.
      # If you use the same .cnf file for MySQL and MariaDB,
      # you can put MariaDB-only options here
      [mariadb]
       
      # This group is only read by MariaDB-10.3 servers.
      # If you use the same .cnf file for MariaDB of different versions,
      # use this group for options that older servers don't understand
      [mariadb-10.3]
      

      3/ TEST DATA SET : CREATE simple test table in test database :

      CREATE TABLE test.txtpeople (id INTEGER, name TEXT, firstname TEXT, age INTEGER, revenue INTEGER, stprop TEXT);
      Add a few data inside :
      INSERT INTO test.txtpeople VALUES (1, 'ABITBOL', 'PAUL', 32, 1000, NULL), (1, 'DUPONT', 'JEAN', 34, 1100, NULL), (1, 'SMITH','BOB', 35, 1200, NULL), (1, 'ABITBOL ', ' PAUL', 36, 1500, NULL);
      To mimic one of our true testcase, we deliberately leave the field "stprop" empty, ie at NULL.

      4/ TESTS and FAILURES/SUCCESS

      TEST 1 : simple select : SUCCESS, ie NO crash
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

      TEST 2 : double select with union DISTINCT : FAILURE (crash)
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      union distinct
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;
      ERROR 1194 (HY000): Table '(temporary)' is marked as crashed and should be repaired

      TEST 3 : double select with union ALL : SUCCESS, ie NO crash
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      union ALL
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

      TEST 4 : double select with union ALL NESTED in an outer SELECT : FAILURE (crash)
      SELECT * FROM (
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      union ALL
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      ) T ;
      ERROR 1194 (HY000): Table '(temporary)' is marked as crashed and should be repaired

      5/ WORKAROUNDS

      5.1/ Workaround : NO trim in field_spec but JUST in "group by" expression

      *TEST 2B (workaround) : NO trim in field_spec but in "group by" expression : SUCCESS !
      *SELECT id, name, age, sum(revenue), firstname, stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      union distinct
      SELECT id, name, age, sum(revenue), firstname, stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

      *TEST 4B (workaround) : NO trim in field_spec but in "group by" expression : SUCCESS !
      *SELECT * FROM (
      SELECT id, name, age, sum(revenue), firstname, stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      union ALL
      SELECT id, name, age, sum(revenue), firstname, stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      ) T ;

      5.2/ Workaround : let TRIM everywhere, but replace TEXT WITH VARCHAR(64) :
      CREATE TABLE test.vchrpeople (id INTEGER, name VARCHAR(64), firstname VARCHAR(64), age INTEGER, revenue INTEGER, stprop TEXT);
      INSERT INTO test.vchrpeople VALUES (1, 'ABITBOL', 'PAUL', 32, 1000, NULL), (1, 'DUPONT', 'JEAN', 34, 1100, NULL), (1, 'SMITH','BOB', 35, 1200, NULL), (1, 'ABITBOL ', ' PAUL', 36, 1500, NULL);

      *TEST 2C : SUCCESS ! so there is a specific problem with TEXT type in temporary tables
      *SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.vchrpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      union distinct
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.vchrpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

      *TEST 4C : SUCCESS ! so there is a specific problem with TEXT type in temporary tables
      *SELECT * FROM (
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.vchrpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      union ALL
      SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.vchrpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
      ) T ;

      *6/ Diagnostic and GUESSES : *
      I guess that there is a problem of TEMPORARY tables managed by the ARIA engine, WHEN optimizing/managing execution plan for requests containing TRIM functions in "select_expr" fields operating on TEXT fields. Clearly, the trim function is not working properly on TEXT fields, it is also documented that TEXT fields push the memory/temporary engine to produce some indexes (see differences between TEXT and VARCHAR in the documentation here : https://mariadb.com/kb/en/library/text/ ) differently than with varchar.

      NB1: On mysql for windows version 5.6.17-log, everything is OK.
      NB2 : with a "CREATE VIEW" based on the same "SELECT", it is the same problem.

        Attachments

          Activity

            People

            • Assignee:
              vlad.lesin Vladislav Lesin
              Reporter:
              pierre13fr Pierre DELAAGE
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: