Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5923

"SELECT ... INTO var_name FOR UPDATE" was not working in MySQL 8.0.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 8.0.x
    • Fix Version/s: 8.0.19-10
    • Component/s: None
    • Labels:

      Description

       
      Description:

      As per document - https://dev.mysql.com/doc/refman/8.0/en/select.html, the user defined variable could be mentioned in SELECT stmt as follows:

      SELECT
      ...
      ...
       | INTO var_name [, var_name]]
       [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] 
       | LOCK IN SHARE MODE]]

      When assigning variables like above, it is working as expected in 5.7 whereas failing in 8.0 versions. This needs code change everywhere when migrating from 5.7 to 8.0 versions. So it needs to be fixed

       

      How to repeat:

      -------------------

      In 5.7 version test:

      -------------------
      mysql> select version();
      ---------------

      version()

      ---------------

      5.7.26-29-log

      ---------------
      1 row in set (0.00 sec)

      mysql> select id from t1 limit 1;
      ------

      id

      ------

      1

      ------
      1 row in set (0.00 sec)

      mysql> select id from t1 limit 1 into @id_var for update;
      Query OK, 1 row affected (0.00 sec)

      mysql>
       

      --------------------
      In 8.0 version test:
      -------------------

      mysql> select version();
      +-----------+
      | version() |
      +-----------+
      | 8.0.15-6 |
      +-----------+
      1 row in set (0.00 sec)
      mysql> select id from t1 limit 1;
      +------+
      | id |
      +------+
      | 1 |
      +------+
      1 row in set (0.00 sec)
      mysql> select id from t1 limit 1 into @id_var for update;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for update' at line 1
      mysql> 
      mysql> 
      mysql> select id from t1 limit 1 for update into @id_var;
      Query OK, 1 row affected (0.00 sec)
      

      +++++++++++++++++++++++++++++

      Suggested fix:

      The following query should work as documented in https://dev.mysql.com/doc/refman/8.0/en/select-into.html

       

      select id from t1 limit 1 into @id_var for update;

       

       

       

        Attachments

          Activity

            People

            Assignee:
            zsolt.parragi Zsolt Parragi
            Reporter:
            vinodh.krishnaswamy Vinodh Krishnaswamy
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 1 day, 5 hours
                1d 5h

                  Smart Checklist