Re: Changing port no of the server using command line method
Micah Stevens wrote: If you want to control the server process, you'll need to start the server process with those options, this mysqld, and the command line options are here: http://dev.mysql.com/doc/refman/5.1/en/server-options.html Thanks for the link, I am going through it. For your purposes though, I would absolutely parse and use the config file instead though because operating directly on the server daemon will likely come into conflict with a lot of distribution specific shell scripts (/etc/init.d/mysql for example) - and any configuration that is done will go away on next restart of the server. The MySQL config file is very standardized and would not be hard to deal with. I also first thought of using the config file, but I have time constraints, that's the reason I asked about any command line option. Please don't take this the wrong way, I hate to discourage anyone, but if you're worried about dealing successfully with the config file, I'd practice up on my text processing before jumping into server admin items. I only say this because in the long run I really believe it will be much easier for you, and a better strategy based on what little I understand of your goals. Don't know what language you're using, but there's lots of libs available for perl and python to do this easily. Not at all, I don't mind criticism if its constructive. I am using Python for making the app. I have experience with text processing but since I can't call myself a champ in this field, so thought of the other way. Thanks for the help though. -- Manish Sinha Personal Blog: http://www.manishsinha.info Tech Blog: http://manishtech.wordpress.com OpenPGP Key: 99E6658F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
slow query log
Hi All, I have enabled slow query log. Generally this file will have sql's which take more than long-query time to execute and also sql's not using indexes. But i see sql's which does not come under the above condition. I have set the long-query time to 1 Sec . The query takes less than 1 sec to execute and also uses indexes, but still its recored in slow query log. Any idea why this happens. Thanks for your help. regards anandkl
Binlog error
Hi all, As my query yesterday did not generate any responses (possibly it was too long and maybe not well written) I am trying to simplify the query: What does the following error mean: ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 173056, event_type: 73 And what is the unit for data_len: 173056 This error is in my binlog after a trigger (before delete) is executed Thanks Olaf - 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/mysql?unsub=arch...@jab.org
Re: slow query log
I'm just guessing, but if the slow query log time resolution is seconds, perhaps 0.5 and higher rounds up? Or, perhaps it has an index, but it can't be used in that query. What does EXPLAIN [paste query here] tell you? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Compare DATETIME to DATE
Hi, I don't quite understand (or even know) what the proper way to compare a DATETIME column to a given DATE value is. I've used various methods but I'd like to know if there's a better way to compare these values. Right now I have a query with this in the WHERE clause (customer.created_dt is a DATETIME): AND CAST(customer.created_dt AS DATE) BETWEEN '2008-12-30' AND '2008-12-30' This was working (MySQL on Win32) before I moved the database to MySQL on RHEL 64-bit (5.0.45-log). Should that work? I've also done this: AND customer.created_dt BETWEEN '2008-12-30 00:00:00' AND '2008-12-30 23:59:59' That works on both servers, but I really don't want to have to put the time in there (unless that's the way you are supposed to do this). I've though about using DATE_FORMAT... not sure about that either. - Johnny Withers 601.209.4985 joh...@pixelated.net
RE: Compare DATETIME to DATE
-Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, December 31, 2008 1:13 PM To: MySQL General List Subject: Compare DATETIME to DATE Hi, I don't quite understand (or even know) what the proper way to compare a DATETIME column to a given DATE value is. I've used various methods but I'd like to know if there's a better way to compare these values. Right now I have a query with this in the WHERE clause (customer.created_dt is a DATETIME): AND CAST(customer.created_dt AS DATE) BETWEEN '2008-12-30' AND '2008-12- 30' This was working (MySQL on Win32) before I moved the database to MySQL on RHEL 64-bit (5.0.45-log). Should that work? I've also done this: AND customer.created_dt BETWEEN '2008-12-30 00:00:00' AND '2008-12-30 23:59:59' That works on both servers, but I really don't want to have to put the time in there (unless that's the way you are supposed to do this). I've though about using DATE_FORMAT... not sure about that either. [JS] I've done it with the BETWEEN, when I'm looking for an interval, and with LEFT(customer.created_dt, 10) = ? if I'm looking for a specific date. - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Compare DATETIME to DATE
Truncate the time part of the datetime field when doing the compare AND DATE_FORMAT(customer.created_dt, '%Y-%m-%d 00:00:00') BETWEEN '2008-12-30' AND '2008-12-30' Should work. Probably not the most efficient. The other options would be to use take end date + 1 day, minue 1 second. That's even a bigger hack but it would probably be more efficient than converting all of the dates on the fly if you have a large number of records to process. From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wed 12/31/2008 10:13 AM To: MySQL General List Subject: Compare DATETIME to DATE Hi, I don't quite understand (or even know) what the proper way to compare a DATETIME column to a given DATE value is. I've used various methods but I'd like to know if there's a better way to compare these values. Right now I have a query with this in the WHERE clause (customer.created_dt is a DATETIME): AND CAST(customer.created_dt AS DATE) BETWEEN '2008-12-30' AND '2008-12-30' This was working (MySQL on Win32) before I moved the database to MySQL on RHEL 64-bit (5.0.45-log). Should that work? I've also done this: AND customer.created_dt BETWEEN '2008-12-30 00:00:00' AND '2008-12-30 23:59:59' That works on both servers, but I really don't want to have to put the time in there (unless that's the way you are supposed to do this). I've though about using DATE_FORMAT... not sure about that either. - Johnny Withers 601.209.4985 joh...@pixelated.net
Happy New Year
Just thought I would take this opportunity to wish everyone on the list a happy, prosperous and peaceful 2009. John Daisley Email: john.dais...@butterflysystems.co.uk Mobile: 07812 451238 MySQL Certified Database Administrator (CMDBA) MySQL Certified Developer (CMDEV) MySQL Certified Associate (CMA) Comptia A+ Certified Professional IT Technician On Wed, 2008-12-31 at 10:23 -0800, Gary W. Smith wrote: Truncate the time part of the datetime field when doing the compare AND DATE_FORMAT(customer.created_dt, '%Y-%m-%d 00:00:00') BETWEEN '2008-12-30' AND '2008-12-30' Should work. Probably not the most efficient. The other options would be to use take end date + 1 day, minue 1 second. That's even a bigger hack but it would probably be more efficient than converting all of the dates on the fly if you have a large number of records to process. From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wed 12/31/2008 10:13 AM To: MySQL General List Subject: Compare DATETIME to DATE Hi, I don't quite understand (or even know) what the proper way to compare a DATETIME column to a given DATE value is. I've used various methods but I'd like to know if there's a better way to compare these values. Right now I have a query with this in the WHERE clause (customer.created_dt is a DATETIME): AND CAST(customer.created_dt AS DATE) BETWEEN '2008-12-30' AND '2008-12-30' This was working (MySQL on Win32) before I moved the database to MySQL on RHEL 64-bit (5.0.45-log). Should that work? I've also done this: AND customer.created_dt BETWEEN '2008-12-30 00:00:00' AND '2008-12-30 23:59:59' That works on both servers, but I really don't want to have to put the time in there (unless that's the way you are supposed to do this). I've though about using DATE_FORMAT... not sure about that either. - Johnny Withers 601.209.4985 joh...@pixelated.net __ This email has been scanned by Netintelligence http://www.netintelligence.com/email
Can a JOIN statement do this?
Hello mysql and Happy New Year, I am working with a Forum database. It contains a forums table, a posts table and a threads table. Some of the posts contain flash objects that I can find using a query like this one: SELECT `pagetext`, `postid` FROM `post` WHERE `pagetext` LIKE '%someuniqueidentifier%'ORDER BY `postid` DESC This query works fine for what I needed. Now, the requirement has changed to finding that latest object posted in a specific forum but the forum table has no direct reference to the postid. The thread table has a reference to the forumID but not a postID. It looked something like this: posts table: +-++ | post_id | thread_id | ++-+ forum table: +--++ | forum_id || +--++ thread table: ++--+ | thread_id | forum_id | ++--+ I know the forum ID that contains the objects but need to query the threads to see which ones contain posts with objects. Then grab the last one for processing. So I think I have at least two queries now instead of the one I used to grab that latest objects from the database. If I did manual queries, I would select the latest thread_id posted to forum_id and then a second query to find all the posts in that thread that contained objects and grab the last one posted. I think this all might be combined with a join but I am not clear about how to do that because the conditional seems to need a result that I don't have until I run the first query, i.e. WHERE `forum_id`='163' AND `thread.thread_id`= (results of query to find last thread) AND `pagetext` LIKE %someobjectidentifier% DESC LIMIT 1 So, I am still wondering if using JOIN is the right path to purse to optimize this query. Any suggestion greatly appreciated. -- Best regards, mikesz mailto:mik...@qualityadvantages.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: Happy New Year
HAPPY NEW YEAR - Original Message - From: John Daisley john.dais...@mypostoffice.co.uk To: MySQL General List mysql@lists.mysql.com Sent: Thursday, January 01, 2009 7:25 AM Subject: Happy New Year Just thought I would take this opportunity to wish everyone on the list a happy, prosperous and peaceful 2009. John Daisley Email: john.dais...@butterflysystems.co.uk Mobile: 07812 451238 MySQL Certified Database Administrator (CMDBA) MySQL Certified Developer (CMDEV) MySQL Certified Associate (CMA) Comptia A+ Certified Professional IT Technician On Wed, 2008-12-31 at 10:23 -0800, Gary W. Smith wrote: Truncate the time part of the datetime field when doing the compare AND DATE_FORMAT(customer.created_dt, '%Y-%m-%d 00:00:00') BETWEEN '2008-12-30' AND '2008-12-30' Should work. Probably not the most efficient. The other options would be to use take end date + 1 day, minue 1 second. That's even a bigger hack but it would probably be more efficient than converting all of the dates on the fly if you have a large number of records to process. From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wed 12/31/2008 10:13 AM To: MySQL General List Subject: Compare DATETIME to DATE Hi, I don't quite understand (or even know) what the proper way to compare a DATETIME column to a given DATE value is. I've used various methods but I'd like to know if there's a better way to compare these values. Right now I have a query with this in the WHERE clause (customer.created_dt is a DATETIME): AND CAST(customer.created_dt AS DATE) BETWEEN '2008-12-30' AND '2008-12-30' This was working (MySQL on Win32) before I moved the database to MySQL on RHEL 64-bit (5.0.45-log). Should that work? I've also done this: AND customer.created_dt BETWEEN '2008-12-30 00:00:00' AND '2008-12-30 23:59:59' That works on both servers, but I really don't want to have to put the time in there (unless that's the way you are supposed to do this). I've though about using DATE_FORMAT... not sure about that either. - Johnny Withers 601.209.4985 joh...@pixelated.net __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Happy New Year
Happy Gnu Year to everyone!! On Wed, Dec 31, 2008 at 11:30 PM, Prathima Rao prathiman...@vsnl.netwrote: HAPPY NEW YEAR - Original Message - From: John Daisley john.dais...@mypostoffice.co.uk To: MySQL General List mysql@lists.mysql.com Sent: Thursday, January 01, 2009 7:25 AM Subject: Happy New Year Just thought I would take this opportunity to wish everyone on the list a happy, prosperous and peaceful 2009. John Daisley Email: john.dais...@butterflysystems.co.uk Mobile: 07812 451238 MySQL Certified Database Administrator (CMDBA) MySQL Certified Developer (CMDEV) MySQL Certified Associate (CMA) Comptia A+ Certified Professional IT Technician On Wed, 2008-12-31 at 10:23 -0800, Gary W. Smith wrote: Truncate the time part of the datetime field when doing the compare AND DATE_FORMAT(customer.created_dt, '%Y-%m-%d 00:00:00') BETWEEN '2008-12-30' AND '2008-12-30' Should work. Probably not the most efficient. The other options would be to use take end date + 1 day, minue 1 second. That's even a bigger hack but it would probably be more efficient than converting all of the dates on the fly if you have a large number of records to process. From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wed 12/31/2008 10:13 AM To: MySQL General List Subject: Compare DATETIME to DATE Hi, I don't quite understand (or even know) what the proper way to compare a DATETIME column to a given DATE value is. I've used various methods but I'd like to know if there's a better way to compare these values. Right now I have a query with this in the WHERE clause (customer.created_dt is a DATETIME): AND CAST(customer.created_dt AS DATE) BETWEEN '2008-12-30' AND '2008-12-30' This was working (MySQL on Win32) before I moved the database to MySQL on RHEL 64-bit (5.0.45-log). Should that work? I've also done this: AND customer.created_dt BETWEEN '2008-12-30 00:00:00' AND '2008-12-30 23:59:59' That works on both servers, but I really don't want to have to put the time in there (unless that's the way you are supposed to do this). I've though about using DATE_FORMAT... not sure about that either. - Johnny Withers 601.209.4985 joh...@pixelated.net __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fish.kun...@gmail.com