I wrote a couple rather large stored procedure some time ago to generate a couple reports. A bash script truncates a temporary file and executes the stored procedure writing to a temporary location, the bash script adds the headers to the final location of the csv file and concatenates the contents of the csv written from the stored procedure.

Yesterday I got a call to state this was no longer working, I checked the temporary files written from the stored procedure and they had the most recent modification times on the files and in addition the final destination was correct . The payload of the data had been written to the target destination appearing as if it was correct. As the temporary files are deleted, the contents of the report would only contain the header of the csv file.

The data within the report stated that the information had outputted data up to the 17th Jan 2011, which we should have had data up to the 4th Feb, indicating that the report had been run previously and the stored procedures had existed at that point.

When looking at the routines themselves there was no body definition for either of the stored procedures and the last modification to them was 2010-09-28 23:07:36, the funny thing is that the report had been ran and the reports delivered to the client. In addition the data in the report stated that they had been ran.

I have no idea how this could occur because the information doesn’t add up.

mysql> SELECT * FROM information_schema.routines WHERE routine_name LIKE 'gk_reports_sector_hit_%'\G
*************************** 1. row ***************************
     SPECIFIC_NAME: gk_reports_sector_hit_journalists
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: gk_live
      ROUTINE_NAME: gk_reports_sector_hit_journalists
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER: NULL
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: NULL
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2010-09-28 23:07:36
      LAST_ALTERED: 2010-09-28 23:07:36
          SQL_MODE: 
   ROUTINE_COMMENT: 
           DEFINER: root@localhost
*************************** 2. row ***************************
     SPECIFIC_NAME: gk_reports_sector_hit_media_outlets
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: gk_live
      ROUTINE_NAME: gk_reports_sector_hit_media_outlets
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER: NULL
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: NULL
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2010-09-29 14:41:10
      LAST_ALTERED: 2010-09-29 14:41:10
          SQL_MODE: 
   ROUTINE_COMMENT: 
           DEFINER: root@localhost
2 rows in set (0.01 sec)

mysql> SHOW CREATE PROCEDURE gk_reports_sector_hit_journalists;
+-----------------------------------+----------+------------------+
| Procedure                         | sql_mode | Create Procedure |
+-----------------------------------+----------+------------------+
| gk_reports_sector_hit_journalists |          | NULL             | 
+-----------------------------------+----------+------------------+
1 row in set (0.00 sec)