Re: EXPLAIN: Select tables optimized away
Hi Counts are extremely fast and since you have no WHERE statement, it takes the count value straight from the internals and does not look at the tables or an index if I remember correctly. Mike - Original Message - From: Gabriel PREDA [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: February 17, 2005 6:15 AM Subject: EXPLAIN: Select tables optimized away MySQL 4.1.10 What does Select tables optimized away mean ? mysql explain SELECT COUNT(*) AS total FROM members_twining_main; | 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL | NULL | Select tables optimized away | Gabriel PREDA -- 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 Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 2005-02-14 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Perl
Hi Gerald
Looks like you need a username Jerry not gjw, here is the format I use
to connect. Mike
$dsn = DBI:mysql:host=$host_name;database=$db_name
return (DBI-connect ($dsn, $username,$password,
{PrintError = 0, RaiseError = 1}));
- Original Message -
From: Gerald Preston [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: January 14, 2005 3:55 AM
Subject: MySQL Perl
I am trying to get Perl to talk to MySQL that I have setup. According to
WinMySQLAdmin1.4
Local Host Name = GJW
Local User name = Jerry
Databases:
GJW
Club
bar
mysql
test
I am using the following code:
#!/perl
use warnings;
use strict;
use DBI;
my $dbh=DBI-connect( 'DBI:mysql:GJW:club:bar', 'gjw', 'password' ) or
die
Cannot connect - gjw!!br$DBI::errstr;
What am I missing?
Thanks,
Jerry
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.10 - Release Date: 2005-01-10
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.10 - Release Date: 2005-01-10
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Bulk] Re: MySQL speed
You have not mentioned the use of a multiple column index, for that matter, no indexes at all. I would suggest a multiple column index in the form of hidden, touser. That index form should speed up your queries and solve the problem. Mike - Original Message - From: Fagyal Csongor [EMAIL PROTECTED] To: Dirk Schippers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: September 14, 2004 12:24 PM Subject: [Bulk] Re: MySQL speed Dirk, Hello, For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. More information: table messages is an MyISAM table of 48MB which has 67000 rows, a primary key on id, a key on touser and a key on isread with the following fields: id - bigint(20) autoincrement, fromuser - int(10) unsigned, touser - int(10) unsigned, ts - datetime, message - text, isread - tinyint(1) unsigned, ipnumber - varchar(20), hidden - tinyint(1). I experience this problem also with other tables and queries but as this is the most simple one, I show this one as an example. So: is it normal that the difference is so big, and that tables which are not so very big (I think 67000 rows, or 48MB is not that huge for a good database) are so hard to find rows into, keeping in mind that the key makes the query only walk trough 2500 rows? Or is this normal? Just some ideas for you. 1: I think you should use CHAR instead of VARCHAR. Might take more place, but using fixed length fields in general mean faster access time. 2: To walk through 2500 rows might take some time, as the hidden fields for each rows must be fetched (IMHO). 3: You should use query caching. That might speed up your query tremendously. 4: You can try a hack: instead of using a field do denote that a user is not hidden, you might want to consider using some other method, e.g. using a signed integer for userid, and storing inactive users with a negative userid. Then you could count on users where userid 0. Regards, - Csongor Fagyal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql /dbi issues
Hi When building my server I came across a similar situation. The .10 and .14 are builds of the same library but are complied using a different C library when building the kernel. If your kernel was built and needs .14 you should be able to find builds for your kernel. In the end, I tried upgrading my C libraries and killed my machine (Linux) and then had to re-install the operating system. If you cannot find the rpm for it, 'making' it from the binaries is pretty easy. Mike - Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: July 23, 2004 7:31 PM Subject: mysql /dbi issues can anyone tell me why when i build/use perl/dbi it crashes because it's looking for libmysqlclient.so.10. i'm using perl 5.8.3 and mysql4.1.3. i've gotten the latest DBI (1.43). why does DBI look for libmysqlclient.so.10, instead of using libmysqlclient.so.14. is there anyway to force a change, and if i can't, then how can i rebuild libmysqlclient.so.10 such that it uses my version of openssl. i can't simply use the default rpm fom mysql thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: oracledump.pl error
Hi Maybe I am missing something. Why not install MySQL on a windows box and use one of those porters. Then just copy your tables to the Linux box. Mike - Original Message - From: J. Allen Crider [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: February 19, 2004 3:37 AM Subject: Re: oracledump.pl error Obviously I wrote my original message too quickly and did not make myself clear. I am well aware that DB2 and numerous other commercial products work on Linux. What I meant to say was that, of all of the products that I could find listed on www.mysql.com for converting data from Oracle to MySQL, all of the commercial products except SQLPorter are written specifically for MS Windows and do not have Linux versions. That leaves the most obvious options for converting the data in my Oracle database to MySQL being oracledump.pl or rolling my own solution. If there are other tools that will do the job easily, either open source or low-cost commercial, I would like to hear about them. Rhino wrote: I don't have any insight into your problem but I want to take exception with your assertion that no other commercial products work on Linux. In fact, DB2 works on Linux and has for some time. Here's a link to information about this product: http://www-306.ibm.com/software/data/db2/udb/ If you nose around the IBM site a bit, you should be able to find a free full-function copy of DB2 for Linux. (By free, I mean you don't need a licence for it unless you use it in production. This enables you to get very acquainted with DB2 for free and only buy a licence if you like it enough to use it in production.) Rhino - Original Message - From: J. Allen Crider [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 10:54 PM Subject: oracledump.pl error I have just decided to try to learn something about MySQL after several years of working with Oracle and wanted to transfer the data I have in an Oracle 9i database to a new MySQL database. Since this is strictly for personal use, I can't justify the cost of SQLPorter, and none of the other commercial products work on Linux, so I decided to try oracledump.pl. However, I am getting the following error when I attempt to run it: DBD::Oracle::st execute failed: ORA-01459: invalid length for variable character string (DBD ERROR: OCIStmtExecute) [for statement ``SELECT a.COLUMN_ID, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.DATA_DEFAULT, b.COMMENTS FROM USER_TAB_COLUMNS a, USER_COL_COMMENTS b WHERE a.TABLE_NAME = ? AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME'' with params: :p1='ALBUMS']) at ./oracledump.pl line 471. (followed by several other errors obviously caused by this error.) Running the same statement in sqlplus works fine. I am running Oracle 9i Release 2 on Gentoo Linux. Any ideas on what I'm doing wrong? Allen Crider Huntsville, AL [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP or Perl?
Hi Mark I can only tell you about my experience from the Perl side of things. The integration to the database is great and the amount of things you can do with the data once you get it in the script is the biggest bonus. My feeling is that a combination of the two would probably be the best. Any pages that just list your records, I would build in PHP. Any that involve handling the data before output, I would use Perl. Mike - Original Message - From: Mark Healey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: August 16, 2003 1:17 AM Subject: PHP or Perl? As part of my own learning mysql project I'm planning to build databases for all my books and DVD's. Stephen Hawking is probably a better typist than I am so I plan to use barcodes to get the info ,taking the information from various web sites. People tell me that PHP is THE way to do database work with mysql. The thing is, I'm familiar with Perl and it has all kinds of neat string manipulation stuff and LWP. Does PHP have comprable libraries (especially LWP) and how difficult is it to move from Perl. I hope I don't start a relegious war here I just want some advice. Mark Healey [EMAIL PROTECTED] This account is only for lists to which I've subscribed. Any spammers invite the worst revenge I think I can get away with. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connection
Hi Sounds like you need to update your path in your shell profile. Check out the file dot file in your home directory and add the /usr/local/mysql/bin directory to it. Mike - Original Message - From: aaldrik groenewold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: August 6, 2003 7:12 PM Subject: connection I just installed mysql 4.0 .There were many problems with upgrading from 3.23 to 4.0 so I deleted 3.23 and made a fresh start with 4.0. Everything works fine now but the only problem to connect to the server is via the /usr/local/mysql/bin directory. Is there a way to connect if I am root. thanks for your help. -aaldrik __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running two Servers?
Hi Why would you need two servers running when you could just have separate databases?? Is there a reason why these two cannot share?? Mike - Original Message - From: Ola Ogunneye [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: August 7, 2003 8:46 AM Subject: Running two Servers? I am looking into installing 2 mysql servers on the same windows 2000 PC. From what I have found, it is usually possible to have 2 different versions on the same machine. (Usually one for testing and the other for production). However, I am trying to run 2 production servers, one for personal family info, and the other for business opportunity. Being that I just started using mysql, is it possible then to have the same version installed twice, or how do I accomplish my task? Thanks all Ola -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ARGGH! - User password problems
Hi
Did you remember to flush???
- Original Message -
From: Adam Fortuno KOVICK [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: July 31, 2003 4:33 PM
Subject: ARGGH! - User password problems
All,
I've been attempting to assign a password to a user with the following
statement:
UPDATE mysql.user SET password=PASSWORD('foo') WHERE user = 'acc_name';
I get the typical:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
However, when the user attempts the login using the password they are
unable (access is denied to acc_name with password yes). If they
attempt to login without a password, they are successful - WTF!.
There is only 1-entry in the user table for this user so I didn't
specify a host.
Next, for the same user account, if I try to login from a remote
machine (via ODBC) I am denied access (event with the blank password!).
The user has the following host '%' (no other hosts present), yet I
receive an error that reads:
Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
HELP before I lose my mind and start pretending I am a glass of orange
juice.
Regards,
Adam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Books advice
Hi I guess I will plug Paul's books. I have his MySQL book and his MySQL and Perl for the Web. I really enjoyed the second one but the MySQL book was very well written. I also have read Sam's teach yourself MySQL in 21 days which seemed pretty light weight and I never look up anything in it. I also have the MySQL reference manual which is great for really technical type questions. Mike - Original Message - From: Ralph Guzman [EMAIL PROTECTED] To: 'Fawad Siddiqui' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: July 31, 2003 8:23 PM Subject: RE: Books advice Here are two other books that I would recommend, specially the first one: * SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL by Michael J. Hernandez, John L. Viescas Joe Celko's SQL for Smarties: Advanced SQL Programming by Joe Celko -Original Message- From: Fawad Siddiqui [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 5:06 PM To: [EMAIL PROTECTED] Subject: Books advice Hi, I would like to learn about RDBMS, namely mysql of course, but know really nothing in this area, so have to learn about; RDBMS, SQL and mysql from scratch. In this regard, if anyone knows of any books they think would start me off on the right foot, I would be very grateful. I have done some searching on Amazon, with the following results. 1.Beginning Databases with MySQL by Richard Stones, Neil Matthew 2.MySQL Cookbook by Paul DuBois 3.Managing and Using MySQL by George Reese, et al 4.Inside Relational Databases by Mark Whitehorn, Bill Marklyn 5.Database Design by Ryan K. Stephens, Ronald R. Plew 6.The Practical SQL Handbook: Using SQL Variants by Judith S. Bowman, et al Many thanks in advance for all your help. Fawad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT SELF Join -- LEFT join on same table
Hi Let me take a stab at it. I don't think you need a join at all. Check the syntax but I think you can do it like this. SELECT *,concat(slm,item,cust,year) as result1,concat(slm,item,cust,2003) as result2 from sales where result1!=result2 AND date2001 year would be taken from your date field in the table Mike - Original Message - From: Charles Haven [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: July 14, 2003 11:29 AM Subject: LEFT SELF Join -- LEFT join on same table Hello all, Is it possible to LEFT JOIN a table to itself? I want to see all records in a table where the year of the sales date is 2003 and where the salesman sold an item to a customer to which he did not sell that item to in 2002. The following is an example table (SALES): ++-+--+++--+-+ | ID | SLM | CUST | DATE | ITEM | COST | QTY | ++-+--+++--+-+ | 1 | 1 | AAA | 2002-01-01 | APPLE | 1.00 | 2 | | 2 | 1 | BBB | 2002-01-01 | APPLE | 1.00 | 2 | | 3 | 1 | CCC | 2002-01-01 | PEAR | 1.00 | 2 | | 4 | 1 | AAA | 2002-01-01 | PEAR | 1.00 | 2 | | 5 | 2 | AAA | 2002-01-01 | APPLE | 1.00 | 2 | | 6 | 3 | CCC | 2002-01-01 | BANANA | 1.00 | 2 | | 7 | 1 | AAA | 2003-01-01 | APPLE | 1.00 | 2 | | 8 | 2 | AAA | 2003-01-01 | APPLE | 1.00 | 2 | | 9 | 3 | CCC | 2003-01-01 | BANANA | 1.00 | 2 | | 10 | 1 | AAA | 2003-01-01 | BANANA | 1.00 | 2 | | 11 | 3 | CCC | 2003-01-01 | APPLE | 1.00 | 2 | ++-+--+++--+-+ What I want to see in my result is that in 2003 Salesman 1 sold bananas to customer AAA and salesman 3 sold apples to customer CCC. I want to see these because these salesmen did not sell these customers these items in 2002. Thanks, Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MATCH AGAINST () ('XXX' IN BOOLEAN MODE) SYNTEX ERROR
Hi
Your first statement looks ok to me but the one you have below has no
space before AGAINST. Otherwise, I cannot see the problem myself. Mike
- Original Message -
From: Francis Van-Lare [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: July 12, 2003 2:42 AM
Subject: MATCH AGAINST () ('XXX' IN BOOLEAN MODE) SYNTEX ERROR
I have run this command without Stating IN BOOLEAN MODE and my search goes
fine. However this query returns a Syntax error WHEN IN BOOLEAN MODE
OPTION IS ADDED , can anyone help ?
Table is MyISAM type running on Linux 8.0
SELECT isbn, title, suggestedretail FROM bookscatalogue WHERE MATCH
(isbn, title ) AGAINST ('+Visual Basic.NET' IN BOOLEAN MODE);
Returns:
Error
SQL-query :
SELECT isbn, title, suggestedretail
FROM bookscatalogue WHERE MATCH (isbn, title )AGAINST ('+Visual Basic.NET'
IN BOOLEAN MODE )
MySQL said:
You have an error in your SQL syntax near 'BOOLEAN MODE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Hi Just read this last night. Try mysqldump databasename -u username --password=yourpassword /to/a/directory/dump.sql Mike - Original Message - From: Kalle Saarinen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: July 7, 2003 8:21 AM Subject: mysqldump Hello I'm trying to make mysqldump in a shell script but I can't deliver password to sql server. I have command in the script: mysqldump databasename -u username -p password /to/a/directory/dump.sql When i run the script it asks for a password and takes the password given in the script for a table name. How can I give the password in the script??? mysqldump --help wasn't wery helpful... -Kalle Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big problem with autoincrementing
Hi First, auto increment is a column type. You set the column to add one to the highest value in that column. If you would like the records to start at 10, either enter you first record with the Client_ID value explicitly set at 10 or enter a dummy MySQL statement like INSERT INTO Clients Client_ID VALUES (9) then the next Full_Name you enter, the Client_ID will be automatically set at 10, then next 11 and so on. Mike - Original Message - From: Fandango del Morro [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: June 22, 2003 3:35 AM Subject: Big problem with autoincrementing Hello, I'm new in this list and I also a newbie with MySql. I have a big problem with autoincrementing with an initial value. For example: use Database Business; create Table Clients(Client_ID int not null primary key auto_increment=10, Full_Name varchar(30)); Each time I assign an initial value to auto_increment I get an error and worse, the MySql server only tell me there is an error, it doesn't tell me what kind of error is. I tried at leat 30 times before writing this message. I really unable to solve this problem. auto_increment=n is an accepted syntax and it is documented in all sql books. Can you explain me why I have this problem? Thanks Fandango _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't drop a database
Hi I really don't have an answer but would question whether MySQL will allow zero db's with it's own grant tables. Usually there is a test db, at least after an install. Why not try adding a database and then try to drop clone_updater??. Mike - Original Message - From: Leo Genyuk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: June 19, 2003 10:06 AM Subject: Can't drop a database Hello. I am running MySQL database on Sun Solaris 9. # mysql -V mysql Ver 11.18 Distrib 3.23.54, for sun-solaris2.8 (sparc) I am having a problem dropping a database. All the tables in the database have been dropped successfully, but the database does not want to go away. To be more specific, when I execute: drop database clone_updater; query runs without errors, but database is still there. mysql show databases; +--+ | Database| +--+ | clone_updater| | mysql | +--+ 51 rows in set (0.01 sec) mysql drop database clone_updater; Query OK, 0 rows affected (0.00 sec) mysql show databases; +--+ | Database| +--+ | clone_updater| | mysql | +--+ 51 rows in set (0.01 sec) Restarting mysql had no effect on this issue. Can anyone please help? Thank you in advance, Leo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql: bug in update (?)
Hi Your problem lies in your columns. A timestamp field has the special property of recording when a record is created or modified. However, only the first timestamp column in a row is treated this way. I hope this helps. Mike - Original Message - From: Dmitry Kosoy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: June 8, 2003 7:51 AM Subject: mysql: bug in update (?) Hi, I have a table with 2 fields (among others) with type timestamp (warndate and date). I run the following update: update dbowner where warndate = now(). This update affects not only on the field warndate but on the field date as well. After it the both fields got the current date and time value. I changed the name of field date to another and got the same result. I got the same behavior in many mysql versions include 4.0.12. Regards, Dmitry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I thought single UPDATE statements were atomic
Hi
You are not defining num so you are adding 1 to 0 to get 1 and you
already have that record. Sounds like you need an autoincrement field.
Mike
- Original Message -
From: Mark Rages [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: June 5, 2003 7:52 PM
Subject: I thought single UPDATE statements were atomic
According to the docs, single update statements are atomic.
So why doesn't this work?
mysql create table t (num INT, UNIQUE (num));
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('1');
Query OK, 1 row affected (0.00 sec)
mysql insert into t values ('2');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+--+
| num |
+--+
|1 |
|2 |
+--+
2 rows in set (0.00 sec)
mysql update t set num=num+1;
ERROR 1062: Duplicate entry '2' for key 1
mysql
Help!
Regards,
Mark
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rating rows only with FULLTEXT
Hi I would like to use a FULLTEXT index to rate each row without comparison to the other rows in the dataset. I am retrieving documents from remote databases and would like to limit the responses from the database before an external script rates them against each other. I was wondering if something might be done in the myisam/ftdefs.h source file to remove the dataset weighting feature. Thanks Mike O'Krongli O'K Web Design -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
