[PATCH] LOCK TABLES missing a needed check in 4.0.1
Description: Any LOCK TABLES command (both READ and WRITE), executed from a non-root MySQL user, would fail, giving a 'select command denied' error message. This showed up as Bugzilla being unable to update a bug's state, since locking the necessary tables would fail every time. Unfortunately, bandwidth limitations prevent me from building a recent snapshot of the 4.0.x branch from BitKeeper sources, as Alexander Keremidarski [EMAIL PROTECTED] suggested in a private discussion. Thus, I am unable to check whether the problem is still present in recent versions of MySQL. The 'Web access to the MySQL BitKeeper repository' link in the '1.6.4 Useful MySQL-related links' section of the MySQL manual seems not to work: Error 503: Can't find project root. How-To-Repeat: With a 4.0.1 server and client, execute the following commands: Script started on Fri Jun 14 12:04:26 2002 Setting up interactive shell params.. [roam@straylight:p6 ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.1-alpha SSL is not in use Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create database locktest; Query OK, 1 row affected (0.02 sec) mysql grant all on locktest.* to 'lockt'@'localhost' identified by 'lockp'; Query OK, 0 rows affected (0.03 sec) mysql use locktest; Database changed mysql create table t(id integer auto_increment not null primary key); Query OK, 0 rows affected (0.06 sec) mysql insert into t values (); Query OK, 1 row affected (0.07 sec) mysql insert into t values (); Query OK, 1 row affected (0.04 sec) mysql select * from t; ++ | id | ++ | 1 | | 2 | ++ 2 rows in set (0.00 sec) mysql quit Bye [roam@straylight:p6 ~]$ mysql -u lockt -p locktest Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 to server version: 4.0.1-alpha SSL is not in use Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from t; ++ | id | ++ | 1 | | 2 | ++ 2 rows in set (0.00 sec) mysql lock tables t write; ERROR 1142: select command denied to user: 'lockt@localhost' for table 't' mysql \q Bye [roam@straylight:p6 ~]$ exit exit Script done on Fri Jun 14 12:06:21 2002 The 'select command denied' was the one that should not have come up :) After applying the below fix, stopping, rebuilding, reinstalling and starting the server, and reconnecting to the same database: Script started on Fri Jun 14 12:12:48 2002 Setting up interactive shell params.. [roam@straylight:p6 ~]$ mysql -u lockt -p locktest Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.1-alpha SSL is not in use Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from t; ++ | id | ++ | 1 | | 2 | ++ 2 rows in set (0.05 sec) mysql lock tables t write; Query OK, 0 rows affected (0.00 sec) mysql insert into t values (), (); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql lock tables t read; Query OK, 0 rows affected (0.09 sec) mysql unlock tables; Query OK, 0 rows affected (0.01 sec) mysql select * from t; ++ | id | ++ | 1 | | 2 | | 3 | | 4 | ++ 4 rows in set (0.00 sec) mysql \q Bye [roam@straylight:p6 ~]$ exit exit Script done on Fri Jun 14 12:13:28 2002 As you can see, the fix allows the server to process the LOCK TABLES command successfully. Fix: The problem seems to be in sql/sql_parse.cc, in the mysql_execute_command() function. The processing of SQLCOM_LOCK_TABLES calls check_grant(), which calls table_hash_search(). It would seem that table_hash_search() attempts to search a hash that is only initialized by a check_table_access() invocation. All the other command processing blocks within mysql_execute_command() call check_table_access() before check_grant(); adding this call to the SQLCOM_LOCK_TABLES processing block fixes the problem. --- sql/sql_parse.cc.orig Thu Jun 13 17:47:19 2002 +++ sql/sql_parse.ccThu Jun 13 18:29:52 2002 @@ -2020,6 +2020,8 @@ } if (check_db_used(thd,tables) || end_active_trans(thd)) goto error; +if (check_table_access(thd, SELECT_ACL, tables)) + goto error; if (grant_option check_grant(thd,SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL,tables)) goto error; thd-in_lock_tables=1; Submitter-Id: Originator:Peter Pentchev [EMAIL PROTECTED] Organization: MySQL support: none Synopsis: [PATCH] LOCK TABLES missing a needed check in 4.0.1 Severity: serious Priority: low Category: mysql Class: sw-bug
Re: Trouble with Cyrilic
On Tue, May 22, 2001 at 02:41:44PM -0400, Dimiter Atanasov wrote: Hello, my name is Dimiter Atanasov. I'm newbie in mysql servers. I have a strange problem, when i try to select anythik written on Bulgarian (windows-1251) I recieve a unexpected results, some letters are unacceptable lice a (BG) or 'n' and 'm' and the server miss them. Do you have any ideas how to overcome the problem (mysql - unknown-freebsdelf4) - ver of my mysql Are you using the appropriate charset? The easiest way to configure MySQL to use Win-1251 is to compile it from the databases/mysql323-server port, using the following command: make WITH_CHARSET=cp1251 WITH_XCHARSET=all fetch clean all install If you have installed MySQL as a binary package, there might be a way to change the currently used character set, though I'm not really sure how to do that. The MySQL manual mentions SET OPTION CHARACTER SET, but I've never done it that way - I've always compiled MySQL from source, and specified the correct character set to use at build time for both the server and the client. G'luck, Peter -- This sentence claims to be an Epimenides paradox, but it is lying. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Average of all NON-NULL columns in a ROW?
On Tue, May 22, 2001 at 12:29:35PM -0400, Graeme B. Davis wrote: Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? SELECT SUM(field) / COUNT(field) FROM table WHERE othercondition AND (field IS NOT NULL) G'luck, Peter -- I am the meaning of this sentence. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to retrieve record-id after 'insert into'?
Did you take a look at the LAST_INSERT_ID() function I mentioned? G'luck, Peter -- This sentence no verb. On Tue, May 22, 2001 at 01:53:24PM -0300, Siomara Pantarotto wrote: In order to retrieve the id you inserted and not someones's id that was inserted a bit after yours, I guess you should lock the table before your insert statement, do the insert, retrieve the id just inserted, and then unlock the table so others can operate with the table again. This is my guess... Anybody have another way??? Siomara From: Peter Pentchev [EMAIL PROTECTED] To: Viktor van den Berg [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to retrieve record-id after 'insert into'? Date: Mon, 21 May 2001 17:21:44 +0300 On Mon, May 21, 2001 at 03:58:10PM +0200, Viktor van den Berg wrote: Hi! I like to know how to retrieve the record id after an insert into query. I have the table user containing two columns: UID (auto_increment) and USERNAME. After I have added a new user (using insert into user (uid) etc.) I like to know the UID of the new record. I can set the record pointer to the last record, but if anybody else also inserts a record at the same time, I think this will give a problem. Can anybody give me a suggestion? Look at the MySQL manual. Reference INSERT, look for mentioning of AUTO_INCREMENT fields, and specifically, for a reference to the LAST_INSERT_ID() function. Simple, isn't it? :) All referenced in the manual, if you take the time to check what the manual has to say about 'INSERT'.. :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Average of all NON-NULL columns in a ROW?
Ahh.. that, too :) G'luck, Peter -- If I were you, who would be reading this sentence? On Tue, May 22, 2001 at 12:04:34PM -0500, Cal Evans wrote: Select avg(id1) from tableName where id1 is not null; ? - Original Message - From: Graeme B. Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 11:29 AM Subject: Average of all NON-NULL columns in a ROW? Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: bug report
Can you try this with a more recent MySQL version? I cannot reproduce it here, on FreeBSD 4.3 running MySQL 3.23.38. G'luck, Peter -- This sentence contains exactly threee erors. On Tue, May 22, 2001 at 08:32:24PM +0300, Tarog Adrian wrote: Hello, I think me and my fellows here at office, found a bug in mysql. The system is: RedHat 7.0, kernel 2.2.16, on Celeron 700MHz mysql version 3.23.22 Here is the script: ( cat EOF connect test; create table test (i numeric(4), j numeric(4)); insert into test (i,j) values (1, 1); insert into test (i,j) values (1, 2); insert into test (i,j) values (1, 3); insert into test (i,j) values (2, 1); insert into test (i,j) values (2, 2); select * from test; select i, min(j), max(j) from test group by i; EOF ) | mysql -p and here is the result: i j 1 1 1 2 1 3 2 1 2 2 i min(j) max(j) 1 0 3 2 0 2 the interesting part is that if I replace create table test (i numeric(4), j numeric(4)) with create table test (i integer, j integer) , then the result is ok We have tested this script on another machine with Pentium 100MHz and RH7.0, and the result is the same. I hope this bug report will be usefull. Looking forward for your reply, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem in creating table structure
On Tue, May 22, 2001 at 10:36:02AM -0700, bineet suri wrote: hello mysql This is bineet from osprey software technology from india actually i just installed and configure the MySql database instead of postgres in linux and i have a script which actaully create the table structure in the database it create the sequence and indexes too.In the sequence it actally increment the one of feild from one table But after installing and creating database in MySql i find that MySql doesnot support sequence so could pl tell me what would be the sloution for that is any autoincrement filed in MySql for this and could you pl send any document related to command and data types etc which actually MySql support. i am very new in MySql and you know it;s different from others. [snip] Before posting, please check: http://www.mysql.com/manual.php (the manual) Look at the link above, specifically at 7.3. Column Types and 7.7. CREATE TABLE syntax. In the latter, take a look at something you described yourself - AUTO_INCREMENT. G'luck, Peter -- This sentence every third, but it still comprehensible. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: why does it not work
On Mon, May 21, 2001 at 11:32:46AM +0530, Adrian D'Costa wrote: Hi, I have been trying this on my localhost and this works select special.contid, special.cityid, special.curr1, special.catalogo, special.hf, special.curr2, special.vitofferta, special.fh, special.club, special.stars, city.city from special,city where DATE_SUB(special.periodo, INTERVAL 3 DAY) '$tdate' and city.id=special.cityid order by RAND() limit 1 My version +--+ | version()| +--+ | 3.23.22-beta-log | +--+ but the same sql statement on my online server does not work. select special.contid, special.cityid, special.curr1, special.catalogo, - special.hf, special.curr2, special.vitofferta, special.fh, special.club, special.stars, city.city from special,city where DATE_SUB(special.periodo, INTERVAL 3 - DAY) '$tdate' and city.id=special.cityid order by RAND() limit 1; ERROR 1064: You have an error in your SQL syntax near 'RAND() limit 1' at line 3 select version(); +---+ | version() | +---+ | 3.22.32 | +---+ What could be the problem A very-very-very quick search for 'ORDER BY RAND' in the MySQL manual turned up the following at the middle of the documentation of the RAND() function: You can't use a column with `RAND()' values in an `ORDER BY' clause, because `ORDER BY' would evaluate the column multiple times. In *MySQL* Version 3.23, you can, however, do: `SELECT * FROM table_name ORDER BY RAND()' It does say 'in MySQL version 3.23', doesn't it now? :) 3.22 does not support this syntax. G'luck, Peter -- Nostalgia ain't what it used to be. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: triggers
On Wed, May 16, 2001 at 04:13:50PM +0530, [EMAIL PROTECTED] wrote: We are planning to migrate from Oracle to mysql. Before we do it, i need clarification on: 1. Does mysql supports triggers ? 2. if a client has to excess mysql then is a copy of mysql needed to be installed on the clients machine (just as u have sql3.3 for oracle)? As to (1), no, MySQL does not support triggers as of now, and I do not really know of any plans to support them in the future. What is done with triggers can be (maybe not just as easily, but certainly faster in most cases) done with client-side programming and proper database design. As to (2), to access a MySQL server, you need the MySQL client libraries. No, you do not need the whole MySQL package (along with the server) installed. G'luck, Peter -- This sentence contains exactly threee erors. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can one validate a date in mysql ?
On Tue, May 15, 2001 at 11:27:19AM +0200, Vankeerberghen, Pieter wrote: Thank you, Ok, I looked in the manual but I coul dnot obtain an answer. How can I check that a date I'm importing is valid, e.g. how to check for 20001131 (MMDD) ? You'll have to do this kind of validation checks in your actual program that uses the MySQL interface, *before* executing the SQL statement itself. G'luck, Peter -- yields falsehood, when appended to its quotation. yields falsehood, when appended to its quotation. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Create MULTIPLE TABLES ?
On Tue, May 15, 2001 at 12:43:02PM +0200, RAZAKA wrote: I would like to create multiple tables in a database. HowTo Use REFERENTIAL INTEGRITY with MySQL ? May I use FOREIGN KEY or REFERENCES? Note: MySQL Release = 3.23.36 Thanks for help :) In short, you can't.. From the MySQL manual (Reference CREATE TABLE): * The `FOREIGN KEY', `CHECK', and `REFERENCES' clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. G'luck, Peter -- What would this sentence be like if pi were 3? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Starting up mysqld at boot time as NOT root
On Tue, May 15, 2001 at 10:24:38AM -0400, Chris Lambrou [CGL] wrote: Hello, This is more like a UNIX question: In our LINUX box startup file, we have an entry to start mysql. However, when the machine boots, mysql starts as root. How do we make it to start as mysqladm (the user we setup for mysql) Any help is much appreciated. Look at the startup script, see where it invokes the safe_mysqld script or mysqld itself, see what flags/options it invokes that with, and find a way to add -u mysqladm to the command line. G'luck, Peter -- If you think this sentence is confusing, then change one pig. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: EXCEPT statement in MYSQL?
On Tue, May 15, 2001 at 04:45:22PM +0200, Viktor van den Berg wrote: Hi, I am new to this list, so maybe this question is asked before. I like to know how to use the except statement in MYSQL. EXCEPT is (almost?) the same as an exlusive or (XOR): select userid from user1 except select userid from user2 The result is a data set containing userid's that are only availlable in user1 or in user2. If the userid is availlable in user1 AND user2, then it won't be availlable in the result. The question is what syntax to use in MYSQL to achieve this result! One of the possible ways would be SELECT u1.userid FROM user1 u1 LEFT JOIN user2 u2 ON u2.userid=u1.userid WHERE u2.userid IS NULL; G'luck, Peter -- This inert sentence is my body, but my soul is alive, dancing in the sparks of your brain. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: in SQL query can we use sysdate to get System date
On Thu, May 10, 2001 at 10:24:11AM -, AVDHUT SHEDGE wrote: in SQL query can we use sysdate to get System date like Oracle What's wrong with NOW(), as seen in the manual? Reference Functions Date and Time Functions Next time, take a look there :) G'luck, Peter -- yields falsehood, when appended to its quotation. yields falsehood, when appended to its quotation. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Root User (super user)
On Thu, May 10, 2001 at 10:16:33AM -0700, Simon Chan wrote: In 6.13 of the mysql manual (Setting up th initial Mysql Privileges), it mentions that the Mysql root user is created as a superuser who can do anything. The Initial root password is empty, so anyone can connect as root without a pasword and be granted all privileges. How is this mysql super user created? Where can I find the username? It is NOT the same as the username I add with the useradd command, is it? MySQL users are kept separate from the system users. The MySQL username is the one you specify when you connect to the server, either with the -u command-line option, or with environment variables, or with the my.cnf file. The MySQL root user, the one created as superuser, comes with the username 'root' :) Just start your MySQL server, and try: mysql -u root This shall bring you to the MySQL prompt, if you have not configured a password with mysqladmin. G'luck, Peter -- This sentence was in the past tense. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem using SELECT INTO DUMPFILE
On Wed, May 09, 2001 at 09:20:52AM -0400, Bill Moran wrote: Thanks for the reply, but I need a binary transfer of the DATA in the field only. INTO OUTFILE does 2 things that corrupt the data: 1. Puts field data in the file 2. Escapes characters. As far as I can tell, I either have to use INTO DUMPFILE or I need to write a C program to do what I need. Any other suggestions? The MySQL manual says: SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RES\ ULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_expression,... [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references So try: SELECT resume INTO DUMPFILE '/data/file.doc' FROM apps WHERE appid=23 In other words, just move the 'INTO DUMPFILE' part where it belongs, just after the expression, before any 'from' clauses :) G'luck, Peter -- I am the thought you are now thinking. Robin Keech wrote: use SELECT * INTO OUTFILE 'filename' . -Original Message- From: Bill Moran [mailto:[EMAIL PROTECTED]] Sent: 09 May 2001 00:44 To: [EMAIL PROTECTED] Subject: Problem using SELECT INTO DUMPFILE I get an error stating: you have an error in your SQL syntax near 'dumpfile '/data/file.doc'' The command I'm trying to execute is: select resume from apps where appid=23 into dumpfile '/data/file.doc' The server and client are on two different computers. I'm using client version 3.23.36 on machine redfs Server version 3.22.32 on machine redsql Do I need to upgrade the server before this will be possible? I understand that SELECT INTO DUMPFILE must put the file on the local machine, but does that mean when running the client on redfs, I'm trying to put the file on redsql? (just thought of this, but it would be weird to get that particular error if that were the case) That wouldn't work, since there is no /data directory on redsql. If that's the problem, I suppose I'll have to establish a NFS mount. Any pointers are welcome. Please keep me in the CC box as I'm not currently subscribed to this list. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem using SELECT INTO DUMPFILE
On Wed, May 09, 2001 at 09:43:54AM -0400, Bill Moran wrote: Thanks for the reply, unfortunately, this produces the same error as the command I tried. (ERROR 1064: near 'dumpfile '/data/test.doc' where appid=23') I can do a INTO OUTFILE just fine if I use this syntax: SELECT resume FROM apps WHERE appid=23 INTO OUTFILE '/data/test.doc'; Using this: SELECT resume FROM apps INTO OUTFILE '/data/test.doc' WHERE appid=23; Produces an error 1064 Of course it would. Put the 'into dumpfile' part BEFORE the 'from' clause. And btw, which version of MySQL are you running? The query I listed, with 'into dumpfile' before the 'from' clause, works fine on MySQL 3.23.37. It does NOT work on MySQL 3.22.x, because, in the same manual, it is plainly listed that: Changes in release 3.23.6 - * Added `mysqld' option `-O lower_case_table_names={0|1}' to allow users to force table names to lowercase. * Added `SELECT ... INTO DUMPFILE'. . So, MySQL 3.22.x does not have the capability to produce dumpfiles, only outfiles. Ah. I just read the whole of your message. You are using a 3.22.32 server - so, sorry, but you're out of luck :( G'luck, Peter -- When you are not looking at it, this sentence is in Spanish. Perhaps this is a documentation goof (although I read the docs to mean the first form was correct, perhaps it could be clarified) One way or the other, this doesn't solve my probem ;) Thanks for the feedback so far. -Bill Peter Pentchev wrote: On Wed, May 09, 2001 at 09:20:52AM -0400, Bill Moran wrote: Thanks for the reply, but I need a binary transfer of the DATA in the field only. INTO OUTFILE does 2 things that corrupt the data: 1. Puts field data in the file 2. Escapes characters. As far as I can tell, I either have to use INTO DUMPFILE or I need to write a C program to do what I need. Any other suggestions? The MySQL manual says: SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RES\ ULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_expression,... [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references So try: SELECT resume INTO DUMPFILE '/data/file.doc' FROM apps WHERE appid=23 In other words, just move the 'INTO DUMPFILE' part where it belongs, just after the expression, before any 'from' clauses :) G'luck, Peter -- I am the thought you are now thinking. Robin Keech wrote: use SELECT * INTO OUTFILE 'filename' . -Original Message- From: Bill Moran [mailto:[EMAIL PROTECTED]] Sent: 09 May 2001 00:44 To: [EMAIL PROTECTED] Subject: Problem using SELECT INTO DUMPFILE I get an error stating: you have an error in your SQL syntax near 'dumpfile '/data/file.doc'' The command I'm trying to execute is: select resume from apps where appid=23 into dumpfile '/data/file.doc' The server and client are on two different computers. I'm using client version 3.23.36 on machine redfs Server version 3.22.32 on machine redsql Do I need to upgrade the server before this will be possible? I understand that SELECT INTO DUMPFILE must put the file on the local machine, but does that mean when running the client on redfs, I'm trying to put the file on redsql? (just thought of this, but it would be weird to get that particular error if that were the case) That wouldn't work, since there is no /data directory on redsql. If that's the problem, I suppose I'll have to establish a NFS mount. Any pointers are welcome. Please keep me in the CC box as I'm not currently subscribed to this list. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem using SELECT INTO DUMPFILE
On Wed, May 09, 2001 at 10:51:44AM -0400, Bill Moran wrote: Peter Pentchev wrote: So, MySQL 3.22.x does not have the capability to produce dumpfiles, only outfiles. Ah. I just read the whole of your message. You are using a 3.22.32 server - so, sorry, but you're out of luck :( A Curses, I was hoping to avoid an upgrade (since we've had nothing but trouble with other clients accessing this server and I don't want to shake things up with an upgrade!) But, really, that answers my question - the SERVER must be 3.23.6. Apparently the client makes little difference. I'll also have to NFS mount the file sever to the SQL server to get the file where I want it. It's a pain, but at least I have an answer. Well, SELECT INTO [OUTFILE | DUMPFILE] is a server-side operation, as (again) clearly outlined in the docs :) * The `SELECT ... INTO OUTFILE 'file_name'' form of `SELECT' writes the selected rows to a file. The file is created on the server host and cannot already exist... So.. yes, you'll have to upgrade the server to use SELECT INTO DUMPFILE. G'luck, Peter -- I had to translate this sentence into English because I could not read the original Sanskrit. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Probs with AUTO_INCREMENT column
On Mon, May 07, 2001 at 07:21:59AM -0700, [EMAIL PROTECTED] wrote: Hi all, I've started using MySQL for the first time and I'm hvaing a bit of a problem with defining an AUTO_INCREMENT column. I'm using ver 3.23.32 which I downloaded for Linux and Win2k. I am having this problem on both systems. Ok if I try to run the create table statement: create table test (id_pk numeric(10) AUTO_INCREMENT) I get the error: Error while executing statement: Invalid argument value: Incorrect column specifier for column 'id_pk' Correct the statement and do another try! Does anyone know why this happens? Am I missing something that needs to included during compilation? Nope, it's just that AUTO_INCREMENT can only be used with INTEGER columns. Also, an AUTO_INCREMENT column must be defined as a key. So, try: CREATE TABLE test (id_pk INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY); G'luck, Peter -- I am not the subject of this sentence. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment
True. The only thing that could be done is write a UDF (user-defined function), which accepts a table and field name as parameters and somehow manages to find the first unused ID in that table.. and hope it is atomic, too.. I don't think this is too easy to do, though :) G'luck, Peter -- Hey, out there - is it *you* reading me, or is it someone else? On Fri, May 04, 2001 at 02:01:42PM +0200, Bruce Stewart wrote: I believe that this behaviour is standard for autoincrementing fields on all database systems that support them. -Original Message- From: Stefan Wehowsky [mailto:[EMAIL PROTECTED]] Sent: Fri, 04 May 2001 10:37 To: [EMAIL PROTECTED] Subject: auto_increment Let's say I got a column id that is of type tinyint and has the extra auto_increment. Let's further say that I have 50 entries in that column. Now if I delete e.g. entry No. 30 and right after that add another entry without naming an id (for ist auto_increment) MySQL gives it the id 51 AND NOT 30 which leads to more and more gaps between the id's. Is there anything i can do about that ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Concat Question
On Mon, Apr 16, 2001 at 07:54:56AM -0500, Jeff Holzfaster wrote: Hi! This query works: select date_format(date, "%W, %e %M %Y") as date from table This query doesn't: select concat(date_format(date, "%W, %e %M %Y")," ",another) as time_of_day I'm wondering if it is possible to use concat in this way and how if it is possible. Is there a newline between the quotes in the second example, or are those just spaces, broken to another line by your mailer? What exactly is 'another' in your second query? This works for me, even with a newline: mysql select concat(date_format(a_mtime, "%W, %e %M %Y"), " " ", a_muser) as time_of_day from articles where a_muser='roam'; +--+ | time_of_day | +--+ | Wednesday, 4 April 2001 roam | | Saturday, 24 March 2001 roam | | Monday, 26 March 2001 roam | +--+ 3 rows in set (0.05 sec) G'luck, Peter -- I've heard that this sentence is a rumor. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count distinct
On Fri, Apr 13, 2001 at 12:37:40PM +0200, Z_da_eXTaZie wrote: I can make this query: select a from table. I can count it: select count(a) from table. I can select it: select distinct a from table. But how can i count it? select count(distinct a) from table doesn't works It works for me on MySQL 3.23.36.. which version of MySQL are you running? G'luck, Peter -- I am jealous of the first word in this sentence. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Q U E R Y
First, get a book on SQL. Read about joins in SELECT statements. Then, if you still need a quick answer, try the following: INSERT INTO table_c(name, score) SELECT name, a.score - b.score FROM table_a a LEFT JOIN table_b b ON b.name = a.name; Hope that helps. G'luck, Peter -- No language can express every thought unambiguously, least of all this one. On Tue, Apr 10, 2001 at 03:57:54PM +0545, Deependra B. Tandukar wrote: Greetings! I am using MySQL in RedHat 6.2 with PHP 4.0. I have a question on MySQL. Suppose I have two tables A and B in my database: Table A Name Score a45 b20 c75 d55 Table B NameScore a20 b9 c25 Now I need to subtract values of table B from table A and result should be like: Table C NameScore a25 b11 c50 d55 How can I do this? Looking forward to hearing from you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax problem...
On Tue, Apr 10, 2001 at 12:48:26PM +0200, Guerin Damien wrote: Hi, How use CHECK constraint in a CREATE TABLE declaration ?? The language used is MySQL Indeed, i try many attempt but no success... Somebody could help me ?? I try to do that : create table Picsou ( name varchar(5) check (name=='fifi' or name=='riri' or name=='loulou') ) ; But ERROR !! Maybe there are some website to explain but i haven't found. Try reading the MySQL manual, Reference CREATE TABLE.. * The `FOREIGN KEY', `CHECK', and `REFERENCES' clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. G'luck, Peter -- I had to translate this sentence into English because I could not read the original Sanskrit. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INET_ATON
It would be my guess that you're trying to store inet_aton()'s value into a signed integer column, which is in the range of roughly -2G - 2G-1, while inet_aton()'s result is unsigned, in the range 0 - 4G-1. Whenever inet_aton() happens to return a value 2G-1, MySQL truncates it and stores into your table the largest value possible for the column type you specified. ALTER TABLE sequoia MODIFY ip INTEGER UNSIGNED whatever else you want; G'luck, Peter -- What would this sentence be like if pi were 3? On Tue, Mar 20, 2001 at 02:09:48PM -0800, Jason Bell wrote: ok... this is stumping me perhaps I'm doing something wring, but It doesn't appear that I am Am I doing, or not doing something that makes the INET_ATON function default to 127.255.255.255 ? why does it work for 24.18.10.5, but not 192.168.50.5 when inserting into a table? mysql SELECT INET_ATON("24.18.10.5"); +-+ | INET_ATON("24.18.10.5") | +-+ | 403835397 | +-+ 1 row in set (0.01 sec) mysql SELECT INET_ATON("192.168.50.5"); +---+ | INET_ATON("192.168.50.5") | +---+ |3232248325 | +---+ 1 row in set (0.01 sec) mysql insert into sequoia VALUES ( INET_ATON("24.18.10.5"), 'TestHost', 'This is a test', 'Test' ); Query OK, 1 row affected (0.03 sec) mysql insert into sequoia VALUES ( INET_ATON("192.168.50.5"), 'TestHost2', 'This is a test', 'Test' ); Query OK, 1 row affected (0.01 sec) mysql select * from sequoia; ++---++--+ | ip | hostname | comment| customer | ++---++--+ | 403835397 | TestHost | This is a test | Test | | 2147483647 | TestHost2 | This is a test | Test | ++---++--+ 2 rows in set (0.01 sec) mysql select INET_NTOA(ip),hostname from sequoia; +-+---+ | INET_NTOA(ip) | hostname | +-+---+ | 24.18.10.5 | TestHost | | 127.255.255.255 | TestHost2 | +-+---+ 2 rows in set (0.01 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please
On Wed, Mar 21, 2001 at 05:07:59AM +0300, [EMAIL PROTECTED] wrote: please tell me about this error: Fatal error: Call to unsupported or undefined function mysql_pconnect() in mainfile.php on line 17 or Fatal error: Call to unsupported or undefined function mysql_pconnect() in ./db_mysql.php on line 73 Your PHP does not have MySQL support compiled in. Recompile PHP, or install a package which has been built with MySQL support. G'luck, Peter -- If I had finished this sentence, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Please Help - Empty Entries
On Tue, Mar 20, 2001 at 03:44:44PM -0800, Marcus Ouimet wrote: I went into my database today and noticed that there was 102,000 entries. Somehow a whole pile of crap was added to the end of the database. I think it can easily be fixed but am not sure. Is there a way that I can eliminate all entries with a field that is blank? ie: if the table products is blank in the name field delete it? Is this possible? The DELETE SQL statement has a WHERE clause similar to that of the SELECT statement; of course you can do a: DELETE FROM table WHERE field = ''; or, maybe more relevant, DELETE FROM table WHERE field IS NULL; depending on what exactly you mean by 'blank'. G'luck, Peter -- Hey, out there - is it *you* reading me, or is it someone else? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: non-standard socket
On Tue, Mar 20, 2001 at 03:12:39PM -0600, Jeff Jones wrote: I am using mysqld_multi to have several different mysql servers start. I have finally gotten it to work with php when I connect to a non-standard socket, such as /tmp/mysql.sock2. Does anyone know how to connect to a database with perl through that socket? I have tried the following with no luck: my($port) = '/tmp/mysql.sock2'; my($mysql) = "DBI:mysql:webhost:localhost:$port"; $dbh = DBI-connect($mysql, 'user', 'pass'); But it will not establish the connection. Has anyone done this before or is it even possible with the Perl DBI? Do a 'perldoc DBD::mysql' (or whatever the module name is on your system). With version 1.2215 of the mSQL/MySQL modules on a FreeBSD system, this is part of the documentation: mysql_socket As of MySQL 3.21.15, it is possible to choose the Unix socket that is used for connecting to the server. This is done, for example, with mysql_socket=/dev/mysql Usually there's no need for this option, unless you are using another location for the socket than that built into the client. So, the following excerpt.. my $dbh = DBI-connect( "DBI:mysql:database=test;host=localhost;mysql_socket=/tmp/mysql-alt.sock", "root", "", 0); ..Works For Me (tm). Hope that helps. G'luck, Peter -- Hey, out there - is it *you* reading me, or is it someone else? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to put and get gziped file to/from mysql db
On Tue, Mar 20, 2001 at 11:55:59AM -0500, [EMAIL PROTECTED] wrote: Hello I am looking for help how to put gziped plain data file to mysql DB and then get it from there. Every time I try OUTFILE to file and then gzip -d file name I got file corrupted Any help will be appreciated Are you sure you want -d with that? gzip -d decompresses an already compressed file; maybe you want to just gzip it first. G'luck, Peter -- I had to translate this sentence into English because I could not read the original Sanskrit. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql command - copy of one row
On Mon, Mar 19, 2001 at 12:22:09PM +0100, - = k o l i s k o = - wrote: Hi! I have a problem. I would like copy one row from table1 to another table2. How could I do? I tried something like this: insert into table2 values (select * from table1 where username = '$uname') both (table1 and table2) have the same structure. Every time when I try do this sql command i get syntax error in sql command. Is there really an SQL server that would accept this query? Try just: insert into table2 select * from table1 where username = '$uname'; G'luck, Peter -- because I didn't think of a good beginning of it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problems migrating from mysql 3.22.22 to 3.23.32
On Fri, Feb 02, 2001 at 03:38:09PM -0500, Noah Romer wrote: mysql insert into bugs_activity (bug_id,who,when,field,oldvalue,newvalue) values (334,18,20010131145149,'bug_status','NEW','RESOLVED'); ERROR 1064: You have an error in your SQL syntax near 'when,field,oldvalue,newvalue) values (334,18,20010131145149,'bug_status','NEW','' at line 1 The MySQL manual, Reference Reserved Words. 'WHEN' is listed as a reserved word. Change your field's name. G'luck, Peter -- Nostalgia ain't what it used to be. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: same file size after DELETE
On Mon, Jan 29, 2001 at 01:30:57PM +0100, Tim Samshuijzen wrote: Hello, I have just deleted thousands of records but the file is still the same size as before. How do I make the file compact? i.e. how do I get rid of all the "empty spaces" in the table? Look at the MySQL manual. Reference DELETE explicitly mentions OPTIMIZE TABLE for such cases. G'luck, Peter -- I am the thought you are now thinking. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to delete records older than 14 days
On Sat, Jan 27, 2001 at 09:58:23AM -0600, Thomas Katsampes wrote: Hello, I'm trying to figure out the correct syntax for requesting the database to remove all records older than 14 days. The mysql query that I am using (from within php4) is: //---update news so only last two weeks of news are available---// $sql2 = "delete from news where newsdate DATE_SUB(NOW(), INTERVAL 14 DAY)"; $result2 = mysql_db_query($dbname,$sql); Uh.. is this an *exact* quote of your PHP script? Why are you defining $sql2, and using $sql in the mysql_db_query()? G'luck, Peter -- If I had finished this sentence, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT - very newbie question probably...
On Tue, Jan 23, 2001 at 11:19:20AM +0100, Gustav Wiberg wrote: Hi Why can't I do like this? insert into tbnamn (fornamn, efternamn) values('g1','g2','g3','g4'); where fornamn and efternamn is the only fields in the table tbnamn Try: insert into tbnamn (fornamn, efternamn) values ('g1','g2'), ('g3','g4); G'luck, Peter -- This would easier understand fewer had omitted. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Update and change a value
I believe Tomi Junnila meant you should try without the comma.. UPDATE users SET visits=visits+1WHERE ... (no comma before WHERE) G'luck, Peter -- I am jealous of the first word in this sentence. On Tue, Jan 23, 2001 at 06:27:22PM +0800, Jamie wrote: I've been trying a variety of things with it incl. 'visits+1' , (visits+1) , ('visits+1') , 'visits=visits+1' and not quotes or commas still doesn't seem to work. -Original Message- From: Tomi Junnila [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 23 January 2001 6:02 PM To: [EMAIL PROTECTED] Subject: Re: Update and change a value * Jamie [EMAIL PROTECTED] wrote on 23.01.01 11:51: UPDATE users SET visits=visits+1, WHERE user_id='$user' AND password='$password' If that comma after +1 is not a typo in the mail, then that's what's causing the problem. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL query problem
On Tue, Jan 23, 2001 at 05:39:47PM +0100, Sander Pilon wrote: Okay, here's one for the guru's out there :) I have a list of entries with unique id numbers X, and a set of sort methods (S1 ... Sy). Now, if I want to get an entry at position P (0...z) in the list of entries ordered by method S1 then I'd make the following query: SELECT X FROM table WHERE ORDER BY S1 LIMIT P,1 But now I want the inverse - given an id X and a sort method, I want the position. something like: SELECT POSITION(X) FROM table WHERE . ORDER BY S1 Is there a way to do this? You could try something like.. SELECT COUNT(X) FROM table WHERE AND X (yourX) ORDER BY S1 where X is the name of the field (literally), and yourX is the value you're interested in. After that, just add 1. NOTE: This does not guarantee that yourX actually exists in the table; if it doesn't, this will happily return the position yourX WOULD HAVE BEEN at, had it existed. To find out if it exists, you'll have to do a separate query. G'luck, Peter -- The rest of this sentence is written in Thailand, on - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php