How-To-Repeat: MySql Connector J 3.x Problem
How-To-Repeat: MySql Connector J 3.x Problem Dear Sir, I have download MySQL Connector/J 3.x (mysql-connector-java-3.0.3-beta mysql-connector-java-3.0.4-gamma) and well install them on my java (JSDK 1.4.1) system (Win XP). When I try to get the result set from the database, the result value character have be change. For example, if the right result string is '01', it return 'pq'. But, when I just replace with the stable verion mysql-connector-java-2.0.14, the result character is correct. For example, if the right result string is '01', it return '01'. Please explain what happen !! Thomas Chan [EMAIL PROTECTED] == ¿Dªù®È¹C®T¼Ö¸ê°T ºÉ¦b·s®ö¿Dªù¯¸ - http://macau.sina.com $8 ¥i¥H¶RÉA ...? - http://sinaplay.sina.com.hk/easypay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using more than one CPU on FreeBSD?
Okay. By the way, sorry about sending you the reply directly before. However, I've run into a new problem new. Tried to compile mysql-4.0.9 with linuxthreads-2.2.3_9 (the most recent from ports). Linuxthreads was installed from ports - and for MySQL I used the following configure-string: CFLAGS=-D__USE_UNIX98 -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads \ -felide-constructors -fno-rtti -fno-exceptions \ ./configure --localstatedir=/var/db/mysql --without-perl --without-debug \ --without-readline --without-bench --with-mit-threads=no --with-libwrap \ --program-prefix= --with-innodb --enable-assembler --prefix=/usr/local \ --with-named-thread-libs=-L/usr/local/lib -llthread -llgcc_r When doing this, it configures and builds nicely, but when I try to start the mysql-server, nothing much happens: -bash-2.05b# /usr/local/share/mysql/mysql.server start -bash-2.05b# Starting mysqld daemon with databases from /var/db/mysql 030115 08:20:35 mysqld ended -And in the hostname.err-file I get: 030115 08:20:35 mysqld started InnoDB: Error: trx_t size is 396 in ha_innodb.cc but 416 in srv0start.c InnoDB: Check that pthread_mutex_t is defined in the same way in these InnoDB: compilation modules. Cannot continue. 030115 8:20:35 Can't init databases 030115 08:20:35 mysqld ended Any ideas? :) Thanks, Tommy -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:51 AM To: Tommy F. Eriksen Subject: RE: Using more than one CPU on FreeBSD? Yes, linuxthreads work superbly with mysql4, which in conjunction with the query cache improves performance quite a lot. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Wed, 15 Jan 2003, Tommy F. Eriksen wrote: Date: Wed, 15 Jan 2003 08:47:05 +0100 From: Tommy F. Eriksen [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Subject: RE: Using more than one CPU on FreeBSD? Hi, First of all, we can presume you're using SMP kernel. Then I'd suggest using Linuxthreads despite your dislike for them - by my experience, they work far better for mysql/FreeBSD than native threads. May be you try it again - compile mysql from the ports (WITH_LINUXTHREADS=yes), and it should solve it. Okay, it's been a while since I last tested them, so my info seems to be outdated ;) Do they work as well with mysql4? (We're currently using mysql4 and are quite fond of the query cache and the likes). Thanks, /Tommy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: unexpected: 260 rows in set on a TINYINT
You know what? You are correct on both accounts. I completely overlooked what you had written as your SQL query and am a blind moron ;-) Yeah, I dunno why you got whacky results then. It is true the TINYINT is still in it's range, so that part is working, so the issue is that the DISTINCT and/or the ORDER BY is not working. Out of curiousity, did you try it like gentradedb.gpatomdb instead of `gentradedb`.`gpatomdb` Mebbe the ` are confusing mySQL? -Original Message- From: Horizon [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 3:48 AM To: Daevid Vincent Cc: Mysql List Subject: RE: unexpected: 260 rows in set on a TINYINT I would suggest appending an ORDER BY atomtype first of all. That's what I did, see original select statement: mysql select distinct atomtype from `gentradedb`.`gpatomdb` order by atomtype; You could also experiment with SELECT DISTINCT to remove any dupes from your output. Experiment how? Also, don't confuse the RANGE of values that the atomtype can be (0-255) if set to be UNSIGNED with the NUMBER OF ROWS in your output. The TINYINT is working. You have no values greater than 255 in there. You just have duplicates which gives you more rows than you were expecting I think. ;-) I don't think I do, the database has several millions rows. Thanks for pointing out the dups, isn't what select distinct is meant to do? Terry - I would suggest appending an ORDER BY atomtype first of all. I say this because if you scan your results, you have duplicates... | 165 | | 166 | | 167 | --- | 168 | | 169 | | 170 | | 171 | | 172 | | 173 | | 174 | | 175 | | 167 | --- | 175 | So you prolly have other in there too. You could also experiment with SELECT DISTINCT to remove any dupes from your output. DÆVID. -Original Message- From: Horizon [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 11, 2003 7:46 PM To: Mysql List Subject: unexpected: 260 rows in set on a TINYINT mysql select distinct atomtype from `gentradedb`.`gpatomdb` order by atomtype; +--+ | atomtype | +--+ |0 | |1 | |2 | |3 | |4 | |5 | |6 | |7 | |8 | |9 | | 10 | | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | | 17 | | 18 | | 19 | | 20 | | 21 | | 22 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 29 | | 30 | | 31 | | 32 | | 33 | | 34 | | 35 | | 36 | | 37 | | 38 | | 39 | | 40 | | 41 | | 42 | | 43 | | 44 | | 45 | | 46 | | 47 | | 48 | | 49 | | 50 | | 51 | | 52 | | 53 | | 54 | | 55 | | 56 | | 57 | | 58 | | 59 | | 60 | | 61 | | 62 | | 63 | | 64 | | 65 | | 66 | | 67 | | 68 | | 69 | | 70 | | 71 | | 72 | | 73 | | 74 | | 75 | | 76 | | 77 | | 78 | | 79 | | 80 | | 81 | | 82 | | 83 | | 84 | | 85 | | 86 | | 87 | | 88 | | 89 | | 90 | | 91 | | 92 | | 93 | | 94 | | 95 | | 96 | | 97 | | 98 | | 99 | | 100 | | 101 | | 102 | | 103 | | 104 | | 105 | | 106 | | 107 | | 108 | | 109 | | 110 | | 111 | | 112 | | 113 | | 114 | | 115 | | 116 | | 117 | | 118 | | 119 | | 120 | | 121 | | 122 | | 123 | | 124 | | 125 | | 126 | | 127 | | 128 | | 129 | | 130 | | 131 | | 132 | | 133 | | 134 | | 135 | | 136 | | 137 | | 138 | | 139 | | 140 | | 141 | | 142 | | 143 | | 144 | | 145 | | 146 | | 147 | | 148 | | 149 | | 150 | | 151 | | 152 | | 153 | | 154 | | 155 | | 156 | | 157 | | 158 | | 159 | | 160 | | 161 | | 162 | | 163 | | 164 | | 165 | | 166 | | 167 | | 168 | | 169
Re: Mysql on FreeBSD with a NT linked database
On Tue, Jan 14, 2003 at 05:49:24PM +0100, Nicolas wrote: Hi, I'm running Freebsd 4.6.2 with mysql server v3.23.49 I have a little question about using Mysql on Freebsd and having a database on a NT 4 linked drive, using Samba (the drive was mounted on my FreeBSD system with Sharity-light because smbmount doesn't work on Freebsd) . I can query my database on the linked drive, but I cannot insert or update anything. I've always the read only error message when I try to insert a row .. Have you verified that the drive is mounted read/write? -- Hi Jeremy, yes the drive is mounted read-write. I've full acces on it and the folder's attribut is archive only. I wonder is this is a Sharity-light file locking problem. [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
Problems with MySql in AIX
Dear Sr. Sorry, but my english isn't very good. I want to install Mysql in Aix 4.3.3 in RS-6000. First I installed mysql 3.23.49 but insn't very good. When I executed /scripts/my_install_db i recived a lot of error like that: Symbol strcat (number 60) is not exported from dependent S module /usr/local/lib/libz.a(shr.o). S Symbol memmove (number 61) is not exported from dependent S module /usr/local/lib/libz.a(shr.o). S Symbol bcopy (number 62) is not exported from dependent S module /usr/local/lib/libz.a(shr.o). In internet i found that this problem was a bug and in the version 3.23.51 is ok. I installed the version 3.23.51 and max 3.23.54a and the error is same. inv:root:/usr/local/felipe/mysql-max-3.23.54a-ibm-aix4.3.2.0-powerpc scripts/mysql_install_db scripts/mysql_install_db[73]: 14726 Illegal instruction(coredump) scripts/mysql_install_db[115]: 14730 Illegal instruction(coredump) WARNING: The host 'inv' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables scripts/mysql_install_db[292]: 14734 Illegal instruction(coredump) Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! Thanks for help. Felipe. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
duplicate inserts during heavy load
Hi we have what we think is a bug regarding an insert (or an update) during times of heavy load on the mysql server. Our application should perform an insert in one table and updates on two other tables but the insert is performed up to 9 times. I'm not entierly sure that the problem is NOT in out application (PHP CGI which is run via crontab) but I checked it 2 times and 2 collegues checked it too seperatly so it should be okay. It can't be a problem with slow queries because we log them and non of the affected tables show up in the log. The job that is responsible for the high load on the server is a mysqlhotcopy FROM the database where the affected tables reside in to a new DB which isn't used at that time. There is much work and time needed to replicate the error so I would like to know beforehand what I can do to get logs or anything else to track down that problem. Any help or suggestions would be appreciated. Regards -- Stefan Immel |N|O|C Network Operation Center -+-+-+--- | Grove Auf der Stuecke 6Tel. +49 2773-8167-0 35708 Haiger / Germany Fax +49 2773-8167-20 -- mailto:[EMAIL PROTECTED] http://www.grove.de There is always hope, only because it is the one thing nobody's figured out how to kill yet. ~ Galen, Crusade Racing The Night -- http://www.nocr2.de - NOC R2 die Lösung für den IT-Workflow -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 reporst system error 1067
At 23:26 14/01/2003 +0100, Guus Landheer wrote: Hi, I am trying to install (first-time installation) Mysqld-nt on a W2000 workgroup computer. when starting it reports system error 1067 has occurred, process terminated unexpectedly TCP/IP is on. The above error means that the server for any reason has aborted. If you are installed the MySQL stuff in another directory than the default c:\mysql you must need the use of \winnt\my.ini file and to use the below keys: [mysqld] basedir=d:\mysql #edit the actual path datadir=d:\mysql\data #path otherwise take a look in the file \mysql\data\mysql.err or using a DOS prompt start the server: \mysql\binmysqld-nt --standalone --console and verify the errors printed. I covered all the installation directives. I have a feeling that it might have something to do with 'localhost'? could you give me a solution? thanks Guus Landheer The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ 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
updating some records in a table
Hi, I am new to mySQL, so I need some help. I have a table, which has record structure like this: - customer - name - register_date - expired Every day, I add some records, new customers. The field 'expired' is just a flag, either '1' or '0'. If register_date is 3 months ago then expired = 1. The format of register_date is unixtime, or Epoch. How can I update the entire database, for setting the expired field ? now = current_unixtime(); threemonths = 3 * 30 * 24 * 60 * 60; select * from table_name where register_date + threemonths now How to construct the proper select statement ? I use VARCHAR for register_date. select for i = 0 to number_of_rows do update table_name set expired = 1; I just could not think how to implement this. I need some example code how to update some records in table like in my situation. I use C ... or Perl Thanks. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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: Using more than one CPU on FreeBSD?
Did you try installing mysql4 also from ports? I didn't try 4.0.9 but 4.0.4 builded nicely this way for me. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Wed, 15 Jan 2003, Tommy F. Eriksen wrote: Date: Wed, 15 Jan 2003 09:23:18 +0100 From: Tommy F. Eriksen [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Using more than one CPU on FreeBSD? Okay. By the way, sorry about sending you the reply directly before. However, I've run into a new problem new. Tried to compile mysql-4.0.9 with linuxthreads-2.2.3_9 (the most recent from ports). Linuxthreads was installed from ports - and for MySQL I used the following configure-string: CFLAGS=-D__USE_UNIX98 -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads \ -felide-constructors -fno-rtti -fno-exceptions \ ./configure --localstatedir=/var/db/mysql --without-perl --without-debug \ --without-readline --without-bench --with-mit-threads=no --with-libwrap \ --program-prefix= --with-innodb --enable-assembler --prefix=/usr/local \ --with-named-thread-libs=-L/usr/local/lib -llthread -llgcc_r When doing this, it configures and builds nicely, but when I try to start the mysql-server, nothing much happens: -bash-2.05b# /usr/local/share/mysql/mysql.server start -bash-2.05b# Starting mysqld daemon with databases from /var/db/mysql 030115 08:20:35 mysqld ended -And in the hostname.err-file I get: 030115 08:20:35 mysqld started InnoDB: Error: trx_t size is 396 in ha_innodb.cc but 416 in srv0start.c InnoDB: Check that pthread_mutex_t is defined in the same way in these InnoDB: compilation modules. Cannot continue. 030115 8:20:35 Can't init databases 030115 08:20:35 mysqld ended Any ideas? :) Thanks, Tommy -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:51 AM To: Tommy F. Eriksen Subject: RE: Using more than one CPU on FreeBSD? Yes, linuxthreads work superbly with mysql4, which in conjunction with the query cache improves performance quite a lot. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Wed, 15 Jan 2003, Tommy F. Eriksen wrote: Date: Wed, 15 Jan 2003 08:47:05 +0100 From: Tommy F. Eriksen [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Subject: RE: Using more than one CPU on FreeBSD? Hi, First of all, we can presume you're using SMP kernel. Then I'd suggest using Linuxthreads despite your dislike for them - by my experience, they work far better for mysql/FreeBSD than native threads. May be you try it again - compile mysql from the ports (WITH_LINUXTHREADS=yes), and it should solve it. Okay, it's been a while since I last tested them, so my info seems to be outdated ;) Do they work as well with mysql4? (We're currently using mysql4 and are quite fond of the query cache and the likes). Thanks, /Tommy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using more than one CPU on FreeBSD?
From ports? This may be a dumb question, but where in the ports tree does mysql4 reside? If I look in /usr/ports/databases, I see a lot of mysql-utilities (mysqltcl etc), mysql323-server 323-client - but no mysql4? Thanks, Tommy -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 11:24 AM To: Tommy F. Eriksen Cc: [EMAIL PROTECTED] Subject: RE: Using more than one CPU on FreeBSD? Did you try installing mysql4 also from ports? I didn't try 4.0.9 but 4.0.4 builded nicely this way for me. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Wed, 15 Jan 2003, Tommy F. Eriksen wrote: Date: Wed, 15 Jan 2003 09:23:18 +0100 From: Tommy F. Eriksen [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Using more than one CPU on FreeBSD? Okay. By the way, sorry about sending you the reply directly before. However, I've run into a new problem new. Tried to compile mysql-4.0.9 with linuxthreads-2.2.3_9 (the most recent from ports). Linuxthreads was installed from ports - and for MySQL I used the following configure-string: CFLAGS=-D__USE_UNIX98 -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads \ -felide-constructors -fno-rtti -fno-exceptions \ ./configure --localstatedir=/var/db/mysql --without-perl --without-debug \ --without-readline --without-bench --with-mit-threads=no --with-libwrap \ --program-prefix= --with-innodb --enable-assembler --prefix=/usr/local \ --with-named-thread-libs=-L/usr/local/lib -llthread -llgcc_r When doing this, it configures and builds nicely, but when I try to start the mysql-server, nothing much happens: -bash-2.05b# /usr/local/share/mysql/mysql.server start -bash-2.05b# Starting mysqld daemon with databases from /var/db/mysql 030115 08:20:35 mysqld ended -And in the hostname.err-file I get: 030115 08:20:35 mysqld started InnoDB: Error: trx_t size is 396 in ha_innodb.cc but 416 in srv0start.c InnoDB: Check that pthread_mutex_t is defined in the same way in these InnoDB: compilation modules. Cannot continue. 030115 8:20:35 Can't init databases 030115 08:20:35 mysqld ended Any ideas? :) Thanks, Tommy -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:51 AM To: Tommy F. Eriksen Subject: RE: Using more than one CPU on FreeBSD? Yes, linuxthreads work superbly with mysql4, which in conjunction with the query cache improves performance quite a lot. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Wed, 15 Jan 2003, Tommy F. Eriksen wrote: Date: Wed, 15 Jan 2003 08:47:05 +0100 From: Tommy F. Eriksen [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Subject: RE: Using more than one CPU on FreeBSD? Hi, First of all, we can presume you're using SMP kernel. Then I'd suggest using Linuxthreads despite your dislike for them - by my experience, they work far better for mysql/FreeBSD than native threads. May be you try it again - compile mysql from the ports (WITH_LINUXTHREADS=yes), and it should solve it. Okay, it's been a while since I last tested them, so my info seems to be outdated ;) Do they work as well with mysql4? (We're currently using mysql4 and are quite fond of the query cache and the likes). Thanks, /Tommy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Problem using the C-API for MySQL Program breakdown after about 1Mio SQL-statements
Hallo,
I apologize for my bad english.
Im desperate about the followwing problem :
I'm using the MySQL C-API for accesssing a database with about 1.2 Mio
SQL-Statemants.
Therefor I make a connect to the database and after that a loop
starts a Subprogram about 1.2 Mio times in which a SQL-statement is sent
to the database. My problem is that the program breaks down after about
1 Mio iterations with a Bus error and Segmentation fault.
If I analyse the core dump with a debugger I get the following statements :
The program breaks down at line :
if (mysql_query(mysql2, abfrage)) {
printf (Fehler bei der Abfrage ! Fehler : %s \n %s \n,
mysql_error(mysql2), abfrage);
exit(1);
Program terminated with signal 10, Bus Error.
#0 0xff35a90c in vio_is_blocking () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#1 0xff35a4e0 in net_clear () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#2 0xff356abc in simple_command () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#3 0xff3594e8 in mysql_real_query () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#4 0x11728 in read_coor (i=1065778) at nclu-datenbank.c:279
(gdb) up
I checked the SQL statement which is sent at that time an it is ok
and leads to suitable result.
Does any one have an idea why my program breaks down ?
Greetings,
Daniel
P.S :
Here is my code for accessing the database :
(some code. )
mysql2 = mysql_init(NULL);
if (mysql2 == NULL){
printf (mysql_init() fehlgeschlagen (kein Speicher ?)\n);
exit (1);
}
if (mysql_real_connect( mysql2, def_host_name, def_user_name,
def_password, def_db_name, 0, NULL, 0) == NULL){
printf(Datenbankverbindung hat nicht geklappt.\n);
}
printf (Datenbankverbindung 1 ok\n);
while(ergebnisarray[zaehler] != NULL) {
/*printf (zahl = %i\n, ergebnisarray[zaehler]);*/
j=read_coor(ergebnisarray[zaehler]);
/* This is my fuction which sends the SQL-statements accessed 1.2 Mio
times */
zaehler++;
}
mysql_close(mysql2);
(some code)
In my function I access the database with the following lines :
(some code)..
for (seglaengenzaehler = 1; seglaengenzaehler = seglength;
seglaengenzaehler ++) {
sprintf (abfrage, select xkoor, ykoor, zkoor, id from %iloop inner
join pdbmolekuel on m%i=molekuelnr inner join pdbatom on molekuelnr =
molekuelzuordnung where loopnummer = %i, seglength, seglaengenzaehler, i);
if (mysql_query(mysql2, abfrage)) {
printf (Fehler bei der Abfrage ! Fehler : %s \n %s \n,
mysql_error(mysql2), abfrage);
exit(1);
}
else {
ergebnis = mysql_use_result(mysql2);
}
}
if (ergebnis == NULL) {
printf (Fehler beim show tables !\n Fehler : %s ,
mysql_error(mysql2));
}
else {
/*printf (Alles in Ordnung !\n);*/
while((myzeile = mysql_fetch_row(ergebnis)))
{
if ( strcmp(myzeile[3],CA)==0 || strcmp(myzeile[3],N) == 0 ||
strcmp(myzeile[3],C) == 0 ||
strcmp(myzeile[3],O) == 0)
{
// Es werden nur die Atome CA, N, C und O beachtet
coor[j] = atof(myzeile[0]);
coor[j+1] = atof(myzeile[1]);
coor[j+2] = atof(myzeile[2]);
j += 3;
}
}
mysql_free_result (ergebnis);
}
resno = j/12;
}
(some code)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: Foreign key in INNODB
On Wed, 15 Jan 2003 11:02:44 +1100 vinita Vigine Murugiah [EMAIL PROTECTED] wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice There is no index on ifname in table equipment_interfaces. mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) *** mysql CREATE TABLE equipment_interfaces_IPv4alias ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifIPv4alias CHAR(20) NOT NULL, - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - INDEX (ifName), - FOREIGN KEY (ifName) REFERENCES equipment_interfaces (ifName) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName, ifIPv4alias) - ) TYPE=INNODB; ERROR 1005: Can't create table './test/equipment_interfaces_IPv4alias.frm' (errno: 150) mysql *** ** [vinita@mungah] vinita [1:54] perror 150 Error code 150: Unknown error: 150 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED]Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: May be bug in MySQL 4
Hi, I think is not a bug.The result is normal becuase function TO_DAYS() return the daynumber and not the date.If you wish to find a date depend on the number of days you can try(like example): select FROM_DAYS(TO_DAYS(NOW())-3); Read the manual about this two functions Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Dobromir Velev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 14, 2003 9:37 PM Subject: May be bug in MySQL 4 Hi, There might be a bug in mysql 4 (tested on 4.0.8-gamma and 4.0.9-gamma) when combining simple arithmetic operations and date conversion I have a table with the following structure date_col date -00-00 int_col tinyint(3) unsigned This is what happens when I try to run a query like the one below. mysql select to_days(Now())-to_days(data_col)-int_col from table_name where to_days(Now())-to_days(data_col)int_col; +---+ | to_days(Now())-to_days(date_col)-int_col | +---+ | 18446744073709551607 | | 18446744073709551609 | | 18446744073709551607 | | 18446744073709551609 | | 18446744073709551607 | | 18446744073709551615 | Tested on Linux Redhat 7.3 and on a Slackware 7.0.0 TIA Dobromir Velev - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Problem using the C-API for MySQL Program breakdown after about 1 Mio SQL-statements
Hi,
Maybe it's a good ideea to increase the value of max_allowed_packet(from
my.cnf configuration file) to 2M.By default is 1MB.
Regards,
Gelu
_
G.NET SOFTWARE COMPANY
Permanent e-mail address : [EMAIL PROTECTED]
[EMAIL PROTECTED]
- Original Message -
From: Daniel Wetzler [EMAIL PROTECTED]
To: MySQL Mailing Liste [EMAIL PROTECTED]
Sent: Wednesday, January 15, 2003 11:54 AM
Subject: Problem using the C-API for MySQL Program breakdown after about 1
Mio SQL-statements
Hallo,
I apologize for my bad english.
Im desperate about the followwing problem :
I'm using the MySQL C-API for accesssing a database with about 1.2 Mio
SQL-Statemants.
Therefor I make a connect to the database and after that a loop
starts a Subprogram about 1.2 Mio times in which a SQL-statement is sent
to the database. My problem is that the program breaks down after about
1 Mio iterations with a Bus error and Segmentation fault.
If I analyse the core dump with a debugger I get the following statements
:
The program breaks down at line :
if (mysql_query(mysql2, abfrage)) {
printf (Fehler bei der Abfrage ! Fehler : %s \n %s \n,
mysql_error(mysql2), abfrage);
exit(1);
Program terminated with signal 10, Bus Error.
#0 0xff35a90c in vio_is_blocking () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#1 0xff35a4e0 in net_clear () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#2 0xff356abc in simple_command () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#3 0xff3594e8 in mysql_real_query () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#4 0x11728 in read_coor (i=1065778) at nclu-datenbank.c:279
(gdb) up
I checked the SQL statement which is sent at that time an it is ok
and leads to suitable result.
Does any one have an idea why my program breaks down ?
Greetings,
Daniel
P.S :
Here is my code for accessing the database :
(some code. )
mysql2 = mysql_init(NULL);
if (mysql2 == NULL){
printf (mysql_init() fehlgeschlagen (kein Speicher ?)\n);
exit (1);
}
if (mysql_real_connect( mysql2, def_host_name, def_user_name,
def_password, def_db_name, 0, NULL, 0) == NULL){
printf(Datenbankverbindung hat nicht geklappt.\n);
}
printf (Datenbankverbindung 1 ok\n);
while(ergebnisarray[zaehler] != NULL) {
/*printf (zahl = %i\n, ergebnisarray[zaehler]);*/
j=read_coor(ergebnisarray[zaehler]);
/* This is my fuction which sends the SQL-statements accessed 1.2 Mio
times */
zaehler++;
}
mysql_close(mysql2);
(some code)
In my function I access the database with the following lines :
(some code)..
for (seglaengenzaehler = 1; seglaengenzaehler = seglength;
seglaengenzaehler ++) {
sprintf (abfrage, select xkoor, ykoor, zkoor, id from %iloop inner
join pdbmolekuel on m%i=molekuelnr inner join pdbatom on molekuelnr =
molekuelzuordnung where loopnummer = %i, seglength, seglaengenzaehler,
i);
if (mysql_query(mysql2, abfrage)) {
printf (Fehler bei der Abfrage ! Fehler : %s \n %s \n,
mysql_error(mysql2), abfrage);
exit(1);
}
else {
ergebnis = mysql_use_result(mysql2);
}
}
if (ergebnis == NULL) {
printf (Fehler beim show tables !\n Fehler : %s ,
mysql_error(mysql2));
}
else {
/*printf (Alles in Ordnung !\n);*/
while((myzeile = mysql_fetch_row(ergebnis)))
{
if ( strcmp(myzeile[3],CA)==0 || strcmp(myzeile[3],N) == 0 ||
strcmp(myzeile[3],C) == 0 ||
strcmp(myzeile[3],O) == 0)
{
// Es werden nur die Atome CA, N, C und O beachtet
coor[j] = atof(myzeile[0]);
coor[j+1] = atof(myzeile[1]);
coor[j+2] = atof(myzeile[2]);
j += 3;
}
}
mysql_free_result (ergebnis);
}
resno = j/12;
}
(some code)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: myisamchk warnings -- open tables
Hi, Some time ago i've read that we shouldn't use myisamchk while the mysql server was running. Now, i don't find where in the manual says something about it. I think you should do a FLUSH TABLES and ensure that no one touches the tables (including mysqld) before using myisamchk. Coudn't you use CHECK/OPTIMIZE/REPAIR TABLES (or whatever you're doing with myisamchk) instead of myisamchk ? I think it's safer... :-) This short text is more specific: http://www.mysql.com/doc/en/myisamchk_syntax.html Note that if you get an error like: myisamchk: warning: 1 clients is using or hasn't closed the table properly This means that you are trying to check a table that has been updated by the another program (like the mysqld server) that hasn't yet closed the file or that has died without closing the file properly. If you mysqld is running, you must force a sync/close of all tables with FLUSH TABLES and ensure that no one is using the tables while you are running myisamchk. In MySQL Version 3.23 the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables. On Mon, 2003-01-13 at 17:09, havoc wrote: Since the http://lists.mysql.com/ archives site is down, I'll have to ask this question without the benifit of the archives... I have a (very large) Perl script that has a bad tendency to cause the following warning from myisamchk: myisamchk: MyISAM file /var/lib/mysql//table_name.MYI myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table '/var/lib/mysql//table_name.MYI' is usable but should be fixed It does this for table after table (I'm running multiple instances of the same database for diffrerent sites). It's fine to go back and run myisamchk --recover -- quick, but I'd rather stop the bleeding than continue applying bandages. I have started through the code making sure there is a $sth-finish(); after every table query/insert/update, but I seem to have made the problem worse! Is there something I should be paying special attention to? Is there something that I might just plain be missing? Many thanks in advance! Jody Harris -- -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select a range
Hi I'm newbie I have to find from xvalue to yvalue; I have written: $query=SELECT * FROM inner-multipli WHERE cod_int LIKE ='%$cod_int1%' AND cod_int LIKE ='%$cod_int2'=; This not work; how can I do? Thanks in advance bruper - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldb is hanging.
Description: How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: 2013 Error - DB Hanging during a Query (one line) Severity: Priority: Category: mysql Class: Release: mysql-3.23.52 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.52, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.52 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 8 min 36 sec Threads: 1 Questions: 2 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries per second avg: 0.004 Environment: System: Linux sysiphus 2.4.18-14 #1 Wed Sep 4 11:57:57 EDT 2002 i586 i586 i386 GNU/Linux Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CXX='g++' CXXFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Dez 10 16:33 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1327065 Sep 6 00:59 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 6 00:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 6 00:50 /usr/lib/libc.so Configure command: ./configure --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --datadir=/usr/share --libdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --without-readline --without-debug --enable-shared --with-extra-charsets=complex --with-bench --localstatedir=/var/lib/mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql --with-extra-charsets=all --with-innodb --enable-local-infile --enable-large-files=yes --enable-largefile=yes --with-berkeley-db --with-thread-safe-client 'CFLAGS=-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' 'CXXFLAGS=-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Error
When I try to connect to the MySql server on localhost I get the following error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I am not quite sure what this means. I am running Mac OS X 10.2.3. Before I upgraded from 10.1, I had no problems, but now am unable to connect at all. Is it possible to remove MySql (and how do I go about it) so that I can reinstall it again? Sorry if these are stupid questions, but I can't seem to find how to uninstall MySql in the manual. Thanks -- Daniel Florian [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (work) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Avg_row_length
Yep, you're right!
Perhaps an extra byte is automatically reserved, not only for a
null/not null status bit, but also for other internal use - eg; a
'row changed' bit, etc.
I did a quick test:
mysql create table t2 (c1 varchar(255));
Query OK, 0 rows affected (0.00 sec)
mysql insert into t2 values ('s');
Query OK, 1 row affected (0.00 sec)
And this gave table status of
Name: t2
Type: MyISAM
Row_format: Dynamic
Rows: 1
Avg_row_length: 20
Data_length: 20
Max_data_length: 4294967295
Index_length: 1024
Data_free: 0
Auto_increment: NULL
so obviously the avg_row_length includes extra bytes for mysql internal info.
Learn something every day!
-steve
At 1:03 PM +0100 1/15/03, you wrote:
Steve,
I believe there is an extra byte for the 'null' flag; if you declare
this column to be NOT NULL, I'll bet the avg length will be 10.
nice bet, but you lose g:
mysql DESCRIBE mynotnullisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col | char(10) | | | | |
+---+--+--+-+-+---+
1 row in set (0.16 sec)
mysql SHOW TABLE STATUS LIKE 'mynotnullisam';
+---+++--++-
+
| Name | Type | Row_format | Rows | Avg_row_length |
Data_length |
+---+++--++-
+
| mynotnullisam | MyISAM | Fixed |3 | 11 |
33 |
+---+++--++-
+
--
++
| Steve Edberg [EMAIL PROTECTED] |
| University of California, Davis (530)754-9127 |
| Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002 |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens... |
++
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: three table join
I've wanted to post this query example a few times (and I hope I got it right; mornings aren't my best time) ... multiple JOINs: SELECT stuff FROM table1 LEFT JOIN table2 ON table1.fk = table2.pk LEFT JOIN table3 ON table2.fk = table3.pk WHERE other_conditions ... You can repeat that as many levels as you want (performance depends on indexing and the optimizer). You need to think in terms of what would be equal to what between tables in the correct result row. So if you would do a secondary sub-select of SELECT fk from table2 where ... then you end up with a left join like above. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. 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: when to normalize out to a table
Cal Evans wrote: Always start with a *fully* normalized design. You can always denormalize for speed but it's real hard to normalize once you are in production. I'd rather be 'stupid' about how normalized my data is and then write caching into my program (or use MySQL 4's query cache, which I don't have yet) than have to extract data properly after the fact for normalization. Reading any old website about normalization will say basically the same thing (and if they don't, they shouldn't be writing about normalization). -- Michael T. Babcock C.T.O., FibreSpeed Ltd. 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
missing big5.conf in my sql
Dear Sir/ Madam, I noticed that the character set big5 is missing in the installation file of My SQL but the index file indicate that MySQL does have this character set. Is there a place to download it? Please help me. Thank you in advance. Yours, Warren - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: select a range
if your using numbers , which i guess you are, you do not need to use LIKE. $query=SELECT * FROM inner-multipli WHERE cod_in =$cod_int1 AND cod_int =$cod_int2; bruno peracchio wrote: Hi I'm newbie I have to find from xvalue to yvalue; I have written: $query=SELECT * FROM inner-multipli WHERE cod_in LIKE ='%$cod_int1%' AND cod_int LIKE ='%$cod_int2'=; This not work; how can I do? Thanks in advance bruper - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- --- === --- ::: ::: ::: ::: :::=== :::=== --- ::: === ::: :: === :: === === ::: === === --- === === === = === === === --- === === === === === === === === --- === === == === === www.dunkfordyce.co.uk== - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Notice: Undefined index:
That looks like a PHP problem to me. Have you tried mysql_fetch_assoc()? I
believe mysql_fetch_row() gets you an array with a numeric index (in which
case lastname would be in $line[0]).
Notice: Undefined index: lastname in E:\IIS Webs\index.php on line 29
?
PRINT HTML;
PRINT HEAD;
PRINT TITLEADDRESSES/title;
PRINT /HEAD;
PRINT BODY BGCOLOR=white TEXT=#00 LINK=#FF VLINK=#66
aLINK=#FF;
mysql_pconnect(127.0.0.1:3308, user, pass) or
die(could not connect);
mysql_select_db(address);
$query = SELECT lastname, firstname, city FROM data;
$result = mysql_query ($query);
while ($line = mysql_fetch_row($result)) {
echo BR.$line['lastname'];
echo BR.$line['firstname'];
echo BR.$line['city'].BR;
}
?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem using the C-API for MySQL Program breakdown after about1 Mio SQL-statements
Thanks for your advice.
I tried it with the higher value (3M) but the program broke down
like before.
I increased the other buffer values too after that and am
testing now if this helps.
Greetings,
Daniel
Gelu Gogancea wrote:
Hi,
Maybe it's a good ideea to increase the value of max_allowed_packet(from
my.cnf configuration file) to 2M.By default is 1MB.
Regards,
Gelu
_
G.NET SOFTWARE COMPANY
Permanent e-mail address : [EMAIL PROTECTED]
[EMAIL PROTECTED]
- Original Message -
From: Daniel Wetzler [EMAIL PROTECTED]
To: MySQL Mailing Liste [EMAIL PROTECTED]
Sent: Wednesday, January 15, 2003 11:54 AM
Subject: Problem using the C-API for MySQL Program breakdown after about 1
Mio SQL-statements
Hallo,
I apologize for my bad english.
Im desperate about the followwing problem :
I'm using the MySQL C-API for accesssing a database with about 1.2 Mio
SQL-Statemants.
Therefor I make a connect to the database and after that a loop
starts a Subprogram about 1.2 Mio times in which a SQL-statement is sent
to the database. My problem is that the program breaks down after about
1 Mio iterations with a Bus error and Segmentation fault.
If I analyse the core dump with a debugger I get the following statements
:
The program breaks down at line :
if (mysql_query(mysql2, abfrage)) {
printf (Fehler bei der Abfrage ! Fehler : %s \n %s \n,
mysql_error(mysql2), abfrage);
exit(1);
Program terminated with signal 10, Bus Error.
#0 0xff35a90c in vio_is_blocking () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#1 0xff35a4e0 in net_clear () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#2 0xff356abc in simple_command () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#3 0xff3594e8 in mysql_real_query () from
/usr/local/mysql/lib/mysql/libmysqlclient.so.10
(gdb) up
#4 0x11728 in read_coor (i=1065778) at nclu-datenbank.c:279
(gdb) up
I checked the SQL statement which is sent at that time an it is ok
and leads to suitable result.
Does any one have an idea why my program breaks down ?
Greetings,
Daniel
P.S :
Here is my code for accessing the database :
(some code. )
mysql2 = mysql_init(NULL);
if (mysql2 == NULL){
printf (mysql_init() fehlgeschlagen (kein Speicher ?)\n);
exit (1);
}
if (mysql_real_connect( mysql2, def_host_name, def_user_name,
def_password, def_db_name, 0, NULL, 0) == NULL){
printf(Datenbankverbindung hat nicht geklappt.\n);
}
printf (Datenbankverbindung 1 ok\n);
while(ergebnisarray[zaehler] != NULL) {
/*printf (zahl = %i\n, ergebnisarray[zaehler]);*/
j=read_coor(ergebnisarray[zaehler]);
/* This is my fuction which sends the SQL-statements accessed 1.2 Mio
times */
zaehler++;
}
mysql_close(mysql2);
(some code)
In my function I access the database with the following lines :
(some code)..
for (seglaengenzaehler = 1; seglaengenzaehler = seglength;
seglaengenzaehler ++) {
sprintf (abfrage, select xkoor, ykoor, zkoor, id from %iloop inner
join pdbmolekuel on m%i=molekuelnr inner join pdbatom on molekuelnr =
molekuelzuordnung where loopnummer = %i, seglength, seglaengenzaehler,
i);
if (mysql_query(mysql2, abfrage)) {
printf (Fehler bei der Abfrage ! Fehler : %s \n %s \n,
mysql_error(mysql2), abfrage);
exit(1);
}
else {
ergebnis = mysql_use_result(mysql2);
}
}
if (ergebnis == NULL) {
printf (Fehler beim show tables !\n Fehler : %s ,
mysql_error(mysql2));
}
else {
/*printf (Alles in Ordnung !\n);*/
while((myzeile = mysql_fetch_row(ergebnis)))
{
if ( strcmp(myzeile[3],CA)==0 || strcmp(myzeile[3],N) == 0 ||
strcmp(myzeile[3],C) == 0 ||
strcmp(myzeile[3],O) == 0)
{
// Es werden nur die Atome CA, N, C und O beachtet
coor[j] = atof(myzeile[0]);
coor[j+1] = atof(myzeile[1]);
coor[j+2] = atof(myzeile[2]);
j += 3;
}
}
mysql_free_result (ergebnis);
}
resno = j/12;
}
(some code)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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
RE: Mysql Error
The error that you have is normally that MySQL in not running. If it is running then MySQL client can not find the sock file. I should not think you will have to reinstall. Simon -Original Message- From: Daniel Florian [mailto:[EMAIL PROTECTED]] Sent: 15 January 2003 13:11 To: [EMAIL PROTECTED] Subject: Mysql Error When I try to connect to the MySql server on localhost I get the following error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I am not quite sure what this means. I am running Mac OS X 10.2.3. Before I upgraded from 10.1, I had no problems, but now am unable to connect at all. Is it possible to remove MySql (and how do I go about it) so that I can reinstall it again? Sorry if these are stupid questions, but I can't seem to find how to uninstall MySql in the manual. Thanks -- Daniel Florian [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (work) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: How-To-Repeat: MySql Connector J 3.x Problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thomas Chan wrote: How-To-Repeat: MySql Connector J 3.x Problem Dear Sir, I have download MySQL Connector/J 3.x (mysql-connector-java-3.0.3-beta mysql-connector-java-3.0.4-gamma) and well install them on my java (JSDK 1.4.1) system (Win XP). When I try to get the result set from the database, the result value character have be change. For example, if the right result string is '01', it return 'pq'. But, when I just replace with the stable verion mysql-connector-java-2.0.14, the result character is correct. For example, if the right result string is '01', it return '01'. Please explain what happen !! Can you create a reproducible test case? What character set is your database set to? What character set is your computer set to? -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+JWvQtvXNTca6JD8RAkqHAKDH0+xI83kykgaCMze0m8sE9NbqYwCeNDhF QS+04+7KosDN9Mma/YfSZvw= =ysZh -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: Compiling error 3.23.54a (src)
Oh.. I see. I dont know about Linux, all I knew was that I wanted it so I burned the necissary CD's and installed linux on my other machine. So, you're saying I should use a mandrake-linux forum, instead of mysql? - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Tyler [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, January 14, 2003 10:32 PM Subject: Re: Compiling error 3.23.54a (src) On Tue, Jan 14, 2003 at 08:58:07PM -0700, Tyler wrote: what appropriate forums? I'm not a Mandrake user, but every Linux distribution I've tried has had various e-mail and web-base forums in which you can ask questions. Furthermore, they have official support channels and ways of filing bug reports if you've found a but. Surely the Madrake documentation mentinons *some* of them. And odds are that Google could find a few more of them. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,015,781,700 queries (379/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Linux Mysql vs Windows Mysql
Jeremy Zawodny wrote: Add error checking to all MySQL API calls and the problem will become apparent. Right now you're assuming they all just work (except for the mysql_connect()) but they probably don't. I highly recommend anyone using MySQL use either AdoDB http://php.weblogs.com/adodb or write their own handler (wrapper) functions around the MySQL library calls. Most of my code uses something that looks like: $Vehicles = db_get_vehicles($Params); meta_show_vehicles($Vehicles, $Format); ... where db_get_vehicles might do something like: if (!($Params = sql_check_params($Params))) return -1; list ($rows, $error) = sql_do_query(SELECT ... FROM Vehicles LEFT JOIN ... WHERE $Params); ... return $rows; I worry about connecting, doing the actual query once connected and error checking in sql_do_query, so my main code looks clean(er). -- Michael T. Babcock C.T.O., FibreSpeed Ltd. 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: MySQL Secure Replication
Jeremy Zawodny wrote: there seem to be a lack of documentation on this part. Mostly because it can't be done (that I'm aware of). Except with stunnel (often recommended) or SSH (which I've had running for months doing this). PS, as with the MySQL daemon, I run my ssh tunnel under 'supervise' so as to make sure its always there and restarts if it gets killed / dies / is stupid / upgraded. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. 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
Uninstall MySQL?
Can someone please guide me in uninstalling all the failed installs of MySQL I've got? I've installed like 2 binaries, and 1 source.. none work :( - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI $sth-fetchrow_hashref() persistence?
Hi, if I call: my $record = $sth-fetchrow_hashref(); then: $sth-finish(); How valid is it for me to keep referring to my $record hash pointer? Thanks, Jeff Snoxell Aetherweb Ltd http://www.aetherweb.co.uk [SPAM FILTER FODDER: 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
Need help with a query, joins, etc.
I had a query that was working fine before for a sports project .. basically, 3 tables .. one stored goal data, one stored team data, and the other player data. To get all the scoring data (player names, jersey #'s), I left joined to the player table 3 different times (on GOAL, ASSIST1, and ASSIST2). My problem now is, I've split the player information up into 2 different tables for other purposes, and need to pull this information from both, but I would seemingly have to double the number of left joins to do it, and I'm not sure if that's even possible now. The query (broken) select teams.TEAMID, teams.TEAMNAME, goals.GOALID, goals.SCORER as GSID, goals.ASSIST1 as GA1ID, goals.ASSIST2 as GA2ID, goals.PERIOD, goals.TIME, goals.TYPE, p1.JERSEYNUM as SCORERNUM, ol_g_u_1.FIRSTNAME as SCORERF, ol_g_u_1.LASTNAME as SCORERL, p2.JERSEYNUM as ASSIST1NUM, ol_g_u_2.FIRSTNAME as ASSIST1F, ol_g_u_2.LASTNAME as ASSIST1L, p3.JERSEYNUM as ASSIST2NUM, ol_g_u_3.FIRSTNAME as ASSIST2F, ol_g_u_3.LASTNAME as ASSIST2L FROM ol_global.users, ol_icehockey.teams, ol_icehockey.goals LEFT JOIN ol_icehockey.players as p1 ON goals.SCORER = p1.USERNUM LEFT JOIN ol_icehockey.players as p2 ON goals.ASSIST1 = p2.USERNUM LEFT JOIN ol_icehockey.players as p3 ON goals.ASSIST2 = p3.USERNUM where GAMEID = '$GAMEID' AND teams.TEAMID = '$TEAMID' AND teams.TEAMID = p1.TEAMID order by PERIOD, TIME DESC Now, this is obviously broken because ol_g_u_* aren't defined yet (would be left joins to the users table 3 times) .. due to the situation, I'm not sure if I'm even using the right kind of joins anymore. Can someone offer some help or point me in the right direction on adding those other joins in there? Thanks, Chad __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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: replication - queries out of order or dropped?
- sql,query On Tue, 2003-01-14 at 23:55, Jeremy Zawodny wrote: What do the relevant sections of the my.cnf files on the master and slave look like? -- Hi, I've had the same issue - but always assumed that replication was meant to ignore create database I've looked into it further, and it looks like if you have any binlog-ignore-db directives in the master's my.cnf file then create database commands are not routed to the binlog. == Martin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB table, NOT NULL question
Harald Fuchs wrote: You _did_ enter some data, namely a string which just happens to be the empty string (which in turn is different from no data, i.e. NULL). How should MySQL know that you don't want empty strings? I think the user expects: INSERT INTO table (bar) VALUES (text); to behave the same as: INSERT INTO table (foo, bar) VALUES (NULL, text); which would throw an error (correctly). No comment ;-) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. 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: Notice: Undefined index:
dont forget mysql_fetch_object()
then you will have $line-lastname;
Maureen Roihl wrote:
That looks like a PHP problem to me. Have you tried mysql_fetch_assoc()? I
believe mysql_fetch_row() gets you an array with a numeric index (in which
case lastname would be in $line[0]).
Notice: Undefined index: lastname in E:\IIS Webs\index.php on line 29
?
PRINT HTML;
PRINT HEAD;
PRINT TITLEADDRESSES/title;
PRINT /HEAD;
PRINT BODY BGCOLOR=white TEXT=#00 LINK=#FF VLINK=#66
aLINK=#FF;
mysql_pconnect(127.0.0.1:3308, user, pass) or
die(could not connect);
mysql_select_db(address);
$query = SELECT lastname, firstname, city FROM data;
$result = mysql_query ($query);
while ($line = mysql_fetch_row($result)) {
echo BR.$line['lastname'];
echo BR.$line['firstname'];
echo BR.$line['city'].BR;
}
?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
---
===
--- ::: ::: ::: ::: :::=== :::===
--- ::: === ::: :: === :: === === ::: === ===
--- === === === = === === ===
--- === === === === === === === ===
--- === === == === ===
www.dunkfordyce.co.uk==
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: Availablility of MySQL 4.0.9 for Windows (bins)
On Tuesday 14 January 2003 22:54, Gabe Geisendorfer wrote: Does anyone know when MySQL 4.0.9 for Windows (bins) will be available for download? I've downloaded 4.0.9 for Win from one of the mirrors .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: filter input
On Wednesday 15 January 2003 04:26, julian haffegee wrote: I want to insert some text into my database, but I'm worried about bad data - metacharacters etc. So I need to filter the input and remove bad characters. Which characters should I remove? What is bad for mysql? does anyone know a good subroutine (or webpage telling me all about it) for reference. I did have a good(ish) php way of doing this, but need perl this time. You should escape some chars: http://www.mysql.com/doc/en/String_syntax.html Take a look at addslashes() function in PHP or mysql_escape_string(). You can also use MySQL function QUOTE(): http://www.mysql.com/doc/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Foreign key in INNODB
On Wednesday 15 January 2003 02:02, vinita Vigine Murugiah wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice [skip] mysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) ifName in the `equipment_interfaces` should be indexed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Notice: Undefined index:
It that does not work. You could try the following snippet:
if ($result = mysql_query ($query);)
{
while ($row = mysql_fetch_array ($result))
{
...
}
mysql_free_result($result);
}
else
{
...
}
-Original Message-
From: Maureen Roihl [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 7:49 AM
To: 'C. Reeve'; MySQL List
Subject: RE: Notice: Undefined index:
That looks like a PHP problem to me. Have you tried mysql_fetch_assoc()? I
believe mysql_fetch_row() gets you an array with a numeric index (in which
case lastname would be in $line[0]).
Notice: Undefined index: lastname in E:\IIS Webs\index.php on line 29
?
PRINT HTML;
PRINT HEAD;
PRINT TITLEADDRESSES/title;
PRINT /HEAD;
PRINT BODY BGCOLOR=white TEXT=#00 LINK=#FF VLINK=#66
aLINK=#FF;
mysql_pconnect(127.0.0.1:3308, user, pass) or
die(could not connect);
mysql_select_db(address);
$query = SELECT lastname, firstname, city FROM data;
$result = mysql_query ($query);
while ($line = mysql_fetch_row($result)) {
echo BR.$line['lastname'];
echo BR.$line['firstname'];
echo BR.$line['city'].BR;
}
?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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
installation problem on XP again...
Okay I have installed mysql on windows XP-home, and cannot get round the Error cant connect to mysql server on localhost 10061. I have read the manual again and again and again...I have read the public mailing list again and again and again...I have tweaked the `ini` file using the winmysqladmin tool again and again and again...I have installed and re-installed again and again and again... what must I do next to get it to run again and again and again __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.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: Notice: Undefined index:
mysql_fetch_array() does the same thing as mysql_fetch_assoc(), both will
return the record as an associative array, which is how you're trying to
access it.
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 9:49 AM
To: 'Maureen Roihl'; 'C. Reeve'; MySQL List
Subject: RE: Notice: Undefined index:
It that does not work. You could try the following snippet:
if ($result = mysql_query ($query);)
{
while ($row = mysql_fetch_array ($result))
{
...
}
mysql_free_result($result);
}
else
{
...
}
-Original Message-
From: Maureen Roihl [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 7:49 AM
To: 'C. Reeve'; MySQL List
Subject: RE: Notice: Undefined index:
That looks like a PHP problem to me. Have you tried
mysql_fetch_assoc()? I
believe mysql_fetch_row() gets you an array with a numeric
index (in which
case lastname would be in $line[0]).
Notice: Undefined index: lastname in E:\IIS Webs\index.php
on line 29
?
PRINT HTML;
PRINT HEAD;
PRINT TITLEADDRESSES/title;
PRINT /HEAD;
PRINT BODY BGCOLOR=white TEXT=#00 LINK=#FF VLINK=#66
aLINK=#FF;
mysql_pconnect(127.0.0.1:3308, user, pass) or
die(could not connect);
mysql_select_db(address);
$query = SELECT lastname, firstname, city FROM data;
$result = mysql_query ($query);
while ($line = mysql_fetch_row($result)) {
echo BR.$line['lastname'];
echo BR.$line['firstname'];
echo BR.$line['city'].BR;
}
?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: problem to stop mysql under windows xp
Massimo, please address these general MySQL questions to [EMAIL PROTECTED] or [EMAIL PROTECTED] I think Miguel has been fixing the stopping of multiple servers on Windows lately. You should test the very latest MySQL-4.0.x release. I am Cc:ing this also to Miguel who can comment on this. Best regards, Heikki Innobase Oy sql query - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Wednesday, January 15, 2003 9:13 AM Subject: Re: problem to stop mysql under windows xp Nobody can help us ? - Original Message - From: Victor King [EMAIL PROTECTED] To: Massimo Petrini [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 14, 2003 4:00 PM Subject: Re: problem to stop mysql under windows xp I run into the same problem. in general, once slave started, it can not be stopped, via mysqlslave stop or net stop mysql (stop Windows Service) Anybody has solution to this problem? Thanks, VK - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 27, 2002 9:54 AM Subject: problem to stop mysql under windows xp Since 4.03 version under windows XP with SP1 (today I am using 4.0.7 max-nt) , on a slave host, when I stopped the service Mysql, via MYSQLADMIN the semaphore became yellow instead red. The only solution is shutdown the pc. Also when I run the command slave stop the process hang and not became in stop (you cannot have the prompt command). Do you know this problem ? Tks in advance - Massimo Petrini c/o Omt spa Via Ferrero 67/a 10090 Cascine Vica (TO) Tel.+39 011 9505334 Fax +39 011 9575474 E-mail [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: Re: Doorstop II - coming soon
At 8:21 -0500 1/15/03, Michael T. Babcock wrote: Paul DuBois wrote: Yes, the second edition really is 1248 pages, 50% more than the first edition. Sorry about that. You should rephrase that to: Yes, the second edition is 1248 pages, that's a 33% discount over the first edition. assuming the price is the same. Heh, that's another way of looking at it. :-) Yes, the price is the same. Such a bargain! -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication - queries out of order or dropped?
Hi Martin, Replication definitely does not ignore CREATE DATABASE all the time, just every now and then :) I had 93 databases that made it through replication, and the 94th failed. There aren't any binlog-ignore-db paramaters. I've dumped the binlog out, and CREATE DATABASE did make it into the log, and the slave did make it past that point without creating the database. Is there any reason to think that replication is more reliable in the 4.0 series? David On Wed, Jan 15, 2003 at 02:39:34PM +, Martin Waite wrote: - sql,query On Tue, 2003-01-14 at 23:55, Jeremy Zawodny wrote: What do the relevant sections of the my.cnf files on the master and slave look like? -- Hi, I've had the same issue - but always assumed that replication was meant to ignore create database I've looked into it further, and it looks like if you have any binlog-ignore-db directives in the master's my.cnf file then create database commands are not routed to the binlog. == Martin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
repost: select now() returns incorrect time
Sorry for the repost, I just want to make sure this didn't go unnoticed. I'm having trouble on one of my SQL servers. It is the slave in a master/slave setup. select now() returns the wrong time (+5 hours). The systems time is in EST and show variables shows timezone=EST. This has been very troublesome for me as alot of code in my website uses this function... Eg. mysql select now(); +-+ | now() | +-+ | 2003-01-14 17:41:20 | +-+ 1 row in set (0.00 sec) mysql quit Bye [root@sql2 /root]# date Tue Jan 14 12:41:32 EST 2003 [root@sql2 /root]# Can anyone help? Thanks! - Ryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 4.0 and ssl
hi all, i know some GUI mysql client software can support SSL. Then, i try to install mysql 4.0 with |--with-vio --with-openssl| options. i haven't install the openssl 0.9.6 again as i found my redhat 7.3 box already has the /usr/include/openssl and /usr/lib/libssl.a. After i followed the docs in www.mysql.com to grant some people use ssl restriction, i try to use the GUI software to connect database. Then, the screen print out the following every time when i try to use the GUI to connect database. And, finally, the connection is failed. Number of processes running now: 0 030115 20:15:50 mysqld restarted Number of processes running now: 0 030115 20:15:59 mysqld restarted If i don't use SSL, the connection is no problem and no error too. It is very strange. And, i am so comfuse that the docs in www.mysql.com does not tell you about creation of private key and public key. As i know, if you want to use SSL, you need to use the key to do encryption and decryption. But, the site never mention about this. Why? Thanks Brian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Perl DBI $sth-fetchrow_hashref() persistence?
I'm not sure how Perl works on variable assignment. If $sth is passing a reference to $record finish() may kill the variable $record holds a reference to during garbage collection. If it *assigns* the row to $record (meaning $record holds a copy of the record data and not a reference to where the data is actually stored) then you should be able to use it all you want. Maybe one of the many perl monks will know more. --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 6:22 AM To: [EMAIL PROTECTED] Subject: Perl DBI $sth-fetchrow_hashref() persistence? Hi, if I call: my $record = $sth-fetchrow_hashref(); then: $sth-finish(); How valid is it for me to keep referring to my $record hash pointer? Thanks, Jeff Snoxell Aetherweb Ltd http://www.aetherweb.co.uk [SPAM FILTER FODDER: 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: Compiling error 3.23.54a (src)
Hi, I just want to inform that I´m having the same problem in a Red Hat 8. I´ve installed this linux only with the basic packages, like gcc, cpp, glibc, ncurses (to recompile the kernel), binutils, make and glibc-kernel headers. And in one of the foruns I visited, somebody told to install gpp that will solve the problem, but I cannot find this rpm package to install in my machine. Anybody knows if this is true?? Tks. -Mensagem original- De: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Enviada em: quarta-feira, 15 de janeiro de 2003 03:33 Para: Tyler Cc: [EMAIL PROTECTED] Assunto: Re: Compiling error 3.23.54a (src) On Tue, Jan 14, 2003 at 08:58:07PM -0700, Tyler wrote: what appropriate forums? I'm not a Mandrake user, but every Linux distribution I've tried has had various e-mail and web-base forums in which you can ask questions. Furthermore, they have official support channels and ways of filing bug reports if you've found a but. Surely the Madrake documentation mentinons *some* of them. And odds are that Google could find a few more of them. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,015,781,700 queries (379/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: three table join
Let me give this a try. I've done 3 and 4 table joins, but I did a little trial and error before I got it right. To break it down, you want to get three things: 1) All meetings that fall under a particular event select * from meetings where eid=2 2) All people id's that are part of that selected meeting list select * from meetings, mpeople where mpeople.mid=meetings.id and meetings.eid=2 3) Link the people names with the people id select * from meetings, mpeople, people where mpeople.mid=meetings.id and people.id=mpeople.id and meetings.eid=2 I think that might do it. Try it out. You only need to do a left join (or right join) if you want to grab every record regardless of whether or not there are related records. Like finding out how many people are attending each meeting. You would want all meetings regardless of whether or not they have zero people attending them. Something like: select meetings.id,count(mpeople.mid) as pcount from mpeople right join meetings on mpeople.mid=meetings.id group by mpeople.mid If that doesn't work, let me know. I'm pretty sure it's close. sql,query,queries,smallint On Tuesday, January 14, 2003, at 07:37 PM, Josh L Bernardini wrote: ?How do I get the list of all the people in all the meetings given an event id? Naturally the following doesn't work in 3.23.54. select firstname, lastname from people left join mpeople on people.id = mpeople.id where mpeople.mid IN (select meetings.id from meetings where eid=2); -- 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: Avg_row_length
Steve,
mysql create table t2 (c1 varchar(255));
Query OK, 0 rows affected (0.00 sec)
mysql insert into t2 values ('s');
Query OK, 1 row affected (0.00 sec)
And this gave table status of
Name: t2
Type: MyISAM
Row_format: Dynamic
Rows: 1
Avg_row_length: 20
Well, isn't the row length supposed to be 2 (not 20)? I mean 1 byte for
the character 's', plus 1 byte to store the length.
BTW, the manual doesn't say anything about internal uses of additional
bits/bytes, AFAIK. Thanks anyway.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
Geschäftsführer / CEO iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Steve Edberg [EMAIL PROTECTED]
To: Stefan Hinz [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, January 15, 2003 2:18 PM
Subject: Re: Avg_row_length
Yep, you're right!
Perhaps an extra byte is automatically reserved, not only for a
null/not null status bit, but also for other internal use - eg; a
'row changed' bit, etc.
I did a quick test:
mysql create table t2 (c1 varchar(255));
Query OK, 0 rows affected (0.00 sec)
mysql insert into t2 values ('s');
Query OK, 1 row affected (0.00 sec)
And this gave table status of
Name: t2
Type: MyISAM
Row_format: Dynamic
Rows: 1
Avg_row_length: 20
Data_length: 20
Max_data_length: 4294967295
Index_length: 1024
Data_free: 0
Auto_increment: NULL
so obviously the avg_row_length includes extra bytes for mysql
internal info.
Learn something every day!
-steve
At 1:03 PM +0100 1/15/03, you wrote:
Steve,
I believe there is an extra byte for the 'null' flag; if you
declare
this column to be NOT NULL, I'll bet the avg length will be 10.
nice bet, but you lose g:
mysql DESCRIBE mynotnullisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col | char(10) | | | | |
+---+--+--+-+-+---+
1 row in set (0.16 sec)
mysql SHOW TABLE STATUS LIKE 'mynotnullisam';
+---+++--++
-
+
| Name | Type | Row_format | Rows | Avg_row_length |
Data_length |
+---+++--++
-
+
| mynotnullisam | MyISAM | Fixed |3 | 11 |
33 |
+---+++--++
-
+
--
+---
-+
| Steve Edberg
[EMAIL PROTECTED] |
| University of California, Davis
(530)754-9127 |
| Programming/Database/SysAdmin
http://pgfsun.ucdavis.edu/ |
+---
-+
| SETI@Home: 1001 Work units on 23 oct 2002
|
| 3.152 years CPU time, 3.142 years SETI user... and STILL no
aliens... |
+---
-+
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: InnoDB table, NOT NULL question
Ahh, but I didn't enter data that is the point. MySQL definitely knows that I omitted a field, it just happens that MySQL automatically turns the field into an empty string. In the absence of CHECK constraints perhaps the following behavior could be implemented. I would like to see MySQL throw an error if: NOT NULL is specified No DEFAULT is specified The field is omitted from an INSERT Possibly a compatibility switch in the cnf file could turn on such a check. Thanks, Gabe -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Harald Fuchs Sent: Wednesday, January 15, 2003 1:23 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB table, NOT NULL question In article [EMAIL PROTECTED], Adolfo Bello [EMAIL PROTECTED] writes: This is wierd. If you define a field as not null is because you want the user to enter some data, for example, First Name and Last Name. Yes, I know one should check it at the application level but, isn't MySQL being a little too flexible with this? You _did_ enter some data, namely a string which just happens to be the empty string (which in turn is different from no data, i.e. NULL). How should MySQL know that you don't want empty strings? Other databases offer check constraints or triggers for that; MySQL still doesn't. [Filter fodder: SQL query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with SQL statement?
Hi, I've got a varchar field in a mysql table that contains 'N' numbers (each a maximum of 4 digits, not zero padded) separated by spaces when more than 1 number is present. eg. field content might be any of the following '' '123 4567 1234 45 3' '3' '3 4 6' I want to select only those records that do not contain a certain number. Problem I have with using LIKE %MyNum% is that obviously this is going to match 3 against 123 which I don't want it to. Is there something I can use like Perl's word boundary pattern match code? My statement so far is: SELECT * FROM MyTable WHERE RefList NOT RLIKE \bMyNum\b # Where \b is a word boundary 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: filter input
If you're looking to qoute strings in perl, your DBI handle has a function called quote: $quoted_string = $dbh-quote($unqouted string) I believe you need to be connected to your database server in order to use this, and I am not POSITIVE whether this works for all RDBMS servers. Jeppe -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: 15. januar 2003 15:44 To: [EMAIL PROTECTED] Subject: re: filter input On Wednesday 15 January 2003 04:26, julian haffegee wrote: I want to insert some text into my database, but I'm worried about bad data - metacharacters etc. So I need to filter the input and remove bad characters. Which characters should I remove? What is bad for mysql? does anyone know a good subroutine (or webpage telling me all about it) for reference. I did have a good(ish) php way of doing this, but need perl this time. You should escape some chars: http://www.mysql.com/doc/en/String_syntax.html Take a look at addslashes() function in PHP or mysql_escape_string(). You can also use MySQL function QUOTE(): http://www.mysql.com/doc/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: select a range
At 14:11 +0100 1/15/03, bruno peracchio wrote: Hi I'm newbie I have to find from xvalue to yvalue; I have written: $query=SELECT * FROM inner-multipli WHERE cod_int LIKE ='%$cod_int1%' AND cod_int LIKE ='%$cod_int2'=; This not work; how can I do? Hard to say. Even if that syntax was legal, the logic doesn't make sense. What does it mean to be greater than or less than a pattern that has a match-anything wildcard at the beginning. That completely negates any possible meaning of comparative ordering, doesn't it? Thanks in advance bruper sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Secure Replication
Mostly because it can't be done (that I'm aware of). Except with stunnel (often recommended) or SSH (which I've had running for months doing this). Another option is CIPE, a method for secure tunnelling of any IP protocol using virtual network interfaces. I believe CIPE comes as standard with RedHat 7.x and above. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] (filter bait: sql, query, queries, smallint) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BDB table creation fails under NetBSD 1.6 for bad file descriptor
Description: When trying to create a type=BDB table under NetBSD 1.6, mysql reports: ERROR 1005: Can't create table './test/test.frm' (errno: 9) and the error log shows: 030114 17:10:19 bdb: fstat: Bad file descriptor 030114 17:10:19 bdb: /home/mysql/././test/test.db: Bad file descriptor How-To-Repeat: I've reproduced this using the default data directory of '/var/mysql' which was symlinked to /home/mysql and recompiling with the datadir set to /home/mysql. Run mysql (as root, with password). Input to mysql: use test; create table test(id int not null primary key) type=bdb; A trace of this shows (in relevant part): T@5: | | | | | | ha_berkeley::create T@5: | | | | | | | fn_format T@5: | | | | | | | | enter: name: ./test/test.frm dsk: form: .db flag: 6 T@5: | | | | | | | | dirname_part T@5: | | | | | | | | | enter: './test/test.frm' T@5: | | | | | | | | dirname_part T@5: | | | | | | | | unpack_dirname T@5: | | | | | | | | | dirname_part T@5: | | | | | | | | | | enter: './test/' T@5: | | | | | | | | | dirname_part T@5: | | | | | | | | | cleanup_dirname T@5: | | | | | | | | | | enter: from: './test/' T@5: | | | | | | | | | | exit: to: './test/' T@5: | | | | | | | | | cleanup_dirname T@5: | | | | | | | | unpack_dirname T@5: | | | | | | | fn_format T@5: | | | | | | | create_sub_table T@5: | | | | | | | | enter: sub_name: main T@5: | | | | | | | create_sub_table T@5: | | | | | | | create_sub_table T@5: | | | | | | | | enter: sub_name: key01 T@5: | | | | | | | | sql_print_error T@5: | | | | | | | | | error: bdb: fstat: Bad file descriptor T@5: | | | | | | | | sql_print_error T@5: | | | | | | | | sql_print_error T@5: | | | | | | | | | error: bdb: /var/mysql/././test/test.db: Bad file descriptor T@5: | | | | | | | | sql_print_error T@5: | | | | | | | | error: Got error: 9 when opening table './test/test.db' T@5: | | | | | | | create_sub_table T@5: | | | | | | ha_berkeley::create T@5: | | | | | | closefrm Creating a myisam type table with the same parameters works. I'm more than willing to assist in tracking/fixing this problem. I need a transactional based table, and innodb won't even compile, due to configuration problems where the include directory for mit-pthreads is missed. Fix: Don't use BDB tables. No work around known. Submitter-Id: Resun2003001 Originator:Don Phillips Organization: MySQL support: none Synopsis: BDB table creation fails under NetBSD 1.6. Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.49 (Source distribution) Environment: System: NetBSD sqlhost01.resun.com 1.6 NetBSD 1.6 (resun) #2: Fri Oct 4 18:06:47 UTC 2002 root@:/usr/src/sys/arch/i386/compile/resun i386 Some paths: /usr/pkg/bin/perl /usr/bin/make /usr/pkg/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) (NetBSD nb3) Compilation info: CC='cc' CFLAGS='-I/usr/pkg/include -O2' CXX='c++' CXXFLAGS='-I/usr/pkg/include -I/usr/pkg/include -O2' LDFLAGS='-L/usr/pkg/lib -Wl,-R/usr/pkg/lib/mysql -Wl,-R/usr/pkg/lib' LIBC: -r--r--r-- 1 root wheel 1345334 Sep 8 11:49 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 13 Sep 8 11:49 /usr/lib/libc.so - libc.so.12.83 lrwxr-xr-x 1 root wheel 13 Sep 8 11:49 /usr/lib/libc.so.12 - libc.so.12.83 -r--r--r-- 1 root wheel 700411 Sep 8 11:49 /usr/lib/libc.so.12.83 Configure command: ./configure --localstatedir=/var/mysql --with-named-z-libs=z --without-libwrap '--with-named-curses-libs=-lcurses -ltermcap' --without-readline --without-docs --without-debug --without-bench --with-low-memory --without-server --host=i386--netbsdelf --prefix=/usr/pkg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: three table join
Nice that this came up when it did.. I'm currently struggling with a three-table join. Table1.PK = Table2.FK1 Table3.PK = Table2.FK2 My last effort looks something like: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 I want it to return 1 row, but it's returning as many rows as there are in Table3. Where am I goofing? TIA, Tab mysql -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:30 AM To: Josh L Bernardini Cc: [EMAIL PROTECTED] Subject: Re: three table join I've wanted to post this query example a few times (and I hope I got it right; mornings aren't my best time) ... multiple JOINs: SELECT stuff FROM table1 LEFT JOIN table2 ON table1.fk = table2.pk LEFT JOIN table3 ON table2.fk = table3.pk WHERE other_conditions ... You can repeat that as many levels as you want (performance depends on indexing and the optimizer). You need to think in terms of what would be equal to what between tables in the correct result row. So if you would do a secondary sub-select of SELECT fk from table2 where ... then you end up with a left join like above. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Password problem
Hello: As the root user, I created a new database and user account: CREATE DATABASE newsletter; GRANT ALL PRIVILEGES ON newsletter.* TO newsletter IDENTIFIED BY 'testPass'; FLUSH PRIVILEGES; I then tried to access the databse via the command-line client: /usr/local/mysql/bin/mysql -u newsletter -ptestPass newsletter And I get this error: ERROR 1045: Access denied for user: 'newsletter@localhost' (Using password: YES) I checked that mysql has it set-up correctly in the user and db tables by logging in as the root account. select * from user give me this line: | % | newsletter | 61fa73f50740c213 | N | N | N | N | N | N | N | N | N| N | N | N | N | N select * from db gives me this line: | % | newsletter | newsletter | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Any ideas? Thanks, Neil. -- Neil Aggarwal JAMM Consulting, Inc.(972) 612-6056, http://www.JAMMConsulting.com Custom Internet DevelopmentWebsites, Ecommerce, Java, databases - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
To BLOB or not to BLOB
Is it wise to always assume that a file content is always binary and hence store it in a MySQL BLOB type, even if the file may be only a text file? Your thoughts on this would be appreciated. sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NOT NULL question
Hello. On Tue 2003-01-14 at 09:32:02 -0800, [EMAIL PROTECTED] wrote: I'm aware that NULL and are not the same thing.. I would like to prevent the column from accepting values automatically ( with out the presence of a DEFAULT). [...] The problem is: You have a DEFAULT, you just don't know it. ;-) Do a SHOW CREATE TABLE stuff, and you'll see what I mean. That is a known deficiency, as Eric told you: It's a known behaviour and is described in the MySQL manual: http://www.mysql.com/doc/en/Bugs.html You can change it if you compile MySQL server with -DDONT_USE_DEFAULT_FIELDS option. But in this case you can't use default values at all. http://www.mysql.com/doc/en/configure_options.html describes -DDONT_USE_DEFAULT_FIELDS. 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
Confirmation of SELECT...IN syntax
It's my understanding that embedded selects are NOT allowed in MySql. Is this correct? Example: SELECT t1.* FROM table1 t1 WHERE ( t1.Id IN ( SELECT t2.Id FROM table2 t2 ) ) Doug Beyer SAS Research and Development 503-617-7103 (Portland) [EMAIL PROTECTED] SAS... The Power to Know - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
error report using mysql 3.23.49 max
Dear Sirs. We are programers in Brasil and we are currently conducting tests with MySQL version 3.23.49 Max working with Dephi 6.0. For testing purposes we developed a very simple program that inserts 500 records into a table. The folowing teste has been conducted: With the test program two stations try to insert records simultaneously and we got several erros such as error 22 from table handler, we lost records and we also got table corruption in some situations.Reviewing tests we found tha the test aplication did not return the insert erros giving the impression that all records have been inserted which was not true. Running exactly the same test program replacing MySQL by INTERBASE (Delphi) we had no records loss. We are at your disposal to supply any details or aditional information tha you might need to clarify this reported problem. Thanks for attention Cilly - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Secure Replication
On Wed, Jan 15, 2003 at 09:18:50AM -0500, Michael T. Babcock wrote: Jeremy Zawodny wrote: there seem to be a lack of documentation on this part. Mostly because it can't be done (that I'm aware of). Except with stunnel (often recommended) or SSH (which I've had running for months doing this). PS, as with the MySQL daemon, I run my ssh tunnel under 'supervise' so as to make sure its always there and restarts if it gets killed / dies / is stupid / upgraded. Right. I should have said there's no built-in way it can be done because folks have doing secure replication via ssh/stunnel for years now. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,039,952,222 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
error report using mysql 3.23.49 max
Dear Sirs. We are programers in Brasil and we are currently conducting tests with MySQL version 3.23.49 Max working with Dephi 6.0. For testing purposes we developed a very simple program that inserts 500 records into a table. The folowing teste has been conducted: With the test program two stations try to insert records simultaneously and we got several erros such as error 22 from table handler, we lost records and we also got table corruption in some situations.Reviewing tests we found tha the test aplication did not return the insert erros giving the impression that all records have been inserted which was not true. Running exactly the same test program replacing MySQL by INTERBASE (Delphi) we had no records loss. We are at your disposal to supply any details or aditional information tha you might need to clarify this reported problem. Thanks for attention Cilly - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Optimizing Ext3 for MySQL
On Tue, Jan 14, 2003 at 11:33:54PM -0800, Jeff Kilbride wrote: Are there any general guidelines for optimizing ext3 for MySQL? I have a perl script that runs 200K + updates into my database once a day and I see pretty wildly fluctuating query/sec numbers using Jeremy Z's mytop program. I've seen in excess of 2000 qps and then seen that number drop to 40 qps. The average seems to be about 200 qps, which seems kinda slow given my hardware: Dual P3 1.3MHz 1GB RAM Dual SCSI drives (160 MB/s) RedHat 7.3 MySQL 3.23.53a w/MyISAM tables This particular table being updated has about 5 million rows. The fields being updated are not indexed. By updated you mean inserts? Or are you doing a table scan for each update (since they're not indexed)? Also, the qps numbers seem to slow down every 5 seconds or so, which I think matches the default write timing for the ext3 journal. Can anybody share their experience with optimizing ext3 -- i.e. which mode is best (writeback, ordered, journal) and any parameters that can be tuned? Any insight is appreciated. Yeah, 5 seconds is the ext3 default. You can tune it. I recently saw someone suggest this: # set disk flush to 30,000 clicks or 5 minutes echo 30 64 64 256 3 3000 60 0 0 /proc/sys/vm/bdflush But have not tried it myself. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,040,147,810 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with SQL statement?
Jeff Snoxell wrote: Hi, I've got a varchar field in a mysql table that contains 'N' numbers (each a maximum of 4 digits, not zero padded) separated by spaces when more than 1 number is present. eg. field content might be any of the following '' '123 4567 1234 45 3' '3' '3 4 6' I want to select only those records that do not contain a certain number. Problem I have with using LIKE %MyNum% is that obviously this is going to match 3 against 123 which I don't want it to. Is there something I can use like Perl's word boundary pattern match code? I assume you know Perl, then. You can use ... WHERE field REGEXP regexp ... which is perl-style regexp. It is very useful. - Cs. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with SQL statement?
At 16:40 + 1/15/03, Jeff Snoxell wrote: Hi, I've got a varchar field in a mysql table that contains 'N' numbers (each a maximum of 4 digits, not zero padded) separated by spaces when more than 1 number is present. eg. field content might be any of the following '' '123 4567 1234 45 3' '3' '3 4 6' I want to select only those records that do not contain a certain number. Problem I have with using LIKE %MyNum% is that obviously this is going to match 3 against 123 which I don't want it to. Is there something I can use like Perl's word boundary pattern match code? My statement so far is: SELECT * FROM MyTable WHERE RefList NOT RLIKE \bMyNum\b # Where \b is a word boundary Yes, you can use REGEXP and the ugly [[::]] and [[::]] markers: ... WHERE RefList NOT REGEXP '[[::]]MyNum[[::]]' ... 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Compiling error 3.23.54a (src)
On Wed, Jan 15, 2003 at 07:13:05AM -0700, Tyler wrote: Oh.. I see. I dont know about Linux, all I knew was that I wanted it so I burned the necissary CD's and installed linux on my other machine. No offense, but in that case you're probably in for a rough ride. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,040,345,008 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Secure Replication
On Wed, Jan 15, 2003 at 04:49:03PM -, Basil Hussain wrote: Mostly because it can't be done (that I'm aware of). Except with stunnel (often recommended) or SSH (which I've had running for months doing this). Another option is CIPE, a method for secure tunnelling of any IP protocol using virtual network interfaces. I believe CIPE comes as standard with RedHat 7.x and above. Right. And if we're shooting for an exhaustive list, some routers have proprietary encryption/tunneling systems. And you could use a more off-the-shelf IPSec or similar VPN scheme. It may be overkill, but they are options. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,040,383,036 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: MS SQL vs MySQL
Hi. As Jeremy said, most info can be found by reading the archives or the manual. On Tue 2003-01-14 at 11:41:16 +0200, [EMAIL PROTECTED] wrote: I've asked on another list which database they recommend among MySQL and MS SQL, and ... possibly PostgreSQL. Most of that list members answered me that they recommend MS SQL because it has much more features. The simple question is: do you need the features? If so, you should probably go with MS SQL or Oracle. If not, why should you (waste your money)? Can you tell me which are the most important differences between MS SQL and MySQL? I am interested in the differences in the following areas: - the speed All benchmark are lies (they show what they are supposed to measure, not what your requirements are). That said, you may want to have a look at e.g. http://www.mysql.com/information/benchmarks.html http://www.eweek.com/article2/0,3959,293,00.asp and MySQL AB's summary of the latter: http://www.mysql.com/eweek/index.html - the max size of a database, the max size of a table, etc. Database and table size are almost unlimited. (8 Million TB for tables), but practically limited by the underlying OS: http://www.mysql.com/doc/en/Table_size.html - the things that can be done in MySQL but can't be done in MS SQL I don't know about the MS SQL side, but here is some of the MySQL side: http://www.mysql.com/doc/en/Compatibility.html http://www.mysql.com/doc/en/Extensions_to_ANSI.html (particularly) Additional MySQL features are: - you have the source and can tweak behaviour, if you want or need - outstanding support on this list and by MySQL AB - quick turn-around time: although no guarantee, experience shows, if you happen to find a critical bug, chances are high that you have a patch the next day. - The things that can be done in MS SQL but not in MySQL Again the MySQL side of things: http://www.mysql.com/doc/en/Differences_from_ANSI.html - How easy is to access a database from Perl I don't know about MS SQL, but I presume it can be accessed via DBI the same way as MySQL, so the answer is probably: it is the same for both. There are quite some interesting, more insightful posts in the list archive about that, but I am a bit too lazy to dig them up. 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
how do i retrieve distinct rows using IN
hello,
I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.
table sys_bld_foo:
component: varchar 16
catNum: varchar 16
price: decimal (10,2)
i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB',
'partB');
my return value ignores the second partB - and my price is short.
when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 'partB');
my return value doubles up on partA and partB - and my price is too much.
here is a sample database to see where the problem lies:
component | item | price
-
foo| item1 | 200.00
foo | item2 | 300.00
primary bar | partA | 75.00
primary bar | partB | 150.50
second bar | partA| 75.00
second bar | partB | 150.50
second bar | partC| 160.00
currently i am using perl to remove the second partB, create a hash $catNum{partB} =
2, and then for each item returned, check the hash and multiple the price by it's
results. This seems extremely slow if my problem can be solved in sql alone.
can anyone help?
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: three table join (9 table join example inside)
Thanks to everyone who answered my question. With some further reading in the searchable archives at http://www.listsearch.com/mysql.lasso, I managed to solve the problem on my own last night and grow up to a 9 table join. Tab, I think you just want two left joins instead of a left join and a right join. SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 LEFT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 Now for those who understand a little better, what's the difference/when do you neeed a right join, straight join, etc? Any examples would be helpful. thanks, jb PS - heres a 9 table left join. I guess you can join any number of tables so long as there is a relationship between them. select people.id, concat(lastname, ', ', firstname) as name, title, organizations.org, ptype, groupname, meeting, room, location, moderator, addnotes, dtime, edtime from people left join organizations on people.org=organizations.id left join otype on organizations.otype=otype.id left join pgroups on people.id=pgroups.pid left join groups on pgroups.gid=groups.id left join mgroups on pgroups.gid=mgroups.gid left join meetings on mgroups.mid=meetings.id left join rooms on meetings.rid=rooms.id left join locations on rooms.lid=locations.id left join events on meetings.eid=events.id where events.id=2 order by meeting \G |-+ | | Tab Alleman| | | Tab.Alleman@Metr| | | oGuide.com | | || | | 01/15/2003 08:53 | | | AM | | || |-+ --| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: RE: three table join | --| Nice that this came up when it did.. I'm currently struggling with a three-table join. Table1.PK = Table2.FK1 Table3.PK = Table2.FK2 My last effort looks something like: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 I want it to return 1 row, but it's returning as many rows as there are in Table3. Where am I goofing? TIA, Tab mysql -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:30 AM To: Josh L Bernardini Cc: [EMAIL PROTECTED] Subject: Re: three table join I've wanted to post this query example a few times (and I hope I got it right; mornings aren't my best time) ... multiple JOINs: SELECT stuff FROM table1 LEFT JOIN table2 ON table1.fk = table2.pk LEFT JOIN table3 ON table2.fk = table3.pk WHERE other_conditions ... You can repeat that as many levels as you want (performance depends on indexing and the optimizer). You need to think in terms of what would be equal to what between tables in the correct result row. So if you would do a secondary sub-select of SELECT fk from table2 where ... then you end up with a left join like above. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Optimizing Ext3 for MySQL
- Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Jeff Kilbride [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Wednesday, January 15, 2003 10:41 AM Subject: Re: Optimizing Ext3 for MySQL On Tue, Jan 14, 2003 at 11:33:54PM -0800, Jeff Kilbride wrote: Are there any general guidelines for optimizing ext3 for MySQL? I have a perl script that runs 200K + updates into my database once a day and I see pretty wildly fluctuating query/sec numbers using Jeremy Z's mytop program. I've seen in excess of 2000 qps and then seen that number drop to 40 qps. The average seems to be about 200 qps, which seems kinda slow given my hardware: Dual P3 1.3MHz 1GB RAM Dual SCSI drives (160 MB/s) RedHat 7.3 MySQL 3.23.53a w/MyISAM tables This particular table being updated has about 5 million rows. The fields being updated are not indexed. By updated you mean inserts? Or are you doing a table scan for each update (since they're not indexed)? No, they're actual updates (UPDATE table SET field=value WHERE primary_key=value2). The field being updated is not an index, so I'm assuming the index files aren't changing -- which, in general, should be faster than a case where an index is changed. (right?) However, I'm using the primary key in the WHERE clause to find the correct record to update -- so it's not a table scan for each record. Also, the qps numbers seem to slow down every 5 seconds or so, which I think matches the default write timing for the ext3 journal. Can anybody share their experience with optimizing ext3 -- i.e. which mode is best (writeback, ordered, journal) and any parameters that can be tuned? Any insight is appreciated. Yeah, 5 seconds is the ext3 default. You can tune it. I recently saw someone suggest this: # set disk flush to 30,000 clicks or 5 minutes echo 30 64 64 256 3 3000 60 0 0 /proc/sys/vm/bdflush But have not tried it myself. What's a click? I've seen some other suggestions for bdflush, also -- but I've seen other articles that say the defaults are pretty good and playing with these numbers could cause more harm than good... I haven't come across anything definitive that deals with tuning ext3. I'm tempted to try mounting the DB drive as ext2, to see what difference it makes. Apache/PHP is also running on this box and accessing the database, but the load is 0.00 until I run the update script -- then the load jumps to anywhere between 2 and 5. If I switch modes with mytop, I see something like this: 233 245 218 158 2 120 250 235 195 4 etc... Which makes me think the slowdown has something to do with the journal writes. I've seen numbers as high as 2000 in mytop consistently over 3 or 4 seconds, and more than once while the script runs, but I don't know why I'm getting these huge bursts of speed intermittently. This isn't a huge problem, I'm just puzzled that I can get such high numbers when my average seems 10 or 20 times less. Is this normal? Is there any way to optimize large numbers of UPDATES with MySQL, like you can INSERTS? Thanks, --jeff Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,040,147,810 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: three table join
Try adding a T2.PK IS NOT NULL or T2.FK1 IS NOT NULL: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 AND T2.FK1 IS NOT NULL Example with old tables i have: mysql select * from t1; +++ | id | v | +++ | 1 | 23 | | 2 | 18 | | 3 | 6 | +++ 3 rows in set (0.00 sec) mysql select * from t2; ++---+---++ | id | id_t1 | id_t3 | v | ++---+---++ | 1 | 0 | 0 | 2 | | 2 | 1 | 1 | 89 | | 3 | 2 | 3 | 8 | ++---+---++ 3 rows in set (0.00 sec) mysql select * from t3; ++++ | id | v1 | v2 | ++++ | 1 | 8 | 6 | | 2 | 28 | 12 | | 3 | 56 | 23 | | 4 | 2 | 34 | ++++ 4 rows in set (0.00 sec) mysql select * from t1 as T1 LEFT JOIN t2 AS T2 ON (T1.id = T2.id_t1) RIGHT JOIN t3 AS T3 ON (T2.id_t3 = T3.id) WHERE T1.id=2 AND T2.id IS NOT NULL; +++--+---+---+--++++ | id | v | id | id_t1 | id_t3 | v| id | v1 | v2 | +++--+---+---+--++++ | 2 | 18 |3 | 2 | 3 |8 | 3 | 56 | 23 | +++--+---+---+--++++ 1 row in set (0.00 sec) On Wed, 2003-01-15 at 16:53, Tab Alleman wrote: Nice that this came up when it did.. I'm currently struggling with a three-table join. Table1.PK = Table2.FK1 Table3.PK = Table2.FK2 My last effort looks something like: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 I want it to return 1 row, but it's returning as many rows as there are in Table3. Where am I goofing? TIA, Tab mysql -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:30 AM To: Josh L Bernardini Cc: [EMAIL PROTECTED] Subject: Re: three table join I've wanted to post this query example a few times (and I hope I got it right; mornings aren't my best time) ... multiple JOINs: SELECT stuff FROM table1 LEFT JOIN table2 ON table1.fk = table2.pk LEFT JOIN table3 ON table2.fk = table3.pk WHERE other_conditions ... You can repeat that as many levels as you want (performance depends on indexing and the optimizer). You need to think in terms of what would be equal to what between tables in the correct result row. So if you would do a secondary sub-select of SELECT fk from table2 where ... then you end up with a left join like above. -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 FreeBSD with a NT linked database
In the last episode (Jan 15), Nicolas said: On Tue, Jan 14, 2003 at 05:49:24PM +0100, Nicolas wrote: I'm running Freebsd 4.6.2 with mysql server v3.23.49 I have a little question about using Mysql on Freebsd and having a database on a NT 4 linked drive, using Samba (the drive was mounted on my FreeBSD system with Sharity-light because smbmount doesn't work on Freebsd) . I can query my database on the linked drive, but I cannot insert or update anything. I've always the read only error message when I try to insert a row .. Have you verified that the drive is mounted read/write? Hi Jeremy, yes the drive is mounted read-write. I've full acces on it and the folder's attribut is archive only. I wonder is this is a Sharity-light file locking problem. That's possible. Try starting mysql with --skip-locking and see if that helps. -- Dan Nelson [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: three table join (RE brent baisley's post) BETTER join syntax?
same results and both varying between .01, .02 seconds to execute. 6 of one half dozen of another or is there an advantage to one? My guess is that the second syntax is preferred given the reduced row count for events in it's explain table. The first statement uses left joins, the second use's Brent suggestion. Also, is the second statement what they call a straight join? thanks, jb mysql explain select people.id, concat(lastname, ', ', firstname) as name, titl e, - organizations.org, ptype, groupname, meeting, room, location, - moderator,addnotes, dtime, edtime - from people - left join organizations on people.org=organizations.id - left join otype on organizations.otype=otype.id - left join pgroups on people.id=pgroups.pid - left join groups on pgroups.gid=groups.id - left join mgroups on pgroups.gid=mgroups.gid - left join meetings on mgroups.mid=meetings.id - left join rooms on meetings.rid=rooms.id - left join locations on rooms.lid=locations.id - left join events on meetings.eid=events.id - where events.id=2 order by type; +---++---+-+-+-- ---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-- ---+--+-+ | people| ALL| NULL | NULL|NULL | NULL | 17 | Using temporary; Using filesort | | organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org |1 | | | otype | eq_ref | PRIMARY | PRIMARY | 2 | organizations.oty pe |1 | | | pgroups | ALL| NULL | NULL|NULL | NULL | 14 | | | groups| eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid |1 | | | mgroups | ALL| NULL | NULL|NULL | NULL |6 | | | meetings | eq_ref | PRIMARY | PRIMARY | 2 | mgroups.mid |1 | | | rooms | eq_ref | PRIMARY | PRIMARY | 2 | meetings.rid |1 | | | locations | eq_ref | PRIMARY | PRIMARY | 2 | rooms.lid |1 | | | events| eq_ref | PRIMARY | PRIMARY | 2 | meetings.eid |1 | where used; Using index | +---++---+-+-+-- ---+--+-+ 10 rows in set (0.00 sec) === === mysql explain select people.id, concat(lastname, ', ', firstname) as name, titl e, - organizations.org, ptype, groupname, meeting, room, location, - moderator, addnotes, dtime, edtime - from people, organizations, otype, pgroups, groups, mgroups, meeting s, rooms, locations, events - where people.org=organizations.id - and organizations.otype=otype.id - and people.id=pgroups.pid - and pgroups.gid=groups.id - and pgroups.gid=mgroups.gid - and mgroups.mid=meetings.id - and meetings.rid=rooms.id - and rooms.lid=locations.id - and meetings.eid=events.id - and events.id=2 order by type; +---++---+-+-+-- ---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-- ---+--+-+ | events| const | PRIMARY | PRIMARY | 2 | const |1 | Using temporary; Using filesort | | pgroups | ALL| NULL | NULL|NULL | NULL | 14 | | | mgroups | ALL| NULL | NULL|NULL | NULL |6 | where used | | people| eq_ref | PRIMARY | PRIMARY | 2 | pgroups.pid |1 | | | groups| eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid |1 | | | organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org |1 | | | otype | eq_ref | PRIMARY | PRIMARY | 2 | organizations.oty pe |1 |
Re: Confirmation of SELECT...IN syntax
In the last episode (Jan 15), Doug Beyer said: It's my understanding that embedded selects are NOT allowed in MySql. Is this correct? Example: SELECT t1.* FROM table1 t1 WHERE ( t1.Id IN ( SELECT t2.Id FROM table2 t2 ) ) Correct. Your example can easily be converted to a join, though. SELECT t1.* from table1 t1, table2 t2 where t1.id=t2.id -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help w/join condition
Do you mean that you want all units to appear, even if they have items not defined (like having 4 defined and the other 5 with no value) ? If is that, you may try using LEFT JOIN, instead of INNER JOIN. Columns which are not defined will appear NULL (I did not test this): SELECT u.name, item1.name AS item1_name,item2.name AS item2_name, item3.name AS item3_name,item4.name AS item4_name, item5.name AS item5_name,item6.name AS item6_name,item7.name AS item7_name, item8.name AS item8_name,item9.name AS item9_name,item1.item_id as item1_id, item2.item_id as item2_id, item3.item_id as item3_id,item4.item_id as item4_id, item5.item_id as item5_id,item6.item_id as item6_id, item7.item_id as item7_id,item8.item_id as item8_id,item9.item_id as item9_id FROM units as u LEFT JOIN items as item1 ON (u.item1_id = item1.item_id) LEFT JOIN items as item2 ON (u.item2_id = item2.item_id) LEFT JOIN items as item9 ON (u.item9_id = item9.item_id) ORDER BY unit_id DESC limit 1; On Wed, 2003-01-15 at 02:10, mike wrote: Hello, Is it possible to have a join condition where the condition is not always me? Here is my query: SELECT u.name, item1.name AS item1_name,item2.name AS item2_name, item3.name AS item3_name,item4.name AS item4_name, item5.name AS item5_name,item6.name AS item6_name,item7.name AS item7_name, item8.name AS item8_name,item9.name AS item9_name,item1.item_id as item1_id, item2.item_id as item2_id, item3.item_id as item3_id,item4.item_id as item4_id, item5.item_id as item5_id,item6.item_id as item6_id, item7.item_id as item7_id,item8.item_id as item8_id,item9.item_id as item9_id FROM items AS item1,items AS item2,items AS item3,items AS item4,items AS item5, items AS item6,items AS item7,items AS item8,items AS item9 INNER JOIN units as u ON u.item1_id=item1.item_id AND u.item2_id=item2.item_id AND u.item3_id=item3.item_id AND u.item4_id=item4.item_id AND u.item5_id=item5.item_id AND u.item6_id=item6.item_id AND u.item7_id=item7.item_id AND u.item8_id=item8.item_id AND u.item9_id=item9.item_id ORDER BY unit_id DESC limit 1; This works great as long as each unit record has a item_id in the units_item_id field. My probelms is not all units will have 9 items. Most units will have less than 9 items. Any help would be greatly appreciated, Thanks in advance, Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Password problem
On Wed, 15 Jan 2003 11:00:21 -0600, Neil Aggarwal wrote: Hello: As the root user, I created a new database and user account: CREATE DATABASE newsletter; GRANT ALL PRIVILEGES ON newsletter.* TO newsletter IDENTIFIED BY 'testPass'; FLUSH PRIVILEGES; I then tried to access the databse via the command-line client: /usr/local/mysql/bin/mysql -u newsletter -ptestPass newsletter And I get this error: ERROR 1045: Access denied for user: 'newsletter@localhost' (Using password: YES) I checked that mysql has it set-up correctly in the user and db tables by logging in as the root account. select * from user give me this line: | % | newsletter | 61fa73f50740c213 | N | N | N | N | N | N | N | N | N| N | N | N | N | N select * from db gives me this line: | % | newsletter | newsletter | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Any ideas? Yes. See Chapter 4.3.5 of the mysql manual. Note that we must issue GRANT statements for both monty@localhost and monty@%. hth, Doug Thanks, Neil. -- Neil Aggarwal JAMM Consulting, Inc.(972) 612-6056, http://www.JAMMConsulting.com Custom Internet DevelopmentWebsites, Ecommerce, Java, databases - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 results of a query?
How do you query the table that is the results of a query? Must you ask MySQL to... ...create a temporary table form the results of the first query ...then query that temporary table ...then delete the temp table when you are done? (and if so how do you ask MySQL to create a temp table from the results of a query?) Is there a better and faster way to do this with minimum burden on the web server with the db on it? Thanks for any help. Will - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Confirmation of SELECT...IN syntax
you are correct - subselects are NOT allowed until version 4 until then: SELECT t1.* FROM table1 t1 LEFT JOIN t2 ON t1.id = t2.id OR SELECT t1.* FROM table1 t1 LEFT JOIN t2 USING (id) -Original Message- From: Doug Beyer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 10:55 AM It's my understanding that embedded selects are NOT allowed in MySql. Is this correct? Example: SELECT t1.* FROM table1 t1 WHERE ( t1.Id IN ( SELECT t2.Id FROM table2 t2 ) ) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Password problem
Neil, GRANT ALL PRIVILEGES ON newsletter.* TO newsletter IDENTIFIED BY 'testPass'; FLUSH PRIVILEGES; I then tried to access the databse via the command-line client: /usr/local/mysql/bin/mysql -u newsletter -ptestPass newsletter And I get this error: ERROR 1045: Access denied for user: 'newsletter@localhost' (Using password: YES) I don't know if you have any other entries in the mysql.user / mysql.db tables. If so, there could be another entry for 'newsletter'@'something_else_but_not_%'. Whatever the reason - it's always a good idea to specify user names the classical MySQL way, i. e. 'user'@'machine', and not - as in most other DBMS - only as 'user'. What the manual says about it, is: The simple form user is a synonym for user@%. Details: http://www.mysql.com/doc/en/GRANT.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Neil Aggarwal [EMAIL PROTECTED] To: Mysql list [EMAIL PROTECTED] Sent: Wednesday, January 15, 2003 6:00 PM Subject: Password problem Hello: As the root user, I created a new database and user account: CREATE DATABASE newsletter; GRANT ALL PRIVILEGES ON newsletter.* TO newsletter IDENTIFIED BY 'testPass'; FLUSH PRIVILEGES; I then tried to access the databse via the command-line client: /usr/local/mysql/bin/mysql -u newsletter -ptestPass newsletter And I get this error: ERROR 1045: Access denied for user: 'newsletter@localhost' (Using password: YES) I checked that mysql has it set-up correctly in the user and db tables by logging in as the root account. select * from user give me this line: | % | newsletter | 61fa73f50740c213 | N | N | N | N | N | N | N | N | N| N | N | N | N | N select * from db gives me this line: | % | newsletter | newsletter | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Any ideas? Thanks, Neil. -- Neil Aggarwal JAMM Consulting, Inc.(972) 612-6056, http://www.JAMMConsulting.com Custom Internet DevelopmentWebsites, Ecommerce, Java, databases - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: error report using mysql 3.23.49 max
Hi, A very evasive description. - Original Message - From: PC Systems [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 15, 2003 8:03 PM Subject: error report using mysql 3.23.49 max Dear Sirs. We are programers in Brasil and we are currently conducting tests with MySQL version 3.23.49 Max working with Dephi 6.0. For testing purposes we What you using to accessing MySQL? MySQL C API,MyODBC,ZEOS...@? developed a very simple program that inserts 500 records into a table. The What is table schema ? folowing teste has been conducted: With the test program two stations try to insert records simultaneously and we got several erros such as error 22 from table handler, we lost records and we also got table corruption in some situations.Reviewing tests we found tha the test aplication did not return In what situations ? the insert erros giving the impression that all records have been inserted which was not true. Very strange.It's for the first time when i heard about that.Is very easy to blame a system if we don't real known almost all about it. Running exactly the same test program replacing MySQL by INTERBASE (Delphi) we had no records loss. We are at your disposal to supply I understand you.And VB programers work with out any problem with MS SQL any details or aditional information tha you might need to clarify this reported problem. We waiting some more details. Thanks for attention You are welcome Cilly Best regards, Gelu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: select a range MySQL
Il mer, 2003-01-15 alle 17:48, Paul DuBois ha scritto: Hard to say. Even if that syntax was legal, the logic doesn't make sense. What does it mean to be greater than or less than a pattern that has a match-anything wildcard at the beginning. That completely negates any possible meaning of comparative ordering, doesn't it? Dear Paul you are right; well, my problem in MySQL is -selecting a code (var 9) range from 10? to 20? with variables in html form- Thanks bruno - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump file fails to recreate database
I've got a large test database and a recent backup made with mysqldump. I've been deleting records from it and would like to get back where I started. The easiest way is to drop database, create database, and then input from the mysqldump backup file. To automate it and make it quick I do it like this: mysqladmin -u rradmin --password=xx drop rrtest2 mysqladmin -u rradmin --password=xx create rrtest2 mysql -u rradmin --password=xx --database=rrtest2 DATA.sql Reloading the DB from DATA.sql gives me this error message: ERROR 1064 at line 679: You have an error in your SQL syntax near 'field(field), KEY type(type), KEY tsrc(tsrc) ) TYPE=MyISAM' at line 10 The whole table definition in question looks like this: CREATE TABLE fnames ( field varchar(40) NOT NULL default '', type varchar(10) NOT NULL default '', fieldorder int(4) default '0', longdesc varchar(200) default NULL, required int(1) default '0', tsrc varchar(40) NOT NULL default '', visible int(1) default '0', misc blob, KEY field(field), # = here's the problem KEY type(type), KEY tsrc(tsrc) ) TYPE=MyISAM; It's the first KEY definition it doesn't like. If I comment out KEY field(field), it works, except of course then the field called 'field' is not set as an index. The same exact problem occurs in two places in the mysqldump file. With them commented out the DB reloads. Perhaps some later version of mysql since this database was originally created came declares that one shouldn't have a field called 'field', or in which a field called 'field' shouldn't be an index (key)? Just a theory. Surely someone on this esteemed list of experts knows. -- Lynn David Newton Phoenix, AZ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Password problem
GRANT ALL PRIVILEGES ON newsletter.* TO 'newsletter'@'localhost' IDENTIFIED BY 'testPass'; Neil Aggarwal wrote: Hello: As the root user, I created a new database and user account: CREATE DATABASE newsletter; GRANT ALL PRIVILEGES ON newsletter.* TO newsletter IDENTIFIED BY 'testPass'; FLUSH PRIVILEGES; I then tried to access the databse via the command-line client: /usr/local/mysql/bin/mysql -u newsletter -ptestPass newsletter And I get this error: ERROR 1045: Access denied for user: 'newsletter@localhost' (Using password: YES) I checked that mysql has it set-up correctly in the user and db tables by logging in as the root account. select * from user give me this line: | % | newsletter | 61fa73f50740c213 | N | N | N | N | N | N | N | N | N| N | N | N | N | N select * from db gives me this line: | % | newsletter | newsletter | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Any ideas? Thanks, Neil. -- Neil Aggarwal JAMM Consulting, Inc.(972) 612-6056, http://www.JAMMConsulting.com Custom Internet DevelopmentWebsites, Ecommerce, Java, databases - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: NOT NULL question
Thanks, I check it out.. Any idea if this 'deficiency' is scheduled to change? Thanks, Gabe -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 9:49 AM To: Gabe Geisendorfer Cc: [EMAIL PROTECTED] Subject: Re: NOT NULL question Hello. On Tue 2003-01-14 at 09:32:02 -0800, [EMAIL PROTECTED] wrote: I'm aware that NULL and are not the same thing.. I would like to prevent the column from accepting values automatically ( with out the presence of a DEFAULT). [...] The problem is: You have a DEFAULT, you just don't know it. ;-) Do a SHOW CREATE TABLE stuff, and you'll see what I mean. That is a known deficiency, as Eric told you: It's a known behaviour and is described in the MySQL manual: http://www.mysql.com/doc/en/Bugs.html You can change it if you compile MySQL server with -DDONT_USE_DEFAULT_FIELDS option. But in this case you can't use default values at all. http://www.mysql.com/doc/en/configure_options.html describes -DDONT_USE_DEFAULT_FIELDS. 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Uninstall MySQL?
On Wed, Jan 15, 2003 at 07:18:46AM -0700, Tyler wrote: Can someone please guide me in uninstalling all the failed installs of MySQL I've got? I've installed like 2 binaries, and 1 source.. none work :( Dear Tyler, You will need to be more specific. What operating system did you do the installs on, where did you try to install, etc. Generally, (under Unix-like OSs) it is just a matter of removing the installations directories /usr/local/mysql- and fixing your startup scripts (if you modified them). -- Zak Greant [EMAIL PROTECTED] | MySQL Advocate | http://zak.fooassociates.com MySQL Tip: Allow duplicate values in columns, but force rows to be unique mysql CREATE TABLE lock (t0 TINYINT NOT NULL, t1 TINYINT NOT NULL, UNIQUE combination (t0, t1)); While we are postponing, life speeds by.--Lucius Annaeus Seneca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with SQL statement?
Try this: SELECT * FROM MyTable WHERE RefList not regexp (^|[^0-9])MyNum([^0-9]|$); Regards Salam Jeff Snoxell wrote: Hi, I've got a varchar field in a mysql table that contains 'N' numbers (each a maximum of 4 digits, not zero padded) separated by spaces when more than 1 number is present. eg. field content might be any of the following '' '123 4567 1234 45 3' '3' '3 4 6' I want to select only those records that do not contain a certain number. Problem I have with using LIKE %MyNum% is that obviously this is going to match 3 against 123 which I don't want it to. Is there something I can use like Perl's word boundary pattern match code? My statement so far is: SELECT * FROM MyTable WHERE RefList NOT RLIKE \bMyNum\b # Where \b is a word boundary 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Will PhpMyAdmin do more than just create a MySQL db ???
Will PhpMyAdmin do more than simply create a MySQL db ? Please cc me directly on any post at [EMAIL PROTECTED] Thanks for any help. Will - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Avg_row_length
On 15 Jan 2003, at 13:03, Stefan Hinz wrote: I believe there is an extra byte for the 'null' flag; if you declare this column to be NOT NULL, I'll bet the avg length will be 10. nice bet, but you lose g: In addition to the bitmap for which columns have null values (if there are any columns that can be null), there's a similar bitmap to show which columns have zero values (or the empty string, for string columns). That normally saves space because no additional storage is used if the column is zero. There's a formula to calculate the row length for dynamic MyISAM tables here: http://www.mysql.com/doc/en/Dynamic_format.html -- 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: how do i retrieve distinct rows using IN
SELECT SUM( price ) from sys_bld_foo where catNum IN ('item1', 'partA',
'partB') group by catNum;
Ken Easson wrote:
hello,
I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.
table sys_bld_foo:
component: varchar 16
catNum: varchar 16
price: decimal (10,2)
i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 'partB');
my return value ignores the second partB - and my price is short.
when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 'partB');
my return value doubles up on partA and partB - and my price is too much.
here is a sample database to see where the problem lies:
component | item | price
-
foo| item1 | 200.00
foo | item2 | 300.00
primary bar | partA | 75.00
primary bar | partB | 150.50
second bar | partA| 75.00
second bar | partB | 150.50
second bar | partC| 160.00
currently i am using perl to remove the second partB, create a hash $catNum{partB} = 2, and then for each item returned, check the hash and multiple the price by it's results. This seems extremely slow if my problem can be solved in sql alone.
can anyone help?
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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
How to enable OpenSSL support
Hi list, I'd like to try out the OpenSSL support of MySQL, but I could not find out how to enable it. The documentation just says, Configure MySQL with --with-vio --with-openssl. Since I do not compile my MySQL binaries myself, this is impossible to me... Instead I'm using the Lnux binary RPMs (Server + Max + Client) for my Linux machine and the Win32 binary (mysqld-max-nt) distribution for my Windows XP machine. Of course I have installed the latest OpenSSL toolkit on both machines first. I went through all startup parameters that mysqld-max-net --help prints out, but I still could not find a clue how to enable it. On the MySQL download page, there is a not about the Max binary packages saying, The Max version includes additional features such as [...] OpenSSL support. So, the Max binarys do support OpenSSL, but how do I enable it? Thanks in advance, Alexander M. Turek PS.: Please also reply to my address because I did not subscribe to this list, 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
mysqld got signal 11;
Hello,
I found more following messages in my mysql log:
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 agaist 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=16773120
record_buffer=131072
sort_buffer=524280
max_used_connections=8
max_connections=200
threads_connected=6
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 144378 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation
030115 09:46:53 mysqld restarted
/usr/local/libexec/mysqld: ready for connections
What is wrong? I have version 3.23.49 on OpenBSD 3.1 compiled from
ports, 256 MB RAM, PHP 4.2.3.
I start MySQL with folowing script:
#!/bin/sh
PREFIX=/usr/local
if [ -x /sbin/ldconfig ]; then
/sbin/ldconfig -m ${PREFIX}/lib/mysql
fi
if [ -x ${PREFIX}/bin/safe_mysqld ]; then
${PREFIX}/bin/safe_mysqld --open-files-limit=32768 --set-variable max_connections=200
/dev/null echo -n ' mysql'
fi
And now my /etc/my.cnf:
[client]
port= 3306
[mysqld]
port= 3306
skip-locking
set-variable= key_buffer=16M
set-variable= max_allowed_packet=1M
set-variable= table_cache=64
set-variable= sort_buffer=512K
set-variable= net_buffer_length=8K
set-variable= myisam_sort_buffer_size=8M
log-bin
server-id = 1
[mysqldump]
quick
set-variable= max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
set-variable= key_buffer=20M
set-variable= sort_buffer=20M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[myisamchk]
set-variable= key_buffer=20M
set-variable= sort_buffer=20M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[mysqlhotcopy]
interactive-timeout
What I may do to repair this problem?
Thanks Radek
--
Regards,
Bc. Radek Kreja
Starnet, s. r. o.
[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: Optimizing Ext3 for MySQL
On Wed, Jan 15, 2003 at 11:24:05AM -0800, Jeff Kilbride wrote: By updated you mean inserts? Or are you doing a table scan for each update (since they're not indexed)? No, they're actual updates (UPDATE table SET field=value WHERE primary_key=value2). The field being updated is not an index, so I'm assuming the index files aren't changing -- which, in general, should be faster than a case where an index is changed. (right?) However, I'm using the primary key in the WHERE clause to find the correct record to update -- so it's not a table scan for each record. Ah, good. I mis-understood what you were saying about indexes. Cool. Yeah, 5 seconds is the ext3 default. You can tune it. I recently saw someone suggest this: # set disk flush to 30,000 clicks or 5 minutes echo 30 64 64 256 3 3000 60 0 0 /proc/sys/vm/bdflush But have not tried it myself. What's a click? I've seen some other suggestions for bdflush, also -- but I've seen other articles that say the defaults are pretty good and playing with these numbers could cause more harm than good... I haven't come across anything definitive that deals with tuning ext3. I'm tempted to try mounting the DB drive as ext2, to see what difference it makes. Yeah, I'd suggest diabling the journal and see what happens. I belive you can use tunefs to do that. I'm a ReiserFS person myself, so this is mostly second-hand info. That's why I don't know what clicks are in that comment either. Apache/PHP is also running on this box and accessing the database, but the load is 0.00 until I run the update script -- then the load jumps to anywhere between 2 and 5. If I switch modes with mytop, I see something like this: 233 245 218 158 2 120 250 235 195 4 etc... Which makes me think the slowdown has something to do with the journal writes. I've seen numbers as high as 2000 in mytop consistently over 3 or 4 seconds, and more than once while the script runs, but I don't know why I'm getting these huge bursts of speed intermittently. This isn't a huge problem, I'm just puzzled that I can get such high numbers when my average seems 10 or 20 times less. Is this normal? Hmm. Yeah I'd expect to see something a bit more even than that. Is there any way to optimize large numbers of UPDATES with MySQL, like you can INSERTS? I don't think so. A bulk-update syntax would be interesting... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,043,772,320 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 do i retrieve distinct rows using IN
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
is a valid SQL statement from all i can see
my return value ignores the second partB - and my price is short.
i dont understand what you mean by 'short'
you comment that if you remove the DISTINCE that it 'doubles up' the partA and
partB data... yes correct.. that is how your data in the database is.
multiple rows for 150.50 price
i think you have your items and parts (of items) in the same table and that
generally is a bad idea.. from your data i would say you have 7 items in your
table... OR 7 parts but i think you are trying to say you have 2 items,
with parts for them maybe??
What is the problem, the business problem you are trying to solve? or what do
you need from your data? i think you are just querieng for the wrong
information .. but since i dont know what you are trying to do, i cant say
more right now.
Id need more information..
Brian Lindner
-- Original Message -
Subject: how do i retrieve distinct rows using IN
Date: Wed, 15 Jan 2003 15:12:37 -0400
From: Ken Easson [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
hello,
I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.
table sys_bld_foo:
component: varchar 16
catNum: varchar 16
price: decimal (10,2)
i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
my return value ignores the second partB - and my price is short.
when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN
('item1', 'partA', 'partB', 'partB');
my return value doubles up on partA and partB - and my price is too much.
here is a sample database to see where the problem lies:
component | item | price
-
foo| item1 | 200.00
foo | item2 | 300.00
primary bar | partA | 75.00
primary bar | partB | 150.50
second bar | partA| 75.00
second bar | partB | 150.50
second bar | partC| 160.00
currently i am using perl to remove the second partB, create a hash $catNum
{partB} = 2, and then for each item returned, check the hash and multiple the
price by it's results. This seems extremely slow if my problem can be solved
in sql alone.
can anyone help?
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
-
Before posting, please check:
http://www.mysql.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
FYI: superior searchable MySQL list archives at
http://www.listsearch.com/mysql.lasso - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Foreign key in INNODB
Rafal Jank wrote: On Wed, 15 Jan 2003 11:02:44 +1100 vinita Vigine Murugiah [EMAIL PROTECTED] wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice There is no index on ifname in table equipment_interfaces. Well.. Then how come Foreign key equipID in table equipment_interfaces worked?? There is no INDEX on equipID in table equipment?? mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) *** mysql CREATE TABLE equipment_interfaces_IPv4alias ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifIPv4alias CHAR(20) NOT NULL, - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - INDEX (ifName), - FOREIGN KEY (ifName) REFERENCES equipment_interfaces (ifName) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName, ifIPv4alias) - ) TYPE=INNODB; ERROR 1005: Can't create table './test/equipment_interfaces_IPv4alias.frm' (errno: 150) mysql *** ** [vinita@mungah] vinita [1:54] perror 150 Error code 150: Unknown error: 150 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 results of a query?
If you're in Java you can call getMetaData on the ResultSet, and then call methods on that (ResultSetMetaData) to determine number of columns, and type of each column. This avoids the steps of creating/deleting a temp table. What language are you using? From: Will Standley [EMAIL PROTECTED] Subject: --- How to query results of a query? Date: 16/01/2003 6:42:05 To: [EMAIL PROTECTED] How do you query the table that is the results of a query? Must you ask MySQL to... ...create a temporary table form the results of the first query ...then query that temporary table ...then delete the temp table when you are done? (and if so how do you ask MySQL to create a temp table from the results of a query?) Is there a better and faster way to do this with minimum burden on the web server with the db on it? Thanks for any help. Will - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php This message was sent through MyMail http://www.mymail.com.au - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
