Re: Update Column in table only if variable is Not NULL
Try: update my_table set fieldname1 = Now(), Fieldname2 = :myVariable where Fieldname3 is not null On 10/28/13 11:06 AM, Neil Tompkins wrote: Hi If I have a update statement like UPDATE MY_TABLE SET FieldName1 = Now(), FieldName2 = :MyVariable WHERE FieldName3 = 'Y' How can I only update the FieldName2 field if the value of MyVariable is NOT NULL ? Thanks Neil -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Performance hiccoughs..
Hey all - We have been focusing on performance in our systems a lot lately, and have made some pretty good progress. Upgrading the mySQL engine from 5.1 to 5.5 was eye-opening. But there are still issues, and one in particular is vexing. It seems like a tuning problem for sure - I notice this even at the command-line interface. I will have a update command: update my_table set test_column = 'tester_value' where key_value = 'a-test-key'; key_value is the primary key for my_table, which is an INNODB table, about 50MB, 96K rows If I run this 10 times with different key values, most of the time, it will return pretty much instantaneously. But at least once, it will take 10, 20, 30 seconds to return. This affects our applications as well - operations that are generally fast will suddenly be very, very slow... and then back to fast. OS: SunOS 5.10 SQL version: 5.5.33-log MySQL Community Server (GPL) Hardware: Virtual Machine (VMWare), 4 cpus - 16GB RAM Tuning section of my.cnf: # tuning key_buffer_size=512M max_allowed_packet=16M table_open_cache=512 sort_buffer_size=10M read_buffer_size=10M read_rnd_buffer_size=8M myisam_sort_buffer_size=512M thread_cache_size=8 query_cache_type=1 query_cache_size=1024M query_cache_limit=10M # 2 x numcpus #thread_concurrency=4 #innodb_thread_concurrency=0 #innodb_read_io_threads=16 #innodb_write_io_threads=16 # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size=2048M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size=100M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=50 innodb=on -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Performance hiccoughs..
On 8/14/13 10:46 AM, Manuel Arostegui wrote:
2013/8/14 Andy Wallace awall...@ihouseweb.com mailto:awall...@ihouseweb.com
Hey all -
We have been focusing on performance in our systems a lot lately, and have
made some pretty
good progress. Upgrading the mySQL engine from 5.1 to 5.5 was eye-opening.
But there are still issues, and one in particular is vexing. It seems like
a tuning problem
for sure - I notice this even at the command-line interface. I will have a
update command:
update my_table set test_column = 'tester_value' where key_value =
'a-test-key';
key_value is the primary key for my_table, which is an INNODB table, about
50MB, 96K rows
Can you provide the whole show create table for that table?
It's a big table:
CREATE TABLE `agent` (
`acnt` varchar(20) NOT NULL,
`passwd` varchar(20) NOT NULL,
`package` char(2) DEFAULT NULL,
`data_template` varchar(20) DEFAULT 'NULL',
`default_search_type` enum('1','2','3') NOT NULL DEFAULT '1',
`status` enum('A','T','P','C','D','X','S') NOT NULL COMMENT 'A=active, T=testdrive, D=deactivated, P=pending, C=in create state,
X=expired td,S=Suspended',
`flags` varchar(10) DEFAULT NULL COMMENT 'pipe-separated
flags',
`aliases` varchar(4000) NOT NULL DEFAULT '',
`offices` varchar(4000) NOT NULL DEFAULT '',
`license_no` varchar(40) NOT NULL DEFAULT '',
`agent_code` varchar(20) DEFAULT NULL,
`office_code` varchar(20) DEFAULT NULL,
`parent_acnt` varchar(20) DEFAULT NULL,
`number_of_agentlinks` int(11) DEFAULT NULL,
`number_of_emails` int(11) DEFAULT NULL,
`name` varchar(60) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL,
`contact` varchar(80) NOT NULL DEFAULT '',
`company` varchar(80) NOT NULL DEFAULT '',
`address` varchar(100) NOT NULL DEFAULT '',
`city` varchar(80) NOT NULL DEFAULT '',
`state` varchar(2) NOT NULL DEFAULT '',
`zip` varchar(10) NOT NULL DEFAULT '',
`country` varchar(80) NOT NULL DEFAULT '',
`phone` varchar(100) NOT NULL DEFAULT '',
`fax` varchar(100) DEFAULT NULL,
`textline1` varchar(100) NOT NULL DEFAULT '',
`textline2` varchar(100) NOT NULL DEFAULT '',
`textline3` varchar(100) NOT NULL DEFAULT '',
`textline4` varchar(100) NOT NULL DEFAULT '',
`domain` varchar(80) NOT NULL DEFAULT '',
`email` varchar(80) NOT NULL,
`url` varchar(200) NOT NULL DEFAULT '',
`state_restriction` varchar(150) NOT NULL DEFAULT '',
`county_restriction` varchar(4000) NOT NULL DEFAULT '',
`area_restriction` varchar(4000) NOT NULL DEFAULT '',
`city_restriction` text,
`ht_freq` enum('d','w') NOT NULL DEFAULT 'd',
`ht_dow` enum('1','2','3','4','5','6','7') NOT NULL DEFAULT
'1',
`signup_date` datetime NOT NULL DEFAULT '-00-00 00:00:00',
`disabled_date` datetime NOT NULL DEFAULT '-00-00
00:00:00',
`reactivation_date` date DEFAULT NULL,
`last_login_date` datetime DEFAULT NULL,
`testdrive_expires` date DEFAULT NULL,
`no_mls_value` varchar(100) DEFAULT NULL,
`internal_account` enum('N','Y') DEFAULT NULL,
`sample_account` enum('N','Y') NOT NULL DEFAULT 'N',
`is_ppc` enum('YES','NO') NOT NULL DEFAULT 'NO',
`is_leadbuilder` enum('YES','NO') NOT NULL DEFAULT 'NO',
`website_created` enum('N','Y') NOT NULL DEFAULT 'N',
`brand` int(11) NOT NULL DEFAULT '0',
`reseller_name` varchar(64) NOT NULL DEFAULT '',
`source_tracking` enum('wordpress') DEFAULT NULL,
`report_to_mls` enum('N','Y') NOT NULL DEFAULT 'Y',
`export_listings_to_parent` enum('N','Y') NOT NULL DEFAULT
'N',
`accepts_leads` enum('N','Y') DEFAULT 'Y',
`last_full_listing_update` datetime DEFAULT NULL,
`last_new_listing_update` datetime DEFAULT NULL,
`last_activity_reminder` datetime DEFAULT NULL,
`mobile_access_count` int(11) DEFAULT '0',
`testdrive_self_extend_count` int(11) DEFAULT '0',
`weak_password` enum('N','Y') DEFAULT 'N',
`weak_email_password` enum('N','Y') DEFAULT 'N',
PRIMARY KEY (`acnt
database perfomance worries
We are on a quest to improve the overall performance of our database. It's
generally
working pretty well, but we periodically get big slowdowns for no apparent
reason. A
prime example today - in the command line interface to the DB, I tried to
update one
record, and got:
mysql update agent set number_of_emails = 5 where acnt = 'AR287416';
Query OK, 1 row affected (36.35 sec)
Rows matched: 1 Changed: 1 Warnings: 0
36 seconds to update one table? The primary key is `acnt`. If I run the same
(basic)
command again a few seconds later, I get:
mysql update agent set number_of_emails = 15 where acnt = 'AR287416';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Why would we be getting such huge variations? We're running Solaris 10 on i386,
with
4 processors and 16GB of memory, MySQL 5.1.46-log. We are working out a plan to
upgrade
to MySQL 5.6, but I certainly don't want to depend on that upgrade to solve all
performance
problems.
CREATE TABLE `agent`
(
`acnt` varchar(20) NOT NULL,
`passwd` varchar(20) NOT NULL,
`package` char(2) DEFAULT NULL,
`data_template` varchar(20) DEFAULT 'NULL',
`default_search_type` enum('1','2','3') NOT NULL DEFAULT '1',
`status` enum('A','T','P','C','D','X','S') NOT NULL
`flags` varchar(10) DEFAULT NULL COMMENT 'pipe-separated flags',
`aliases` varchar(4000) NOT NULL DEFAULT '',
`offices` varchar(4000) NOT NULL DEFAULT '',
`license_no` varchar(40) NOT NULL DEFAULT '',
`agent_code` varchar(20) DEFAULT NULL,
`office_code` varchar(20) DEFAULT NULL,
`parent_acnt` varchar(20) DEFAULT NULL,
`number_of_agentlinks` int(11) DEFAULT NULL,
`number_of_emails` int(11) DEFAULT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL,
whole bunch of other fields
PRIMARY KEY (`acnt`),
KEY `parent_acnt` (`parent_acnt`),
KEY `status` (`status`),
KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
Sometimes it pays to stay in bed on Monday, rather than spending the rest of the
week debugging Monday's code.
- Christopher Thompson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: database perfomance worries
Thanks for the response: how large is your database? about 33GB, and growing how large is innodb_ubber? from my.cnf: # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size=2048M innodb_additional_mem_pool_size = 20M how large is the table? about 60MB, 95K rows for innodb innodb_buffer should be a large as the whole databases not only the one, all of them, dumb but fact Yeah, to do that we'll have to throw a lot more memory in the machine. Thanks for the info. On 7/2/13 3:50 PM, Reindl Harald wrote: Am 02.07.2013 23:28, schrieb Andy Wallace: mysql update agent set number_of_emails = 5 where acnt = 'AR287416'; Query OK, 1 row affected (36.35 sec) Rows matched: 1 Changed: 1 Warnings: 0 36 seconds to update one table? The primary key is `acnt`. If I run the same (basic) command again a few seconds later, I get: mysql update agent set number_of_emails = 15 where acnt = 'AR287416'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Why would we be getting such huge variations? We're running Solaris 10 on i386, with 4 processors and 16GB of memory, MySQL 5.1.46-log. We are working out a plan to upgrade to MySQL 5.6, but I certainly don't want to depend on that upgrade to solve all performance problems how large is your database? how large is innodb_ubber? how large is the table? for innodb innodb_buffer should be a large as the whole databases not only the one, all of them, dumb but fact -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: NOW() is stuck...
Nope, it was locked on a single value for about 36 hours, until we restarted the engine last night. Now it's running fine, and we're setting up a testbed to evaluate MySQL 5.6 and FreeBSD 9 (?) for replacing our current Solaris 10/MySQL 5.1.46 setup. On 6/28/13 12:44 AM, walter harms wrote: hi, does the value change at all like below ? mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372404355 | +---++ 1 row in set (0.00 sec) mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372404371 | +---++ 1 row in set (0.00 sec) re, wh Am 27.06.2013 20:19, schrieb Andy Wallace: Benjamin - Unfortunately: mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372238834 | +---++ 1 row in set (0.00 sec) And: mysql set global timestamp = 0; ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL This does indeed persist across sessions. Any command line connection I make to the database shows the bad value for NOW(). I also tweaked the application code to include NOW() in an existing query, and the value returned to my PHP code is also the bad value. Thanks for looking, andy On 6/27/13 11:10 AM, Stillman, Benjamin wrote: It persists across sessions? Does this return anything: show global variables like 'timestamp'; Hopefully it returns: Empty set (0.00 sec) I vaguely remember reading about a bug in 5.1.4x with something to do with a global timestamp. I thought it only showed one though, and that you couldn't set it. If the above returned a timestamp and not an empty set, try: set global timestamp = 0; That should return something like this: ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL But if it returns: Query OK, 0 rows affected (0.00 sec) And then your queries return correct timestamps, you've found a bug. I'd hope that it would fail, but the only thing I can think of is if it's being set as a global variable. If this does fix your problem, and if you're using replication, you may have an issue with your replicated data. Replication uses timestamp extensively. On 6/27/13 1:44 PM, Andy Wallace awall...@ihouseweb.com wrote: But the question is how. I have nothing in the code that does it, or this would have been true for months instead of just the last 24 hours. In addition, this is currently set globally - no matter what connection to the database, it all comes up with this value. Which means that all my time-based queries no longer work correctly. Does your message suggest that setting it to 0 might clear the problem? On 6/27/13 10:31 AM, Stillman, Benjamin wrote: Timestamp is a session variable, so it must have been set to something other than 0 (1372228034 epoch is the date you're showing) in your current session. mysql set timestamp = 1372228034; Query OK, 0 rows affected (0.00 sec) mysql select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-26 02:27:14 | 2013-06-27 13:20:48 | +-+-+ 1 row in set (0.00 sec) mysql set timestamp = 0; Query OK, 0 rows affected (0.00 sec) mysql select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-27 13:21:34 | 2013-06-27 13:21:34 | +-+-+ 1 row in set (0.00 sec) Cliff's notes: set timestamp = 0; On 6/26/13 6:10 PM, Andy Wallace awall...@ihouseweb.com wrote: We've been having some issues with one of our MySQL servers lately, and currently the dang thing is stuck. For at least the last hour, NOW() is returning the same value: mysql select now(); +-+ | now() | +-+ | 2013-06-26 02:27:14 | +-+ The system variable timestamp also has that same time value stored in it. How can we kick this loose so that the values are more current with real time? (it is currently 3:08PM here, despite our MySQL instance thinking it's 2am. The system time on the machine is correct: $ date Wed Jun 26 15:08:56 PDT 2013 This is MySQL 5.1.46 running on solaris2.10. Any ideas short of restarting the MySQL engine? I'm willing to do that, but would much rather wait and not do it in the middle of the day. Thanks, Andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week
Re: NOW() is stuck...
Problem is that I don't set the timestamp variable anywhere (except yesterday as a test to try and fix the problem). This is stuff that has been working correctly for many months. We had some network/dns and load issues over the last couple of days, and the mysql clock is frozen at: mysql select now(); +-+ | now() | +-+ | 2013-06-26 02:27:14 | +-+ While the machine/system date is: $ date Thu Jun 27 09:15:25 PDT 2013 I had actually planned to restart the mysql instance on this server last night, but there was a miscommunication, and that will have to wait for tonight. Once suggestion I got was to explicitly set the global timezone value, but can't do that because we didn't load the timezone tables. Guess we have to wait for tonight. thanks, guys Andy On 6/26/13 6:34 PM, Eric Bergen wrote: This is the expected behavior if you set the timestamp variable in your session. This is the same mechanism that replication uses to execute transactions on the slave with the correct time. Setting timestamp back to default or reopening your connection will fix it. MariaDB [(none)] set timestamp=1372296737; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] select now(); select sleep(5); select now(); +-+ | now() | +-+ | 2013-06-26 21:32:17 | +-+ 1 row in set (0.00 sec) +--+ | sleep(5) | +--+ |0 | +--+ 1 row in set (5.00 sec) +-+ | now() | +-+ | 2013-06-26 21:32:17 | +-+ 1 row in set (0.00 sec) MariaDB [(none)] set timestamp=default; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] select now(); +-+ | now() | +-+ | 2013-06-26 21:33:53 | +-+ 1 row in set (0.00 sec) MariaDB [(none)] select now(); +-+ | now() | +-+ | 2013-06-26 21:33:54 | +-+ 1 row in set (0.00 sec) On Wed, Jun 26, 2013 at 4:18 PM, John Meyer johnme...@pueblocomputing.com wrote: Well, if you want to get unstuck in time, maybe you need to call Billy Pilgrim ;-) Andy Wallace wrote: We've been having some issues with one of our MySQL servers lately, and currently the dang thing is stuck. For at least the last hour, NOW() is returning the same value: mysql select now(); +-+ | now() | +-+ | 2013-06-26 02:27:14 | +-+ The system variable timestamp also has that same time value stored in it. How can we kick this loose so that the values are more current with real time? (it is currently 3:08PM here, despite our MySQL instance thinking it's 2am. The system time on the machine is correct: $ date Wed Jun 26 15:08:56 PDT 2013 This is MySQL 5.1.46 running on solaris2.10. Any ideas short of restarting the MySQL engine? I'm willing to do that, but would much rather wait and not do it in the middle of the day. Thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: NOW() is stuck...
But the question is how. I have nothing in the code that does it, or this would have been true for months instead of just the last 24 hours. In addition, this is currently set globally - no matter what connection to the database, it all comes up with this value. Which means that all my time-based queries no longer work correctly. Does your message suggest that setting it to 0 might clear the problem? On 6/27/13 10:31 AM, Stillman, Benjamin wrote: Timestamp is a session variable, so it must have been set to something other than 0 (1372228034 epoch is the date you're showing) in your current session. mysql set timestamp = 1372228034; Query OK, 0 rows affected (0.00 sec) mysql select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-26 02:27:14 | 2013-06-27 13:20:48 | +-+-+ 1 row in set (0.00 sec) mysql set timestamp = 0; Query OK, 0 rows affected (0.00 sec) mysql select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-27 13:21:34 | 2013-06-27 13:21:34 | +-+-+ 1 row in set (0.00 sec) Cliff's notes: set timestamp = 0; On 6/26/13 6:10 PM, Andy Wallace awall...@ihouseweb.com wrote: We've been having some issues with one of our MySQL servers lately, and currently the dang thing is stuck. For at least the last hour, NOW() is returning the same value: mysql select now(); +-+ | now() | +-+ | 2013-06-26 02:27:14 | +-+ The system variable timestamp also has that same time value stored in it. How can we kick this loose so that the values are more current with real time? (it is currently 3:08PM here, despite our MySQL instance thinking it's 2am. The system time on the machine is correct: $ date Wed Jun 26 15:08:56 PDT 2013 This is MySQL 5.1.46 running on solaris2.10. Any ideas short of restarting the MySQL engine? I'm willing to do that, but would much rather wait and not do it in the middle of the day. Thanks, Andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: NOW() is stuck...
Sort of: mysql show variables like 'init_connect'; +---+---+ | Variable_name | Value | +---+---+ | init_connect | | +---+---+ On 6/27/13 11:23 AM, Eric Bergen wrote: Does show variables like 'init_connect'; return anything? On Thu, Jun 27, 2013 at 11:19 AM, Andy Wallace awall...@ihouseweb.com wrote: Benjamin - Unfortunately: mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372238834 | +---++ 1 row in set (0.00 sec) And: mysql set global timestamp = 0; ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL This does indeed persist across sessions. Any command line connection I make to the database shows the bad value for NOW(). I also tweaked the application code to include NOW() in an existing query, and the value returned to my PHP code is also the bad value. Thanks for looking, andy On 6/27/13 11:10 AM, Stillman, Benjamin wrote: It persists across sessions? Does this return anything: show global variables like 'timestamp'; Hopefully it returns: Empty set (0.00 sec) I vaguely remember reading about a bug in 5.1.4x with something to do with a global timestamp. I thought it only showed one though, and that you couldn't set it. If the above returned a timestamp and not an empty set, try: set global timestamp = 0; That should return something like this: ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL But if it returns: Query OK, 0 rows affected (0.00 sec) And then your queries return correct timestamps, you've found a bug. I'd hope that it would fail, but the only thing I can think of is if it's being set as a global variable. If this does fix your problem, and if you're using replication, you may have an issue with your replicated data. Replication uses timestamp extensively. On 6/27/13 1:44 PM, Andy Wallace awall...@ihouseweb.com wrote: But the question is how. I have nothing in the code that does it, or this would have been true for months instead of just the last 24 hours. In addition, this is currently set globally - no matter what connection to the database, it all comes up with this value. Which means that all my time-based queries no longer work correctly. Does your message suggest that setting it to 0 might clear the problem? On 6/27/13 10:31 AM, Stillman, Benjamin wrote: Timestamp is a session variable, so it must have been set to something other than 0 (1372228034 epoch is the date you're showing) in your current session. mysql set timestamp = 1372228034; Query OK, 0 rows affected (0.00 sec) mysql select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-26 02:27:14 | 2013-06-27 13:20:48 | +-+-+ 1 row in set (0.00 sec) mysql set timestamp = 0; Query OK, 0 rows affected (0.00 sec) mysql select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-27 13:21:34 | 2013-06-27 13:21:34 | +-+-+ 1 row in set (0.00 sec) Cliff's notes: set timestamp = 0; On 6/26/13 6:10 PM, Andy Wallace awall...@ihouseweb.com wrote: We've been having some issues with one of our MySQL servers lately, and currently the dang thing is stuck. For at least the last hour, NOW() is returning the same value: mysql select now(); +-+ | now() | +-+ | 2013-06-26 02:27:14 | +-+ The system variable timestamp also has that same time value stored in it. How can we kick this loose so that the values are more current with real time? (it is currently 3:08PM here, despite our MySQL instance thinking it's 2am. The system time on the machine is correct: $ date Wed Jun 26 15:08:56 PDT 2013 This is MySQL 5.1.46 running on solaris2.10. Any ideas short of restarting the MySQL engine? I'm willing to do that, but would much rather wait and not do it in the middle of the day. Thanks, Andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason
Re: NOW() is stuck...
Benjamin - Unfortunately: mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372238834 | +---++ 1 row in set (0.00 sec) And: mysql set global timestamp = 0; ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL This does indeed persist across sessions. Any command line connection I make to the database shows the bad value for NOW(). I also tweaked the application code to include NOW() in an existing query, and the value returned to my PHP code is also the bad value. Thanks for looking, andy On 6/27/13 11:10 AM, Stillman, Benjamin wrote: It persists across sessions? Does this return anything: show global variables like 'timestamp'; Hopefully it returns: Empty set (0.00 sec) I vaguely remember reading about a bug in 5.1.4x with something to do with a global timestamp. I thought it only showed one though, and that you couldn't set it. If the above returned a timestamp and not an empty set, try: set global timestamp = 0; That should return something like this: ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL But if it returns: Query OK, 0 rows affected (0.00 sec) And then your queries return correct timestamps, you've found a bug. I'd hope that it would fail, but the only thing I can think of is if it's being set as a global variable. If this does fix your problem, and if you're using replication, you may have an issue with your replicated data. Replication uses timestamp extensively. On 6/27/13 1:44 PM, Andy Wallace awall...@ihouseweb.com wrote: But the question is how. I have nothing in the code that does it, or this would have been true for months instead of just the last 24 hours. In addition, this is currently set globally - no matter what connection to the database, it all comes up with this value. Which means that all my time-based queries no longer work correctly. Does your message suggest that setting it to 0 might clear the problem? On 6/27/13 10:31 AM, Stillman, Benjamin wrote: Timestamp is a session variable, so it must have been set to something other than 0 (1372228034 epoch is the date you're showing) in your current session. mysql set timestamp = 1372228034; Query OK, 0 rows affected (0.00 sec) mysql select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-26 02:27:14 | 2013-06-27 13:20:48 | +-+-+ 1 row in set (0.00 sec) mysql set timestamp = 0; Query OK, 0 rows affected (0.00 sec) mysql select now(), sysdate(); +-+-+ | now() | sysdate() | +-+-+ | 2013-06-27 13:21:34 | 2013-06-27 13:21:34 | +-+-+ 1 row in set (0.00 sec) Cliff's notes: set timestamp = 0; On 6/26/13 6:10 PM, Andy Wallace awall...@ihouseweb.com wrote: We've been having some issues with one of our MySQL servers lately, and currently the dang thing is stuck. For at least the last hour, NOW() is returning the same value: mysql select now(); +-+ | now() | +-+ | 2013-06-26 02:27:14 | +-+ The system variable timestamp also has that same time value stored in it. How can we kick this loose so that the values are more current with real time? (it is currently 3:08PM here, despite our MySQL instance thinking it's 2am. The system time on the machine is correct: $ date Wed Jun 26 15:08:56 PDT 2013 This is MySQL 5.1.46 running on solaris2.10. Any ideas short of restarting the MySQL engine? I'm willing to do that, but would much rather wait and not do it in the middle of the day. Thanks, Andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code
Re: NOW() is stuck...
Well, that begs the question - will restarting the MySQL server instance tonight fix the current problem? We do have a plan in place to test and eventually deploy a more recent version of MySQL (5.6?), but for now, I have to support 1000's of customers. My fingers are crossed. On 6/27/13 12:22 PM, Claudio Nanni wrote: Hi, On 06/27/2013 08:19 PM, Andy Wallace wrote: Benjamin - Unfortunately: mysql show global variables like 'timestamp'; +---++ | Variable_name | Value | +---++ | timestamp | 1372238834 | +---++ 1 row in set (0.00 sec) And: mysql set global timestamp = 0; ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL Then, as Benjamin said, you have found the bug. 'GLOBAL timestamp' should not exist http://bugs.mysql.com/bug.php?id=49686 Your GLOBAL (ghost) instance of this variable sets the SESSION one at every client connection. But you are sort of trapped because there is no syntax to manipulate that GLOBAL instance. Also, sadly the manual page does not explain what happens if you set it to DEFAULT: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html Cheers -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: NOW() is stuck...
Ok, I appreciate the Einsteinian and Vonnegut humor... just wanted to say. Still have the problem though. 8-( On 6/27/13 9:51 AM, Nick Khamis wrote: Just out of curiosity, is the hardware stationed, or traveling close to the speed of light (i.e., 18,000 miles per second)? Sorry I could not help it N. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
NOW() is stuck...
We've been having some issues with one of our MySQL servers lately, and currently the dang thing is stuck. For at least the last hour, NOW() is returning the same value: mysql select now(); +-+ | now() | +-+ | 2013-06-26 02:27:14 | +-+ The system variable timestamp also has that same time value stored in it. How can we kick this loose so that the values are more current with real time? (it is currently 3:08PM here, despite our MySQL instance thinking it's 2am. The system time on the machine is correct: $ date Wed Jun 26 15:08:56 PDT 2013 This is MySQL 5.1.46 running on solaris2.10. Any ideas short of restarting the MySQL engine? I'm willing to do that, but would much rather wait and not do it in the middle of the day. Thanks, Andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Understanding Slow Query Log
Ok, this raises a question for me - what's a better way to do pagination? On 9/5/12 2:02 PM, Rick James wrote: * LIMIT 0, 50 -- are you doing pagination via OFFSET? Bad idea. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Understanding Slow Query Log
Thanks, Rick - definitely something to think about. I've been troubled by the pagination stuff in our code. This looks like something I can definitely use! andy On 9/5/12 2:40 PM, Rick James wrote: Remember where you left off. Your Next button now says something like ?page=5size=50 When you get there, you are doing something like SELECT ... ORDER BY ... LIMIT 250, 50 Instead... Make it say ?after_id=12345size=50 and then do SELECT ... WHERE id 12345 ORDER BY ... LIMIT 51 With 51, you get 3 things: * the 50 items (or fewer) for the page * a clue that there will be a Next page * the id of the first item for that Next page 'Exercises for the reader': * 'Prev' * each of the next 5 * each of the previous 5 * go to last page * go to first page * Knowing whether to have those links or 'gray them out'. A sample UI layout (you've probably seen web pages like this): GoTo Page [1] ... [13] [14] 15 [16] [17] ... [last] Where * [] represents a link. * You are currently (for this example) on page 15 * It is showing you only the Next/Prev 2 pages. I have encountered multiple cases where a crawler (eg, search engine) brought a site to its knees because of pagination via OFFSET. Pagination via OFFSET is Order(N) to fetch a page; Order(N*N) to scan the entire list. The first page takes 1 unit of effort. The second takes 2; etc. By the time the entire list has been paged through, about N*N/2 units of work have been done. My technique is Order(1) for a page, Order(N) for a complete scan. N is the number of pages. Some implementations have more than 10,000 pages. 10,000 * 10,000 = 100 million ! -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Wednesday, September 05, 2012 2:05 PM To: mysql@lists.mysql.com Subject: Re: Understanding Slow Query Log Ok, this raises a question for me - what's a better way to do pagination? On 9/5/12 2:02 PM, Rick James wrote: * LIMIT 0, 50 -- are you doing pagination via OFFSET? Bad idea. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: strange select/join/group by with rollup issue....
Thanks, it seems to be working now. I just discovered WITH ROLLUP. It made me very happy on this project... On 2/8/12 2:54 AM, Arthur Fuller wrote: I'm not sure your method isn't working, but try changing changing the to date part to '2012-02-08' and see what you get. HTH, Arthur -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
strange select/join/group by with rollup issue....
I am having a problem with select results that I don't understand. It seems to be tied up with a GROUP BY statement. Forgive the complexity of the SQL, I inherited some problematic data structuring. If I use this statement: SELECT lu_rcode_bucket.bucket AS 'BUCKET', CP_PKG.value AS 'PRODUCT', CP_PKG.value 'PACKAGE', client.active AS 'ACTIVE', client.created AS 'CREATED', count(*) as 'CNT' FROM client JOIN client_profile CP_RCODE ON client.acnt = CP_RCODE.acnt AND CP_RCODE.item = 'rcode' JOIN lu_rcode_bucket ON INSTR(CP_RCODE.value, lu_rcode_bucket.prefix) = 1 JOIN client_profile CP_PKG ON client.acnt = CP_PKG.acnt AND CP_PKG.item = 'pkg' LEFT JOIN client_profile CP_IDX_PKG ON client.acnt = CP_IDX_PKG.acnt AND CP_IDX_PKG.item = 'IDX_PKG' WHERE client.created = '2012-02-07' AND client.created = '2012-02-07' GROUP BY BUCKET, PRODUCT, PACKAGE, active with ROLLUP I get what I expect, having a number of rows where the client.created date is 2012-02-07. But if I change it to this (the only change is the from date): SELECT lu_rcode_bucket.bucket AS 'BUCKET', CP_PKG.value AS 'PRODUCT', CP_IDX_PKG.value 'PACKAGE', client.active AS 'ACTIVE', client.created AS 'CREATED', count(*) as 'CNT' FROM client JOIN client_profile CP_RCODE ON client.acnt = CP_RCODE.acnt AND CP_RCODE.item = 'rcode' JOIN lu_rcode_bucket ON INSTR(CP_RCODE.value, lu_rcode_bucket.prefix) = 1 JOIN client_profile CP_PKG ON client.acnt = CP_PKG.acnt AND CP_PKG.item = 'pkg' LEFT JOIN client_profile CP_IDX_PKG ON client.acnt = CP_IDX_PKG.acnt AND CP_IDX_PKG.item = 'IDX_PKG' WHERE client.created = '2012-02-01' AND client.created = '2012-02-07' GROUP BY BUCKET, PRODUCT, PACKAGE, active with ROLLUP The results contain no data with client.created = 2012-02-07. If I get rid of the group by (and the count(*)), there are rows with all 7 dates. I have tried changing the to date from '2012-02-07' to '2012-02-08', in case this was a less than issue, but that doesn't change. Why is the group by dropping the last date of my data? thanks, andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: why does basic MySQLdump on db table fail with Permission denied, or else it creates a zero KB file
I would suggest trying: mysqldump -uroot -p myDBname myTableName /tmp/myTestDumpedTable.sql Maybe you don't have permission (or space) to write into /usr/local/mysql/bin. That would be an unusual place for such files. On 12/29/11 9:15 AM, Govinda wrote: Hi Everyone This should be quick and simple, but after researching on Google quite a bit I am still stumped. I am mostly newbie with: server admin, CLI, MySQL. I am developing my PHP site locally, and now need to move some new MySQL tables from my local dev setup to the remote testing site. First step for me is just to dump the tables, one at a time. I successfully login to my local MySQL like so: Govind% /usr/local/mysql/bin/mysql -uroot but while in this dir (and NOT logged into MySQL): /usr/local/mysql/bin ...when I try this: mysqldump -uroot -p myDBname myTableName myTestDumpedTable.sql ..then I keep getting this: myTestDumpedTable.sql: Permission denied. Same result if I do any variation on that (try to dump the whole db, drop the '-p', etc.) On StackOverflow I asked this question [1], and replies there led me to trying being logged in as root user, and then (the same): mysqldump -uroot -p myDBname myTableName myTestDumpedTable.sql produces: sh: mysqldump: command not found ...which is odd because it does produce a zero-KB file named myTestDumpedTable.sql in that dir. So then I try (in Mac OS X Terminal, while logged in as me (not root)): mysqldump -uroot -p myDBname myTableName ~/myTestDumpedTable.sql ...and again it produces: sh: mysqldump: command not found... and again a zero-KB file named myTestDumpedTable.sql, in ~/ I am embarrassed as I am sure this is going to be incredibly simple, or just reveal a gaping (basic) hole in my knowledge. .. but I would appreciate any help ;-) [1] http://stackoverflow.com/questions/8663454/why-does-basic-mysqldump-on-db-table-fail-with-permission-denied Thanks -Govinda -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Deleting Records in Big tables
I've had some luck in the past under similar restrictions deleting in chunks: delete from my_big_table where id 2474 limit 1000 But really, the best way is to buy some more disk space and use the new table method On 11/4/11 1:44 AM, Adarsh Sharma wrote: Thanks Anand, Ananda Kumar wrote: Why dont you create a new table where id 2474, rename the original table to _old and the new table to actual table name. I need to delete rows from 5 tables each 50 GB , I don't have sufficient space to store extra data. My application loads 2 GB data daily in my databases. or You need to write a stored proc to loop through rows and delete, which will be faster. Can U provide me a simple example of stored proc Doing just a simple delete statement, for deleting huge data will take ages. Even the Create Index command on ID takes hours too complete. I think there is no easiest way to delete that rows from mysql tables. regards anandkl On Fri, Nov 4, 2011 at 12:52 PM, Adarsh Sharma adarsh.sha...@orkash.com mailto:adarsh.sha...@orkash.com wrote: Dear all, Today I need to delete some records in 70 GB tables. I have 4 tables in mysql database. my delete command is :- delete from metadata where id2474; but it takes hours to complete. One of my table structure is as :- CREATE TABLE `metadata` ( `meta_id` bigint(20) NOT NULL AUTO_INCREMENT, `id` bigint(20) DEFAULT NULL, `url` varchar(800) DEFAULT NULL, `meta_field` varchar(200) DEFAULT NULL, `meta_value` varchar(2000) DEFAULT NULL, `dt_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`meta_id`) ) ENGINE=InnoDB AUTO_INCREMENT=388780373 ; Please let me know any quickest way to do this. I tried to create indexes in these tables on id, but this too takes time. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Deleting Records in Big tables
Excellent point... replication makes many things trikier On 11/4/11 9:54 AM, Derek Downey wrote: Be careful deleting with limit. If you're replicating, you're not guaranteed the same order of those you've deleted. Perhaps a better way to delete in smaller chunks is to increase the id value: DELETE FROM my_big_table WHERE id 5000; DELETE FROM my_big_table WHERE id 4000; etc -- Derek On Nov 4, 2011, at 12:47 PM, Andy Wallace wrote: I've had some luck in the past under similar restrictions deleting in chunks: delete from my_big_table where id 2474 limit 1000 But really, the best way is to buy some more disk space and use the new table method On 11/4/11 1:44 AM, Adarsh Sharma wrote: Thanks Anand, Ananda Kumar wrote: Why dont you create a new table where id 2474, rename the original table to _old and the new table to actual table name. I need to delete rows from 5 tables each 50 GB , I don't have sufficient space to store extra data. My application loads 2 GB data daily in my databases. or You need to write a stored proc to loop through rows and delete, which will be faster. Can U provide me a simple example of stored proc Doing just a simple delete statement, for deleting huge data will take ages. Even the Create Index command on ID takes hours too complete. I think there is no easiest way to delete that rows from mysql tables. regards anandkl On Fri, Nov 4, 2011 at 12:52 PM, Adarsh Sharmaadarsh.sha...@orkash.commailto:adarsh.sha...@orkash.com wrote: Dear all, Today I need to delete some records in 70 GB tables. I have 4 tables in mysql database. my delete command is :- delete from metadata where id2474; but it takes hours to complete. One of my table structure is as :- CREATE TABLE `metadata` ( `meta_id` bigint(20) NOT NULL AUTO_INCREMENT, `id` bigint(20) DEFAULT NULL, `url` varchar(800) DEFAULT NULL, `meta_field` varchar(200) DEFAULT NULL, `meta_value` varchar(2000) DEFAULT NULL, `dt_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`meta_id`) ) ENGINE=InnoDB AUTO_INCREMENT=388780373 ; Please let me know any quickest way to do this. I tried to create indexes in these tables on id, but this too takes time. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=de...@orange-pants.com -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with slow query
On 3/10/11 10:46 AM, Shawn Green (MySQL) wrote: On 3/10/2011 12:32, Jim McNeely wrote: Rhino, Thanks for the help and time! Actually, I thought the same thing, but what's weird is that is the only thing that doesn't slow it down. If I take out all of the join clauses EXCEPT that one the query runs virtually instantaneously. for some reason it will use the index in that case and it works. If I take out everything like this: SELECT a.IdAppt, a.IdPatient, p.NameLast, p.NameFirst, p.NameMI from Appt_ a LEFT JOIN patient_ p ON a.IdPatient = p.IdPatient WHERE a.ApptDate= '2009-03-01'; 1) Verify that the indexes on `patient_` haven't been disabled SHOW INDEXES FROM `patient_`; http://dev.mysql.com/doc/refman/5.5/en/show-index.html 2) Verify that the data types of `Appt_`.`IdPatient` and `patient_`.`IdPatient` are not incompatible. (for example: one is varchar, the other int) This last one can be HUGE. I tracked a big performance issue to this exact problem - the columns used in the join had the same name, but different data types. Correcting to be the same type (both ints) made a terrific performance increase. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table/select problem...
Greetings, all... I'm having an issue with a SELECT in our system. We have an event log table, with about 9 million rows in it. Inserts happen with some pretty high frequency, and these selects happen periodically. The event_log table is MyISAM, the rest of the tables are InnoDB. What's happening is that, periodically, when this select gets run, the whole damn thing locks up, and that pretty much shuts us down (since many things insert events into the table, and the table gets locked, so all the inserts hang). The statement and the explain for it are below. the enduser table has about a million rows in it, the event_type table 35 rows. The weird part is that, if I strip down the query to use no joins, the explain wants to return about 17,000 rows, but the query itself does the table locking thing. Should we perhaps change the event log to InnoDB to avoid table locking? Might the table itself be corrupt in some way? Any thoughts? thanks, andy EXPLAIN SELECT EL.event_log_id, EL.event_time, DATE_FORMAT(EL.event_time, '%c-%d-%Y %H:%i:%s') as 'time_formatted', ET.event_type_id, ET.description, EL.csr_name, EL.enduser_acnt, EL.csr_name, EL.referer, EL.mls_id, EL.mls_no, EL.ss_id, EL.details, E.fname, E.lname, E.email, E.phone1 FROM event_log EL JOIN event_type ET ON EL.event_type_id = ET.event_type_id JOIN enduser E ON EL.enduser_acnt = E.enduser_acnt WHERE EL.acnt = 'AR238156' AND EL.enduser_acnt != '' AND EL.event_type_id = 'EndUserLogin' AND event_time BETWEEN DATE_SUB(CURDATE(), INTERVAL '7' DAY) AND NOW() ORDER BY EL.event_time DESC *** 1. row *** id: 1 select_type: SIMPLE table: ET type: const possible_keys: PRIMARY key: PRIMARY key_len: 92 ref: const rows: 1 Extra: Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: EL type: index_merge possible_keys: agent,enduser,event_log_ibfk_1 key: agent,event_log_ibfk_1 key_len: 62,92 ref: NULL rows: 1757 Extra: Using intersect(agent,event_log_ibfk_1); Using where *** 3. row *** id: 1 select_type: SIMPLE table: E type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: idx_acnt.EL.enduser_acnt rows: 1 Extra: Using where 3 rows in set (0.00 sec) -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question about database value checking
So, a problem popped up today that has caused us no end of hair-pulling, and
it brought to mind a similar issue that I found very, well, wrong.
If you have a table defined:
CREATE TABLE `tester_table` (
`acnt`varchar(20) NOT NULL DEFAULT '',
`method` varchar(10) NOT NULL DEFAULT '',
`card_num`varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`acnt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And try this:
INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', NULL);
That fails. and gives a nice error. But:
INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', 'A12345');
UPDATE tester_table set card_num = NULL WHERE acnt = 'test1';
That succeeds, but it puts an empty string into the card_num column. I
would have thought (hoped) that an error would be thrown in that case as
well. On a similar note, the following table:
CREATE TABLE `tester_table2` (
`acnt` varchar(20) NOT NULL,
`required` enum('NO','DETAILS','RESULTS') NOT NULL,
PRIMARY KEY (`acnt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Lets you insert:
INSERT INTO tester_table2 (acnt, required) VALUES ('A123456', 'THIS IS NOT
REAL');
Though it just puts an empty string into the required column.
Is there a setting for mysql to return errors in these cases? It seems silly to
set
up an enum column, or a not null column, and not have the possible values
enforced?
thanks,
andy
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about database value checking
Thanks Peter, exactly what I was hoping for!
andy
On 2/4/11 3:11 PM, Peter He wrote:
Are you using the strict SQL mode? Check your my.cnf file.
Peter
Date: Fri, 4 Feb 2011 14:08:01 -0800
From: awall...@ihouseweb.com
To: mysql@lists.mysql.com
Subject: Question about database value checking
So, a problem popped up today that has caused us no end of hair-pulling, and
it brought to mind a similar issue that I found very, well, wrong.
If you have a table defined:
CREATE TABLE `tester_table` (
`acnt` varchar(20) NOT NULL DEFAULT '',
`method` varchar(10) NOT NULL DEFAULT '',
`card_num` varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`acnt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And try this:
INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', NULL);
That fails. and gives a nice error. But:
INSERT INTO tester_table (acnt, method, card_num)
VALUES ('test1', 'push', 'A12345');
UPDATE tester_table set card_num = NULL WHERE acnt = 'test1';
That succeeds, but it puts an empty string into the card_num column. I
would have thought (hoped) that an error would be thrown in that case as
well. On a similar note, the following table:
CREATE TABLE `tester_table2` (
`acnt` varchar(20) NOT NULL,
`required` enum('NO','DETAILS','RESULTS') NOT NULL,
PRIMARY KEY (`acnt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Lets you insert:
INSERT INTO tester_table2 (acnt, required) VALUES ('A123456', 'THIS IS NOT
REAL');
Though it just puts an empty string into the required column.
Is there a setting for mysql to return errors in these cases? It seems silly to
set
up an enum column, or a not null column, and not have the possible values
enforced?
thanks,
andy
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=phe1...@hotmail.com
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.
--
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 Parallel Inserts
Shawn/Krishna, Thank you. I will try this. -Andy On Tue, Dec 14, 2010 at 8:13 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Andy, I agree, prefer LOAD DATA INFILE... command for bulk loading. It is 30% faster than normal inserts. Krishna On Tue, Dec 14, 2010 at 8:02 AM, Andy listan...@gmail.com wrote: Greetings everyone. I am in a situation where I need to do parallel inserts into MySQL database from inside my Perl program. Basically, I have several million records to insert into the database, and hence I would rather do them in parallel than doing them one at a time. I looked around but did not find any information on doing this. Does MySQL not support parallel reads/writes? Thanks in advance. Andy
MySQL Parallel Inserts
Greetings everyone. I am in a situation where I need to do parallel inserts into MySQL database from inside my Perl program. Basically, I have several million records to insert into the database, and hence I would rather do them in parallel than doing them one at a time. I looked around but did not find any information on doing this. Does MySQL not support parallel reads/writes? Thanks in advance. Andy
Re: question about restoring...
Thanks, guys. I have copies of the innodb files. The boss went whole hog on using zfs for everything, so backups of files are readily available. Looks like I'll be having the db reconstituted... thanks again On 11/12/10 1:05 AM, Johan De Meersman wrote: From the OP: I have a copy of the INNODB files for these two tables - is there a way to extract the table contents from these files short of a full import? I have to agree, that's quite ambiguous. Andy, is it a copy of the innoDB datafiles, or a database dump that you have ? In the latter case, it's reasonably simple to extract what you need; in the former case you're gonna have to try attaching them to a new instance - good luck with that. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
question about restoring...
So, I got a request this morning to recover some specific records for a client. I just want a handful of records from a couple of tables here. I have a copy of the INNODB files for these two tables - is there a way to extract the table contents from these files short of a full import? thanks, ansdy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Update query problem
So I'm having a problem with an update query. I have three tables: Table: A Columns: acnt, name, company, email, domain Table: AM Columns: acnt, m_id Table: M Columns: m_id, name, company, email, domain and I want to conditionally update the columns in one to values from the other. i.e., I want to put the value of A.name into M.name, but only if M.name is currently NULL, AND A.name has a usable value (not an empty string). This is what I came up with, but it doesn't work - it only replaces the values where the column in M is not null. update A join AM on A.acnt = AM.acnt joinM on AM.m_id = M.m_id SET M.name= IF( (!M.nameAND A.name != ''),A.name,M.name), M.company = IF( (!M.company AND A.company != ''), A.company, M.company), M.email = IF( (!M.email AND A.email != ''), A.email, M.email), M.domain = IF( (!M.domain AND A.domain != ''), A.domain, M.domain) Any thoughts? THanks, andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Possible tricks to ALTER on huge tables?
I had to do this trick with a few million rows in the table, and what I did was to create a new table with the required structure, then did insert into select from, starting with the newest data first, cause that made sense for my application. Then, renamed the old table and the new. YMMV andu Jigal van Hemert wrote: Daevid Vincent wrote: We currently have some tables that are approaching 1 BILLION rows (real Billion, with nine zeros, not that silly six zero version). Trying to do an ALTER on them to add a column can sometimes take hours. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: IS NULL returns Empty set, but I have empty items
blank is not null... I'll bet if you did SELECT url FROM product WHERE url = ''; you'll get a row or three. If the field was NULL, your product_id select would look like: +--+ | url | +--+ |NULL | +--+ andy Norman Khine wrote: Hello, I don't know what I am missing, but I have this: mysql SELECT url FROM product WHERE url IS NULL; Empty set (0.05 sec) mysql SELECT url FROM product WHERE product_Id = 67; +--+ | url | +--+ | | +--+ 1 row in set (0.00 sec) what goes with this, how can i ensure that this is NULL? here is the table structure http://pastie.org/1066140 thanks for any insight. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- 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 For Huge Collections
Hello all, Thanks much for your replies. OK, so I realized that I may not have explained the problem clearly enough. I will try to do it now. I am a researcher in computational linguistics, and I am trying to research language usage and writing styles across different genres of books over the years. The system I am developing is not just to serve up e-book content (that will happen later possibly) but to help me analyze at micro-level the different constituent elements of a book ( say at chapter level or paragraph level). As part of this work, I need to break-up, store and repeatedly run queries across multiple e-books. Here are several additional sample queries: * give me books that use the word ABC * give me the first 10 pages of e-book XYZ * give me chapter 1 of all e-books Definitely, at a later stage when I start making my research available to the community, I will need to be able to provide fulltext (or chapter-wise) search also to the users, among other things. Please let me know if you have additional comments. Andy On Thu, Jun 10, 2010 at 9:05 PM, Peter Chacko peterchack...@gmail.comwrote: Usually, you better use a NAS for such purpose. Database is designed to store highly transactional, record oriented storage that needs fast access... You can look for any Enterprise content management systems that rest its storage on a scalable NAS, with file virtualization in the long run. thanks On Fri, Jun 11, 2010 at 8:04 AM, SHAWN L.GREEN shawn.l.gr...@oracle.com wrote: On 6/10/2010 10:16 PM, Andy wrote: Hello all, I am new to MySQL and am exploring the possibility of using it for my work. I have about ~300,000 e-books, each about 100 pages long. I am first going to extract each chapter from each e-book and then basically store an e-book as a collection of chapters. A chapter could of course be arbitrarily long depending on the book. My questions are: (1) Can MySQL handle data of this size? (2) How can I store text (contents) of each chapter? What data type will be appropriate? longtext? (3) I only envision running queries to extract a specific chapter from a specific e-book (say extract the chapter titled ABC from e-book number XYZ (or e-book titled XYZ)). Can MySQL handle these types of queries well on data of this size? (4) What are the benefits/drawbacks of using MySQL compared to using XML databases? I look forward to help on this topic. Many thanks in advance. Andy Always pick the right tool for the job. MySQL may not be the best tool for serving up eBook contents. However if you want to index and locate contents based on various parameters, then it may be a good fit for the purpose. Your simple queries would best be handled by a basic web server or FTP server because you seem to want http://your.site.here/ABC/xyz where ABC is your book and xyz is your chapter. Those types of technology are VERY well suited for managing the repetitive streaming and distribution of large binary objects (chapter files) like you might encounter with an eBook content delivery system. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=peterchack...@gmail.com
MySQL For Huge Collections
Hello all, I am new to MySQL and am exploring the possibility of using it for my work. I have about ~300,000 e-books, each about 100 pages long. I am first going to extract each chapter from each e-book and then basically store an e-book as a collection of chapters. A chapter could of course be arbitrarily long depending on the book. My questions are: (1) Can MySQL handle data of this size? (2) How can I store text (contents) of each chapter? What data type will be appropriate? longtext? (3) I only envision running queries to extract a specific chapter from a specific e-book (say extract the chapter titled ABC from e-book number XYZ (or e-book titled XYZ)). Can MySQL handle these types of queries well on data of this size? (4) What are the benefits/drawbacks of using MySQL compared to using XML databases? I look forward to help on this topic. Many thanks in advance. Andy
Re: Connecting Remotely to MySQL Server
OK so I managed to open 3306. Now when I connect using 127.0.0.1 from the intranet box it works, but when I connect using it's IP it's still the same situation :( And now when I connect remotely (from my front end which is accessible via WWW), it says ERROR 1045 (28000): Access denied for user 'andy'@'frontend.internal' (using password: YES). Does it have something to do with the fact that 'andy' in the mysql user tables is only authorized to connect from 'localhost'? How do I fix this problem. Thank you very much. Andy On Mon, Jun 7, 2010 at 3:41 PM, Qu, Gang gan...@quickenloans.com wrote: Make sure the port is open from the db server. you can test it using telnet server 3306(if that's what you use). If not, have your network admin open the port for you. -Original Message- From: listan...@gmail.com [mailto:listan...@gmail.com] Sent: Monday, June 07, 2010 3:37 PM To: mysql@lists.mysql.com Subject: Connecting Remotely to MySQL Server Hello all, New to MySQL and still exploring. I have MySQL installed on one of the boxes on the intranet (on which I am not the root) and am trying to connect to it remotely via a box (that can be see from the external world). Unfortunately it does not seem to be working. Here's what I did from the shell: my sql -h IP_OF_INTRANET_BOX -u andy -p After I do this, it asks for my password, and after I enter it, it just sits there doing nothing. I am however able to login using my credentials from the intranet box directly. However when from the intranet box, I try to login using the exact command above, it's exactly the same situation - it just sits there after I enter the password. Any suggestions? Thanks. Andy
logging of BAD queries
I've used the general and slow query log in the past, but I am trying to track down some queries from a compiled app that never seem to be hitting the DB server. My guess is that the SQL syntax is bad and never get executed, but I don't see any related queries in the general query log. Does the general log include invalid SQL? I've also tried to use the driver logging, but on Windows it overwrites with the last SQL command so I cannot get a good capture as requests are sent to the DB. DB is MySQL 5.0.x Thanks andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: logging of BAD queries
Unfortunately, I'm using a commercial application and trying to debug as to why some data does and does not get updated properly. On Feb 9, 2010, at 2:57 PM, mos wrote: I do something like that in my compiled application. All SQL queries are sent to a single procedures and executed there. I trap any errors and log the SQL in a table along with the error message. This is useful to determine if someone is trying to break into the database (sql injection). Having a central procedure to execute all queries is paramount in controlling and capturing errors. I can also unplug and plug in a different database engine quite easily rather than hunting down all direct calls to the database. I also don't have to worry about trapping errors throughout the application. It's all done at one central point. I've been doing it this way for 5 years and would never start a large application without it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join on a where clause.
A couple of thoughts - it's not no quotes on integers, but no quotes around column references. When you use 'mappings.end_ip', you are saying the string mappings.end_ip, and not referring to a column in the mappings table. It just becomes a constant at that point. As for the performance, you should look at the indices on the tables involved. Try doing explain on the query: EXPLAIN SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event join mappings ON event.src_ip between mappings.start_ip and mappings.end_ip WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' GROUP BY event.src_ip ORDER BY count DESC LIMIT 20; That'll give you some more information on what mysql is doing with your data. andy Paul Halliday wrote: On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi everybody! Neil Aggarwal wrote: Paul: SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN 'mappings.start_ip' AND 'mappings.end_ip' GROUP BY event.src_ip ORDER BY count DESC LIMIT 20; I am surprised by the quotes you have around the start_ip and end_ip columns; to me, this makes that look like strings. That because I don't know what I am doing :). No quotes on integers; got it! From your posting, I see the result you hope to get but not the one you actually get. IMO, just dropping the single quotes around the two column names should produce the data you want to get. Or what is the result you receive? Removing the quotes does work. The query however took 1h15m to complete. Yuck. I am guessing this is because even though there is a limit, it is still doing the lookup on everything past 20. Also, if the first address has a count of say 2000, it would be doing the lookup 2000 times for a single address. Is that right? If it is I guess I will have to post process the results. Which is fine, I just like to keep as much in the queries as I can. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: inserting csv - solved, but more to the puzzle
I've run into similar situations w/regard to Mac vs PC CSV files, it usually has to do with the EOL character sequence. Macs use LF (chr(10)), while PCs use CRLF (chr(13)chr(10)). andy Patrice Olivier-Wilson wrote: In case anyone might find this of the least interest, probably not, but I always hope to add to discussion just as part of the thank you for help. Further work with same issues found that a file received from a PC based client, if opened in PC environment, didn't have the same problems (so far) as if opened in a Mac environment. The next file I had to work with in this particular project, I opened in PC, then uploaded using phpMyadmin with no issues. Same client, same type of file. So, maybe there is a PC/Mac thing happening to cause the line 17, missing commas in csv? Just a thought. If anyone has any insight, most appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Where the hell did 5.4 come from?
My thoughts exactly! This article might help: http://dev.mysql.com/tech-resources/articles/mysql-54.html It worries me though that 5.1 went through a large number of alpha releases, then a set of beta releases before the GA release came out. It looks like they've thrown 5.4 straight out without anyone even being aware that it existed! Hell, 6.0 is on its tenth release and it's still in alpha. Like you say, it'd be interesting to see which blackhole 5.2 and 5.3 fell into...! Andy Daevid Vincent wrote: Have I been in a coma or something? WTF happened to 5.2 and 5.3? Hell, we're still on 5.0.51 and 5.1 just came out a month or two ago right? -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 30, 2009 7:40 AM To: mysql@lists.mysql.com Subject: Is there a MySQL 5.4 Speed advantage for MyISAM tables? I see MySQL 5.4 is out. http://www.mysql.com/news-and-events/generate-article.php?id=1602 Sun claims there are speed improvements for Innodb and ClusterDb tables, but is there any reason to upgrade if I'm only using MyISAM tables? Also I didn't see a Windows binary download. Does this mean I have to compile the source from one of the Linux distros? What compiler do I use? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Start MySQL with --intit-file?
Hi,
try putting the full path to the init file, and also try putting
the update command after the two grant commands (if you put it first
and the user r...@173.8.172.53 didn't already exist then the password
will not be set for that user) and put a flush privileges as the last
line ,
thanks Andy.
Quoting Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com:
Hi,
I am trying:
[r...@server1 ~]# /etc/init.d/mysqld restart --init-file=cloudsql.sql
Stopping MySQL:[ OK ]
Starting MySQL:[ OK ]
the cloudsql.sql file contains:
UPDATE mysql.user SET Password=PASSWORD('xxx') WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON mysql.* TO 'root'@'localhost';
FLUSH PRIVILEGES;
GRANT ALL ON mysql.* TO 'root'@'173.8.172.53';
commit;
But I still get that I cannot connect from 173.8.172.53
I still dont see what I am doing wrong...
-Jason
--
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 on Ultrasparc T2 and floating point performance
Hi Dan, Yes, actually, I already know that parallel performance will be much improved, however, I was expecting more improvement on single threads as well, since the specs say that it takes 40 clock cycles just to access the FPU on the T1, but something like 6 clock cycles on the T2. So just from that perspective it seems like there should be a significant improvement for single threads, not just parallel performance. At least that's the way I read the docs from Sun on this. At any rate, my expectations here are clearly wrong, and I guess I'd just like a better understanding of why I'm getting it wrong. Hi Rod, I wouldn't expect any noticable difference running a single query that is taking anthing than less than a second if the only technical difference is that it takes 34 less clock cylces to access the FPU, thats 34 clock cycles on a cpu with 1GHz clock. The additional FPUs and reduced clock cycles to access the FPUs simply improve preformance when running mutliple threads that require FPU calculations, ie almost no overhead up to 8 simultaneous FPU calculations and less overhead when the number of simultaneous calculations goes over the number of physical FPUs due to the reduced clock cycles to access a FPU. In general my understanding of the T1 vs T2 architectures is that the CPU and FPU cores are essentially the same regarding preformance per thread but with additional threads per core and additional FPUs and some other nice bits like on board Gig Ethernet and support for 2 socket servers, plus any bump in the clock speed over the older boxes obviously. thanks Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Oracle , what else ?
Hi, To see what will happen to MySQL take a look at how Oracle handled InnoDb. How many updates have they released since they purchased it? I really don't know so someone will need to check. Is Oracle is too big to make MySQL updates any kind of priority? It seems that the larger the company and the more products they have, the less interest they have in their lower revenue making products. I hope this is not the case with Oracle, but the updates in the next year will determine where MySQL is headed. On a similar note, Oracle bought Sleepycat in February 2006 and hence acquired the embedded BerkeleyDB database in the process. In the 3 years since then I believe there has been two updates released to BerkeleyDB. Previous to the acquisition I was updating BerkeleyDB on my servers roughly once every few months. Personally (and I hope I'm wrong) I don't believe there's room in Oracle's portfolio for two diverse RDBMSs, and I envisage them re-branding MySQL as an Oracle open-source derivative which begins as being the MySQL codebase but is slowly migrated toward Oracle's engineering, to ease the transition for growing companies moving from MySQL/Oracle open-source to the Oracle enterprise versions. Having said that this is pure speculation, and only yesterday I read something in the manual that a particular option was going to be deprecated in MySQL 7 - we haven't even seen 6 in beta yet! Like Mike said, the next year or so will tell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with mysql prob
Hi,
abdulazeez alugo wrote:
Date: Sun, 19 Apr 2009 23:19:56 +0100
From: andy-li...@networkmail.eu
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: Need help with mysql prob
Hi Alugo,
Hi Andy,
Thanks for your prompt response. However, since tbl1_id has an auto_increment value in tbl1, mysql is actually generating the values for it automatically.
Ah, I see your point. I'm guessing by your code you're using PHP? If
so call mysql_insert_id() after you've inserted your record into tbl1.
That will give you the ID of the auto increment column - see the note in
the below link about bigint columns if your data type is bigint.
http://uk3.php.net/mysql_insert_id
Hope this helps,
Andy
Yes I'm using PHP. Can it be something like
function newPost_tbl1($id, $entry, $text)
{
$conn;
$result= mysql_query(INSERT INTO tbl1 (tbl1_id, entrytitle, entrytext)
VALUES ('$id','$entry', $text)',
$conn);
$tbl1_id = mysql_insert_id($conn);
}
Yep that's about it. You said that tbl1_id is an auto-increment column,
why are you including it in the insert query? You should just need
entrytitle and entrytext, then $tbl1_id will be the value of the tbl1_id
field.
I hope you've just missed out everything on the $conn line just for
short-hand, because $conn needs to be a valid connection resource
(result from mysql_connect) before passing it to mysql_query. Also on
your MySQL query line, you've started the insert command string with a
quote but terminated it with a single apostrophe, this terminator
should also be a quote. Your $text should also be enclosed with a
single apostrophe, and don't forget to clean your input ($entry and
$text) otherwise you'll be vulnerable to SQL injection and XSS attacks.
Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with mysql prob
Hi Alugo, abdulazeez alugo wrote: Hi Andy, Thank you very much you have been really very helpful. All those mistakes you pointed at in the script about the apostrophe and others, are simple mistakes I just made in the rush of typing the message; and yes $conn is the result from mysql_connect. In my original script I clean my script to avoid mysql injection. No worries, just thought I'd point it out in case ;-) However, if you look at the code very well, you'll realize that I have stored the result of the mysql_insert-id($conn) in a php variable $tbl1_id and this is defined within a function *newPost_tbl1($id, $entry, $text).* My problem now is how to access that variable in the function that inserts into the child table so that I'll have the same value for tbl1_id in tbl1 and tbl1_id in tbl2. Why not just return it from the function? $tbl1_id = mysql_insert_id(); return $tbl1_id; Then in the code that calls newPost_tbl1 ... $tbl1_id = newPost_tbl1($id, $entry, $text); newPost_tbl2($tbl1_id, ); Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Sun bought by Oracle
I've just been made aware by a client that Oracle have purchased Sun Microsystems. The article below on Sun's website mentions that Oracle are committed to Linux and other open platforms and mentions the fact that Java touches practically every business system around. http://www.sun.com/third-party/global/oracle/index.jsp I wonder what Oracle's plans are when it comes to MySQL? There is no mention of MySQL in the above article. Will it eventually come under the Oracle umbrella, much like BerkeleyDB did? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Sun bought by Oracle
Only time will tell what will happen, but I have read a few articles saying Oracle had been interested specifically in buying MySQL from Sun as it gives them an open source DB offering (which they dont have now right?), and that brings with it contact with potential customers for support or non-free solutions etc... However that was from the internet press so its all just rumours and supositions ;) cheers Andy. Quoting Krishna Chandra Prajapati prajapat...@gmail.com: Whats the future of 'MySQL' under Oracle ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with mysql prob
Hi, Now I have successfully created a relationship between the two tables but how do I make sure the value of tbl1_id in tbl1 is equal to the value of tbl1_id in tbl2??? MySQL handles this for you. Simply INSERT the value into tbl1, then INSERT the value of tbl1_id in tbl2. Then try INSERT'ing a value into tbl2 that doesn't exist in tbl1_id and MySQL will throw an error and the insert will fail. Thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with mysql prob
Hi Alugo, Hi Andy, Thanks for your prompt response. However, since tbl1_id has an auto_increment value in tbl1, mysql is actually generating the values for it automatically. Ah, I see your point. I'm guessing by your code you're using PHP? If so call mysql_insert_id() after you've inserted your record into tbl1. That will give you the ID of the auto increment column - see the note in the below link about bigint columns if your data type is bigint. http://uk3.php.net/mysql_insert_id Hope this helps, Andy -- 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 runs on 16-cores server
Hi, In what way can having more cores slow down MySQL (or any other app for that matter)? Are you simlpy referring to the fact that some mutlicore servers might be slower in single threaded preformance than a higher clocked single core system? If I have a mutlicore system with fast single threaded performance I wouldnt expect it to be slower in almost any cases with something like a mutliprocess database system, thanks Andy. Quoting mos mo...@fastmail.fm: Using more cores with MySQL doesn't mean it will run faster. In fact, it could slow it down. Make sure you have done benchmarking with your current computer so you can compare the difference. InnoDb and MyISAM don't scale well with multi-cores I'm afraid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
displaying a specific row within a group by
Consider the ff. table: ++--+--+ | game | rank | date | ++--+--+ | GTA| 11 | 20081001 | ++--+--+ | SPORE | 1 | 20081103 | ++--+--+ | SPORE | 2 | 20091001 | ++--+--+ | SINSOL | 8 | 20081011 | ++--+--+ | SINSOL | 31 | 20080808 | ++--+--+ | SPORE | 50 | 20090402 | ++--+--+ | SINSOL | 11 | 20090104 | ++--+--+ | GTA| 21 | 20080821 | ++--+--+ | WOW| 1 | 20080922 | ++--+--+ | WOW| 11 | 20081023 | ++--+--+ | WOW| 15 | 20090106 | ++--+--+ I want a single SQL query that will return the latest ranking for each game: ++--+--+ | game | rank | date | ++--+--+ | SPORE | 50 | 20090402 | ++--+--+ | SINSOL | 11 | 20090104 | ++--+--+ | GTA| 21 | 20080821 | ++--+--+ | WOW| 15 | 20090106 | ++--+--+ How do I go about it? The initial 'obvious' solution: select game,rank,max(date) from gametbl group by game DOESN'T work because the 'rank' value we get will not necessarily come from the same row holding the 'max(date)'!! Instead, you have to sort by date first in a subquery before applying the GROUP BY: select * from ( select * from gametbl order by date desc ) as t group by game This seems to work in MySQL but I do not trust this construction because it relies on ordering, which relational philosophy is supposed to not depend on. (Will this really safely work on all proper SQL implementations?) Another reason I don't like it is because it relies on a subquery and I was wondering if it is possible to do away with that. I can also imagine a solution relying on a self-join which does not depend on sorting, but which would require a surrogate primary key which I find even less elegant than relying a subquery. = The Webmechs Webpress blog http://www.webmechs.com/webpress/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: db is bring dropped, binlog help
Hi John, I would almost certainly suspect some form of foul play, whether that be internal (i.e. an employee/colleague) or network intrusion. As you've figured the first thing to do is check which MySQL account is dropping the database. You already have the timestamp in your binlog so what you need to is dig around in MySQL's general log file. According to the manual, this log file records not only connections and disconnections, but also the SQL queries sent by the client, so you should be able to see quite clearly the DROP DATABASE statement being issued. See this manual page: http://dev.mysql.com/doc/refman/5.0/en/query-log.html If you already have the query log turned on, then just search for the entries around 15:09:13 09/04/2009 for the DROP statement. If you don't, restart your MySQL server with the logging option turned on and wait for it to happen again! I've just had a quick glance in my server's log and it appears that a connection is given an ID (which is where you see which user it was) then that ID is used throughout the log to indicate which queries have been executed from that connection. Hope you get this sorted out! Andy John Sun wrote: Hi folks, We're experiencing a really bizarre thing. One of our mysql 5.0 databases is mysteriously and constantly being dropped. This is a cap from our binlog: #090409 15:09:13 server id 1 end_log_pos 326997 Query thread_id=9923 exec_time=1 error_code=0 SET TIMESTAMP=1239304153/*!*/; SET @@session.foreign_key_checks=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; DROP DATABASE `prod_db3` /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET completion_ty...@old_completion_type*/; This has occured once a day for the past 3 days. Is there any way to track this down further to which mysql account is being used? Should I suspect foulplay or network intrusion? Appreciate any insight and help. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Data structure for matching for company data
You can use this structure with MyISAM tables. It will work fine except you won't have the advantage of database-level enforcement of foreign key constraints--do it with code. Or use InnoDB tables (enable/load the innobase plugin.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SSL Connections
Hi Stefano, I'm guessing your remote (non-local) server is using a different user account than your local server. r...@localhost r...@% r...@somehost.com are all different users. If your remote host, or wildhost user account has the REQUIRE X509 flag (user must be certified) but your local one doesn't, that would explain it. What are your MySQL versions on both your server and remote client? What errors do you get on your client? Regards, Andy Stefano Elmopi wrote: Hi, I'm trying to make connections ssl. I followed the MySQL guide and I've configured the server then I created a normal user, without the REQUIRE SSL. On the same server can connect with the only option: mysql --ssl-ca=cacert.pem -u Pluto -p but if I try to connect from another host, I must, forcibly, also specify the options: --ssl-cert=client-cert.pem --ssl-key=client-key.pem Why ?? The guide says that even from another host, on a normal user, you can use the only option: --ssl-ca but it is not, I am forced to certify the client. Something wrong? Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need a Brief Overview - SSL Connections
Hi Seth, I implemented SSL successfully just a couple of weeks ago on 5.1.30, and I too found some aspects confusing. Here's my answers from my own experience so please forgive me if they're inaccurate. 1) On the server side, I believe ssl-ca, ssl-cert and ssl-key are all required to establish the server's identity. On the client side, I believe a user can still login without encryption/SSL unless REQUIRE SSL is set on their account. From what I can tell, a client can also login using SSL with just ssl-ca (the server's certification authority certificate.) To verify that the client is who they say they are, then you set REQUIRE X509 on their account, and the client has to connect using ssl-ca, ssl-cert and ssl-key with a valid certificate and private key. 2) I also don't understand all concepts of SSL - I'm used to just providing a certificate request to a trusted partner (e.g. Verisign) and for them to send me back my certificate. However I presume that the trusted CA certificates (e.g. Verisign, Thawte etc) are present on all browsers/e-mail clients and that's why we don't need to worry about it for these types of applications. I believe that MySQL is allowing you to effectively issue your own certificates, and as such the client needs the CA certificate to verify the server's issuing authority (yourself.) The first step the MySQL manual takes you through is creating a private key and a certificate which will become your CA certificate (and is the file needed for ssl-ca/ssl-capath.) This is a different private key and certificate to both the server's and the client's (unless your server/client is the same box, then they can share the same certificate/key but will be different from the CA cert/key.) 3) I followed the instructions in the manual to the letter and I had no issues whatsoever. In my client (Navicat) I do get an SSL connection error - invalid parameter if one of the certificates are missing, but that may be coming from Navicat not the MySQL library. It'll be interesting to see what happens in some situations, for example, what happens when the client certificate or the CA certificate expires? Interestingly I can also still connect using SSL when the CA certificate is invalid (ie. non-existent or a private key file instead of a certificate file.) Hope this helps some! I do agree it wasn't the clearest of things to get my head around. Andy Seth Willits wrote: Howdy, I've read through all of the MySQL docs on SSL and I just need a brief overview of a few things to understand some things that aren't crystal clear to me since I'm not very familiar with SSL. 1) Which SSL options are *required*? It seems that only ssl-key is _always_ required for the client to connect to the server. If REQUIRE X509 is set, then ssl-cert is required as well in order to authenticate who the actual client is, right? 2) The options I don't understand are ssl-ca/ssl-capath. Why would the client specify a certificate authority? Is this the authority (or authorities) that's used to authenticate the server's certificate? Is there a platform default for this value? I'm not used to having to specify a list of authorities for other programs to validate certificates (such as with email). 3) How does I know if the server/client authentication (validating the certificate against given authorities) failed? Do I just get a vague SSL connection error back from MySQL and that's it? I think that's mostly it. Thanks, -- Seth Willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Multiple batch files
Hi David, Why not use a FOR loop in a batch file? This post seems to be doing the same thing, using MS SQL Server: http://bytes.com/topic/windows/answers/647680-xp-batch-file-loop-help Andy David Scott wrote: Thanks for responding Gary I get: --- Volume in drive D has no label. Volume Serial Number is C45F-3341 Directory of D:\data\03\09 03/19/2009 05:45 PM 389,305 sessions-20090309-00.sql 03/19/2009 05:45 PM 402,264 sessions-20090309-01. sql 03/19/2009 05:45 PM 417,579 sessions-20090309-02. sql 03/19/2009 05:45 PM 402,084 sessions-20090309-03. sql etc 220 File(s) 50,149,425 bytes 0 Dir(s) 116,891,242,496 bytes free --- How should I edit this? -- Dave 2009/3/19 Gary Smith g...@primeexalia.com Dir *.sql my.bat Edit batch file for each line to append | mysql Call batch file Sent via BlackBerry by ATT -Original Message- From: David Scott critt...@desktopcreatures.com Date: Thu, 19 Mar 2009 11:08:28 To: mysql@lists.mysql.com Subject: Multiple batch files HeyaI have folders of sql files which, when there were a few, I executed using source d:\folder\file.sqlI now I have 100's, so is there a way to do source d:\folder\*.sql (which sadly does not work) ? How would you go about automating this? The DB is running on a windows box. Thanks for any suggestions you may have. -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls
ggghh mich...@j3ksolutions.com wrote: On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas OK! I do understand, thank you. But hypothetically speaking, what value would you use if you didn't have a I don't what this is value like null? I ask this because I started programming when NULL was really zero, and part of the ASCII collating sequence. I'd use -9., I'd never allow a i don't know what it is value like Null in my database. Mike. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
:S Hi Bryan Actually changing the slave setup after a reboot is a very bad idea, you will have inconsistent data on your slave because every transaction made from when you rebooted the server to when you reset the position will not be copied from the master. Thats the point of the position information, that MySQL can know what data has been replicated and what data has not been. My understanding is that you should never have to reset the binlog position info after initial setup unless you are reinitialising your slave with a new copy of the data from the master because the slave broke for whatever reason. I have several DBs copied to slaves and have never had to reset anything following a reboot of the slave (or master for that matter). If you are finding replication stops after a reboot then I think you have a serious problem, but don't know what that might be. Post some more info and someone on the list can probably help, thanks Andy. Quoting Bryan Irvine sparcta...@gmail.com: The information is there and the replica can connect on a reboot but only if that information has not changed correct? e.g. I reboot the slave, and on the master just run reset master; the bin.01 could change to bin.02 and the pos change from 98 to 15443 or some such. If I do that do I then need to re-run the set master to statements when the slave comes back up? or can that be handled automatically? -Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
BTW there is a list specially dedicated to replication configurations: http://lists.mysql.com/replication -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrading
Hi Mat, How many databases have you got running on 4.0? You can certainly go through the motions of downloading each interim release, however my best advice would be (if time/disk space permits) to dump your databases to plain SQL files (using mysqldump) obliterate your 4.0 install, install a fresh copy of 5.1 and restore your database dumps. If your databases are hefty (I'd say above 1GB) you may need to go for an upgrade, in which case I think you need to go to 4.1 before 5.0 if memory serves me correctly. Of course, depending on your setup, you could install a fresh copy of 5.1 and run it alongside 4.0 (ie on different port numbers) then you can migrate your databases/systems across one-by-one. Andy Matthew Stuart wrote: Hi all, I am on... wait for it... version 4.0.25 and I want to upgrade to MySQL 5.x Is there anything special I should do in order to upgrade? Do I need to uninstall v4 or can I just download the most current version and double click to upgrade? I am not particularly hardcore mysql minded, and quite honestly, if it ain't broke don't fix it is a good mantra of mine... but I need to move on now as I have received an sql file that is v5 compatible but not v4 compatible. Once I have it up and running I'll be fine, it's just that I am nervous about upgrading and consequently breaking it, and at that point, I'll be struggling to put it right. Any advice on how I can best do this / best practices etc will be very much appreciated. Many thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: whoa!
Hi PJ,
By the look of your language you're using PHP to communicate with
MySQL. Is this assumption correct?
If so you don't need to use that section of the manual - that's the C
API for programmers developing applications in the C or C++ language.
PHP is a whole different ball game and you're right - mysql_store_result
would do nothing for you (PHP wouldn't even recognise it as a function.)
If you are using PHP, you'd be better off looking at the PHP manual
instead - http://uk3.php.net/mysql.
Hope this helps,
Andy
PJ wrote:
Hi Andy,
I still don't understand what is the purpose of calling
mysql_store_result().
I got what I needed this way:
$text = Joe of Egypt;
$sql = SELECT title FROM book WHERE title LIKE '$text';
$result = mysql_query($sql);
if (mysql_num_rows($result) 0) {
echo (We have results);
}
else {echo (Empty!);
}
And if I change the Joe of Egypt to Cleopatra of Egypt the return is
correct also.
So why would I need mysql_store_result()? Don't forget, I'm using a
SELECT query, not INSERT, UPDATE or whatever the third was... :-)
PJ
Did I do something wrong?
Andy Shellam wrote:
Hi PJ,
Having been a C programmer for a while this makes perfect sense to
me. You call mysql_(real)_query to execute the query and get the
results from the server. Because the MySQL library doesn't know when
you've finished with the result-set, you have to tell it to write the
results to memory (mysql_store_result.) This can then be read using
mysql_num_rows and other data retrieval functions.
This sentence in the manual makes this clear:
After you have called mysql_store_result() and gotten back a result
that isn't a null pointer, you can call mysql_num_rows() to find out
how many rows are in the result set.
When you're finished with the result-set you can then use
mysql_free_result to release the memory allocated by the library.
What difficulties are you having with error checking? Most MySQL
functions return NULL if the operation fails - mysql_real_connect,
mysql_store_result etc. Others return non-zero, like
mysql_real_query. All the function documentation in the manual gives
the return codes and how to tell if a call failed.
Personally I've found the MySQL manual the best of any documentation
for systems we use, although I agree an example of a simple
client/query would have been useful.
Regards,
Andy
PJ wrote:
What is this supposed to mean from the manual:
The use of |mysql_num_rows()|
http://dev.mysql.com/doc/refman/5.0/en/mysql-num-rows.html depends on
whether you use |mysql_store_result()|
http://dev.mysql.com/doc/refman/5.0/en/mysql-store-result.html or
|mysql_use_result()|
http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html to return
the result set
|mysql_num_rows()|
http://dev.mysql.com/doc/refman/5.0/en/mysql-num-rows.html is intended
for use with statements that return a result set, such as |SELECT|
http://dev.mysql.com/doc/refman/5.0/en/select.html.
Does this mean you have to use
mysql_store_result() before using mysql_num_rows() ? kind of doesn't
make sense to have to do that. And there are no clear cut examples or
explanations...
I do not wish to piss-and-moan but I do find that there is a lot to be
desired in the manual; things that are very ;contradictory and/ or
unclear and certainly lacking in examples.
And please, somebody guide me to some tutorial or something where I can
learn to set up proper error checking so you guys don't have to listen
to my problems. :'(
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: whoa!
Hi PJ, Having been a C programmer for a while this makes perfect sense to me. You call mysql_(real)_query to execute the query and get the results from the server. Because the MySQL library doesn't know when you've finished with the result-set, you have to tell it to write the results to memory (mysql_store_result.) This can then be read using mysql_num_rows and other data retrieval functions. This sentence in the manual makes this clear: After you have called mysql_store_result() and gotten back a result that isn't a null pointer, you can call mysql_num_rows() to find out how many rows are in the result set. When you're finished with the result-set you can then use mysql_free_result to release the memory allocated by the library. What difficulties are you having with error checking? Most MySQL functions return NULL if the operation fails - mysql_real_connect, mysql_store_result etc. Others return non-zero, like mysql_real_query. All the function documentation in the manual gives the return codes and how to tell if a call failed. Personally I've found the MySQL manual the best of any documentation for systems we use, although I agree an example of a simple client/query would have been useful. Regards, Andy PJ wrote: What is this supposed to mean from the manual: The use of |mysql_num_rows()| http://dev.mysql.com/doc/refman/5.0/en/mysql-num-rows.html depends on whether you use |mysql_store_result()| http://dev.mysql.com/doc/refman/5.0/en/mysql-store-result.html or |mysql_use_result()| http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html to return the result set |mysql_num_rows()| http://dev.mysql.com/doc/refman/5.0/en/mysql-num-rows.html is intended for use with statements that return a result set, such as |SELECT| http://dev.mysql.com/doc/refman/5.0/en/select.html. Does this mean you have to use mysql_store_result() before using mysql_num_rows() ? kind of doesn't make sense to have to do that. And there are no clear cut examples or explanations... I do not wish to piss-and-moan but I do find that there is a lot to be desired in the manual; things that are very ;contradictory and/ or unclear and certainly lacking in examples. And please, somebody guide me to some tutorial or something where I can learn to set up proper error checking so you guys don't have to listen to my problems. :'( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best RAID for a DB + LVM?
What RAID level to use, whether to use SCSI or SATA etc are all pretty much how long is a piece of string? questions. If you have a really high end hardware array RAID 5 may be faster than RAID1+0 is on a cheaper system. Basically RAID 5 = slower SATA = slower RAID 1+0 = faster SCSI = faster more physical disks = faster more expensive controller = faster ;) If you want to compare specific hardware you'll need to get your hands on it or find someone else who has already done a comparison. But it will make a huge difference to performance what disk array you have hooked up, just depends how much you want to spend Quoting Waynn Lue waynn...@gmail.com: I currently have a RAID 5 setup for our database server. Our space is running out, so I'm looking to increase the disk space. Since I'm doing that anyway, I decided to re-evaluate our current disk array. I was told that RAID 5 isn't a good choice for databases since it's slower to write. In addition, I've also been considering setting up LVM to take quick db snapshots, after reading various links on the web (and posts to this list). So on to the questions! First, if that's what I eventually want to do (get a new RAID server with LVM), do I need to do anything special to set up LVM on the new system? Second, what is a good RAID setup for databases? RAID 10? 0+1? Third, I have the choice of using SATA or SCSI in conjuction with the RAID drives I choose. How much of a difference is there in using SATA instead of SCSI, especially in light of whatever RAID I end up going with? Thanks for any insights, Waynn -- 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 full tutorial download or any book
To be honest, I got started straight from the MySQL manual. There is a tutorial section in the manual as well, and it's also available to download as a PDF. Links: Manual (English): http://dev.mysql.com/doc/refman/5.1/en/index.html Manual (English PDF US Letter): http://downloads.mysql.com/docs/refman-5.1-en.pdf Tutorial (English): http://dev.mysql.com/doc/refman/5.1/en/tutorial.html Substitute 5.1 with whatever version you're running. HTH, Andy muzaffar hussain wrote: hi all where can i can download full mysql tutorial . thanks regards muzaffar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
It could be that your my.cnf isn't being read (permissions or in the wrong directory), or that you have multiple my.cnf files. Quoting csego...@gmail.com csego...@gmail.com: That is intentional. I want to write the log to a non-standard location. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
What happened to the 5.1.31 release announcement?
I've just read someone's troubles about getting 5.1.31 installed on Solaris, and I thought 5.1.30 was the latest stable release. So, I went onto the website and found that 5.1.31 was released on 19th Jan, but the only announcement notices I received about that time was for 6.0.9 alpha. Did anyone else receive an announcement for the release of 5.1.31? Was it actually made? Thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Instance scale-out
Hi Claudio, I don't think its your English, I agree with you that its not just confusing it is wrong. Each server must have its own network interface At least for my 10 years experience in IT and UNIX I would understand network interface as physical network interface unless specified as otherwise. Maybe the MySQL community has a differenet opinion :P ;) cheers Andy. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Instance scale-out
Quoting John Daisley john.dais...@mypostoffice.co.uk: I would say in terms of the MySQL server the interface is either a TCP/IP Port, a Named Pipe, shared memory or a UNIX Socket. Depending on the host operating system it can use any of those interfaces but each instance must have its own interface. Just to chip in on this, an interface can obviosly mean a lot of things depending on the context and I accept the above discription in relation to MySQL. However in the text originally referenced the term used was network interface which I think most Sys/DB admins etc would understand to be a network interface in the sense the operating system considers it, ie a physical or virtual IP network intreface at the OS level. With regards the MySQL requirement the original text was discussing, I believe it is in reference to a TCP port which will normally be sitting on an OS level network interface hence I think this is badly worded in the text. thanks Andy. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Hi Philip, Am I missing something here? (It is late after a long day, I admit!) In the example case you've given, if the foreign key in Parts is set to ON DELETE CASCADE, and you delete a row from Manufacturer, MySQL will first delete the associated records in Parts before deleting the row from Manufacturer - all you have to do is issue the DELETE FROM Manufacturer WHERE query - MySQL will handle the rest. If Parts was also the primary key table for another table (e.g. PartNumbers) and that relationship was set to ON DELETE CASCADE, and you issued the DELETE FROM Manufacturer... query, MySQL would delete the associated records from all 3 tables automatically. The only time you'd have to manually issue all 3 DELETE statements were if your foreign keys didn't have the ON DELETE CASCADE option set. Andy Philip Pemberton wrote: Hi, First of all, I apologise in advance for any mind-altering, or headache-inducing effects this question may have. I've spent the past two days trying to figure it out, and all I've got to show for it is a mostly-working recursive depth-first-search routine and an empty packet of painkillers. MySQL version: 5.0.67-0ubuntu6 I'm trying to write a code generator (in Python) that reads in a MySQL database, enumerates all the tables, then produces INSERT, DELETE and UPDATE code in PHP. The INSERT and UPDATE code generation was fairly easy, and works quite well. What I'm having trouble with is the DELETE code generator -- more specifically, resolving foreign key references. Basically, what I have is a tree built in memory, so I can go: tableinfo['thetable']['fieldname']['refs'] And get a complete list of all the tables (and the fields within that table) that reference 'fieldname' in 'thetable'. What I want is an answer to the question: If all my foreign keys were set to 'ON DELETE CASCADE', what would I need to do to delete row 'X' in table 'Y' without violating any foreign key constraints? Here's an example. Let's say I've got these tables: CREATE TABLE `Manufacturers` ( `idManufacturer` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`idManufacturer`) ) ENGINE=InnoDB CREATE TABLE `Parts` ( `idPart` int(11) NOT NULL auto_increment, `idManufacturer` int(11) NOT NULL, `partnumber` int(11) NOT NULL, PRIMARY KEY (`idPart`), KEY `Parts_idManufacturer_FKIndex` (`idManufacturer`), CONSTRAINT `Parts_ibfk_1` FOREIGN KEY (`idManufacturer`) REFERENCES `Manufacturers` (`idManufacturer`) ) ENGINE=InnoDB And my database contains: Manufacturers: idManufacturername 123 Any Company Inc. Parts: idPart idManufacturer partnumber 1 123 12345 Now, let's say I want to do this: DELETE FROM Manufacturers WHERE idManufacturer=123 Because I have a part that references Manufacturer #123, I have to do this instead: DELETE FROM Parts WHERE idManufacturer=123 DELETE FROM Manufacturer WHERE idManufacturer=123 What I want is something I can feed the table definitions to, and the name of the table I want to delete a row from (in this case 'Manufacturers'), and generate a list of the DELETE commands that would allow me to delete that row while enforcing FK dependencies. I figure this is going to have to work something like mathematical expression evaluation -- build up a list of dependencies, then deal with the deepest dependency first. Catch being I can't see an obvious way to deal with generating the necessary DELETE commands without having to write a massive if recursion_level = 0 then generate_a_straight_delete else if recursion_level = 1 then... statement... Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysqld fails to start
Hi JD, I believe the error about mysql.plugin is a red herring - it needs fixing, but it's not the reason MySQL isn't starting. The second error is the one about InnoDB not having access rights to the directory. Check that /var/lib/mysql is writeable by the MySQL user (usually MySQL.) Make sure this is done, then MySQL will start, then you can run mysql_upgrade. HTH, Andy JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127 10:00:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended So, I tried to run mysql_upgrade: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed So, it seems like a chicken and egg thing??? Cannot run mysql_upgrade bcause the server is not running. Cannot run the server because I must first run mysql_upgrade. PS: I know nothing about configuring and setting up mysql. Amarok2 needs a running mysqld in order to organize music tracks collections ... etc. I need help to get mysqld started and running and to add users that are allowed to access and modify their databases. Could someone send me a set of user commands to get thing going? These are all the mysql related packages on my system: mysql-5.1.30-1.fc7.remi mysql-libs-5.1.30-1.fc7.remi sqlite-3.4.2-1.fc7 libdbi-dbd-pgsql-0.8.1a-2.fc7 sqlite-3.4.2-1.fc7 postgresql-libs-8.2.9-1.fc7 hsqldb-1.8.0.8-1jpp.5.fc7 postgresql-libs-8.2.9-1.fc7 mysql-server-5.1.30-1.fc7.remi mysqlclient15-5.0.67-1.fc7.remi mod_auth_mysql-3.0.0-3.1 mysqlclient15-5.0.67-1.fc7.remi sqlite2-2.8.17-1.fc6 php-pgsql-5.2.8-1.fc7.remi sqlite-devel-3.4.2-1.fc7 postgresql-python-8.2.9-1.fc7 postgresql-8.2.9-1.fc7 postgresql-server-8.2.9-1.fc7 mono-data-sqlite-1.2.3-5.fc7 mysql-devel-5.1.30-1.fc7.remi mysqlclient15-devel-5.0.67-1.fc7.remi mod_auth_pgsql-2.0.3-3 mysql-connector-odbc-3.51.12-2.2 libdbi-dbd-mysql-0.8.1a-2.fc7 mysqlclient15-devel-5.0.67-1.fc7.remi php-mysql-5.2.8-1.fc7.remi Cheers, JD -- 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-availability loadbalanced mysql server farm
Hi, Jake Maul wrote: *If 1/2 your tables are on server A and the other 1/2 are on server B, then you've effectively split the read *and* write load between them. How to do this without modifying the frontend is an exercise left to the reader. :) From what I've read in the past about MySQL Proxy, you can set it up so it examines the query being executed and directs it to a different server depending on your logic. So, in Jake's example, you could configure MySQL Proxy and if it contains tables that are on server A, send the query to server A, otherwise send it to server B. This could also help in replication situations - connect an application to the MySQL Proxy which talks to all master and slave servers. If the query is an UPDATE, INSERT or DELETE, direct it at the master only. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
frequent issues restoring mysqldump file
Hi, I'm not asking this as a specific question, more a general gripe looking for some kind of explanation. I don't do mysql restores very often, but many times when I have tried I get nasty errors as if its bombing out due to dodgy characters, or quote problems etc. Normally I am doing restores across the same major and minor revision (ie 5.1), but quite possibly with a point release difference. Its just quite frustrating, and also for such an old and widely used product surprising that a simple backup and restore doesnt work at least 99% of the time. So I suppose, Im asking is there anything people like me regularly do wrong that might cause this, or do other people experience similar issues and is there anything that can be done to improve matters? :S thanks for any help :) PS Im no MySQL expert but have used it a fair bit as well as Oracle and many Unixs and open source bits and pieces and Im usually quite good at making them do what I need ;) cheers Andy. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: On fighting with master-slave replication lag
Hi Xu, The check_mysql plugin is part of the standard plugins package (see the downloads page at http://www.nagios.org/download/download.php.) At a very minimum this plugin will check that the slave's SQL thread is running and compare the number of seconds it is behind the master, allowing you to set a threshold on how long is a warning, and how long is a failure (error.) I would hazard a guess that it uses the output of show slave status; to gather its information. Andy xufeng wrote: Hi All On http://www.nagiosexchange.org/cgi-bin/search.cgi?d=1query=check_mysqlGo=Go I found some useful scripts to do the work and there are some scripts that do the work as you have described. The most important lesson I have learned is to know how theses various tools achieve the goals (not only the tools themselves). Thank you all for your great help. Yours Xu Feng -Original Message- From: xufeng [mailto:xuf...@yuanjie.net] Sent: 2008年12月25日 10:13 To: 'Baron Schwartz'; 'Jake Maul'; claudio.na...@gmail.com; andy-li...@networkmail.eu; claudio.na...@gmail.com Cc: mysql@lists.mysql.com Subject: RE: On fighting with master-slave replication lag -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: 2008年12月24日 22:06 To: Jake Maul Cc: mysql@lists.mysql.com Subject: Re: On fighting with master-slave replication lag On Wed, Dec 24, 2008 at 2:31 AM, Jake Maul jakem...@gmail.com wrote: Slightly more complicated (and also probably more accurate- the time reported by show slave status is known to be unreliable in some cases) would be a script that inserts a row into a table, then check the slave over and over till it arrives. Or even better, insert 2 values... a timestamp that *you* provide (in a shell script, something like $(date) would work) and a timestamp generated by MySQL assuming the times are syncronized on the master, slave, and the box you're inserting from, when the insert hits the slave it'll generate it's own timestamp, which you can then subtract *your* timestamp from. There's also a tool in maatkit which does replication tracking, although I've not yet used it. Judging by the other tools in that package though, it's probably pretty decent :). It is mk-heartbeat, and it does pretty much what you described, although it's been tweaked to be slightly more complex to suit various real-world scenarios. I have read some stuff on http://www.maatkit.org/doc/mk-heartbeat.html and am interested in this tool. I guess in reality the mk-heartbeat tool checks the output of show master status on the master with focus on the File and Position fileds. mysql show master status \G *** 1. row *** File: mysql-bin.04 Position: 3037 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) And it can give the delay alert or calculating the delay by checking the output of show slave status on the slave with the same focus on the Master_Log_File and Exec_Master_Log_Pos fileds.It compares the two fields from slave with the two fields from master. mysql show slave status \G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.20.15.120 Master_User: replication1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.04 Read_Master_Log_Pos: 3037 Relay_Log_File: localhost-relay-bin.49 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test_db1 Replicate_Ignore_DB: mysql 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: 3037 Relay_Log_Space: 235 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.00 sec) Am I right? Or can you provide some info on the detailed description of mk-heartbeat? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=xuf...@yuanjie.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=xuf
Re: On fighting with master-slave replication lag
Xu, Slightly off-topic, but you might also want to look at your DNS set up as you're not capable of receiving e-mails: --- This message was created automatically by mail delivery software. A message that you sent could not be delivered to one or more of its recipients. This is a permanent error. The following address(es) failed: xuf...@yuanjie.net all relevant MX records point to non-existent hosts or (invalidly) to IP addresses --- Here's the problem: ;; QUESTION SECTION: ;yuanjie.net. IN MX ;; ANSWER SECTION: yuanjie.net. 3600 IN MX 1 221.130.195.83. 1 221.130.195.83. is not a valid IP address anyway, but MX records must also be the hostname of an existing A record, not an IP address. Regards, Andy Andy Shellam wrote: Hi Xu, The check_mysql plugin is part of the standard plugins package (see the downloads page at http://www.nagios.org/download/download.php.) At a very minimum this plugin will check that the slave's SQL thread is running and compare the number of seconds it is behind the master, allowing you to set a threshold on how long is a warning, and how long is a failure (error.) I would hazard a guess that it uses the output of show slave status; to gather its information. Andy xufeng wrote: Hi All On http://www.nagiosexchange.org/cgi-bin/search.cgi?d=1query=check_mysqlGo=Go I found some useful scripts to do the work and there are some scripts that do the work as you have described. The most important lesson I have learned is to know how theses various tools achieve the goals (not only the tools themselves). Thank you all for your great help. Yours Xu Feng -Original Message- From: xufeng [mailto:xuf...@yuanjie.net] Sent: 2008年12月25日 10:13 To: 'Baron Schwartz'; 'Jake Maul'; claudio.na...@gmail.com; andy-li...@networkmail.eu; claudio.na...@gmail.com Cc: mysql@lists.mysql.com Subject: RE: On fighting with master-slave replication lag -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: 2008年12月24日 22:06 To: Jake Maul Cc: mysql@lists.mysql.com Subject: Re: On fighting with master-slave replication lag On Wed, Dec 24, 2008 at 2:31 AM, Jake Maul jakem...@gmail.com wrote: Slightly more complicated (and also probably more accurate- the time reported by show slave status is known to be unreliable in some cases) would be a script that inserts a row into a table, then check the slave over and over till it arrives. Or even better, insert 2 values... a timestamp that *you* provide (in a shell script, something like $(date) would work) and a timestamp generated by MySQL assuming the times are syncronized on the master, slave, and the box you're inserting from, when the insert hits the slave it'll generate it's own timestamp, which you can then subtract *your* timestamp from. There's also a tool in maatkit which does replication tracking, although I've not yet used it. Judging by the other tools in that package though, it's probably pretty decent :). It is mk-heartbeat, and it does pretty much what you described, although it's been tweaked to be slightly more complex to suit various real-world scenarios. I have read some stuff on http://www.maatkit.org/doc/mk-heartbeat.html and am interested in this tool. I guess in reality the mk-heartbeat tool checks the output of show master status on the master with focus on the File and Position fileds. mysql show master status \G *** 1. row *** File: mysql-bin.04 Position: 3037 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) And it can give the delay alert or calculating the delay by checking the output of show slave status on the slave with the same focus on the Master_Log_File and Exec_Master_Log_Pos fileds.It compares the two fields from slave with the two fields from master. mysql show slave status \G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.20.15.120 Master_User: replication1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.04 Read_Master_Log_Pos: 3037 Relay_Log_File: localhost-relay-bin.49 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test_db1 Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error
Re: On fighting with master-slave replication lag
Hi I would suggest a Nagios monitoring system, useful for many different checks and with plugins to check also mysql replication. I'll second this. The standard check_mysql plugin included with Nagios allows you to monitor a MySQL slave and alert when the lag behind the master is larger than a given threshold (e.g. 600 seconds.) We had an issue last week where the slave's SQL thread died following a server failure - Nagios caught it and let us know immediately. www.nagios.org. Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How many users access MySQL
Hi If you don't mind keeping the general log, you can periodically parse it. I read that MySQL 5.1 (or some later version) will have an option to keep the general log in a table - that will be very handy. Be careful if you use this option (logging to a table) - while it's an extremely attractive feature, I've read that performance while it's switched on is less than desirable. The 5.1 manual also supports this: Currently, logging to tables incurs significantly more server overhead than logging to files. If you enable the general log or slow query log and require highest performance, you should log to files and not to tables. Hopefully this performance difference will be resolved in future releases of 5.1. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Average Rating, like Netflix
Or you could wrap your entire SELECT in another query, and do an IFNULL around the rating field to convert it to 0 (or some other value important to you) as follows: SELECT movie_id, ... any other fields from movies table you want ..., IFNULL(ratings, 0) AS rating FROM ( SELECT movies.*, average(ratings.rating) AS rating FROM movies LEFT JOIN ratings ON movies.movie_id=ratings.movie_id GROUP BY movies.movie_id ) result Andy Brent Baisley wrote: The ratings field would be NULL. You could also add a count in your query to tell how many ratings there were. If count is 0, you know there are no ratings. SELECT count(ratings.rating_id) AS rate_count, ... Brent Baisley On Mon, Dec 22, 2008 at 12:39 PM, Brian Dunning br...@briandunning.com wrote: If I did the left join to include movies with no ratings, how would I tell if it had no ratings? If I used mysql_fetch_array in PHP, would $result['rating'] == 0, or '', or NULL, or what? On Dec 22, 2008, at 9:29 AM, Brent Baisley wrote: The biggest problem is your join condition (and no group by). It's fine for MySQLv4, but things have changed in v5. You should start getting in the habit of moving the join filters from the WHERE clause to a specific JOIN condition. Use the WHERE clause to perform filters after the join occurs. For example: SELECT movies.* average(ratings.rating) FROM movies INNER JOIN ratings ON movies.movie_id=ratings.movie_id GROUP BY movies.movie_id Change the INNER JOIN to a LEFT JOIN if you want all movies, even those with no ratings. Brent Baisley On Mon, Dec 22, 2008 at 11:13 AM, Brian Dunning br...@briandunning.com wrote: Pretend I'm Netflix and I want to return a list of found movies, including the average of related ratings for each movie. Something like this: select movies.*, average(ratings.rating) from movies, ratings where movies.movie_id=ratings.movie_id I'm sure that's wrong in about 10 different ways but hopefully you get what I'm trying to do. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: installation nightmare
Hi PJ, To be quite honest you may have better luck downloading and installing MySQL yourself. It's very rare I install anything from the ports on FreeBSD or apt repositories on Debian, simply because you have little (if any) control over what goes on. At least if you do it yourself you know how you're setting the system up. That's just from personal experience. Download the FreeBSD binary from http://dev.mysql.com/downloads/mysql/5.1.html, and extract it somewhere on your system (e.g. /tmp.) Follow the instructions in http://dev.mysql.com/doc/refman/5.1/en/installing-binary.html which explains in full detail what to do and why you're doing what you're doing. Just in case you're wondering, your problems with 5.1.30 appear to be that the data directory it's trying to use (/var/db/mysql) is from a pre-5.1 server (and you need to run mysql_upgrade to correct it, but this requires an up-and-running system in the first place.) Your second issue is that InnoDB does not have permissions to create files in this directory. Check the owner/group MySQL is trying to run as has access to /var/db/mysql. Regards, Andy PJ wrote: Or should I say 4-day-mare... I have been trying to set up mysql for the last 4 days with weird success. I first installed mysql 6.0.8alpha not knowing I needed 5.0 compatibility for my ISP Website host. I go everything working pretty well... mysql6, php5, phpMyAdmin, all on FreeBSD 7.0 with apache2.2.9_5; all but the setup of the pba_history table for phpmyadmin - just no way. Anyway, I unistalled mysql6 and am now trying to install mySQL 5.1.30 from the FreeBSD ports collection. I have tried about everything I can think of besides the arcane documentation that is not at all accurate - for instance, there is no mysq-test.pl or something like that that is referenced in the database, but there is mysqltest in the .../bin directory. However, running that results in errors. When I do manage to set the root user password, it is not accepted... I just cannot understand what kind of a mess this is. The only thing I can provide is the error file: (biggie - is the name of the server) 081218 17:40:17 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:40:17 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:40:18 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:40:18 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended 081218 17:46:04 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:46:04 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:46:04 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:46:04 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended 081218 17:54:09 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:54:09 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:54:09 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:54:09 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended Either I am totally incompetent and cannot decipher the instructions or there is some kind of beatie running here... Hope somebody can help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with query...
Hi Afan
Why not prefix your field names with the table name?
select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
o.org_department AS organization_org_department,
a.addres1 AS addresses_address1,
a.address2 AS addresses_address2,
a.city AS addresses_city,
a.state AS addresses_state,
a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id
Then in PHP (which I guess you're using from your example) do something
like:
// Get every record from the database ($result is your MySQL result from
mysql_query)
while ($row = mysql_fetch_assoc($result))
{
$result = Array();
// Run through each field in the row
foreach ($row as $field = $value)
{
// Split the field into 2 segments split by _
$fieldSplit = explode('_', $field, 1);
// $fieldSplit will be, for example, Array(0 = 'person', 1 =
'first_name')
$result[$fieldSplit[0]][$fieldSplit[1]] = $value;
// Now you should be able to access the person's first name using
$result['person']['first_name']
}
}
This code may not be perfect as I've just typed it out from memory so it
may take a bit of tweaking.
Thanks,
Andy
Jason Pruim wrote:
On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
I hate when somebody put in Subject line something like I just did
but after 15 minutes to try to be specific just with one short
sentence - I gave up. So, you can hate me - I understand (though,
help with my problem too) :-)
I have let say 3 tables people, organization, addresses. and they are
linked to each other with column person_id.
to select person by specified person_id it's not big deal, really
simple. though, I wonder how can I select records from these three
tables but somehow marked which table it belongs?
e.g.
select p.first_name, p.last_name, p.status, p.date_registered,
o.org_id, o.org_name, o.org_department, a.addres1, a.address2,
a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id
I need somehow, together with result data, info which table data belogs?
e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}
thanks for any help.
-afan
It may not be the best option, but when I've done that type of stuff
in the past, I add another field to the database and call it like
List and put People in the people database. and then you could
just query the field List and display it how ever you needed.
--
Jason Pruim
japr...@raoset.com
616.399.2355
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with query...
Jerry Schwartz wrote: -Original Message- From: Andy Shellam [mailto:andy-li...@networkmail.eu] Sent: Wednesday, December 17, 2008 3:29 PM To: Lamp Lists Cc: mysql@lists.mysql.com Subject: Re: need help with query... Hi Afan Why not prefix your field names with the table name? select p.first_name AS person_first_name, p.last_name AS person_last_name, p.status AS person_status, p.date_registered AS person_date_registered, o.org_id AS organization_org_id, o.org_name AS organization_org_name, o.org_department AS organization_org_department, a.addres1 AS addresses_address1, a.address2 AS addresses_address2, a.city AS addresses_city, a.state AS addresses_state, a.zip AS addresses_zip from people p, organization o, addresses a where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id [JS] That would only retrieve a person_id if it is all three tables. I'm not sure that's what is wanted. That was the original query as specified by the original poster - I just added the AS xxx to each field he'd selected. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with query...
Hi,
Hi Andy,
the reason I can't use this because fields (columns) in select
statement (p.first_name, p.last_name,...) are actually dynamically
created. In my project different client will select different fields
to be shown. 99% will select first_name, and last_name, but some don't
care about date_registered, some will need more org data...
actually, it will be more this way:
SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...
where
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address,
a.city, a.state, a.zip
or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip
So just tag AS table_field_name to each field when you're building
your list of $selected_fields - e.g.
$selected_fields = p.first_name AS person_first_name, p.last_name AS
person_last_name, o.org_name AS organization_org_name
You don't have to use the full table name either - for example in the
following statement, you would then access the data using
$result['p']['first_name'];
$selected_fields = p.first_name AS p_first_name, p.last_name AS
p_last_name, o.org_name AS o_org_name
This approach is actually easier if you're creating the query
dynamically, because you don't have to manually type a load of AS xxx
statements after every field.
I've recently done something similar in one of my applications to wrap
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.
Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: captured percentage values not inserting properly into table
Hello Hagen, Hagen Finley wrote: Should I be using a different datatype for these kind of numerical values? Yes. INT is by definition a whole number (0, 1, 2 etc.) You will want to change the fmarg column to DOUBLE or DECIMAL. Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Now I just have this error message: ERROR: 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 'end // delimiter' at line 1 Query = end // delimiter I'm guessing it's the first semi-colon in your IF statement. Does this work...? if @counted = 1 then SET dummy = 'Cannot delete this record' end if; Just a guess! Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
I think you are missing the point. Where is 'OLD' or 'old' defined? Before you try to imbed it in a trigger, try the basic query. That seems to be what its complaining about. OLD is a virtual table which is only present in a trigger - it's like a table with the same layout as the table the trigger is attached to, which contains the row (or rows) that were deleted (for a DELETE query) or updated (for an UPDATE query - pre-update.) Just the same as (IIRC) NEW is a virtual table that contains the row (or rows) that were inserted (for an INSERT query) or updated (for an UPDATE query - post-update.) The query in question will fail anywhere outside the trigger because NEW and OLD are only in scope in triggers. OLD is akin to the deleted virtual table in MS SQL Server triggers, just like NEW is akin to the inserted virtual table in MSSQL. Going back to the OP's problem - the original issue I believe was he was using old instead of OLD (case-sensitive) - now that's sorted, MySQL is complaining about a syntax error toward the end of the function declaration. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maintaining sort order with 'GROUP BY' and 'HAVING'
Hi Nishi, I think what's more relevant is why did you downgrade to 5.0.22? The sorting issue you're experiencing was fixed for the 5.0.67 community release. Many bugs will have been fixed between 5.0.22 and 5.0.51a; downgrading is not a solution. Upgrade. If this is a new system you could even consider going to 5.1.30. Andy Nishikant Kapoor wrote: Its been a while since I posted this. Would like to check again with the folks on this list if they know why the results of query (b) are all blank on MySQL 5.0.22. It works fine with 5.0.51a. If it is a bug in 5.0.22, can someone suggest a workaround for this (other than upgrading MySQL). Thanks, Nishi CREATE TABLE t ( id TINYINT, title VARCHAR(10) ); INSERT INTO t (id, title) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4, 'ddd'), (5, 'eee'); (a) DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id ORDER BY id desc; | sn | id | title | +--+--+---+ |1 |5 | eee | |2 |4 | ddd | |3 |3 | ccc | |4 |2 | bbb | |5 |1 | aaa | (b) [MySQL 5.0.51a]: DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t HAVING sn=2 ORDER BY id desc; +--+--+---+ | sn | id | title | +--+--+---+ |3 |4 | ddd | +--+--+---+ 1 row in set (0.00 sec) (b) [MySQL 5.0.22]: mysql DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t HAVING sn=2 ORDER BY id desc; Query OK, 0 rows affected (0.00 sec) Empty set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create single resultset from multiple sets
Hi Bryan, My gut reaction would be to use a temporary table - create the temporary table at the start of proc1, run multiple inserts into the temp table for the results of proc2, then just before proc1 ends, select from the temp table and drop it. Andy Cantwell, Bryan wrote: I have a procedure (proc1) that calls another procedure (proc2) several times. I'd like to make proc1 return the proc2 results in a single resultset. How can this be done in mysql 5.0.51? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Result ordering
Hi Morten, I think this is valid in MySQL (it certainly is for SQL Server) but you can use a CASE statement directly in the ORDER BY clause. Try something like this: SELECT name FROM foo WHERE bar = 34 OR baz 100 ORDER BY CASE bar WHEN 34 THEN 0 ELSE 1 END ASC, baz DESC LIMIT 5; Regards, Andy Morten wrote: Hi, I'm retrieving the name of some records where either one of two criteria are satisfied: SELECT name FROM foo WHERE bar = 34 OR baz 100 ORDER BY baz DESC LIMIT 5; I would like to sort that result set, such that the records matching bar = 34 occur before records with baz 100. I could do this using a CASE: SELECT name, (CASE WHEN bar = 34 THEN 0 ELSE 1 END) AS rank FROM foo WHERE bar = 34 OR baz 100 ORDER BY rank ASC, baz DESC LIMIT 5; But I need the name DISTINCT, how can I accomplish this? I could wrap the above select in a SELECT DISTINCT name FROM (SELECT ... ) but is there a nicer way to use the rank than including it in the result set? Thanks! Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: linq with mysql
Hi, For those not familiar with LINQ, it's a new Microsoft feature in it's .NET languages that is supposed to allow developers to write querying language (i.e. SQL) code in the same format as the .NET language (e.g. C#.) The .NET compiler will convert the code into a SQL statement - e.g. I code the following at design-time (in C#) for a simple database table called config containing name and value columns: var q = from config where name == test select q; .NET will compile this into the following SQL statement at run-time: SELECT [t0].[name], [t0].[value] FROM [config] AS [t0] WHERE [t0].[name] = 'test'; The important thing to remember is that the generated SQL code cannot be viewed or edited at design time (AFAIK) so Johan's suggestion will be impossible to use, however he is right - the square brackets are the issue here. A colleague of mine has a similar issue with the ASP.NET designer - when creating SQL statements in ASP.NET's front-end tools (e.g. data-grids) it automatically encloses the table names in [...] instead of `...` Whether the MySQL .NET Connector people have already solved these issues, I don't know. Andy Johan Höök wrote: Hi, you're using Sqlserver syntax for handling reserved words. In MySQL you use backtick` for the same, i.e. select `t0`.`amount` etc. /Johan Sharique uddin Ahmed Farooqui skrev: Hi, I'm using VS Express 2008, and trying to use linq with Mysql.What I have done 1. Created a db with a single table name account(fields : acct_num int, amount int) 2. created a Linqto sql file with same model as db table. 3. A page with gridview and code behind is (on page load event): MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings[mysqltest].ConnectionString); MyLinqDataContext db = new MyLinqDataContext(con); var q = from p in db.Accounts p; GridView1.DataSource = q; GridView1.DataBind(); I'm getting this error: 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 '[t0].[amount], [t0].[acct_num] FROM [Account] AS [t0]' at line 1 I run this query in a mysql tool (HeidiSql), it gives same error. Is it due to brackets '[' ? select [t0].[amount], [t0].[acct_num] FROM [Account] AS [t0] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Logging all Access
Hi Darvin, Does this not help? http://dev.mysql.com/doc/refman/5.0/en/query-log.html (replace 5.0 with 5.1 if you've already upgraded.) The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients ... Regards, Andy Darvin Denmian wrote: I wonder if there is the possibility of registering the users' access to the server Mysql. Something similar to the command last of Unix-like systems. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maintaining sort order with 'GROUP BY' and 'HAVING'
Hi Nishi, There was a bug in that version that affected the sort order when combined with a group by statement (http://bugs.mysql.com/bug.php?id=32202.) There are a couple of workarounds but they are bad (e.g. removing primary key!) I would strongly suggest you upgrade to 5.0.67 or possibly even the newly-released 5.1.30, depending on how many applications you have that are dependent on that box. Regards, Andy Nishikant Kapoor wrote: CREATE TABLE t ( id TINYINT, title VARCHAR(10) ); INSERT INTO t (id, title) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4, 'ddd'), (5, 'eee'); (a) DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id ORDER BY id desc; | sn | id | title | +--+--+---+ |1 |5 | eee | |2 |4 | ddd | |3 |3 | ccc | |4 |2 | bbb | |5 |1 | aaa | (b) DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id HAVING sn=2 ORDER BY id desc; |2 |2 | bbb | (c) DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t HAVING sn=2 ORDER BY id desc; |3 |4 | ddd | I do not understand why (b) is not able to retain the same order of sn as in (a). I am assuming it is the 'HAVING sn=2' that is forcing (b) to reset the sort order. However, (c) is able to maintain the same sn order as in (a) even with 'HAVING sn=2' clause in it, although it is not using the 'GROUP BY id' anymore. Is there a way I can retain the sn sort order in (b) same as in (a)? | version() | +---+ | 5.0.51a | Thanks, Nishi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replacing a timestamped row
Hi Dave, You have no primary key on your table, thus MySQL has no way of knowing when the row is unique and needs to be updated rather than inserted. REPLACE INTO effectively does the following: - insert into table - did a primary key violation occur? --- yes - delete existing row from table where primary key matches the record to be inserted - insert into table --- no - row was inserted OK Judging by your table layout, I'm guessing the server_id column would be a good candidate for a primary key - providing of course that you only need one status row per server. Delete all existing records, make server_id your primary key (and not null-able) and retry your script. Regards, Andy David Halik wrote: Hi everyone, I'm fairly new to MySQL and I have a procedure that writes some status info to a table. The problem is, I just want the values and row to be replaced, rather than constantly adding a new row. I tried using REPLACE as well as different timestamp methods, but I always get a new row. How can I ensure the old row is overwritten? Here's what I'm trying to do: create table check_master_log (ts timestamp, server_id int, master_status varchar(30)); mysql describe check_master_log; +---+-+--+-+---+-+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+---+-+ | ts| timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | server_id | int(11) | YES | | NULL | | | master_status | varchar(30) | YES | | NULL | | +---+-+--+-+---+-+ Then I have a script that runs every 15 seconds and does one of the following: insert into check_master_log values (now(), @@server_id, master OK); insert into check_master_log values (now(), @@server_id, master is dead); Everything works great, but I end up with a growing table that adds a new row every 15 seconds: | 2008-11-25 11:42:12 | 1 | master OK | | 2008-11-25 11:42:27 | 1 | master OK | | 2008-11-25 11:42:42 | 1 | master OK | | 2008-11-25 11:42:57 | 1 | master OK | +-+---+---+ What I'd like to do is just have it replace the existing one so I just have one status row that I can select and check. I'm sure it's just a matter of the proper replace and table syntax, but I haven't been able to get it to work without constantly appending rows. Any suggestions? Thanks in advance, -Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: Is it a bug or my mistake in server configuration?]
This mailing list has a stupid configuration. Pressing the answer button, the message goes to the sender not to the list :-( Hint - use Reply to All - it's not specific to this mailing list. :-) Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql dump problems, no data dumped
This is a known bug, Ive installed MySQL 4 client binaries as a work around... _ From: Andy Smith [mailto:[EMAIL PROTECTED] Sent: 2008-10-30 18:26 To: 'mysql@lists.mysql.com' Subject: mysql dump problems, no data dumped Im having an issue using mysqldump to dump a DB from comercial app which includes mysql 4.0.18-pro. It doesnt however include mysql dump for online backups so Im using the one installed by default in my linux dist which as you can see below is version 10.11. My problem is that the dump is exiting with exist status 0 but Im not getting any of the data dumped. The WHOLE dump is shown in text below:
RE: mysql dump problems, no data dumped
Hi Rick, ok sorry, heres a bit more detail, and I see some more useful stuff I didn't see before too (I still duno whats wrong but guess will be helpful to those more knowledgeable!). I believe its using InnoDB for the tables, previously I was seeing an error 24 which from digging around can be related to too many open files, so I then tried with single transaction mode and get the successful backup exit status, but in verbose mode I see that there are obvious issues. mysqldump -v --single-transaction --socket=/usr/AdventNet/ME/OpManager/mysql/mysql.socket OpManagerDB -- Connecting to localhost... -- MySQL dump 10.11 -- -- Host: localhostDatabase: OpManagerDB -- -- -- Server version 4.0.18-pro /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- Retrieving table structure for table ADGRAPHS... -- Skipping dump data for table 'ADGRAPHS', it has no fields -- Retrieving table structure for table ADMonitorRuleList... -- Skipping dump data for table 'ADMonitorRuleList', it has no fields -- Retrieving table structure for table ADServiceRuleList... -- Skipping dump data for table 'ADServiceRuleList', it has no fields -- Retrieving table structure for table ADServicesDefinition... -- Skipping dump data for table 'ADServicesDefinition', it has no fields -- Retrieving table structure for table ADWMIPolledData... -- Skipping dump data for table 'ADWMIPolledData', it has no fields . . -- Disconnecting from localhost... /*!40103 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- Dump completed on 2008-10-30 19:06:25 Without single transaction I get this: mysqldump --socket=/usr/AdventNet/ME/OpManager/mysql/mysql.socket OpManagerDB -- MySQL dump 10.11 -- -- Host: localhostDatabase: OpManagerDB -- -- -- Server version 4.0.18-pro /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; mysqldump: Got error: 1017: Can't find file: './OpManagerDB/WANIntfTypes.frm' (errno: 24) when using LOCK TABLES This file mentioned does exist in the location where all the datafiles for the OpManagerDB DB are stored: file WANIntfTypes.frm WANIntfTypes.frm: MySQL table definition file Version 7 Thanks, Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql dump problems, no data dumped
Hi, Im having an issue using mysqldump to dump a DB from comercial app which includes mysql 4.0.18-pro. It doesnt however include mysql dump for online backups so Im using the one installed by default in my linux dist which as you can see below is version 10.11. My problem is that the dump is exiting with exist status 0 but Im not getting any of the data dumped. The WHOLE dump is shown in text below: -- MySQL dump 10.11 -- -- Host: localhostDatabase: OpManagerDB -- -- -- Server version 4.0.18-pro /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40103 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- Dump completed on 2008-10-27 13:50:53 Can anyone help me? I need to work out why no data is written, thanks Andy.
Re: Rotate regular log file only
Hi Olaf, We use our mysqldump script to rotate the binlogs; it's much safer as it allows MySQL to do the log rotate natively (if you use logrotate, MySQL will complain that either the log doesn't exist when it expects it to, or your slaves will bail out because they didn't know the log was changed. It happened to us recently when we moved the log directory and didn't update the log index.) At 2am our backup system runs the mysqldump script with the extra parameter --flush-logs. This causes MySQL to rotate the log it's using, and as you found out, all slaves respond to the change without an issue. Andy Olaf Stein wrote: Thanks all... Rotating actually does not affect the slaves, they adjust to the new binlog just fine, I guess I should have tried that first. I will nevertheless take a closer look at logrotate... Olaf On 10/23/08 12:13 PM, Uwe Kiewel [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Olaf Stein schrieb: Hi all Is it possible to rotate just the regular (--log) log file? I am not sure if it will be safe, but maybe with logrotate and for /var/log/mysqld.log the copytruncate option for logrotate. If I do flush-logs I have to tell my slaves that (at least I have done so in the past, maybe I don't and the slves realizes by itself?) I think so, b/c I've never told my slaves... HTH, Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJAKK3AAoJEEJXG7BUuynntkkP/R5IiZWpafUfQqR+hVUax9at NV8YKfUIz8J1QLrT7cWOEqpuliABP0P6AOS06Tmm4t2ve15BJ1fwxRqHiHEem9BE 7nb1AuQDlGW+qTOVpzJqj2H8b5SARdLoKswTisT0Yz++NDj3WQxVM/UIKotwRnLH edDHSrfjPl+38TmlmGP7/3ZYA2gEAKosgYGrax6bHtSnrw2pfDq6BaXvEwXABAHc aCE6P3DKGr4Ycs2Xlc49IkPHgE6/+SNM9MqVAs83OgxNZK5+c474YdJl7i5hfth1 8RKMPweQgBtYRT3vfrvJdfzg2Wg75pJv1RwkKiGofaAjBmO9y93iNkE57pNXq3sd eWFZR5YcPA+3+GCnAvOMcjzytISlpxNNic235qaYSuoNDMV1rukxSYNpH62kzQPH V3gTKuZcjWYWasa0Y6ylSBWywSOnfc49n0mVdXeoHb7CpIQn3jwCtRG2+UCZUM1W O4U5+bKgXERqqwjNS5sk9SNmq5gQAKYU4IsDZwZcyFY7t/XEHwB3+bCVnm1y4V/s Fzin0FoAIbqm9VzALzTs5YUkWzoSzniGepIBrZR0PO98sDxOlDFUESpYnFj8oNap wjM/5P0tgbw99lIsLAMy7+FdPIlSssWxq+LFC4dR6o+pzVrYjFjoRg3MdYn9ein8 svOEP/N79cK5pPZJpDyY =cN1H -END PGP SIGNATURE- - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rotate regular log file only
Nope, we use the expire_logs_days parameter in MySQL's my.cnf (set to expire_logs_days=7 - removes log files that are 7 days old.) mysqldump's flush-logs does indeed begin a new log - that's the idea of it: # ls -l /srv/mysql rw-rw 1 mysql localservice 213 2008-10-16 00:56 sql-m2-bin.17 -rw-rw 1 mysql localservice 213 2008-10-17 00:56 sql-m2-bin.18 -rw-rw 1 mysql localservice 213 2008-10-18 00:56 sql-m2-bin.19 -rw-rw 1 mysql localservice 213 2008-10-19 00:56 sql-m2-bin.20 -rw-rw 1 mysql localservice 213 2008-10-20 00:56 sql-m2-bin.21 -rw-rw 1 mysql localservice 213 2008-10-21 00:56 sql-m2-bin.22 -rw-rw 1 mysql localservice 213 2008-10-22 00:56 sql-m2-bin.23 -rw-rw 1 mysql localservice 213 2008-10-23 00:56 sql-m2-bin.24 -rw-rw 1 mysql localservice 98 2008-10-23 00:56 sql-m2-bin.25 # /usr/local/mysql/bin/mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --user=xxx --password=xxx /tmp/backup_2008-10-23.sql # ls -l /srv/mysql -rw-rw 1 mysql localservice 213 2008-10-17 00:56 sql-m2-bin.18 -rw-rw 1 mysql localservice 213 2008-10-18 00:56 sql-m2-bin.19 -rw-rw 1 mysql localservice 213 2008-10-19 00:56 sql-m2-bin.20 -rw-rw 1 mysql localservice 213 2008-10-20 00:56 sql-m2-bin.21 -rw-rw 1 mysql localservice 213 2008-10-21 00:56 sql-m2-bin.22 -rw-rw 1 mysql localservice 213 2008-10-22 00:56 sql-m2-bin.23 -rw-rw 1 mysql localservice 213 2008-10-23 00:56 sql-m2-bin.24 -rw-rw 1 mysql localservice 213 2008-10-23 19:37 sql-m2-bin.25 -rw-rw 1 mysql localservice 98 2008-10-23 19:37 sql-m2-bin.26 Notice the last write time of sql-m2-bin.25 and the new file sql-m2-bin.26. Note this was on my slave server which was why the logs never get written to past 00:56 each morning! Also my backup script substitutes the date as appropriate into the dump file. Regards, Andy Olaf Stein wrote: And I assume you backup script also archives or removes the old log file, because flush-logs does not start a new log file if there is still one present On 10/23/08 2:20 PM, Andy Shellam [EMAIL PROTECTED] wrote: Hi Olaf, We use our mysqldump script to rotate the binlogs; it's much safer as it allows MySQL to do the log rotate natively (if you use logrotate, MySQL will complain that either the log doesn't exist when it expects it to, or your slaves will bail out because they didn't know the log was changed. It happened to us recently when we moved the log directory and didn't update the log index.) At 2am our backup system runs the mysqldump script with the extra parameter --flush-logs. This causes MySQL to rotate the log it's using, and as you found out, all slaves respond to the change without an issue. Andy Olaf Stein wrote: Thanks all... Rotating actually does not affect the slaves, they adjust to the new binlog just fine, I guess I should have tried that first. I will nevertheless take a closer look at logrotate... Olaf On 10/23/08 12:13 PM, Uwe Kiewel [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Olaf Stein schrieb: Hi all Is it possible to rotate just the regular (--log) log file? I am not sure if it will be safe, but maybe with logrotate and for /var/log/mysqld.log the copytruncate option for logrotate. If I do flush-logs I have to tell my slaves that (at least I have done so in the past, maybe I don't and the slves realizes by itself?) I think so, b/c I've never told my slaves... HTH, Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJAKK3AAoJEEJXG7BUuynntkkP/R5IiZWpafUfQqR+hVUax9at NV8YKfUIz8J1QLrT7cWOEqpuliABP0P6AOS06Tmm4t2ve15BJ1fwxRqHiHEem9BE 7nb1AuQDlGW+qTOVpzJqj2H8b5SARdLoKswTisT0Yz++NDj3WQxVM/UIKotwRnLH edDHSrfjPl+38TmlmGP7/3ZYA2gEAKosgYGrax6bHtSnrw2pfDq6BaXvEwXABAHc aCE6P3DKGr4Ycs2Xlc49IkPHgE6/+SNM9MqVAs83OgxNZK5+c474YdJl7i5hfth1 8RKMPweQgBtYRT3vfrvJdfzg2Wg75pJv1RwkKiGofaAjBmO9y93iNkE57pNXq3sd eWFZR5YcPA+3+GCnAvOMcjzytISlpxNNic235qaYSuoNDMV1rukxSYNpH62kzQPH V3gTKuZcjWYWasa0Y6ylSBWywSOnfc49n0mVdXeoHb7CpIQn3jwCtRG2+UCZUM1W O4U5+bKgXERqqwjNS5sk9SNmq5gQAKYU4IsDZwZcyFY7t/XEHwB3+bCVnm1y4V/s Fzin0FoAIbqm9VzALzTs5YUkWzoSzniGepIBrZR0PO98sDxOlDFUESpYnFj8oNap wjM/5P0tgbw99lIsLAMy7+FdPIlSssWxq+LFC4dR6o+pzVrYjFjoRg3MdYn9ein8 svOEP/N79cK5pPZJpDyY =cN1H -END PGP SIGNATURE- - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only
Re: Down list
You might like to try here: http://lists.mysql.com/, then click on Unsubscribe underneath the General Discussion list. Vidal Garza wrote: Please let me down of the list. I resigns from my job. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any easier way to compare mysql schema of 50 databases?!
Hi, I was also going to go down this route some time back, but then when I looked at it, it's pretty simple how it works and you can do the same thing yourself for free. Take a dump of both servers (mysqldump or via MySQL Administrator) of the databases in question, then use WinMerge (for free) to compare the dump files and (optionally) create a 3rd file which is a result of merging the 2 by choosing the changes you want to keep. I've used this method plenty of times to synchronise changes between servers, some that concern 000s of rows of data. Granted you cannot do this with PostgreSQL as those dumps tend to be in binary format, but it works well for MySQL backups. WinMerge: http://www.winmerge.org/ Andy D. Dante Lorenso wrote: Uma Bhat wrote: We are in progress of *optimizing* and designing the existing mysql database enviromnent on *linux*. And need help in comaparing schema of 50 databases from the same mysql instance. If you can afford to spend a few dollars to get the right tool, you want to get DB Comparer for MySQL from the folks at EMS: http://www.sqlmanager.net/en/products/mysql/dbcomparer This tool will compare the schemas of 2 MySQL Databases and allow you to selectively choose which changes to make in order to synch to the master or the target DB. I've been using the PostgreSQL version of this tool for many years and just recently started using their MySQL one. -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access Rights ?
Hi Sachin, MySQL is capable of logging all queries to a log file, see http://dev.mysql.com/doc/refman/5.0/en/query-log.html. This includes connections, disconnections and executed SQL statements - no coding needed! If you want this information imported into a database table, a couple of hours and a few cups of coffee will allow you to write a simple PHP script (or C program for the hard-core developers) to read this file and write the details of each record to a database table. Regards, Andy Sachin Gaikwad wrote: Hi all, I am newbie using MySQL. I want to log each client request to database server. I thought when server checks access rights of user(client) will be good place to log all access/read/update requests. If somebody knows where exactly this is done in source code, I will get a real kick start. Right now I am browsing through the source code through cscope to find out this. Any help in this regard is appreciated. Thanks, Sachin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access Rights ?
Hi Sachin, What version of MySQL are you running? I believe --log-output was only added in 5.1 which is why a 5.0 server would fail to start with this option set. I think the relevant option should also be log-output in my.cnf / my.ini (e.g. log-output = FILE, TABLE.) It looks like 5.1 greatly improves on the functionality you're looking for, including the native ability to write the logs to a database table, however it's currently more efficient to write to a file than it is to a table. See http://dev.mysql.com/doc/refman/5.1/en/log-tables.html for more information. Andy Sachin Gaikwad wrote: Alrite guys. I added log=/tmp/logfile in /etc/my.cnf and removed log = FILE, TABLE. Now it is logging each query. Thanks for the support. Sachin On Mon, Oct 6, 2008 at 3:13 PM, Sachin Gaikwad [EMAIL PROTECTED] wrote: More on this: I set following in /etc/my.cnf: log = FILE, TABLE after this I am not able to start my mysqld, it fails with timeout error message Timeout error occurred trying to start MySQL Daemon. Thanks, Sachin On Mon, Oct 6, 2008 at 3:01 PM, Sachin Gaikwad [EMAIL PROTECTED] wrote: Thanks a bunch for the quick reply. I will surely write a C program to read this logfile of general queries. But before that I am not able to find this logfile for general queries. I started my server like this: $/etc/init.d/mysqld start --log=logfile --log-output=/tmp Meaning log file will be in = /tmp/logfile $mysql mysql select * from whowhatwhen; xx xxx x mysql So this query should exist in logfile. But I am not able to see this logfile in /tmp itself. What am I missing ? Thanks, Sachin On Mon, Oct 6, 2008 at 2:29 PM, Weston, Craig (OFT) [EMAIL PROTECTED] wrote: This problem is Chapter 16.6 of The MYSQL Cookbook, (second edition) that has made my(sql) life much easier. You might want to look around at http://artfulsoftware.com/ which has lots of examples and a great e-book resource. Both of these are fantastic resources for me, and they are probably my most used books in my mysql library. Good luck, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Sachin Gaikwad [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 2:07 PM To: mysql@lists.mysql.com Subject: Access Rights ? Hi all, I am newbie using MySQL. I want to log each client request to database server. I thought when server checks access rights of user(client) will be good place to log all access/read/update requests. If somebody knows where exactly this is done in source code, I will get a real kick start. Right now I am browsing through the source code through cscope to find out this. Any help in this regard is appreciated. Thanks, Sachin -- 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]
