instable behaviour of mysql
Hi turns me crazy: no reply button found in your ... mailing-list. Hello. Please send us an ouput of the following statement, which you should perform in the middle of your test case: show grants for current_user(); THIS IS THE OUTPUT: DROP TABLE fai_accounts DROP TABLE fai_contacts DROP TABLE fai_knowledge DROP TABLE fai_tasks DROP TABLE fai_timer DELETE FROM participants WHERE ident = 'fai' DELETE FROM part_val WHERE ident = 'fai' UPDATE SET activ = 'inactiv' WHERE ident = 'fai' Query 'UPDATE SET activ = 'inactiv' WHERE ident = 'fai'' failed. MySQL Error Number: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET activ = 'inactiv' WHERE ident = 'fai'' at line 1 REVOKE all ON fai_accounts FROM 'fai'@'mydom.tld' MySQL Error Number: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET activ = 'inactiv' WHERE ident = 'fai'' at line 1 show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_contacts FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_knowledge FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_tasks FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_timer FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_files FROM 'fai'@'mydom.tld' Query 'REVOKE all ON fai_files FROM 'fai'@'mydom.tld'' failed. MySQL Error Number: 1147: There is no such grant defined for user 'fai' on host 'mydom.tld' on table 'fai_files' show grants for current_user() MySQL Error Number: 1147: There is no such grant defined for user 'fai' on host 'mydom.tld' on table 'fai_files' Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select ON participants FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select,update ON part_val FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE GRANT OPTION ON *.* FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION DROP USER 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION and after that i get [EMAIL PROTECTED]:~ mysql -h mydom.tld -u root -p Enter password: ERROR 1130 (0): Host 'mydom.tld' is not allowed to connect to this MySQL server [EMAIL PROTECTED]:~ and when i do a (in a valid root window of course) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql i can login again: [EMAIL PROTECTED]:~ mysql -h mydom.tld -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 113 to server version: 4.1.7-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: instable behaviour of mysql
First of all you have an eroneus update statement: UPDATE SET activ = 'inactiv' WHERE ident = 'fai' After UPDATE the tablename must be pesent UPDATE `tble_name` SET activ = 'inactiv' WHERE ident = 'fai' Now next in line... REVOKE all ON fai_accounts FROM 'fai'@'mydom.tld' You are revoking user 'fai' from host 'mydom.tld' at database 'fai_accounts'... but this has nothing to do with show grants for current_user() Because that will show the grant 'source' for the current user that I think is not 'fai' but 'root' as you output says... To see grants for 'fai'@'mydom.tld' you should use: SHOW GRANTS FOR 'fai'@'mydom.tld' Hope it helps ! Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi in the meantime i installed mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.07 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ shows exactly the same behaviour as the older version: DROP TABLE fili_accounts DROP TABLE fili_contacts DROP TABLE fili_knowledge DROP TABLE fili_tasks DROP TABLE fili_timer DELETE FROM participants WHERE ident = 'fili' DELETE FROM part_val WHERE ident = 'fili' UPDATE SET activ = 'inactiv' WHERE ident = 'fili' Query 'UPDATE SET activ = 'inactiv' WHERE ident = 'fili'' failed. MySQL Error Number: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET activ = 'inactiv' WHERE ident = 'fili'' at line 1 REVOKE all ON fili_accounts FROM 'fili'@'mydom.tld' MySQL Error Number: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET activ = 'inactiv' WHERE ident = 'fili'' at line 1 show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_contacts FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_knowledge FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_tasks FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_timer FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_files FROM 'fili'@'mydom.tld' Query 'REVOKE all ON fili_files FROM 'fili'@'mydom.tld'' failed. MySQL Error Number: 1147: There is no such grant defined for user 'fili' on host 'mydom.tld' on table 'fili_files' show grants for current_user() MySQL Error Number: 1147: There is no such grant defined for user 'fili' on host 'mydom.tld' on table 'fili_files' Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select ON participants FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select,update ON part_val FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE GRANT OPTION ON *.* FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION DROP USER 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION and after that [EMAIL PROTECTED]:~ mysql -h mydom -u root -p Enter password: ERROR 1130 (0): Host 'rosetta.ayni.com' is not allowed to connect to this MySQL server [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ and then doing a mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql which makes me again go into mysql: [EMAIL PROTECTED]:~ mysql -h mydom -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 4.1.9-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: instable behaviour of mysql
Hello. Please send us an ouput of the following statement, which you should perform in the middle of your test case: show grants for current_user(); Can you find, if your grants change? Can you reproduce a problem on the latest release (4.1.9 now)? schlubediwup [EMAIL PROTECTED] wrote: Hi again mysql-listers regarding the problem i mentionned this morning: for a description check out my message of this morning. in the meantime i have found out 1. how to reproduce the problem 2. how to work around the problem 1. how to reproduce: DROP TABLE afir_accounts ; DROP TABLE afir_contacts ; DROP TABLE afir_knowledge ; DROP TABLE afir_tasks ; DROP TABLE afir_timer ; DELETE FROM participants WHERE ident = 'afir' ; DELETE FROM part_val WHERE ident = 'afir' ; REVOKE all ON afir_accounts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_contacts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_knowledge FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_tasks FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_timer FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_files FROM 'afir'@'myhost.tld' ; REVOKE select ON participants FROM 'afir'@'myhost.tld' ; REVOKE select,update ON part_val FROM 'afir'@'myhost.tld' ; REVOKE GRANT OPTION ON *.* FROM 'afir'@'myhost.tld' ; DROP USER 'afir'@'myhost.tld'; you may substitute whatever string for afir. after such a sequence of commands the root-userid i described in my message earlier today cannot be used to access the mysql db anymore. you get the error message host myhost.tld is not allowed to access this mysql server. 2. how to work around i just added FLUSH PRIVILEGES ; to the above sequence, and everything works well. this command, however, according to mysql docu, should not be necessary after such a sequence of commands. suomi -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi again mysql-listers regarding the problem i mentionned this morning: for a description check out my message of this morning. in the meantime i have found out 1. how to reproduce the problem 2. how to work around the problem 1. how to reproduce: DROP TABLE afir_accounts ; DROP TABLE afir_contacts ; DROP TABLE afir_knowledge ; DROP TABLE afir_tasks ; DROP TABLE afir_timer ; DELETE FROM participants WHERE ident = 'afir' ; DELETE FROM part_val WHERE ident = 'afir' ; REVOKE all ON afir_accounts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_contacts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_knowledge FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_tasks FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_timer FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_files FROM 'afir'@'myhost.tld' ; REVOKE select ON participants FROM 'afir'@'myhost.tld' ; REVOKE select,update ON part_val FROM 'afir'@'myhost.tld' ; REVOKE GRANT OPTION ON *.* FROM 'afir'@'myhost.tld' ; DROP USER 'afir'@'myhost.tld'; you may substitute whatever string for afir. after such a sequence of commands the root-userid i described in my message earlier today cannot be used to access the mysql db anymore. you get the error message host myhost.tld is not allowed to access this mysql server. 2. how to work around i just added FLUSH PRIVILEGES ; to the above sequence, and everything works well. this command, however, according to mysql docu, should not be necessary after such a sequence of commands. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi mysql-listers mysql select version(); ++ | version() | ++ | 4.1.7-standard-log | ++ 1 row in set (0.10 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ following mysql-behaviour made me loose several hours yesterday: 1. a root user is installed with password having overall permission over the database. i can access the db through this root user in the following manner: [EMAIL PROTECTED]:~ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 to server version: 4.1.7-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 2. for the sake of an application i added a second user named root, but this one from the host rosetta. i added the password for this second root user using use mysql; update user set password ... where user = root and host = rosetta.ayni.com; flush privileges; the password i attributed to this root-user is the same as that one for the original root user. my intention was to access the database in the following manner: [EMAIL PROTECTED]:~ mysql -h rosetta -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.7-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 3. this second root-user, however, presented the following strange behaviour: i could access the database in the manner described in 2. several times. and then, at once, access was denied, and i had to redo the flush privileges; from then on, i could access the db again several times as described in 2., but again after some time, access was denied and i had to redo the flush privileges; 4. what i did then: 4.a took a mysqldump of the entire mysql installation. 4.b stopped the mysql daemon 4.c removed the entire directory where the database had been 4.d uninstalled mysql-server 4.e re-installed mysql-server 4.f re-created the database from the mysqldump 5. remains to be seen if now mysql is stable suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
