Foreign Keys with MySQL 3.23.49 and 3.23.50
sql,query Hello, i've tried the foreign key-example out of the documentation(3.5.6 Using Foreign Keys) on W2K (3.23.49 + 3.23.50) and LINUX (3.23.49). The tables persons and shirts are created as innodb-tables. I could insert a shirt for a non-existing person. Also i could delete a person with existing shirts. I have experimented with 'ON DELETE' and 'ON UPDATE' with no effects. What's wrong? Is there a switch to activate foreign keys? Heiner Jostkleigrewe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fulltext search - several tables
Hello everybody! How can I do a fulltext search on several tables with just one SQL query? Best regards /Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Join or smth.
I have a table that looks smth. like this: id product price store -- 1 p1100s1 2 p1120s2 3 p1100s3 4 p2120s1 5 p2 95s2 6 p2300s3 7 p3100s1 8 p3120s2 9 p3125s3 10 p3130s1 CREATE TABLE `products` ( `id` tinyint(3) unsigned NOT NULL auto_increment, `product` varchar(4) NOT NULL default '0', `price` smallint(3) unsigned NOT NULL default '0', `store` varchar(4) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM; # # Dumping data for table 'products' # INSERT INTO products VALUES(1,p1,100,s1); INSERT INTO products VALUES(2,p1,120,s2); INSERT INTO products VALUES(3,p1,110,s3); INSERT INTO products VALUES(4,p2,120,s1); INSERT INTO products VALUES(5,p2,95,s2); INSERT INTO products VALUES(6,p2,300,s3); INSERT INTO products VALUES(7,p3,100,s1); INSERT INTO products VALUES(8,p3,120,s2); INSERT INTO products VALUES(9,p3,125,s3); INSERT INTO products VALUES(10,p3,130,s1); I want to build a query to find out where I can find the least expensive product. I know how to solve the problem using a script language (PHP) but I'd like to do it SQL-style :). I guess it can be done using some sort of JOIN. -- Ciprian Always smile. It makes people wonder what you're up to. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql values
Hi, Currently I am using | (pipe) (without quotes) to separate values if they belong to the same user. eg : If a user wants to receive an e-mail on Friday and Saturday I will put in the required field 5|6. And than retrieve that split/explode it with | and check the value. Is there any other solution to this where we require to store more than one value against the same entry/user. kindly explain with example. Piyush. --- [EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Hi, Currently I am using | (pipe) (without quotes) to separate values if they belong to the same user. eg : If a user wants to receive an e-mail on Friday and Saturday I will put in the required field 5|6. And than retrieve that split/explode it with | and check the value. Is there any other solution to this where we require to store more than one value against the same entry/user. kindly explain with example. Piyush. __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.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
Server speed working with databases and apache
Hi everybody: I´m looking for some information about server speed. The problem: I need a server for about 40 concurren users, it will be in an internal net, the server will manage a mysql database with a master table of about 4000 records and some chil tables from 2 to 4 records, it will be accesed trougth apache with php. I need a response time about 0.1-0.2 seconds. Is there some information or good article about this issue What kind of machine would be better? Can anybody give me some information or reference??? Thanks a lot Sorry about my english Javier Armendáriz [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
problems with BACKUP TABLE
When try to use BACKUP TABLE, i get an error 'Failed copying .frm file: errno = 2' in the result table. What's the problem? P.S. Unix FreeBSD, MySQL 3.23.40 and type of the table is MyISAM (CHECK works fine). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: New User: Installing MySQL - Linux Mandrake ?
pmoscatt, Tuesday, May 28, 2002, 2:23:00 AM, you wrote: p I am new to mysql and having trouble understanding the installation procedures for linux (Mandrake). p I have downloaded the mysql-3.23.49a-pc-linux-gnu-i686.tar.gz and unpacked it in /usr/local as root. p I then issued the following commands as per the install procedures: p groupadd mysql p useradd -g mysql mysql p cd /usr/local p gunzip the tar file p ln -s mysql-3.23.49a-pc-linux-gnu-i686 p cd mysql p scripts/mysql_install_db p chown -R root /usr/local/mysql p chown -R mysql /usr/local/mysql/data p chgrp -R mysql /usr/local/mysql p chown -R root /usr/local/mysql/bin p bin/safe_mysql --user=mysql p From here, this is where I have lost the scent. I would have thought I needed to run ./configure ... p But it indicated that this has already been done. I have tested the system and I can see the test database and it's contents. p Later in the documentation under Connecting to and Disconnecting from the Server I see the following command: p mysql -h host -u user -p p and from there I should see the welcom screen, but this dosen't happen. Did you received Access denied error? p What am I doing wrong ?? By default MySQL has 2 user after installation: user 'root' without a password and anonymous user ''(empty string) without a password, too. User 'root' has all privileges on all databases. Anonymous user has grants only on the database 'test'. Look at: http://www.mysql.com/doc/D/e/Default_privileges.html p Would I be better trashing it and getting the RPM from rpmfind and installing that instead - where I am assuming the rpm version will set up all the background admin work ready for use ? p Best Regards p Pete -- 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: MyODBC connection to Linux MySQL
yvon, Monday, May 27, 2002, 11:33:42 PM, you wrote: yd I tried to connect to a mysql database running on a yd linux server using an odbc connection in windows 98. I yd get the following message: yd [TCX][MyODBC]Host x.x.x.x is not allowed to connect to yd this MySQL server yd (#1130)[Microsoft][ODBC Driver Manager] Connection not yd open (#0) yd The username specified in the data source name is root yd and I can ping the server where MySQLis running. yd What did I do wrong or did not do at all on client or yd server side? Your root user has a problem with privileges. 'root' doesn't have privilege to connect from the host. You should allow user to connect from your host. Check it: http://www.mysql.com/doc/A/c/Access_denied.html yd Plase help me. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Fw: Problem starting server
Matt,
Monday, May 27, 2002, 9:39:05 PM, you wrote:
MH Been there, done that. Didn't change a thing.
MH Guess I should have changed the e-mail before resending it, but I'm not
MH really sure what the problem is. The server won't start for more than a
MH tenth of a second. Here's what happens when I try to start mysqld:
MH [root@server mysql]# safe_mysqld
MH Starting mysql daemon with databases from /var/lib/mysql
MH 020527 02:26:27 mysql ended
MH Both statements ('Starting...' and '0205...') pop up within a tenth of a
MH second of each-other and when I check processes, there is nothing that
MH starts with 'my' running.
Matt, check your .err file that is located in the MySQL datadir
(/var/lib/mysql). If there are any causes of error in the .err file?
MH Does that help?
--
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: Installation
Ma., Tuesday, May 28, 2002, 7:09:37 AM, you wrote: MRC hi! im a newbie.. ive installed both mysql and php MRC under windows 2000.. and ive tried the command MRC mysqld-nt -install.. and it didnt work, only the net MRC start mysql worked.. and i cant run php or browse in MRC IE .. Can you provide more info about what exactly doesn't work? MRC pls help.. MRC Thanks.. -- 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: INSERT IF NOT EXISTS?
(feed the hungry filters: sql, query) On Monday 27 May 2002 2:14 pm, Victoria Reznichenko wrote: ML Is there a simple way to do something like INSERT IF NOT EXISTS, ML other than first doing a SELECT and checking if it returns any rows? ML If not, that would be pretty high up on my whish-list : ) You can use IGNORE keyword in INSERT statement, but it's not quite the same as INSERT IF NOT EXIST. If you specify IGNORE, any rows that duplicate an existing PRIMARY or UNIQUE key in the table will be ignored in INSERT: Yes, I was aware of the INSERT IGNORE statement, but I've only got one primary/unique key, which happens to be the id. Perhaps I should elaborate further. I've got three tables; ip_name_tbl, loc_tbl and conn_tbl. ip_name_tbl has got fields id, comp_loc, ip_stat_dyn, ip, mac, network, name and comments. loc_tbl has got fileds id, comp_loc and loc_name and conn has got fileds id, wall_nr, hub_switch_nr, comp_id. ip_name_tbl.comp_loc points to loc_tbl.comp_loc=20 conn.comp_id points to ip_name_tbl.id. I want to be sure that while inserting values, say NULL (since id is AUTO_INCREMENT), 1 and Administration, administration isn't already in the table. Rob's idea is of course a solution, but that sort of brings me back to my original question : is there a -SIMPLE- way? ; ) INSERT IGNORE wouldn't, if I understood the manual correctly, help me here, since the other rows in the table aren't UNIQUE, no? Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library, Finland [EMAIL PROTECTED] +358-6-325 3589/+358-40-832 6709 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 starting server
this is the error message I get Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Why is this ? Here is a copy of the my.cnf file [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 creating large number of files
At 17:04 5/16/2002 +0200, Melvyn Sopacua wrote: I don't know what caused it, nor whether it's reproducable, Well - it's at least repetative, cause I have the same thing again. We're now at rep_log.81775. Could I please get an insight on what possibly causes this, so I might find the trigger and create a reproducable testcase? but on an AIX system, I just found out that both of the 4.0.1 masters (typical master=slave_of_master loop using same source tree configuration) have created 100.000+ files of the replication log. I can only think they are replicating the RESET MASTER command to eachother which I have issued once: mysql SHOW BINLOG EVENTS IN 'rep_log.100587'; ++-++---+-++ | Log_name | Pos | Event_type | Server_id | Log_seq | Info | ++-++---+-++ | rep_log.100587 | 4 | Rotate | 3406120 | 2 | rep_log.100588;pos=4; forced by master | | rep_log.100587 | 45 | Stop | 3406121 | 3 || ++-++---+-++ 2 rows in set (8.33 sec) mysql SHOW BINLOG EVENTS IN 'rep_log.002'; +-+-++---+-++ | Log_name| Pos | Event_type | Server_id | Log_seq | Info | +-+-++---+-++ | rep_log.002 | 4 | Start | 3406121 | 1 | Server ver: 4.0.1-alpha-log, Binlog ver: 2 | | rep_log.002 | 79 | Rotate | 3406120 | 3 | rep_log.003;pos=4; forced by master| | rep_log.002 | 117 | Stop | 3406121 | 4 || +-+-++---+-++ 3 rows in set (0.00 sec) mysql SHOW BINLOG EVENTS IN 'rep_log.001'; +-+-++---+-++ | Log_name| Pos | Event_type | Server_id | Log_seq | Info | +-+-++---+-++ | rep_log.001 | 4 | Start | 3406121 | 1 | Server ver: 4.0.1-alpha-log, Binlog ver: 2 | | rep_log.001 | 79 | Stop | 3406121 | 2 || +-+-++---+-++ 2 rows in set (0.00 sec) I'll have to clean this up a.s.a.p as it's eating away inodes. I'll tar the directory to keep for reference, just in case you guys need it. I have no way to check whether it's reproducable. Best regards, Melvyn Sopacua WebMaster IDG.nl _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ If it applies, where it applies - this email is a personal contribution and does not reflect the views of my employer IDG.nl. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Best regards, Melvyn Sopacua WebMaster IDG.nl _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ If it applies, where it applies - this email is a personal contribution and does not reflect the views of my employer IDG.nl. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: bug 11 wysql 3.23.49 win (OS Windows XP)
sql From: [EMAIL PROTECTED] To: Antoine Clanché [EMAIL PROTECTED] Subject: Re: bug 11 wysql 3.23.49 win (OS Windows XP) Date: Tue, 28 May 2002 10:32:48 +0200 Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Antoine Clanché [EMAIL PROTECTED] 0663645239 _ MSN Photos est le moyen le plus simple de partager et d'imprimer vos photos : http://photos.msn.com/support/worldwide.aspx Antoine Clanché [EMAIL PROTECTED] 0663645239 _ MSN Photos est le moyen le plus simple de partager et d'imprimer vos photos : http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Join or smth.
select * from table_name where product like 'p1' order by price limit 1; - Original Message - From: Ciprian Trofin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 11:20 AM Subject: Join or smth. I have a table that looks smth. like this: id product price store -- 1 p1100s1 2 p1120s2 3 p1100s3 4 p2120s1 5 p2 95s2 6 p2300s3 7 p3100s1 8 p3120s2 9 p3125s3 10 p3130s1 CREATE TABLE `products` ( `id` tinyint(3) unsigned NOT NULL auto_increment, `product` varchar(4) NOT NULL default '0', `price` smallint(3) unsigned NOT NULL default '0', `store` varchar(4) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM; # # Dumping data for table 'products' # INSERT INTO products VALUES(1,p1,100,s1); INSERT INTO products VALUES(2,p1,120,s2); INSERT INTO products VALUES(3,p1,110,s3); INSERT INTO products VALUES(4,p2,120,s1); INSERT INTO products VALUES(5,p2,95,s2); INSERT INTO products VALUES(6,p2,300,s3); INSERT INTO products VALUES(7,p3,100,s1); INSERT INTO products VALUES(8,p3,120,s2); INSERT INTO products VALUES(9,p3,125,s3); INSERT INTO products VALUES(10,p3,130,s1); I want to build a query to find out where I can find the least expensive product. I know how to solve the problem using a script language (PHP) but I'd like to do it SQL-style :). I guess it can be done using some sort of JOIN. -- Ciprian Always smile. It makes people wonder what you're up to. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOINS: MySQL Query Speed
Hello I have a question on how MySQL JOIN has effect on query (search) performance: Our database consists of about 250.000 datasets (rows). Right now, all of the 150 columns are in one big table. However, we do have certain columns that are empty for most rows (for example information of the status of pictures of customer). Out of the 250.000, only 20.000 have information on photo-status. The idea is to put the columns (amount: 5) about the photo-status in a seperate table to not blow up the database (to normalize the database ?). A search over, lets say, 15 columns, that includes one columns out of the photo-status-columns would then have to JOIN these two tables. Does that lead to performance drop ? Or is maybe even faster to search with a JOIN, because we only have 20.000 rows in that seperate photo-status-table ? What would probably happen if we did this with, lets say, 5 other data-sets (we would the have 7 tables connected through JOIN) ? I hope someone can help me with performance issues about JOIN. Thanks. Flo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
bug mySQL 3.23.49
sql Antoine Clanché [EMAIL PROTECTED] 0663645239 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: problems with BACKUP TABLE
On mardi, mai 28, 2002, at 09:35 , Evgeny Chuykov wrote: When try to use BACKUP TABLE, i get an error 'Failed copying .frm file: errno = 2' in the result table. What's the problem? BACKUP TABLE write files with the user MySQLd is running... So if your deamon run as mysql, you had to autorize user mysql to write into your backup destination... R. PS: 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
Fulltext search - several tables
Hello everybody! How can I do a fulltext search on several tables with just one SQL query? Best regards /Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOINS: MySQL Query Speed
Hello I have a question on how MySQL JOIN has effect on query (search) performance: Our database consists of about 250.000 datasets (rows). Right now, all of the 150 columns are in one big table. However, we do have certain columns that are empty for most rows (for example information of the status of pictures of customer). Out of the 250.000, only 20.000 have information on photo-status. The idea is to put the columns (amount: 5) about the photo-status in a seperate table to not blow up the database (to normalize the database ?). A search over, lets say, 15 columns, that includes one columns out of the photo-status-columns would then have to JOIN these two tables. Does that lead to performance drop ? Or is maybe even faster to search with a JOIN, because we only have 20.000 rows in that seperate photo-status-table ? What would probably happen if we did this with, lets say, 5 other data-sets (we would the have 7 tables connected through JOIN) ? I hope someone can help me with performance issues about JOIN. Thanks. Flo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[2]: Join or smth.
Doesn't work the way I want to: I want the result to be like: id | product | price | store 1 | p1 | 100 | s1 3 | p1 | 100 | s3 5 | p2 | 95 | s2 7 | p3 | 100 | s1 CL select * from table_name where product like 'p1' order by price limit 1; I have a table that looks smth. like this: id product price store -- 1 p1100s1 2 p1120s2 3 p1100s3 4 p2120s1 5 p2 95s2 6 p2300s3 7 p3100s1 8 p3120s2 9 p3125s3 10 p3130s1 CREATE TABLE `products` ( `id` tinyint(3) unsigned NOT NULL auto_increment, `product` varchar(4) NOT NULL default '0', `price` smallint(3) unsigned NOT NULL default '0', `store` varchar(4) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM; # # Dumping data for table 'products' # INSERT INTO products VALUES(1,p1,100,s1); INSERT INTO products VALUES(2,p1,120,s2); INSERT INTO products VALUES(3,p1,110,s3); INSERT INTO products VALUES(4,p2,120,s1); INSERT INTO products VALUES(5,p2,95,s2); INSERT INTO products VALUES(6,p2,300,s3); INSERT INTO products VALUES(7,p3,100,s1); INSERT INTO products VALUES(8,p3,120,s2); INSERT INTO products VALUES(9,p3,125,s3); INSERT INTO products VALUES(10,p3,130,s1); I want to build a query to find out where I can find the least expensive product. I know how to solve the problem using a script language (PHP) but I'd like to do it SQL-style :). I guess it can be done using some sort of JOIN. -- Ciprian Un cuvant de sfarsit: Useless fact: Odds of being killed in a car crash are 1 in 5000. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[2]: problems with BACKUP TABLE
When try to use BACKUP TABLE, i get an error 'Failed copying .frm file: errno = 2' in the result table. What's the problem? RC BACKUP TABLE write files with the user MySQLd is running... RC So if your deamon run as mysql, you had to autorize user RC mysql to write into your backup destination... well... it doesn't work even when i'm dumping it to '/tmp/test.sql'. i suppose '/tmp' is world writable. -- Ñ óâàæåíèåì, Evgeny mailto:[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
killing mysql-threads?
Is it possible to set a variable to kill mysql-threads which sleeps for a time e.g. 5 hours? Which variables have to be set therefore? Regards, Anatol - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[2]: problems with BACKUP TABLE
RC On mardi, mai 28, 2002, at 09:35 , Evgeny Chuykov wrote: When try to use BACKUP TABLE, i get an error 'Failed copying .frm file: errno = 2' in the result table. What's the problem? Sorry, my stupid mistake. As you said there was problems whith rights and dir 'test' didn't exists in '/tmp', so mysqld couldn't write. Thanks. -- Best regards, Evgeny mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql reconstructor
Hello list, In one of my mysql databases I have an ISAM type table that has the .ISM file corrupted. I would like to recover this file but have no idea how to do it so that's the reason I addres this time to the list looking for some help :-[ . I´m lost! Does it exist any reconstructor tool (public if possible but otherwise might be useful as well) that will construct a new .ISM file from a ISD data file? Looking forward to your answers ;-) , thanks a lot in advance. Sonia. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: killing mysql-threads?
wait_timeout 8 hours as standard Simon -Original Message- From: Anatol Mayr / HEXAGON [mailto:[EMAIL PROTECTED]] Sent: 28 May 2002 09:26 To: [EMAIL PROTECTED] Subject: killing mysql-threads? Is it possible to set a variable to kill mysql-threads which sleeps for a time e.g. 5 hours? Which variables have to be set therefore? Regards, Anatol - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: killing mysql-threads?
Can you tell me how the variable has to be declared? - time in seconds? e.g. is wait_timeout=7200 for 2 hours OK? Thanks, Anatol - Original Message - From: Simon Green [EMAIL PROTECTED] To: 'Anatol Mayr / HEXAGON' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 11:39 AM Subject: RE: killing mysql-threads? wait_timeout 8 hours as standard Simon -Original Message- From: Anatol Mayr / HEXAGON [mailto:[EMAIL PROTECTED]] Sent: 28 May 2002 09:26 To: [EMAIL PROTECTED] Subject: killing mysql-threads? Is it possible to set a variable to kill mysql-threads which sleeps for a time e.g. 5 hours? Which variables have to be set therefore? Regards, Anatol - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
query
Hi, I want to start the autoincrement field in mysql primary key from 1000 instead of 1. Any ideas. Thank You. Piyush. __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.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
synopsis of the problem (one line)
Description: How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:FR/I/*KO_TBD// Admin Apache Organization: organization of RHODIA F.TOUITOU (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-3.23.49 (Source distribution) Environment: System: AIX ldousyp3 3 4 0041329A4C00 Some paths: /bin/perl /bin/make /usr/local/bin/gcc /bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/powerpc-ibm-aix4.3.2.0/3.0.1/specs Configured with: ./configure --enable-threads=aix --disable-nls Thread model: single gcc version 3.0.1 Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 bin bin 19 19 mai 2001 /lib/libc.a - /usr/ccs/lib/libc.a lrwxrwxrwx 1 bin bin 19 19 mai 2001 /usr/lib/libc.a - /usr/ccs/lib/libc.a Configure command: ./configure --with-unix-socket-path=/var/tmp/mysql.sock --with-low-memory --with-mit-threads=yes --without-perl --enable-thread-safe-client --with-berkeley-db --enable-local-infile --with-innodb Perl: This is perl, version 5.005_03 built for aix-thread - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: 2 way replication
On Sun, 26 May 2002 23:15:22 +0200 Bjarne Jørgensen [EMAIL PROTECTED] wrote: Hi! I've watched the development of replication on mysql with great joy. However I haven't been able to understand when og if mysql is going to support 2 way replication. In other words. Will it be possible to have 2 databases on 2 different locations, that synchronize with each other in a way, that the users of the databases can change data on both ends and when the link comes up again, the data will be updated both ways. I've read about master/slave relations, but as i can understand, it is only possible to chance data on the master now. If you change data on the slave it wont come on the master. Will that be or is it available on mysql now or in the future? already capable on current version, if i'm not mistaken ... starting 3.23.28 ... you just set ... on server A ... as master for server B, and also as slave from server B and on server B ... as master for server A, and as slave from server A -- Let's call it an accidental feature. -- Larry Wall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: killing mysql-threads?
sql,query - Original Message - From: [EMAIL PROTECTED] To: Anatol Mayr / HEXAGON [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 12:07 PM Subject: Re: Re: killing mysql-threads? Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Can you tell me how the variable has to be declared? - time in seconds? e.g. is wait_timeout=7200 for 2 hours OK? Thanks, Anatol - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: query
insert into table (auto_inc_field) values (1000); mysql will carry on from there Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: p shah [mailto:[EMAIL PROTECTED]] Sent: 28 May 2002 10:54 To: mysql list Subject: query Hi, I want to start the autoincrement field in mysql primary key from 1000 instead of 1. Any ideas. Thank You. Piyush. __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: Join or smth.
create temporary table best_values select max(product) as prodName, min(price) as bestPrice from products group by product; select products.* from best_values,products where best_values.prodName = products.product and best_values.bestPrice = products.price; drop table best_values; (and note that your INSERT statements don't match the data you've printed - is s3 offering p1 at 100 or 110?) On 28/5/02 at 12:24 pm, Ciprian Trofin [EMAIL PROTECTED] wrote: Doesn't work the way I want to: I want the result to be like: id | product | price | store 1 | p1 | 100 | s1 3 | p1 | 100 | s3 5 | p2 | 95 | s2 7 | p3 | 100 | s1 CL select * from table_name where product like 'p1' order by price limit 1; I have a table that looks smth. like this: id product price store -- 1 p1100s1 2 p1120s2 3 p1100s3 4 p2120s1 5 p2 95s2 6 p2300s3 7 p3100s1 8 p3120s2 9 p3125s3 10 p3130s1 CREATE TABLE `products` ( `id` tinyint(3) unsigned NOT NULL auto_increment, `product` varchar(4) NOT NULL default '0', `price` smallint(3) unsigned NOT NULL default '0', `store` varchar(4) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM; # # Dumping data for table 'products' # INSERT INTO products VALUES(1,p1,100,s1); INSERT INTO products VALUES(2,p1,120,s2); INSERT INTO products VALUES(3,p1,110,s3); INSERT INTO products VALUES(4,p2,120,s1); INSERT INTO products VALUES(5,p2,95,s2); INSERT INTO products VALUES(6,p2,300,s3); INSERT INTO products VALUES(7,p3,100,s1); INSERT INTO products VALUES(8,p3,120,s2); INSERT INTO products VALUES(9,p3,125,s3); INSERT INTO products VALUES(10,p3,130,s1); I want to build a query to find out where I can find the least expensive product. I know how to solve the problem using a script language (PHP) but I'd like to do it SQL-style :). I guess it can be done using some sort of JOIN. -- Ciprian Un cuvant de sfarsit: Useless fact: Odds of being killed in a car crash are 1 in 5000. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Shared message queue?
The short way between two points, it's right line Hi, If you use MySQL for inter process communication you waste performance.Benjamin is right. You can use the classical forms of IPC or not.But the principle must remain the same if you wish than your work that not to be in vain/for nothing. A simple way can be, if you have a data structure which contain ,IP address for incoming,IP for destination and a status flag.This struct you can use in a array global variable which is attached to one segment of shared memory(which is already created). Scenario: When one of users is knocking at your server: - take the message and put it in MySQL database(i suppose it's your desire to use MySQL for exchange messages), because you don't know if the destination IP user is connected or not(or you can make a routine to check this ). -your global variable: IP_INCOMMING=164.0.0.1 IP_OUTGOING = 164.0.0.2 STATUS =(let's say) FOR_READ -if destination IP is connected , he receive the signal FOR_READ, in this moment can read from MySQL the message and change the status to IS_READ.The incoming IP receive the signal and tell to the user which send the message :OK.Message was delivered.After this, a reset of both location from array it's needed and status can be WAIT_FOR_MESSAGES. This is not an usualy way for inter process comunication, but it's fast.The child process establish a way for communications directly with other child process and not with father process. It's seems to be very simple but you maybe already know, that type of work have big advantage and some disadvantage. This is my opinion and i hope to help you. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Philip Mak [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 27, 2002 10:48 PM Subject: Shared message queue? I have a bunch of processes running on the same machine that should be able to send unicast, multicast and broadcast messages to each other. Messages should be received in the same order that they were sent. I'm trying to figure out how to implement that. These processes are already sharing the same MySQL database, so I'm thinking that a process which wants to send a message could INSERT it into a table, then another process can SELECT it to read it. This would seem to require all processes to constantly poll the table, though, so I'm thinking maybe I should use something other than MySQL for the shared message queue functionality... Suggestions? Should I: (1) use MySQL as I described above, (2) use MySQL with some other method I haven't thought of, or (3) use something else? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[2]: Join or smth.
I'm sure this question should be put in the docs. It's asked at least once a fortnight. Welcome to the wonderful world of Mysql variables. create temporary table productorder select *,(@a:= IF(@b=product,@a+1,1)) as cnt, @b:=product from products order by product,price; mysql select * from productorder; ++-+---+---+--+-+ | id | product | price | store | cnt | @b:=product | ++-+---+---+--+-+ | 1 | p1 | 100 | s1|1 | p1 | | 3 | p1 | 110 | s3|2 | p1 | | 2 | p1 | 120 | s2|3 | p1 | | 5 | p2 |95 | s2|1 | p2 | | 4 | p2 | 120 | s1|2 | p2 | | 6 | p2 | 300 | s3|3 | p2 | | 7 | p3 | 100 | s1|1 | p3 | | 8 | p3 | 120 | s2|2 | p3 | | 9 | p3 | 125 | s3|3 | p3 | | 10 | p3 | 130 | s1|4 | p3 | ++-+---+---+--+-+ 10 rows in set (0.04 sec) select * from productorder where cnt=1; mysql select * from productorder where cnt=1; ++-+---+---+--+-+ | id | product | price | store | cnt | @b:=product | ++-+---+---+--+-+ | 1 | p1 | 100 | s1|1 | p1 | | 5 | p2 |95 | s2|1 | p2 | | 7 | p3 | 100 | s1|1 | p3 | ++-+---+---+--+-+ 3 rows in set (0.00 sec) Ric. - Original Message - From: Ciprian Trofin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 12:24 PM Subject: Re[2]: Join or smth. Doesn't work the way I want to: I want the result to be like: id | product | price | store 1 | p1 | 100 | s1 3 | p1 | 100 | s3 5 | p2 | 95 | s2 7 | p3 | 100 | s1 CL select * from table_name where product like 'p1' order by price limit 1; I have a table that looks smth. like this: id product price store -- 1 p1100s1 2 p1120s2 3 p1100s3 4 p2120s1 5 p2 95s2 6 p2300s3 7 p3100s1 8 p3120s2 9 p3125s3 10 p3130s1 CREATE TABLE `products` ( `id` tinyint(3) unsigned NOT NULL auto_increment, `product` varchar(4) NOT NULL default '0', `price` smallint(3) unsigned NOT NULL default '0', `store` varchar(4) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM; # # Dumping data for table 'products' # INSERT INTO products VALUES(1,p1,100,s1); INSERT INTO products VALUES(2,p1,120,s2); INSERT INTO products VALUES(3,p1,110,s3); INSERT INTO products VALUES(4,p2,120,s1); INSERT INTO products VALUES(5,p2,95,s2); INSERT INTO products VALUES(6,p2,300,s3); INSERT INTO products VALUES(7,p3,100,s1); INSERT INTO products VALUES(8,p3,120,s2); INSERT INTO products VALUES(9,p3,125,s3); INSERT INTO products VALUES(10,p3,130,s1); I want to build a query to find out where I can find the least expensive product. I know how to solve the problem using a script language (PHP) but I'd like to do it SQL-style :). I guess it can be done using some sort of JOIN. -- Ciprian Un cuvant de sfarsit: Useless fact: Odds of being killed in a car crash are 1 in 5000. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
JOINS: MySQL Query Speed
Hello I have a question on how MySQL JOIN has effect on query (search) performance: Our database consists of about 250.000 datasets (rows). Right now, all of the 150 columns are in one big table. However, we do have certain columns that are empty for most rows (for example information of the status of pictures of customer). Out of the 250.000, only 20.000 have information on photo-status. The idea is to put the columns (amount: 5) about the photo-status in a seperate table to not blow up the database (to normalize the database ?). A search over, lets say, 15 columns, that includes one columns out of the photo-status-columns would then have to JOIN these two tables. Does that lead to performance drop ? Or is maybe even faster to search with a JOIN, because we only have 20.000 rows in that seperate photo-status-table ? What would probably happen if we did this with, lets say, 5 other data-sets (we would the have 7 tables connected through JOIN) ? I hope someone can help me with performance issues about JOIN. Thanks. Flo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOINS: MySQL Query Speed
Hello I have a question on how MySQL JOIN has effect on query (search) performance: Our database consists of about 250.000 datasets (rows). Right now, all of the 150 columns are in one big table. However, we do have certain columns that are empty for most rows (for example information of the status of pictures of customer). Out of the 250.000, only 20.000 have information on photo-status. The idea is to put the columns (amount: 5) about the photo-status in a seperate table to not blow up the database (to normalize the database ?). A search over, lets say, 15 columns, that includes one columns out of the photo-status-columns would then have to JOIN these two tables. Does that lead to performance drop ? Or is maybe even faster to search with a JOIN, because we only have 20.000 rows in that seperate photo-status-table ? What would probably happen if we did this with, lets say, 5 other data-sets (we would the have 7 tables connected through JOIN) ? I hope someone can help me with performance issues about JOIN. Thanks. Flo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fulltext search - several tables
Hello everybody! How can I do a fulltext search on several tables with just one SQL query? Best regards /Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 starting mysqld after compiling the source code
Dear Sir, I did all the steps to compile the code i.e. ./configure --prefix=/usr/local/mysql --with-named-curses-libs=/lib/libncurses.so.5.2 make make install scripts/mysql_install_db and then I tried mysqld --user=root then it gave the following error message. Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add for example, innodb_data_file_path = ibdata1:30M But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html 020528 16:39:02 mysqld: Table 'mysql.host' doesn't exist Please help me resolve it. I don't know where to make changes and which directory to create.If I have not done anything please inform me. Note: I tried to compile it 5-7 times so I did the following steps before compiling each time rm config.cache make clean Waiting for an eager response. -- Karan Singal 2nd year student CSE , IIT Guwahati Guwahati - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Keys with MySQL 3.23.49 and 3.23.50
Hi Nothing is wrong! The behaviour of your example is correct, because MySQL does not support the concept of foreign keys. See section 1.4.4.5 of the MySQL manual. In other words data integrity between two tables is not ensured by MySQL. Your application code must solve this. Regards Rene Moonen Jostkleigrewe, Heiner wrote: sql,query Hello, i've tried the foreign key-example out of the documentation(3.5.6 Using Foreign Keys) on W2K (3.23.49 + 3.23.50) and LINUX (3.23.49). The tables persons and shirts are created as innodb-tables. I could insert a shirt for a non-existing person. Also i could delete a person with existing shirts. I have experimented with 'ON DELETE' and 'ON UPDATE' with no effects. What's wrong? Is there a switch to activate foreign keys? Heiner Jostkleigrewe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Sub-selects for WHERE clauses
I'm a comparative MySQL newbie who is struggling with sub-selects. In other DBMS's, I can use sub-selects to return a set that I can use in a condition of a WHERE clause. An example is the recent query by Ciprian Trofin about finding the cheapest price of a product. In other DBMS's I would use: SELECT a.id, a.product, a.price, a.store FROM mytable a WHERE a.price = ALL ( SELECT b.price FROM mytable b WHERE a.id = b.id ) However, this doesn't work in MySQL. I've tried using the LEAST function instead of ALL, which triggered an exception at the sub-select. The MySQL docs say that sub-selects are due for implementation in 4.x. In the meantime, is there a way to emulate the preceding statement that won't cause concurrency problems? TIA, -- Geoff Lane Cornwall, UK [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
MySQL and Syslog.
Hi people, Is there a way to have the mysqld log to syslog, instead of the errorlog flatfile? I tried piping it to a stdin-to-syslog script, but that wouldn't work either. Thanks and cya, Dries. -- Dries Robberechts - Unix System Engineer Belgacom Skynet NV/SA, Rue Carli 2-4, B-1140 Brussels - IT Network Dept. - [EMAIL PROTECTED] http://www.skynet.be Lex: It's a UNIX system! I know this! (Jurassic Park) *** DISCLAIMER *** This e-mail and any attachments thereto may contain information, which is confidential and/or protected by intellectual property rights and are intended for the sole use of the recipient(s) named above. Any use of the information contained herein (including, but not limited to, total or partial reproduction, communication or distribution in any form) by persons other than the designated recipient(s) is prohibited. If you have received this e-mail in error, please notify the sender either by telephone or by e-mail and delete the material from any computer. Thank you for your cooperation. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Compiling under Cygwin
Hi, I have a problem by compiling Mysql 3.23.49 under Cygwin. My Cygwin version is 1.2. I downloaded Mysql package from this url http://www.mysql.com/Downloads/MySQL-3.23/mysql-3.23.49.tar.gz I didn't succeded in compiling these sources files, by following the instructions. I created the group, the users, then I unzipped the package, then ./configure etc. Then make. Someone could help me by finding out the right procedure please? Thank you a lot in advance Fausto By typing make, after a long procedure I receveid at the end the following messages: make[2]: *** [libmysqlclient.la] Error 1 make[2]: Leaving directory `/usr/src/mysql-3.23.49/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-3.23.49' make: *** [all-recursive-am] Error 2 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-Multi Select
I'm sort of new/rusty to SQL and I was trying to figure out the best way to do this problem. I'm creating a database for tracking a football pool. I'm kind of stuck on displaying the Games database. The Games database has a home team ID and an away team ID as well as the winning team ID. Thus I need to do three lookups per record on the Teams database to get/display the full name of the teams for viewing. Is the best way to do this is by using temporary tables for merging? There are about 15 games that need to be displayed at once. I'm using PHP for the interface to MySQL, so I know I could also do it using arrays. But I would presume I would want to stay away from doing 45+ selects (3 x 15). Any ideas/advice are appreciated. -- 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
trouble with INSERT
i have a tag textarea in my file HTML as: tdtextarea name=text rows=30 cols=15 maxlength=65000/textarea/td In this field of the WEB page , i have many lines , i would like that each line of this fied becomes a row in my Database How do I have to do for my query ?? _ Rejoignez le plus grand service de messagerie au monde avec MSN Hotmail. http://www.hotmail.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: query
p, Tuesday, May 28, 2002, 12:54:01 PM, you wrote: ps I want to start the autoincrement field in mysql ps primary key from 1000 instead of 1. ps Any ideas. You can examine the -A option of myisamchk. Take a look also at CREATE TABLE and ALTER TABLE statements: http://www.mysql.com/doc/C/R/CREATE_TABLE.html http://www.mysql.com/doc/A/L/ALTER_TABLE.html ps Thank You. ps Piyush. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: Problem starting server
Neville, Tuesday, May 28, 2002, 11:23:06 AM, you wrote: NL this is the error message I get NL Can't connect to local MySQL server through socket NL '/var/lib/mysql/mysql.sock' (2) NL Why is this ? Check permissions on the MySQL datadir. Look also into the .err file in the MySQL data dir you can find possible causes of error there. NL Here is a copy of the my.cnf file NL [mysqld] NL datadir=/var/lib/mysql NL socket=/var/lib/mysql/mysql.sock NL [mysql.server] NL user=mysql NL basedir=/var/lib NL [safe_mysqld] NL err-log=/var/log/mysqld.log NL pid-file=/var/run/mysqld/mysqld.pid -- 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: Error starting mysqld after compiling the source code
Karan, Tuesday, May 28, 2002, 2:05:37 PM, you wrote: KS Dear Sir, KS I did all the steps to compile the code i.e. KS ./configure --prefix=/usr/local/mysql KS --with-named-curses-libs=/lib/libncurses.so.5.2 KS make KS make install KS scripts/mysql_install_db KS and then I tried mysqld --user=root then it gave the following error KS message. KS Cannot initialize InnoDB as 'innodb_data_file_path' is not set. KS If you do not want to use transactional InnoDB tables, add a line KS skip-innodb KS to the [mysqld] section of init parameters in your my.cnf KS or my.ini. If you want to use InnoDB tables, add for example, KS innodb_data_file_path = ibdata1:30M KS But to get good performance you should adjust for your hardware KS the InnoDB startup options listed in section 2 at KS http://www.innodb.com/ibman.html KS 020528 16:39:02 mysqld: Table 'mysql.host' doesn't exist KS Please help me resolve it. Check your permissions on the MySQL data dir. MySQL must be owner of this dir. KS make clean KS Waiting for an eager response. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: problems with BACKUP TABLE
Evgeny, Tuesday, May 28, 2002, 12:23:58 PM, you wrote: When try to use BACKUP TABLE, i get an error 'Failed copying .frm file: errno = 2' in the result table. What's the problem? RC BACKUP TABLE write files with the user MySQLd is running... RC So if your deamon run as mysql, you had to autorize user RC mysql to write into your backup destination... EC well... it doesn't work even when i'm dumping it to '/tmp/test.sql'. EC i suppose '/tmp' is world writable. If you use BACKUP TABLE to make database backup you should specify _path_ to the backup _dir_, not for file: BACKUP TABLE mytable TO '/tmp'; -- 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
MySQL-Max and Innodb
I am attempting to get mysql-max running with Innodb. I am using the latest version .49 on a Redhat 6.2 server. The following is my my.cnf, which I have placed in the /var/lib/mysql directory: # Example mysql config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # a important part and systems up to 128M very MySQL is used together with # other programs (like a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock 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 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=1 # Uncomment the following if you are using Innobase table innodb_data_file_path = ibdata1:200M innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [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 Everytime I attempt to start the MySql server I get the following error message: 020522 09:47:08 mysqld started InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 020522 9:47:09 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 400 MB InnoDB: Database physically writes the file full: wait... InnoDB: Operating system error number 22 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Look from section 13.2 at http://www.innodb.com/ibman.html InnoDB: what the error number means or use the perror program of MySQL. InnoDB: File name /var/lib/mysql/ibdata1 InnoDB: Cannot continue operation. 020522 09:47:51 mysqld ended Any suggestions would be greatly appreciated. Kent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 reconstructor
Hello. Have a look at the isamchk tool. Be sure to make a backup of the table in question beforehand and to shut down the server while the tool is running. Regards, Benjamin. On Tue, May 28, 2002 at 11:37:15AM +0200, [EMAIL PROTECTED] wrote: Hello list, In one of my mysql databases I have an ISAM type table that has the .ISM file corrupted. I would like to recover this file but have no idea how to do it so that's the reason I addres this time to the list looking for some help :-[ . I´m lost! Does it exist any reconstructor tool (public if possible but otherwise might be useful as well) that will construct a new .ISM file from a ISD data file? Looking forward to your answers ;-) , thanks a lot in advance. Sonia. -- [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: Sub-selects for WHERE clauses
Hi. That's an FAQ and explained in the tutorial: http://www.mysql.com/doc/e/x/example-Maximum-row.html Regards, Benjamin. On Tue, May 28, 2002 at 12:05:19PM +0100, [EMAIL PROTECTED] wrote: I'm a comparative MySQL newbie who is struggling with sub-selects. In other DBMS's, I can use sub-selects to return a set that I can use in a condition of a WHERE clause. An example is the recent query by Ciprian Trofin about finding the cheapest price of a product. In other DBMS's I would use: SELECT a.id, a.product, a.price, a.store FROM mytable a WHERE a.price = ALL ( SELECT b.price FROM mytable b WHERE a.id = b.id ) However, this doesn't work in MySQL. I've tried using the LEAST function instead of ALL, which triggered an exception at the sub-select. The MySQL docs say that sub-selects are due for implementation in 4.x. In the meantime, is there a way to emulate the preceding statement that won't cause concurrency problems? TIA, -- Geoff Lane -- [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: Multi-Multi Select
Hi. Presuming you have the team names in a seperate table, you could write something like: SELECT home.name, away.name, winner.name FROM games, teams AS home, teams AS away, teams AS winner WHERE home.id = games.homeid AND away.id = games.awayid AND winner.id = games.winnderid AND whatever needed to get the 15 games in question If that does not help, you have to show us your table layout at least with the queries you are using currently. Regards, Benjamin. On Tue, May 28, 2002 at 08:16:44AM -0400, [EMAIL PROTECTED] wrote: I'm sort of new/rusty to SQL and I was trying to figure out the best way to do this problem. I'm creating a database for tracking a football pool. I'm kind of stuck on displaying the Games database. The Games database has a home team ID and an away team ID as well as the winning team ID. Thus I need to do three lookups per record on the Teams database to get/display the full name of the teams for viewing. Is the best way to do this is by using temporary tables for merging? There are about 15 games that need to be displayed at once. I'm using PHP for the interface to MySQL, so I know I could also do it using arrays. But I would presume I would want to stay away from doing 45+ selects (3 x 15). Any ideas/advice are appreciated. -- [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: Fulltext search - several tables
You will get better response if you post only once, and NOT as a reply to someone else's question. Peter Engström wrote: Hello everybody! How can I do a fulltext search on several tables with just one SQL query? Best regards /Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Binary File in DB vs. File in directory --looking for archived discussion
I'm trying to find any and all thoughts on whether to store files in the db (PDF's, word docs etc) or in the filesystem. If I do in the fs then there is tons of overhead because of new folders, each client having thier own folder(and subfolders) etc. And then htaccess'ing the dir's independently along with the overhead of keeping track of the htaccess files. I admit php has good classes for htaccess stuff, but the intergration between apache and php security isn't at that level yet. Right now I'm developing on my own server, but it will be moved to a hosting co, so I'm not sure if they will have mod_auth_mysql ,probably not due to the limitations for required settings in the conf file. So I started developing with the htaccess stuff, and am now pondering the use of the db to store the files, I did some searching and found several people saying there was a huge discussion on the mailing list about the middle of 2001, but I've been unable to find it. Does anyone know where this is? Can anyone throw out a few drawbacks/advantages other than the ones I'm thinking of? Will anyone elighten me? Thank you, Dan 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
SET SQL_SLAVE_SKIP_COUNTER=
Can someone tell me what the correct syntax for this is? SET SQL_SLAVE_SKIP_COUNTER=n Skip the next n events from the master. Only valid when the slave thread is not running, otherwise, gives an error. Useful for recovering from replication glitches. mysql show slave status\G *** 1. row *** Master_Host: dbserver.quickbusiness.com Master_User: repslave2 Master_Port: 3306 Connect_retry: 60 Log_File: QBSLXDB1-bin.044 Pos: 2753978 Slave_Running: No Replicate_do_db: qbslive Replicate_ignore_db: 1 row in set (0.00 sec) mysql SET SQL_SLAVE_SKIP_COUNTER=1; ERROR 1064: You have an error in your SQL syntax near 'SQL_SLAVE_SKIP_COUNTER=1' at line 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Visualizing the shema of the db
Greetings from Nairobi, Kenya Try MySql Front You can download it from www.mysqlfront.de Kind regards Njamba - Original Message - From: andy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 27, 2002 4:48 PM Subject: Visualizing the shema of the db Hi there, I am wondering if there is a software (free or shareware) which is visualizing the shema (table strukture) of the mysql database. thanx for any hint, Andy 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 - This email and any files transmitted with it are confidential and intended solely for the use of the addressee. If you have received this email in error you may not disclose, copy, distribute or act upon its contents without the authors prior written consent. Please can you notify the system manager at [EMAIL PROTECTED] immediately and delete this email. - Any views or opinions expressed are those of the author and do not necessarily represent those of Somak Travel Limited, Kenya. - This footnote also confirms that this email message has been swept for the presence of computer viruses. No responsibility can be accepted in the event that a virus is contained within it or any attachments. - SOMAK TRAVEL LTD - KENYA NAIROBI, PO BOX 48495. Tel: +(2542) 557832 Fax:+(2542) 535175 MOMBASA, PO BOX 90738. Tel: +(25411)486197 Fax:+(25411)487324 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
- Foreign key constraints
Hi all! Question. I try to understand with 4.3 Foreign key constraints InnoDB Engine in MySQL-Max-3.23.50/MySQL-4.0.1. I use MySQL Max (InnoDB) 3.23.49 for Win2000 I have created two tables: CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE ) TYPE=INNODB; As I have understood, this design in cascade allows to delete record child table at removal(distance) of the line connected to it(her) parent table. Or in other words to organize cascade removal(distance) of a line in child table at removal(distance) of the connected line in parent table. Problem in the following. At attempt of removal(distance): delete from parent where id = 1; There is a mistake: Error: Cannot delete a parent row: a foreign key constraint: fails. Probably it is connected with blocking? I do not understand. How to remove a line from parent table so that the line child table has in cascade left connected on a key parent_id? -- Best regards, maxim mailto:[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: Multi-Multi Select
That works perfectly. I did not know that you could alias a table name multiple times to get multiple distinct selects out of it. Simple yet elegant. Thanks. Hi. Presuming you have the team names in a seperate table, you could write something like: SELECT home.name, away.name, winner.name FROM games, teams AS home, teams AS away, teams AS winner WHERE home.id = games.homeid AND away.id = games.awayid AND winner.id = games.winnderid AND whatever needed to get the 15 games in question If that does not help, you have to show us your table layout at least with the queries you are using currently. Regards, Benjamin. On Tue, May 28, 2002 at 08:16:44AM -0400, [EMAIL PROTECTED] wrote: I'm sort of new/rusty to SQL and I was trying to figure out the best way to do this problem. I'm creating a database for tracking a football pool. I'm kind of stuck on displaying the Games database. The Games database has a home team ID and an away team ID as well as the winning team ID. Thus I need to do three lookups per record on the Teams database to get/display the full name of the teams for viewing. Is the best way to do this is by using temporary tables for merging? There are about 15 games that need to be displayed at once. I'm using PHP for the interface to MySQL, so I know I could also do it using arrays. But I would presume I would want to stay away from doing 45+ selects (3 x 15). Any ideas/advice are appreciated. -- 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: timestamp(8) GROUP BY problem
try timestamp(14) instead. [EMAIL PROTECTED] wrote: Description: Problem with timestamp(8) and GROUP BY How-To-Repeat: mysql CREATE TABLE test ( - id int auto_increment, - dd timestamp(8), - data int, - PRIMARY KEY (id) - ); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO test (data) VALUES(1),(2),(3),(4); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql SELECT * FROM test; ++--+--+ | id | dd | data | ++--+--+ | 1 | 20020527 |1 | | 2 | 20020527 |2 | | 3 | 20020527 |3 | | 4 | 20020527 |4 | ++--+--+ 4 rows in set (0.00 sec) mysql SELECT dd,count(*) FROM test GROUP BY dd; +--+--+ | dd | count(*) | +--+--+ | 20020527 |4 | +--+--+ 1 row in set (0.00 sec) mysql UPDATE test SET dd='2002052701' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql UPDATE test SET dd='2002052702' WHERE id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql UPDATE test SET dd='2002052703' WHERE id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql UPDATE test SET dd='2002052704' WHERE id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql SELECT dd,count(*) FROM test GROUP BY dd; +--+--+ | dd | count(*) | +--+--+ | 20020527 |1 | | 20020527 |1 | | 20020527 |1 | | 20020527 |1 | +--+--+ 4 rows in set (0.00 sec) 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.49a-max-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket/tmp/mysql.sock Uptime:65 days 4 hours 9 min 10 sec Threads: 6 Questions: 69973912 Slow queries: 688 Opens: 129933 Flush tables: 3 Open tables: 506 Queries per second avg: 12.427 Environment: System: Linux gap 2.4.18 #3 SMP Fri Mar 15 14:40:03 EET 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1384040 Dec 18 18:24 /lib/libc.so.6 -rw-r--r--1 root root 25214756 Dec 18 18:07 /usr/lib/libc.a -rw-r--r--1 root root 178 Dec 18 18:07 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-innodb --with-berkeley-db --enable-thread-safe-client --with-other-libc=/usr/local/mysql-glibc '--with-comment=Official MySQL Binary' --prefix=/usr/local/mysql --with-extra-charset=complex --enable-thread-safe-client --enable-local-infile --with-server-suffix=-max 'CFLAGS=-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-r! tti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX=gcc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check:
@INC
I'm a little confused on how to add things to @Inc path...i'm trying to install Msql-Mysql-modules and it says that my DBI/DBD is not in the @INC Thanks Amy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
VB Code
I am new to transactions in mySql I am using Visual Basic to access a mySql database. I had seen somewhere that to use transactions through ODBC you have to send the commit and rollback commands like a select statement. But even when I send the begin work before my other statements, the commit is till done even if I issue a rollback statement. I can be able to use transactions when working from the mysql command prompt. But I want to use the same in Visual Basic to be able to prevent saving when halfway through my statements. I have put a set autocommit=0 at the start of my code. But still nothing doing Someone give me some sample VB code to connect to mySql and use transactions Thanx a heap Harrison Njamba Somak Travel Limited P.O. Box 48495 Tel 535509 / 0733-923214 Nairobi Kenya - This email and any files transmitted with it are confidential and intended solely for the use of the addressee. If you have received this email in error you may not disclose, copy, distribute or act upon its contents without the authors prior written consent. Please can you notify the system manager at [EMAIL PROTECTED] immediately and delete this email. - Any views or opinions expressed are those of the author and do not necessarily represent those of Somak Travel Limited, Kenya. - This footnote also confirms that this email message has been swept for the presence of computer viruses. No responsibility can be accepted in the event that a virus is contained within it or any attachments. - SOMAK TRAVEL LTD - KENYA NAIROBI, PO BOX 48495. Tel: +(2542) 557832 Fax:+(2542) 535175 MOMBASA, PO BOX 90738. Tel: +(25411)486197 Fax:+(25411)487324 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
privilege problem
I am having a problem with a user having the correct privleges in mysql. The user cannot connect to get access to the database, but everything looks correct to me. User table... Host User Password Privs localhostuser1password Only Y is for File_priv ip_addr user1password Only Y is for File_priv db table Host DBUserSelect Insert Update Delete Create Drop Grant References Index Alter % some-db user1Y Y Y NY N N N Y N I think I am missing something fairly obvious, but Im not sure what it is. Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: SET SQL_SLAVE_SKIP_COUNTER=
It is kinda old (3.23.32), but I did set some kind of skip counter before, just can't remeber how I did it. -Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 9:50 AM To: [EMAIL PROTECTED] Subject: Re: SET SQL_SLAVE_SKIP_COUNTER= In article [EMAIL PROTECTED], Luc Foisy [EMAIL PROTECTED] writes: mysql SET SQL_SLAVE_SKIP_COUNTER=1; ERROR 1064: You have an error in your SQL syntax near 'SQL_SLAVE_SKIP_COUNTER=1' at line 1 MySQL version too old? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
List problems
Good morning, I'm trying to send an email to the list but this happens. Im not sure if this is because it was an html email but I kept getting this: ** Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: SSdtIHRyeWluZyB0byBmaW5kIGFueSBhbmQgYWxsIHRob3VnaHRzIG9uIHdoZXRoZXIgdG8gc3Rv cmUgZmlsZXMgaW4gdGhlIGRiIChQREYncywgd29yZCBkb2NzIGV0Yykgb3IgaW4gdGhlIGZpbGVz eXN0ZW0uIElmIEkgZG8gaW4gdGhlIGZzIHRoZW4gdGhlcmUgaXMgdG9ucyBvZiBvdmVyaGVhZCBi ZWNhdXNlIG9mIG5ldyBmb2xkZXJzLCBlYWNoIGNsaWVudCBoYXZpbmcgdGhpZXIgb3duIGZvbGRl cihhbmQgc3ViZm9sZGVycykgZXRjLiBBbmQgdGhlbiBodGFjY2VzcydpbmcgdGhlIGRpcidzIGlu ZGVwZW5kZW50bHkgYWxvbmcgd2l0aCB0aGUgb3ZlcmhlYWQgb2Yga2VlcGluZyB0cmFjayBvZiB0 aGUgaHRhY2Nlc3MgZmlsZXMuIEkgYWRtaXQgcGhwIGhhcyBnb29kIGNsYXNzZXMgZm9yIGh0YWNj ZXNzIHN0dWZmLCBidXQgdGhlIGludGVyZ3JhdGlvbiBiZXR3ZWVuIGFwYWNoZSBhbmQgcGhwIHNl Y3VyaXR5IGlzbid0IGF0IHRoYXQgbGV2ZWwgeWV0LiBSaWdodCBub3cgSSdtIGRldmVsb3Bpbmcg b24gbXkgb3duIHNlcnZlciwgYnV0IGl0IHdpbGwgYmUgbW92ZWQgdG8gYSBob3N0aW5nIGNvLCBz byBJJ20gbm90IHN1cmUgaWYgdGhleSB3aWxsIGhhdmUgbW9kX2F1dGhfbXlzcWwgLHByb2JhYmx5 IG5vdCBkdWUgdG8gdGhlIGxpbWl0YXRpb25zIGZvciByZXF1aXJlZCBzZXR0aW5ncyBpbiB0aGUg Y29uZiBmaWxlLg0KDQoJU28gSSBzdGFydGVkIGRldmVsb3Bpbmcgd2l0aCB0aGUgaHRhY2Nlc3Mg c3R1ZmYsIGFuZCBhbSBub3cgcG9uZGVyaW5nIHRoZSB1c2Ugb2YgdGhlIGRiIHRvIHN0b3JlIHRo ZSBmaWxlcywgSSBkaWQgc29tZSBzZWFyY2hpbmcgYW5kIGZvdW5kIHNldmVyYWwgcGVvcGxlIHNh eWluZyB0aGVyZSB3YXMgYSBodWdlIGRpc2N1c3Npb24gb24gdGhlIG1haWxpbmcgbGlzdCBhYm91 dCB0aGUgbWlkZGxlIG9mIDIwMDEsIGJ1dCBJJ3ZlIGJlZW4gdW5hYmxlIHRvIGZpbmQgaXQuIERv ZXMgYW55b25lIGtub3cgd2hlcmUgdGhpcyBpcz8gQ2FuIGFueW9uZSB0aHJvdyBvdXQgYSBmZXcg ZHJhd2JhY2tzL2FkdmFudGFnZXMgb3RoZXIgdGhhbiB0aGUgb25lcyBJJ20gdGhpbmtpbmcgb2Y/ DQoNCglXaWxsIGFueW9uZSBlbGlnaHRlbiBtZT8NCg0KCVRoYW5rIHlvdSwNCg0KCURhbg0KDQoJ c3FsLCBxdWVyeQ0KCQ0KDQo= (Obviously I did not write that) Another time I got this: * Your message cannot be postsed to mysql@ because it contained an attachment. If you need to make a file publically available you should upload it to some public URL, and then paste the URL into your message instead of trying to send it to everyone on the list. blah blah blah ** And when I used a plain text email, i got this: *** Sorry. Your message could not be delivered to: mailing list (Mailbox or Conference is full.) *** Is the mailing list having problems (cause I'm getting email), or is it just me? Thanks Dan VandeMore _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Server speed working with databases and apache
I think any descent server would be fine: Linux/FreeBSD dual PIII 512MB ram (or better 1GB) 2 scsi drives with raid1 Frankly, you could even get cheaper than that with 1 processor. -Original Message- From: Javier Armendáriz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 3:33 AM To: [EMAIL PROTECTED] Subject: Server speed working with databases and apache Hi everybody: I´m looking for some information about server speed. The problem: I need a server for about 40 concurren users, it will be in an internal net, the server will manage a mysql database with a master table of about 4000 records and some chil tables from 2 to 4 records, it will be accesed trougth apache with php. I need a response time about 0.1-0.2 seconds. Is there some information or good article about this issue What kind of machine would be better? Can anybody give me some information or reference??? Thanks a lot Sorry about my english Javier Armendáriz [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: Foreign Keys with MySQL 3.23.49 and 3.23.50
This example is in InnoDB though, which should support foreign key constraints. Chris Book In other words data integrity between two tables is not ensured by MySQL. Your application code must solve this. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Sub-selects for WHERE clauses
On 28 May 2002, 14:20, Benjamin Pflugmann wrote: That's an FAQ and explained in the tutorial: http://www.mysql.com/doc/e/x/example-Maximum-row.html ~~~ Thanks for the link, which I've bookmarked. Thanks also to those that responded off-list. Unfortunately, the approach given in the tutorial doesn't work everywhere that a sub-select is required. For example, in one case I have two tables: tblmain (id, ...) tblcount (id, counter) where tblcount.id is a foreign key referencing tblmain.id tblcount exists to record the number of times something happens to each record in tblmain. At the start of operations, tblcount is empty. When the something happens to records in tblmain that meet certain criteria, I need to increment tblcount.counter, creating new tblcount records where necessary. Now, I can use the same condition to determine the result table from tblmain and to determine the related records in tblcount. In other DBMS's I would issue three statements in transaction: SELECT * FROM tblmain WHERE condition; UPDATE tblcount SET counter=counter+1 WHERE id IN (SELECT id FROM tblmain WHERE condition); INSERT INTO tblcount (id, count) SELECT (id, 1 FROM tblmain WHERE condition AND id NOT IN (SELECT id FROM tblcount); However, thanks to off-list correspondence, I now know that I need to do some research into temporary tables because these can provide most (if not all) the functionality of sub-selects. Thanks again to all, -- Geoff Lane Cornwall, UK [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: @INC
You need to install DBI first. Amy Zediak wrote: I'm a little confused on how to add things to @Inc path...i'm trying to install Msql-Mysql-modules and it says that my DBI/DBD is not in the @INC Thanks Amy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: timestamp(8) GROUP BY problem
sitnikov, Monday, May 27, 2002, 10:32:07 PM, you wrote: s Description: s Problem with timestamp(8) and GROUP BY s How-To-Repeat: mysql CREATE TABLE test ( s - id int auto_increment, s - dd timestamp(8), s - data int, s - PRIMARY KEY (id) s - ); s Query OK, 0 rows affected (0.01 sec) [skip] TIMESTAMP(8) is a display size, not the size of field. If you change column to TIMESTAMP(14) you can see that you have different values for dd. mysql alter table test change dd dd timestamp(14); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql SELECT dd,count(*) FROM test GROUP BY dd; ++--+ | dd | count(*) | ++--+ | 2002052701 |1 | | 2002052702 |1 | | 2002052703 |1 | | 2002052704 |1 | ++--+ 4 rows in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: - Foreign key constraints
maxim, Tuesday, May 28, 2002, 4:47:13 PM, you wrote: m Question. I try to understand with 4.3 Foreign key constraints m InnoDB Engine in MySQL-Max-3.23.50/MySQL-4.0.1. m I use MySQL Max (InnoDB) 3.23.49 for Win2000 m I have created two tables: m CREATE TABLE parent (id INT NOT NULL, m PRIMARY KEY (id)) TYPE=INNODB; m CREATE TABLE child (id INT, parent_id INT, m INDEX par_ind (parent_id), m FOREIGN KEY (parent_id) REFERENCES parent (id) m ON DELETE CASCADE m ) TYPE=INNODB; m As I have understood, this design in cascade allows to delete record m child table at removal(distance) of the line connected to it(her) parent table. m Or in other words to organize cascade removal(distance) of a line in child m table at removal(distance) of the connected line in parent table. m Problem in the following. At attempt of removal(distance): m delete from parent where id = 1; m There is a mistake: Error: Cannot delete a parent row: a foreign key m constraint: fails. ON DELETE CASCADE have worked since 3.23.50, 3.23.49 doesn't support ON DELETE CASCADE. So, you should first delete rows from child table that have parent_id=1 and then delete row from parent table. m Probably it is connected with blocking? I do not understand. How to remove m a line from parent table so that the line child table has in cascade left m connected on a key parent_id? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: INSERT IF NOT EXISTS?
Markus, Tuesday, May 28, 2002, 2:15:09 PM, you wrote: You can use IGNORE keyword in INSERT statement, but it's not quite the same as INSERT IF NOT EXIST. If you specify IGNORE, any rows that duplicate an existing PRIMARY or UNIQUE key in the table will be ignored in INSERT: ML Yes, I was aware of the INSERT IGNORE statement, but I've only got one ML primary/unique key, which happens to be the id. ML Perhaps I should elaborate further. ML I've got three tables; ip_name_tbl, loc_tbl and conn_tbl. ML ip_name_tbl has got fields id, comp_loc, ip_stat_dyn, ip, ML mac, network, name and comments. ML loc_tbl has got fileds id, comp_loc and loc_name and ML conn has got fileds id, wall_nr, hub_switch_nr, comp_id. ML ip_name_tbl.comp_loc points to loc_tbl.comp_loc=20 ML conn.comp_id points to ip_name_tbl.id. ML I want to be sure that while inserting values, say ML NULL (since id is AUTO_INCREMENT), 1 and Administration, ML administration isn't already in the table. ML Rob's idea is of course a solution, but that sort of brings me back to ML my original question : is there a -SIMPLE- way? ; ) Nope. You can't do it with one SQL statement ... ML INSERT IGNORE wouldn't, if I understood the manual correctly, ML help me here, since the other rows in the table aren't UNIQUE, no? If your column for value Administration is not indexed as PRIMARY or UNIQUE key, INSERT IGNORE is not that you want ... ML Cheers, ML Markus -- 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
AW: Foreign Keys with MySQL 3.23.49 and 3.23.50
Hello, excuse me. I've looked at the wrong place. I found the solution at www.innodb.com: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; This examples works great. Heiner Jostkleigrewe -Ursprüngliche Nachricht- Von: Harald Fuchs [SMTP:[EMAIL PROTECTED]] Gesendet am: Dienstag, 28. Mai 2002 12:46 An: [EMAIL PROTECTED] Betreff: Re: Foreign Keys with MySQL 3.23.49 and 3.23.50 In article [EMAIL PROTECTED], Jostkleigrewe, Heiner [EMAIL PROTECTED] writes: sql,query Hello, i've tried the foreign key-example out of the documentation(3.5.6 Using Foreign Keys) on W2K (3.23.49 + 3.23.50) and LINUX (3.23.49). The tables persons and shirts are created as innodb-tables. I could insert a shirt for a non-existing person. Also i could delete a person with existing shirts. I have experimented with 'ON DELETE' and 'ON UPDATE' with no effects. What's wrong? Is there a switch to activate foreign keys? No. Could you post some SQL code which lets us reproduce the problem? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: @INC
This is OT, perhaps post to comp.lang.perl.*? Ric p.s. try use DBI or use lib qw(/path/to/module) - Original Message - From: Amy Zediak [EMAIL PROTECTED] To: MySql List [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 3:01 PM Subject: @INC I'm a little confused on how to add things to @Inc path...i'm trying to install Msql-Mysql-modules and it says that my DBI/DBD is not in the @INC Thanks Amy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Foreign key constraints
Hi all! Question. I try to understand with 4.3 Foreign key constraints InnoDB Engine in MySQL-Max-3.23.50/MySQL-4.0.1. I use MySQL Max (InnoDB) 3.23.49 for Win2000 I have created two tables: CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE ) TYPE=INNODB; As I have understood, this design in cascade allows to delete record child table at removal(distance) of the line connected to it(her) parent table. Or in other words to organize cascade removal(distance) of a line in child table at removal(distance) of the connected line in parent table. Problem in the following. At attempt of removal(distance): delete from parent where id = 1; There is a mistake: Error: Cannot delete a parent row: a foreign key constraint: fails. Probably it is connected with blocking? I do not understand. How to remove a line from parent table so that the line child table has in cascade left connected on a key parent_id? -- Best regards, maxim mailto:[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: killing mysql-threads?
Hi, I must tell you something for nothing you try to kill the user, because if he try twice to execute some query, MySQL 1-st time respond something like lost connction or Server is gone and the 2-nd time recovery the last connection. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Anatol Mayr / HEXAGON [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 12:26 PM Subject: killing mysql-threads? Is it possible to set a variable to kill mysql-threads which sleeps for a time e.g. 5 hours? Which variables have to be set therefore? Regards, Anatol - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[2]: Join or smth.
select id, product, min(price), store from tablename group by product Better? Regards Chris - Original Message - From: Ciprian Trofin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 1:24 PM Subject: Re[2]: Join or smth. Doesn't work the way I want to: I want the result to be like: id | product | price | store 1 | p1 | 100 | s1 3 | p1 | 100 | s3 5 | p2 | 95 | s2 7 | p3 | 100 | s1 CL select * from table_name where product like 'p1' order by price limit 1; I have a table that looks smth. like this: id product price store -- 1 p1100s1 2 p1120s2 3 p1100s3 4 p2120s1 5 p2 95s2 6 p2300s3 7 p3100s1 8 p3120s2 9 p3125s3 10 p3130s1 CREATE TABLE `products` ( `id` tinyint(3) unsigned NOT NULL auto_increment, `product` varchar(4) NOT NULL default '0', `price` smallint(3) unsigned NOT NULL default '0', `store` varchar(4) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM; # # Dumping data for table 'products' # INSERT INTO products VALUES(1,p1,100,s1); INSERT INTO products VALUES(2,p1,120,s2); INSERT INTO products VALUES(3,p1,110,s3); INSERT INTO products VALUES(4,p2,120,s1); INSERT INTO products VALUES(5,p2,95,s2); INSERT INTO products VALUES(6,p2,300,s3); INSERT INTO products VALUES(7,p3,100,s1); INSERT INTO products VALUES(8,p3,120,s2); INSERT INTO products VALUES(9,p3,125,s3); INSERT INTO products VALUES(10,p3,130,s1); I want to build a query to find out where I can find the least expensive product. I know how to solve the problem using a script language (PHP) but I'd like to do it SQL-style :). I guess it can be done using some sort of JOIN. -- Ciprian Un cuvant de sfarsit: Useless fact: Odds of being killed in a car crash are 1 in 5000. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: SET SQL_SLAVE_SKIP_COUNTER=
I am sure I did something to set the skip counter, it might not have been that command, but I am sure I did it somehow at the direction of someone else on the list when I had problems before -Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 10:56 AM To: [EMAIL PROTECTED] Subject: Re: SET SQL_SLAVE_SKIP_COUNTER= In article [EMAIL PROTECTED], Luc Foisy [EMAIL PROTECTED] writes: MySQL version too old? It is kinda old (3.23.32), but I did set some kind of skip counter before, just can't remeber how I did it. Are you sure? The manual says that SQL_SLAVE_SKIP_COUNTER was added in release 3.23.33. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: JOINS: MySQL Query Speed
* [EMAIL PROTECTED] I have a question on how MySQL JOIN has effect on query (search) performance: Our database consists of about 250.000 datasets (rows). Right now, all of the 150 columns are in one big table. However, we do have certain columns that are empty for most rows (for example information of the status of pictures of customer). Out of the 250.000, only 20.000 have information on photo-status. The idea is to put the columns (amount: 5) about the photo-status in a seperate table to not blow up the database (to normalize the database ?). This is called database normalization, yes. A search over, lets say, 15 columns, that includes one columns out of the photo-status-columns would then have to JOIN these two tables. Does that lead to performance drop ? Probably not, because the amount of data to process is smaller (Your 230.000 x 5 empty photo-status columns are gone) and the join lookup is (should be) indexed. Or is maybe even faster to search with a JOIN, because we only have 20.000 rows in that seperate photo-status-table ? Eh... right. It is probably faster with the join. :) What would probably happen if we did this with, lets say, 5 other data-sets (we would the have 7 tables connected through JOIN) ? The query would probably be even faster. :) There is an upper limit on how many joins you can do in a single statement, but this is rarely a problem. (32 in version 3.22 and 64 in version 3.23, according to crashme.) I hope someone can help me with performance issues about JOIN. As long as all joined tables have an index (normally you join using the primary key, which is a perfectly good index, but any column/combination of columns could be used, just make sure the used columns are indexed!) there are (afaik) no performance issues with JOIN. On the contrary: most mysql databases (of some size) will be faster when normalized. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
numbered tables in mysql
I am attempting to migrate a MySQL driven web application that, in my opinion, was designed by a stupid monkey. The php web frontend dynamically creates tables to store chunks of data rather than just adding rows to existing tables. Don't ask me why! However, when attempting to migrate the entire database to another system via mysqldump, mysql gives an error which I assume is associated with the tables being named by number rather than by name. The error is as follows: ./mysqldump: Can't get CREATE TABLE for table '17' (You have an error in your SQL syntax near '17' at line 1) Also, the only way to select data from one of the tables is to use ticks in the from statement: mysql use MYSTUPIDDATABASE; mysql show tables; ... ... ... 2777 rows in set... -- in other words, there are 2777 dynamically created tables, all named by numbers mysql select * from 94; -- without ticks ERROR 1064: You have an error in your SQL syntax near '94' at line 1 mysql select * from `94`; -- with ticks that works. however, how do I make it so mysqldump can build the create statement correctly for these tables? Augey Mikus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Batching sql statements
I want to write a batch file of sql statements so that I can schedule this batch file to run at night automatically and send the sql commands to a mysql database on a Win2K server. Does anybody have any experience doing this? Any pointers in the right direction would be much appreciated. Tab - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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_pconnect doesn/t work in php
i use mysql_pconnect in php, but after call mysql_pconnect every time,i find that connection increase one.it says in php manual, mysql_pconnect will first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection. but why open a new connection every time in my program? __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.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: numbered tables in mysql
I seem to have answered my own question :-) The answer is that you have to use -Q or --quote-names in mysqldump Cheers! Augey Mikus Augey Mikus wrote: I am attempting to migrate a MySQL driven web application that, in my opinion, was designed by a stupid monkey. The php web frontend dynamically creates tables to store chunks of data rather than just adding rows to existing tables. Don't ask me why! However, when attempting to migrate the entire database to another system via mysqldump, mysql gives an error which I assume is associated with the tables being named by number rather than by name. The error is as follows: ./mysqldump: Can't get CREATE TABLE for table '17' (You have an error in your SQL syntax near '17' at line 1) Also, the only way to select data from one of the tables is to use ticks in the from statement: mysql use MYSTUPIDDATABASE; mysql show tables; ... ... ... 2777 rows in set... -- in other words, there are 2777 dynamically created tables, all named by numbers mysql select * from 94; -- without ticks ERROR 1064: You have an error in your SQL syntax near '94' at line 1 mysql select * from `94`; -- with ticks that works. however, how do I make it so mysqldump can build the create statement correctly for these tables? Augey Mikus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Batching sql statements
The mysql command line client can accept batch files as: mysql -uuser -ppassword database file.sql file.sql is just a file with the plain text SQL statements in it that you want to run. Then since you are on Windows 2000, you could setup Task Scheduler to run it every night at whatever time you specify. Shouldn't be too hard at all. Good luck. Harrison - Original Message - From: Tab Alleman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 12:21 PM Subject: Batching sql statements I want to write a batch file of sql statements so that I can schedule this batch file to run at night automatically and send the sql commands to a mysql database on a Win2K server. Does anybody have any experience doing this? Any pointers in the right direction would be much appreciated. Tab - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Error 1114 when import mysqldump in InnoDB table
Hi all. I import a mysqldump from MyISAM database in a new innodb database. It gives me an error saying that the table is full ERROR 1114 at line 3035: The table 'LALBARANPR' is full These are the values for the innodb variables in the my.cnf file: innodb_data_file_path = ibdata1:2000M;ibdata2:2000M innodb_data_home_dir = /mysql/ innodb_log_group_home_dir = /mysql/ innodb_log_arch_dir = /mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 What could I do? Thanks in advance. Iago. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: timestamp(8) GROUP BY problem
On 27 May 2002, at 22:32, [EMAIL PROTECTED] wrote: mysql SELECT dd,count(*) FROM test GROUP BY dd; +--+--+ | dd | count(*) | +--+--+ | 20020527 |1 | | 20020527 |1 | | 20020527 |1 | | 20020527 |1 | +--+--+ 4 rows in set (0.00 sec) The 8 in timestamp(8) affects only the display. The other digits are still there internally, so the values are still distinct when you attempt to group them. Perhaps what you want is something like SELECT LEFT(dd, 8), COUNT(*) GROUP BY LEFT(dd, 8); Also, if you're not using the automatic-update property of TIMESTAMP, you might want to use a DATE or DATETIME column instead. [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
warning: 1 clients is using or hasn't closed the table properly
I get this message when I do a myisamchk on a table right after I use phpMyAdmin or other mysql client tools. What do I need to do to get the table to close properly? I have been having to run myisamchk -r on the talbe every time I do something to it. Thanks in advance, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
msql.h
Where is msql.h located? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A Date and Select question.
My SQL query problem: I met a strange problem: For example, I have the following table: Mytable: No Name Date Project 1 Bob 2002-05-27Bob's project 2 John -00-00 John's project When I use select * from Mytable WHERE Date = 'WWW'; it gives me the result: 2 John -00-00 John's project I think it should not return any values. Why? Thanks. I. TS. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Date and Select problem
My SQL query problem: I met a strange problem: For example, I have the following table: Mytable: No Name Date Project 1 Bob 2002-05-27Bob's project 2 John -00-00 John's project When I use select * from Mytable WHERE Date = 'WWW'; it gives me the result: 2 John -00-00 John's project I think it should not return any rows. Why? Thanks. I. TS - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Delete Problem
Hello everyone.
I have an issue that can't figure out, and was wondering if someone can help
me with it.
I offer mySQL based e-mail account to my users.
The actual e-mails are stored in a text based directory.
Sometimes when the e-mail is put in the trashcan, it actually deletes the
e-mail folder, so when the user goes to clear the trash, it doesn't find the
referred folder/file, and gives ISE.
Looking at the below subroutine, can anyone please tell me what I can do to
fix this?
I basically want to check for the e-mail directory or the file, and if it
doesn't exist, simply ignore it, and delete the ID from the database.
Thanks so much
sub deleteemails {
my $self = shift;
my $emails = shift;
my $whereE = undef;
my $whereA = undef;
# print Content-Type: Text/Html\n\n;
foreach my $id (@$emails) {
if ((! $id) || ($id =~ /\D/)) {next;}
my $qid = $self-{_dbobj}-quote($id);
if ($self-{_StoragePath} !~ /\w/) { die(StoragePath does not contain
a
useful value) }
#deletes all files in EmailID Directory (Email.txt and all attachment
files)
my $EmailDir = $self-{_StoragePath}/$self-{_UserID}/$id;
if (! -d $EmailDir) { die($EmailDir is not a directory); return}
opendir (EMAIL, $EmailDir) or die(Can't open directory $EmailDir
$!),
return;
while(my $files = readdir(EMAIL)) {
if ($files =~ /\w/) {
if (! -e $EmailDir/$files) { die($EmailDir/$files
does not exist) }
unlink $EmailDir/$files or die(Can't delete file:
$EmailDir/$files
$!);
$whereE .= EmailID = $qid OR ;
}
}
closedir(EMAIL);
rmdir($EmailDir) or die(Can't delete directory: $EmailDir $!);
}
$whereE =~ s/ OR $//;
if ($whereE){
my $UserID = $self-{_dbobj}-quote($self-{_UserID});
my $query = DELETE FROM EMAILS WHERE $whereE AND UserID = $UserID;
my $sdb = $self-{_dbobj}-prepare($query);
$sdb-execute;
$query = DELETE FROM ATTACHMENTS WHERE $whereE;
$sdb = $self-{_dbobj}-prepare($query);
$sdb-execute;
}
}
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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 1114 when import mysqldump in InnoDB table
Hi, the internal space for innodb is full... create new extends (add another ibdata file and restart the mysql) to see how much space is free, execute the command: show table status like 'yourtableinnodb' Iago Sineiro wrote: Hi all. I import a mysqldump from MyISAM database in a new innodb database. It gives me an error saying that the table is full ERROR 1114 at line 3035: The table 'LALBARANPR' is full These are the values for the innodb variables in the my.cnf file: innodb_data_file_path = ibdata1:2000M;ibdata2:2000M innodb_data_home_dir = /mysql/ innodb_log_group_home_dir = /mysql/ innodb_log_arch_dir = /mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 What could I do? Thanks in advance. Iago. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- -- \|/ __\|/ `@ / o . \ @' Microsoft? Por acaso é alguma nova /___| \/ |___\ marca de papel higiênico? \___U__/ .^. Luciano Barcaro - Linux User # 99517 /v\ Registre-se gratuitamente em // \\http://counter.li.org //(.)\\ ICQ # 17266954 ^`~'^ -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: msql.h
or mysql.h?...is in your_src_directory/include - Original Message - From: Amy Zediak [EMAIL PROTECTED] To: MySql List [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 7:59 PM Subject: msql.h Where is msql.h located? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Interesting datetime problem
Hi, I have an interesting problem when updating columns of type DATETIME. It seems that exactly one day is subtracted from the DATETIME value that I submit in an update query. Has anyone encountered this? Any ideas? Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Date and Select problem
Hehe, this is almost identical to the post I submitted yesterday, entitled SELECT 'foobar' = 0 There's a patch in that post, doesn't work for dates but should give an idea of what has to be done. The reason is that 'WWW' has to be converted to a date. And the conversion gives it a value of 0. So you're comparing 0 and 0, which results in 'true'. Anyway, that's why it happens :). Adam Hooper [EMAIL PROTECTED] On Tue, 28 May 2002 11:07:08 -0600 I. TS [EMAIL PROTECTED] wrote: My SQL query problem: I met a strange problem: For example, I have the following table: Mytable: No Name Date Project 1 Bob 2002-05-27Bob's project 2 John -00-00 John's project When I use select * from Mytable WHERE Date = 'WWW'; it gives me the result: 2 John -00-00 John's project I think it should not return any rows. Why? Thanks. I. TS - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
error when trying to load large dump files
I've created a dump file using mysqldump that is about 4MB. When I try to load it on another server using mysql database dump.sql I get errors. If I create a smaller dump file (below 1MB) it loads without a problem. I noticed that the default max_allowed_packet size is 1MB, but I've not had much success changing it. Everytime I try to create a my.cnf file to reset the default, mysqld starts and then ends abruptly. I'm pretty sure I don't have any syntax errors in my my.cnf file. Any ideas? Tracy Tracy Kitts Director of Web Services National Business Incubation Association 20 East Circle Drive, Suite 190, Athens, Ohio 45701 (740) 593-4331 phone; (740) 593-1996 fax Visit us virtually at http://www.nbia.org. The National Business Incubation Association advances the business incubation process to increase entrepreneurial success and individual opportunity, strengthening communities worldwide. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.351 / Virus Database: 197 - Release Date: 4/19/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A date problem
Hi, I also have a date problem: My SQL query problem: For example, I have the following table: Mytable: No Name Date Project 1 Bob 2002-05-27Bob's project 2 John -00-00 John's project When I query select * from Mytable WHERE Date = 'WWW'; it gives me the result: 2 John -00-00 John's project think it should not return any rows. Why? Thanks. I. TS - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: msql.h
On 28/5/02 at 12:59, [EMAIL PROTECTED] (Amy Zediak) wrote: Where is msql.h located? On which OS? Regards, Phil. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: A date problem
Since it's a date field, it's a date comparison and the date that you
specify ('WWW') gets parsed by the optimizer as being the date '-00-00'
since it can't find any valid date information. This is different from if
it was to convert the date to a string and do a string comparison, which is
what you were expecting it would seem.
If you try this with the date field actually being a CHAR() field, it will
probably come back with no matches.
Thanks,
Andy Moyer
Jetson Direct Mail Services, Inc.
PHP Developer Linux/MySQL Admin
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: SET SQL_SLAVE_SKIP_COUNTER=
Sigh, sanity check. I did set a skip counter, and you were correct, it was on a newer version ( on a different slave ) That slave server crashed, when we did the reinstall, i reverted back to 3.23.32 ( the same as the rest of or slaves were running ) So that threw me for a loop seeing now that all our slaves are running at that older version I checked back about 12000 messages (2/21/02) and then realized what went down. -Original Message- From: Luc Foisy Sent: Tuesday, May 28, 2002 11:53 AM To: MYSQL-List (E-mail) Subject: RE: SET SQL_SLAVE_SKIP_COUNTER= I am sure I did something to set the skip counter, it might not have been that command, but I am sure I did it somehow at the direction of someone else on the list when I had problems before -Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 10:56 AM To: [EMAIL PROTECTED] Subject: Re: SET SQL_SLAVE_SKIP_COUNTER= In article [EMAIL PROTECTED], Luc Foisy [EMAIL PROTECTED] writes: MySQL version too old? It is kinda old (3.23.32), but I did set some kind of skip counter before, just can't remeber how I did it. Are you sure? The manual says that SQL_SLAVE_SKIP_COUNTER was added in release 3.23.33. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
