randgen team mailing list archive

[Bernt M Johnsen <1074485@xxxxxxxxxxxxxxxxxx>]

On 11/02/2012 10:28 PM, RoelV wrote:
> Another option is to fix up the trace on RQG completion:
>
> cat out.sql | sed 's/^\(.*\)PROCEDURE\(.*\)$/DELIMITER |\n\1PROCEDURE\2
> |\nDELIMITER ;/' > out_fixed.sql
>
> Fixes the problem. What would be the best place to fix this (presumably
> runall.pl)?
>
No. It _*has*_ to be fixed in gentest.pl or preferrably in the 
appropriate module. We are using RQG from other testing frameworks and 
there we don't use runall-new.pl/runall.pl.

-- 
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