Re: Error Code 28
Hi Heston, all! Heston James - Cold Beans wrote: [[...]] One thing which is puzzling me at the moment is why its producing such large record sets from my query. Even when limiting the query to 700 records it still exceeds this 8Mb limit. The query is quite basic and is only returning very simple strings and integers, no more than maybe 8 chars long, like so: SELECT bluetooth_session.bluetooth_session_id AS bluetooth_session_bluetooth_session_id, bluetooth_session.result AS bluetooth_session_result, bluetooth_session.address AS bluetooth_session_address, bluetooth_session.message_id AS bluetooth_session_message_id, bluetooth_session.campaign_id AS bluetooth_session_campaign_id, bluetooth_session.created AS bluetooth_session_created, bluetooth_session.modified AS bluetooth_session_modified FROM bluetooth_session WHERE bluetooth_session.created %s ORDER BY bluetooth_session.created LIMIT 1, 699 The limit clause restricts the data returned, but not the data examined. You ask the server to examine a certain set of rows (where ...), sort it (order by ...), and then to return a limited number of these rows (limit ...). As the data only exist in some other order and you (probably) do not have a way to access them in the order desired (or do you have an index on bluetooth_session.created?), the server must first sort all qualifying rows. This is where it needs so much space. Somebody else has already commented on the limit clause you use, I need not repeat that. bluetooth_session.result and bluetooth_session.address are both 8 character varchars and the rest are integers and dates. Would you really expect a record set from this query to be so large? Is there any way to make it smaller and more efficient? I strongly suspect an index on bluetooth_session.created would help, both in evaluating the where condition and in avoiding the sort for order by, and so both reduce the time and avoid the temp space. Disclaimer: Not tested, not checked - just assuming. Regards and HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error Code 28
Morning Jorg, Thanks for the reply. The limit clause restricts the data returned, but not the data examined. You ask the server to examine a certain set of rows (where ...), sort it (order by ...), and then to return a limited number of these rows (limit ...). As the data only exist in some other order and you (probably) do not have a way to access them in the order desired (or do you have an index on bluetooth_session.created?), the server must first sort all qualifying rows. This is where it needs so much space. This makes perfect sense, I see what you mean about the fact that the data has to effectively be collated in its entirety before it is ordered and limited, that's a very fair statement. Somebody else has already commented on the limit clause you use, I need not repeat that. They did indeed, I hadn't noticed this myself, the SQL is produced by an ORM so I'll examine how I'm using that, thanks to all who pointed that one out. I strongly suspect an index on bluetooth_session.created would help, both in evaluating the where condition and in avoiding the sort for order by, and so both reduce the time and avoid the temp space. I can see your point on this, I've not worked with index's at all at this point in time so I'll give them a shot and see what difference that makes, the created date definitely makes a very strong candidate as this is generally how the data is queried, so that'll be my first index, I also regularly query on the address column so I'll be sure to try indexing that too. Disclaimer: Not tested, not checked - just assuming. I really appreciate the suggestions, I certainly see the logic in them, let's hope they bare fruit :-) Cheers, Heston -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rotate regular log file only
Hi all Is it possible to rotate just the regular (--log) log file? 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?) 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/[EMAIL PROTECTED]
Re: Rotate regular log file only
-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- -- 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
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: MySQL Sort by Array
Pretty standard mysql function. Its been in mysql for a while. http://dev.mysql.com/doc/refman/4.1/en/string-functions.html#function_field Jim Lyons wrote: I'm not familiar with order by field (unless field is a UDF). I know of order by binary. Is this standard mysql syntax? On Wed, Oct 22, 2008 at 10:42 AM, Peter Brawley [EMAIL PROTECTED] wrote: ORDER BY id(5, 34, 9, 25) Can anyone tell me the proper syntax to accomplish this task? ORDER BY FIELD( id, 5, 34, 9, 25 ) PB - Keith Spiller wrote: Hi Guys, I'm trying to sort by a particular order: SELECT * FROM tablename WHERE id='5' OR id='9' OR id='25' OR id='34' ORDER BY id(5, 34, 9, 25) Can anyone tell me the proper syntax to accomplish this task? Thanks for your help. Keith No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.8.2/1739 - Release Date: 10/22/2008 7:23 AM -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850 ext 141/ Tel (888) 829-3631/ Fax -- 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
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
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 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 for
MySQL 6.0.7 Alpha has been released!
Dear MySQL users, MySQL 6.0.7-alpha, a new version of the MySQL database system has been released. The main page for MySQL 6.0 release is at http://www.mysql.com/mysql60/ MySQL 6.0 includes two new storage engines: the transactional Falcon engine, and the crash-safe Maria engine. If you are new to the Falcon storage engine and need more information, please read the Falcon Evaluation Guide at http://www.mysql.com/why-mysql/white-papers/falcon-getting-started.php and the Falcon White Paper at http://www.mysql.com/why-mysql/white-papers/storage-engines-falcon.php The Maria storage engine is a crash safe version of MyISAM. Maria supports all of the main functionality of the MyISAM engine, but includes recovery support (in the event of a system crash), full logging (including CREATE, DROP, RENAME and TRUNCATE operations), all MyISAM row formats and a new Maria specific row format. Maria is documented at http://dev.mysql.com/doc/refman/6.0/en/se-maria.html MySQL 6.0.7-alpha is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/6.0.html and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, and patches at http://forge.mysql.com/wiki/Contributing The following section lists important, incompatible and security changes since the previous version of MySQL 6.0. The full changelog, including many more fixes can be viewed online at http://dev.mysql.com/doc/refman/6.0/en/news-6-0-7.html Important functionality added or changed: * Important Change: mysqlbinlog now supports --verbose and --base64-output=DECODE-ROWS options to display row events as commented SQL statements. (The default otherwise is to display row events encoded as base-64 strings using BINLOG statements.) See Section 4.6.7.2, mysqlbinlog Row Event Display. (Bug#31455: http://bugs.mysql.com/31455) Important, security, or incompatible bugs fixed: * Security Enhancement: The server consumed excess memory while parsing statements with hundreds or thousands of nested boolean conditions (such as OR (OR ... (OR ... ))). This could lead to a server crash or incorrect statement execution, or cause other client statements to fail due to lack of memory. The latter result constitutes a denial of service. (Bug#38296: http://bugs.mysql.com/38296) * Incompatible Change: There were some problems using DllMain() hook functions on Windows that automatically do global and per-thread initialization for libmysqld.dll: + Per-thread initialization: MySQL internally counts the number of active threads, which causes a delay in my_end() if not all threads have exited. But there are threads that can be started either by Windows internally (often in TCP/IP scenarios) or by users. Those threads do not necessarily use libmysql.dll functionality but still contribute to the open-thread count. (One symptom is a five-second delay in times for PHP scripts to finish.) + Process-initialization: my_init() calls WSAStartup that itself loads DLLs and can lead to a deadlock in the Windows loader. To correct these problems, DLL initialization code now is not invoked from libmysql.dll by default. (Bug#37226: http://bugs.mysql.com/37226) * Incompatible Change: Some performance problems of SHOW ENGINE INNODB STATUS were reduced by removing used cells and Total number of lock structs in row lock hash table from the output. These values are now present only if UNIV_DEBUG is defined at MySQL build time. (Bug#36941: http://bugs.mysql.com/36941, Bug#36942: http://bugs.mysql.com/36942) * Important Change: The INFORMATION_SCHEMA.FALCON_TABLES table has been removed. (Bug#29211: http://bugs.mysql.com/29211, Bug#34705: http://bugs.mysql.com/34705, Bug#34706: http://bugs.mysql.com/34706) Enjoy! -- Jonathan Perkin, Product Engineering, MySQL Database Technology Group, Sun Microsystems -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
which solution is better for $count and @cols
well, we have a where $where, and I want some @cols depends on $start, $rows. besides, I want $count too. so we have two solution here. A, two SQLs. 1, SELECT COUNT(*) FROM table WHERE $where 2, SELECT col FROM table WHERE $where LIMIT $start, $rows. B one SQLs with some operation SELECT col FROM table WHERE $where while $count is scalar @cols and real cols is splice(@cols, $start, $rows) which solution is better? or it depends on the $count, big count A is better and small is B? Thanks. -- Fayland Lam // http://www.fayland.org/ Foorum based on Catalyst // http://www.foorumbbs.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which solution is better for $count and @cols
Fayland Lam wrote:
well, we have a where $where, and I want some @cols depends on $start,
$rows. besides, I want $count too.
so we have two solution here.
A, two SQLs.
1, SELECT COUNT(*) FROM table WHERE $where
2, SELECT col FROM table WHERE $where LIMIT $start, $rows.
B one SQLs with some operation
SELECT col FROM table WHERE $where
while $count is scalar @cols and real cols is splice(@cols, $start, $rows)
which solution is better? or it depends on the $count, big count A is
better and small is B?
Use A always. You might get away with using SQL_CALC_FOUND_ROWS, but
I've always found that I need to know the total row count before I run
the query because if you are asking for a $start which is beyond the
$count, I want to modify $start before running the second query.
It ends up being like this:
A, two SQLs
1, SELECT COUNT(*) FROM table WHERE $where
1.5, if ($count $start) { $start = 1; }
2, SELECT col FROM table WHERE $where LIMIT $start, $rows.
Option B is horrible for large result sets. Only drawback to A is the
tediousness of having 2 queries, but you get over that once you develop
a pattern for writing them that way.
-- 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: which solution is better for $count and @cols
Thanks very much.
D. Dante Lorenso wrote:
Fayland Lam wrote:
well, we have a where $where, and I want some @cols depends on $start,
$rows. besides, I want $count too.
so we have two solution here.
A, two SQLs.
1, SELECT COUNT(*) FROM table WHERE $where
2, SELECT col FROM table WHERE $where LIMIT $start, $rows.
B one SQLs with some operation
SELECT col FROM table WHERE $where
while $count is scalar @cols and real cols is splice(@cols, $start,
$rows)
which solution is better? or it depends on the $count, big count A is
better and small is B?
Use A always. You might get away with using SQL_CALC_FOUND_ROWS, but
I've always found that I need to know the total row count before I run
the query because if you are asking for a $start which is beyond the
$count, I want to modify $start before running the second query.
It ends up being like this:
A, two SQLs
1, SELECT COUNT(*) FROM table WHERE $where
1.5, if ($count $start) { $start = 1; }
2, SELECT col FROM table WHERE $where LIMIT $start, $rows.
Option B is horrible for large result sets. Only drawback to A is the
tediousness of having 2 queries, but you get over that once you
develop a pattern for writing them that way.
-- Dante
--
D. Dante Lorenso
[EMAIL PROTECTED]
--
Fayland Lam // http://www.fayland.org/
Foorum based on Catalyst // http://www.foorumbbs.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Sort by Array
Just staightly use ... in .. is ok. On Fri, Oct 24, 2008 at 12:57 AM, Bill Newton [EMAIL PROTECTED]wrote: Pretty standard mysql function. Its been in mysql for a while. http://dev.mysql.com/doc/refman/4.1/en/string-functions.html#function_field Jim Lyons wrote: I'm not familiar with order by field (unless field is a UDF). I know of order by binary. Is this standard mysql syntax? On Wed, Oct 22, 2008 at 10:42 AM, Peter Brawley [EMAIL PROTECTED] wrote: ORDER BY id(5, 34, 9, 25) Can anyone tell me the proper syntax to accomplish this task? ORDER BY FIELD( id, 5, 34, 9, 25 ) PB - Keith Spiller wrote: Hi Guys, I'm trying to sort by a particular order: SELECT * FROM tablename WHERE id='5' OR id='9' OR id='25' OR id='34' ORDER BY id(5, 34, 9, 25) Can anyone tell me the proper syntax to accomplish this task? Thanks for your help. Keith No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.8.2/1739 - Release Date: 10/22/2008 7:23 AM -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850 ext 141/ Tel (888) 829-3631/ Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Rotate regular log file only
You're wrong.The new log file will be generated when you flush logs manually. On Fri, Oct 24, 2008 at 2:23 AM, Olaf Stein [EMAIL PROTECTED] 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 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Permissions
I know it.Thanks. I'm sorry that I had a mistake to what you said. On Thu, Oct 23, 2008 at 2:32 AM, Ian Christian [EMAIL PROTECTED] wrote: 2008/10/21 Moon's Father [EMAIL PROTECTED]: Could you please give me an idea of how to manage the privileges inside mysql? http://www.google.co.uk/search?q=mysql+grant first hit :) -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: user expires?
It didn't occured unless you manually changed your user's privilege. On Wed, Oct 22, 2008 at 11:05 PM, kalin m [EMAIL PROTECTED] wrote: hi all... i had a weired thing happened is it possible for a user privileges to expire?! suddenly today an application stopped working and i was getting the message that the user can't login. now, i did mess with it last night trying to give it file privileges but since i would have to give it file privileges to everything (doesn't make sense) i didn't do it. i did try thought with: mysql grant file to the [EMAIL PROTECTED] etc. it didn't work and i left it alone. didn't flush any privileges. it appears all the privileges were taken off the user anyway?! i just did grant all again and it seems to be working again... whats up with that?!??! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Stopping DNS Lookups
Add skip-name-reslove in my.cnf and restart mysql immediately. On Thu, Oct 23, 2008 at 12:37 AM, Richard S. Huntrods [EMAIL PROTECTED]wrote: Awesome! Thanks very much - exactly what I was looking for. I'm in the field and was under the gun, otherwise would have checked the manuals first. Again, thanks. -Richard Hassan Schroeder wrote: On Wed, Oct 22, 2008 at 7:40 AM, Richard S. Huntrods [EMAIL PROTECTED] wrote: Recently I had to start monitoring the firewall traffic on this intranet, and discovered the MySQL server is routinely sending queries to the main DNS server (outside the firewall). I suspect the server is performing reverse DNS lookups for some reason. Is there a quick way of disabling these calls to the DNS server? See http://dev.mysql.com/doc/refman/5.0/en/dns.html HTH, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
