my.cnf [client] options

[Shadow]

First of all, it is MySql and not SQLyog that honors this setting (it is not SQLyog that enforces this option). Interesting, however, if you set the safe-update option in the config file and you connect via mysql's command line utility, then select @@sql_safe_updates; command yields to 1 (set), while the same command in SQLyog's SQL window yields to 0 (not set). Hopefully, Ritesh will be able to explain this behaviour.

Quote:

I can select more than 1000 rows and update without a where clause

If you use SQLyog's GUI to retrieve and manipulate data, then this behaviour is understandable: SQLyog uses PKs to update or delete rows, and adds explicit LIMIT clauses to its SELECT statements that override the default setting.

Unfortunately, I was unable to start the server with this option, so currently you have no other option othar than adding these statements to the personal folder in SQLyog and issuing them every time you start working.

[jtolsdorf]

According to the mysql website:

Quote:

When you use the –safe-updates option, mysql issues the following statement when it connects to the MySQL server:

SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000, SQL_MAX_JOIN_SIZE=1000000

http://dev.mysql.com/doc/mysql/en/safe-updates.html

So it is up to the client to recognize this option and issue the statement above to the mysql server. After that, the mysql server enforces the options set for your connection.

During normal use of SQLyog I would fully expect to be able to select more than 1000 rows. However the safe-updates options is supposed to prevent this (1000 rows max by default and no updates without a where clause containing a key constraint or limit), I was only using it as an example of the safe-updates options not being set.

[Shadow]

MySql clients do not read my.cnf or my.ini directly, those settings must be propagated by MySql server. No matter what related settings you configure in my.cnf, they are not reflected in the session variables of MySql . I belive, this rather an issue with MySql rather than an issue with SQLyog. Unfortunately, it is Ritesh only, who can tell for sure which side is fault in this particular case.

[Author]

Posts