Re: Please Help. selectcol_arrayref problem
I did a GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.23' IDENTIFIED BY 'psswd'; on the master. Doesn't *.* mean everything? Why would it just show me to databases? 2013/4/2 Larry Martell larry.mart...@gmail.com On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com wrote: use DBI; my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw, { RaiseError = 3 } ); my $dbs = $dbh-selectcol_arrayref(show databases); #my $dsn = dbi:mysql:information_schema:192.168.0.1:3306; #my $dbh = DBI-connect($dsn, $usrr, $passw); my $dbs = $dbh-selectcol_arrayref('show databases'); print @$dbs\n; When I query the server for a list of databases with the code above it returns the name of just two and there are over 10. Any ideas? Permissions - the user you're logging in as probably only has permission to see the 2 that are being returned.
Re: Please Help. selectcol_arrayref problem
On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com wrote: use DBI; my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw, { RaiseError = 3 } ); my $dbs = $dbh-selectcol_arrayref(show databases); #my $dsn = dbi:mysql:information_schema:192.168.0.1:3306; #my $dbh = DBI-connect($dsn, $usrr, $passw); my $dbs = $dbh-selectcol_arrayref('show databases'); print @$dbs\n; When I query the server for a list of databases with the code above it returns the name of just two and there are over 10. Any ideas? Permissions - the user you're logging in as probably only has permission to see the 2 that are being returned. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Please Help. selectcol_arrayref problem
Nope. That's just granting replication privileges so it can read updates on all tables on all databases. It cannot select anything. Why are you trying to connect with a replication slave user? On 4/2/13 1:47 PM, Richard Reina gatorre...@gmail.com wrote: I did a GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.23' IDENTIFIED BY 'psswd'; on the master. Doesn't *.* mean everything? Why would it just show me to databases? 2013/4/2 Larry Martell larry.mart...@gmail.com On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com wrote: use DBI; my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw, { RaiseError = 3 } ); my $dbs = $dbh-selectcol_arrayref(show databases); #my $dsn = dbi:mysql:information_schema:192.168.0.1:3306; #my $dbh = DBI-connect($dsn, $usrr, $passw); my $dbs = $dbh-selectcol_arrayref('show databases'); print @$dbs\n; When I query the server for a list of databases with the code above it returns the name of just two and there are over 10. Any ideas? Permissions - the user you're logging in as probably only has permission to see the 2 that are being returned. Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Please help me.
Hi You can make a backup of the original table and recreate the table and import. That will help you ASAP Muthukumar Selvarasu, Project Manager, Webmaster Ltd. -Original Message- From: Gary Smith [mailto:g...@primeexalia.com] Sent: Thursday, March 19, 2009 7:25 AM To: Valentin Ionescu; mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: RE: Please help me. Velentin, http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html Note the section for the droping of foreign keys used the contraint name, not the key name. Try this and see if it solves the first problem (of removing the constraint). Then you should be able to drop the column after that. From: Valentin Ionescu [colibry...@yahoo.com] Sent: Wednesday, March 18, 2009 11:27 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Please help me. Hi! My name is Valentin and I am writing to you for the following problem: I created a database containing the table: CREATE TABLE `documents_ex` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Journal_ex_ID` int(10) unsigned DEFAULT NULL, `Documents_ID` int(10) unsigned DEFAULT NULL, `Data` datetime DEFAULT NULL, `Nr` varchar(50) DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Journal_ex_ID` (`Journal_ex_ID`,`Documents_ID`,`Data`,`Nr`), KEY `Documents_ID` (`Documents_ID`), CONSTRAINT `documents_ex_ibfk_1` FOREIGN KEY (`Journal_ex_ID`) REFERENCES `journal_ex` (`ID`) ON DELETE CASCADE, CONSTRAINT `documents_ex_ibfk_2` FOREIGN KEY (`Documents_ID`) REFERENCES `documents` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 After some time I decided to drop 'Journal_ex_ID' column and all its environment from this non empty table. All I tried to do like: alter table documents_ex drop column Journal_ex_ID or alter table documents_ex drop foreign key Journal_ex_ID or alter table documents_ex drop key Journal_ex_ID I receive the same error 150 and I don't know what to do. Please help me. Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 To unsubscribe: http://lists.mysql.com/win32?unsub=muthukumar...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Please help me.
Velentin, http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html Note the section for the droping of foreign keys used the contraint name, not the key name. Try this and see if it solves the first problem (of removing the constraint). Then you should be able to drop the column after that. From: Valentin Ionescu [colibry...@yahoo.com] Sent: Wednesday, March 18, 2009 11:27 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Please help me. Hi! My name is Valentin and I am writing to you for the following problem: I created a database containing the table: CREATE TABLE `documents_ex` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Journal_ex_ID` int(10) unsigned DEFAULT NULL, `Documents_ID` int(10) unsigned DEFAULT NULL, `Data` datetime DEFAULT NULL, `Nr` varchar(50) DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Journal_ex_ID` (`Journal_ex_ID`,`Documents_ID`,`Data`,`Nr`), KEY `Documents_ID` (`Documents_ID`), CONSTRAINT `documents_ex_ibfk_1` FOREIGN KEY (`Journal_ex_ID`) REFERENCES `journal_ex` (`ID`) ON DELETE CASCADE, CONSTRAINT `documents_ex_ibfk_2` FOREIGN KEY (`Documents_ID`) REFERENCES `documents` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 After some time I decided to drop 'Journal_ex_ID' column and all its environment from this non empty table. All I tried to do like: alter table documents_ex drop column Journal_ex_ID or alter table documents_ex drop foreign key Journal_ex_ID or alter table documents_ex drop key Journal_ex_ID I receive the same error 150 and I don't know what to do. Please help me. Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Please help to solve a serios problem
i believe you need to have root create the file /var/lib/mysql/mysql.sock and then make it readable and writable by the user that owns mysql. You can make that same file owner by the user that runs mysql. -Original Message- From: Ali Nebi [EMAIL PROTECTED] Sent: Nov 6, 2007 9:24 AM To: mysql@lists.mysql.com Subject: Please help to solve a serios problem Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
On Tue, 2007-11-06 at 09:32 -0600, [EMAIL PROTECTED] wrote: i believe you need to have root create the file /var/lib/mysql/mysql.sock and then make it readable and writable by the user that owns mysql. You can make that same file owner by the user that runs mysql. -Original Message- From: Ali Nebi [EMAIL PROTECTED] Sent: Nov 6, 2007 9:24 AM To: mysql@lists.mysql.com Subject: Please help to solve a serios problem Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This socket file is created automatically when i run mysql by this way: /etc/init.d/mysql start. The file has these permissions set. I think this is ok, problem is something different i think, but i don't know where. srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
The mysql.sock location '/var/lib/mysql/mysql.sock' attempted to be used by your connecting client may be wrong. linux$ ls -la /var/lib/mysql/mysql.sock Does the file exist? You say this is the sock file created by mysqld: srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock So your client would need to connect like so: linux$ mysql --socket=system_u:object_r:mysqld_var_run_t mysql.sock --user=user -p What OS are you running MySQL on? -RG Ali Nebi wrote: On Tue, 2007-11-06 at 09:32 -0600, [EMAIL PROTECTED] wrote: i believe you need to have root create the file /var/lib/mysql/mysql.sock and then make it readable and writable by the user that owns mysql. You can make that same file owner by the user that runs mysql. -Original Message- From: Ali Nebi [EMAIL PROTECTED] Sent: Nov 6, 2007 9:24 AM To: mysql@lists.mysql.com Subject: Please help to solve a serios problem Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This socket file is created automatically when i run mysql by this way: /etc/init.d/mysql start. The file has these permissions set. I think this is ok, problem is something different i think, but i don't know where. srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
- Original Message - From: Russell E Glaue [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 06, 2007 8:55 PM Subject: Re: Please help to solve a serios problem The mysql.sock location '/var/lib/mysql/mysql.sock' attempted to be used by your connecting client may be wrong. linux$ ls -la /var/lib/mysql/mysql.sock srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock So your client would need to connect like so: linux$ mysql --socket=system_u:object_r:mysqld_var_run_t mysql.sock --user=user -p What OS are you running MySQL on? -RG Thanks for the reply. Does the file exist? Yes, i see that the file exist. You say this is the sock file created by mysqld: when mysqld daemon is stopped the mysql.sock file not exist, when we run /etc/init.d/mysqd (this is mysql.server), it start mysql_safe and create mysql.sock under /var/lib/mysql/. linux$ mysql -- socket=system_u:object_r:mysqld_var_run_t mysql.sock --user=user -p We don't need to use socket option because in /etc/my.cnf we have set where is mysql.sock file. By this way when we start mysql server, mysql client already know where is mysql socket. I suppose this is correct, if not please correct me. We are using CentOS 5, 64 bits version. Also this: system_u:object_r:mysqld_var_run_t. It is not need to be set in sock=, because these are SELinux permissions, this is different. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help reading DB deadlock notice
Nikita Tovstoles [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Do both of your SELECT statements use the same index (`token`) when setting locks on the rows? My guess is that the situation is as follows. TX2: SELECT ... WHERE id = ... FOR UPDATE TX2 sets an exclusive lock for a row on PRIMARY index. TX1: SELECT ... WHERE token = ... FOR UPDATE TX1 tries to acquire X-lock for a row in `token` index, which succedes. This however requires also a lock on PRIMARY, but the corresponding row on that index is already locked by TX2. TX1 waits. TX2: DELETE ... WHERE id = ... TX2 tries to delete the row. This of course requires the removal of its presence in the indxexes, including `token`, but because of the earlier exclusive lock set by TX1 this cannot be accomplished without waiting for the lock to be freed. TX2 waits. Hence, the deadlock. I'm wondering if granting X-locks for the secondary indexes could not be delayed until such lock on the primary key is acquired...? Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help*
VeeJay wrote: How and at What place, can I configure the Makefile under /usr/ports/databases/mysql50-server/ to have MySQL server working with above mentioned configurations? Why not just use MySQL that comes from ports? If you really want to build from source, read: http://dev.mysql.com/doc/refman/5.0/en/installing-source.html -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Mobile: +614 12 593 292 / Skype: colincharles Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please Help - Stored Procedure Issue
Aaah. OK. That's what the @ stands for. I could not figure out what the @ was for, and I posted a message on the list yesterday and did not receive a response. You're right. That solved the problem. Thanks, Jesse - Original Message - From: Chris White [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 27, 2006 5:15 PM Subject: Re: Please Help - Stored Procedure Issue On Tuesday 27 June 2006 01:52 pm, Jesse wrote: @cInvNo replace all instances of this with just cInvNo. cInvNo is already declared as an OUT variable, and @cInvNo will be set to that value when you run: CALL sp_GetNextInv(-1,@cInvNo); -- Chris White PHP Programmer/DBBD Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please Help - Stored Procedure Issue
On Tuesday 27 June 2006 01:52 pm, Jesse wrote: @cInvNo replace all instances of this with just cInvNo. cInvNo is already declared as an OUT variable, and @cInvNo will be set to that value when you run: CALL sp_GetNextInv(-1,@cInvNo); -- Chris White PHP Programmer/DBBD Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help me.
On 6/2/06, yuan edit [EMAIL PROTECTED] wrote: my operating system is linux redhat 9.0. i am installing mysql 5.0.x binary distribution. Which edition is the most fit in the following editions? You notices most of those files are .asc and .md5 used to verify the integrity of the archive after you download it, right? So, reduced to 3 versions to choose. And would you like to tell me the difference among these editions? You can search the MySQL site, and I would advice you to download it from the site, not from this mirror that I never heard of. If you were at the site, there are docs explaining the difference between each version. mysql-standard-5.0.22-linux-i686-glibc23.tar.gz Compiled against glibc-2.3 mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz Compiled with ICC?! Dunno, never heard of it and its not at the official MySQL site. mysql-standard-5.0.22-linux-i686.tar.gz Static linked, as far as I know. I strongly advice you to: 1) Never copy and paste a whole web page in a mail. 2) Strip down what is unrelevant of text 3) Clean html tags 4) Learn about md5 and asc. 5) Be thankful that someone read all this blob and answered 6) Download MySQL from www.mysql.com -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help optimize this query
Thank you for your help. I will try using chars instead of varchars. That should be fairly easy to try. I already do have a table for symbols, though the key is varchar not integer. I was using shares and value as varchar because there was some weird rounding happening when I used decimal, or floats. It seemed easier to keep them as character. Also the date is part of the key since for different days there is different values as the stock market changes. I will try char though and see if that makes it faster. Thank you. From: DreamWerx [EMAIL PROTECTED] Date: May 31, 2006 12:25:50 PM CDT To: Joseph Alotta [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: please help optimize this query I'd start by looking at your schema.. Quite a lot of use of varchars... I'd suggest using chars (takes more space but processes faster).. Or even better, normalize the data so that you have a table for symbols that is linked to this table via an integer based foreign key. Also it seems wierd for 'shares' and 'value' to be varchar? I'd think them to be integer and float types.. Also using date as the PK seems odd.. A lot of times queries perform poorly because of poor database/table design. On 5/31/06, Joseph Alotta [EMAIL PROTECTED] wrote: Greetings, I generate this report of all the holdings by symbol, summing up over accounts. It is taking much too long. I was wondering if I can add an index on something to make it more efficient. The first query gets the most recent date, the next query sums it up by symbol. Thank you for you help, Joe. # mdate = $m.query(select max(date) from positions where symbol = '# {sym}' # and date = '#{last_date}' ).fetch_row.at(0) # this query was taking too long to process. It has been replace with the # following: line = $m.query(select * from positions where symbol = '# {sym}' order by date desc limit 1).fetch_row next if line.nil? mdate = line.at(4)# fourth position is the date next if last_date mdate total = $m.query(select sum(value) from positions where symbol = '#{sym}' and date = '#{mdate}' ).fetch_row.at (0).to_f mysql describe positions; +-+-+--+-++---+ | Field | Type| Null | Key | Default| Extra | +-+-+--+-++---+ | account | varchar(12) | | PRI || | | symbol | varchar(12) | | PRI || | | shares | varchar(12) | YES | | NULL | | | value | varchar(20) | YES | | NULL | | | date| date| | PRI | -00-00 | | +-+-+--+-++---+ 5 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help optimize this query
I'd start by looking at your schema.. Quite a lot of use of varchars... I'd suggest using chars (takes more space but processes faster).. Or even better, normalize the data so that you have a table for symbols that is linked to this table via an integer based foreign key. Also it seems wierd for 'shares' and 'value' to be varchar? I'd think them to be integer and float types.. Also using date as the PK seems odd.. A lot of times queries perform poorly because of poor database/table design. On 5/31/06, Joseph Alotta [EMAIL PROTECTED] wrote: Greetings, I generate this report of all the holdings by symbol, summing up over accounts. It is taking much too long. I was wondering if I can add an index on something to make it more efficient. The first query gets the most recent date, the next query sums it up by symbol. Thank you for you help, Joe. # mdate = $m.query(select max(date) from positions where symbol = '# {sym}' # and date = '#{last_date}' ).fetch_row.at(0) # this query was taking too long to process. It has been replace with the # following: line = $m.query(select * from positions where symbol = '# {sym}' order by date desc limit 1).fetch_row next if line.nil? mdate = line.at(4)# fourth position is the date next if last_date mdate total = $m.query(select sum(value) from positions where symbol = '#{sym}' and date = '#{mdate}' ).fetch_row.at(0).to_f mysql describe positions; +-+-+--+-++---+ | Field | Type| Null | Key | Default| Extra | +-+-+--+-++---+ | account | varchar(12) | | PRI || | | symbol | varchar(12) | | PRI || | | shares | varchar(12) | YES | | NULL | | | value | varchar(20) | YES | | NULL | | | date| date| | PRI | -00-00 | | +-+-+--+-++---+ 5 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help, can not delete database
What makes you think the delete of the database failed? It looks like the message from the DROP command indicates that the database was dropped successfully. -- Rhino - Original Message - From: Randy Paries [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 22, 2006 9:22 AM Subject: please help, can not delete database Hello, Not sure what is going on i have mysql Ver 12.22 Distrib 4.0.17, I have a database that i can not delete. i do this #mysqladmin drop billmax -u admin --password Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'billmax' database [y/N] y Database billmax dropped But it does not do anything. I have tried restarting the DB and restarting the box as well Please help Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help, can not delete database
Randy Paries wrote: Hello, Not sure what is going on i have mysql Ver 12.22 Distrib 4.0.17, I have a database that i can not delete. i do this #mysqladmin drop billmax -u admin --password Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'billmax' database [y/N] y Database billmax dropped But it does not do anything. I have tried restarting the DB and restarting the box as well Please help Randy Remember that MySQL uses directories to implement databases. When you drop a database, it deletes the table files and then tries to delete the directory. If the directory remains, even if it is empty, it still looks like a databbase to MySQL. So check the directory that corresponds to this troublesome database for unusual permissions or content (such as some files other than MySQL's table files) that may be preventing the deletion of the directory. (Check /etc/my.cnf for the location of your database directories. RedHat usually puts them in /var/lib/mysql.) Let us know if that was the problem. --John PS Remember to reply to the list :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help: recovering db from crash
This is actually for Linux/Unix, not Windows. What error do you get from MySQL when trying to log in? Does the mysqld(-nt) process show within Task Manager? What does the new error log say? You may need to reset permissions: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Or you may need to set the appropriate datadir / basdir etc. depending on how you set up MySQL: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Best regards Mark Adrian Bruce wrote: not sure, but it may be worth trying the following run the script: mysql_install_db --user=root In the installation dir this should change ownership and make mysql recognise the data dir. good luck Ade Foo Ji-Haw wrote: Hi all, My Windows-based database server crashed (no fault of MySQL. probably OS or hardware), and I managed to copy out the data files. I am using version 5.0 of the Essentials package. I tried to install a similar setup on another server, then copy the data\ folder over. The MySQL service starts, but I am not able to login, even as root. Is there anyone who can advise me on the recovery steps? Appreciate your feedback! -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help: recovering db from crash
not sure, but it may be worth trying the following run the script: mysql_install_db --user=root In the installation dir this should change ownership and make mysql recognise the data dir. good luck Ade Foo Ji-Haw wrote: Hi all, My Windows-based database server crashed (no fault of MySQL. probably OS or hardware), and I managed to copy out the data files. I am using version 5.0 of the Essentials package. I tried to install a similar setup on another server, then copy the data\ folder over. The MySQL service starts, but I am not able to login, even as root. Is there anyone who can advise me on the recovery steps? Appreciate your feedback! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help: recovering db from crash
Thanks for coming to the rescue, Mark and Bruce. Mark Leith wrote: This is actually for Linux/Unix, not Windows. What error do you get from MySQL when trying to log in? Does the mysqld(-nt) process show within Task Manager? What does the new error log say? You may need to reset permissions: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Or you may need to set the appropriate datadir / basdir etc. depending on how you set up MySQL: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Best regards Mark Adrian Bruce wrote: not sure, but it may be worth trying the following run the script: mysql_install_db --user=root In the installation dir this should change ownership and make mysql recognise the data dir. good luck Ade Foo Ji-Haw wrote: Hi all, My Windows-based database server crashed (no fault of MySQL. probably OS or hardware), and I managed to copy out the data files. I am using version 5.0 of the Essentials package. I tried to install a similar setup on another server, then copy the data\ folder over. The MySQL service starts, but I am not able to login, even as root. Is there anyone who can advise me on the recovery steps? Appreciate your feedback! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help .........very urgent
Kane Wilson wrote: I wanted to check the following condition and if it is success i wanted to display a massage.I tried as follows. but no luck. nothing displayed. $dbQuery = results; $result = mysql_query($dbQuery) or die(Couldn't get file list); if (!isset($result)) {echo NULL;} //if (mysql_result($result == 0))( //echo sorry; ? Although this is something for a PHP mailing list of forum, I'll try to give you some pointers. I assume that you first used mysql_connect() to connect to the MySQL db. mysql_query() returns FALSE if the query failed for some reason. In that case you can use mysql_error() to retrieve the error message. $result = mysql_query($dbQuery); if (!$result) die('Query failed: ' . mysql_error()); Checking isset() is useless, since $result is always set (either with false, true or the result resource of the query). http://www.php.net/manual/en/ref.mysql.php contains an example which shows all the steps you need to set up a connection and run a query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help .........very urgent
Hello. $dbQuery = results; Have you forgotten to put '$' before the 'results'? Do you have display_errors = On in your php.ini? Kane Wilson [EMAIL PROTECTED] wrote: hi , I wanted to check the following condition and if it is success i wanted to display a massage.I tried as follows. but no luck. nothing displayed. $dbQuery = results; $result = mysql_query($dbQuery) or die(Couldn't get file list); if (!isset($result)) {echo NULL;} //if (mysql_result($result == 0))( //echo sorry; ? please , tell me how can i do this ? Thanx Kane. __ Click here to donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: please help .........very urgent
Hi!! $dbQuery = select * from table where 1 ; $result = mysql_query($dbQuery) or die(Error trying to execute the query: .mysql_error()); If(mysql_num_rows($result)1){ //also: ==0 works echo NULL; -Original Message- From: Kane Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 07, 2005 1:40 AM To: mysql Subject: please help .very urgent hi , I wanted to check the following condition and if it is success i wanted to display a massage.I tried as follows. but no luck. nothing displayed. $dbQuery = results; $result = mysql_query($dbQuery) or die(Couldn't get file list); if (!isset($result)) {echo NULL;} //if (mysql_result($result == 0))( //echo sorry; ? please , tell me how can i do this ? Thanx Kane. __ Click here to donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
John thegimper wrote: This is what i need: Posted by gogman on Monday May 5 2003, @10:42am on the mysql website: MySQL defaults to an 'OR'. Example: 'dog cat' = 'dog OR cat'. Most fulltext search engines default to an 'AND'. These include: AltaVista, Fast Search, Google, Hotbot, Lycos, Northern Light and Yahoo. Excite is an exception that defaults to an 'OR'. It's not clear to me that this is entirely accurate (Google, for example, is a relevance search http://www.google.com/support/bin/answer.py?answer=427topic=352), but I don't think web search engine front ends are particularly relevant in any case. New Feature: set-variable = ft_boolean_default='AND' vs 'OR' ('OR' would be the default setting so as to not break older code) With a 'AND' default 'OR' would have to be explicit. Example: 'dog cat' = 'dog AND cat', 'dog OR cat' - requires 'OR' to be set. Performance tests are indicating a 5-7 times increase in search speed with AND vs OR statements. -- I have done some searches on google and found several people wanting to do the same... but no solutions. Is there still no solution for this? Every large searchengine works like this. Why do you *need* this? More to the point, why should mysql do this? Honestly, I think you are confusing the user interface, the application, and the back end. Your user interface is free to include a search box where the user can type 'dog cat' with the expectation that only documents containing both (AND) will be returned. Your application needs to parse the request and send the proper query to mysql (the back end). Mysql is just a useful tool. It stores your data and provides various forms of full-text searching: Relevance scoring - WHERE MATCH (doc, description) AGAINST ('dog cat') OR searches - WHERE MATCH (doc, description) AGAINST ('dog cat' IN BOOLEAN MODE) AND searches - WHERE MATCH (doc, description) AGAINST ('+dog +cat' IN BOOLEAN MODE) Given these choices, I'm not sure what difference it makes what the default is. You surely don't propose to pass unmodified user input to mysql, as that's not a good idea (see SQL injection http://www.google.com/search?q=SQL+injection). If you want AND searches, simply have your app add the + signs to the user input as it builds the query to send to mysql. If you are determined to change mysql's default behavior, then Sergei has already given a solution earlier in this thread: swap the '+' and ' ' in the ft_boolean_syntax variable. The only objection raised to this was the suggestion that if the user prepends a '+' to a word, it becomes optional (OR instead of AND), but that's a moot point, as your app will, of course, strip the '+' when parsing the user's input. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
Is there still no solution for this? Every large searchengine works like this. Quoting: Hi that works fine only now if a user puts + in front of a word that word becomes optional = OR? What i would like is the search to work exactly like before only that it defaults to AND instead of OR. Almost every search engine i have tried, google etc. works like this. black horse cat dog only show results with all words present. This is what i did, set global ft_boolean_syntax = ' +-()~*:|' I really appreciate your help!! From: Sergei Golubchik [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Please help me: Boolean fulltext searches, AND instead of OR Date: Wed, 23 Mar 2005 22:32:49 +0100 Hi! On Mar 23, Jessica Svensson wrote: Is there any way i can get results with AND instead of OR? Trying to search for black cat should only return records that contains both black and cat. I'm using the following code to get my result: SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN MODE); sure there must be an easy way to change the default word separator to AND instead of OR? There is. See ft_boolean_syntax variable - it defines what characters is used for each operator. In particular it defines '+' for must be present and a space ' ' for optionally present words. You want to put the space first (for must be present words) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com _ Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/ - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
This is what i need: Posted by gogman on Monday May 5 2003, @10:42am on the mysql website: MySQL defaults to an 'OR'. Example: 'dog cat' = 'dog OR cat'. Most fulltext search engines default to an 'AND'. These include: AltaVista, Fast Search, Google, Hotbot, Lycos, Northern Light and Yahoo. Excite is an exception that defaults to an 'OR'. New Feature: set-variable = ft_boolean_default='AND' vs 'OR' ('OR' would be the default setting so as to not break older code) With a 'AND' default 'OR' would have to be explicit. Example: 'dog cat' = 'dog AND cat', 'dog OR cat' - requires 'OR' to be set. Performance tests are indicating a 5-7 times increase in search speed with AND vs OR statements. -- I have done some searches on google and found several people wanting to do the same... but no solutions. Quoting John thegimper [EMAIL PROTECTED]: Is there still no solution for this? Every large searchengine works like this. Quoting: Hi that works fine only now if a user puts + in front of a word that word becomes optional = OR? What i would like is the search to work exactly like before only that it defaults to AND instead of OR. Almost every search engine i have tried, google etc. works like this. black horse cat dog only show results with all words present. This is what i did, set global ft_boolean_syntax = ' +-()~*:|' I really appreciate your help!! From: Sergei Golubchik [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Please help me: Boolean fulltext searches, AND instead of OR Date: Wed, 23 Mar 2005 22:32:49 +0100 Hi! On Mar 23, Jessica Svensson wrote: Is there any way i can get results with AND instead of OR? Trying to search for black cat should only return records that contains both black and cat. I'm using the following code to get my result: SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN MODE); sure there must be an easy way to change the default word separator to AND instead of OR? There is. See ft_boolean_syntax variable - it defines what characters is used for each operator. In particular it defines '+' for must be present and a space ' ' for optionally present words. You want to put the space first (for must be present words) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com _ Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/ - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help : linux version
Drew Reid wrote: Hello I need to download a version of mySql v4, but I don't know which one to download. As the server is mySql compatible they must already be running mySql on the system. Uh-oh -- not the perennial faceless they, again!? Shouldn't you ask them what version is on the server so you can get the corresponding package(s) for your own system(s)? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
I couldn't find much on the variable you were trying to change. Although it may have changed and now be called ft_boolean_syntax. But that still won't help you since it doesn't appear to have an option to change the default separator. I think you will need to add the + to each word if you want the AND behavior. Why are you so averse to parsing? It would make it easier for the user to input search terms. If it's helpful, below is some php code I use for parsing search phrases entered by users. It adds + and * based on quotes or independent words. I wrote it a long time ago and it problem needs to be looked at to make it better, but it's worked for me for a while now. It will give you the AND behavior you are looking for. function prepFullTextSearch($searchVal) { //Split words into list $word_List = explode(' ',trim($searchVal)); //Step through word list to get search phrases $i = 0; $isPhrase= false; foreach($word_List as $word) { $searchItems[$i] = trim( ($isPhrase?$searchItems[$i].' '.$word:$word) ); //Check for start of Phrase if(substr($searchItems[$i],0,1) == '') { $isPhrase = true; } //If not building a phrase, append wildcard (*) to end of word if(!$isPhrase) { $searchItems[$i] .= '*'; $i++; } //Check for end of Phrase if(substr($searchItems[$i],-1) == '') { $isPhrase = false; $i++; } } $searchVal= '+'.implode(' +',$searchItems); return $searchVal; } On Mar 23, 2005, at 2:45 PM, Jessica Svensson wrote: Is there any way i can get results with AND instead of OR? Trying to search for black cat should only return records that contains both black and cat. I'm using the following code to get my result: SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN MODE); sure there must be an easy way to change the default word separator to AND instead of OR? I found this: set-variable = ft_boolean_default='AND' SET ft_boolean_default = 'AND' But it does not work, everything would be sooo much easier if this was possible. black cat is only an example and the real query comes from user input. So it can be anything like +cat -dog +big nose -horse white black -red so parsing the input is not what i want, i just want to change the default word separator to AND instead of OR. Now i´m running 4.1.10 and also tried with 4.0.24 Please help me, there must be a way to change this?!?! _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
Hi! On Mar 23, Jessica Svensson wrote: Is there any way i can get results with AND instead of OR? Trying to search for black cat should only return records that contains both black and cat. I'm using the following code to get my result: SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN MODE); sure there must be an easy way to change the default word separator to AND instead of OR? There is. See ft_boolean_syntax variable - it defines what characters is used for each operator. In particular it defines '+' for must be present and a space ' ' for optionally present words. You want to put the space first (for must be present words) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
Hi that works fine only now if a user puts + in front of a word that word becomes optional = OR? What i would like is the search to work exactly like before only that it defaults to AND instead of OR. Almost every search engine i have tried, google etc. works like this. black horse cat dog only show results with all words present. This is what i did, set global ft_boolean_syntax = ' +-()~*:|' I really appreciate your help!! From: Sergei Golubchik [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Please help me: Boolean fulltext searches, AND instead of OR Date: Wed, 23 Mar 2005 22:32:49 +0100 Hi! On Mar 23, Jessica Svensson wrote: Is there any way i can get results with AND instead of OR? Trying to search for black cat should only return records that contains both black and cat. I'm using the following code to get my result: SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN MODE); sure there must be an easy way to change the default word separator to AND instead of OR? There is. See ft_boolean_syntax variable - it defines what characters is used for each operator. In particular it defines '+' for must be present and a space ' ' for optionally present words. You want to put the space first (for must be present words) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com _ Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
On Wed, 23 Mar 2005 22:22:34 +, Jessica Svensson [EMAIL PROTECTED] said: Hi that works fine only now if a user puts + in front of a word that word becomes optional = OR? What i would like is the search to work exactly like before only that it defaults to AND instead of OR. Almost every search engine i have tried, google etc. works like this. black horse cat dog only show results with all words present. I'm a little late in the discussion but... Are you using a scripting language with MYSQL? It'd be easy w/PHP This is what i did, set global ft_boolean_syntax = ' +-()~*:|' I really appreciate your help!! From: Sergei Golubchik [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Please help me: Boolean fulltext searches, AND instead of OR Date: Wed, 23 Mar 2005 22:32:49 +0100 Hi! On Mar 23, Jessica Svensson wrote: Is there any way i can get results with AND instead of OR? Trying to search for black cat should only return records that contains both black and cat. I'm using the following code to get my result: SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN MODE); sure there must be an easy way to change the default word separator to AND instead of OR? There is. See ft_boolean_syntax variable - it defines what characters is used for each operator. In particular it defines '+' for must be present and a space ' ' for optionally present words. You want to put the space first (for must be present words) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com _ Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help - MySQL4.1.10 don't run properly after data restored from 5.0.1
But when I execute a complex query, it hangs there forever. I don't have a chance to see the result because I need to go home to have dinner. The same query run in a FreeBSD system in Mysql5.01 just takes 2 seconds. Please, execute this statements while the huge query is running, and send us the output: show processlist; show variables; show status; Send us an output of SHOW CREATE TABLE your_table, for each table you use in your queries. Send us the actual query. Please, run this statements using mysql command line client. The questions I would like to ask is how to make sure mysqld is start up properly? Because it seems that the start script that come with the Usually MySQL writes errors to error log. If you find something there, please, send it us. To check that MySQL server is running you may use 'mysqladmin ping'. Sometimes server started normally, but it's threads dies during the queries, messages about this are written into error log. See: http://dev.mysql.com/doc/mysql/en/error-log.html The second question is how to make sure all indexes are in-placed after the data restore? I used the following script backup all data from Usually indexes are created normally, and if you see them with show index then everything should be ok. After receiving the information, which I asked, we can make more exact conlusions. sam wun [EMAIL PROTECTED] wrote: Hi, I have removed all old data and restore the backup data to the directory /usr/local/mysql/data I also created root password with mysqladmin and flush its previliges... Execute the following perl+dbi script seems fine, it retrieves all data from the customer table in DB which I just restored: #!/usr/bin/perl use DBI; $database = DB; $username = me; $pw = me0901; $dbh = DBI-connect(DBI:mysql:$database,$username,$pw); die Unable to connect: $DBI::errstr\n unless (defined $dbh); $sth = $dbh-prepare(q{SELECT * from customer}) or die Unable to prep our query:.$dbh-errstr.\n; $rc = $sth-execute or die Unable to execute our query:.$dbh-errstr.\n; while ($aref = $sth-fetchrow_arrayref){ for ($i=0; $i $sth-{NUM_OF_FIELDS};$i++){ print $sth-{NAME}-[$i].:. $aref-[$i] . \n; } } $sth-finish; $dbh-disconnect; But when I execute a complex query, it hangs there forever. I don't have a chance to see the result because I need to go home to have dinner. The same query run in a FreeBSD system in Mysql5.01 just takes 2 seconds. The MySQL 5.01 in FreeBSD is the source of the backup. I restore this backup to MySQL 4.1.10 in Redhat9.0. I have checked the DB's indexes on the most complex table, it shows indexes are being indexed. mysql show index from transaction; +-++-+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++-+--+-+---+-+--++--++-+ | transaction | 0 | PRIMARY |1 | transcode | A | 161629 | NULL | NULL | | BTREE | | | transaction | 1 | custcode|1 | custcode| A | 715 | NULL | NULL | | BTREE | | | transaction | 1 | prodcode|1 | prodcode| A |3367 | NULL | NULL | | BTREE | | | transaction | 1 | date|1 | date| A |1197 | NULL | NULL | | BTREE | | | transaction | 1 | netsales|1 | netsales| A | 23089 | NULL | NULL | | BTREE | | | transaction | 1 | salesvolume |1 | salesvolume | A | 206 | NULL | NULL | | BTREE | | | transaction | 1 | custcode_2 |1 | custcode| A | 788 | NULL | NULL | | BTREE | | | transaction | 1 | custcode_2 |2 | date| A | 53876 | NULL | NULL | | BTREE | | | transaction | 1 | prodcode_2 |1 | prodcode| A | 923 | NULL | NULL | | BTREE | | | transaction | 1 | prodcode_2 |2 | date| A | 161629 | NULL | NULL | | BTREE | | +-++-+--+-+---+-+--++--++-+ 10 rows in set (0.47 sec) One strange thing I found is when
Re: Please help with query to show duplicate addresses... TIA!
Please post the structure of your orders table. (SHOW CREATE TABLE orders). I need to know what you are using as a primary key in order to help you to uniquely identify each duplicated row. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Paul Fine [EMAIL PROTECTED] wrote on 09/30/2004 02:22:44 PM: I am trying to come up with a query that shows duplicate last names and the order numbers for each occurance of. I can get as far as determining the duplicates but my query result only outputs one order for each. Here is my current query, an example of the results and an example of the results I want. P.S. This is just an example, looking at duplicate last names is seldom of any practical value! Thanks for any help! customer_last_name order_number +---+-+ +smith + 1 + +smith + 2 + +smith + 3 + +-+ SELECT customer_last_name, order_number, COUNT(customer_last_name) AS duplicate_customer_last_names FROM orders GROUP BY customer_last_name HAVING (duplicate_customer_last_names 1) Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +-+-+ Desired Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +smith + 2 + 3 + +smith + 3 + 3 + +-+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with query to show duplicate addresses... TIA!
If you are using 4.1, you can look into the GROUP_CONCAT function. Otherwise, try a self join like this: SELECT A.LastName,A.OrderNum,B.OrderNum FROM Orders AS A LEFT JOIN Orders AS B ON A.LastName=B.LastName WHERE A.OrderNum!=B.OrderNum ORDER BY A.LastName That joins the Order table with itself on LastName and filters out the non-duplicates, since there will always be at least one match, by filtering out Order Numbers that match for the duplicate last names. It will output something like this: A.LastName A.Order B.OrderNum - smith 1 2 smith 1 3 jones 5 10 jones 5 12 jones 5 23 I don't know if the not equal syntax I used != is valid for your version of MySQL. I know it works in 4.1. That shows that smith has order 1,2, and 3. Jones has 5,10,12 and 23. On Sep 30, 2004, at 2:22 PM, Paul Fine wrote: I am trying to come up with a query that shows duplicate last names and the order numbers for each occurance of. I can get as far as determining the duplicates but my query result only outputs one order for each. Here is my current query, an example of the results and an example of the results I want. P.S. This is just an example, looking at duplicate last names is seldom of any practical value! Thanks for any help! customer_last_name order_number +---+-+ +smith + 1 + +smith + 2 + +smith + 3 + +-+ SELECT customer_last_name, order_number, COUNT(customer_last_name) AS duplicate_customer_last_names FROM orders GROUP BY customer_last_name HAVING (duplicate_customer_last_names 1) Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +-+-+ Desired Result: customer_last_name order_number duplicate_customer_last_names +---+-+-+ +smith + 1 + 3 + +smith + 2 + 3 + +smith + 3 + 3 + +-+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with query to show duplicate addresses... TIA!
Brent Baisley wrote: If you are using 4.1, you can look into the GROUP_CONCAT function. Otherwise, try a self join like this: SELECT A.LastName,A.OrderNum,B.OrderNum FROM Orders AS A LEFT JOIN Orders AS B ON A.LastName=B.LastName WHERE A.OrderNum!=B.OrderNum ORDER BY A.LastName That joins the Order table with itself on LastName and filters out the non-duplicates, since there will always be at least one match, by filtering out Order Numbers that match for the duplicate last names. It will output something like this: A.LastNameA.OrderB.OrderNum - smith12 smith13 jones510 jones512 jones523 No, it won't. It will produce output like this: +--+--+--+ | LastName | OrderNum | OrderNum | +--+--+--+ | jones| 10 |5 | | jones| 12 |5 | | jones| 23 |5 | | jones|5 | 10 | | jones| 12 | 10 | | jones| 23 | 10 | | jones|5 | 12 | | jones| 10 | 12 | | jones| 23 | 12 | | jones|5 | 23 | | jones| 10 | 23 | | jones| 12 | 23 | | smith|2 |1 | | smith|3 |1 | | smith|1 |2 | | smith|3 |2 | | smith|1 |3 | | smith|2 |3 | +--+--+--+ 18 rows in set (0.08 sec) Each row in A is paired with each non-matching row in B. I don't know if the not equal syntax I used != is valid for your version of MySQL. I know it works in 4.1. It is. http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html That shows that smith has order 1,2, and 3. Jones has 5,10,12 and 23. The answer is in there, but it's a mess. A simple SELECT LastName, OrderNum FROM orders would do, except you want to leave out the rows with unique LastName values. The following should work: # Collect the non-unique last names CREATE TEMPORARY TABLE lastnames SELECT LastName FROM orders GROUP BY LastName HAVING COUNT(*) 1; # Find the rows with the non-unique last names SELECT orders.LastName, OrderNum FROM orders, lastnames WHERE orders.Lastname = lastnames.lastname; ORDER BY orders.Lastname, OrderNum; +--+--+ | LastName | OrderNum | +--+--+ | jones|5 | | jones| 10 | | jones| 12 | | jones| 23 | | smith|1 | | smith|2 | | smith|3 | +--+--+ 7 rows in set (0.01 sec) # Clean up DROP TABLE lastnames; With 4.1 and subqueries, this becomes: SELECT LastName, OrderNum FROM orders WHERE LastName IN (SELECT LastName FROM orders GROUP BY LastName HAVING COUNT(*) 1) ORDER BY Lastname, OrderNum; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me optimize a MySQL server.
Mohammed Sameer [EMAIL PROTECTED] wrote: I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Does MySQL use indexes? You can check it with EXPLAIN SELECT: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Some optimization tips you can find at: http://dev.mysql.com/doc/mysql/en/MySQL_Optimisation.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[uniball@gmx.net: Re: Please help me optimize a MySQL server.]
sorry Egor Egorov, Mutt sent the mail to your private inbox ;) resending to the mailing list. On Wed, May 19, 2004 at 02:02:29PM +0300, Egor Egorov wrote: Mohammed Sameer [EMAIL PROTECTED] wrote: I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Thanks for replying... Does MySQL use indexes? You can check it with EXPLAIN SELECT: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html It's phpBB and phpnuke still fighting with them to trash phpnuke default tables. The website is really active, with about 67000 visits/day. We are using a RAID controller for the server. Some optimization tips you can find at: http://dev.mysql.com/doc/mysql/en/MySQL_Optimisation.html I tried all this, but no use. CPU idle is usually 0% a 1.5 gig log file was generated in about 12h or so. move to innodb from MyISAM ? Is it a Debian woody problem ? load balancing ? I tried to do what I can, but nothing improved the performance. -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- signature.asc Description: Digital signature
Re: Please help me optimize a MySQL server.
Something that small shouldn't really need optimizing. What is the size of your data (mb?, gb?) and what does your query look like? If you are doing a wild card search on a large text field without a full text index, then those times may be the best you're going to get. Many times it's about optimizing your query statement or you indexes rather than MySQL settings. For instance, I have a test machine (Mac G4 400Mhz, 512MB, MySQL 4.0.18) that does a three table join with each table having just over 100,000 rows. My initial query took about 12 seconds, but changing my query statement around a little I got it to just under 1 second. Which is pretty good considering the hardware it's on. What does you explain for the query look like? It's probably doing a full table scan, which means you're bottleneck is the disk. On May 19, 2004, at 3:26 AM, Mohammed Sameer wrote: Hi, I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Many thanks! -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me optimize a MySQL server.
On Wed, May 19, 2004 at 08:47:28AM -0400, Brent Baisley wrote: Something that small shouldn't really need optimizing. What is the size of your data (mb?, gb?) and what does your query look like? If you are doing a wild card search on a large text field without a full text index, then those times may be the best you're going to get. Many times it's about optimizing your query statement or you indexes rather than MySQL settings. mainly we are using 2 databases: 675M for phpbb and 77M for phpnuke. That's not much. Ok, It seems that the actual queries performed by phpbb are optimized. For instance, I have a test machine (Mac G4 400Mhz, 512MB, MySQL 4.0.18) that does a three table join with each table having just over 100,000 rows. My initial query took about 12 seconds, but changing my query statement around a little I got it to just under 1 second. Which is pretty good considering the hardware it's on. What does you explain for the query look like? It's probably doing a full table scan, which means you're bottleneck is the disk. but the server is running on a duel P III with 2 GB RAM and a RAID controller. Do you think there is much to be done, or shall I look for better hardware/or clustering ? On May 19, 2004, at 3:26 AM, Mohammed Sameer wrote: Hi, I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Many thanks! -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- signature.asc Description: Digital signature
Re: Please help me optimize a MySQL server.
I wouldn't upgrade until you know where the bottleneck is (CPU, disk, network, or RAM). Since you are using professional software, I wouldn't try to change the queries. Have you made changes to your my.cnf file? Since you have enough ram to hold all the data, ram is probably not your bottleneck. The question is whether you have your system configured to use it. I forget if phpbb and phpnuke use PEAR::DB to access the underlying database. If they do, there is a certain amount of overhead involved that you just can't get around without changing the database access code to use native calls. Are your database and web server on the same machine (a security no-no, but performance yes-yes)? If they are on separate machines, make sure you have a fast connection between them, at least 100MB and make sure it's full duplex. On May 19, 2004, at 10:34 AM, Mohammed Sameer wrote: On Wed, May 19, 2004 at 08:47:28AM -0400, Brent Baisley wrote: Something that small shouldn't really need optimizing. What is the size of your data (mb?, gb?) and what does your query look like? If you are doing a wild card search on a large text field without a full text index, then those times may be the best you're going to get. Many times it's about optimizing your query statement or you indexes rather than MySQL settings. mainly we are using 2 databases: 675M for phpbb and 77M for phpnuke. That's not much. Ok, It seems that the actual queries performed by phpbb are optimized. For instance, I have a test machine (Mac G4 400Mhz, 512MB, MySQL 4.0.18) that does a three table join with each table having just over 100,000 rows. My initial query took about 12 seconds, but changing my query statement around a little I got it to just under 1 second. Which is pretty good considering the hardware it's on. What does you explain for the query look like? It's probably doing a full table scan, which means you're bottleneck is the disk. but the server is running on a duel P III with 2 GB RAM and a RAID controller. Do you think there is much to be done, or shall I look for better hardware/or clustering ? On May 19, 2004, at 3:26 AM, Mohammed Sameer wrote: Hi, I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Many thanks! -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me optimize a MySQL server.
On Wed, May 19, 2004 at 11:36:38AM -0400, Brent Baisley wrote: I wouldn't upgrade until you know where the bottleneck is (CPU, disk, network, or RAM). Since you are using professional software, I wouldn't try to change the queries. Have you made changes to your my.cnf file? Since you have enough ram to hold all the data, ram is probably not your bottleneck. The question is whether you have your system configured to use it. Here is the uncommented parts in the /etc/my.cnf: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock key_buffer = 384M skip-locking set-variable= max_connections=3500 max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 2 server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M I forget if phpbb and phpnuke use PEAR::DB to access the underlying database. If they do, there is a certain amount of overhead involved that you just can't get around without changing the database access code to use native calls. Are your database and web server on the same machine (a security no-no, but performance yes-yes)? If they are on separate machines, make sure you have a fast connection between them, at least 100MB and make sure it's full duplex. I think they don't use it, The database on a separate server, with a 1000MB LAN connection, Same for the 2 webservers. I verified this now. On May 19, 2004, at 10:34 AM, Mohammed Sameer wrote: On Wed, May 19, 2004 at 08:47:28AM -0400, Brent Baisley wrote: Something that small shouldn't really need optimizing. What is the size of your data (mb?, gb?) and what does your query look like? If you are doing a wild card search on a large text field without a full text index, then those times may be the best you're going to get. Many times it's about optimizing your query statement or you indexes rather than MySQL settings. mainly we are using 2 databases: 675M for phpbb and 77M for phpnuke. That's not much. Ok, It seems that the actual queries performed by phpbb are optimized. For instance, I have a test machine (Mac G4 400Mhz, 512MB, MySQL 4.0.18) that does a three table join with each table having just over 100,000 rows. My initial query took about 12 seconds, but changing my query statement around a little I got it to just under 1 second. Which is pretty good considering the hardware it's on. What does you explain for the query look like? It's probably doing a full table scan, which means you're bottleneck is the disk. but the server is running on a duel P III with 2 GB RAM and a RAID controller. Do you think there is much to be done, or shall I look for better hardware/or clustering ? On May 19, 2004, at 3:26 AM, Mohammed Sameer wrote: Hi, I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. MySQL 4.0.18 compiled from source. We have 2 webservers running apache, And this is the backend database server. The server is really slow. a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I have about 50% CPU idle. using MyISAM table types. sure I can provide any statistics about the running server. Do you think that switching to innodb'd help ? Is it a problem with Debian woody ?? Any suggestions are welcomed. Many thanks! -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK CODE BLOCK-- -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT d-(++)@ s+(++):-+++ a-- C+++$ UL+++$ P+++$+ L+++()$+ E+++ W++?$ N o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e+ h--++ !r y? --END GEEK
Re: Please help me optimize a MySQL server.
on 05/19/2004 08:36 AM, Brent Baisley at [EMAIL PROTECTED] wrote: I wouldn't upgrade until you know where the bottleneck is (CPU, disk, network, or RAM). Since you are using professional software, I wouldn't try to change the queries. Have you made changes to your my.cnf file? Since you have enough ram to hold all the data, ram is probably not your bottleneck. The question is whether you have your system configured to use it. I forget if phpbb and phpnuke use PEAR::DB to access the underlying database. If they do, there is a certain amount of overhead involved that you just can't get around without changing the database access code to use native calls. Are your database and web server on the same machine (a security no-no, but performance yes-yes)? If they are on separate machines, make sure you have a fast connection between them, at least 100MB and make sure it's full duplex. PhpBB is just php access to mysql, no PEAR::DB involved. I can say from eperience, the phpBB code is kinda bad in regards to how it talks to mysql, on some pages it is not at all uncommon for there to be 10 or more complex querries. I would be willing to bet there are many installed mods on phpBB, they offer stuff like, show all online users which though a nice feature, is heavy on the DB. There are hundreds of these mods, people just copy and paste and install them, before they know it the database is taking 30 selects on every single page load, just so you can say happy birthday to: someuser on every page. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP !!! Can not restart server
Please look in your data directory and post the contents of the host name.ERR file that you find there. That will give folks the information they need to help solve your problem. -Original Message- From: Ginger Cheng [mailto:[EMAIL PROTECTED] Sent: Thursday, April 08, 2004 10:21 AM To: [EMAIL PROTECTED] Subject: Please HELP !!! Can not restart server Hi, MySQL Gurus, Version of mysql is Distrib 3.23.54, for redhat-linux-gnu (i386). I started mysql server with 'safe_mysqld --user=root ' . Then I found some variables needs to be optimized. SO I shut it down with 'mysqladmin -p shutdown' using root. But I was connected to the mysql server at the moment as ginger thru another connection. But the server is down all right (at least to me). Then I logged out in my connection as ginger to mysql server. Then I changed the variables in /etc/my.cnf and try to bring up the server using 'safe_mysqld --user=root '. Here is what I got: /usr/usr/bin/safe_mysqld --user=root /usrStarting mysqld daemon with databases from /var/lib/mysql 040408 08:00:06 mysqld ended [1]+ Done/usr/bin/safe_mysqld --user=root I moved the old mysql under datadir to another place and run 'mysql_install_db'. Then I run 'safe_mysqld --user=root '. Still the same thing. I even tried to restart the server again but it does not work either. I have also tried to restore the my.cnf file and there is no effect. Please help me. I need it to be fixed as soon as I can. Thank you so much ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP !!! Can not restart server
What information is being logged in *.err? -Original Message- From: Ginger Cheng To: [EMAIL PROTECTED] Sent: 4/8/04 10:20 AM Subject: Please HELP !!! Can not restart server Hi, MySQL Gurus, Version of mysql is Distrib 3.23.54, for redhat-linux-gnu (i386). I started mysql server with 'safe_mysqld --user=root ' . Then I found some variables needs to be optimized. SO I shut it down with 'mysqladmin -p shutdown' using root. But I was connected to the mysql server at the moment as ginger thru another connection. But the server is down all right (at least to me). Then I logged out in my connection as ginger to mysql server. Then I changed the variables in /etc/my.cnf and try to bring up the server using 'safe_mysqld --user=root '. Here is what I got: /usr/usr/bin/safe_mysqld --user=root /usrStarting mysqld daemon with databases from /var/lib/mysql 040408 08:00:06 mysqld ended [1]+ Done/usr/bin/safe_mysqld --user=root I moved the old mysql under datadir to another place and run 'mysql_install_db'. Then I run 'safe_mysqld --user=root '. Still the same thing. I even tried to restart the server again but it does not work either. I have also tried to restore the my.cnf file and there is no effect. Please help me. I need it to be fixed as soon as I can. Thank you so much ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP !!! Can not restart server
I don't have such files. Unfortunately. Am I hopeless? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP !!! Can not restart server
Here is the error msg: 040408 08:47:14 mysqld started 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 to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend 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 040408 8:47:14 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist 040408 08:47:14 mysqld ended But I am not sure how to fix it. Could anyone help. Thanks ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP !!! Can not restart server
I got it fixed with the msg from --err-log. THank you so much for all your help. I couldn't have made it without your hints. ALl the best ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please HELP !!! Can not restart server
Ginger Cheng [EMAIL PROTECTED] wrote: Here is the error msg: 040408 08:47:14 mysqld started 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 to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend 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 040408 8:47:14 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist 040408 08:47:14 mysqld ended But I am not sure how to fix it. Could anyone help. If you didn't install privilege tables you must run mysql_install_db script. Otherwise check permissions on the MySQL data dir. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with this: ERROR 1047: Unknown command
Hiep Ho [EMAIL PROTECTED] wrote: i have problem adding user to mysql. I can create a database and there is no problem however I got this ERROR 1047: Unknown command when I tried to add new user. I don't think I have this problem before. I run this command GRANT ALL ON *.* TO usename IDENTIFIED BY 'password' ; All I got is this ERROR 1047: Unknown command Execute FLUSH PRIVILEGES and then GRANT command. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with join issue
Erica L Ridley [EMAIL PROTECTED] wrote: I need help with rewriting a sql query. Nested queries that work in MS databases apparently do not work in MySQL databases. Here is what I need in MS query pseudo code: SELECT table1.myfield1, table2.myfield2 FROM table1, table2 WHERE table1.key=table2.key AND myfield1=something AND myfield2 NOT IN (SELECT myfield2 FROM table3 WHERE table3.myfield3=whatever) For example, if I wanted to get all Forum Posts (Table1) for TopicA that do NOT have replies (Table3) by User1. I have been going crazy trying joins - lefts, rights, inners, outers... Any help would be greatly appreciated!! SELECT table1.myfield1, table2.myfield2 FROM table1 INNER JOIN table2 ON(table1.key=table2.key AND table1.myfield1=something) LEFT JOIN table3 ON (table2.myfield2=table3.myfield2 AND table3.myfield3=whatever) WHERE table3.myfield2 IS NULL; -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with check syntax
Aaron P. Martinez [EMAIL PROTECTED] wrote: I am trying to set up a table from a script that came with some software Value accounting/CRM and i'm getting a few errors, one of which i can't seem to figure out/fix. My system is RH 3.0 ES with mysql-server-3.23.58-1. I have innodb tables configured with the following statement in my /etc/my.cnf: innodb_data_file_path = ibdata1:10M:autoextend set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 The create table statement is below followed by the error. create table ItemSalesTax ( STYPE integer not null primary key, /* STax Type */ SDESC varchar(35), SPERC numeric(13,4) zerofill not null /* Percentage */ check(SPERC = 0), SCONUM integer not null, SYRNO integer not null, SLEVEL varchar(4) not null, /* Access Control Level */ constraint staxlevel_ck check (SLEVEL in ('READ','RW','DENY')), constraint STax_fk foreign key (SCONUM, SYRNO) references AccYear(ACONUM, AYEARNO) ); ERROR 1064: You have an error in your SQL syntax near 'check(SPERC = 0), SCONUM integer not null, SYRNO i' at line 9 I am not great w/mysql but gradually learning. I have looked in the online manual and can't find anything that helps. I would really like to get this going as soon as possible to evaluate...any and all help is GREATLY appreciated. The above create table statement works fine for me on MySQL version 4.0.17. Note: Currently CHECK clause does nothing in MySQL: http://www.mysql.com/doc/en/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql if you could send errors you are getting that would be a help for us One thing, you don't use mysqldump from the mysql prompt, you use it from the msyql directory on your C:/Drive --example C:\mysqlbin/mysqldump -u yourusername -pyoupassword --alldatabases outfile.sql specifying the password in the string is not the best way to go hth Jeff If I can't get the dump file to work, how do I get the database on my local machine up to the remote server. I am using version 4.0.15. Are there any bugs? Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
On Tue, 13 Jan 2004 14:07:38 +, Matthew Stuart [EMAIL PROTECTED] wrote: I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql mysqldump -udavidrayner -pdavidrayner eeetic eeetic.sql mysqldump -udavidrayner -pdavidrayner -A all.sql zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
Matthew Stuart wrote: I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql Run mysqldump from a shell (DOS) prompt, not from the mysql client. Something like: prompt% mysqldump -u root -prootpassword csi_db01 :: will dump to standard out; add the path to where you want to save the dump, like: prompt% mysqldump -uroot -p csi_db01 /path/to/dumpfile.sql I'm not sure how a path with spaces -- My Documents -- is going to work, but experiment (or pick another location). :-) HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
mysqldump is run at the system command-line, not within the mysql client environment. This is most likely to be my problem then. I assumed that what is called the system command line to be the mysql client environment. The tutorial book that I have been going through instructs me to issue this command in the start menus run dialogue box: C:\Windows\Desktop cd C:\mysql\bin and from there I have issued all commands in the black window. Is this not the command line, and if not, what is? Sorry for my ignorance, I am still a beginner. Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
On Tue, 13 Jan 2004 14:07:38 +, Matthew Stuart [EMAIL PROTECTED] wrote: Similarly. SELECT intDEVID,txtDEVPOSTCODE INTO OUTFILE c:/aaa/dump.sql from ytbl_development; (dump.sql file must NOT already exist) zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
Matthew Stuart wrote: mysqldump is run at the system command-line, not within the mysql client environment. This is most likely to be my problem then. I assumed that what is called the system command line to be the mysql client environment. The tutorial book that I have been going through instructs me to issue this command in the start menus run dialogue box: C:\Windows\Desktop cd C:\mysql\bin and from there I have issued all commands in the black window. Is this not the command line, and if not, what is? That is the command line. Open a new Command Prompt in windows (something like Start-Programs-Accessories-Command Prompt) Then type cd c:\mysql assuming that is where you installed MySQL then type : bin\mysqldump -uusername -ppassword dbname csi_db01 c:\csi_db01.sql This will create a file called csi_db01.sql in the base of your C: drive that contains all the SQL needed to recreate your table. Note that the c:\mysql isn't strictly needed - and it would work just as well to type c:\mysq\bin\mysqldump in any folder in your system. I'm sure this functionality definately works, so if you can't make it work then post back to the list and someone will realise what mistake you're making. Andrew [Also - most individuals choose not to disclose address and telephone numbers on public mailing lists because they are so widely distributed - espically popular ones like this. I'd suggest removing that information from your signature. But it's just a suggestion ;) ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help DB Error: unknown error
Hi Thai, I think you're just joining wy too many tables! LOL Matt - Original Message - From: Thai Thanh Ha Sent: Sunday, November 16, 2003 8:10 PM Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID = 79 AND t11.FieldValue = 1 AND t1.UserID=t12.UserID AND t12.FieldID = 79 AND t12.FieldValue = 2 AND t1.UserID=t13.UserID AND t13.FieldID = 79 AND t13.FieldValue = 5 AND t1.UserID=t14.UserID AND t14.FieldID = 79 AND t14.FieldValue = 6 AND t1.UserID=t15.UserID AND t15.FieldID = 79 AND t15.FieldValue = 4 AND t1.UserID=t16.UserID AND t16.FieldID = 131 AND t16.FieldValue = 4 AND t1.UserID=t17.UserID AND t17.FieldID = 131 AND t17.FieldValue = 2 AND t1.UserID=t18.UserID AND t18.FieldID = 131
Re: Please help DB Error: unknown error
Typically, this is the kind of query I see at MS-SQL houses. :) -- R. Deuce - Original Message - From: Matt W [EMAIL PROTECTED] To: Thai Thanh Ha [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:28 PM Subject: Re: Please help DB Error: unknown error Hi Thai, I think you're just joining wy too many tables! LOL Matt - Original Message - From: Thai Thanh Ha Sent: Sunday, November 16, 2003 8:10 PM Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile ... snip ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help DB Error: unknown error
At 9:10 +0700 11/17/03, Thai Thanh Ha wrote: Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Joins can join up to 31 tables in MySQL. Looks like you're exceeding that limit. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID = 79 AND t11.FieldValue = 1 AND t1.UserID=t12.UserID AND t12.FieldID = 79 AND t12.FieldValue = 2 AND t1.UserID=t13.UserID AND t13.FieldID = 79 AND t13.FieldValue = 5 AND t1.UserID=t14.UserID AND t14.FieldID = 79 AND t14.FieldValue = 6 AND t1.UserID=t15.UserID AND t15.FieldID = 79 AND t15.FieldValue = 4 AND t1.UserID=t16.UserID AND t16.FieldID = 131 AND t16.FieldValue = 4 AND t1.UserID=t17.UserID AND t17.FieldID = 131 AND t17.FieldValue = 2 AND t1.UserID=t18.UserID AND t18.FieldID = 131 AND t18.FieldValue = 1 AND t1.UserID=t19.UserID AND t19.FieldID = 131 AND t19.FieldValue = 3 AND t1.UserID=t20.UserID AND t20.FieldID = 137 AND
RE: Please help DB Error: unknown error
Can you please post the error? -Original Message- From: Thai Thanh Ha [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 8:10 PM To: '[EMAIL PROTECTED]' Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID = 79 AND t11.FieldValue = 1 AND t1.UserID=t12.UserID AND t12.FieldID = 79 AND t12.FieldValue = 2 AND t1.UserID=t13.UserID AND t13.FieldID = 79 AND t13.FieldValue = 5 AND t1.UserID=t14.UserID AND t14.FieldID = 79 AND t14.FieldValue = 6 AND t1.UserID=t15.UserID AND t15.FieldID = 79 AND t15.FieldValue = 4 AND t1.UserID=t16.UserID AND t16.FieldID = 131 AND t16.FieldValue = 4 AND t1.UserID=t17.UserID AND t17.FieldID = 131 AND t17.FieldValue = 2 AND t1.UserID=t18.UserID AND t18.FieldID = 131 AND t18.FieldValue = 1 AND t1.UserID=t19.UserID AND t19.FieldID =
RE: Please help DB Error: unknown error
I see the only message DB Error: unknown error. Even when I run mysqld.exe with --log and --error-log options, I still cannot get a more descriptive error message. I guess the problem is because of the number of joins is too large or the number of *temporary* rows is too large. But I don't know how to resolve this problem. Regards, Thai -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 8:02 PM To: 'Thai Thanh Ha'; '[EMAIL PROTECTED]' Subject: RE: Please help DB Error: unknown error Can you please post the error? -Original Message- From: Thai Thanh Ha [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 8:10 PM To: '[EMAIL PROTECTED]' Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID
RE: Please help. MySQL Error.
do a show status like 'open_files' and a show variables like 'open_files_limit' -Original Message- From: William Bailey [mailto:[EMAIL PROTECTED] Sent: Friday, November 14, 2003 6:56 AM To: [EMAIL PROTECTED] Subject: Please help. MySQL Error. -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I am currently getting the following error on one of the mysql servers im looking after and wonder if anybody knows what specifically it relates to. Error in accept: Too many open files Im currently running MySQL version '4.0.14' under FreeBSD 5.1 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/tNDpzSfrYDJMXmERAvKBAKC6vY0PnowjAaI8sRIIu+Mumeum8gCfVWAH hRU4PeRdpbIGgWPI9/xWVJY= =wHd+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help out
Easy, use:'select * INTO OUTFILE full path and file name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY \n from user where race='chinese' and state='US'Nitin- Original Message - From: Pey Ling [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:51 PM Subject: please help out Dear all, Hi, i have a database called 'admin' and a table called 'user'. Inside table 'user', there are a lot of fields, i.e: name, email, race, state, and etc. There are totally 10 records. When I wrote the sql query 'select * from user where race='chinese' and state='US''. It shows me more than 1000 results. Now, how can I save only these 1000++ data into a file in my pc? Please help out. Thanks. best regards, Pey Ling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help out
Hi, how to write this :full path and file name? can i copy it to my local pc rather than the server? best regards, Pey Ling From: Nitin [EMAIL PROTECTED] To: Pey Ling [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 5:09 PM Subject: Re: please help out Easy, use:'select * INTO OUTFILE full path and file name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY \n from user where race='chinese' and state='US'Nitin- Original Message - From: Pey Ling [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:51 PM Subject: please help out Dear all, Hi, i have a database called 'admin' and a table called 'user'. Inside table 'user', there are a lot of fields, i.e: name, email, race, state, and etc. There are totally 10 records. When I wrote the sql query 'select * from user where race='chinese' and state='US''. It shows me more than 1000 results. Now, how can I save only these 1000++ data into a file in my pc? Please help out. Thanks. best regards, Pey Ling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help out
no, it'll save it in the data directory of the database only, reason is mysql doesn't have permission to write to any other directory. full path and file name could be just file name like data.txt or full path of that directory like /var/lib/mysql/db_name/data.txt Nitin - Original Message - From: Pey Ling [EMAIL PROTECTED] To: Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 3:31 PM Subject: Re: please help out Hi, how to write this :full path and file name? can i copy it to my local pc rather than the server? best regards, Pey Ling From: Nitin [EMAIL PROTECTED] To: Pey Ling [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 5:09 PM Subject: Re: please help out Easy, use:'select * INTO OUTFILE full path and file name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY \n from user where race='chinese' and state='US'Nitin- Original Message - From: Pey Ling [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:51 PM Subject: please help out Dear all, Hi, i have a database called 'admin' and a table called 'user'. Inside table 'user', there are a lot of fields, i.e: name, email, race, state, and etc. There are totally 10 records. When I wrote the sql query 'select * from user where race='chinese' and state='US''. It shows me more than 1000 results. Now, how can I save only these 1000++ data into a file in my pc? Please help out. Thanks. best regards, Pey Ling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: please help out
Now, how can I save only these 1000++ data into a file in my pc? Normaly you should get the data into a file with that something you sent the query to mysql with. What is that something in your case? Regards, TomH -- PROSOFT EDV-Lösungen GmbH Co. KG Geschäftsführer: Axel-Wilhelm Wegmann AG Regensburg HRA 6608 USt.183/68311 Verwaltung : 93053 Regensburg, Stadlerstraße 13 office : 93049 Regensburg, Ladehofstraße 28 www: http://www.proSoft-Edv.de email : [EMAIL PROTECTED] phone : +49 941 / 78 88 7 - 121 fax: +49 941 / 78 88 7 - 20 cellphone : +49 174 / 41 94 97 0 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please Help
I'd cross post to the mysql-java/jdbc mailing list... Most likely you need to modify mysql config to allow larger packet sizes.. search the list archive/website for max_allowed_packet info.. On Fri, 8 Aug 2003, Ma Mei wrote: Dear administrator, Now I have a quesion and want to get your help. When I insert an image file data (data size 64KB) to a BLOB field of MySQL database by com.mysql.jdbc.driber,there is error. Error message as follows: aq.executeQuery:Communication link failure:comm.mysql.jdbc.packetTooBigException The part of my program as follows: .. FileInputStream fis = new FileInputStream(untitl2.gif); .. conn = DriverManager.getConnection(jdbc:mysql://dbgserver.ihep.ac.cn/bsrf?user=bsrfpassword=bsrfuseUnicode=truecharacterEncoding=Gb2312); String ins =insert into myimg values(?,?); PreparedStatement stmt = conn.prepareStatement(ins); System.out.println(Test1*); stmt.setInt(1,1001); try { int len= fis.available(); System.out.println(len); stmt.setBinaryStream(2,fis,len); System.out.println(Test***2*); int rowsupdated = stmt.executeUpdate(); // When program run in here , it appears error. System.out.println(Test3*); System.out.println(RowsUpdated= +rowsupdated); } catch(IOException ex) { System.out.println(IOException:+ex.getMessage());} Could you help to relve this quesion as soon as. Thank you very much. I am looking forward ro hearing from you. Best Regards, Ma Mei --- Ma Mei Computing Center Institute of High Energy Physics P.O.Box 918 Ext.7 Beijing 100039 P.R. China Phone: (8610) 88235037 FAX: (8610) 88236839 E-Mail: [EMAIL PROTECTED] - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please HELP Romanian charset Collate in MySQL
Iulian, this is in the manual. Look at Database Administration Localisation Character arrays Regards, Thomas Spahni On Wed, 30 Jul 2003, Primaria Falticeni wrote: I can copy the latin2.conf into new one, but I need to know what each code from the conf file represents and how can I obtain the relation from these codes and the chars.So that's two problems: 1) Can I use asc function or what function I need to know to obtain the codes from the chars? 2) How is the way to arrange the codes in the conf file? Assuming that I know the codes for each characters (found at the above point) how can I put these codes in the three tables within the latin2.conf file? - Original Message - From: Primaria Falticeni [EMAIL PROTECTED] To: MySQL LIST [EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:00 PM Subject: Please HELP Romanian charset Collate in MySQL Hello, Simply I made a table with romanian characters aAîÎâÂsStT. I tried to sort it, in fact to order it in a query. I settled the default_charset to some charsets: latin1(default)(latin2 win1250(central european)) ÎteIbur Ibur Îte ItoIto The correct result must be: Ibur, Ito, Îte in ascending order. How can I do this? Please give me an example at how can I change the latin2.conf in a romanian one to fairly sort the chars for me. I must do change the behaviour and I don't know how. Thanks Anticipated, Iulian Teodosiu Economist/Analyst Programmer Primaria Falticeni Falticeni (town), jud. Suceava Romania, Europe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please HELP Romanian charset Collate in MySQL
I can copy the latin2.conf into new one, but I need to know what each codefrom the conf file represents and how can I obtain the relation from thesecodes and the chars.So that's two problems:1) Can I use asc function or what function I need to know to obtain thecodes from the chars?2) How is the way to arrange the codes in the conf file? Assuming that Iknow the codes for each characters (found at the above point) how can I putthese codes in the three tables within the latin2.conf file?- Original Message -From: "Primaria Falticeni" [EMAIL PROTECTED]To: "MySQL LIST" [EMAIL PROTECTED]Sent: Monday, July 28, 2003 10:00 PMSubject: Please HELP Romanian charset Collate in MySQL Hello, Simply I made a table with romanian characters "aAîÎâÂsStT". I tried to sort it, in fact to order it in a query. I settled the default_charset to some charsets: latin1(default) (latin2 win1250(central european)) Îte Ibur Ibur Îte Ito Ito The correct result must be: Ibur, Ito, Îte in ascending order. How can I do this? Please give me an example at how can I change the latin2.conf in a romanian one to fairly sort the chars for me. I must do change the behaviour and I don't know how. Thanks Anticipated, Iulian Teodosiu Economist/Analyst Programmer Primaria Falticeni Falticeni (town), jud. Suceava Romania, Europe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: Please HELP Romanian charset Collate in MySQL
Hi! On Jul 28, Primaria Falticeni wrote: Hello, Simply I made a table with romanian characters aAsStT. I tried to sort it, in fact to order it in a query. How can I do this? Please give me an example at how can I change the latin2.conf in a romanian one to fairly sort the chars for me. I must do change the behaviour and I don't know how. check http://www.mysql.com/doc/en/Localisation.html Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please help me!
As this is really a PHP specific question, you may get a better response on the PHP general mailing list at http://www.php.net/mailing-lists.php Anyway, if my limited experience in PHP serves correctly, you may need to call echo(mysql_error()); near the potentially troublesome code. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Varghonan [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2003 10:36 AM To: [EMAIL PROTECTED] Subject: Please help me! Hi! I am new here and wonder if anyone could help me with a problem in my community. The adress is http://trashankarna.net/community/default.php I can't figure this out. I get this message when I choose to read a message in my outbox/utkorg: Warning: 1 is not a valid MySQL-Link resource in C:\apache\htdocs\community\meddelanden\lasaut.php on line 69 I can read the message, anyway. My lasaut.php looks like this: ?php @session_start(); @$user = $iwcuser; @$pass = $iwcpass; if(!$user) { echo(script language=JavaScript); echo(self.location.replace('http://trashankarna.net/community/login/ouch.php')); echo(/script); } $open = mysql_connect(localhost, community, tarot); $info = mysql_db_query(iwcusers, SELECT * FROM users WHERE username = '$user', $open); $rad = mysql_fetch_array($info); if($user == $rad[username] $pass == $rad[password]) { ? ?php // top.inc ? ?php require ('c:\\apache\htdocs\community\_inc\top.inc'); ? ?php // Innehåll ? table cellpadding=0 cellspacing=0 style=border-width: 1pt; border-color: #00; border-style: solid align=left tr td class=menux width=20/td td class=menuxbrspan class=rubrikLäs e-postmeddelande/spanbrimg src=/community/_media/line.gif vspace=5 height=1 width=100%/td td class=menux width=10/td /tr tr td class=menux width=10/td td class=menux width=600 height=200 valign=topspan class=brodtext a href=/community/meddelanden/skriv.php class=bodylankSkriv ett e-postmeddelande/a | a href=/community/meddelanden/adressbok.php class=bodylankMin adressbok/a | a href=/community/meddelanden/mappar.php class=bodylankSkapa mapp/a | p ?php $open = mysql_connect(localhost, community, tarot); $db = mysql_db_query(iwepost, SELECT * FROM meddelanden WHERE id = '$id'); $rad = mysql_fetch_array ($db); print $rad[subject]; print | ; print $rad[datum]; print brbr; print pre class=body; print $rad[body]; print /pre; print Tillbaka till a href=/community/meddelanden/default.php class=bodylankinkorgen/a; mysql_close($open); ? p ?php // bottom.inc ? ?php require ('c:\\apache\htdocs\community\_inc\bottom.inc'); ? ?php mysql_close($open); ? ?php } ? My lasa.php looks like this: ?php @session_start(); @$user = $iwcuser; @$pass = $iwcpass; if(!$user) { echo(script language=JavaScript); echo(self.location.replace('http://trashankarna.net/community/login/ouch.php')); echo(/script); } $open = mysql_connect(localhost, community, tarot); $info = mysql_db_query(iwcusers, SELECT * FROM users WHERE username = '$user', $open); $rad = mysql_fetch_array($info); if($user == $rad[username] $pass == $rad[password]) { ? ?php // top.inc ? ?php require ('c:\\apache\htdocs\community\_inc\top.inc'); ? ?php // Innehåll ? table cellpadding=0 cellspacing=0 style=border-width: 1pt; border-color: #00; border-style: solid align=left tr td class=menux width=20/td td class=menuxbrspan class=rubrikLäs e-postmeddelande/spanbrimg src=/community/_media/line.gif vspace=5 height=1 width=100%/td td class=menux width=10/td /tr tr td class=menux width=10/td td class=menux width=600 height=200 valign=topspan class=brodtext a href=/community/meddelanden/skriv.php class=bodylankSkriv ett e-postmeddelande/a | a href=/community/meddelanden/adressbok.php class=bodylankMin adressbok/a | a href=/community/meddelanden/mappar.php class=bodylankSkapa mapp/a | p ?php $open = mysql_connect(localhost, community, tarot); $db = mysql_db_query(iwepost, SELECT * FROM meddelanden WHERE id = '$id'); $rad = mysql_fetch_array ($db); print $rad[subject]; print | ; print $rad[datum]; print brbr; print pre class=body; print $rad[body]; print /pre; print Tillbaka till a href=/community/meddelanden/default.php class=bodylankinkorgen/a; mysql_close($open); $open = mysql_connect(localhost, community, tarot); mysql_db_query(iwepost, UPDATE meddelanden SET last = 'j' WHERE id = '$id'); ? p ?php // bottom.inc ? ?php require ('c:\\apache\htdocs\community\_inc\bottom.inc'); ? ?php mysql_close($open);
Re: Please help: Can not insert binary data larger than 16 megabytes
Try using 'max_allowed_packet=16M' instead of your current value. You may also find help by reviewing the below. http://www.mysql.com/doc/en/Packet_too_large.html Is there a reason you are including the BLOBs in the database instead of just linking via it? I know it's generally considered better practice to do the latter. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, 06 June, 2003 17:11 Subject: Please help: Can not insert binary data larger than 16 megabytes Hello -- I am having trouble inserting binary data that is larger than 16 megabytes into a MySQL database table (binary data such as pdf's, gif's, jpeg's, etc). Can someone take a look at what I am doing and give me any suggestions. If I do insert binary data larger than 16 megabytes I get the following error code: Error 2020 : Got packet bigger than 'max_allowed_packet'. However with MySQL version 4.0.1 and up I should be able to insert binary data larger than 16 megabytes. And if you look below at the my.cnf file I have max_allowed_packet set to 524288000. I am running MySQL version: mysql Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Database table has the following fields: Binary_IdINT UNSIGNED NOT NULL AUTO_INCREMENT BinaryName VARCHAR(40) BinaryMIMEType VARCHAR(40) BinaryData LONGBLOB The my.cnf file in /etc looks like the following: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock key_buffer_size=500M max_allowed_packet=524288000 [mysql.server] user=mysql basedir=/var/lib key_buffer_size=500M max_allowed_packet=524288000 [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid key_buffer_size=500M max_allowed_packet=524288000 [mysql.client] key_buffer_size=500M max_allowed_packet=524288000 Here is the funny thing. If I do a 'mysql --help' I get the following in the help data: max_allowed_packet 16777216. Why would I get this when I have set max_allowed_packet in my.cnf. Can anyone help steer me in the right direction? What am I doing wrong? --Brad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please HELP!!! Re: Database Core Dumps
Stewart, Yesterday I posted the message listed below. I have some more information. We have found the command that pushes our database over the edge! It is: SHOW VARIABLES; All other db commands work (such as SHOW VARIABLES %a;) except for: SHOW VARIABLES % This should be SHOW VARIABLES LIKE '%'. I tested all variants with 4.0.10, no problems. SHOW VARIABLES '%' gives me error 1064, as should be. Does anyone have any idea why this could be happening? In addition we are connecting to the database from a JSP Script using the driver: org.gjt.mm.mysql.Driver So your problem seems to be related to the driver, or to your JSP app. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please HELP!!! Re: Database Core Dumps
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 TRANTER,STEWART (HP-Germany,ex1) wrote: | Hello Everyone, | | Yesterday I posted the message listed below. I have some more information. | We have found the command that pushes our database over the edge! It is: | | SHOW VARIABLES; | | All other db commands work (such as SHOW VARIABLES %a;) except for: | | SHOW VARIABLES % | | Does anyone have any idea why this could be happening? In addition we are | connecting to the database from a JSP Script using the driver: | | org.gjt.mm.mysql.Driver | | The JSP page doesn't actually explicitly call the SHOW VARIABLES; command, | but I guess (?) that this is called explicitly in the driver connection | somehow... We confirmed that it was the SHOW VARIABLES; command by checking | the log file (with --log and --log-update options). We then tried this at | the MySQL command line and it caused the db to restart. | | Could this mean that one of the variables displayed by the SHOW VARIABLES; | command is causing some form of problem? | | Once again, many thanks in advance for taking the time to read this mail, | and I hope someone can help! | | The very best of regards, | | Stewart | ..._/.. | Stewart Tranter[EMAIL PROTECTED] _/ | Hewlett-Packard GmbH Tel: (+49) 7031 14-1554 _/_/_/_/ | _/_/_/_/ | Software Engineering Services Fax: (+49) 7031 14-4961 _/_/ _/_/ | Schickard Str.25 Mob: (+49) 1784 701 079 _/_/ _/_/ | 71034 Böblingen _/_/ | _/_/_/_/ | Germany_/ | https://ecardfile.com/id/stewarttranter | https://ecardfile.com/id/stewarttranter _/ | ... | Management and security solutions. We make your e-solutions work. | On budget. On time. Every time. | External Web Site: http://www.hp.com/hps/ http://www.hp.com/hps/ | Internal Web Site: http://ses.cup.hp.com/ http://ses.cup.hp.com/ | ... | | MESSAGE FROM 2nd April 2003: | | Hello, | | I have a MySQL Database with two instances running. They were both working | 100% normally, until yesterday. When we connect to one instance we get the | following error: | | mysqld got signal 10; | This could be because you hit a bug. It is also possible that this binary | or one of the libraries it was linked against is corrupt, improperly built, | or misconfigured. This error can also be caused by malfunctioning hardware. | We will try our best to scrape up some info that will hopefully help | diagnose | the problem, but since we have already crashed, something is definitely | wrong | and this may fail | | key_buffer_size=16773120 | record_buffer=131072 | sort_buffer=524280 | max_used_connections=0 | max_connections=100 | threads_connected=1 | It is possible that mysqld could use up to | key_buffer_size + (record_buffer + sort_buffer)*max_connections = 80379 K | bytes of memory | Hope that's ok, if not, decrease some variables in the equation | | 030402 11:32:22 mysqld restarted | | The other instance however works totally fine! The server diskspace is ok, | we rebooted the server, everything looks ok! | | We are running HP-UX B.11.11 U 9000/800 839927861 unlimited-user license | with MySQL version: Ver 3.23.54-max for hp-hpux11.11 on hppa2.0w | | Can anyone offer any suggestions please? We are all totally stumped!!! | | Many thanks in advance and best regards, | | Stewart There is a known issue on 64 bit platforms with MySQL versions 3.23.56 causing a crash when 'SHOW VARIABLES' is executed. You are right in thinking that the JDBC driver issues this query, because it does. The JDBC driver uses the results of 'SHOW VARIABLES' to set many internal parameters related to various capabilities that different versions of MySQL and different configurations of MySQL have. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma ~__ ___ ___ __ ~ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] ~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java ~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ~___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+jDA+tvXNTca6JD8RAtiDAJ9EOsjZ8fE0T79BYC8qv+qJq7RubwCfcWp9 lo7USihZqgkfQQ7izJSTaxI= =p7oA -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP!!! Re: Database Core Dumps
Hi Mark and Steffan, We solved this problem today and it was as Mark details below. We have installed the latest version available, and it now works fine :-) Phew! Thanks very much to everyone for their help, Best regards, Stewart -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: 03 April 2003 15:00 To: TRANTER,STEWART (HP-Germany,ex1) Cc: '[EMAIL PROTECTED]' Subject: Re: Please HELP!!! Re: Database Core Dumps -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 TRANTER,STEWART (HP-Germany,ex1) wrote: | Hello Everyone, | | Yesterday I posted the message listed below. I have some more information. | We have found the command that pushes our database over the edge! It is: | | SHOW VARIABLES; | | All other db commands work (such as SHOW VARIABLES %a;) except for: | | SHOW VARIABLES % | | Does anyone have any idea why this could be happening? In addition we | are connecting to the database from a JSP Script using the driver: | | org.gjt.mm.mysql.Driver | | The JSP page doesn't actually explicitly call the SHOW VARIABLES; | command, but I guess (?) that this is called explicitly in the driver | connection somehow... We confirmed that it was the SHOW VARIABLES; | command by checking | the log file (with --log and --log-update options). We then tried | this at the MySQL command line and it caused the db to restart. | | Could this mean that one of the variables displayed by the SHOW | VARIABLES; command is causing some form of problem? | | Once again, many thanks in advance for taking the time to read this | mail, and I hope someone can help! | | The very best of regards, | | Stewart | ..._/.. | Stewart Tranter[EMAIL PROTECTED] _/ | Hewlett-Packard GmbH Tel: (+49) 7031 14-1554 _/_/_/_/ | _/_/_/_/ | Software Engineering Services Fax: (+49) 7031 14-4961 _/_/ _/_/ | Schickard Str.25 Mob: (+49) 1784 701 079 _/_/ _/_/ | 71034 Böblingen _/_/ | _/_/_/_/ | Germany_/ | https://ecardfile.com/id/stewarttranter | https://ecardfile.com/id/stewarttranter _/ | ... | Management and security solutions. We make your e-solutions work. | On budget. On time. Every time. External Web Site: | http://www.hp.com/hps/ http://www.hp.com/hps/ Internal Web Site: | http://ses.cup.hp.com/ http://ses.cup.hp.com/ | ... | | MESSAGE FROM 2nd April 2003: | | Hello, | | I have a MySQL Database with two instances running. They were both working | 100% normally, until yesterday. When we connect to one instance we get the | following error: | | mysqld got signal 10; | This could be because you hit a bug. It is also possible that this | binary or one of the libraries it was linked against is corrupt, | improperly built, | or misconfigured. This error can also be caused by malfunctioning hardware. | We will try our best to scrape up some info that will hopefully help | diagnose the problem, but since we have already crashed, something is | definitely wrong | and this may fail | | key_buffer_size=16773120 | record_buffer=131072 | sort_buffer=524280 | max_used_connections=0 | max_connections=100 | threads_connected=1 | It is possible that mysqld could use up to | key_buffer_size + (record_buffer + sort_buffer)*max_connections = | 80379 K bytes of memory Hope that's ok, if not, decrease some | variables in the equation | | 030402 11:32:22 mysqld restarted | | The other instance however works totally fine! The server diskspace is ok, | we rebooted the server, everything looks ok! | | We are running HP-UX B.11.11 U 9000/800 839927861 unlimited-user | license with MySQL version: Ver 3.23.54-max for hp-hpux11.11 on | hppa2.0w | | Can anyone offer any suggestions please? We are all totally | stumped!!! | | Many thanks in advance and best regards, | | Stewart There is a known issue on 64 bit platforms with MySQL versions 3.23.56 causing a crash when 'SHOW VARIABLES' is executed. You are right in thinking that the JDBC driver issues this query, because it does. The JDBC driver uses the results of 'SHOW VARIABLES' to set many internal parameters related to various capabilities that different versions of MySQL and different configurations of MySQL have. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma ~__ ___ ___ __ ~ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] ~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java ~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ~___/ www.mysql.com
re: please help me
On Friday 28 March 2003 13:59, Ing.Peter Misovic wrote: i have deleted mysql database, can i restore it with start defaults ? mysql_install_db is your solution. :) Thailon, Slovakia -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help me - problem with innodb foreign keys
Natale, please address these general questions to [EMAIL PROTECTED] InnoDB requires that the indexes are created explicitly. It would require a change to MySQL to automate the creation of them. Without indexes foreign key checks would be very slow because then every check would have to do a table scan. Regards, Heikki Innobase Oy sql query - Original Message - From: Natale Babbo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 14, 2003 5:52 PM Subject: please help me - problem with innodb foreign keys # - PLEASE HELP -- # hi, i've a problem with innodb foreign keys creation as following: is it still true that mysql/innodb needs explicit index creation on foreign keys? why can't i use a standard syntax for foreign keys creations? i have a database schema (ddl) with over 50 tables and i was trying to create the database on mysql when i receive a lot of errors like this: ERROR 1005: Can't create table (errno 150) how can i create the database without creating explicitly an index on each foreign keys of my database? any suggestions are appreciated. Please help me. Many thanks. __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please help with strange Win2K error
Hi, Perror 13 means : perror 13 Error code 13: Permission denied So it seems you don't have the permission to create the temporary table is this directory. Regards, Jocelyn - Original Message - From: Stefan Hinz [EMAIL PROTECTED] To: MySQL Main List [EMAIL PROTECTED] Sent: Monday, November 18, 2002 6:08 PM Subject: Please help with strange Win2K error Dear list, sorry for bothering you again, but no one has answered to my request for help so far, not even Jeremy Z. who *I know* can solve every and any problem remotely ;-) On Win2K with MySQL 4.0.4 I get this error with a MyISAM table: mysql ALTER TABLE auftrag CHANGE Nummer Nummer INT UNSIGNED NOT NULL PRIMARY KEY; ERROR 7: Error on rename of '.\trainee\auftrag.MYI' to '.\trainee\#sql2-648-2.MYI' (Errcode: 13) Trying a workaround, I switched the table type of this table to innoDB. Now, I can issue the above statement without getting an error. However, innoDB tables have a MyISAM style .frm file. I get a quite similar error when I try the following: mysql ALTER TABLE auftrag ADD FOREIGN KEY (Auftraggeber) REFERENCES auftraggeber(Schl); ERROR 1005: Can't create '.\trainee\#sql-6fc_2.frm'. (Error: 150) Seems to be an (internal) temporary table problem. Does anyone know what's happening, and how to solve this prob? Any help is greatly appreciated. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Please help with strange Win2K error
Dear Jocelyn, On Win2K with MySQL 4.0.4 I get this error with a MyISAM table: mysql ALTER TABLE auftrag CHANGE Nummer Nummer INT UNSIGNED NOT NULL PRIMARY KEY; ERROR 7: Error on rename of '.\trainee\auftrag.MYI' to '.\trainee\#sql2-648-2.MYI' (Errcode: 13) Perror 13 means : perror 13 Error code 13: Permission denied So it seems you don't have the permission to create the temporary table is this directory. First thing I was thinking of. Unfortunately, this is not the problem. If there would actually _be_ a permissions problem, the server would have it, meaning it would not be able to write to this directory. But I can CREATE tables there, so why not temporary ones? Thanks anyway for trying to help! Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Jocelyn Fournier [EMAIL PROTECTED] To: Stefan Hinz [EMAIL PROTECTED]; MySQL Main List [EMAIL PROTECTED] Sent: Monday, November 18, 2002 8:32 PM Subject: Re: Please help with strange Win2K error Hi, Perror 13 means : perror 13 Error code 13: Permission denied So it seems you don't have the permission to create the temporary table is this directory. Regards, Jocelyn - Original Message - From: Stefan Hinz [EMAIL PROTECTED] To: MySQL Main List [EMAIL PROTECTED] Sent: Monday, November 18, 2002 6:08 PM Subject: Please help with strange Win2K error Dear list, sorry for bothering you again, but no one has answered to my request for help so far, not even Jeremy Z. who *I know* can solve every and any problem remotely ;-) On Win2K with MySQL 4.0.4 I get this error with a MyISAM table: mysql ALTER TABLE auftrag CHANGE Nummer Nummer INT UNSIGNED NOT NULL PRIMARY KEY; ERROR 7: Error on rename of '.\trainee\auftrag.MYI' to '.\trainee\#sql2-648-2.MYI' (Errcode: 13) Trying a workaround, I switched the table type of this table to innoDB. Now, I can issue the above statement without getting an error. However, innoDB tables have a MyISAM style .frm file. I get a quite similar error when I try the following: mysql ALTER TABLE auftrag ADD FOREIGN KEY (Auftraggeber) REFERENCES auftraggeber(Schl); ERROR 1005: Can't create '.\trainee\#sql-6fc_2.frm'. (Error: 150) Seems to be an (internal) temporary table problem. Does anyone know what's happening, and how to solve this prob? Any help is greatly appreciated. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Please Help Can't Build
Hi there! After include /usr/local/bin on the library path as was suggested by ED appears to work because isn't complaining about any more but It broke again on something different. Please any Idea? Thanks Jose Albert Here the output \... I'm still trying to build mysql-3.23.51 /local/mysql/share/mysql\ -DHAVE_CONFIG_H - I../innobase/include - I./../include -I./../regex-I. - I../include -I.. -I.-O3 -DDBUG_OFF -fno-implicit-templates - fno-exceptions -fno-rtti -DHAVE_RWLOCK_T -c mysqld.cc mysqld.cc: In function `void* handle_connections_sockets(void*)': mysqld.cc:2394: invalid conversion from `size_socket*' to `socklen_t*' mysqld.cc:2460: invalid conversion from `size_socket*' to `socklen_t*' make[3]: *** [mysqld.o] Error 1 make[3]: Leaving directory `/web/compile/mysql-3.23.51/sql' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/web/compile/mysql-3.23.51/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/web/compile/mysql-3.23.51' 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
RE: RE: Please Help Can't Build
Ed thanks for your replay But the libraries are there on the path PATH=/usr/local/lib:/usr/lib:/usr/local/:/usr/local/bin:/usr/bin:/u sr/ccs/bin/:/usr/ucb:/etc:. bash-2.05$ ls /usr/local/lib/libstdc++.so.5 /usr/local/lib/libstdc++.so.5 - Jose g++ -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno- rtti -DHAVE_RWLOCK_T -o gen_lex_hash gen_lex_hash.o ../isam/libnisam.a ../merge/libmerge.a ../myisam/libm yisam.a ../myisammrg/libmyisammrg.a ../heap/libheap.a ../mysys/libmy sys.a ../dbug/libdbug.a ../regex/libregex.a ../strings/libmystrings. a -lpthread -lthread -lz -lcrypt -lgen -lsocket -lnsl -lm - lpthread -lthread make[4]: Leaving directory `/web/compile/mysql-3.23.52/sql' ./gen_lex_hash lex_hash.h ld.so.1: ./gen_lex_hash: fatal: libstdc++.so.5: open failed: No such file or directory It's very obvious what the problem is. The g++ shared libraries aren't instelled or they aren't where they're supposed to be. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please Help!
On Tuesday, Oct 1, 2002, at 16:39 America/Phoenix, DeNewbie wrote: I am a newbie and I am having a chronic problem getting mysql started up and running. I got so frustrated that I uninstalled the mysql rpms and started over again but its hasn't helped my situation. My whole problem revolves around mysql_install_db setup. This is what I am doing and its simply giving me problems. All I want to do is enter the root and host paswords but I can't get past the root password setup. PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: /usr/bin/mysqladmin -u root password 'new-password' I input this; cords-orj86jfje:/usr/bin # mysqladmin -u root password sqlpass mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! How do I correct this situation. Why am I having such a hard time entering these passwords. I can't even enter the root password. I'm nearly giving up. It sounds like the server isn't running. You need to start it with cd /usr/local ./bin/safe_mysqld --user=mysql You may need to start the server as root. -- Clayburn W. Juniel, III -- Effective Software Solutions Phone: (602) 326-7707Mobile: (602)326-7707 Email: [EMAIL PROTECTED] http://EffectiveSoftwareSolutions.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: Please Help!
On Tue, 1 Oct 2002, DeNewbie wrote: I am a newbie and I am having a chronic problem getting mysql started up and running. I got so frustrated that I uninstalled the mysql rpms and started over again but its hasn't helped my situation. My whole problem revolves around mysql_install_db setup. This is what I am doing and its simply giving me problems. All I want to do is enter the root and host paswords but I can't get past the root password setup. PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: /usr/bin/mysqladmin -u root password 'new-password' I input this; cords-orj86jfje:/usr/bin # mysqladmin -u root password sqlpass mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! How do I correct this situation. Why am I having such a hard time entering these passwords. I can't even enter the root password. I'm nearly giving up. Hi, Really, please check that the server itself _is_ running _and_ the socket file exists at the mentioned location. If the server is down, start it in your mysql directory by entering bin/mysqld_safe (if using MySQL 4.x) or bin/safe_mysqld (if using 3.xx). Regards, Iikka ps. The manual has a section on those can't-connect things (at least I think I've seen this somewhere before). ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: please help with sql query
Hi there Try This: SELECT products.product_name, products.fg_number, images.image_name, images.thumbnail, images.image_path, images.color_depth, images.width_inches, images.height_inches, images.resolution, images.filesize, images.filetype, images.notes FROM products LEFT JOIN images ON products.fg_number = images.fg_number WHERE products.fg_number='$fg_number'; Regards Clive Michael Knauf/Niles wrote: I have two tables: products +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(6) | | PRI | NULL| auto_increment | | fg_number | varchar(9) | | MUL | 0 | | | product_name| varchar(64) | | MUL | | | | product_description | varchar(255) | | | | | +-+--+--+-+-++ and images +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(6) | | PRI | NULL| auto_increment | | fg_number | varchar(8) | | | || | image_name| varchar(64) | | | || | thumbnail | varchar(255) | YES | | NULL|| | image_path| varchar(255) | | | || | color_depth | enum('rgb','cmyk','greyscale','bitmap','vector') | YES | | NULL|| | width_inches | decimal(6,0) | YES | | 0 || | height_inches | decimal(6,0) | YES | | 0 || | resolution| decimal(6,0) | YES | | 0 || | filesize | varchar(36) | YES | | NULL|| | filetype | varchar(36) | YES | | NULL|| | notes | text | YES | | NULL|| +---+--+--+-+-++ I've got a web page (php) that's displaying the images available for a particular product using a query like: select products.product_name, images.fg_number, images.image_name, images.thumbnail, images.image_path, images.color_depth, images.width_inches, images.height_inches, images.resolution, images.filesize, images.filetype, images.notes from products, images where products.fg_number ='$fg_number' and images.fg_number = products.fg_number; Which works fine, as long as there is data in the images table corresponding to a product, but in the case where there are no images related to a particular fg_number, the query returns an empty set. What I'd like to get, is the products.product_name regardless of weather there are any corresponding images, so the variable $product_name will have a value on the php page and I can generate a message something like sorry there are no images of '$product_name' available... I can see how to do this with 2 queries, but shouldn't I be able to do it with one? and wouldn't I take a performance hit by running more than one query per page? Michael - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Please Help
Files never get smaller. They can only grow. Deleteing a record marks the area used as available, but does not remove it from the file. I believe you will have to optimize the table to pack out the unused records. This is another good argument for storing images in the filesystem, and only their locations in the database. [EMAIL PROTECTED] wrote: Hi I am a mysql user. I use a long blob field to store image data. But whenever I make a deletion with my image data I find that the hard disk availability is still intact. There is no change. What could be the problem with my delete statement. Please Help Regards Roslee AJ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Please help if you can...
Either I'm misunderstanding what you're trying to do or that Oracle query is a really complicated way of doing something simple. create table Customer ( name text, id int auto_increment primary key ); create table Skill ( description text, id int auto_increment primary key ); create table CustomerSkills ( customer int NOT NULL, skill int NOT NULL ); insert into Customer (name) values ('Steve'),('Harry'),('Rachel'),('Iggy'),('Harold'),('Saori'); select * from Customer; +++ | name | id | +++ | Steve | 1 | | Harry | 2 | | Rachel | 3 | | Iggy | 4 | | Harold | 5 | | Saori | 6 | +++ 6 rows in set (0.00 sec) insert into Skill (description) values ('Typing'),('Dancing'),('SQL'),('Modelling'),('Judo'),('Acrobatics'); select * from Skill; +-++ | description | id | +-++ | Typing | 1 | | Dancing | 2 | | SQL | 3 | | Modelling | 4 | | Judo| 5 | | Acrobatics | 6 | +-++ 6 rows in set (0.00 sec) insert into CustomerSkills (customer, skill) select Customer.id, Skill.id from Customer,Skill where Customer.name='Steve' and Skill.description in ('Typing','SQL'); insert into CustomerSkills (customer, skill) select Customer.id, Skill.id from Customer,Skill where Customer.name='Harry' and Skill.description in ('Typing','SQL','Judo'); insert into CustomerSkills (customer, skill) select Customer.id, Skill.id from Customer,Skill where Customer.name='Rachel' and Skill.description in ('Dancing'); insert into CustomerSkills (customer, skill) select Customer.id, Skill.id from Customer,Skill where Customer.name='Iggy' and Skill.description in ('Dancing','Judo'); insert into CustomerSkills (customer, skill) select Customer.id, Skill.id from Customer,Skill where Customer.name='Harold' and Skill.description in ('SQL','Modelling'); insert into CustomerSkills (customer, skill) select Customer.id, Skill.id from Customer,Skill where Customer.name='Saori' and Skill.description in ('Typing','Dancing','SQL','Modelling','Judo','Acrobatics'); select Customer.name, Skill.description from Customer,Skill,CustomerSkills where Customer.id = CustomerSkills.customer and Skill.id = CustomerSkills.skill; ++-+ | name | description | ++-+ | Steve | Typing | | Steve | SQL | | Harry | Typing | | Harry | SQL | | Harry | Judo| | Rachel | Dancing | | Iggy | Dancing | | Iggy | Judo| | Harold | SQL | | Harold | Modelling | | Saori | Typing | | Saori | Dancing | | Saori | SQL | | Saori | Modelling | | Saori | Judo| | Saori | Acrobatics | ++-+ 16 rows in set (0.00 sec) select Customer.name, Skill.description from Customer,Skill,CustomerSkills where Customer.id = CustomerSkills.customer and Skill.id = CustomerSkills.skill and Skill.description in ('SQL'); ++-+ | name | description | ++-+ | Steve | SQL | | Harry | SQL | | Harold | SQL | | Saori | SQL | ++-+ 4 rows in set (0.01 sec) select Customer.name, Skill.description from Customer,Skill,CustomerSkills where Customer.id = CustomerSkills.customer and Skill.id = CustomerSkills.skill and Skill.description in ('SQL','Dancing'); ++-+ | name | description | ++-+ | Steve | SQL | | Harry | SQL | | Rachel | Dancing | | Iggy | Dancing | | Harold | SQL | | Saori | Dancing | | Saori | SQL | ++-+ 7 rows in set (0.01 sec) Of course, you'll probably want to use Skill.id instead of skill.description for your WHERE... Were you trying to do something more complex than this? -rob On 7/6/02 at 2:52 pm, Hekuran Vokshi [EMAIL PROTECTED] wrote: Hi everybody, I'm new to MySQL and have got the following code that works with Oracle SQL... After looking at the documentation I realised that DECODE seems to be reserved for encryption... any idea of how this would work with MySQL? Here is the code: The tables are as follows tabletable table CUSTOMER CUSTOMER_SKILLS SKILL CUSTOMER_ID (p) CUSTOMER_ID(f) SKILL_ID(p) CUSTOMER_NAME SKILL_ID (f) SKILL_DESC (p) Primary Key (f) Foreign Key The idea is to select the customer(s) that have 'n' skill(s) and return the customer name and skill(s) description. The skills are know from the user input. SELECT DECODE (A.SKILLS_ID, B.SKILLS_ID, D.CUSTOMER_NAME, NULL) Customer Name, A.SKILL_DESC Skill Title FROM CUSTOMER_DETAILS D, CUSTOMER_SKILLS C, SKILLS B, SKILLS A WHERE C.SKILLS_ID = A.SKILLS_ID AND D.CUSTOMER_ID = C.CUSTOMER_ID AND A.SKILLS_ID IN (1,3,5) ANDB.SKILLS_ID= (SELECT MIN(A.SKILLS_ID)
Re: please help me understand users privileges
At 23:49 29/5/2002 -0400, Charles Brown wrote: Hi, I removed the user= and password= entries from the my.ini file, because I didn't like seeing a password exposed in plain text. This seemed to have no effect on MySQL at all. What are those entries for? Do I need them? What should they be? The MySQL account name and password? No you don't need them if you don't want that the tool queries the server for to see the variables, databases/tables and make reports, in another words them are used for to connect to the server, not for start/stop the server. However you need to consider that the my.ini file is locates at WinDir directory (WINNT\Windows) and for security you need to protect that directory with the Windows directives, in this case the my.ini file even in plain text should be protected. Regards, Miguel -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: please help a newbie!
Mohamadally, Monday, May 20, 2002, 4:53:05 AM, you wrote: M I believe this question is very simple and hope u ppl can M help me out. Well i'm trying to develop a site using M chinese characters where my users M can login. I'm suppose to store their username which will M be in chinese character in mysql . I tried looking for info M abt it but in vain. Does mysql support chinese char ? if M not wht shd i do... i can only use M mysql. M Mr Son nguyen can you please tell me how u did the magic of M using viet char? Take a look at such character sets as gbk, gb2312, big5. How to set up new character set read at: http://www.mysql.com/doc/C/h/Character_sets.html http://www.mysql.com/doc/M/u/Multi-byte_characters.html Currently you can run mysqld only with one charset. so, if you want to use another charset you should restart MySQL server or start another instance of mysqld. M thank you very much for your help and comprehension. M regards, M ally -- 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: please help: search database - code attached
Get rid of percentage signs in your keyword... See: http://www.mysql.com/doc/F/u/Fulltext_Search.html Gurhan -Original Message- From: Shehryar Shafiq [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 12:46 PM To: [EMAIL PROTECTED] Subject: please help: search database - code attached Hi, I have a table of jobs. I would like user to search the jobs on three columns: description, location, type, on the search page, i have one text field, and one drop down menu. the search field will be used for keywords against the description and location columns. the drop down will choose between the ONLY two types of entry for a type column (permanent and temp) here is my sql (i'm using mysql fulltext search) Select * from tbl_jobs WHERE MATCH (description, location) AGAINST ('%mm_keywords%') AND type = 'mm_type' i keep on getting empty sets. is there anything i am missing here? any help would be greatly appreciated. cheers Shez __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please Help Me
Whats your INSERT statement look like? And does it give any errors back? -Nick I am using MySql with Visual Basic. I Got an ERROR when trying to add a new record in situation as under. I have a table Company and a field in that table EmpName char (20) and one record is there value is = Piyush I am trying to add a new record I can add if the value of field is less then or equal to the length of old record len(Piyush) = 6.(Max Length of value from old records) If i will try to add Ashok i can add it BUT If I TRY TO ADD SANDEEP the length is 7 I could not add it. getting an error. and if there is not any record means maximum value from old record is zero then i could not add a record means clearly i could not add FIRST RECORD ANYWAY FORM VISUAL BASIC. what is solution Please help ME. Thanks in Advance - Piyush - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e- mail mysql- [EMAIL PROTECTED] To unsubscribe, e-mail mysql- unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please help - About doing a delete.
well i can do the select that way, but i was wondering how the following delete command would only pick up the rows in the select. isn't there a way of imbedding delete in the select statement? can anyone else help as well please? thanks sunny At 19:46 14/03/02 -0500, Jim Philips wrote: To select the rows you are looking for, you can use: select * from messages left join topics on topics.topic_id=messages.topic_id where topics.topic_id is null; This works fine for selecting. But for some reason the corresponding SQL for a delete fails: mysql delete from messages - left join topics on topics.topic_id=messages.topic_id - where topics.topic_id is null; ERROR 1064: You have an error in your SQL syntax near 'left join topics on topics.topic_id=messages.topic_id Anybody know how to make the delete statement work? On Thu, 2002-03-14 at 15:57, sunny wrote: How do i do a select first and then delete those rows? sorry.. im just confused. i tried that mysql, but it didn't work... gave me an error :( Any ideas? thanks! /sunny At 20:54 13/03/02 -0500, you wrote: What you need is a delete with a left join. Something like this: delete from messages left join messages on topics.topic_id=messages.topic_id where topics.topic_id is null; Try it first using a select instead of a delete to be sure it captures the right rows. But this ought to work. On Wed, 2002-03-13 at 17:53, sunny wrote: Hi everyone, I've got a really annoying problem with mysql, and I was hoping someone could help. I've got 2 tables in Mysql: Table 1 - Topics | topic_id | name | subject | comment | - | 1 | john | weather| the weather today blah blah| Table 2 - Messages |message_id | topic_id | name | subject | comment | - | 12| 1 | billy | yes it ...| i totally agree| Basically, in the messages table, each row has its own unique id, but also a topic_id column which relates to the topic_id in the Topics table. What I want to do is delete all messages in the message table for which the relating topic_id has been already deleted in the Topics table. For example, if the row with topic_id 5 was deleted in Topics table, I want to make sure there are no messages in the Messages table which have a topic_id of 5. Please please, any help would be greatly appreciated. I'm running MySQL 3.23.46 on UNIX. TIA! sunny - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Please help! - About doing a delete.
To select the rows you are looking for, you can use: select * from messages left join topics on topics.topic_id=messages.topic_id where topics.topic_id is null; This works fine for selecting. But for some reason the corresponding SQL for a delete fails: mysql delete from messages - left join topics on topics.topic_id=messages.topic_id - where topics.topic_id is null; ERROR 1064: You have an error in your SQL syntax near 'left join topics on topics.topic_id=messages.topic_id Anybody know how to make the delete statement work? On Thu, 2002-03-14 at 15:57, sunny wrote: How do i do a select first and then delete those rows? sorry.. im just confused. i tried that mysql, but it didn't work... gave me an error :( Any ideas? thanks! /sunny At 20:54 13/03/02 -0500, you wrote: What you need is a delete with a left join. Something like this: delete from messages left join messages on topics.topic_id=messages.topic_id where topics.topic_id is null; Try it first using a select instead of a delete to be sure it captures the right rows. But this ought to work. On Wed, 2002-03-13 at 17:53, sunny wrote: Hi everyone, I've got a really annoying problem with mysql, and I was hoping someone could help. I've got 2 tables in Mysql: Table 1 - Topics | topic_id | name | subject | comment | - | 1 | john | weather| the weather today blah blah| Table 2 - Messages |message_id | topic_id | name | subject | comment | - | 12| 1 | billy | yes it ...| i totally agree| Basically, in the messages table, each row has its own unique id, but also a topic_id column which relates to the topic_id in the Topics table. What I want to do is delete all messages in the message table for which the relating topic_id has been already deleted in the Topics table. For example, if the row with topic_id 5 was deleted in Topics table, I want to make sure there are no messages in the Messages table which have a topic_id of 5. Please please, any help would be greatly appreciated. I'm running MySQL 3.23.46 on UNIX. TIA! sunny - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10?
John Dean [EMAIL PROTECTED] writes: Hi All you need to do is include a symlink on libmysqlclient.so.11 to point to libmysqlclient.so.10 e.g ln libmysqlclient.so.11 libmysqlclient.so.10 Not a good suggestion for libraries in general. If a library so name has changed, it means it not compatible. Install the older library in addition -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10?
Hi All you need to do is include a symlink on libmysqlclient.so.11 to point to libmysqlclient.so.10 e.g ln libmysqlclient.so.11 libmysqlclient.so.10 At 15:18 01/03/2002 -0500, Ronald Arenas wrote: I have installed MySql 3.23.49, PHP 4.06, and Apache 1.3.20 successfully on my RH 7.2 box. Now I try to install MySql 4.01 (after removing MySql 3.23.49) I get a Php-mysql.4.xx requires libmysqlclient.so.10. My attempt to install MySql 4.01 ends there. I did some reading and found that libmysqlclient.so.10 comes with MySql 3.23.xx. I surfed my directories to find that MySql 4.01 installs libmysqlclient.so.11 I installed theses packages using the RH RPM Manager. Am I missing something? Ron Arenas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Regards John, Former MySQL Developer - [EMAIL PROTECTED] http://www.rygannon.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: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10?
At 13:26 01/03/2002 -0700, Van wrote: Ronald Arenas wrote: I have installed MySql 3.23.49, PHP 4.06, and Apache 1.3.20 successfully on my RH 7.2 box. Now I try to install MySql 4.01 (after removing MySql 3.23.49) I get a Php-mysql.4.xx requires libmysqlclient.so.10. My attempt to install MySql 4.01 ends there. I did some reading and found that libmysqlclient.so.10 comes with MySql 3.23.xx. I surfed my directories to find that MySql 4.01 installs libmysqlclient.so.11 I installed theses packages using the RH RPM Manager. Am I missing something? Ron Arenas - Ron: You should be aware that V4.0.x is as stable as V3.23.x. With the exception of the new features V4.0.x is V3.23.x Might make sense to stick with a stable MySQL 3.23.xx db for now. Unless, of course, you need the enhanced features in 4.xx. Also (OT), I'd recommend getting the most current version of PHP 4.x due to the recent CERT announcement on the file upload flaw. PHP can be found at php.net. Regards, Van -- = Linux rocks!!! http://www.dedserius.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 Regards John, Former MySQL Developer - [EMAIL PROTECTED] http://www.rygannon.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: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10?
Ronald Arenas wrote: I have installed MySql 3.23.49, PHP 4.06, and Apache 1.3.20 successfully on my RH 7.2 box. Now I try to install MySql 4.01 (after removing MySql 3.23.49) I get a Php-mysql.4.xx requires libmysqlclient.so.10. My attempt to install MySql 4.01 ends there. I did some reading and found that libmysqlclient.so.10 comes with MySql 3.23.xx. I surfed my directories to find that MySql 4.01 installs libmysqlclient.so.11 I installed theses packages using the RH RPM Manager. Am I missing something? Ron Arenas - Ron: Might make sense to stick with a stable MySQL 3.23.xx db for now. Unless, of course, you need the enhanced features in 4.xx. Also (OT), I'd recommend getting the most current version of PHP 4.x due to the recent CERT announcement on the file upload flaw. PHP can be found at php.net. Regards, Van -- = Linux rocks!!! http://www.dedserius.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: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10?
Hi, Try to upgrade PHP to the latest version (4.1.2) , it should run fine. Regards, Jocelyn Fournier - Original Message - From: Ronald Arenas [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Friday, March 01, 2002 9:18 PM Subject: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10? I have installed MySql 3.23.49, PHP 4.06, and Apache 1.3.20 successfully on my RH 7.2 box. Now I try to install MySql 4.01 (after removing MySql 3.23.49) I get a Php-mysql.4.xx requires libmysqlclient.so.10. My attempt to install MySql 4.01 ends there. I did some reading and found that libmysqlclient.so.10 comes with MySql 3.23.xx. I surfed my directories to find that MySql 4.01 installs libmysqlclient.so.11 I installed theses packages using the RH RPM Manager. Am I missing something? Ron Arenas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10?
PHP is looking for the client library that gets installed with MySQL 3.23.4+. Now, you can try a QUICK fix that might not work, but it's worth a shot. Go to the directory that has the libmysqlclient.so.11 file and type the following: ln -s libmysqlclient.so.11 libmysqlclient.so.10 That should create the file libmysqlclient.so.10 that links to ...so.11. If the client library is vastly different, though, it might not work. If it doesn't work, you should go uninstall PHP, go to the directory where everything was being compiled, edit the necessary files to have PHP use libmysqlclient.so.11 instead (this might be in the Makefile - but be sure you run a make clean first), and recompile. I don't know how different the PHP RPM will be, but there should be a place where you can change the reference to libmysqlclient. I use FreeBSD's ports system and it has a special configure script that creates a little addon to the Makefile that specifies the library version numbers like 10 or 11, and you can change that file before compiling. - Jonathan -Original Message- From: Ronald Arenas [mailto:[EMAIL PROTECTED]] Sent: Friday, March 01, 2002 12:19 PM To: Mysql Subject: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10? I have installed MySql 3.23.49, PHP 4.06, and Apache 1.3.20 successfully on my RH 7.2 box. Now I try to install MySql 4.01 (after removing MySql 3.23.49) I get a Php-mysql.4.xx requires libmysqlclient.so.10. My attempt to install MySql 4.01 ends there. I did some reading and found that libmysqlclient.so.10 comes with MySql 3.23.xx. I surfed my directories to find that MySql 4.01 installs libmysqlclient.so.11 I installed theses packages using the RH RPM Manager. Am I missing something? Ron Arenas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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