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

Stored Procedure - Geometry parameter not working

Details

    • 5.5.44

    Description

      A stored procedure, SP(data text, out g geometry), does mess up the geomtery content, when used inside a trigger.
      Try the attached test case with mysql / mariadb.
      (Tried lots of different versions mysql and the latest mariadb, all mariadb has the same bug)
      Note that the column data get messed up, as illustrated with the attach image.

      Attachments

        Activity

          There turns out to be a valgrind issue (Invalid read), which probably causes the problem. The valgrind warning is reproducible on MariaDB 5.3, 5.5 and on MySQL 5.5, 5.6; but not on MariaDB 10.0.4.
          The rest is apparently the matter of luck, depending on what the invalid read has read.

          Are you willing to file a bug report at bugs.mysql.com? We usually report upstream bugs to MySQL as well.

          Stack trace from MariaDB 5.3:

          ==10277== Thread 4:
          ==10277== Invalid read of size 1
          ==10277== at 0x4C2D1A0: memcpy@@GLIBC_2.14 (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
          ==10277== by 0x9341B1: _mi_rec_pack (mi_dynrec.c:1015)
          ==10277== by 0x931674: _mi_write_blob_record (mi_dynrec.c:300)
          ==10277== by 0x954E5E: mi_write (mi_write.c:149)
          ==10277== by 0x90FA6A: ha_myisam::write_row(unsigned char*) (ha_myisam.cc:851)
          ==10277== by 0x810272: handler::ha_write_row(unsigned char*) (handler.cc:4978)
          ==10277== by 0x748E05: write_record(THD*, st_table*, st_copy_info*) (sql_insert.cc:1692)
          ==10277== by 0x746C0A: mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) (sql_insert.cc:887)
          ==10277== by 0x68B560: mysql_execute_command(THD*) (sql_parse.cc:3253)
          ==10277== by 0x6946B3: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6173)
          ==10277== by 0x68610E: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1243)
          ==10277== by 0x68537F: do_command(THD*) (sql_parse.cc:923)
          ==10277== by 0x682219: handle_one_connection (sql_connect.cc:1231)
          ==10277== by 0x548DE99: start_thread (pthread_create.c:308)
          ==10277== by 0x5F9ACBC: clone (clone.S:112)
          ==10277== Address 0xf873978 is 136 bytes inside a block of size 148 free'd
          ==10277== at 0x4C2A82E: free (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
          ==10277== by 0xB7477E: _myfree (safemalloc.c:337)
          ==10277== by 0x56B571: String::free() (sql_string.h:240)
          ==10277== by 0x65165D: Field_blob::free() (field.h:1896)
          ==10277== by 0x6FB69C: free_blobs(st_table*) (table.cc:2531)
          ==10277== by 0x8C1684: sp_rcontext::~sp_rcontext() (sp_rcontext.cc:58)
          ==10277== by 0x8BA4E7: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:2069)
          ==10277== by 0x68F73B: mysql_execute_command(THD*) (sql_parse.cc:4500)
          ==10277== by 0x8BC917: sp_instr_stmt::exec_core(THD*, unsigned int*) (sp_head.cc:2976)
          ==10277== by 0x8BC22C: sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) (sp_head.cc:2794)
          ==10277== by 0x8BC6D9: sp_instr_stmt::execute(THD*, unsigned int*) (sp_head.cc:2919)
          ==10277== by 0x8B8590: sp_head::execute(THD*) (sp_head.cc:1283)
          ==10277== by 0x8B911A: sp_head::execute_trigger(THD*, st_mysql_lex_string const*, st_mysql_lex_string const*, st_grant_info*) (sp_head.cc:1586)
          ==10277== by 0x8CF381: Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool) (sql_trigger.cc:2130)
          ==10277== by 0x6F00E7: fill_record_n_invoke_before_triggers(THD*, List<Item>&, List<Item>&, bool, Table_triggers_list*, trg_event_type) (sql_base.cc:8747)
          ==10277== by 0x746914: mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) (sql_insert.cc:808)
          ==10277== Invalid read of size 1
          ==10277== at 0x4C2D1AD: memcpy@@GLIBC_2.14 (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
          ==10277== by 0x9341B1: _mi_rec_pack (mi_dynrec.c:1015)
          ==10277== by 0x931674: _mi_write_blob_record (mi_dynrec.c:300)
          ==10277== by 0x954E5E: mi_write (mi_write.c:149)
          ==10277== by 0x90FA6A: ha_myisam::write_row(unsigned char*) (ha_myisam.cc:851)
          ==10277== by 0x810272: handler::ha_write_row(unsigned char*) (handler.cc:4978)
          ==10277== by 0x748E05: write_record(THD*, st_table*, st_copy_info*) (sql_insert.cc:1692)
          ==10277== by 0x746C0A: mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) (sql_insert.cc:887)
          ==10277== by 0x68B560: mysql_execute_command(THD*) (sql_parse.cc:3253)
          ==10277== by 0x6946B3: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6173)
          ==10277== by 0x68610E: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1243)
          ==10277== by 0x68537F: do_command(THD*) (sql_parse.cc:923)
          ==10277== by 0x682219: handle_one_connection (sql_connect.cc:1231)
          ==10277== by 0x548DE99: start_thread (pthread_create.c:308)
          ==10277== by 0x5F9ACBC: clone (clone.S:112)
          ==10277== Address 0xf873976 is 134 bytes inside a block of size 148 free'd
          ==10277== at 0x4C2A82E: free (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
          ==10277== by 0xB7477E: _myfree (safemalloc.c:337)
          ==10277== by 0x56B571: String::free() (sql_string.h:240)
          ==10277== by 0x65165D: Field_blob::free() (field.h:1896)
          ==10277== by 0x6FB69C: free_blobs(st_table*) (table.cc:2531)
          ==10277== by 0x8C1684: sp_rcontext::~sp_rcontext() (sp_rcontext.cc:58)
          ==10277== by 0x8BA4E7: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:2069)
          ==10277== by 0x68F73B: mysql_execute_command(THD*) (sql_parse.cc:4500)
          ==10277== by 0x8BC917: sp_instr_stmt::exec_core(THD*, unsigned int*) (sp_head.cc:2976)
          ==10277== by 0x8BC22C: sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) (sp_head.cc:2794)
          ==10277== by 0x8BC6D9: sp_instr_stmt::execute(THD*, unsigned int*) (sp_head.cc:2919)
          ==10277== by 0x8B8590: sp_head::execute(THD*) (sp_head.cc:1283)
          ==10277== by 0x8B911A: sp_head::execute_trigger(THD*, st_mysql_lex_string const*, st_mysql_lex_string const*, st_grant_info*) (sp_head.cc:1586)
          ==10277== by 0x8CF381: Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool) (sql_trigger.cc:2130)
          ==10277== by 0x6F00E7: fill_record_n_invoke_before_triggers(THD*, List<Item>&, List<Item>&, bool, Table_triggers_list*, trg_event_type) (sql_base.cc:8747)
          ==10277== by 0x746914: mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) (sql_insert.cc:808)

          elenst Elena Stepanova added a comment - There turns out to be a valgrind issue (Invalid read), which probably causes the problem. The valgrind warning is reproducible on MariaDB 5.3, 5.5 and on MySQL 5.5, 5.6; but not on MariaDB 10.0.4. The rest is apparently the matter of luck, depending on what the invalid read has read. Are you willing to file a bug report at bugs.mysql.com? We usually report upstream bugs to MySQL as well. Stack trace from MariaDB 5.3: ==10277== Thread 4: ==10277== Invalid read of size 1 ==10277== at 0x4C2D1A0: memcpy@@GLIBC_2.14 (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so) ==10277== by 0x9341B1: _mi_rec_pack (mi_dynrec.c:1015) ==10277== by 0x931674: _mi_write_blob_record (mi_dynrec.c:300) ==10277== by 0x954E5E: mi_write (mi_write.c:149) ==10277== by 0x90FA6A: ha_myisam::write_row(unsigned char*) (ha_myisam.cc:851) ==10277== by 0x810272: handler::ha_write_row(unsigned char*) (handler.cc:4978) ==10277== by 0x748E05: write_record(THD*, st_table*, st_copy_info*) (sql_insert.cc:1692) ==10277== by 0x746C0A: mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) (sql_insert.cc:887) ==10277== by 0x68B560: mysql_execute_command(THD*) (sql_parse.cc:3253) ==10277== by 0x6946B3: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6173) ==10277== by 0x68610E: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1243) ==10277== by 0x68537F: do_command(THD*) (sql_parse.cc:923) ==10277== by 0x682219: handle_one_connection (sql_connect.cc:1231) ==10277== by 0x548DE99: start_thread (pthread_create.c:308) ==10277== by 0x5F9ACBC: clone (clone.S:112) ==10277== Address 0xf873978 is 136 bytes inside a block of size 148 free'd ==10277== at 0x4C2A82E: free (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so) ==10277== by 0xB7477E: _myfree (safemalloc.c:337) ==10277== by 0x56B571: String::free() (sql_string.h:240) ==10277== by 0x65165D: Field_blob::free() (field.h:1896) ==10277== by 0x6FB69C: free_blobs(st_table*) (table.cc:2531) ==10277== by 0x8C1684: sp_rcontext::~sp_rcontext() (sp_rcontext.cc:58) ==10277== by 0x8BA4E7: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:2069) ==10277== by 0x68F73B: mysql_execute_command(THD*) (sql_parse.cc:4500) ==10277== by 0x8BC917: sp_instr_stmt::exec_core(THD*, unsigned int*) (sp_head.cc:2976) ==10277== by 0x8BC22C: sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) (sp_head.cc:2794) ==10277== by 0x8BC6D9: sp_instr_stmt::execute(THD*, unsigned int*) (sp_head.cc:2919) ==10277== by 0x8B8590: sp_head::execute(THD*) (sp_head.cc:1283) ==10277== by 0x8B911A: sp_head::execute_trigger(THD*, st_mysql_lex_string const*, st_mysql_lex_string const*, st_grant_info*) (sp_head.cc:1586) ==10277== by 0x8CF381: Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool) (sql_trigger.cc:2130) ==10277== by 0x6F00E7: fill_record_n_invoke_before_triggers(THD*, List<Item>&, List<Item>&, bool, Table_triggers_list*, trg_event_type) (sql_base.cc:8747) ==10277== by 0x746914: mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) (sql_insert.cc:808) ==10277== Invalid read of size 1 ==10277== at 0x4C2D1AD: memcpy@@GLIBC_2.14 (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so) ==10277== by 0x9341B1: _mi_rec_pack (mi_dynrec.c:1015) ==10277== by 0x931674: _mi_write_blob_record (mi_dynrec.c:300) ==10277== by 0x954E5E: mi_write (mi_write.c:149) ==10277== by 0x90FA6A: ha_myisam::write_row(unsigned char*) (ha_myisam.cc:851) ==10277== by 0x810272: handler::ha_write_row(unsigned char*) (handler.cc:4978) ==10277== by 0x748E05: write_record(THD*, st_table*, st_copy_info*) (sql_insert.cc:1692) ==10277== by 0x746C0A: mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) (sql_insert.cc:887) ==10277== by 0x68B560: mysql_execute_command(THD*) (sql_parse.cc:3253) ==10277== by 0x6946B3: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6173) ==10277== by 0x68610E: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1243) ==10277== by 0x68537F: do_command(THD*) (sql_parse.cc:923) ==10277== by 0x682219: handle_one_connection (sql_connect.cc:1231) ==10277== by 0x548DE99: start_thread (pthread_create.c:308) ==10277== by 0x5F9ACBC: clone (clone.S:112) ==10277== Address 0xf873976 is 134 bytes inside a block of size 148 free'd ==10277== at 0x4C2A82E: free (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so) ==10277== by 0xB7477E: _myfree (safemalloc.c:337) ==10277== by 0x56B571: String::free() (sql_string.h:240) ==10277== by 0x65165D: Field_blob::free() (field.h:1896) ==10277== by 0x6FB69C: free_blobs(st_table*) (table.cc:2531) ==10277== by 0x8C1684: sp_rcontext::~sp_rcontext() (sp_rcontext.cc:58) ==10277== by 0x8BA4E7: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:2069) ==10277== by 0x68F73B: mysql_execute_command(THD*) (sql_parse.cc:4500) ==10277== by 0x8BC917: sp_instr_stmt::exec_core(THD*, unsigned int*) (sp_head.cc:2976) ==10277== by 0x8BC22C: sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) (sp_head.cc:2794) ==10277== by 0x8BC6D9: sp_instr_stmt::execute(THD*, unsigned int*) (sp_head.cc:2919) ==10277== by 0x8B8590: sp_head::execute(THD*) (sp_head.cc:1283) ==10277== by 0x8B911A: sp_head::execute_trigger(THD*, st_mysql_lex_string const*, st_mysql_lex_string const*, st_grant_info*) (sp_head.cc:1586) ==10277== by 0x8CF381: Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool) (sql_trigger.cc:2130) ==10277== by 0x6F00E7: fill_record_n_invoke_before_triggers(THD*, List<Item>&, List<Item>&, bool, Table_triggers_list*, trg_event_type) (sql_base.cc:8747) ==10277== by 0x746914: mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) (sql_insert.cc:808)

          OK, Filed mysql bug report:
          http://bugs.mysql.com/bug.php?id=70104

          jorenu Jonas Reinhardt added a comment - OK, Filed mysql bug report: http://bugs.mysql.com/bug.php?id=70104
          jorenu Jonas Reinhardt added a comment - - edited

          While waiting for a version that is fixed I use the following workaround inside a few triggers.
          (Perhaps its always working with user variables, or we are just lucky below, but so far it has been working, no POINT(0 0) detected)

            IF (NEW.data is not null) THEN
              CALL DataToGeometry(NEW.data, NEW.g);
              IF (NEW.g is null) or (AsText(NEW.g) is null) THEN
                SET @data=NEW.data,@g=null;
                CALL DataToGeometry(@data, @g);
                IF (@g is null) or (AsText(@g) is null) THEN
                  SET NEW.g=GeomFromText(concat('POINT(0 0)'));
                ELSE
                  NEW.g=@g;
                END IF;
              END IF;
            END IF;

          (DataToGeometry procedure converts a formated string into a geometry)

          jorenu Jonas Reinhardt added a comment - - edited While waiting for a version that is fixed I use the following workaround inside a few triggers. (Perhaps its always working with user variables, or we are just lucky below, but so far it has been working, no POINT(0 0) detected) IF (NEW.data is not null ) THEN CALL DataToGeometry(NEW.data, NEW.g); IF (NEW.g is null ) or (AsText(NEW.g) is null ) THEN SET @data=NEW.data,@g= null ; CALL DataToGeometry(@data, @g); IF (@g is null ) or (AsText(@g) is null ) THEN SET NEW.g=GeomFromText(concat( 'POINT(0 0)' )); ELSE NEW.g=@g; END IF ; END IF ; END IF ; (DataToGeometry procedure converts a formated string into a geometry)

          The upstream bug is said to have been fixed in 5.7.3.
          I still can't reproduce the problem on 10.0, only on 5.x ( (5.5 produces NULLs if not the valgrind warning).
          So I leave it to holyfoot to decide whether to fix it in 5.5.
          Raising the priority to get it moved somewhere – either fix or close as "won't fix".

          elenst Elena Stepanova added a comment - The upstream bug is said to have been fixed in 5.7.3. I still can't reproduce the problem on 10.0, only on 5.x ( (5.5 produces NULLs if not the valgrind warning). So I leave it to holyfoot to decide whether to fix it in 5.5. Raising the priority to get it moved somewhere – either fix or close as "won't fix".
          holyfoot Alexey Botchkov added a comment - Fixing patch: http://lists.askmonty.org/pipermail/commits/2015-June/008013.html

          People

            holyfoot Alexey Botchkov
            jorenu Jonas Reinhardt
            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.