my.cnf [client] options

[jtolsdorf]

I am working with a couple of mysql servers and wish to implement the –safe-updates option available in mysql on them. One is on Windows and the other Red Hat. I am only working with the Windows machine at this time.

When I add the “safe-updates” option to the [client] section in the c:my.cnf file, SQLyog fails to recognize it (I can select more than 1000 rows and update without a where clause). If I connect using the command line mysql program, the option is recognized and enforced. No errors or exceptions are thrown in SQLyog.

I can run the equivalient sql statement of:

Code:

set sql_safe_updates=1,sql_select_limit=1000, sql_max_join_size=1000000;

after connecting with SQLyog and everything is enforced, but I really don't want to have to remember to run that statement everytime I connect. Is there a way to automatically run a sql statement after a successful connection?

Windows Server 2003

MySql 3.23.58-nt

SQLyog 3.71

The [client] portion of the c:my.cnf file is:

Code:

# This will be passed to all mysql clients
[client]
safe-updates
#password=my_password
port=3306
#socket=MySQL

I have been unable to find an answer, does anyone have a solution or suggestion? Any help is greatly appreciated. Thanks!

-Jeremy

[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