mySQL on Windows CE
Hi, Is this supported? I mean I just look at the FAQ and find it is on the list of supported platform (Handheld: Windows CE), but I hardly see any article mention about it. I have downloaded two connector (.NET and ODBC), but which one should I use for Windows CE development? Could someone help clarify? For Embedded Visual C++ 4.0, what connector should I use and does it supported? Or, if I go for .NET Compact Framework with Visual Studio .NET 2003, should I use .NET connector? Or ODBC? I have seen previously someone said connector for .NET Compact Framework is not yet out, but today I see handheld Windows CE on the list of supported platform by mySQL, and I am almost confused. Khan Ming -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repair table priv
up Bgs wrote: Does this silence mean that nobody knows?!? :) I've been trying to find what privilege is needed to 'REPAIR TABLE'. I couldn't find any usefull hint on the net or in the archives. Could anyone help me out? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repair table priv
I think you need to have root privileges (ALL) on the table to run REPAIR TABLE since this is a disaster recovery sql command. It works only for MyISAM tables in MySQL. I don't think it is supported for all tables. Logically a repair table command would update, insert, delete and alter the tables: so a superset of these privileges should be available. -Goutham S Mohan --- Bgs [EMAIL PROTECTED] wrote: up Bgs wrote: Does this silence mean that nobody knows?!? :) I've been trying to find what privilege is needed to 'REPAIR TABLE'. I couldn't find any usefull hint on the net or in the archives. Could anyone help me out? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Send a seasonal email greeting and help others. Do good. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repair table priv
Hi, I think you need to have root privileges (ALL) on the table to run REPAIR TABLE since this is a disaster recovery sql command. It works only for MyISAM tables in MySQL. I don't think it is supported for all tables. I need it because I got a server with big load that sometimes has db problems. The server is a big mess but I cannot replace it for now. So I set up a script that removes unneded stuff every few minutes but I also want to do a repair table from that script. The script uses a restricted user (resztricted to only delete from a ceratin db) and I wanted to avoid giving to many rights to that user. I started to add rights but it didn't work. The user almost got all rights by now but it still doesn't work. The server is going to be shut down soon at last, but I will experiment with this and send the results to the list later for others to know. Thanks Zoltan Logically a repair table command would update, insert, delete and alter the tables: so a superset of these privileges should be available. -Goutham S Mohan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and x86-64
Hi! It seems that I need to buy a 64bit server and have some questions regarding the two architectures from AMD (Opteron) and Intel (Xeon with EM64T). Am I right if I say an Opteron based server is the better choice for MySQL because of the more advanced and faster bus system? Is there anywhere a good benchmark out there where I could see the performance differences between the different architectures (PIII, P4, Xeon, Opteron etc.) when running MySQL? How much memory could MySQL use on a 64 bit system running a 32 bit Linux distribution? Can I run MySQL in 64 bit mode? Thank you for your help! Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and x86-64
Hey, 1. We just installed a very large MySQL installation on a dual opteron 2ghz system with 16GB of memory and it -flies-. That's pretty subjective, so check: http://www6.tomshardware.com/cpu/20030422/opteron-17.html http://www.infoworld.com/article/04/09/17/38FE64shootout2_1.html Definately, go Opteron. 2. A 32-bit Linux distribution (ie, 32-bit kernel) is pretty much like running an Athlon XP processor (save for the perfformance increases of the Opteron chip). You don't get any 64-bit advantages. You definitely need a 64-bit kernel and 64-bit libraries to take advantage of the Opteron processor. That said, 64-bit kernels come with 32-bit libraries usually, so you don't lose backwards compatibility, but simply gain 64-bit computing. Also, generally, 64-bit binaries compiled specifically for the opteron are faster than 32-bit code executed on the same opteron CPU. 64-bit Linux is pretty much everywhere. SuSE and RedHat ship a stable 64-bit distribution, though Debian 3.1 works great as well (i personally use Debian). Once you have a 64-bit system setup, skies the limit, RAM wise. Hope it helps, Dylan On 15-Dec-04, at 5:54 AM, Andreas Ahlenstorf wrote: Hi! It seems that I need to buy a 64bit server and have some questions regarding the two architectures from AMD (Opteron) and Intel (Xeon with EM64T). Am I right if I say an Opteron based server is the better choice for MySQL because of the more advanced and faster bus system? Is there anywhere a good benchmark out there where I could see the performance differences between the different architectures (PIII, P4, Xeon, Opteron etc.) when running MySQL? How much memory could MySQL use on a 64 bit system running a 32 bit Linux distribution? Can I run MySQL in 64 bit mode? Thank you for your help! Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: MySQL and x86-64
Hi, Dylan Neild schrieb: 1. We just installed a very large MySQL installation on a dual opteron 2ghz system with 16GB of memory and it -flies-. That's pretty subjective, so check: http://www6.tomshardware.com/cpu/20030422/opteron-17.html http://www.infoworld.com/article/04/09/17/38FE64shootout2_1.html Thank you for the informative links. 2. A 32-bit Linux distribution (ie, 32-bit kernel) is pretty much like running an Athlon XP processor (save for the perfformance increases of the Opteron chip). You don't get any 64-bit advantages. You definitely need a 64-bit kernel and 64-bit libraries to take advantage of the Opteron processor. That said, 64-bit kernels come with 32-bit libraries usually, so you don't lose backwards compatibility, but simply gain 64-bit computing. Also, generally, 64-bit binaries compiled specifically for the opteron are faster than 32-bit code executed on the same opteron CPU. 64-bit Linux is pretty much everywhere. SuSE and RedHat ship a stable 64-bit distribution, though Debian 3.1 works great as well (i personally use Debian). Good to know. I was a little bit afraid to go with a 64 bit linux, 'cos at the local linux user group someone reported that Suse 9.2 64 Bit doesn't work with his EM64T system. But it's Suse and Intel, I'm a friend of AMD and Debian. Hope it helps, It definitely does. Regards, A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
Eric, the column type will limit the number of characters per row. A column of type TEXT will hold up to 65,535 characters but with LONGTEXT you can put up to 4,294,967,295 charcters into one row. I have an application with Texts of up to 200 pages in one column. Full-Text Search is handling this very well. Thomas Spahni On Tue, 14 Dec 2004, EP wrote: I've looked in the documentation but didn't see any indication of the limits of Full-Text Search in terms of how many characters/words it can process per row. For example, if I have a column with 4,000 character strings in it, can I use it effectively in Full-Text Searching? What if the column holds gigabytes of text in each row? My mind is probably stuck in an indexing paradigm, but I'd like to know where the limits (of Full Text search) are, if any. Can anyone advise? Eric Pederson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Definition of password hashing algorithm in 4.1.7
I've been looking into what algorithm MySQL 4.1.7 uses for password hashing/encryption, with a view to ascertaining how secure it is. Does it conform to any combinations of published Specs e.g. MD5/SHA-1/etc? I had a look at com.mysql.jdbc.Util#newHash() and #newCrypt() in Connector/J 3.0, but the code is somewhat opaque. Is this algorithm native to MySQL or is it just an implementation of a published algorithm? Is it worth my time trying to track down the intriguing 'Monty' code mentioned in Util.java? Ta, -- Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot connect to local server problem
Hi, i built an application which uses MySQL 4.0.17 using Windows XP Professional. Tomorrow, i need to present the application to my client, so i preparing my enviroment in a laptop, which runs Windows 2000. When i installed MySQL 4.0.17 and tried to run the client from command line, i got the following error message: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I don't remember this problem when i first installed the db in windows XP (or maybe i just don't remeber solving it). What am i missing? How do i fix that? Thanks, ltcmelo ___ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot connect to local server problem
Did you reboor after install? -Original Message- From: Leandro Melo [mailto:[EMAIL PROTECTED] Sent: woensdag 15 december 2004 14:51 To: lista mysql Subject: Cannot connect to local server problem Hi, i built an application which uses MySQL 4.0.17 using Windows XP Professional. Tomorrow, i need to present the application to my client, so i preparing my enviroment in a laptop, which runs Windows 2000. When i installed MySQL 4.0.17 and tried to run the client from command line, i got the following error message: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I don't remember this problem when i first installed the db in windows XP (or maybe i just don't remeber solving it). What am i missing? How do i fix that? Thanks, ltcmelo ___ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.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: Cannot complete database transfer
Did you install MySQL on the other box? It sounds like you took a snapshot of the data but have not installed the executables yet. James Sherwood wrote: I am trying to transfer a database from one linux box to another, both using redhat. On one I performed a mysqldump and it worked fine. On the other I created the database using a manager and tried the 'mysql username password database filename.sql' command The problem is, I cannot seem to get the box to see the mysql. I found it in init.d I have root access I keep getting 'command not found'. Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot complete database transfer
I keep getting 'command not found'. This error indicates that your shell cannot find the mysql file to execute (or that it is not executable for some reason... bad permissions). This can be caused by mysql (client) not being installed or by the client not being on a path that your user can see. more below... Victor Pendleton wrote: Did you install MySQL on the other box? It sounds like you took a snapshot of the data but have not installed the executables yet. James Sherwood wrote: I am trying to transfer a database from one linux box to another, both using redhat. On one I performed a mysqldump and it worked fine. On the other I created the database using a manager and tried the 'mysql username password database filename.sql' command The problem is, I cannot seem to get the box to see the mysql. I found it in init.d The mysql in init.d is a control script that starts and stops the daemon. It is not the mysql client program and mysql mysqld. vi (or whatever your text editor preference may be) the script and look at it. You can find your bin dir somewhere in this file. Once you find it you can see if mysql is in there. I have root access try su - instead of su if that is what you are using for root access. I keep getting 'command not found'. Any ideas would be greatly appreciated James hth, -- Jason McKnight Mgr. Information Services The InSite Group,LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Definition of password hashing algorithm in 4.1.7
Mike, The newest MySQL uses SHA1 in combo with random generated 20 byte session hash values. The procedure is irreversible and therefore why it is not possible to obtain the original password. MySQL is thus very secure and only vulnerable to a bruteforce attack. You can partially secure yourself against this by limiting users to specific hosts. The day will surely come when MySQL will built in a timeout after a failed login attempt (i.e. when the username and host is ok, but the password failed). This will render even the bruteforce attack useless, as the attacker will have to wait years to test even a billion passwords (depending on the timeout value of course). As a typical bruteforce attack (depending on the number of valid characters and password length) can easily run into 10+ billion password permutations, this attack will be in vain as it will take decades to test all the passwords. Currently though, has MySQL no such feature. This allows you to test passwords against it upward of 10,000+ per second (if it is localhost), i.e. you can therefore test a billion passwords in approx. 30 mins. All this is obviously just estimates, as it depends on factors such as the MySQL hardware, your hardware, where the MySQL is running relative to you and how fast a connection can be established, etc etc. Typically (using a remote MySQL server) even just the connection setup time takes 1 second, i.e. 1 password/sec, thus 1 billion passwords will take 31 years :) Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -Original Message- From: Mike Moran [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED];[EMAIL PROTECTED] [EMAIL PROTECTED] CC: Subject: Definition of password hashing algorithm in 4.1.7 Sent: Wed, 15 Dec 2004 12:44:10 GMT Received: Wed, 15 Dec 2004 12:48:19 GMT Read: Wed, 15 Dec 2004 13:46:54 GMT X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on mail3.infinology.net X-Spam-Status: No, hits=0.0 required=7.0 tests=none autolearn=no version=2.63 I've been looking into what algorithm MySQL 4.1.7 uses for password hashing/encryption, with a view to ascertaining how secure it is. Does it conform to any combinations of published Specs e.g. MD5/SHA-1/etc? I had a look at com.mysql.jdbc.Util#newHash() and #newCrypt() in Connector/J 3.0, but the code is somewhat opaque. Is this algorithm native to MySQL or is it just an implementation of a published algorithm? Is it worth my time trying to track down the intriguing 'Monty' code mentioned in Util.java? Ta, -- Mike -- 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: Cannot complete database transfer
It seems like the database installed fine(it is up and running, but I use a manager from a windows box to manipulate it) but maybe the client messed up on install? If so is there a way to just install the mysql client? James - Original Message - From: Jason McKnight [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 11:07 AM Subject: Re: Cannot complete database transfer I keep getting 'command not found'. This error indicates that your shell cannot find the mysql file to execute (or that it is not executable for some reason... bad permissions). This can be caused by mysql (client) not being installed or by the client not being on a path that your user can see. more below... Victor Pendleton wrote: Did you install MySQL on the other box? It sounds like you took a snapshot of the data but have not installed the executables yet. James Sherwood wrote: I am trying to transfer a database from one linux box to another, both using redhat. On one I performed a mysqldump and it worked fine. On the other I created the database using a manager and tried the 'mysql username password database filename.sql' command The problem is, I cannot seem to get the box to see the mysql. I found it in init.d The mysql in init.d is a control script that starts and stops the daemon. It is not the mysql client program and mysql mysqld. vi (or whatever your text editor preference may be) the script and look at it. You can find your bin dir somewhere in this file. Once you find it you can see if mysql is in there. I have root access try su - instead of su if that is what you are using for root access. I keep getting 'command not found'. Any ideas would be greatly appreciated James hth, -- Jason McKnight Mgr. Information Services The InSite Group,LLC -- 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]
ERROR 1044 (42000): Access denied for user 'root'
Hi, As the root user I created a DB a populated with tables... but when I want to create a user to handle that DB i get this error message: mysql grant all on db.* to [EMAIL PROTECTED] identified by 'password'; ERROR 1044 (42000): Access denied for user 'root' What could be the reason??? I though the mysql root user was analogous to the unix counterpart Thanks a lot in advance for your help. Regards, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To use Quotes or not to, that's the question.
On Wednesday 15 December 2004 15:38, Dave Juntgen might have typed: Hi! Could someone please explain to me why the second query below is faster then the first query? The only difference between the two is that ext_doc_id's value is quoted. Index and column information follow and the table being queried contains approximately 3.5 million rows. SQL query cache? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To use Quotes or not to, that's the question.
Hi! Could someone please explain to me why the second query below is faster then the first query? The only difference between the two is that ext_doc_id's value is quoted. Index and column information follow and the table being queried contains approximately 3.5 million rows. Thanks! --Dave EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND ext_doc_id=412625; +---+--+---+--+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+---+-+ | documents | ref | idx7 | idx7 | 30 | const | 94761 | Using where | +---+--+---+--+-+---+---+-+ 1 row in set (0.03 sec) EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND ext_doc_id='412625'; +---+--+---+--+-+-+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+-+--+-+ | documents | ref | idx7 | idx7 | 60 | const,const |3 | Using where | +---+--+---+--+-+-+--+-+ 1 row in set (0.00 sec) +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | doc_id | int(10) unsigned| | PRI | NULL | auto_increment | | revision_number | smallint(6) | | | 0 || | user_id | int(10) unsigned| | MUL | 0 || | origin_id | int(10) unsigned| | MUL | 0 || | pat_id | int(10) unsigned| | MUL | 0 || | doc_type| char(10)| | MUL | || | storage_type| int(10) unsigned| | | 0 || | storage_id | bigint(20) unsigned | | | 0 || | volume_id | char(1) | YES | | NULL || | filename| char(255) | YES | | NULL || | service_location| char(10)| YES | | NULL || | origin_date | datetime| | | -00-00 00:00:00 || | enter_date | datetime| | MUL | -00-00 00:00:00 || | revision_date | datetime| | | -00-00 00:00:00 || | service_date| datetime| | | -00-00 00:00:00 || | approx_service_date | tinyint(1) unsigned | | | 0 || | review_date | datetime| | | -00-00 00:00:00 || | review_user_id | int(11) | | | 0 || | ext_doc_id | char(30)| | | || | interface | char(30)| | MUL | || +-+-+--+-+-++ 20 rows in set (0.00 sec) mysql show index from documents; +---++--+--+---+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+---+---+-+--++--++-+ | documents | 0 | PRIMARY |1 | doc_id| A | 3566754 | NULL | NULL | | BTREE | | | documents | 1 | idx2 |1 | pat_id| A | 89168 | NULL | NULL | | BTREE | | | documents | 1 | idx2 |2 | service_date | A | 1188918 | NULL | NULL | | BTREE | | | documents | 1 | idx3 |1 | pat_id| A | 89168 | NULL | NULL | | BTREE | | | documents | 1 | idx3 |2 | doc_type | A | 891688 | NULL | NULL | | BTREE | |
Re: text retrieval functions
Thx for your interest.. The type of my question is for learning what to choose. I would use what u suggest me.. The only information i can provide u is that i am running linux.. If u can suggest me the version of mysql and the appropriate function and after i will continue searching... I am searching if i can implement some functionality of an information retrieval system using mysql...Do u have any resources available? --- [EMAIL PROTECTED] wrote: You mention nothing about your environment. What OS are you using? What version MySQL (server and client) are you using? How do you intend to interact with your database? Will you be using one of the many tools available to directly interact with a MySQL server or will you be writing a script/program on your own? If you are scripting/programming, what language will you use and which connection libraries will you consider? Sorry to ask so many questions but your question was so vague that it is unanswerable. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alaios [EMAIL PROTECTED] wrote on 12/15/2004 03:56:40 AM: Hi... I need some function and the appropriate type field s of a table that are able to search and store big texts (such as books,magazines,articles) Thx a lot __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Dress up your holiday email, Hollywood style. Learn more. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot complete database transfer
Yes, there are other databases on that mysql installation. The only thing is, I recently upgraded it to the latest release of mysql. James - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: James Sherwood [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 10:47 AM Subject: Re: Cannot complete database transfer Did you install MySQL on the other box? It sounds like you took a snapshot of the data but have not installed the executables yet. James Sherwood wrote: I am trying to transfer a database from one linux box to another, both using redhat. On one I performed a mysqldump and it worked fine. On the other I created the database using a manager and tried the 'mysql username password database filename.sql' command The problem is, I cannot seem to get the box to see the mysql. I found it in init.d I have root access I keep getting 'command not found'. Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
EP [EMAIL PROTECTED] wrote on 15/12/2004 15:44:15: Thomas Spahni [EMAIL PROTECTED] wrote: the column type will limit the number of characters per row. A column of type TEXT will hold up to 65,535 characters but with LONGTEXT you can put up to 4,294,967,295 charcters into one row. I have an application with Texts of up to 200 pages in one column. Full-Text Search is handling this very well. Thanks... Really?! If I can follow-up with another question, does experience suggest Full-Text Search handles a large number of such documents efficiently? For example, I am expecting to have (up to) one million documents in my database. I was considering breaking each document into paragraphs for search efficiency, but if Full-Text Search can search return results quickly on a large number of long (e.g. 10,000+ character) documents, my database has just become much simpler. I see no reason why not. AIUI, Full Text search breaks the documents up into words and indexes each document by avery word in the document. Breaking into paragraphs gives you an approximate position within the document for a hit, and changes the behaviour for multiple keywords (they must both be in the same pararaph) but should have little effect on the efficiency of the index. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To use Quotes or not to, that's the question.
Query caching? The second response probably came from the cache.
To be absolutely sure (a.k.a overkill) that you have no cached results,
you can restart the server between each query. Or, you can use the RESET
QUERY CACHE command to clear your cache without the restart.
http://dev.mysql.com/doc/mysql/en/RESET.html
The quoting rule is: If it's a NUMBER, don't quote it. Strings get quotes,
so do dates. Database elements (tables, columns, etc. ) whose names are a
reserved word or contain invalid characters (like spaces) or are otherwise
invalid would need to be surrounded by backticks (`) not single quotes (')
to be referenced. The use of invalid names is supported for cross platform
compatibility (through the backtick mechanism) but is *strongly
discouraged* as it usually indicates a poor design choice.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Dave Juntgen [EMAIL PROTECTED] wrote on 12/15/2004 10:38:03 AM:
Hi!
Could someone please explain to me why the second query below is
faster then the first query? The only difference between the two is
that ext_doc_id's value is quoted. Index and column information
follow and the table being queried contains approximately 3.5 million
rows.
Thanks!
--Dave
EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND
ext_doc_id=412625;
+---+--+---+--+-+---+---
+-+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+---+--+---+--+-+---+---
+-+
| documents | ref | idx7 | idx7 | 30 | const | 94761
| Using where |
+---+--+---+--+-+---+---
+-+
1 row in set (0.03 sec)
EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND
ext_doc_id='412625';
+---+--+---+--+-+-
+--+-+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+---+--+---+--+-+-
+--+-+
| documents | ref | idx7 | idx7 | 60 | const,const |
3 | Using where |
+---+--+---+--+-+-
+--+-+
1 row in set (0.00 sec)
+-+-+--+-
+-++
| Field | Type| Null | Key | Default
| Extra |
+-+-+--+-
+-++
| doc_id | int(10) unsigned| | PRI | NULL
| auto_increment |
| revision_number | smallint(6) | | | 0
||
| user_id | int(10) unsigned| | MUL | 0
||
| origin_id | int(10) unsigned| | MUL | 0
||
| pat_id | int(10) unsigned| | MUL | 0
||
| doc_type| char(10)| | MUL |
||
| storage_type| int(10) unsigned| | | 0
||
| storage_id | bigint(20) unsigned | | | 0
||
| volume_id | char(1) | YES | | NULL
||
| filename| char(255) | YES | | NULL
||
| service_location| char(10)| YES | | NULL
||
| origin_date | datetime| | |
-00-00 00:00:00 ||
| enter_date | datetime| | MUL |
-00-00 00:00:00 ||
| revision_date | datetime| | |
-00-00 00:00:00 ||
| service_date| datetime| | |
-00-00 00:00:00 ||
| approx_service_date | tinyint(1) unsigned | | | 0
||
| review_date | datetime| | |
-00-00 00:00:00 ||
| review_user_id | int(11) | | | 0
||
| ext_doc_id | char(30)| | |
||
| interface | char(30)| | MUL |
||
+-+-+--+-
+-++
20 rows in set (0.00 sec)
mysql show index from documents;
+---++--+--+---
+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+---++--+--+---
Re: Definition of password hashing algorithm in 4.1.7
Mark Matthews wrote: [ ... ] Mike Moran wrote: I've been looking into what algorithm MySQL 4.1.7 uses for password hashing/encryption, with a view to ascertaining how secure it is. Does it conform to any combinations of published Specs e.g. MD5/SHA-1/etc? [ ... ] Mike, MySQL-4.1.7 uses SHA-1. The code you're looking at is for MySQL-4.0 and older. See MysqlIO.secureAuth411(), which also happens to have the entire algorithm in a comment block ;) Ta for that (and also the SciBit person). I think I understand what is going on here, going by the doc comment you mention. However, I'm unsure why the password is being hashed twice i.e. why is hash_stage2 needed? I suppose it doesn't hurt, but I'm just curious. Also, I take it from this that the authentication algorithm/protocol used is MySQL-specific? Incidentally, I notice Security#scramble411() uses getBytes() without specifying a character set. As far as I understand it, because getBytes() uses the default platform character set, this will give a different scramble value on different Java platforms when you use characters outside the small range which is encoded the same in multiple character sets e.g. if you use a non-ASCII password. I haven't double-checked this; I just noticed it in passing. -- Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: text retrieval functions
You mention nothing about your environment. What OS are you using? What version MySQL (server and client) are you using? How do you intend to interact with your database? Will you be using one of the many tools available to directly interact with a MySQL server or will you be writing a script/program on your own? If you are scripting/programming, what language will you use and which connection libraries will you consider? Sorry to ask so many questions but your question was so vague that it is unanswerable. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alaios [EMAIL PROTECTED] wrote on 12/15/2004 03:56:40 AM: Hi... I need some function and the appropriate type field s of a table that are able to search and store big texts (such as books,magazines,articles) Thx a lot __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 1044 (42000): Access denied for user 'root'
Ed, It sounds like root doesn't have the GRANT permission for the localhost. You can check by querying the User table in mysql. --Dave David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax : 260.459.6271 -Original Message- From: Guru Vai Kernel-Hacker!!! [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 11:34 AM To: [EMAIL PROTECTED] Subject: ERROR 1044 (42000): Access denied for user 'root' Hi, As the root user I created a DB a populated with tables... but when I want to create a user to handle that DB i get this error message: mysql grant all on db.* to [EMAIL PROTECTED] identified by 'password'; ERROR 1044 (42000): Access denied for user 'root' What could be the reason??? I though the mysql root user was analogous to the unix counterpart Thanks a lot in advance for your help. Regards, Ed -- 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: To use Quotes or not to, that's the question.
ext_doc_id is a CHAR type, which means that you may have two rows, 0412625, and 412625, both of which match the expression WHERE ext_doc_id=412625. MySQL can't use an index because it has to convert each field to a number before doing the comparison. Your second query is doing a direct string comparison, so MySQL can use the index. If you know you will only be storing numbers in ext_doc_id, consider converting it to an INT or BIGINT and save yourself some disk space. This was my assumption as well, int-string conversion each time. But if MySQL knows that the index is a char and I send a int, why not just do the int-string conversion once and store the value in a buffer and then call the index? Thanks, --Dave David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax : 260.459.6271 -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 11:10 AM To: Dave Juntgen Cc: [EMAIL PROTECTED] Subject: Re: To use Quotes or not to, that's the question. In the last episode (Dec 15), Dave Juntgen said: Could someone please explain to me why the second query below is faster then the first query? The only difference between the two is that ext_doc_id's value is quoted. Index and column information follow and the table being queried contains approximately 3.5 million rows. ext_doc_id is a CHAR type, which means that you may have two rows, 0412625, and 412625, both of which match the expression WHERE ext_doc_id=412625. MySQL can't use an index because it has to convert each field to a number before doing the comparison. Your second query is doing a direct string comparison, so MySQL can use the index. If you know you will only be storing numbers in ext_doc_id, consider converting it to an INT or BIGINT and save yourself some disk space. EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND ext_doc_id=412625; +---+--+---+--+-+---+---+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+---+--- --+ | documents | ref | idx7 | idx7 | 30 | const | 94761 | Using where | +---+--+---+--+-+---+---+--- --+ 1 row in set (0.03 sec) EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND ext_doc_id='412625'; +---+--+---+--+-+-+- -+-- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+-+- -+-- ---+ | documents | ref | idx7 | idx7 | 60 | const,const | 3 | Using where | +---+--+---+--+-+-+- -+-- ---+ 1 row in set (0.00 sec) +-+-+--+-+-- ---++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-- ---++ | ext_doc_id | char(30)| | | || +-+-+--+-+-- ---++ 20 rows in set (0.00 sec) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help getting mysqld to read my my.cnf on start from init.d on mandrake 10.1 and mysql 4
Hello, I am building a new DB box. The box is mandrake 10.1 and mysql 4.0.20 All my other boxes are RH9 and mysql 3 The one problem I am having is they appear to do the my.cnf differently There was no my.cnf in the /etc dir, so I created one from my-huge.cnf and placed it in the /etc But the startup does not use it. So I tried to append the same command to the mysql script that is called from init.d --defaults-file=/etc/my.cnf and I get the error 041215 10:51:59 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'defaults-file=/etc/my.cnf' 041215 10:51:59 mysqld ended That part that confuses me is in the script /etc/rc.d/init.d/mysqld (I put in some debug echos to see where it is exiting) When I run the startup it print 2 But I am not failure with /usr/bin/my_print_defaults Thanks for any help! Randy ==SNIP=== # Get arguments from the my.cnf file, # groups [mysqld] [mysql_server] and [mysql.server] if test -x ./bin/my_print_defaults then echo 1 print_defaults=./bin/my_print_defaults elif test -x $bindir/my_print_defaults then echo 2 print_defaults=$bindir/my_print_defaults elif test -x $bindir/mysql_print_defaults then echo 3 print_defaults=$bindir/mysql_print_defaults else echo I am here # Try to find basedir in /etc/my.cnf conf=/etc/my.cnf print_defaults= if test -r $conf then subpat='^[^=]*basedir[^=]*=\(.*\)$' dirs=`sed -e /$subpat/!d -e 's//\1/' $conf` for d in $dirs do d=`echo $d | sed -e 's/[ ]//g'` if test -x $d/bin/my_print_defaults then print_defaults=$d/bin/my_print_defaults break fi if test -x $d/bin/mysql_print_defaults then print_defaults=$d/bin/mysql_print_defaults break fi done fi # Hope it's in the PATH ... but I doubt it test -z $print_defaults print_defaults=my_print_defaults Fi ==SNIP=== -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help getting mysqld to read my my.cnf on start from init.d on mandrake 10.1 and mysql 4
At 11:02 -0600 12/15/04, Randy Paries wrote: Hello, I am building a new DB box. The box is mandrake 10.1 and mysql 4.0.20 All my other boxes are RH9 and mysql 3 The one problem I am having is they appear to do the my.cnf differently There was no my.cnf in the /etc dir, so I created one from my-huge.cnf and placed it in the /etc But the startup does not use it. Run mysqld --help and look at the help message to see what option files the server thinks it's supposed to read. One possibility is that it reads /etc/mysql/my.cnf instead of /etc/my.cnf -- I believe some vendors hack MySQL to change the location of the global option file. So I tried to append the same command to the mysql script that is called from init.d --defaults-file=/etc/my.cnf and I get the error 041215 10:51:59 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'defaults-file=/etc/my.cnf' 041215 10:51:59 mysqld ended The probable cause of this is that --defaults-file is not the first option. That part that confuses me is in the script /etc/rc.d/init.d/mysqld (I put in some debug echos to see where it is exiting) When I run the startup it print 2 But I am not failure with /usr/bin/my_print_defaults Thanks for any help! Randy ==SNIP=== # Get arguments from the my.cnf file, # groups [mysqld] [mysql_server] and [mysql.server] if test -x ./bin/my_print_defaults then echo 1 print_defaults=./bin/my_print_defaults elif test -x $bindir/my_print_defaults then echo 2 print_defaults=$bindir/my_print_defaults elif test -x $bindir/mysql_print_defaults then echo 3 print_defaults=$bindir/mysql_print_defaults else echo I am here # Try to find basedir in /etc/my.cnf conf=/etc/my.cnf print_defaults= if test -r $conf then subpat='^[^=]*basedir[^=]*=\(.*\)$' dirs=`sed -e /$subpat/!d -e 's//\1/' $conf` for d in $dirs do d=`echo $d | sed -e 's/[ ]//g'` if test -x $d/bin/my_print_defaults then print_defaults=$d/bin/my_print_defaults break fi if test -x $d/bin/mysql_print_defaults then print_defaults=$d/bin/mysql_print_defaults break fi done fi # Hope it's in the PATH ... but I doubt it test -z $print_defaults print_defaults=my_print_defaults Fi ==SNIP=== -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
I'm currently doing a large number of REPLACE queries, I know that these evaluate as if doing a DELETE/INSERT pair, and I'm wondering if this is true on a disk io level as well with extra io occuring for the delete, and then re-insertion, vs what would occur with an UPDATE. The way it works roughly each row gets updated around 12-24 times, the updated do not affect the primary key, or any of the other keys for that matter. The table is INNODB. So I'm thinking if the row is deleted then re-inserted, there is the overhead of one finding the old row and marking it deleted, two searching for the correct pos for the row in the table and in the key structures, and three writing the row to disk. Vs, and UPDATE would have the overhead of one finding the old row, and two writing the updates to disk. As such it seems like it actually be faster for me to attempt an UPDATE, and if it fails, then do an INSERT, rather than using REPLACE? Is this correct? -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help getting mysqld to read my my.cnf on start from init.d on mandrake 10.1 and mysql 4
At 11:23 -0600 12/15/04, Randy Paries wrote: So I tried $bindir/mysqld_safe --defaults-file=/etc/my.cnf --datadir=$datadir --defaults-file=/etc/my.cnf --pid-file=$pid_file /dev/null I get the error:: 041215 11:15:11 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'defaults-file=/etc/my.cnf' 041215 11:15:11 mysqld ended mysqld does accept the --defaults-file, but I am trying to get this to run properly from the init.d startup It appears that mysqld_safe does not accept --defaults-file I guess I could hack the mysqld_safe, but I would really like to do it the correct way... thanks What did mysqld --help report for the set of option files that mysqld will read? -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 11:08 AM To: Randy Paries; [EMAIL PROTECTED] Subject: Re: Help getting mysqld to read my my.cnf on start from init.d on mandrake 10.1 and mysql 4 At 11:02 -0600 12/15/04, Randy Paries wrote: Hello, I am building a new DB box. The box is mandrake 10.1 and mysql 4.0.20 All my other boxes are RH9 and mysql 3 The one problem I am having is they appear to do the my.cnf differently There was no my.cnf in the /etc dir, so I created one from my-huge.cnf and placed it in the /etc But the startup does not use it. Run mysqld --help and look at the help message to see what option files the server thinks it's supposed to read. One possibility is that it reads /etc/mysql/my.cnf instead of /etc/my.cnf -- I believe some vendors hack MySQL to change the location of the global option file. So I tried to append the same command to the mysql script that is called from init.d --defaults-file=/etc/my.cnf and I get the error 041215 10:51:59 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'defaults-file=/etc/my.cnf' 041215 10:51:59 mysqld ended The probable cause of this is that --defaults-file is not the first option. That part that confuses me is in the script /etc/rc.d/init.d/mysqld (I put in some debug echos to see where it is exiting) When I run the startup it print 2 But I am not failure with /usr/bin/my_print_defaults Thanks for any help! Randy ==SNIP=== # Get arguments from the my.cnf file, # groups [mysqld] [mysql_server] and [mysql.server] if test -x ./bin/my_print_defaults then echo 1 print_defaults=./bin/my_print_defaults elif test -x $bindir/my_print_defaults then echo 2 print_defaults=$bindir/my_print_defaults elif test -x $bindir/mysql_print_defaults then echo 3 print_defaults=$bindir/mysql_print_defaults else echo I am here # Try to find basedir in /etc/my.cnf conf=/etc/my.cnf print_defaults= if test -r $conf then subpat='^[^=]*basedir[^=]*=\(.*\)$' dirs=`sed -e /$subpat/!d -e 's//\1/' $conf` for d in $dirs do d=`echo $d | sed -e 's/[ ]//g'` if test -x $d/bin/my_print_defaults then print_defaults=$d/bin/my_print_defaults break fi if test -x $d/bin/mysql_print_defaults then print_defaults=$d/bin/mysql_print_defaults break fi done fi # Hope it's in the PATH ... but I doubt it test -z $print_defaults print_defaults=my_print_defaults Fi ==SNIP=== -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
On Wed, 2004-12-15 at 11:46 -0600, gerald_clark wrote: John McCaskey wrote: I'm currently doing a large number of REPLACE queries, I know that these evaluate as if doing a DELETE/INSERT pair, and I'm wondering if this is true on a disk io level as well with extra io occuring for the delete, and then re-insertion, vs what would occur with an UPDATE. The way it works roughly each row gets updated around 12-24 times, the updated do not affect the primary key, or any of the other keys for that matter. The table is INNODB. So I'm thinking if the row is deleted then re-inserted, there is the overhead of one finding the old row and marking it deleted, two searching for the correct pos for the row in the table and in the key structures, and three writing the row to disk. Vs, and UPDATE would have the overhead of one finding the old row, and two writing the updates to disk. As such it seems like it actually be faster for me to attempt an UPDATE, and if it fails, then do an INSERT, rather than using REPLACE? This provides a window for errors. You can attempt the update, and before the insert, another process could insert the record. And insert followed by an update on failure closes this window. Thats true, and thanks for pointing it out. However, in my situation, I am not concerned with this. The tables in question are basically data logging tables, which are updated by a single process which will always serial UPDATE, check if any rows were matched (I'll use the CLIENT_FOUND_ROWS option when connecting), and then INSERT if not. Furthermore, the window for errors could be closed in any situation by fist UPDATE, check if any rows were matched, then REPLACE (rather than insert). This would make the situation where a row did not already exist actually more expensive than before, but because that situation occurs only about 1/24 times for me, it is still much faster overall adding the UPDATE I think. This is assuming that as I stated above the REPLACE query works as described and is truly just a DELETE/INSERT and is not optimized itself to avoid some of the overhead when a row already exists. Is this correct? -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auth Problem
Hi there im running a Server with several domains on it. i installed a shop today and i got problems with the user i made for the database. its possible to login with root account also the other accounts are working but not this one. i get this well known error message: *Warning*: mysql_connect(): Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in */var/htdocs/removed/htdocs/shop/catalog/includes/functions/database.php* on line *19* Unable to connect to database server! wtf did i wrong that its not working with this account? kind regards jimmy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To use Quotes or not to, that's the question.
In the last episode (Dec 15), Dave Juntgen said: ext_doc_id is a CHAR type, which means that you may have two rows, 0412625, and 412625, both of which match the expression WHERE ext_doc_id=412625. MySQL can't use an index because it has to convert each field to a number before doing the comparison. Your second query is doing a direct string comparison, so MySQL can use the index. If you know you will only be storing numbers in ext_doc_id, consider converting it to an INT or BIGINT and save yourself some disk space. This was my assumption as well, int-string conversion each time. But if MySQL knows that the index is a char and I send a int, why not just do the int-string conversion once and store the value in a buffer and then call the index? Because that wouldn't be right. If it did that, then in my example, it would only match 1 record instead of two. If the two fields were 0412625 and 0412625, it wouldn't match either of them. It has to convert each field to an int to see whether it evaluates to 412625 or not. I've put some sample SQL at the bottom of my post showing this. If mysql had computed indexes (it doesn't), you could create an index on (ext_doc_id+0), and then use WHERE (ext_doc_id+0) = 41265 in your query. That would use the index. mysql CREATE TABLE test ( myvalue char(30) ); mysql INSERT INTO test VALUES (412625),( 412625),(0412625); mysql SELECT * FROM test WHERE myvalue = 412625; +-+ | myvalue | +-+ | 412625 | | 0412625 | | 412625 | +-+ mysql SELECT * FROM test WHERE myvalue = 412625; +-+ | myvalue | +-+ | 412625 | +-+ -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Double conversion error
Hello, The create table statement is as follows: CREATE TABLE `call` ( `call_id` int(10) unsigned NOT NULL auto_increment, `init_lband` int(10) unsigned NOT NULL default '0', `chan_assign_sec` tinyint(3) unsigned default NULL, `adn_time_sec` tinyint(3) unsigned default NULL, `setup` char(3) NOT NULL default '---', `setup_sec` tinyint(3) unsigned default NULL, `completion` char(3) NOT NULL default '---', `duration_sec` int(10) unsigned default NULL, `ab_term` char(3) default '---', `session_id` int(10) unsigned NOT NULL default '0', `lgc_x` int(11) default NULL, `lgc_y` int(11) default NULL, `lgc_z` int(11) default NULL, PRIMARY KEY (`call_id`), KEY `FK_session_id` (`session_id`), CONSTRAINT `call_ibfk_4` FOREIGN KEY (`session_id`) REFERENCES `call_session` (`session_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB' The row I'm trying to match on is an auto incremented row in an InnoDB table. The SQL Query is as follows: update call set CHAN_ASSIGN_SEC = 3, ADN_TIME_SEC = 4, SETUP_SEC = 7, DURATION_SEC = 45, AB_TERM = 'N', COMPLETION = 'Y', SETUP = 'Y' where (CALL_ID = 2.37000e+002); There is indeed a row with call_id=237. Regards, -Brett Berry --- Hello. Can you send complete test for your problem (i.e SHOW CREATE TABLE on your tables, buggy sql statement...)? On my 4.1.7 instance of MySQL everything looks fine: mysql desc v1; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | v | int(11) | YES | | NULL| | +---+-+--+-+-+---+ select * from v1; +--+ | v| +--+ | 237 | +--+ mysql select * from v1 where v=2.37000e+002; +--+ | v| +--+ | 237 | +--+ update v1 set v=11 where v=2.37000e+002; select * from v1; +--+ | v| +--+ | 11 | +--+ I have a query where I perform an update where (CALL_ID = 2.37000e+002); This query updates nothing, even though my CALL_ID column has an id of 237. If I change the end of this query to read: where (CALL_ID = 237);, then the row with CALL_ID=237 is updated. Is there a reason why the double value 2.37000e+002 is not evaluating to 237? Regards, -Brett Berry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YOUR EMAIL ADDRESS HAS BEEN ADDED TO MY WHITE LIST
[EMAIL PROTECTED] wrote: Your email address has been added to my Spam Fighter White List. Adding your address to the White List ensures that I will always receive email you send to me. How do I unsubscribe from your Spam Figher White List spam?! :) Kevn -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restart of Mysql and tomcat error
Hello Since that list is wonderfull to solve my problem, I will try again. But this might not be the best place since the problem concerns mysql access througt tomcat (jakarta-tomcat-5.0.28). The java application we have, when start after a mysql restart (night backup) , will give an error (reset of the connection) for the first person that log in. Then all subsequent logging will be fine until a mysql restart. How can I prevent that? Johanne Duhaime IRCM
Re: Restart of Mysql and tomcat error
Duhaime Johanne wrote: Hello Since that list is wonderfull to solve my problem, I will try again. But this might not be the best place since the problem concerns mysql access througt tomcat (jakarta-tomcat-5.0.28). The java application we have, when start after a mysql restart (night backup) , will give an error (reset of the connection) for the first person that log in. Then all subsequent logging will be fine until a mysql restart. How can I prevent that? Johanne Duhaime IRCM Johanne, It sounds like tomcat thing. You could get around it by setting up replication and taking the slave db off line instead of the main db. It'd probably be a good idea anyway since your only cost is a little bit of time and some cheap hardware. walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No connect to mysql
Hi, I have the following permission: #ls -l /var/lib/ drwxr-xr-x 4 mysql root .. mysql .. in the mail.err: /usr/sbin/mysqld: Cant't open file: 'host:MYI'. (errno: 142) Fatal error: Can't open privilege tables: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) and: #ls -l /var/lib/mysql/mysql/host.MYI -rw-- 1 mysql root ..sorry but the message correct is: error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock (2)' Check that mysql is running and that the socket: '/var/lib/mysql/mysql.sock' exists ! ..and I have tried to create mysql.sock but I don't have good result. What I can to do for to resolve my problem ?? thanks. Salvatore. - Original Message - From: sasa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 14, 2004 12:41 PM Subject: No connect to mysql Hi, I have a problem with mysql 4.0.22 on fedora core 2. ..when I try: #/usr/bin/mysqladmin -u root password '' /usr/bin/mysqladmin: connect to server at 'localhost' failed error: Can't connect to local MySQL server through socket '/var/lib/mysql.sock (2)' Check that mysql is running and that the socket: '/var/lib/mysql.sock' exists ! .. the file mysql.sock not exists but I can to resolve ? .. thanks and sorry for my banal question. Salvatore. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Restart of Mysql and tomcat error
Johanne, There are numerous questions about connection methods, pooling etc that would be better asked in the tomcat list and would require work in your web application. However, putting on my pragmatic system integrator hat, could you get round this by simply doing a request to your application using wget at the end of your MySQL backup script? Worth considering, Andy -Original Message- From: Duhaime Johanne [mailto:[EMAIL PROTECTED] Sent: 15 December 2004 19:22 To: [EMAIL PROTECTED] Subject: Restart of Mysql and tomcat error Hello Since that list is wonderfull to solve my problem, I will try again. But this might not be the best place since the problem concerns mysql access througt tomcat (jakarta-tomcat-5.0.28). The java application we have, when start after a mysql restart (night backup) , will give an error (reset of the connection) for the first person that log in. Then all subsequent logging will be fine until a mysql restart. How can I prevent that? Johanne Duhaime IRCM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unions will full column names won't work in 4.1.7
Not sure if this is a bug... probably should be. On 4.1.18 I can run: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY FOO.COL_A Which will work just fine However when I use this query on 4.1.7 I get ERROR 1250 (42000): Table 'ARTICLE' from one of the SELECTs cannot be used in global ORDER clause I have to rewrite it to use: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY COL_A .. see the change in the ORDER BY... I can't call if FOO.COL_A I have to call it COL_A Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unions will full column names won't work in 4.1.7
At 16:23 -0800 12/15/04, Kevin A. Burton wrote: Not sure if this is a bug... probably should be. On 4.1.18 I can run: 4.1.18? I assume you mean 4.0.18. Anyway, what you describe is according to the documentation: http://dev.mysql.com/doc/mysql/en/UNION.html (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY FOO.COL_A Which will work just fine However when I use this query on 4.1.7 I get ERROR 1250 (42000): Table 'ARTICLE' from one of the SELECTs cannot be used in global ORDER clause I have to rewrite it to use: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY COL_A .. see the change in the ORDER BY... I can't call if FOO.COL_A I have to call it COL_A Kevin -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unions will full column names won't work in 4.1.7
- Original Message - From: Kevin A. Burton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 7:23 PM Subject: unions will full column names won't work in 4.1.7 Not sure if this is a bug... probably should be. On 4.1.18 I can run: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY FOO.COL_A Which will work just fine However when I use this query on 4.1.7 I get ERROR 1250 (42000): Table 'ARTICLE' from one of the SELECTs cannot be used in global ORDER clause I have to rewrite it to use: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY COL_A .. see the change in the ORDER BY... I can't call if FOO.COL_A I have to call it COL_A I'm not sure if I'd call that a bug either; you may want to check the manual to see if FOO.COL_A is *supposed* to work. Perhaps the bug is that it works in 4.0.18 when it shouldn't? I don't know if this helps but another way to do ORDER BY for UNIONs is to say Order by n where 'n' is an integer describing which column of the result set you are sorting on. Therefore, if you are sorting on the 3rd and 5th columns of the result set (both ascending), you'd say 'Order by 3, 5'. I'm not crazy about this approach myself - it seems likely that some users will scratch their heads and wonder what the '3' and '5' refer to - but it is certainly very concise and eliminates the issue of having a table name in the ORDER BY. Remember, the ORDER BY always refers to the result of the UNION, never to any of the individual SELECTs that make up the full query. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Multiple Selects
A quick question on the performance of the SELECT statement. I have a table with a set of 50 products, each product has about 10 attributes associated with it. The user will select certain aspects of a product (height, weight, colour, length etc.), and many products may display a particular attribute, which is why one select may not give me the results I am after. (I also want to display the top 5 products in order of relevance.) At the moment, I am making 12 individual SELECT statements in the code and using the results to allocate a rank to each product found in that SELECT. e.g. the first select would be SELECT * from table where weight 100; and each product in the result would be allocated 1 point - the second search would be for height 30, and so on. Is there a problem with doing multiple SELECT statements (I am using php) or would this be common place. Is there an alternative way of doing this search using the SELECT or some other statement ? I'm just interested to see if I'm being too demanding on the SQL server, as there is a potential that up to 10 users per minute may use this search.
Re: Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
Hi, On Wednesday, December 15, 2004, at 12:51 PM, John McCaskey wrote: On Wed, 2004-12-15 at 11:46 -0600, gerald_clark wrote: John McCaskey wrote: I'm currently doing a large number of REPLACE queries, I know that these evaluate as if doing a DELETE/INSERT pair, and I'm wondering if this is true on a disk io level as well with extra io occuring for the delete, and then re-insertion, vs what would occur with an UPDATE. The way it works roughly each row gets updated around 12-24 times, the updated do not affect the primary key, or any of the other keys for that matter. The table is INNODB. So I'm thinking if the row is deleted then re-inserted, there is the overhead of one finding the old row and marking it deleted, two searching for the correct pos for the row in the table and in the key structures, and three writing the row to disk. Vs, and UPDATE would have the overhead of one finding the old row, and two writing the updates to disk. As such it seems like it actually be faster for me to attempt an UPDATE, and if it fails, then do an INSERT, rather than using REPLACE? This provides a window for errors. You can attempt the update, and before the insert, another process could insert the record. And insert followed by an update on failure closes this window. Thats true, and thanks for pointing it out. However, in my situation, I am not concerned with this. The tables in question are basically data logging tables, which are updated by a single process which will always serial UPDATE, check if any rows were matched (I'll use the CLIENT_FOUND_ROWS option when connecting), and then INSERT if not. Furthermore, the window for errors could be closed in any situation by fist UPDATE, check if any rows were matched, then REPLACE (rather than insert). This would make the situation where a row did not already exist actually more expensive than before, but because that situation occurs only about 1/24 times for me, it is still much faster overall adding the UPDATE I think. This is assuming that as I stated above the REPLACE query works as described and is truly just a DELETE/INSERT and is not optimized itself to avoid some of the overhead when a row already exists. Is this correct? No. In InnoDB an UPDATE is done as a DELETE/INSERT internally because it is multiversioning and it has to be able to rollback in case of a problem. So the UPDATE effectively does the same thing as the REPLACE that hits a problem. I would most likely stick with REPLACE since it is a bit easier to understand how it is working and has less client code. Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Mobile: +1 315 380-6048 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table types
How do I find out what table type is associated with each of the tables in MYSQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: text retrieval functions
Alaios [EMAIL PROTECTED] added: Thx for your interest.. The type of my question is for learning what to choose. I would use what u suggest me.. The only information i can provide u is that i am running linux.. If u can suggest me the version of mysql and the appropriate function and after i will continue searching... I am searching if i can implement some functionality of an information retrieval system using mysql...Do u have any resources available? Alaios, MySQL is well suited for that sort of thing, but you'll have some homework to do. You need a programming language to build your information retrieval system in, and you would likely use that same language to access the data in your database. Potential languages are many and include C++, PHP, Perl, Python, etc., etc. I presume you intend to serve (as in server) your information to many people. If you would do this via a browser/the web (intranet or Internet) you might prefer a language like PHP over others, the point being you would want to decide this at the beginning of your project - how you will provide the data to people not sitting at your PC. MySQL has tons of capabilities and applications - its not just for the web - but web applications are an easy thing to gain some knowledge of. Suggestions: - Google terms like web MySQL tutorial or PHP MySQL tutorial and do some reading to get oriented - Buy a book like MySQL (Paul DuBois) or PHP and MySQL Web Development (Luke Wellington, Laura Thomson). If the price of a new edition is cost prohibitive, try picking up a used (good' or new) condition book via Amazon.com - an easy way to get information on subjects you are not sure you are ready to invest in. - Read the online MySQL documentation, and or download it to your PC - Download MySQL (any version, but the latest stable version makes sense) and start playing with it. good luck - have fun exploring. Eric P.S. I'm not really PHP-positive-biased -- it's not my favorite language -- but I know there is an awful lot of tutorial material on the web re: PHP/MySQL... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to download database
Julie - Please specify which database binaries you are downloading. If you could provide us with the url's that would be much appreciated in helping you troubleshoot this. For Windows binaries, I would refer to the HTTP / FTP mirror links listed at the bottom of this page: http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-4.1.7-win.zip/from/pick I hope that helps! --bemansell Brian E. Mansell MySQL Professional On Tue, 14 Dec 2004 12:30:08 -0500, Woo, Julie [EMAIL PROTECTED] wrote: I've downloaded the database from several different servers today and each time I try to uncompress the file, I get the error message that the file is not a valid archive. I've tried using the built in compression tools in win 2003, and I've also tried downloading WinZip 9.0 eval to attempt the unzip. Neither work. Please advise. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: moving DB from one box to another.
Sorry, When I do the mysql dbname oldbox.sql I get the error message It creates mysqldump dbname -h oldbox oldbox.sql fine Thanks -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 11:25 PM To: Randy Paries; [EMAIL PROTECTED] Subject: Re: moving DB from one box to another. At 23:21 -0600 12/15/04, Randy Paries wrote: hello, I have built a new DB box. Orig db is mysqld Ver 3.23.58 New DB is mysqld Ver 4.0.20 I want to move the data from one box to another. I have a couple of really large tables with longtext fields. This data is a bunch of HTML My plan was to do a mysqldump dbname -h oldbox oldbox.sql then do a mysql dbname oldbox.sql The problem is I get this wonderfully verbose :-( error message :::ERROR at line 362701 It might help to specify at which point in this process the error occurs. During the dump? During the reload? I am wondering if there is some single quotes that may be messing it up or something like. What is the best way to do this? Thanks -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
text retrieval functions
Hi... I need some function and the appropriate type field s of a table that are able to search and store big texts (such as books,magazines,articles) Thx a lot __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1064: type mismatch
Hello list. I'm new to MySQL but so far I like it a lot. I have it running on WinNT4 w/SP6a and I use MySQL Admin. I'm having trouble running an application from a third party, this application launches but whenever I try a query, I get a message that says Type mismatch for field field name here, expecting:AuntoInc actual: Unknown. We've checked (the developer and I) the table definition and the referred field is an integer with autoinc on, and so it's shown in MySQL Admin. ¿Any ideas? I've googled for an answer but couldn't find it, also red the mysql manual but that didn't do either. Thanks a lot in advance.- -- -= Nicolás Conde - SS.AA.=- -= Facultad de Ingeniería=- -= Universidad de la República =- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot connect to local server problem
Have you verified that the MySQL server is currently running? Was this installed as service to automatically start up? Leandro Melo wrote: Hi, i built an application which uses MySQL 4.0.17 using Windows XP Professional. Tomorrow, i need to present the application to my client, so i preparing my enviroment in a laptop, which runs Windows 2000. When i installed MySQL 4.0.17 and tried to run the client from command line, i got the following error message: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I don't remember this problem when i first installed the db in windows XP (or maybe i just don't remeber solving it). What am i missing? How do i fix that? Thanks, ltcmelo ___ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot complete database transfer
I am trying to transfer a database from one linux box to another, both using redhat. On one I performed a mysqldump and it worked fine. On the other I created the database using a manager and tried the 'mysql username password database filename.sql' command The problem is, I cannot seem to get the box to see the mysql. I found it in init.d I have root access I keep getting 'command not found'. Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
find all records with more than one occurrence
Hi, How can I check all duplicated rows out from a large table? The values are not keys so they may have more than one occurrence. Thanks for your help. Regards, CHAN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot complete database transfer
What do you get when you do a `which mysql` from the command line? James Sherwood wrote: Yes, there are other databases on that mysql installation. The only thing is, I recently upgraded it to the latest release of mysql. James - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: James Sherwood [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 10:47 AM Subject: Re: Cannot complete database transfer Did you install MySQL on the other box? It sounds like you took a snapshot of the data but have not installed the executables yet. James Sherwood wrote: I am trying to transfer a database from one linux box to another, both using redhat. On one I performed a mysqldump and it worked fine. On the other I created the database using a manager and tried the 'mysql username password database filename.sql' command The problem is, I cannot seem to get the box to see the mysql. I found it in init.d I have root access I keep getting 'command not found'. Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Definition of password hashing algorithm in 4.1.7
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mike Moran wrote: I've been looking into what algorithm MySQL 4.1.7 uses for password hashing/encryption, with a view to ascertaining how secure it is. Does it conform to any combinations of published Specs e.g. MD5/SHA-1/etc? I had a look at com.mysql.jdbc.Util#newHash() and #newCrypt() in Connector/J 3.0, but the code is somewhat opaque. Is this algorithm native to MySQL or is it just an implementation of a published algorithm? Is it worth my time trying to track down the intriguing 'Monty' code mentioned in Util.java? Ta, Mike, MySQL-4.1.7 uses SHA-1. The code you're looking at is for MySQL-4.0 and older. See MysqlIO.secureAuth411(), which also happens to have the entire algorithm in a comment block ;) -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity Office: +1 708 332 0507 www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBwE6rtvXNTca6JD8RAoPXAJ94K07bT9Z9PcioDnrLYSgs3SqRoQCeKBu/ DsQkRNkfYRDoahIYMZ1mhuQ= =fA0E -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: find all records with more than one occurrence
Hi, How can I check all duplicated rows out from a large table? The values are not keys so they may have more than one occurrence. Thanks for your help. Regards, CHAN Chan, what about using DISTINCT in the select? .. or am I missing something? Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find all records with more than one occurrence
At 08:49 AM 12/15/2004, you wrote: Hi, How can I check all duplicated rows out from a large table? The values are not keys so they may have more than one occurrence. Thanks for your help. Regards, CHAN Try something like: select count(*) num, colvalue from table1 group by colvalue having num 1 If you want to check for duplicates among multiple columns, then try: select count(*) num, colvalue1, colvalue2, colvalue3 from table1 group by colvalue1, colvalue2, colvalue3 ... having num 1 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
YOUR EMAIL ADDRESS HAS BEEN ADDED TO MY WHITE LIST
Your email address has been added to my Spam Fighter White List. Adding your address to the White List ensures that I will always receive email you send to me. If you aren't using All-in-One SECRETMAKER, I recommend that you examine this powerful freeware. I recommend All-in-One SECRETMAKER software to all my friends and email contacts. This compact, smart tool includes Spam Fighter, Pop-Up Killer, Banner Blocker, Cookie Eraser, History Cleaner, Privacy Protector and more. Innovative new features, with many refinements, are presently being tested. All-in-One SECRETMAKER has been downloaded and restored Internet privacy for thousands of satisfied users. More detailed information about the All-in-One SECRETMAKER can be found at the SECRETMAKER home page, located at: WWW.SECRETMAKER.COM(copy / paste the URL in to your browser) If you would like to turn email and surfing the Web into a pleasant and secure experience once again, you can download the totally free All-in-One SECRETMAKER atWWW.SECRETMAKER.COM/DOWNLOADS/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To use Quotes or not to, that's the question.
In the last episode (Dec 15), Dave Juntgen said: Could someone please explain to me why the second query below is faster then the first query? The only difference between the two is that ext_doc_id's value is quoted. Index and column information follow and the table being queried contains approximately 3.5 million rows. ext_doc_id is a CHAR type, which means that you may have two rows, 0412625, and 412625, both of which match the expression WHERE ext_doc_id=412625. MySQL can't use an index because it has to convert each field to a number before doing the comparison. Your second query is doing a direct string comparison, so MySQL can use the index. If you know you will only be storing numbers in ext_doc_id, consider converting it to an INT or BIGINT and save yourself some disk space. EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND ext_doc_id=412625; +---+--+---+--+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+---+-+ | documents | ref | idx7 | idx7 | 30 | const | 94761 | Using where | +---+--+---+--+-+---+---+-+ 1 row in set (0.03 sec) EXPLAIN SELECT doc_id FROM documents WHERE interface='tasklist' AND ext_doc_id='412625'; +---+--+---+--+-+-+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+-+--+-+ | documents | ref | idx7 | idx7 | 60 | const,const |3 | Using where | +---+--+---+--+-+-+--+-+ 1 row in set (0.00 sec) +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | ext_doc_id | char(30)| | | || +-+-+--+-+-++ 20 rows in set (0.00 sec) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
Thomas Spahni [EMAIL PROTECTED] wrote: the column type will limit the number of characters per row. A column of type TEXT will hold up to 65,535 characters but with LONGTEXT you can put up to 4,294,967,295 charcters into one row. I have an application with Texts of up to 200 pages in one column. Full-Text Search is handling this very well. Thanks... Really?! If I can follow-up with another question, does experience suggest Full-Text Search handles a large number of such documents efficiently? For example, I am expecting to have (up to) one million documents in my database. I was considering breaking each document into paragraphs for search efficiency, but if Full-Text Search can search return results quickly on a large number of long (e.g. 10,000+ character) documents, my database has just become much simpler. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Query Browser
Hassan Shaikh wrote: The MySQL Query Browser online documentation clearly mentions that it runs on 32-bit Windows operating systems, including Windows 95, 98 and Me. However, while installing it on WinME, I get the following error: The Operating System is not adequate for running MySQL Query Browser 1.1 Can anyone please explain? Thanks. Hassan This was mentioned, briefly, on the MySQL GUI list: http://lists.mysql.com/gui-tools/1563 I don't know any more than that. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
John McCaskey wrote: I'm currently doing a large number of REPLACE queries, I know that these evaluate as if doing a DELETE/INSERT pair, and I'm wondering if this is true on a disk io level as well with extra io occuring for the delete, and then re-insertion, vs what would occur with an UPDATE. The way it works roughly each row gets updated around 12-24 times, the updated do not affect the primary key, or any of the other keys for that matter. The table is INNODB. So I'm thinking if the row is deleted then re-inserted, there is the overhead of one finding the old row and marking it deleted, two searching for the correct pos for the row in the table and in the key structures, and three writing the row to disk. Vs, and UPDATE would have the overhead of one finding the old row, and two writing the updates to disk. As such it seems like it actually be faster for me to attempt an UPDATE, and if it fails, then do an INSERT, rather than using REPLACE? This provides a window for errors. You can attempt the update, and before the insert, another process could insert the record. And insert followed by an update on failure closes this window. Is this correct? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : Mysql client that does export and import
Thank you everybody for the answers. Finally I downloaded SQLyog and it works fine for import /export Johanne -Message d'origine- De : Karam Chand [mailto:[EMAIL PROTECTED] Envoyé : 14 décembre 2004 22:32 À : Duhaime Johanne Cc : [EMAIL PROTECTED] Objet : Re: Mysql client that does export and import I use SQLyog everyday at my job. Check out http://www.webyog.com Karam --- [EMAIL PROTECTED] wrote: When you install MySQL you get several tools (not just the command-line client and the various server versions). Two of them are mysqldump and mysqlimport. The docs for all of them can be linked to from here: http://dev.mysql.com/doc/mysql/en/Client-Side_Scripts.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Duhaime Johanne [EMAIL PROTECTED] wrote on 12/14/2004 03:03:03 PM: Hello I have looked at MySql browser and MySqlAdministration and then in the mysql lists but I could not find what I was looking for. A developper is working with one of the many databasees we have. We want to allow him to import and export data from this database throught a window client (mysql in on a unix machine). Can someone suggest something? Thank you in advance Johanne Duhaime IRCM __ Do you Yahoo!? Send a seasonal email greeting and help others. Do good. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Query Browser
YOu could try the free version of SQLyog. It works on my Win98. www.webyog.com Karam --- Chris [EMAIL PROTECTED] wrote: Hassan Shaikh wrote: The MySQL Query Browser online documentation clearly mentions that it runs on 32-bit Windows operating systems, including Windows 95, 98 and Me. However, while installing it on WinME, I get the following error: The Operating System is not adequate for running MySQL Query Browser 1.1 Can anyone please explain? Thanks. Hassan This was mentioned, briefly, on the MySQL GUI list: http://lists.mysql.com/gui-tools/1563 I don't know any more than that. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delete or Update on Import
Once each day I have a PHP application that reads a legacy dBase format file and updates a customer table in a MySQL database. The PHP script reads each dBase record, searches for it in the table, updates the field values if they have changed, or creates a new record in the MySQL database if one does not exist. So far, so good. My problem is that occasionally a customer will be deleted in the dBase format system. In this case the deleted customer is still present in the MySQL database. It's never updated but it still appears in reports, etc. I could first bring all the dBase data into a separate table and then compare the two tables deleting records in the main table that have no match in the freshly imported data. I believe this would cause me to examine all the records several times between importing, checking for changes, deletions, etc. I would appreciate any suggestions for a more elegant/efficient method. There are about 20,000 records. Thanks in advance, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repeat records on results
I sort of know why this is happening. Just not sure how to correct it. My query: SELECT Reg.firstname,Reg.lastname, Profiles.ProfileName FROM Profiles INNER JOIN Reg ON (Profiles.LID = Reg.RegID) INNER JOIN PSkicerts ON Profiles.ProfileID = PSkicerts.ProfileID) where Profiles.Status != 2 The table PSkicerts has multiple records for each ProfileID. When I run a results set I get back a row of the same firstname, lastname and profilename for each record that exists in PSkicerts under the same ProfileID. Now if I add a DISTINCT, that seems to fix it, but this is really part of a bigger query that has more tables similar to PSkicerts. When I add those into the mix, DISTINCT seems to no longer help. Hope this makes sense. Perhaps someone knows how I can fix it. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading from 3.23 to 4.1
Hi all, I have installed FC3 on a system which supposed to act as Database server on a small LAN. MySQL server version 3.23 (I think) and all its parts has been installed. How can I upgrade it to MySQL 4.1 (latest version). Can anyone guide me step by step ? Here is some critical information on server and it is almost hard to reformat it. Thanks, Danesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No connect to mysql
J.R. Bullington wrote: Make sure that you have run `#/usr/local/mysql/bin/safe_mysqld ` ..when I try: #/usr/bin/safe_mysqld [1] #Starting mysqld daemon with database from /var/lib/mysql STOPPING server from pid file /var/lib/mysql/mail.pid 041215 23:56:46 mysqld ended and: #/usr/bin/mysqladmin status /usr/bin/mysqladmin: connect to server at 'localhost' failed error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock (2)' Check that mysql is running and that the socket: '/var/lib/mysql.sock' exists ! thanks. Salvatore. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
moving DB from one box to another.
hello, I have built a new DB box. Orig db is mysqld Ver 3.23.58 New DB is mysqld Ver 4.0.20 I want to move the data from one box to another. I have a couple of really large tables with longtext fields. This data is a bunch of HTML My plan was to do a mysqldump dbname -h oldbox oldbox.sql then do a mysql dbname oldbox.sql The problem is I get this wonderfully verbose :-( error message :::ERROR at line 362701 I am wondering if there is some single quotes that may be messing it up or something like. What is the best way to do this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving DB from one box to another.
At 23:21 -0600 12/15/04, Randy Paries wrote: hello, I have built a new DB box. Orig db is mysqld Ver 3.23.58 New DB is mysqld Ver 4.0.20 I want to move the data from one box to another. I have a couple of really large tables with longtext fields. This data is a bunch of HTML My plan was to do a mysqldump dbname -h oldbox oldbox.sql then do a mysql dbname oldbox.sql The problem is I get this wonderfully verbose :-( error message :::ERROR at line 362701 It might help to specify at which point in this process the error occurs. During the dump? During the reload? I am wondering if there is some single quotes that may be messing it up or something like. What is the best way to do this? Thanks -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL load balancing...
Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
koi8r dbd::mysql
Hello!
redhat 2.3 as
DBD-mysql-2.9004
perl 5.8.0
MySQL-server-4.1.7-0.i386.rpm from mysql.org
[EMAIL PROTECTED] start]# cat /etc/my.cnf
[client]
default-character-set=koi8r
[mysqld]
default-character-set=koi8r
[mysql]
default-character-set = koi8r
From mysql command line all select's work ok, russian character's
returned in koi8. But from perl:
[EMAIL PROTECTED] start]# cat ./test.pl
#!/usr/bin/perl
use DBI;
my ($dsn) = DBI:mysql:partn_db:localhost;my ($user_name) = 'test';my
($password) = 'test';my ($dbh, $sth);my (@ary);
$dbh = DBI-connect ($dsn, $user_name, $password);
$sth = $dbh-prepare (SELECT name_rus FROM anket_deti_tbl;);
$sth - execute();
while (@ary=$sth-fetchrow_array()) { print (join (::,@ary));}
$sth-finish();$dbh-disconnect();
[EMAIL PROTECTED] start]# ./test.pl
123? [EMAIL PROTECTED] start]#
only returned. what's wrong?
sorry for my terrible english :)
--
Alexey A. Wasilyev | GazPriborAvtomatikaService | Web: www.gpas.ru
(845-2) 45-85-12 | Chief of IT Department | EMail: [EMAIL
PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
