rang + order + limit optimization
i did SELECT .. FROM table WHERE cateid=1 AND ctimexx ORDER BY ctime DESC LIMIT 10 mysql can use INDEX(cateid,ctime), with NO filesort, this is very fast, cos LIMIT is used by optimization but now i have to query: cateid IN(1,2,3,4,5) now mysql use filesort, which is bad, cos it get all matched result(no LIMIT) in file then sort(using LIMIT here) any workaround? btw, i cannot add more field, e.g. ccateid=1 which eq to cateid IN(1,2,3,4,5) cos i have: cateid IN(1,2,3) cateid IN(2,4,5) cateid IN(1,3,6) etc... just they may in any combine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what does Rows_examined mean exactly?
slow.log: # Query_time: 14 Lock_time: 0 Rows_sent: 30 Rows_examined: 771327 SELECT * FROM `post` LIMIT 771297, 30; i dp have privmary key on table `post` does 'Rows_examined: 771327' means mysqlserver read through those 771327 rows to get 30 rows? it takes 14 seconds! possible to show 'Rows_examined' in a explain or other commands? slow log is hard to debug.. and possible to optimize? _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what does Rows_examined mean exactly?
hrm.. but i tried SELECT * FROM post ORDER BY postdate DESC LIMIT 771297, 30 postdate is not primary key but just an INDEX it still examined 771297 rows From: Michael Stassen [EMAIL PROTECTED] To: tinys xuefer [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: what does Rows_examined mean exactly? Date: Sun, 25 Jul 2004 10:32:49 -0400 MIME-Version: 1.0 Received: from out014.verizon.net ([206.46.170.46]) by mc4-f19.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Sun, 25 Jul 2004 07:32:49 -0700 Received: from verizon.net ([68.163.178.105]) by out014.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id [EMAIL PROTECTED]; Sun, 25 Jul 2004 09:32:49 -0500 X-Message-Info: JGTYoYF78jFocj+u73FHpy/MHvLpLYvD Message-ID: [EMAIL PROTECTED] User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 X-Accept-Language: en-us, en References: [EMAIL PROTECTED] In-Reply-To: [EMAIL PROTECTED] X-Authentication-Info: Submitted using SMTP AUTH at out014.verizon.net from [68.163.178.105] at Sun, 25 Jul 2004 09:32:49 -0500 Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 25 Jul 2004 14:32:50.0020 (UTC) FILETIME=[43016240:01C47254] tinys xuefer wrote: slow.log: # Query_time: 14 Lock_time: 0 Rows_sent: 30 Rows_examined: 771327 SELECT * FROM `post` LIMIT 771297, 30; i dp have privmary key on table `post` does 'Rows_examined: 771327' means mysqlserver read through those 771327 rows to get 30 rows? it takes 14 seconds! possible to show 'Rows_examined' in a explain or other commands? slow log is hard to debug.. and possible to optimize? But you didn't use the primary key! In fact, you didn't ask for any order at all. Mysql does not try to guess that you meant to order by the primary key, it simpply does what you tell it. Your query, in effect, tells mysql to pick 771327 rows from post in any order and send you the last 30. Assuming your primary key column is named id, you need to change this query to SELECT * FROM post ORDER BY id LIMIT 771297, 30; With the explicit ORDER BY on the primary key, mysql will use the index to quickly find the 30 rows you want. Michael _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 query lock all others query
i've once noticed it it has been 8 but i remove it now, problem still exists From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'xuefer tinys' [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: RE: 1 query lock all others query Date: Thu, 7 Aug 2003 11:39:34 -0700 Check you mysqld server veriable thread_concurrency. I'm not sure that this will solve your problem since your problem has changed from a select locking to concurrent selects. ---Original Message- --From: xuefer tinys [mailto:[EMAIL PROTECTED] --Sent: Thursday, August 07, 2003 11:18 AM --To: [EMAIL PROTECTED]; [EMAIL PROTECTED] --Subject: RE: 1 query lock all others query -- --but one guy in #mysql said: --[quote] --with myisam tables if an update is running everything else is blocked, --multiple selects are allowed to run at the same time, selects and non --interfering inserts can run at the same time --[/quote] -- --so i ask in mailinglist --because i can't get my multiple selects run at the same time. -- --From: Dathan Vance Pattishall [EMAIL PROTECTED] --To: 'xuefer tinys' [EMAIL PROTECTED], [EMAIL PROTECTED] --Subject: RE: 1 query lock all others query --Date: Thu, 7 Aug 2003 10:59:46 -0700 -- --For MYISAM tables selects cause table locks while INNODB does row level --locking. -- --Now if your select is taking a long time you might want to look at your --index schema. If that does not work for you then split up your data. -- -Original Message- From: xuefer tinys [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 10:24 AM To: [EMAIL PROTECTED] Subject: 1 query lock all others query when i show processlist 1 of my queries is sending data (SELECT ...) while others queries (SELECT/UPDATE) is locked what's up? _ MSN Messenger: --http://messenger.msn.com/cn -- 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] -- -- --_ -- MSN Hotmail http://www.hotmail.com _ MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.14 runs CPU usage up and eventually stop
you have similar problem as me i have my search box very slow, which eating so much cpu usage and if 1+ UPDATE wait for this query, and all other SELECT queries wait the UPDATE to be finished are u going to report this bug(high cpu usage) to bugs.mysql.net? From: Cliff [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: 4.0.14 runs CPU usage up and eventually stop Date: Fri, 8 Aug 2003 08:00:47 - (GMT) Hi, I am running 4.0.14 on a FreeBSD 4.8-STABLE machine (upgraded to CVS from last week) and I am having a problem. We run a fairly large website using PHP so there is always many connections to the database. On 4.0.12 the cpu usage never shot to over 20% and ran extremely fast. After upgrading to 4.0.14, the cpu goes anywhere between 20-60% and in many situations will not accept any database connections. After browsing the website for a few minutes, I will type mysql on the command line and it will give an error of too many connections. Does anyone know what may be causing this high load from the upgrade? Again the previous versions did not do this and ran very well. I could use 4.0.12 but I wanted to report this in case it continues in future versions. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ MSN Explorer: http://explorer.msn.com/lccn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1 query lock all others query
when i show processlist 1 of my queries is sending data (SELECT ...) while others queries (SELECT/UPDATE) is locked what's up? _ MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 query lock all others query
i've noticed that, when a thread sending data an update is first locked then other select is locked From: xuefer tinys [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: 1 query lock all others query Date: Fri, 08 Aug 2003 02:31:14 +0800 i've once noticed it it has been 8 but i remove it now, problem still exists From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'xuefer tinys' [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: RE: 1 query lock all others query Date: Thu, 7 Aug 2003 11:39:34 -0700 Check you mysqld server veriable thread_concurrency. I'm not sure that this will solve your problem since your problem has changed from a select locking to concurrent selects. ---Original Message- --From: xuefer tinys [mailto:[EMAIL PROTECTED] --Sent: Thursday, August 07, 2003 11:18 AM --To: [EMAIL PROTECTED]; [EMAIL PROTECTED] --Subject: RE: 1 query lock all others query -- --but one guy in #mysql said: --[quote] --with myisam tables if an update is running everything else is blocked, --multiple selects are allowed to run at the same time, selects and non --interfering inserts can run at the same time --[/quote] -- --so i ask in mailinglist --because i can't get my multiple selects run at the same time. -- --From: Dathan Vance Pattishall [EMAIL PROTECTED] --To: 'xuefer tinys' [EMAIL PROTECTED], [EMAIL PROTECTED] --Subject: RE: 1 query lock all others query --Date: Thu, 7 Aug 2003 10:59:46 -0700 -- --For MYISAM tables selects cause table locks while INNODB does row level --locking. -- --Now if your select is taking a long time you might want to look at your --index schema. If that does not work for you then split up your data. -- -Original Message- From: xuefer tinys [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 10:24 AM To: [EMAIL PROTECTED] Subject: 1 query lock all others query when i show processlist 1 of my queries is sending data (SELECT ...) while others queries (SELECT/UPDATE) is locked what's up? _ MSN Messenger: --http://messenger.msn.com/cn -- 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] -- -- --_ -- MSN Hotmail http://www.hotmail.com _ MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 query lock all others query
but one guy in #mysql said: [quote] with myisam tables if an update is running everything else is blocked, multiple selects are allowed to run at the same time, selects and non interfering inserts can run at the same time [/quote] so i ask in mailinglist because i can't get my multiple selects run at the same time. From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'xuefer tinys' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: 1 query lock all others query Date: Thu, 7 Aug 2003 10:59:46 -0700 For MYISAM tables selects cause table locks while INNODB does row level locking. Now if your select is taking a long time you might want to look at your index schema. If that does not work for you then split up your data. ---Original Message- --From: xuefer tinys [mailto:[EMAIL PROTECTED] --Sent: Thursday, August 07, 2003 10:24 AM --To: [EMAIL PROTECTED] --Subject: 1 query lock all others query -- --when i show processlist --1 of my queries is sending data (SELECT ...) --while others queries (SELECT/UPDATE) is locked --what's up? -- --_ -- MSN Messenger: http://messenger.msn.com/cn -- -- --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] _ MSN Hotmail http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
suggestion to max_connection
often, mysql in web services, there're many situation make a mysql connection idle for example: after php open mysql_pconnect(), finished a request, http start keep-alive or service other pages/images which do not require mysql connection however, the connection is still there, counted as connection count, which is limited under max_connection imagine that so many connection idling, and no more connection can be taken so, my idea is to add setting like this: max_connection(idle+running): 400 max_connection_running(not idle): 50 i wonder if possible to make mysqld hold a connection without start a child process _ MSN Messenger: http://messenger.msn.com/cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
question about mysql-4.x escaping with USE_MB
quoted from mysql-4.1/libmysql/libmysql.c = static ulong mysql_sub_escape_string(CHARSET_INFO *charset_info, char *to, const char *from, ulong length) { const char *to_start=to; const char *end; #ifdef USE_MB my_bool use_mb_flag=use_mb(charset_info); #endif for (end=from+length; from != end ; from++) { #ifdef USE_MB int l; if (use_mb_flag (l = my_ismbchar(charset_info, from, end))) { while (l--) *to++ = *from++; from--; continue; } #endif = notice that USE_MB(it exists in older version, all mysql 4.x) it use connection CHARSET to escape string differently but it is said mysql-4.1 can have different charset on column,table,database how can the client know which table it's querying on? i guess this is why it failed to insert blob data to GBK mysql servers. so do anyone use multibyte mysql server? do mysql developers tested insert blob data under UTF-8 Mysql server? _ MSN Messenger: http://messenger.msn.com/cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Japanese Charset
i'm sure mysql4.x have wrong algorithm to escape/unescape multibyte chars a multibyte is escaped while server read it, it maybe a pair of single byte or a pair of single byte escaped while server read it, it appears as multibyte both of these two situation make server unescape incorrectly i've post the problem, no one take attention to it. at least, those who not using multibyte will never care about this problem. dunno weather your problem really cause by this wrong multi-byte-escape-algorithm From: Joel Rees [EMAIL PROTECTED] To: Dawn Friedland [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Japanese Charset Date: Fri, 20 Sep 2002 16:25:29 +0900 ... The problem characters are the ASCII backslash and the ASCII tilde - Good to know, I will eliminate those, although there are still many more problem characters. Well, actually, the one-byte backslash and tilde you can leave alone. They survive intact, they just display differently over here. Everybody's used to it, so no problem. Even programming in C, when we write something like '\t', the backslash (0x5c) shows up in our editors as the yen symbol, and we pretend that the escape character is the yen symbol, because, for us, when the encoding is shift-JIS, it is. So don't worry about the one-byte characters. ... I checked the text you gave me, and I found what's getting clobbered. It's the latter half of characters like the katakana 'so'. Although the byte that is getting walked on here is 0x5c, this is _not_ the escape character. It is preceded (in the case of katakana 'so') by a byte of 0x83. The entire character is '0x835c', and the 0x5c is being treated as if it were a backslash. There are other characters that will get hit by this, by the way. Bells ringing all over in my head. I think your content tool is mishandling backslashes, but it could be that MySQL or the driver is doing something the tool doesn't expect. (Well, really, the tool is probably mis-handling the backslashes.) This is actually independent of the language issues. I'm pretty sure I've seen this subject come up before on the list, just can't remember which way the turkey rolled. But your content tool will need to do something slightly different with the input. Could you search the archives about escape sequences or the backslash character? (Maybe someone who remembers could chime in here?) -- Joel Rees [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà MSN Messenger: http://messenger.microsoft.com/cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Japanese Charset
yes, when i post a problem, someone told me to give more info when i post the whole php-script, no response mysql-4.x unable to handle binary data when using multibyte charset, (maybe the old mysql-3.x escape/unescape is fine) file: mysql-4.0.1-alpha/libmysql_r/libmysql.c in function mysql_sub_escape_string ... #ifdef USE_MB int l; if (use_mb_flag (l = my_ismbchar(charset_info, from, end))) { while (l--) *to++ = *from++; from--; continue; } #endif what the hell is that while (l--) *to++ = *from++; ?? it has never been in old mysql-3.x and now, re-post my mail here ok, finally have a kind man take a look at my problem :) now before pasting a long example-script here is the test result: ** case 1 php4.2, mysql4.0.2 using GBK charset, windows mysqlclient-gbk is not supported by php4.2 win build, but i made gbk.conf in C:\mysql\share\charsets result: string - ? ( [ ?] [ ] ) escaped - È\ ( [ ?] [ \ ] [ ] ) result - È\ ( [ ?] [ \ ] [ ] ) *** damn! string - ? ( [ ?] [ ' ] ) escaped - È\' ( [ ?] [ \ ] [ ' ] ) cant query, error:#1064 You have an error in your SQL syntax near ''È\''' at line 1 string - È\' ( [ ?] [ \ ] [ ' ] ) escaped - È\\\' ( [ ?] [ \ ] [ \ ] [ \ ] [ ' ] ) cant query, error:#1064 You have an error in your SQL syntax near ''È\\\''' at line 1 ** case 2 php4.2, mysql4.0.2 using GBK charset, linux php4.2 compiled with lib mysql, GBK supported result: string - ? ( [ ?] [ ] ) escaped - È\ ( [ ?] [ \ ] [ ] ) result - È\ ( [ ?] [ \ ] [ ] ) *** damn! string - ? ( [ ?] [ ' ] ) escaped - È\' ( [ ?] [ \ ] [ ' ] ) cant query, error:#1064 You have an error in your SQL syntax near ''È\''' at line 1 string - È\' ( [ ?] [ \ ] [ ' ] ) escaped - È\\' ( [ ?] [ \ ] [ \ ] [ ' ] ) result - È\' ( [ ?] [ \ ] [ ' ] ) * fine and the php test script ** ?php error_reporting(E_ALL); $conn = mysql_connect('localhost', 'user', 'pass') or die('cant connect'); test(chr(200) . '', $conn); test(chr(200) . ', $conn); test(chr(200) . \\', $conn); function test($str, $conn) { echo brbr; dump_str('string', $str); $q_str = mysql_escape_string($str); // you may also try mysql_escape_string() (php cvs only) dump_str('escaped', $q_str); $res = mysql_query(SELECT '$q_str'); if (!$res) { print('font color=redcant query/font, error:#' . mysql_errno() . ' ' . mysql_error()); return; } $row = mysql_fetch_row($res) or die('empty result'); dump_str('result', $row[0]); echo $row[0] === $str ? * finebr:*** damn!br; } function dump_str($name, $str) { echo $name -gt; $str (; for ($i = 0; $i strlen($str); $i ++) { echo ' [ ' , $str{$i}, ' ] '; } echo )br; } ? From: Paul DuBois [EMAIL PROTECTED] To: xuefer tinys [EMAIL PROTECTED], [EMAIL PROTECTED],[EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Japanese Charset Date: Fri, 20 Sep 2002 08:56:22 -0500 At 9:09 + 9/20/02, xuefer tinys wrote: i'm sure mysql4.x have wrong algorithm to escape/unescape multibyte chars a multibyte is escaped while server read it, it maybe a pair of single byte or a pair of single byte escaped while server read it, it appears as multibyte both of these two situation make server unescape incorrectly i've post the problem, no one take attention to it. at least, those who not using multibyte will never care about this problem. You'll probably find that the issue receives more developer attention if you can provide hard evidence that there is a problem, preferably accompanied by a repeatable test case. The assertion I'm sure MySQL has a problem just doesn't carry much weight otherwise. There are plenty of assertions like that on this mailing list, the vast majority of which turn out to be misunderstanding on the user end. I'm not saying you haven't uncovered a real bug, just that a better demonstration that there *is* a bug would be more helpful than just making a claim. dunno weather your problem really cause by this wrong multi-byte-escape-algorithm From: Joel Rees [EMAIL PROTECTED] To: Dawn Friedland [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Japanese Charset Date: Fri, 20 Sep 2002 16:25:29 +0900 ... The problem characters are the ASCII backslash and the ASCII tilde - Good to know, I will eliminate those, although there are still many more problem characters. Well, actually, the one-byte backslash and tilde you can leave alone. They survive intact, they just display differently over here. Everybody's used to it, so no problem. Even programming in C, when we write something like '\t', the backslash (0x5c) shows up in our editors as the yen symbol, and we pretend that the escape character is the yen symbol
RE: Japanese Charset
i'm sorry that i've not read through nor understand the whole thread From: Shashank Tripathi [EMAIL PROTECTED] To: 'xuefer tinys' [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: RE: Japanese Charset Date: Sat, 21 Sep 2002 00:22:04 +0900 I hope you will recognize that what you stated as a problem is _not_ what this thread was about. You are talking about -- (a) A new (and as yet non-universal) version of MySQL (b) Only binary data (c) Conf done by you, which is not standard Whereas the thread was about simple Japanese multibyte display. You'll notice that being more specific and relevant within the context of a mailing list thread is more fruitful as Paul indicated in an earlier note. Anyway, hope you sort out your problem (if there is one). Cheers, Shanx Sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ ÏíÓÃÊÀ½çÉÏ×î´óµÄµç×ÓÓʼþϵͳ¡ª MSN Hotmail¡£http://www.hotmail.com/cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL-4.x GBK Problem with Binary Data
re-posting multibyte problem. does anyone have problem with binary data in MySQL-4.x using GBK charset or any other multibyte charset? inserting/updating/replacing the full description is in mysql mailing list archive, sent by me will not repost full desc until someone have or care this problem thx _ ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà MSN Messenger: http://messenger.microsoft.com/cn/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL-4.x GBK Problem with Binary Data
it's true that mysql-4.x process binary data well when not using multi-byte charset but mysql-4.x process it differently when using multi-byte it's only for GBK text escaping, which is not suitable for binary data that's the key point if u don't use multi-byte, and mysql-4.x, u will never have such problem From: Gerald Clark [EMAIL PROTECTED] To: xuefer tinys [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: MySQL-4.x GBK Problem with Binary Data Date: Fri, 20 Sep 2002 15:35:20 -0500 Binary data is binary data. It doesn't mattrer whether it is GBK or and audio file, or a jpeg. If you escape the data properly before inserting it you will get back the same data you stored. Your application must display it properly. xuefer tinys wrote: re-posting multibyte problem. does anyone have problem with binary data in MySQL-4.x using GBK charset or any other multibyte charset? inserting/updating/replacing the full description is in mysql mailing list archive, sent by me will not repost full desc until someone have or care this problem thx _ ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà MSN Messenger: http://messenger.microsoft.com/cn/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ ÏíÓÃÊÀ½çÉÏ×î´óµÄµç×ÓÓʼþϵͳ¡ª MSN Hotmail¡£http://www.hotmail.com/cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Aliasing all fields
he may want SELECT table1.* prefix t1,table2.* prefix t2 which not seems to be supported. instead of select table1.c1 as t1c1,table1.c2 as t1c2, table2.c1 as t2c2.. From: z [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Aliasing all fields Date: Wed, 18 Sep 2002 19:51:16 -0700 On Wed, 18 Sep 2002 22:45:29 -0400, Rob Hutton wrote: Is there a way to get MYSQL to alias all fields in a select even if there is no naming conflict? For instance, if I do a: select * from table1, table2 where table1.t1c1 = table2.t2c2 then I want would select table1.*,table2.* from do that? -z -- z, [EMAIL PROTECTED] on 09/18/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ ÏíÓÃÊÀ½çÉÏ×î´óµÄµç×ÓÓʼþϵͳ¡ª MSN Hotmail¡£http://www.hotmail.com/cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
question about fulltext index
when will we be able to set our own word determination? when will mysql support multibyte fulltext index _ ÏíÓÃÊÀ½çÉÏ×î´óµÄµç×ÓÓʼþϵͳ¡ª MSN Hotmail¡£http://www.hotmail.com/cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
question about cached result
i'm using mysql 4.x it is said the query result is cached by mysql for next query i have a forum, having post table post: postid, title, content, postdate, clickcount and clickcount is increased when the post is viewed and a page to search post select * from post where title like %word to match% will this result cached by mysql ? _ Ãâ·ÑÏÂÔØ MSN Explorer: http://explorer.msn.com/lccn/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: question about varchars
i think it's just a length limiter in serversite (mysql server) string longer than this limit will be cut off allowing you to be lazy to cut it off in clientsite (mysql client) and also a way to indicate the string length, don't have to write a txt file for document From: toby - To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: question about varchars Date: Sat, 27 Jul 2002 05:20:01 + desmond one of my teachers at college once told me to give as less a digit as possible that is give it jst a BIT more den i need coz it takes up space . :S ... will someone correct me if im rong here . pleez toby Hello there Mysql documentation says the following: values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. So, then would there be anything wrong with making all my varchar's up to 255. That is defining most of my fields to be varchar(255) even when i know they may only go 100? Thanks Desmond _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà MSN Messenger: http://messenger.microsoft.com/cn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
fail to insert BLOB data on multibyte charset MySQL server
failed to insert BLOB data on multibyte charset i'm using GBK charset. mysql 4.01 use PHP to insert, did addslashes before insert however, failed. i've read the mysql_escape_string() source code. noticed that, mb char is processed differently, but is it a must ? yes, php+libmysql is not up to date, but i'm trying to use my own escaping function function GBK_escape_string($data) { $ret = ''; $l = strlen($data); for ($i = 0; $i $l; $i ++) { $c = ord($data{$i}); if (0x81 = $c $c = 0xfe ($i+1) $l) { $c2 = ord($data{$i+1}); if ((0x40=$c2 $c2=0x7e) || (0x80=$c2 $c2=0xfe)) { $ret .= $data{$i}; $ret .= $data{++$i}; continue; } } switch ($data{$i}) { case \\: $ret .= ; break; case \0: $ret .= \\0; break; case \n: $ret .= \\n; break; case \r: $ret .= \\r; break; case ': $ret .= \\'; break; case \: $ret .= \\\; break; case \032: $ret .= \\Z; break; default: $ret .= $data{$i}; break; } } return $ret; } still won't work anyone could give me a hand? i'm supposing the error: string: \n\xE0'. according to ctype-gbk.c ismbchar_gbk \xE0' is not mbchar, \xE0 should not escape, and ' should escaped. result: \x5Cn\xE0\x5C'\x5C. (0x5C is backslash) when mysqld scanning this string \x5Cn is scan as \n, correct but \xE0\x5C is scan as a GBK char, then ' will be a string terminator and complain that SQL syntax near '\' which is the escaped double-quote ...0x5C... _ ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà MSN Messenger: http://messenger.microsoft.com/cn/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: how to prevent from Creating Large Numbers of Tables ???
SET? i know how to use SET but... attribute of products is value such as Size Weight Color and so on the problem is, different CATE of products have different bunch of attribute On Tue, 2002-06-18 at 00:53, Xuefer wrote: (mailbox full? repost) I have many product CATE (category) and every CATE of products have different attribute how can I orgnize table struct ? each CATE a table with many cols for attribute? [cateid attr1 attr2 ... ] or get attribute in one table and each row one attribute [produceID attributeID attributeValue ] or any other ways? Hello Xuefer, You can use one or more SET type columns to store the properties. See http://www.mysql.com/doc/S/E/SET.html for more details. Good Luck! -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ideas to mysql replication
why the current implement of replication is so difficult to make use of? a few days recently, i though of mysql replication and got an ideas - idea 1: i leant it from IRC server no master and slave servers have equal rights and specify 1 and only 1 server as REGISTER SERVER(may be named as id server) all client update goto nearby server and server judge if the QUERY is and UPDATE/INSERT or SELECT for SELECT do locally for UPDATE/INSERT, which care about autoindex/primary key/unique index, do locally else, send to REGISTER SERVER, and wait for result(error OR last_insert_id()) this can solve non conflicting KEY updates between the tables problem but still can't solve update the sam rows on two servers -- idea 2: i've lookup the mysql client library code there's a code doing update/insert query AUTO redirect to master server only SELECT done in slave server but.. it's in CLIENT library if we got application in binary release, how can we make use of replication? query auto redirect should can be done in server, and we can do replication without even recompiling source -- do u guys have any other ideas ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Re: how to prevent from Creating Large Numbers of Tables ???
thx for replying it's really a new course for me option 1 will still make lots of tables does option 2 waste of space ? cause 2 id with only 1 value i'm going to make table as described below. i wonder it's feasibility also bring another question here: my forum has favorite table for use to collect their favorite post Table `favorite`: cols: userid INT, postid INT unique index(userid, postid) only a pair of ID pre row question: does this struct waste of space? if so, any better solution ? - struct of product attribute: - Table `cate`: cols: cateID cateName example row: 1 switches-100 Table `Attribute`: cols: cateID attributeID attributeName displayOrder example row: 1 1 color 1 1 2 size 2 Table `product`: cols: cateID productID productName productDescription example row: 1 1 greenswitch blah blash... Table `AttributeValue`: cols: productID attributeID attributeValue(varchar) example row: 1 1 green 1 2 10*10 - to delete attribute: DELETE FROM Attribute where attributeID=$id DELETE FROM AttributeValue where attributeID=$id On Tue, 2002-06-18 at 12:28, Xuefer wrote: SET? i know how to use SET but... attribute of products is value such as Size Weight Color and so on the problem is, different CATE of products have different bunch of attribute Heh. Sorry - should have read more carefully. On Tue, 2002-06-18 at 00:53, Xuefer wrote: (mailbox full? repost) I have many product CATE (category) and every CATE of products have different attribute how can I orgnize table struct ? each CATE a table with many cols for attribute? [cateid attr1 attr2 ... ] or get attribute in one table and each row one attribute [produceID attributeID attributeValue ] or any other ways? Option one is good if you have a small number of attributes that are not likely to change. A major drawback is that you will need to alter the table to add/delete additional properties. Option two is more flexible, and will be more efficient for large numbers of dissimilar attributes. This approach is also better when you need to add/delete attribute types. If you have *very* dissimilar attributes and sets of attributes for each item then you might want to store the data in a serialized form in a blob field. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Re: Re: how to prevent from Creating Large Numbers of Tables ???
sorry, i can't quite get what u meant dunno how to do as u said You might want to associate the attributes with categories in a different table. This way you could put an attribute into multiple categories. You could also specify the displayORder in the category table as well. On Tue, 2002-06-18 at 15:42, Xuefer wrote: thx for replying it's really a new course for me option 1 will still make lots of tables does option 2 waste of space ? cause 2 id with only 1 value How many tables are created and how much spaces is wasted depends on the data and the design. Test a few different designs - see how much space the designs take and how complex the queries are. i'm going to make table as described below. i wonder it's feasibility also bring another question here: my forum has favorite table for use to collect their favorite post Table `favorite`: cols: userid INT, postid INT unique index(userid, postid) only a pair of ID pre row question: does this struct waste of space? if so, any better solution ? This is probably the best way to handle it. - struct of product attribute: - Table `cate`: cols: cateID cateName example row: 1 switches-100 Table `Attribute`: cols: cateID attributeID attributeName displayOrder example row: 1 1 color 1 1 2 size 2 Table `product`: cols: cateID productID productName productDescription example row: 1 1 greenswitch blah blash... Table `AttributeValue`: cols: productID attributeID attributeValue(varchar) example row: 1 1 green 1 2 10*10 - to delete attribute: DELETE FROM Attribute where attributeID=$id DELETE FROM AttributeValue where attributeID=$id Good Luck! -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Re: Re: Re: how to prevent from Creating Large Numbers ofTables ???
cateogry.members a list of att_type_id, right? and also displayOrder i got it. it's nice :) but is it a must to have attributes.id ? unique id (product_id,att_type_id) is not enough ? to remove a product delete from attributes where product_id=$id delete from product where id=$id On Tue, 2002-06-18 at 19:02, Xuefer wrote: sorry, i can't quite get what u meant dunno how to do as u said You might want to associate the attributes with categories in a different table. This way you could put an attribute into multiple categories. You could also specify the displayORder in the category table as well. Hi Xuefer, No problem - sometimes code is the best explanation! :) Here are the rough tables for what I described - they *may* work! :) CREATE TABLE category ( id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, nameVARCHAR(64) NOT NULL, members TEXT NOT NULL, PRIMARY KEY (id,name), ... ); CREATE TABLE att_type ( id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, nameVARCHAR(64) NOT NULL, description TEXT NOT NULL, PRIMARY KEY (id,name), ... ); CREATE TABLE product ( id MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL, categorySMALLINT UNSIGNED NOT NULL, nameVARCHAR(64) NOT NULL, description TEXT NOT NULL, PRIMARY KEY (id), ... ); CREATE TABLE attributes ( id INT UNSIGNED AUTO_INCREMENT NOT NULL, product_id MEDIUMINT UNSIGNED NOT NULL, att_type_id SMALLINT UNSIGNED NOT NULL, value VARCHAR(64) NOT NULL PRIMARY KEY (id), ... ); Good Luck! -- Gosh, Batman. The nobility of the almost-human porpoise. --Robin __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com Feed the Dolphin! Order MySQL support from the MySQL developers at https://order.mysql.com/?ref=mzgr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hello, i need help, about mysql MISSING charsets
i've report this bug to php, they said it's not bug of php but i've found the problem * ALL is ok for SERVER, problem issue only by CLIENT(libmysql.a) php-win-binary don't compile libmysql supporting GBK charset! neight other multibyte charset. also the one i compiled is linked with libmysql.a without GBK build-in but It might NOT a bug, just a SERIOUS problem: does a clinet REALLY need build-in charset? ctype-gbk.c is about 140KB! how can EVERY client bear that? :( Victoria Reznichenko wrote: It might be PHP or Apache problem. Please, check how encoding works with another client (i.e. MySQL client package). Did you specified path to your character set dirs in the my.cnf (my.ini) file? GBK should be buildin charset, shouldn't in that path. but now, what i can do is just copy latin1.conf to gbk.conf What exactly doesn't work? client complain about missing charset #28 (gbk) Xuefer [EMAIL PROTECTED] 2002-04-16 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
bug in file ibmysql_r/libmysql.c
Description: enable-reads-from-master and repl-parse-query is mixed How-To-Repeat: . Fix: line 839: case 22: /* enable-reads-from-master */ options-rpl_parse = 1; break; case 23: /* repl-parse-query */ options-no_master_reads = 0; break; should be: case 22: /* enable-reads-from-master */ options-no_master_reads = 0; break; case 23: /* repl-parse-query */ options-rpl_parse = 1; break; or change static const char *default_options[]=.. and those comments; Submitter-Id: xuefer Originator:personal Organization: 123 MySQL support: none Synopsis: option wrong processed Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.1-alpha (Source distribution) Environment: System: Linux test 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.0.4/specs Configured with: Thread model: single gcc version 3.0.4 Compilation info: CC='gcc' CFLAGS='-O6 -mcpu=pentiumpro -fomit-frame-pointer' CXX='c++' CXXFLAGS='-O6 -fomit-frame-pointer -mcpu=pentiumpro -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2ÔÂ 12 22:08 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1283964 12ÔÂ 8 22:14 /lib/libc-2.2.4.so -rw-r--r--1 root root 27314296 12ÔÂ 8 22:02 /usr/lib/libc.a -rw-r--r--1 root root 178 12ÔÂ 8 22:02 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared --with-openssl --with-default-port=7706 --with-charset=gbk --with-default-character-set=gbk --with-extra-charsets=none - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
hello, i need help, about mysql MISSING charsets
mysql: mysql-4.01 binary package windows also the same in linux source/binary package when SHOW VARIABLES got: - character_set gbk character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 - but in apache+php, file Apache\logs\error.log got: - File 'c:\mysql\\share\charsets\gbk.conf' not found (Errcode: 2) Character set '#28' is not a compiled character set and is not specified in the 'c:\mysql\\share\charsets\Index' file - i've also compiled in linux with charset gbk gbk should work, but it didn't why? either with gb2312 does MultiByte Charset support broken? Xuefer [EMAIL PROTECTED] 2002-04-12 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php