Strange BUG? with a working table
Dear Ladies and Sirs, I have a problem to create a tables duplicate with: CREATE TABLE new_1 SELECT * FROM output_1; The table output_1 has this structure: Field TypeNullKey af_1int(11) YES MUL af_3varchar(255)YES af_2varchar(255)YES af_23 varchar(255)YES af_10 varchar(255)YES af_11 varchar(255)YES af_10001varchar(255)YES af_16 varchar(255)YES af_9varchar(255)YES af_8varchar(255)YES af_30 varchar(255)YES af_24 varchar(255)YES af_52 varchar(255)YES af_300 varchar(255)YES af_53 varchar(255)YES af_54 varchar(255)YES af_18 varchar(255)YES af_5varchar(255)YES af_25 varchar(255)YES af_26 varchar(255)YES af_27 varchar(255)YES af_28 varchar(255)YES af_55 varchar(255)YES af_56 varchar(255)YES af_57 varchar(255)YES af_58 varchar(255)YES dub_flagint(11) YES MUL dub_kopfint(11) YES dub_purge int(11) YES Type = myisam MySQL = 3.23-41-log OS = SUSE 7.3 Kernel 2.4.10 FS = Reiser This table has about 2,5 million rows and it's file-size 530543 K. When i tried the above statement i got error 27 when the new table reached a size of more then 200 K ! I exported the file to csv and everything seemed fine. I imported again into a new table via LOAD DATA INFILE ... and the same error occured. Is there anyone that has a clue, why this table is changing size with this. Could it be that there is a 'forbidden' character inside the data ? I now try to manage with dump-file but I'd like to know how I can fix the problem itself, because if I join this table with another, the same strange behaviour occurs. CHECK TABLE (extended) said the output_1-Table is OK. please help Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - 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: Strange BUG? with a working table
It seems that you have reached the 2G file limit with many Linux system. I am not familiar with SuSe. As I know, you may try to use raid option. You may refer to MySQL doc. on 'create' for details. KH Dear Ladies and Sirs, I have a problem to create a tables duplicate with: CREATE TABLE new_1 SELECT * FROM output_1; The table output_1 has this structure: Field TypeNullKey af_1 int(11) YES MUL af_3 varchar(255)YES af_2 varchar(255)YES af_23 varchar(255)YES af_10 varchar(255)YES af_11 varchar(255)YES af_10001 varchar(255)YES af_16 varchar(255)YES af_9 varchar(255)YES af_8 varchar(255)YES af_30 varchar(255)YES af_24 varchar(255)YES af_52 varchar(255)YES af_300varchar(255)YES af_53 varchar(255)YES af_54 varchar(255)YES af_18 varchar(255)YES af_5 varchar(255)YES af_25 varchar(255)YES af_26 varchar(255)YES af_27 varchar(255)YES af_28 varchar(255)YES af_55 varchar(255)YES af_56 varchar(255)YES af_57 varchar(255)YES af_58 varchar(255)YES dub_flag int(11) YES MUL dub_kopf int(11) YES dub_purge int(11) YES Type = myisam MySQL = 3.23-41-log OS = SUSE 7.3 Kernel 2.4.10 FS = Reiser This table has about 2,5 million rows and it's file-size 530543 K. When i tried the above statement i got error 27 when the new table reached a size of more then 200 K ! I exported the file to csv and everything seemed fine. I imported again into a new table via LOAD DATA INFILE ... and the same error occured. Is there anyone that has a clue, why this table is changing size with this. Could it be that there is a 'forbidden' character inside the data ? I now try to manage with dump-file but I'd like to know how I can fix the problem itself, because if I join this table with another, the same strange behaviour occurs. CHECK TABLE (extended) said the output_1-Table is OK. please help Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - 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 mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Yours, KH Chiu CA Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.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
BUG Mysql 3.23.54
When using the client library with php: Earler(earler mysql version) mysqllib: SELECT userfield.*,user.* FROM . [1] =123 [userid] = 123 Now with MySQL 3.23.54 mysqllib: SELECT userfield.*,user.* FROM . [1] =123 [userid] = Here you can see that mysql doesn't give the userid anymore if the userid's are the key to join 2 tables. I corrected this with: SELECT userfield.*,user.*,user.userid as userid FROM . [1] =123 [userid] = 123 Is this is a bug? Or a behaviour change? With kind regards, Gijs - 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
sql select from question
Hello list, I want to select all the columns in a table except one column. Can I do this without writing all the column names to the query? For example assume that I have table with the name table1 and there are 50 columns in table1 with the names col1, col2, col3.col50. To be able to select all the columns except col35 I have to write a query like SELECT col1, col2, , col34, col36, col50 FROM table1. Is there a simpler way to do this for example using not operator with the column name col35? Thanks. sql, query, select vb. -- 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
AW: Strange BUG? with a working table
Hi Chiu, It seems that you have reached the 2G file limit with many Linux system. I am not familiar with SuSe. As I know, you may try to use raid option. You may refer to MySQL doc. on 'create' for details. Yes I know, that I can just manage files to 2 GB. The question was, why is the new tables, that should just be the copy of output_1, more than 3 times bigger. I didn't add any data, neither did I change structure. may refer to MySQL doc. on 'create' for details. I tried without CEATE ... SELECT (via LOAD DATA INFILE) and it didn't work either. Did I miss something in the docs ? thanks and greetings Klaus KH Dear Ladies and Sirs, I have a problem to create a tables duplicate with: CREATE TABLE new_1 SELECT * FROM output_1; The table output_1 has this structure: FieldTypeNullKey af_1 int(11) YES MUL af_3 varchar(255)YES af_2 varchar(255)YES af_23varchar(255)YES af_10varchar(255)YES af_11varchar(255)YES af_10001 varchar(255)YES af_16varchar(255)YES af_9 varchar(255)YES af_8 varchar(255)YES af_30varchar(255)YES af_24varchar(255)YES af_52varchar(255)YES af_300 varchar(255)YES af_53varchar(255)YES af_54varchar(255)YES af_18varchar(255)YES af_5 varchar(255)YES af_25varchar(255)YES af_26varchar(255)YES af_27varchar(255)YES af_28varchar(255)YES af_55varchar(255)YES af_56varchar(255)YES af_57varchar(255)YES af_58varchar(255)YES dub_flag int(11) YES MUL dub_kopf int(11) YES dub_purgeint(11) YES Type = myisam MySQL = 3.23-41-log OS = SUSE 7.3 Kernel 2.4.10 FS = Reiser This table has about 2,5 million rows and it's file-size 530543 K. When i tried the above statement i got error 27 when the new table reached a size of more then 200 K ! I exported the file to csv and everything seemed fine. I imported again into a new table via LOAD DATA INFILE ... and the same error occured. Is there anyone that has a clue, why this table is changing size with this. Could it be that there is a 'forbidden' character inside the data ? I now try to manage with dump-file but I'd like to know how I can fix the problem itself, because if I join this table with another, the same strange behaviour occurs. CHECK TABLE (extended) said the output_1-Table is OK. please help Klaus - 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: Strange BUG? with a working table
Compare tables with : =show create table t1; =show create table t2; Check table type. Or use dump command from shell: shellmysqldump -pPassword --user=username db_name t1 t1.sql mysqlrename table t1 to t2; shellmysql -pPassword --user=username t1.sql db_name Or try to set MIN_ROWS = 2,5 million. Dear Ladies and Sirs, I have a problem to create a tables duplicate with: CREATE TABLE new_1 SELECT * FROM output_1; The table output_1 has this structure: Field TypeNullKey af_1 int(11) YES MUL af_3 varchar(255)YES af_2 varchar(255)YES af_23 varchar(255)YES af_10 varchar(255)YES af_11 varchar(255)YES af_10001 varchar(255)YES af_16 varchar(255)YES af_9 varchar(255)YES af_8 varchar(255)YES af_30 varchar(255)YES af_24 varchar(255)YES af_52 varchar(255)YES af_300varchar(255)YES af_53 varchar(255)YES af_54 varchar(255)YES af_18 varchar(255)YES af_5 varchar(255)YES af_25 varchar(255)YES af_26 varchar(255)YES af_27 varchar(255)YES af_28 varchar(255)YES af_55 varchar(255)YES af_56 varchar(255)YES af_57 varchar(255)YES af_58 varchar(255)YES dub_flag int(11) YES MUL dub_kopf int(11) YES dub_purge int(11) YES Type = myisam MySQL = 3.23-41-log OS = SUSE 7.3 Kernel 2.4.10 FS = Reiser This table has about 2,5 million rows and it's file-size 530543 K. When i tried the above statement i got error 27 when the new table reached a size of more then 200 K ! I exported the file to csv and everything seemed fine. I imported again into a new table via LOAD DATA INFILE ... and the same error occured. Is there anyone that has a clue, why this table is changing size with this. Could it be that there is a 'forbidden' character inside the data ? I now try to manage with dump-file but I'd like to know how I can fix the problem itself, because if I join this table with another, the same strange behaviour occurs. CHECK TABLE (extended) said the output_1-Table is OK. please help Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - 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 mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Yours, KH Chiu CA Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.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: Strange BUG? with a working table
Use BIGINT use altertable ...that will take a week Russell Griechen - Original Message - From: Franz, Fa. PostDirekt MA [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 2:35 AM Subject: Strange BUG? with a working table Dear Ladies and Sirs, I have a problem to create a tables duplicate with: CREATE TABLE new_1 SELECT * FROM output_1; The table output_1 has this structure: Field Type Null Key af_1 int(11) YES MUL af_3 varchar(255) YES af_2 varchar(255) YES af_23 varchar(255) YES af_10 varchar(255) YES af_11 varchar(255) YES af_10001 varchar(255) YES af_16 varchar(255) YES af_9 varchar(255) YES af_8 varchar(255) YES af_30 varchar(255) YES af_24 varchar(255) YES af_52 varchar(255) YES af_300 varchar(255) YES af_53 varchar(255) YES af_54 varchar(255) YES af_18 varchar(255) YES af_5 varchar(255) YES af_25 varchar(255) YES af_26 varchar(255) YES af_27 varchar(255) YES af_28 varchar(255) YES af_55 varchar(255) YES af_56 varchar(255) YES af_57 varchar(255) YES af_58 varchar(255) YES dub_flag int(11) YES MUL dub_kopf int(11) YES dub_purge int(11) YES Type = myisam MySQL = 3.23-41-log OS = SUSE 7.3 Kernel 2.4.10 FS = Reiser This table has about 2,5 million rows and it's file-size 530543 K. When i tried the above statement i got error 27 when the new table reached a size of more then 200 K ! I exported the file to csv and everything seemed fine. I imported again into a new table via LOAD DATA INFILE ... and the same error occured. Is there anyone that has a clue, why this table is changing size with this. Could it be that there is a 'forbidden' character inside the data ? I now try to manage with dump-file but I'd like to know how I can fix the problem itself, because if I join this table with another, the same strange behaviour occurs. CHECK TABLE (extended) said the output_1-Table is OK. please help Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - 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: sql select from question
On 24-Feb-2003 Veysel Harun Sahin wrote:
Hello list,
I want to select all the columns in a table except one column. Can I do
this without writing all the column names to the query? For example
assume that I have table with the name table1 and there are 50 columns
in table1 with the names col1, col2, col3.col50. To be able to
select all the columns except col35 I have to write a query like SELECT
col1, col2, , col34, col36, col50 FROM table1. Is there a
simpler way to do this for example using not operator with the column
name col35?
Not that I know of.
Depending on the application language it might be easier to post-process:
$result = mysql_query('SELECT * FROM mytable');
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
unset($row[34]);
echo 'The values :', implode(', ', $row), 'br';
}
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table 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
hpux Unresolved symbol Abort(coredump)
Description: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FI_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FD_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/local/mysql/bin/mysql_install_db[292]: 12966 Abort(coredump) Installation of grant tables failed! How-To-Repeat: /usr/local/mysql/bin/mysql_install_db Fix: not known Submitter-Id: submitter ID Originator:MySQL Database User Organization: Hauni Maschinenbau AG MySQL support: none Synopsis: install mysql hpux 11.0 Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.54 (Source distribution) Environment: System: HP-UX khs034 B.11.11 U 9000/800 2006554011 unlimited-user license Some paths: /usr/contrib/bin/perl /usr/bin/make /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/hppa2.0n-hp-hpux11.00/3.1/specs Configured with: ./configure : (reconfigured) ./configure : (reconfigured) ./configure Thread model: posix gcc version 3.1 Compilation info: CC='gcc' CFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' CXX='g++' CXXFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' LDFLAGS='-L/usr/local/lib' LIBC: lrwxr-xr-x 1 root root 8 Jun 3 2002 /lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /lib/libc.sl - /usr/lib/libc.2 lrwxr-xr-x 1 root root 8 Jun 3 2002 /usr/lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /usr/lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /usr/lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /usr/lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /usr/lib/libc.sl - /usr/lib/libc.2 Configure command: ./Configure '--with-pthread' '--prefix=/usr/local/mysql' '--exec-prefix=/usr/local/mysql' '--with-named-thread-libs=-lpthread' '--with-low-memory' 'CC=gcc' 'CFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CPPFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXXFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXX=g++' 'LDFLAGS=-L/usr/local/lib' - 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
hpux Unresolved symbol Abort(coredump)
Description: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FI_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FD_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/local/mysql/bin/mysql_install_db[292]: 12966 Abort(coredump) Installation of grant tables failed! How-To-Repeat: /usr/local/mysql/bin/mysql_install_db Fix: not known Submitter-Id: submitter ID Originator:MySQL Database User Organization: Hauni Maschinenbau AG MySQL support: none Synopsis: install mysql hpux 11.0 Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.54 (Source distribution) Environment: System: HP-UX khs034 B.11.11 U 9000/800 2006554011 unlimited-user license Some paths: /usr/contrib/bin/perl /usr/bin/make /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/hppa2.0n-hp-hpux11.00/3.1/specs Configured with: ./configure : (reconfigured) ./configure : (reconfigured) ./configure Thread model: posix gcc version 3.1 Compilation info: CC='gcc' CFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' CXX='g++' CXXFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' LDFLAGS='-L/usr/local/lib' LIBC: lrwxr-xr-x 1 root root 8 Jun 3 2002 /lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /lib/libc.sl - /usr/lib/libc.2 lrwxr-xr-x 1 root root 8 Jun 3 2002 /usr/lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /usr/lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /usr/lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /usr/lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /usr/lib/libc.sl - /usr/lib/libc.2 Configure command: ./Configure '--with-pthread' '--prefix=/usr/local/mysql' '--exec-prefix=/usr/local/mysql' '--with-named-thread-libs=-lpthread' '--with-low-memory' 'CC=gcc' 'CFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CPPFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXXFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXX=g++' 'LDFLAGS=-L/usr/local/lib' - 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
hpux Unresolved symbol Abort(coredump)
Description: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FI_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FD_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/local/mysql/bin/mysql_install_db[292]: 12966 Abort(coredump) Installation of grant tables failed! How-To-Repeat: /usr/local/mysql/bin/mysql_install_db Fix: not known Submitter-Id: submitter ID Originator:MySQL Database User Organization: Hauni Maschinenbau AG MySQL support: none Synopsis: install mysql hpux 11.0 Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.54 (Source distribution) Environment: System: HP-UX khs034 B.11.11 U 9000/800 2006554011 unlimited-user license Some paths: /usr/contrib/bin/perl /usr/bin/make /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/hppa2.0n-hp-hpux11.00/3.1/specs Configured with: ./configure : (reconfigured) ./configure : (reconfigured) ./configure Thread model: posix gcc version 3.1 Compilation info: CC='gcc' CFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' CXX='g++' CXXFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' LDFLAGS='-L/usr/local/lib' LIBC: lrwxr-xr-x 1 root root 8 Jun 3 2002 /lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /lib/libc.sl - /usr/lib/libc.2 lrwxr-xr-x 1 root root 8 Jun 3 2002 /usr/lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /usr/lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /usr/lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /usr/lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /usr/lib/libc.sl - /usr/lib/libc.2 Configure command: ./Configure '--with-pthread' '--prefix=/usr/local/mysql' '--exec-prefix=/usr/local/mysql' '--with-named-thread-libs=-lpthread' '--with-low-memory' 'CC=gcc' 'CFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CPPFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXXFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXX=g++' 'LDFLAGS=-L/usr/local/lib' - 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
hpux Unresolved symbol Abort(coredump)
Description: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FI_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FD_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/local/mysql/bin/mysql_install_db[292]: 12966 Abort(coredump) Installation of grant tables failed! How-To-Repeat: /usr/local/mysql/bin/mysql_install_db Fix: not known Submitter-Id: submitter ID Originator:MySQL Database User Organization: Hauni Maschinenbau AG MySQL support: none Synopsis: install mysql hpux 11.0 Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.54 (Source distribution) Environment: System: HP-UX khs034 B.11.11 U 9000/800 2006554011 unlimited-user license Some paths: /usr/contrib/bin/perl /usr/bin/make /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/hppa2.0n-hp-hpux11.00/3.1/specs Configured with: ./configure : (reconfigured) ./configure : (reconfigured) ./configure Thread model: posix gcc version 3.1 Compilation info: CC='gcc' CFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' CXX='g++' CXXFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' LDFLAGS='-L/usr/local/lib' LIBC: lrwxr-xr-x 1 root root 8 Jun 3 2002 /lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /lib/libc.sl - /usr/lib/libc.2 lrwxr-xr-x 1 root root 8 Jun 3 2002 /usr/lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /usr/lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /usr/lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /usr/lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /usr/lib/libc.sl - /usr/lib/libc.2 Configure command: ./Configure '--with-pthread' '--prefix=/usr/local/mysql' '--exec-prefix=/usr/local/mysql' '--with-named-thread-libs=-lpthread' '--with-low-memory' 'CC=gcc' 'CFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CPPFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXXFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXX=g++' 'LDFLAGS=-L/usr/local/lib' - 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: sql select from question
Thanks dread. Yes it can be done programmatically. But i try to do only
with sql. I don't want to hardcode it.
[EMAIL PROTECTED] wrote:
On 24-Feb-2003 Veysel Harun Sahin wrote:
Hello list,
I want to select all the columns in a table except one column. Can I do
this without writing all the column names to the query? For example
assume that I have table with the name table1 and there are 50 columns
in table1 with the names col1, col2, col3.col50. To be able to
select all the columns except col35 I have to write a query like SELECT
col1, col2, , col34, col36, col50 FROM table1. Is there a
simpler way to do this for example using not operator with the column
name col35?
Not that I know of.
Depending on the application language it might be easier to post-process:
$result = mysql_query('SELECT * FROM mytable');
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
unset($row[34]);
echo 'The values :', implode(', ', $row), 'br';
}
Regards,
--
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
FW: vacancy
INTAS is an independent International Association formed by the European Community, European Union's Member States and like minded countries acting to preserve and promote the valuable scientific potential of the NIS partner countries through East-West Scientific co-operation. We are currently looking to reinforce our IT team. We are looking for the following profile: You have minimum 2 years experience in a database driven environment, of which at least 1 year in SQL, You have succesfully completed one or more Microsoft Curriculum exams, You have a proven competence in .NET, ASP, VB, XML, HTML etc... You can administer the latest Windows operating systems and office packages You can operate without problems in an English-speaking environment. INTAS can offer the following opportunities: Initially you will assist in the development of a complete new infrastructure, whereafter you will: administer web- and SQL servers be the helpdesk for users regarding databases create/update queries and forms for internal users web updates, support and creation of new html and ASP codes share the helpdesk workload with your colleagues. You are invited to send your CV with accompanying letter to: [EMAIL PROTECTED] === Kim Schotanus Information Systems Manager INTAS Avenue des Arts 58 B-1000 Brussels Belgium T. +32 2 549 01 11 F. +32 2 549 01 56 http://www.intas.be === - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to query an entire row?
Hello, In a table like this: ID Item1 char(100) Item2 char(100) . . ItemN char(100) What's the cleanest way to do this mysql query: SELECT * FROM MyTable WHERE AnyColumn LIKE '%mysearch%' Only way I can think to do it is: SELECT * FROM MyTable WHERE (Item1 LIKE '%mysearch%' OR Item2 LIKE '%mysearch%' OR ) Many thanks, 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:Re:Speed improvement with packet proceeding!?
Dear Don, KH Chiu, List, P.S (Chiu): Proceed as is in INSERT VALUES (),()...N? Oops! What about futured stored procedures in Mysql 5.x ? Anyway... Yes, there is a problems with error handling and proceeding but when you use INSERT ... VALUES (),().. (for example 5 rows and after 3th rows mysql fail to proceed with last 2, these 3 rows will *not* be removed from db i.e INSERT will succeed particualry.. is it a bug in mysql?) Moreover in all packets (batch) operations (not only in database/mysql sense) when some of queries fail..all other queries in the packet fails too. Of course I thought about problem how to return multiple results for example in two or more selects... however I'm not familar with mysql core.. :( BUT if mysql team release cache for futured operations..no,no.. AMD,INTEL processors do exaly the same to speed up execution of CPU operations... So if application *tells* to mysql that it will proceed two select queries to same table, mysql may execute second operation in advance: FUTURE - imaginary SQL statement: FUTURE select_1 * from table where..., select_2 * from table where... So when Mysql execute select_1: select * from table where... it *may* proceed/cache select_2 Example: $sth = $dbh-prepare(FUTURE select * from table where id=52 and name='some', select * from table where id=23 and email='[EMAIL PROTECTED]'); ... $sth-execute(); ... # Execute first query $sth = $dbh-prepare(select * from table where id=52 and name='some'); $sth-execute(); ... # Execute second query $sth = $dbh-prepare(select * from table where id=23 and email='[EMAIL PROTECTED]'); $sth-execute(); So Mysql *may* optimize these two queries; to test conditions, but: to execute them separately. Of course when error occure in first query Mysql will *not* be able to optimize them... Moreover, if Mysql proceed 1/2 of DB file with first query (for example restricted with LIMIT), Mysql still may do a particular cache/optimizations. Imaginary sql statement FUTURE may be used for other optimizations too! The main idea was: In fact I'm not talking only about 'select'-s but any tables *examination*/*traverse* (i.e where clauses etc..), so queries like these could be also speed up I know you are a clever guys, so *Mysql* will obtain the MAXIMUM from that idea ;-) Here I will answare to question about multi-user nature of Mysql: Due Mysql reads only once from database (hard disk) i.e. proceed two queries (conditions) for single row read, there will *not* be penalty for disk seeks/read, because when given row is proceeded it *should* be read in memory the whole. However in case when you apply bulk update to different tables the things getting complicated :(, but with FUTURE-like statement there are still possibilities for optimizations :) On 23-Feb-2003 Julian wrote: Speed improvement with packet proceeding!? 1. Packet proceeding: I'm not quite sure is it possible with Mysql but it could be easy to be implemented. For example: select * from table where id=52 and name='some' select * from table where id=23 and email='[EMAIL PROTECTED]' these two queries select row(s) from table 'table' which means that these two queries could be tested simultaneously, so database file will be proceed only once. What about the case where one (or both) selects fail? And how would the application tell that there were multiple rows where id=23 and email='[EMAIL PROTECTED]' ? If you *know* that these two rows exist and unique then: SELECT a.*,b.* from table as a, table as b WHERE a.id=52 and a.name='some' AND b.id=23 and b.email='[EMAIL PROTECTED]' would do the same thing. snipage In fact I'm not talking only about 'select'-s but any tables examination/traverse (i.e where clauses etc..), so queries like these could be also speed up: update table set data='test' where name='some' select * from table where id=10 What would be the sensible error message if your update failed but your select succeeds ? What would be the expected value of mysql_numrows() ? And what if there are multiple rows where id=10 ? snipage 2. Bulk update/delete etc.. Take a look at this update query (not implemented.. yet!) update table1 set column=value,... where clause limit #, update table2 set (or delete from table1 where clause limit rows, delete from table2 where clause...) The same question: How would a program tell which statement failed? snip again Have I a good point here? Any ideas and discussion about these suggestions? You'll need to rethink the failure modes. What you're suggesting might be useful in certain special case(s) but badly fails the general case. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it.
MySQL on windows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi I am used to using/accessing MySQL on Unix, but I have to write a small client application on Windows, to record and log activity to a remote MySQL database. The options that I have appear to be - - Perl + DBI+DBD-ODBC - - Perl + DBI+DBD-mysql - - Visual C++ + ODBC - - Visual C++ + MySQL++ can anyone advice me on the best to use, for stability, and no memory leaks etc. Many thanks Simon - -- Simon Windsor Email: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07720 447385 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+Wg/bSJvgVrMNWjYRAkNvAJ0VKk1D0t7EOEigG3FssqK1ahEz3wCdEvXd H7oZniZ/Ay5Yyk/jPMbAhUE= =rOZS -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL on windows
Hi Simon, The options that I have appear to be - - Perl + DBI+DBD-ODBC - - Perl + DBI+DBD-mysql - - Visual C++ + ODBC - - Visual C++ + MySQL++ What about Java + JDBC? Rob :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question mark becomes NULL
Greetings, I have this strange problem. I use a perl script DBI to insert records into a mySQL database. This database holds 1 text-column. Now when this text-field holds any question marks, they appear in the record as`NULL`. I went thrue the mySQL docs to find out how to avoid this but to no avail. Anyone any thoughts? Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to query an entire row?
Hello. On Mon 2003-02-24 at 11:28:05 +, [EMAIL PROTECTED] wrote: Hello, In a table like this: ID Item1 char(100) Item2 char(100) . . ItemN char(100) What's the cleanest way to do this mysql query: SELECT * FROM MyTable WHERE AnyColumn LIKE '%mysearch%' Only way I can think to do it is: SELECT * FROM MyTable WHERE (Item1 LIKE '%mysearch%' OR Item2 LIKE '%mysearch%' OR ) Yes, that's it. If you think that's unpretty, you are right. With a normalized design, you usually shouldn't need such a query. In other words, if you find yourself needing to do such queries regularly, you may want to re-evaluate your database design. Depending on the context, a look at FULLTEXT indexes may be helpful, too. HTH, Benjamin. -- [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: MySQL on windows
Depends on what you are experienced with on win platform I would of thought. Something like vB or Delphi is very quick and easy to use. Jerry - Original Message - From: Simon Windsor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 12:28 PM Subject: MySQL on windows -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi I am used to using/accessing MySQL on Unix, but I have to write a small client application on Windows, to record and log activity to a remote MySQL database. The options that I have appear to be - - Perl + DBI+DBD-ODBC - - Perl + DBI+DBD-mysql - - Visual C++ + ODBC - - Visual C++ + MySQL++ can anyone advice me on the best to use, for stability, and no memory leaks etc. Many thanks Simon - -- Simon Windsor Email: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07720 447385 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+Wg/bSJvgVrMNWjYRAkNvAJ0VKk1D0t7EOEigG3FssqK1ahEz3wCdEvXd H7oZniZ/Ay5Yyk/jPMbAhUE= =rOZS -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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 on windows
In my experience, Perl + DBD-mysql will be the most easy tool for UNIXer (Linuxer). Especially, if you plan to write some non-GUI based system service program. Best regards, KH Depends on what you are experienced with on win platform I would of thought. Something like vB or Delphi is very quick and easy to use. Jerry - Original Message - From: Simon Windsor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 12:28 PM Subject: MySQL on windows -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi I am used to using/accessing MySQL on Unix, but I have to write a small client application on Windows, to record and log activity to a remote MySQL database. The options that I have appear to be - - Perl + DBI+DBD-ODBC - - Perl + DBI+DBD-mysql - - Visual C++ + ODBC - - Visual C++ + MySQL++ can anyone advice me on the best to use, for stability, and no memory leaks etc. Many thanks Simon - -- Simon Windsor Email: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07720 447385 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+Wg/bSJvgVrMNWjYRAkNvAJ0VKk1D0t7EOEigG3FssqK1ahEz3wCdEvXd H7oZniZ/Ay5Yyk/jPMbAhUE= =rOZS -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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 mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Yours, KH Chiu CA Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: How to query an entire row?
At 13:53 24/02/03 +0100, you wrote: Hello. On Mon 2003-02-24 at 11:28:05 +, [EMAIL PROTECTED] wrote: Hello, In a table like this: ID Item1 char(100) Item2 char(100) . . ItemN char(100) What's the cleanest way to do this mysql query: SELECT * FROM MyTable WHERE AnyColumn LIKE '%mysearch%' Only way I can think to do it is: SELECT * FROM MyTable WHERE (Item1 LIKE '%mysearch%' OR Item2 LIKE '%mysearch%' OR ) Yes, that's it. If you think that's unpretty, you are right. With a normalized design, you usually shouldn't need such a query. In other words, if you find yourself needing to do such queries regularly, you may want to re-evaluate your database design. Can I not do: WHERE CONCAT(Item1,Item2,ItemN) LIKE '%mysearch%' ? 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
Heiki, InnoDB crash #2 (update)
The patch got my server up and going again for a bit, but now it has died again after running for about 8 hours or so. InnoDB: Error: trying to access a stray pointer c22fbff8 InnoDB: buf pool start is at 413cc000, number of pages 1024 030223 19:14:31 InnoDB: Assertion failure in thread 66581 in file ../include/buf0buf.ic line 284 InnoDB: We intentionally generate a memory trap. Stack trace: 0x80cd61f handle_segfault__Fi + 379 0x40029532 _end + 935703418 0x82ae78f page_cur_search_with_match + 1807 0x821af59 btr_cur_search_to_nth_level + 2889 0x81e425e row_ins_index_entry_low + 182 0x81e49bf row_ins_index_entry + 59 0x81e4a5c row_ins_index_entry_step + 116 0x81e4d1d row_ins + 693 0x81e4e6a row_ins_step + 278 0x81e606d row_insert_for_mysql + 457 0x811ce6d write_row__11ha_innobasePc + 977 0x80f8df8 write_record__FP8st_tableP12st_copy_info + 484 0x80f86ef mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15enum_duplicates13thr_lock_type + 1043 0x80d4a4b mysql_execute_command__Fv + 5311 0x80d68e3 mysql_parse__FP3THDPcUi + 63 0x80d2bed do_command__FP3THD + 1181 0x80d21cb handle_one_connection__FPv + 563 And now that its running again, I'm going to be doing a full backup and then rebuild my data files for a full restore. Should I keep my original files for your reference/debugging? -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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
AUTO_INCREMENT and INDEX feature?
Hi All, I have MyISAM table with auto_increment property. CREATE TABLE rcatdb_categories ( ID bigint(15) NOT NULL auto_increment, ... PRIMARY KEY (ID), ... however I would like to create as smaller as possible index for ID (by default this is automaticaly done with PRIMARY KEY..), but bigint(15) is too big, I would like to use for example MEDIUMINT or whatever smaller. The problem here is that auto_increment value can become to fast to the limit of MEDIUMINT, because I will have often insert/delete statment. For *example* if the limit of *some* column type is ID=65535 and I have insert/delete 1 rows for 5 days I will not be able to insert new rows. Moreover I will put every day in DB aprox. 1 free records (because I will insert 1 rows and after that I *could* delete 1 rows too, but table never will become empty), so there will be available aprox 55000 free indexes but the next auto_increment value will be 65536 (i.e every day auto_increment value will be inc. with 1 and one pretty day auto_increment will become over than 65535 - limit, but in the table I will have only 1-15000 rows)! If I chose another coulumn type for example with 4G limit the size of INDEX will become bigger and slower. Another way is to strart with SMALLINT..and when auto_increment takes to the limit of maximum allowed value I could do automatic ALTER and substitute SMALLINT with MEDIUMINT...after that with BIGINT..and so on... but if my table has too small rows..for example 2 milion I would not like to use BIGINT only because value of auto_increment :-( I know about myisamchk and --set-auto-increment[=value] option.. so how I could reset safely auto_increment value using SQL query? I will find min value with SELECT MIN(ID), but how to update auto_increment value for respective table? __ 12MB-POP3-WAP-SMS-AHTCAM--TOBA-E-mail.bG -- HOB EATEH APEC - http://mail.bg/new/ -- - 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 on windows
As I said, all depends on what you are used to , as to which is going to be the least painful path. Jerry - Original Message - From: KH Chiu [EMAIL PROTECTED] To: Jerry [EMAIL PROTECTED]; Simon Windsor [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 1:18 PM Subject: Re: MySQL on windows In my experience, Perl + DBD-mysql will be the most easy tool for UNIXer (Linuxer). Especially, if you plan to write some non-GUI based system service program. Best regards, KH Depends on what you are experienced with on win platform I would of thought. Something like vB or Delphi is very quick and easy to use. Jerry - Original Message - From: Simon Windsor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 12:28 PM Subject: MySQL on windows -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi I am used to using/accessing MySQL on Unix, but I have to write a small client application on Windows, to record and log activity to a remote MySQL database. The options that I have appear to be - - Perl + DBI+DBD-ODBC - - Perl + DBI+DBD-mysql - - Visual C++ + ODBC - - Visual C++ + MySQL++ can anyone advice me on the best to use, for stability, and no memory leaks etc. Many thanks Simon - -- Simon Windsor Email: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07720 447385 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+Wg/bSJvgVrMNWjYRAkNvAJ0VKk1D0t7EOEigG3FssqK1ahEz3wCdEvXd H7oZniZ/Ay5Yyk/jPMbAhUE= =rOZS -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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 mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Yours, KH Chiu CA Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.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
Large table or several tables ?
Hye the list, There is my problem. I have to register about 400 large arrays (~1 lines each) in my MySQL database and I don't know which of the following choices is better. Either create a large tables as a stack, which contains all arrays Either create as many tables as there are arrays and use references to each table. Any advice is welcome. Thank you very much Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question mark becomes NULL
On 24 Feb 2003, at 13:42, Daneman wrote: I use a perl script DBI to insert records into a mySQL database. This database holds 1 text-column. Now when this text-field holds any question marks, they appear in the record as`NULL`. Post the Perl code you're using for the insert query. It sounds like you're doing something wrong with placeholders. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: Heiki, InnoDB crash #2 (update)
Michael, this is further evidence that the corruption last time was not a bug in the insert buffer, but this is general Linux table corruption. I already suggested that you should upgrade to Linux-2.4.20 or some other recent kernel. Please send me the full .err log. If you can keep the old corrupt files, that is nice, but probably not needed. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL sql query Subject: Heiki, InnoDB crash #2 (update) From: Michael T. Babcock Date: Mon, 24 Feb 2003 08:11:57 -0500 The patch got my server up and going again for a bit, but now it has died again after running for about 8 hours or so. InnoDB: Error: trying to access a stray pointer c22fbff8 InnoDB: buf pool start is at 413cc000, number of pages 1024 030223 19:14:31 InnoDB: Assertion failure in thread 66581 in file ../include/buf0buf.ic line 284 InnoDB: We intentionally generate a memory trap. Stack trace: 0x80cd61f handle_segfault__Fi + 379 0x40029532 _end + 935703418 0x82ae78f page_cur_search_with_match + 1807 0x821af59 btr_cur_search_to_nth_level + 2889 0x81e425e row_ins_index_entry_low + 182 0x81e49bf row_ins_index_entry + 59 0x81e4a5c row_ins_index_entry_step + 116 0x81e4d1d row_ins + 693 0x81e4e6a row_ins_step + 278 0x81e606d row_insert_for_mysql + 457 0x811ce6d write_row__11ha_innobasePc + 977 0x80f8df8 write_record__FP8st_tableP12st_copy_info + 484 0x80f86ef mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15en um_duplicates13thr_lock_type + 1043 0x80d4a4b mysql_execute_command__Fv + 5311 0x80d68e3 mysql_parse__FP3THDPcUi + 63 0x80d2bed do_command__FP3THD + 1181 0x80d21cb handle_one_connection__FPv + 563 And now that its running again, I'm going to be doing a full backup and then rebuild my data files for a full restore. Should I keep my original files for your reference/debugging? -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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: Speed improvement with packet proceeding!?
It sounds like you're suggestion something like Oracle does with PL/SQL, which I think is the better way to go. Then it's really up to the user to decide how to optimize multiple steps by creating a batch of commands to be run. On Sunday, February 23, 2003, at 03:57 PM, Julian wrote: I'm not quite sure is it possible with Mysql but it could be easy to be implemented. For example: select * from table where id=52 and name='some' select * from table where id=23 and email='[EMAIL PROTECTED]' these two queries select row(s) from table 'table' which means that these two queries could be tested simultaneously, so database file will be proceed only once. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:Re: AUTO_INCREMENT and INDEX feature?
No, you understood me wrong. MEDIUMINT takes only 8 bytes, but BIGINT takes 20, so the whole DB grows, moreover in Mysql documentation I read about indexes and column optimization and thay sad: medium int is always better than int - in context of that article. I would like to do as small as possible my db, so select-s to be much faster using index/key. However I would like to support a large number of possible rows(to be inserted in respective tables). Also auto_increment remember the biggest value, which not means that table can't takes no more rows, i.e. INSERT_ID = MAX_VALUE and I could not insert new rows, no matter whether I have only 1 row in that table... So the only reasonable solution was to ALTER table whenever ID reach the maximum available value for respective column type. I could start with MEDIUMINT type and finish with BIGINT, but I'm wondering is it another way to do that? Hi Julian, If I understand your question, you have mistaken how indexes work. The size of an index field depends upon the number of entries in it, not the size of the largest/smallest entries. There is no such thing as free indexes in MySQL. The index is not an array, it is a b-tree. So as long as the total number of entries in your table stays the same, so will (roughly) the size of the index. You can check this by doing the following: create table indexed ( a bigint(15)not null, primary key (a) ) ; insert into indexed values (0), (1234567890123) ; then checking the size of the indexed.MYI file. It is only 2048 bytes (1 block) on my system, not 1234567890123x15 bytes as you seem to think, and it probably won't get any bigger until you put in about 100 rows. Alec Mysql --- - I have MyISAM table with auto_increment property. CREATE TABLE rcatdb_categories ( ID bigint(15) NOT NULL auto_increment, ... PRIMARY KEY (ID), ... however I would like to create as smaller as possible index for ID (by default this is automaticaly done with PRIMARY KEY..), but bigint(15) is too big, I would like to use for example MEDIUMINT or whatever smaller. The problem here is that auto_increment value can become to fast to the limit of MEDIUMINT, because I will have often insert/delete statment. For *example* if the limit of *some* column type is ID=65535 and I have insert/delete 1 rows for 5 days I will not be able to insert new rows. Moreover I will put every day in DB aprox. 1 free records (because I will insert 1 rows and after that I *could* delete 1 rows too, but table never will become empty), so there will be available aprox 55000 free indexes but the next auto_increment value will be 65536 (i.e every day auto_increment value will be inc. with 1 and one pretty day auto_increment will become over than 65535 - limit, but in the table I will have only 1-15000 rows)! If I chose another coulumn type for example with 4G limit the size of INDEX will become bigger and slower. Another way is to strart with SMALLINT..and when auto_increment takes to the limit of maximum allowed value I could do automatic ALTER and substitute SMALLINT with MEDIUMINT...after that with BIGINT..and so on... but if my table has too small rows..for example 2 milion I would not like to use BIGINT only because value of auto_increment :-( I know about myisamchk and --set-auto-increment[=value] option.. so how I could reset safely auto_increment value using SQL query? I will find min value with SELECT MIN(ID), but how to update auto_increment value for respective table? __ 12MB-POP3-WAP-SMS-AHTCAM--TOBA-E-mail.bG -- HOB EATEH APEC - http://mail.bg/new/ -- - 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 __ 12MB-POP3-WAP-SMS-AHTCAM--TOBA-E-mail.bG -- HOB EATEH APEC - http://mail.bg/new/ -- - 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: Large table or several tables ?
Depends on in which manner you are going to be using the dB I'd suggest, i.e. just for storage for the arrays or querying the data while it is in the dB. If the data is all related and you are going to be running queries across the whole data set I'd put it all in one table, if the quires that you are running are specific that you can narrow it down to a table (i.e. design it so that the application accessing the dB knows what table to go for) do that, because then the isolation would provided better management. Personally I'd use different tables, but the amount of data is no where near an issue, I run a app against mysql that has arrays around 250-300K at the moment and its fine. Jerry - Original Message - From: gregory lefebvre [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 3:07 PM Subject: Large table or several tables ? Hye the list, There is my problem. I have to register about 400 large arrays (~1 lines each) in my MySQL database and I don't know which of the following choices is better. Either create a large tables as a stack, which contains all arrays Either create as many tables as there are arrays and use references to each table. Any advice is welcome. Thank you very much Greg - 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: mysqld got signal 11; This could be because you hit a bug ....
Hello, I have followed your suggestion. I have installed mysql-debug-4.0.10-gamma-sun-solaris2.9-sparc-64bit on the Solaris 8 (Machine with 8 cpus, 32GB of Memory and 64 of swap) server. I have started the mysqld program as root I have specified the core-file in the /etc/my.cnf file. Other info that could be interesting in the /etc/my.cnf are _ set-variable = max_connections=16 set-variable = max_tmp_tables=512 set-variable= table_cache=512 set-variable = key_buffer=16384M set-variable= sort_buffer=1K set-variable= record_buffer=1K set-variable= thread_cache_size=8 set-variable= thread_cache=512 set-variable= thread_concurrency=12 _ I have started 7 concurrent mysql threads (7 OPTIMIZE TABLE from mysql command line) and after 9 hours I got a segmentation fault with Error ERROR 2013: Lost connection to MySQL server during query ERROR 2013: Lost connection to MySQL server during query ERROR 2013: Lost connection to MySQL server during query ERROR 2013: Lost connection to MySQL server during query ERROR 2013: Lost connection to MySQL server during query ERROR 2013: Lost connection to MySQL server during query ERROR 2013: Lost connection to MySQL server during query Segmentation Fault 030223 16:11:05 mysqld restarted I have looked at the mysqld error log file and this is the message 030223 07:03:11 mysqld started /usr/local/mysql-debug-4.0.10-gamma-sun-solaris2.9-sparc-64bit/bin/mysqld: ready for connections. Version: '4.0.10-gamma-debug-debug-log' socket: '/tmp/mysql.sock' port: 3306 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=17179869184 read_buffer_size=8192 sort_buffer_size=32768 max_used_connections=7 max_connections=16 threads_connected=8 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 16777856 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Writing a core file 030223 16:11:05 mysqld restarted __ I have looked for the core file, but I couldn't find it, even though in the error log it says Writing a core file. Does this give you any clue about what is causing the error ? Could you help please about the core file Do I need to specify the option differently ? Thanks in advance for your help, Mariella At 04:34 PM 1/29/03 +0200, Sinisa Milivojevic wrote: On Tue, 28 Jan 2003 17:12:14 -0700 Mariella Di Giacomo [EMAIL PROTECTED] wrote: Hello, I am running mysql server (mysql-standard-4.0.5-beta-64bit) 64 bit on Sun Solaris 2.8 and I have got the binaries form the mysql web site. The machine I am using has 32GB of RAM + 64GB of swap. Thanks in advance for your help, Mariella Hi! The reason why MySQL is crashing is not due to the fact that your memory is exhausted. It is possible that you have shell limits that are far below the available memory, but in your case even that is not a problem. What you should do is get a 4.0.10 debug binary for Solaris 2.8 from our site and send us a core file that is created on the crash. Also add --core-file to startup options. -- Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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: mysqld got signal 11; This could be because you hit a bug ....
Mariella Di Giacomo writes: Hello, I have followed your suggestion. I have installed mysql-debug-4.0.10-gamma-sun-solaris2.9-sparc-64bit on the Solaris 8 (Machine with 8 cpus, 32GB of Memory and 64 of swap) server. I have started the mysqld program as root I have specified the core-file in the /etc/my.cnf file. Other info that could be interesting in the /etc/my.cnf are HI! Causes of the crashes could be many, among the others: * using 2.9 binary on Solaris 2.8 !! Please use 2.8 binary, we have it * setting sort and record buffers to 1K !! Please set them at 1M There are many other things that could be improved, but that can be done when and if you become a registered customer. I have looked for the core file, but I couldn't find it, even though in the error log it says Writing a core file. Make sure all ulimit values are OK, including the one for core file. Does this give you any clue about what is causing the error ? Could you help please about the core file Do I need to specify the option differently ? Thanks in advance for your help, Mariella Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com Join MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld got signal 11; This could be because you hit a bug ....
Hi, I will install the 2.8 debug version. The reason why I set setting sort and record buffers to 1K is because with a few tests I have noticed that I was getting a better response time for queries. The limit for the core file was unlimited. I will look into customer support. Mariella At 06:17 PM 2/24/03 +0200, you wrote: Mariella Di Giacomo writes: Hello, I have followed your suggestion. I have installed mysql-debug-4.0.10-gamma-sun-solaris2.9-sparc-64bit on the Solaris 8 (Machine with 8 cpus, 32GB of Memory and 64 of swap) server. I have started the mysqld program as root I have specified the core-file in the /etc/my.cnf file. Other info that could be interesting in the /etc/my.cnf are HI! Causes of the crashes could be many, among the others: * using 2.9 binary on Solaris 2.8 !! Please use 2.8 binary, we have it * setting sort and record buffers to 1K !! Please set them at 1M There are many other things that could be improved, but that can be done when and if you become a registered customer. I have looked for the core file, but I couldn't find it, even though in the error log it says Writing a core file. Make sure all ulimit values are OK, including the one for core file. Does this give you any clue about what is causing the error ? Could you help please about the core file Do I need to specify the option differently ? Thanks in advance for your help, Mariella Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com Join MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld got signal 11; This could be because you hit a bug ....
Mariella Di Giacomo writes: Hi, I will install the 2.8 debug version. The reason why I set setting sort and record buffers to 1K is because with a few tests I have noticed that I was getting a better response time for queries. The limit for the core file was unlimited. I will look into customer support. Mariella Please also make sure that mysqld is run under mysql user and that all UNIX permissions are fine. BTW 4.0.11 is out with some fixes for OPTIMIZE table. Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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: Large table or several tables ?
4 million rows is a large table, but not a huge table. 400 is a large number of tables. Does the data in the 400 arrays describe the same type of entity or do they provide *generic* attribute info for different types of entities? If so, they would logically belong in a single table, otherwise not. I would start out by using the logical grouping, and worry about partitioning the table(s) later - if required for performance. HTH, Tore. - Original Message - From: gregory lefebvre [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 10:07 AM Subject: Large table or several tables ? Hye the list, There is my problem. I have to register about 400 large arrays (~1 lines each) in my MySQL database and I don't know which of the following choices is better. Either create a large tables as a stack, which contains all arrays Either create as many tables as there are arrays and use references to each table. Any advice is welcome. Thank you very much Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:Re: AUTO_INCREMENT and INDEX feature?
On 24 Feb 2003, at 17:44, Julian wrote: MEDIUMINT takes only 8 bytes, but BIGINT takes 20, so the whole DB grows, Where did you get those numbers? According to the documentation (http://www.mysql.com/doc/en/Storage_requirements.html), MEDIUMINT is 3 bytes and BIGINT is 8. And why are you going straight from MEDIUMINT to BIGINT? It seems that INT would work for you, since if you're inserting 1 records per day it will take you more than 1000 years to reach the upper limit for an unsigned INT. You seem to be saying that your table will only have about 1 rows, so I think you're worrying too much about the size of your index. Even if you use BIGINT, the keys would be contributing only 80K to the index size and 80K to the data size. But if you like you can make the index size smaller by using PACK_KEYS=1 when creating the table (assuming it's MyISAM): http://www.mysql.com/doc/en/Key_space.html [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: AUTO_INCREMENT and INDEX feature?
* Julian MEDIUMINT takes only 8 bytes, but BIGINT takes 20, so the whole DB No, MEDIUMINT occupies 3 bytes, BIGINT 8 bytes. URL: http://www.mysql.com/doc/en/Storage_requirements.html grows, moreover in Mysql documentation I read about indexes and column optimization and thay sad: medium int is always better than int - in context of that article. It is normally better to use the smallest possible datatype, simply to preserve space (disk ram). I would like to do as small as possible my db, so select-s to be much faster using index/key. However I would like to support a large number of possible rows(to be inserted in respective tables). Then you are in conflict with yourself... ;) You have to choose: a small datatype to preserve space _or_ a wider datatype to allow for bigger values. [...] will be inc. with 1 and one pretty day auto_increment will become over than 65535 - limit, but in the table I will have only 1-15000 rows)! If I chose another coulumn type for example with 4G limit the size of INDEX will become bigger and slower. Yes, but this may not be significant in your case. With this small amount of data (10-15K rows), a SMALLINT key would occupy 20-30K bytes (2 bytes/row), a MEDIUMINT would occupy 30-45K bytes, and a BIGINT would occupy 80-120K bytes. This is not much, the difference between a SMALLINT and a BIGINT key would probably not even be noticeable. A unsigned MEDIUMINT would give you 16777216 possible key values. If you use 10.000 each day, you would hit the ceiling after about 4.5 years. If that is not acceptable, a normal INT (4 bytes) may be a reasonable tradeoff: you would need 1176 years to exhaust all keys, if you use 10.000 each day. A BIGINT would of course be overkill, you would have to write 10.000 keys per _second_ in 60 million years to use all keys... :) -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld got signal 11; This could be because you hit a bug ....
Hi, I would prefer the debug version which the latest I believe is the 4.0.10. The same error I get it when I run ALTER TABLES or I try to insert data into tables. I will try and let you know. Thanks, Mariella At 06:31 PM 2/24/03 +0200, you wrote: Mariella Di Giacomo writes: Hi, I will install the 2.8 debug version. The reason why I set setting sort and record buffers to 1K is because with a few tests I have noticed that I was getting a better response time for queries. The limit for the core file was unlimited. I will look into customer support. Mariella Please also make sure that mysqld is run under mysql user and that all UNIX permissions are fine. BTW 4.0.11 is out with some fixes for OPTIMIZE table. Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
Unknown table in field list
Hi again. I've tried searching the list archives for this all morning, but they don't seem to be working (never get any results, page times out). I found one report of this on google but the guy said he figured out the problem but didn't say what he did to fix it! I just added a table to my database (about four hours ago, actually). This table is called 'building'. My overall database looks like this: mysql show tables; ++ | Tables_in_tmp_db_work | ++ | building | | parcels| ++ 2 rows in set (0.00 sec) I'm trying to run the following query: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels where parcels.relname like '%jones%' order by parcels.relname desc; That query returns: ERROR 1109: Unknown table 'building' in field list If I try this query instead: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels building INNER JOIN building as building on parcels.DXF = building.DXF where parcels.relname LIKE '%jones%' order by parcels.relname desc; The query returns: ERROR 1066: Not unique table/alias: 'building' So what's wrong?? Thanks. __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.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: mysqld got signal 11; This could be because you hit a bug ....
Mariella Di Giacomo writes: Hi, I would prefer the debug version which the latest I believe is the 4.0.10. The same error I get it when I run ALTER TABLES or I try to insert data into tables. I will try and let you know. Thanks, Mariella 4.0.11 will be quite soon, but your problems indicate that it is some system setup error, which would be very fast diagnosed if we would login to your system. Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
INSERT INTO table1 VALUES (DEFAULT): Auto_increment colums do not count up
==
INSERT INTO table1 VALUES (DEFAULT): Auto_increment colums do not count up
==
I am trying to insert values via PHP into a table with an autoincrement
field and dynamic column-count. To avoid writing down the column-names of
the target table (insert into table (column1, column2, ...) explicitly, I
was trying to fill up the non-used colums with the value DEFAULT, as it is
suggested in the mySQL documentation.
When inserting using DEFAULT values in SQL, auto_increment fields do not
seem to count up.
As a result, an error Duplicate entry '182929' for key 1 appears
reproduceably at the second insert statement.
Here are two insert statements while the first works (with empty table) and
the second does not:
delete from import_bericht;
insert into import_bericht values
('02','0066','016','000','000','01610014434',NULL,NULL,NULL,NULL,NULL,'01','
95','00016','016','','',NULL,'000
00','',NULL,NULL,'2003-02-21','0536','0',DEFAULT,DEFAULT,DEF
AULT,DEFAULT);
insert into import_bericht values
('02','0066','016','000','000','01681107025',NULL,NULL,NULL,NULL,NULL,'01','
49','72622','172','','',NULL,'000
00','',NULL,NULL,'2003-02-21','0544','0',DEFAULT,DEFAULT,DEF
AULT,DEFAULT);
I testet autocommit=1 as well as running the statements within a
transaction.
The auto_increment field even remains the same when executing the first
statement twice and a delete in between.
Seems as if I have to workaround that writing down the target colums in SQL.
But would be fine if it worked as described above as it made it easier to
fill tables with dynamic number of colums (just had to read out the number
of colums and fill the rest of the colums with DEFAULT).
Thanks in advance
TM
=
MySQL 4.0.9 gamma
RedHat Linux 7.3
Dell PowerEdge 2600
2GB RAM
MySQL RPM-Binaries (RedHat) installed
=
The Table I used:
-- MySQL dump 9.07
--
-- Host: localhostDatabase: basis
-
-- Server version 4.0.9-gamma-Max
--
-- Table structure for table 'import_bericht'
--
DROP TABLE IF EXISTS import_bericht;
CREATE TABLE import_bericht (
scannart tinyint(3) unsigned NOT NULL default '0',
terminal smallint(3) unsigned NOT NULL default '0',
tour smallint(5) unsigned NOT NULL default '0',
gebiet smallint(3) unsigned default NULL,
stopp_nr smallint(3) unsigned default NULL,
paketnummer bigint(20) unsigned NOT NULL default '0',
fc1 tinyint(3) unsigned default NULL,
fc2 tinyint(3) unsigned default NULL,
fc3 tinyint(3) unsigned default NULL,
fc4 tinyint(3) unsigned default NULL,
fc5 tinyint(3) unsigned default NULL,
paketart tinyint(1) unsigned default NULL,
land tinyint(2) unsigned default NULL,
plz mediumint(5) unsigned default NULL,
route smallint(3) unsigned default NULL,
gk_nr varchar(4) default NULL,
gk_filiale varchar(4) default NULL,
unbenutzt varchar(20) default NULL,
stopp varchar(40) default NULL,
kaufhausnummer int(8) unsigned default NULL,
auftragsnummer varchar(13) default NULL,
importnummer varchar(7) default NULL,
datum date NOT NULL default '-00-00',
zeit varchar(4) default NULL,
gewicht mediumint(8) unsigned default NULL,
ber_id int(10) unsigned NOT NULL auto_increment,
send_id int(10) unsigned NOT NULL default '0',
kunden_nr mediumint(8) unsigned default NULL,
importdatum date default NULL,
PRIMARY KEY (ber_id),
KEY idx_pkt (paketnummer),
KEY idx_datum (datum),
KEY idx_kd (kunden_nr),
KEY idx_importdatum (importdatum),
KEY idx_send_id (send_id)
) TYPE=InnoDB;
-
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: Unknown table in field list
Change the query to: SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels INNER JOIN building on parcels.DXF = building.DXF where parcels.relname LIKE '%jones%' order by parcels.relname desc; For tables in the from list, the next word after a table name is considered to be the alias, unless it is a keyword. So, when you had: from parcels building INNER JOIN building as building since building was immediately after parcels, it thought you wanted to use building as an alias for parcels. You then go on to list building, so according to your query you wanted parcels aliased as building, and building also to be called building, so there was a conflict. -Original Message- From: Diver8 [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 10:47 AM To: [EMAIL PROTECTED] Subject: Unknown table in field list Hi again. I've tried searching the list archives for this all morning, but they don't seem to be working (never get any results, page times out). I found one report of this on google but the guy said he figured out the problem but didn't say what he did to fix it! I just added a table to my database (about four hours ago, actually). This table is called 'building'. My overall database looks like this: mysql show tables; ++ | Tables_in_tmp_db_work | ++ | building | | parcels| ++ 2 rows in set (0.00 sec) I'm trying to run the following query: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels where parcels.relname like '%jones%' order by parcels.relname desc; That query returns: ERROR 1109: Unknown table 'building' in field list If I try this query instead: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels building INNER JOIN building as building on parcels.DXF = building.DXF where parcels.relname LIKE '%jones%' order by parcels.relname desc; The query returns: ERROR 1066: Not unique table/alias: 'building' So what's wrong?? Thanks. __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.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
MYSQL 3.23.55 Install Issues
Hi, I've been trying to get the 3.23.55 version of mysqld-nt.exe working with our product (GMS Mail) and I'm running into install\service related issues. I've read through the various threads on this list and nothing seems to solve my problem. The situation is that we include MySQL with our product and manage the starting\stopping of the service ourselves (don't ask why). We currenlly use 3.23.43 and are looking to upgrade to 3.23.55. We don't ship the full MySQL install dues to size but just ship those parts we need. Using 3.23.43 this is fine and we are able to run mysqld-nt as a service under our own service name with no problems. With 3.23.55 this does not work. It seems that the executable requires itself to be installed as the MySQL service. If I do this then all is fine and good. However we don't need to install the service as we have it installed ourselves under a different name and we don't wish to do this as it may interfer with a customer who potentially has a different version of MySQL installed on the same machine. The startup options we use are: mysqld-nt -default-file=file -basedir=dir -datadir=dir --port=non-std port -bind-address=127.0.0.1 -socket= The cnf file is generally empty. With these options the service starts but querying it's status fails and the port\ip that it is listening on are incorrect - the defaults are used. Any ideas? Simon - 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 Mysql 3.23.54
On Mon, Feb 24, 2003 at 10:19:01AM +0100, G. Hesen wrote: When using the client library with php: Have you also changed versions of PHP? Earler(earler mysql version) mysqllib: SELECT userfield.*,user.* FROM . [1] =123 [userid] = 123 Now with MySQL 3.23.54 mysqllib: SELECT userfield.*,user.* FROM . [1] =123 [userid] = Here you can see that mysql doesn't give the userid anymore if the userid's are the key to join 2 tables. I corrected this with: SELECT userfield.*,user.*,user.userid as userid FROM . [1] =123 [userid] = 123 Is this is a bug? Or a behaviour change? What does the query return if you run it from the MySQL command line client? Cheers! -- Zak Greant [EMAIL PROTECTED] MySQL AB Community Advocate Personal Blog: http://zak.fooassociates.com Using and Managing MySQL MySQL Training: Toronto, March 24-28, 2003 Visit http://mysql.com/training for more information - 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
Directory structure
Hi, I am trying to figure out the best method for creating a directory with mySQL (Lasso). Let me first say that I am new-ish to SQL, so might be jumping into the deep-end. I am building this for a site and am looking for a fast method of displaying where users are in the directory, or where an entry exists within it's structure. At the moment I have this table (just for the areas of the directory) and another table for all the entries that live in the directory: ID BIGINT Category VARCHAR(35) Parent_ID BIGINT Branch VARCHAR(255) ++---+---+--+ | ID | Category | Parent_ID | Branch | ++---+---+--+ | 1 | Home | NULL | Home | | 6 | Software | 1 | Home/Software| | 13 | Internet | 6 | Home/Software/Internet | | 34 | Servers |13 | Home/Software/Internet/Servers | ++---+---+--+ I am not sure whether I have got any of this right, but I have developed it this way in order to minimize the amount of searches that need to be performed in order to show the Branch of multiple entries within my search results. i.e. as far as I am aware if I don't store the Branch alongside then I have to loop through IDs and ParentIDs with multiple searches to build the correct Branch for each entry that I am displaying in my search results. This amounts to a lot of searches. My questions are: Should I be using separate tables for each category deep? If, so how could I make sure it could grow deeper? Can I generate the Branch dynamically within my table(s)? Can anyone suggest a proven method for creating a flexible directory structure that can grow and have categories that can have multiple parents? Any suggestions would be greatly appreciated, Thanks in advance, Adam -- // Adam de Zoete \\ [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
redirect mysql output to file
Hi, How do i redirect a mysql output to a file from the command line? For example, I want to save DESCRIBE test_table test_table.file without doing a MYSQLDUMP. thanks, MT - 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
optimizing large InnoDB table
I have a couple of questions here. This table currently contains about 12 million records. This table is mostly read from, but several times thoughout the day there will be inserts or updates of batches of about 30,000 rows, and there are periodic purges of old rows, which could be up to several million rows. The table has 1 index over three of the columns. My questions are: 1. There is one varchar(15) column, that is part of the index. Would there be any performance benefit to making it a char column? 2. Does InnoDB do updates/deletes row by row, even if it is just one query? 3. Will it just update the index once per query, or for each row? 3. Any other advice for speeding up the updates/deletes? - 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
# of Business Days?
Can anyone out there help me with a SQL query? I need to find the number of business days between date a and b? Obviously finding simply the number of days is easy, but I have no clue how to find the number of business days. TIA! Lucas Cowgar Information Technologies Department Eldorado Services Group Inc. http://www.eldoserv.com [EMAIL PROTECTED] (330) 861-3009 All your base are belong to us - 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: redirect mysql output to file
At 10:50 AM 2/24/2003 -0700, Mike Doanh Tran wrote:
How do i redirect a mysql output to a file from the command line?
For example, I want to save DESCRIBE test_table test_table.file
without doing a MYSQLDUMP.
From the command line:
$ echo 'describe test_table' | mysql {dbname} test_table.file
you may have to provide options to mysql, e.g. -h {host} -u {user} -p
From within the mysql client, I don't know.
Regards,
- Robert
-
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: redirect mysql output to file
In *NIX you could do this: $ echo sql commands here | mysql -uroot -ppassword -hhost database /path/to/file For extra scripting fun you could backup tables using this: for i in `echo show databases | /usr/local/mysql/bin/mysql -uroot | grep -v Database` do mysqldump -uroot $i /path/to/backups/$i.sql echo $i done --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. -Original Message- From: Mike Doanh Tran [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 9:51 AM To: [EMAIL PROTECTED] Subject: redirect mysql output to file Hi, How do i redirect a mysql output to a file from the command line? For example, I want to save DESCRIBE test_table test_table.file without doing a MYSQLDUMP. thanks, MT - 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: # of Business Days?
I'm not sure of an exact query, but if you can get the total number of days
you can mathmatically get the biz days with the following math (in psuedo
code):
$days = total_days_between_a_and_b;
$weeks = ($days / 7);
$weekend_days = $weeks * 2;
$business_days = ($days - $weekend_days);
Hope this helps. There may be more elegant ways of getting such a value.
This doesn't take into account day A being wednesday. You might be able to
do a SELECT COUNT(*) on evaluating your date to it's Day value in
('Mon','Tue','Wed','Thu','Fri')
Check the date commands.
--Joe
--
Joe Stump [EMAIL PROTECTED]
http://www.joestump.net
Label makers are proof God wants Sys Admins to be happy.
-Original Message-
From: Lucas Cowgar [mailto:[EMAIL PROTECTED]
Sent: Monday, February 24, 2003 9:59 AM
To: MySQL Mailing List
Subject: # of Business Days?
Can anyone out there help me with a SQL query? I need to find the number of
business days between date a and b? Obviously finding simply the number of
days is easy, but I have no clue how to find the number of business days.
TIA!
Lucas Cowgar
Information Technologies Department
Eldorado Services Group Inc.
http://www.eldoserv.com
[EMAIL PROTECTED]
(330) 861-3009
All your base are belong to us
-
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 starting replication across platforms
I am trying to start replication from a 4.0.9 solaris master to a 4.0.8 linux slave. I am already sucessfully replicating this master to another sun slave. I followed the same procedures on the linux box but no joy. The error log keeps saying : 030221 10:08:56 mysqld started 030221 10:08:56 InnoDB: Started /usr/mysql/libexec/mysqld: ready for connections 030221 10:08:56 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'udd-bin.008' at position 154 030221 10:08:56 Error updating slave list: Query error 030221 10:08:56 Slave I/O thread exiting, read up to log 'udd-bin.008', position 154 Show slave status yields that the Slave_SQL_Running value is 'yes', but the Slave_IO_Running values is 'No'. Does anyone have any ideas to help troubleshoot this ? Thanks - 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: Unknown table in field list
Just what it says. Your first query attempts to list a column from a table that is not selected from (building). Your second query attempts to assign the alias building to both the parcels table and the building table, and then subsequently does a join on the building table. Use the following (iuse table aliases for increased readability): SELECT P.DXF as 'record', B.address as 'results1', P.relname as 'results2' FROM parcels as P INNER JOIN building as B ON P.DXF = B.DXF WHERE P.relname LIKE '%jones%' ORDER BY P.relname desc; HTH, Tore. - Original Message - From: Diver8 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 11:46 AM Subject: Unknown table in field list Hi again. I've tried searching the list archives for this all morning, but they don't seem to be working (never get any results, page times out). I found one report of this on google but the guy said he figured out the problem but didn't say what he did to fix it! I just added a table to my database (about four hours ago, actually). This table is called 'building'. My overall database looks like this: mysql show tables; ++ | Tables_in_tmp_db_work | ++ | building | | parcels| ++ 2 rows in set (0.00 sec) I'm trying to run the following query: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels where parcels.relname like '%jones%' order by parcels.relname desc; That query returns: ERROR 1109: Unknown table 'building' in field list If I try this query instead: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels building INNER JOIN building as building on parcels.DXF = building.DXF where parcels.relname LIKE '%jones%' order by parcels.relname desc; The query returns: ERROR 1066: Not unique table/alias: 'building' So what's wrong?? Thanks. __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.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: redirect mysql output to file
On Mon, Feb 24, 2003 at 10:50:53AM -0700, Mike Doanh Tran wrote: Hi, How do i redirect a mysql output to a file from the command line? For example, I want to save DESCRIBE test_table test_table.file without doing a MYSQLDUMP. Use the mysql command line client from your command line: % mysql --execute \u mysql; DESCRIBE table; table.file -- Zak Greant [EMAIL PROTECTED] MySQL AB Community Advocate Personal Blog: http://zak.fooassociates.com MySQL Tip: Upgrading your servers to 4.0.8? Make sure to upgrade your clients to 4.0.8 as well! See http://mysql.com/doc/en/News-4.0.8.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BUG: Format function fails when query ordered
Hello, I am reporting a bug. The format number function fails when a query is sorted. I have included an example page to look at (the error is reproduced here): http://www.meetscoresonline.com/test_format.asp You'll see there are 2 result sets, the first is not ordered and the 2nd is ordered. In the first example, the format function indeed works, but not in the 2nd. Is this indeed a bug, or something I am doing wrong? MySQL Version: 4.0.0-alpha Karl - 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: Directory structure
Depending on how you plan to use the data, you may be interested in the Nested Set Model. There are articles listed at searchdatabase.techtarget.com if you search for that string, but you may need to register there (can't supply links as they contain user ID...). Or if you have Joe Celko's SQL for Smarties, a whole chapter is dedicated to this model. If you are presenting one level at a time, the adjacency model (which is the one you are using) may be a good choice. If you want to perform cross-section operations or operate on entire subtrees, the nested set model would be better suited. HTH, Tore. - Original Message - From: Adam de Zoete [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 12:46 PM Subject: Directory structure Hi, I am trying to figure out the best method for creating a directory with mySQL (Lasso). Let me first say that I am new-ish to SQL, so might be jumping into the deep-end. I am building this for a site and am looking for a fast method of displaying where users are in the directory, or where an entry exists within it's structure. At the moment I have this table (just for the areas of the directory) and another table for all the entries that live in the directory: ID BIGINT Category VARCHAR(35) Parent_ID BIGINT Branch VARCHAR(255) ++---+---+--+ | ID | Category | Parent_ID | Branch | ++---+---+--+ | 1 | Home | NULL | Home | | 6 | Software | 1 | Home/Software| | 13 | Internet | 6 | Home/Software/Internet | | 34 | Servers |13 | Home/Software/Internet/Servers | ++---+---+--+ I am not sure whether I have got any of this right, but I have developed it this way in order to minimize the amount of searches that need to be performed in order to show the Branch of multiple entries within my search results. i.e. as far as I am aware if I don't store the Branch alongside then I have to loop through IDs and ParentIDs with multiple searches to build the correct Branch for each entry that I am displaying in my search results. This amounts to a lot of searches. My questions are: Should I be using separate tables for each category deep? If, so how could I make sure it could grow deeper? Can I generate the Branch dynamically within my table(s)? Can anyone suggest a proven method for creating a flexible directory structure that can grow and have categories that can have multiple parents? Any suggestions would be greatly appreciated, Thanks in advance, Adam -- // Adam de Zoete \\ [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: # of Business Days?
Here's a link to a solution that Oracle came up with: http://dco-proxima.dco.pima.edu/oracle/tfts/PRE00136.HTM You should be able to adapt it to MySQL and PHP (or whatever language you are using). Basically, the script gets the start date and end date, then steps through every day counting only business week days. Of course, and another question to consider, is if you wish to count holidays or not. jeff At 12:58 -0500 2/24/03, Lucas Cowgar wrote: Can anyone out there help me with a SQL query? I need to find the number of business days between date a and b? Obviously finding simply the number of days is easy, but I have no clue how to find the number of business days. TIA! Lucas Cowgar Information Technologies Department Eldorado Services Group Inc. http://www.eldoserv.com [EMAIL PROTECTED] (330) 861-3009 All your base are belong to us -- _ ____ +--+ / | / /__ _/ /_ _|Jeff Shapiro | / |/ / _ \/ __ \/ ___/ __ \/ __ `/|Photography and Graphic Design| / /| / __/ / / (__ ) / / / /_/ / |Colorado Springs, CO, USA | /_/ |_/\___/_/ /_//_/ /_/\__,_/ |www.nensha.com ||| [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: redirect mysql output to file
Mike, How do i redirect a mysql output to a file from the command line? For example, I want to save DESCRIBE test_table test_table.file without doing a MYSQLDUMP. To have the mysql client write a protocol, start it, and then do: mysql tee myoutfile.txt Logging to file 'myoutfile.txt' mysql tee Currently logging to file 'myoutfile.txt' mysql notee Outfile disabled. Use a path for myoutfile.txt (or however you name it) if you don't want the file to be written in the directory you started mysql from. tee will append to your outfile, creating it if necessary. If it's there, tee will not overwrite its content. tee does not work in batch mode, and you cannot use it together with mysqlc.exe (for Win95/98). Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question for the GRANT statement
Hi, I am new to MySQL and SQL in genreal. I just installed mysql 3.23.55 on my Red Hat Linux 7.1. I tried to GRANT user in the following way: CREATE DATABASE izdb; GRANT ALL ON izdb.* TO iz1 IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO [EMAIL PROTECTED] IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO iz3@% IDENTIFIED BY 'iz'; FLUSH PRIVILEGES; It seems to me that I can only login using iz2. The output as follows: [EMAIL PROTECTED] mysql]$ bin/mysql -u iz1 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- [EMAIL PROTECTED] mysql]$ bin/mysql -u iz2 -p'iz' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 3.23.55-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED] mysql]$ bin/mysql -u iz3 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- Can anyone tell me what I did wrong with iz1 and iz3? I thought iz1 and iz2 should be the same and iz3 should be on any hosts. By the way, I print out all my SHOW GRANTS command output: Thank you. Ian Zhang mysql SHOW GRANTS FOR iz1; +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT USAGE ON *.* TO 'iz1'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz1'@'%' | +---+ 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR iz2; ERROR 1141: There is no such grant defined for umysql SHOW GRANTS FOR iz3; +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT USAGE ON *.* TO 'iz3'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz3'@'%' | +---+ 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR [EMAIL PROTECTED]; ERROR 1141: There is no such grant defined for user 'iz3' on host 'localhost' ser 'iz2' on host '%' mysql SHOW GRANTS FOR [EMAIL PROTECTED]; +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT USAGE ON *.* TO 'iz2'@'localhost' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz2'@'localhost' | +---+ 2 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question for the GRANT statement
Try this: GRANT ALL PRIVILEGES ON izdb.* TO iz2@'localhost' IDENTIFIED BY 'iz'; Lucas Cowgar Information Technologies Department Eldorado Services Group Inc. http://www.eldoserv.com [EMAIL PROTECTED] (330) 861-3009 All your base are belong to us - Original Message - From: ianhzhang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 1:37 PM Subject: Question for the GRANT statement Hi, I am new to MySQL and SQL in genreal. I just installed mysql 3.23.55 on my Red Hat Linux 7.1. I tried to GRANT user in the following way: CREATE DATABASE izdb; GRANT ALL ON izdb.* TO iz1 IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO [EMAIL PROTECTED] IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO iz3@% IDENTIFIED BY 'iz'; FLUSH PRIVILEGES; It seems to me that I can only login using iz2. The output as follows: [EMAIL PROTECTED] mysql]$ bin/mysql -u iz1 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- [EMAIL PROTECTED] mysql]$ bin/mysql -u iz2 -p'iz' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 3.23.55-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED] mysql]$ bin/mysql -u iz3 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- Can anyone tell me what I did wrong with iz1 and iz3? I thought iz1 and iz2 should be the same and iz3 should be on any hosts. By the way, I print out all my SHOW GRANTS command output: Thank you. Ian Zhang mysql SHOW GRANTS FOR iz1; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'iz1'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz1'@'%' | +--- + 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR iz2; ERROR 1141: There is no such grant defined for umysql SHOW GRANTS FOR iz3; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'iz3'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz3'@'%' | +--- + 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR [EMAIL PROTECTED]; ERROR 1141: There is no such grant defined for user 'iz3' on host 'localhost' ser 'iz2' on host '%' mysql SHOW GRANTS FOR [EMAIL PROTECTED]; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'iz2'@'localhost' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz2'@'localhost' | +--- + 2 rows in set (0.00 sec) - 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: Question for the GRANT statement
Thank you Lucas. But why I have to put localhost at the end? The manual says that I can put a wild card %, like iz3@%. Or if I did not put anything, it will automatically assume localhost. Thanks again. Ian Zhang [EMAIL PROTECTED] wrote: Try this: GRANT ALL PRIVILEGES ON izdb.* TO iz2@'localhost' IDENTIFIED BY 'iz'; Lucas Cowgar Information Technologies Department Eldorado Services Group Inc. http://www.eldoserv.com [EMAIL PROTECTED] (330) 861-3009 All your base are belong to us - Original Message - From: ianhzhang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 1:37 PM Subject: Question for the GRANT statement Hi, I am new to MySQL and SQL in genreal. I just installed mysql 3.23.55 on my Red Hat Linux 7.1. I tried to GRANT user in the following way: CREATE DATABASE izdb; GRANT ALL ON izdb.* TO iz1 IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO [EMAIL PROTECTED] IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO iz3@% IDENTIFIED BY 'iz'; FLUSH PRIVILEGES; It seems to me that I can only login using iz2. The output as follows: [EMAIL PROTECTED] mysql]$ bin/mysql -u iz1 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- [EMAIL PROTECTED] mysql]$ bin/mysql -u iz2 -p'iz' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 3.23.55-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED] mysql]$ bin/mysql -u iz3 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- Can anyone tell me what I did wrong with iz1 and iz3? I thought iz1 and iz2 should be the same and iz3 should be on any hosts. By the way, I print out all my SHOW GRANTS command output: Thank you. Ian Zhang mysql SHOW GRANTS FOR iz1; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'iz1'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz1'@'%' | +--- + 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR iz2; ERROR 1141: There is no such grant defined for umysql SHOW GRANTS FOR iz3; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'iz3'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz3'@'%' | +--- + 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR [EMAIL PROTECTED]; ERROR 1141: There is no such grant defined for user 'iz3' on host 'localhost' ser 'iz2' on host '%' mysql SHOW GRANTS FOR [EMAIL PROTECTED]; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'iz2'@'localhost' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz2'@'localhost' | +--- + 2 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Visual Basic Embedded VC++ DLL
Hi! Has anyone created a DLL in VC++ the uses the embedded mysqlserver.lib library? Is it possible to do this? Is there any special settings that are needed? I have compiled a DLL that does this, but then I run a function wich encapsulates mysql_server_init(), I get a memory error and my VB program which references the DLL I created crashes. (When I comment out the section of code that references mysql_server_init, the code executes without errors and when I link this lib file to a C program the code also works without problems). Any suggestions? Thanks Eric Talk to you Later. -- Derick -- _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Question for the GRANT statement
The % wildcard works for granting permission over the network. When you are working on the localhost, you are using mysql.sock to connect to the server. Try this (I believe it should work) /bin/mysql -u iz3 ip'iz' -h'youripaddress' This should allow you to connect using the network and not mysql.sock If you do not want to connect this way then you must add a line for localhost to each user. Hope this helps. Roger -Original Message- From: ianhzhang [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 2:15 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Question for the GRANT statement Thank you Lucas. But why I have to put localhost at the end? The manual says that I can put a wild card %, like iz3@%. Or if I did not put anything, it will automatically assume localhost. Thanks again. Ian Zhang [EMAIL PROTECTED] wrote: Try this: GRANT ALL PRIVILEGES ON izdb.* TO iz2@'localhost' IDENTIFIED BY 'iz'; Lucas Cowgar Information Technologies Department Eldorado Services Group Inc. http://www.eldoserv.com [EMAIL PROTECTED] (330) 861-3009 All your base are belong to us - Original Message - From: ianhzhang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 1:37 PM Subject: Question for the GRANT statement Hi, I am new to MySQL and SQL in genreal. I just installed mysql 3.23.55 on my Red Hat Linux 7.1. I tried to GRANT user in the following way: CREATE DATABASE izdb; GRANT ALL ON izdb.* TO iz1 IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO [EMAIL PROTECTED] IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO iz3@% IDENTIFIED BY 'iz'; FLUSH PRIVILEGES; It seems to me that I can only login using iz2. The output as follows: [EMAIL PROTECTED] mysql]$ bin/mysql -u iz1 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- [EMAIL PROTECTED] mysql]$ bin/mysql -u iz2 -p'iz' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 3.23.55-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED] mysql]$ bin/mysql -u iz3 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- Can anyone tell me what I did wrong with iz1 and iz3? I thought iz1 and iz2 should be the same and iz3 should be on any hosts. By the way, I print out all my SHOW GRANTS command output: Thank you. Ian Zhang mysql SHOW GRANTS FOR iz1; +-- - + | Grants for [EMAIL PROTECTED] | +-- - + | GRANT USAGE ON *.* TO 'iz1'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz1'@'%' | +-- - + 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR iz2; ERROR 1141: There is no such grant defined for umysql SHOW GRANTS FOR iz3; +-- - + | Grants for [EMAIL PROTECTED] | +-- - + | GRANT USAGE ON *.* TO 'iz3'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz3'@'%' | +-- - + 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR [EMAIL PROTECTED]; ERROR 1141: There is no such grant defined for user 'iz3' on host 'localhost' ser 'iz2' on host '%' mysql SHOW GRANTS FOR [EMAIL PROTECTED]; +-- - + | Grants for [EMAIL PROTECTED] | +-- - + | GRANT USAGE ON *.* TO 'iz2'@'localhost' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz2'@'localhost' | +-- - + 2 rows in set (0.00 sec) - 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
Re: Question for the GRANT statement
Ian, Yes, you can put a wildcard but I would advise against it if you are going to be inside any kind of secured environment. If soneone were trying to hack your database, the could do it remotely because MySQL wouldn't care where the connection was coming from, for example, someone could log into your system using the account [EMAIL PROTECTED] If you were to only allow connections from your localhost, they would first have to gain access to a shell on your box, then usernames and passwords for MySQL. Lucas Cowgar Information Technologies Department Eldorado Services Group Inc. http://www.eldoserv.com [EMAIL PROTECTED] (330) 861-3009 All your base are belong to us - Original Message - From: ianhzhang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 2:14 PM Subject: Re: Question for the GRANT statement Thank you Lucas. But why I have to put localhost at the end? The manual says that I can put a wild card %, like iz3@%. Or if I did not put anything, it will automatically assume localhost. Thanks again. Ian Zhang [EMAIL PROTECTED] wrote: Try this: GRANT ALL PRIVILEGES ON izdb.* TO iz2@'localhost' IDENTIFIED BY 'iz'; Lucas Cowgar Information Technologies Department Eldorado Services Group Inc. http://www.eldoserv.com [EMAIL PROTECTED] (330) 861-3009 All your base are belong to us - Original Message - From: ianhzhang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 1:37 PM Subject: Question for the GRANT statement Hi, I am new to MySQL and SQL in genreal. I just installed mysql 3.23.55 on my Red Hat Linux 7.1. I tried to GRANT user in the following way: CREATE DATABASE izdb; GRANT ALL ON izdb.* TO iz1 IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO [EMAIL PROTECTED] IDENTIFIED BY 'iz'; GRANT ALL ON izdb.* TO iz3@% IDENTIFIED BY 'iz'; FLUSH PRIVILEGES; It seems to me that I can only login using iz2. The output as follows: [EMAIL PROTECTED] mysql]$ bin/mysql -u iz1 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- [EMAIL PROTECTED] mysql]$ bin/mysql -u iz2 -p'iz' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 3.23.55-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED] mysql]$ bin/mysql -u iz3 -p'iz' ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- Can anyone tell me what I did wrong with iz1 and iz3? I thought iz1 and iz2 should be the same and iz3 should be on any hosts. By the way, I print out all my SHOW GRANTS command output: Thank you. Ian Zhang mysql SHOW GRANTS FOR iz1; +-- - + | Grants for [EMAIL PROTECTED] | +-- - + | GRANT USAGE ON *.* TO 'iz1'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz1'@'%' | +-- - + 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR iz2; ERROR 1141: There is no such grant defined for umysql SHOW GRANTS FOR iz3; +-- - + | Grants for [EMAIL PROTECTED] | +-- - + | GRANT USAGE ON *.* TO 'iz3'@'%' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz3'@'%' | +-- - + 2 rows in set (0.00 sec) mysql SHOW GRANTS FOR [EMAIL PROTECTED]; ERROR 1141: There is no such grant defined for user 'iz3' on host 'localhost' ser 'iz2' on host '%' mysql SHOW GRANTS FOR [EMAIL PROTECTED]; +-- - + | Grants for [EMAIL PROTECTED] | +-- - + | GRANT USAGE ON *.* TO 'iz2'@'localhost' IDENTIFIED BY PASSWORD '077deb434925b35d' | | GRANT ALL PRIVILEGES ON `izdb`.* TO 'iz2'@'localhost' | +-- - + 2 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To
RE: # of Business Days?
Can anyone out there help me with a SQL query? I need to find the number
of business days between date a and b? Obviously finding simply the number
of days is easy, but I have no clue how to find the number of business days.
TIA!
Here's some ropey perl I wrote a ages back to calculate working minutes
between two unix timestamps (based on UK bank holidays and working hours of
9 to 6). I'm sure you could adapt to whatever you need
Please - no-one correct me on my perl (I already know), Don't fix what
ain't broke :)
Even so - I would reccoment some changes on a system that uses the below
heavily...
Cheers,
Andrew
### Used to calculate working minutes between two unix timestamps
#
# $sdate = 1040807553;
# $edate = 1040809553;
#
# $result = calc_workmins($sdate, $edate);
##
sub calc_workmins {
local ($sdate,$edate) = @_;
$mins_total = 1;
$rows = 0;
while ($sdate le $edate) {
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime($sdate);
$year += 1900;
$mon += 1; ## perl returns months in the range 0 to 11
my ($todaysdate) = $mday . - . $mon . - . $year;
my $bankflag = 0;
for my $bankhol
(1-1-2002,29-3-2002,1-4-2002,3-6-2002,4-6-2002,26-8-2002,25-12-
2002,26-12-2002,1-1-2003,18-4-2003,21-4-2003,5-5-2003,26-5-2003
,25-8-2003,25-12-2003,26-12-2003,27-12-2003,28-12-200,31-1-2004,
9-4-2004,12-4-2004,3-5-2004,31-5-2004,30-8-2004,25-12-2004,26-1
2-2004,27-12-2004,3-1-2005,25-3-2005,28-3-2005,2-5-2005,30-5-200
5,29-8-2005,25-12-2005,26-12-2005) {
if ($todaysdate == $bankhol) {
$bankflag = 1;
}
$bankholshow = $bankhol;
}
if (($hour 8) ($hour 19) ($wday 0) ($wday
6) ($bankflag 1)) {
$mins_total++;
}
$sdate = $sdate + 60;
$rows++;
}
return $mins_total;
}
-
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 3.23.55 Install Issues
At 17:43 24/02/2003 +, Simon Tyler wrote: Hi, Hi, I've been trying to get the 3.23.55 version of mysqld-nt.exe working with our product (GMS Mail) and I'm running into install\service related issues. I've read through the various threads on this list and nothing seems to solve my problem. The situation is that we include MySQL with our product and manage the starting\stopping of the service ourselves (don't ask why). We currenlly use 3.23.43 and are looking to upgrade to 3.23.55. We don't ship the full MySQL install dues to size but just ship those parts we need. Using 3.23.43 this is fine and we are able to run mysqld-nt as a service under our own service name with no problems. With 3.23.55 this does not work. It seems that the executable requires itself to be installed as the MySQL service. If I do this then all is fine and good. However we don't need to install the service as we have it installed ourselves under a different name and we don't wish to do this as it may interfer with a customer who potentially has a different version of MySQL installed on the same machine. The startup options we use are: mysqld-nt -default-file=file -basedir=dir -datadir=dir --port=non-std port -bind-address=127.0.0.1 -socket= The cnf file is generally empty. With these options the service starts but querying it's status fails and the port\ip that it is listening on are incorrect - the defaults are used. Any ideas? You can use either the version 3.23.XX or 4.0.X with a custom service name different than the default MySQL. There are 2 options: mysqld-nt --install myservice in the above case you need to use the same my.ini file used by the default installation, however you need to write the custom set under a section with the same name of the service instead of mysqld eg: [myservice] port=3307 ... if you are running several servers on the same machine you need to set different ports and not to mix the databases location which is the datadir set. The second case is to use a different service name with a custom configuration file: mysqld --install myservice --defaults-file=c:\anydir\my.ini In the above case you use the section default server: mysqld and apply the same rules if you are running several servers. -- 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 returning a row count with rows() function
Greetings - I have a simple CGI script written in Perl (using v5.8) that seemed to work well under MySQL 3.23.49. However, after recently upgrading to version 3.23.55 I am running into the following problem. Previously, I used the rows() function to return the number of rows returned from a SELECT query as in the example below. However, this has now ceased to function as before, and returns zero (0) in the $count variable no matter what is returned from the database. Can anyone tell what might have changed, or what I might do to get around this problem? #Set the database query my $query = SELECT * FROM table_name; #Run a query to return all records my $sth = $dbh-prepare($query); $sth-execute; #Count the number of records returned my $count=$sth-rows(); Many thanks for any help you can provide... Colin - 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: # of Business Days?
use Date::Manip
http://www.perldoc.com/perl5.6.1/lib/Date/Manip.html
-J
-Original Message-
From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]
Sent: Monday, February 24, 2003 1:33 PM
To: 'Lucas Cowgar'; MySQL Mailing List
Subject: RE: # of Business Days?
Can anyone out there help me with a SQL query? I need to find the number
of business days between date a and b? Obviously finding simply the number
of days is easy, but I have no clue how to find the number of business days.
TIA!
Here's some ropey perl I wrote a ages back to calculate working minutes
between two unix timestamps (based on UK bank holidays and working hours of
9 to 6). I'm sure you could adapt to whatever you need
Please - no-one correct me on my perl (I already know), Don't fix what
ain't broke :)
Even so - I would reccoment some changes on a system that uses the below
heavily...
Cheers,
Andrew
### Used to calculate working minutes between two unix timestamps
#
# $sdate = 1040807553;
# $edate = 1040809553;
#
# $result = calc_workmins($sdate, $edate);
##
sub calc_workmins {
local ($sdate,$edate) = @_;
$mins_total = 1;
$rows = 0;
while ($sdate le $edate) {
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime($sdate);
$year += 1900;
$mon += 1; ## perl returns months in the range 0 to 11
my ($todaysdate) = $mday . - . $mon . - . $year;
my $bankflag = 0;
for my $bankhol
(1-1-2002,29-3-2002,1-4-2002,3-6-2002,4-6-2002,26-8-2002,25-12-
2002,26-12-2002,1-1-2003,18-4-2003,21-4-2003,5-5-2003,26-5-2003
,25-8-2003,25-12-2003,26-12-2003,27-12-2003,28-12-200,31-1-2004,
9-4-2004,12-4-2004,3-5-2004,31-5-2004,30-8-2004,25-12-2004,26-1
2-2004,27-12-2004,3-1-2005,25-3-2005,28-3-2005,2-5-2005,30-5-200
5,29-8-2005,25-12-2005,26-12-2005) {
if ($todaysdate == $bankhol) {
$bankflag = 1;
}
$bankholshow = $bankhol;
}
if (($hour 8) ($hour 19) ($wday 0) ($wday
6) ($bankflag 1)) {
$mins_total++;
}
$sdate = $sdate + 60;
$rows++;
}
return $mins_total;
}
-
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/doc/ (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question mark becomes NULL
At 10:06 24-2-03 -0500, Keith C. Ivey wrote:
On 24 Feb 2003, at 13:42, Daneman wrote:
I use a perl script DBI to insert records into a mySQL database.
This database holds 1 text-column. Now when this text-field holds any
question marks, they appear in the record as`NULL`.
Post the Perl code you're using for the insert query. It sounds like
you're doing something wrong with placeholders.
I got an email from Matthew Smith who adviced me to do:
$sql=qq{ INSERT INTO MyTable (MyFields) VALUES (?, ?, etc) };
$sth=$dbh-prepare($sql);
$sth-execute( $val1 ,$val2, etc);
instead of what I did originally:
INSERT INTO MyTable (MyFields) VALUES ($val1, $val2, etc);
Question marks are no longer replaced with NULL values in the text column
this way, still, when I enter question marks into the charvar fields, the
record is not taken into he database. I'm not sure whether this is a mySQL
or a Perl problem at this stage.
Any ideas?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: # of Business Days?
Hello
This is not correct, see this at example:
From friday to mondey there are 4 days (or 3, depends on how you count ;-).
your calcs:
$day = 4
$weeks = 4 / 7 = 0
$weekend_day = 0 * 2 = 0
$business_days = 4 - 0 = 4
If you have 100 weeks, then it will be quite correct.
here is my solution:
$day = total_days_between_a_and_b;
$less = weekend_days_between_WeekDAY_a_and_WeekDAY_b (for example: from
friday to Mondey, then it will be 2)
$weeks = $day DIV 7;
$bussiness_days = $day - $weeks * 2 - $less;
my example again:
$day = 4;
$less = 2;
$weeks = 0;
$bussiness_days = 4 - 0 * 2 - 2 = 2
or use a function from a programming language, if available. You have to
deal also with some special days (12-24 / 12-25 and so on).
greetings from Switzerland
Patrick
Joe Stump wrote:
I'm not sure of an exact query, but if you can get the total number of days
you can mathmatically get the biz days with the following math (in psuedo
code):
$days = total_days_between_a_and_b;
$weeks = ($days / 7);
$weekend_days = $weeks * 2;
$business_days = ($days - $weekend_days);
Hope this helps. There may be more elegant ways of getting such a value.
This doesn't take into account day A being wednesday. You might be able to
do a SELECT COUNT(*) on evaluating your date to it's Day value in
('Mon','Tue','Wed','Thu','Fri')
Check the date commands.
--Joe
--
Joe Stump [EMAIL PROTECTED]
http://www.joestump.net
Label makers are proof God wants Sys Admins to be happy.
-Original Message-
From: Lucas Cowgar [mailto:[EMAIL PROTECTED]
Sent: Monday, February 24, 2003 9:59 AM
To: MySQL Mailing List
Subject: # of Business Days?
Can anyone out there help me with a SQL query? I need to find the number of
business days between date a and b? Obviously finding simply the number of
days is easy, but I have no clue how to find the number of business days.
TIA!
Lucas Cowgar
Information Technologies Department
Eldorado Services Group Inc.
http://www.eldoserv.com
[EMAIL PROTECTED]
(330) 861-3009
All your base are belong to us
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question mark becomes NULL
And you still have not shown us how you are entering the question marks.
Daneman wrote:
At 10:06 24-2-03 -0500, Keith C. Ivey wrote:
On 24 Feb 2003, at 13:42, Daneman wrote:
I use a perl script DBI to insert records into a mySQL database.
This database holds 1 text-column. Now when this text-field holds any
question marks, they appear in the record as`NULL`.
Post the Perl code you're using for the insert query. It sounds like
you're doing something wrong with placeholders.
I got an email from Matthew Smith who adviced me to do:
$sql=qq{ INSERT INTO MyTable (MyFields) VALUES (?, ?, etc) };
$sth=$dbh-prepare($sql);
$sth-execute( $val1 ,$val2, etc);
instead of what I did originally:
INSERT INTO MyTable (MyFields) VALUES ($val1, $val2, etc);
Question marks are no longer replaced with NULL values in the text
column this way, still, when I enter question marks into the charvar
fields, the record is not taken into he database. I'm not sure whether
this is a mySQL or a Perl problem at this stage.
Any ideas?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
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
Cardinality for FULLTEXT-indexes MySQL 4.0.10
Hello! I never got any comment on this one... Have noone else observed this behavior? I'm a bit worried to use this in my production site without knowing if it's and indication of some problem with FULLTEXT indexes... :) Regards, Tobias Lind Hi! I have a question regarding Cardinality for FULLTEXT-indexes. I have a table with 9 indexes - the last 3 indexes are FULLTEXT. When I run show index from table, the last index (FULLTEXT) always shows cardinality=NULL... Is this normal? ...everything seems to work ok when I do selects using the index... I ran analyze table and got the response Table is already up to date I shut down mysqld, ran myisamchk -a and it went througt the first 8 indexes quickly, but on the last one it did a check record links, going through all rows in the table. After I started mysqld up again and ran show index from table, I still got cardinality=NULL for the last index. Ran myisamchk -a again, and it behaved just like the first time - passed the first 8 indexes quickly, and went through all rows on the last index. I'm running MySQL 4.0.10, rpm-binary version. Red Hat Linux 8.0 Dual P3400 MHz, 768 Mb RAM. On other tables (with less number on indexes), I have always got a cardinality after running analyze table also on fulltext-indexes. Could this be a bug? Something to worry about? Regards, Tobias Lind - 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 NULL in auto_increment column
Can you update an auto_increment with NULL in MySQL? Obviously if you try and put a NULL in directly it will increment to the next integer. Is there some way to actually get a NULL in there instead of the integer? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question mark becomes NULL
At 14:25 24-2-03 -0600, gerald_clark wrote:
And you still have not shown us how you are entering the question marks.
Like this:
$sql = qq{ INSERT INTO main (id, categorie, achternaam, voornaam,
aanspreektitel, straat, huisnummer, postcode, plaatsnaam, telefoon, mobiel,
email1, email2, tekst) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) };
$sth = $dbh-prepare( $sql);
$sth-execute($id, now(), $achternaam, $voornaam, $aanspreektitel,
$straat, $huisnummer, $postcode, $plaatsnaam, $telefoon, $mobiel, $email1,
$email2, $tekst);
(BTW: the now() value results in an empty (.00.00 etc) date field)
-
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: # of Business Days?
Where I work:
1) We created a table for holidays (projected data for the next few years).
2) Created a function for the day of the week.
3) If M-F and not Holiday, then perform routine.
We had to go this route because there are way too many bank and market
holidays to keep straight with just code. Plus, it is flexible in the event
of change (i.e. 9/11 - the markets were closed for several days, so they do
not count as business days).
Hope this helps.
Bruce
- Original Message -
From: Patrick Näf [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Joe Stump [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, February 24, 2003 2:21 PM
Subject: Re: # of Business Days?
Hello
This is not correct, see this at example:
From friday to mondey there are 4 days (or 3, depends on how you count
;-).
your calcs:
$day = 4
$weeks = 4 / 7 = 0
$weekend_day = 0 * 2 = 0
$business_days = 4 - 0 = 4
If you have 100 weeks, then it will be quite correct.
here is my solution:
$day = total_days_between_a_and_b;
$less = weekend_days_between_WeekDAY_a_and_WeekDAY_b (for example: from
friday to Mondey, then it will be 2)
$weeks = $day DIV 7;
$bussiness_days = $day - $weeks * 2 - $less;
my example again:
$day = 4;
$less = 2;
$weeks = 0;
$bussiness_days = 4 - 0 * 2 - 2 = 2
or use a function from a programming language, if available. You have to
deal also with some special days (12-24 / 12-25 and so on).
greetings from Switzerland
Patrick
Joe Stump wrote:
I'm not sure of an exact query, but if you can get the total number of
days
you can mathmatically get the biz days with the following math (in psuedo
code):
$days = total_days_between_a_and_b;
$weeks = ($days / 7);
$weekend_days = $weeks * 2;
$business_days = ($days - $weekend_days);
Hope this helps. There may be more elegant ways of getting such a value.
This doesn't take into account day A being wednesday. You might be able
to
do a SELECT COUNT(*) on evaluating your date to it's Day value in
('Mon','Tue','Wed','Thu','Fri')
Check the date commands.
--Joe
--
Joe Stump [EMAIL PROTECTED]
http://www.joestump.net
Label makers are proof God wants Sys Admins to be happy.
-Original Message-
From: Lucas Cowgar [mailto:[EMAIL PROTECTED]
Sent: Monday, February 24, 2003 9:59 AM
To: MySQL Mailing List
Subject: # of Business Days?
Can anyone out there help me with a SQL query? I need to find the number
of
business days between date a and b? Obviously finding simply the number
of
days is easy, but I have no clue how to find the number of business days.
TIA!
Lucas Cowgar
Information Technologies Department
Eldorado Services Group Inc.
http://www.eldoserv.com
[EMAIL PROTECTED]
(330) 861-3009
All your base are belong to us
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: getting NULL in auto_increment column
Can you update an auto_increment with NULL in MySQL? Obviously if you try and put a NULL in directly it will increment to the next integer. Is there some way to actually get a NULL in there instead of the integer? No that's why you declare it NOT NULL when you create it. ---John Holmes... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question mark becomes NULL
On 24 Feb 2003, at 21:07, Daneman wrote: INSERT INTO MyTable (MyFields) VALUES ($val1, $val2, etc); Question marks are no longer replaced with NULL values in the text column this way, still, when I enter question marks into the charvar fields, the record is not taken into he database. I'm not sure whether this is a mySQL or a Perl problem at this stage. I think it's a user problem, but it's hard to be absolutely sure because you still haven't posted the exact Perl code you're using, so I'm having to guess what you're doing. From what you have above it appears that you are not putting quotes around your strings or escaping special characters in them, so you're probably getting SQL errors. Are you getting any error message? Do you have RaiseError on, or at least PrintError? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: getting NULL in auto_increment column
Danny wrote: Can you update an auto_increment with NULL in MySQL? Obviously if you try and put a NULL in directly it will increment to the next integer. Is there some way to actually get a NULL in there instead of the integer? No. Why would you want to? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question mark becomes NULL
If you want to insert question marks into a character field, then you
must surround them with quotation marks as you would
with any string.
I don't see you entering any questionmarks.
I see you defining an insert command that uses placeholders for the
values entered in the execute statement.
You are entering the string now() where you probably want the value
for the function now(). Leave off the quotes.
Daneman wrote:
At 14:25 24-2-03 -0600, gerald_clark wrote:
And you still have not shown us how you are entering the question marks.
Like this:
$sql = qq{ INSERT INTO main (id, categorie, achternaam, voornaam,
aanspreektitel, straat, huisnummer, postcode, plaatsnaam, telefoon,
mobiel, email1, email2, tekst) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) };
$sth = $dbh-prepare( $sql);
$sth-execute($id, now(), $achternaam, $voornaam, $aanspreektitel,
$straat, $huisnummer, $postcode, $plaatsnaam, $telefoon, $mobiel,
$email1, $email2, $tekst);
(BTW: the now() value results in an empty (.00.00 etc) date field)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question mark becomes NULL
At 15:45 24-2-03 -0500, Keith C. Ivey wrote:
On 24 Feb 2003, at 21:07, Daneman wrote:
INSERT INTO MyTable (MyFields) VALUES ($val1, $val2, etc);
Question marks are no longer replaced with NULL values in the text
column this way, still, when I enter question marks into the charvar
fields, the record is not taken into he database. I'm not sure whether
this is a mySQL or a Perl problem at this stage.
I think it's a user problem, but it's hard to be absolutely sure
because you still haven't posted the exact Perl code you're using
I have somewhere in the last half hour. Here it is:
$sql = qq{ INSERT INTO main (id, categorie, achternaam, voornaam,
aanspreektitel, straat, huisnummer, postcode, plaatsnaam, telefoon, mobiel,
email1, email2, tekst) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) };
$sth = $dbh-prepare( $sql);
$sth-execute($id, now(), $achternaam, $voornaam, $aanspreektitel,
$straat, $huisnummer, $postcode, $plaatsnaam, $telefoon, $mobiel, $email1,
$email2, $tekst);
(BTW: the now() value results in an empty (.00.00 etc) date field)
I'm having to guess [..]
Are you getting any error message?
I'm not getting any errors. I've tried eval{my code}; print $@ if
($@); but I didn't get any errors at all.
Do I have to do: $sth-execute($id, $achternaam, etc)?
Do you have RaiseError
on, or at least PrintError?
No, I must admit I don't know what they are.. (only beginning)
Thanks,
Dave
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: # of Business Days?
If you truly want only business days - i.e. you don't want to count Holidays - the only solution is to have a tables with all the business days. You'd populate this periodically with future dates as required, and a human may be required to mark off the holidays (unless you can create an algorithm that generically describes your company's Holidays - and they never change...). Once you have that table, use a Count(*) on dates between a and b. HTH, Tore. - Original Message - From: Lucas Cowgar [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Sent: Monday, February 24, 2003 12:58 PM Subject: # of Business Days? Can anyone out there help me with a SQL query? I need to find the number of business days between date a and b? Obviously finding simply the number of days is easy, but I have no clue how to find the number of business days. TIA! Lucas Cowgar Information Technologies Department Eldorado Services Group Inc. http://www.eldoserv.com [EMAIL PROTECTED] (330) 861-3009 All your base are belong to us - 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
SQL question - no CREATE VIEW in mysql
Hi People. SQL problem: given a table where each column is a number, let's say that I wish to create a second table where columns are the sum of specific columns of the first, i.e.: col_1 + col_2 + col_3 + col_4 col_1 of second table col_5 + col_6 + col_7 + col_8 col_2 of second table col_9 + col_10 + col_11 + col_12 col_3 of second table . In Oracle I could create a view from the initial table, what about MySQL? Do I really have to loop through each row to build the second table? Thanks in advance - 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
case expression
Hello all ... be gentle, I'm new at this: I've got a simple case query. One works (the top one), but when I try to add additional columns to the query it fails. I reckon it's a pretty simple mistake I'm making, but I don't know what. Any help is appreciated. - This works ... SELECT CASE WHEN CHARACTER_LENGTH(system) 65 then CONCAT(LEFT(system, 60), ...) ELSE system END -- This doesn't ... SELECT CASE WHEN CHARACTER_LENGTH(system) 65 then CONCAT(LEFT(system, 60), ...), wsh_year, id ELSE system, wsh_year, id END FROM wsh Maybe an IF statement would work better here? Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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 mysql-unsubscribe- [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
analyzing mysql general query log
Hello, Can anyone recommend a script to analyze a mysql server general query log. My goal is to determine which users are putting the most load on the server. Any other methods to achieve this same goal would be appreciated as well. Thank you. _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question mark becomes NULL
sql, query I'm not sure about perl, but I know in JSP you can substitue ? for values to avoid getting SQL syntax errors from variable values that have funky characters, apostrophes and the like. I'm not sure if Perl implements the same SQL statement syntax but with php and stripslashes - it's not needed. HTH MS - Original Message - From: Keith C. Ivey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Daneman [EMAIL PROTECTED] Sent: Monday, February 24, 2003 1:45 PM Subject: Re: Question mark becomes NULL On 24 Feb 2003, at 21:07, Daneman wrote: INSERT INTO MyTable (MyFields) VALUES ($val1, $val2, etc); Question marks are no longer replaced with NULL values in the text column this way, still, when I enter question marks into the charvar fields, the record is not taken into he database. I'm not sure whether this is a mySQL or a Perl problem at this stage. I think it's a user problem, but it's hard to be absolutely sure because you still haven't posted the exact Perl code you're using, so I'm having to guess what you're doing. From what you have above it appears that you are not putting quotes around your strings or escaping special characters in them, so you're probably getting SQL errors. Are you getting any error message? Do you have RaiseError on, or at least PrintError? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: Unknown table in field list
Jon and Tore, thanks so much for your kind help. I greatly appreciate it. I still have one bit of confusion about this whole thing. I'm reading through the manual as I type this so maybe I'll find the answer. If someone can help clarify, I'd appreciate that as well. Running this query: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', building.ADDRESS as 'results2' from parcels INNER JOIN building on parcels.DXF = building.DXF where building.ADDRESS LIKE '%21369 Vails%' order by building.ADDRESS desc; Returns these results: +-+-+-+ | record | results1| results2 | +-+-+-+ | 150-3-6 | 21369 VAILS MILL RD | 21369 VAILS MILL RD | | 150-3-6 | 21369 VAILS MILL RD | 21369 VAILS MILL RD | +-+-+-+ 2 rows in set (2.08 sec) Okay. Basically, what's happening is that I'm ending up with a duplicate result. I think I understand why that's happening - I'm searching two tables it's returning the results from each table that match the 'DXF' entry. My problem is that I need the query to be smart enough to figure out if the result is a true duplicate if so, to discard that second result. To further complicate the issue... there will be instances where duplicate entries *are* to be expected. For instance, one parcel of land may have two addressed buildings on it (for instance a duplex or apartment building). If that's the case, the DXF entry would be the same for both addresses, and I would expect to get two results from the query. In the case of the query cited at the top of this message, that happens to be one parcel of land with one addressed structure on it. Therefore, I would only want to get one result back. My point in mentioning this is that I don't think a simple LIMIT 1 would work here. I know it won't because I've tried it. Maybe this is going to be impossible to do? The parcels table has a unique key - 'DXF'. The buildings table does not. There may be duplicate 'DXF' entries in that table... the only common link between the two tables, though, is the 'DXF' entry. I'm stumped. Any suggestions? Thanks. --- Jon Wagoner [EMAIL PROTECTED] wrote: Change the query to: SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels INNER JOIN building on parcels.DXF = building.DXF where parcels.relname LIKE '%jones%' order by parcels.relname desc; __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.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: case expression
Put the other fields after the CASE statement: SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system END, wsh_year, id FROM wsh The CASE statement just returns 1 field. -Original Message- From: Richard Forgo [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 3:21 PM To: 'MySQL Mailing List' Subject: case expression Hello all ... be gentle, I'm new at this: I've got a simple case query. One works (the top one), but when I try to add additional columns to the query it fails. I reckon it's a pretty simple mistake I'm making, but I don't know what. Any help is appreciated. - This works ... SELECT CASE WHEN CHARACTER_LENGTH(system) 65 then CONCAT(LEFT(system, 60), ...) ELSE system END -- This doesn't ... SELECT CASE WHEN CHARACTER_LENGTH(system) 65 then CONCAT(LEFT(system, 60), ...), wsh_year, id ELSE system, wsh_year, id END FROM wsh Maybe an IF statement would work better here? Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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 mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: case expression
Thanks, that works fine. If I wanted to create an alias for the column, how would I do that? The first select statement (before the Else) is the current column name. When I add 'AS system' to the end of the string, it fails. Ex. SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system AS system END, wsh_year, id FROM wsh Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: Jon Wagoner [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:34 PM To: Richard Forgo; MySQL Mailing List Subject: RE: case expression Put the other fields after the CASE statement: SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system END, wsh_year, id FROM wsh The CASE statement just returns 1 field. - 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: SQL question - no CREATE VIEW in mysql
Luca, In Oracle I could create a view from the initial table, what about MySQL? MySQL will support views as of version 5.1. I cannot find it in the todo (http://www.mysql.com/doc/en/TODO.html) but I saw it elsewhere. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: case expression
Consider the CASE statement to be a function, that can only return values. Aliases would go outside the CASE STATEMENT, as follows: SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system END AS system, wsh_year, id FROM wsh -Original Message- From: Richard Forgo [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 3:44 PM To: Jon Wagoner; 'MySQL Mailing List' Subject: RE: case expression Thanks, that works fine. If I wanted to create an alias for the column, how would I do that? The first select statement (before the Else) is the current column name. When I add 'AS system' to the end of the string, it fails. Ex. SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system AS system END, wsh_year, id FROM wsh Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: Jon Wagoner [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:34 PM To: Richard Forgo; MySQL Mailing List Subject: RE: case expression Put the other fields after the CASE statement: SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system END, wsh_year, id FROM wsh The CASE statement just returns 1 field. - 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: case expression
Makes sense. Thanks for the help! Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: Jon Wagoner [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:51 PM To: Richard Forgo; MySQL Mailing List Subject: RE: case expression Consider the CASE statement to be a function, that can only return values. Aliases would go outside the CASE STATEMENT, as follows: SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system END AS system, wsh_year, id FROM wsh -Original Message- From: Richard Forgo [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 3:44 PM To: Jon Wagoner; 'MySQL Mailing List' Subject: RE: case expression Thanks, that works fine. If I wanted to create an alias for the column, how would I do that? The first select statement (before the Else) is the current column name. When I add 'AS system' to the end of the string, it fails. Ex. SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system AS system END, wsh_year, id FROM wsh Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: Jon Wagoner [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:34 PM To: Richard Forgo; MySQL Mailing List Subject: RE: case expression Put the other fields after the CASE statement: SELECT CASE WHEN CHARACTER_LENGTH(system) 65 THEN CONCAT(LEFT(system, 60), ...) ELSE system END, wsh_year, id FROM wsh The CASE statement just returns 1 field. - 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: getting NULL in auto_increment column
gerald_clark writes: Danny wrote: Can you update an auto_increment with NULL in MySQL? Obviously if you try and put a NULL in directly it will increment to the next integer. Is there some way to actually get a NULL in there instead of the integer? No. Why would you want to? Because I have a column that is normally NULL since I normally don't have information about that column. Occasionally I acquire information about it and assign it to a category. AUTO_INCREMENT is useful because I don't have to think about what the next new category will be. Instead of NULL I could use 0, if I don't have information, but NULL would be the more natural state for lack of information. No biggy though. - 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: Cardinality for FULLTEXT-indexes MySQL 4.0.10
Hi! On Feb 24, Tobias Lind wrote: Hello! I never got any comment on this one... Have noone else observed this behavior? I'm a bit worried to use this in my production site without knowing if it's and indication of some problem with FULLTEXT indexes... :) Regards, Tobias Lind It's ok, as cardinality is meaningless for FULLTEXT indexes. Hi! I have a question regarding Cardinality for FULLTEXT-indexes. I have a table with 9 indexes - the last 3 indexes are FULLTEXT. When I run show index from table, the last index (FULLTEXT) always shows cardinality=NULL... Is this normal? ...everything seems to work ok when I do selects using the index... I ran analyze table and got the response Table is already up to date I shut down mysqld, ran myisamchk -a and it went througt the first 8 indexes quickly, but on the last one it did a check record links, going through all rows in the table. After I started mysqld up again and ran show index from table, I still got cardinality=NULL for the last index. Ran myisamchk -a again, and it behaved just like the first time - passed the first 8 indexes quickly, and went through all rows on the last index. I'm running MySQL 4.0.10, rpm-binary version. Red Hat Linux 8.0 Dual P3400 MHz, 768 Mb RAM. On other tables (with less number on indexes), I have always got a cardinality after running analyze table also on fulltext-indexes. Could this be a bug? Something to worry about? Regards, Tobias Lind Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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
4.0.10 rpm doesn't include POSTIN script
The 4.0.10 MySQL-server rpm's don't include the POSTIN script. I'm
guessing this is because of moving the server to a subpackage and the
POSTIN scripts being spec'd for the main package. It's easy to see this
by running:
rpm -qp --qf %{POSTIN}\n MySQL*4.0.9*.i386.rpm
rpm -qp --qf %{POSTIN}\n MySQL*4.0.10*.i386.rpm
The first shows the script is there while the second produces basically
nothing.
I ran into this while installing 4.0.10 on a new box today. I'm guessing
that installing 4.0.9 and then upgrading to 4.0.10 will work around the
problem, but I haven't tried that yet.
--
/chris
Never offend people with style when you can offend them with substance.
- Sam Brown
-
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: getting NULL in auto_increment column
It does not sound like your data is properly normalized. You might want to take a look at the way you have things set up, this sounds like it cause problems later on. -Original Message- From: Danny [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 1:54 PM To: [EMAIL PROTECTED] Subject: Re: getting NULL in auto_increment column gerald_clark writes: Danny wrote: Can you update an auto_increment with NULL in MySQL? Obviously if you try and put a NULL in directly it will increment to the next integer. Is there some way to actually get a NULL in there instead of the integer? No. Why would you want to? Because I have a column that is normally NULL since I normally don't have information about that column. Occasionally I acquire information about it and assign it to a category. AUTO_INCREMENT is useful because I don't have to think about what the next new category will be. Instead of NULL I could use 0, if I don't have information, but NULL would be the more natural state for lack of information. No biggy though. - 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: several newbie problems
At 21:23 19.02.2003 +0100, you wrote: Or you could configure this in your c:\windows\my.ini configuration file: [mysqld] ... skip-innodb (In this case, you have to restart the server for the changes to take effect.) Nice, thanks. mysql use nick Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A My question is, what does this message mean, and how to get rid of it? As it says: Start the command line tool like that: mysql -A (or: mysql --disable-auto-rehash) On a slow machine, this can save you some seconds of your valuable time :) But, what are the negative effects? If none, why not disabeling that option by default? File 'NONEXISTENT/charsets/?.conf' not found (Errcode: 2) Character set '#27' is not a compiled character set and is not specified in the 'NONEXISTENT/charsets/Index' file Hmm. Probably your basedir variable is not set, and PHP sort of chokes on this. When starting the server (or in your my.ini, see above), you can set this variable (in most cases that's not necessary, but in your case, this seems to be a source of trouble): mysqld --basedir=c:\mysql (or whatever your installation directory is) or [mysqld] ... basedir=c:/mysql (note the slash instead of the backslash) Hope it helps, and have fun with MySQL! Cool, but that question was for sun/sparc platform :-) Colud you, please, just tell me which is equivalent of c:\windows\my.ini on UNIX, and I'd be the happies man :-) BTW, thanks for all the help :-) -- Pozdrav/Regards, Nikola [Nick] Skoric. ...ket csillag csak oz egen/mint a szemed... http://newusers.cjb.net/ - site o Usenetu na hrvatskom! - 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 Grant issue
I had asked in the past the following: In MySQL I need to create a user that has limited privileges (I figure the GRANT command can handle this), just need to know how to make sure they can only view what they have access to, or does the GRANT command handle that also? Thanks -- Jon Miller [EMAIL PROTECTED] MMT Networks Pty Ltd - 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: Unknown table in field list
You are getting two rows not because there is one in parcels and one in building, but because one of the tables has two rows and the other has one that matches according to the WHERE clause and the join criteria. To see the source data, do separate selects on the two tables: select * from parcels where DXF = '150-3-6' select * from building where DXF = '150-3-6' and ADDRESS LIKE '%21369 Vails%' Looking at the resulting data will help you determine why you are getting two result rows. If the source data is correct/as you want it to be, the keyword DISTINCT will suppress duplicate rows in the output. I usuallu try to avoid using it unless strictly required, since its use tends to hide an undesired Cartesian Products in a badly written query (usually from incomplete or improper join criteria). After reviewing your source data, try running your query with SELECT DISTINCT ... (rest of query unchanged). It should return 1 row in the sample you show, but if you had 2 DIFFERENT addresses, it would return both rows. (DISTINCT works across the entire select list (unless used in an aggregate) - this tends to be a source of some confusion among people who have limited SQL experience). HTH, Tore. - Original Message - From: Diver8 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:36 PM Subject: RE: Unknown table in field list Jon and Tore, thanks so much for your kind help. I greatly appreciate it. I still have one bit of confusion about this whole thing. I'm reading through the manual as I type this so maybe I'll find the answer. If someone can help clarify, I'd appreciate that as well. Running this query: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', building.ADDRESS as 'results2' from parcels INNER JOIN building on parcels.DXF = building.DXF where building.ADDRESS LIKE '%21369 Vails%' order by building.ADDRESS desc; Returns these results: +-+-+-+ | record | results1| results2 | +-+-+-+ | 150-3-6 | 21369 VAILS MILL RD | 21369 VAILS MILL RD | | 150-3-6 | 21369 VAILS MILL RD | 21369 VAILS MILL RD | +-+-+-+ 2 rows in set (2.08 sec) Okay. Basically, what's happening is that I'm ending up with a duplicate result. I think I understand why that's happening - I'm searching two tables it's returning the results from each table that match the 'DXF' entry. My problem is that I need the query to be smart enough to figure out if the result is a true duplicate if so, to discard that second result. To further complicate the issue... there will be instances where duplicate entries *are* to be expected. For instance, one parcel of land may have two addressed buildings on it (for instance a duplex or apartment building). If that's the case, the DXF entry would be the same for both addresses, and I would expect to get two results from the query. In the case of the query cited at the top of this message, that happens to be one parcel of land with one addressed structure on it. Therefore, I would only want to get one result back. My point in mentioning this is that I don't think a simple LIMIT 1 would work here. I know it won't because I've tried it. Maybe this is going to be impossible to do? The parcels table has a unique key - 'DXF'. The buildings table does not. There may be duplicate 'DXF' entries in that table... the only common link between the two tables, though, is the 'DXF' entry. I'm stumped. Any suggestions? Thanks. --- Jon Wagoner [EMAIL PROTECTED] wrote: Change the query to: SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels INNER JOIN building on parcels.DXF = building.DXF where parcels.relname LIKE '%jones%' order by parcels.relname desc; __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.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
