Re: Populating dataset
Hi, We have a table which contains the following sample of information 2010-03-20 2010-03-21 2010-03-22 2010-03-23 2010-03-26 2010-03-27 2010-03-28 2010-03-30 As you can see, the dates listed are not constant e.g missing 2010-03-29 as a example. For our query like SELECT check_in_date FROM bookings how would I know to automatically insert a missing date like 2010-03-29 between the date range I search ? So, you want to select something that isn't available from the database? Makes you wonder why you need to go to the database for this at all... ? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql_free_result vs. mysqli_free_result
http://php.net/manual/en/function.mysql-free-result.php says memory is automatically freed at the end of script execution but http://kr.php.net/manual/en/mysqli-result.free.php says you should always call mysqli_free_result and does not mention automatic free. Is there any plan to support automatic memory free for mysqli_free_result as mysql_free_result do? Thanks. -- http://kldp.org/~kss
Re: Populating dataset
how would I know to automatically insert a missing date A common problem. Build a calendar table (http://www.artfulsoftware.com/infotree/queries.php?#95) PB - Tompkins Neil wrote: Hi, We have a table which contains the following sample of information 2010-03-20 2010-03-21 2010-03-22 2010-03-23 2010-03-26 2010-03-27 2010-03-28 2010-03-30 As you can see, the dates listed are not constant e.g missing 2010-03-29 as a example. For our query like SELECT check_in_date FROM bookings how would I know to automatically insert a missing date like 2010-03-29 between the date range I search ? Thanks, Neil No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.437 / Virus Database: 271.1.1/2769 - Release Date: 03/25/10 07:33:00
MySQL Slave is almost 1 day behind
Good day :) We've had our master/slave server running for a while now, and just yesterday, we started getting behind. Not entirely sure what happened, but it is getting further and furhter behind. (master server) mysql show master status\G *** 1. row *** File: mysql-bin.000280 Position: 58090245 Binlog_Do_DB: admin_server,baf,freeradius,radius Binlog_Ignore_DB: 1 row in set (0.00 sec) (slave server) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.7.101 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000280 Read_Master_Log_Pos: 55208258 Relay_Log_File: backup-relay-bin.000530 Relay_Log_Pos: 96663109 Relay_Master_Log_File: mysql-bin.000259 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: admin_server,baf,freeradius,radius 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: 96662972 Relay_Log_Space: 2211376614 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: 77473 1 row in set (0.00 sec) Now, we are logging the freeradius packets into mysql, and like I said, it has been running fine, up until yesterday. Any idea how the slave would get this far behind, and not be generating any errors? It is my understanding, that the slave only does update/insert/delete queries, so even if there was a lot of select queries on the master, the slave wouldn't see them. We are not running any queries on the slave (it was set up for backup purposes, so we could stop the slave and backup completely), and we haven't done a backup on the slave in a couple of days (yeah, i know... bad bad) so there is really no reason for this. Can anyone help/assist/point me in the right direction to figure out how to catch the slave back up to the master? The master is not being overloaded, it is keeping up no problem, and the backup server is 8x the server than the application server, so it shoulnd't even be an i/o or cpu issue. Please help! :) Thanks in advance Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Slave is almost 1 day behind
A few things to keep in mind: 1: the master may have several threads feeding into the binlog at a time, but a slave only executes in a single thread. Are you throwing more stuff at the slave in multiple mysql threads? 2: is there something else going on with the slave box? some big backup or gzip or something that would chew up cycles? any big mysql query or update going on? 3: have you checked the disks on your slave. Whenever I notice a slave falling behind for an extended period of time, I ask the sys admins to check the disk drives - if you're using some kind of RAID, they can become degraded. 4: you might also check the slave's mysql error log to see if there's any hint there. On Fri, Mar 26, 2010 at 9:45 AM, Steven Staples sstap...@mnsi.net wrote: Good day :) We've had our master/slave server running for a while now, and just yesterday, we started getting behind. Not entirely sure what happened, but it is getting further and furhter behind. (master server) mysql show master status\G *** 1. row *** File: mysql-bin.000280 Position: 58090245 Binlog_Do_DB: admin_server,baf,freeradius,radius Binlog_Ignore_DB: 1 row in set (0.00 sec) (slave server) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.7.101 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000280 Read_Master_Log_Pos: 55208258 Relay_Log_File: backup-relay-bin.000530 Relay_Log_Pos: 96663109 Relay_Master_Log_File: mysql-bin.000259 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: admin_server,baf,freeradius,radius 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: 96662972 Relay_Log_Space: 2211376614 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: 77473 1 row in set (0.00 sec) Now, we are logging the freeradius packets into mysql, and like I said, it has been running fine, up until yesterday. Any idea how the slave would get this far behind, and not be generating any errors? It is my understanding, that the slave only does update/insert/delete queries, so even if there was a lot of select queries on the master, the slave wouldn't see them. We are not running any queries on the slave (it was set up for backup purposes, so we could stop the slave and backup completely), and we haven't done a backup on the slave in a couple of days (yeah, i know... bad bad) so there is really no reason for this. Can anyone help/assist/point me in the right direction to figure out how to catch the slave back up to the master? The master is not being overloaded, it is keeping up no problem, and the backup server is 8x the server than the application server, so it shoulnd't even be an i/o or cpu issue. Please help! :) Thanks in advance Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: MySQL Slave is almost 1 day behind
Also, run SHOW PROCESSLIST, to see what sql's are running on slave. This will give u any idea if any huge dml was run on master and its getting sync'd with slave. regards anandkl On Fri, Mar 26, 2010 at 8:25 PM, Jim Lyons jlyons4...@gmail.com wrote: A few things to keep in mind: 1: the master may have several threads feeding into the binlog at a time, but a slave only executes in a single thread. Are you throwing more stuff at the slave in multiple mysql threads? 2: is there something else going on with the slave box? some big backup or gzip or something that would chew up cycles? any big mysql query or update going on? 3: have you checked the disks on your slave. Whenever I notice a slave falling behind for an extended period of time, I ask the sys admins to check the disk drives - if you're using some kind of RAID, they can become degraded. 4: you might also check the slave's mysql error log to see if there's any hint there. On Fri, Mar 26, 2010 at 9:45 AM, Steven Staples sstap...@mnsi.net wrote: Good day :) We've had our master/slave server running for a while now, and just yesterday, we started getting behind. Not entirely sure what happened, but it is getting further and furhter behind. (master server) mysql show master status\G *** 1. row *** File: mysql-bin.000280 Position: 58090245 Binlog_Do_DB: admin_server,baf,freeradius,radius Binlog_Ignore_DB: 1 row in set (0.00 sec) (slave server) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.7.101 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000280 Read_Master_Log_Pos: 55208258 Relay_Log_File: backup-relay-bin.000530 Relay_Log_Pos: 96663109 Relay_Master_Log_File: mysql-bin.000259 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: admin_server,baf,freeradius,radius 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: 96662972 Relay_Log_Space: 2211376614 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: 77473 1 row in set (0.00 sec) Now, we are logging the freeradius packets into mysql, and like I said, it has been running fine, up until yesterday. Any idea how the slave would get this far behind, and not be generating any errors? It is my understanding, that the slave only does update/insert/delete queries, so even if there was a lot of select queries on the master, the slave wouldn't see them. We are not running any queries on the slave (it was set up for backup purposes, so we could stop the slave and backup completely), and we haven't done a backup on the slave in a couple of days (yeah, i know... bad bad) so there is really no reason for this. Can anyone help/assist/point me in the right direction to figure out how to catch the slave back up to the master? The master is not being overloaded, it is keeping up no problem, and the backup server is 8x the server than the application server, so it shoulnd't even be an i/o or cpu issue. Please help! :) Thanks in advance Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Birthday Calendar
Apparently not. Noel Butler wrote: does this list not have a dickhead filter? On Tue, 2010-03-23 at 15:17 +, Ganeswar Mishra wrote: Hi Please click on the link below and enter your birthday for me. I am creating a birthday calendar for myself. Don't worry, it'll take less than a minute (and you don't have to enter your year of birth). http://www.birthdayalarm.com/bd2/86622257a687264490b1506094186c258984146d1386 Ganeswar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
High-level transaction isolation architecture of InnoDB
I've noticed that Innodb seems to exhibit true serializability for the serializable transaction isolation level. Does this mean it implements predicate locking? Also out of curiosity, is it possible to set a snapshot isolation transaction isolation level (is Innodb implemented using MVCC)? Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: High-level transaction isolation architecture of InnoDB
Hi Yang, On Mar 26, 2010, at 4:28 PM, Yang Zhang wrote: I've noticed that Innodb seems to exhibit true serializability for the serializable transaction isolation level. Does this mean it implements predicate locking? Kinda, but not exactly. In serializable, all reads will use shared locks on the tree as it accesses the rows. It doesn't have true predicate locking, since it doesn't lock non- existent rows, but instead locks gaps where rows could go. For example, if you do try to read the non-existent row 1000, it may also prevent 999 from being inserted while with true predicate locking it would be allowed. In addition, it locks based on access path, so there can be additional locks from that as well. This does make it truly mathematically serializable, but does have additional locks than would be required by 'real' predicate locking. Also out of curiosity, is it possible to set a snapshot isolation transaction isolation level (is Innodb implemented using MVCC)? Thanks in advance. Yes, it is MVCC. InnoDB in repeatable read will use a 'snapshot' that is taken at the beginning of the transaction for all of the normal non- locking reads it performs during the transaction. Regards, Harrison -- Harrison C. Fisk, Senior Principal Technical Support Engineer MySQL @ Oracle, Inc., http://www.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[joke] SQL Injection License Plate Hopes to Foil Euro Traffic Cameras
http://gizmodo.com/5498412/sql-injection-license-plate-hopes-to-foil-euro-t raffic-cameras -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Freespace threshold be having to OPTIMIZE TABLE?
Hiya My query below, looks for tables with 10% freespace but also the space is greater than 100K. mysql SHOW TABLE STATUS WHERE Data_free / Data_length 0.1 AND Data_free 102400 \G; *** 1. row *** Name: bayes_words Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 97134 Avg_row_length: 27 Data_length: 2960488 Max_data_length: 281474976710655 Index_length: 4182016 Data_free: 330136 Auto_increment: NULL Create_time: 2010-02-21 05:17:26 Update_time: 2010-03-27 01:12:01 Check_time: 2010-02-21 05:17:27 Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) Question I would like to ask is, what is an acceptable threshhold or amount of freespace before I must run OPTIMIZE TABLE? Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
sql to duplicate records with modified value
I have Postfix virtual mailboxes in MySQL table like below: I'd like to duplicate all records whilst MODIFYING two fields like so: current record has format like: user 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@domain.tld/.spam/' so that I'll end up with two record, existing, plus new one field 'user' - insert '+spam' ahead of '@' field 'maildir' append '.spam/' what's the best way mysql Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3 Server version: 4.1.22-standard mysql show tables; +---+ | Tables_in_postfix | +---+ | admin | | alias | | config| | domain| | domain_admins | | fetchmail | | log | | mailbox | | vacation | | vacation_notification | +---+ 10 rows in set (0.00 sec) -- Voytek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
