Re: seeing errors
On 3/25/2017 10:03 AM, Hal.sz S.ndor wrote: 2017/03/24 15:19 ... william drescher: While loading a database using the mysql command 'source' I see occasional warnings flash by on the screen. when it finished I used the command "show warnings," but only one warning showed (the last one). How can I see the warnings earlier in the load ? There is an option to the client program mysql, "--show-warnings", that not summaries but full messages are shown. Furthermore, if, as by default, warnings, too, go to the error log, you will find everything interesting there. Thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
seeing errors
While loading a database using the mysql command 'source' I see occasional warnings flash by on the screen. when it finished I used the command "show warnings," but only one warning showed (the last one). How can I see the warnings earlier in the load ? -bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: find any row with NULL
On 7/8/2016 9:15 AM, Johan De Meersman wrote: As a clear example of what it is useful for, imagine a situation where you're performing an inventory on an existing warehouse. You have the list of all the products they've ever sold, but you need to differentiate between 'this product is not in stock' (count = 0) and 'I have not counted this product yet' (count IS NULL). Excellent example -bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave to master
On 4/30/2016 3:22 AM, Thomas wrote: Am Freitag, 29. April 2016, 14:49:57 schrieb william drescher: On 4/28/2016 5:20 PM, Thomas wrote: Hi, I have setup an master slave replication. This works fine. I have running an Apache webserver and some other programms accessing the master. Whats the standard pocedure if master fail? When the master is repaired and up you need to stop the programs on the slave from accessing mysql (ie: stop the programs on the slave), make a database copy of the slave, copy it to the master and reload the database on the master, and then use the Change Master to command on the slave to set up the slave to begin replicating and start slave. This is the poor man's failover for low volume systems. In essence the slave becomes the main database server until you are ready to restart the master and replication. Hi thanks for all the answers, I only want that when master fails the slave will be the new master and do everythink that the master has before done. So I have more time to repair the master. I have to change the slave to master because I need write access to the mySQL databases. Sure that when mySQL data on the master are damaged I will have same problems on the slave, but for this I have an undependent data saving, not realtime but acceptable. Sure I need in this case more time to repair the whole system. slave to master: Like this: https://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html Is this the way to go? thanks Thomas yes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave to master
On 4/28/2016 5:20 PM, Thomas wrote: Hi, I have setup an master slave replication. This works fine. I have running an Apache webserver and some other programms accessing the master. Whats the standard pocedure if master fail? I want to start up the programms on the slave by hand and then they are accessing the mySQL slave. Can they write to the slave or do I have to change something before in the mysql slave configuration? thanks Thomas The simple answer is that you don't have to do anything. At some point you will want to stop the slave so that it will not start replicating when the master comes back up. When the master is repaired and up you need to stop the programs on the slave from accessing mysql (ie: stop the programs on the slave), make a database copy of the slave, copy it to the master and reload the database on the master, and then use the Change Master to command on the slave to set up the slave to begin replicating and start slave. This is the poor man's failover for low volume systems. In essence the slave becomes the main database server until you are ready to restart the master and replication. bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: need help from the list admin
On 3/22/2016 7:49 AM, Lentes, Bernd wrote: - On Mar 22, 2016, at 12:07 PM, william drescher will...@techservsys.com wrote: sent for Bernd, and to see if it works from another sender -- Lentes, Bernd wrote: Hi, i know that there is a list dedicated to replication, but when you have a look in the archive it's nearly complete empty. Really not busy. So i hope it's ok if i ask here. we have a web app which runs a MySQL DB and dynamic webpages with perl and apache httpd. Webpages serve reading and writing into the db. The db is important for our own work flow, so i'd like to make it HA. I have two HP servers and will use SLES 11 SP4 64bit as OS. MySQL is 5.5.47. For HA i'd like to use pacemaker, which is available in SLES High Availibility Extension. I have experience in linux, but i'm not a database administrator nor developer. HA is important for us, we don't have performance problems. My first idea was to run the web app and the db in a virtual machine on the host and in case of a failure of one host pacemaker would run the vm on the other host. VM would be stored on a FC SAN. I stopped following this idea. I have bought a book about HA: "..." from Oliver Liebel. It's only available in german. But i can recommend it, it's very detailed and well explained. He proposed to have two hosts, and on each is running a MySQL instance as master AND slave. But it's not a "real multi master solution", because pacemaker takes care that the IP for the web app just points to one master. So i don't have the multi-master problems with concurrent inserts (i believe). His idea is that host A is master for the slave on host B, and host B is the master for the slave on host A. OK ? Let's imagining that the IP to the web app points to host A, inserts are done to the master on host A and replicated to the slave on host B. Now host A has problems, pacemaker redirects the IP to host B, and everything should be fine. What do you think about this setup ? Where is the advantage to a "classical Master/Slave Replication" ? How should i configure log-slave-updates in this scenario ? Let's imagine i have two hosts again: Host A is master, host B is slave. Nothing else. No real or pseudo "Multi-Master". IP points to host A. Host A has problems, pacemaker recognizes it, promotes B to master and pivot the IP. Everything should be fine. Where is the disadvantage of this setup compared to the "Multi-Master Replication" in the book ? The OCF ressource agent for mysql should be able to handle the mysql stuff and the RA for the IP pivots the IP. Now some dedicated questions to replication. I read a lot in the official documentation, but some things are not clear to me. In our db we have MyISAM and InnoDB tables. From what i read i'd prefer row based replication. The doc says is the safest approach. But there seems to be still some problems: The doc says: "For tables using the MYISAM storage engine, a stronger lock is required on the slave for INSERT statements when applying them as row-based events to the binary log than when applying them as statements. This means that concurrent inserts on MyISAM tables are not supported when using row-based replication." What does this exactly mean ? Concurrent inserts in MyISAM-tables are not possible if using RBL ? Or unsafe in the meaning they create inconsistencies ? "RBL (Row Based Logging) and synchronization of nontransactional tables. When many rows are affected, the set of changes is split into several events; when the statement commits, all of these events are written to the binary log. When executing on the slave, a table lock is taken on all tables involved, and then the rows are applied in batch mode. (This may or may not be effective, depending on the engine used for the slave抯 copy of the table.)" What does that mean ? Effective ? Is it creating inconsistencies ? Or just not effective in the sense of slow or inconvinient ? Or should i prefer MIXED for binlog_format ? The doc says: " If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped. This is true whether or not any temporary tables are actually logged. Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables." What does that mean ? Unsafe ? Causing inconsistencies ? Problem with SBL or RBL ? The doc says: "Due to concurrency issues, a slave can become inconsistent when a transaction contains updates to both transactional and nontran
Re: need help from the list admin
sent for Bernd, and to see if it works from another sender -- Lentes, Bernd wrote: Hi, i know that there is a list dedicated to replication, but when you have a look in the archive it's nearly complete empty. Really not busy. So i hope it's ok if i ask here. we have a web app which runs a MySQL DB and dynamic webpages with perl and apache httpd. Webpages serve reading and writing into the db. The db is important for our own work flow, so i'd like to make it HA. I have two HP servers and will use SLES 11 SP4 64bit as OS. MySQL is 5.5.47. For HA i'd like to use pacemaker, which is available in SLES High Availibility Extension. I have experience in linux, but i'm not a database administrator nor developer. HA is important for us, we don't have performance problems. My first idea was to run the web app and the db in a virtual machine on the host and in case of a failure of one host pacemaker would run the vm on the other host. VM would be stored on a FC SAN. I stopped following this idea. I have bought a book about HA: "..." from Oliver Liebel. It's only available in german. But i can recommend it, it's very detailed and well explained. He proposed to have two hosts, and on each is running a MySQL instance as master AND slave. But it's not a "real multi master solution", because pacemaker takes care that the IP for the web app just points to one master. So i don't have the multi-master problems with concurrent inserts (i believe). His idea is that host A is master for the slave on host B, and host B is the master for the slave on host A. OK ? Let's imagining that the IP to the web app points to host A, inserts are done to the master on host A and replicated to the slave on host B. Now host A has problems, pacemaker redirects the IP to host B, and everything should be fine. What do you think about this setup ? Where is the advantage to a "classical Master/Slave Replication" ? How should i configure log-slave-updates in this scenario ? Let's imagine i have two hosts again: Host A is master, host B is slave. Nothing else. No real or pseudo "Multi-Master". IP points to host A. Host A has problems, pacemaker recognizes it, promotes B to master and pivot the IP. Everything should be fine. Where is the disadvantage of this setup compared to the "Multi-Master Replication" in the book ? The OCF ressource agent for mysql should be able to handle the mysql stuff and the RA for the IP pivots the IP. Now some dedicated questions to replication. I read a lot in the official documentation, but some things are not clear to me. In our db we have MyISAM and InnoDB tables. From what i read i'd prefer row based replication. The doc says is the safest approach. But there seems to be still some problems: The doc says: "For tables using the MYISAM storage engine, a stronger lock is required on the slave for INSERT statements when applying them as row-based events to the binary log than when applying them as statements. This means that concurrent inserts on MyISAM tables are not supported when using row-based replication." What does this exactly mean ? Concurrent inserts in MyISAM-tables are not possible if using RBL ? Or unsafe in the meaning they create inconsistencies ? "RBL (Row Based Logging) and synchronization of nontransactional tables. When many rows are affected, the set of changes is split into several events; when the statement commits, all of these events are written to the binary log. When executing on the slave, a table lock is taken on all tables involved, and then the rows are applied in batch mode. (This may or may not be effective, depending on the engine used for the slave抯 copy of the table.)" What does that mean ? Effective ? Is it creating inconsistencies ? Or just not effective in the sense of slow or inconvinient ? Or should i prefer MIXED for binlog_format ? The doc says: " If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped. This is true whether or not any temporary tables are actually logged. Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables." What does that mean ? Unsafe ? Causing inconsistencies ? Problem with SBL or RBL ? The doc says: "Due to concurrency issues, a slave can become inconsistent when a transaction contains updates to both transactional and nontransactional tables. MySQL tries to preserve causality among these statements by writing nontransactional statements to the transaction cache, which is flushed
Re: Simple test
On 6/18/2015 1:25 PM, AZ 9901 wrote: Hello, I only get SPAM answers when I try to post to this list so here is a simple message… Sorry… Marc. your message came through on gmane.comp.db.mysql.general just fine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: not replicating one table
On 10/15/2014 11:30 AM, a.sm...@ukgrid.net wrote: Quoting william drescher will...@techservsys.com: I read the manual forwards and backwards but I can't figure out how to set it up to replicate everything except the in memory table. Is this what you are after? http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-ignore-table thanks, Andy. Exactly THanks Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
not replicating one table
Ubuntu 14.04 LTS, MySQL 5.5.38 I have a setup with multiple tables in one database. One is a memory table. I have replication running to a remote computer for a hot backup. I read the manual forwards and backwards but I can't figure out how to set it up to replicate everything except the in memory table. Suggestions, hints, and links appreciated. -bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: INTO OUTFILE ERROR...
On 9/19/2014 10:59 AM, Don Wieland wrote: On Sep 19, 2014, at 7:50 AM, Reindl Harald wrote: the target folder don't matter that is clearly a *MySQL error* that your *MYSQL user* lack the needed permissions Yes i figured that - so the question is, how do I give full permissions to that user? I did go to my cPanel and delete the user and recreated the user adding them to the DB with ALL PRIVILEGES. Obviously this is not enough. If this is not a simple thing that I can do myself, I am willing to compensate someone for support they can offer via GoToMeeting and SKYPE. I am spinning my wheels and I need to resolve this issue asap. Please note: cpanel can create linux users. You need to create and manage the MySQL users. There is no relationship between the linux users and the MySQL users. -bill -- 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 )
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
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
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 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: fragmentation in innodb index
On 8/29/2014 5:51 AM, Johan De Meersman wrote: Senior Oracle and MySQL DBA Corporate Trainer and Database Security Am I the only one worried about that line, then? yes. I welcome help from anyone willing. Expertise and willingness both are important. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Replication problem
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: Help with cleaning up data
On 3/29/2014 2:26 PM, william drescher wrote: I am given a table: ICD9X10 which is a maping of ICD9 codes to ICD10 codes. Unfortunately the table contains duplicate entries that I need to remove. CREATE TABLE `ICD9X10` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `icd9` char(8) NOT NULL, `icd10` char(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `icd9` (`icd9`,`id`), UNIQUE KEY `icd10` (`icd10`,`id`) ) ENGINE=InnoDB AUTO_INCREMENT=671 DEFAULT CHARSET=ascii id icd9 icd10 25 29182 F10182 26 29182 F10282 27 29182 F10982 I just can't think of a way to write a querey to delete the duplicates. Does anyone have a suggestion ? bill Thanks for all the suggestions. I learned a lot, which is the most important part of the exercise. bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Help with cleaning up data
I am given a table: ICD9X10 which is a maping of ICD9 codes to ICD10 codes. Unfortunately the table contains duplicate entries that I need to remove. CREATE TABLE `ICD9X10` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `icd9` char(8) NOT NULL, `icd10` char(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `icd9` (`icd9`,`id`), UNIQUE KEY `icd10` (`icd10`,`id`) ) ENGINE=InnoDB AUTO_INCREMENT=671 DEFAULT CHARSET=ascii id icd9 icd10 25 29182 F10182 26 29182 F10282 27 29182 F10982 I just can't think of a way to write a querey to delete the duplicates. Does anyone have a suggestion ? bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: hypothetical question about data storage
On 7/26/2013 6:58 PM, Chris Knipe wrote: The issue that we have identified is caused by seek time - hundreds of clients simultaneously searching for a single file. The only real way to explain this is to run 100 concurrent instances of bonnie++ doing random read/writes... Your disk utilization and disk latency essentially goes through the roof resulting in IO wait and insanely high load averages (we've seen it spike to over 150 on a 8-core Xeon - at which time the application (at a 40 load average already) stops processing requests to prevent the server crashing). back in the day (many years ago) when I worked for IBM we had disk controllers that would queue and sort pending reads so that the heads would seek from low tracks across the disk to high tracks and then back to low. This resulted in very low seek _averages_. The controller was smart enough to make sure that if a write occurred, chronologically later reads got the right data, even if it had not been physically written to disk yet. Is there such a controller available now? bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Query help -
I have a table, schedule: CREATE TABLE `schedule` ( `schedule_id` mediumint(9) NOT NULL AUTO_INCREMENT, `provider` varchar(15) NOT NULL, `apptTime` datetime NOT NULL, `location` varchar(10) NOT NULL, `duration` smallint(5) unsigned NOT NULL, `standing_script` mediumint(9) DEFAULT NULL, `appt_status` char(1) NOT NULL, `patient_number` mediumint(9) NOT NULL, `notify` smallint(6) DEFAULT NULL, `comment` varchar(80) DEFAULT NULL, `history` varchar(200) DEFAULT NULL, `posted` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`schedule_id`), UNIQUE KEY `patient` (`patient_number`,`apptTime`,`schedule_id`), UNIQUE KEY `user` (`user`,`apptTime`,`schedule_id`), KEY `standing` (`standing_script`), KEY `posted` (`posted`,`user`,`apptTime`) ) ENGINE=InnoDB DEFAULT CHARSET=ascii; all of which can be ignored except for 'provider' and apptTime. I want to query the database and have the result be only the next appointment for 'patient_number' with each user (the doctor). eg: 2013-04-04 JSmith 2013-04-20 WJones where the database contains: 2013-04-04 JSmith 2013-04-10 JSmith 2013-04-17 Jsmith 2013-04-20 WJones 2013-04-24 JSmith etc I can get a list of future appointments for 1 patient, but can't figure out how to just get the first for each provider (there might be 1..5 providers) Any suggestions will be appreciated. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query help -
On 3/31/2013 7:32 AM, william drescher wrote: I have a table, schedule: CREATE TABLE `schedule` ( `schedule_id` mediumint(9) NOT NULL AUTO_INCREMENT, `provider` varchar(15) NOT NULL, `apptTime` datetime NOT NULL, `location` varchar(10) NOT NULL, `duration` smallint(5) unsigned NOT NULL, `standing_script` mediumint(9) DEFAULT NULL, `appt_status` char(1) NOT NULL, `patient_number` mediumint(9) NOT NULL, `notify` smallint(6) DEFAULT NULL, `comment` varchar(80) DEFAULT NULL, `history` varchar(200) DEFAULT NULL, `posted` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`schedule_id`), UNIQUE KEY `patient` (`patient_number`,`apptTime`,`schedule_id`), UNIQUE KEY `user` (`user`,`apptTime`,`schedule_id`), KEY `standing` (`standing_script`), KEY `posted` (`posted`,`user`,`apptTime`) ) ENGINE=InnoDB DEFAULT CHARSET=ascii; all of which can be ignored except for 'provider' and apptTime. I want to query the database and have the result be only the next appointment for 'patient_number' with each user (the doctor). eg: 2013-04-04 JSmith 2013-04-20 WJones where the database contains: 2013-04-04 JSmith 2013-04-10 JSmith 2013-04-17 Jsmith 2013-04-20 WJones 2013-04-24 JSmith etc I can get a list of future appointments for 1 patient, but can't figure out how to just get the first for each provider (there might be 1..5 providers) Any suggestions will be appreciated. --bill This will be a seldom used query and the schedule database is relatively small, so overhead is not a big deal. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query help - Solved
of course, Group By bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update doesn't
On 8/20/2012 10:09 AM, Mogens Melander wrote:
On Sun, August 19, 2012 18:19, william drescher wrote:
On 8/17/2012 12:13 PM, Rik Wasmus wrote:
I get 1 row affected, but the status does not change when I look
at the row.
If I set it to 'X' it does change.
To make it even more wacky, if I (using phpMyAdmin) change it to
'H' it will change and the row is shown change, but when I go to
examine the row (using the pencil icon=Edit) it changes back to 'W'.
Either there is something really strange or my mysql is possessed.
I am using Server version: 5.1.63-0ubuntu0.10.04.
Anyone have any thoughts about this or suggestions on how to
debug it?
1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show
any
that could be doing this?
2) However, in 99.999% of cases, it is just a logic error in the
application
(be it your application or PHPMyAdmin), not anything in MySQL. Can you
connect
with the command line client, run the UPDATE statement, en then check
what the
SELECT shows? If it shows a correct result... the problem ain't in MySQL
itself.
mysql select status from tasks;
++
| status |
++
| W |
++
1 row in set (0.00 sec)
mysql update tasks set status= 'H';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed 1 Warnings: 0
mysql select status from tasks;
++
| status |
++
| W |
++
1 row in set (0.00 sec)
whoops
bill
Maybe a SHOW CREATE TABLE `tasks`\g could shed some light.
I can't figure out how to get puTTY to log the session so I can't
cut and paste the output, but here is a phpMySQL export of the
table with the extra stuff typed in from the create table
task_id mediumint(9) NOT NULL AUTO_INCREMENT
status char(1) NOT NULL DEFAULT ''
priority char(1) NOT NULL
due_date_time datetimeNOT NULL
hold_date_time datetime NOT NULL
review_date_time datetime Default NULL
requestor varchar(10) NOT NULL
performer varchar(10) NOT NULL
repeat_frequency char(1) NOT NULL
repeat_time char(2) NOT NULL
repeat_from char(1) NOT NULL
task_title varchar(60) NOT NULL
description text Yes NULL
history text Yes NULL
function_to_run varchar(80) DEFAULT NULL
last_access datetime NOT NULL
completed datetime NOT NULL
notify tinyint(1) DEFAULT NULL
PRIMARY kEY('TASK_ID')
UNIQUE KEY 'performer' ('performer', 'status', priority',
due_date_time', 'task_id')
UNIQUE KEY 'requestor' ('requestor', prirority','due_date_time',
'task_id')
ENGINE=InnoDB AUTO_INCREMENT=312 DEFAULT CHARSET=ascii
As a workaround I changed status to task_status and now it
works just fine.
bill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: update doesn't
On 8/17/2012 12:13 PM, Rik Wasmus wrote: I get 1 row affected, but the status does not change when I look at the row. If I set it to 'X' it does change. To make it even more wacky, if I (using phpMyAdmin) change it to 'H' it will change and the row is shown change, but when I go to examine the row (using the pencil icon=Edit) it changes back to 'W'. Either there is something really strange or my mysql is possessed. I am using Server version: 5.1.63-0ubuntu0.10.04. Anyone have any thoughts about this or suggestions on how to debug it? 1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show any that could be doing this? 2) However, in 99.999% of cases, it is just a logic error in the application (be it your application or PHPMyAdmin), not anything in MySQL. Can you connect with the command line client, run the UPDATE statement, en then check what the SELECT shows? If it shows a correct result... the problem ain't in MySQL itself. mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) mysql update tasks set status= 'H'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed 1 Warnings: 0 mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) whoops bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update doesn't
On 8/19/2012 1:25 PM, Johnny Withers wrote: The client indicates a warning after the update. Issue a show warnings after the update. actually, it doesn't. but I did a show warnings and it replied: Empty Set (0.00 sec) I also did a show triggers and it replied: Empty Set (0.00 sec) On Aug 19, 2012 11:19 AM, william drescher will...@techservsys.com wrote: On 8/17/2012 12:13 PM, Rik Wasmus wrote: I get 1 row affected, but the status does not change when I look at the row. If I set it to 'X' it does change. To make it even more wacky, if I (using phpMyAdmin) change it to 'H' it will change and the row is shown change, but when I go to examine the row (using the pencil icon=Edit) it changes back to 'W'. Either there is something really strange or my mysql is possessed. I am using Server version: 5.1.63-0ubuntu0.10.04. Anyone have any thoughts about this or suggestions on how to debug it? 1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show any that could be doing this? 2) However, in 99.999% of cases, it is just a logic error in the application (be it your application or PHPMyAdmin), not anything in MySQL. Can you connect with the command line client, run the UPDATE statement, en then check what the SELECT shows? If it shows a correct result... the problem ain't in MySQL itself. mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) mysql update tasks set status= 'H'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed 1 Warnings: 0 mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) whoops bill -- 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: update doesn't
On 8/19/2012 5:56 PM, william drescher wrote: mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) mysql update tasks set status= 'H'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed 1 Warnings: 0 mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) whoops bill further information - interesting, it will accept X but not H mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) mysql update tasks set status= 'X'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed 1 Warnings: 0 mysql select status from tasks; ++ | status | ++ | X | ++ 1 row in set (0.00 sec) mysql update tasks set status= 'H'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed 1 Warnings: 0 mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) whoops However, If I fully qualify the col name it works. mysql select status from tasks; ++ | status | ++ | W | ++ 1 row in set (0.00 sec) mysql update Information_server.tasks set status= 'H'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed 1 Warnings: 0 mysql select status from tasks; ++ | status | ++ | H | ++ 1 row in set (0.00 sec) I suspect there is a problem naming a col status bill bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
update doesn't
I have a table (tasks) with: task_id mediumint(9) status char(1) priority char(1) and more fields when I do the following (using phpMyAdmin): update tasks set status='H' where task_id='1' I get 1 row affected, but the status does not change when I look at the row. If I set it to 'X' it does change. To make it even more wacky, if I (using phpMyAdmin) change it to 'H' it will change and the row is shown change, but when I go to examine the row (using the pencil icon=Edit) it changes back to 'W'. Either there is something really strange or my mysql is possessed. I am using Server version: 5.1.63-0ubuntu0.10.04. Anyone have any thoughts about this or suggestions on how to debug it? I discovered this because the application was failing to change the W to a H using update syntax. bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql friendly dates
On 4/16/2012 3:04 PM, Haluk Karamete wrote:
What's the right built in php date function formatting would be to take a
simple date, that's in a format like 05/16/1960 and turn it into a mysql
datetime datatype friendly format?
I don't mind H M S to be as 00:00:00/
wrote my own:
function US2ISODateTime($date) {
$dateTime = strtotime($date);
return date('Y-m-d H:i:00', $dateTime);
} //convert from US format to ISO format
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
two 5.1 servers, different behaviour
I am using DBvisualizer to inspect two MySQL 5.1 databases on two
different systems. The two different systems are nearly identical
in the O/S and the version of MySQL (5.1.58 vs 5.1.60). Both the
systems have MySQL configured in the same way (see below) and are
successfully using MySQL underneath a Perl CGI application.
mysql5.1.60% cat /usr/local/etc/mysql/my.cnf
[mysqld]
max_allowed_packet = 16M
#skip-networking
mysql5.1.58% cat /usr/local/etc/mysql/my.cnf
[mysqld]
max_allowed_packet = 16M
#skip-networking
I can successfully connect to the 5.1.60 server using DBvisualizer,
but I get the following well-known error when I try to connect to the
5.1.58 server on the other system:
com.mysql.jdbc.PacketTooBigException: Packet for query is too
large (7696217 1048576). You can change this value on the server
by setting the max_allowed_packet' variable.
I have contacted the support folks at DBvisualizer sending them the
following stack trace when the above exception occurs. They have no
clue and suggested I contact the MySQL community which I am now doing.
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Product: DbVisualizer Personal 8.0
Build: #1689 (2011/06/16 12:10)
Java VM: OpenJDK Client VM
Java Version: 1.6.0
Java Vendor: Sun Microsystems Inc.
OS Name: FreeBSD
OS Arch: i386
OS Version: 8.2-STABLE
13:32:29 [DEBUG AWT-EventQueue-1 DbVisualizerGUI.?] Init Seconds: 4.630 splash
display: 1.238 window display: 4.629
13:32:39 [DEBUG AWT-EventQueue-1 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG AWT-EventQueue-1 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG Thread-6 G.?] Connecting: myapp test/development server
13:32:39 [DEBUG Thread-6 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG Thread-6 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG pool-1-thread-1 D.?] RootConnection:
Driver.acceptsURL(jdbc:mysql://xxx.yyy.zzz.www:3306/myapp)
13:32:39 [DEBUG pool-1-thread-2 D.?] RootConnection:
Driver.connect(jdbc:mysql://xxx.yyy.zzz.www:3306/myapp, {user=,
password=})
13:32:39 [DEBUG pool-1-thread-2 D.?] RootConnection: EXCEPTION -
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (7696217
1048576). You can change this value on the server by setting the
max_allowed_packet' variable.
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
I could easily upgrade to 5.1.60 on the failing system, but I'd rather
not have to do that unless that is the called for solution.
I would be greatful for any hints or suggestions as to how I might go
about correcting this problem. Thank you in advance.
Regards,
web...
--
William Bulley Email: w...@umich.edu
72 characters width template -|
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 11:42: That error is coming from neither the MySQL server nor from DBVisualizer. That is coming from your JDBC driver. Check the version of that and research the effect of configuration options. http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html Thanks for the reply. I understood that the error came from the JBDC driver because of the Java class path given in the exception text: com.mysql.jdbc.PacketTooBigException But that isn't the issue. The same DbVisualizer instance works just fine when connecting to the MySQL 5.1.60 server on system A, but fails with the query too large exception when trying to connect to the MySQL 5.1.58 server on system B. It strains credulity to think that the same DbVisualizer instance would use two different JBDC drivers when the DbVisualizer connect string begins with jdbc:mysql://... in both cases. I found this file in my DbVisualizer installation directory: unix% cat /usr/local/share/dbvis/jdbc/mysql/README MySQL-AB JDBC Driver Version:5.1.16 Files: mysql.jar Reference: http://www.mysql.com DbVis Software AB is a MySQL Network Certified Partner and have the right to distribute the Connector/J driver. So what I can't explain is why it works for one and not the other. Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 12:58: I see your point. Configuration files aside, what do you get when you query the servers themselves with: show variables like 'max_allowed_packet' There *might* be something in your start-up scripts overriding that config setting. The query on the 5.1.60 server returned this: mysql show variables like 'max_allowed_packet'; ++--+ | Variable_name | Value| ++--+ | max_allowed_packet | 16777216 | ++--+ 1 row in set (0.00 sec) mysql And from the 5.1.58 server this: mysql show variables like 'max_allowed_packet'; ++--+ | Variable_name | Value| ++--+ | max_allowed_packet | 16777216 | ++--+ 1 row in set (0.01 sec) mysql This is what I exepected since the my.cnf files are configured identically on the two servers as I indicated earlier. BTW, this query was run by us yesterday in an attempt to debug this issue. At this point we were stumped and called it a day. :-( Today, I got the less than helpful from the support folks at DbVisualizer and then I contacted the MySQL community. :-) Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 13:23: At this point, I would not know what else to do except fire up wireshark and start debugging the packets. Well, doesn't that beat all, sigh... Now I'm back to square zero... :-( Are both servers on the same subnet? Negative. Is your DBVisualizer client local to either of these or on the same subnet as one and not another? The DbVisualizer client tool is local to the 5.1.60 server. You mentioned a minor version difference between the servers.. Have you read the relevant release notes between those versions? I think that might be a next step, but even more expedient would be upgrading the 5.1.58 server to 5.1.60 or 5.1.61 version and retest. This looks like an ugly one. I don't envy you. Gee, thanks for those words of encouragement - NOT! :-) Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Andrew Moore eroomy...@gmail.com on Fri, 02/24/12 at 14:03: To rule out a version issue have you tried another host with the problematic version and same/similar config? No, for two reasons: a) I just tumbled onto the idea of doing a version upgrade today, and b) the amount of work to do as you suggest is greater than simply upgrading the 5.1.58 server to 5.1.60 or 5.1.61 server Thanks for the reply! Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 14:10: *maybe* your router is doing some packet mangling? Wild shot in the dark.. That is worth investigating... :-) Thanks. Perhaps try hitting that 'local' server' from a remote client to see if the effect is the same? Is this the one misbehaving? This is also worth trying, but both of these attempts will have to wait until Monday when I can arrange the tests. What I meant to say was: I can't think of a better way to spend a weekend. Have fun! Weekend, no, but come Monday, I will be able to try out some of these concepts. I do, however, plan to have fun this weekend... ;^) Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: conditional updating
On 2/9/2012 8:58 AM, Reindl Harald wrote: Am 09.02.2012 14:55, schrieb william drescher: On 2/9/2012 8:22 AM, Johnny Withers wrote: Update table set mydate=now() where mydate='-00-00'; should do it. can't do that because the record is selected by other criteria. so explain the criteria, show us the query usually you do exatcly the same WHERE as for the select and add and mydate='-00-00' The query was to find a record by a unique ID, then update last access to now, and then update first access date to curdate() if it is -00-00. So, I obviously can not select the record based on first access as I need to update it regardless of the value of first access. IF() works like a charm. bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
conditional updating
I want to update a date field in a record. if the date in the field is -00-00 I want to change it to the current date. I would appreciate suggestions or links on how to do this. Yup, tried reading the manual, but need a bit of help. I will be updating another field at the same time. bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: conditional updating
On 2/9/2012 8:22 AM, Johnny Withers wrote: Update table set mydate=now() where mydate='-00-00'; should do it. can't do that because the record is selected by other criteria. Thanks bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: conditional updating
On 2/9/2012 8:18 AM, Michael Dykman wrote: untested: update set mydate = IF(mydate = '-00-00', now(), mydate) - michael dykman Thank you very much ! bill On Thu, Feb 9, 2012 at 8:14 AM, william drescher will...@techservsys.com wrote: I want to update a date field in a record. if the date in the field is -00-00 I want to change it to the current date. I would appreciate suggestions or links on how to do this. Yup, tried reading the manual, but need a bit of help. I will be updating another field at the same time. bill -- 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: setting the default of a date field
On 1/27/2012 6:00 PM, Peter Brawley wrote: On 1/27/2012 2:24 PM, william drescher wrote: On 1/27/2012 3:21 PM, Peter Brawley wrote: On 1/27/2012 11:18 AM, william drescher wrote: Is there a way to set the default of a date field to the date the record is generated ? bill Use a TIMESTAMP. Default DATE(TIME) columns with Triggers. PB When I try ALTER TABLE `ptInfo` CHANGE `lastQuarterlyReview` `lastQuarterlyReview` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP I get: #1067 - Invalid default value for 'lastQuarterlyReview' Yes, if you want a default value use a timestamp column. Thanks Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: setting the default of a date field
On 1/27/2012 3:21 PM, Peter Brawley wrote: On 1/27/2012 11:18 AM, william drescher wrote: Is there a way to set the default of a date field to the date the record is generated ? bill Use a TIMESTAMP. Default DATE(TIME) columns with Triggers. PB When I try ALTER TABLE `ptInfo` CHANGE `lastQuarterlyReview` `lastQuarterlyReview` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP I get: #1067 - Invalid default value for 'lastQuarterlyReview' bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
counting between dates across number of tables
I have three tables show below which I am querying in a number of ways. e.g. I have a report which lists number of reports provided compaired to number which were due over the duration of the project. I am puzzling over how to select the reports which were due and were delivered during a week based on frequency** and current date. I would like to produce an output based on the past week ending on a Friday (eg: if run on Tuesday 2nd it would still report on the previous week ending Friday 29th). I can mostly work out the due part based on the frequency ie: weekly due every week, fortnightly - I am using MOD to see if week is odd or even, monthly not sure yet but work out if this is the last week of the month I suppose. **project_cstm -- reportingfrequency will be something like weekly, fortnightly or monthly TIA W ===mixed workings === SELECT DATE_SUB(DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY),INTERVAL 7 DAY)AS startOfPeriod, DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY)AS endOfPeriod, CASE project_cstm.`reportingfrequency_c` WHEN WeeklyTHEN 1WHEN FortnightlyTHEN IF(MOD(week(project.`estimated_start_date`),2)=MOD(WEEK(DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY)),2),1,0)WHEN MonthlyTHEN 'month'ELSE 'bugger!'END AS reportDue, COUNT(notes.`parent_id`)AS deliveredReports, project_cstm.`reportingfrequency_c` AS reportFreaquency, project.`name` AS project_name FROM `project` project LEFT OUTER JOIN `notes` notes ON project.`id` = notes.`parent_id` LEFT OUTER JOIN `project_cstm` project_cstm ON project.`id` = project_cstm.`id_c` WHERE project.`deleted` = 0 AND project.`estimated_end_date` DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY) GROUP BY project.`id` table descriptions mysql describe notes; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | id | char(36) | NO | PRI | | | | date_entered | datetime | NO | | | | | date_modified| datetime | NO | | | | | modified_user_id | char(36) | YES | | NULL| | | created_by | char(36) | YES | | NULL| | | name | varchar(255) | YES | MUL | NULL| | | filename | varchar(255) | YES | | NULL| | | file_mime_type | varchar(100) | YES | | NULL| | | parent_type | varchar(25) | YES | | NULL| | | parent_id| char(36) | YES | MUL | NULL| | | contact_id | char(36) | YES | MUL | NULL| | | portal_flag | tinyint(1) | NO | | 0 | | | embed_flag | tinyint(1) | NO | | 0 | | | description | text | YES | | NULL| | | deleted | tinyint(1) | NO | | 0 | | +--+--+--+-+-+---+ 15 rows in set (0.00 sec) mysql describe project; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | id | char(36) | NO | PRI | | | | date_entered | datetime | NO | | | | | date_modified| datetime | NO | | | | | assigned_user_id | char(36) | YES | | NULL| | | modified_user_id | char(36) | YES | | NULL| | | created_by | char(36) | YES | | NULL| | | name | varchar(50) | NO | | | | | description | text | YES | | NULL| | | deleted | tinyint(1) | NO | | 0 | | | estimated_start_date | date | NO | | | | | estimated_end_date | date | NO | | | | | status | varchar(255) | YES | | NULL| | | priority | varchar(255) | YES | | NULL| | +--+--+--+-+-+---+ 13 rows in set (0.00 sec) mysql describe project_cstm; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id_c | char(36) | NO | PRI | | | | project_0bjective_1_c | varchar(255) | NO | | Insert text | | | reportingfrequency_c | varchar(100) | YES | | Weekly | | | account_id_c | char(36) | YES | | NULL| |
join to return first record for each patient number
I have two tables: PtActive ptNum // the patient's number user // the user who made this patient active expires// when the patient becomes inactive again primary index: PtNum PtName ptNum sequence lname fname primary index: ptNum, sequence The table PtName may have multiple rows with the same ptNum (if the patient changes his/her name. I am going mildly nuts trying to devise a query that will retrieve only the lowest ptName (ie: their current name) for all active patients for this user. in PHP I tried: $sql =select PtName.ptNum, lname, fname from PtName, PtActive where PtName.ptNum = PtActive.ptNum and PtActive.user = '$currentUser' order by PtName.ptNum, PtName.nameSequence ; but this retrieves all names for this patient. I tried: $sql =select distinct PtName.ptNum, lname, fname from PtName, PtActive where PtName.ptNum = PtActive.ptNum and PtActive.user = '$currentUser' order by PtName.ptNum, PtName.nameSequence ; but this retrieves all names for all active patients. I tried a subquery $sql =select ptNum, lname, fname from PtName where ptNum =(select ptNum from PtActive where PtActive.user = '$currentUser' limit 1); but this returns all the names for the first active patient. If I remove the limit 1, it fails with the error message Subquery returns more than 1 row Help ! bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Intro to indexing?
muhammad subair wrote: On Mon, Jul 27, 2009 at 9:25 PM, Ken D'Ambrosio k...@jots.org wrote: Hey, all. I'm trying to get indexing -- like, when do you specify an index name during index creation, is index use implicit or explicit, and, honestly, how exactly does it work, anyway? I've been RTFM'ing, but haven't found anything that really laid it out in black and white; usually, they'd give an example or two, but were awfully sparse on the whys and wherefores. So, if anyone has something they could point me to -- electronic or dead tree -- I'd be deeply appreciative. Thanks! Indexes can be on a single column or can span multiple columns (just like keys). An index will be used when running a query, if the search is being performed on the following: - A single column that has a single-column index for example, if we index departments on departmentID and perform a query like SELECT...WHERE departmentID=n. - A set of columns that forms a multicolumn index for example, if we have created an index on the employee.assignment table on (clientID, employeeID, workdate) and we perform a query like SELECT...WHERE clientID=x AND employeeID=y AND workdate=z. - A column or set of columns that forms a subset of a multicolumn index, as long as there is a leftmost prefix of the index columns for example, with the assignment table as before, with an index on (clientID, employeeID, workdate), indexes would be used for these types of queries: Source: MySQL Tutorial - SAMS Publishing When you set up the table, you must have a primary index. You make your best guess as to what would make it easier for the sql engine to find the data you want, but the sql engine, in its own wisdom will decide whether or not to use an index. You do not explicitly tell it to do a select using an index. Considerations: every time you do an insert, replace, or update of data included in an index, the index needs to be updated - which takes a small amount of time. indexes take space on disk - usually not a problem. if the engine can use an index, finding is much faster. if the database is small, who cares. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Could not start the service MySQL:Error 0
I suffered the same problem, and the following worked for me: http://www.andy.name.my/2009/03/cannot-create-windows-service-for-mysqlerror0/
Re: Re: Could not start the service MySQL:Error 0
Try looking in Control Panel-Administrative Tools- Services and deleting all the MySQL services.
Fw: mysql query, min, max with where conditions
Well, if your particular problem has a well defined maximum minimum and minimum maximum (Ie the max(q) 4294967296, because q is a 32 bit unsigned int, and min(q) -1) then you can do it without any extra joins or sub selects. select a, b, min(IF(date 100, q, 4294967296)) as min_q, max(IF(date100, q, -1)) as min_q, from a left join b left join c group by a.p - Original Message From: CRISTEA, Adrian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 9, 2008 9:33:37 AM Subject: mysql query, min, max with where conditions Hello there, What is the corect syntax for selecting something like: select a, b, (min(q) where date100), (max(q) where date100) from a left join b left join c group by a.p i need min() max() values each of them with other WHERE clause. How can I do that? Adrian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: No query specified
I'm guessing you are adding a semi-colon (;) to the end of the statement. Its unnecessary with the \G - Original Message From: Jeff Mckeon [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Thursday, December 6, 2007 12:19:22 PM Subject: Error: No query specified When I run a Show slave status \G I get a message at the bottom that says Error: No query specified I don't recall ever seeing this before and can't find anything online about it. Anyone know what it means? Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 845962457 Relay_Log_Space: 739790470 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: 0 1 row in set (0.01 sec) ERROR: No query specified mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
Re: innodb rollback question
Use smaller transactions that don't have 140 million rows. When attempting an action with important data, make sure you can survive the actions failure. If you can't, then you need to think of a different way of doing it that will allow a recoverable failure. - Original Message From: B. Keith Murphy [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 16, 2007 10:29:17 AM Subject: innodb rollback question I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++
If you are going to rely on obfuscation to protect valuable data, you might want to consider not posting the particular method you will use on a public mailing list. I think any method you implement will lower the overall security of the system. But, if you must search for encrypted text, you could have another representation of the text salted and hashed word for word. Then salt and hash each search word and search for it in the hashed text. You're still leaking information about word popularity if you do this which may help a determined attacker. - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: mos [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, October 26, 2007 3:54:11 PM Subject: Re: How to encrypt Text and still be able to use full text search? 3rd Attempt ++ I also need to protect a couple dozen Float fields and thought I could obscure them a bit by adding an offset to them based on an encrypted id stored with each row. It is not going to be as good as encryption but will help to obfuscate the data. How much will obfuscation save you? Are you saving nickels and dimes to protect millions of dollars? I've seen people get burned by rolling their own encryption (I could tell you a great war story about a consultant I worked with who invented encryption for SSNs in a database). An insurance policy is something else to consider. Heck, buy the insurance and do weak obfuscation, then get the insurance money and go to Mexico. ... I could put strychnine in the guacamole... Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Deadlocks with High Concurrency SELECT FOR UPDATE
Hello List, I have this table that has a single row in it: CREATE TABLE `quicktable` ( `x` int(11) NOT NULL auto_increment, `quick_id` int(11) NOT NULL default '0', PRIMARY KEY (`x`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 select * from quicktable; +---+--+ | x | quick_id | +---+--+ | 1 |0 | +---+--+ 1 row in set (0.00 sec) I have a large number of connections executing these queries: BEGIN: SELECT quick_id FROM quicktable FOR UPDATE; COMMIT; This works well until I hit a large number of concurrent connections (around 200), when I start getting deadlocks. Despite the fact, that I'm only selecting a single table. Here is the deadlock section from SHOW INNODB STATUS; LATEST DETECTED DEADLOCK 071015 20:22:35 *** (1) TRANSACTION: TRANSACTION 0 79790779, ACTIVE 2 sec, process no 7658, OS thread id 1185077584 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 368 MySQL thread id 3961, query id 2102790 10.1.10.122 bnewton statistics SELECT x, quick_id FROM quicktable WHERE x=1 FOR UPDATE *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790779 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) TRANSACTION: TRANSACTION 0 79790775, ACTIVE 2 sec, process no 7658, OS thread id 1191733584 2 lock struct(s), heap size 368 MySQL thread id 4094, query id 2102743 10.1.10.122 bnewton *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790775 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79791014 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH *** WE ROLL BACK TRANSACTION (2) Can anyone explain whats going on? Is there a limit for the number of concurrent transactions, before looking at the lock graph becomes too expensive? Is that documented somewhere? Thanks, William Newton Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. http://farechase.yahoo.com/
Full Innodb Table Locks deadlocking with AUTO_INC locks.
FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `test/quicktable` trx id 0 26382733 lock mode AUTO-INC waiting -- ---TRANSACTION 0 26382732, ACTIVE 17 sec, process no 6820, OS thread id 24264721 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320 MySQL thread id 1472, query id 24517 localhost bob Table lock LOCK TABLES quicktable WRITE FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 754 OS file reads, 14786 OS file writes, 14479 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.56 writes/s, 0.33 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 5, seg size 7, 2 inserts, 2 merged recs, 1 merges Hash table size 69257, used cells 491, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 3746566754 Log flushed up to 0 3746566754 Last checkpoint at 0 3746566754 0 pending log writes, 0 pending chkp writes 14298 log i/o's done, 0.11 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 36830648; in additional pool allocated 2097152 Buffer pool size 1024 Free buffers 125 Database pages 897 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 876, created 21, written 450 0.00 reads/s, 0.00 creates/s, 0.33 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 31444, id 131081, state: waiting for server activity Number of rows inserted 7054, updated 0, deleted 126, read 589 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT Does any one know whats going on? Is this expected behavior? Thanks in advance, William Catch up on fall's hot new shows on Yahoo! TV. Watch previews, get listings, and more! http://tv.yahoo.com/collections/3658
Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.
Thanks for the quick reply Barron, but doesn't SET AUTOCOMMIT = 0; disable
AUTOCOMMIT ?
from http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html :
The correct way to use LOCK TABLES and
UNLOCK TABLES with transactional tables,
such as InnoDB tables, is to set
AUTOCOMMIT = 0 and not to call
UNLOCK TABLES until you commit the
transaction explicitly. When you call LOCK
TABLES, InnoDB internally takes
its own table lock, and MySQL takes its own table lock.
InnoDB releases its table lock at the
next commit, but for MySQL to release its table lock, you
have to call UNLOCK TABLES. You should
not have AUTOCOMMIT = 1, because then
InnoDB releases its table lock
immediately after the call of LOCK
TABLES, and deadlocks can very easily happen. Note
that we do not acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.
So it says deadlocks can happen very easily if AUTOCOMMIT=1, but we do not
acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.
In this particular situation the deadlock does not happen if AUTO COMMIT =1,
but that could cause other deadlocks.
Am I confused, or is it that really unclear?
- Original Message
From: Baron Schwartz [EMAIL PROTECTED]
To: William Newton [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, October 2, 2007 12:05:41 PM
Subject: Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.
Hi William,
William Newton wrote:
Hello List,
I discovered an unusual problem with the way Innodb handles the AUTO_INC lock
with a full table lock. I was wondering if this is a known issue, or I'm
doing something completely wrong. I'm working with MYSQL Server version:
5.0.42-debug-log on Gentoo Linux.
So lets say I have this table:
CREATE TABLE `quicktable` (
`x` int(11) NOT NULL auto_increment,
`quicktext` varchar(50) default NULL,
PRIMARY KEY (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Several connections are inserting concurrently to the table with normal
single statements such as:
INSERT INTO quicktable (quicktext) VALUES ('Bob 25 item 5');
The value inserted into quicktable changes for every insert to indicate which
connection and which insert its doing
Now in a unique connection is locking the table using the INNODB suggested
method:
SET AUTOCOMMIT = 0;
LOCK TABLES quicktable WRITE;
For demonstration purposes this thread sleeps for a second to simulate
processing that might be going on in the application.
After 1 Second:
COMMIT;
UNLOCK TABLES;
SET AUTOCOMMIT =1;
The result is a dead lock where all queries wait until one of the INSERT's
times out then the LOCK statement manages to get the table lock. But it
happens repeatedly with as few as two connections sending inserts. The server
can handle many many more concurrent inserts if the lock is removed , with
out resulting in any locks.
Here is the output of show processlist:
SHOW PROCESSLIST;
+--+--+---++-+--+++
| Id | User | Host | db | Command | Time | State | Info
|
+--+--+---++-+--+++
| 1470 | bob | localhost | test | Query | 19 | update | INSERT INTO
quicktable (quicktext) VALUES ('Bob 26816 item 5') |
| 1471 | bob | localhost | test | Query | 19 | Locked | INSERT INTO
quicktable (quicktext) VALUES ('Bob 6817 item 2') |
| 1472 | bob | localhost | test | Query | 19 | Locked | LOCK TABLES
quicktable WRITE |
+--+--+---++-+--++--+
SHOW INNODB STATUS:
=
071002 16:51:55 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 9 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 91, signal count 91
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 170, OS waits 85; RW-excl spins 7, OS waits 6
LATEST DETECTED DEADLOCK
071002 16:51:37
*** (1) TRANSACTION:
TRANSACTION 0 26382727, ACTIVE 0 sec, process no 6820, OS thread id 24264721
setting table lock
mysql tables in use 1, locked 0
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1472, query id 24493 localhost bob System lock
LOCK TABLES quicktable WRITE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test
Re: how would mysqld restart affect dynamically set global variables?
On Wed, March 14, 2007 9:35, Bing Du said: Hi, We're running mysql 4.1.20. If I understand the manual correctly, I can change max_connections while mysqld is running without restart mysqld to make the change take effect. But what if mysqld restarts later in some other situations, like machine reboot, would my (global) change on max_connections remain? I'm just very clear when to make dynamic changes and when is better to put changes in my.cnf. Please advise. Thanks in advance, Bing Put the changes in dynamically. If they work change the my.cnf to make them survive the next time mysql is restarted (for example on a reboot). --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENCODE() and DECODE()
On Mon, March 12, 2007 8:04, Neil Tompkins said: I've been researching the best method to store credit card numbers in a database which are saved encrypted. My version of mysql is 3.23 therefore I think the only function I could use is ENCODE() and DECODE(). I've tried these functions and they appear to work as I want. I've a couple of questions though, can I use varchar when saving the data and are these functions suitable for my requirements ? Thanks, Neil use 'Bin' versions of fields since the encoded data may be binary. Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practice: Timezones and web development
On Tue, March 6, 2007 9:40, Marcus Bointon said: On 6 Mar 2007, at 17:12, David T. Ashley wrote: Best practice is that all times maintained in a database (or anywhere on the server) are UTC, and are only converted to local timezone and/or adjusted to daylight savings time as required to display data for a specific user. Exactly right. Now, as far as the best way to implement the two paragraphs above (especially with DST), I have not a clue. I do this using the date extension that was updated in PHP 5.1. I store the string representation of the time zone, for example 'Europe/ London', and set that as the time environment whenever a session is started using: http://www.php.net/manual/en/function.date-default- timezone-set.php After that it all just magically works - whenever you call date() and friends, it's all corrected for the time zone. A slightly harder question is how to get the user's time zone in the first place. You can take a wild guess according to their IP, but it could well be wrong. Next you could use Javascript to find out the local time and get an offset, but then you have no way of getting DST info, and it doesn't tell you where they really are. Finally, you can just ask - I've made the time zone a user preference, and most systems I've seen do the same. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ Added problem. What if their computer clock is way off. Before I figured out session cookies I had cookies disappearing on random computers because the computer clock was a couple of days off. FWIW. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch - easy check
On Wed, February 28, 2007 14:10, Ryan Stille said:
I am on 4.1.20-1.
Maybe your OS isn't patched?
Try this: SELECT @@global.time_zone;
Won't help if you are on debian which is still on 4.0.
If you get back SYSTEM, then MySQL is looking to the OS for timezone
data. And its only loaded when MySQL starts, so if you haven't
restarted MySQL since you patched your OS, you need to do that.
-Ryan
Jerry Schwartz wrote:
What version of MySQL are you using? I'm running 4.1.21, and that check
doesn't work even after I've updated (I think) the time zone tables.
I should probably eyeball the output of mysql_tzinfo_to_sql.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Ryan Stille [mailto:[EMAIL PROTECTED]
Sent: Saturday, February 24, 2007 4:28 PM
To: mysql@lists.mysql.com
Subject: Re: MySQL Daylight Savings Time Patch - easy check
Ryan Stille wrote:
Paul DuBois wrote:
At 4:40 PM -0600 2/20/07, Ryan Stille wrote:
Is there an easy way to test to see if MySQL already has
the proper
tables loaded?
-Ryan
Yes, reload them. :-) After that, they're current! ...
After digging around on the net for a while I found an easy
way to tell
if your MySQL installation is ready for the new daylight savings time.
SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),
UNIX_TIMESTAMP('2007-03-11 03:00:00');
This should return the same value, even though you are feeding it
different times, because this is when the 1 hr change occurs.
I get the
correct result on both of my machines. On one of them I've run the
suggested |mysql_tzinfo_to_sql command, on the other, the time zone
tables are completely empty!
Any wisdom on these time zone tables - are they ever used, should I
populate them or not?
-Ryan
|
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: RE: [PART 2/2] InnoDB - Different EXPLAINs for same query]
Please post to the list not to me personnally. Original Message Subject: RE: [PART 2/2] InnoDB - Different EXPLAINs for same query From: John Anderson [EMAIL PROTECTED] Date:Thu, January 18, 2007 10:24 To: William R. Mussatto [EMAIL PROTECTED] -- I optimized every table after I first imported the data. The tables were probably in use, off and on for testing, for about a week after the optimize table was ran on every table before I noticed this problem. I'm not saying the problem didn't exist within that week, I'm just saying I didn't notice it ;) . Another thing. Does the query optimizer keep any sort of statistics and use them to make decisions for future queries on the same table? If so, then that could be the problem because we have certain fields, containing only numbers, but were previously setup as varchars for some unknown reason. I changed them all to int types but some queries in obscure parts of our applications are still querying this field as if it were a character field, using LIKE, etc. I'm slowly but sure tracking those down and fixing them, I'm just curious if that could have anything to do with this strange behavior. Thanks, John A. -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 5:17 PM To: mysql@lists.mysql.com Subject: Re: [PART 2/2] InnoDB - Different EXPLAINs for same query Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: mysql SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 1 | SIMPLE | rb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | sb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | ser | ref| PRIMARY,billedCurrencyCode | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | cur | eq_ref | PRIMARY | PRIMARY | 2 | global.ser.billedCurrencyCode |1 | Using index
Re: [PART 2/2] InnoDB - Different EXPLAINs for same query
for the customerdetail table which has much better cardinality (almost 1 key per row). The obvious workaround is use FORCE INDEX(accno_trans_idx) in the query for now, but I'm beginning to think this may be a bug. Has anyone else had any similar issues? I haven't found anything like this in the bug database though. If anyone doesn't see anything blatantly wrong with my setup, I'll submit this as a bug. Further information: Here is how MySQL is configured, and the my.cnf I'm using. CC=gcc -m64 CXX=g++ -m64 \ ./configure --prefix=/usr \ --sbindir=/usr/sbin \ --libexecdir=/usr/sbin \ --infodir=/usr/share/man \ --mandir=/usr/share/info \ --libdir=/usr/lib64 \ --enable-shared \ --enable-static \ --enable-thread-safe-client \ --enable-local-infile \ --with-extra-charsets=all \ --with-gnu-ld \ --with-pthread \ --with-unix-socket-path=/tmp/mysql.sock \ --with-mysqld-user=mysql \ --without-debug \ --with-openssl=/usr \ --with-big-tables \ --with-archive-storage-engine \ --with-csv-storage-engine \ --with-blackhole-storage-engine \ --with-federated-storage-engine \ --with-berkeley-db \ --with-berkeley-includes=/usr/include \ --with-berkeley-libs=/usr/lib64 \ --without-extra-tools \ --with-mysqlmanager=no \ --with-ndbcluster \ --without-geometry -- [mysqld] #Directories datadir=/var/db/mysql socket=/tmp/mysql.sock log-error=/var/log/mysql/mysql.log pid-file=/var/run/mysqld/mysqld.pid tmpdir=/tmp #Replication server-id=127 #log-bin replicate-ignore-db=mysql #log-slave-updates #Network max_connections=1024 max_allowed_packet=1024M net_buffer_length=16k #Files open_files_limit=8192 # Anything higher needs corresponding ulimit entry #Buffers join_buffer_size=128M key_buffer_size=512M key_buffer=512M large_pages max_heap_table_size=1024M myisam_sort_buffer_size=256M read_buffer_size=64M read_buffer=64M query_cache_size=32M query_cache_type=1 record_buffer=512 sort_buffer=512M table_cache=512 thread_cache=4M thread_stack=512K thread_cache_size=300 thread_concurrency=16 tmp_table_size=1G #innodb innodb-table-locks=off transaction_isolation=REPEATABLE-READ innodb_buffer_pool_size=1024M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_file_size=128M innodb_additional_mem_pool_size=32M innodb_thread_concurrency=16 innodb_commit_concurrency=4 innodb_flush_method=O_DIRECT innodb_open_files=8192 innodb_sync_spin_loops=32 innodb_thread_sleep_delay=1000 innodb_autoextend_increment=1024M innodb_file_per_table=TRUE [client] socket=/tmp/mysql.sock John Anderson --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: LAMP appliance for non-profit use
Have you looked at ubuntu distribution of linux. I think they have a 'school' variant which might be close to what you need. Also, the people there might be able to give you more help. Good luck. On Thu, December 7, 2006 9:39, Saqib Ali said: Hello All, I know this is little bit off-topic but I think users of this group can give good advice on this topic. We have a in-house built application using MySQL + PHP + Apache. It is essentially a elaborate collection of online forms that will be used for managing school scholarship in the 3rd world countries. The student/teachers will either come to the office or fill out the forms online. We have identified few pilot location and would like to deploy this application. However due to lack of IT support at the location we have decided to use a pre-configured appliance which support the LAMP stack. All we want to do is directly ship the appliance to the location, and remotely install our app. We want the appliance to require minimal maintenance and administration, and have a auto-update feature to get security patches etc. I had previously worked on a similar project and used Sun Cobalt 550 servers. But now I think Sun has stopped making them and you can only buy them refurbished and the auto-update features are severely limited. Any other suggestion??? We want the appliance to cost us $1,000 / unit. Thanks saqib http://www.full-disk-encryption.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-text searching with quoted bind variables
I am using Full-Text searching with In Boolean Mode. I am generating
my query by using binding parameters. If a user types in a quoted
string on the search form (in order to match that string as-is), the
binding mechanism escape it with a backslash. The query runs fine and
it appears to return the same results when I run the same query by
hand without the backslashes. I just want to make sure these
backslashes are not being interpreted differently and that the
full-text search engine is treating the double-quotes as they are
intended (and not looking for them literally)
E.g.
... AND MATCH (title, description) AGAINST ('chev* \malibu\' IN BOOLEAN MODE)
vs
... AND MATCH (title, description) AGAINST ('chev* malibu' IN BOOLEAN MODE)
They both seem to be Ok, but I just want to make sure now, before
wierd problems come up later...
thanks
-william
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
test email
Sorry, but I haven't gotten any emails from the list and I'm trying to determine if there is a blockages. Sorry to disturb the list. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
crystal reports andd mysql
hello everybody anybody had to deal with this? i have a windows form created on VB.net to get a report on embbeded crystal reports develop program im trying to customize the fields to show by using the CR form via Sql Sintax option for the fields but the formula editor seems does not understand mysql syntax any coments? please hlp!!
Tej Kohli, Software Tej Kohli, Software MYSQL
Tej Kohli, Software http://www.tejkohlifund.com http://www.tej-kohli-foundation.com http://www.tejkohlimagazine.com http://www.tej-kohli-magazine.com http://www.tejkohli-news.com http://www.tejkohlionline.com http://www.tejkohli-online.com http://www.tejkohlitoday.com Today noted entrepreneur and philanthropist, Tej Kohli, announced the establishment of the Kohli Scholarship Fund for underprivileged kids. Mr Kohli has agreed to donate $1M dollars to the scholarship fund and has committed to provided at least an additional $100,000 per quarter for the next 20 years. The fund will be used to provide partial scholarships to underprivileged Central American children who have achieved academic excellence and demonstrated an interest and aptitude in mathematics and computer sciences. Graduating seniors may apply during the 2006-2007 academic year for scholarships to be awarded in their freshman year of college. Any interested student should contact _@ for more information. On a related note, Tej Kohli has been supporting 87 children and taking care of their education for the past 5 years. These are the children of poor families from the Guanacaste province in Costa Rica. Tej Kohli resides in Henley on Thames, England where he lives with his wife and two small children. He is an IIT graduate and is a member of MENSA. Mr. Kohli has business interest in several countries and is a principal in a privately owned gaming group. Sponsors: http://www.mylasikweb.com Boothe Lasik http://www.mylasikweb.com/ Dr William Boothe http://www.mylasikweb.com/ Dr Boothe http://www.mylasikweb.com/ Lasik Boothe http://www.mylasikweb.com/ Boothe http://www.mylasikweb.com/ Boothe Lasik http://www.mylasikweb.com/ Dr William Boothe http://www.mylasikweb.com/ Dr Boothe http://www.mylasikweb.com/ Boothe http://www.mylasikweb.com/ Lasik Boothe http://www.mylasikweb.com/ Boothe Lasik http://mylasikweb.com/lasik.html Dr William Boothe http://mylasikweb.com/lasik.html Dr Boothe http://mylasikweb.com/lasik.html Boothe http://mylasikweb.com/lasik.html Lasik Boothe http://mylasikweb.com/lasik.html Boothe Lasik http://mylasikweb.com/restor.html Dr William Boothe http://mylasikweb.com/restor.html Dr Boothe http://mylasikweb.com/restor.html Boothe http://mylasikweb.com/restor.html Lasik Boothe http://mylasikweb.com/restor.html Boothe Lasik http://mylasikweb.com/technology.html Dr William Boothe http://mylasikweb.com/technology.html William Boothe http://mylasikweb.com/technology.html Boothe http://mylasikweb.com/technology.html Lasik Boothe http://mylasikweb.com/technology.html Boothe Lasik http://mylasikweb.com/about.html.html Dr William Boothe http://mylasikweb.com/about.html.html William Boothe http://mylasikweb.com/about.html.html Boothe http://mylasikweb.com/about.html.html Lasik Boothe http://mylasikweb.com/about.html.html
Glenn Kawesch Dr. Kawesch is the surgical director of Kawesch Lasik-
Glenn Kawesch Dr. Kawesch is the surgical director of Kawesch Lasik--one of Southern California's most experienced refractive surgery facilities. We have been specializing in refractive surgery since 1989 and have completed approximately 25,000 procedures. Dr. Kawesch attended medical school at Northwestern University Medical School in Chicago. He performed important research in Ophthalmology at the world famous Jules Stein Eye Institute at UCLA. He performed his internship at UCLA and his Ophthalmology at UC San Diego. http://www.kaweschlaser.com/ http://www.kaweschlaser.com/ http://www.kaweschlaser.com/ http://www.kaweschlaser.com/ 1-888-215-2020 Call today for more information! San Diego Office 4520 Executive Drive San Diego, CA 92121 Los Angeles Office 11600 Wilshire Blvd, Suite 120, Los Angeles, CA 90025 Dr Kawesch Dr Glenn Kawesch Glenn Kawesch Glenn Kawesch Lasik Lasik Kawesch Kawesch Lasik Surgeon Kawesch Eye Surgeon Kawesch Laser Center What Are The Goals Of Lasik Surgery? The goal of LASIK is to reduce or eliminate your dependence on corrective lenses. By improving the uncorrected vision in patients, LASIK allows patients to go about their daily routine and enjoy life without the need for corrective lenses. How Does Lasik Treat Nearsightedness and Farsightedness? Your eye is anesthetized with eye drops (injections are not necessary). We then use a precise motorized instrument called a microkeratome to create a hinged flap of tissue on the center of the cornea. This flap of tissue is lifted back and the excimer laser is used to sculpt a new flatter surface under this flap. For farsightedness, the laser sculpts a new steeper surface. The flap is then replaced and adheres without requiring any stitches. People sit up from their procedure and can see better immediately. What is No-Cut Lasik? At Custom Laser Center, we're happy to also offer Epi-LASIK, the no-cut LASIK eye surgery procedure. The difference between LASIK and Epi-LASIK is that Epi-LASIK involves no blades or alcohol in the procedure. Epi-LASIK is a less abrasive procedure that many find preferable to traditional LASIK eye surgery. With Epi-LASIK eye surgery, available at our Los Angeles and San Diego offices, a doctor can significantly reduce recovery time. Epi-LASIK is especially recommended for those who have extremely sensitive eyes and are concerned with the effects of putting alcohol on their eyes, or having an incision made. Another benefit of Epi-LASIK is that it's an outpatient procedure. Since we numb your eyes with anesthetic drops, there will be no pain, no needles, and no injections. Epi-LASIK takes only seconds, and you'll be able to see right away. What is Wavefront, or Custom Lasik? Corneas are like fingerprints, just as unique and intricate. They have lumps, bumps, ridges and grooves. These shapes create distortions that can't be treated with glasses or contacts. Even some standard LASIK treatments can only correct so much. That's why Custom Laser Center employs a device called a Wavefront Analyzer, which sends multiple parallel beams of light into the eye. These beams get distorted as they bounce through the cornea, off the retina, and back again. These distortions are registered by the analyzer and used to direct the laser specifically to those areas causing them. These distortions are then targeted and corrected, frequently resulting in better than 20/20 vision. To learn more about the latest in refractive surgery and LASIK eye surgery, contact our San Diego or Los Angeles office to schedule your free consultation. Simply click on the link to the right, or call our staff at 888-215-2020. Find out instantly if you qualify for our amazing LASIK financing options. [EMAIL PROTECTED] 1-888-215-2020 San Diego Office 4520 Executive Drive San Diego, CA 92121 Los Angeles Office 11600 Wilshire Blvd, Suite 120, Los Angeles, CA 90025
Re: Alter Table Add Column - How Long to update
On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? I'm using InnoDB by the way Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
excel and Mysql?!
hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers
Re: Simple JOIN on three tables
On Wed, October 18, 2006 12:46, spacemarc said: Hi I have 3 tables with the same fields. I would want to find the data that they are comprised in the time interval: SELECT a.*, b.*, c.* FROM tab1 a, tab2 b, tab3 c WHERE a.date between '-MM-DD' and '-MM-DD' OR b.date between '-MM-DD' and '-MM-DD' OR c.date between '-MM-DD' and '-MM-DD' ORDER BY a.date DESC But this query returns all the fields duplicated. Where it is mistaken? -- http://www.spacemarc.it Well that's what you asked it to do, if you look closely at your query. How are table a, b, and c related to each other? Do you want all the information from each of the tables (which are NOT related to each other) between the specified dates? If that is the case you are looking at a UNION rather than a strait JOIN. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
On Wed, October 18, 2006 13:21, spacemarc said: ok, instead I use (SELECT * FROM tab1 WHERE mydate between 'the-date1' and 'the-date2' ) UNION (SELECT * FROM tab2 WHERE mydate between 'the-date1' and 'the-date2' ) etc But if I wanted to use a join I can make it however or not? -- http://www.spacemarc.it A JOIN implies that the tables are related, that is, a column (or columns) in table a has the same value as a column (or columns) in table b. Thats what they mean when they call it a RELATIONAL database. If the tables don't have such a relationship you are asking for the UNION of the separate information from table a, where it meets its WHERE conditions, and the information from table b where it meets its WHERE conditions. The fact that both where conditions are the same doesn't matter. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
On Wed, October 18, 2006 13:37, spacemarc said: 2006/10/18, Peter Brawley [EMAIL PROTECTED]: You can join on any row(s) you like. What are you trying to acccomplish? I simply want to select the records from my three tables that are comprised in time interval. Now, the first table comprises the records until to 2004 year; the second table unitl 2005 and the third table until 2006. If a user select a date (example) between 2005 and 2006 I want to select the data from two tables -- http://www.spacemarc.it Then it is really a UNION. I hope you have the date field as an index otherwise you are looking at a table scan which is always slow. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: How to rewrite query
Would it not be best to have the field with the fewest repeats (i.e., the closest to unique) first, or is that what you meant. Bill On Tue, October 17, 2006 10:12, Jerry Schwartz said: I didn't think of that (combinations). You are probably right. Due to my background, I tend not to think a lot about multi-column indices. I would think that you want field with the most possible values first, then the next, etc. Is that what you were thinking? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: I would think that with so few possible values for all but the ip field, indexing the other fields would accomplish nothing. In fact, I'd be surprised if the optimizer didn't realize that and do a sequential read anyways. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query At 08:34 AM 10/17/2006, you wrote: Hello, For the Radius server we're using MySQL cluster and the following query looks too slow: select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; Table ipaddr is small (~6MB, 38000 records). Fields in WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data time wrong format between mysql 5.01 and visual.net
hello i'm developing a program on visual.net 2003 where i need to extract data from a date time format column working with mysql.net component for extract via mysqlcommands and dataadapters im fillin datagrills but the column displayed only presents the date but not the time im still analizing data format presentation on viusual.net elements but it seems something is missing any comments? tanks a lot
Re: Windows Server Configuration
Just noticed that you said partitions. I am assuming that you meat multiple drives in a raid array. Bill David Lazo said: Thanx again. For the time being, we will keep 4 drives with Dan's suggestion. OS and MySQL running from there. On 8/25/06 11:03 AM, Dan Buettner [EMAIL PROTECTED] wrote: James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: file i/o operations...
A couple of comments: - Simultaneous connections can be increased, but at some point the user than runs the mysqld process will run out of file handles it can allocate (each table takes 2 or 3). - If we are talking about a database server and test server being the same box then what are you trying to test. Once you exceed the number of processors on the box, the OS will just queue up the various processes and that will be the limit of scalablity. Unless you overlap real I/O with computation there is not much gain beyond a certain point. When you run out of memory for processes, its page to disk time (not a pleasent site). Not sure what you are testing here. BTW: please expain the 'black hole table'. Jut my $0.1 worth. Bill Brent Baisley said: Just getting that number of processes running I think would be a challenge. A setup I recently worked on runs a few hundred processes per box, and that kind of maxes out the CPU. Approach 1, been there, done that. Too messy. Approach 2, considered it, but you may end up with processes that never connect. You would need a queueing/scheduling mechanism. Essentially you would be trying to do what an OS does, manage resources to make sure every process gets it's turn. Approach 3, what we currently use. The processes connect to the db, does a bulk insert and then disconnects. We decided to limit each process to blocks of 100. Inserting a single record at a time will quickly degrade. This setup actually moved the bottleneck from the database to the processes doing their job. When each process starts, it inserts a record into a table and gets it's id. The process then handles the autoincrement value. The unique id for each record is then the process id plus the increment value. To really scale, you may want to look into the black hole table format. Essentially it's a black hole, nothing is saved so there really isn't much overhead. But you set it up to be replicated and a replication log is generated. An easy setup would be to have multiple tables on a master server, each table replicating a black hole table from another server. Then create a merge table encompassing the multiple tables for easy querying. This is the next idea we are pursueing, so it may or may not work. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, August 25, 2006 1:12 PM Subject: file i/o operations... hi... i'm trying to determine which is the better way/approach to go. should an app do a great deal of file i/o, or should it do a great deal of read/writes to a mysql db... my test app will create a number of spawned child processes, 1000's of simultaneous processes, and each child process will create data. the data will ultimately need to be inserted into a db. Approach 1 --- if i have each child app write to a file, i'm going to have a serious hit on the disk, for the file i/o, but i'm pretty sure Centos/RH could handle it. (although, to be honest, i don't know if there's a limit to the number of simultaneous file descriptors that the OS allows to be open at the same time.) i'm assuming that the number is multiples of magnitudes more than the number of simultaneous connections i can have with a db i could then have a process/app collect the information from each output file, writing the information to the db, and deleting the output files as required. Approach 2 -- i could have each child app write to a local db, with each child app, waiting to get the next open db connection. this is limited, as i'd run into the max connection limit for the db. i'd also have to implement a process to get the information from the local db, to the master db. .. Approach 3 --- i could have each child app write directly to the db.. the problem with this approach is that the db has a max regarding the number of simultaneous connections, based on system resources. this would be the cleanest solution.. so... anybody have any thoughts/comments as to how one can essentially accept 1000's-1's of simultaneous hits with an app... i've been trying to find out if there's any kind of distributed parent/child/tiered kind of app, where information/data is more or less collected and received at the node level... does anyone know of a way to create a distributed kind of db app, where i can enter information into a db on a given server, and the information is essentially pulled into the master server from the child server... thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: 1 to many relationships
Gerald L. Clark said: Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table Then it is many to many. Many customers are associated with many issues. i have Customers /Issues as tables, -- Gerald L. Clark Supplier Systems Corporation I beg to differ. It would be a many to many if several customers were related to the same issue. If each customer can have one or more issues, but no issue can 'belong' to more than one customer then its 1 to many and you can put the customer ID in the issues table. If several customers can have the same issue (row) then you will need an intermediate table which has two columns: customerID and issueID. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Incrementing using Max(Field) ?
Thank you very much! -Original Message- From: Visolve DB TEAM [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 15, 2006 12:44 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Incrementing using Max(Field) ? Hello William Try the below Query to insert next maximum value of the field into same table INSERT INTO Sample(id) SELECT MAX(id)+1 FROM Sample Thanks Visolve DB Team - Original Message - From: William DeMasi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 12:34 AM Subject: Incrementing using Max(Field) ? Does anyone have any ideas of how I can select the max value and insert the next highest value? I want something that would do something like this: Insert into table1 (select max(field1)+1 from table1); This obviously doesn't work. I know if the table was set to auto-increment it wouldn't be an issue, but I am not able to change its schema. Thank you. - William -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incrementing using Max(Field) ?
Does anyone have any ideas of how I can select the max value and insert the next highest value? I want something that would do something like this: Insert into table1 (select max(field1)+1 from table1); This obviously doesn't work. I know if the table was set to auto-increment it wouldn't be an issue, but I am not able to change its schema. Thank you. - William -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with using IN for a sub-query statement
The select statement I am trying to run is: select * from c2iedm_dev2.act where act_id =(select obj_act_id from c2iedm_dev2.act_functl_assoc where subj_act_id =24); But I get the error below: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select obj_act_id from c2iedm_dev2.act_functl_assoc where subj_ I have looked at the documentation for MySQL and this seems to be the correct syntax for using IN. Could someone please let me know what is wrong? Thank you. William DeMasi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Invalid syntax with STD() function when more than one field is used in select query
Oops, the version numbers were 4.1.18-nt and 4.1.19-standard. I have tried it now on the latest 4.1.20 version and still have the same problem. Does anyone have any ideas? Is this a bug? Cheers, Bill -Original Message- From: William Bronsema Sent: Thursday, July 20, 2006 10:18 AM To: mysql@lists.mysql.com Subject: Invalid syntax with STD() function when more than one field is used in select query Hello, I am encountering a strange issue when using the STD function. On my local development machine (MYSQL version 4.18-nt) I can run the following basic SELECT query with no problems: SELECT STD(`LAPSETIME`),UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY When I test this query on my hosted production machine (MYSQL version 4.19-standard) that query results in an invalid syntax error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `LAPSETIME` ) , UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY LIM' at line 1 The query will work if I remove the UKEY field in the select: SELECT STD(`LAPSETIME`) FROM 4b3f91f64a19529a84dff4982c8a6bc5 Any ideas? Cheers, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Invalid syntax with STD() function when more than one field is used in select query
Hello, I am encountering a strange issue when using the STD function. On my local development machine (MYSQL version 4.18-nt) I can run the following basic SELECT query with no problems: SELECT STD(`LAPSETIME`),UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY When I test this query on my hosted production machine (MYSQL version 4.19-standard) that query results in an invalid syntax error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `LAPSETIME` ) , UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY LIM' at line 1 The query will work if I remove the UKEY field in the select: SELECT STD(`LAPSETIME`) FROM 4b3f91f64a19529a84dff4982c8a6bc5 Any ideas? Cheers, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LIMIT Question
Have your where clause select the customer, sort result by autoincrement column DESC limit 50 Only problem is the order would be the inverse (i.e., last in first displayed) from your goal. Only way I can thing to go around this would be to select into a temp table and then sort that into the desired sequence. Just a thougth. Dirk Bremer said: Dan, That might be close. The rows are inserted with an auto-increment primary key, but I have no ready way of knowing what the latest 50-IDs are. There are also various date columns, but I won't readily know the dates in this scenario. The goal of the query, which currently returns all of the results, is to find all entries for a given customer regardless of when they occurred. I would like no more than the last 50 rows inserted for this customer (this could be based upon the auto-increment value) and would prefer to have them ordered within the 50-possible results in the order they were inserted, from lowest-ID to the highest-ID. This will prevent the query from showing possible hundreds of results. There are multiple customers in the table. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:28 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: LIMIT Question Depends what you mean by last - you could show the 50 with the latest datestamps by ending your query with something like: ORDER BY datestampcolumn DESC LIMIT 50; or the 50 with the highest ID numbers, same thing: ORDER BY id DESC LIMIT 50; only real problem there is then they're sorted highest to lowest, but it is still the last 50. Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: Is there a way to use a LIMIT clause to show the last X amount of rows or a way to emulate this behavior? For example, a table has somewhere between 1000 and 2000 rows, but you just want to see the last 50. These last 50 might be the most recent entries, for example. Can this be done in single query? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to find matching tables that have specific field name.
Dear Sir, I have a database with over 80 tables. Is there an easy way to find table names that has PERSON_ID field using SELECT query? Thanks in advance for any help. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Connection Pooling
romyd misc said: Hi Everyone, I'm developing an application using C# .NET and mysql as database. It's a multithreaded application, we open a mysql database connection at the very beginning when the application is started and all the database requests use the same connection. But under stress or when more than one request try to access database, i get object reference errors. I don't get this error when frequency of database calls is low. Does it sounds like i need to implement connection pooling? I tried to lookup online, but couldn't find any help under mysql documentation. Can someone help me setting up mysql connection pooling with C#.NET. Thanks in advance, Romy Your comment about one connection for all of the threads disturbs me. Your application will have to ensure that each thread is finished with the connection and returns it to the pool. Two threads cannot, at the same time, use the same connection. Say thread A had performed a select which returned 2000 row resultset. Until that thread had read in all 2000 rows, they would still be in the connection. If thread B tried to use the same connection and asked for a different result set when thread A went back for the rest of its results where would they be? When you put stress on your application this is more likely to happen. What a pool does is allow your threads to formally release their connections back to the pool when they are done with them and re-aquire them later without the full overhead to going all the way back to the server to open a connection. Instead you go to some intermediate point where a set of threads are already avaiable (in Apache its at the child level and in java its at the container level). Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldumps from java program
Is it possible to setup replication so you would have another server to do backups on? Replicate the data, do whatever you want to the spare, and then delete the data from the production server. On 4/19/06, balaraju mandala [EMAIL PROTECTED] wrote: Hi Everybody, I need a suggestion regarding mysqldump. My problem is my application is creatiing around 500Mb of data per day. As i want my application run 24*7*365. I need a mechanisem where i can move, previous day's data to another location(i.e) at any given time i just want to store one or two days data only in my current DB. So i planned to make this by using mysqldump, as u know it will create files which we can upload where ever we need. Is this is a good idea, or we have another better mechanisem? I am planning to automate this using Java. Is we have any prebiuild tools for this?
Re: need help to delete duplicates
Sample Data: ID-Row1-Row2 1-A-B 2-A-B Row1 and Row2 are duplicate, so you only want one. Which ID do you want? -will On 4/17/06, Patrick Aljord [EMAIL PROTECTED] wrote: hey all, I have a table mytable that looks like this: id tinyint primary key auto_increment row1 varchar 150 row2 varchar 150 I would like to remove all duplicates, which means that if n records have the same row1 and row2, keep only one record and remove the duplicates. Any idea how to do this? thanks in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help to delete duplicates
If the ID doesn't represent anything, you can CREATE TABLE new_table SELECT DISTINCT Row1, Row2 FROM old_table And then recreate your index(es). All your autoincrement IDs will be changed. On 4/17/06, Patrick Aljord [EMAIL PROTECTED] wrote: On 4/18/06, William Fong [EMAIL PROTECTED] wrote: Sample Data: ID-Row1-Row2 1-A-B 2-A-B Row1 and Row2 are duplicate, so you only want one. Which ID do you want? one of them has to be deleted, it doesn't really matter which one it is. id isn't attached to any other table and doesn't represent important data or anything. So i just want to end up with only one record having Row1= A and Row2= B. it doesn't matter if id=1 or 2
Re: New User Setting up MYSQL
You might want to look get the mysql admin tool. It will call the query browser if needed (so don't get rid of that), but its what you use to supervise the server (set up users etc.) Hope this helps. Chuck Wildeman said: Hi, I am very new to mysql. In the past I was using Access. I don't have any other experience setting up databases. I bought a book on MYSQL and downloaded the installation file to one of our server and then installed it using most of the defaults. I am now at my XP Pro laptop and want to access the database and start doing things such as importing table from access etc. I wasn't exactly sure what to use for a front end so I choose MYSQL Query browser. I am having trouble connecting to the newly created database. MYSQL Query Browser comes up with a screen that asks for a couple of things. Under stored connection I put in OCRPDC which is the name we use for the server I put this on. For stored host I put in the IP address of this server. I left the port as 3306 and arranged for this port to be open during the installation. Under username I have tried both root and cwildeman. I thought during the installation it asked for a user ID and this is what I plugged in. For a password I have used both my normal windows password which I use for many things and our administrator password which I thought I used during the installation. I wasn't sure what to use to Default Schema so I just put in test. I keep getting a connection error number 2003 stating I can't connect to MYSQL server. Is there something that someone can suggest? Thanks, Chuck --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table keys
fbsd_user said: create table members ( logon_idvarchar(15) NOT NULL, email_addr varchar(30) NOT NULL, member_type char(1), email_verified char(1), logon_pwvarchar(15), date_added date, last_login timestamp, count_of_logons INT, first_name varchar(30), last_name varchar(30), primary key login_id (login_id), UNIQUE INDEX email_addr (email_addr)); --- When doing a insert row, if the logon_id value is all ready in the table I get a dup id msg. This is fine and what I want to happen. But when inserting a row with a unique logon_id value that has a email_addr that is already used by some other logon_id, mysql allows the insert. This is not the action I want. I need to be able to do lookup by logon_id or by email_addr and retrieve the row. I can do that now, but if 2 logon_id's have the same email address I get both rows. I need the email address to be unique across all rows. How can I change this table definition so email_addr is unique across all rows of the table? I read the manual about 'unique index' options, but still don't comprehend what the manual says. Also as you can see I do not select a engine type, is there some engine type better suited and or faster for the way I am trying to use the keys? Thanks for your advice and help. Close but you need to specify that the index is UNIQUE (see changes above) --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row Count Discrepency
Some more information can be found here: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html On 12/31/05 7:29 PM, JJ [EMAIL PROTECTED] wrote: I have an InnoDB table in a MySQL 4.1.14 database. Can anyone suggest why MySQL Adminstrator says the table has 497 rows, while doing a query or a count on the same table shows that it only has 434? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User can see all databases...
This did the trick. Many thanks. I had found that option, but misinterpreted what I read; I thought it was only available in 4.x. Thanks again! -Bill On Dec 14, 2005, at 6:59 PM, Michael Stassen wrote: William R. Dickson wrote: OK, I strongly suspect I've just done something stupid here, but I'm having trouble figuring it out. I had a disk go bad on a MySQL server this past weekend. I did a clean system install (FreeBSD 5.4) on a new disk, installed the MySQL 3.23 port, and restored the mysql data directory from backup. Everything is working fine...except now, every user is able to get a list of every database on the system. They can't actually use the databases, but I'd rather they couldn't get the list, either. Following some suggestions I found in the list archives, I did a SHOW GRANTS and found the following (database names match usernames): GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD 'blablabla' GRANT ALL PRIVILEGES ON `username`.* TO 'username'@'%' I suspect that the problem lies with every user having USAGE' privileges on every database (although entering use otherusername; returns an error indicating the user has no permissions to read the database). However, I can't seem to revoke this privileges. I can't even find the privilege in any of the tables. Can someone point my addled brain in the right direction here? Thanks! -Bill I know it is counter-intuitive, but USAGE means no privileges. In 3.23, seeing all databases is the default behavior, turned off by starting mysqld with the --skip-show-database option. This changed to the behavior you are expecting in 4.0.2. See the manual for more http://dev.mysql.com/doc/refman/4.1/en/ show-databases.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User can see all databases...
OK, I spoke a little too soon. Users can no longer list all databases, which is great. Unfortunately, they also can't list their own, resulting in errors when they log into phpMyAdmin: SQL-query: SHOW DATABASES ; MySQL said: #1045 - Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) mysql SHOW GRANTS FOR 'username'@'%'; +--- ---+ | Grants for username@ %| +--- ---+ | GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD 'blablabla' | | GRANT ALL PRIVILEGES ON `username`.* TO 'username'@'%' | +--- ---+ OK, the documentation says: If the server was started with the --skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege. From what I can tell, however, I can't grant the SHOW DATABASES privilege to a user for his own databases in 3.x. Is there any way in 3.x to allow a user to list ONLY his own databases? The ultimate problem is this: we have a number of users with Lasso code that is getting confused when it can see the full list (plus, we'd just rather people not be able to do that). So we needed to disable show databases to get Lasso working properly. Unfortunately, we also need phpMyAdmin to work properly, and it seems to have problems if this feature is disabled. Thanks, -Bill On Dec 19, 2005, at 7:34 AM, William R. Dickson wrote: This did the trick. Many thanks. I had found that option, but misinterpreted what I read; I thought it was only available in 4.x. Thanks again! -Bill On Dec 14, 2005, at 6:59 PM, Michael Stassen wrote: William R. Dickson wrote: OK, I strongly suspect I've just done something stupid here, but I'm having trouble figuring it out. I had a disk go bad on a MySQL server this past weekend. I did a clean system install (FreeBSD 5.4) on a new disk, installed the MySQL 3.23 port, and restored the mysql data directory from backup. Everything is working fine...except now, every user is able to get a list of every database on the system. They can't actually use the databases, but I'd rather they couldn't get the list, either. Following some suggestions I found in the list archives, I did a SHOW GRANTS and found the following (database names match usernames): GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD 'blablabla' GRANT ALL PRIVILEGES ON `username`.* TO 'username'@'%' I suspect that the problem lies with every user having USAGE' privileges on every database (although entering use otherusername; returns an error indicating the user has no permissions to read the database). However, I can't seem to revoke this privileges. I can't even find the privilege in any of the tables. Can someone point my addled brain in the right direction here? Thanks! -Bill I know it is counter-intuitive, but USAGE means no privileges. In 3.23, seeing all databases is the default behavior, turned off by starting mysqld with the --skip-show-database option. This changed to the behavior you are expecting in 4.0.2. See the manual for more http://dev.mysql.com/doc/refman/4.1/en/ show-databases.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User can see all databases...
OK, I strongly suspect I've just done something stupid here, but I'm having trouble figuring it out. I had a disk go bad on a MySQL server this past weekend. I did a clean system install (FreeBSD 5.4) on a new disk, installed the MySQL 3.23 port, and restored the mysql data directory from backup. Everything is working fine...except now, every user is able to get a list of every database on the system. They can't actually use the databases, but I'd rather they couldn't get the list, either. Following some suggestions I found in the list archives, I did a SHOW GRANTS and found the following (database names match usernames): GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD 'blablabla' GRANT ALL PRIVILEGES ON `username`.* TO 'username'@'%' I suspect that the problem lies with every user having USAGE' privileges on every database (although entering use otherusername; returns an error indicating the user has no permissions to read the database). However, I can't seem to revoke this privileges. I can't even find the privilege in any of the tables. Can someone point my addled brain in the right direction here? Thanks! -Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relocation of database store
I'm trying to relocate the database files for MySQL 4.1 I've seen two primary techniques for doing this: 1) create/alter a my.cnf file with 'datadir' set to new location 2) moving the physical files and creating a symlink in the original location Unfortunately, neither of these methods work for me. I have my MySQL server running on a Mandrake 10.1 Linux box with limited hard drive space. I want to place my database on a NAS drive. The NAS drive support many protocols: NFS, SMB, AFP, etc. so I have no problem at all accessing the device from my database server. However, the problem seems to be that, as part of MySQL server's normal operation, it creates a socket file. Unfortunately, this activity is not allowed on a remote share. So, when the server starts up, its attempt to create mysql.sock fails. It appears that both relocation methods indicated above cause EVERYTHING to be relocated, i.e. not only the hard files, but the socket file also. Is there a way to have the hard files relocated but keep the socket file in /var/lib/mysql? Thanks! - Liam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LVM-snapshot + mysqldump -- is this a reasonable backup
George Herson said: James G. Sack (jim) wrote: On Mon, 2005-09-19 at 13:14 -0400, George Herson wrote: James G. Sack (jim) wrote: On Mon, 2005-09-19 at 12:15 -0400, George Herson wrote: Dear Jim, Re: your post at http://lists.mysql.com/mysql/189058, why bother creating the mysqldump if you already have the snapshot? Why not just backup the snapshot? [...] The output of mysqldump *is* the backup. If the db goes away, it can be restored with (something like) mysql dumpfile.2005-09-01. The dump operation is run periodically, and some number of back versions can be kept around (or offloaded) for archival value. Yes, but can't you also save your snapshot instead, then, when/if you want, restore it, 4. mount the snapshot 5. load a second database server daemon accessing the db within the snapshot (with a suitable alternate my.cnf file) 6. perform mysqldump operation on the snapshot-db ?? .. George, LVM snapshots are generally intended to be short-lived -- snip ..jim Jim, I didn't word my question quite right because I was only guessing at what a LVM snapshot was. Moreover, what you're saying is all correct. However, I was not suggesting that the snapshot be kept around once the backup is made. Let's go to article What is a Logical Volume Manager (LVM) snapshot and how do I use it? http://kbase.redhat.com/faq/dml_fetch.pl?CompanyID=842ContentID=4097FaqID=3640word=What%20is%20a%20Logical%20Volume%20Managerfaq_template=http://kbase.redhat.com/faq/searchfaq.shtmtopic=80back_refr=http://kbase.redhat.com/faq/topicname=AS/ES/WS%20BasicsId=Instance=Shared= in the RedHat k'base http://www.redhat.com/apps/support/knowledgebase/. It says After performing the backup of the snapshot partition we release the snapshot. This implies, at least to me, that one doesn't need a 2nd database server or to do a mysqldump (your steps 5-7). Instead, we just tar cv /mnt/ops/dbbackup (to use the article's example name for the mounted snapshot), save the tape, and dispense with the snapshot. Wouldn't that work? MySQL keeps its data in files already, so why is it necessary to mysqldump it? Are you only trying to avoid having to also backup the mysqld version that wrote the data files to ensure that these can be read later? George The LVM snapshot will hold the state of the database as it is written to the disk. However, the database engine may have to write several things to the disk to ensure consistency (e.g, main table and index). If you take the snapshot between these two operations the database will be in an inconsistent state. Mysqldump locks the tables before performing a dump to prevent this. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Difference between Blob and varchar binary
I was storing some 8 bit information in a varchar binary field (encrypted stuff) and I think its getting corrupted. I thought they were (except for size) interchangable? I'm running debian GNU Linux and haven't moved to sarge so I'm still on 3.23.x but will be moving shortly. --- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between Blob and varchar binary
Gleb Paharenko said: Hello. In my opinion, one of the causes of the problem can be the processing of trailing spaces in varbinary fields. See: http://dev.mysql.com/doc/mysql/en/binary-varbinary.html Thanks, you may be right since the results are too short. William R. Mussatto [EMAIL PROTECTED] wrote: I was storing some 8 bit information in a varchar binary field (encrypted stuff) and I think its getting corrupted. I thought they were (except for size) interchangable? I'm running debian GNU Linux and haven't moved to sarge so I'm still on 3.23.x but will be moving shortly. --- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lexer for query strings
Hello, I'm not sure if this question is more appropriate for general discussion or the internals list. I want to create a tokenizer/lexer for MySQL's dialect of SQL to use in a JAVA application. I thought I could do this by creating a JAVA wrapper on the lexer code for MySQL. I looked through the source and found the yacc file and the sql_lex file, but was wondering why I couldn't find the original lex file? I had hoped to use this to generate the lexer code for my application. Any guidance on this issue is appreciated. -GJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
