insert into x select * from x doesn't work
In mysql 4.1 (and since mysql 4.0.14 or something like that) I believe it's documented that an insert into X select * from X should work. It's not working for me on MySQL 4.1.0 alpha, anyone else have this problem? is it a bug? I haven't found any other reports of this not working for anyone else. Bellow is a pasted copy of what I get. --- [1006:[EMAIL PROTECTED]:~]more /proc/version Linux version 2.4.18-26.8.0 ([EMAIL PROTECTED]) (gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) #1 Mon Feb 24 10:21:42 EST 2003 [1007:[EMAIL PROTECTED]:~]mysql --version mysql Ver 13.5 Distrib 4.1.0-alpha, for pc-linux (i686) [1008:[EMAIL PROTECTED]:~]mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.0-alpha Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create database foo; Query OK, 1 row affected (0.00 sec) mysql create table foo.bar (id int); Query OK, 0 rows affected (0.08 sec) mysql insert into foo.bar select id from foo.bar; ERROR 1066: Not unique table/alias: 'bar' mysql -- Being normal is vastly over rated : Ted Leung -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump error (mysql 4.0.14)
I have a user who unfortunately named one of his tables order. He also has table names with a dash in them. mysqldump is unable to dump the order table and I'm unable to use the ALTER command on it and the tables with the dashes in their names to rename them. Is there any way to quote the table names so it doesn't treat them as sql commands? /local_a/servers/mysql/bin/mysqldump: Got error: 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order READ /*!32311 LOCAL */,order_pricing READ /*!32311 LOCAL when using LOCK TABLES -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC : determining the schema name for MySQL
Mark Matthews wrote: MySQL doesn't have schemas (currently). They are being developed in a future version. The DatabaseMetaData interface in JDBC has a method, 'getSchemaTerm' which tells you whether a certain database supports schemas or not (returning when schemas are not supported). MySQL supports _catalogs_, so you would use the DatabaseMetaData method 'getCatalogs' to see all catalogs (databases in 'mysql' vernacular) in your database, or the 'getCatalog' method in the Connection interface to retreive the catalog the connection is currently using. Thanks Mark, that helps a lot. If I call database.getCatalogs(); I get a list of all of my databases ... about 6 of them - so thats ok. If I then call databasemetadata.getTables(null,null,table-name,null); I get catalogName as , schemaName as null, tableName as table-name. So why is it returning the catalogName for this table as (i.e no catalog) if catalog maps across to 'database' in MySQL ? TIA -- Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Somebody tried MySQL version 5 / Stored Procedures
Hi all, We want to migrate to MySQL, and because of its speed, we want to use MyISAM db. I plan to write code as an alternate transaction mechanism. But I have read that MySQL version 5 supports Stored Procedures (PL/SQL like). In PL/SQL it is possible to use in your stored procedures transactions (at the end (eg) of a stored procedure you can do a commit and in an exception handler (eg) you can do a rollback). My question to somebody who has tried version 5 (or the development team), is this possible in MySQL stored procedures too? So it is not nessecery for me to write much code, but just a little and in version 5 I write my transactions to stored procedures... In anticipation much thanks!! Kind Regards Hans van Dalen/ NL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Somebody tried MySQL version 5 / Stored Procedures
On Mon, Sep 29, 2003 at 08:45:39AM +0200, Hans van Dalen wrote: Hi all, We want to migrate to MySQL, and because of its speed, we want to use MyISAM db. I plan to write code as an alternate transaction mechanism. But I have read that MySQL version 5 supports Stored Procedures (PL/SQL like). In PL/SQL it is possible to use in your stored procedures transactions (at the end (eg) of a stored procedure you can do a commit and in an exception handler (eg) you can do a rollback). My question to somebody who has tried version 5 (or the development team), is this possible in MySQL stored procedures too? So it is not nessecery for me to write much code, but just a little and in version 5 I write my transactions to stored procedures... In anticipation much thanks!! Didn't you ask this roughly 3 days ago? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 15 days, processed 538,364,636 queries (407/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Somebody tried MySQL version 5 / Stored Procedures
Excuse me, I get an automatic reply from the mailinglist that my message wasn't send so I send it again. regards hans At 23:50 28-9-03 -0700, you wrote: On Mon, Sep 29, 2003 at 08:45:39AM +0200, Hans van Dalen wrote: Hi all, We want to migrate to MySQL, and because of its speed, we want to use MyISAM db. I plan to write code as an alternate transaction mechanism. But I have read that MySQL version 5 supports Stored Procedures (PL/SQL like). In PL/SQL it is possible to use in your stored procedures transactions (at the end (eg) of a stored procedure you can do a commit and in an exception handler (eg) you can do a rollback). My question to somebody who has tried version 5 (or the development team), is this possible in MySQL stored procedures too? So it is not nessecery for me to write much code, but just a little and in version 5 I write my transactions to stored procedures... In anticipation much thanks!! Didn't you ask this roughly 3 days ago? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 15 days, processed 538,364,636 queries (407/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump error (mysql 4.0.14)
Try putting the name inside of backticks; From the Manual: 6.1.2 Database, Table, Index, Column, and Alias Names --- Note that if the identifier is a restricted word or contains special characters you must always quote it with a ` (backtick) when you use it: mysql SELECT * FROM `select` WHERE `select`.id 100; See Section 6.1.7 [Reserved words], page 437. KL Rusty Wright wrote: I have a user who unfortunately named one of his tables order. He also has table names with a dash in them. mysqldump is unable to dump the order table and I'm unable to use the ALTER command on it and the tables with the dashes in their names to rename them. Is there any way to quote the table names so it doesn't treat them as sql commands? /local_a/servers/mysql/bin/mysqldump: Got error: 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order READ /*!32311 LOCAL */,order_pricing READ /*!32311 LOCAL when using LOCK TABLES -- 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: ** Composit Key - Is it possible, and if so, what is the syntax?
I did the following (not much different): CREATE TABLE `foo` ( `pri_1` INT UNSIGNED NOT NULL, `pri_2` INT UNSIGNED NOT NULL, `pri_3` INT UNSIGNED NOT NULL, `test_data` VARCHAR(120) NOT NULL, PRIMARY KEY (`pri_1`, `pri_2`, `pri_3`) ) TYPE = InnoDB; Regards, Ritter -- Jason k Larson Fortuno, Adam wrote: All, I've got an InnoDb table that requires a composit key constraint. However, I'm not certain how to word the DDL syntax. Pretending for a sec this was SQL Server 2000 or Sybase ASE 12.5 I'd use the following. CREATE TABLE foo( pri_1 INTEGER NOT NULL, pri_2 INTEGER NOT NULL, pri_3 INTEGER NOT NULL, test_data VARCHAR(120) NULL CONSTRAINT pk_foo PRIMARY KEY (pri_1, pri_2, pri_3) ) Type = InnoDB; In MySQL, this would return an error. I'd really appreciate knowing if its possible and (if so) what the syntax is. Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Q: how to send the records of a table to a file and then print them out?
Greetings, I use WinXP and MySQL. I have a customer table, which contains about 200 records. Now I want to print all the records of the table out. How can I achieve that? Can I send them to a .doc OR .txt file before printing? cheers, feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Q: how to send the records of a table to a file and th en print them out?
http://www.mysql.com/doc/en/SELECT.html Look at INTO OUTFILE for what you need. Simon -Original Message- From: Wang Feng [mailto:[EMAIL PROTECTED] Sent: 29 September 2003 08:54 To: [EMAIL PROTECTED] Subject: Newbie Q: how to send the records of a table to a file and then print them out? Greetings, I use WinXP and MySQL. I have a customer table, which contains about 200 records. Now I want to print all the records of the table out. How can I achieve that? Can I send them to a .doc OR .txt file before printing? cheers, feng -- 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: insert into x select * from x doesn't work
Hi In mysql 4.1 (and since mysql 4.0.14 or something like that) I believe it's documented that an insert into X select * from X should work. IIRC, the table you're INSERTing into cannot be the same table you SELECT from - you're trying to take records from the table 'foo' and insert them into the same table. mysql insert into foo.bar select id from foo.bar; ERROR 1066: Not unique table/alias: 'bar' mysql This error seems to support that idea - although its not a particularly friendly way of saying it. Try creating another table, and do the select from that. Hope this helps! David P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump error (mysql 4.0.14)
Rusty Wright [EMAIL PROTECTED] wrote: I have a user who unfortunately named one of his tables order. He also has table names with a dash in them. mysqldump is unable to dump the order table and I'm unable to use the ALTER command on it and the tables with the dashes in their names to rename them. Is there any way to quote the table names so it doesn't treat them as sql commands? /local_a/servers/mysql/bin/mysqldump: Got error: 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order READ /*!32311 LOCAL */,order_pricing READ /*!32311 LOCAL when using LOCK TABLES Use -Q option of mysqldump: http://www.mysql.com/doc/en/mysqldump.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: insert into x select * from x doesn't work
Tedman Leung [EMAIL PROTECTED] wrote: In mysql 4.1 (and since mysql 4.0.14 or something like that) I believe it's documented that an insert into X select * from X should work. It's not working for me on MySQL 4.1.0 alpha, anyone else have this problem? is it a bug? I haven't found any other reports of this not working for anyone else. MySQL 4.1 was released before MySQL 4.0.14. -- 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]
install script errors
Hello, I am installing mysql 4.1.0 rpm on SuSE 8.2. I am logged in as root and run the rpm command as follows: rpm -Uvh MySQL-server-4.1.0-0.i386.rpm The script completes but gives two errors. The errors in mysql_install_db are on lines 166 and 383. Both lines are the same: if test $in_rpm -eq 0 || $windows -eq 0 I thought maybe the errors could be ignored, but I immediately try to execute the required commands to set the root password, the command is not found: /usr/bin/mysqladmin -u root password 'newpassword' So I guess the install didn't go so well. This same problem happened to me last month when I tried to install on another clean copy of SuSE 8.2 and also when I tried on Mandrake 9.1. Am I doing something ovbvious that is wrong?? thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Job requirement
Hello Everybody, With reference to the above subject we would like to inform you that we are one of the leading companies specializing in providing the IT consulting / IT staff augmentation business. We are based at London and have our offices with support centers in USA, India and UAE. Our company also has a full fledged offshore development centre in India and UK. We are in need of professionals in J2EE, MYSQL / Oracle, SAP andPeoplesoft eligible to work in UK / EU. It is beneficial to have a person from Financial / Banking Domain but not mandatory For more details please replyto this mailimmediately. Thanks and Regards, Jobber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Authentication
Access denied for user: '@localhost' to database 'database' I have a database named 'database' and i got this problem while trying to get it run. I am connecting that database to localhost without user and password, it works just fine on my previous windows box, but since lately when i move it onto my linux box, it starts showing me that error message. It works when i connecting the database with user root , my problem is that how can i get it to work without using user root , just like the old days on windows box. Or is there anyway to add user/password for that particular database so that only that single user can access to that db ? Thanks ___ This mail is protected by Silicon Communications S/B The information contained in this message maybe confidential and protected from disclosure. If you are not the intended recipient of this message, please delete this message immediately. You are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. ~~~This email has been scanned by our anti-virus system. For precaution, please make sure you scan every attachment in this email. Please use at your own risk. Thank you. :) Mailadmin~~~ ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'IF NOT EXISTS' ignored?
Hassan Schroeder [EMAIL PROTECTED] wrote: Is there a problem with 'IF NOT EXISTS' in the following scenario? This statement creates the table as expected ... CREATE TEMPORARY TABLE IF NOT EXISTS showcase ( PRIMARY KEY (product) ) SELECT product FROM productList .. but re-running it (from the console or via page reload) results in ERROR 1062: Duplicate entry 'foo' for key 1 I expected it to simply exit silently when it finds that the table *does* already exist. So what am I missing? :-) Duplicate entry means that you already have value 'foo' in the column 'product' which is defined as primary key. -- 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: insert into x select * from x doesn't work
I think this should be reported as a BUG for version 4.1 Below is a small text retreived from the URL http://www.mysql.com/doc/en/INSERT_SELECT.html --- Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. This limitation is lifted in 4.0.14. --- This should apply to 4.1.0 because it is not prior to 4.0.14 ! Thanks Emery - Original Message - From: Tedman Leung [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 29, 2003 08:08 Subject: insert into x select * from x doesn't work In mysql 4.1 (and since mysql 4.0.14 or something like that) I believe it's documented that an insert into X select * from X should work. It's not working for me on MySQL 4.1.0 alpha, anyone else have this problem? is it a bug? I haven't found any other reports of this not working for anyone else. Bellow is a pasted copy of what I get. --- [1006:[EMAIL PROTECTED]:~]more /proc/version Linux version 2.4.18-26.8.0 ([EMAIL PROTECTED]) (gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) #1 Mon Feb 24 10:21:42 EST 2003 [1007:[EMAIL PROTECTED]:~]mysql --version mysql Ver 13.5 Distrib 4.1.0-alpha, for pc-linux (i686) [1008:[EMAIL PROTECTED]:~]mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.0-alpha Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create database foo; Query OK, 1 row affected (0.00 sec) mysql create table foo.bar (id int); Query OK, 0 rows affected (0.08 sec) mysql insert into foo.bar select id from foo.bar; ERROR 1066: Not unique table/alias: 'bar' mysql -- Being normal is vastly over rated : Ted Leung -- 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]
UNDOing query operations
Hi group? Suppose I run a query by mistake that for example deletes my records. Is there a way to go back to a previous state? Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/
Re: mysqldump error (mysql 4.0.14)
`TableNamesOr FieldNames` - Original Message - From: Kelley Lingerfelt [EMAIL PROTECTED] To: Rusty Wright [EMAIL PROTECTED]; Mysql List [EMAIL PROTECTED] Sent: Monday, September 29, 2003 09:10 Subject: Re: mysqldump error (mysql 4.0.14) Try putting the name inside of backticks; From the Manual: 6.1.2 Database, Table, Index, Column, and Alias Names --- Note that if the identifier is a restricted word or contains special characters you must always quote it with a ` (backtick) when you use it: mysql SELECT * FROM `select` WHERE `select`.id 100; See Section 6.1.7 [Reserved words], page 437. KL Rusty Wright wrote: I have a user who unfortunately named one of his tables order. He also has table names with a dash in them. mysqldump is unable to dump the order table and I'm unable to use the ALTER command on it and the tables with the dashes in their names to rename them. Is there any way to quote the table names so it doesn't treat them as sql commands? /local_a/servers/mysql/bin/mysqldump: Got error: 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order READ /*!32311 LOCAL */,order_pricing READ /*!32311 LOCAL when using LOCK TABLES -- 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: insert into x select * from x doesn't work
Read the docs at: http://www.mysql.com/doc/en/INSERT_SELECT.html They said that with 4.0.14 and up, that should work!!! Thanks Emery - Original Message - From: David Precious [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:09 Subject: Re: insert into x select * from x doesn't work Hi In mysql 4.1 (and since mysql 4.0.14 or something like that) I believe it's documented that an insert into X select * from X should work. IIRC, the table you're INSERTing into cannot be the same table you SELECT from - you're trying to take records from the table 'foo' and insert them into the same table. mysql insert into foo.bar select id from foo.bar; ERROR 1066: Not unique table/alias: 'bar' mysql This error seems to support that idea - although its not a particularly friendly way of saying it. Try creating another table, and do the select from that. Hope this helps! David P -- 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: MySQL certification [slightly-ot]
Hi Jennifer, On Fri, 2003-09-26 at 20:55, Jennifer Goodie wrote: I have recently re-entered the job market and I was wondering if anyone has found that having certification really helps in landing a position. If so, which cert do you have? I can only answer in general terms, but hope you might still find this insight helpful... I have talked to quite a number of hiring managers, and there seems to be some consensus regarding the following points, when it comes to certifications in hiring: If a manager looks at certifications, it's often the first thing he does to split the candidates, with a view to look at the certified people first. While holding certificate XYZ will often make a difference as to who makes it into the stack of interesting candidates, it rarely makes a difference when deciding on the final runner-ups for the position. At this time, your CV and personality are what makes the difference. The value of a given certification also depends on where you are looking for a job. In large corporations, where a centralized HR department is involved, the people making the initial processing of applicants often don't know (or maybe they don't understand) the details of what's being asked of the candidates. To them, even an introductory certification (like MySQL ABs Core certification) can make a big difference. Smaller companies tend to look only at higher-level or specialized certifications when hiring. I hope you found this input helpful -- you (and anyone else reading this reply) are very welcome to contact me directly if you want further details on our certification program. Best regards, Carsten Pedersen Certification Manager, MySQL AB -- Warning: Certification can seriously increase your wealth! http://www.mysql.com/certification Carsten Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com Office: +45 56 36 16 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reinstall package
gamalt tant [EMAIL PROTECTED] wrote: hi i can locate my.cnf on my redhat8. should i uninstall the mysql package and reinstalled to have my.cnf in etc directory? i remember i get flag erros when i was installing it? No. If you want to have my.cnf, you should create it by yourself. You can find examples of my.cnf (my-large.cnf, my-huge.cnf etc.) in the support-files directory: http://www.mysql.com/doc/en/Option_files.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]
Re: Authentication
While connected to MySQL, issue the following instruction: GRANT ALL PRIVILEGES to 'YourChoosesUsername'@'%' ON `database` I don't remember exactly the syntax, but I think this should work. It means, you giving all the permissions to the user YourChoosenUsername and you are allowing him/her to connect from any computer (even the Internet if applicable) on just the database `database` Thanks Emery - Original Message - From: ketvin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:42 Subject: Authentication Access denied for user: '@localhost' to database 'database' I have a database named 'database' and i got this problem while trying to get it run. I am connecting that database to localhost without user and password, it works just fine on my previous windows box, but since lately when i move it onto my linux box, it starts showing me that error message. It works when i connecting the database with user root , my problem is that how can i get it to work without using user root , just like the old days on windows box. Or is there anyway to add user/password for that particular database so that only that single user can access to that db ? Thanks -- 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: 'IF NOT EXISTS' ignored?
It seems Victoria didn't understand the real problem: I think that query should be stopped as soon as the table exists. But if it doesn't exist, the query should create it and insert some records. The problem is: WHY is MySQL trying to insert records while the table exists? It should only insert records after creating the table (and table will be created only when t doesn't exist already). So, in that query, will the IF condition apply for table creation only? Thanks Emery - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:37 Subject: Re: 'IF NOT EXISTS' ignored? Hassan Schroeder [EMAIL PROTECTED] wrote: Is there a problem with 'IF NOT EXISTS' in the following scenario? This statement creates the table as expected ... CREATE TEMPORARY TABLE IF NOT EXISTS showcase ( PRIMARY KEY (product) ) SELECT product FROM productList .. but re-running it (from the console or via page reload) results in ERROR 1062: Duplicate entry 'foo' for key 1 I expected it to simply exit silently when it finds that the table *does* already exist. So what am I missing? :-) Duplicate entry means that you already have value 'foo' in the column 'product' which is defined as primary key. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Authentication
ketvin [EMAIL PROTECTED] wrote: Access denied for user: '@localhost' to database 'database' I have a database named 'database' and i got this problem while trying to get it run. I am connecting that database to localhost without user and password, it works just fine on my previous windows box, but since lately when i move it onto my linux box, it starts showing me that error message. It works when i connecting the database with user root , my problem is that how can i get it to work without using user root , just like the old days on windows box. Or is there anyway to add user/password for that particular database so that only that single user can access to that db ? Initial MySQL privileges on Windows are not the same as on your Linux box. By default on Windows user ''@'localhost' has all privileges. On Linux only user 'root' has all privileges, anonymous user has privileges only on the database 'test'. So, you should give permissions to the user on the database 'database': http://www.mysql.com/doc/en/Default_privileges.html http://www.mysql.com/doc/en/GRANT.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: UNDOing query operations
Hi, Read the manual about transactions. Hans At 11:56 29-9-03 +0200, you wrote: Hi group? Suppose I run a query by mistake that for example deletes my records. Is there a way to go back to a previous state? Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql c api
hi,
i found a strange user comment in the current documentation of c api
for mysql_real_connect.
http://www.mysql.com/doc/en/mysql_real_query.html
If a result set (MYSQL_RES) is in use, then mysql_free_result(...)
must be called before a call to this function will be successful.
if this is true, my question is:
need i open a new connection for _query2_ ?
for example:
conn=mysql_real_connect(...);
//query 1
mysql_real_query(conn,select CustomerId from Invoices,xx);
result=mysql_use_result(conn);
while (...) {
//query 2
mysql_real_query(conn,\
select Name from Customers where CustomerId=id_from_query1),xx);
}
ps: i know, this is easyer with a simple join, but this is just a
dummy example...
thanks,
ati
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'IF NOT EXISTS' ignored?
On Mon, 29 Sep 2003 12:30:28 +0200 Director General: NEFACOMP [EMAIL PROTECTED] wrote: It seems Victoria didn't understand the real problem: I think that query should be stopped as soon as the table exists. But if it doesn't exist, the query should create it and insert some records. The problem is: WHY is MySQL trying to insert records while the table exists? It should only insert records after creating the table (and table will be created only when t doesn't exist already). I don't think you're right. The main aim of this query is to INSERT data into table. And before this it checks if the table already exists and creates it if not. So, there is no problem imho. So, in that query, will the IF condition apply for table creation only? Yes. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Injection
www.securityfocus.com ran a three part article, http://www.securityfocus.com/infocus/1722, that discussed SQL Injections and MySQL. -Original Message- From: Tony Thomas [mailto:[EMAIL PROTECTED] Sent: Sunday, September 28, 2003 11:36 PM To: [EMAIL PROTECTED] Subject: SQL Injection Hi All, I've been hearing a bit about SQL injection lately, but the only documentation I can find refers to Microsoft or Oracle. Anyone know of good articles about injection in MySQL? Prevention? Detection? Is MySQL less vulnerable? Thanks, Tony -- 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: 'IF NOT EXISTS' ignored?
When I replied I was not meaning the aim of this query but the aim of Mr. Hassan who first asked that question on September 28, 2003. I just rephrased his question!!! Below is a text he wrote. Please note where he said: . I expected it to simply exit silently when it finds that the table *does* already exist. === Is there a problem with 'IF NOT EXISTS' in the following scenario? This statement creates the table as expected ... CREATE TEMPORARY TABLE IF NOT EXISTS showcase ( PRIMARY KEY (product) ) SELECT product FROM productList .. but re-running it (from the console or via page reload) results in ERROR 1062: Duplicate entry 'foo' for key 1 I expected it to simply exit silently when it finds that the table *does* already exist. So what am I missing? :-) === Now, do you think I was right in my rephrasing? To me, I think that is a bug and should be reported to MySQL. When the IF condition is false, it should break the rest of the statement. What is your view on this? Thanks Emery - Original Message - From: Antony Dovgal [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 29, 2003 13:28 Subject: Re: 'IF NOT EXISTS' ignored? On Mon, 29 Sep 2003 12:30:28 +0200 Director General: NEFACOMP [EMAIL PROTECTED] wrote: It seems Victoria didn't understand the real problem: I think that query should be stopped as soon as the table exists. But if it doesn't exist, the query should create it and insert some records. The problem is: WHY is MySQL trying to insert records while the table exists? It should only insert records after creating the table (and table will be created only when t doesn't exist already). I don't think you're right. The main aim of this query is to INSERT data into table. And before this it checks if the table already exists and creates it if not. So, there is no problem imho. So, in that query, will the IF condition apply for table creation only? Yes. --- WBR, Antony Dovgal aka tony2001 [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: 'IF NOT EXISTS' ignored?
On Mon, 29 Sep 2003 15:16:57 +0200 Director General: NEFACOMP [EMAIL PROTECTED] wrote: Now, do you think I was right in my rephrasing? To me, I think that is a bug and should be reported to MySQL. When the IF condition is false, it should break the rest of the statement. What is your view on this? For me this is not a bug, just poorly documented feature. So, Hassan can't do it using this statement, he needs to check if table exists and then INSERT, cause the statement he's trying to use is not intended for conditional INSERT, but for conditional CREATE. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
Hi It seems to me that combination from the subject just doesn't work. Every time I try to incorporate spatial column into Innodb table Mysql dies. The simplest case, easy to repeat is bellow. When I'm trying to create table with just one spatial column I'm getting following error: mysql create table t (p polygon not null, spatial index(p)) type=innodb; ERROR 2013: Lost connection to MySQL server during query In error log mysql writes: assertion 0 failed: file ha_innodb.cc, line 1547 030929 16:52:24 mysqld restarted I have two identical systems and both demonstrate behaviour described above. About systems config. Mysql: Version: '4.1.0-alpha' OS: FreeBSD 4.8-RELEASE So could someone test this and confirm or disconfirm above-mentioned abnormal behaviour. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW slave STATUS returning no rows.
From a Java connection object, Show Slave Status returns no rows Running mysql on linux. Also testing the SQL through mysql control center 0.8.9 Thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'IF NOT EXISTS' ignored?
It is good that we both understand his problem. Do you have a solution for his problem? For me, I think he may use two statements: 1. Create the table if it does not exist. 2. Insert data if that will not create duplicates. /* This is the first instruction for creating the table */ CREATE TEMPORARY TABLE IF NOT EXISTS showcase ( PRIMARY KEY (product) ); /* This is the second instruction for dumping records */ INSERT IGNORE INTO showcase SELECT product FROM productList; Another alternative is to first DROP that table and recreate it. But I think the above 2 steps procedure will work. Unfortunately, I don't see a way to send these queries at once to the server for execution. Is this helpful? Thanks Emery - Original Message - From: Antony Dovgal [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 29, 2003 15:23 Subject: Re: 'IF NOT EXISTS' ignored? On Mon, 29 Sep 2003 15:16:57 +0200 Director General: NEFACOMP [EMAIL PROTECTED] wrote: Now, do you think I was right in my rephrasing? To me, I think that is a bug and should be reported to MySQL. When the IF condition is false, it should break the rest of the statement. What is your view on this? For me this is not a bug, just poorly documented feature. So, Hassan can't do it using this statement, he needs to check if table exists and then INSERT, cause the statement he's trying to use is not intended for conditional INSERT, but for conditional CREATE. --- WBR, Antony Dovgal aka tony2001 [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]
How to change to the unicode character set
Hello, I would like to use the unicode character set instead of the latin1. Can anyone tell me how to do that please? Thanks in advance, Julien.
Re: 'IF NOT EXISTS' ignored?
On Mon, 29 Sep 2003 16:02:23 +0200 Director General: NEFACOMP [EMAIL PROTECTED] wrote: For me, I think he may use two statements: 1. Create the table if it does not exist. 2. Insert data if that will not create duplicates. Yep, IMHO this is the only solution. Another alternative is to first DROP that table and recreate it. But I think the above 2 steps procedure will work. I suppose in this case Hassan should use DROP IF EXISTS and SELECT .. INSERT. Unfortunately, I don't see a way to send these queries at once to the server for execution. I don't think, that this queries should be sent at once (and there is no way to do it). They can be successfully executed one by one. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Composit Key - Is it possible, and if so, what is the syntax?
Try taking out the identifier name for the primary key (pk_foo). The way I read the documentation, MySQL does not support identifiers or names for primary keys although it does for other index types. HTH Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't Access DB from MySQLCC
This irritating rather than life-threatening: I can connect to the MySQL database on my Linux box, from my Win2K box, using the command line environment just fine: c:mysql --protocol=TCP -h host_name -u my_name -p mysql No matter what I try, however, I can't connect using MySQLCC. I always get this error: [Lucifer] ERROR 1249: Client does not support authentication protocol requested by server. Consider upgrading MySQL client. This is the same error my test user gets trying to connect to this database using MyODBC 3.51. Of course, I'm not using ODBC from MySQLCC. MySQL 4.1.0 alpha is running on the Linux box. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC : determining the schema name for MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Andy Jefferson wrote: Mark Matthews wrote: MySQL doesn't have schemas (currently). They are being developed in a future version. The DatabaseMetaData interface in JDBC has a method, 'getSchemaTerm' which tells you whether a certain database supports schemas or not (returning when schemas are not supported). MySQL supports _catalogs_, so you would use the DatabaseMetaData method 'getCatalogs' to see all catalogs (databases in 'mysql' vernacular) in your database, or the 'getCatalog' method in the Connection interface to retreive the catalog the connection is currently using. Thanks Mark, that helps a lot. If I call database.getCatalogs(); I get a list of all of my databases ... about 6 of them - so thats ok. If I then call databasemetadata.getTables(null,null,table-name,null); I get catalogName as , schemaName as null, tableName as table-name. So why is it returning the catalogName for this table as (i.e no catalog) if catalog maps across to 'database' in MySQL ? Looks like a bug. Currently, I have catalogName returnning what you passed in for catalog in the getTables() method. I'll fix that. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/eEdAtvXNTca6JD8RAq0TAJ4/ULPFuzx8Er5na8o/m0AmDmvpSwCgqNYg Q3wD6N1eWk4CExxjczl7Fo8= =1yOQ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to change to the unicode character set
Julien Martin [EMAIL PROTECTED] wrote: Hello, I would like to use the unicode character set instead of the latin1. Can anyone tell me how to do that please? Unicode is supported from MySQL 4.1. You can find more info about new charsets in the following chapter of the manual: http://www.mysql.com/doc/en/Charset.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]
Representing time sheet data in Mysql
Hello, I am trying to store our timesheet data in the Mysql database. I would appreciate suggestions on the database scheme to use. One of the possible ways to do it is to have a table indexed with the employee ID and then having as many columns as there are days in the year. This approach would mean that the table has 200 rows if we have 200 employees and it has 365 columns for each day of the year. Another approach would be to index based on the number of days, i.e., have 365 rows and have as many columns as employees, i.e., 200 columns. If an employee takes half a day off, then I can store an array in the value for that employee ID and that particular day. I am sure there are other approaches to, and I would appreciate any suggestions. Thank you...
Re: Can't Access DB from MySQLCC
Randy Chrismon [EMAIL PROTECTED] wrote: This irritating rather than life-threatening: I can connect to the MySQL database on my Linux box, from my Win2K box, using the command line environment just fine: c:mysql --protocol=TCP -h host_name -u my_name -p mysql No matter what I try, however, I can't connect using MySQLCC. I always get this error: [Lucifer] ERROR 1249: Client does not support authentication protocol requested by server. Consider upgrading MySQL client. This is the same error my test user gets trying to connect to this database using MyODBC 3.51. Of course, I'm not using ODBC from MySQLCC. MySQL 4.1.0 alpha is running on the Linux box. MySQL 4.1 provides new password hashing mechanism. This error appears if you connect with pre-4.1 client to the server 4.1. Look at: http://www.mysql.com/doc/en/Password_hashing.html In the above section of the manual you can find description of possible scenarios for 4.1 server. -- 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: Representing time sheet data in Mysql
In the last timetracking system I built, I used a 'timeentry' table which was basically timeentry_id (pk) employee_id date num_hours_worked task_id I put a few other indexes in there based on the searching that I had to do, but to each their own... -Original Message- From: Syed Ali [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:04 AM To: [EMAIL PROTECTED] Subject: Representing time sheet data in Mysql Hello, I am trying to store our timesheet data in the Mysql database. I would appreciate suggestions on the database scheme to use. One of the possible ways to do it is to have a table indexed with the employee ID and then having as many columns as there are days in the year. This approach would mean that the table has 200 rows if we have 200 employees and it has 365 columns for each day of the year. Another approach would be to index based on the number of days, i.e., have 365 rows and have as many columns as employees, i.e., 200 columns. If an employee takes half a day off, then I can store an array in the value for that employee ID and that particular day. I am sure there are other approaches to, and I would appreciate any suggestions. Thank you...
error configuring mysql
I am trying to run the initial configure program on a Cobalt RaQ2 appliance, following the instruction from http://www.ospex.com/raq2-php-mysql-apache.html It seems, that some initial parameters are missing, like --host. What are the right ones I have to use? Here are the errors it displays: [root mysql-3.23.56]# ./configure \ --with-low-memory \ --disable-assembler \ --disable-shared \ --with-mysqld-ldflags=-all-static \ --with-client-ldflags=-all-static \ --prefix=/home/mysql \ --localstatedir=/home/mysql/data \ --disable-maintainer-mode \ --with-mysqld-user=mysql \ --without-comment \ --without-debug \ --without-bench \ --without-test \ --without-raid configure: WARNING: you should use --build, --host, --target configure: WARNING: invalid host type: checking build system type... config.sub: missing argument Try `config.sub --help' for more information. configure: error: /bin/sh ./config.sub failed
Enabling Transactions
I am having a problem in one of my scripts and I need to use transactions to fix it. When I looked it up on the mySQL documentation I found: If you are using transaction-safe tables (like InnoDB or BDB), you can put MySQL into non-autocommit mode with the following command: Does this mean that transactions will not work if I type in a CREATE TABLE command? Are there any problems with switching to InnoDB or BDB? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
G B U [EMAIL PROTECTED] wrote: It seems to me that combination from the subject just doesn't work. Every time I try to incorporate spatial column into Innodb table Mysql dies. The simplest case, easy to repeat is bellow. When I'm trying to create table with just one spatial column I'm getting following error: mysql create table t (p polygon not null, spatial index(p)) type=innodb; ERROR 2013: Lost connection to MySQL server during query In error log mysql writes: assertion 0 failed: file ha_innodb.cc, line 1547 030929 16:52:24 mysqld restarted I have two identical systems and both demonstrate behaviour described above. About systems config. Mysql: Version: '4.1.0-alpha' OS: FreeBSD 4.8-RELEASE So could someone test this and confirm or disconfirm above-mentioned abnormal behaviour. Currently you can use spatial columns only in the MyISAM tables. -- 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: 'IF NOT EXISTS' ignored?
Director General: NEFACOMP wrote: To me, I think that is a bug and should be reported to MySQL. When the IF condition is false, it should break the rest of the statement. Exactly what I thought :-) and Antony Dovgal responded: For me this is not a bug, just poorly documented feature. So, Hassan can't do it using this statement, he needs to check if table exists and then INSERT, cause the statement he's trying to use is not intended for conditional INSERT, but for conditional CREATE. Bummer. It still seems like a bug to allow *one part* of a compound statement to succeed when another part fails, but ... BTW, thanks for the suggestion -- 1. Create the table if it does not exist. 2. Insert data if that will not create duplicates. - but the problem is: The table must be populated one time *only* per session; subsequent page loads delete records of products already displayed. So back to the drawing board, I guess. :-) Thanks again, -- 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: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
At 19:16 +0300 9/29/03, Victoria Reznichenko wrote: G B U [EMAIL PROTECTED] wrote: It seems to me that combination from the subject just doesn't work. Every time I try to incorporate spatial column into Innodb table Mysql dies. The simplest case, easy to repeat is bellow. When I'm trying to create table with just one spatial column I'm getting following error: mysql create table t (p polygon not null, spatial index(p)) type=innodb; ERROR 2013: Lost connection to MySQL server during query In error log mysql writes: assertion 0 failed: file ha_innodb.cc, line 1547 030929 16:52:24 mysqld restarted I have two identical systems and both demonstrate behaviour described above. About systems config. Mysql: Version: '4.1.0-alpha' OS: FreeBSD 4.8-RELEASE So could someone test this and confirm or disconfirm above-mentioned abnormal behaviour. Currently you can use spatial columns only in the MyISAM tables. Also, the crash described above is fixed in MySQL 4.1.1. GIS features still are available only in MyISAM (as Victoria notes), but attempting to use them with InnoDB won't cause loss of connection or server restart. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://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: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
At 19:16 +0300 9/29/03, Victoria Reznichenko wrote:
G B U [EMAIL PROTECTED] wrote:
It seems to me that combination from the subject just doesn't work.
Every time I try to incorporate spatial column into Innodb table
Mysql dies. The simplest case, easy to repeat is bellow.
When I'm trying to create table with just one spatial column I'm
getting following error:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
ERROR 2013: Lost connection to MySQL server during query
In error log mysql writes:
assertion 0 failed: file ha_innodb.cc, line 1547
030929 16:52:24 mysqld restarted
I have two identical systems and both demonstrate behaviour
described above.
About systems config.
Mysql: Version: '4.1.0-alpha'
OS: FreeBSD 4.8-RELEASE
So could someone test this and confirm or disconfirm above-mentioned
abnormal behaviour.
Currently you can use spatial columns only in the MyISAM tables.
Also, the crash described above is fixed in MySQL 4.1.1. GIS features
still are available only in MyISAM (as Victoria notes), but attempting
to use them with InnoDB won't cause loss of connection or server restart.
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that all is working
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2 2, 1 2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row 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: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
On Mon, Sep 29, 2003 at 09:33:29PM +0400, G B U wrote:
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that all is working
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2 2, 1 2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row in set (0.00 sec)
Is it *really* an InnoDB table?
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
MySQL 4.0.15-Yahoo-SMP: up 15 days, processed 553,853,916 queries (407/sec. avg)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
corrupted passwd
Hello All, I think the passwd on my installation of mysql of mysql-standard-4.0.14-pc-linux-i686 is corrupted. I can't login with the root account. I was able to do so till last week. I trying to follow the instructions on resetting the passwd, but I get the following err msg. # ./mysqld --skip-grant-tables Fatal error: Please read Security section of the manual to find out how to run mysqld as root! Any ideas? Saqib Ali - http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SHOW slave STATUS returning no rows. SOLVED
Solved issue. Sorry. Connection was connecting to Master not slave... Hence... Master possessed no Slave Status information. _A Deduction, dear Watson... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using mySQL connectJ issue
Hi all, when I used JDBC driver (Connector/J3.0.8) to run my java database agent codes, It always have following errors: java.lang.classNotFoundException : com.mysql.jdbc.driver at java.net.URLClassloader$1.run(URLClassLoader.java:198) I set the Classpath to mysql-connector-java-3.0.8-stable-bin.jar and my url=jdbc:mysql://localhost:3306/test,, (test is my database name) using JDBC DriverManager and com.mysql.jdbc.driver to load the driver: class.forName(com.mysql.jdbc.driver).newInstance(); I have no clue what's going on, I appriciate if anyone can give soem advices!Thx. Joe _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
On Mon, Sep 29, 2003 at 09:33:29PM +0400, G B U wrote:
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that all is working
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2 2, 1 2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row in set (0.00 sec)
Is it *really* an InnoDB table?
Yes it is really InnoDB table and I'm really confused now.
mysql show table status like 't';
+--+++--++-+
-+--+---++-+-+--
--+---++--+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_le
ngth | Index_length | Data_free | Auto_increment | Create_time | Update_time | C
heck_time | Charset | Create_options | Comment |
+--+++--++-+
-+--+---++-+-+--
--+---++--+
| t| InnoDB | Dynamic|1 | 16384 | 16384 |
NULL |16384 | 0 | NULL | NULL| NULL| N
ULL | latin1_swedish_ci || InnoDB free: 4096 kB |
+--+++--++-+
-+--+---++-+-+--
--+---++--+
1 row in set (0.01 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
At 21:33 +0400 9/29/03, G B U wrote:
At 19:16 +0300 9/29/03, Victoria Reznichenko wrote:
G B U [EMAIL PROTECTED] wrote:
It seems to me that combination from the subject just doesn't work.
Every time I try to incorporate spatial column into Innodb table
Mysql dies. The simplest case, easy to repeat is bellow.
When I'm trying to create table with just one spatial column I'm
getting following error:
mysql create table t (p polygon not null, spatial index(p))
type=innodb;
ERROR 2013: Lost connection to MySQL server during query
In error log mysql writes:
assertion 0 failed: file ha_innodb.cc, line 1547
030929 16:52:24 mysqld restarted
I have two identical systems and both demonstrate behaviour
described above.
About systems config.
Mysql: Version: '4.1.0-alpha'
OS: FreeBSD 4.8-RELEASE
So could someone test this and confirm or disconfirm above-mentioned
abnormal behaviour.
Currently you can use spatial columns only in the MyISAM tables.
Also, the crash described above is fixed in MySQL 4.1.1. GIS features
still are available only in MyISAM (as Victoria notes), but attempting
to use them with InnoDB won't cause loss of connection or server restart.
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that all is working
See:
http://www.mysql.com/doc/en/Creating_spatial_columns.html
I'll add a note to the beginning of the chapter as well to make this
more obvious.
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2 2, 1
2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row in set (0.00 sec)
My guess is that the table type may not really be InnoDB. What does
SHOW CREATE TABLE t or SHOW TABLE STATUS LIKE 't' say?
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://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: Representing time sheet data in Mysql
So if you needed to know status of a work day for an employee day (say 9/1/2003), how did you go about looking it up in your table? Assuming that a employee can be present (p), on vacation (v), sick day (s)? Your table seems to store only the number of hours worked Thank you, Syed Ali (609) 951-2989 -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:12 AM To: Syed Ali; [EMAIL PROTECTED] Subject: RE: Representing time sheet data in Mysql In the last timetracking system I built, I used a 'timeentry' table which was basically timeentry_id (pk) employee_id date num_hours_worked task_id I put a few other indexes in there based on the searching that I had to do, but to each their own... -Original Message- From: Syed Ali [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:04 AM To: [EMAIL PROTECTED] Subject: Representing time sheet data in Mysql Hello, I am trying to store our timesheet data in the Mysql database. I would appreciate suggestions on the database scheme to use. One of the possible ways to do it is to have a table indexed with the employee ID and then having as many columns as there are days in the year. This approach would mean that the table has 200 rows if we have 200 employees and it has 365 columns for each day of the year. Another approach would be to index based on the number of days, i.e., have 365 rows and have as many columns as employees, i.e., 200 columns. If an employee takes half a day off, then I can store an array in the value for that employee ID and that particular day. I am sure there are other approaches to, and I would appreciate any suggestions. Thank you...
small bug in mysqldump
I have a database with the name design-network when I use mysqldump the database name is write: design-network instead design-network in table name, field name, etc all work fine. The dump finish correctly but when I try use generated SQL I get an error. The problem is -. Other dump tool (ex. phpMyAdmin work fine) If someone confirm I post it in the bug repository. Best Regards Lorenzo Sicilia -- Kemen srl http://www.kemen.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: small bug in mysqldump
On 29 Sep 2003 at 20:47, Lorenzo Sicilia wrote: when I use mysqldump the database name is write: design-network instead design-network If you have odd characters in your names, then you need to use the --quote-names option on mysqldump, so that it puts backticks around them. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
At 22:05 +0400 9/29/03, G B U wrote:
On Mon, Sep 29, 2003 at 09:33:29PM +0400, G B U wrote:
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that
all is working
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2
2, 1 2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row in set (0.00 sec)
Is it *really* an InnoDB table?
Yes it is really InnoDB table and I'm really confused now.
It's a bug. The Windows version of 4.1.0 will allow this, but it shouldn't.
You should change your table to MyISAM, because it probably will
be disallowed in 4.1.1.
mysql show table status like 't';
+--+++--++-+
-+--+---++-+-+--
--+---++--+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_le
ngth | Index_length | Data_free | Auto_increment | Create_time |
Update_time | C
heck_time | Charset | Create_options | Comment |
+--+++--++-+
-+--+---++-+-+--
--+---++--+
| t| InnoDB | Dynamic|1 | 16384 | 16384 |
NULL |16384 | 0 | NULL | NULL|
NULL| N
ULL | latin1_swedish_ci || InnoDB free: 4096 kB |
+--+++--++-+
-+--+---++-+-+--
--+---++--+
1 row in set (0.01 sec)
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://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: Representing time sheet data in Mysql
In the system, there was a task_id associated with pretty much anything an employee could work on per project... In addition there was a project for 'overhead', which had tasks like vacation, sick, maternity, etc... so when the person entered their time, they put 8 hours toward their vacation time task you could tweak the task_id column to become time_type enumeration allowing p,v,s as values if you like -Original Message- From: Syed Ali [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 2:46 PM To: [EMAIL PROTECTED] Subject: RE: Representing time sheet data in Mysql So if you needed to know status of a work day for an employee day (say 9/1/2003), how did you go about looking it up in your table? Assuming that a employee can be present (p), on vacation (v), sick day (s)? Your table seems to store only the number of hours worked Thank you, Syed Ali (609) 951-2989 -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:12 AM To: Syed Ali; [EMAIL PROTECTED] Subject: RE: Representing time sheet data in Mysql In the last timetracking system I built, I used a 'timeentry' table which was basically timeentry_id (pk) employee_id date num_hours_worked task_id I put a few other indexes in there based on the searching that I had to do, but to each their own... -Original Message- From: Syed Ali [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:04 AM To: [EMAIL PROTECTED] Subject: Representing time sheet data in Mysql Hello, I am trying to store our timesheet data in the Mysql database. I would appreciate suggestions on the database scheme to use. One of the possible ways to do it is to have a table indexed with the employee ID and then having as many columns as there are days in the year. This approach would mean that the table has 200 rows if we have 200 employees and it has 365 columns for each day of the year. Another approach would be to index based on the number of days, i.e., have 365 rows and have as many columns as employees, i.e., 200 columns. If an employee takes half a day off, then I can store an array in the value for that employee ID and that particular day. I am sure there are other approaches to, and I would appreciate any suggestions. Thank you...
Re: Using mySQL connectJ issue
hi it seems that the mysql-connector-java-3.0.8-stable-bin.jar file is not in the classpath. best regards benny - Original Message - From: Joe Yan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 29, 2003 7:58 PM Subject: Using mySQL connectJ issue Hi all, when I used JDBC driver (Connector/J3.0.8) to run my java database agent codes, It always have following errors: java.lang.classNotFoundException : com.mysql.jdbc.driver at java.net.URLClassloader$1.run(URLClassLoader.java:198) I set the Classpath to mysql-connector-java-3.0.8-stable-bin.jar and my url=jdbc:mysql://localhost:3306/test,, (test is my database name) using JDBC DriverManager and com.mysql.jdbc.driver to load the driver: class.forName(com.mysql.jdbc.driver).newInstance(); I have no clue what's going on, I appriciate if anyone can give soem advices!Thx. Joe _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- 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]
slow 'sending data' phase
I've fixed my swapping issues, but the system continues to get stuck in a 'sending data' phase from time to time. With mod_perl + mysql, this phase SHOULD be when mysql collects the rows (after sorting, etc) and sends them to the perl handler for processing. Any ideas why this phase would ever be taking 100-500 seconds? The system appears to be stable for long times, then gets in a huge bottleneck locking on one sending data process. These queries aren't locked and then just finally being processed - they actually remain in the sending data phase for most of this time. The process varies from time to time - I believe it is something to do with the OS, disk, or ram, but have no idea where to look. The system is NOT swapping, and has 105 MB Ram free. I am using the same disk for my tmp drive and data storage, which is because we were having problems with software raid slowing down the system. An example query stuck in sending data phase: Id UserHostdb Command TimeState Info 130 allpoetry localhost allpoetry Query 231 Sending dataSELECT lid,brief,title,content,collection,written,created,cat1,cat2,cat3,type,p oems.contest,critical,poems.mid,name,preferred,lastlogin,deleted,image,s yndicated FROM poems use index (type) left join poets on poems.mid = poets.mid WHERE poems.mid =2001 ORDER BY created DESC LIMIT 0,30 explained: +---++---+-+-+---+-- --+-| table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --+-| poems | index | NULL | created | 4 | NULL | 272319 | Using where | | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid | 1 | | +---++---+-+-+---+-- --+- Thanks for any help anyone can give me - this is driving me nuts! Ciao, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Saturday, September 27, 2003 6:55 PM To: Kevin Cc: [EMAIL PROTECTED] Subject: Re: # processes vs. #threads, and memory usage (Revisited for thread cache) In the last episode (Sep 27), Kevin said: Bringing this back up again, because the number of extra 'threads' mysql is using seems to vary widely, from I've noticed sometimes, from 5 more than the threads I'm using to more than 50! My memory usage also seems to differ accordingly. It seems to be because my thread cache is set to 40, so 'mytop' shows 38 threads cached, with only 20 connected right now. Shouldn't it kill off those threads after awhile? Is that what the wait_timeout field is for? I thought it was for keeping the connection open, rather than the connection cached. Mine is currently at '600', and it doesn't seem to be clearing up the cache... Idle threads should take up almost no RAM (thread_stack plus a little bit of overhead), so there should be no need to kill them. If you're swapping, add more RAM, or reduce mysql's memory usage by reducing its buffer sizes (check the manual for which ones are used when). wait_timeout is how long before an idle client connection is terminated. If the total thread count is greater than thread_cache, the thread exits too. The reason this is important is because I suspect my 'slow queries' comes up when I run out of ram and it starts using disk swap - which happens because mysql is taking up so much extra memory. Well, that's easy enough to test; just watch vmstat output during a query. If you are swapping, either reduce the global cache settings (key_buffer, query_cache_size, or one of the many innodb_*_size variables), or the per-query settings (sort_buffer, tmp_table_size, join_buffer_size, etc). RAM is cheap too. -- Dan Nelson [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]
Like MySQL Dump, but with FoxPro Tables
Hi all! I want to create an application that transfers data from FoxPro (*.dbf) tables to MySQL. Does MySQL have a DUMP like or LOAD LIKE function that transfers this data in one step? I just don't want to loop into thousands of records to insert them one by one into my MySQL database. It makes my program too slow and very unlikely to use... Thanks in advance Carlos Antonio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Like MySQL Dump, but with FoxPro Tables
you can export the foxpro table to a text file and import to your mysql table. like this: mysql SET AUTOCOMMIT=1; # Used for quick re-create of the table mysql DELETE FROM pet; mysql LOAD DATA LOCAL INFILE pet.txt INTO TABLE pet FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n'; if there are many indexes, it's better to disable indexes, then enable them ALTER TABLE .. DISABLE KEYS ALTER TABLE .. ENABLE KEYS - Original Message - From: Carlos Vazquez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 29, 2003 3:29 PM Subject: Like MySQL Dump, but with FoxPro Tables Hi all! I want to create an application that transfers data from FoxPro (*.dbf) tables to MySQL. Does MySQL have a DUMP like or LOAD LIKE function that transfers this data in one step? I just don't want to loop into thousands of records to insert them one by one into my MySQL database. It makes my program too slow and very unlikely to use... Thanks in advance Carlos Antonio -- 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: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR
I installed a fresh copy mySQL-4.1alpha on XP. I ran it standalone as follows: mysqld-max --standalone --console --default-table-type=innodb I get the same results with my script. Since I didn't specify a character set, I assume it is the default (ISO-8859-1) set as specified in the Section 4.6.1 of the 4.1 manual. I appreciate any and all help. For now, I am having to disable foreign key integrity constraint checking. Ed --- Victoria Reznichenko [EMAIL PROTECTED] wrote: Ed Smith [EMAIL PROTECTED] wrote: Greetings. When I execute the SQL script below in mySQL 4.1, I get ERROR 1216: Cannot add or update a child row: a foreign key constraint fails It is, of course, choking on the enroll row insert. Why is this happening? Here are some things that make the problem go away: 1. Take out the name VARCHAR(30) attribute from student OR change the type to CHAR(30): In Section 6.5.3.1 (second bullet), it says that if one attribute is variable length, all attributes silently become variable length. Does this mean student.sid is really a VARCHAR? Could this be related to the problem? 2. Change type of enroll.sid to VARCHAR(5). 3. Change Earl to Early My theory: student.sid get silently changed from CHAR to VARCHAR since student.name is VARCHAR; however, enroll.sid does not change because there are no variable length fields in enroll. This means that enroll.sid = Earl student.sid = Earl Consequently, there is no match. Note that I did try specifying a length for the index on sid (i.e., INDEX sidindex (sid(5)),) but that didn't help. Even shortening to 4 doesn't help, which doesn't jive with my cohersion theory, assuming I understand the index length specification. I tested your example and it worked fine for me. I didn't get any error. What is value of default-character-set? -- 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] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: corrupted passwd
Saqib, I think the passwd on my installation of mysql of mysql-standard-4.0.14-pc-linux-i686 is corrupted. I can't login with the root account. I was able to do so till last week. I trying to follow the instructions on resetting the passwd, but I get the following err msg. # ./mysqld --skip-grant-tables Fatal error: Please read Security section of the manual to find out how to run mysqld as root! Any ideas? Try starting the MySQL server with the --user option, like this: shell mysqld --user=anyone-but-not-root --skip-grant-tables 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: slow 'sending data' phase
On Mon, 29 Sep 2003, Kevin wrote: I've fixed my swapping issues, but the system continues to get stuck in a 'sending data' phase from time to time. With mod_perl + mysql, this phase SHOULD be when mysql collects the rows (after sorting, etc) and sends them to the perl handler for processing. Any ideas why this phase would ever be taking 100-500 seconds? The system appears to be stable for long times, then gets in a huge bottleneck locking on one sending data process. These queries aren't locked and then just finally being processed - they actually remain in the sending data phase for most of this time. Which version of Apache are you using, for I have heard whisperings of problems with socket communications in apache 1.3.28 + mod_perl -- this might be a good place to look. Rudy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR
At 15:59 +0300 9/28/03, Victoria Reznichenko wrote: Ed Smith [EMAIL PROTECTED] wrote: Greetings. When I execute the SQL script below in mySQL 4.1, I get ERROR 1216: Cannot add or update a child row: a foreign key constraint fails It is, of course, choking on the enroll row insert. Why is this happening? Here are some things that make the problem go away: 1. Take out the name VARCHAR(30) attribute from student OR change the type to CHAR(30): In Section 6.5.3.1 (second bullet), it says that if one attribute is variable length, all attributes silently become variable length. Does this mean student.sid is really a VARCHAR? Could this be related to the problem? 2. Change type of enroll.sid to VARCHAR(5). 3. Change Earl to Early My theory: student.sid get silently changed from CHAR to VARCHAR since student.name is VARCHAR; however, enroll.sid does not change because there are no variable length fields in enroll. This means that enroll.sid = Earl student.sid = Earl Consequently, there is no match. Note that I did try specifying a length for the index on sid (i.e., INDEX sidindex (sid(5)),) but that didn't help. Even shortening to 4 doesn't help, which doesn't jive with my cohersion theory, assuming I understand the index length specification. I tested your example and it worked fine for me. I didn't get any error. What is value of default-character-set? I tried it and it worked for me as well -- with MySQL 4.1.1. With 4.1.0, I get the same error. Looks like a problem with 4.1.0 that is fixed in 4.1.1. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB / Linux
Hi, I'm in the process of setting up a new database server that will run on redhat linux. The machine will be dual processor with 4GB ram and about 16GB disk. The machine is going to be used purely with InnoDB tables and will have a few very large tables acting as cache data. The amount of data I want to store will be between 2 and 4 GB to start with but might grow larger. I've been reading alot on how to set up InnoDB and have come across the 2GB limit problem. There is actually 2 problems here. 1. From reading many articles Linux may or may not support files larger than 2GB. 2. There is a problem with glibc that a process may become unstable if a process allocates more than 2GB. The 1st one isn't a problem, I can have 2 data files of 2GB, but I would like to overcome this issue. The second is where I'm stuck on, the InnoDB configuration page gives a nice formula that you should use so that you can calculate how much memory you should use. It gives an example configuration but this exceeds the 2GB limit even with only 200 concurrent connections. I really need to get the connections to something like 1000 without going over the limit. What configuration can be used and how can this be achieved ? Additionally I have read that each linux thread has a stack of 2MB, this is taken into account in the formula, this can be changed as I understand by changing a #define somewhere and recompiling the kernel and then recompiling the mysql server. Any input would be greatly appreciated. Best Regards, Marvin Wright This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB / Linux
Marvin Wright said: Hi, I'm in the process of setting up a new database server that will run on redhat linux. The machine will be dual processor with 4GB ram and about 16GB disk. The machine is going to be used purely with InnoDB tables and will have a few very large tables acting as cache data. The amount of data I want to store will be between 2 and 4 GB to start with but might grow larger. I've been reading alot on how to set up InnoDB and have come across the 2GB limit problem. There is actually 2 problems here. 1. From reading many articles Linux may or may not support files larger than 2GB. 2. There is a problem with glibc that a process may become unstable if a process allocates more than 2GB. The 1st one isn't a problem, I can have 2 data files of 2GB, but I would like to overcome this issue. The second is where I'm stuck on, the InnoDB configuration page gives a nice formula that you should use so that you can calculate how much memory you should use. It gives an example configuration but this exceeds the 2GB limit even with only 200 concurrent connections. I really need to get the connections to something like 1000 without going over the limit. What configuration can be used and how can this be achieved ? Additionally I have read that each linux thread has a stack of 2MB, this is taken into account in the formula, this can be changed as I understand by changing a #define somewhere and recompiling the kernel and then recompiling the mysql server. Any input would be greatly appreciated. Best Regards, Marvin Wright Depends on: your version of Linux, File system and processor. I believe that the basic 2 GB limit is gone in Linux 2.4 with ext2 or ext3 file system. You may have to turn on some flags or define an estimated table size to cause mysql to use large enough pointers, but I thought that InnoDB could use multiple extants each of which could be up to 2 GB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple charsets do not work in InnoDB-4.1.0; Re: FOREIGN KEY Weirdness in mySQL 4.1..
All,
ok, I found the reason why the non-latin1 bug of 4.0.12 affects also the
default latin1 charset in 4.1.0.
The reason is that the charset 'name' (really the collation name) is
internally 'latin1_swedish_ci', and InnoDB thinks it is a non-'latin1'
charset!
(gdb) print *default_charset_info
$2 = {number = 8, primary_number = 0, binary_number = 0, state = 33,
csname = 0x8389ef0 latin1, name = 0x8389ede latin1_swedish_ci
Current InnoDB-4.1 still does not support multiple charsets, but uses always
the default charset and collation of mysqld. We will see if the support
makes it to 4.1.1.
This also means that if you have created tables with 4.1.0, then you must
not change the default charset later, because InnoDB does not remember the
charset in the table yet. Let us hope InnoDB-4.1.1 will.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Tuesday, September 30, 2003 1:31 AM
Subject: Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR
At 15:59 +0300 9/28/03, Victoria Reznichenko wrote:
Ed Smith [EMAIL PROTECTED] wrote:
Greetings. When I execute the SQL script below in
mySQL 4.1, I get
ERROR 1216: Cannot add or update a child row: a
foreign key constraint fails
It is, of course, choking on the enroll row insert.
Why is this happening? Here are some things that make
the problem go away:
1. Take out the name VARCHAR(30) attribute from
student OR change the type to CHAR(30): In Section
6.5.3.1 (second bullet), it says that if one attribute
is variable length, all attributes silently become
variable length. Does this mean student.sid is really
a VARCHAR? Could this be related to the problem?
2. Change type of enroll.sid to VARCHAR(5).
3. Change Earl to Early
My theory: student.sid get silently changed from
CHAR to VARCHAR since student.name is VARCHAR;
however, enroll.sid does not change because there are
no variable length fields in enroll. This means that
enroll.sid = Earl
student.sid = Earl
Consequently, there is no match.
Note that I did try specifying a length for the index
on sid (i.e., INDEX sidindex (sid(5)),) but that
didn't help. Even shortening to 4 doesn't help, which
doesn't jive with my cohersion theory, assuming I
understand the index length specification.
I tested your example and it worked fine for me. I didn't get any error.
What is value of default-character-set?
I tried it and it worked for me as well -- with MySQL 4.1.1.
With 4.1.0, I get the same error. Looks like a problem with
4.1.0 that is fixed in 4.1.1.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
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: Slow query
I've the same problem with sub-selects too. One way to resolve this problem is creating index on the attribute that you are using in the sub-select (in your case, you are doing a full scan, so the index don't works). Another way is re-creating the query. You can transform the sub-select in a join like this: SELECT T.*, C.NAME FROM arTAGIH T, arCUSTOMER C, arBILL_LNS B WHERE T.BILL_NO != B.BILL_NO AND T.CUST_CODE ... (I don't know if this will resolve your problem :/) But i think that MySQL have some problems with optimizing sub-select. Alexis Guia Quoting DenBaguse MasRodjie [EMAIL PROTECTED]: I'm using MySQL-4.1 alpha because it's support sub-select. But i've tried and the result takes very long time (more then 30 seconds). The query is: SELECT T.*, C.NAME FROM arTAGIH T, arCUSTOMER C, arBILL_LNS WHERE T.BILL_NO NOT IN (SELECT BILL_NO FROM arBILL_LNS) AND T.CUST_CODE=C.CUST_CODE AND T.D_R=arBILL_LNS.D_R AND T.CUST_CODE='ABA' ORDER BY T.TANGGAL The comparison, using MySQL takes 80 seconds, and using MSDE not more than 5 seconds. Is there something wrong with that query or it's true that MySQL has limited ability for sub-select Thank's Roji -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Like MySQL Dump, but with FoxPro Tables
On Monday, September 29, 2003, at 03:29 PM, Carlos Vazquez wrote: I want to create an application that transfers data from FoxPro (*.dbf) tables to MySQL. Does MySQL have a DUMP like or LOAD LIKE function that transfers this data in one step? I just don't want to loop into thousands of records to insert them one by one into my MySQL database. It makes my program too slow and very unlikely to use... You can use the SQL PassThrough features of Visual FoxPro to do this quickly and easily. Create the table in MySQL with the same structure as your Fox DBF. Then run the following code in Fox: lnHandle = SQLCONNECT( [your connection info] ) lcSQL = select * from mytable where 0 * This will create an empty cursor with the structure you need SQLEXEC(lnHandle, lcSQL, crsMyTable) * Make the cursor updatable MakeUpdatable(mytable, crsMyTable, 5, idfield) SELECT crsMyTable APPEND FROM MyFoxTable.DBF ? TABLEUPDATE(.T.) If all goes well, the last line should print .T. on the Fox screen. MakeUpdatable is a handy utility written by Paul McNett that automatically does all the CURSORSETPROP calls for you. It is available for free on my website: http://leafe.com/dls/vfp ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Access DB from MySQLCC
Victoria Reznichenko wrote: MySQL 4.1 provides new password hashing mechanism. This error appears if you connect with pre-4.1 client to the server 4.1. ook at: http://www.mysql.com/doc/en/Password_hashing.html In the above section of the manual you can find description of possible scenarios for 4.1 server. Don't think this is the issue. If it were, I wouldn't be able to use MySQLCC on my own local database which is also 4.1.0 alpha, would I? The only way that I can get the command line on my laptop to connect to my linux box is to include the protocol option (--protocol=TCP), even though the laptop is purely a 4.1.0 install. MySQLCC, apparently, does not accept the protocol command line parameter and it gives me the same error message I get when I don't use the protocol parameter on the command line. Of course, MySQLCC connects just fine to my other MySQL database which also happens to be a 4.0.15 setup on a Win2K desktop. So, I guess it's one of those things where it's both fish and fowl. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
convert grant tables to innodb?
Is it possible, desirable, etc. to convert the mysql grant, system, etc. tables to innodb? I.e., use mysql; alter table columns_priv type = innodb; alter table db type = innodb; alter table func type = innodb; alter table host type = innodb; alter table tables_priv type = innodb; alter table user type = innodb; quit; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: convert grant tables to innodb?
On Mon, Sep 29, 2003 at 06:06:56PM -0700, Rusty Wright wrote: Is it possible, desirable, etc. to convert the mysql grant, system, etc. tables to innodb? I.e., use mysql; alter table columns_priv type = innodb; alter table db type = innodb; alter table func type = innodb; alter table host type = innodb; alter table tables_priv type = innodb; alter table user type = innodb; quit; No, don't do that. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 560,337,668 queries (404/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow 'sending data' phase
Could it be possible that the number of rows returned causes the slowness? There is a 'limit 0,30' in the query, but does it still lookup the join for each row? --+-| poems | index | NULL | created | 4 | NULL | 272319 | Using where | | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid | 1 | There are 272,000 rows returned... This slow 'sending data' phase only happens under decently high load... But my impression of this query was that it should be relatively instantm since its only loading 30 rows and there is an index. Are all 272,000 rows matched against the poets table? Ciao, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -Original Message- From: Kevin [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 12:21 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: slow 'sending data' phase I've fixed my swapping issues, but the system continues to get stuck in a 'sending data' phase from time to time. With mod_perl + mysql, this phase SHOULD be when mysql collects the rows (after sorting, etc) and sends them to the perl handler for processing. Any ideas why this phase would ever be taking 100-500 seconds? The system appears to be stable for long times, then gets in a huge bottleneck locking on one sending data process. These queries aren't locked and then just finally being processed - they actually remain in the sending data phase for most of this time. The process varies from time to time - I believe it is something to do with the OS, disk, or ram, but have no idea where to look. The system is NOT swapping, and has 105 MB Ram free. I am using the same disk for my tmp drive and data storage, which is because we were having problems with software raid slowing down the system. An example query stuck in sending data phase: Id UserHostdb Command TimeState Info 130 allpoetry localhost allpoetry Query 231 Sending dataSELECT lid,brief,title,content,collection,written,created,cat1,cat2,cat3,type,p oems.contest,critical,poems.mid,name,preferred,lastlogin,deleted,image,s yndicated FROM poems use index (type) left join poets on poems.mid = poets.mid WHERE poems.mid =2001 ORDER BY created DESC LIMIT 0,30 explained: +---++---+-+-+---+-- --+-| table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --+-| poems | index | NULL | created | 4 | NULL | 272319 | Using where | | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid | 1 | | +---++---+-+-+---+-- --+- Thanks for any help anyone can give me - this is driving me nuts! Ciao, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Saturday, September 27, 2003 6:55 PM To: Kevin Cc: [EMAIL PROTECTED] Subject: Re: # processes vs. #threads, and memory usage (Revisited for thread cache) In the last episode (Sep 27), Kevin said: Bringing this back up again, because the number of extra 'threads' mysql is using seems to vary widely, from I've noticed sometimes, from 5 more than the threads I'm using to more than 50! My memory usage also seems to differ accordingly. It seems to be because my thread cache is set to 40, so 'mytop' shows 38 threads cached, with only 20 connected right now. Shouldn't it kill off those threads after awhile? Is that what the wait_timeout field is for? I thought it was for keeping the connection open, rather than the connection cached. Mine is currently at '600', and it doesn't seem to be clearing up the cache... Idle threads should take up almost no RAM (thread_stack plus a little bit of overhead), so there should be no need to kill them. If you're swapping, add more RAM, or reduce mysql's memory usage by reducing its buffer sizes (check the manual for which ones are used when). wait_timeout is how long before an idle client connection is terminated. If the
Re: convert grant tables to innodb?
At 18:06 -0700 9/29/03, Rusty Wright wrote: Is it possible, desirable, etc. to convert the mysql grant, system, etc. tables to innodb? I.e., use mysql; alter table columns_priv type = innodb; alter table db type = innodb; alter table func type = innodb; alter table host type = innodb; alter table tables_priv type = innodb; alter table user type = innodb; quit; Not a good idea: The MyISAM storage engine is always present. InnoDB is not. You'll no longer have the option of running a server without InnoDB compiled in, and you'll no longer have the option of starting the server with --skip-innodb. Also mysql_fix_privilege_tables will change the tables to MyISAM even if you change them to InnoDB. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://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: slow 'sending data' phase
Hi Kevin, - Original Message - From: Kevin Sent: Monday, September 29, 2003 2:21 PM Subject: slow 'sending data' phase I've fixed my swapping issues, but the system continues to get stuck in a 'sending data' phase from time to time. With mod_perl + mysql, this phase SHOULD be when mysql collects the rows (after sorting, etc) and sends them to the perl handler for processing. Yes, it may be sending rows, but it also needs to read/process them before sending, which takes time (if filesort is used (not for your query), rows will be read before and after the sort, I think). Any ideas why this phase would ever be taking 100-500 seconds? Yes, when a lot of data needs to be read. :-) I've had the same problem on a smaller scale. :-( How many MB is your poems table? Let's check the EXPLAIN and see if something can be changed... [snip] explained: +---++---+-+-+---+-- --+-| table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --+-| poems | index | NULL | created | 4 | NULL | 272319 | Using where | | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid | 1 | | +---++---+-+-+---+-- --+- It's easier to read that output here if you use \G at the end of the query instead of ;. :-) OK, all 272,000 poems rows are being scanned (assuming mid isn't part of the created index) and the index is being used for ORDER BY. Actually, since there's no filesort, it will abort when and if the LIMIT is satisfied. If mid isn't in the created index, MySQL needs to jump to the data file for each row to check if mid matches the WHERE. If your data file is too big to be cached in RAM by the OS and LIMIT rows aren't found early, the disk seeks will REALLY slow it down. Do all the problem queries have WHERE poems.mid=number in them? Why don't you try adding an index to poems.mid? Even if the WHERE matches a couple thousand rows and filesort is used, it should be a lot faster than reading the whole data file. If you're searching for a single mid value (ref type in EXPLAIN) and you're using MySQL 4+, you can eliminate filesort by creating a composite index on (mid, created) together. By the way, remove use index (type) from the query as there's nothing in your example that would allow an index on type to be used anyway. Thanks for any help anyone can give me - this is driving me nuts! Yeah, see if indexing mid helps. Funny little riddles in your sig BTW. :-D Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert into x select * from x doesn't work
Hi, 4.1.0 was released before 4.0.14 so it wouldn't have the new functionality in it. It should be in 4.1.1 but hasn't been added to the change-log yet. Matt - Original Message - From: Director General: NEFACOMP Sent: Monday, September 29, 2003 4:51 AM Subject: Re: insert into x select * from x doesn't work I think this should be reported as a BUG for version 4.1 Below is a small text retreived from the URL http://www.mysql.com/doc/en/INSERT_SELECT.html --- Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. This limitation is lifted in 4.0.14. --- This should apply to 4.1.0 because it is not prior to 4.0.14 ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow 'sending data' phase
Hi Matt, Thanks for the excellent play-by-play for my query. I can't believe I didn't see the problem - you're right exactly, its using the 'created' index instead of 'mid', and so loading every row into memory, which when the site is under high load causes wicked slowness. This appears to be a semi-bug, since its only when the erroneous 'use index(type)' (a bug in my query-creation routine) appears, it uses the 'created' index. Removing the 'use index(type)' part causes it to correctly choose the 'mid' index instead. Hopefully the other queries I'm seeing the slow 'sending data' phase with are plagued by similar problems. I got too used to only looking to see whether the query in explain was using filesort/whatever, rather than contemplating what its doing to the rows its returning. I guess the complication involved with limiting and joining later confused me to the problem. Ciao, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 7:00 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: slow 'sending data' phase Hi Kevin, - Original Message - From: Kevin Sent: Monday, September 29, 2003 2:21 PM Subject: slow 'sending data' phase I've fixed my swapping issues, but the system continues to get stuck in a 'sending data' phase from time to time. With mod_perl + mysql, this phase SHOULD be when mysql collects the rows (after sorting, etc) and sends them to the perl handler for processing. Yes, it may be sending rows, but it also needs to read/process them before sending, which takes time (if filesort is used (not for your query), rows will be read before and after the sort, I think). Any ideas why this phase would ever be taking 100-500 seconds? Yes, when a lot of data needs to be read. :-) I've had the same problem on a smaller scale. :-( How many MB is your poems table? Let's check the EXPLAIN and see if something can be changed... [snip] explained: +---++---+-+-+---+-- --+-| table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --+-| poems | index | NULL | created | 4 | NULL | 272319 | Using where | | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid | 1 | | +---++---+-+-+---+-- --+- It's easier to read that output here if you use \G at the end of the query instead of ;. :-) OK, all 272,000 poems rows are being scanned (assuming mid isn't part of the created index) and the index is being used for ORDER BY. Actually, since there's no filesort, it will abort when and if the LIMIT is satisfied. If mid isn't in the created index, MySQL needs to jump to the data file for each row to check if mid matches the WHERE. If your data file is too big to be cached in RAM by the OS and LIMIT rows aren't found early, the disk seeks will REALLY slow it down. Do all the problem queries have WHERE poems.mid=number in them? Why don't you try adding an index to poems.mid? Even if the WHERE matches a couple thousand rows and filesort is used, it should be a lot faster than reading the whole data file. If you're searching for a single mid value (ref type in EXPLAIN) and you're using MySQL 4+, you can eliminate filesort by creating a composite index on (mid, created) together. By the way, remove use index (type) from the query as there's nothing in your example that would allow an index on type to be used anyway. Thanks for any help anyone can give me - this is driving me nuts! Yeah, see if indexing mid helps. Funny little riddles in your sig BTW. :-D Matt -- 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 index chooser
Anyone know how the index chooser works, and why it is often so bad? For example, with the following query: SELECT lid,brief,title FROM poems left join poets on poems.mid = poets.mid WHERE poems.mid =9365 ORDER BY created DESC LIMIT 0,10 With an explain of: *** 1. row *** table: poems type: ref possible_keys: mid,mid_2 key: mid_2 key_len: 3 ref: const rows: 17 Extra: Using where; Using filesort *** 2. row *** table: poets type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 3 ref: poems.mid rows: 1 Extra: 2 rows in set (0.00 sec) The index 'mid' is on (mid, created), while mid_2 is on (mid,type,created). It should be obvious in this case that 'type' field is not used while all the elements of the first one are, so it can avoid the filesort by using mid. I find myself adding complex sections to the dynamic search portion of my site for the query-creation code to add 'use index(blah)' in many different cases, as the optimizer isn't getting it right... The explain for the query with a 'use index(mid)' is: *** 1. row *** table: poems type: ref possible_keys: mid key: mid key_len: 3 ref: const rows: 26 Extra: Using where *** 2. row *** table: poets type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 3 ref: poems.mid rows: 1 Extra: 2 rows in set (0.00 sec) The obvious answer to my question would be that it choses the index that returns the least number of rows to be searched. But why would the indicies return different numbers in this case, when they're both only using the (mid) part of the field??? I suspect it uses some algorithm to 'guess' the number of rows, and this usually gives a lower number to bigger indicies? Thanks, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query
Hi, I am trying to learn PHP using MySQL, I downloaded Abriamerlin package for a start as I used this software when I was in uni. But I am having problem to install the connection. When I open the MySQL administrator, my server connection cant be found. Would you mind let me know what I need to install again. Thanks Iona Augustine Pre-Paid Legal Services Pty Ltd Suite 1 / 160 Burswood Burswood, WA 6100
Newbie Q: loading data from a textfile into MySQL database
Greetings, I have a datafile called 'salary.txt', and it's a tab delimited file, the structure is like this: employee_nametabsalaryenter employee_nametabsalaryenter ... I have a table called 'salary', and it has the same structure as the 'salary.txt' --- employee_name salary as the columns; I use the command LOAD DATA INFILE salary.txt INTO TABLE salary; After doing that, I do the 'SELECT * FROM databname' to look at the data I loaded. Although the data are there and can be found out by the SELECT query, the layout seems NOT *tidy* at all comparing with other rows typed in by hand directly using the INSERT command. Please advise how to solve this problem. Do you think it's so messy to load data from a text file to a table? cheers, feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
