randgen team mailing list archive

[RoelV <1074485@xxxxxxxxxxxxxxxxxx>]

The fix may be as easy as a regex looking for 'PROCEDURE' and 'FUNCTION'
in a line. If found, auto-add 'DELIMITER |' before that line and
'DELIMITER ;' after that line in the sqltrace log.

-- 
You received this bug notification because you are a member of Random
Query Generator Team, which is subscribed to Random Query Generator.
https://bugs.launchpad.net/bugs/1074485

Title:
  Reproducibility issue: stored procedures/functions not correctly
  prefixed with delimiters in query log

Status in SQL Generator for testing SQL servers (MySQL, JavaDB, PostgreSQL):
  Confirmed

Bug description:
  The bug:
  Stored procedures and functions executed from within RQG may execute correctly from RQG/Perl, but they do not execute correctly when the query log is executed directly. They need proper delimiter prefixing. This could be a major cause for non-reproducibility of issues seen during runs.

  How to reproduce:
  - Use any RQG run which employs stored procedures or stored functions
  - Use --sqltrace
  - Check the RQG log (containing the queries) after the run

  An example:
  => In the yy file:
  proc_func:
          DROP PROCEDURE IF EXISTS _letter[invariant] ; CREATE PROCEDURE _letter[invariant] ( proc_param ) BEGIN SELECT COUNT( _field ) INTO @a FROM _table ; END ; CALL _letter[invariant](@a); |
  => resulting in the following query being logged (copy/paste from the RQG log):
          DROP PROCEDURE IF EXISTS n ; CREATE PROCEDURE n ( IN v BLOB ) BEGIN SELECT COUNT( `c70` ) INTO @a FROM `view_table500_innodb_default_int_autoinc` ; END ; CALL n(@a);;

  Executing this at the command line (fails):
  mysql> DROP PROCEDURE IF EXISTS n ; CREATE PROCEDURE n ( IN v BLOB ) BEGIN SELECT COUNT( `c70` ) INTO @a FROM `view_table500_innodb_default_int_autoinc` ; END ; CALL n(@a);;
  Query OK, 0 rows affected (0.01 sec)
  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 '' at line 1
  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 'END' at line 1
  ERROR 1305 (42000): PROCEDURE test.n does not exist
  ERROR: No query specified

  Executing this with the proper prefix/append DELIMITER statements (works):
  mysql> DELIMITER |
  mysql> DROP PROCEDURE IF EXISTS n ; CREATE PROCEDURE n ( IN v BLOB ) BEGIN SELECT COUNT( `c70` ) INTO @a FROM `view_table500_innodb_default_int_autoinc` ; END ; CALL n(@a);;
      -> |
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Query OK, 0 rows affected (0.01 sec)
  Query OK, 1 row affected (0.51 sec)
  mysql> DELIMITER ;

  Proposed fix:
  Auto-prefix and auto-append the following items:
  - Prefix the procedure/function line with "DELIMITER |"
  - End the actual procedure/function line with "|"
  - Change the delimiter back with "DELIMITER ;" afterwards

  Again, this may be a major cause for non-reproducibility.

To manage notifications about this bug go to:
https://bugs.launchpad.net/randgen/+bug/1074485/+subscriptions