Mysql – SHOW CREATE PROCEDURE procedure_name not showing the procedure content

linuxMySQLmysql-workbenchstored-procedures

Hello I'm struggling with the Issue where i have to view the stored procedure content for that i have issue the command to get the name of the Procedure

mysql> SHOW PROCEDURE STATUS;
+-----+---------------------------------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db  | Name                            | Type      | Definer   | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----+---------------------------------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| vmp | ccontrol_table | PROCEDURE | user1@% | 2015-11-10 01:01:51 | 2015-11-10 01:01:51 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-----+---------------------------------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.27 sec)

It shows me the Procedure Exists with name control_table
To view the Procedure i have used SHOW CREATE PROCEDURE control_table it's display in this format not the Content

mysql> SHOW CREATE PROCEDURE control_table;
+---------------------------------+----------+------------------+----------------------+----------------------+--------------------+
| Procedure                       | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+---------------------------------+----------+------------------+----------------------+----------------------+--------------------+
| control_table |          | NULL             | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+---------------------------------+----------+------------------+----------------------+----------------------+--------------------+
1 row in set (0.28 sec)

I Have executed this Using MySQL Client and mySQL Client Workbench, not able to view the procedure

There is no routine

mysql> SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES;
+--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.28 sec)

The MySQL Version

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.73              |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.26 sec)


mysql> DESC control_table;
ERROR 1146 (42S02): Table 'control_table' doesn't exist

Best Answer

It just dawned on me. You must be using MySQL 8.0

I just heard about this headache not too long ago.

The SHOW CREATE PROCEDURE for MySQL 8.0 does not show a body column anymore.

The design of INFORMATION_SCHEMA.ROUTINES is also different for MySQL 8.0.

Someone wrote about this 4 months ago (See No more mysql.proc in MySQL 8.0). This paradigm will take getting used to. Personally, I am not comfortable with it, but we need to just get used to it.

You also gotta wrap your head around INFORMATION_SCHEMA.PARAMETERS, which itemizes each parameter passed to a stored procedure.

Just to skip all this other yucky stuff, MySQL 8.0 FAQ says the following:

Stored procedures are stored in the mysql.routines and mysql.parameters tables, which are part of the data dictionary. You cannot access these tables directly. Instead, query the INFORMATION_SCHEMA ROUTINES and PARAMETERS tables. See Section 25.19, “The INFORMATION_SCHEMA ROUTINES Table”, and Section 25.12, “The INFORMATION_SCHEMA PARAMETERS Table”.

You can also use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 13.7.6.9, “SHOW CREATE PROCEDURE Syntax”.

I will leave it to you to reserch the rest of this.

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.

Mysql – ERROR 1166 (42000) at line 65203: Incorrect column name ”

The only database object I can think of with the option CREATE ALGORITHM is a VIEW

To see all the views you have, run this

SELECT table_schema,table_name FROM information_schema.views;

SELECT table_schema,table_name FROM information_schema.tables WHERE engine IS NULL;

From the given error message, one of the views in your database has a column that no longer exists. This can easily happen if you drop a table or a column from a table that is referenced in the view.

Please look over all your views and make sure you can SELECT from all of them.

CORRECTIVE ACTION

You may have to recreate the mysqldump after you fix the VIEW in the source database.

If you cannot recreate the mysqldump due to the size, you may have edit the mysqldump to ignore the VIEW definition altogether.

Best Answer

Related Solutions

MySQL database drop insanely slow

UPDATE 2013-04-15 18:04 EDT

Recommendation : Remove all Data and Index Pages from ibdata1

UPDATE 2013-04-22 08:10 EDT

Mysql – ERROR 1166 (42000) at line 65203: Incorrect column name ”

CORRECTIVE ACTION

Related Question