Re: Unknown error when runing stored procedure

[Ike Walker]

Adrian,

What version of MySQL are you using?

I don't have any idea why you are getting that error. Try checking the error log.

If you are going to create your own copy of INFORMATION_SCHEMA, you might as well go ahead and do that and see if it solves your problem. It should be easy to implement since you are only using 2 tables.

If you don't want to do that, you could try rewriting the case 0 query to use a UNION rather than the OR you currently have to see if this efficiency gets rid of the problem or makes it happen less frequently:

SELECT information_schema.columns.table_name AS "Table", information_schema.columns.column_name AS "Column"
FROM information_schema.columns, information_schema.tables
WHERE information_schema.columns.table_schema = 'pabeta'
AND information_schema.columns.table_name LIKE CONCAT('%', what, '%')
AND information_schema.columns.table_name = information_schema.tables.table_name
AND information_schema.tables.table_type = 'BASE TABLE'
UNION
SELECT information_schema.columns.table_name AS "Table", information_schema.columns.column_name AS "Column"
FROM information_schema.columns, information_schema.tables
WHERE information_schema.columns.table_schema = 'pabeta'
AND information_schema.columns.column_name LIKE CONCAT('%', what, '%')
AND information_schema.columns.table_name = information_schema.tables.table_name
AND information_schema.tables.table_type = 'BASE TABLE'
ORDER BY 1,2;

Also, be aware of the fact that even if you index table_name and column_name in your new tables, queries like this are still going to be relatively slow because the leading wildcard makes the index almost useless, but they at least will benefit from the key cache or buffer pool if you create your own tables:

AND information_schema.columns.column_name LIKE CONCAT('%', what, '%')