MySQL oddity with disappearing body of stored procedures

In: MySQL

5 Feb 2011

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)

Share

Comment Form

About this blog

Andrew Johnstone is a software engineer / lead developer working at Everlution Software.

Photostream

  • Multiple Ucarp on the same host | PaoloBertasi: […] http://ajohnstone.com/achives/running-several-vips-on-the-same-interface-with-ucarp-and-ha [...]
  • mohit: I am working the same on windows server 2008 and mysql version 5.1.39 ,but it is not working and i a [...]
  • Steve: Hi there... sorry that this is old, but I'm trying to use your script to check for my usage per seco [...]
  • vinodh: great. thanks . My boss wanted me to configure multiple mysql instances on same physical machine . i [...]
  • andrew.johnstone: In the example above this was in fact using spl_autoload_register. I've never debugged this properly [...]

GitHub

GitHub Octocat

ajohnstone @ GitHub

  • Status updating...