Mysql – Permenantly disable Safe Updates in MySQL Workbench

mysqlmysql-workbench

Re: the error "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column"

The two options to fix this are disabling safe mode through the gui (Edit>Preferences>Sql Editor) or by manually entering SET SQL_SAFE_UPDATES = 0;

However, everything I'm reading seems to indicate you must do this every time you connect. Is there a way to disable Safe Update Mode permanently so I don't need to do this every time?

Best Solution

Given that Workbench (at least in Windows-world) restores your open queries, if you keep SET SQL_SAFE_UPDATES = 0; in a query window, it will be quicker to re-run at start-up than re-entering it. Maybe there are other settings which aren't saved but could be added too. Like if there was one to say "no side panels please", or "show Schema, not Administration".

Not ideal, but every little helps.

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Mysql – How to reset AUTO_INCREMENT in MySQL

You can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1

For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel):

Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

See How can I reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.

Best Solution

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Mysql – How to reset AUTO_INCREMENT in MySQL

Related Question