WHERE doesn't work
From: Michael Stassen [EMAIL PROTECTED] With all due respect, there is almost certainly an error in your code. I've been on this list for years, and so far as I can recall, every instance of mysql appearing to do something strange from php turned out to be a flaw in the php code, not a problem with mysql. Many of us on the list perform updates with WHERE clauses via php without problems. The most likely explanation is that there is something wrong with the WHERE clause as sent to mysql by your php app. Perfectily =) see my last mail, Im posting all. So, you can keep repeating that it doesn't work, and we can keep flailing away with blind guesses, but I expect you won't get a solution until you actually show us the code. Also, you've told us that your PHP Version is 4.4.2, and your Mysql client API version is 3.23.49, by which I expect you mean that your copy of php was built with the 3.23.49 version of the mysql client library. Unless I've missed it though, you haven't told us what version of mysql you have, how it was installed (official binary, some other binary, compiled yourself?), and what OS it's on. That information may help. Exactily =) Sorry me. I wasnt clear. 2.23.49 is the version of the mysql api of the php and of the mysql server. The mysql server run on Windows XP Pro. Mysql server I installed from binary packge mysql-3.23.49-win.zip from mysql.org. However, I tested all this in the web host too, with versions little different (Linux: PHP Version 4.4.1, mysql client api version 4.0.25, Mysql server 4.0.25) and got same results: WHERE _apparentily_ is ignored. Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE doesn't work
From: [EMAIL PROTECTED] you really need to show your php code and the output from an echo of the update line so that people can try to spot what you're doing wrong. from what i've seen, people are basically just guessing at the issue with you saying tried that, didn't work. updating mysql records via php code, with a where, works just fine. i.e., this isn't a php/mysql bug. rather there's something in your code that's not quite right. so, show your code, the table (definition and data) that you're trying to update, and output from appropriate echoing of statements and we can probably help you figure out your problem. Perfectly, I didnt show it before just for dont bore you with so many code =) The echo ouput: UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24' WHERE id = '5' The php and db structure and data are attacheds. Two infos: 1) Im not a expert 2) The code is in the beggining of the development, so are too many uglyness yet =) Thank you Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: describe table : improvement
Thankyou Michael. I'm trying to work my way through the 5.0.18/19 manual, but I seem to have missed that. Regards Keith In theory, theory and practice are the same; in practice they are not. On Sat, 22 Apr 2006, Michael Stassen wrote: To: [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] Subject: Re: describe table : improvement [EMAIL PROTECTED] wrote: Thankyou Gabriel. So how does one set a column comment then? Regards Keith As part of the column definition, as documented in the manual http://dev.mysql.com/doc/refman/5.0/en/create-table.html. column_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE doesn'r works
From: [EMAIL PROTECTED] you really need to show your php code and the output from an echo of the update line so that people can try to spot what you're doing wrong. from what i've seen, people are basically just guessing at the issue with you saying tried that, didn't work. updating mysql records via php code, with a where, works just fine. i.e., this isn't a php/mysql bug. rather there's something in your code that's not quite right. so, show your code, the table (definition and data) that you're trying to update, and output from appropriate echoing of statements and we can probably help you figure out your problem. Perfectly, I didnt show it before just for dont bore you with so many code =) The echo ouput: UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24' WHERE id = '5' The php and db structure and data are attacheds. Two infos: 1) Im not a expert 2) The code is in the beggining of the development, so are too many uglyness yet =) Thank you Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
From 'Section 13.5.3 SET Syntax' of the 5.0.18 ref manual: IDENTITY = value The variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other database systems. You can read its value with SELECT @@IDENTITY, and set it using SET IDENTITY. INSERT_ID = value Set the value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log. LAST_INSERT_ID = value Set the value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. Eg. mysql select @@last_insert_id; +--+ | @@last_insert_id | +--+ |0 | +--+ 1 row in set (0.04 sec) mysql select @@insert_id; +-+ | @@insert_id | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql select @@identity; ++ | @@identity | ++ | 0 | ++ 1 row in set (0.00 sec) mysql set @@last_insert_id = 5; Query OK, 0 rows affected (0.00 sec) mysql select @@last_insert_id; +--+ | @@last_insert_id | +--+ |5 | +--+ 1 row in set (0.00 sec) mysql select @@insert_id; +-+ | @@insert_id | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql select @@identity; ++ | @@identity | ++ | 5 | ++ 1 row in set (0.00 sec) So it appears you can use either of the three variables above to achieve the same effect. Regards Keith On Sat, 22 Apr 2006, Michael Stassen wrote: To: David T. Ashley [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] Subject: Re: How to Find Most Recent Autoincrement Index Assigned??? David T. Ashley wrote: I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, Dave. LAST_INSERT_ID() http://dev.mysql.com/doc/refman/4.1/en/information-functions.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.20a has been released
Hi, MySQL 5.0.20a, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the current production release family. It replaces 5.0.20, published last week. For the benefit of all those who did not download and install, I repeat the 5.0.20 news in this announcement, while mentioning the differences between 5.0.20 and 5.0.20a in a separate paragraph. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. This section documents all changes and bug fixes that have been applied since the last official MySQL release. If you would like to receive more fine-grained and personalised update alerts about fixes that are relevant to the version and features you use, please consider subscribing to MySQL Network (a commercial MySQL offering). For more details please see http://www.mysql.com/network/advisors.html. We welcome and appreciate your feedback! Changes from 5.0.20 to 5.0.20a: * The fix for Command line options are ignored for mysql client (Bug #16855 (http://bugs.mysql.com/16855)) has been revoked, because it had introduced an incompatible change in the way the command line client (mysql) selects the server to connect to. In the worst case, this might have led to a client issuing commands to a server for which they were not intended, and this must not happen. To help all users in understanding this subject, the manual now includes additional explanations in the section 4.2. Invoking MySQL Programs (http://dev.mysql.com/doc/refman/5.0/en/invoking-programs.html) * The code of the yaSSL library has been improved to avoid the dependency on a C++ runtime library, so a link with pure C applications is now possible on further (but not all) platforms. We are trying to fix the remaining issues. Additional information about SSL support: * With version 5.0.20a, SSL support is contained in all binaries for all Unix (including Linux) and Windows platforms except AIX, HP-UX, OpenServer 6, and the RPMs specific for RHAS3/RHAS4/SLES9 on Itanium CPUs (ia64); it is also not contained in those for Novell Netware. We are trying to add these platforms in future versions. * Please note that the original 5.0.20 announcement included inexact wording, I am sorry for that and hope it did not cause too many searches: SSL support is included in both server and client, but by default not enabled - this can be done by passing the SSL-related options (--ssl --ssl-key=... --ssl-cert=... --ssl-ca=...) when starting the server and the client or by setting these options in the config file. More information is in section 5.9.7. Using Secure Connections (http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html) of the manual. Functionality added or changed in 5.0.20, but missing in previous announcement: * Added the --sysdate-is-now option to mysqld to enable SYSDATE() to be treated as an alias for NOW(). See Section 12.5, Date and Time Functions. (Bug#15101 (http://bugs.mysql.com/15101)) * Large file support added to build for QNX platform. (Bug#17336 (http://bugs.mysql.com/17336)) * Large file support was re-enabled for the MySQL server binary for the AIX 5.2 platform. (Bug#13571 (http://bugs.mysql.com/13571), also listed as fixed Bug#10776 (http://bugs.mysql.com/10776)) Bugs fixed in 5.0.20, but missing in previous announcement: * If the WHERE condition of a query contained an OR-ed FALSE term, the set of tables whose rows cannot serve for null-complements in outer joins was determined incorrectly. This resulted in blocking possible conversions of outer joins into joins by the optimizer for such queries. (Bug#17164 (http://bugs.mysql.com/17164)) * mysql_config returned incorrect libraries on x86_64 systems. (Bug#13158 (http://bugs.mysql.com/13158)) * Stored routine names longer than 64 characters were silently truncated. Now the limit is properly enforced and an error occurs. (Bug#17015 (http://bugs.mysql.com/17015)) * During conversion from one character set to ucs2, multi-byte characters with no ucs2 equivalent were converted to multiple characters, rather than to 0x003F QUESTION MARK. (Bug#15375 (http://bugs.mysql.com/15375)) * The mysql_close() C API function leaked handles for shared-memory connections on Windows. (Bug#15846 (http://bugs.mysql.com/15846)) * If InnoDB ran out of buffer space for row locks and adaptive hashes, the server would crash. Now
Re: How to Find Most Recent Autoincrement Index Assigned???
Hi, Actually i update(increment by 1 ) a value in a table with only one row, ie. update table1 set col = col+1; Can i get the updated value without diong select in the same query with update. Because if i run Select after that than some time is lost and in the mean time any other process updates the table and i do not get the exact value. Thanks, Abhishek Jain On 4/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: From 'Section 13.5.3 SET Syntax' of the 5.0.18 ref manual: IDENTITY = value The variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other database systems. You can read its value with SELECT @@IDENTITY, and set it using SET IDENTITY. INSERT_ID = value Set the value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log. LAST_INSERT_ID = value Set the value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. Eg. mysql select @@last_insert_id; +--+ | @@last_insert_id | +--+ |0 | +--+ 1 row in set (0.04 sec) mysql select @@insert_id; +-+ | @@insert_id | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql select @@identity; ++ | @@identity | ++ | 0 | ++ 1 row in set (0.00 sec) mysql set @@last_insert_id = 5; Query OK, 0 rows affected (0.00 sec) mysql select @@last_insert_id; +--+ | @@last_insert_id | +--+ |5 | +--+ 1 row in set (0.00 sec) mysql select @@insert_id; +-+ | @@insert_id | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql select @@identity; ++ | @@identity | ++ | 5 | ++ 1 row in set (0.00 sec) So it appears you can use either of the three variables above to achieve the same effect. Regards Keith On Sat, 22 Apr 2006, Michael Stassen wrote: To: David T. Ashley [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] Subject: Re: How to Find Most Recent Autoincrement Index Assigned??? David T. Ashley wrote: I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, Dave. LAST_INSERT_ID() http://dev.mysql.com/doc/refman/4.1/en/information-functions.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.18-64 bit won't start after JS2E update on OS X
On 22 Apr 2006, at 06:30, Michael Stassen wrote: Are you saying the update changed the ownership, group, or permissions? I have had OS X updates change the permissions on / tmp 2 or 3 times in the past, but there was no change for me when I just installed JS2E 5.0 Release 4 update. Also, why was it necessary to remove and recreate the link? Couldn't you just reset whatever changed? It completely changed /tmp. It made /tmp a folder in it's own right, owned by geoffrey (me, admin) and staff. I've had it change the perms before, but never physically delete the link. - Geoffrey Sneddon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why does this query takes a lot of time
Dear Friends, I have two table joined by the followng query, the problem is this simple query takes a lot of time greater than 10 mins depending on the number of records, Pl. help me find out the reason: Table 1: id_key primary and auto increment recordID varchar(100) login_name varchar(255) blah blah Table 2: id_key primary and auto increment recordID varchar(100) blah blah Query: SELECT count( * ) FROM table1 s, table2 c WHERE s.login_name = 'abhishek' and s.recordID=c.recordID; recordID in table 1 needs to be same to that in table2.I mean recordID needs to be the mapping thing. Pl. help me it is important to me. Thanks, Abhishek Jain
Why does this query takes a lot of time
Dear Friends, I have two table joined by the followng query, the problem is this simple query takes a lot of time greater than 10 mins depending on the number of records, Pl. help me find out the reason: Table 1: id_key primary and auto increment recordID varchar(100) login_name varchar(255) blah blah Table 2: id_key primary and auto increment recordID varchar(100) blah blah Query: SELECT count( * ) FROM table1 s, table2 c WHERE s.login_name = 'abhishek' and s.recordID=c.recordID; recordID in table 1 needs to be same to that in table2.I mean recordID needs to be the mapping thing. Pl. help me it is important to me. Thanks, Abhishek Jain
please help, can not delete database
Hello, Not sure what is going on i have mysql Ver 12.22 Distrib 4.0.17, I have a database that i can not delete. i do this #mysqladmin drop billmax -u admin --password Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'billmax' database [y/N] y Database billmax dropped But it does not do anything. I have tried restarting the DB and restarting the box as well Please help Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE doesn'r works
- Original Message - From: Tom Lobato [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 22, 2006 4:03 AM Subject: Re: WHERE doesn'r works From: [EMAIL PROTECTED] you really need to show your php code and the output from an echo of the update line so that people can try to spot what you're doing wrong. from what i've seen, people are basically just guessing at the issue with you saying tried that, didn't work. updating mysql records via php code, with a where, works just fine. i.e., this isn't a php/mysql bug. rather there's something in your code that's not quite right. so, show your code, the table (definition and data) that you're trying to update, and output from appropriate echoing of statements and we can probably help you figure out your problem. Perfectly, I didnt show it before just for dont bore you with so many code =) The echo ouput: UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24' WHERE id = '5' Most of the time, when I see a column named 'id', it is defined as an integer. If _your_ 'id' column is defined as an integer, then the reason your WHERE clause is failing is very simple: Your WHERE clause is looking for all the rows where the 'id' value is a character-string containing '5', not the integer value 5. In other words, remove the apostrophes on either side of the 5 in the WHERE clause so that it says: WHERE id = 5 _not_ WHERE id = '5' and your WHERE clause will probably start working just fine. If your 'id' column is defined as a CHAR, VARCHAR or similar data type, then the apostrophes around the 5 are fine and there is some other problem. The php and db structure and data are attacheds. I don't see them in my copy of the email Two infos: 1) Im not a expert 2) The code is in the beggining of the development, so are too many uglyness yet =) Also, just a small note about English grammar since English doesn't appear to be your first language: contractions like 'didnt' and 'dont' should _always_ be spelled with apostrophes. In other words: use don't, not dont; use didn't, not didnt. Unfortunately, even some people who know only English are starting to spell contractions without the apostrophes but this is always wrong and makes the writer look illiterate. Obviously, we make allowances for those who are relatively new to English but I wanted you to know the right way to handle contractions. I assume you want to write English as well as you can so please don't copy the bad habits of English-speakers who don't have enough education or self-respect to spell their own language correctly. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help, can not delete database
What makes you think the delete of the database failed? It looks like the message from the DROP command indicates that the database was dropped successfully. -- Rhino - Original Message - From: Randy Paries [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 22, 2006 9:22 AM Subject: please help, can not delete database Hello, Not sure what is going on i have mysql Ver 12.22 Distrib 4.0.17, I have a database that i can not delete. i do this #mysqladmin drop billmax -u admin --password Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'billmax' database [y/N] y Database billmax dropped But it does not do anything. I have tried restarting the DB and restarting the box as well Please help Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
- Original Message - From: David T. Ashley [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 22, 2006 12:53 AM Subject: How to Find Most Recent Autoincrement Index Assigned??? I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, RTFM? If you search the MySQL manual on increment, you'll get several hits, one of which is 3.6.9 Using AUTO_INCREMENT. Here is the link: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html If you read that page, you should find your answer -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does this query takes a lot of time
2006/4/22, abhishek jain [EMAIL PROTECTED]: Dear Friends, I have two table joined by the followng query, the problem is this simple query takes a lot of time greater than 10 mins depending on the number of records, Pl. help me find out the reason: Table 1: id_key primary and auto increment recordID varchar(100) login_name varchar(255) blah blah Table 2: id_key primary and auto increment recordID varchar(100) blah blah Query: SELECT count( * ) FROM table1 s, table2 c WHERE s.login_name = 'abhishek' and s.recordID=c.recordID; alter table s add index(login_name); alter table c add index(recordID); Do you it recordID to be a varchar ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does this query takes a lot of time
On Apr 22, Philippe Poelvoorde wrote: alter table s add index(login_name); alter table c add index(recordID); To make this much faster, I think you may want: alter table s add index(recordID, login_name); alter table c add index(recordID); Because after the join, the engine can use the two-key index to filter the results. I may be wrong about this though -- I haven't tried it. A 2006/4/22, abhishek jain [EMAIL PROTECTED]: Dear Friends, I have two table joined by the followng query, the problem is this simple query takes a lot of time greater than 10 mins depending on the number of records, Pl. help me find out the reason: Table 1: id_key primary and auto increment recordID varchar(100) login_name varchar(255) blah blah Table 2: id_key primary and auto increment recordID varchar(100) blah blah Query: SELECT count( * ) FROM table1 s, table2 c WHERE s.login_name = 'abhishek' and s.recordID=c.recordID; Do you it recordID to be a varchar ? -- 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]
Fw: please help, can not delete database
I'm putting this back on the list where it belongs; that enables everyone to help and to learn from the discussion, either now or in the future via the list archive. -- Ahh, so you've tried to re-create the database after it appeared to be safely dropped! You didn't say that in your note so I wanted to be sure you had done that much before writing the note. Have you looked in the MySQL log to see if it is reporting any problems with the DROP? If you are using INNODB, have you tried a SHOW STATUS INNODB to see if it reported any problems? -- Rhino - Original Message - From: Randy Paries [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Sent: Saturday, April 22, 2006 11:04 AM Subject: Re: please help, can not delete database when i go back and try to create it , it says it already exisit? Randy On 4/22/06, Rhino [EMAIL PROTECTED] wrote: What makes you think the delete of the database failed? It looks like the message from the DROP command indicates that the database was dropped successfully. -- Rhino - Original Message - From: Randy Paries [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 22, 2006 9:22 AM Subject: please help, can not delete database Hello, Not sure what is going on i have mysql Ver 12.22 Distrib 4.0.17, I have a database that i can not delete. i do this #mysqladmin drop billmax -u admin --password Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'billmax' database [y/N] y Database billmax dropped But it does not do anything. I have tried restarting the DB and restarting the box as well Please help Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Locking Question
Hi, I'm doing a PHP application, and there are just a few instances where I need to do atomic operations on more than one table at a time and I can't express what I want to do as a single SQL statement. What I'm trying to guard against, naturally, is race conditions when more than one process is making modifications at a time, i.e. multiple simultaneous page hits. Because my application is so non-demanding in terms of server resources, what I decided to do is use only one simple locking schema throughout the code that locks every table at the same time, i.e. -- LOCK TABLE widgets WRITE, thingamabobs WRITE ... Make multiple interrelated table changes. UNLOCK TABLES -- In my estimation, what this should do is cause every other process to sleep briefly until the first one is through to the UNLOCK TABLES. I can't see going to a more complex locking model with such a simple application. Will the lock every table approach work as I intend? Anything I should watch out for? Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Locking Question
David T. Ashley wrote: Hi, I'm doing a PHP application, and there are just a few instances where I need to do atomic operations on more than one table at a time and I can't express what I want to do as a single SQL statement. What I'm trying to guard against, naturally, is race conditions when more than one process is making modifications at a time, i.e. multiple simultaneous page hits. LOCK TABLE widgets WRITE, thingamabobs WRITE ... Make multiple interrelated table changes. UNLOCK TABLES -- In my estimation, what this should do is cause every other process to sleep briefly until the first one is through to the UNLOCK TABLES. I can't see going to a more complex locking model with such a simple application. Will the lock every table approach work as I intend? Yes, it will work as you suggest: It lock your database solid, and aim the programming rifle squarely at both feet in readiness for the fateful day which your client doesn't complete as quickly as you've estimated. Anything I should watch out for? Your entire database freezing solid when the client fails to terminate. If your using persistent connections with a mod_php webserver and the script aborts without relasing the lock mod_php will kindly hold the mysql connection with the lock open for you. If you can't or won't do this properly by using a transactional table and begin/commit at least look at using get_lock() based guard conditions which only lock a string leaving the database accessable. Whatever you do if you client is php install a shutdown handler to clean up any locks. HTH Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help, can not delete database
Randy Paries wrote: Hello, Not sure what is going on i have mysql Ver 12.22 Distrib 4.0.17, I have a database that i can not delete. i do this #mysqladmin drop billmax -u admin --password Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'billmax' database [y/N] y Database billmax dropped But it does not do anything. I have tried restarting the DB and restarting the box as well Please help Randy Remember that MySQL uses directories to implement databases. When you drop a database, it deletes the table files and then tries to delete the directory. If the directory remains, even if it is empty, it still looks like a databbase to MySQL. So check the directory that corresponds to this troublesome database for unusual permissions or content (such as some files other than MySQL's table files) that may be preventing the deletion of the directory. (Check /etc/my.cnf for the location of your database directories. RedHat usually puts them in /var/lib/mysql.) Let us know if that was the problem. --John PS Remember to reply to the list :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Locking Question
Nigel wrote: If you can't or won't do this properly by using a transactional table and begin/commit at least look at using get_lock() based guard conditions which only lock a string leaving the database accessable. Whatever you do if you client is php install a shutdown handler to clean up any locks. Hi Nigel, Just one question: the documentation of get_lock() says that the lock is freed when a process terminates. Does this also occur with mod_php and persistent connections if a PHP script dies (say, due to fatal error or CPU limit timeout)? It isn't clear how mod_php works and why with persistent connections the LOCK TABLES locks wouldn't be freed but the get_lock() lock would. You were suggesting that I replace the LOCK/UNLOCK TABLES critical section with one implemented using get_lock()??? Or maybe you are suggesting something else ... Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE doesn'r works
From: Rhino [EMAIL PROTECTED] Most of the time, when I see a column named 'id', it is defined as an integer. If _your_ 'id' column is defined as an integer, then the reason your WHERE clause is failing is very simple: Your WHERE clause is looking for all the rows where the 'id' value is a character-string containing '5', not the integer value 5. In other words, remove the apostrophes on either side of the 5 in the WHERE clause so that it says: WHERE id = 5 _not_ WHERE id = '5' I tried this, but didn't work yet. I tried double, simple and no quotes, same problem: WHERE only works when executed directly in the mysql client, no from mysql api of the php. See the table creation: CREATE TABLE `clientes` ( `id` int(5) NOT NULL auto_increment, `tipo` char(1) default NULL, `razao_social` varchar(30) default NULL, and your WHERE clause will probably start working just fine. If your 'id' column is defined as a CHAR, VARCHAR or similar data type, then the apostrophes around the 5 are fine and there is some other problem. The php and db structure and data are attacheds. I don't see them in my copy of the email Also, you can see the codes in... http://www.spalha.com.br/spalha/DB_code.html http://www.spalha.com.br/spalha/insert_client_code.html (thanks to GESHI project, http://qbnz.com/highlighter/index.php =) Two infos: 1) Im not a expert 2) The code is in the beggining of the development, so are too many uglyness yet =) Also, just a small note about English grammar since English doesn't appear to be your first language: contractions like 'didnt' and 'dont' should _always_ be spelled with apostrophes. In other words: use don't, not dont; use didn't, not didnt. Unfortunately, even some people who know only English are starting to spell contractions without the apostrophes but this is always wrong and makes the writer look illiterate. Obviously, we make allowances for those who are relatively new to English but I wanted you to know the right way to handle contractions. I assume you want to write English as well as you can so please don't copy the bad habits of English-speakers who don't have enough education or self-respect to spell their own language correctly. So I will have begin to pay the list =) Beyond mysql I learn English too? Well, thank you by the hint, I'll stay alive about this. Thank you Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Locking Question
David T. Ashley wrote: Nigel wrote: If you can't or won't do this properly by using a transactional table and begin/commit at least look at using get_lock() based guard conditions which only lock a string leaving the database accessable. Whatever you do if you client is php install a shutdown handler to clean up any locks. Hi Nigel, Just one question: the documentation of get_lock() says that the lock is freed when a process terminates. Does this also occur with mod_php and persistent connections if a PHP script dies (say, due to fatal error or CPU limit timeout)? It isn't clear how mod_php works and why with persistent connections the LOCK TABLES locks wouldn't be freed but the get_lock() lock would. You were suggesting that I replace the LOCK/UNLOCK TABLES critical section with one implemented using get_lock()??? Or maybe you are suggesting something else ... Hi Dave, mod_php will persist the MySQL connection holding open any lock or syncronisation token obtained through any of the three methods : begin/commit, lock/unlock tables or get_lock/release_lock. PHP does ensure that even in the event of timeouts or fatal errors any shutdown handlers registered are still executed so it is possible to clean up properly whichever method is used. http://uk.php.net/manual/en/function.register-shutdown-function.php If you use php's pdo with transactions it perform a rollback for you on abort or completion. Database locking always comes down the safety vs concurrency. In a multi-user enviroment you want to lock as few rows as possible during your critical section. Innodb transactions are the only way to ensure correctness in a multi-statement update, but if you can't use transactions your goal is to find the smallest impact scheme which is still safe. I've used get_lock() string locks in the past to simulate row level locks without transactions but it's only safe in certain update schemes, if all your developers use the scheme consistently and never safe if your application isn't the only thing which modifies the rows of the tables/fields which need protection. Whether its a smart thing to do depends on your situation. HTH Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE doesn'r works
- Original Message - From: Tom Lobato [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 22, 2006 4:18 PM Subject: Re: WHERE doesn'r works From: Rhino [EMAIL PROTECTED] Most of the time, when I see a column named 'id', it is defined as an integer. If _your_ 'id' column is defined as an integer, then the reason your WHERE clause is failing is very simple: Your WHERE clause is looking for all the rows where the 'id' value is a character-string containing '5', not the integer value 5. In other words, remove the apostrophes on either side of the 5 in the WHERE clause so that it says: WHERE id = 5 _not_ WHERE id = '5' I tried this, but didn't work yet. I tried double, simple and no quotes, same problem: WHERE only works when executed directly in the mysql client, no from mysql api of the php. See the table creation: CREATE TABLE `clientes` ( `id` int(5) NOT NULL auto_increment, `tipo` char(1) default NULL, `razao_social` varchar(30) default NULL, Since your table definition says that 'id' is definitely an int, WHERE id = 5 _should_ work. But obviously, it doesn't. I'm not sure what to try next. It's possible that php is messing you up somehow but I don't know how to be sure; I don't know php so I don't know the problems that you can encounter with it. There is one thing slightly odd about your table definition: you have defined 'id' as int(5). Normally, I define a column like 'id' as int, not int(5). According to the manual, it is okay for you to have int(5) but I wonder if your problem is caused by the int(5)? Perhaps you could try changing the column definition from int(5) to int and see if the php code works after that? It shouldn't make any difference but you never know: perhaps this will solve the problem. If that doesn't work, you could try searching for bug reports involving int (or int(5)) columns; perhaps this is a known bug? If you don't find anything in the bug reports, perhaps you are the first to find this problem; in that case, you could create a new bug report. Maybe someone will be able to suggest a workaround. I'd be surprised if this is a bug though; it seems like very basic functionality that should have been debugged a long time ago. and your WHERE clause will probably start working just fine. If your 'id' column is defined as a CHAR, VARCHAR or similar data type, then the apostrophes around the 5 are fine and there is some other problem. The php and db structure and data are attacheds. I don't see them in my copy of the email Also, you can see the codes in... http://www.spalha.com.br/spalha/DB_code.html http://www.spalha.com.br/spalha/insert_client_code.html (thanks to GESHI project, http://qbnz.com/highlighter/index.php =) Two infos: 1) Im not a expert 2) The code is in the beggining of the development, so are too many uglyness yet =) Also, just a small note about English grammar since English doesn't appear to be your first language: contractions like 'didnt' and 'dont' should _always_ be spelled with apostrophes. In other words: use don't, not dont; use didn't, not didnt. Unfortunately, even some people who know only English are starting to spell contractions without the apostrophes but this is always wrong and makes the writer look illiterate. Obviously, we make allowances for those who are relatively new to English but I wanted you to know the right way to handle contractions. I assume you want to write English as well as you can so please don't copy the bad habits of English-speakers who don't have enough education or self-respect to spell their own language correctly. So I will have begin to pay the list =) Beyond mysql I learn English too? Well, thank you by the hint, I'll stay alive about this. No charge for the English pointers :-) I just want you to know the correct way to write things. I hope you'd do the same for me if I was trying to write Portuguese and made a consistent mistake :-) -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE doesn't work
Tom, UPDATE command is not working for only the record that I want, but for all in the table. I sees WHERE is not interpretated by Mysql server. At least the result is identic to it. For example, If my php execute UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24' WHERE id = '5' With a previous message you posted attachments which turned out _not_ to be the PHP code which you say is not working. If you want help with this problem, you will have to post the minimal table data and PHP code needed to demonstrate your difficulty. Otherwise this discussion appears to be going nowhere. PB - Tom Lobato wrote: Hi! UPDATE command is not working for only the record that I want, but for all in the table. I sees WHERE is not interpretated by Mysql server. At least the result is identic to it. For example, If my php execute UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24' WHERE id = '5' on the server, I have all records in the table clientes updated for these values. But, if I copy exactily this command from php output and execute in the Mysql server directly, via some mysql client, it updates only record whose id = '5'. PHP Version: 4.4.2, Mysql client API version: 3.23.49. For windows servers or Linux. Some Hint? Thank you Tom -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 4/21/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE doesn't work
From: Peter Brawley [EMAIL PROTECTED]
Tom,
UPDATE command is not working for only the record that I want, but for
all in the table. I sees WHERE is not interpretated by Mysql server. At
least the result is identic to it. For example, If my php execute
UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24'
WHERE id = '5'
With a previous message you posted attachments which turned out _not_ to
be the PHP code which you say is not working.
Maybe a comunication trouble... Let me confirm. Are you saying that
http://www.spalha.com.br/spalha/DB_code.html
http://www.spalha.com.br/spalha/insert_client_code.html
are not the code Im having troubles? As the code Im having trouble were some
secret or so?
if ($yes){
$communication_trouble = true;
echo segmentation fault =);
}else
echo my english is very bad =);
If you want help with this problem, you will have to post the minimal
table data and PHP code needed to demonstrate your difficulty. Otherwise
this discussion appears to be going nowhere.
php code is http://www.spalha.com.br/spalha/insert_client_code.html and DB
is the http://www.spalha.com.br/spalha/DB_code.html,
table data especifcly are lines 46 and 47 of the last link, enough for test
the errors. Please, if you want, see also the form attached, whose 'action'
call insert_client.php.
PB
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
