About charset
Hello list, I have a table whose 'default charset=utf8'. I insert a record into it with non-utf8 charset,like GBK.Then I select this record,it print GBK characters correctly. Then I insert a utf8 record into it,and read this record in scripts and decode it with utf8_decode,the result is also correct. So I think,for table (or column) with utf8 charset,I could insert it with any characters like GBK,gb2312,iso8859-1.Mysql doesn't convert them to utf8 characters automaticly. Am I right? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld fails to start with error unknown option '--enable-named-pipe'
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: mysql@lists.mysql.com Subject: MySQL daemon fails to start Description: mysqld fails with error 070827 10:52:04 [ERROR] /usr/sbin/mysqld: unknown option '--enable-named-pipe' This error happened right at install: [EMAIL PROTECTED] ~]# ls /tmp/MySQL-server-community-5.0.45-0.rhel5.i386.rpm /tmp/MySQL-server-community-5.0.45-0.rhel5.i386.rpm [EMAIL PROTECTED] ~]# rpm -i /tmp/MySQL-server-community-5.0.45-0.rhel5.i386.rpm 070827 10:52:04 [ERROR] /usr/sbin/mysqld: unknown option '--enable-named-pipe' Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. You can try to start the mysqld daemon with: /usr/sbin/mysqld --skip-grant and use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/bin/mysqlbug script! Starting MySQL ERROR! Couldn't find MySQL manager or server Giving mysqld 2 seconds to start How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: [EMAIL PROTECTED] Originator:root Organization: Credics Technologies MySQL support: [none] Synopsis: mysqld fails to start Severity: critical Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-5.0.45-community (MySQL Community Edition (GPL)) C compiler:gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) C++ compiler: gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) Environment: machine, os, target, libraries (multiple lines) System: Linux localhost.localdomain 2.6.22.2-42.fc6 #1 SMP Wed Aug 15 12:34:26 EDT 2007 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i386-redhat-linux Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-libgcj-multifile --enable-languages=c,c++,objc,obj-c++,java,fortran,ada --enable-java-awt=gtk --disable-dssi --enable-plugin --with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre --with-cpu=generic --host=i386-redhat-linux Thread model: posix gcc version 4.1.2 20070626 (Red Hat 4.1.2-13) Compilation info: CC='gcc' CFLAGS='-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables' CXX='gcc' CXXFLAGS='-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 11 Aug 7 11:09 /lib/libc.so.6 - libc-2.5.so -rwxr-xr-x 1 root root 1589908 Jul 5 00:48 /lib/libc-2.5.so -rw-r--r-- 1 root root 2789028 Jul 5 00:29 /usr/lib/libc.a -rw-r--r-- 1 root root 238 Jul 5 00:10 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-server-suffix=-community' '--without-embedded-server' '--with-ndbcluster' '--with-innodb' '--with-csv-storage-engine' '--with-archive-storage-engine' '--with-blackhole-storage-engine' '--with-example-storage-engine' '--with-federated-storage-engine' '--without-bench' '--with-zlib-dir=bundled' '--with-big-tables' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-pic' '--prefix=/' '--with-extra-charsets=all' '--with-yassl' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=MySQL Community Edition (GPL)' '--with-readline' 'CC=gcc' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables' 'CXXFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
Open Position in the MySQL Developer Tools Team - MySQL Workbench
Hi everybody, I know this email is slightly off-topic for this list and I'm sorry about that but I wanted as many people as possible to be aware of this opportunity. If you are not looking for a job, please skip this email. We have another open position in the MySQL Developer Tools Team that we want to fill as soon as possible. If you ever wanted to work in an Open Source company (and I consider MySQL AB to be *the best* OSS company to work for) this is your chance. You would work with me and the team on the upcoming MySQL Workbench project. This position does not require you to be an absolute guru developer - but you should have solid skills in C++, especially templates - and bring a lot of dedication and motivation to the team. Please see this job posting for details. http://tbe.taleo.net/NA3/ats/careers/requisition.jsp?org=MYSQLcws=1rid=45 Prior work on Open Source projects is a plus, but not a requirement. Since almost the whole team is located in the European timezone we would prefer someone located in or near that timezone, although the position is open for worldwide applications. Please send your CV either to me directly or follow the procedure on the job posting site. Looking forward to your applications, Mike -- Michael Zinner, Team Lead, Developer Tools MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query sloooow
Hi Jim - I'm using MySQL on Fedora 6 as well, with no performance problems. Did not need to do anything to speed it up on Fedora. It's difficult to answer the question why one is faster than the other, as there are any number of potential differences. Some more specifics about your setup (hardware, software) and the queries you're running would be helpful. The output of EXPLAIN from both machines may be very telling. In general, some things to consider are: - do you have sufficient RAM in the Fedora machine for what you're running? If you're paging memory that can slow things down a lot - are you running the same version of MySQL on the two machines? Older versions can perform particularly poorly with subqueries, for example - do you have the MySQL query cache enabled on Windows and not on Fedora, or cache settings that are very different? Dan On 8/26/07, Jim Douglas [EMAIL PROTECTED] wrote: I installed MySQL on Fedora 6 and simple queries are very slow. I have ten databases with very few hundred records and there are only a few hundred records in the table I am querying. I only have this issue on Fedora ONLY. The same query on on my Windows machine is as expected. What can I do to speed things up on Fedora? Jim _ See what you're getting into…before you go there http://newlivehotmail.com/?ocid=TXT_TAGHM_migration_HM_viral_preview_0507 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage - MyISAM vs InnoDB
Hi! Comments inline. Edoardo Serra wrote: SELECT sum(usercost) FROM cdr WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59' If I run it on the MyISAM table, MySQL choose the right index (the one on the calldate column) and the query is fast enough If I run it on the InnoDB table, MySQL uses no index even if an EXPLAIN query tells me that 'calldate' is between the available indexes Here are my EXPLAIN results mysql EXPLAIN SELECT sum(usercost) FROM cdr_innodb WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+--+-+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+-+--+-+--+-+-+ | 1 | SIMPLE | cdr | ALL | calldate,date-context-cause | NULL | NULL| NULL | 5016758 | Using where | ++-+---+--+-+--+-+--+-+-+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT sum(usercost) FROM cdr_myisam WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+---+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+--+-+--++-+ | 1 | SIMPLE | cdr | range | calldate,date-context-cause | calldate | 8 | NULL | 772050 | Using where | ++-+---+---+-+--+-+--++-+ 1 row in set (0.11 sec) Another strange thing is that the EXPLAIN on InnoDB says the table has 5016758 rows but a SELECT count(*) returns 4999347 rows (which is the correct number) The rows returned in EXPLAIN SELECT (and SHOW TABLE STATUS) for InnoDB tables is an estimate. For MyISAM, it is the actual number of rows in the table. This is because InnoDB has to track a version for each row in the table (for transactional isolation), and MyISAM does not, which makes it much easier to just have a simple row count for the table. This estimate of rows returned is what is used by the optimizer to determine what execution plan is optimal for this particular query. In this case, there are approximately 772K out of 5M rows which meet the WHERE condition -- or about 15% of the total number of rows in the table. There is a certain threshold, where above it the optimizer will choose to do a sequential table scan of the data, versus do many random seeks into memory or disk. It seems that you are hovering around the threshold for where the optimizer chooses to do a sequential table scan (InnoDB) vs a range operation on a btree with lookups into the data file for each matched row in the index (MyISAM). The difference in returning an estimate vs. the actual row count *might* be the cause of the difference in execution plans. Or, it could have something to do with the weights that the optimizer chooses to place on bookmark lookups in MyISAM vs a quick table scan in InnoDB. I'd be interested to see what the difference in *performance* is? Also, in *either* engine, if you are executing this particular query a *lot*, the best thing for you to do would be to put the index on (calldate, usercost) so that you have a covering index available to complete the query. Cheers! Jay Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
servers full potential / FT searches locking tables
Ok.. Straight to the point.. Here is what I currently have. MySQL Ver 14.12 Distrib 5.0.27 RHEL vs 5 584GB Raid 5 storage 8GB of RAM and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024 key cache division limit 100 language /usr/share/mysql/english/ large files support ON large page size 0 large pages OFF lc time names en_US license GPL local infile ON locked in memory OFF log OFF log bin OFF log bin trust function creators OFF log error /var/log/mysql/error.log log queries not using indexes OFF log slave updates OFF log slow queries OFF log warnings 1 long query time 10 low priority updates OFF lower case file system OFF lower case table names 0 max allowed packet 1,073,740,800 max binlog cache size 4,294,967,295 max binlog size 1,073,741,824 max connect errors 10 max connections 5,000 max delayed threads 20 max error count 64 max heap table size 16,777,216 max insert delayed threads 20 max join size 18446744073709551615 max length for sort data 1,024 max prepared stmt count 16,382 max relay log size 0 max seeks for key 4,294,967,295 max sort length 1,024 max sp recursion depth 0 max tmp tables 32 max user connections 0 max write lock count 4,294,967,295 multi range count 256 myisam data pointer size 6 myisam max sort file size 2,147,483,647
Re: servers full potential / FT searches locking tables
SELECTs don't lock the table. Are you having frequent UPDATEs while selecting? That would be the reason for locks. -jay Justin wrote: Ok.. Straight to the point.. Here is what I currently have. MySQL Ver 14.12 Distrib 5.0.27 RHEL vs 5 584GB Raid 5 storage 8GB of RAM and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024 key cache division limit 100 language /usr/share/mysql/english/ large files support ON large page size 0 large pages OFF lc time names en_US license GPL local infile ON locked in memory OFF log OFF log bin OFF log bin trust function creators OFF log error /var/log/mysql/error.log log queries not using indexes OFF log slave updates OFF log slow queries OFF log warnings 1 long query time 10 low priority updates OFF lower case file system OFF lower case table names 0 max allowed packet 1,073,740,800 max binlog cache size 4,294,967,295 max binlog size 1,073,741,824 max connect errors 10 max connections 5,000 max delayed threads 20 max error count 64 max heap table size 16,777,216 max insert delayed threads 20 max join size 18446744073709551615 max length for sort data 1,024 max prepared stmt count 16,382 max relay log size 0 max seeks for key 4,294,967,295 max sort length 1,024 max sp recursion depth 0 max tmp tables 32 max user
Re: servers full potential / FT searches locking tables
SELECTs do lock the tables implicitly. According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), here is what the first bulletpoint says under the heading A lock on data can be acquired implicitly or explicitly: For a client that does nothing special to acquires locks, the MySQL server implicitly acquires locks as necessary to process the client's statments sdafely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement. - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, August 27, 2007 2:26:29 PM (GMT-0500) America/New_York Subject: Re: servers full potential / FT searches locking tables SELECTs don't lock the table. Are you having frequent UPDATEs while selecting? That would be the reason for locks. -jay Justin wrote: Ok.. Straight to the point.. Here is what I currently have. MySQL Ver 14.12 Distrib 5.0.27 RHEL vs 5 584GB Raid 5 storage 8GB of RAM and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key
Re: servers full potential / FT searches locking tables
sorry.. you're right.. that came out wrong.. actually shouldn't of even of put that in there like that.. The locking is some other issue that I can't for the life of me remember what the query was.. I know it wasn't an Update but it was copying to a tmp table then sorting the result which was locking the table from the process I was looking at. I'll keep an eye on it to see if I Can replicate it again.. I just want to make sure I've got the server config'd right to utilize everything I have. thanks for the response - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, August 27, 2007 2:26 PM Subject: Re: servers full potential / FT searches locking tables SELECTs don't lock the table. Are you having frequent UPDATEs while selecting? That would be the reason for locks. -jay Justin wrote: Ok.. Straight to the point.. Here is what I currently have. MySQL Ver 14.12 Distrib 5.0.27 RHEL vs 5 584GB Raid 5 storage 8GB of RAM and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024 key cache division limit 100 language /usr/share/mysql/english/ large files support ON large page size 0 large pages OFF lc time names en_US license GPL local infile ON locked in memory OFF log OFF log bin
Re: servers full potential / FT searches locking tables
A read lock does not prevent other reads. Rolando Edwards wrote: SELECTs do lock the tables implicitly. According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), here is what the first bulletpoint says under the heading A lock on data can be acquired implicitly or explicitly: For a client that does nothing special to acquires locks, the MySQL server implicitly acquires locks as necessary to process the client's statments sdafely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement. - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, August 27, 2007 2:26:29 PM (GMT-0500) America/New_York Subject: Re: servers full potential / FT searches locking tables SELECTs don't lock the table. Are you having frequent UPDATEs while selecting? That would be the reason for locks. -jay Justin wrote: Ok.. Straight to the point.. Here is what I currently have. MySQL Ver 14.12 Distrib 5.0.27 RHEL vs 5 584GB Raid 5 storage 8GB of RAM and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024
Re: where column
Olav Mørkrid wrote: hello does anyone know what is returned when you do a where column without further parameters? SELECT * FROM TABLE WHERE COLUMN; for integer columns it seems to return non-zero columns, but for other types of columns the results seemed unpredictable. The value of the column will be converted to a TRUE or FALSE value (1 or 0). If the column would evaluate in a numerical setting to a value of 0, the statement would read WHERE FALSE and not show that row. Can you provide an example of a resultset that does not make sense to you? Its no different than providing an actual equation to provide the true or false condition SELECT... FROM ... WHERE 0 will always return no data as the WHERE condition will never be true. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ ___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: servers full potential / FT searches locking tables
Sometimes I get about 300 connections to the server, all are selects and all select and get the data returned but the connection doesn't go away and the website doesn't load up.. usually if there is a lock, the selects wait 2-3 secs and build up, but once unlocked the queries all are performed and then go away.. and the sites load fine.. 3 times today this has happened and I'm trying to figure out what could be the cause of it. if I restart MySQL everything is good. Anyone have any ideas or any ideas on how I can trace where the culprit would be.. it's a LAMP backend.. - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Rolando Edwards [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; Justin [EMAIL PROTECTED] Sent: Monday, August 27, 2007 3:03 PM Subject: Re: servers full potential / FT searches locking tables A read lock does not prevent other reads. Rolando Edwards wrote: SELECTs do lock the tables implicitly. According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), here is what the first bulletpoint says under the heading A lock on data can be acquired implicitly or explicitly: For a client that does nothing special to acquires locks, the MySQL server implicitly acquires locks as necessary to process the client's statments sdafely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement. - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, August 27, 2007 2:26:29 PM (GMT-0500) America/New_York Subject: Re: servers full potential / FT searches locking tables SELECTs don't lock the table. Are you having frequent UPDATEs while selecting? That would be the reason for locks. -jay Justin wrote: Ok.. Straight to the point.. Here is what I currently have. MySQL Ver 14.12 Distrib 5.0.27 RHEL vs 5 584GB Raid 5 storage 8GB of RAM and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb
[MYSQL]Time formatting for cycle time.
Hello all, I am working on Martin Minka's date diff function as found at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful thing. However, I am trying to alter it or identify a similar function that instead of giving me the number of days between two dates it returns the number of workday hours:minutes between two datetimes, or some other date differential (such as an exact number of days between two dates with remainder) I hate asking open ended questions, but can anyone give me any hints as to how to make this conversion? I may be able to figure it out... In a few weeks. My long term goal is to identify the business hours(minutes, seconds, whatever) between two dates, taking into account weekends, holidays, and business hours. Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
Re: servers full potential / FT searches locking tables
Justin wrote: Sometimes I get about 300 connections to the server, all are selects and all select and get the data returned but the connection doesn't go away and the website doesn't load up.. usually if there is a lock, the selects wait 2-3 secs and build up, but once unlocked the queries all are performed and then go away.. and the sites load fine.. 3 times today this has happened and I'm trying to figure out what could be the cause of it. if I restart MySQL everything is good. Anyone have any ideas or any ideas on how I can trace where the culprit would be.. it's a LAMP backend.. Are you classing insert...select or create tableselect as selects? They have very different locking effects. Is the database in replication? if so is it a master or slave? Do you have NFS mounts anywhere on the database server? Are you using exclusively innodb tables? if not the source is probably table contention. Get a couple of 'show full processlist', 'show innodb status' query outputs during the lockups and run vmstat 1 -S M in another terminal. With the outputs from both you've something to work with. Nigel Wood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BUG: DATE_ADD 99999 fails, but 9999 works.
using 9 as the DATE_ADD interval value will result in 000-00-00 but one less 9 will work. root# mysql --version mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine wrapper CREATE TABLE `Users` ( `CoreID` int(10) unsigned NOT NULL auto_increment, `Username` varchar(155) default NULL, `Password` varchar(64) default NULL, `password_expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`CoreID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL DAY) WHERE CoreID = 1 LIMIT 1; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) WHERE CoreID = 1 LIMIT 1; Added as bug: http://bugs.mysql.com/bug.php?id=30656 Added as a tip: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html BTW, it's extremely obnoxious that when I enter in a comment on that page, then choose bug from the select box, it throws me to another page that says, Sorry, but this is not the correct place to report bugs. You need to report bugs using our online bug reporting system. You can start filling out a bug report with the text you have already entered by clicking the following button: ... Well why the heck did you let me choose that option then! UGH! ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG: DATE_ADD 99999 fails, but 9999 works.
At 4:56 PM -0700 8/27/07, Daevid Vincent wrote: using 9 as the DATE_ADD interval value will result in 000-00-00 but one less 9 will work. root# mysql --version mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine wrapper CREATE TABLE `Users` ( `CoreID` int(10) unsigned NOT NULL auto_increment, `Username` varchar(155) default NULL, `Password` varchar(64) default NULL, `password_expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`CoreID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL DAY) WHERE CoreID = 1 LIMIT 1; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) WHERE CoreID = 1 LIMIT 1; Added as bug: http://bugs.mysql.com/bug.php?id=30656 Added as a tip: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html BTW, it's extremely obnoxious that when I enter in a comment on that page, then choose bug from the select box, it throws me to another page that says, Sorry, but this is not the correct place to report bugs. You need to report bugs using our online bug reporting system. You can start filling out a bug report with the text you have already entered by clicking the following button: ... Well why the heck did you let me choose that option then! UGH! Why do you consider this extremely obnoxious? Also, I'm curious why you tried to use the user comment page to report a bug, given that the page says: If you've found a bug or wish to request a feature MySQL is missing, please use the bug system. When doing this, please perform a search first in order not to report a bug or request a feature that's already in that system. The option is there to let you know that the user comment system isn't for reporting bugs *and* to let you know the correct place to report them. It's there as extra reinforcement for people who ignore or overlook the statement that I mentioned in the preceding paragraph. -- 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]
Can't start mysql
Does anybody know what is the problem. MySql ran out of memory and I have not been able to start it again. Here's an error from log file: 070827 15:09:17 mysqld started ^G/usr/sbin/mysqld: Character set 'utf8 |' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file 070827 15:09:17 [ERROR] Aborting 070827 15:09:17 [Note] /usr/sbin/mysqld: Shutdown complete 070827 15:09:17 mysqld ended Any help appreciated. -Original Message- From: Weston, Craig (OFT) [mailto:[EMAIL PROTECTED] Sent: Monday, August 27, 2007 1:02 PM To: mysql@lists.mysql.com Subject: [MYSQL]Time formatting for cycle time. Hello all, I am working on Martin Minka's date diff function as found at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful thing. However, I am trying to alter it or identify a similar function that instead of giving me the number of days between two dates it returns the number of workday hours:minutes between two datetimes, or some other date differential (such as an exact number of days between two dates with remainder) I hate asking open ended questions, but can anyone give me any hints as to how to make this conversion? I may be able to figure it out... In a few weeks. My long term goal is to identify the business hours(minutes, seconds, whatever) between two dates, taking into account weekends, holidays, and business hours. Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG: DATE_ADD 99999 fails, but 9999 works.
I don't think this is a bug. I think what's happening is that your timestamp column can't hold that date, it's max value is somewhere in 2038. So I guess either change your timestamp column to a datetime column, or prevent users from putting invalid data in. Daevid Vincent wrote: using 9 as the DATE_ADD interval value will result in 000-00-00 but one less 9 will work. root# mysql --version mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine wrapper CREATE TABLE `Users` ( `CoreID` int(10) unsigned NOT NULL auto_increment, `Username` varchar(155) default NULL, `Password` varchar(64) default NULL, `password_expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`CoreID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL DAY) WHERE CoreID = 1 LIMIT 1; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) WHERE CoreID = 1 LIMIT 1; Added as bug: http://bugs.mysql.com/bug.php?id=30656 Added as a tip: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html BTW, it's extremely obnoxious that when I enter in a comment on that page, then choose bug from the select box, it throws me to another page that says, Sorry, but this is not the correct place to report bugs. You need to report bugs using our online bug reporting system. You can start filling out a bug report with the text you have already entered by clicking the following button: ... Well why the heck did you let me choose that option then! UGH! ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BUG: DATE_ADD 99999 fails, but 9999 works.
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Monday, August 27, 2007 5:04 PM To: Daevid Vincent; 'MySQL General' Subject: Re: BUG: DATE_ADD 9 fails, but works. At 4:56 PM -0700 8/27/07, Daevid Vincent wrote: using 9 as the DATE_ADD interval value will result in 000-00-00 but one less 9 will work. root# mysql --version mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine wrapper CREATE TABLE `Users` ( `CoreID` int(10) unsigned NOT NULL auto_increment, `Username` varchar(155) default NULL, `Password` varchar(64) default NULL, `password_expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`CoreID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL DAY) WHERE CoreID = 1 LIMIT 1; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) WHERE CoreID = 1 LIMIT 1; Added as bug: http://bugs.mysql.com/bug.php?id=30656 Added as a tip: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html BTW, it's extremely obnoxious that when I enter in a comment on that page, then choose bug from the select box, it throws me to another page that says, Sorry, but this is not the correct place to report bugs. You need to report bugs using our online bug reporting system. You can start filling out a bug report with the text you have already entered by clicking the following button: ... Well why the heck did you let me choose that option then! UGH! Why do you consider this extremely obnoxious? Also, I'm curious why you tried to use the user comment page to report a bug, given that the page says: If you've found a bug or wish to request a feature MySQL is missing, please use the bug system. When doing this, please perform a search first in order not to report a bug or request a feature that's already in that system. The option is there to let you know that the user comment system isn't for reporting bugs *and* to let you know the correct place to report them. It's there as extra reinforcement for people who ignore or overlook the statement that I mentioned in the preceding paragraph. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Because it's also a tip as your form has an option for, so some poor sucker doesn't spend hours trying to figure out why their passwords are all expired and nobody can log into their $40,000 appliance and a shit storm comes down upon their head because some dumb user thought they'd _try_ to put 9 in so their password wouldn't expire for 274 years. Plus I did do a bug search and found nothing for DATE_ADD. Not everyone peruses the 6 million bugs in your system, trying every possible keyword... It is more useful to have something listed at the bottom there (as I did), so someone that was doing a search on that page for DATE_ADD would then see this _tip_. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG: DATE_ADD 99999 fails, but 9999 works.
At 5:44 PM -0700 8/27/07, Chris wrote: I don't think this is a bug. I think what's happening is that your timestamp column can't hold that date, it's max value is somewhere in 2038. So I guess either change your timestamp column to a datetime column, or prevent users from putting invalid data in. Ahh ... yes, indeed. mysql select DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY); +-+ | DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) | +-+ | 2281-06-10 | +-+ Daevid, one strategy that might work for you is to enable strict or traditional SQL mode so that you get an error if the timestamp value is out of range: mysql UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL - 9 DAY) WHERE CoreID = 1 LIMIT 1; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql show warnings; +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1264 | Out of range value for column 'password_expire' at row 1 | +-+--+--+ 1 row in set (0.00 sec) mysql set sql_mode='traditional'; Query OK, 0 rows affected (0.01 sec) mysql UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) WHERE CoreID = 1 LIMIT 1; ERROR 1292 (22007): Incorrect datetime value: '2281-06-10' for column 'password_expire' at row 1 Daevid Vincent wrote: using 9 as the DATE_ADD interval value will result in 000-00-00 but one less 9 will work. root# mysql --version mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine wrapper CREATE TABLE `Users` ( `CoreID` int(10) unsigned NOT NULL auto_increment, `Username` varchar(155) default NULL, `Password` varchar(64) default NULL, `password_expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`CoreID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL DAY) WHERE CoreID = 1 LIMIT 1; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) WHERE CoreID = 1 LIMIT 1; Added as bug: http://bugs.mysql.com/bug.php?id=30656 Added as a tip: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html BTW, it's extremely obnoxious that when I enter in a comment on that page, then choose bug from the select box, it throws me to another page that says, Sorry, but this is not the correct place to report bugs. You need to report bugs using our online bug reporting system. You can start filling out a bug report with the text you have already entered by clicking the following button: ... Well why the heck did you let me choose that option then! UGH! ÐÆ5ÏÐ -- 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]
RE: BUG: DATE_ADD 99999 fails, but 9999 works.
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Monday, August 27, 2007 5:45 PM I don't think this is a bug. I think what's happening is that your timestamp column can't hold that date, it's max value is somewhere in 2038. You appear to be correct, burried in the plethora of bullet points here: http://dev.mysql.com/doc/refman/5.0/en/datetime.html For example, TIMESTAMP values cannot be earlier than 1970 or later than 2038. So that _is_ the root cause of the problem, but it's still a bug. There is no reason (from a mySQL user/PHP developer's perspective) that 2038 should be my upper year limit. I should be able to make any date up to -12-31 !!? This is absurd. We're making enterprise level tools that run at US Government offices, The entire state of Alaska, Military, Colleges, Fortune 500 companies You mean in 21 years from now, all this will just fail miserably because of some obscure 2038 limitation? This is Y2K all over again -- unless mySQL fixes this bug. So I guess either change your timestamp column to a datetime column, Interesting thing with that, we used to use datetime columns (where applicable) but since we store everything in UTC now, as this product is international, we had to switch (painfully I might add) to timestamp. I forget the exact reason, and this was about a year ago, so it may be moot now anyways -- it had to do with using mySQL's conversion routines so the dates would display in the GUI for the user's local timezone they set in their profile, and those routines didn't work on datetime or some such nonsense. or prevent users from putting invalid data in. I've limited the text field to 4 digits from 5. but that doesn't make this any less of a mySQL issue, that's just a band-aid to mask an inadequacy of the RDBMS. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MYSQL]Time formatting for cycle time.
Craig, I am working on Martin Minka's date diff function as found at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful thing. However, I am trying to alter it or identify a similar function that instead of giving me the number of days between two dates it returns the number of workday hours:minutes between two datetimes, or some other date differential (such as an exact number of days between two dates with remainder) Here's a logically equivalent datediff calc, mebbe slightly simpler: SET @d1 = '2007-1-1'; SET @d2 = '2007-3-31'; SET @dow1 = DAYOFWEEK(@d1); SET @dow2 = DAYOFWEEK(@d2); SET @days = DATEDIFF(@d2,@d1); SET @wknddays = 2 * FLOOR( @days / 7 ) + IF( @dow1 = 1 AND @dow2 1, 1, IF( @dow1 = 7 AND @dow2 = 1, 1, IF( @dow1 1 AND @dow1 @dow2, 2, if( @dow1 7 AND @dow2 = 7, 1, 0 ) ) ) ); SELECT FLOOR(@[EMAIL PROTECTED]) AS BizDays; To include time in the difference, you could adopt as a return convention a string format like 'N days hh:mm:ss', where N is the date difference calculated above, minus one if the time portion of d1 is later than than that of d2. Something like this: SET @d1 = '2007-1-1 00:00:00'; SET @d2 = '2007-3-31 12:00:00'; SET @tdiff = TIMEDIFF( TIME(@d1), TIME(@d2) ); SET @dow1 = DAYOFWEEK(@d1); SET @dow2 = DAYOFWEEK(@d2); SET @days = DATEDIFF(@d2,@d1); SET @wknddays = 2 * FLOOR( @days / 7 ) + IF( @dow1 = 1 AND @dow2 1, 1, IF( @dow1 = 7 AND @dow2 = 1, 1, IF( @dow1 1 AND @dow1 @dow2, 2, IF( @dow1 7 AND @dow2 = 7, 1, 0 ) ) ) ); SET @days = FLOOR(@days - @wkndDays) - IF( @tdiff 0, 1, 0 ); SET @tdiff = IF( ASCII(@tdiff) = 45, SUBSTRING(@tdiff,2), TIMEDIFF( '24:00:00', @tdiff )); SELECT CONCAT( @days, ' days ', @tdiff ); PB - Weston, Craig (OFT) wrote: Hello all, I am working on Martin Minka's date diff function as found at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful thing. However, I am trying to alter it or identify a similar function that instead of giving me the number of days between two dates it returns the number of workday hours:minutes between two datetimes, or some other date differential (such as an exact number of days between two dates with remainder) I hate asking open ended questions, but can anyone give me any hints as to how to make this conversion? I may be able to figure it out... In a few weeks. My long term goal is to identify the business hours(minutes, seconds, whatever) between two dates, taking into account weekends, holidays, and business hours. Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.484 / Virus Database: 269.12.9/975 - Release Date: 8/26/2007 9:34 PM
Re: servers full potential / FT searches locking tables
Your settings doesn't seem optimized much. So here first question, do you use 32bits or 64 bits platform? If you have 64 bits platform with 64 bits mysql and os you can boost most the settings to use almost the 8G of ram you have on the server. If you are using 32bits you will have to do some calculation so you don't go over ~2.6G (why not 4Gb?, go read on that on the net) So the 2 most importants settings are: key_buffer_size (mainly myisam table) and/or innodb_buffer_pool_size (innodb table) Depending if you're using more innodb or myisam (or a mix) you'll tweak those pamareters differently, it's usually however not recommended to go over 4Gb for the key_buffer_size. MyIsam only stores the key into that buffer, so you don't have much index, not worth taking it too big for no reason. Innodb however can cache data as well, and will benefit from the biggest value possible. The server generate statistic that you can look to know the effect of that. If you are using phpmyadmin in the variables and status part you can see the index usage to guide you. You can have a look at the different my.cnf that comes with mysql distribution they put comment in there with interesting value for thumbs rule. Here the except for key_buffer_size and innodb_buffer_pool_size: # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=2G # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size=2G Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Justin a écrit : Ok.. Straight to the point.. Here is what I currently have. MySQL Ver 14.12 Distrib 5.0.27 RHEL vs 5 584GB Raid 5 storage 8GB of RAM and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query
