Some MySQL Learnings: Comments & Procedures & MySQL Workbench Settings

Just a place to record some of the cool MySQL stuff I have learnt in the last week.

1) Working with MySQL Table Comments:

• SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME = 'test_comments';
• ALTER TABLE test_comments COMMENT = 'This is a comment';
• SHOW CREATE TABLE test_comments\G

From: https://dba.stackexchange.com/questions/59587/changing-mysql-table-comment

2) Understanding Stored Procedures and Call in MySQL

“The CALL statement invokes a stored procedure that was defined previously with CREATE PROCEDURE.”

• SHOW PROCEDURE STATUS;
• SHOW PROCEDURE STATUS LIKE '%Order%';
• SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
• SELECT routine_name FROM information_schema.routines WHERE routine_type = 'PROCEDURE' AND routine_schema = 'classicmodels';
• SHOW CREATE PROCEDURE test.citycount\G

Note: In order to see the stored procedures you need to be logged in with the right permission.

• From + further reading:
• https://dev.mysql.com/doc/refman/8.0/en/call.html
• https://www.mysqltutorial.org/listing-stored-procedures-in-mysql-database.aspx/
• https://dev.mysql.com/doc/refman/8.0/en/show-create-procedure.html
• http://www.java2s.com/Tutorial/MySQL/0201__Procedure-Function/StoredProcedurePermissions.htm

3) MySQL error code: 1175 during UPDATE in MySQL Workbench

SET SQL_SAFE_UPDATES = 0;

• In MySQL Workbench:
• Go to Edit --> Preferences
• Click "SQL Editor" tab and uncheck "Safe Updates" check box
• Query --> Reconnect to Server // logout and then login
• Now execute your SQL query

From: https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench

Image: Untick Safe Updates

4) Problem Loading Local File with MySQL Workbench

In your MySQL connection settings you need:

OPT_LOCAL_INFILE=1

Image: MySQL Workbench OPT_LOCAL_INFILE = 1 (so you can load local files)