how to select last records
hello, ususally we use : select * from table limit 50; This will display the rows which were entered first the table created (i..e, that end); if i wnat to select from the other side then what should i use ? if there is a serial no column like id or some autoincremt column or DATE then we can query on desc order or date wise . other than that if i hv just 2-3 fields in my table(name, city,phone). i want to retrieve the records from top end (oldest 10) or from the other end (latest 50) --How to select then? i also want anybody to tell if they know : is there any way to select from particular row no to othereg. row 10 to row 30 ? like this? N. Kavithashree === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing datas
Hi, Here is my problem : I want to copy datas from an existing website to my local website. The website is a collection of images with thumbnails. With phpMyAdmin, i made an export of the image table and i have a 16 MB file 'image.sql' containing sql orders to create table and insert values. With phpMyAdmin of local website, I tried to execute that sql file but got errors related to memory allowed to php (2 MB max). I tried to modify 'memory_limit', 'upload_max_filesize' in /etc/php.ini but still got errors. So I tried with mysql tools. with source /path/image.sql; the table is created but i got the following error : ERROR 1153: Got a packet bigger than 'max_allowed_packet' I've seen threads in this list about same error, but I feel i'm in a wrong way... What would be a good way of doing that export/import of datas ? What to do if I want to upload bigger datas on the existing website ? (I only have a phpMyAdmin access) Sorry if the answer is obvious, but I'm new to MySQL, phpMyAdmin. My local config is PowerMac G4, Mac OS X 10.3.5, MySQL 4.0.21 Thanks in advance Michel RENON -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Correlated subquery help
Hi Dan- Thx for responding. And yes, I think you're absolutely correct. Let me update that query - should look like: sql select a.k1, a.k2, a.total_amt from Z a where a.total_amt in (select b.total_amt from Z b where b.k1 = a.k1 order by b.total_amt desc limit 10) order by a.k1, a.total_amt desc ; /sql Anyone else? Thanks, R -Original Message- From: Dan Sashko [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 9:49 PM To: Mysql Subject: Re: Correlated subquery help isn't the where subquery would always return only one record if set of (k1,k2) is a primary key? I dont have 4.1+ installed to test on but if you remove 'limit 10' and run it don't you get the same list as if you ran 'select k1,k2,total_amt from Z' ? - Original Message - From: Rick Robinson [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 6:08 PM Subject: Correlated subquery help Hi all- I'm using MySQL 4.1.7, trying to do a subquery that's apparently unsupported - I'm hoping someone can provide a quick alternative for me. I have a simple table Z with 3 columns, k1, k2, and total_amt, where k1 and k2 make up the primary key. I want to create a report that lists the the top 10 total_amt for each k1, k2. My original query was going to be of the form: sql select a.k1, a.k2, a.total_amt from Z a where a.total_amt in (select b.total_amt from Z b where b.k1 = a.k1 and b.k2 = a.k2 order by b.total_amt desc limit 10) order by a.k1, a.total_amt desc ; /sql But I get the dreaded error: ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Is there a better way to do this query? Thanks for your help. Regards, R -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.7 Bug?
Hi, We have done the following test, which boils down to a simple connect and then a select * from accounts; (accounts=MyISAM, 3 records, 3 columns). Clientside: A stable win32 app capable of using any libmysql.dll Serverside: 4.1.7-nt win32 MySQL service (stable download), with vanilla setup Location: Client and Server running on same win32 machine (XP/SP2) Communication: TCP/IP Username/Password: root/ (full privileges) Test A: 1. Setup client with any 3.23.x-4.1.0 libmysql.dll (as provided with distribution) 2. Start MySQL 3. Connect Select 4. Result = OK 5. Disconnect, now use any 4.1.1 libmysql.dll 6. Connect Select 7. Result = columnnames screwy, i.e. contains illegible characters [8. Optionally switch back and forth between dlls, result is the same, i.e. connecting with any 4.1.1+ dll gives odd results while anything 4.1.0 works 100%) 9. Shutdown MySQL Test B (Inverse sequence): 1. Setup client with 4.1.1 libmysql.dll (as provided with distribution) 2. Restart MySQL 3. Connect Select 4. Result = OK 5. Disconnect, now use 3.23.x-4.1.0 libmysql.dll 6. Connect Select 7. Result = columnnames screwy, i.e. contains illegible characters [8. Optionally switch back and forth between dlls, result is the same, i.e. connecting with any 4.1.0 dll gives odd results while anything 4.1.1+ works 100%) 9. Shutdown MySQL Conclusion: MySQL 4.1.7 sets itself up for either an old or new style connection (i.e. CLIENT_PROTOCOL_41 in client/server capabilities) depending on the version of the first client which connects to it after it initially fires up. After which the other type (i.e. old or new) can't communicate with it properly. Initial connections are fine, but pulling any results, result in weird columnnames. We have done exactly the same tests (i.e. A and B) with other versions of MySQL, ex. 3.23.42, 4.0.20, 4.1.4 and even 5.0.0a all of which returned the correct results independent of the client version connecting first after it starts up, and always returns the correct results and columnames. In all tests the client app was exactly the same, no different settings, only the MySQL AB client dll was exchanged. We would appreciate any feedback as to if this is in fact a bug, or some MySQL setting causing this behavior or if this is something specific to our MySQL 4.1.7 installation (i.e. if no one else experience these symptoms with their 4.1.7 MySQL). Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hung MySQL queries
Joakim, please post the FULL .err log, the full output of SHOW PROCESSLIST and SHOW INNODB STATUS, as well as your my.cnf. Please also post the output of the Unix 'top'. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Joakim Ryden [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, December 02, 2004 8:35 AM Subject: Hung MySQL queries Hey everyone - I just migrated a database from one server to another (4.0.18 official RPM on RH ES 3) and now on the new server I'm running into a problem where queries hang in state statistics as shown by 'mysqladmin processlist'. I tried to see if there was something wrong with the queries themselves and went and grabbed one of them from show full processlist and tried to run it with 'explain' and that hung too. Tried the same thing on the old server and everything seems perfectly fine. Running out of ideas. :( Does anyone have any tips at all?? --Jo -- 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]
Re: MySQL 5.0.2-alpha has been released
Daniel, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, December 02, 2004 8:30 AM Subject: Re: MySQL 5.0.2-alpha has been released wow this is a massive feat, 5 will make many people proud, 4.1 has done the same aswell as 4.0.Well done, if you need testers for solaris or OSX lemme know. I also had a question thoughregarding INNODB and fulltext searching, when is this going to be made available or if ever ? I'vehad to do patchy work arounds like creating seperate tables to store the it is in the InnoDB roadmap at http://www.innodb.com/todo.php If someone sponsors the implementation, it could be available by the end of 2005. Otherwise, I am afraid it will take to 2006 or 2007. indexable data in that inthe past, but one of fields has a one to many relationship in the table of Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations with 4.1.7
On Tue, 30 Nov 2004 19:24:05 +0200, Gleb Paharenko [EMAIL PROTECTED] wrote: The first impression is that you forgot to convert character columns. See: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html http://dev.mysql.com/doc/mysql/en/Charset-conversion.html Once the Character Sets are set up (everything is utf8 now on my installation), how can it be achieved to convert *ALL* columns in *ALL* tables to the same, new COLLATION value? Changing them by hand would lead to admin's fun for, say, weeks ;) Thanks in advance, Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to select last records
To select row 10 to 30 below query might be useful select * from mytable limit 10,20; The query will list 11th record to 30th record. N. Kavithashree wrote: hello, ususally we use : select * from table limit 50; This will display the rows which were entered first the table created (i..e, that end); if i wnat to select from the other side then what should i use ? if there is a serial no column like id or some autoincremt column or DATE then we can query on desc order or date wise . other than that if i hv just 2-3 fields in my table(name, city,phone). i want to retrieve the records from top end (oldest 10) or from the other end (latest 50) --How to select then? i also want anybody to tell if they know : is there any way to select from particular row no to othereg. row 10 to row 30 ? like this? N. Kavithashree === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indices and Constraints.
Hi All, In oracle I can query user_constraints, user_indexes for getting the constraints and indices for a particular table. User_constraints and user_idexes are view which holds all the constraints and indices for a particular table. Need to know the similar one in MySQL. Do we have views or any other system tables in MySQL 4.0.21 which OUTPUTS the constraints and indices in a particular table? Please reply ASAP. Regards, Narasimha Ver: MYSQL 4.0.21 Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: Indices and Constraints.
Thats usually setup in the same table schema no ? On 02/12/2004, at 9:52 PM, [EMAIL PROTECTED] wrote: Hi All, In oracle I can query user_constraints, user_indexes for getting the constraints and indices for a particular table. User_constraints and user_idexes are view which holds all the constraints and indices for a particular table. Need to know the similar one in MySQL. Do we have views or any other system tables in MySQL 4.0.21 which OUTPUTS the constraints and indices in a particular table? Please reply ASAP. Regards, Narasimha Ver: MYSQL 4.0.21 Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reg SubQuery
[EMAIL PROTECTED] wrote: Thanks for the reply. The main aim is to select the first record or the last record. Is there any direct command for these. Please help me in this. There is no first or last unless there is a sort order. To get the first row: select * from table order by col1 limit 1 ...and to get the last row: select * from table order by col1 DESC limit 1 DESC in this case means descending, the default for ORDER BY is ASC, wich is short for ascending. LIMIT 1 is used to limit the result to only one row. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indices and Constraints.
[EMAIL PROTECTED] wrote: Need to know the similar one in MySQL. Do we have views or any other system tables in MySQL 4.0.21 which OUTPUTS the constraints and indices in a particular table? - show indexes from DATABASE_NAME.TABLE_NAME - or, show create table DATABASE_NAME.TABLE_NAME; - ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indices and Constraints.
Hi, Thank you. But I want select the constraints and indices used on the table. How can we get this information?. Please help me in this. Thanks, Narasimha -Original Message- From: Ian Sales [mailto:[EMAIL PROTECTED] Sent: Thursday, December 02, 2004 5:07 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: [EMAIL PROTECTED] Subject: Re: Indices and Constraints. [EMAIL PROTECTED] wrote: Need to know the similar one in MySQL. Do we have views or any other system tables in MySQL 4.0.21 which OUTPUTS the constraints and indices in a particular table? - show indexes from DATABASE_NAME.TABLE_NAME - or, show create table DATABASE_NAME.TABLE_NAME; - ian Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing bug in 4.1.7
Hi, All It's happen after upgrade from 3.23.46. Just look at this set of queries mysql select 1 or null; +---+ | 1 or null | +---+ | 1 | - Ok +---+ 1 row in set (0.00 sec) mysql create table a ( a int not null ); Query OK, 0 rows affected (0.00 sec) mysql select min( a ) is null from a; +--+ | min( a ) is null | +--+ |1 | - Ok +--+ 1 row in set (0.00 sec) mysql select min( a ) is null or null from a; +--+ | min( a ) is null or null | +--+ | NULL | - Why ??? +--+ 1 row in set (0.00 sec) It is very important for me to solve this problem. Thanks for any help Info from mysqlbug script Server version 4.1.7 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/.mysql.sock Uptime: 23 hours 43 min 34 sec Threads: 2 Questions: 53 Slow queries: 0 Opens: 20 Flush tables: 1 Open tables: 6 Queries per second avg: 0.001 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: SunOS asv 5.8 Generic_108529-23 i86pc i386 i86pc Architecture: i86pc Some paths: /usr/local/bin/perl /usr/ccs/bin/make /opt/sfw/bin/gmake /opt/sfw/bin/gcc /usr/local/bin/cc GCC: Reading specs from /opt/sfw/lib/gcc-lib/i386-pc-solaris2.8/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: -rw-r--r-- 1 root bin 1608268 Jul 30 2003 /lib/libc.a lrwxrwxrwx 1 root root 11 Mar 3 2004 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 956112 Jul 30 2003 /lib/libc.so.1 -rw-r--r-- 1 root bin 1608268 Jul 30 2003 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Mar 3 2004 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 956112 Jul 30 2003 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql-4.1.7' '--exec-prefix=/usr/local/mysql-4.1.7' '--libexecdir=/usr/local/mysql-4.1.7/bin' '--localstatedir=/main/MysqlDB' '--enable-thread-safe-client' '--with-unix-socket-path=/tmp/.mysql.sock' '--with-mysqld-user=mysql' '--without-debug' '--without-bench' '--with-charset=koi8r' Perl: This is perl, version 5.005_03 built for i86pc-solaris -- Vlad A. Shalnev E-mail: [EMAIL PROTECTED] Gravity can't be blamed for someone falling in love ( Albert Einstein ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indices and Constraints.
[EMAIL PROTECTED] wrote: Hi, Thank you. But I want select the constraints and indices used on the table. How can we get this information?. Please help me in this. Ian gave you the answer: From: Ian Sales [...] - show indexes from DATABASE_NAME.TABLE_NAME The syntax is: SHOW INDEX FROM tablename FROM dbname - or, show create table DATABASE_NAME.TABLE_NAME; To elaborate: You can not use the SELECT statement to get this information in MySQL, like you can in Oracle. In MySQL, you can only get this information from other statements, like SHOW INDEX FROM ... and SHOW CREATE TABLE. URL: http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html URL: http://dev.mysql.com/doc/mysql/en/SHOW_CREATE_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
Hello Vlad, The reason is: If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe = equality comparison operator. Thereore, or NULL in your select statement will always returned NULL. You can find all the rules for comparaison at URL: http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html Regards, Bernard On Thursday 02 December 2004 06:48, Vlad Shalnev wrote: Hi, All It's happen after upgrade from 3.23.46. Just look at this set of queries mysql select 1 or null; +---+ | 1 or null | +---+ | 1 | - Ok +---+ 1 row in set (0.00 sec) mysql create table a ( a int not null ); Query OK, 0 rows affected (0.00 sec) mysql select min( a ) is null from a; +--+ | min( a ) is null | +--+ |1 | - Ok +--+ 1 row in set (0.00 sec) mysql select min( a ) is null or null from a; +--+ | min( a ) is null or null | +--+ | NULL | - Why ??? +--+ 1 row in set (0.00 sec) It is very important for me to solve this problem. Thanks for any help Info from mysqlbug script Server version 4.1.7 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/.mysql.sock Uptime: 23 hours 43 min 34 sec Threads: 2 Questions: 53 Slow queries: 0 Opens: 20 Flush tables: 1 Open tables: 6 Queries per second avg: 0.001 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: SunOS asv 5.8 Generic_108529-23 i86pc i386 i86pc Architecture: i86pc Some paths: /usr/local/bin/perl /usr/ccs/bin/make /opt/sfw/bin/gmake /opt/sfw/bin/gcc /usr/local/bin/cc GCC: Reading specs from /opt/sfw/lib/gcc-lib/i386-pc-solaris2.8/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: -rw-r--r-- 1 root bin 1608268 Jul 30 2003 /lib/libc.a lrwxrwxrwx 1 root root 11 Mar 3 2004 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 956112 Jul 30 2003 /lib/libc.so.1 -rw-r--r-- 1 root bin 1608268 Jul 30 2003 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Mar 3 2004 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 956112 Jul 30 2003 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql-4.1.7' '--exec-prefix=/usr/local/mysql-4.1.7' '--libexecdir=/usr/local/mysql-4.1.7/bin' '--localstatedir=/main/MysqlDB' '--enable-thread-safe-client' '--with-unix-socket-path=/tmp/.mysql.sock' '--with-mysqld-user=mysql' '--without-debug' '--without-bench' '--with-charset=koi8r' Perl: This is perl, version 5.005_03 built for i86pc-solaris -- --- - Vlad A. Shalnev E-mail: [EMAIL PROTECTED] Gravity can't be blamed for someone falling in love ( Albert Einstein ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Results question
I have a select, from, where, query set up. There are a number of inner joins in it as well. Now what I noticed is if there are some null fields in the records, nothing will get returned. If I remove those particular joins (where the NULLS are), the record is returned. Does this sounds like a join issue ? The tables that are joined are interpretive tables. Meaning I store a 1 for Alabama in the main table. The States table I join has the 1 and then the associate label. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for MySQL Authors
Hi, beehive KG is looking for authors to write articles about advanced MySQL topics. Please go here for our writer's guidelines: http://www.beehive-eu.com/MySQLAuthors.html Cheers, Mark Pratt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Results question
Stuart Felenstein wrote: I have a select, from, where, query set up. There are a number of inner joins in it as well. Now what I noticed is if there are some null fields in the records, nothing will get returned. If I remove those particular joins (where the NULLS are), the record is returned. Does this sounds like a join issue ? The tables that are joined are interpretive tables. Meaning I store a 1 for Alabama in the main table. The States table I join has the 1 and then the associate label. Stuart You need LEFT JOIN to get results for the lines that contain NULL values in the joined table. http://dev.mysql.com/doc/mysql/en/JOIN.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
Bernard Clement wrote: Hello Vlad, The reason is: If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe = equality comparison operator. Thereore, or NULL in your select statement will always returned NULL. You can find all the rules for comparaison at URL: http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html Thanks for help but this is not my case. My case described in section Logical Operators: OR || Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any operand is NULL, otherwise 0 is returned. mysql SELECT 1 || 1; - 1 mysql SELECT 1 || 0; - 1 mysql SELECT 0 || 0; - 0 mysql SELECT 0 || NULL; - NULL mysql SELECT 1 || NULL; - 1 Any help appreciated. Hi, All It's happen after upgrade from 3.23.46. Just look at this set of queries mysql select 1 or null; +---+ | 1 or null | +---+ | 1 | - Ok +---+ 1 row in set (0.00 sec) mysql create table a ( a int not null ); Query OK, 0 rows affected (0.00 sec) mysql select min( a ) is null from a; +--+ | min( a ) is null | +--+ |1 | - Ok +--+ 1 row in set (0.00 sec) mysql select min( a ) is null or null from a; +--+ | min( a ) is null or null | +--+ | NULL | - Why ??? +--+ 1 row in set (0.00 sec) It is very important for me to solve this problem. Thanks for any help Info from mysqlbug script Server version 4.1.7 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/.mysql.sock Uptime: 23 hours 43 min 34 sec Threads: 2 Questions: 53 Slow queries: 0 Opens: 20 Flush tables: 1 Open tables: 6 Queries per second avg: 0.001 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: SunOS asv 5.8 Generic_108529-23 i86pc i386 i86pc Architecture: i86pc Some paths: /usr/local/bin/perl /usr/ccs/bin/make /opt/sfw/bin/gmake /opt/sfw/bin/gcc /usr/local/bin/cc GCC: Reading specs from /opt/sfw/lib/gcc-lib/i386-pc-solaris2.8/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: -rw-r--r-- 1 root bin 1608268 Jul 30 2003 /lib/libc.a lrwxrwxrwx 1 root root 11 Mar 3 2004 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 956112 Jul 30 2003 /lib/libc.so.1 -rw-r--r-- 1 root bin 1608268 Jul 30 2003 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Mar 3 2004 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 956112 Jul 30 2003 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql-4.1.7' '--exec-prefix=/usr/local/mysql-4.1.7' '--libexecdir=/usr/local/mysql-4.1.7/bin' '--localstatedir=/main/MysqlDB' '--enable-thread-safe-client' '--with-unix-socket-path=/tmp/.mysql.sock' '--with-mysqld-user=mysql' '--without-debug' '--without-bench' '--with-charset=koi8r' Perl: This is perl, version 5.005_03 built for i86pc-solaris -- --- - Vlad A. Shalnev E-mail: [EMAIL PROTECTED] Gravity can't be blamed for someone falling in love ( Albert Einstein ) -- Vlad A. Shalnev E-mail: [EMAIL PROTECTED] Gravity can't be blamed for someone falling in love ( Albert Einstein ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
Vlad Shalnev wrote: [...] OR || Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any operand is NULL, otherwise 0 is returned. This definition (from the manual) is self-contradicting: 1 OR NULL should evaluate to 1 because any operand is non-zero, but it should also evaluate to NULL because any operand is NULL. URL: http://dev.mysql.com/doc/mysql/en/Logical_Operators.html With an unclear definition, it is perhaps not so strange that behaviour will change with different versions of MySQL...? Maybe if you explain why you depend on this behaviour someone can suggest a workaround or a different solution? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing bug in 4.1.7
Hi This definition (from the manual) is self-contradicting: 1 OR NULL should evaluate to 1 because any operand is non-zero, but it should also evaluate to NULL because any operand is NULL. Why self-contradicting ? If you apply this rule as described ( from left to right ) you will get correct result. And it isn't work in 4.1.7 :(( As you see my first query work as described in definition but when i use aggregate function - it doesn't work. Thanks OR || Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any operand is NULL, otherwise 0 is returned. mysql SELECT 1 || 1; - 1 mysql SELECT 1 || 0; - 1 mysql SELECT 0 || 0; - 0 mysql SELECT 0 || NULL; - NULL mysql SELECT 1 || NULL; - 1 mysql select 1 or null; +---+ | 1 or null | +---+ | 1 | - Ok +---+ 1 row in set (0.00 sec) mysql create table a ( a int not null ); Query OK, 0 rows affected (0.00 sec) mysql select min( a ) is null from a; +--+ | min( a ) is null | +--+ |1 | - Ok +--+ 1 row in set (0.00 sec) mysql select min( a ) is null or null from a; +--+ | min( a ) is null or null | +--+ | NULL | - Why ??? +--+ 1 row in set (0.00 sec) -- Vlad A. Shalnev E-mail: [EMAIL PROTECTED] Gravity can't be blamed for someone falling in love ( Albert Einstein ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Locking Problem? Very Slow MyISAM DB - PLS HELP!
When the server begins to slow down, what does top reveal? Andrew Nelson wrote: The reason I ask is because eight select statements should not bog down a production server. On the MySQL side, is anything being written to the slow query log? On the application side is there any virus scanning or similar activity being performed? Does iostat show any heavy reading or writing activity? Is memory being swapped? What is the server load? Do you have a high wait time or is CPU usage the only symptom? Nothing is being written very often.. Maybe a few email accounts added/modified each day. iostat shows very small disk activity (around 0.4Mb/s average). No memory is being swapped.. We do have a high wait time - the email is queueing up and the website interface to update the database (in PHP) times out when it's busy and I figured it's the CPU - I don't get what is so computational, the Databases are quite small (it's just a userbase - about 200Kb). I appreciate your help, Thanks, Andrew. Andrew Nelson wrote: Hi Victor, How did you deduce that the database server is the bottleneck? Are all your processes running on the same machine? Because 'ps -aux' shows it running at 94% of the CPU and when I stop/start the mysql server, it seems to be ok again for another hour. Any ideas? Andrew Nelson wrote: Hi, I have a MySQL 3.23.55 server managing accounts on my exim mail server.. The table type on all tables MyISAM.. I have the MTA performing various queries for each incoming email - determining mail aliases, vacation messages and filtering rules etc but they're all pretty much SELECT statements.. I know this isn't ideal and i've started replacing runtime queries with processes that search text files instead (generated every few minutes etc) but it should still be able to cope I would have thought? At it's busiest, it's performing about 8 trivial queries per second. It's a Xeon 2.6Ghz machine with 1Gb of RAM (running on FreeBSD) but it's grinding to a halt. I have to keep stopping and restarting the MySQL server to regain speed. As there's many processes trying to access the same tables to do SELECTs I thought it might be a locking issue.. BDB didn't seem to help - can anyone suggest something that might help? Thanks, Andrew. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Cluster support for Windows
Hello, Does anyone know when MySQL cluster will become available for Windows? Thank you in advance, -- Vlasis Hatzistavrou, System Administrator, Hellenic Academic Libraries Link (HEAL-Link), Library of Physics Informatics, Aristotle University of Thessaloniki, email: [EMAIL PROTECTED] Phone: +30 2310 998208 Fax: +30 2310 999428 http://www.heal-link.gr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table Locking Problem? Very Slow MyISAM DB - PLS HELP!
Andrew, DO you have the slow query log turned on? What does one of your tables look like and the one of the 8 queries you talk about? It very well could just be a index problem. And what is the size of the data. Donny -Original Message- From: Andrew Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 11:22 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Table Locking Problem? Very Slow MyISAM DB - PLS HELP! The reason I ask is because eight select statements should not bog down a production server. On the MySQL side, is anything being written to the slow query log? On the application side is there any virus scanning or similar activity being performed? Does iostat show any heavy reading or writing activity? Is memory being swapped? What is the server load? Do you have a high wait time or is CPU usage the only symptom? Nothing is being written very often.. Maybe a few email accounts added/modified each day. iostat shows very small disk activity (around 0.4Mb/s average). No memory is being swapped.. We do have a high wait time - the email is queueing up and the website interface to update the database (in PHP) times out when it's busy and I figured it's the CPU - I don't get what is so computational, the Databases are quite small (it's just a userbase - about 200Kb). I appreciate your help, Thanks, Andrew. Andrew Nelson wrote: Hi Victor, How did you deduce that the database server is the bottleneck? Are all your processes running on the same machine? Because 'ps -aux' shows it running at 94% of the CPU and when I stop/start the mysql server, it seems to be ok again for another hour. Any ideas? Andrew Nelson wrote: Hi, I have a MySQL 3.23.55 server managing accounts on my exim mail server.. The table type on all tables MyISAM.. I have the MTA performing various queries for each incoming email - determining mail aliases, vacation messages and filtering rules etc but they're all pretty much SELECT statements.. I know this isn't ideal and i've started replacing runtime queries with processes that search text files instead (generated every few minutes etc) but it should still be able to cope I would have thought? At it's busiest, it's performing about 8 trivial queries per second. It's a Xeon 2.6Ghz machine with 1Gb of RAM (running on FreeBSD) but it's grinding to a halt. I have to keep stopping and restarting the MySQL server to regain speed. As there's many processes trying to access the same tables to do SELECTs I thought it might be a locking issue.. BDB didn't seem to help - can anyone suggest something that might help? Thanks, Andrew. -- 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]
Re: Comparing bug in 4.1.7
Hi! On Dec 02, Vlad Shalnev wrote: Looks like a bug. Could you submit a bugreport at http://bugs.mysql.com ? It happens after upgrade from 3.23.46. mysql create table a ( a int not null ); Query OK, 0 rows affected (0.00 sec) mysql select min( a ) is null or null from a; +--+ | min( a ) is null or null | +--+ | NULL | - Why ??? +--+ 1 row in set (0.00 sec) It is very important for me to solve this problem. Thanks for any help Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query--SelectionFromSameTable
Suggestions intermixed with your questions below. However, because you only described your table and neglected to post the actual CREATE TABLE statement, I will need to make a few assumptions (the actual name of your table and the fact that missing data is stored as nulls are two I can think of right away). N. Kavithashree [EMAIL PROTECTED] wrote on 12/02/2004 01:33:01 AM: hello, Example : i hv a table containg date, flower, perfume,codeno,regno etc There are some row which have only flower entries for a day. there are some rows which have only perfume entries for a day. some rows have both florwer and perfume entries for a day..ie same day contains both entries. same flower names may repeat but on diff dates or on diff regno. similarly for perfume. but table has no duplicate entries. Query : what i want is 1) To retrieve those records for which there is both entries for flower and perfume on same date This is a simple WHERE condition check SELECT * FROM flowerdata WHERE flower is not null and perfume is not null 2) To retriev records for perfume entries which have flower entries for the same date , same regno and same codeno. You can accomplish this with a self-join SELECT p.* FROM flowerdata p INNER JOIN flowerdata f ON f.flower is not null AND f.regno = p.regno AND f.date = p.date AND f.codeno = p.codeno WHERE p.perfume IS NOT null 3) To retrieve records which doesnt hv a flower entry for the same date as that of perfume This is also a self-join but this time we use a LEFT JOIN and not an INNER JOIN SELECT p.* FROM flowerdata p LEFT JOIN flowerdata f ON f.flower is not null AND f.date = p.date WHERE p.perfume IS NOT null AND f.date IS null AND p.flower IS null The reason this query works is that we first try to match records that meet your test condition (same date with flower entry) then look for the exceptions (the non-matching records) from the table on the LEFT side of the join. The ON clause handles the test condition while the WHERE clause identifies only those that don't match your test (the first WHERE condition makes sure that these are perfume records while the first AND-ed condition in the WHERE clause will eliminate any matches). Since you can have both flower and perfume data on the same date in some records I also had to also exclude those dual-status records from this result (the last AND-ed condition in the WHERE clause) as they would have violated your query requirements. N. Kavithashree === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: how to select last records
I think you need to add some kind of uniqueness to your table. Either a date column (dates are only accurate to the nearest second which may not be fine enough precision for your situation) or an auto_incremented integer-type column (make sure you select the correct storage size for your data). That extra bit of information (I prefer the auto_increment-ed column) will help tremendously to solve your particular issue. As the database administrator it is sometimes necessary to actually store MORE information than what the users want just so that the users can get at their information in useful and convenient ways. I believe this is one of those situations. Shawn Green Database Administrator Unimin Corporation - Spruce Pine N. Kavithashree [EMAIL PROTECTED] wrote on 12/02/2004 04:38:56 AM: hello, ususally we use : select * from table limit 50; This will display the rows which were entered first the table created (i..e, that end); if i wnat to select from the other side then what should i use ? if there is a serial no column like id or some autoincremt column or DATE then we can query on desc order or date wise . other than that if i hv just 2-3 fields in my table(name, city,phone). i want to retrieve the records from top end (oldest 10) or from the other end (latest 50) --How to select then? i also want anybody to tell if they know : is there any way to select from particular row no to othereg. row 10 to row 30 ? like this? N. Kavithashree === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indices and Constraints.
Those types of queries will be available soon as the INFORMATION_SCHEMA views are in development. Keep your eyes on the next few releases of MySQL for this feature to appear (it may already be active in the 5.0.2 release just announced but I haven't had time to check yet). Until then you will need to parse the results of SHOW CREATE TABLE just as everyone has already suggested. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Roger Baklund [EMAIL PROTECTED] wrote on 12/02/2004 07:01:23 AM: [EMAIL PROTECTED] wrote: Hi, Thank you. But I want select the constraints and indices used on the table. How can we get this information?. Please help me in this. Ian gave you the answer: From: Ian Sales [...] - show indexes from DATABASE_NAME.TABLE_NAME The syntax is: SHOW INDEX FROM tablename FROM dbname - or, show create table DATABASE_NAME.TABLE_NAME; To elaborate: You can not use the SELECT statement to get this information in MySQL, like you can in Oracle. In MySQL, you can only get this information from other statements, like SHOW INDEX FROM ... and SHOW CREATE TABLE. URL: http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html URL: http://dev.mysql.com/doc/mysql/en/SHOW_CREATE_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
Vlad Shalnev wrote: * from the manual: Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any operand is NULL, otherwise 0 is returned. * Roger Baklund: This definition (from the manual) is self-contradicting: 1 OR NULL should evaluate to 1 because any operand is non-zero, but it should also evaluate to NULL because any operand is NULL. Why self-contradicting ? It is self-contradicting because one part of the definition contradicts another part of the same definition. If you apply this rule as described ( from left to right ) you will get correct result. Definitions are usually not implemented left to right. Consider this definition: The day can be devided into two parts: at night it is dark, in the day you can breathe. Implemented from left to right, it gives the correct answer, but that does not make it a good definition, mostly because the second part does not only fit the day, it also fits the night. A good definition is clear and unambiguous. While we're at it: the term non-zero... what does it mean? As we all know, NULL != 0, and 0 == zero, consequently NULL must be non-zero. I guess non-false or simply true would be more correct, as NULL evaluates to false in a boolean expression. And it isn't work in 4.1.7 :(( I don't know why the behaviour has changed. I suggest it is because the definition is unclear, but I don't know. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is this the best/fastest solution?
Two tables (simplified, because other fields are not used in query; indexes
other than primary key removed):
CREATE TABLE `msg_content` (
`msg_id` int(14) NOT NULL auto_increment,
`subject` varchar(255) NOT NULL default '',
`content` mediumtext NOT NULL,
PRIMARY KEY (`msg_id`),
) TYPE=InnoDB COMMENT='contains actual content of messages';
CREATE TABLE `msg_addressee` (
`id` int(14) NOT NULL auto_increment,
`account_id` int(14) NOT NULL default '0',
`msg_id` int(14) NOT NULL default '0',
`status` set('deleted','replied','forwarded','admin') default NULL,
PRIMARY KEY (`id`),
) TYPE=InnoDB COMMENT='link table to link accounts to messages';
A message is inserted once in the `msg_content` table and for each
from/to/cc/.. an entry is inserted in the `msg_addressee` table.
If someone deletes the message from his/her mailbox the entry in the
`msg_addressee` table is marked 'deleted' by setting the `status` field
accordingly.
I want to do some garbage collection and find the messages for which *all*
entries in the msg_addressee table have the status field set to 'deleted'
This is what I came up with:
SELECT t1.`msg_id`
FROM `msg_content` AS t1
JOIN `msg_addressee` AS t2 ON t1.`msg_id` = t2.`msg_id` AND
FIND_IN_SET( t2.`status` , 'deleted' ) 0
LEFT JOIN `msg_addressee` t3 ON t1.`msg_id` = t3.`msg_id` AND
FIND_IN_SET( t3.`status` , 'deleted' ) =0
GROUP BY t2.`msg_id` , t3.`msg_id`
HAVING COUNT( t3.`msg_id` ) =0
First I JOIN the tables to find the records which have at least one entry in
the msg_addressee table set to 'deleted' and then I select the records which
have no entries in the msg_addressee table that are *not* set to 'deleted'.
I've tried a few other queries, but this was the only one that seems to
work...
Anybody have shorter/faster ideas (whithout changing the table structure)?
Does this query result in false positives or false negatives?
Thanks in advance. Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Results question
Stuart, Comparing anything to null, including another null, will always return false. This is why you query is returning no results when the clauses that reference columns that contain nulls are included. Regards, Eric Stuart Felenstein [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have a select, from, where, query set up. There are a number of inner joins in it as well. Now what I noticed is if there are some null fields in the records, nothing will get returned. If I remove those particular joins (where the NULLS are), the record is returned. Does this sounds like a join issue ? The tables that are joined are interpretive tables. Meaning I store a 1 for Alabama in the main table. The States table I join has the 1 and then the associate label. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Windows 2003 read-only problem
I do not have access to the server at the moment to do this, but was planning on it. I was just curious if I could point our admin people in the right direction(s). Thanks, Scott -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 12:47 PM To: Hathaway, Scott L Cc: [EMAIL PROTECTED] Subject: Re: Windows 2003 read-only problem Have you tried connecting with this user directly from the MySQL monitor? Hathaway, Scott L wrote: I have a server that is Windows 2003 server. It is running php from IIS 6. I access the latest 4.0x MySQL and am having some trouble. My database access is readonly. The user that I connect as has update privileges (all privileges, actually). We checked the file permissions to the Mysql directory, the data directory and the directory that houses the database. The ISUR and IWAM users have read/write to those folders. Does anyone know what else might be causing this problem? Thanks, Scott Hathaway -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how do you install berkeley db?
I am trying to install mysql from source but I am having problems with the berkeley db part. When I type make install I get this: Making install in bdb make[2]: Entering directory `/home/steve/mysql-4.0.22/bdb' make[2]: Nothing to be done for `install'. make[2]: Leaving directory `/home/steve/mysql-4.0.22/bdb' Making install in innobase The reason I get that is in the bdb Makefile: # May want to fix this, and MYSQL/configure, to install things install dvi check installcheck: There is no rule for make install in the bdb Makefile. So how do you install berkeley db? Steve Cohen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
You are overthinking the issue. mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 4.1.7 | +---+ 1 row in set (0.00 sec) mysql SELECT 1 OR NULL; +---+ | 1 OR NULL | +---+ | 1 | +---+ 1 row in set (0.00 sec) We do not need to know x to determine that 1 OR x is TRUE (1). That is the nature of OR - it only takes one TRUE value to result in TRUE (1). Hence, 1 or NULL must evaluate to TRUE (1). This is a case where we should correct the definition in the manual, rather than redefining how OR should behave based on the the manual's poor choice of wording. I have no doubt that the wording is a translation of the code. Something like: if either argument is 1 return 1, else if any argument is null return null, else return 0. Unfortunately, the elses are missing, leaving the progression implied rather than explicit. Therefore, Vlad has found a bug: mysql CREATE TABLE nullbug (a INT NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql SELECT MIN(a), MIN(a) IS NULL, MIN(a) IS NULL OR NULL FROM nullbug; ++++ | MIN(a) | MIN(a) IS NULL | MIN(a) IS NULL OR NULL | ++++ | NULL | 1 | NULL | ++++ 1 row in set (0.00 sec) I expect an overly aggressive optimizer. Michael Roger Baklund wrote: Vlad Shalnev wrote: * from the manual: Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any operand is NULL, otherwise 0 is returned. * Roger Baklund: This definition (from the manual) is self-contradicting: 1 OR NULL should evaluate to 1 because any operand is non-zero, but it should also evaluate to NULL because any operand is NULL. Why self-contradicting ? It is self-contradicting because one part of the definition contradicts another part of the same definition. If you apply this rule as described ( from left to right ) you will get correct result. Definitions are usually not implemented left to right. Consider this definition: The day can be devided into two parts: at night it is dark, in the day you can breathe. Implemented from left to right, it gives the correct answer, but that does not make it a good definition, mostly because the second part does not only fit the day, it also fits the night. A good definition is clear and unambiguous. While we're at it: the term non-zero... what does it mean? As we all know, NULL != 0, and 0 == zero, consequently NULL must be non-zero. I guess non-false or simply true would be more correct, as NULL evaluates to false in a boolean expression. And it isn't work in 4.1.7 :(( I don't know why the behaviour has changed. I suggest it is because the definition is unclear, but I don't know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade from mysql 3.23 to 4.1
On Tuesday 30 November 2004 04:44 pm, Greg Macek wrote: OK, sounds like what I should do is the following: * Upgrade current mysql install (3.23.49) to latest stable 4.0 series (4.0.22 according to the website) * Test out all applications and make sure everything is working as expected. * Test new features in 4.0.x vs. 3.23.x 4.0.22 has a new=1 option. I would turn it on right before going to 4.1.x. Its suppose to kinda mimic some of the trouble spots you may incounter going to 4.1 * After sufficient time to test, upgrade to latest stable 4.1.x version. Sounds like a good plan. Jeff pgpNKQ0K5Llcr.pgp Description: PGP signature
Re: Comparing bug in 4.1.7
Michael Stassen wrote: You are overthinking the issue. Probably. :) mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 4.1.7 | +---+ 1 row in set (0.00 sec) mysql SELECT 1 OR NULL; +---+ | 1 OR NULL | +---+ | 1 | +---+ 1 row in set (0.00 sec) We do not need to know x to determine that 1 OR x is TRUE (1). That is the nature of OR - it only takes one TRUE value to result in TRUE (1). I agree. Hence, 1 or NULL must evaluate to TRUE (1). This is a case where we should correct the definition in the manual, rather than redefining how OR should behave based on the the manual's poor choice of wording. I agree. Therefore, Vlad has found a bug: I agree again. :) This is (as I see it) a documentation issue, I was not trying to say that Vlad was wrong. While we're at it: the term non-zero... what does it mean? As we all know, NULL != 0, and 0 == zero, consequently NULL must be non-zero. I would like to have a comment on this as well... or rather: I wonder if anyone agrees with me that non-zero is a bad term to use in this context (MySQL documentation, description of logical operator OR), or if I am just overthinking again... :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Comparing bug in 4.1.7
-Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] This definition (from the manual) is self-contradicting: 1 OR NULL should evaluate to 1 because any operand is non-zero, but it should also evaluate to NULL because any operand is NULL. URL: http://dev.mysql.com/doc/mysql/en/Logical_Operators.html With an unclear definition, it is perhaps not so strange that behaviour will change with different versions of MySQL...? I suspect the reason 1 OR NULL returns 1 in some versions is that the OR operator short circuits, like in C. A short circuit operator is one that stops executing code as soon as the result is clear. In the case of OR, that means if you find one operand that's true, you don't have to check the other operands. (OR also short-circuits in Perl, which is why statements like 'do_something() or die(It didn't work!)' work as expected. If do_something() returns true, the die() is never executed.) What does the SQL standard say about this, if anything? I thought the result of any operation on NULL was supposed to return NULL, but I could be wrong. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
Roger Baklund wrote: snip This is (as I see it) a documentation issue, I was not trying to say that Vlad was wrong. Right, that's why I'm copying the docs list. While we're at it: the term non-zero... what does it mean? As we all know, NULL != 0, and 0 == zero, consequently NULL must be non-zero. I would like to have a comment on this as well... or rather: I wonder if anyone agrees with me that non-zero is a bad term to use in this context (MySQL documentation, description of logical operator OR), or if I am just overthinking again... :) Well, if we imagined a NONZERO function, I think we would agree that NONZERO(1) is 1, NONZERO(0) is 0, and NONZERO(NULL) is NULL (for the usual reasons). Of course, that doesn't mean that non-zero alone is the best way to describe this in the docs. Perhaps something like this would be better: OR || Logical OR. Returns 1 if either operand evaluates to a non-zero integer, else it returns NULL if either operand is NULL, otherwise 0 is returned. or perhaps OR || Logical OR. If either operand evaluates to a non-zero integer, returns 1; else if either operand is NULL, returns NULL; otherwise 0 is returned. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing datas
On Thursday 02 of December 2004 01:15, Michel RENON wrote: Hi, Here is my problem : I want to copy datas from an existing website to my local website. The website is a collection of images with thumbnails. With phpMyAdmin, i made an export of the image table and i have a 16 MB file 'image.sql' containing sql orders to create table and insert values. With phpMyAdmin of local website, I tried to execute that sql file but got errors related to memory allowed to php (2 MB max). I tried to modify 'memory_limit', 'upload_max_filesize' in /etc/php.ini but still got errors. So I tried with mysql tools. with source /path/image.sql; the table is created but i got the following error : ERROR 1153: Got a packet bigger than 'max_allowed_packet' I've seen threads in this list about same error, but I feel i'm in a wrong way... What would be a good way of doing that export/import of datas ? What to do if I want to upload bigger datas on the existing website ? (I only have a phpMyAdmin access) Sorry if the answer is obvious, but I'm new to MySQL, phpMyAdmin. My local config is PowerMac G4, Mac OS X 10.3.5, MySQL 4.0.21 Thanks in advance Michel RENON Hello! I'm not familiar with mysql command-line tools. To fix 'max_allowed_packet' error edit 'my.cnf' file and set 'max_allowed_packet' to something bigger, and restart mysqld. Then using 'source /path/image.sql;' should work. Also I have few tips about phpMyAdmin. You should increase 'post_max_size' in your php.ini. A short quote from PHP manual: post_max_size integer Sets max size of post data allowed. This setting also affects file upload. To upload large files, this value must be larger than upload_max_filesize. If memory limit is enabled by your configure script, memory_limit also affects file uploading. Generally speaking, memory_limit should be larger than post_max_size. If you use Apache the edit your httpd.conf and increase 'Timeout' value. If you use other web server, it should also have 'Timeout' directive in its configuration file. And of course remember to restart your web server ;). Then edit 'config.inc.php' in your phpMyAdmin directory, adjust $cfg['ExecTimeLimit'] and try to upload your file. Myself I tried to upload 16MB file. The upload was successful but my web browser frozen when displaying result, as it is over 16 000 000 characters to display ;) and I have to kill its process. If your web browser process starts to use almost all your CPU, queries should be already executed. -- Piotr Bogdan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
I upgraded from 5.01 to 5.02 and now I am getting the error localhost is not allowed to connect to this MySQL server. What should I do, root cannot connect a well. I'm currently using win32 and have old-passwords in my.cnf Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade 5.01 to 5.02
Sorry, forgot to change the subject in previous message. I upgraded from 5.01 to 5.02 and now I am getting the error localhost is not allowed to connect to this MySQL server. What should I do, root cannot connect a well. I'm currently using win32 and have old-passwords in my.cnf Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade 5.01 to 5.02
Hmmm, found the problem. In previous versions the installer did not delete and write over existing tables in the mysql database. Good thing I had the database backed up. Be careful all... Fredrick - Original Message - From: Fredrick Bartlett [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 02, 2004 8:56 AM Subject: Upgrade 5.01 to 5.02 Sorry, forgot to change the subject in previous message. I upgraded from 5.01 to 5.02 and now I am getting the error localhost is not allowed to connect to this MySQL server. What should I do, root cannot connect a well. I'm currently using win32 and have old-passwords in my.cnf Thanks -- 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]
Re: Comparing bug in 4.1.7
At 11:42 -0500 12/2/04, Michael Stassen wrote: Roger Baklund wrote: snip This is (as I see it) a documentation issue, I was not trying to say that Vlad was wrong. Right, that's why I'm copying the docs list. I updated the description to account for the cases when there are 1 or 2 NULL operands. It should show up on the Web site soon. Basically, the idea is that if there is a NULL operand, OR returns true if the result can be determined to be true, and NULL otherwise. 1 OR NULL = 1 0 OR NULL = NULL NULL OR NULL = NULL While we're at it: the term non-zero... what does it mean? As we all know, NULL != 0, and 0 == zero, consequently NULL must be non-zero. I would like to have a comment on this as well... or rather: I wonder if anyone agrees with me that non-zero is a bad term to use in this context (MySQL documentation, description of logical operator OR), or if I am just overthinking again... :) Well, if we imagined a NONZERO function, I think we would agree that NONZERO(1) is 1, NONZERO(0) is 0, and NONZERO(NULL) is NULL (for the usual reasons). Of course, that doesn't mean that non-zero alone is the best way to describe this in the docs. Perhaps something like this would be better: OR || Logical OR. Returns 1 if either operand evaluates to a non-zero integer, else it returns NULL if either operand is NULL, otherwise 0 is returned. or perhaps OR || Logical OR. If either operand evaluates to a non-zero integer, returns 1; else if either operand is NULL, returns NULL; otherwise 0 is returned. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-5.0.2 is released
Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 5.0.2 is a snapshot of the 5.0 development branch of MySQL. Unfortunately, this snapshot still contains some critical bugs, like http://bugs.mysql.com/bug.php?id=5401, which causes MySQL to return wrong results if a SELECT uses two indexes at the same time. InnoDB in MySQL-5.0.2 is almost the same as in the upcoming MySQL-4.1.8 release. Marko's new compact InnoDB table format did not make it to 5.0.2. The new compact table format will be pushed to the 5.0 BitKeeper tree today, and it will be included in 5.0.3. The biggest downside of InnoDB when compared to MyISAM has been that InnoDB tables take a lot more space than MyISAM tables. The new compact InnoDB table format will make InnoDB tables substantially smaller. You can look at the InnoDB roadmap at http://www.innodb.com/todo.php InnoDB functionality changed from 4.1: * If you specify the option innodb_locks_unsafe_for_binlog in my.cnf, InnoDB no longer in an UPDATE or a DELETE locks rows that do not get updated or deleted. This greatly reduces the probability of deadlocks. If you do not specify the option, InnoDB locks all rows that the UPDATE or DELETE scans, to ensure serializability. Upgrading to 5.0.2: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases. * If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later. * If you have used accent characters (ASCII codes = 128) in database names, table names, constraint names, or column names in versions 4.1, you cannot upgrade to = 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisampack
Is it possible to append rows to a table packed by myisampack? Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible bug with wait_timeout
Hi all, In version 4.0.18 when setting the wait_timeout variable to 10 in my.cnf, it seems to work when looking at 'mysqladmin variables' as it is indeed showing up as 10. However, when in the mysql client and I do a 'show variables' it is showing up with the default value of 28800. I'm certain that I've connected to the same server and was using all the tools from /usr/bin/mysql and specifying paths like this bin/safe_mysqld, bin/mysqladmin, bin/mysql etc... Anyone seen this before or am I going crazy? Cheers, Andrew query, sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very Slow preformance of mysql 4.1.7 innodb
We are considering migration to mysql 4.1. innodb. So I've made some tests comparing innodb and myisam performance. Innodb was very very slow, so I suspect something is wrong. It can't be so bad! Mysql 4.1.7 on Linux (binary from www.mysql.com).System - Duron 800MHz, 500MB RAM. Myisam database is 6GB. After converting to innodb with ALTER TABLE it is already 12GB. Setup with multiple tablespaces. Here is the config: [mysqld] port= 3307 socket = /usr/local/mysql/mysql.sock pid-file= /usr/local/mysql/mysql.pid datadir = /data skip-bdb skip-locking key_buffer_size = 64M max_allowed_packet = 2M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M net_buffer_length = 2M thread_stack = 1M max_connections = 100 query_cache_type = 1 maximum-query_cache_size = 24M thread_cache = 8 thread_concurrency = 2 innodb_file_per_table innodb_data_home_dir = /ibdata/ innodb_data_file_path = ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir = /iblog/ innodb_log_arch_dir = /iblog/ innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 16M innodb_log_buffer_size = 4M Tests: First test was a sql dump file from the production server which was played to the test mysql server like this: mysql -f -u root -ppass database sqldump logfile Myisam finished for about 10 hours and Innodb for 45 hours. Second test was a script wich simulates multiple user load (20 users at a time , 1 all). It took Myisam half an hour to complete the test and more than 30 hours to Innodb ( infact it is still running). No errors in the mysql log. Ofcource there was no optimization for Innodb but though It can't be so bad. What's wrong? -- Hristo Chernev - Âñè÷êî å ïî-áúðçî è ñèãóðíî ñ ÁÒÊ ADSL! www.telecom.bg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade from mysql 3.23 to 4.1
Yes.I know that 3 - 4.0 - 4.1 is the recommended way but It will cost a lot of downtime. So I decided to do 3-4.1 way. The part that I am not quite sure is the converting the database. Why it is needed? If I just copy the old db and run the 4.1? I 've not found clear explination in the docs. I've tested two ways of conversion (8a and 8b in my plan) - with mysqldump and with copy - sql create. I didn't see any difference of created databases and the copy old db and sql select from create was two times faster. But is it equal? -- Hristo Chernev - Êàê äà îòñëàáíà ñ 1, 2, 3, 4, 5, 10, 15, 20 êã.? http://www.otslabvane.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing datas
If you have direct MySQL tool access to your existing data you could re-export your data using the mysqldump tool and set the --max_allowed_packet option to the same value as the max_allowed_packet size for your new server. That way, if you source the resulting dump file into your new server, you will not get the 1153 error because mysqldump would have broken the INSERT statement into chunks small enough for the new server to handle. I have no idea how to do the same adjustment to an export through phpMyAdmin, sorry. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michel RENON [EMAIL PROTECTED] wrote on 12/01/2004 07:15:24 PM: Hi, Here is my problem : I want to copy datas from an existing website to my local website. The website is a collection of images with thumbnails. With phpMyAdmin, i made an export of the image table and i have a 16 MB file 'image.sql' containing sql orders to create table and insert values. With phpMyAdmin of local website, I tried to execute that sql file but got errors related to memory allowed to php (2 MB max). I tried to modify 'memory_limit', 'upload_max_filesize' in /etc/php.ini but still got errors. So I tried with mysql tools. with source /path/image.sql; the table is created but i got the following error : ERROR 1153: Got a packet bigger than 'max_allowed_packet' I've seen threads in this list about same error, but I feel i'm in a wrong way... What would be a good way of doing that export/import of datas ? What to do if I want to upload bigger datas on the existing website ? (I only have a phpMyAdmin access) Sorry if the answer is obvious, but I'm new to MySQL, phpMyAdmin. My local config is PowerMac G4, Mac OS X 10.3.5, MySQL 4.0.21 Thanks in advance Michel RENON -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.02 Shuts down on win32
When I try to connect from a remote client 5.02 shuts down with no errors reported in .err file. Fredrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking question
Howdy all, quick question about how INNODB handles locks. If autocommit is off and I perform a select statement without then issuing a commit, will INNODB remove any read locks that it issued? I would assume that the locks would be removed when the statement finished. Just want to verify that this is/isn't the case. Cheers, Tripp __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update optimization...
I am trying to execute this query and it is failing with Table is full error (I know I can make temp tables big). update t1, t2 set t1.XXX=1 where t1.YYY=t2. and t2. like '%X%'; My t1 has 10,00,000+ records and t2 has about 70,000 records. I would like to know how can I optimize this query? What are the parameters for this optimization? Can someone give me links where I can read up about such optimizations for update query. TIA, - Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very Slow preformance of mysql 4.1.7 innodb
Hristo Chernev wrote: We are considering migration to mysql 4.1. innodb. So I've made some tests comparing innodb and myisam performance. Innodb was very very slow, so I suspect something is wrong. It can't be so bad! Mysql 4.1.7 on Linux (binary from www.mysql.com).System - Duron 800MHz, 500MB RAM. Myisam database is 6GB. After converting to innodb with ALTER TABLE it is already 12GB. Setup with multiple tablespaces. Here is the config: [mysqld] port= 3307 socket = /usr/local/mysql/mysql.sock pid-file= /usr/local/mysql/mysql.pid datadir = /data skip-bdb skip-locking key_buffer_size = 64M max_allowed_packet = 2M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M net_buffer_length = 2M thread_stack = 1M max_connections = 100 query_cache_type = 1 maximum-query_cache_size = 24M thread_cache = 8 thread_concurrency = 2 innodb_file_per_table innodb_data_home_dir = /ibdata/ innodb_data_file_path = ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir = /iblog/ innodb_log_arch_dir = /iblog/ innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 16M innodb_log_buffer_size = 4M Tests: First test was a sql dump file from the production server which was played to the test mysql server like this: mysql -f -u root -ppass database sqldump logfile Myisam finished for about 10 hours and Innodb for 45 hours. Second test was a script wich simulates multiple user load (20 users at a time , 1 all). It took Myisam half an hour to complete the test and more than 30 hours to Innodb ( infact it is still running). No errors in the mysql log. Ofcource there was no optimization for Innodb but though It can't be so bad. What's wrong? Hristo: Some queries are really bad on InnoDB compared to MyISAM, the most notorious is probably SELECT COUNT(*) A full table scan is also much faster on MyISAM than on InnoDB. MyISAM generally is superior in large record reads. InnoDB is superior in a heavy read-write mix when both reads and writes are small. With InnoDB it becomes particularly important that you use good keys. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with backup
I am using the MySql Administrator tool to schedule weekly backups on my databases. I have defined the databases I want backed up and how often, plus where to store the data. I ran a sample, but it appears that just the structure is being backed up, not the data in the tables as well. How do you define this? What am I missing? Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Privileges - table privileges question
Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. Any better solutions than this one? Thanks! Mihail Manolov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best learning path to DBA?
I am an web designer / php programmer / unofficial network administrator with a well-rounded technical background - but, as they say, a jack of all trades (expert in nothing). If I were to pursue a DBA path, what would be a good way of going about it? School, books, etc... - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges - table privileges question
Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. That is correct. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very Slow preformance of mysql 4.1.7 innodb
Hristo, if you are doing INSERTs, UPDATEs, or DELETEs, try setting innodb_flush_log_at_trx_commit=2 But read the caveats in the manual. You can also set innodb_buffer_pool_size bigger. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Hristo Chernev [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, December 02, 2004 7:40 PM Subject: Very Slow preformance of mysql 4.1.7 innodb We are considering migration to mysql 4.1. innodb. So I've made some tests comparing innodb and myisam performance. Innodb was very very slow, so I suspect something is wrong. It can't be so bad! Mysql 4.1.7 on Linux (binary from www.mysql.com).System - Duron 800MHz, 500M= B RAM. Myisam database is 6GB. After converting to innodb with ALTER TABLE it = is already 12GB. Setup with multiple tablespaces. Here is the config: [mysqld] port=3D 3307 socket =3D /usr/local/mysql/mysql.sock pid-file=3D /usr/local/mysql/mysql.pid datadir =3D /data skip-bdb skip-locking key_buffer_size =3D 64M max_allowed_packet =3D 2M table_cache =3D 256 sort_buffer_size =3D 1M read_buffer_size =3D 1M read_rnd_buffer_size =3D 4M myisam_sort_buffer_size =3D 16M net_buffer_length =3D 2M thread_stack =3D 1M max_connections =3D 100 query_cache_type =3D 1 maximum-query_cache_size =3D 24M thread_cache =3D 8 thread_concurrency =3D 2 innodb_file_per_table innodb_data_home_dir =3D /ibdata/ innodb_data_file_path =3D ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir =3D /iblog/ innodb_log_arch_dir =3D /iblog/ innodb_buffer_pool_size =3D 64M innodb_additional_mem_pool_size =3D 8M innodb_log_file_size =3D 16M innodb_log_buffer_size =3D 4M Tests: First test was a sql dump file from the production server which was played t= o the test mysql server like this: mysql -f -u root -ppass database sqldump logfile Myisam finished for about 10 hours and Innodb for 45 hours. Second test was a script wich simulates multiple user load (20 users at a ti= me , 1 all). It took Myisam half an hour to complete the test and more than 3= 0 hours to Innodb ( infact it is still running). No errors in the mysql log. Ofcource there was no optimization for Innodb but though It can't be so bad. What's wrong? -- Hristo Chernev - =C2=F1=E8=F7=EA=EE =E5 =EF=EE-=E1=FA=F0=E7=EE =E8 =F1=E8=E3=F3=F0=ED=EE =F1 =C1=D2=CA ADSL! www.telecom.bg -- 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]
More on 5.02 server crash
MySQL Query Browser 1.1.2 causes 5.02 mysqld-nt.exe server crash as well remote and local. Fredrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with mysql_num_rows() on HP and MySql 3.23.x
Has anyone ran into problems where mysql_num_rows() return 0 when the result set clearly contains a certain number of rows? My C code looks something like this: mysql_query(conn, show databases); result = mysql_store_result (conn); numRows = mysql_num_rows (result); On an HP 11.11 machine running MySql 3.23.39, mysql_num_rows() always returns 0. After mysql_store_result() gets called, MYSQL_RES.num_rows is set to 0. It works fine on a sparc and alpha. I can do mysql_fetch_row() just fine, and mysql_num_fields() works, but mysql_num_rows() would still return 0. Anyone seen this same problem before? Thanks, Hebron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-5.0.2 is released
MySQL to return wrong results if a SELECT uses two indexes at the same time Does mysql 5.0.x have the ability to use more than one index per table on a select ? We had to rewrite a simple select id from table_a where last_name like 'smith%' and first_name like 'john%' to select id from table_a left join ( select id from table_a where last_name like 'smith%' group by id ) as t2 on t2.id = table_a.id where table_a.first_name like 'john%' limit 201; We had tried an index on last_name, an index on first_name, and a combo index of (last_name, first_name). We cut the run time from 1min 57sec to 3seconds. walt Heikki Tuuri wrote: Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 5.0.2 is a snapshot of the 5.0 development branch of MySQL. Unfortunately, this snapshot still contains some critical bugs, like http://bugs.mysql.com/bug.php?id=5401, which causes MySQL to return wrong results if a SELECT uses two indexes at the same time. InnoDB in MySQL-5.0.2 is almost the same as in the upcoming MySQL-4.1.8 release. Marko's new compact InnoDB table format did not make it to 5.0.2. The new compact table format will be pushed to the 5.0 BitKeeper tree today, and it will be included in 5.0.3. The biggest downside of InnoDB when compared to MyISAM has been that InnoDB tables take a lot more space than MyISAM tables. The new compact InnoDB table format will make InnoDB tables substantially smaller. You can look at the InnoDB roadmap at http://www.innodb.com/todo.php InnoDB functionality changed from 4.1: * If you specify the option innodb_locks_unsafe_for_binlog in my.cnf, InnoDB no longer in an UPDATE or a DELETE locks rows that do not get updated or deleted. This greatly reduces the probability of deadlocks. If you do not specify the option, InnoDB locks all rows that the UPDATE or DELETE scans, to ensure serializability. Upgrading to 5.0.2: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases. * If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later. * If you have used accent characters (ASCII codes = 128) in database names, table names, constraint names, or column names in versions 4.1, you cannot upgrade to = 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges - table privileges question
I am afraid you have read the docs correctly. Privileges exist at 4 levels: Global, Database, Table, and Column. So, for someone to only see part of a database, you have to GRANT permissions to the specific tables that user gets rights to work with. No other way around it. However, you may be able to quickly write a batch SQL script using a spread sheet to help speed up the process. Run the SHOW TABLES command then copy hose results into a spreadsheet. Add a formula to take a table name and embed it inside the correct GRANT phraseology for your situation. Now you should have a single cell that looks like a valid GRANT statement. Copy the formula so that it processes every table name in the list (select the formula cell, copy it to the clipboard, highlight the rows around your formula that are next to the rest of the table names then hit paste). You end up mass creating a list of GRANT statements to run for the user. Since each row has a different table name, each GRANT statement ends up affecting a different table. Highlight all of the formula results, copy them to the clipboard then past them into the mysql client and you should be done in no time flat. Don't forget to end each GRANT statement with a semicolon (;) This works very well for me using mysql in a Windoze command shell and M$ Excel. I hope it works with what you have, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mihail Manolov [EMAIL PROTECTED] wrote on 12/02/2004 02:30:51 PM: Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. Any better solutions than this one? Thanks! Mihail Manolov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binlog question in replication setup
Hello everyone, I have a question on how MySQL database write to binlogs in replication environment. My table type is MyISAM. MySQL version is 4.0.21. I have a replication farm. Let's suppose I am running a ALTER TABLE statement on central master and had a syntax error in table name. Replication abort everywhere saying that table do not exists. I need to know that how MySQL write to binlog file. Does it write before or after a successful execution or commit the statement. Why a syntax error statement need to hand over to Slave IO thread to relay log. Is there any control like any parameter in option file or anything else in order to control to write only those statements in binary log which ran successful on master. Appreciate it.
Load data question in cross database replication
Hello Everyone, I have a question on using LOAD DATA command in cross database replication setup. MySQL version is 4.0.21 I have replication farm where few slaves have been set up as cross database replication slave by using (replicate-rewrite-db). When Load data command get executed on central master, it get replicated fine in those slave which are not cross database but it abort with error saying that database do not exist in those slave, which are configured with replicate-rewrite-db. This is my understanding that it has hard coded database name in .ini file, which it generate for load data command. Is there any way to make it happen that load data should get executed irrespective of replicate-rewrite-db configuration. Any parameter or any work around? Regards,
RE: Binlog question in replication setup
DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Sanjeev Sagar [mailto:[EMAIL PROTECTED] Sent: Thursday, December 02, 2004 4:04 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: Sanjeev Sagar Subject: Binlog question in replication setup Hello everyone, I need to know that how MySQL write to binlog file. It gets written in order of compeltion, so if the table exists on the master and the alter takes 50 seconds to run the commit to the binlog happens on the 50th second. If the table doesn't exist on the slave a slave error get produced and the SQL thread stops while the IO thread keeps running downloading the transaction in a file queue waiting for commital. Does it write before or after a successful execution or commit the statement. Why a syntax error statement need to hand over to Slave IO thread to relay log. If a syntax error happens on the master it should not show up in the bin log. Is there any control like any parameter in option file or anything else in order to control to write only those statements in binary log which ran successful on master. That's it's default behavior. Appreciate it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup problems
I am using the MySql Administrator tool to schedule weekly backups on my databases. I have defined the databases I want backed up and how often, plus where to store the data. I ran a sample, but it appears that just the structure is being backed up, not the data in the tables as well. How do you define this? What am I missing? Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question...
I'm very new to mysql and replication. I've got a case where I have 2 servers, each have database A and database B. I want server 1 to be master for database A and slave for database B and I would like server 2 to be slave for database A and master for database B. From what I've read, if a server is a master, it's a master and likewise for a slave and they can't share the role depending on the requirements of the user. Can someone enlighten me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More efficient way?
I have an application which keeps a table of daily event counters related to other records in a databse. Since the trackingrecords are kept on a daily basis new records are created each day for items being referenced. In pseudo-code: // Check for the existance of daily tracking record SELECT dailycounterid FROM dailycounters WHERE trackingdate = today AND thingid = somerecordid if query.recordwasfound // If it exists, increment counter UPDATE dailycounters SET count = count + 1 WHERE dailycounterid = query.dailycounterid else // Otherwise add new record with count of 1 INSERT INTO dailycounters (trackingdate, thingid, count) VALUES (today, somerecordid, 1) endif Is there any way to do this with a single MySQL query instead of a check followed by either an insert or an update? The MySQL version is 3.2x using MyISAM tables. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column type question ?
Hi ALL, I have an application that reads files converts them and then inserts them into a database. It has 2 columns that I need help with. First is the OFFSET column - this stores the datafile offset .. In C it is a UNSIGNED LONG and looks like : 0x2528 the second colun is a CRC for the file chunk and looks like 0x2e04b273 when I insert them into the database it is currently stored as binary(4) but this is no good as a select does not return the desired info - it actually returns nothing... I have no clue on how to get this to work - so all suggestions are welcome Thanks Tonino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges - table privileges question
Is this a typical situation? If those 1 or 2 tables have higher security requirements than the rest, so some users should have access to all the tables except them, another option would be to move them to a separate db. Then you could grant the average user access to the db with the rest of the tables, but only privileged users get access to both dbs. This would be easier to maintain, at the cost of slightly complicating queries that use tables in both dbs. That is, you'd have to qualify those 1 or 2 tables with db names every time you want to join them to another table. On the other hand, avoiding table-level privileges can have performance benefits http://dev.mysql.com/doc/mysql/en/Query_Speed.html. Michael [EMAIL PROTECTED] wrote: I am afraid you have read the docs correctly. Privileges exist at 4 levels: Global, Database, Table, and Column. So, for someone to only see part of a database, you have to GRANT permissions to the specific tables that user gets rights to work with. No other way around it. However, you may be able to quickly write a batch SQL script using a spread sheet to help speed up the process. Run the SHOW TABLES command then copy hose results into a spreadsheet. Add a formula to take a table name and embed it inside the correct GRANT phraseology for your situation. Now you should have a single cell that looks like a valid GRANT statement. Copy the formula so that it processes every table name in the list (select the formula cell, copy it to the clipboard, highlight the rows around your formula that are next to the rest of the table names then hit paste). You end up mass creating a list of GRANT statements to run for the user. Since each row has a different table name, each GRANT statement ends up affecting a different table. Highlight all of the formula results, copy them to the clipboard then past them into the mysql client and you should be done in no time flat. Don't forget to end each GRANT statement with a semicolon (;) This works very well for me using mysql in a Windoze command shell and M$ Excel. I hope it works with what you have, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mihail Manolov [EMAIL PROTECTED] wrote on 12/02/2004 02:30:51 PM: Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. Any better solutions than this one? Thanks! Mihail Manolov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
Sergei Golubchik wrote: Hi! On Dec 02, Vlad Shalnev wrote: Looks like a bug. Could you submit a bugreport at http://bugs.mysql.com ? I've submitted a bugreport. Downgrade to 3.23 and wait for this problem solving. Thanks for all It happens after upgrade from 3.23.46. mysql create table a ( a int not null ); Query OK, 0 rows affected (0.00 sec) mysql select min( a ) is null or null from a; +--+ | min( a ) is null or null | +--+ | NULL | - Why ??? +--+ 1 row in set (0.00 sec) It is very important for me to solve this problem. Thanks for any help Regards, Sergei -- Vlad A. Shalnev E-mail: [EMAIL PROTECTED] Gravity can't be blamed for someone falling in love ( Albert Einstein ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
Hi Vlad ! Why not using select (select min( a ) is null from a) or null; as a workaround ? Regards, Jocelyn - Original Message - From: Vlad Shalnev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 03, 2004 7:01 AM Subject: Re: Comparing bug in 4.1.7 Sergei Golubchik wrote: Hi! On Dec 02, Vlad Shalnev wrote: Looks like a bug. Could you submit a bugreport at http://bugs.mysql.com ? I've submitted a bugreport. Downgrade to 3.23 and wait for this problem solving. Thanks for all It happens after upgrade from 3.23.46. mysql create table a ( a int not null ); Query OK, 0 rows affected (0.00 sec) mysql select min( a ) is null or null from a; +--+ | min( a ) is null or null | +--+ | NULL | - Why ??? +--+ 1 row in set (0.00 sec) It is very important for me to solve this problem. Thanks for any help Regards, Sergei -- -- -- Vlad A. Shalnev E-mail: [EMAIL PROTECTED] Gravity can't be blamed for someone falling in love ( Albert Einstein ) -- 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]
