Re: password problem
Data directory path mention in cnf is of old mysql. Make a fresh data directory, configure it in configuration file and execute mysqlinstall_db, I don't understand the sentence about the data directory path mention. The my.cnf file is at /etc/my.cnf . It doesn't have any data directory path mention, but neither does the my.cnf file on a laptop, which works. So there seems to be nothing wrong with the location or content of the my.cnf file. On Fri, Jul 31, 2015 at 5:11 AM, Martin Mueller martinmuel...@northwestern.edumailto:martinmuel...@northwestern.edu wrote: I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot run the mysql command because it challenges me for a password. But I did not set any password, either for the root, for mysql, or for myself as a user. So the installation has somehow installed passwords about which I know nothing or there is some error in the installation process. There is a lot on the Web about resetting a forgotten password. But the assumption is always that you can get at the program via some other password. But in this case every door is shut. Does anybody recognize this problem? I've uninstalled and re-installed the program, but the results are always the same. Martin Mueller Professor emeritus of English and Classics Northwestern University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Thanks and Regards: Nikhil Anand +91 9650024197
Re: password problem
Am 31.07.2015 um 14:40 schrieb Martin Mueller: Sorry for the off-list reply. It was an oversight. That said, the instructions for resetting a forgotten root password have a section for Windows and a section for Unix. The Unix section begins as follows: 1. Log on to your system as the Unix user that the MySQL server runs as (for example, mysql). But if I do this with the command 'mysql -u mysql I get the answer Access denied for user 'mysql'@'localhost' (using password: NO) I can do this as super user or normal, and I can try passwords from earlier installations, but none of them work. So I am stopped dead in my tracks, am I not? what do you not understand in: Resetting the Root Password: Generic Instructions Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option jesus christ, put skip-grant-tables in your my.cnf, make sure the server is not reachable from outside and just type myysql -u root and don't forget remove skip-grant-tables after you defined a password you are knowing and restarting the server again As for the datadir, the command update db locate mysql works on the Mac and gives me info about a whole set of files in /usr/local/mysql-5.1.73-osx10.6-x86_64. That's where I thought it was, and I deleted a previous installation because I had moved the data I needed to another machine. I'm not a very experienced programmer and have trouble wrestling with the command line. But I think I did my due diligence and didn't find any open doors. well, you have a bad mix * missing knowledge * a blackbox with a installer * refusing to read more than the begin of docs On 7/31/15 3:36 AM, Reindl Harald h.rei...@thelounge.net wrote: first: don't reply off-list, a answer on a mailing-list is no invitation for private support! Am 31.07.2015 um 02:34 schrieb Martin Mueller: I read that section but was stopped in my tracks by Log on to your system as the Unix user that the MySQL server runs as (for example, mysql) Because I have no password for ANY thing. read the f**ng https://dev.mysql.com/doc/refman5.0/en/resetting-permissions.html https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html - unbelievebale that users these days need anything ready chewed and are too lazy to click on a link and read more than 5 lines Resetting the Root Password: Generic Instructions Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option I used the uninstall routine recommended by Rob Allen, in which you remove the directories /usr/local/mysql as well as /usr/local/mysql* and a lot of other library and etc files. So there is no trace of the old system on my machine. How come a routine installation of mysql then locks up the application. the datadir is *not* removed by any sane installer, dunno where it lives on Apple machines since i banned them 5 years ago for good reasons on a non-OSX i would just type updatedb; locate mysql als root On 7/30/15, 19:22, Reindl Harald h.rei...@thelounge.net wrote: Am 31.07.2015 um 01:41 schrieb Martin Mueller: I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot run the mysql command because it challenges me for a password. But I did not set any password, either for the root, for mysql, or for myself as a user. So the installation has somehow installed passwords about which I know nothing or there is some error in the installation process. There is a lot on the Web about resetting a forgotten password. But the assumption is always that you can get at the program via some other password. But in this case every door is shut. Does anybody recognize this problem? I've uninstalled and re-installed the program, but the results are always the same * install and uninstall *never* removes the datadir * users and permissions are in the DB mysql * https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html signature.asc Description: OpenPGP digital signature
Re: password problem
Am 31.07.2015 um 16:23 schrieb Martin Mueller: Dear Mr Harald, I've learned some things from your responses and even more from shawn green's. You might learn a lot from him about patience and courtesy, which make life on a technical forum a lot easier. You clearly know a lot about technical stuff, but you're short on patience, and it would help you a lot to practice a little courtesy and refrain from vulgar language. well, i am developer and sysadmin, not a politican my first response pointed again to the docs and quotet that: Resetting the Root Password: Generic Instructions Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option https://www.google.at/search?q=skip-grant-tables would have flooded you with informations P.S.: on the right side of the docs page is a Section Navigation with a link https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-generic On 7/31/15 9:12 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 31.07.2015 um 15:40 schrieb shawn l.green: 1. Log on to your system as the Unix user that the MySQL server runs as (for example, mysql). Everything that executes on a Linux/Unix/Mac machine executes in the context of some kind of user account (the system login). By default, mysqld (the database server daemon) is installed to run under the host machine user account 'mysql'. It can be changed if you want to change it but that is the default. That is why 'mysql' was listed in the for example section of that instruction but this part of the docs is completly bullshit a) on no sane system the user mysql has a password, hence no login possible and typically it has also no shell configured b) for what reason mysql -u root and you are done with skip-grant-tables (and skip-grant-tables is the only relevant point) why in the world should i need to logon as the user mysqld runs for connect to mysqld? but anyways, mysql -u mysql would have worked also as well as mysql -u bullshit because skip-grant-tables does what it says, you can do anything you like to do signature.asc Description: OpenPGP digital signature
Re: password problem
Am 31.07.2015 um 14:45 schrieb Martin Mueller: Data directory path mention in cnf is of old mysql. Make a fresh data directory, configure it in configuration file and execute mysqlinstall_db, I don't understand the sentence about the data directory path mention. The my.cnf file is at /etc/my.cnf . It doesn't have any data directory path mention, but neither does the my.cnf file on a laptop, which works. So there seems to be nothing wrong with the location or content of the my.cnf file. your current problem is that you have no clue where your mysql-datadir is *because* it's some random default, from the moment on you specify it a) you know it - good for a million reasons b) it is empty and you can start from scratch or you seek the current one and make the folder empty and start with mysql_install_db initializes the MySQL data directory and creates the system tables that it contains, if they do not exist. On Fri, Jul 31, 2015 at 5:11 AM, Martin Mueller martinmuel...@northwestern.edumailto:martinmuel...@northwestern.edu wrote: I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot run the mysql command because it challenges me for a password. But I did not set any password, either for the root, for mysql, or for myself as a user. So the installation has somehow installed passwords about which I know nothing or there is some error in the installation process. There is a lot on the Web about resetting a forgotten password. But the assumption is always that you can get at the program via some other password. But in this case every door is shut. Does anybody recognize this problem? I've uninstalled and re-installed the program, but the results are always the same. Martin Mueller Professor emeritus of English and Classics Northwestern University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Thanks and Regards: Nikhil Anand +91 9650024197 -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development m: +43 (676) 40 221 40, p: +43 (1) 595 3999 33 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: password problem
Am 31.07.2015 um 15:40 schrieb shawn l.green: 1. Log on to your system as the Unix user that the MySQL server runs as (for example, mysql). Everything that executes on a Linux/Unix/Mac machine executes in the context of some kind of user account (the system login). By default, mysqld (the database server daemon) is installed to run under the host machine user account 'mysql'. It can be changed if you want to change it but that is the default. That is why 'mysql' was listed in the for example section of that instruction but this part of the docs is completly bullshit a) on no sane system the user mysql has a password, hence no login possible and typically it has also no shell configured b) for what reason mysql -u root and you are done with skip-grant-tables (and skip-grant-tables is the only relevant point) why in the world should i need to logon as the user mysqld runs for connect to mysqld? but anyways, mysql -u mysql would have worked also as well as mysql -u bullshit because skip-grant-tables does what it says, you can do anything you like to do signature.asc Description: OpenPGP digital signature
Re: password problem
Sorry for the off-list reply. It was an oversight. That said, the instructions for resetting a forgotten root password have a section for Windows and a section for Unix. The Unix section begins as follows: 1. Log on to your system as the Unix user that the MySQL server runs as (for example, mysql). But if I do this with the command 'mysql -u mysql I get the answer Access denied for user 'mysql'@'localhost' (using password: NO) I can do this as super user or normal, and I can try passwords from earlier installations, but none of them work. So I am stopped dead in my tracks, am I not? As for the datadir, the command update db locate mysql works on the Mac and gives me info about a whole set of files in /usr/local/mysql-5.1.73-osx10.6-x86_64. That's where I thought it was, and I deleted a previous installation because I had moved the data I needed to another machine. I'm not a very experienced programmer and have trouble wrestling with the command line. But I think I did my due diligence and didn't find any open doors. Martin Mueller Professor emeritus of English and Classics Northwestern University On 7/31/15 3:36 AM, Reindl Harald h.rei...@thelounge.net wrote: first: don't reply off-list, a answer on a mailing-list is no invitation for private support! Am 31.07.2015 um 02:34 schrieb Martin Mueller: I read that section but was stopped in my tracks by Log on to your system as the Unix user that the MySQL server runs as (for example, mysql) Because I have no password for ANY thing. read the f**ng https://dev.mysql.com/doc/refman5.0/en/resetting-permissions.html https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html - unbelievebale that users these days need anything ready chewed and are too lazy to click on a link and read more than 5 lines Resetting the Root Password: Generic Instructions Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option I used the uninstall routine recommended by Rob Allen, in which you remove the directories /usr/local/mysql as well as /usr/local/mysql* and a lot of other library and etc files. So there is no trace of the old system on my machine. How come a routine installation of mysql then locks up the application. the datadir is *not* removed by any sane installer, dunno where it lives on Apple machines since i banned them 5 years ago for good reasons on a non-OSX i would just type updatedb; locate mysql als root On 7/30/15, 19:22, Reindl Harald h.rei...@thelounge.net wrote: Am 31.07.2015 um 01:41 schrieb Martin Mueller: I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot run the mysql command because it challenges me for a password. But I did not set any password, either for the root, for mysql, or for myself as a user. So the installation has somehow installed passwords about which I know nothing or there is some error in the installation process. There is a lot on the Web about resetting a forgotten password. But the assumption is always that you can get at the program via some other password. But in this case every door is shut. Does anybody recognize this problem? I've uninstalled and re-installed the program, but the results are always the same * install and uninstall *never* removes the datadir * users and permissions are in the DB mysql * https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: password problem
Dear Mr Harald, I've learned some things from your responses and even more from shawn green's. You might learn a lot from him about patience and courtesy, which make life on a technical forum a lot easier. You clearly know a lot about technical stuff, but you're short on patience, and it would help you a lot to practice a little courtesy and refrain from vulgar language. Martin Mueller Professor emeritus of English and Classics Northwestern University On 7/31/15 9:12 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 31.07.2015 um 15:40 schrieb shawn l.green: 1. Log on to your system as the Unix user that the MySQL server runs as (for example, mysql). Everything that executes on a Linux/Unix/Mac machine executes in the context of some kind of user account (the system login). By default, mysqld (the database server daemon) is installed to run under the host machine user account 'mysql'. It can be changed if you want to change it but that is the default. That is why 'mysql' was listed in the for example section of that instruction but this part of the docs is completly bullshit a) on no sane system the user mysql has a password, hence no login possible and typically it has also no shell configured b) for what reason mysql -u root and you are done with skip-grant-tables (and skip-grant-tables is the only relevant point) why in the world should i need to logon as the user mysqld runs for connect to mysqld? but anyways, mysql -u mysql would have worked also as well as mysql -u bullshit because skip-grant-tables does what it says, you can do anything you like to do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: password problem
On 7/31/2015 8:40 AM, Martin Mueller wrote: Sorry for the off-list reply. It was an oversight. That said, the instructions for resetting a forgotten root password have a section for Windows and a section for Unix. The Unix section begins as follows: 1. Log on to your system as the Unix user that the MySQL server runs as (for example, mysql). Everything that executes on a Linux/Unix/Mac machine executes in the context of some kind of user account (the system login). By default, mysqld (the database server daemon) is installed to run under the host machine user account 'mysql'. It can be changed if you want to change it but that is the default. That is why 'mysql' was listed in the for example section of that instruction. But if I do this with the command 'mysql -u mysql I get the answer No. That is how you log into mysqld to open a MySQL client session. The instruction was to login to your operating system as the user that mysqld operates as. These are fundamentally different accounts at two very different levels. Access denied for user 'mysql'@'localhost' (using password: NO) I can do this as super user or normal, and I can try passwords from earlier installations, but none of them work. So I am stopped dead in my tracks, am I not? That is because you didn't add this line to the [mysqld] section of your configuration file before you started mysqld. skip-grant-tables If you had, you would not have needed to use any passwords at all. This command (on the system prompt) would be all you need to connect to your now completely-unlocked database server (see the third section of generic instructions that work on any platform). mysql As for the datadir, the command update db locate mysql works on the Mac and gives me info about a whole set of files in /usr/local/mysql-5.1.73-osx10.6-x86_64. That's where I thought it was, and I deleted a previous installation because I had moved the data I needed to another machine. I'm not a very experienced programmer and have trouble wrestling with the command line. But I think I did my due diligence and didn't find any open doors. The door is there, you just just need to be able to see it as a door. Just a little more experience working on the command line will help. ... remainder snipped ... -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: password problem
first: don't reply off-list, a answer on a mailing-list is no invitation for private support! Am 31.07.2015 um 02:34 schrieb Martin Mueller: I read that section but was stopped in my tracks by Log on to your system as the Unix user that the MySQL server runs as (for example, mysql) Because I have no password for ANY thing. read the f**ng https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html - unbelievebale that users these days need anything ready chewed and are too lazy to click on a link and read more than 5 lines Resetting the Root Password: Generic Instructions Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option I used the uninstall routine recommended by Rob Allen, in which you remove the directories /usr/local/mysql as well as /usr/local/mysql* and a lot of other library and etc files. So there is no trace of the old system on my machine. How come a routine installation of mysql then locks up the application. the datadir is *not* removed by any sane installer, dunno where it lives on Apple machines since i banned them 5 years ago for good reasons on a non-OSX i would just type updatedb; locate mysql als root On 7/30/15, 19:22, Reindl Harald h.rei...@thelounge.net wrote: Am 31.07.2015 um 01:41 schrieb Martin Mueller: I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot run the mysql command because it challenges me for a password. But I did not set any password, either for the root, for mysql, or for myself as a user. So the installation has somehow installed passwords about which I know nothing or there is some error in the installation process. There is a lot on the Web about resetting a forgotten password. But the assumption is always that you can get at the program via some other password. But in this case every door is shut. Does anybody recognize this problem? I've uninstalled and re-installed the program, but the results are always the same * install and uninstall *never* removes the datadir * users and permissions are in the DB mysql * https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html signature.asc Description: OpenPGP digital signature
Re: password problem
Data directory path mention in cnf is of old mysql. Make a fresh data directory, configure it in configuration file and execute mysqlinstall_db, On Fri, Jul 31, 2015 at 5:11 AM, Martin Mueller martinmuel...@northwestern.edu wrote: I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot run the mysql command because it challenges me for a password. But I did not set any password, either for the root, for mysql, or for myself as a user. So the installation has somehow installed passwords about which I know nothing or there is some error in the installation process. There is a lot on the Web about resetting a forgotten password. But the assumption is always that you can get at the program via some other password. But in this case every door is shut. Does anybody recognize this problem? I've uninstalled and re-installed the program, but the results are always the same. Martin Mueller Professor emeritus of English and Classics Northwestern University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- *Thanks and Regards:* *Nikhil Anand* *+91 9650024197*
Re: password problem
Am 31.07.2015 um 01:41 schrieb Martin Mueller: I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot run the mysql command because it challenges me for a password. But I did not set any password, either for the root, for mysql, or for myself as a user. So the installation has somehow installed passwords about which I know nothing or there is some error in the installation process. There is a lot on the Web about resetting a forgotten password. But the assumption is always that you can get at the program via some other password. But in this case every door is shut. Does anybody recognize this problem? I've uninstalled and re-installed the program, but the results are always the same * install and uninstall *never* removes the datadir * users and permissions are in the DB mysql * https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html signature.asc Description: OpenPGP digital signature
RE: installation problem of MySQL on OS Lion
Share error log file -Original Message- From: Martin Mueller martinmuel...@northwestern.edu Sent: 29-07-2015 21:05 To: mysql@lists.mysql.com mysql@lists.mysql.com Subject: installation problem of MySQL on OS Lion I installed MySQL on a Mac Pro running OS Lion. The installation was successful, but starting the mysql server generated the following error message: Martin-Muellers-Mac-Pro:~ martin$ sudo /usr/local/mysql/support-files/mysql.server start Starting MySQL ... ERROR! Manager of pid-file quit without updating file. I'll be grateful for any help Martin Mueller Professor emeritus of English and Classics Northwestern University
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 107 | | |
+--+--+--+--+
1 row in set (0.00 sec)
Slave
* 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 107
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29727610
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
1 transaction entered:
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 1837 | | |
+--+--+--+--+
1 row in set (0.00 sec)
SLave
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 1837
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29729340
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
Re: Replication problem
On 8/29/2014 5:11 PM, wagnerbianchi.com wrote: Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? It was not, I reconfigured and restarted mysql and... 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? Not - see prior post 3-) Why are you reconfiguring all the replication just because the link went down? AFAIK, I am no reconfiguring, after the link comes back up, the slave does not start replicating - sometimes. Happens quite infrequently. I intend to be just restoring the database and restarting replication Cheers, Thanks, I need the cheers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
after a more complex transaction;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 5952 | | |
+--+--+--+--+
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 5952
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29733455
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
There's a duplicate key on the audit table, 18699. Delete it and restart
slave (start slave). Check slave status again, might be more rows in there
duplicated.
You might want to compare the row to master to ensure it's a duplicate
before deleting from slave.
On Aug 30, 2014 7:52 AM, william drescher will...@techservsys.com
wrote:
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on
Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
after a more complex transaction;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 5952 | | |
+--+--+--+--+
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 5952
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key
'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert
into audit (audit_type, subtype, user_Id, ptNum, data) values ('login',
'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29733455
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key
'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert
into audit (audit_type, subtype, user_Id, ptNum, data) values ('login',
'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously ) global var, below
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 8919 | | |
+--+--+--+--+
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 8919
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29736422
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699'
for key 'PRIMARY'' on query. Default database:
'Information_server'. Query: 'insert into audit (audit_type,
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD',
0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
mysql show global variables like 'log-bin%';
Empty set (0.01 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Based on the SHOW SLAVE STATUS output you've sent us, I'd suggest that you
check what the application is doing, understand *why* the application is
violating the PK of the table information_server.audit, repair the possible
application problem and the, reconfigure the replication.
It seems that there's something not really good happening on the
application side since the error 1062 is being threw out by a INSERT sent
to the database ... by the application.
*PS.: looking at the purpose of information_server.audit, I wonder if this
is not a *design* problem!*
1-) Fix the application if you can, investigating the problem the
replication is showing up on SHOW SLAVE STATUS - remember that the
column Last_SQL_Error
is one of SHOW SLAVE STATUS best friends in this context;
2-) After to fix the problem, if design or application, recreate the slave
and then, start replication again;
It's possible to use another things to make the replication to bypass this
kind of problem, but, it's not that cool to have it configured. due to
that, I'd like to omit it at this point.
Let's keep in touch, happy mysql'ing!!
--
*Wagner Bianchi*
2014-08-30 9:54 GMT-03:00 Johnny Withers joh...@pixelated.net:
There's a duplicate key on the audit table, 18699. Delete it and restart
slave (start slave). Check slave status again, might be more rows in there
duplicated.
You might want to compare the row to master to ensure it's a duplicate
before deleting from slave.
On Aug 30, 2014 7:52 AM, william drescher will...@techservsys.com
wrote:
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on
Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
after a more complex transaction;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 5952 | | |
+--+--+--+--+
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 5952
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key
'PRIMARY'' on query. Default database: 'Information_server'. Query:
'insert
into audit (audit_type, subtype, user_Id, ptNum, data) values ('login',
'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29733455
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key
'PRIMARY'' on query. Default database: 'Information_server'. Query:
'insert
into audit (audit_type, subtype, user_Id, ptNum, data) values ('login',
'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 30/08/14 09:39, william drescher wrote:
On 8/29/2014 7:40 PM, Suresh Kuna wrote:
You can paste the show slave status\G here for us to review and on
Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 107 | | |
+--+--+--+--+
1 row in set (0.00 sec)
Slave
* 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 107
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29727610
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
1 transaction entered:
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 1837 | | |
+--+--+--+--+
1 row in set (0.00 sec)
SLave
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 1837
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29729340
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)
Well , it seems that you have a 'Duplicate entry ' issue on slave, so
the Slave_SQL_Running threads is locked :) , there's something wrong
here with the initial slave status, maybe you have to rebuild the slave
from scratch(backup or a dump from master), and be sure that you don't
change anything on slave( phpmyadmin? )
Bye
Julian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
On 8/29/2014 7:40 PM, Suresh Kuna wrote: You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) mysql show global variables like 'log_bin%'; +-+---+ | Variable_name | Value | +-+---+ | log_bin | ON| | log_bin_trust_function_creators | OFF | +-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
On 30/08/14 12:56, william drescher wrote: Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill Just after you resync the slave, last error should be empty. Bye Julian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem -solved
On 8/30/2014 12:53 PM, Jose Julian Buda wrote: On 30/08/14 12:56, william drescher wrote: Thanks for pointing out the importance of the last error I resynced the slave to the master, reset the master position, and restarted the slave. Now all works fine and I am much better equipped next time to debug the loss of the link. When is the Last Error data deleted from the show slave data ? --bill Just after you resync the slave, last error should be empty. Bye Julian Thanks Julian, it is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? 3-) Why are you reconfiguring all the replication just because the link went down? Cheers, -- *WB* 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com: Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication problem
You can paste the show slave status\G here for us to review and on Master, show global variables like 'log-bin%'; show master status ( 3 to 4 times continuously ) On Fri, Aug 29, 2014 at 5:11 PM, wagnerbianchi.com m...@wagnerbianchi.com wrote: Hello guys, some points to check here: 1-) Is the master server configured with sync_binlog=1 ? 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading events from master, is the Exec_Master_Log_Pos incrementing or not? 3-) Why are you reconfiguring all the replication just because the link went down? Cheers, -- *WB* 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com: Whilst there are a few possibilities, check on the master that your binary logs are being written to. Another possible reason could be filtering. On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote: Replication novice I have a master server at the office and a replication server at home. This setup has been working for a couple of years. Occasionally the replication server gets out of sync (usually following a internet problem and the vpn going down.) I just stop the slave, make sure there is nothing going to the master (when the office is closed), copy the database, transfer the file, load the backup, and start the slave and all is well. This time there was not a communications problem of which I am aware. The slave status said the slave_IO_state was Waiting for master to send event but it was not replicating. I did the usual now it is not updating the replication database (transactions made on the master do not show on the slave - using phpMyAdmin on both servers) BUT show master status shows the correct log file and the position is incrementing AND show slave status shows the same master log file and the same position as the master. So, looking at the status info it seems to be running fine, but the transactions do not appear to appear on the slave. I seek suggestions how to 1) find out what goes wrong when the vpn goes down, and 2) (much more important now) how to find out whether or not the slave is actually replicating or not. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- -- Thanks Suresh Kuna MySQL Database Consutant MongoDB DBA Hadoop Admin
Re: access problem for a particular table
Hi Run mysql_upgrade command at OS shell DK Sent from Phone On 28-May-2014, at 1:40 pm, Lentes, Bernd bernd.len...@helmholtz-muenchen.de wrote: Hi, we just migrated from 5.0 to 5.5. Nearly everything went well. But we can't access one particular table. Automysqlback 3.0 says: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'backup'@'localhost' for table 'cond_instances' when using LOCK TABLES. root has these rights: ++ | Grants for root@localhost | ++ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*948BD740C15428999D549B9632F3C432415E93A4' WITH GRANT OPTION | | GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'root'@'localhost' WITH GRANT OPTION| | GRANT SELECT ON `vectordb%`.* TO 'root'@'localhost' | | GRANT USAGE ON `performance_schema`.`cond_instances` TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | ++ backup has these rights: ++ | Grants for backup@localhost | ++ | GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup'@'localhost' IDENTIFIED BY PASSWORD '*1827DC630AAEB1E997DB2B212CC94EFD9C431555' | | GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'backup'@'localhost' | | GRANT SELECT ON `vectordb%`.* TO 'backup'@'localhost' | ++ If I try to specify (as root) exactly the select and lock tables right to user backup, I get the following error: mysql grant select, lock tables on performance_schema.cond_instances to 'backup'@'localhost'; ERROR 1142 (42000): SELECT,GRANT,LOC command denied to user 'root'@'localhost' for table 'cond_instances' What I understood is that the usage right for root on performance_schema.cond_instances means no rights. Trying to revoke seems to work: mysql revoke usage on performance_schema.cond_instances from 'root'@'localhost'; Query OK, 0 rows affected (0.00 sec) But the usage right remains, it does not disappear. How can I grant these rights to user backup ? Thanks for any hint. Bernd -- Bernd Lentes Systemadministration Institut für Entwicklungsgenetik Gebäude 35.34 - Raum 208 HelmholtzZentrum münchen bernd.len...@helmholtz-muenchen.de phone: +49 89 3187 1241 fax: +49 89 3187 2294 http://www.helmholtz-muenchen.de/idg Die Freiheit wird nicht durch weniger Freiheit verteidigt Helmholtz Zentrum München Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen Registergericht: Amtsgericht München HRB 6466 USt-IdNr: DE 129521671 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: access problem for a particular table
- Original Message - From: Bernd Lentes bernd.len...@helmholtz-muenchen.de To: mysql@lists.mysql.com Sent: Wednesday, 28 May, 2014 10:10:33 AM Subject: access problem for a particular table we just migrated from 5.0 to 5.5. Nearly everything went well. But we can't access one particular table. Automysqlback 3.0 says: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'backup'@'localhost' for table 'cond_instances' when using LOCK TABLES. You don't need to backup performance_schema or information_schema. Fix the backup tool. root has these rights: [...] Why does the root user have such specific rights? It suggests that you use it for application purposes. Typically you'd set up root or another user as admin with all privileges on *.*; and NEVER use that for anything but administrative purposes. backup has these rights: [...] As said above, no need to back up performance_schema or information_schema - they're dynamically generated by the MySQL server. You've already granted the necessary rights (well, there could be more, but you've probably got what you need) on *.*, so no more need for all the specifics. Get rid of them, they only confuse people looking at them. What I understood is that the usage right for root on performance_schema.cond_instances means no rights. [...] But the usage right remains, it does not disappear. How can I grant these rights to user backup ? Well, yes and no. It does mean a user has no rights, but it is really something implicit that comes with the very existence of a user. Thus, it's only visible when a user has no other rights; and you can't revoke it short of dropping the user entirely. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restore problem
Hello, Thanks for your quick reply. I use WEBMIN and within the Webmin modules you can make simply a SQL backup, which is in fact a flat file consisting of MYSQL commands. Explains this enough? Thanks, BR Op 8 sep. 2013, om 21:02 heeft Luis H. Forchesatto luisforchesa...@gmail.com het volgende geschreven: How did you backed up the MySQL? Att. Luis H. Forchesatto Em 08/09/2013 16:00, Trianon33 triano...@gmail.com escreveu: Hello all, As this is my first post to this list (though reading for some time yet), I maybe not complete in asking my question. Apologies for that. On the other hand, this is not a prio 1 problem, so if it takes some mailing, that's ok. I have a website on my private webserver, which consist of some kind of a LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu and the same kind of LAMP-server. I'm trying to restore the SQL-backup from the old machine to restore with the raspberry. While finding the file and starting the restore is no problem, I get the following message and a full stop: SELECT MAX( version ) FROM `phpmyadmin`.`pma_tracking` WHERE `db_name` = 'bbz' AND `table_name` = 'wp_links VALUES' AND FIND_IN_SET( 'INSERT', tracking ) 0 MySQL retourneerde: b_help.png #1100 - Table 'pma_tracking' was not locked with LOCK TABLES Since I do a full restore I'm processing the younameit.SQL file I'm bad in interpreting the error messag, don't understand what it exactly mens and what I can do to cure this problem. Anyone a suggestion? Thanks in advance, BR
Re: restore problem
I would suggest that you test your backup file on another full-featured server to determine that it is a valid first. I have done a little work with the raspberry pi and I doubt that the mysql distribution for that platform comes with all the features your server-class ubuntu does, so it is quite possible that you backup file is trying to take advantage of some facilities available on the source host that are not available on your lightweight target. On Sun, Sep 8, 2013 at 3:05 PM, Trianon33 triano...@gmail.com wrote: Hello, Thanks for your quick reply. I use WEBMIN and within the Webmin modules you can make simply a SQL backup, which is in fact a flat file consisting of MYSQL commands. Explains this enough? Thanks, BR Op 8 sep. 2013, om 21:02 heeft Luis H. Forchesatto luisforchesa...@gmail.com het volgende geschreven: How did you backed up the MySQL? Att. Luis H. Forchesatto Em 08/09/2013 16:00, Trianon33 triano...@gmail.com escreveu: Hello all, As this is my first post to this list (though reading for some time yet), I maybe not complete in asking my question. Apologies for that. On the other hand, this is not a prio 1 problem, so if it takes some mailing, that's ok. I have a website on my private webserver, which consist of some kind of a LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu and the same kind of LAMP-server. I'm trying to restore the SQL-backup from the old machine to restore with the raspberry. While finding the file and starting the restore is no problem, I get the following message and a full stop: SELECT MAX( version ) FROM `phpmyadmin`.`pma_tracking` WHERE `db_name` = 'bbz' AND `table_name` = 'wp_links VALUES' AND FIND_IN_SET( 'INSERT', tracking ) 0 MySQL retourneerde: b_help.png #1100 - Table 'pma_tracking' was not locked with LOCK TABLES Since I do a full restore I'm processing the younameit.SQL file I'm bad in interpreting the error messag, don't understand what it exactly mens and what I can do to cure this problem. Anyone a suggestion? Thanks in advance, BR -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: restore problem
H, this remark makes sense, thanks for reminding that. Since I have a backup in SQl format (it is readable statements), I was thinking on splitting the backup file in several separate restores and execute them manually in consecutive order. Hmmm, the idea attracts me. Wait for some results. BR. Op 8 sep. 2013, om 23:16 heeft Michael Dykman mdyk...@gmail.com het volgende geschreven: I would suggest that you test your backup file on another full-featured server to determine that it is a valid first. I have done a little work with the raspberry pi and I doubt that the mysql distribution for that platform comes with all the features your server-class ubuntu does, so it is quite possible that you backup file is trying to take advantage of some facilities available on the source host that are not available on your lightweight target. On Sun, Sep 8, 2013 at 3:05 PM, Trianon33 triano...@gmail.com wrote: Hello, Thanks for your quick reply. I use WEBMIN and within the Webmin modules you can make simply a SQL backup, which is in fact a flat file consisting of MYSQL commands. Explains this enough? Thanks, BR Op 8 sep. 2013, om 21:02 heeft Luis H. Forchesatto luisforchesa...@gmail.com het volgende geschreven: How did you backed up the MySQL? Att. Luis H. Forchesatto Em 08/09/2013 16:00, Trianon33 triano...@gmail.com escreveu: Hello all, As this is my first post to this list (though reading for some time yet), I maybe not complete in asking my question. Apologies for that. On the other hand, this is not a prio 1 problem, so if it takes some mailing, that's ok. I have a website on my private webserver, which consist of some kind of a LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu and the same kind of LAMP-server. I'm trying to restore the SQL-backup from the old machine to restore with the raspberry. While finding the file and starting the restore is no problem, I get the following message and a full stop: SELECT MAX( version ) FROM `phpmyadmin`.`pma_tracking` WHERE `db_name` = 'bbz' AND `table_name` = 'wp_links VALUES' AND FIND_IN_SET( 'INSERT', tracking ) 0 MySQL retourneerde: b_help.png #1100 - Table 'pma_tracking' was not locked with LOCK TABLES Since I do a full restore I'm processing the younameit.SQL file I'm bad in interpreting the error messag, don't understand what it exactly mens and what I can do to cure this problem. Anyone a suggestion? Thanks in advance, BR -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: restore problem
I would suggest making a physical backup. Shutdown MySQL on source, copy datadir and start on the the destination server. (observe configuration differences between the two machines) *Benefits;* consistent backup of non-transactional files. *Drawbacks;* downtime required. On Sun, Sep 8, 2013 at 10:16 PM, Michael Dykman mdyk...@gmail.com wrote: I would suggest that you test your backup file on another full-featured server to determine that it is a valid first. I have done a little work with the raspberry pi and I doubt that the mysql distribution for that platform comes with all the features your server-class ubuntu does, so it is quite possible that you backup file is trying to take advantage of some facilities available on the source host that are not available on your lightweight target. On Sun, Sep 8, 2013 at 3:05 PM, Trianon33 triano...@gmail.com wrote: Hello, Thanks for your quick reply. I use WEBMIN and within the Webmin modules you can make simply a SQL backup, which is in fact a flat file consisting of MYSQL commands. Explains this enough? Thanks, BR Op 8 sep. 2013, om 21:02 heeft Luis H. Forchesatto luisforchesa...@gmail.com het volgende geschreven: How did you backed up the MySQL? Att. Luis H. Forchesatto Em 08/09/2013 16:00, Trianon33 triano...@gmail.com escreveu: Hello all, As this is my first post to this list (though reading for some time yet), I maybe not complete in asking my question. Apologies for that. On the other hand, this is not a prio 1 problem, so if it takes some mailing, that's ok. I have a website on my private webserver, which consist of some kind of a LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu and the same kind of LAMP-server. I'm trying to restore the SQL-backup from the old machine to restore with the raspberry. While finding the file and starting the restore is no problem, I get the following message and a full stop: SELECT MAX( version ) FROM `phpmyadmin`.`pma_tracking` WHERE `db_name` = 'bbz' AND `table_name` = 'wp_links VALUES' AND FIND_IN_SET( 'INSERT', tracking ) 0 MySQL retourneerde: b_help.png #1100 - Table 'pma_tracking' was not locked with LOCK TABLES Since I do a full restore I'm processing the younameit.SQL file I'm bad in interpreting the error messag, don't understand what it exactly mens and what I can do to cure this problem. Anyone a suggestion? Thanks in advance, BR -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: InnoDB problem.
What's the MySQL error log have to say? - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 3:39:55 PM Subject: Re: InnoDB problem. Yep, I do backup of /home/mysql/ib* files too :D What it occurs is that even with ibdata1, ib_logfile0 and ib_logfile1 in it's due place, MySQL (provided by xampp) shows me the following messages, when trying to open InnoDB tables: SHOW FULL FIELDS FROM `my_innodb_table` ; #1286 - Unknown table engine 'InnoDB' skin-innodb is commented but either way InnoDB engine are not shown when I execute show engines command. 2013/7/22 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: InnoDB problem.
Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures.
RE: InnoDB problem.
Did you change innodb_log_file_size? -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, July 23, 2013 9:57 AM To: Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: InnoDB problem.
2013/7/23 Rick James rja...@yahoo-inc.com Did you change innodb_log_file_size? innodb_log_file_size error always appears in the logs...he only posted a few lines of his log...but I guess (or I want to believe) he's gone through the whole log before starting the thread :-) Manuel -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, July 23, 2013 9:57 AM To: Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures. -- Manuel Aróstegui Systems Team tuenti.com
RE: InnoDB problem.
Either change it back, or delete the log files so that they will be built in the new size. (Backup the entire tree, just in case.) From: Manuel Arostegui [mailto:man...@tuenti.com] Sent: Tuesday, July 23, 2013 1:05 PM To: Rick James Cc: Johan De Meersman; Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. 2013/7/23 Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com Did you change innodb_log_file_size? innodb_log_file_size error always appears in the logs...he only posted a few lines of his log...but I guess (or I want to believe) he's gone through the whole log before starting the thread :-) Manuel -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.bemailto:vegiv...@tuxera.be] Sent: Tuesday, July 23, 2013 9:57 AM To: Luis H. Forchesatto; mysql list Subject: Re: InnoDB problem. Eek. No immediate clue here, but maybe someone else does - so please keep the list in CC at all times :-p Random question: were the files backed up from a different version? I'd expect some kind of warning about that in the logs, really, but you never know. - Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.commailto:luisforchesa...@gmail.com To: Johan De Meersman vegiv...@tuxera.bemailto:vegiv...@tuxera.be Sent: Tuesday, 23 July, 2013 6:34:47 PM Subject: Re: InnoDB problem. The error log: 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error. 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.1.41' socket: '' port: 3306 Source distribution 2013/7/23 Johan De Meersman vegiv...@tuxera.bemailto:vegiv...@tuxera.be -- Att. Luis H. Forchesatto http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67 -- Unhappiness is discouraged and will be corrected with kitten pictures. -- Manuel Aróstegui Systems Team tuenti.comhttp://tuenti.com
Re: InnoDB problem.
- Original Message - From: Luis H. Forchesatto luisforchesa...@gmail.com Subject: InnoDB problem. Permission and owner of the table files (.frm files) are ok, since it recognizes MyISAM tables (they have the same permission). Oops. You should always read the fine manual. You took file-level backups, yes? Did they include the ibdata1 and similar files? Those contain innodb's dictionary - and in default installs also all the actual tables. The database/* files only contain the .frm, for innodb. If I'm right, you haven't got a backup at all. I'm crossing my fingers that I'm wrong... -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: PreparedStatement problem
- Original Message - From: ZhangFangXue zhangfang...@sogou-inc.com Hi, when I use PreparedStatement in c++ connector, I find some unexcepted error, Well, first of all, you don't actually say what the error is that you're seeing. This tends to be on the rather helpful side when trying to diagnose it. prep_stmt = con - prepareStatement (INSERT INTO City (CityName) VALUES (?)); prep_stmt - setInt (1, 23); //this statement didn't act normally!!! However, if you're inserting into a text field, it may well be that the parser will balk at you trying to bind an integer to that, no? If you want to insert a string, it's fairly common practice to provide an actual string to insert. Quite the novel idea, I know. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication problem
On 12/3/2012 7:24 AM, Trimurthy wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. ... snipped ... This one should have been easy for the list to advise you about. Let me step in to keep things on track. For each instance of MySQL that creates binary logs there will be an 'index' file that keeps track of which files this instance is managing. For example, if the binary logs are called mysql-bin.00, then the index file will be called mysql-bin.index What has happened is that the contents of the index file no longer matches the inventory of actual binary logs present on disk. They have either been moved, erased, or had their permissions changed so that the system user that the mysqld daemon executes as can no longer see them. The solution is to manually update the .index file with the current listing of binary log files. The longer term solution is to stop manually removing binary log files and allow the system to perform that for you with a PURGE BINARY LOGS command. When you have MySQL do the purging, the .index file will be automatically updated. For more details about the binary log, please read: http://dev.mysql.com/doc/refman/5.5/en/binary-log.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication problem
HI share detail Slave_IO_Running: Yes/No Slave_SQL_Running: Yes/No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: On Tue, Dec 4, 2012 at 5:59 AM, Rick James rja...@yahoo-inc.com wrote: SHOW SLAVE STATUS\G SHOW MASTER STATUS; What directory are the binlogs in? -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Monday, December 03, 2012 8:53 AM To: trimur...@tulassi.com Cc: mysql@lists.mysql.com Subject: Re: replication problem Trimurthy, you will have to describe the method you are using to setup replication. The error message seems plain but an observer could not reasonably guess what caused it without more information. - michael dykman On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication problem
Trimurthy, you will have to describe the method you are using to setup replication. The error message seems plain but an observer could not reasonably guess what caused it without more information. - michael dykman On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: replication problem
SHOW SLAVE STATUS\G SHOW MASTER STATUS; What directory are the binlogs in? -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Monday, December 03, 2012 8:53 AM To: trimur...@tulassi.com Cc: mysql@lists.mysql.com Subject: Re: replication problem Trimurthy, you will have to describe the method you are using to setup replication. The error message seems plain but an observer could not reasonably guess what caused it without more information. - michael dykman On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote: hi list, i am trying to set up replication but i couldn't complete because of the following error Could not find first log file name in binary log index file can any one please help me. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: facing problem in MYSQL
Hi Ajay, there is no attachment.. On Sat, Jul 28, 2012 at 1:17 PM, Ajay Bansal, System Officer so...@indianjudiciary.gov.in wrote: Dear Sir/Mam, we are facing the problem in our server, kindly help us to solve our problem. mysqlbug script is attached with this mail. hope for your positive response ASAP. -- Regards* *Er. Ajay Bansal System Officer District Sessions Court, Amritsar +919988460582 | so...@indianjudiciary.gov.in -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
RE: query problem with null
Have you tried to set city = null (i.e. without the quotes)? David. -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Friday, March 09, 2012 4:24 PM To: mysql@lists.mysql.com Subject: query problem with null When I do the following query: SELECT * FROM geo_trivia WHERE city IS NULL; certain columns that DO have 'NULL' value for city and not a '' (blank) value do not show up. I have even gone to the extent of reseting these records value as ='NULL' with UPDATE and they are still are not selected when I run the above query. Can anyone help? The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: query problem with null
Ahhh... Thank you, that was exactly what the problem was. I will fix the code that is setting the value of these new records to 'NULL'. Thank you. 2012/3/9 David Lerer dle...@us.univision.com Have you tried to set city = null (i.e. without the quotes)? David. -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Friday, March 09, 2012 4:24 PM To: mysql@lists.mysql.com Subject: query problem with null When I do the following query: SELECT * FROM geo_trivia WHERE city IS NULL; certain columns that DO have 'NULL' value for city and not a '' (blank) value do not show up. I have even gone to the extent of reseting these records value as ='NULL' with UPDATE and they are still are not selected when I run the above query. Can anyone help? The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
Re: query problem with null
- Original Message -
From: David Lerer dle...@us.univision.com
Have you tried to set city = null (i.e. without the quotes)?
Spot on, I'd think.
NULL values are not a string with NULL in it - that's only what it looks like
in query results :-) An empty string ('') is to strings what 0 (zero) is for
integers: it says the value of this field is nothing. NULL, on the other
hand, means the value of this field is a total unknown, which is useful, for
example, in a field 'quantity': zero is still a valid, meaningful quantity;
whereas you would use NULL to indicate that you simply do not know the quantity.
It's a bit of a peculiar concept, but as David indicated, IS NULL will not
match fields set to the string NULL - as that is a string, not an unknown.
Another funny attribute of NULL is that NULL != NULL. There simply *is* nothing
to compare, so you cannot ever say it's equal.
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Transactional problem
Hola, At the first look, it looks like one of the following will work: 1.Use MySQL's MERGE statement (that is, INSERT with ON DUPLICATE KEY). What really happens, when the two transactions execute SELECT followed by an INSERT, there is no way to hold off SELECT. The natural instinct is to make both operations (selecting followed by inserting if it does not exist and locking if it does) the same thing. The solution would then work like this: - if the record does not exist, the first transaction to execute would insert it, hopefully another transaction will wait; - if the record does exist, the first transaction to execute would update it, thus locking the record, the other transaction would then hopefully wait; - the lock placed by the first transaction to execute will block the second transaction at the very beginning, and it will then proceed after the first one commits or rolls back. 2. Put the transaction in a MySQL procedure and call it. Inside a procedure, write code to handle unique constraint violation. The procedure would just start with attempting to insert a row without bothering to check if it exists. If it succeeds, it will lock the other transactions out. If it fails with unique constraint violation, do something creative like sleeping, hanging on an update, or whatever, followed by starting over. I did not mean to provide a solution but just to suggest some ideas about how this could be resolved. Assuming it is on InnoDB tables without autocommit, the locking behavior might be tricky, so any solution should be well tested. Peace Karen. On Jan 3, 2012, at 9:40 PM, KK Everest wrote: Hi all, Can anyone help me with the problem explained here please: http://www.reddit.com/r/mysql/comments/o256m/a_transactional_problem/ Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Transactional problem
INSERT IGNORE is 'the' SQL solution, as you know and mention. I dont know Rails but this is not MySQL / SQL / Database issue but more a specific framework one so the solution depends on it. You can also make some higher level programming workaround. Claudio On Jan 4, 2012 6:46 AM, KK Everest everest5...@ymail.com wrote: Hi all, Can anyone help me with the problem explained here please: http://www.reddit.com/r/mysql/comments/o256m/a_transactional_problem/ Thanks in advance.
Re: index problem
The optimizer is right, you are wrong, as simple as that :-) value between [field1] and [field2] cannot use indices, as your primary reference is a constant, not a field. Rewrite that to start = 1988778880 and end = 1988778880 and the optimizer should pick up the index. Index hints are rarely ever needed. It's best to stay away from them unless you know exactly what's going on under the hood :-) - Original Message - From: xucheng xuch...@sankuai.com To: mysql@lists.mysql.com Sent: Thursday, 28 July, 2011 2:50:46 PM Subject: index problem Hi i found a strange problem . when i using index for 'select' , i got a slower result than without index . i have a tabe : create table geo_query ( `id` int(10) unsigned not null auto_increment , `start` bigint(20) unsigned not null , `end` bigint(20) unsigned not null, `desc` varchar(1000) not null, primary key (`id`) , key `range` (`start`,`end`) ) engine=myisam ; the whole table contains 43 rows . 1, the query ' select * from geo_query where 1988778880 between start and end ;' used 0.15 second ; and i used 'explain' and found that it didn't use index and scanned the whole table . 2, so i changed the query for ' select * from geo_query force index(`range`) where 1988778880 between start and end ;' . it used 0.36 second . i can't figure it out .why the query used index spend more time than not ? any comment appreciate : ) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: index problem
[Note to self, reply to mailinglist, not to author ;) ] i found a strange problem . when i using index for 'select' , i got a slower result than without index . i have a tabe : create table geo_query ( `id` int(10) unsigned not null auto_increment , `start` bigint(20) unsigned not null , `end` bigint(20) unsigned not null, `desc` varchar(1000) not null, primary key (`id`) , key `range` (`start`,`end`) ) engine=myisam ; the whole table contains 43 rows . 1, the query ' select * from geo_query where 1988778880 between start and end ;' used 0.15 second ; and i used 'explain' and found that it didn't use index and scanned the whole table . 2, so i changed the query for ' select * from geo_query force index(`range`) where 1988778880 between start and end ;' . it used 0.36 second . i can't figure it out .why the query used index spend more time than not ? any comment appreciate : ) The query optimizer examined your answer, and decided a full-table scan was faster then using an index. It estimated it would require less IO operations to read the table in sequence in this case then reading the index fetching the appropriate records from the table. Turned out if was right. This is often the case when large portions of a table (or index) could possibly matched by the first guess. Here, the first 'guess' is that everything below start = 1988778880 is a possible match (as it's first field of the index `range`). Every one of them has to be verified of having an `end` your number, and has to fetch the appropriate record if it does which is costly in harddisk IO. BTW: as this looks as a GeoIP query, based on IP, if the `start` `end` ranges cannot overlap, this is probably faster: SELECT * FROM geo_query WHERE 1988778880 start ORDER BY start DESC LIMIT 1. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: index problem
thanks . i dropped the primary key , and it still didn't use the index . when i dropped the index `range`, and add two indexes `start` and `end` . it picks up the index , but it still used more seconds than using no index with `start` and `end` indexed as one --`range`. 2011/7/28 Johan De Meersman vegiv...@tuxera.be: The optimizer is right, you are wrong, as simple as that :-) value between [field1] and [field2] cannot use indices, as your primary reference is a constant, not a field. Rewrite that to start = 1988778880 and end = 1988778880 and the optimizer should pick up the index. Index hints are rarely ever needed. It's best to stay away from them unless you know exactly what's going on under the hood :-) - Original Message - From: xucheng xuch...@sankuai.com To: mysql@lists.mysql.com Sent: Thursday, 28 July, 2011 2:50:46 PM Subject: index problem Hi i found a strange problem . when i using index for 'select' , i got a slower result than without index . i have a tabe : create table geo_query ( `id` int(10) unsigned not null auto_increment , `start` bigint(20) unsigned not null , `end` bigint(20) unsigned not null, `desc` varchar(1000) not null, primary key (`id`) , key `range` (`start`,`end`) ) engine=myisam ; the whole table contains 43 rows . 1, the query ' select * from geo_query where 1988778880 between start and end ;' used 0.15 second ; and i used 'explain' and found that it didn't use index and scanned the whole table . 2, so i changed the query for ' select * from geo_query force index(`range`) where 1988778880 between start and end ;' . it used 0.36 second . i can't figure it out .why the query used index spend more time than not ? any comment appreciate : ) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=helloworldje...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: odd problem with select as statement
I can't tell you 'why' it is occurring when the field name begins with 4E5, but you can solve your problem by enclosing all your field names in backticks ( ` ). IE: SELECT field AS `4E5664736F400E8B482EA7AA67853D13` On Mon, Dec 20, 2010 at 11:43 AM, Ramsey, Robert L robert-ram...@uiowa.eduwrote: I am having the hardest time getting a select as statement right. Here is the full query: select SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19, SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815, SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889, SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A, SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784, SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE, SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13, --offending line SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C from dsrssfeed If I remove that one line, the query works fine. If I do: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ; it works. But these fail: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ; select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ; It can't be field name length, since even 4E5 fails, the field name can start with a number since 4E succeeds. Any ideas? The goal is to see what arbitrary images have information associated with them. The table has two fields: image is a UID that is the primary key, and caption which is a varchar(255) that has information about the image. Images are added and deleted from the table as they are changed on a web page. The UID is generated by a third party program that I have to interface with and have no control over. An array of image UIDs is sent to the php script and the script needs to determine which UIDs are present in the table. Rather than make N number of individual queries as I iterate through the array, I iterate through the array and build the query on the fly to make one query. Then I iterate through the array again and check the value in the field. 1 means the UID has an entry, 0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of calls as I iterate through the array. But since there will probably never be more than 100 images in the table at any one time, it may not make any difference. But now I'm just curious as to why this is happening. Thanks, Bob -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: odd problem with select as statement
Here's my 5 second guess.. 4E5664736... is being interpreted as a number in scientific notation .. i.e. 4*10^5664736 and the parser doesn't like that as a field name. -Hank On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L robert-ram...@uiowa.eduwrote: I am having the hardest time getting a select as statement right. Here is the full query: select SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19, SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815, SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889, SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A, SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784, SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE, SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13, --offending line SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C from dsrssfeed If I remove that one line, the query works fine. If I do: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ; it works. But these fail: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ; select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ; It can't be field name length, since even 4E5 fails, the field name can start with a number since 4E succeeds. Any ideas? The goal is to see what arbitrary images have information associated with them. The table has two fields: image is a UID that is the primary key, and caption which is a varchar(255) that has information about the image. Images are added and deleted from the table as they are changed on a web page. The UID is generated by a third party program that I have to interface with and have no control over. An array of image UIDs is sent to the php script and the script needs to determine which UIDs are present in the table. Rather than make N number of individual queries as I iterate through the array, I iterate through the array and build the query on the fly to make one query. Then I iterate through the array again and check the value in the field. 1 means the UID has an entry, 0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of calls as I iterate through the array. But since there will probably never be more than 100 images in the table at any one time, it may not make any difference. But now I'm just curious as to why this is happening. Thanks, Bob
Re: odd problem with select as statement
i.e. just try this: mysql select 4E5664736F400E8B482EA7AA67853D13; ERROR 1367 (22007): Illegal double '4E5664736' value found during parsing -Hank On Mon, Dec 20, 2010 at 12:50 PM, Hank hes...@gmail.com wrote: Here's my 5 second guess.. 4E5664736... is being interpreted as a number in scientific notation .. i.e. 4*10^5664736 and the parser doesn't like that as a field name. -Hank On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L robert-ram...@uiowa.edu wrote: I am having the hardest time getting a select as statement right. Here is the full query: select SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19, SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815, SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889, SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A, SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784, SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE, SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13, --offending line SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C from dsrssfeed If I remove that one line, the query works fine. If I do: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ; it works. But these fail: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ; select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ; It can't be field name length, since even 4E5 fails, the field name can start with a number since 4E succeeds. Any ideas? The goal is to see what arbitrary images have information associated with them. The table has two fields: image is a UID that is the primary key, and caption which is a varchar(255) that has information about the image. Images are added and deleted from the table as they are changed on a web page. The UID is generated by a third party program that I have to interface with and have no control over. An array of image UIDs is sent to the php script and the script needs to determine which UIDs are present in the table. Rather than make N number of individual queries as I iterate through the array, I iterate through the array and build the query on the fly to make one query. Then I iterate through the array again and check the value in the field. 1 means the UID has an entry, 0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of calls as I iterate through the array. But since there will probably never be more than 100 images in the table at any one time, it may not make any difference. But now I'm just curious as to why this is happening. Thanks, Bob
RE: odd problem with select as statement
Yes! The illegal double error only happens if you do the select like you did. The only error I was getting was the generic there's an error in your sql. Thank you! Bob From: Hank [mailto:hes...@gmail.com] Sent: Monday, December 20, 2010 11:52 AM To: Ramsey, Robert L Cc: mysql@lists.mysql.com Subject: Re: odd problem with select as statement i.e. just try this: mysql select 4E5664736F400E8B482EA7AA67853D13; ERROR 1367 (22007): Illegal double '4E5664736' value found during parsing -Hank On Mon, Dec 20, 2010 at 12:50 PM, Hank hes...@gmail.commailto:hes...@gmail.com wrote: Here's my 5 second guess.. 4E5664736... is being interpreted as a number in scientific notation .. i.e. 4*10^5664736 and the parser doesn't like that as a field name. -Hank On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L robert-ram...@uiowa.edumailto:robert-ram...@uiowa.edu wrote: I am having the hardest time getting a select as statement right. Here is the full query: select SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19, SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815, SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889, SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A, SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784, SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE, SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13, --offending line SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C from dsrssfeed If I remove that one line, the query works fine. If I do: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ; it works. But these fail: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ; select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ; It can't be field name length, since even 4E5 fails, the field name can start with a number since 4E succeeds. Any ideas? The goal is to see what arbitrary images have information associated with them. The table has two fields: image is a UID that is the primary key, and caption which is a varchar(255) that has information about the image. Images are added and deleted from the table as they are changed on a web page. The UID is generated by a third party program that I have to interface with and have no control over. An array of image UIDs is sent to the php script and the script needs to determine which UIDs are present in the table. Rather than make N number of individual queries as I iterate through the array, I iterate through the array and build the query on the fly to make one query. Then I iterate through the array again and check the value in the field. 1 means the UID has an entry, 0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of calls as I iterate through the array. But since there will probably never be more than 100 images in the table at any one time, it may not make any difference. But now I'm just curious as to why this is happening. Thanks, Bob
RE: Join Problem
What do you mean by not working? What results do you get?
-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Monday, August 16, 2010 6:59 AM
To: mysql@lists.mysql.com
Subject: Join Problem
Hi;
I have this code:
select f.id from Flights f join Planes p where f.plane_id=p.id and
p.in_service=1
mysql describe Flights;
+-+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+-++
| id | int(11) | NO | PRI | NULL| auto_increment |
| plane_id| int(11) | NO | MUL | NULL||
| pilot_id| int(11) | NO | MUL | NULL||
| flight_date | date | NO | | NULL||
| departure | time | NO | | NULL||
| arrival | time | NO | | NULL||
| origination | enum('STT','STX') | YES | | NULL||
| destination | enum('STT','STX') | YES | | NULL||
| price | float(6,2)| NO | | NULL||
+-+---+--+-+-++
mysql describe Planes;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-++
| id | int(11) | NO | PRI | NULL| auto_increment |
| name | varchar(20) | NO | | NULL||
| in_service | tinyint(1) | NO | | 1 ||
| capacity | tinyint(2) | NO | | NULL||
| total_weight | int(6) | NO | | NULL||
+--+-+--+-+-++
My goal is to exclude results in which in_service !=1; however, the filter
isn't working. Please advise.
TIA,
Victor
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept liability
for any loss or damage caused by viruses or errors or omissions in the contents
of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join Problem
Review your join type.
From: ext Gavin Towey [gto...@ffn.com]
Sent: 16 August 2010 19:36
To: Victor Subervi; mysql@lists.mysql.com
Subject: RE: Join Problem
What do you mean by not working? What results do you get?
-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Monday, August 16, 2010 6:59 AM
To: mysql@lists.mysql.com
Subject: Join Problem
Hi;
I have this code:
select f.id from Flights f join Planes p where f.plane_id=p.id and
p.in_service=1
mysql describe Flights;
+-+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+-++
| id | int(11) | NO | PRI | NULL| auto_increment |
| plane_id| int(11) | NO | MUL | NULL||
| pilot_id| int(11) | NO | MUL | NULL||
| flight_date | date | NO | | NULL||
| departure | time | NO | | NULL||
| arrival | time | NO | | NULL||
| origination | enum('STT','STX') | YES | | NULL||
| destination | enum('STT','STX') | YES | | NULL||
| price | float(6,2)| NO | | NULL||
+-+---+--+-+-++
mysql describe Planes;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-++
| id | int(11) | NO | PRI | NULL| auto_increment |
| name | varchar(20) | NO | | NULL||
| in_service | tinyint(1) | NO | | 1 ||
| capacity | tinyint(2) | NO | | NULL||
| total_weight | int(6) | NO | | NULL||
+--+-+--+-+-++
My goal is to exclude results in which in_service !=1; however, the filter
isn't working. Please advise.
TIA,
Victor
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept liability
for any loss or damage caused by viruses or errors or omissions in the contents
of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newb problem
On Tue, Jul 20, 2010 at 9:58 PM, dennis skinner dlsvi...@hotmail.comwrote: Hello I am a new mysql user. Can anyone tell me why this does not create a table? ?php(the spaces before the question mark are not in the code) $dbuser=smeduser; $dbpassword=x; $dbname=smed; mysql_connect(localhost, $dbuser, $dbpassword); mysql_select_db($dbname) or die(unable to select database); $query=create table patnotes(patid int(9) not null unsigned, patnote int(6) not null unsigned auto_increment, parentid int not null unsigned, appuserid varchar(40) not null, subject varchar(100) not null, * body longtext not null),* primary key(patnote), unique id(patnote); mysql_query($query); mysql_close(); then the closing question mark and carat on this line this does not build a file and I am wondering what syntax I am missing here thanks dennis In the above at the field body you have a close paranthesis, but I couldn't find a open paranthesis anywhere. I had highlighted the field above, pls check. Regards, Jay MySQL DBA, Datavail Corp.
Re: newb problem
On Tue, July 20, 2010 09:28, dennis skinner wrote: Hello I am a new mysql user. Can anyone tell me why this does not create a table? ?php(the spaces before the question mark are not in the code) $dbuser=smeduser; $dbpassword=x; $dbname=smed; mysql_connect(localhost, $dbuser, $dbpassword); mysql_select_db($dbname) or die(unable to select database); $query=create table patnotes(patid int(9) not null unsigned, patnote int(6) not null unsigned auto_increment, parentid int not null unsigned, appuserid varchar(40) not null, subject varchar(100) not null, body longtext not null), primary key(patnote), unique id(patnote); mysql_query($query); mysql_close(); then the closing question mark and carat on this line this does not build a file and I am wondering what syntax I am missing here thanks dennis First question: Does smeduser have table creation privilages in this database? -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newb problem
'unsigned' is part of your data type which must be before the 'not null' Your closing ) needs to be at the very end. There is no reason t have the unique id since the primary key is unique. First you need the column name 'patid' then the data type 'INT UNSIGNED' then the other column options 'NOT NULL' You should really use the back tick quotes around your column and table names. The (9) after int is of no use in a php application since you will have to do all your output formating in your php code. I have changed the query to use upper case letters as that is the standard way queries are written. CREATE TABLE `patnotes` ( `patid` INT UNSIGNED NOT NULL, `patnote` INT UNSIGNED NOT NULL AUTO_INCREMENT, `parentid` INT UNSIGNED NOT NULL , `appuserid` VARCHAR(40) NOT NULL, `subject` VARCHAR(100) NOT NULL, `body` LONGTEXT NOT NULL, PRIMARY KEY(`patnote`)) Chris W dennis skinner wrote: Hello I am a new mysql user. Can anyone tell me why this does not create a table? ?php(the spaces before the question mark are not in the code) $dbuser=smeduser; $dbpassword=x; $dbname=smed; mysql_connect(localhost, $dbuser, $dbpassword); mysql_select_db($dbname) or die(unable to select database); $query=create table patnotes(patid int(9) not null unsigned, patnote int(6) not null unsigned auto_increment, parentid int not null unsigned, appuserid varchar(40) not null, subject varchar(100) not null, body longtext not null), primary key(patnote), unique id(patnote); mysql_query($query); mysql_close(); then the closing question mark and carat on this line this does not build a file and I am wondering what syntax I am missing here thanks dennis Hotmail. Get busy. _ The New Busy is not the old busy. Search, chat and e-mail from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: alias problem and odd warnings
Hi Brian, all! brian wrote: On 10-06-14 09:13 PM, brian wrote: [[...]] This not only gives an empty set, but also throws 171 warnings (more on that below). I've remove both the WHERE and GROUP BY clauses with no success. I've been staring at this for an hour now and can't see what the trouble is. Can any of you? Solved. I just noticed the quoting difference here: ON `Member`.`country_id` = 'Country.id' Country.id is entirely wrapped in single quotes rather than the alias and column being separately wrapped with back-ticks. I've changed my code so that the query is created properly. Quote inserted by Jörg: As for the warnings: Warning | 1292 | Truncated incorrect DOUBLE value: 'Country.id' I'm still curious about the strange warning, though. AIUI, the wrong quotes in your statement made the parser take Country.id as a string, which was then to be compared to an integer (the country_id column). This comparison forced a conversion from string to number, and the string contained a dot which was assumed to be a decimal point, hence a floating point number. If 171 is the number of rows in your countries table, then it is also the number of times this comparison is done. The warnings should be gone, I assume, now that you fixed the quoting. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: alias problem and odd warnings
On 10-06-14 09:13 PM, brian wrote: Using 5.1.41/Ubuntu I have 2 tables, members countries. The former has a column, country_id (tinyint(3) unsigned). The countries id column is the same (although auto_increment). I'm trying to select just those countries for which there is a member. So I decided this query should do the trick: SELECT c.id, c.name FROM countries AS c INNER JOIN members AS m ON m.country_id = c.id GROUP BY c.id; And, indeed, it works like a charm. However, I'm using the CakePHP framework, which creates a query like: SELECT `Country`.`id`, `Country`.`name` FROM `countries` AS `Country` INNER JOIN members AS `Member` ON `Member`.`country_id` = 'Country.id' WHERE 1 = 1 GROUP BY `Country`.`id`; This not only gives an empty set, but also throws 171 warnings (more on that below). I've remove both the WHERE and GROUP BY clauses with no success. I've been staring at this for an hour now and can't see what the trouble is. Can any of you? Solved. I just noticed the quoting difference here: ON `Member`.`country_id` = 'Country.id' Country.id is entirely wrapped in single quotes rather than the alias and column being separately wrapped with back-ticks. I've changed my code so that the query is created properly. I'm still curious about the strange warning, though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: some problem of InnoDB performance optimization
Hi, Lets see If I can help. Overly long queries (transactions in general) are bad for performance as a lot of unpurged versions accumulate. In this sentence, I don't know the meaning about 'unpureged version accumulate' When rows are updated new versions are created. They are later removed by purge thread - only then no active transactions may need them. This is why long open transactions are expensive. And I don't how to understanding 'open transaction'? This is transaction which is started (opened) but not yet ended by commit or rollback. Required for logical level replication to work properly. What's the meaning about logical level replication? MySQL has statement level, also caused logical as well as row based replication. statement level replication requires updates to be serializable to work. Can give problems for portable applications if you port from MySQL-4.0 to later What's the meaning about this sentence? This means you can run into the problems if you upgrade from MySQL 4.0 to later version. Probably is not much relevant any more. -- Peter Zaitsev, CEO, Percona Inc. Tel: +1 888 401 3401 ext 501 Skype: peter_zaitsev 24/7 Emergency Line +1 888 401 3401 ext 911 Percona Training Workshops http://www.percona.com/training/
Re: some problem of InnoDB performance optimization
HI Peter: Thanks for your answer. I have understand your answer. Thank you very much. ―― Best regards Timo Seven blog: http://zauc.wordpress.com #请翻墙浏览，或者指定hosts到74.200.243.252### UNIX System Admin 2010/3/23 Peter Zaitsev p...@percona.com Hi, Lets see If I can help. Overly long queries (transactions in general) are bad for performance as a lot of unpurged versions accumulate. In this sentence, I don't know the meaning about 'unpureged version accumulate' When rows are updated new versions are created. They are later removed by purge thread - only then no active transactions may need them. This is why long open transactions are expensive. And I don't how to understanding 'open transaction'? This is transaction which is started (opened) but not yet ended by commit or rollback. Required for logical level replication to work properly. What's the meaning about logical level replication? MySQL has statement level, also caused logical as well as row based replication. statement level replication requires updates to be serializable to work. Can give problems for portable applications if you port from MySQL-4.0 to later What's the meaning about this sentence? This means you can run into the problems if you upgrade from MySQL 4.0 to later version. Probably is not much relevant any more. -- Peter Zaitsev, CEO, Percona Inc. Tel: +1 888 401 3401 ext 501 Skype: peter_zaitsev 24/7 Emergency Line +1 888 401 3401 ext 911 Percona Training Workshops http://www.percona.com/training/
Re: login problem from django script, using python2.5/MySQLdb/connections.py
John Griessen wrote: If I can login from a shell, what could stop a script from login? privileges were stopping it and at first, maybe a mistake in GRANT setup of a new user. Nevermind JG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: login problem from django script, using python2.5/MySQLdb/connections.py
Access Denied means you're using an incorrect username and password combination. Test your credentials using the mysql cli. You can log in as root to mysql to make changes as necessary, or supply the correct user/pass from your script. Regards, Gavin Towey -Original Message- From: John Griessen [mailto:j...@industromatic.com] Sent: Wednesday, December 09, 2009 10:16 AM To: mysql@lists.mysql.com Subject: login problem from django script, using python2.5/MySQLdb/connections.py If I can login from a shell, what could stop a script from login? I'm following a newbie tutorial for django, a web content mgt. system. The following user and password are good if I use them fromthe same shell the script launches from. Here's the error message from a django script using a python module about mysql: File /usr/lib/pymodules/python2.5/MySQLdb/connections.py, line 170, in __init__ super(Connection, self).__init__(*args, **kwargs2) _mysql_exceptions.OperationalError: (1044, Access denied for user 'django_editor'@'%' to database 'django_server') Any ideas? thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select Problem
On Sun, Dec 6, 2009 at 2:42 PM, Steve Edberg edb...@edberg-online.comwrote: At 1:26 PM -0500 12/6/09, Victor Subervi wrote: Hi; I have the following: mysql select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = prodCat2; ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause' mysql describe categoriesProducts; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql select * from categoriesProducts; ++--++ | ID | Category | Parent | ++--++ | 1 | prodCat1 | None | | 2 | prodCat2 | None | ++--++ 2 rows in set (0.00 sec) So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand how I specified that in my query. Please advise. TIA, Victor You didn't quote prodCat2 in the query, so it was assuming you were referring to the column name. Try: select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = 'prodCat2'; Thanks! V
Re: Select Problem
At 1:26 PM -0500 12/6/09, Victor Subervi wrote: Hi; I have the following: mysql select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = prodCat2; ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause' mysql describe categoriesProducts; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql select * from categoriesProducts; ++--++ | ID | Category | Parent | ++--++ | 1 | prodCat1 | None | | 2 | prodCat2 | None | ++--++ 2 rows in set (0.00 sec) So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand how I specified that in my query. Please advise. TIA, Victor You didn't quote prodCat2 in the query, so it was assuming you were referring to the column name. Try: select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = 'prodCat2'; - s -- ++ | Steve Edberg edb...@edberg-online.com | | Programming/Database/SysAdminhttp://www.edberg-online.com/ | ++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
So, as a followup, I ran mysqldump on the actual server (with the output directed over AFP to another machine on the network) -- as opposed to running mysqldump on the destination server and connecting to the databases over TCP/IP (both are running 5.1.39 PPC 64-bit). *That* dump file imported OK. So... It appears there's a bug in mysqldump that occurs intermittently when using TCP/IP connections to the MySQL database server. I haven't seen this bug occur on smaller databases, but it was pretty consistent on my non-damaged database of 9GB+. Anyway, for now I'm going to run automysqlbackup on the actual machine and simply dump to an external drive or another server on the network. On 2009-11-20, at 12:58 PM, René Fournier wrote: Yes, still exhibits this problem -- although at a different line in the file. (Seems random.) I should also mention, the backup is running across a local network. The machine the starts automysqlbackup (and internally, mysqldump) connects over TCP/IP to the database server. I am going to try running mysqldump on the database server itself, but it's not something I can do during normal hours -- plus at 10GB, it's a time-consuming thing to test. (Would it matter? I guess I need to find out.)
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
It's just odd, because that error was on 5.0. I'm running 5.1.39 -- surely enough time for the MySQL devs to fix it right? Could it be with extended inserts that the max statement length is greater than the default 16MB max allowed packet? I'm increasing that value to 256 MB and going to try again. Thanks for the idea though. At least I'm not the only one to have encountered that error. ...Rene On 2009-11-21, at 5:44 AM, 王浩 wrote: i googled the problem,it says that's a mysql bug.This is the source page :http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#backup_restore, And i suggest you ignore the error when import or correct the dump to the source database and then import again. Best wishes!
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
There is a bug in mysqldump that prevents mysql from version 5.0.80 from importing dumps from a old version of mysqldump. I had this verified by MySQL support. You can have a look at this bug: http://bugs.mysql.com/bug.php?id=41486 Cheers Claudio 2009/11/21 René Fournier m...@renefournier.com It's just odd, because that error was on 5.0. I'm running 5.1.39 -- surely enough time for the MySQL devs to fix it right? Could it be with extended inserts that the max statement length is greater than the default 16MB max allowed packet? I'm increasing that value to 256 MB and going to try again. Thanks for the idea though. At least I'm not the only one to have encountered that error. ...Rene On 2009-11-21, at 5:44 AM, 王浩 wrote: i googled the problem,it says that's a mysql bug.This is the source page :http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#backup_restore, And i suggest you ignore the error when import or correct the dump to the source database and then import again. Best wishes! -- Claudio
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Except that, in my case, both machines are running 5.1.39. Mac OS X PPC 64-bit. I tried importing another, different dump file of the same database, and this time mysql returned a different error: Sat Nov 21 13:16:20 -- Minas-Tirith :: mysql -u root -p dump_file.sql Enter password: ERROR 1136 (21S01) at line 6300: Column count doesn't match value count at row 6253 So, a different error. And it appears that each time mysqldump runs, the resulting file has different problem. On 2009-11-21, at 5:05 PM, Claudio Nanni wrote: There is a bug in mysqldump that prevents mysql from version 5.0.80 from importing dumps from a old version of mysqldump. I had this verified by MySQL support. You can have a look at this bug: http://bugs.mysql.com/bug.php?id=41486 Cheers Claudio 2009/11/21 René Fournier m...@renefournier.com It's just odd, because that error was on 5.0. I'm running 5.1.39 -- surely enough time for the MySQL devs to fix it right? Could it be with extended inserts that the max statement length is greater than the default 16MB max allowed packet? I'm increasing that value to 256 MB and going to try again. Thanks for the idea though. At least I'm not the only one to have encountered that error. ...Rene On 2009-11-21, at 5:44 AM, 王浩 wrote: i googled the problem,it says that's a mysql bug.This is the source page :http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#backup_restore, And i suggest you ignore the error when import or correct the dump to the source database and then import again. Best wishes! -- Claudio
RE: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Have you tried dumping that table manually using mysqldump on the command line to confirm it's not an issue with automysqlbackup? Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Friday, November 20, 2009 8:31 AM To: mysql Subject: Strange problem with mysqldump / automysqlbackup (ERROR 1300) I've been using automysqlbackup 2.5 for years on a particular database, and it's always performed great. Recently, however, I've become encountering problems when trying to re-import one of its dumped sql files. (Not sure if it matters, but the database file in question is large and growing -- about 10GB. The other databases automysqlbackup backs up are fine.) Basically on the import, MySQL fails and returns an error indicating a problem with the dump file: mysql -u root -p dump_file.sql (~10GB) Enter password: ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' Sure enough, I look at the line in dump_file.sql, which should contain two unsigned ints, and two unsigned small ints: [...],(32562206,1228?03422,1641,135),[...] And yup, there's a question mark in the middle of the second unsigned int, for some strange reason. Not in any of the other rows in that statement. When I look at the existing database from which the dump file was made, that row is fine: mysql SELECT * FROM bandwidth WHERE id = 32562206; +--++---+---+ | id | time_sec | device_id | bytes | +--++---+---+ | 32562206 | 1228803422 | 1641 | 135 | +--++---+---+ 1 row in set (0.00 sec) So... It appears either mysqldump and/or automysqlbackup is having a problem dumping a true copy of the database. Anyone else run into this sort of thing? Any suggestions? Thanks. ...Rene The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Yes, still exhibits this problem -- although at a different line in the file. (Seems random.) I should also mention, the backup is running across a local network. The machine the starts automysqlbackup (and internally, mysqldump) connects over TCP/IP to the database server. I am going to try running mysqldump on the database server itself, but it's not something I can do during normal hours -- plus at 10GB, it's a time-consuming thing to test. (Would it matter? I guess I need to find out.) In any case, it seems clear that during the dump, mysqldump is periodically adding a character to the dump file. On 2009-11-20, at 12:41 PM, Gavin Towey wrote: Have you tried dumping that table manually using mysqldump on the command line to confirm it's not an issue with automysqlbackup? Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Friday, November 20, 2009 8:31 AM To: mysql Subject: Strange problem with mysqldump / automysqlbackup (ERROR 1300) I've been using automysqlbackup 2.5 for years on a particular database, and it's always performed great. Recently, however, I've become encountering problems when trying to re-import one of its dumped sql files. (Not sure if it matters, but the database file in question is large and growing -- about 10GB. The other databases automysqlbackup backs up are fine.) Basically on the import, MySQL fails and returns an error indicating a problem with the dump file: mysql -u root -p dump_file.sql (~10GB) Enter password: ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' Sure enough, I look at the line in dump_file.sql, which should contain two unsigned ints, and two unsigned small ints: [...],(32562206,1228?03422,1641,135),[...] And yup, there's a question mark in the middle of the second unsigned int, for some strange reason. Not in any of the other rows in that statement. When I look at the existing database from which the dump file was made, that row is fine: mysql SELECT * FROM bandwidth WHERE id = 32562206; +--++---+---+ | id | time_sec | device_id | bytes | +--++---+---+ | 32562206 | 1228803422 | 1641 | 135 | +--++---+---+ 1 row in set (0.00 sec) So... It appears either mysqldump and/or automysqlbackup is having a problem dumping a true copy of the database. Anyone else run into this sort of thing? Any suggestions? Thanks. ...Rene The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Table problem
On Thursday 09 July 2009 01:56:37 pm Zakai Kinan wrote: I have been getting this error on a database: Incorrect key file for table '/tmp/#sql_a99_0.MYI'; try to repair it. How do I know which table is causing this error? TIA, ZK You could be running out of space on your temp partition.. if a query is either creating an explicit temporary table or generating one as part of execution, it may be filling it up. kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Table problem
run a quick df df Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: li...@voidship.net To: mysql@lists.mysql.com Subject: Re: Table problem Date: Thu, 9 Jul 2009 14:07:08 -0400 On Thursday 09 July 2009 01:56:37 pm Zakai Kinan wrote: I have been getting this error on a database: Incorrect key file for table '/tmp/#sql_a99_0.MYI'; try to repair it. How do I know which table is causing this error? TIA, ZK You could be running out of space on your temp partition.. if a query is either creating an explicit temporary table or generating one as part of execution, it may be filling it up. kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail® has ever-growing storage! Don’t worry about storage limits. http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009
Re: Table problem
two servers were having the same problem and they each have 1GB of space in the temp dir. I was able to fix the problem by recreating the biggest table in the database. The total space in each database is 350MB. The deleted table's size was 500kb. The mysql version is 5.0.51a. I am afraid that when the table gets to that size it is going to crapped out again. Is there anything that I may not be taking into consideration. Thanks, ZK --- On Thu, 7/9/09, kabel li...@voidship.net wrote: From: kabel li...@voidship.net Subject: Re: Table problem To: mysql@lists.mysql.com Date: Thursday, July 9, 2009, 11:07 AM On Thursday 09 July 2009 01:56:37 pm Zakai Kinan wrote: I have been getting this error on a database: Incorrect key file for table '/tmp/#sql_a99_0.MYI'; try to repair it. How do I know which table is causing this error? TIA, ZK You could be running out of space on your temp partition.. if a query is either creating an explicit temporary table or generating one as part of execution, it may be filling it up. kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=titanyen2...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: replication problem
Hi. Can you give me a flow of your master/slave? On Wed, Jun 24, 2009 at 7:45 PM, 赵琦 tyzha...@gmail.com wrote: hi all: I have three mysql database,tow run as master and the other one runs as slave. Some tables in the database have an autoincreament field named as 'rowid'. These tables have 100 records on master, but some of these tables on the slave only have thousands of record. The tables on the slave are not the same as those on master. When i use 'show slave status', i find that Last_Errno equals to 0. How to solve this problem. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: ndbcluster problem
Have you checked the type of column you are using. Depending on what the 108 number means, it could be altering the table to say int or bigint column. If it means total number of records, it does not seem to correspond to a medint value, either signed or not. If it means the record number, your column might have a large start number. Some new companies don't like invoicing starting out at record 1. I have no experience in ndbcluster but I would assume that it has some kind of column limit for performance gains in indexing. Mike O'Krongli Acorg Inc http://www.acorg.com - Original Message - From: sangprabv sangpr...@gmail.com To: mysql@lists.mysql.com Sent: Thursday, June 18, 2009 1:10 PM Subject: ndbcluster problem Is there any record limitation in ndbcluster? Because I can't insert more records after it reached 108 records. How to solve this? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mike_...@acorg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ndbcluster problem
That 108 is the total number of records. This caused by the DataMemory directive in the config and I must increase the value. Willy On Thu, 2009-06-18 at 14:10 -0400, Mike OK wrote: Have you checked the type of column you are using. Depending on what the 108 number means, it could be altering the table to say int or bigint column. If it means total number of records, it does not seem to correspond to a medint value, either signed or not. If it means the record number, your column might have a large start number. Some new companies don't like invoicing starting out at record 1. I have no experience in ndbcluster but I would assume that it has some kind of column limit for performance gains in indexing. Mike O'Krongli Acorg Inc http://www.acorg.com - Original Message - From: sangprabv sangpr...@gmail.com To: mysql@lists.mysql.com Sent: Thursday, June 18, 2009 1:10 PM Subject: ndbcluster problem Is there any record limitation in ndbcluster? Because I can't insert more records after it reached 108 records. How to solve this? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mike_...@acorg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Compile problem (was: MySQL Community Server 5.1.35 has been released)
Dave, do you really consider it helpful to do reply all to a release announcement, trying to send it to the announce and packagers list, including a full quote of the announcement text, and using the original subject line, when you are asking for help about a compile problem? Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: [[...]] Error in compiling: mv -f .deps/liboptions_la-priv.Tpo .deps/liboptions_la-priv.Plo /bin/sh ../../libtool --preserve-dup-deps --tag=CXX --mode=link g++ -O3 -fno-implicit-templates -fno-exceptions -fno-rtti -DDEFAULT_PID_FILE_NAME=/usr/contrib/mysqld/mysqlmanager.pid -DDEFAULT_LOG_FILE_NAME=/usr/contrib/mysqld/mysqlmanager.log -DDEFAULT_SOCKET_FILE_NAME=/tmp/mysqlmanager.sock -DDEFAULT_PASSWORD_FILE_NAME=/etc/mysqlmanager.passwd -DDEFAULT_MYSQLD_PATH=/usr/contrib/libexec/mysqld -DDEFAULT_CONFIG_FILE=my.cnf -DPROTOCOL_VERSION=10 -O3 -fno-implicit-templates -fno-exceptions -fno-rtti -o liboptions.la liboptions_la-options.lo liboptions_la-priv.lo ../../libmysql/get_password.lo -lm test: unrecognized integer ` ar cru .libs/liboptions.a .libs/liboptions_la-options.o .libs/liboptions_la-priv.o ../../libmysql/.libs/get_password.o ~ranlib .libs/liboptions.a/liboption' ../../libtool: arith: syntax error: 47 + .libs/liboptions_la-options.o~RANLI gmake[2]: *** [liboptions.la] Error 2 gmake[2]: Leaving directory `/usr/src/contrib/mysql/mysql-5.1.35/server-tools/instance-manager' gmake[1]: *** [all-recursive] Error 1 gmake[1]: Leaving directory `/usr/src/contrib/mysql/mysql-5.1.35/server-tools' gmake: *** [all-recursive] Error 1 *** Error code 2 Stop. Help!! I can't. I cannot even try to help you, if you do not provide the necessary information. All I can do is give you the corresponding line from my log, split into several lines for better readability: /bin/sh ../../libtool --preserve-dup-deps --tag=CXX --mode=link gcc -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused-function -Wunused-label -Wunused-value -Wunused-variable -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mtune=native -O3 -fno-omit-frame-pointer -g -fno-implicit-templates -fno-exceptions -fno-rtti -DDEFAULT_PID_FILE_NAME=/usr/local/mysql/var/mysqlmanager.pid -DDEFAULT_LOG_FILE_NAME=/usr/local/mysql/var/mysqlmanager.log -DDEFAULT_SOCKET_FILE_NAME=/tmp/mysqlmanager.sock -DDEFAULT_PASSWORD_FILE_NAME=/etc/mysqlmanager.passwd -DDEFAULT_MYSQLD_PATH=/usr/local/mysql/libexec/mysqld -DDEFAULT_CONFIG_FILE=my.cnf -DPROTOCOL_VERSION=10 -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused-function -Wunused-label -Wunused-value -Wunused-variable -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mtune=native -O3 -fno-omit-frame-pointer -g -fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o liboptions.la liboptions_la-options.lo liboptions_la-priv.lo ../../libmysql/get_password.lo -lpthread -lcrypt -lnsl -lm -lpthread This was running on Linux (SuSE 10.3) running on an Athlon, using libtool 1.5.24 and gcc 4.2.1. Looking at the text you quote, it seems the commands executed by libtool got garbled: A command starting with ar cru suddenly continues with ~ranlib which doesn't make any sense to me. I suspect some problem around your libtool installation, or with your file system (affecting some generated files). Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql problem
AZZOPARDI Konrad wrote:
Hello people,
I do not know if this the right listI am migrating a very basic application from an older mysql version 4.1.9-standard to a new mysql version 5.0.45 {RedHat default package}. I have migrated DB data from one to the other and all data seems to be there including the structureMy problem is that I run a query like this :
SELECT x.application_name, y.role_name FROM application x, role y JOIN logical_app_role_link l ON x.application_id = l.application_id AND y.role_id = l.role_id WHERE l.logical_id = 15;
It works for the old mysql version but for the new mysql version I receive the
following error :
ERROR 1054 (42S22): Unknown column 'x.application_id' in 'on clause'
and I am sure that application_id exists in table application.
Thanks
konrad
Don't mix implicit and explicit joins.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql problem
Konrad,
AZZOPARDI Konrad schrieb:
Hello people,
I do not know if this the right listI am migrating a very basic application from an older mysql version 4.1.9-standard to a new mysql version 5.0.45 {RedHat default package}. I have migrated DB data from one to the other and all data seems to be there including the structureMy problem is that I run a query like this :
SELECT x.application_name, y.role_name FROM application x, role y JOIN logical_app_role_link l ON x.application_id = l.application_id AND y.role_id = l.role_id WHERE l.logical_id = 15;
It works for the old mysql version but for the new mysql version I receive the
following error :
ERROR 1054 (42S22): Unknown column 'x.application_id' in 'on clause'
and I am sure that application_id exists in table application.
read the upgrading instructions and pay special attention
to http://dev.mysql.com/doc/refman/5.0/en/join.html,
Join Processing Changes in MySQL 5.0.12.
Best to avoid this issue is to not mix implicit and
explicit joins, as Gerald pointed out.
Ciao,
Thomas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: facing problem with is null in stored procedure
hai shawn green,
thanks for ur response.My situation is to generate a production
plan.for this I collect the data from sales orders and compare the stock
availability in rthe warehouse and if the stock is less than the ordered
quantity I plan for production and for this I have to issue the raw material
required for the production by calculating based on bom(bill of material)
details.I am doing this but for every item in the sales oredr I am inserting
the record even though same item is in the another order.I have to update
the quantity i the item is already in the list otherwise i have to insert.
please give me solution if u got my situation.
On Sun, Apr 12, 2009 at 1:07 AM, Shawn Green shawn.gr...@sun.com wrote:
syed basha wrote:
delimiter //
create procedure sample1(in p_item varchar(30),
in p_size varchar(6),
in p_quantity decimal(10,3),
in p_unit varchar(3),
in p_autoincrement varchar(30),
out v_mess varchar(50)
)
begin
declare done int default 0;
declare v_bhqty decimal(10,3);
declare v_bhunit varchar(3);
declare v_blrawm varchar(30);
declare v_blqty decimal(10,3);
declare v_blunit varchar(3);
declare v_puid varchar(30);
declare v_conqty decimal(10,3);
declare v_plid int(3) default 1;
declare v_calqty decimal(10,3);
declare c_bomdet cursor for select bh.qty,bh.unit,bl.rawm,bl.qty,bl.unit
from bomhead bh,bomlins bl
where bl.item=bh.item and bl.szid=bh.szid and bl.item=p_item and
bl.szid=p_size;
declare continue handler for not found set done=1;
select ppid into v_puid from prplrawm where ppid=(select
concat('pph',max(convert(substr(ppid,4),unsigned integer))) from
prpllins);
open c_bomdet;
repeat
fetch c_bomdet into v_bhqty,v_bhunit,v_blrawm,v_blqty,v_blunit;
if not done then
call convertion(p_unit,v_bhunit,p_item,p_size,p_quantity,v_conqty);
set v_calqty=v_conqty*v_blqty;
if v_puid is not null then
set v_mess='max id is exists';
else
insert into prplrawm(ppid,plid,item,szid,rawm,rqty,runt)
values(p_autoincrement,v_plid,p_item,p_size,v_blrawm,v_blrqty,v_blrunt);
set v_plid=v_plid+1;
end if;
end if;
until done end repeat;
close c_bomdet;
end
//
delimiter ;
when i call this procedure it is not performing the insert statement even
though the condition is true.
please tell me why.
thank you
How do you know your condition is true?
Could this be part of a transaction that is rolled-back instead of
committed?
There are too many unknowns about your situation to make a clear judgement
on this particular problem.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
sibasha
Re: facing problem with is null in stored procedure
syed basha wrote:
delimiter //
create procedure sample1(in p_item varchar(30),
in p_size varchar(6),
in p_quantity decimal(10,3),
in p_unit varchar(3),
in p_autoincrement varchar(30),
out v_mess varchar(50)
)
begin
declare done int default 0;
declare v_bhqty decimal(10,3);
declare v_bhunit varchar(3);
declare v_blrawm varchar(30);
declare v_blqty decimal(10,3);
declare v_blunit varchar(3);
declare v_puid varchar(30);
declare v_conqty decimal(10,3);
declare v_plid int(3) default 1;
declare v_calqty decimal(10,3);
declare c_bomdet cursor for select bh.qty,bh.unit,bl.rawm,bl.qty,bl.unit
from bomhead bh,bomlins bl
where bl.item=bh.item and bl.szid=bh.szid and bl.item=p_item and
bl.szid=p_size;
declare continue handler for not found set done=1;
select ppid into v_puid from prplrawm where ppid=(select
concat('pph',max(convert(substr(ppid,4),unsigned integer))) from prpllins);
open c_bomdet;
repeat
fetch c_bomdet into v_bhqty,v_bhunit,v_blrawm,v_blqty,v_blunit;
if not done then
call convertion(p_unit,v_bhunit,p_item,p_size,p_quantity,v_conqty);
set v_calqty=v_conqty*v_blqty;
if v_puid is not null then
set v_mess='max id is exists';
else
insert into prplrawm(ppid,plid,item,szid,rawm,rqty,runt)
values(p_autoincrement,v_plid,p_item,p_size,v_blrawm,v_blrqty,v_blrunt);
set v_plid=v_plid+1;
end if;
end if;
until done end repeat;
close c_bomdet;
end
//
delimiter ;
when i call this procedure it is not performing the insert statement even
though the condition is true.
please tell me why.
thank you
How do you know your condition is true?
Could this be part of a transaction that is rolled-back instead of
committed?
There are too many unknowns about your situation to make a clear
judgement on this particular problem.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A problem relative ibdata1
You can't! it is part of the datafiles of InnoDB storage engine! and It contains important data like the information schema. So I would rather ask you, is it abnormally growing or is it just your feeling? You should be more precise to have good answers, for instance how much megabytes per minute is it growing? How many inserts do you have on the tables per second/minute/hour? Ciao! Claudio 2009/3/26 Riccardo Michele Filippone ethern...@gmail.com Hello! First of all sorry for my terrible english. Well I've a big problem with ibdata1, this damn file increase its size constantly. I tryed to use innodb_file_per_table... but: - MySQL create a lot of ibd files for all DB (correct) - ibdata1 is however created and increase its size... how can I prevent the creation of this file (ibdata1)? Thanks for your future suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: A problem relative ibdata1
Hi Riccardo, I don't think you should need any other storage engine if you need foreign keys. InnoDB is by far the most used, standard, transactional, foreign key-supporting MySQL storage engine. No other valid option comes to my mind now. But please share your needs and let's see if I am wrong. Ciao Claudio 2009/3/26 Riccardo Michele Filippone ethern...@gmail.com Well, I think it's abnormal because after a few days I can have a ibdata1 of 7-8 GB!!! I don't think it's normal because, if ibdata1 continue is growth... I must be purchase a datacente :P Now I try to retrive a charts of queries... however do you know if there is an engine that support/manage foreign key? I use innodb for this reason.. but the size of ibdata1 is a problem :'( Thanks for ur reply Il giorno 26/mar/09, alle ore 12:30, Claudio Nanni ha scritto: You can't! it is part of the datafiles of InnoDB storage engine! and It contains important data like the information schema. So I would rather ask you, is it abnormally growing or is it just your feeling? You should be more precise to have good answers, for instance how much megabytes per minute is it growing? How many inserts do you have on the tables per second/minute/hour? Ciao! Claudio 2009/3/26 Riccardo Michele Filippone ethern...@gmail.com Hello! First of all sorry for my terrible english. Well I've a big problem with ibdata1, this damn file increase its size constantly. I tryed to use innodb_file_per_table... but: - MySQL create a lot of ibd files for all DB (correct) - ibdata1 is however created and increase its size... how can I prevent the creation of this file (ibdata1)? Thanks for your future suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: A problem relative ibdata1
In the last episode (Mar 26), Riccardo Michele Filippone said: Hello! First of all sorry for my terrible english. Well I've a big problem with ibdata1, this damn file increase its size constantly. I tryed to use innodb_file_per_table... but: - MySQL create a lot of ibd files for all DB (correct) - ibdata1 is however created and increase its size... how can I prevent the creation of this file (ibdata1)? The main cause of ibdata1 growth when using innodb_file_per_table is large transactions. InnoDB rollback data is stored in ibdata1. To minimize the size of ibdata1, try to commit frequently when doing table loads or other operations that might change a lot of data in a single transaction. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
On Feb 12, 2009, at 6:28 PM, csego...@gmail.com wrote: Andy, Michael, and Walter - thank you! Adding a [mysqld_safe] group to my.cnf gets me further but the start still fails. The good thing is that the failure is no longer due to the inability to write the log file. The [mysqld_safe] section of my.cnf reads: [mysqld_safe] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir = /app/mysql/data #data_file_path = /app/mysql/data #log = /app/mysql/log/mysqld.log log-bin = /app/mysql/log/mysql-bin.log log-output = FILE general_log = 1 general_log_file = /app/mysql/log/msyql_general.log socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log Luckily, I now have an error log which reads: 090212 18:24:04 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data InnoDB: Log scan progressed past the checkpoint lsn 0 39548 090212 18:24:04 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 46409 090212 18:24:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 090212 18:24:04 InnoDB: Started; log sequence number 0 46409 090212 18:24:04 [ERROR] /app/mysql/libexec/mysqld: Can't create/ write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2) 090212 18:24:04 [ERROR] Can't start server: can't create PID file: No such file or directory 090212 18:24:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended Now I need to figure out how to point PID file creation the appropriate directory. Sounds like you want: pid-file = /app/mysql/log/mysql.pid http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_pid-file -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
Colin, hi, so I think your mysql account doesn't have access /var/log/ directory Permission. Yours, fire9 在 2009-2-13，上午1:02， csego...@gmail.com 写道： In the process of installing MySQL 5.1.31, I receive the following error when I attempt to start mysqld_safe: 090212 10:57:40 mysqld_safe Logging to '/var/log/mysqld.log'. 090212 10:57:40 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied /app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied 090212 10:57:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied My my.cnf file reads: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir = /app/mysql/data #data_file_path = /app/mysql/data general_log = 1 general_log_file = /app/mysql/log/ socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log I thought the general_log_file variable would define the location of the log file, yet mysqld is trying to write the log file to a different directory. Does anyone have any suggestions on how to fix this? Thanks! Colin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com
Re: Install problem: log file issue
That is intentional. I want to write the log to a non-standard location. Thanks! C fire9 wrote: Colin, hi, so I think your mysql account doesn't have access /var/log/ directory Permission. Yours, fire9 ÔÚ 2009-2-13£¬ÉÏÎç1:02£¬ csego...@gmail.com mailto:csego...@gmail.com дµÀ£º In the process of installing MySQL 5.1.31, I receive the following error when I attempt to start mysqld_safe: 090212 10:57:40 mysqld_safe Logging to '/var/log/mysqld.log'. 090212 10:57:40 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied /app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied 090212 10:57:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied My my.cnf file reads: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir = /app/mysql/data #data_file_path = /app/mysql/data general_log = 1 general_log_file = /app/mysql/log/ socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log I thought the general_log_file variable would define the location of the log file, yet mysqld is trying to write the log file to a different directory. Does anyone have any suggestions on how to fix this? Thanks! Colin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
It could be that your my.cnf isn't being read (permissions or in the wrong directory), or that you have multiple my.cnf files. Quoting csego...@gmail.com csego...@gmail.com: That is intentional. I want to write the log to a non-standard location. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
Andy, Thanks for the suggestion. my_print_defaults mysqld indicates that it is reading my my.cnf. Furthermore, I have tried both the log and log-output variables in my.cnf with no success. Any other ideas? Thanks! C Andy Smith wrote: It could be that your my.cnf isn't being read (permissions or in the wrong directory), or that you have multiple my.cnf files. Quoting csego...@gmail.com csego...@gmail.com: That is intentional. I want to write the log to a non-standard location. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
I haven't looked into it in detail an don't have time right nw, but it is also hardcoded in mysqld_safe. I have seen a case where it had to be changed there to make it work. try that and please report back :) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Thu, Feb 12, 2009 at 6:39 PM, csego...@gmail.com csego...@gmail.com wrote: Andy, Thanks for the suggestion. my_print_defaults mysqld indicates that it is reading my my.cnf. Furthermore, I have tried both the log and log-output variables in my.cnf with no success. Any other ideas? Thanks! C Andy Smith wrote: It could be that your my.cnf isn't being read (permissions or in the wrong directory), or that you have multiple my.cnf files. Quoting csego...@gmail.com csego...@gmail.com: That is intentional. I want to write the log to a non-standard location. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
Andy, Michael, and Walter - thank you! Adding a [mysqld_safe] group to my.cnf gets me further but the start still fails. The good thing is that the failure is no longer due to the inability to write the log file. The [mysqld_safe] section of my.cnf reads: [mysqld_safe] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir = /app/mysql/data #data_file_path = /app/mysql/data #log = /app/mysql/log/mysqld.log log-bin = /app/mysql/log/mysql-bin.log log-output = FILE general_log = 1 general_log_file = /app/mysql/log/msyql_general.log socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log Luckily, I now have an error log which reads: 090212 18:24:04 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data InnoDB: Log scan progressed past the checkpoint lsn 0 39548 090212 18:24:04 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 46409 090212 18:24:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 090212 18:24:04 InnoDB: Started; log sequence number 0 46409 090212 18:24:04 [ERROR] /app/mysql/libexec/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2) 090212 18:24:04 [ERROR] Can't start server: can't create PID file: No such file or directory 090212 18:24:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended Now I need to figure out how to point PID file creation the appropriate directory. C Walter Heck wrote: I haven't looked into it in detail an don't have time right nw, but it is also hardcoded in mysqld_safe. I have seen a case where it had to be changed there to make it work. try that and please report back :) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Thu, Feb 12, 2009 at 6:39 PM, csego...@gmail.com csego...@gmail.com wrote: Andy, Thanks for the suggestion. my_print_defaults mysqld indicates that it is reading my my.cnf. Furthermore, I have tried both the log and log-output variables in my.cnf with no success. Any other ideas? Thanks! C Andy Smith wrote: It could be that your my.cnf isn't being read (permissions or in the wrong directory), or that you have multiple my.cnf files. Quoting csego...@gmail.com csego...@gmail.com: That is intentional. I want to write the log to a non-standard location. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: configure --prefix not working
/var/lib/mysql is the standard loation for the data_file_path, the data directory. change it in my.cnf. var/log is the default location for log files, also changeable in the config file. Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Feb 11, 2009 at 5:35 PM, csego...@gmail.com csego...@gmail.com wrote: I am trying to install mysql in a non-standard directory - specifically /app/mysql Subsequently, I ran the following configure command: ./configure --prefix=/app/mysql --with-plugins=innobase,myisam There were no problems with the compile, but mysql failed to start. Furthermore, the following output was produced in the nohup.out file -bash-3.2$ cat nohup.out 090211 09:40:35 mysqld_safe Logging to '/var/log/mysqld.log'. mkdir: cannot create directory `/var/lib/mysql': Permission denied chown: cannot access `/var/lib/mysql': No such file or directory chmod: cannot access `/var/lib/mysql': No such file or directory 090211 09:40:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied /app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied 090211 09:40:35 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied For some reason, the mysql install is looking to /var even though I specificied /app/mysql in the compile. How do I fix this? Thanks! Colin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Problem when ORing w/ Long.MIN_VALUE
For the curious: As usual select is not broken. Lesson learned: Always watch out for warnings: http://bugs.mysql.com/bug.php?id=41007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update Problem when ORing w/ Long.MIN_VALUE
Hi Gautam nope yours is not a bug. That's all fine. Hex numbers are 64 bit unsigned. So for -1 you have to insert cast(0x as signed). Cheers, Daniel Hi Daniel, I can see the problem without using update. However, I am a newbie at mysql, so can't say for certain if it's a bug: mysql drop table if exists foo; mysql create table foo (id int signed, val bigint signed); mysql insert into foo values (0x, 0x), (-1, -1); mysql select hex(id), hex(val) from foo; +--+--+ | hex(id) | hex(val) | +--+--+ | 7FFF | 7FFF | | | | +--+--+ 2 rows in set (0.00 sec) Regards Gautam Daniel Doubleday wrote: Hi everybody - I'm experiencing some really weird update behaviour (mysql 5.0) when or'ing results from subselects using Long.MIN_VALUE. But before I post a bug report I wanted to ask if I'm missing something. drop table if exists foo; drop table if exists bar; create table foo (fooid int, fooval bigint); create table bar (barid int, barval bigint); insert into foo values (1, null), (2, null); insert into bar values (1, 123), (2, 345); update foo set fooval = (select barval from bar where barid = fooid) | 0x8000; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # Oops result is Long.MAX_VALUE (as if subselect result was 0 - bit logic result is always unsigned bigint) # Same thing when you replace subselect by multi table update syntax update foo, bar set fooval = barval | 0x8000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # and it seems that its all about MSB sign bit, cause thats fine: update foo, bar set fooval = barval | 0x7000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 8070450532247928955 | # | 2 | 8070450532247929177 | # +---+-+ # 2 rows in set (0.00 sec) # and casting the or result! does the trick too though I dont understand why ... update foo set fooval = cast((select barval from bar where barid = fooid) | 0x8000 as signed); select * from foo; # +---+--+ # | fooid | fooval | # +---+--+ # | 1 | -9223372036854775685 | # | 2 | -9223372036854775463 | # +---+--+ # 2 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update Problem when ORing w/ Long.MIN_VALUE
Hi Daniel, I can see the problem without using update. However, I am a newbie at mysql, so can't say for certain if it's a bug: mysql drop table if exists foo; mysql create table foo (id int signed, val bigint signed); mysql insert into foo values (0x, 0x), (-1, -1); mysql select hex(id), hex(val) from foo; +--+--+ | hex(id) | hex(val) | +--+--+ | 7FFF | 7FFF | | | | +--+--+ 2 rows in set (0.00 sec) Regards Gautam Daniel Doubleday wrote: Hi everybody - I'm experiencing some really weird update behaviour (mysql 5.0) when or'ing results from subselects using Long.MIN_VALUE. But before I post a bug report I wanted to ask if I'm missing something. drop table if exists foo; drop table if exists bar; create table foo (fooid int, fooval bigint); create table bar (barid int, barval bigint); insert into foo values (1, null), (2, null); insert into bar values (1, 123), (2, 345); update foo set fooval = (select barval from bar where barid = fooid) | 0x8000; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # Oops result is Long.MAX_VALUE (as if subselect result was 0 - bit logic result is always unsigned bigint) # Same thing when you replace subselect by multi table update syntax update foo, bar set fooval = barval | 0x8000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # and it seems that its all about MSB sign bit, cause thats fine: update foo, bar set fooval = barval | 0x7000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 8070450532247928955 | # | 2 | 8070450532247929177 | # +---+-+ # 2 rows in set (0.00 sec) # and casting the or result! does the trick too though I dont understand why ... update foo set fooval = cast((select barval from bar where barid = fooid) | 0x8000 as signed); select * from foo; # +---+--+ # | fooid | fooval | # +---+--+ # | 1 | -9223372036854775685 | # | 2 | -9223372036854775463 | # +---+--+ # 2 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
