mysql

Safe update – MySQL workbench

Posted on Updated on

sometime when we run update / delete in my sql using mysql workbench, we’ll encounter the following:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect. 0.000 sec

Solution:

run the following command:

SET SQL_SAFE_UPDATES = 0;

[mysql] Show all column from all tables

Posted on

Sometime when we work on a large database structure that contains many tables, we don’t know the relationship between tables. If the architect never write any documentation, we may have difficulties to trace the relationship.

Luckily, most of the Database system allow us to query the columns and tables name in the database.
Following is the SQL script to select all tables from a MySQL database.

select * from information_schema.columns where table_schema = ‘l2gs’
 order by table_name,ordinal_position

So when we are frustrated on how  a table is related to another table, we can easily filter by the column name, hopefully the database architect create the same column name for the related table

select * from information_schema.columns where table_schema = ‘l2gs’and column_name like ‘%REASON%’ order by table_name,ordinal_position




Hope it helps all the developers who have same problem