Re: innodb deadlock leads to server crash
Hi! Your email address gives the error: DNS for host dev.noris.de is mis-configured The following recipients did not receive this message: [EMAIL PROTECTED] Still one question: is this a deadlock of threads at all? Maybe the sorting which mysqld does, or the fetches which: mysql -q ... | program executes take so long that InnoDB thinks the server has hung? How big is the result set of your ORDER BY query in terms of rows and megabytes? Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:05 AM Subject: Re: innodb deadlock leads to server crash Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? 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 sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting index read MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 updater preparing select timestamp,seq,wann from ticketlast where ticket = '1823' and person = '3 406' ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 , holds adaptive hash latch MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 updater Sending data select timestamp,ticket,person from ticketlast where timestamp = FROM_UNIXTIME (916760612) order by ticket,person Trx read view will not see trx with id = 0 2108138, sees 0 2108138 How-To-Repeat: Create a table with suitably many records. mysql -q -e select id from FOO order by id | program The program would do a loop with select * from FOO where id=$ID, and do an occasional UPDATE. Fix: the two processes should not block each other. Dropping the -q is not a solution; the table is too large. Submitter-Id: submitter ID Originator: Organization: noris network AG, Nuernberg, Germany MySQL support: license Synopsis: innodb deadlock Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.56 Environment: System: Linux dev1.dev.noris.de
Re: innodb deadlock leads to server crash
Hi! Ok, now I think I understand the problem. Since you use the -q option, the client mysql retrieves rows in the result set in chunks of some size. As InnoDB keeps the adaptive hash index latch S-locked till the end of the the big SELECT ... ORDER BY ... query, it will probably be S-latched when you start retrieving the rows. And then you cannot perform much further operations in the database because of the S-latch! As you pointed out, a workaround would be to remove the -q option, since then mysql would retrieve all rows in one chunk and release the S-latch. Hmm... the underlying problem is that InnoDB does not know when mysqld moves control to the client, and does not know to release the S-latch then. I have to check if I can see when mysql_use_result() is used to retrieve the result in smaller chunks. I could then release the S-latch always when the program control leaves InnoDB. Thank you, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:24 AM Subject: Re: innodb deadlock leads to server crash Hi! Your email address gives the error: DNS for host dev.noris.de is mis-configured The following recipients did not receive this message: [EMAIL PROTECTED] Still one question: is this a deadlock of threads at all? Maybe the sorting which mysqld does, or the fetches which: mysql -q ... | program executes take so long that InnoDB thinks the server has hung? How big is the result set of your ORDER BY query in terms of rows and megabytes? Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:05 AM Subject: Re: innodb deadlock leads to server crash Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? 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 sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326
MysqlCC
Hello, In MySQL Control Center 0.9.10 you cannot add an hotkey for cell edit (double click on cell to edit it's content). I think that adding a key for this is very important so the user will not need the mouse to edit cells. Sorin Sbarnea - 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
optimizer bug in the index used by mysql/Innodb in the search
Description: Hello Peter, I have sent to ftp://support.mysql.com/pub/mysql/secret the table definition and data (Giros.txt) in a compressed file named Giros.zip so you can invetigate it. You can see the following queries: SELECT TIPO,DOC,NRE FROM GIROS WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE'01/01') OR (GIROS.TIPO='R' AND GIROS.DOC'ZA03003996') OR GIROS.TIPO'R' ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC LIMIT 1 InnoDB Time: 0.20 secs. Explain: table type possible_keys keykey_len ref rows Extra GIROS range PRIMARY,TipoFeVCod TipoFeVCod1 NULL 9417 Using where; Using index; Using filesort MYISAM Time:0.02 secs Explain: table type possible_keys keykey_len ref rows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16NULL 19472 Using where; Using index --- SELECT TIPO,DOC,NRE FROM GIROS WHERE CONCAT(TIPO,DOC,NRE)='RZA0300399601/01' ORDER BY TIPO DESC,DOC DESC,NRE DESC LIMIT 1 INNODB/MyIsam Time: 0.02 secs. INNODB/MyIsam Explain: table type possible_keys key key_len ref rows Extra GIROS index NULL PRIMARY 16 NULL 19516 Using where; Using index Both MyIsam and InnoDB return the record in the same time and use the same index PRIMARY. This query is the same as the above query. --- Also, in this query (I deleted the third condition of the where clause TIPO'R') SELECT TIPO,DOC,NRE FROM GIROS WHERE (TIPO='R' AND DOC='ZA03003996' AND NRE'01/01') OR (TIPO='R' AND DOC'ZA03003996') ORDER BY TIPO DESC, DOC DESC,NRE DESC limit 1 Both MyIsam and InnoDB use the index PRIMARY. InnoDB Time:0.03 secs. Explain: table type possible_keys key key_len refrows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 1 Const 1933 Using where; Using index MyIsam Time: 0.02 secs. table type possible_keys key key_len refrows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16 Null 3910 Using where; Using index I don´t understand why if I add the third condition: OR TIPO'R' InnoDB isn´t still using the PRIMARY INDEX. --- Finally, SELECT TIPO,DOC,NRE FROM GIROS WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE'01/01') OR (GIROS.TIPO='R' AND GIROS.DOC'ZA03003996') OR GIROS.TIPO'R' ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC There is no LIMIT. Table type MyIsam: Returned records: 19486 in 0.59 secs. Explain: table type possible_keys key key_len ref rows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16 NULL 19472 Using where; Using index Table type InnoDb: Returned records: 19486 in 1.18 secs. Explain: table type possible_keys key key_len ref rows Extra GIROS range PRIMARY,TipoFeVCod TipoFeVCod 1 NULL 9417 Using where; Using index; Using filesort With InnoDB, the optimizer believes it must examine 9417 records, but actually it returns 19486 records, the same records as MyIsam but twice slower, so I think the optimizer is okey in MyIsam and wrong in InnoDB. Thanks in advance, Rafa How-To-Repeat: Select ... from giros ... Fix: - Synopsis:optimizer bug in the index used by mysql/Innodb in the search Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.11 Gamma(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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
problem: MySql: Got signal 11. Aborting!
Hello, I installed MySQL version 4.0.7-gamma on WinXP Professional couple weeks ago and I usually connect to the server through ODBC drivers. Last time I tried to stop server through panel Services and it lasted very long time and after that I was not able to start the server again After restart it seems in Services like it is working but i cannot connect to the server any more. Here are last lines from mysql.err: MySql: ready for connections 030227 7:58:11 InnoDB: Started 030228 15:38:13 MySql: Normal shutdown 030228 15:38:14 MySql: Forcing close of thread 28 user: 'root' 030228 15:38:14 MySql: Forcing close of thread 5 user: 'root' 030228 15:38:14 InnoDB: Starting shutdown... 030228 15:38:18 InnoDB: Shutdown completed 030228 15:38:18 MySql: Shutdown Complete 030228 15:39:59 MySql: Got signal 11. Aborting! 030303 7:35:10 MySql: Got signal 11. Aborting! 030303 9:07:13 MySql: Got signal 11. Aborting! Please, could someone help me with this problem or at least give me some idea hot to solve it? Thanks in advance Dusan --- Odchozí zpráva neobsahuje viry. Zkontrolováno antivirovým systémem AVG (http://www.grisoft.cz). Verze: 6.0.459 / Virová báze: 258 - datum vydání: 25.2.2003 - 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: problem: MySql: Got signal 11. Aborting!
At 10:21 03/03/2003 +0100, Dusan Pavlica wrote: Hi, Hello, I installed MySQL version 4.0.7-gamma on WinXP Professional couple weeks ago and I usually connect to the server through ODBC drivers. Last time I tried to stop server through panel Services and it lasted very long time and after that I was not able to start the server again After restart it seems in Services like it is working but i cannot connect to the server any more. Here are last lines from mysql.err: MySql: ready for connections 030227 7:58:11 InnoDB: Started 030228 15:38:13 MySql: Normal shutdown 030228 15:38:14 MySql: Forcing close of thread 28 user: 'root' 030228 15:38:14 MySql: Forcing close of thread 5 user: 'root' The line above should means that during the shutdown process, the server found clients connected and tries to close them. Are you using the server with --enable-named-pipes ? -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ São Paulo - Brazil /_/ /_/\_, /___/\___\_\___/ ___/ 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
Problems with Berkeley DB support in version 3.23.55
Hallo, we have downloaded the new 3.23.55 mysql DB from you as Source Download Tarball (tar.gz) and want to build them in our environment for tests under SUN OS. We have tared the downloaded version in our Scource Directory environment. (for example directory/mysql/src/mysql-3.23.55) After that we want configure the downloaded version from our bulid and configure environment. (for example directory/mysql/obj/mysql-3.23.55). We take the ./configure from the obj-Directory: directory/mysql/src/mysql-3.23.55/configure --with-berkeley-db to have berkeley DB support. After we take the make command we become some errors at the make output. (Sorry, but I have cancelled the make error output and so I can't write the error output to you.) To fix the problem I look in the config.status file, and there I found entries for the bdb variables: s,@bdb_includes@, s,@bdb_libs@, s,@bdb_libs_with_path@, In the config.status file I found only entries for the src-path: s,@bdb_includes@,-I,directory/mysql/src/mysql-3.23.55/bdb/build_unix,;t t s,@bdb_libs@,-L,directory/mysql/src/mysql-3.23.55/bdb/build_unix,;t t s,@bdb_libs_with_path@,directory/mysql/src/mysql-3.23.55/bdb/build_unix/libdb.a,;t t And so I missed entries for the obj path. After that I take in the config.status file the new entries for the obj-path: s,@bdb_includes@ -Idirectory/mysql/obj/mysql-3.23.55/bdb/build_unix s,@bdb_libs@ -Ldirectory/mysql/obj/mysql-3.23.55/bdb/build_unix s,@bdb_libs_with_path@ directory/mysql/obj/mysql-3.23.55/bdb/build_unix/libdb.a Then I make a new ./configure call with the option --enable-maintainer-mode and after taken make the make output goes without errors an so I can fix the problem. Greetings, Carsten Thoene Carsten Thoene eMail: [EMAIL PROTECTED] Technische Fakultaet Universitaet Bielefeld D-33594 Bielefeld - 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
Hit on Reg.Expression like in Perl
Hi, I wonder if something like in perl is possible: data in rows: 1) a12345xb54321x 2) b54321xa12333x I want to extract b(number) and store into another table. In perl this would work with pattern matching (reg.expressions) like: $data =~ s/.*?b(.*?)x.*?/$1/; $data_b = $1; so, reg.expressions in MySQL also works as I know, but how could I get the $1 to go further and store a substring of the hit into another table? Regards, Reiner - 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
Problems with Innobase DB support in version 3.23.55
Hallo, we have downloaded the new 3.23.55 mysql DB from you as Source Download Tarball (tar.gz) and want to build them in our environment for tests under SUN OS. We have tared the downloaded version in our Scource Directory environment. (for example directory/mysql/src/mysql-3.23.55) After that we want configure the downloaded version from our bulid and configure environment. (for example directory/mysql/obj/mysql-3.23.55). We take the ./configure from the obj-Directory-Path: directory/mysql/src/mysql-3.23.55/configure --with-innodb (to have Innobase DB support). After we take the make command we become some errors at the make output. For example a little Extract from the make output: directory/mysql/src/mysql-3.23.55/innobase/os/os0proc.c:10:21: os0proc.h: No such file or directory directory/mysql/src/mysql/mysql-3.23.55/innobase/os/os0proc.c:19:20: ut0mem.h: No such file or directory directory/mysql/src/mysql-3.23.55/innobase/os/os0proc.c:28: parse error before n directory/mysql/src/mysql-3.23.55/innobase/os/os0proc.c: In function `os_mem_alloc_nocache': directory/mysql/src/mysql/mysql-3.23.55/innobase/os/os0proc.c:39: `n' undeclared (first use in this function) Then I begin to look in the mysql scources to fix the problem. (for example directory/mysql/src/mysql-3.23.55/innobase) And in the file for example: directory/mysql/src/mysql-3.23.55/innobase/usr/Makefile.in here I think I found the mistake: (extract from the Makefile.in): $(srcdir)/Makefile.in: @MAINTAINER_MODE_TRUE@ Makefile.am $(srcdir)/../include/Makefile.i $(top_srcdir)/configure.in $(ACLOCAL_M4) The same error I found under: directory/mysql/src/mysql-3.23.55/innobase/os/Makefile.in I looked to the whole Scource Code and there I can't find a directory with the name Makefile.i And so for our test's with the 3.23.55 mysql we decide to built the software only in the scource tree directory, and not in the obj tree directory, to expect the mysql version can build there with Innobase and Berkeley DB support without errors. Greetings, Carstn Thoene -- Carsten Thoene eMail: [EMAIL PROTECTED] Technische Fakultaet Universitaet Bielefeld D-33594 Bielefeld - 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: The diferences about /dev/hda1 and access via raw device
At 18:35 Uhr -0200 23.01.2003, Dyego Souza do Carmo wrote: /dev/hda1:20Gnewraw /dev/raw1:20Gnewraw The speed is more fast in the last case or no ? In my tests which I did half a year ago with mysql 3.X it depended on the usage pattern. The advantage of using hda1 (buffered raw partition) is that linux (you're talking about linux right?) dynamically handles buffering. So if you are frequently accessing more memory than you have configured in innodb_buffer_pool, in the hda1 case linux will deliver data from linux' cache (if linux does have enough ram to buffer it) and thus will be faster than the raw1 case where data will be fetched from disk again. If, on the other side, you configure innodb_buffer_pool as big as perhaps 3/4 of all available RAM, linux will not be able to deliver from RAM either since it's own buffers are too small, and the raw1 case starts to be faster because of lower overhead. So on a dedicated mysql machine where +-nothing else than innodb is running, the raw1 case will (probably) gain you some advantage. The difference is not very big, though. I.e. if you are careful configuring your setup you should gain something but if you are not then you will loose much more. I thought I had sent my results to the list, but I just realized that I never sent that mail. Well I think I didn't because it's though to write correctly down what you have seen without making wrong conclusions, and also because just after writing it i did some more tests, and the test scripts were (and are still) not prepared to be published either. Maybe it's still worth something, see below. Christian. Date: Mon, 22 Jul 2002 23:59:59 +0100 To: Heikki Tuuri [EMAIL PROTECTED], [EMAIL PROTECTED] From: Christian Jaeger [EMAIL PROTECTED] Subject: Re: Innodb and unbuffered raw io on linux? I'll test it more thoroughly in the next days. mysql 3.23.51 with your patches worked without problems in my tests. But, performance seemed generally rather (a bit) worse than with OS buffered IO (= using /dev/sdaX). - Batches of 1000 inserts of ~2kb data and a commit took about 8% more time with rawio (/dev/raw/raw1) than buffered (/dev/sdaX). Though I must say I did take another partition on the same disk for this measurement (it seems that the 'raw' tool, at least the one from Debian, cannot unmap a raw device once mapped onto a block device, and so I had to use two different partitions of equal size, one mapped to /dev/raw/raw1, the other not mapped), so the disk itself could have made this difference. - The biggest difference was (as expected) when doing full table scans on tables bigger than what seemed to fit into the innodb buffer pool. The first scanning query took the same amount of time in both cases (~10 seconds), whereas with OS buffered IO the subsequent scans took only about 6 seconds, but unbuffered IO still 10 seconds for each run. Increasing innodb_buffer_pool_size from 90MB to 290MB changed it so that also with rawio the subsequent queries profit from cached data: (the test machine has 512MB RAM, runs linux 2.4.17, one seagate SCSI disk, 1Ghz PentiumIII) Query time for the first runs after a fresh mysqld start [s] *) 90MB buffer pool: /dev/raw/raw1 10.60 11.09 11.01 /dev/sda10 10.67 6.82 6.80 290MB buffer pool: /dev/raw/raw19.53 4.18 4.17 3.96**) /dev/sda109.48 3.98 3.96 *) note that kernel 2.4 drops it's own buffered pages when the last application closes the buffer device, so it's enough to restart mysqld and not necessary to reboot the machine before subsequent test runs (there's no cache left over). **) this last number is from a different run çç .. ist the autoadapting secondlevel cache. So if I conclude correctly this means: (no big news) 1. if there are no applications besides mysql running on the machine, it's important to make the buffer cache just as big as possible, regardless of whether rawio is used or not. In this scenario there's no compelling reason for rawio either. 2. if there *are* other applications besides mysql, then there may be two possible strategies: a) to set the innodb_buffer_pool_size to exactly the size of the working set, and use rawio. Thus innodb keeps everything in it's own memory, and the OS doesn't spend cache memory for useless duplicates which makes more memory available to the other apps. b) to set the innodb_buffer_pool_size smaller, but use normal buffered /dev/sd* devices (or files in the filesystem), thus making the kernel buffer what doesn't fit into the innodb buffer. Some data will be buffered twice, though. I haven't made tests with other applications competing with mysql for ram. I also haven't tested using raw partitions or unbuffered IO for the logs. Would this work, or make sense? Are the logs cached in innodb as well? I've noticed some memory pressure (?, at least emacs has been swapped out) when using a 290MB buffer pool and 2*125M logs, so with big logs one should maybe compensate
RE: Connector/J
What is Connector/J. Is it just the program which runs on the server? What GUI do you use for mysql? Gustavo. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 03 March 2003 13:10 To: Gustavo Cebrian Subject: Re: Connector/J Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: What is Connector/J. Is it just the program which runs on the server? What GUI do you use? Gustavo. - 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
max_data_length - impact of very large value
Hi all, In one of our customers databases there is table that have an extremely high max_data_length of about 256TB(!). Another have 1 TB, while the rest is at 4GB. (all of these tables are basically the same, but with these two as the ones withthe most data). The largest table is currently at about 8.7 GB. Is there any practical consequences of having such overly large max_table_length? Or is max_data_length mostly useful as a way to ensure that disk don't run full or as a quota system? And if there is, what is the more practical way reduce the value to something remotely sane. -sig sql,query,queries,smallint -- Sigurd Urdahl - 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
MyOLEDB / ADO
Environment: Windows XP Home MySQL 3.23.55 MDAC 2.7 MyOLEDB 3.0 VC++ 6.0 I use MySQL from ADO using MyOLEDB. Everything worked fine so far till I started using SQL joins. My application is pure breeded C++, but for demonstration purposes I rewrote the essential part in VB: Set cnn = New ADODB.Connection ' ' Open Connection ' cnn.Provider = MySqlProv cnn.ConnectionString = test cnn.CommandTimeout = 1 cnn.ConnectionTimeout = 1 cnn.CursorLocation = adUseServer cnn.Open ' ' Set recordset variable Set rs = New ADODB.Recordset ' Open Recordset rs.CacheSize = 100 rs.Open SELECT t1.* FROM t1 INNER JOIN t2 ON t1.f1=t2.f1, _ cnn, adOpenStatic, adLockOptimistic, adCmdText ' ...do interesting stuff here... This simple program fails at the Open statement with error code Operation aborted. For a test I typed in the same SELECT statement in the MySQL command line and it worked just fine. I also replaced the SELECT statement by a statement without a JOIN clause. This also worked properly. To see what ADO hands down to MySQL I enabled logging in MySQL. Here is an excerpt of the log file: 030303 15:16:54 18 Connect [EMAIL PROTECTED] as anonymous on test 18 Query show tables 18 Field List t1 18 Query show keys from t1 18 Field List t2 18 Query show keys from t2 18 Query show tables 18 Field List t1 18 Query show keys from t1 18 Field List t2 18 Query show keys from t2 18 Quit 19 Connect [EMAIL PROTECTED] as anonymous on test 19 Query SELECT t1.* FROM t1 INNER JOIN t2 ON t1.f1 = t2.f1 Before I posted this message, I searched the MySQL archives and I found one message back in 2001 that describes exactly the same symptoms. But the former sender didn't receive any responses, no answers. Is there any known solution to solve this problem? Anything I could try for further testing? Thanks in advance, any help greatly appreciated Peter _ MSN Groups Chat - Freunde finden - leicht gemacht http://groups.msn.com/people/ - 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
Data mining
Hello, Does anybody use MySQL for dataminig such as clustering or decision trees? Is there anything that can be integrated with the database or do you use some other software? Thank you With regards, Pavel Kusch - 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
Table Create Defaults (Type=InnoDB)?
I would like to create a Table default (Type=InnoDB) for a particular database. Is there a way to do this, or is this being planned for in a future release? For most of my databases, I am only interested in transactions. Having to remember to specify that table type = InnoDB for each table create, is a nuisance. I am also trying to keep to SQL standard code. TIA, Warren. -- Warren W. Gay VE3WWG http://home.cogeco.ca/~ve3wwg - 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
Crazy SELECT SUM ... JOIN ... GROUP problem
I have a complex JOIN statement that I can't seem to get to work. Here is my schema... I have a 'forsale' table with various colunms. Here is the query for the data I am interested in: SELECT id, clientid, price FROM forsale WHERE user_id = 152 +--+-++ |id|clientid |price | +--+-++ | 2863 | 317 | 27500 | | 2864 | 317 | 5 | | 2865 | 317 | 24000 | | 2866 | 317 | 2315 | | 2867 | 317 | 7968 | | 2868 | 317 | 0 | | 2869 | 317 | 0 | | 2872 | 51 | 0 | +--+-++ 8 rows selected. Each 'forsale' row has an associated clientid which I want to GROUP BY to make a summary like this: SELECT clientid, SUM(price) FROM forsale WHERE user_id = 152 GROUP BY clientid +-+---+ |clientid |SUM(price) | +-+---+ | 51 | | | 317 | 111783| +-+---+ 2 rows selected. Now I also have a 'forsale_log' table that tracks the number of hits a particular 'forsale' row gets on the web side of things: SELECT forsale.id, forsale.clientid, forsale.price, COUNT(forsale_log.id) as hits FROM forsale LEFT JOIN forsale_log ON forsale_log.forsaleid = forsale.id WHERE user_id = 152 GROUP BY forsale.id +--+-+ +---+ |id|clientid |price |hits | +--+-+ +---+ | 2863 | 317 | 27500 | 2 | | 2864 | 317 | 5 | 1 | | 2865 | 317 | 24000 | 1 | | 2866 | 317 | 2315 | 2 | | 2867 | 317 | 7968 | 2 | | 2868 | 317 | 0 | 4 | | 2869 | 317 | 0 | 5 | | 2872 | 51 | 0 | 0 | +--+-+ +---+ 8 rows selected. Now if I go and try to GROUP BY the clientid again to get a summary of both the hits AND total price I get crazy results: SELECT forsale.clientid, SUM(forsale.price), COUNT(forsale_log.id) as hits FROM forsale LEFT JOIN forsale_log ON forsale_log.forsaleid = forsale.id WHERE user_id = 152 GROUP BY forsale.clientid +-++---+ |clientid |SUM(forsale.price) |hits | +-++---+ | 51 || 0 | | 317 | 149566 |17 | +-++---+ 2 rows selected. The hits are correct but the price total is wrong and it is out by factors of the individual 'forsale' items individual 'hits'. I am sure I just have the wrong JOIN structure but I can't figure it out. Any help would be appreciated. Thanks, Dan - 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: Crazy SELECT SUM ... JOIN ... GROUP problem
Dan Tappin wrote: I am sure I just have the wrong JOIN structure but I can't figure it out. Any help would be appreciated. Ah, if only it were that easy! The problem is that you're really trying to summarize at two ways at the same time, and SQL doesn't like to do that. On one hand you're aggregating sales in client; on the other you're aggregating rows in forsale_log. If you left out the GROUP BY (and adjusted the rest of the query accordingly, eliminating the count() and sum()), you'd see that you're getting one row for each *log hit*, instead of one row for each sale, before summarization. (Actually, you're also getting a row for eachsale without any log hits, since it's a left join, but that's irrelevant to my comments, although correct.) So, when you do the aggregation, each sale is counted multiple times. In a full implementation of SQL, you'd use a view to get around this. In MySQL, you don't have that luxury, so the best you can do is create a temporary table and use it. It would be something like this: create table client as select clientID, sum(price) as totPrice, count(*) as saleCount from forsale group by clientID; select f.clientID, f.totPrice, f.saleCount, count(*) as hits from forsale f LEFT JOIN forsale_log l ON f.clientID = l.clientID group by clientID, totPrice, saleCount; drop table client; I'm new to MySQL, and I haven't tested the above, so there could be minor errors. One minor bit of weirdness in the above that I'd better explain is the grouping by totPrice and saleCount. That's there because in an aggregate query it's only possible to select items grouped by or aggregates. *We* know that there will be only one value of totPrice and one of saleCount for each clientID, but SQL doesn't, unless we do the grouping that way. An alternative would be to select max(f.totPrice) and max(f.saleCount) instead. Bruce Feist - 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: Table Create Defaults (Type=InnoDB)?
On Mon, Mar 03, 2003 at 09:36:37AM -0500, Warren W. Gay VE3WWG wrote: I would like to create a Table default (Type=InnoDB) for a particular database. Is there a way to do this, or is this being planned for in a future release? For most of my databases, I am only interested in transactions. Having to remember to specify that table type = InnoDB for each table create, is a nuisance. I am also trying to keep to SQL standard code. The table_type option controls what type of database is created by default. You can set this at various places: * When starting mysqld via the --default-table-type option * In a mysql options file via the table_type variable * As a server variable via a SET [GLOBAL] table_type call You cannot set this on a per-database basis, however you can set it on a session-wide basis. To keep closer to standard SQL, wrap the SET call in an executable comment, like so: /*! SET table_type=innodb; */ Most (all that I know of :) databases except MySQL will treat the comment like a normal multi-line comment. MySQL will strip the comment and execute the code. Cheers! -- Zak Greant [EMAIL PROTECTED] MySQL AB Community Advocate Personal Blog: http://zak.fooassociates.com OSCON 2003 (http://conferences.oreillynet.com/os2003/) From July 7-11 in Portland, Oregon Gosh, Batman. The nobility of the almost-human porpoise. --Robin, the Boy Wonder - 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: Crazy SELECT SUM ... JOIN ... GROUP problem
To add to this I am using this data via PHP. I think it might be faster and easier to just create my summary via one select for the hits and then an inline select for the hits as I loop thru my PHP code to display the list of clients. I also could just create to separate selects grouped by clientid for both SUM(forsale_log.id) and SUM(forsale.price). It's a bit gross but I am only expecting a hand full of rows returned on each select. Thanks, Dan On Monday, March 3, 2003, at 08:11 AM, Bruce Feist wrote: Dan Tappin wrote: I am sure I just have the wrong JOIN structure but I can't figure it out. Any help would be appreciated. Ah, if only it were that easy! The problem is that you're really trying to summarize at two ways at the same time, and SQL doesn't like to do that. On one hand you're aggregating sales in client; on the other you're aggregating rows in forsale_log. If you left out the GROUP BY (and adjusted the rest of the query accordingly, eliminating the count() and sum()), you'd see that you're getting one row for each *log hit*, instead of one row for each sale, before summarization. (Actually, you're also getting a row for eachsale without any log hits, since it's a left join, but that's irrelevant to my comments, although correct.) So, when you do the aggregation, each sale is counted multiple times. In a full implementation of SQL, you'd use a view to get around this. In MySQL, you don't have that luxury, so the best you can do is create a temporary table and use it. It would be something like this: create table client as select clientID, sum(price) as totPrice, count(*) as saleCount from forsale group by clientID; select f.clientID, f.totPrice, f.saleCount, count(*) as hits from forsale f LEFT JOIN forsale_log l ON f.clientID = l.clientID group by clientID, totPrice, saleCount; drop table client; I'm new to MySQL, and I haven't tested the above, so there could be minor errors. One minor bit of weirdness in the above that I'd better explain is the grouping by totPrice and saleCount. That's there because in an aggregate query it's only possible to select items grouped by or aggregates. *We* know that there will be only one value of totPrice and one of saleCount for each clientID, but SQL doesn't, unless we do the grouping that way. An alternative would be to select max(f.totPrice) and max(f.saleCount) instead. Bruce Feist - 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: Table Create Defaults (Type=InnoDB)?
On Mon, Mar 03, 2003 at 08:21:58AM -0700, Zak Greant wrote: ... To keep closer to standard SQL, wrap the SET call in an executable comment, like so: /*! SET table_type=innodb; */ Most (all that I know of :) databases except MySQL will treat the comment like a normal multi-line comment. MySQL will strip the comment and execute the code. One note: The above statement confuses the parser slightly. It believes that the above statement should continue. This will make the subsequent statement fail. I have submitted a bug report. For the short-term, get around the problem by insterting a second semi-colon. The parser will complain about an empty query, but the SET statement and the subsequent query will both succeed. Cheers! -- Zak Greant [EMAIL PROTECTED] MySQL AB Community Advocate Personal Blog: http://zak.fooassociates.com MySQL Tip: Display the option files read by the server % mysqld --help Sincere Choice: Supporting a Fair Software Market (http://sincerechoice.org) - 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 replication started
Hello, I am trying to start replication between 2 linux servers mysql version is 4.0.8. I am following all instructions, but I keep getting the following error message on the slave when I restart. 030303 10:35:28 mysqld started 030303 10:35:29 InnoDB: Started /usr/mysql/libexec/mysqld: ready for connections 030303 10:35:29 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'floyd-bin.008' at position 164 030303 10:35:29 Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 030303 10:35:29 Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log 030303 10:35:29 Slave I/O thread exiting, read up to log 'floyd-bin.008', position 164 Can anyone help on this ? Thank You, floyd - 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: Indices do not seem to work for OR queries
On Saturday 01 March 2003 01:26, Henning Schulzrinne wrote: Both email columns have the same format (varchar) and are indexed individually: describe SELECT person FROM person WHERE email1='foo' OR email2='foo'; ++--+---+--+-+--+---+-- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra ++--+---+--+-+--+---+-- ---+ | person | ALL | email1,email2 | NULL |NULL | NULL | 57051 | Using where | ++--+---+--+-+--+---+-- ---+ [skip] I'm curious why this is. (In general, I have been surprised by how often indices are not used even if they exist.) it's know behaviour and described in the manual: http://www.mysql.com/doc/en/MySQL_indexes.html http://www.mysql.com/doc/en/Searching_on_two_keys.html -- 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: Create table with IF condition not working properly
On Friday 28 February 2003 22:00, Scott wrote:
I have a client word database and I am attempting to create a database with
the results from a select that splits the description field based on the
spaces.
When just executing the select command, the output is correct. When using
the create command with the select command, apparently the IF statement
result (0/1) is being placed into the field instead of the word.
My Mysql version is: mysql Ver 12.12 Distrib 4.0.3-beta, for pc-linux-gnu
(i686)
clientid description
-
client1 chair couch piano
client2 bed dresser
client3 chair
client4 table stove couch
drop table if exists wordsplit;
create table wordsplit
select clientid,
if(locate(' ',description)=0,trim(substring(description,1)),
trim(substring(description,1,locate(' ',description as firstword,
if(locate(' ',trim(substring(description,locate(' ',description=0,
substring(trim(substring(description,locate(' ',description))),1),
substring(trim(substring(description,locate(' ',description))),1,
locate(' ',trim(substring(description,locate(' ',description)) as
secondword,
(trim(substring(ltrim(substring(description,locate(' ',description))),
locate(' ',ltrim(substring(description,locate(' ',description))) as
thirdword
from clientword order by clientid;
Could you test it on 4.0.11 version of MySQL server? If you get the same
result, please, provide a repeatable test case with table structure (output
of SHOW CREATE TABLE or mysqldump).
--
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: max_data_length - impact of very large value
On Monday 03 March 2003 16:03, Sigurd Urdahl wrote: In one of our customers databases there is table that have an extremely high max_data_length of about 256TB(!). Another have 1 TB, while the rest is at 4GB. (all of these tables are basically the same, but with these two as the ones withthe most data). The largest table is currently at about 8.7 GB. Is there any practical consequences of having such overly large max_table_length? Nothing really. Or is max_data_length mostly useful as a way to ensure that disk don't run full or as a quota system? No, because you'll have to provide 256TB of free disk space for a 8.7gb table - I don't think that's reasonable. And if there is, what is the more practical way reduce the value to something remotely sane. There is no need to. -- 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: mysql.sock
On Monday 03 March 2003 04:36, anhduc nguyen wrote: I don't know why when i run mysql the message Can't connect to mysql server throught port /tmp/mysql.sock appear. Check that MySQL server is running. If so find where mysql.sock is located. -- 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: bug - sec_to_time has a low upper limit on the range
On Friday 28 February 2003 22:17, Brian Bittman wrote: Subject: bug - sec_to_time has a low upper limit on the range Description: sec_to_time will not return times greater than 24, my hunch is that sec_to_time() is wrapping at 24:00 for some reason or that's some kind of bound, and it's not sticking to the TIME type which has a documented range of -838:.. to 838 hours. How-To-Repeat: run this query: select sec_to_time(10.5*60*60), sec_to_time(18*60*60), sec_to_time(22*60*60), sec_to_time(23*60*60), sec_to_time(24*60*60), sec_to_time(24.5*60*60), sec_to_time(24.999*60*60), sec_to_time(26*60*60), sec_to_time(35*60*60), (35*60*60)/60/60 you should see 10:30:00, 18:00:00, 22:00:00, 23:00:00, 23:00:00, 24:30:00, 24:59:56, 26:00:00, 35:00:00, 35 but what you do get: 10:30:00, 18:00:00, 22:00:00, 23:00:00, 23:00:00, 23:30:00, 23:59:56, 23:00:00, 23:00:00, 35 I tested your example on 4.0.11 and got the following result: mysql select - sec_to_time(10.5*60*60), - sec_to_time(18*60*60), - sec_to_time(22*60*60), - sec_to_time(23*60*60), - sec_to_time(24*60*60), - sec_to_time(24.5*60*60), - sec_to_time(24.999*60*60), - sec_to_time(26*60*60), - sec_to_time(35*60*60), - (35*60*60)/60/60 - ; +-+---+---+---+---+-+---+---+---+--+ | sec_to_time(10.5*60*60) | sec_to_time(18*60*60) | sec_to_time(22*60*60) | sec_to_time(23*60*60) | sec_to_time(24*60*60) | sec_to_time(24.5*60*60) | sec_to_time(24.999*60*60) | sec_to_time(26*60*60) | sec_to_time(35*60*60) | (35*60*60)/60/60 | +-+---+---+---+---+-+---+---+---+--+ | 10:30:00| 18:00:00 | 22:00:00 | 23:00:00 | 24:00:00 | 24:30:00| 24:59:56 | 26:00:00 | 35:00:00 | 35. | +-+---+---+---+---+-+---+---+---+--+ 1 row in set (0.00 sec) -- 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
Replication 4.0.10
Trying to setup Mysql 4.0.10 in a replication environment. 64bit compiled Sun solaris 2.8 platform. We have had Mysql 4.0.5 installed and running in replication for several months and are required to move version due to the bug in 4.0.5. We have compiled the Mysql 4.0.10 from source and installed onto the master and slave servers. Now when we try to start the slave we get an error 1045 access denied although the file and replication slave privs have been granted and all the directories have the right permissions. I can also connect using mysql from the slave as the replication user to the master. I have also tried to give full access to the slave without success. Please check this error. Regards MANJIT REHAL BBCi at http://www.bbc.co.uk/ This e-mail (and any attachments) is confidential and may contain personal views which are not the views of the BBC unless specifically stated. If you have received it in error, please delete it from your system, do not use, copy or disclose the information in any way nor act in reliance on it and notify the sender immediately. Please note that the BBC monitors e-mails sent or received. Further communication will signify your consent to this. - 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 installation and security question
Greetings, I have been working with a software provider whose software db configuration uses the default mysql installation (ie, root, no password). They contend that since the mysql server itself is not shared (ie, installed on a vps for a single user) that there is no need to add a password. Are they correct? All the documentation I have every read recommends at the very least immediately adding a password. Please advise. Best regards, Nicole - 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: getting replication started
Hello, I am trying to start replication between 2 linux servers mysql version is 4.0.8. I am following all instructions, but I keep getting the following error message on the slave when I restart. 030303 10:35:28 mysqld started 030303 10:35:29 InnoDB: Started /usr/mysql/libexec/mysqld: ready for connections 030303 10:35:29 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'floyd-bin.008' at position 164 030303 10:35:29 Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 030303 10:35:29 Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log 030303 10:35:29 Slave I/O thread exiting, read up to log 'floyd-bin.008', position 164 Can anyone help on this ? Thank You, floyd - 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: Slow FULLTEXT searches
On Thu, Feb 27, 2003 at 06:05:02PM +0100, Thomas Spahni wrote:
Jesse
But then something else must be terribly wrong. As long as you are pulling
ten thousands of hits from the server, it may be slow. But when you reduce
the number of results with (let's say) 'LIMIT 100' I expect typical serch
times of 0.02 sec. That's what I see on a comparable machine holding 200
MB of text plus index.
Can you check for the response time on a not so common single word?
Sorry for the delay in following up.
Even when I'm searching for relatively uncommon single words, it's
still often slower than I'd like, but certainly under a second in
most cases.
Still, the problem is that I really do often need to search
for very common words, and these are extremely slow. People
will need to do phrase searches on this material that includes
shorter words or stopwords, and while I'm willing to shorten
the ft_min_word_length and remove the stopword list and take
the storage hit, it's still not OK that a query like SELECT
qt FROM q WHERE MATCH(qt) AGAINST ('in the cut' IN BOOLEAN
MODE) will take 2m20s to return 37 rows, or 'his computer'
IN BOOLEAN MODE will take 17s to return 117 rows, and that's
without sorting, or joining in five other tables some with
their own restrictions, etc.
Is there any way to improve the speed of these searches, given
that fulltext phrase searching is one of the main things I had
been hoping to use this database for? In many cases, other
restrictions from joined tables will even further reduce the
number of possible matches, but perhaps this doesn't matter if
the fulltext search is done independently of these; I don't know
how the optimizer handles this.
Thanks.
Jesse Sheidlower
-
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
What to Download?
MySQL List, On the http://www.mysql.com/downloads/mysql-3.23.html page, under Linux x86 RPM downloads, there are 5 available downloads. Do I need all of them? I would like to have all capabilities. Can someone advise me as to what each download is for? Thanks, Jeremy Whitlock --- MCP/MCSA IT Manager for Star Precision, Inc. Phone: (970) 535-4795 Metro: (303) 926-0559 Fax: (970) 535-0780 Metro Fax: (303) 926-0559 http://www.starprecision.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
Slow COUNT queries
I have a Web application that allows users to search for text in a potentially complicated form, and then return results. Leaving aside the issue of the speed of FULLTEXT searching, which I'm discussing in a separate thread, I'm having a problem with an initial COUNT query. When the user enters their search, I first do a COUNT so I can get the total result size and set up the pager for the usual next/previous skipping through the results. When the result set is large, this initial COUNT can be extremely slow; this, for example, is from a question about words in the letter M within the last six months: mysql SELECT(cg.cw) AS cwcount FROM cg,q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw LIKE 'm%' AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH); +-+ | cwcount | +-+ |4666 | +-+ 1 row in set (1 min 11.26 sec) Everything is indexed here; the EXPLAIN shows: *** 1. row *** table: cg type: range possible_keys: q_id,cw key: cw key_len: 101 ref: NULL rows: 147780 Extra: Using where *** 2. row *** table: q type: eq_ref possible_keys: PRIMARY,cit_id key: PRIMARY key_len: 4 ref: cg.q_id rows: 1 Extra: *** 3. row *** table: cit type: eq_ref possible_keys: PRIMARY,sref_id key: PRIMARY key_len: 4 ref: q.cit_id rows: 1 Extra: *** 4. row *** table: sref type: eq_ref possible_keys: PRIMARY,cd key: PRIMARY key_len: 4 ref: cit.sref_id rows: 1 Extra: Using where 4 rows in set (0.00 sec) This is on a 1.4GHz PIII server running FreeBSD 4.7 with 1G of RAM. Needless to say, over a minute for a single user's query is unacceptably slow; generally after the COUNT, when I'm issuing LIMITed SELECT queries, things get much better. Is there any way to improve on this? Thanks. Jesse Sheidlower - 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
Remove -?
Hi all, I want to take the data in a column, modify and then set it into another column. In specific, I want to remove the dash from our Product ID column and place it in another column without the dash. For instance, a ProdID may be 'SK-22'. I want to take this value from ProdID and place it as 'SK22' into ProdKeywords column for the same record. How would I go about removing the '-' and placing the remainder in the ProdKeywords column. Thank you, Doug Coning sql,query,queries,smallint - 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 deadlock leads to server crash
Hi! I have now fixed the hang to MySQL-4.0.12. I let mysqld to release the adaptive hash S-latch when it passes the control to the client as it sends result rows of a SELECT to the client. Can you test the latest 4.0.12 bk tree? Best regards, Heikki sql query ... Subject: Re: innodb deadlock leads to server crash From: Heikki Tuuri Date: Mon, 3 Mar 2003 10:42:48 +0200 Hi! Ok, now I think I understand the problem. Since you use the -q option, the client mysql retrieves rows in the result set in chunks of some size. As InnoDB keeps the adaptive hash index latch S-locked till the end of the the big SELECT ... ORDER BY ... query, it will probably be S-latched when you start retrieving the rows. And then you cannot perform much further operations in the database because of the S-latch! As you pointed out, a workaround would be to remove the -q option, since then mysql would retrieve all rows in one chunk and release the S-latch. Hmm... the underlying problem is that InnoDB does not know when mysqld moves control to the client, and does not know to release the S-latch then. I have to check if I can see when mysql_use_result() is used to retrieve the result in smaller chunks. I could then release the S-latch always when the program control leaves InnoDB. Thank you, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:24 AM Subject: Re: innodb deadlock leads to server crash Hi! Your email address gives the error: DNS for host dev.noris.de is mis-configured The following recipients did not receive this message: [EMAIL PROTECTED] Still one question: is this a deadlock of threads at all? Maybe the sorting which mysqld does, or the fetches which: mysql -q ... | program executes take so long that InnoDB thinks the server has hung? How big is the result set of your ORDER BY query in terms of rows and megabytes? Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:05 AM Subject: Re: innodb deadlock leads to server crash Hi! A deadlock of threads is a bug. It is is not connected to transactions or multiversioning. Is the problem repeatable in your computer? Can you compile a debug version of mysqld? Go to the source tree root directory /mysql/ and do: ./BUILD/compile-pentium-debug-max Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: (gdb) info threads (gdb) thread 1 (gdb) bt full ... and so on for all threads. Send the output to me. You could also try an official MySQL binary you can download from www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat suspicious and might produce broken binaries. I tried to repeat the hang in my computer, but did not succeed. Can you send me what SHOW CREATE TABLE ticketlast; prints? 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 sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 3:58 AM Subject: innodb deadlock leads to server crash Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash
Selecting from mySql database regarding dates
I have a column in my mySql database that holds a date that I've added each record called designadddate I'm trying to create a filter to only pull up the records that have been added over the last two weeks. This is my code ... I added the echo for the $today and $twoweeksago variables to make sure something was being calculated, they are showing up correct as 2003-03-03 and 2003-02-17 ... but it isn't pulling up any records -- it's not bombing out and giving me the Couldn't execute query message - but I know there's three records that I made sure had dates in between this range. I also tried using BETWEEN and it didn't seem to work either. $today = date (Y-m-d); $twoweeksago = date(Y-m-d,mktime(0,0,0,date(m), date(d)-14, date(Y))); echo$today\n; echo$twoweeksago\n; /* Select designs of the given type */ $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND (designs.designadddate = $today AND designs.designadddate = $twoweeksago)GROUP BY catcode ORDER BY designfile; $result = mysql_query($query) or die (Couldn't execute query.); Any help would me much appreciated! TIA Renee Toth - 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: What to Download?
Jeremy, If you just want to use the server and connect to it to perform queries and maintain it, just the server and clients should suffice. If you want to link your own C programs against mysql or measure the exact performance on your hardware, you may want the others. By the fact that you're asking this question (no offence intended), I think you'll probably just want the server and client programs. Andy mysql query -Original Message- From: Jeremy Whitlock [mailto:[EMAIL PROTECTED] Sent: 03 March 2003 16:39 To: 'MySQL' Subject: What to Download? MySQL List, On the http://www.mysql.com/downloads/mysql-3.23.html page, under Linux x86 RPM downloads, there are 5 available downloads. Do I need all of them? I would like to have all capabilities. Can someone advise me as to what each download is for? Thanks, Jeremy Whitlock --- MCP/MCSA IT Manager for Star Precision, Inc. Phone: (970) 535-4795 Metro: (303) 926-0559 Fax: (970) 535-0780 Metro Fax: (303) 926-0559 http://www.starprecision.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: Selecting from mySql database regarding dates
try 's arround the dates $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND (designs.designadddate = '$today' AND designs.designadddate = '$twoweeksago')GROUP BY catcode ORDER BY designfile; otherwise your getting 2003 minus 3 minus 3 (2003-3-3) On Monday 03 March 2003 12:01, you wrote: I have a column in my mySql database that holds a date that I've added each record called designadddate I'm trying to create a filter to only pull up the records that have been added over the last two weeks. This is my code ... I added the echo for the $today and $twoweeksago variables to make sure something was being calculated, they are showing up correct as 2003-03-03 and 2003-02-17 ... but it isn't pulling up any records -- it's not bombing out and giving me the Couldn't execute query message - but I know there's three records that I made sure had dates in between this range. I also tried using BETWEEN and it didn't seem to work either. $today = date (Y-m-d); $twoweeksago = date(Y-m-d,mktime(0,0,0,date(m), date(d)-14, date(Y))); echo$today\n; echo$twoweeksago\n; /* Select designs of the given type */ $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND (designs.designadddate = $today AND designs.designadddate = $twoweeksago)GROUP BY catcode ORDER BY designfile; $result = mysql_query($query) or die (Couldn't execute query.); Any help would me much appreciated! TIA Renee Toth - 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, sql, query, sql, sql, 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
RE: Selecting from mySql database regarding dates
That brought me up the record that was dated 3/1 but not the ones dated 2/17 and 2/18 which fit into the criteria also We're getting there is there something going on with the fact that the other two are in the previous month? That's kinda freaky considering that I know because I echo'd the results of $today and $twoweeksago and I know they're calculating correctly??? Any ideas on where to go from here? -Original Message- From: Ray [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2003 1:36 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Selecting from mySql database regarding dates try 's arround the dates $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND (designs.designadddate = '$today' AND designs.designadddate = '$twoweeksago')GROUP BY catcode ORDER BY designfile; otherwise your getting 2003 minus 3 minus 3 (2003-3-3) On Monday 03 March 2003 12:01, you wrote: I have a column in my mySql database that holds a date that I've added each record called designadddate I'm trying to create a filter to only pull up the records that have been added over the last two weeks. This is my code ... I added the echo for the $today and $twoweeksago variables to make sure something was being calculated, they are showing up correct as 2003-03-03 and 2003-02-17 ... but it isn't pulling up any records -- it's not bombing out and giving me the Couldn't execute query message - but I know there's three records that I made sure had dates in between this range. I also tried using BETWEEN and it didn't seem to work either. $today = date (Y-m-d); $twoweeksago = date(Y-m-d,mktime(0,0,0,date(m), date(d)-14, date(Y))); echo$today\n; echo$twoweeksago\n; /* Select designs of the given type */ $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND (designs.designadddate = $today AND designs.designadddate = $twoweeksago)GROUP BY catcode ORDER BY designfile; $result = mysql_query($query) or die (Couldn't execute query.); Any help would me much appreciated! TIA Renee Toth - 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, sql, query, sql, sql, 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: Selecting from mySql database regarding dates
Here's how I would do it. (I'm sure that you'll probably get other solutions as well.) $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND TO_DAYS(NOW()) - TO_DAYS(designs.designadddate) = 14 GROUP BY catcode ORDER BY designfile; That is unless you are allowing people to enter future dates in the tables. Unless there is a good reason, I generally have error routines prevent future dates into the table. jeff At 13:01 -0500 3/3/03, Stitchin' wrote: I have a column in my mySql database that holds a date that I've added each record called designadddate I'm trying to create a filter to only pull up the records that have been added over the last two weeks. This is my code ... I added the echo for the $today and $twoweeksago variables to make sure something was being calculated, they are showing up correct as 2003-03-03 and 2003-02-17 ... but it isn't pulling up any records -- it's not bombing out and giving me the Couldn't execute query message - but I know there's three records that I made sure had dates in between this range. I also tried using BETWEEN and it didn't seem to work either. $today = date (Y-m-d); $twoweeksago = date(Y-m-d,mktime(0,0,0,date(m), date(d)-14, date(Y))); echo$today\n; echo$twoweeksago\n; /* Select designs of the given type */ $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND (designs.designadddate = $today AND designs.designadddate = $twoweeksago)GROUP BY catcode ORDER BY designfile; $result = mysql_query($query) or die (Couldn't execute query.); Any help would me much appreciated! TIA Renee Toth - 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 -- Jeff Shapiro, Colorado Springs, CO, USA At work I *have* to use a Windows machine, at home I *get* to use a Mac. - 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: Remove -?
Try : (on a test table first of course) UPDATE prodtable SET ProKeywords = REPLACE(ProdID, '-', ''); You may want to look at this page: http://www.mysql.com/doc/en/String_functions.html jeff At 11:57 -0600 3/3/03, Doug Coning wrote: Hi all, I want to take the data in a column, modify and then set it into another column. In specific, I want to remove the dash from our Product ID column and place it in another column without the dash. For instance, a ProdID may be 'SK-22'. I want to take this value from ProdID and place it as 'SK22' into ProdKeywords column for the same record. How would I go about removing the '-' and placing the remainder in the ProdKeywords column. Thank you, Doug Coning sql,query,queries,smallint - 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 -- Jeff Shapiro, Colorado Springs, CO, USA At work I *have* to use a Windows machine, at home I *get* to use a Mac. - 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
Querying for performance...
All,
I have been writing my applications using PHP/MySQL for about a year and
a half now. During this time and up to now, there hasn't been anything
wrong with my apps; however, I need to know if what I am doing is the
best route for optimal performance.
So I ask these questions:
1. How many queries can MySQL handle?
For example, one of my functions does this:
While (I am selecting some data)
{
while (use one of the results as an element to query a
different table)
{
echo the results formatted in html
}
}
I'm sure that I could be using a join, but this is an example.
2. I am under the impression that a char is 'a character', but I
have read and think I have seen it used with more than one character.
For example, char (5). If this is correct, how many values can char
hold?
3. Is it true that varchar appends spaces to the value if it's not
all set? For example, if a varchar is set to 255 and my value is really
only 245, then 10 whitespaces will be appended to the original value.
-Jonathan
-
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
Error 2013: Lost connection to mysql server during query
Like several other people I noticed on the web, I am receiving an error message Error 2013: Lost connection to mysql server during query, however I can not find the results of you corrections? Can you please point me to the place where I can find a fix for this error? Thanks Bob - 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: Selecting from mySql database regarding dates
You are a genius and I'm a moron Duh ... I put a GROUP BY in there!!! So since all three designs were in the same group, it only showed one!!! I didn't mean to make it a GROUP BY for the catcode, I meant to do an ORDER BY the catcode then designfile see that's what I get for being a greenie you get so focused on what you think is the problem that you totally lose site of the fact that the problem is something totally different thanks for helping me take my blinders off!! Renee :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2003 2:24 PM To: [EMAIL PROTECTED] Subject: Re: Selecting from mySql database regarding dates What happens if you statically set the dates in your query such as this: SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.designadddate = '2003-03-03' AND designs.designadddate = '2003-02-17'; (For the sake of solving the date problem I simplified the query to isolate the issue and make sure that really -is- where the issue lies. If this returned properly I would then modify the dates to use the dynamic variables you have been using and then slowly fade the rest back in and see where it breaks down. Oh... and I don't see any reason to have brackets around your = and = comparisons...) Jason - Original Message - From: Stitchin' [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 03, 2003 12:01 PM Subject: Selecting from mySql database regarding dates I have a column in my mySql database that holds a date that I've added each record called designadddate I'm trying to create a filter to only pull up the records that have been added over the last two weeks. This is my code ... I added the echo for the $today and $twoweeksago variables to make sure something was being calculated, they are showing up correct as 2003-03-03 and 2003-02-17 ... but it isn't pulling up any records -- it's not bombing out and giving me the Couldn't execute query message - but I know there's three records that I made sure had dates in between this range. I also tried using BETWEEN and it didn't seem to work either. $today = date (Y-m-d); $twoweeksago = date(Y-m-d,mktime(0,0,0,date(m), date(d)-14, date(Y))); echo$today\n; echo$twoweeksago\n; /* Select designs of the given type */ $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND (designs.designadddate = $today AND designs.designadddate = $twoweeksago)GROUP BY catcode ORDER BY designfile; $result = mysql_query($query) or die (Couldn't execute query.); Any help would me much appreciated! TIA Renee Toth - 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: Errors on ALTER table in InnoDB
Hi! - Original Message - From: Yasaswi Pulavarti [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 7:19 PM Subject: Re: Errors on ALTER table in InnoDB I don't know the answer. But I get similar errors when I use Erwin (Data Modelling software) to publish or alter a database to my InnoDB database. An expert's answer would be helpful. Thanks, Yasaswi [EMAIL PROTECTED] (My Deja) wrote in message news:[EMAIL PROTECTED]... When I try to alter some tables in InnoDB I some times get this sort of message. What do they mean? Error on rename of '.\transfer20\#sql-268_2' to '.\transfer20\exrates2' (errno: 150) ALTER TABLE EXRATES2 DROP COLUMN XR_DESTCURRENCY Error on rename of '.\transfer20\#sql-268_2' to '.\transfer20\exrates2' (errno: 150) The ALTER TABLE you are trying to do would make some FOREIGN KEY declarations not to obey the rules specified at http://www.innodb.com/ibman.html#InnoDB_foreign_keys. Similarly, if an ALTER TABLE fails and it refers to errno 150, that means a foreign key definition would be incorrectly formed for the altered table Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The diferences about /dev/hda1 and access via raw device
Christian, - Original Message - From: Christian Jaeger [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 2:31 PM Subject: Re: The diferences about /dev/hda1 and access via raw device At 18:35 Uhr -0200 23.01.2003, Dyego Souza do Carmo wrote: /dev/hda1:20Gnewraw /dev/raw1:20Gnewraw The speed is more fast in the last case or no ? ... - The biggest difference was (as expected) when doing full table scans on tables bigger than what seemed to fit into the innodb buffer pool. The first scanning query took the same amount of time in both cases (~10 seconds), whereas with OS buffered IO the subsequent scans took only about 6 seconds, but unbuffered IO still 10 seconds for each run. Increasing innodb_buffer_pool_size from 90MB to 290MB changed it so that also with rawio the subsequent queries profit from cached data: (the test machine has 512MB RAM, runs linux 2.4.17, one seagate SCSI disk, 1Ghz PentiumIII) Query time for the first runs after a fresh mysqld start= [s] *) 90MB buffer pool: /dev/raw/raw1 10.60 11.09 11.01 /dev/sda10 10.67 6.82 6.80 290MB buffer pool: /dev/raw/raw19.53 4.18 4.17 3.96**) /dev/sda109.48 3.98 3.96 *) note that kernel 2.4 drops it's own buffered pages when the last application closes the buffer device, so it's enough to restart mysqld and not necessary to reboot the machine before subsequent test runs (there's no cache left over). **) this last number is from a different run very interesting data. Thank you! Thanks, Christian. PS. some random observations (with mysql 3.23.51+your patches, built from Debian source package): - in some tests (1000 inserts of ~2kb and one commit in a loop, looping about once per second) the CPU is 40% idle even when using /dev/sda10 (os buffered) for data and innodb_flush_log_at_trx_commit=0 (I would have expected it to take all CPU since disk shouldn't be limiting?). At first, I had put the logs on Reiserfs, but using ext2 didn't change anything. Looks like there is some inefficiency in fsync() in your Linux/hardware combination. - mysql takes a long time (at least a minute) for shutting down shortly after having deleted 300'000 rows (of ~2kb each), in spite of innodb_fast_shutdown=1 and innodb_flush_log_at_trx_commit=3D0 in my.cnf. Mysql is taking about 20% cpu during this phase. It seems like there's cleanup work going on in the background that can't be interrupted even by fast_shutdown, correct? (I assume it isn't problematic when mysql is terminated by init issuing SIGKILL). innodb_fast_shutdown=1 is the default nowadays. But even then InnoDB has to flush the modified pages from the buffer pool to disk. That can take several minutes with a fairly large buffer pool. Of course, the fastest shutdown is simply killall -9 mysqld. Killing the mysqld process is a valid way of shutting down InnoDB, but then InnoDB has to do a crash recovery at the next startup. ... (- compared with a mysql 3.23.49 installation on an identical machine (with identical setup and identical data, both without any other load, both using /dev/sdaX with OS buffering), 3.23.51 took longer for the same queries involving smaller table scans (0.26 vs. 0.17 seconds in mysql client). Not sure what's the reason.) New versions tend to become slower CPU-wise as more debug code and hooks for new features are added. There is also significant random fluctuation which probably depends on how code is located in cache lines of the processor. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Too many connections high processor utilization
Good Day, I have Linux 7.3 server with mysql and apache 1.3 with php 4.1 with the first web site using php and mysql I install on the server, I found the processor utilization too high processor idle time is 0% and mysql is about 85%, I don't know why. Also the site gives too many connections error from mysql and php, while the programmer says there is only 10 connections (users browsing the site) It is not a complex site, just a db for movies in the cinema and photos with few articles for each film so I do not know how to troubleshoot this problem. - 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/mysqld_safe immediatly crashes on startup]
Description: A freshly installed mysql 4.0.11a crashes at first startup: (mysql_install_db runs fine), mysqld-safe --user=mysql chrashes and restarts in an endless loop. This is true for both the linux binary and the source distro. The same mysql version is stable on a system with identical softrware makeup. The system seems stable otherwise (compiled kernel, apache, php, etc.. no problems) hostname.err 030303 21:58:33 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 030303 21:58:33 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 030303 21:58:35 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 030303 21:58:36 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 030303 21:58:37 InnoDB: Started mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 Number of processes running now: 9 mysqld process hanging, pid 17434 - killed mysqld process hanging, pid 17433 - killed mysqld process hanging, pid 17432 - killed mysqld process hanging, pid 17431 - killed mysqld process hanging, pid 17430 - killed mysqld process hanging, pid 17429 - killed mysqld process hanging, pid 17428 - killed mysqld process hanging, pid 17427 - killed mysqld process hanging, pid 17426 - killed 030303 21:58:38 mysqld restarted 030303 21:58:38 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 37082 InnoDB: Doing recovery: scanned up to log sequence number 0 37082 030303 21:58:38 InnoDB: Flushing modified pages from the buffer pool... InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 030303 21:58:38 InnoDB: Started mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 and so on and so on until the manual killing of mysqld_safe key_buffer_size=8388600 read_buffer_size=131072 sort_buffer_size=-286397205 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 141264 2 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x83befc8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe278, stack_bottom=0x18171615, thre ad_stack=196608, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x14131211 is invalid pointer thd-thread_id=138147512 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 138147512 did to cause the crash. In some cases of reall y bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. 030303 21:58:41 mysqld ended How-To-Repeat: install binary / compile source on the target machine Tried recommended CFLAGS etc. settings (from onlein manual) without any difference in outcome. Fix: no known fix Submitter-Id: submitter ID Originator: [EMAIL PROTECTED] Organization: Larissa Naber fachhochschule technikum wien [EMAIL PROTECTED] MySQL support: [none] Synopsis: mysqld 4.0.11a crashes on start up Severity: critical Priority:medium Category: mysql Class: sw-bug Release: mysql-4.0.11a-gamma (Source distribution) C compiler:2.95.3 C++ compiler:
invalid aggregate function value for 0 row results (from a full join)
Description: It appears that when an aggregate like MAX is used in a query, mysql will put any values in that it already has from internal statistics, however, this is wrong if the query being executed results in zero records, in this example, the MAX of zero values is NULL, however mysql continues to report the real MAX. How-To-Repeat: DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, val int (11) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, val int(11) NOT NULL, PRIMARY KEY(id) ); INSERT INTO t1 (val) VALUES (1); INSERT INTO t1 (val) VALUES (2); SELECT MAX(id), MAX(val) from t1; SELECT MAX(id), MAX(val) from t2; SELECT MAX(t1.id), MAX(t2.id) from t1, t2; SELECT MAX(t1.val), MAX(t2.val) from t1, t2; SELECT MAX(t1.id), MAX(t2.id) from t1, t2 WHERE t1.id 2; DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; Some of the selects are for completeness, the key one is the third, since t2 contains no rows, the cross between t1 and t2 will be empty, but the query returns the real MAX value. Furthermore, when introducing the WHERE clause in the fifth query, it causes mysql to return the correct MAX value. This might indicate an optimization that was done: * if no where clause exists, then internal statistics on MAX value would be accurate, therefore, substitute this data for query results, however, this does not hold when there are no rows in the query In the format necessary for mysql test cases, the expected test output is below: MAX(id) MAX(val) 2 2 MAX(id) MAX(val) NULLNULL MAX(t1.id) MAX(t2.id) NULLNULL MAX(t1.val) MAX(t2.val) NULLNULL MAX(t1.id) MAX(t2.id) NULLNULL Fix: unknown Submitter-Id: [EMAIL PROTECTED] Originator:Paul DeMarco Organization: MySQL support: none Synopsis: invalid aggregate function value for 0 row results (from a full join) Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.55-max (Official MySQL-max binary) Environment: System: Linux stevenite.com 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 May 20 2002 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1285788 Apr 2 2002 /lib/libc-2.2.4.so -rw-r--r--1 root root 27332668 Apr 2 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 2 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-max binary' '--with-extra-charsets=complex' '--with-server-suffix=-max' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-berkeley-db' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' - 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
ERROR 1044: Access denied after upgrade to MySQL 4.0.11a-gamma
I have an application that has stopped working after the ISP upgraded from MySQL 3.23.36 to MySQL 4.0.11a-gamma-log All I'm told is: ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'jomaridb' This is a commercial server so I have no access to the mySQL install. I'd appreciate help in how to troubleshooting the likely cause of the ERROR 1044. This application was working flawlessly prior to the upgrade. John Hughes - 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 does not start
Sat Mar 1 04:20:27 2003]: I have MySQL 3.23 and i have gone thro the manual and also book by Paul.B. I get the following message error 2002:: Can't connect to local MySQL server through socket '/var/li/mysql/mysql..sock Pl mail your suggestion. Ramachandran - 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
Error 2013 - Lost connection to MySQL server during query
Hi I installed MySQL on a server running RedHat 8.0 and it's working fine. But when I try to connect to MySQL from a remote station, using MySQL Control Center, or even using the command-line mysql, I get the message: Error 2013 - Lost connection to MySQL server during query The remote station is running Windows XP Professional SP1 The server is running RedHat 8.0 + MySQL 3.23.54 Thanks Nivaldo - 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
Problems w/ MySQL 4.0.10-gamma and SSL
Below is a patch to fix some problems I've had with the SSL connections
via the MySQL 4.0.10-gamma client library (is there are bug tracker
or something to file this in?)
SSL_CTX_use_certificate_chain_file allows the server/client to use
certificates signed by sub CAs.
Having SSL_VERIFY_FAIL_IF_NO_PEER_CERT set prevents clients from
using SSL even if the server isn't requiring and cert verification.
I have also have some connection attempts to MySQL server (also
4.0.10-gamma) just hang.
The perl (standalone) DBD-mysql interface using SSL connects fine,
but if you try and use the same dsn while running under mod_perl it
hangs.
I get the same problems when using postfix and proftpd when linked
against the new mysql library, but not with courier-imap. None of those
are trying to connect via SSL.
--- viosslfactories.c.orig 2003-03-03 22:20:17.0 +1300
+++ viosslfactories.c 2003-03-03 22:21:45.0 +1300
@@ -89,7 +89,7 @@
ctx, cert_file, key_file));
if (cert_file != NULL)
{
-if (SSL_CTX_use_certificate_file(ctx,cert_file,SSL_FILETYPE_PEM) =
0)
+if (SSL_CTX_use_certificate_chain_file(ctx,cert_file) = 0)
{
DBUG_PRINT(error,(unable to get certificate from
'%s'\n,cert_file));
/* FIX stderr */
@@ -272,9 +272,7 @@
const char *ca_path,
const char *cipher)
{
- int verify = (SSL_VERIFY_PEER|
- SSL_VERIFY_FAIL_IF_NO_PEER_CERT |
- SSL_VERIFY_CLIENT_ONCE);
+ int verify = (SSL_VERIFY_PEER | SSL_VERIFY_CLIENT_ONCE);
struct st_VioSSLAcceptorFd* ptr;
int result;
DH *dh=NULL;
-
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 does not start
It looks like you've got a type in your my.cnf check where your mysql.sock is trying to create. It should be something like: /var/lib/mysql/mysql.sock ^ =C= * Cal Evans * Stay Plugged Into Your Audience * http://www.christianperformer.com -Original Message- From: Chidambaram Ramachandran [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2003 2:05 AM To: [EMAIL PROTECTED] Subject: Mysql does not start Sat Mar 1 04:20:27 2003]: I have MySQL 3.23 and i have gone thro the manual and also book by Paul.B. I get the following message error 2002:: Can't connect to local MySQL server through socket '/var/li/mysql/mysql..sock Pl mail your suggestion. Ramachandran - 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
FULLTEXT indexes and select query problem
I found some stuff in the mySql manual about setting columns as fulltext
indexes for fulltext searches and the syntax is
WHERE MATCH (column names...) AGAINST (the search stuff)
So I went in and did an alter table statement to set my four search columns
to be in a fulltext index.
So here's the freaky thing ... I create this hideous looking select
statement to grab the right stuff out of the database (all the stuff ending
in _int and $searchwords are the variables from the search form that the
user fills out to pick the designs they want to see)
$query = SELECT * FROM designs, appliquetable, puffyfoamtable
WHERE designs.puffyfoam = puffyfoamtable.puffyfoam
AND designs.applique = appliquetable.applique
AND designs.applique=$applique_int
AND designs.puffyfoam=$pfoam_int
AND designs.largesize = $largesize_int
AND designs.smallsize = $smallsize_int
AND designs.availablefiles LIKE %$file_int%
AND MATCH(designname,designcomments,catcode,textsearch)
AGAINST ($searchwords);
$result = mysql_query($query)
or die (Couldn't execute query A2 P2.);
And I get the Couldn't execute query A2 P2. Error... but when I copy the
statement into phpMyAdmin SQL screen and put values in the spots like below,
it actually finds the one record in the database that matches:
SELECT * FROM designs, appliquetable, puffyfoamtable
WHERE designs.puffyfoam = puffyfoamtable.puffyfoam
AND designs.applique = appliquetable.applique
AND designs.applique = 0
AND designs.puffyfoam = 0
AND designs.largesize = 4.00
AND designs.smallsize = 4.00
AND designs.availablefiles LIKE %SEW%
AND MATCH ( designname, designcomments, catcode, textsearch) AGAINST
('sports')
It probably something stupid like quotes or something ... I'm gonna go back
and double-check those variables from the search form ... Anybody out there
see what I missed? :)
RENEE :)
Renee Toth
Stitchin' Up A Storm
www.stitchinupastorm.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: Error 2013 - Lost connection to MySQL server during query
Suporte schrieb: Hi I installed MySQL on a server running RedHat 8.0 and it's working fine. But when I try to connect to MySQL from a remote station, using MySQL Control Center, or even using the command-line mysql, I get the message: Error 2013 - Lost connection to MySQL server during query The remote station is running Windows XP Professional SP1 The server is running RedHat 8.0 + MySQL 3.23.54 i am not in the list you can try this one and please post it *in* the list if it could solve your problem https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=77467 lost connection ... [mysqld] set-variable=thread_stack=256k - 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: FULLTEXT indexes and select query problem
At 21:56 -0500 3/3/03, Stitchin' wrote:
I found some stuff in the mySql manual about setting columns as fulltext
indexes for fulltext searches and the syntax is
WHERE MATCH (column names...) AGAINST (the search stuff)
So I went in and did an alter table statement to set my four search columns
to be in a fulltext index.
So here's the freaky thing ... I create this hideous looking select
statement to grab the right stuff out of the database (all the stuff ending
in _int and $searchwords are the variables from the search form that the
user fills out to pick the designs they want to see)
$query = SELECT * FROM designs, appliquetable, puffyfoamtable
WHERE designs.puffyfoam = puffyfoamtable.puffyfoam
AND designs.applique = appliquetable.applique
AND designs.applique=$applique_int
AND designs.puffyfoam=$pfoam_int
AND designs.largesize = $largesize_int
AND designs.smallsize = $smallsize_int
AND designs.availablefiles LIKE %$file_int%
AND MATCH(designname,designcomments,catcode,textsearch)
AGAINST ($searchwords);
$result = mysql_query($query)
or die (Couldn't execute query A2 P2.);
And I get the Couldn't execute query A2 P2. Error... but when I copy the
statement into phpMyAdmin SQL screen and put values in the spots like below,
it actually finds the one record in the database that matches:
SELECT * FROM designs, appliquetable, puffyfoamtable
WHERE designs.puffyfoam = puffyfoamtable.puffyfoam
AND designs.applique = appliquetable.applique
AND designs.applique = 0
AND designs.puffyfoam = 0
AND designs.largesize = 4.00
AND designs.smallsize = 4.00
AND designs.availablefiles LIKE %SEW%
AND MATCH ( designname, designcomments, catcode, textsearch) AGAINST
('sports')
It probably something stupid like quotes or something ... I'm gonna go back
and double-check those variables from the search form ... Anybody out there
see what I missed? :)
It probably something stupid like quotes or something. Maybe next time
you could go back and double-check *first* before posting? Thanks.
But you might also try changing your die() call so that it prints the
value of mysql_error() -- that way you'll see the actual error message
being returned by the server. That'll give you some information you're
now not taking advantage of. Also, print the text of the query; that'll
likely show you what you need to know. Yes, that's a hint as to the
source of your problem.
RENEE :)
Renee Toth
Stitchin' Up A Storm
www.stitchinupastorm.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: Querying for performance...
At 13:19 -0600 3/31/03, Jonathan Villa wrote:
All,
I have been writing my applications using PHP/MySQL for about a year and
a half now. During this time and up to now, there hasn't been anything
wrong with my apps; however, I need to know if what I am doing is the
best route for optimal performance.
So I ask these questions:
1. How many queries can MySQL handle?
For example, one of my functions does this:
While (I am selecting some data)
{
while (use one of the results as an element to query a
different table)
{
echo the results formatted in html
}
}
I'm sure that I could be using a join, but this is an example.
You're probably thinking that you're issuing multiple queries simultaneously
when you're doing that, but unless you're playing tricks with buffering,
you're not. By the time you get to the while loop (selecting some data),
the first query has already been processed completely and the entire result
set has been received on the client end. The server's done with it by
the time you start issuing queries inside the loop.
2. I am under the impression that a char is 'a character', but I
have read and think I have seen it used with more than one character.
For example, char (5). If this is correct, how many values can char
hold?
CHAR(5) means a 5-byte char column. That is, a column that can hold
up to 5 bytes of character data. For single-byte character sets (the
usual case), that means 5 characters.
3. Is it true that varchar appends spaces to the value if it's not
all set? For example, if a varchar is set to 255 and my value is really
only 245, then 10 whitespaces will be appended to the original value.
No, this is false for VARCHAR. It's true for CHAR in the sense that for
a CHAR(255) column, 255 bytes will always be store.
VARCHAR only stores as many bytes as needed, after trailing spaces, if
any, are removed.
-Jonathan
-
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: default installation and security question
At 7:53 -0800 3/3/03, Nicole Lallande wrote: Greetings, I have been working with a software provider whose software db configuration uses the default mysql installation (ie, root, no password). They contend that since the mysql server itself is not shared (ie, installed on a vps for a single user) that there is no need to add a password. Are they correct? All the documentation I have every read recommends at the very least immediately adding a password. Please advise. No. They're incorrect. Running as root is an invitation for trouble. Running without a password is an invitation for trouble. Best regards, Nicole - 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
Data query by date
Hi all. I have spent some time studying the manual and previous posts, trying to get a grip on the whole date_format issue... I am VERY new to MySQL having been converted from an ADO/DAO environment with Access dBs... Where I am having trouble is coming up for a syntax to SELECT * FROM tableA WHERE ba_id_num = 'ABC12345' AND ba_make_date = '2003-25-2' ba_id_num = varchar(8) ba_make_date = datetime Even though I can see several rows from the CC, the query returns 0... I have unsuccessfully attempted to use the DATE_FORMAT function also. Any hint of where I am going wrong would be greatly appreciated ;-) Jeff - 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: Data query by date
At 13:53 +1000 3/4/03, Throbware Support wrote: Hi all. I have spent some time studying the manual and previous posts, trying to get a grip on the whole date_format issue... I am VERY new to MySQL having been converted from an ADO/DAO environment with Access dBs... Where I am having trouble is coming up for a syntax to SELECT * FROM tableA WHERE ba_id_num = 'ABC12345' AND ba_make_date = '2003-25-2' ba_id_num = varchar(8) ba_make_date = datetime Even though I can see several rows from the CC, the query returns 0... I have unsuccessfully attempted to use the DATE_FORMAT function also. Any hint of where I am going wrong would be greatly appreciated ;-) Well, for one thing, if ba_make_date is a DATETIME column, you're comparing it to what looks like a DATE value. That may make a difference, if the ba_make_date values don't have 00:00:00 in the time part. What's the CC you're referring to? Jeff - 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
AW: Remove -?
Hi Doug, try: UPDATE table_name SET ProdKeyword = REPLACE(ProdID,-,); prosit Klaus MySQL, Query, whatever, be a keyword :o) - 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
how to show chinese in phpmyadmin
Hi, I am new in programming. So hope to get some helps from your precious experiences. I am using mysql-3.23 in my FreeBsd4.3 (English version) server. And I am also using phpMyAdmin to view and manage all the databases, tables and fields from a workstation IE browser to access my server, which is very convenient and straight forward. I designed some web pages, which allow me to input data records to my database. I can type simplified Chinese and English records from a workstation into my database, and I also can retrieve those Chinese data records from an workstation's IE browser and those Chinese characters can be shown properly, on the condition of those pages' charset is gb2312. There is one problem that when I use phpMyAdmin to access my databases, and I found that those Chinese records in the table only show ÏÖÓ®¢¿â´æ»õÎïÊÇ·ÉÂíÅç©àºÍÄÏ°©Åç©à Which is unreadable. Can anybody tell me how I can fix this problem? Thanks very much Jennifer - 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: How to connect to mysql server without myODBC
www.astatech.com and ZEOS components. -Original Message- From: Liu Qianghua-qch1942 [mailto:[EMAIL PROTECTED] Sent: Saturday, March 01, 2003 11:50 PM To: [EMAIL PROTECTED] Subject: How to connect to mysql server without myODBC All, I want to develop application with Delphi which can connect to remote mysql server. Whether I must install myODBC? If are there another way, please tell me? Thanks. Best Regards, Q.H. Liu - 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
[help] with browsing table?
why am i not able to select the browse link on mysql phpmyadmin to view my table ultimatefootballleague.com/index.php [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: [help] with browsing table?
Is your table empty? [EMAIL PROTECTED] wrote: why am i not able to select the browse link on mysql phpmyadmin to view my table ultimatefootballleague.com/index.php [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 -- Veysel Harun Sahin [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
Indexes
Filter-fodder: mysql, query Hi, How can I avoid Using temporary, Using filesort when creating indices when my selects MUST be ordered? Donna - 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
Extending SquidGuard to work out of a SQL database
All, My company is looking to extend the Squid redirector, SquidGuard to work realtime out of a SQL database. We are looking to target the GNU/Linux environment and the MySql database server. We are doing this for enterprise scalability reasons. This means that all flat-file configuration that currently drives SquidGuard would be run out of the sql tables during runtime. These include the source/destination/rewrite/redirect/acl/log configuration directives as well as the entries for each individual list. Runtime performance is also likely to be an issue. We are interested in coders familiar with Squid,SquidGuard and MySql who would like this job. If you are interested, please send me a message with your estimate and some examples of your work. Group Collaboration efforts are welcome as well. We would provide any reasonable resources needed. www.squid-cache.org www.squidguard.org www.mysql.com Joe Maimon Tri-Tech Associates New York,NY USA - 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: [squid-users] Extending SquidGuard to work out of a SQL database
Joe Maimon wrote: All, My company is looking to extend the Squid redirector, SquidGuard to work realtime out of a SQL database. We are looking to target the GNU/Linux environment and the MySql database server. We are doing this for enterprise scalability reasons. This means that all flat-file configuration that currently drives SquidGuard would be run out of the sql tables during runtime. These include the source/destination/rewrite/redirect/acl/log configuration directives as well as the entries for each individual list. Runtime performance is also likely to be an issue. We are interested in coders familiar with Squid,SquidGuard and MySql who would like this job. If you are interested, please send me a message with your estimate and some examples of your work. Group Collaboration efforts are welcome as well. We would provide any reasonable resources needed. But squidguard is already using BerkelyDB for fast lookups. Do you think this is not fast enough ? M. www.squid-cache.org www.squidguard.org www.mysql.com Joe Maimon Tri-Tech Associates New York,NY USA -- 'Time is a consequence of Matter thus General Relativity is a direct consequence of QM (M.E. Mar 2002) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [squid-users] Extending SquidGuard to work out of a SQL database
Using SQL database would be a big hit in straightforward performance but would be a huge win for the flexibility of rolling it out to many different customers with different needs. Something a Value-Adding ISP would want to use. As for performance, there is always decision caching, defered logging and larger hardware to throw at the problem I know that proprietry solutions are doing this, with central SQL servers and distributed engines - the lag in decision changes almost definitely means that they are caching and they have options to process logging as flat file updates at scheduled intervals, but they use RDBMS. I myself have implemented a National network interception engine that served traffic for 5-10k users running on 5 engines, 1 sql server (Compaq DL380's) Joe M Marc Elsen wrote: Joe Maimon wrote: All, My company is looking to extend the Squid redirector, SquidGuard to work realtime out of a SQL database. We are looking to target the GNU/Linux environment and the MySql database server. We are doing this for enterprise scalability reasons. This means that all flat-file configuration that currently drives SquidGuard would be run out of the sql tables during runtime. These include the source/destination/rewrite/redirect/acl/log configuration directives as well as the entries for each individual list. Runtime performance is also likely to be an issue. We are interested in coders familiar with Squid,SquidGuard and MySql who would like this job. If you are interested, please send me a message with your estimate and some examples of your work. Group Collaboration efforts are welcome as well. We would provide any reasonable resources needed. But squidguard is already using BerkelyDB for fast lookups. Do you think this is not fast enough ? M. www.squid-cache.org www.squidguard.org www.mysql.com Joe Maimon Tri-Tech Associates New York,NY USA - 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
