Re: Maximum number of rows in the table
I don't think there is a limit. Just as long as your harddisk can contain the data there should be no problem. Remco - Original Message - From: chandra shekar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:21 AM Subject: Maximum number of rows in the table Hi MySql gurus, We are using MySql for our database support in our project. It is a multiserver environment. We are using a single table to store info abt objects of each server. We want to know what is the maximum number of rows that the MySql supports for a table. In our case when the number of rows exceeds 1500 the server crashes. Is it the max number of rows ? with regards, Chandrashekar B.N. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SUPPORT PLEASE - Error while running mysql_install_db
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: SUPPORT PLEASE - Error while running mysql_install_db Description: After installing successfully mysql in my sun solaris - sparc machine, while i am trying to run the script mysql_install_db system has given the following error message ld.so.1: ./bin/my_print_defaults: fatal: libz.so.1: open failed: No such file or directory WARNING: The host 'inatp12wks99' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables ld.so.1: ./bin/mysqld: fatal: libpthread.so.1: version `SUNW_1.2' not found (required by file ./bin/mysqld) Killed Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data 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 ./bin/mysqlbug script! Submitter-Id: Ramnarayanan Originator: Super-User Organization: iNautix Technologies India Pvt Ltd., TN, Chennai MySQL support : none Synopsis: Error while running mysql_install_db Severity: critical Priority: high Category: mysql Class:support Release: mysql-3.23.49 (Official MySQL binary) Environment: machine, os, target, libraries (multiple lines) System: SunOS inatp12wks99 5.6 Generic_105181-26 sun4u sparc SUNW,Ultra-5_10 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gmake /usr/local/bin/gcc /opt/SUNWspro/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1623052 Apr 27 2001 /lib/libc.a lrwxrwxrwx 1 root root 11 Aug 29 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1024620 Apr 27 2001 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1623052 Apr 27 2001 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Aug 29 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1024620 Apr 27 2001 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql ' --with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Maximum number of rows in the table
Our MySQL table currently has 2.5million rows, but I have seen this go upto at least 17 million rows without a single problem. sql, query - Original Message - From: Remco Oosten To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:08 PM Subject: Re: Maximum number of rows in the table I don't think there is a limit. Just as long as your harddisk can contain the data there should be no problem. Remco - Original Message - From: chandra shekar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:21 AM Subject: Maximum number of rows in the table Hi MySql gurus, We are using MySql for our database support in our project. It is a multiserver environment. We are using a single table to store info abt objects of each server. We want to know what is the maximum number of rows that the MySql supports for a table. In our case when the number of rows exceeds 1500 the server crashes. Is it the max number of rows ? with regards, Chandrashekar B.N. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Load data infile
sql, query Hello Dear Group I want to use the command LOAD DATA INFILE I have a table with 18 columns. But to fill the columns unfortunately I don't have one input file put two. The first on shall fill 16 of the 18 columns. The second one contains the data of column 8 and column 12. How do I have to perform the LOAD DATA INFILE command, to get that thing working? Thanks in advance dcp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: moving databases question...
Hi, Richard Idalski wrote: I have before me what seems to be a very daunting task to someone with my limited SQL knowledge. I'm upgrading our ad server which runs on MySQL 3.22.32 to a newer machine and MySQL 3.23.39. Right now there are 16GB worth of databases that need to be transferred over, and the old server lacks enough hard disks space to even do a dump on one of those tables. So is there a way I can do a mysqldump directly from the old machine to the new one over the network? And if I can or if I'm offered a better way to do this, will the difference in MySQL version affect anything? Like all commandline tools mysqldump also has option -h host mysqldump -h oldhost mysqldump --help to see all it's options http://www.mysql.com/doc/m/y/mysqldump.html -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Maximum number of rows in the table
I don't think there is a limit. Just as long as your harddisk can contain the data there should be no problem. Yes, I guess this should be the case. Theoretically, though; the limit would be the number of indexable rows, wouldn't it? Since the integer length used to store this is 11 digits, this would amount to 10^11 = 100 000 000 000... Although probably not practially achievable; at least its quite far from 1500... Best regards, Eivind :-) mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: join group problem
Hi Dvoracek, I think you forgot to put firstly GROUP BY one.id so your select statement must be as : SELECT one.descr, COUNT(two.id) xxx FROM one LEFT JOIN two USING(id) GROUP BY one.id, two.id ORDER BY one.id; Regards, Tolga GOZE Oksijen Teknoloji www.o2.com.tr Michal Dvoracek michal.dvoracek@c To: [EMAIL PROTECTED] apitol.cz cc: Subject: join group problem 07/05/2002 08:38 Please respond to Dvoracek Michal Hello, i have 2 tables, one: ++---+ | id | descr | ++---+ | 1 | fir | | 2 | two | | 3 | fre | | 4 | fou | ++---+ two: ++--+ | id | data | ++--+ | 1 | a| | 1 | b| | 1 | c| | 1 | d| ++--+ SELECT one.descr, COUNT(two.id) xxx FROM one LEFT JOIN two USING(id) GROUP BY two.id ORDER BY one.id; i got: +---+-+ | descr | xxx | +---+-+ | fir | 4 | | two | 0 | +---+-+ 2nd row is what(mysql Ver 11.15 Distrib 3.23.43, for pc-linux-gnu (i686)) ??? I tested it home and i got only one row (tested on mysql 4.0.1 on win). but i need this result: descr count -- fir 4 two 0 fre 0 fou 0 it's possible to get this result in one guery ?? i need that goup by will affect only join, no whole query. Thank you for help. Regards, Michal Dvoracek [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: join group problem
Hi Dvoracek, I think you forgot to put firstly GROUP BY one.id so your select statement must be as : SELECT one.descr, COUNT(two.id) xxx FROM one LEFT JOIN two USING(id) GROUP BY one.id, two.id ORDER BY one.id; Regards, Tolga GOZE Oksijen Teknoloji www.o2.com.tr Michal Dvoracek michal.dvoracek@c To: [EMAIL PROTECTED] apitol.cz cc: Subject: join group problem 07/05/2002 08:38 Please respond to Dvoracek Michal Hello, i have 2 tables, one: ++---+ | id | descr | ++---+ | 1 | fir | | 2 | two | | 3 | fre | | 4 | fou | ++---+ two: ++--+ | id | data | ++--+ | 1 | a| | 1 | b| | 1 | c| | 1 | d| ++--+ SELECT one.descr, COUNT(two.id) xxx FROM one LEFT JOIN two USING(id) GROUP BY two.id ORDER BY one.id; i got: +---+-+ | descr | xxx | +---+-+ | fir | 4 | | two | 0 | +---+-+ 2nd row is what(mysql Ver 11.15 Distrib 3.23.43, for pc-linux-gnu (i686)) ??? I tested it home and i got only one row (tested on mysql 4.0.1 on win). but i need this result: descr count -- fir 4 two 0 fre 0 fou 0 it's possible to get this result in one guery ?? i need that goup by will affect only join, no whole query. Thank you for help. Regards, Michal Dvoracek [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Maximum number of rows in the table
Jeremy at yahoo quoted one with 280 million rows, I think. Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: Paul [mailto:[EMAIL PROTECTED]] Sent: 07 May 2002 08:20 To: Remco Oosten Cc: [EMAIL PROTECTED] Subject: Re: Maximum number of rows in the table Our MySQL table currently has 2.5million rows, but I have seen this go upto at least 17 million rows without a single problem. sql, query - Original Message - From: Remco Oosten To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:08 PM Subject: Re: Maximum number of rows in the table I don't think there is a limit. Just as long as your harddisk can contain the data there should be no problem. Remco - Original Message - From: chandra shekar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:21 AM Subject: Maximum number of rows in the table Hi MySql gurus, We are using MySql for our database support in our project. It is a multiserver environment. We are using a single table to store info abt objects of each server. We want to know what is the maximum number of rows that the MySql supports for a table. In our case when the number of rows exceeds 1500 the server crashes. Is it the max number of rows ? with regards, Chandrashekar B.N. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Order by date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all I've been searching for a solution to the following: Is there an easy way to order by date ( - MM - DD), at the moment my results are coming back with the *day* ordered but not the whole date like this: 02 02 2002 03 03 2005 18 02 2003 See what I mean? I can't beleive there is nothing that can be done with sql about this but I can't seem to find anything that will tell me how to do it so a little pointer would be smashing. Many thanks... - -- Nick Wilson // www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE815hbHpvrrTa6L5oRAtdGAJ9MhgAcbdHqQnBJpfRPHGCf3msHvQCeLq0S qL5vUD6SumKXFFL/mOnhp/0= =k44P -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Maximum Number of rows in a table.
Hi Mysql Gurus, We are working a project using Mysql for the database support. The environment is multiserver. We are using a single table for all the servers to store server information. We basically want to know what is the maximum number of rows that we can have in a table. In our case when the number of rows exceeds 1500 the server crashes. Is it 1500 maximum number of rows that we can have ??? with regards, Chandrashekar B.N. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication
Hello, Excuse me for my English, but I am French. I am studing MySQL and particularly replications. When we have 2 threads which use BEGIN-COMMIT/ROLLBACK (with Berkeley-db), there is a conflict if we make this schema: THREAD 1! THREAD 2 ! begin; ! ! begin; ! delete from table where id=4; update table... ! An error comes on thread 1: ERROR 1030: Got error -30996 from table handler What does it mean? What can we do to lock table at the first BEGIN and not at the first update or delete? (We want that thread 1 locks table at the begin to avoid the error)? Thanks a lot, and have a good day. -- Heidi DIAS -- GAEL Consultant Software Engineer Cité Descartes 18, rue Albert Einstein 77420 Champs-sur-Marne France mailto:[EMAIL PROTECTED] http://www.gael.fr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication
Hello, Excuse me for my English, but I am French. I am studing MySQL and particularly replications. When we have 2 threads which use BEGIN-COMMIT/ROLLBACK (with Berkeley-db), there is a conflict if we make this schema: THREAD 1! THREAD 2 ! begin; ! ! begin; ! delete from table where id=4; update table... ! An error comes on thread 1: ERROR 1030: Got error -30996 from table handler What does it mean? What can we do to lock table at the first BEGIN and not at the first update or delete? (We want that thread 1 locks table at the begin to avoid the error)? Thanks a lot, and have a good day. -- Heidi DIAS -- GAEL Consultant Software Engineer Cité Descartes 18, rue Albert Einstein 77420 Champs-sur-Marne France mailto:[EMAIL PROTECTED] http://www.gael.fr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication
Hello, Excuse me for my English, but I am French. I am studing MySQL and particularly replications. When we have 2 threads which use BEGIN-COMMIT/ROLLBACK (with Berkeley-db), there is a conflict if we make this schema: THREAD 1! THREAD 2 ! begin; ! ! begin; ! delete from table where id=4; update table... ! An error comes on thread 1: ERROR 1030: Got error -30996 from table handler What does it mean? What can we do to lock table at the first BEGIN and not at the first update or delete? (We want that thread 1 locks table at the begin to avoid the error)? Thanks a lot, and have a good day. -- HeidiDIAS -- GAEL Consultant Software Engineer Cité Descartes 18, rue Albert Einstein 77420 Champs-sur-Marne France mailto:[EMAIL PROTECTED] http://www.gael.fr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
instead of subquery?
Hi. I have this table (A) IDJOB ¦ IDKIT 4 ¦ 19 4 ¦ 19 2 ¦ 19 2 ¦ 5 I need to extract IDKIT with IDJOB2 if and only if IDKIT IDKIT when IDJOB=2 In this case my query should return null With a subquery I can do this and it works SELECT idkit from A where A.id_job2 and A.id_kit not in (select A.id_kit from A where A.id_job=2) Anyone could suggest how I could do this in mysql without subquery? Thanks in advance Matteo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: instead of subquery?
Try to build the following table: IDJOB | IDKIT | IDJOB2 4 | 19 | 4 4 | 19 | 4 4 | 19 | 4 4 | 19 | 4 4 | 19 | 2 4 | 19 | 2 2 | 19 | 4 2 | 19 | 4 2 | 19 | 2 2 | 5 | 2 (some join) Then throw lines with IDJOB=2, group by IDKIT, with max(IDJOB2=2) You'll get: IDKIT | max(IDJOB=2) 19 | 1 Take lines with max(IDJOB=2)=0 Hope that helps, Oren. -Original Message- From: Matteo Ferrari [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: instead of subquery? Hi. I have this table (A) IDJOB ¦ IDKIT 4 ¦ 19 4 ¦ 19 2 ¦ 19 2 ¦ 5 I need to extract IDKIT with IDJOB2 if and only if IDKIT IDKIT when IDJOB=2 In this case my query should return null With a subquery I can do this and it works SELECT idkit from A where A.id_job2 and A.id_kit not in (select A.id_kit from A where A.id_job=2) Anyone could suggest how I could do this in mysql without subquery? Thanks in advance Matteo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication
Hello, bon jour try first with setAutoCommit off then TRANSACTION Begin COMMIT OR ROLLBACK setAutoCommit ON younes Heidi DIAS wrote: Hello, Excuse me for my English, but I am French. I am studing MySQL and particularly replications. When we have 2 threads which use BEGIN-COMMIT/ROLLBACK (with Berkeley-db), there is a conflict if we make this schema: THREAD 1! THREAD 2 ! begin; ! ! begin; ! delete from table where id=4; update table... ! An error comes on thread 1: ERROR 1030: Got error -30996 from table handler What does it mean? What can we do to lock table at the first BEGIN and not at the first update or delete? (We want that thread 1 locks table at the begin to avoid the error)? Thanks a lot, and have a good day. -- HeidiDIAS -- GAEL Consultant Software Engineer Cité Descartes 18, rue Albert Einstein 77420 Champs-sur-Marne France mailto:[EMAIL PROTECTED] http://www.gael.fr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Exporting Data from web page
In simple terms I need to be able to click a link on a webpage which runs some SQL to produce a result set and then prompts the user to save this as a pipe delimited file onto their HDD. Or which does the saving to HDD without the prompt but it still needs to be triggered from the webpage. Can't find any info online, in lists or in my MYSQL book. Regards Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: instead of subquery?
Hi, Hi. I have this table (A) IDJOB ¦ IDKIT 4 ¦ 19 4 ¦ 19 2 ¦ 19 2 ¦ 5 I need to extract IDKIT with IDJOB2 if and only if IDKIT IDKIT when IDJOB=2 In this case my query should return null With a subquery I can do this and it works SELECT idkit from A where A.id_job2 and A.id_kit not in (select A.id_kit from A where A.id_job=2) Maybe you could try this query: mysql select distinct a1.idjob - from a a1 left join a a2 on a1.idkit = a2.idkit and a1.idjob a2.idjob - where a1.idjob 2 and (a2.idjob 2 or a2.idjob is null); Regards, Sasa »mysql, select, database« - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Maximum Number of rows in a table.
chandrashekar, Tuesday, May 07, 2002, 12:19:54 PM, you wrote: cn We are working a project using Mysql for the database support. cn The environment is multiserver. We are using a single table for cn all the servers to store server information. We basically want cn to know what is the maximum number of rows that we can have cn in a table. In our case when the number of rows exceeds 1500 cn the server crashes. Is it 1500 maximum number of rows that we cn can have ??? No. What do you mean server crashes? It's going down or you get any error message or what? cn with regards, cn Chandrashekar B.N. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SUPPORT PLEASE - Error while running mysql_install_db
RRamasamymani, Tuesday, May 07, 2002, 10:11:08 AM, you wrote: R From: [EMAIL PROTECTED] R To: [EMAIL PROTECTED] R Subject: SUPPORT PLEASE - Error while running mysql_install_db R Description: R After installing successfully mysql in my sun solaris - sparc machine, R while i am trying to run the script mysql_install_db R system has given the following error message R ld.so.1: ./bin/my_print_defaults: fatal: libz.so.1: open failed: No R such file or directory Looks like you should install zlib on your box. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Converting text files to MySQL
Christy, Monday, May 06, 2002, 8:56:53 PM, you wrote: CR I've been trying to import text files into MySQL, but I can't get it to CR work. I'm new to MySQL and am still getting familiar with it, so CR perhaps I'm missing something. I'm using the LOAD DATA LOCAL command CR but I keep getting error 1148 The used command is not allowed with this CR MySQL version. Now from reading the information in the docs section CR (http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html) it appears that LOAD CR DATA LOCAL is sometimes disabled for security reasons, and that there CR are two ways around this. I've tried both ways but it doesn't seem to CR work. I tried using the command --enable-local-infile but apparently CR that's only compatible with version 3.23.48 and before, and we have CR 3.23.49. Is there something else I should do? Can anyone help me CR please? Thanks so much! Christy, give me some more info: 1. What OS are your using? 2. What MySQL server are you using (version, binary or source distribution etc.)? 3. What is your client software? CR Christy Roberts -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: re: setting up innodb
Azharullah, Tuesday, May 07, 2002, 4:30:16 AM, you wrote: AK I am running mysql on solaris. This is a binary release. However, now I AK would like to start my server with the innodb functionality. But I can't AK start my server working for this. AK I am not able to run the ./configure --with-innodb AK I dont know how and where innodb_data_file_path should be specified AK I can't open the mysqld file to add the pathtodatafile AK Where am I suppose to set the variables at the startup(of mysql) AK Does someone have step by step process in mind that I can follow. Help AK would be extremely helpful. First of all, if you want to use InnoDB you should install MySQL-Max. About all startup options you can read in the manual. there are a lot of info about InnoDB: http://www.mysql.com/doc/I/n/InnoDB.html AK Regards. AK --Azhar Khan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by date
Nick, Tuesday, May 07, 2002, 12:06:31 PM, you wrote: NW I've been searching for a solution to the following: NW Is there an easy way to order by date ( - MM - DD), at the moment my NW results are coming back with the *day* ordered but not the whole date NW like this: NW 02 02 2002 NW 03 03 2005 NW 18 02 2003 NW See what I mean? What is your column type? Why you don't use ORDER BY your_date_column in the SELECT statement? It should work, but I can say you exactly because I don't know your table structure. The other causes of wrong result might be that something is not correct in your SQL statement. NW I can't beleive there is nothing that can be done with sql about this NW but I can't seem to find anything that will tell me how to do it so a little pointer would be smashing. NW Many thanks... -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Bug or error in SQL syntax - Specifying and using a user variable inside a single select query
Gregg, Tuesday, May 07, 2002, 12:15:27 AM, you wrote: Check the manual: http://www.mysql.com/doc/V/a/Variables.html GG According to the manual from the above link, where it states: GG == GG You can set a variable with the SET syntax: GG SET @variable= { integer expression | real expression | string expression } GG [,@variable= ...]. GG You can also assign a value to a variable in statements other than SET. GG However, in this case the assignment operator is := rather than =, because = GG is reserved for comparisons in non-SET statements: GG mysql SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; GG +--+--+--+--+ GG | @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 | GG +--+--+--+--+ GG |5 |5 |1 |4 | GG +--+--+--+--+ GG == GG mysql should be SET'ing the user variables in my query above. If mysql is GG processing the WHERE first and then the SELECT, shouldn't the user variables GG be set anyhow (even if the query returns an empty set)? For example: GG mysql SELECT GG -annual_filing.*, GG -companies.name AS company_name, GG -YEAR(filing_date) AS filing_year, GG -@predictedYear := 1999 AS predictedYear, GG -@earliestYear := 1996 AS earliestYear, GG -@learnYear := 1998 AS learnYear GG - FROM GG -annual_filing, GG -companies GG - WHERE GG - annual_filing.company_id = companies.ds_id GG -AND GG - YEAR(filing_date) = @predictedYear GG -AND GG - YEAR(filing_date) = @earliestYear; GG Empty set (25.89 sec) GG mysql SELECT @predictedYear, @earliestYear, @learnYear; GG ++---++ GG | @predictedYear | @earliestYear | @learnYear | GG ++---++ GG | NULL | NULL | NULL | GG ++---++ GG GG 1 row in set (0.00 sec) GG == At first, you didn't initialized variables. So, variables @predictedYear and @earliestYear in your first SELECT statement are NULL. There are no records in tables, that meet these conditions and then SELECT does not initialize variables in the first part of the statement. GG === GG Gregg Graubins [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: strmov
Chirag, Tuesday, May 07, 2002, 6:57:00 AM, you wrote: C Which header file or library i need to include for strmov ? i have C mysql v3 and i have included mysqlclient library. But it still tells C undefined reference. strmov is defined in m_string.h include: blend:/home/egor/mysql-3.23.49/include grep strmov *.h m_string.h:#define strmov(A,B) stpcpy((A),(B)) m_string.h:#define strmov(A,B) (memccpy(A,B,0,INT_MAX)-1) m_string.h:#define strmov_overlapp(A,B) strmov(A,B) m_string.h:#ifndef strmov m_string.h:extern char *strmov(char *dst,const char *src); C Thanx in advance C Chirag -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load data infile
Dennis, Tuesday, May 07, 2002, 10:25:25 AM, you wrote: I want to use the command LOAD DATA INFILE I have a table with 18 columns. But to fill the columns unfortunately I don't have one input file put two. The first on shall fill 16 of the 18 columns. The second one contains the data of column 8 and column 12. How do I have to perform the LOAD DATA INFILE command, to get that thing working? You can insert data not only in the whole table but in certain columns. Look at LOAD DATA INFILE syntax: http://www.mysql.com/doc/L/O/LOAD_DATA.html Thanks in advance dcp -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb tables problem
I have defined some InnoDB tables and try to feed one of them with 3,000,000 records. Using a script piped to mysql. First I tried to insert the rows one by one. It worked very slow. I didn't wait to see if it ends. Next try was to fill some temporary table with the numbers 1..3,000,000. I was able to do it with Log(3,000,000) inserts. select count(*) from TmpTbl; returns 3,000,000 successfully. And now I used 'insert null,Num,concat('user',Num) into .. select Num from TmpTbl' (null is for a auto_increment primary key). This fails, the mysqld crashed, restarts, trying to roll back the transaction, and I've been wating for a long time and the database is still not responsive. Do I need to set some parameters? Do you know a better way to do this? P.S. I'm also using log-bin (so another mysqld will be the slave of this one). Oren Zeev-Ben-Mordehai Infrastructure Engineer PhoneDo Networks office: +972-9-951-7771 ext. 204 fax:+972-9-951-7772 email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Converting text files to MySQL
CR I've been trying to import text files into MySQL, but I can't get it to CR work. I'm new to MySQL and am still getting familiar with it, so CR perhaps I'm missing something. I'm using the LOAD DATA LOCAL command CR but I keep getting error 1148 The used command is not allowed with this CR MySQL version. Now from reading the information in the docs section CR (http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html) it appears that LOAD CR DATA LOCAL is sometimes disabled for security reasons, and that there CR are two ways around this. I've tried both ways but it doesn't seem to CR work. I tried using the command --enable-local-infile but apparently CR that's only compatible with version 3.23.48 and before, and we have CR 3.23.49. Is there something else I should do? Can anyone help me CR please? Thanks so much! Christy, give me some more info: 1. What OS are your using? 2. What MySQL server are you using (version, binary or source distribution etc.)? 3. What is your client software? Hi Egor - we're running Windows XP on that machine. We're running MySQL version 3.23.49, binary distribution. I'm not sure what you mean by client software. Doesn't MySQL include server and client? Maybe I'm missing something here. :) Thanks, Christy :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: can't set a password
[EMAIL PROTECTED] writes: ^^ Oh forgot to ask in the last post, how are you setting the password in the ^^ first place? Are you using a grant statement or inserting values into the ^^ table. I reckon the former is better (and safer) but if you're doing the ^^ later the you need to flush privliges after any changes so that mysql ^^ rereads the grant tables. ^^ ^^ If you can give us the exact line(s) you're putting in then that could help ^^ us see the problem (change/mask the password of course). I've tried mysql update user set password=password('pass') where user='submit_write'; and mysql grant insert,delete,update on *.* to submit_write@% identified by - pass; I had previously issued mysql grant select on *.* to %@%; as well. Either way, I can only login as submit_write with a null password. However, I must use the correct password to login as root. Is there a significance to the order in which users are listed in the usre table? Brandon -- Well-married, a man is winged: ill-matched, he is shackled. --Henry Ward Beecher - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 * and then Victoria Reznichenko declared NW I've been searching for a solution to the following: NW Is there an easy way to order by date ( - MM - DD), at the moment my NW results are coming back with the *day* ordered but not the whole date NW like this: NW 02 02 2002 NW 03 03 2005 NW 18 02 2003 NW See what I mean? What is your column type? Why you don't use ORDER BY your_date_column in the SELECT statement? It should work, but I can say you exactly because I don't know your table structure. The other causes of wrong result might be that something is not correct in your SQL statement. Right. here is the db schema: Feel free to berate me if I'm missing the obvious :-) CREATE TABLE iaadiplom_timetable ( id INT NOT NULL AUTO_INCREMENT, module ENUM('0', '1', '2', '3', '4', '5'), week VARCHAR(255) NULL, date DATE NOT NULL, ektione VARCHAR(255) NULL, title TEXT NULL, teacher_id INT NULL, PRIMARY KEY(id) ) and here is my select: $qry=SELECT id, module, week, ; $qry.=DATE_FORMAT(date, \%d %m %Y\) as date, ; $qry.=ektione, title, teacher_id ; $qry.=FROM $this-table ; $qry.=WHERE module = '$module' ; $qry.=ORDER BY date ASC; Thanks very much for the help! - -- Nick Wilson // www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE819AVHpvrrTa6L5oRAtj2AJ9CFMVEZEyBZKxy7zhOoSmMDAWUrwCfelhu UbSqDCWAxbYSgmGA2/u6OL8= =qD2+ -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb tables problem
Oren, what MySQL version you are running? What OS? Send me a copy of your my.cnf, the error log 'hostname'.err, and the exact sequence of SQL statements you used to crash InnoDB, including the table CREATE statements. Did InnoDB run out of tablespace? Regarding the performance, did you set innodb_flush_log_at_trx_commit=0 when you tried to use the script to pipe inserts to MySQL? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, May 07, 2002 3:16 PM Subject: innodb tables problem I have defined some InnoDB tables and try to feed one of them with 3,000,000 records. Using a script piped to mysql. First I tried to insert the rows one by one. It worked very slow. I didn't wait to see if it ends. Next try was to fill some temporary table with the numbers 1..3,000,000. I was able to do it with Log(3,000,000) inserts. select count(*) from TmpTbl; returns 3,000,000 successfully. And now I used 'insert null,Num,concat('user',Num) into .. select Num from TmpTbl' (null is for a auto_increment primary key). This fails, the mysqld crashed, restarts, trying to roll back the transaction, and I've been wating for a long time and the database is still not responsive. Do I need to set some parameters? Do you know a better way to do this? P.S. I'm also using log-bin (so another mysqld will be the slave of this one). Oren Zeev-Ben-Mordehai Infrastructure Engineer PhoneDo Networks office: +972-9-951-7771 ext. 204 fax:+972-9-951-7772 email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unable to find the mysql.host table!!!
Hi Mysql Gurus, We are facing a problem when we start mysql server. It is giving the error, unable to start mysql, Unable to locate the mysql.host table. Can anyone tell us how to resolve this error ? When we tried to reinstall the mysql package it started the mysql server with no errors. But we are not able to trace what was the problem ? with regards, Chandrashekar B.N. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by date
Hello! May be you are using data as text field or date is being stored in 3 separate columns. Tell me if I am right. You will need to store the date as date data type and will be automatically sorted. Regards ~~~ Tahir Ata Barry BestPriceHost.com The Hosting With 3 MySQL Databases ~~~ 5/7/2002 6:01:09 PM, Nick Wilson [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 * and then Victoria Reznichenko declared NW I've been searching for a solution to the following: NW Is there an easy way to order by date ( - MM - DD), at the moment my NW results are coming back with the *day* ordered but not the whole date NW like this: NW 02 02 2002 NW 03 03 2005 NW 18 02 2003 NW See what I mean? What is your column type? Why you don't use ORDER BY your_date_column in the SELECT statement? It should work, but I can say you exactly because I don't know your table structure. The other causes of wrong result might be that something is not correct in your SQL statement. Right. here is the db schema: Feel free to berate me if I'm missing the obvious :-) CREATE TABLE iaadiplom_timetable ( id INT NOT NULL AUTO_INCREMENT, module ENUM('0', '1', '2', '3', '4', '5'), week VARCHAR(255) NULL, date DATE NOT NULL, ektione VARCHAR(255) NULL, title TEXT NULL, teacher_id INT NULL, PRIMARY KEY(id) ) and here is my select: $qry=SELECT id, module, week, ; $qry.=DATE_FORMAT(date, \%d %m %Y\) as date, ; $qry.=ektione, title, teacher_id ; $qry.=FROM $this-table ; $qry.=WHERE module = '$module' ; $qry.=ORDER BY date ASC; Thanks very much for the help! - -- Nick Wilson // www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE819AVHpvrrTa6L5oRAtj2AJ9CFMVEZEyBZKxy7zhOoSmMDAWUrwCfelhu UbSqDCWAxbYSgmGA2/u6OL8= =qD2+ -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Tahir Ata Barry CEO, Sulata iSoft http://www.sulata.com.pk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
gcc linking libmySQL (unresolved load_defaults symbol)
Hi, I'm having problems porting a working client from Solaris/Linux to Windows NT (the client uses Paul DuBois' client4.c ideas to load_defaults() and getopt_long() to get connection parameters at runtime). I'm using the gcc compiler, and the compile phase seems to work fine, but when linking against the dynamic library libmysql.dll (as suggested in the mysql's INSTALL-SOURCES) I get the following unresolved references: load_defaults getopt_long optarg optind I've noted that these references are in the static library mysqlclient.lib nm mysqlclient.lib | grep load_default but not in the libmysql.lib (thus not in the libMysql.dll). In the spirit of Murphy's Law, if I attempt to link against the static mysqlclient.lib library, I get other (many) unresolved symbols. Has anybody come across and solved something like this? For the record: gcc 2.95 (minGW distribution) MySQL Ver 11.15 Distribution 3.23.38 for Win95/Win98 Windows NT 4 Regards, -- David A. James Statistics Research, Room 2C-253Phone: (908) 582-3082 Bell Labs, Lucent Technologies Fax:(908) 582-3340 Murray Hill, NJ 09794-0636 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysqld runs with high cpu load on freebsd 4.4
Hey All, We recently picked up a box running a web site with mysql on freebsd. The cpu load gets pegged high realy quickly under its normal load. This wasnt always the case.My designers do not believe it to be the queries shown in the slow query log, which return quickly on development platforms (winnt) and appear to be highly optimized. I have tried the port version, a compiled from source version, and the binary off the web site. The system is a PIII 800 with 1G RAM. While soaking the cpu, the mysqladmin processlist return minimal activity happening. The mysqld spends most of its time in the poll stage. Disk I/O seems minimal. The web site response slows down considerably. Restarting the mysqld daemon puts the load back down, but within minutes its bouncing back up there and after about an hour it stabilizes at ~70 percent or higher. This is the contents of the my.cnf I have been playing with. I havent seen much performance difference no matter what values I put in here. [mysqld] #port= 3306 #socket = /tmp/mysql.sock skip-locking set-variable= max_connections=100 set-variable= key_buffer=512M set-variable= max_allowed_packet=2M set-variable= table_cache=1000 set-variable= sort_buffer=32M set-variable= record_buffer=4M set-variable= myisam_sort_buffer_size=92M set-variable= thread_cache=32 set-variable= tmp_table_size=128M #set_variable = log-slow-queries # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 log-bin server-id = 1 Has anyone come across a freeBSD 4.4 issue - possibly with the thread library? Short of putting in a test box, is there a conclusive way to determine that its the queries/configuration causing this not the box? Joe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Order by date
[snip] NW Is there an easy way to order by date ( - MM - DD), at the moment my NW results are coming back with the *day* ordered but not the whole date NW like this: NW 02 02 2002 NW 03 03 2005 NW 18 02 2003 NW See what I mean? [/snip] If the date is in the format above you can select a substring +++ | field1 | rDate | +++ | 1 | 2002-03-01 | | 2 | 2002-03-03 | | 3 | 2002-03-05 | | 4 | 2002-03-07 | | 5 | 2002-03-11 | +++ mysql select substring_index(rDate, -, -1) as Day - from tblDate1 - order by Day; +--+ | Day | +--+ | 01 | | 03 | | 05 | | 07 | | 11 | +--+ If you want to get fancy then you could CONCAT the whole date back together, with the day in the lead position, month in second, year in third. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: newbie question:count() and display details
You may want to use GROUP BY clause to get a count of the results .. See: http://www.mysql.com/doc/G/r/Group_by_functions.html For example, to retrieve the number of row where nation is German do: SELECT COUNT(*) AS mycount, nation FROM memberscopy WHERE nation='German' GROUP BY nation; Gurhan -Original Message- From: Sven Bentlage [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 12:00 AM To: [EMAIL PROTECTED] Subject: newbie question:count() and display details Hi I have a (probably fairly simple newbie question: Using a select statement (via DBI) I get some details from a table and at the same time have a row count of how many rows are returned (to be displayed at the website). A query like select count(nation) AS nation, name, nation from memberscopy where nation = Austrian order by course; results in the following error: ERROR: Query failed (Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause) If I use : select count(*) AS nation, name, nation from memberscopy where nation = German group by surname order by course ; I get those details | nation | name| nation | | 1 | Christian | Austrian | | 1 | Christian | Austrian | | 1 | Maximilian | Austrian | | 1 | Edgar Alexander | Austrian | | 1 | Erik| Austrian | | 1 | Maria | Austrian | Is there any way that I can get a line count of the result set and the details with onl one query? Thanks for your help! Sven - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Order by date
Nick, Tuesday, May 07, 2002, 4:01:09 PM, you wrote: What is your column type? Why you don't use ORDER BY your_date_column in the SELECT statement? It should work, but I can say you exactly because I don't know your table structure. The other causes of wrong result might be that something is not correct in your SQL statement. NW Right. here is the db schema: Feel free to berate me if I'm missing the NW obvious :-) NW CREATE TABLE iaadiplom_timetable NW ( NW id INT NOT NULL AUTO_INCREMENT, NW module ENUM('0', '1', '2', '3', '4', '5'), NW week VARCHAR(255) NULL, NW date DATE NOT NULL, NW ektione VARCHAR(255) NULL, NW title TEXT NULL, NW teacher_id INT NULL, NW PRIMARY KEY(id) NW ) NW and here is my select: NW $qry=SELECT id, module, week, ; NW $qry.=DATE_FORMAT(date, \%d %m %Y\) as date, ; The result of DATE_FORMAT() function is a string. So, in ORDER BY clause is used string 'date'. That is why you get wrong sorting. Use another alias for your date column, it should help you, i.e.: $qry=SELECT id, module, week, ; $qry.=DATE_FORMAT(date, \%d %m %Y\) as mydate, ; [skip] $qry.=ORDER BY date ASC; NW $qry.=ektione, title, teacher_id ; NW $qry.=FROM $this-table ; NW $qry.=WHERE module = '$module' ; NW $qry.=ORDER BY date ASC; NW Thanks very much for the help! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: Converting text files to MySQL
Christy, Tuesday, May 07, 2002, 3:07:25 PM, you wrote: CR I've been trying to import text files into MySQL, but I can't get it CR to CR work. I'm new to MySQL and am still getting familiar with it, so CR perhaps I'm missing something. I'm using the LOAD DATA LOCAL CR command CR but I keep getting error 1148 The used command is not allowed with CR this CR MySQL version. Now from reading the information in the docs CR section CR (http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html) it appears that CR LOAD CR DATA LOCAL is sometimes disabled for security reasons, and that CR there CR are two ways around this. CR Christy, give me some more info: CR 1. What OS are your using? CR 2. What MySQL server are you using (version, binary or source CR distribution etc.)? CR 3. What is your client software? CR Hi Egor - we're running Windows XP on that machine. We're running MySQL CR version 3.23.49, binary distribution. Add in your my.cnf file rows: [mysqld] local-infile=1 [client] local-infile=1 CR I'm not sure what you mean by CR client software. Doesn't MySQL include server and client? Sure, MySQL has server and client packages, but in Windows binary they combine into one package. CR Maybe I'm CR missing something here. :) :) Seems, you have worked only with Windows ... CR Thanks, CR Christy CR :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unable to find the mysql.host table!!!
chandrashekar, Tuesday, May 07, 2002, 12:22:43 PM, you wrote: cn We are facing a problem when we start mysql server. It cn is giving the error, unable to start mysql, Unable to locate cn the mysql.host table. Can anyone tell us how to resolve cn this error ? Did you installed grant tables? (run mysql_install_db) If so, check permissions on the MySQL data dir. cn When we tried to reinstall the mysql package it started cn the mysql server with no errors. But we are not able to trace cn what was the problem ? cn with regards, cn Chandrashekar B.N. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 * and then Jay Blanchard declared If the date is in the format above you can select a substring +++ | field1 | rDate | +++ | 1 | 2002-03-01 | | 2 | 2002-03-03 | | 3 | 2002-03-05 | | 4 | 2002-03-07 | | 5 | 2002-03-11 | +++ mysql select substring_index(rDate, -, -1) as Day - from tblDate1 - order by Day; Thanks, but as I understand it this should not be needed as it is a regular date column and mysql should sort it for me right? - -- Nick Wilson // www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE819tsHpvrrTa6L5oRAkUjAJ0WsLUYpOkKmWAwvn08m8ewQ6cgMQCcDxoq iOgEUaj2XvG3trF/nWqQXt4= =Uh1r -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Support
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: SUPPORT PLEASE - Error while running mysql_install_db Description: After installing successfully mysql in my sun solaris - sparc machine, while i am trying to run the script mysql_install_db system has given the following error message ld.so.1: ./bin/my_print_defaults: fatal: libz.so.1: open failed: No such file or directory WARNING: The host 'inatp12wks99' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables ld.so.1: ./bin/mysqld: fatal: libpthread.so.1: version `SUNW_1.2' not found (required by file ./bin/mysqld) Killed Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data 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 ./bin/mysqlbug script! Submitter-Id: Ramnarayanan Originator: Super-User Organization: iNautix Technologies India Pvt Ltd., TN, Chennai MySQL support : none Synopsis: Error while running mysql_install_db Severity: critical Priority: high Category: mysql Class:support Release: mysql-3.23.49 (Official MySQL binary) Environment: machine, os, target, libraries (multiple lines) System: SunOS inatp12wks99 5.6 Generic_105181-26 sun4u sparc SUNW,Ultra-5_10 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gmake /usr/local/bin/gcc /opt/SUNWspro/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1623052 Apr 27 2001 /lib/libc.a lrwxrwxrwx 1 root root 11 Aug 29 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1024620 Apr 27 2001 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1623052 Apr 27 2001 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Aug 29 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1024620 Apr 27 2001 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql ' --with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared Thanks and regards, R.M.Ramnarayanan, iNautix Technologies India Private Limited, EMail - [EMAIL PROTECTED] Olivier Beutels To: [EMAIL PROTECTED] olivier@mysql cc: [EMAIL PROTECTED] .comSubject: Re: Support 05/07/02 07:15 PM Please respond to olivier
mysqld don't start up anymore
Our mysql-server is down and can't start anymore. He can't connect server through socket '/path/to//mysql.sock' (number) When I take a look in hostname.err I get this on www.sin.khk.be/~crashke/error.log you can find the latest error Jurgen Campforts Meidoornlaan 2/1 2200 Herentals 0496/60.25.75 http://www.scnoorderwijk.be - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysqld runs with high cpu load on freebsd 4.4
Hi Joe, I'm actually going through this same issue on 4.5 box right now running Mysql max.. i've submitted ticket into support @ mysql since i bought it.. I guess this seems to be common thing in Bsd Hope we can all figure whats wrong with it.. -Frankie - Original Message - From: Joe Maimon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 9:28 AM Subject: Mysqld runs with high cpu load on freebsd 4.4 Hey All, We recently picked up a box running a web site with mysql on freebsd. The cpu load gets pegged high realy quickly under its normal load. This wasnt always the case.My designers do not believe it to be the queries shown in the slow query log, which return quickly on development platforms (winnt) and appear to be highly optimized. I have tried the port version, a compiled from source version, and the binary off the web site. The system is a PIII 800 with 1G RAM. While soaking the cpu, the mysqladmin processlist return minimal activity happening. The mysqld spends most of its time in the poll stage. Disk I/O seems minimal. The web site response slows down considerably. Restarting the mysqld daemon puts the load back down, but within minutes its bouncing back up there and after about an hour it stabilizes at ~70 percent or higher. This is the contents of the my.cnf I have been playing with. I havent seen much performance difference no matter what values I put in here. [mysqld] #port= 3306 #socket = /tmp/mysql.sock skip-locking set-variable= max_connections=100 set-variable= key_buffer=512M set-variable= max_allowed_packet=2M set-variable= table_cache=1000 set-variable= sort_buffer=32M set-variable= record_buffer=4M set-variable= myisam_sort_buffer_size=92M set-variable= thread_cache=32 set-variable= tmp_table_size=128M #set_variable = log-slow-queries # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 log-bin server-id = 1 Has anyone come across a freeBSD 4.4 issue - possibly with the thread library? Short of putting in a test box, is there a conclusive way to determine that its the queries/configuration causing this not the box? Joe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: moving databases question...
While I haven't tested this, you might try something like... mysqldump -h host -u userid -p database | mysql -h newhost -u new_userid -p database or, share out and NFS mount the new system's partition to the old system and do a dump that way. Just a few thoughts, Ron Richard Idalski wrote: I have before me what seems to be a very daunting task to someone with my limited SQL knowledge. I'm upgrading our ad server which runs on MySQL 3.22.32 to a newer machine and MySQL 3.23.39. Right now there are 16GB worth of databases that need to be transferred over, and the old server lacks enough hard disks space to even do a dump on one of those tables. So is there a way I can do a mysqldump directly from the old machine to the new one over the network? And if I can or if I'm offered a better way to do this, will the difference in MySQL version affect anything? Thanks in advance for any help/information. --- Richard Idalski System Administrator Nando Media Office: 919.836.4805 AIM: Ryusonuke www.nandomedia.com -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb tables problem
Oren, I did not find any clear crash from the error log. Looks like MySQL somehow failed in shutdown: ... 020507 9:59:12 /usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/bin/mysqld: Forcing close of thread 109 user: 'root' A mysqld process already exists at Tue May 7 09:59:39 IDT 2002 ... Below we see that the big rollback actually finished successfully in 45 minutes: 020507 10:25:39 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 94594158 InnoDB: Doing recovery: scanned up to log sequence number 0 94659584 InnoDB: Doing recovery: scanned up to log sequence number 0 94725120 ... InnoDB: Rolling back trx with id 0 315126 020507 10:51:38 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Rolling back of trx id 0 315126 completed InnoDB: Rollback of uncommitted transactions completed InnoDB: Last MySQL binlog file offset 0 7074979, file name ./sun06-bin.024 020507 11:11:26 InnoDB: Flushing modified pages from the buffer pool... 020507 11:11:27 InnoDB: Started Status information: Current dir: /usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/data/ Current locks: key_cache status: blocks used: 0 not flushed: 0 w_requests: 0 writes: 0 r_requests: 0 reads: 0 handler status: read_key:0 read_next: 0 read_rnd 0 read_first: 0 write: 0 delete 0 update: 0 Table status: Opened tables: 0 Open tables:0 Open files: 2 Open streams: 0 /usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/bin/mysqld: ready for connections 020507 11:11:27 Error in accept: Software caused connection abort ... Looks like the communication between the client and the server is somehow blocked. You can try killing all mysqld and client processes, and restarting mysqld. To get good performance please set my.cnf options as recommended in section 2 of http://www.innodb.com/ibman.html. Regards, Heikki - Original Message - From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:26 PM Subject: RE: innodb tables problem my.cnf -- [mysqld] innodb_data_file_path = ibdata1:2000M log-bin server-id=37 hostname.err (Attached) -- I didn't use 'set innodb_flush_log_at_trx_commit=0'. I'm going to try this. Table creation: create table A ( A1 int(11) not null auto_increment, A2 varchar(32) not null, A3 int(11), A4 varchar(32) not null, primary key (A1), index (A4), unique index (A3,A4), foreign key (A4) B (B4) ) type = InnoDB; insertion: printf insert into A select null,concat('user',Num+1),1,$base_number+Num from TmpTbl;\n Where TmpTbl contains the numbers 1..3,000,000 Regards, Oren. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 3:11 PM To: Oren Zeev-Ben-Mordehai Cc: [EMAIL PROTECTED] Subject: Re: innodb tables problem Oren, what MySQL version you are running? What OS? Send me a copy of your my.cnf, the error log 'hostname'.err, and the exact sequence of SQL statements you used to crash InnoDB, including the table CREATE statements. Did InnoDB run out of tablespace? Regarding the performance, did you set innodb_flush_log_at_trx_commit=0 when you tried to use the script to pipe inserts to MySQL? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, May 07, 2002 3:16 PM Subject: innodb tables problem I have defined some InnoDB tables and try to feed one of them with 3,000,000 records. Using a script piped to mysql. First I tried to insert the rows one by one. It worked very slow. I didn't wait to see if it ends. Next try was to fill some temporary table with the numbers 1..3,000,000. I was able to do it with Log(3,000,000) inserts. select count(*) from TmpTbl; returns 3,000,000 successfully. And now I used 'insert null,Num,concat('user',Num) into .. select Num from TmpTbl' (null is for a auto_increment primary key). This fails, the mysqld crashed, restarts, trying to roll back the transaction, and I've been wating for a long time and the database is still not
Re: Order by date
Quoting Nick Wilson ([EMAIL PROTECTED]): -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 * and then Victoria Reznichenko declared NW I've been searching for a solution to the following: NW Is there an easy way to order by date ( - MM - DD), at the moment my NW results are coming back with the *day* ordered but not the whole date NW like this: NW 02 02 2002 NW 03 03 2005 NW 18 02 2003 ... and here is my select: $qry=SELECT id, module, week, ; $qry.=DATE_FORMAT(date, \%d %m %Y\) as date, ; $qry.=ektione, title, teacher_id ; $qry.=FROM $this-table ; $qry.=WHERE module = '$module' ; $qry.=ORDER BY date ASC; Get rid of the as date from your sql query. I think that should make it work the way you want. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
please take me off from the list thank you
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 9:49 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Support From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: SUPPORT PLEASE - Error while running mysql_install_db Description: After installing successfully mysql in my sun solaris - sparc machine, while i am trying to run the script mysql_install_db system has given the following error message ld.so.1: ./bin/my_print_defaults: fatal: libz.so.1: open failed: No such file or directory WARNING: The host 'inatp12wks99' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables ld.so.1: ./bin/mysqld: fatal: libpthread.so.1: version `SUNW_1.2' not found (required by file ./bin/mysqld) Killed Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data 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 ./bin/mysqlbug script! Submitter-Id: Ramnarayanan Originator: Super-User Organization: iNautix Technologies India Pvt Ltd., TN, Chennai MySQL support : none Synopsis: Error while running mysql_install_db Severity: critical Priority: high Category: mysql Class:support Release: mysql-3.23.49 (Official MySQL binary) Environment: machine, os, target, libraries (multiple lines) System: SunOS inatp12wks99 5.6 Generic_105181-26 sun4u sparc SUNW,Ultra-5_10 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gmake /usr/local/bin/gcc /opt/SUNWspro/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1623052 Apr 27 2001 /lib/libc.a lrwxrwxrwx 1 root root 11 Aug 29 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1024620 Apr 27 2001 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1623052 Apr 27 2001 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Aug 29 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1024620 Apr 27 2001 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql ' --with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared Thanks and regards, R.M.Ramnarayanan, iNautix Technologies India Private Limited, EMail - [EMAIL PROTECTED] Olivier Beutels To: [EMAIL PROTECTED] olivier@mysql cc: [EMAIL PROTECTED] .comSubject: Re: Support 05/07/02 07:15 PM Please respond to olivier Dear R.M.Ramnarayanan, Thank you for your e-mail. Could you please try once again. Regards, Olivier On 7 May 2002 at 13:00, [EMAIL PROTECTED] wrote: Dear MySQL, I want to post a bug to your existing list [EMAIL PROTECTED]. But, when i sent it, Its bouncing back to my mail box with the following error message Sorry. Your message could not be delivered to: w3d3demo (Mailbox or Conference is full.) Please redirect me with the right email-id. Sorry for this inconvenience. Thanks and regards, R.M.Ramnarayanan, iNautix Technologies India Private Limited, EMail - [EMAIL PROTECTED] -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___
MySQL performance issues - PLEASE ADVICE!!
I am having performance problems with my MySQL installation - what would be an appropriate channel for requesting help to such a problem? I have posted to this list twice and another one as well. Volumes are very high on using MySQL/standard SQL questions, but not an single suggestion has been submitted to my request. Is the only valid alternative to go for an annual support deal with MySQL.org? My site can't afford that. Lots of enthusiastic 'community' members will be disappointed if it should go down because I cant afford proper support. Please respond if you have any suggestion where it might be wise to direct my request! All the best; Eivind :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Exporting Data from web page
Hi Dave, This won't have purely SQL only solution , you can use SELECT .. INTO OUTFILE .. syntax to create a file and you can zip the file with a random name and link to that zip file.. This will force the explorer to save the file. If you just let it stay as a text file, the browser won't download it, it will just display, but there might be a way to force the browser download the file by defining a MIME TYPE even if the file is in text format. You may want to make a search on MIME TYPES and see what you come up with.. For the SELECT .. INTO OUTFILE.. syntax, see: http://www.mysql.com/doc/S/E/SELECT.html Gurhan -Original Message- From: Dave [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 7:38 AM To: [EMAIL PROTECTED] Subject: Exporting Data from web page In simple terms I need to be able to click a link on a webpage which runs some SQL to produce a result set and then prompts the user to save this as a pipe delimited file onto their HDD. Or which does the saving to HDD without the prompt but it still needs to be triggered from the webpage. Can't find any info online, in lists or in my MYSQL book. Regards Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Order by date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 * and then Victoria Reznichenko declared NW and here is my select: NW $qry=SELECT id, module, week, ; NW $qry.=DATE_FORMAT(date, \%d %m %Y\) as date, ; The result of DATE_FORMAT() function is a string. So, in ORDER BY clause is used string 'date'. That is why you get wrong sorting. Use another alias for your date column, it should help you, i.e.: Yes! How strange, the penny dropped whilst I was making a cup of tea and I was sure I had it. Now I /know/ I do. Many thanks Victoria :-) sql - -- Nick Wilson // www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE81+GXHpvrrTa6L5oRAuMXAJ0clmtEYrYscsNlNgCktrN5AlPDfwCffgWS bfCr8rkBNl4YwtNo7TAbbWY= =/rKf -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL performance issues - PLEASE ADVICE!!
[snip] I am having performance problems with my MySQL installation - what would be an appropriate channel for requesting help to such a problem? [/snip] Repost your original concern and I will see if I can help. Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Number of affected rows
Hi, I'm using the C API for mysql and i wanna know how can i get the number of affected rows after a UPDATE statement. Thank u very much, Edilson. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Default-less columns ?
Hi, is it possible to have a column which does NOT have a default value, i.e. an INSERT query should fail if no value is given..? The obvious idea, i.e. CREATE TABLE foo (bar integer not null default null); doesn't work. -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: Converting text files to MySQL
Thanks, Egor. I still can't get it to work though. I added those lines under the [mysqld] and [client] sections - into my four .cnf files, they are my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf. I dind't have a my.cnf like you mentioned. Is there something else I should be doing? Thanks so much for your help! Egor Egorov wrote: Add in your my.cnf file rows: [mysqld] local-infile=1 [client] local-infile=1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL From Windows to Linux
Hi everybody. If I start a database using MySQL for Windows, can I later transfer it to MySQL for Linux by just copying the database directory. Or will I have redo it from scratch. What about my odcb connections will they continue to work without modification on the client side. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Apache::Session::MySQL
Greetings. I am new to MySQL and am starting to use Apache::Session::MySQL. I created a table as the instructions mention. CREATE TABLE sessions ( id CHAR(32) NOT NULL PRIMARY KEY, a_session TEXT ) ; I have also tried a_session as BLOB. I can set and retrieve values from the created sessions using CGI scripting. I just cannot see the data when I query MySQL from the mysql prompt when I telnet into the server. I have seen other threads elsewhere that mention this is not problem. I would feel more comfortable if I could see this data with a query. Or at least understand why. What am I missing? Thanks in advance, Michael Wick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Number of affected rows
At 11:42 -0300 5/7/02, Edilson Vasconcelos de Melo Junior wrote: Hi, I'm using the C API for mysql and i wanna know how can i get the number of affected rows after a UPDATE statement. mysql_affected_rows() is probably what you want. For more info, check the MySQL manual C API chapter: http://www.mysql.com/doc/C/C/C.html Thank u very much, Edilson. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: mysqld don't start up anymore
Our mysql-server is down and can't start anymore. He can't connect server through socket '/path/to//mysql.sock' (number) When I take a look in hostname.err I get this on www.sin.khk.be/~crashke/error.log you can find the latest error Jurgen Campforts Meidoornlaan 2/1 2200 Herentals 0496/60.25.75 http://www.scnoorderwijk.be - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: Converting text files to MySQL
Christy, You need to edit the defaults file for your MySQL server. It is by default, /etc/my.cnf , unless another one is specified when mysqld is started. The four files that you have mentioned comes with MySQL downloads to be used as an example... See: http://www.mysql.com/doc/O/p/Option_files.html Gurhan -Original Message- From: Christy Roberts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:02 AM To: 'Egor Egorov'; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL Thanks, Egor. I still can't get it to work though. I added those lines under the [mysqld] and [client] sections - into my four .cnf files, they are my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf. I dind't have a my.cnf like you mentioned. Is there something else I should be doing? Thanks so much for your help! Egor Egorov wrote: Add in your my.cnf file rows: [mysqld] local-infile=1 [client] local-infile=1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC connection to MySQL
On Tue, 7 May 2002, George Pitcher wrote: Date: Tue, 7 May 2002 06:15:28 +0100 From: George Pitcher [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: JDBC connection to MySQL Just a guess, but how about an ampersand? Connection c = DriverManager.getConnection(jdbc:mysql://localhost/dbName?user=usernamepas sword=password); Well this is what I use.. DriverManager.getConnection(this.DBUrl,this.dbuser,this.dbpasswd); where DBUrl = jdbc:mysql://localhost:3306/+database_name; and dbuser = StringofPermittedUser and dbpasswd = StringofCleartextPasswordforUser George - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:11 AM Subject: JDBC connection to MySQL Hello, I've been struggling with this for two weeks, I can't seem to find the answer wherever I look whether in the core mysql book or the documentation on what variables, if any go between parameters(user and password) as in: Connection c = DriverManager.getConnection(jdbc:mysql://localhost/dbName?user=username, password=password); what goes between the user parameter and the password parameter( a comma? a space? no space?). I presume since the mm.mysql driver (org.gjt.) is under the servlet directory and the servlets are working I don't need to set the classpath to the driver(i.e already set to the parent, servlet directory). Thanks in advance. Patrick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
One slow connection make mysql slow
I use mysql to register data in an accounting system, comunications happen in a 200 Mbit network and database is really fast, so lock situation ( ie delete after a long select ... ) is really fast and does not create problems. When I connect from a dialup connection obviously query take the same time but sending results to me may take 30 seconds. The problem is that mysql locks the table up to the end of the data sending process so other local client can't work. Is this a bug? Are there a logical explanation? Thanks for your help Luca mysql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using RAND()
The RAND() syntax does not appear to be working for me. What version mySQL is required? At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
which parameter to tune?
Hi - Before I start fiddling with buffer sizes and what not, is there any documented issue with truncated results of concat_ws() within joined tables, when using an order by clause? Using MySQL 3.23.48 on Linux. Will post SQL queries and table structure if there's not just an answer somewhere that I haven't yet found. Thanks much! - Julie -- Julie Meloni -- [EMAIL PROTECTED] -- www.thickbook.com Find Sams Teach Yourself MySQL in 24 Hours at http://www.amazon.com/exec/obidos/ASIN/0672323494/thickbookcom-20 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help! Deciding on Java/C++ ?
Hi all... I'm about to start heavy development of an application with MySQL at the backend. However, I need some help deciding on what language to approach the project from. What are the ACTUAL speed comparisons - JDBC vs MySQL++ ?? How is the speed of ODBC (Win vs Linux?)? Are there any existing base classes representing MySQL's Server / Database / Table / Field / Tuple? I need a solid object-oriented MySQL programming foundation before I start hacking at the project. I would rather not reinvent the wheel if such classes already exist. I would really appreciate any help, tips, etc. that you guys could provide. Much thanks! -Ryan Hatch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using RAND()
You have to have MySQL 3.23 or greater.. http://www.mysql.com/doc/M/a/Mathematical_functions.html Gurhan -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:33 AM To: [EMAIL PROTECTED]; Ulf Harnhammar Cc: [EMAIL PROTECTED] Subject: Using RAND() The RAND() syntax does not appear to be working for me. What version mySQL is required? At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help! Deciding on Java/C++ ?
If you really need the speed than go with C++. However, if it is a web application I would recommend you go with Java. The performance for JDBC is very decent. Don't forget, speed is also determined by your network. I hope this help -Original Message- From: Ryan Hatch [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 10:47 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Help! Deciding on Java/C++ ? Hi all... I'm about to start heavy development of an application with MySQL at the backend. However, I need some help deciding on what language to approach the project from. What are the ACTUAL speed comparisons - JDBC vs MySQL++ ?? How is the speed of ODBC (Win vs Linux?)? Are there any existing base classes representing MySQL's Server / Database / Table / Field / Tuple? I need a solid object-oriented MySQL programming foundation before I start hacking at the project. I would rather not reinvent the wheel if such classes already exist. I would really appreciate any help, tips, etc. that you guys could provide. Much thanks! -Ryan Hatch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL performance issues - PLEASE ADVICE!!
Hi, Don't give up.Try againso ... what's your problem? More details about your problem are welcome. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: webmaster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:13 PM Subject: MySQL performance issues - PLEASE ADVICE!! I am having performance problems with my MySQL installation - what would be an appropriate channel for requesting help to such a problem? I have posted to this list twice and another one as well. Volumes are very high on using MySQL/standard SQL questions, but not an single suggestion has been submitted to my request. Is the only valid alternative to go for an annual support deal with MySQL.org? My site can't afford that. Lots of enthusiastic 'community' members will be disappointed if it should go down because I cant afford proper support. Please respond if you have any suggestion where it might be wise to direct my request! All the best; Eivind :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newbie question (can I build on MySQL/InnoDB for my project)
Hi all, I'm in the process of evaluating MySQL/InnoDB for a big project. The data is composed from few tens millions of records with foreign keys used between tables. In the main table the search will be based on a string key (varchar(32)). The scenarios in the systems are: 1. Bulk update (initialization, once an hour). 2. ~10 updates per second. 3. ~1 queries per second. We'll also need replication (master-slave). From your experience can we achieve this on Netra computers running SunOS 5.8? The other natural selection is Oracle. How much memory will we need (cache buffers ?) Should we prefetch and search in the memory (in memory db?) or can we really work in front of the MySQL server? How many queries can we expect to be served simultaneously? What is the expected time for a single query (from the main table?) Thanks for your inputs, Zbenmo. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL From Windows to Linux
Hi, Yes. 1.You must shutdown mysqld from Windows and from Linux. 2.Copy files from Windows to Linux. ...optional shutdown Windows ... for ever.(Ha,ha,ha...just a joke) 3.Change owner of the files to mysql. 4.Check access permision to files and directory. Anyway, is recomended to use mysql utility administration software. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Yvon Darang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 4:04 PM Subject: MySQL From Windows to Linux Hi everybody. If I start a database using MySQL for Windows, can I later transfer it to MySQL for Linux by just copying the database directory. Or will I have redo it from scratch. What about my odcb connections will they continue to work without modification on the client side. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql (BMP)
Hola y de antemano gracias. Bien, estoy usando Redhat, C, Mysql. Desde un programa en C utilizo una base de datos de mysql por medio de las API. Necesito escribir los bytes de un fichero que puede ser de los siguientes tipos (BMP, jpg, gij, avi, wav), el campo que estoy utilizando es de tipo BLOB. la verdad es que esto me urge; o por lomenos saber si mysql lo permite Gracias... _ No olvides nada. Recuerda los cumpleaños, tus citas, tus cenas románticas y todo lo que quieras usando la Agenda personal de Wanadoo.es, desde cualquier lugar conectado a Internet. Visítala en http://www.wanadoo.es/agenda - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: One slow connection make mysql slow
Hi, My opinion: For sure is not a bug. Perhaps the client lock the table for read/write before access the table and remain locked until retrieve data. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Luca Vertua [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 6:34 PM Subject: One slow connection make mysql slow I use mysql to register data in an accounting system, comunications happen in a 200 Mbit network and database is really fast, so lock situation ( ie delete after a long select ... ) is really fast and does not create problems. When I connect from a dialup connection obviously query take the same time but sending results to me may take 30 seconds. The problem is that mysql locks the table up to the end of the data sending process so other local client can't work. Is this a bug? Are there a logical explanation? Thanks for your help Luca mysql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL From Windows to Linux
there are limitations of course but you could also just do a mysqldump of all the data and re-create the database -Don -Original Message- From: Gelu [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 12:31 PM To: Yvon Darang; [EMAIL PROTECTED] Subject: Re: MySQL From Windows to Linux Hi, Yes. 1.You must shutdown mysqld from Windows and from Linux. 2.Copy files from Windows to Linux. ...optional shutdown Windows ... for ever.(Ha,ha,ha...just a joke) 3.Change owner of the files to mysql. 4.Check access permision to files and directory. Anyway, is recomended to use mysql utility administration software. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Yvon Darang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 4:04 PM Subject: MySQL From Windows to Linux Hi everybody. If I start a database using MySQL for Windows, can I later transfer it to MySQL for Linux by just copying the database directory. Or will I have redo it from scratch. What about my odcb connections will they continue to work without modification on the client side. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using RAND()
Hi When I used the RAND() function (MySQL 3.23.45) I found the results were not very random, instead I use an extra bit of php code to make sure the results are more random. mt_srand((double)microtime()*100); $sql = select order by RAND(.mt_rand(0,32767).) limit 50 $rs = mysql_query($sql); $num=mysql_num_rows($rs); if($num1) mysql_data_seek($rs,mt_rand(0,$num-1)); $row=mysql_fetch_array($rs); To get a random record from earlier versions of MySQL you could do something like this. mt_srand((double)microtime()*100); $sql = select... ..where limit 0,50 $rs = mysql_query($sql); $num=mysql_num_rows($rs); if($num1) mysql_data_seek($rs,mt_rand(0,$num-1)); $row=mysql_fetch_array($rs); if you have a lot of records you can always put random numbers in the limit clause, (select ... where ...limit random_number, random_number + 50) this will return a random block of records, then use the seek functions to randomly pick a record from the record block, just make sure random_number isn't greater than the total records in your recordset. Pete Kelly - Original Message - From: Gurhan Ozen [EMAIL PROTECTED] To: Cummings, Shawn (GNAPs) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:11 PM Subject: RE: Using RAND() You have to have MySQL 3.23 or greater.. http://www.mysql.com/doc/M/a/Mathematical_functions.html Gurhan -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:33 AM To: [EMAIL PROTECTED]; Ulf Harnhammar Cc: [EMAIL PROTECTED] Subject: Using RAND() The RAND() syntax does not appear to be working for me. What version mySQL is required? At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: One slow connection make mysql slow
Ok, I'm agree with you, someone know how to avoid this? Why mysql does not retrive data in the buffer before send them on network? My query is about 400 Kbyte. Luca - Original Message - From: Gelu [EMAIL PROTECTED] To: Luca Vertua [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 6:45 PM Subject: Re: One slow connection make mysql slow Hi, My opinion: For sure is not a bug. Perhaps the client lock the table for read/write before access the table and remain locked until retrieve data. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Luca Vertua [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 6:34 PM Subject: One slow connection make mysql slow I use mysql to register data in an accounting system, comunications happen in a 200 Mbit network and database is really fast, so lock situation ( ie delete after a long select ... ) is really fast and does not create problems. When I connect from a dialup connection obviously query take the same time but sending results to me may take 30 seconds. The problem is that mysql locks the table up to the end of the data sending process so other local client can't work. Is this a bug? Are there a logical explanation? Thanks for your help Luca mysql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help! Deciding on Java/C++ ?
Hi, My opinion: If you wish to made a very safe application use C/C++ with API function. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Ryan Hatch [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 6:50 PM Subject: Help! Deciding on Java/C++ ? Hi all... I'm about to start heavy development of an application with MySQL at the backend. However, I need some help deciding on what language to approach the project from. What are the ACTUAL speed comparisons - JDBC vs MySQL++ ?? How is the speed of ODBC (Win vs Linux?)? Are there any existing base classes representing MySQL's Server / Database / Table / Field / Tuple? I need a solid object-oriented MySQL programming foundation before I start hacking at the project. I would rather not reinvent the wheel if such classes already exist. I would really appreciate any help, tips, etc. that you guys could provide. Much thanks! -Ryan Hatch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: Converting text files to MySQL
Thanks, Gurhan. I don't seem to have a my.cnf - the only cnf files I have are the four samples - so can I just use one of the sample ones, change the name, and add the local-infile =1 line? -Original Message- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 12:58 PM To: Christy Roberts; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL Christy, You need to edit the defaults file for your MySQL server. It is by default, /etc/my.cnf , unless another one is specified when mysqld is started. The four files that you have mentioned comes with MySQL downloads to be used as an example... See: http://www.mysql.com/doc/O/p/Option_files.html Gurhan -Original Message- From: Christy Roberts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:02 AM To: 'Egor Egorov'; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL Thanks, Egor. I still can't get it to work though. I added those lines under the [mysqld] and [client] sections - into my four .cnf files, they are my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf. I dind't have a my.cnf like you mentioned. Is there something else I should be doing? Thanks so much for your help! Egor Egorov wrote: Add in your my.cnf file rows: [mysqld] local-infile=1 [client] local-infile=1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: One slow connection make mysql slow
Hi, I think it's better to lock the table before to delete.If you offer more details maybe we can help. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Luca Vertua [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 7:52 PM Subject: Re: One slow connection make mysql slow Ok, I'm agree with you, someone know how to avoid this? Why mysql does not retrive data in the buffer before send them on network? My query is about 400 Kbyte. Luca - Original Message - From: Gelu [EMAIL PROTECTED] To: Luca Vertua [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 6:45 PM Subject: Re: One slow connection make mysql slow Hi, My opinion: For sure is not a bug. Perhaps the client lock the table for read/write before access the table and remain locked until retrieve data. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Luca Vertua [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 6:34 PM Subject: One slow connection make mysql slow I use mysql to register data in an accounting system, comunications happen in a 200 Mbit network and database is really fast, so lock situation ( ie delete after a long select ... ) is really fast and does not create problems. When I connect from a dialup connection obviously query take the same time but sending results to me may take 30 seconds. The problem is that mysql locks the table up to the end of the data sending process so other local client can't work. Is this a bug? Are there a logical explanation? Thanks for your help Luca mysql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Getting difference between two datatime values
The following code does not work because time_to_sec() works on the 24 hour clock and cannot work between 2 days. For example a session started at 11:59 PM will not carry forward until 12:01 AM based on this logic. Does anyone have a better solution for me? David McInnis $sql = select *, (time_to_sec(now()) - time_to_sec(dt_expire)) as elapsed from sessions_client where sessionid = '$cp_sessionid'; $result = @mysql_query($sql, $connection) or die ('Database Error - Could not query sessions_client.'); $numrows = mysql_num_rows($result); if ($numrows == 1) { $data = mysql_fetch_array($result); $clientid = $data[clientid]; $dt_lastaccess = $data[dt_lastaccess]; $elapsed= $data[elapsed]; if ($elapsed 0) { // validate session // this is where the logic fails around midnight } } else { // expired session } SQL and Query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using RAND()
At 11:32 -0400 5/7/02, Cummings, Shawn (GNAPs) wrote: The RAND() syntax does not appear to be working for me. What version mySQL is required? 3.23.2. Prior to that, use SELECT something, any_col*0+RAND() AS r FROM sometable WHERE ... ORDER BY r LIMIT 10 At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Bug or error in SQL syntax - Specifying and using a user variableinside a single select query
Hi, Gregg Graubins wrote: Gregg, Monday, May 06, 2002, 6:57:55 PM, you wrote: GG Upon reading into the user variables section of the mysql manual, I cut cut According to the manual from the above link, where it states: cut mysql SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +--+--+--+--+ | @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 | +--+--+--+--+ |5 |5 |1 |4 | +--+--+--+--+ == mysql should be SET'ing the user variables in my query above. If mysql is processing the WHERE first and then the SELECT, shouldn't the user variables be set anyhow (even if the query returns an empty set)? For example: == mysql SELECT -annual_filing.*, -companies.name AS company_name, -YEAR(filing_date) AS filing_year, -@predictedYear := 1999 AS predictedYear, -@earliestYear := 1996 AS earliestYear, -@learnYear := 1998 AS learnYear - FROM -annual_filing, -companies - WHERE - annual_filing.company_id = companies.ds_id -AND - YEAR(filing_date) = @predictedYear -AND - YEAR(filing_date) = @earliestYear; Empty set (25.89 sec) mysql SELECT @predictedYear, @earliestYear, @learnYear; ++---++ | @predictedYear | @earliestYear | @learnYear | ++---++ | NULL | NULL | NULL | ++---++ 1 row in set (0.00 sec) == If I execute a separate SELECT statement beforehand (Or SET for that matter) setting the user variables then it works. However, I'm trying to use MyODBC to connect into the server with my data mining software and I can't perform multiple queries in this fashion (it expects me to only send one query). :( Perhaps there's a workaround? That is correct result. Imagine you have to process this statement manually. Think about order of execution. At 1st step WHERE clause must be processed to find matching rows At 2nd step evrything in SELECT part nust be processed Do you agree with above? If so it must be clear that in you case order in time will be: 1. WHERE ,.. AND YEAR(filing_date) = @predictedYear 2. SELECT (@predictedYear := 1999) That is why in Where clause your variable is undefined. The same reason prevents you from using grouping functions in WHERE clause like SELECT col ... WHERE col = MAX(col); So as Victoria already suggested you must initialize User variables Before select statement. Or just use common API techniques to generate SQL statement on the fly. I don't see any benefits of using User Variables in your case. Ofcourse you can 'initialize' each variable twice in every statement like: SELECT (@var1 := 1999) as ... WHERE YEAR(filing_date) = (@var1 := 1999) but don't believe it will save you any effort. -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: Converting text files to MySQL
are you sure you don't have a my.cnf file in /etc dir? Who has installed, started MySQL server? If no default files are given, mysqld will read options from /etc/my.cnf . Gurhan -Original Message- From: Christy Roberts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 1:00 PM To: 'Gurhan Ozen'; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL Thanks, Gurhan. I don't seem to have a my.cnf - the only cnf files I have are the four samples - so can I just use one of the sample ones, change the name, and add the local-infile =1 line? -Original Message- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 12:58 PM To: Christy Roberts; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL Christy, You need to edit the defaults file for your MySQL server. It is by default, /etc/my.cnf , unless another one is specified when mysqld is started. The four files that you have mentioned comes with MySQL downloads to be used as an example... See: http://www.mysql.com/doc/O/p/Option_files.html Gurhan -Original Message- From: Christy Roberts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:02 AM To: 'Egor Egorov'; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL Thanks, Egor. I still can't get it to work though. I added those lines under the [mysqld] and [client] sections - into my four .cnf files, they are my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf. I dind't have a my.cnf like you mentioned. Is there something else I should be doing? Thanks so much for your help! Egor Egorov wrote: Add in your my.cnf file rows: [mysqld] local-infile=1 [client] local-infile=1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help! Deciding on Java/C++ ?
This question is purely academic, surely you need to investigate what the system is required to do in terms of processing (Batch/Transaction, GUI Front-end etc.) and then base your decision against the language which would suit the type of processing required. Perhaps you will need to use a bit of both. Gelu, it would appear, is giving you his preferred language and that may not suit the needs of your application. Go and look at Mark Matthews JDBC drivers for Java, and perform some speed tests on the platforms you are likely to use in both development and a release situation. Perform a comparable test using ODBC, and see which suits you better. A good system is written in the spec, not the code, so you're right not to rush in head long. Cheers Christopher Marsh-Bourdon -Original Message- From: Gelu [mailto:[EMAIL PROTECTED]] Sent: 07 May 2002 17:57 To: Ryan Hatch; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Help! Deciding on Java/C++ ? Hi, My opinion: If you wish to made a very safe application use C/C++ with API function. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Ryan Hatch [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 6:50 PM Subject: Help! Deciding on Java/C++ ? Hi all... I'm about to start heavy development of an application with MySQL at the backend. However, I need some help deciding on what language to approach the project from. What are the ACTUAL speed comparisons - JDBC vs MySQL++ ?? How is the speed of ODBC (Win vs Linux?)? Are there any existing base classes representing MySQL's Server / Database / Table / Field / Tuple? I need a solid object-oriented MySQL programming foundation before I start hacking at the project. I would rather not reinvent the wheel if such classes already exist. I would really appreciate any help, tips, etc. that you guys could provide. Much thanks! -Ryan Hatch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using RAND()
Quoted from the manual page I have given below is: RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version. So it was intentionally built so... Gurhan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 12:55 PM To: [EMAIL PROTECTED] Subject: Re: Using RAND() Hi When I used the RAND() function (MySQL 3.23.45) I found the results were not very random, instead I use an extra bit of php code to make sure the results are more random. mt_srand((double)microtime()*100); $sql = select order by RAND(.mt_rand(0,32767).) limit 50 $rs = mysql_query($sql); $num=mysql_num_rows($rs); if($num1) mysql_data_seek($rs,mt_rand(0,$num-1)); $row=mysql_fetch_array($rs); To get a random record from earlier versions of MySQL you could do something like this. mt_srand((double)microtime()*100); $sql = select... ..where limit 0,50 $rs = mysql_query($sql); $num=mysql_num_rows($rs); if($num1) mysql_data_seek($rs,mt_rand(0,$num-1)); $row=mysql_fetch_array($rs); if you have a lot of records you can always put random numbers in the limit clause, (select ... where ...limit random_number, random_number + 50) this will return a random block of records, then use the seek functions to randomly pick a record from the record block, just make sure random_number isn't greater than the total records in your recordset. Pete Kelly - Original Message - From: Gurhan Ozen [EMAIL PROTECTED] To: Cummings, Shawn (GNAPs) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:11 PM Subject: RE: Using RAND() You have to have MySQL 3.23 or greater.. http://www.mysql.com/doc/M/a/Mathematical_functions.html Gurhan -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:33 AM To: [EMAIL PROTECTED]; Ulf Harnhammar Cc: [EMAIL PROTECTED] Subject: Using RAND() The RAND() syntax does not appear to be working for me. What version mySQL is required? At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help! Deciding on Java/C++ ?
- Original Message - From: Ryan Hatch [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 10:50 AM Subject: Help! Deciding on Java/C++ ? Hi all... I'm about to start heavy development of an application with MySQL at the backend. However, I need some help deciding on what language to approach the project from. What are the ACTUAL speed comparisons - JDBC vs MySQL++ ?? How is the speed of ODBC (Win vs Linux?)? They are not directly comparable. However I would venture that JDBC is comparable to the C++ speed for most applications, as you will be waiting on I/O. In my opinion, there are more class libraries out there for doing web-based applications in Java, but more C++-based class libraries for doing thick-client type applications, so that might skew what the appropriate choice is. -Mark Are there any existing base classes representing MySQL's Server / Database / Table / Field / Tuple? I need a solid object-oriented MySQL programming foundation before I start hacking at the project. I would rather not reinvent the wheel if such classes already exist. These classes for the most part come built-in with JDBC. -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
why mysql doesn't want to use my indexes???
Hi! I have a little pb with indexes. when I make an explain on my query I have strange results. my query looks like this select * from LIST l, WIN c where l.ID_LIST=c.ID_LIST and l.KEY = '421' and l.ACTIF='1'; table LIST : ID_LIST(primary key), KEY, ACTIF, ... table WIN ID_WIN(primary key), ID_LIST, ... there is 2 indexes. One on (LIST.KEY, LIST.ACTIF) and one on (WIN.ID_LIST) the pb is that explain returns like this : |table|type|possible_keys|key|key_len|ref|rows|Extra |l|ref|PRIMARY,idx_key_actif|idx_key_actif|20|const,const|1|where used; Using index| |c|ALL|ID_LIST |NULL|NULL|NULL|5|where used| which means (if I understood the mysql manual...) it prefers not to use the index from c(WIN table) and scan the whole table for each row from LIST... let me say it's not what I want... can someone explain me why? Internet et mail gratuit AltaVista http://www.altavista.fr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: Converting text files to MySQL
When I do a search on my entire machine, the only 4 cnf files it finds are those four samples. Where should the file be? I installed and started MySQL myself but I did it under an administrator account. I'm now running under my own account...would this make a difference? -Original Message- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 2:56 PM To: Christy Roberts; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL are you sure you don't have a my.cnf file in /etc dir? Who has installed, started MySQL server? If no default files are given, mysqld will read options from /etc/my.cnf . Gurhan -Original Message- From: Christy Roberts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 1:00 PM To: 'Gurhan Ozen'; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL Thanks, Gurhan. I don't seem to have a my.cnf - the only cnf files I have are the four samples - so can I just use one of the sample ones, change the name, and add the local-infile =1 line? -Original Message- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 12:58 PM To: Christy Roberts; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL Christy, You need to edit the defaults file for your MySQL server. It is by default, /etc/my.cnf , unless another one is specified when mysqld is started. The four files that you have mentioned comes with MySQL downloads to be used as an example... See: http://www.mysql.com/doc/O/p/Option_files.html Gurhan -Original Message- From: Christy Roberts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:02 AM To: 'Egor Egorov'; [EMAIL PROTECTED] Subject: RE: RE: Converting text files to MySQL Thanks, Egor. I still can't get it to work though. I added those lines under the [mysqld] and [client] sections - into my four .cnf files, they are my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf. I dind't have a my.cnf like you mentioned. Is there something else I should be doing? Thanks so much for your help! Egor Egorov wrote: Add in your my.cnf file rows: [mysqld] local-infile=1 [client] local-infile=1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php