[MDEV-13005] Fixing bugs in SEQUENCE, part 3 Created: 2017-06-06  Updated: 2023-12-13  Resolved: 2022-03-30

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.3.1
Fix Version/s: 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3, 10.9.1

Type: Bug Priority: Major
Reporter: Michael Widenius Assignee: Rucha Deodhar
Resolution: Fixed Votes: 2
Labels: beginner-friendly

Issue Links:
PartOf
is part of MDEV-10139 Support for SEQUENCE objects Closed
Problem/Incident
causes MDEV-32795 ALTER SEQUENCE IF NOT EXISTS non_exis... Closed
Relates
relates to MDEV-22705 Assertion `llabs(next_free_value % re... Confirmed
relates to MDEV-28152 Features for sequence Stalled
Epic Link: Compatibility

 Description   

This is a list smaller bugs for SEQUENCE's that should be fixed before 10.3.1. A lot of the following things where reported by Peter Gulutzan.

Bugs and missing features

  • SET @x = PREVIOUS VALUE FOR x;
    > Result = Error 1146 (42S02) Table 'test.x' doesn't exist
    Should be SEQUENCE doesn't exists
  • CREATE SEQUENCE x START WITH 1 INCREMENT BY 123456789012345678;
    > Result = Error 4061 (HY000) Sequence 'test.x' values are conflicting
    > This is not a helpful message, saying "too big number" would be better.
  • Create better error messages for the test of initial seqeunce values in:
    bool sequence_definition::check_and_adjust(bool set_reserved_until)
    But try to create reusable error messages
    Type:
    %s must be less than %s
    Too big value for %s
    Add also a check the cache can't be < 0 and not equal or bigger than
    LONGLONG_MAX.
  • This should give an error for the second create:
    CREATE TEMPORARY TABLE s1 (s1 INT);
    CREATE TEMPORARY SEQUENCE s1 (s1 INT);
  • CREATE TABLE s1 (s1 INT);
    DROP SEQUENCE s1;
    > Error 4066 (42S02) 'test.s1' is not a SEQUENCE
    CREATE TEMPORARY TABLE s1 (s1 INT);
    DROP TEMPORARY SEQUENCE s1;
    > Error 4067 (42S02) Unknown SEQUENCE: 'test.s1'

The second DROP should also give error 4066

  • create trigger s1 before update on sequence_name for each row set @a = 5;
    The above should fail with a new error 'One can't create a trigger on
    a sequence"

 Comments   
Comment by Rucha Deodhar [ 2019-03-13 ]

I am new dev and can help with this. I went through the code base, but couldn't find the code where these bugs are present. Any help is appreciated. Thanks.

Comment by Anel Husakovic [ 2019-03-13 ]

Hi rucha174
let's start with 

 CREATE SEQUENCE x START WITH 1 INCREMENT BY 123456789012345678;


So what I usually do when dealing with error messages is to put the breakpoint on 

 my_message_sql

.
After doing so you will get the backtrace like so:

(gdb) bt
 
+bt
 
#0  my_message_sql (error=4085, str=0x7ffff410a230 "Sequence 'test.x' values are conflicting", MyFlags=0) at sql/mysqld.cc:3258
 
#1  0x000055555674fb4e in my_error (nr=4085, MyFlags=0) at mysys/my_error.c:125
 
#2  0x0000555555f310ba in MYSQLparse (thd=0x7fff64000cf8) at /home/anel/workspace/server/sql/sql_yacc.yy:2708
 
#3  0x0000555555cd53de in parse_sql (thd=0x7fff64000cf8, parser_state=0x7ffff410c1b0, creation_ctx=0x0, do_pfs_digest=true) at sql/sql_parse.cc:10247
 
#4  0x0000555555cd0314 in mysql_parse (thd=0x7fff64000cf8, rawbuf=0x7fff64014af0 "CREATE SEQUENCE x START WITH 1 INCREMENT BY 123456789012345678", length=62, parser_state=0x7ffff410c1b0, is_com_multi=false, is_next_command=false) at sql/sql_parse.cc:8157
 
#5  0x0000555555cbb95d in dispatch_command (command=COM_QUERY, thd=0x7fff64000cf8, packet=0x7fff64009f79 "CREATE SEQUENCE x START WITH 1 INCREMENT BY 123456789012345678", packet_length=62, is_com_multi=false, is_next_command=false) at sql/sql_parse.cc:1828
 
#6  0x0000555555cba193 in do_command (thd=0x7fff64000cf8) at sql/sql_parse.cc:1357
 
#7  0x0000555555e2da46 in do_handle_one_connection (connect=0x555558a68948) at sql/sql_connect.cc:1399
 
#8  0x0000555555e2d7aa in handle_one_connection (arg=0x555558a68948) at sql/sql_connect.cc:1302
 
#9  0x00007ffff6c996db in start_thread (arg=0x7ffff410d700) at pthread_create.c:463
 
#10 0x00007ffff5e7f88f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Now if you go in 

 
(gdb) f 2
 
+f 2
 
#2  0x0000555555f310ba in MYSQLparse (thd=0x7fff64000cf8) at /home/anel/workspace/server/sql/sql_yacc.yy:2708

you will see error 

 ER_SEQUENCE_INVALID_DATA

that is raised in .yacc file:

if (unlikely(lex->create_info.seq_create_info->check_and_adjust(1)))                                        │
 
   │2707                {                                                                                                           │
 
  >│2708                  my_error(ER_SEQUENCE_INVALID_DATA, MYF(0),                                                                │
 
   │2709                           lex->first_select_lex()->table_list.first->db.str,                                               │
 
   │2710                           lex->first_select_lex()->table_list.first->                                                      │
 
   │2711                             table_name.str);                                                                               │
 
   │2712                  MYSQL_YYABORT;                                                                                            │
 
   │2713                }

Now to see this error you will need to go in 

./sql/share/errmsg-utf8.txt


Try to do the same procedure, find this message and change it, compile again and run, new result should be obtained.
If you need additional help please feel free to ask on our zulip channel https://mariadb.zulipchat.com/# where also other developer will be active and provide you additional help.
Happy coding ^^

Comment by Rucha Deodhar [ 2019-05-28 ]

I changed error message for:
(42S02) "Table 'test.x' doesn't exist" to "Sequence doesn't exist." and
Error 4061 (HY000) "Sequence 'test.x' values are conflicting" to "Too big number"

Comment by Rucha Deodhar [ 2021-12-06 ]

Hi julien.fritsch , yes. Thank you !

Comment by Rucha Deodhar [ 2022-03-24 ]

Made separate patch for each sub-task: bb-10.4-MDEV-13005

Comment by Oleksandr Byelkin [ 2022-03-29 ]

OK to push

Generated at Thu Feb 08 08:02:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.