RE: Increasing max_connections and table_cache on Solaris 8
Hi, Hm, using ulimit on my own Ultra Sparc w/ Solaris 8, I can raise ulimit to max. 1024. Probably not quite enough. More study will be required :-/ Martijn -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: donderdag 23 juni 2005 16:27 To: mysql@lists.mysql.com Subject: Re: Increasing max_connections and table_cache on Solaris 8 Hello. I don't have enough experience with Solaris.. But on most Unixes your could change the limit of file descriptors for process, not for whole system. See: http://dev.mysql.com/doc/mysql/en/not-enough-file-handles.html Martijn van den Burg [EMAIL PROTECTED] wrote: Hi, This applies to 4.1.10 on Solaris 8, with 1 GB of RAM.=20 Our internal customer wants to be able to make 1000 concurrent connections to our database, with a mix of MyISAM and InnoDB tables, but according to http://dev.mysql.com/doc/mysql/en/table-cache.html and an online Solaris reference (http://docsun.cites.uiuc.edu/sun_docs/C/solaris_9/SUNWaadm/SOLTUNEPAR AM REF/p44.html) I would need a table_cache of roughly 1000*2*3 =3D 6000 (assuming only MyISAM tables are used which need two file descriptors per table, and three tables per join). However, from experience I know that increasing table_cache from 64 to 256 will already result in 'Too many open files' errors and the database becoming unaccessible. To solve this, and actually allow 1000 connections, should I let the sysadmin increase the limit of open file descriptors on the Solaris box, or is there a setting in MySQL that I overlooked? Note: max_connections is set to 1000 in the my.cnf file, but when starting up, MySQL doesn't accept that value and decreases it to 246. Setting the variable to 1000 whilst MySQL is running /is/ accepted. Kind regards, -- Martijn ASML ITMS Application Support / Webcenter -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A question about the select count(*) performance and the InnoDB engine
Hi all I was reading the High Performance MySQL book (by O'Reilly) and there was mentioned that executing a count(*) is slower on the InnoDB engine compared to the MyISAM engine, because InnoDB tables do not keep track of the number of the records. I just wanted to know that if this performance degradation is a feature of all transactional engines (i.e. Oracle, DB2, PostgreSQL, ...) or it's just a weakness of the InnoDB engines? Best Regards, Behrang S. -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A question about the open source license of MySQL
Hi We've developed a J2EE application for one of our customers. Currently, the system uses SQLServer as the backend database. The databse system is not embedded with the J2EE application and we can safely switch to other RDBMSes. I wanted to know that if we change the RDBMS from SQLServer to MySQL, do we have to buy the commercial license or the open source license suffieces as we've not embedded/tied our application with the RDBMS? Best Regards, Behrang S. -- Behrang Saeedzadeh http://www.jroller.com/page/behrangsa Using Opera's revolutionary e-mail client -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Advanced Data Generator 1.5.1 released
Dear ladies and gentlemen, Upscene Productions is happy to announce a new version of the database developer tool: Advanced Data Generator (version 1.5.1) A fast test-data generator tool that comes with a library of real-life data, can generate data to your database, SQL script or CSV files, many filling options, presets and much more. This new release consists of four versions: - Pro: ADO and ODBC connectivity - InterBase Edition - Firebird Edition - MySQL Edition More info and a 30-day trial version on www.upscene.com Pricing information available on www.upscene.com/purchase.htm#adg Recent changes: V1.5.1 - - Fixed: Running multiple projects returned an Access Violation - Fixed: Creating a custom SQL while the Project Options page was active resulted in a cannot focus disabled or invisible window error - Fixed: Setting fill with to Referential Link for Custom SQL-items showed properties for value from list - Fixed: Boolean data in INSERT scripts was quoted - Fixed: Some changes to datetime/date/time data generation - Fixed: Setting fill with to Link to Generator not supported in CSV files - Fixed: Setting fill with to Link to Generator would fail for INSERT scripts - Fixed: ADO: MS Access via ADO could fail on non-string fields due to wrong parameter type being reported With regards, Martijn Tonies Upscene Productions - Database Tools for Developers http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about the open source license of MySQL
Hello. Complete information about licensing policy you can receive from [EMAIL PROTECTED] Behrang Saeedzadeh [EMAIL PROTECTED] wrote: Hi We've developed a J2EE application for one of our customers. Currently, the system uses SQLServer as the backend database. The databse system is not embedded with the J2EE application and we can safely switch to other RDBMSes. I wanted to know that if we change the RDBMS from SQLServer to MySQL, do we have to buy the commercial license or the open source license suffieces as we've not embedded/tied our application with the RDBMS? Best Regards, Behrang S. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dynamic insertion of date for LOAD INFILE
Hello. You could import data in the temporary table and then use a full power of INSERT... SELECT statement. For example: INSERT into t1 select col1,col2,curdate() from tmp_t1; David Perron [EMAIL PROTECTED] wrote: Im trying to get dynamically insert the current date into a LOAD FILE statement for some ETL automation, but Im having difficulty passing the string into the LOAD statement: This will not work. SELECT @Today:=3DCURDATE(); LOAD DATA LOCAL INFILE '/s3/data/[EMAIL PROTECTED]' INTO TABLE Data; Anyone ever try something similar? How can this be done? Thanks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about the select count(*) performance and the InnoDB engine
Selon Behrang Saeedzadeh [EMAIL PROTECTED]: Hi all I was reading the High Performance MySQL book (by O'Reilly) and there was mentioned that executing a count(*) is slower on the InnoDB engine compared to the MyISAM engine, because InnoDB tables do not keep track of the number of the records. I just wanted to know that if this performance degradation is a feature of all transactional engines (i.e. Oracle, DB2, PostgreSQL, ...) or it's just a weakness of the InnoDB engines? Best Regards, Behrang S. -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi Behrang, ORACLE : ** There are two ways to have count(*) in oracle : statistics not updated : SQL select count(*) from titi; COUNT(*) -- 655360 statistics updated : SQL select num_rows from user_tables where table_name='TITI'; NUM_ROWS -- 655360 Rows Row Source Operation --- --- 5 TABLE ACCESS FULL TITI -- -- First execution, statistics not updated -- select count(*) from titi call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch2 0.07 0.07 0 1000 0 1 --- -- -- -- -- -- -- total4 0.07 0.07 0 1000 0 1 -- -- First execution, with updated statistics -- select count(*) from titi call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch4 0.15 0.15 0 2000 0 2 --- -- -- -- -- -- -- total8 0.15 0.15 0 2000 0 2 Yoiu can see that time is the same (it's cumulated here : 2 executions) -- -- Access from data dictionary -- select num_rows from user_tables where table_name='TITI' call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.00 0.01 2 9 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch2 0.00 0.02 1 21 0 1 --- -- -- -- -- -- -- total4 0.00 0.04 3 30 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 5 Rows Row Source Operation --- --- 1 NESTED LOOPS 1 NESTED LOOPS OUTER 1NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS 1TABLE ACCESS BY INDEX ROWID OBJ$ 1 INDEX RANGE SCAN I_OBJ2 (object id 37) 1TABLE ACCESS CLUSTER TAB$ 1 INDEX UNIQUE SCAN I_OBJ# (object id 3) 1 TABLE ACCESS BY INDEX ROWID OBJ$ 1INDEX UNIQUE SCAN I_OBJ1 (object id 36) 0 INDEX UNIQUE SCAN I_OBJ1 (object id 36) 1 TABLE ACCESS CLUSTER USER$ 1 INDEX UNIQUE SCAN I_USER# (object id 11) 1TABLE ACCESS CLUSTER SEG$ 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (object id 9) 1 TABLE ACCESS CLUSTER TS$ 1INDEX UNIQUE SCAN I_TS# (object id 7) DB2 : ** This is the same. TBSCAN if select count(*), but there are statistics based count(*). For example,with REORGCHK (runstats ...), you have count(*) without scanning the table : SCHEMANAME CARDOVNPFP ACTBLKTSIZE F1 F2 F3 REORG Table : MFATENE.STAFF MFATENE STAFF 35 0 1 1 - 1575 0 - 100 --- LOOK At the column CARD. I will not explain the same thing for sqlserver, sybase ..., but when your RDBMs have a data dictionnary, you don't need to execute count(*) :o) WITH Updated statistics of course. With information_schema in 5.x and higher, innodb will act as
Re: A question about the select count(*) performance and the InnoDB engine
Mathias, Thanks a lot! I will not explain the same thing for sqlserver, sybase ..., but when your RDBMs have a data dictionnary, you don't need to execute count(*) :o) WITH Updated statistics of course. I'm a little bit confused here. Why the count(*) is not transformed to a select from the data dictionary if this way is faster? And what's the difference between updated statistics and statistics not updated? With information_schema in 5.x and higher, innodb will act as it's done in all the other RDBMS. Hope that helps Sure! It helped by orders of magnitured more than I thought it can help ;-) :o) Mathias -- Behrang Saeedzadeh http://www.jroller.com/page/behrangsa Using Opera's revolutionary e-mail client -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about the select count(*) performance and the InnoDB engine
Selon Behrang Saeedzadeh [EMAIL PROTECTED]: Mathias, Thanks a lot! I will not explain the same thing for sqlserver, sybase ..., but when your RDBMs have a data dictionnary, you don't need to execute count(*) :o) WITH Updated statistics of course. I'm a little bit confused here. Why the count(*) is not transformed to a select from the data dictionary if this way is faster? And what's the difference between updated statistics and statistics not updated? With information_schema in 5.x and higher, innodb will act as it's done in all the other RDBMS. Hope that helps Sure! It helped by orders of magnitured more than I thought it can help ;-) :o) Mathias -- Behrang Saeedzadeh http://www.jroller.com/page/behrangsa Using Opera's revolutionary e-mail client Well, The information in data dictionnary are correct only just after updating them. imagine at 12h, you update statistics, num_rows=2000. At 12h05, you insert 1000 lignes and delete 500. At 12h10, you ask the data dictinary num_rows, it will give you 2000, even if they are 2500. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculate LONG/LAT from ZIP+4
There are about 70 million ZIP+4's in the USA. Buying a data file that you can import into your own database typically costs around $10,000 from the small handful of companies that actually compile the data! However, there are data broker companies such as Melissa Data (http://www.melissadata.com/GeoCoder/geodata.htm) that sell a COM object that you can query to get the let/lng of a ZIP+4. The Melissa Data solution costs just over $1,000. On Jun 26, 2005, at 8:29 PM, Jack Lauman wrote: A couple of months ago these was a discussion about ZIP code plotting on this list. Does anyone know how to calculate LONG/LAT coordinates from ZIP+4? Thanks, Jack -- 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: upgrade mysql 3.23.58 to 4.1
Hassan Schroeder wrote: mm wrote: There is a lot a pain here. I downloaded the rpm packages that's where the pain starts :-) If you're trying to run multiple versions of MySQL for pre-migration testing (or whatever), *don't use rpms* -- get the tar file of the binary, and just untar it as /usr/local/mysql-4.1.12 or whatever. It will be *so* much simpler to switch between the old and new, or run them simultaneously as needed. FWIW! Thanks a lot. Now I have 2 folders with 4.0 and 4.1 distribution. (/usr/local/mysql40, /usr/local/mysql41) They said to upgrade from 3.23 to 4.0 and then from 4.0 to 4.1 2.7 Installing MySQL on Other Unix-Like Systems ... 6. If you haven't installed MySQL before, you must create the MySQL grant tables: what about 'Else' ? --- Finally I did start the server and mysql 4.0 with my database. I did this by running my script mount-mysql [EMAIL PROTECTED] mircea]# ./mount-mysql [EMAIL PROTECTED] mircea]# Starting mysqld daemon with databases from /var/lib/mysql mount-mysql: mount --bind /mnt/120GB/mysql5 /var/lib/mysql cd /usr/local/mysql40 ; /usr/local/mysql40/bin/safe_mysqld [EMAIL PROTECTED] ~]$ /usr/local/mysql40/bin/mysql -u mircea -psome_pass -S/var/lib/mysql/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.0.24-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql Now, as I am yang in Linux, wish to understand what it is all about in: mysql_install_db mysql.server mysql_setpermission mysqld_safe safe_mysqld mysqlaccess mysqlbug I have to read about bash suppose. Thank you Hassan, Kishore, Gleb, and others for your help! mt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisam parameters in innodb db
Hello! Do You know if myISAM-specific settings have any impact on database performance, if the only MyISAM database is mysql? I know that there are some default settings and I wonder if for example memory buffers are allocated even if they are not used? Is it necessary to disable those settings? Regards Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculate LONG/LAT from ZIP+4
This one goes for $169, and you get longitudes and latitudes. http://www.buyzips.com/platinum-expanded.htm Regards, Mattias Håkansson - Original Message - From: Jack Lauman [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Monday, June 27, 2005 2:29 AM Subject: Calculate LONG/LAT from ZIP+4 A couple of months ago these was a discussion about ZIP code plotting on this list. Does anyone know how to calculate LONG/LAT coordinates from ZIP+4? Thanks, Jack -- 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]
create unique index
Hello, I am reading the docs, but I am slightly confused. I have a table with a varchar(50) column (not a primary column) where I do not want duplicates. It is a properties column, and I am getting duplicates inserted, which is causing problems in my display. An Oracle DBA that works with me suggested creating a unique index on the column. I am reading the docs here: http://dev.mysql.com/doc/mysql/en/create-index.html but I am not have a clear understanding of an index, so I am having trouble visualizing what I need to do. The column already exists. I am running 4.0.15 on a PC. The current column type is: MyISAM. I am not sure if that is proper or not. Its usage is for a web-site. Here is what I created a while back: CREATE TABLE PROPERTIES ( property varchar(50), value varchar(200), description varchar(200) ) TYPE=MyISAM; Also, if this is doable, can I also create an index across two columns? I have another situation where I need a combination of two columns to be unique. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: create unique index
From: Scottnbsp;PurcellDate: June 28 2005 3:36pm
Subject: create unique index
Hello,
I am reading the docs, but I am slightly confused.
I have a table with a varchar(50) column (not a primary column) where I =
do not want duplicates. It is a properties column, and I am getting =
duplicates inserted, which is causing problems in my display.
An Oracle DBA that works with me suggested creating a unique index on =
the column. I am reading the docs here:
http://dev.mysql.com/doc/mysql/en/create-index.html
but I am not have a clear understanding of an index, so I am having =
trouble visualizing what I need to do. The column already exists.=20
I am running 4.0.15 on a PC. The current column type is: MyISAM. I am =
not sure if that is proper or not. Its usage is for a web-site.
Here is what I created a while back:
CREATE TABLE PROPERTIES (
property varchar(50),
value varchar(200),
description varchar(200)
) TYPE=3DMyISAM;
Also, if this is doable, can I also create an index across two columns? =
I have another situation where I need a combination of two columns to be =
unique.
Thanks,
Scott
***
Hi scott,
I think that what you want to do is this :
mysql CREATE TABLE PROPERTIES (
-property varchar(50),
-value varchar(200),
-description varchar(200)
- ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql create unique index UNQ on PROPERTIES(property,value);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql insert into properties values('test1','Val of test1','test');
Query OK, 1 row affected (0.02 sec)
mysql insert into properties values('test1','Val of test1','test');
ERROR 1062 (23000): Duplicate entry 'test1-Val of test1' for key 1
mysql
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create unique index
Scott Purcell [EMAIL PROTECTED] wrote on 28/06/2005 14:36:37: Hello, I am reading the docs, but I am slightly confused. I have a table with a varchar(50) column (not a primary column) where I do not want duplicates. It is a properties column, and I am getting duplicates inserted, which is causing problems in my display. An Oracle DBA that works with me suggested creating a unique index on the column. I am reading the docs here: http://dev.mysql.com/doc/mysql/en/create-index.html but I am not have a clear understanding of an index, so I am having trouble visualizing what I need to do. The column already exists. Your DBA is correct: you need to add a UNIQUE index onto the column. MySQL has no way of knowing that you want a column to be unique unless you tell it so. And if you want it to be unique, you have to create an index so that MySQL can do a fast lookup to see if the column already exists before adding a new one. If you did not have an index, MySQL would have to search the entire table to check for duplicates on each insert - an intolerably slow operation. So you need a UNIQUE index. It is very easy to add an index to an existing table: ALTER TABLE properties ADD UNIQUE(property) ; but you must get rid of the duplicates first - it cannot create a UNIQUE index where duplicates exist. Also, when you make a column unique, you must consider what the software that inserts records is to do if it encounters a duplicate. You may need, for example, to convert your INSERT commands into REPLACE (see manual). I am running 4.0.15 on a PC. The current column type is: MyISAM. I am not sure if that is proper or not. Its usage is for a web-site. Here is what I created a while back: CREATE TABLE PROPERTIES ( property varchar(50), value varchar(200), description varchar(200) ) TYPE=MyISAM; Also, if this is doable, can I also create an index across two columns? I have another situation where I need a combination of two columns to be unique. Yes, you can - and it is the correct thing to do in this case. ALTER TABLE my_table ADD UNIQUE index_name (col_1, col_2) ; If, as you say, you do not have a clear visualisation of an index, I suggest that you should attempt to acquire one fast. Indexing is absolutely central to database programming and no-one should be writing database access software without understanding it. The concept is not very complex. You are probably familiar with indexes in the back of reference books: a list of words drawn from the text of the book is listed in alphabetic order, and each entry gives the page number(s) upon which you find those words. So that if you want to find a word, look it up in the index and then turn straight to the right pages, rather than having to read the entire book to find the reference you want. A database index is the same, except that it indexes every word in a particular column, and the page number it looks up is the databases hidden internal reference to the record which contains the indexed word (or number, or date, or ...). The marvellous thing is that you just tell MySQL you want an index, and MySQL magically creates and maintains the index, then uses it when appropriate to speed up database searches. Of course, maintaining an index takes extra CPU and disk power, so the database will not build an index unless you ask for it, which you should only do for columns used in WHERE clauses. But once created, the rest happens by magic (or rather, courtesy of the skills of the MySQL engineers). Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordinal number within a table
[Sorry for cross-posting.] This is in continuation with the above mentioned subject - I am trying to find the 'display data order' for the returned resultset. The following thread very well answers my question: http://lists.mysql.com/mysql/185626 a) SET @row=0; b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY empno; +-++---+ | row | ename | empno | +-++---+ | 1 | SMITH | 7369 | | 2 | ALLEN | 7499 | | 3 | WARD | 7521 | | 4 | JONES | 7566 | | 5 | MARTIN | 7654 | +-++---+ However, I am trying to use it in a perl script instead of from command line, and I am not sure how exactly to do it. I need to execute both statements a b together or else I get +-++---+ | row | ename | empno | +-++---+ |NULL | SMITH | 7369 | |NULL | ALLEN | 7499 | |NULL | WARD | 7521 | |NULL | JONES | 7566 | |NULL | MARTIN | 7654 | +-++---+ How can I execute both (a) and (b) in my perl script? Thanks for any help. Nishi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm unsubscribe to mysql@lists.mysql.com
On Tuesday 28 Jun 2005 15:19, [EMAIL PROTECTED] wrote: To confirm that you would like [EMAIL PROTECTED] removed from the mysql mailing list, please click on the following link: http://lists.mysql.com/u/mysql/42c15c7a3623a14d/partners= symbulos.com This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. We haven't checked whether your address is currently on the mailing list. To see what address you used to subscribe, look at the messages you are receiving from the mailing list. Each message has your address hidden inside its return path; for example, [EMAIL PROTECTED] receives messages with return path: mysql-return-number[EMAIL PROTECTED] --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: For help and a description of available commands, send a message to: [EMAIL PROTECTED] To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 22886 invoked by uid 48); 28 Jun 2005 14:19:37 - Date: 28 Jun 2005 14:19:37 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 213.122.26.154. -- symbulos - ethical services for your organisation website www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordinal number within a table
Selon Kapoor, Nishikant [EMAIL PROTECTED]:
[Sorry for cross-posting.]
This is in continuation with the above mentioned subject - I am trying to
find the 'display data order' for the returned resultset. The following
thread very well answers my question:
http://lists.mysql.com/mysql/185626
a) SET @row=0;
b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY
empno;
+-++---+
| row | ename | empno |
+-++---+
| 1 | SMITH | 7369 |
| 2 | ALLEN | 7499 |
| 3 | WARD | 7521 |
| 4 | JONES | 7566 |
| 5 | MARTIN | 7654 |
+-++---+
However, I am trying to use it in a perl script instead of from command line,
and I am not sure how exactly to do it. I need to execute both statements a
b together or else I get
+-++---+
| row | ename | empno |
+-++---+
|NULL | SMITH | 7369 |
|NULL | ALLEN | 7499 |
|NULL | WARD | 7521 |
|NULL | JONES | 7566 |
|NULL | MARTIN | 7654 |
+-++---+
How can I execute both (a) and (b) in my perl script?
Thanks for any help.
Nishi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi,
You dont need to use @row in perl,
just use :
$n=0;
while (fetch..) {
$n++;
print $n.$ename ...\n;
}
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ordinal number within a table
-Original Message-
This is in continuation with the above mentioned subject -
I am trying to
find the 'display data order' for the returned resultset.
The following
thread very well answers my question:
http://lists.mysql.com/mysql/185626
a) SET @row=0;
b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp
ORDER BY empno;
+-++---+
| row | ename | empno |
+-++---+
| 1 | SMITH | 7369 |
| 2 | ALLEN | 7499 |
| 3 | WARD | 7521 |
| 4 | JONES | 7566 |
| 5 | MARTIN | 7654 |
+-++---+
However, I am trying to use it in a perl script instead of
from command line,
and I am not sure how exactly to do it. I need to execute
both statements a
b together or else I get
+-++---+
| row | ename | empno |
+-++---+
|NULL | SMITH | 7369 |
|NULL | ALLEN | 7499 |
|NULL | WARD | 7521 |
|NULL | JONES | 7566 |
|NULL | MARTIN | 7654 |
+-++---+
How can I execute both (a) and (b) in my perl script?
Thanks for any help.
Nishi
Hi,
You dont need to use @row in perl,
just use :
$n=0;
while (fetch..) {
$n++;
print $n.$ename ...\n;
}
Mathias
I could, but I am assigning the entire resultset in one shot to another
construct as follows:
my $str = SELECT [EMAIL PROTECTED]:[EMAIL PROTECTED] as row, fields FROM
tables WHERE ...;
my $sth = $conn-prepare($st);
$sth-execute();
return $sth-fetchall_arrayref( {} );
Thanks,
-Nishi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculate LONG/LAT from ZIP+4
A lot cheaper here: http://www.zipwise.com/database-download-now.php On Jun 28, 2005, at 5:30 AM, Mattias Håkansson wrote: This one goes for $169, and you get longitudes and latitudes. http://www.buyzips.com/platinum-expanded.htm Regards, Mattias Håkansson - Original Message - From: Jack Lauman [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Monday, June 27, 2005 2:29 AM Subject: Calculate LONG/LAT from ZIP+4 A couple of months ago these was a discussion about ZIP code plotting on this list. Does anyone know how to calculate LONG/LAT coordinates from ZIP+4? Thanks, Jack -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculate LONG/LAT from ZIP+4
http://www.buyzips.com/platinum-expanded.htm This one also says it's only updated every 6 months. Ouch!! Another reason I recommend Zipwise instead. Cheaper and fresher data: http://www.zipwise.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Preventing duplicates with load data
Hello Mike, On 27 Jun 05, mos wrote to mySQL list: How can I prevent duplicate entries when I fill the data base with load data? I tried ignore, but that has no effect. m Ignore/Replace will only work on Unique keys and I bet your key is m not unique. If you make it unique, then Ignore will keep the m existing value, or Replace will replace the existing row with the m new row. You're good at betting :) The next question would of course be: how do I create a unique key, but somebody else already asked that and got a clear answer. Thank you very much. Regards, Hans. jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about the select count(*) performance and the InnoDB engine
Mathias [EMAIL PROTECTED] wrote on 06/28/2005 06:13:08 AM: Selon Behrang Saeedzadeh [EMAIL PROTECTED]: Mathias, Thanks a lot! I will not explain the same thing for sqlserver, sybase ..., but when your RDBMs have a data dictionnary, you don't need to execute count(*) :o) WITH Updated statistics of course. I'm a little bit confused here. Why the count(*) is not transformed to a select from the data dictionary if this way is faster? And what's the difference between updated statistics and statistics not updated? With information_schema in 5.x and higher, innodb will act as it's done in all the other RDBMS. Hope that helps Sure! It helped by orders of magnitured more than I thought it can help ;-) :o) Mathias -- Behrang Saeedzadeh http://www.jroller.com/page/behrangsa Using Opera's revolutionary e-mail client Well, The information in data dictionnary are correct only just after updating them. imagine at 12h, you update statistics, num_rows=2000. At 12h05, you insert 1000 lignes and delete 500. At 12h10, you ask the data dictinary num_rows, it will give you 2000, even if they are 2500. Hope that helps :o) Mathias Mathias, COUNT(*) is not slow in InnoDB due to a lack of statistics. It's due to the fact that for any user the value of COUNT(*) can be completely different than for any other user. Assume for a moment that there is a table stored in InnoDB that has 1000 records in it. UserA starts a transaction that adds 200 records and changes 50. UserB also starts a transaction and adds 500 records of his own. For the rest of this example, both transactions remain pending. Physically, the database now contains 1000 (original) + 200 (added by UserA) + 50 (changes pending from UserA) + 500 (added by UserB) = 1750 total records. However, if UserA performs a COUNT(*) query, they would only be able to see the 1200 records visible within their transaction. UserB will only be able to count 1500 records for the same reason. The slowness of performing a COUNT(*) query is caused by the need to individually evaluate all 1750 records to see if the user that asked to count them should actually know about them. Unless the engine is changed to maintain a separate set of table statistics for each user there won't be any way to just look up the number because the record count can (and usually will) be different for each user. After both transactions commit, the database will only have 1700 records (total) as the 50 pending updates, from UserA's transaction, will have overwritten the 50 original records. Does that help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: myisam parameters in innodb db
Hello. Use skip-innodb, this should prevent MySQL from InnoDB initialization. Remigiusz Soko$owski [EMAIL PROTECTED] wrote: Hello! Do You know if myISAM-specific settings have any impact on database performance, if the only MyISAM database is mysql? I know that there are some default settings and I wonder if for example memory buffers are allocated even if they are not used? Is it necessary to disable those settings? Regards Remigiusz -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ordinal number within a table
Selon Kapoor, Nishikant [EMAIL PROTECTED]:
-Original Message-
This is in continuation with the above mentioned subject -
I am trying to
find the 'display data order' for the returned resultset.
The following
thread very well answers my question:
http://lists.mysql.com/mysql/185626
a) SET @row=0;
b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp
ORDER BY empno;
+-++---+
| row | ename | empno |
+-++---+
| 1 | SMITH | 7369 |
| 2 | ALLEN | 7499 |
| 3 | WARD | 7521 |
| 4 | JONES | 7566 |
| 5 | MARTIN | 7654 |
+-++---+
However, I am trying to use it in a perl script instead of
from command line,
and I am not sure how exactly to do it. I need to execute
both statements a
b together or else I get
+-++---+
| row | ename | empno |
+-++---+
|NULL | SMITH | 7369 |
|NULL | ALLEN | 7499 |
|NULL | WARD | 7521 |
|NULL | JONES | 7566 |
|NULL | MARTIN | 7654 |
+-++---+
How can I execute both (a) and (b) in my perl script?
Thanks for any help.
Nishi
Hi,
You dont need to use @row in perl,
just use :
$n=0;
while (fetch..) {
$n++;
print $n.$ename ...\n;
}
Mathias
I could, but I am assigning the entire resultset in one shot to another
construct as follows:
my $str = SELECT [EMAIL PROTECTED]:[EMAIL PROTECTED] as row, fields FROM
tables WHERE ...;
my $sth = $conn-prepare($st);
$sth-execute();
return $sth-fetchall_arrayref( {} );
Thanks,
-Nishi
Then alter your table to add an auto_increment column, update it and play your
query without @row.
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about the select count(*) performance and the InnoDB engine
Selon [EMAIL PROTECTED]: Mathias [EMAIL PROTECTED] wrote on 06/28/2005 06:13:08 AM: Selon Behrang Saeedzadeh [EMAIL PROTECTED]: Mathias, Thanks a lot! I will not explain the same thing for sqlserver, sybase ..., but when your RDBMs have a data dictionnary, you don't need to execute count(*) :o) WITH Updated statistics of course. I'm a little bit confused here. Why the count(*) is not transformed to a select from the data dictionary if this way is faster? And what's the difference between updated statistics and statistics not updated? With information_schema in 5.x and higher, innodb will act as it's done in all the other RDBMS. Hope that helps Sure! It helped by orders of magnitured more than I thought it can help ;-) :o) Mathias -- Behrang Saeedzadeh http://www.jroller.com/page/behrangsa Using Opera's revolutionary e-mail client Well, The information in data dictionnary are correct only just after updating them. imagine at 12h, you update statistics, num_rows=2000. At 12h05, you insert 1000 lignes and delete 500. At 12h10, you ask the data dictinary num_rows, it will give you 2000, even if they are 2500. Hope that helps :o) Mathias Mathias, COUNT(*) is not slow in InnoDB due to a lack of statistics. It's due to the fact that for any user the value of COUNT(*) can be completely different than for any other user. Assume for a moment that there is a table stored in InnoDB that has 1000 records in it. UserA starts a transaction that adds 200 records and changes 50. UserB also starts a transaction and adds 500 records of his own. For the rest of this example, both transactions remain pending. Physically, the database now contains 1000 (original) + 200 (added by UserA) + 50 (changes pending from UserA) + 500 (added by UserB) = 1750 total records. However, if UserA performs a COUNT(*) query, they would only be able to see the 1200 records visible within their transaction. UserB will only be able to count 1500 records for the same reason. The slowness of performing a COUNT(*) query is caused by the need to individually evaluate all 1750 records to see if the user that asked to count them should actually know about them. Unless the engine is changed to maintain a separate set of table statistics for each user there won't be any way to just look up the number because the record count can (and usually will) be different for each user. After both transactions commit, the database will only have 1700 records (total) as the 50 pending updates, from UserA's transaction, will have overwritten the 50 original records. Does that help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks Shawn, but i'm not speaking about data consistency during transaction and isolation levels. I spoke about what is seen in the data dictionary as num_rows an why it can not be used even it's quite faster. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about the select count(*) performance and the InnoDB engine
Mathias [EMAIL PROTECTED] wrote on 06/28/2005 01:11:59 PM: Selon [EMAIL PROTECTED]: snip Thanks Shawn, but i'm not speaking about data consistency during transaction and isolation levels. I spoke about what is seen in the data dictionary as num_rows an why it can not be used even it's quite faster. Hope that helps :o) Mathias And I was trying to explain why there is not a number IN the data dictionary that represents row count. Unless a separate dictionary is maintained FOR EACH TRANSACTION, the record counts will be wrong. The record counts determined by SELECT COUNT(*) are *per transaction* so the only way to do a record count is by checking each row (pending or not) against cross-transaction isolation. This has everything to do with the row-level locking built into InnoDB and unless they enhance the engine to maintain a list of table statistics (I think this is part of what you are calling the dictionary) for each transaction, there can't be a rapid lookup of the row count. Right now I don't see that as high on their priorities. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: A question about the select count(*) performance and the InnoDB engine
Selon [EMAIL PROTECTED]: Mathias [EMAIL PROTECTED] wrote on 06/28/2005 01:11:59 PM: Selon [EMAIL PROTECTED]: snip Thanks Shawn, but i'm not speaking about data consistency during transaction and isolation levels. I spoke about what is seen in the data dictionary as num_rows an why it can not be used even it's quite faster. Hope that helps :o) Mathias And I was trying to explain why there is not a number IN the data dictionary that represents row count. Unless a separate dictionary is maintained FOR EACH TRANSACTION, the record counts will be wrong. The record counts determined by SELECT COUNT(*) are *per transaction* so the only way to do a record count is by checking each row (pending or not) against cross-transaction isolation. This has everything to do with the row-level locking built into InnoDB and unless they enhance the engine to maintain a list of table statistics (I think this is part of what you are calling the dictionary) for each transaction, there can't be a rapid lookup of the row count. Right now I don't see that as high on their priorities. Shawn Green Database Administrator Unimin Corporation - Spruce Pine What i call data dictionary is this : mysql use information_schema; mysql select table_name,table_rows from tables; +---++ | table_name| table_rows | +---++ | SCHEMATA | NULL | | TABLES| NULL | | COLUMNS | NULL | | CHARACTER_SETS| NULL | | COLLATIONS| NULL | | COLLATION_CHARACTER_SET_APPLICABILITY | NULL | | ROUTINES | NULL | | STATISTICS| NULL | | VIEWS | NULL | | USER_PRIVILEGES | NULL | | SCHEMA_PRIVILEGES | NULL | | TABLE_PRIVILEGES | NULL | | COLUMN_PRIVILEGES | NULL | | TABLE_CONSTRAINTS | NULL | | KEY_COLUMN_USAGE | NULL | | columns_priv | 0 | | db| 0 | | func | 0 | | help_category | 29 | | help_keyword |325 | | help_relation |548 | | help_topic|405 | | host | 0 | | proc | 0 | | procs_priv| 0 | | tables_priv | 0 | | time_zone | 0 | | time_zone_leap_second | 0 | | time_zone_name| 0 | | time_zone_transition | 0 | | time_zone_transition_type | 0 | | user | 1 | +---++ 32 rows in set (0.06 sec) mysql create table test.test1(a int); Query OK, 0 rows affected (0.08 sec) mysql insert into test.test1 values(1); Query OK, 1 row affected (0.01 sec) mysql insert into test.test1 values(2); Query OK, 1 row affected (0.01 sec) mysql select table_name,table_rows from tables; +---++ | table_name| table_rows | +---++ | SCHEMATA | NULL | | TABLES| NULL | | COLUMNS | NULL | | CHARACTER_SETS| NULL | | COLLATIONS| NULL | | COLLATION_CHARACTER_SET_APPLICABILITY | NULL | | ROUTINES | NULL | | STATISTICS| NULL | | VIEWS | NULL | | USER_PRIVILEGES | NULL | | SCHEMA_PRIVILEGES | NULL | | TABLE_PRIVILEGES | NULL | | COLUMN_PRIVILEGES | NULL | | TABLE_CONSTRAINTS | NULL | | KEY_COLUMN_USAGE | NULL | | columns_priv | 0 | | db| 0 | | func | 0 | | help_category | 29 | | help_keyword |325 | | help_relation |548 | | help_topic|405 | | host
Re: Calculate LONG/LAT from ZIP+4
One compromise between the large 5 digit zips and the 9-digit zip+4's is carrier route. There are about 600,000 carrier routes in the USA each denoted by the 5-digit zip and the carrier route, for example 34685-R036. 600,000 is certainly more manageable than 70,000,000 zip+4's. Does anyone know where you can buy USA carrier route lat/lng data? -Kieran Blog: http://webobjects.webhop.org/ On Jun 28, 2005, at 11:53 AM, Brian Dunning wrote: http://www.buyzips.com/platinum-expanded.htm This one also says it's only updated every 6 months. Ouch!! Another reason I recommend Zipwise instead. Cheaper and fresher data: http://www.zipwise.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: Calculate LONG/LAT from ZIP+4
The USPS has been de-emphasizing the usage of carrier-routes for several years now. They will eventually phase them out completely in lieu of other schemes, including enhanced line-of-travel (ELOT), etc. The pool of eligible carrier-routes decreases every month (from the mailer's perspective). Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Kieran Kelleher [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 28, 2005 16:21 To: Brian Dunning Cc: mysql@lists.mysql.com Subject: Re: Calculate LONG/LAT from ZIP+4 One compromise between the large 5 digit zips and the 9-digit zip+4's is carrier route. There are about 600,000 carrier routes in the USA each denoted by the 5-digit zip and the carrier route, for example 34685-R036. 600,000 is certainly more manageable than 70,000,000 zip+4's. Does anyone know where you can buy USA carrier route lat/lng data? -Kieran Blog: http://webobjects.webhop.org/ On Jun 28, 2005, at 11:53 AM, Brian Dunning wrote: http://www.buyzips.com/platinum-expanded.htm This one also says it's only updated every 6 months. Ouch!! Another reason I recommend Zipwise instead. Cheaper and fresher data: http://www.zipwise.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to edit part of a field?
Hi all - I have an urgent need to update several million records. There is a URL stored in a MySQL 'text' field. Many of the records contain 1234 like this: http://www.domain.com?etc=etcarg=1234etc=etc Any occurence of 1234 has to be changed to 5678 like this: http://www.domain.com?etc=etcarg=5678etc=etc ...without changing the rest of the string. I'm hoping it's possible to make this update to the entire table with a single SQL statement If so I have no idea how to create it. Any help appreciated. :) - Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to edit part of a field?
Selon Brian Dunning [EMAIL PROTECTED]: Hi all - I have an urgent need to update several million records. There is a URL stored in a MySQL 'text' field. Many of the records contain 1234 like this: http://www.domain.com?etc=etcarg=1234etc=etc Any occurence of 1234 has to be changed to 5678 like this: http://www.domain.com?etc=etcarg=5678etc=etc ...without changing the rest of the string. I'm hoping it's possible to make this update to the entire table with a single SQL statement If so I have no idea how to create it. Any help appreciated. :) - Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi, update TABLE set field=replace(field,'=1234','=5678'); that's it. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to edit part of a field?
On 28/06/05, Brian Dunning [EMAIL PROTECTED] wrote: Hi all - I have an urgent need to update several million records. There is a URL stored in a MySQL 'text' field. Many of the records contain 1234 like this: http://www.domain.com?etc=etcarg=1234etc=etc Any occurence of 1234 has to be changed to 5678 like this: http://www.domain.com?etc=etcarg=5678etc=etc ...without changing the rest of the string. I'm hoping it's possible to make this update to the entire table with a single SQL statement If so I have no idea how to create it. Any help appreciated. :) I think the replace() function should do it: update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]'); I use it quite a lot - lifesaving-function :) Hope this helps, -- Will -- The Corridor of Uncertainty -- -- http://www.cricket.mailliw.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to edit part of a field?
Wow - so easy! What a dork. Thanks guys. :) :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining tables - restricting selected records
This must have come up before, but I've not found it using a google search. I have two tables customer and purchases customer: customerID customerName purchases: purchaseID customerID purchaseDate purchaseValue Is it possible in MySQL to join the tables so I only get the value of the latest purchase? Or is this something that's better done in PHP, say select all my customers and then one at a time do a query to select the value of their last purchase? Thanks! Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining tables - restricting selected records
Selon Russell Horn [EMAIL PROTECTED]: This must have come up before, but I've not found it using a google search. I have two tables customer and purchases customer: customerID customerName purchases: purchaseID customerID purchaseDate purchaseValue Is it possible in MySQL to join the tables so I only get the value of the latest purchase? Or is this something that's better done in PHP, say select all my customers and then one at a time do a query to select the value of their last purchase? Thanks! Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi , join the tables with max(purshase_date) in the select,an dof course group by customer_id Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIke
How to make this work? field like '%DATA_FORMAT(now(), '%m%d%y') %' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIke
Jerry Swanson wrote:
How to make this work?
field like '%DATA_FORMAT(now(), '%m%d%y') %'
field LIKE CONCAT('%',DATE_FORMAT(now(), '%m%d%y'),'%');
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining tables - restricting selected records
Mathias wrote: Selon Russell Horn [EMAIL PROTECTED]: This must have come up before, but I've not found it using a google search. I have two tables customer and purchases customer: customerID customerName purchases: purchaseID customerID purchaseDate purchaseValue Is it possible in MySQL to join the tables so I only get the value of the latest purchase? Or is this something that's better done in PHP, say select all my customers and then one at a time do a query to select the value of their last purchase? Thanks! Russell. Hi , join the tables with max(purchase_date) in the select,and of course group by customer_id Hope that helps :o) Mathias Join with MAX(purchase_date)? Do you mean in a subquery? If so, that requires 4.1. This is a FAQ. Three solutions are given in the manual, a subquery solution for 4.1+, a temporary table solution for all versions, and a trick. http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error importing from mysqldump output
Hi all. I'm testing out mysql-5.0.7. I dumped a database from 5.0.4 with the command: mysqldump --opt DB_NAME DB_NAME.sql -p Now I'm importing with: mysql DB_NAME DB_NAME.sql -p The data has a field which has some quotes in it. The field looks like this ( all quotes included ): '' '' ie 2x single quotes, a space, and 2x single quotes. Don't ask me why. I didn't put it there. Anyway, mysqldump has packaged this field as follows: '\'\' \'\'' I'll put it into context inside an SQL statement: insert into some_table ( some_field ) values ( '\'\' \'\'' ); When I mysql hits this line, I get: ERROR at line 895: Unknown command '\''. The line looks properly escaped to me. Should I file a bug report? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error importing from mysqldump output
Daniel Kasak wrote: The data has a field which has some quotes in it. The field looks like this ( all quotes included ): '' '' ie 2x single quotes, a space, and 2x single quotes. Don't ask me why. I didn't put it there. Anyway, mysqldump has packaged this field as follows: '\'\' \'\'' Update. Perhaps this is 2 bugs in 1. I've found a LOT of this sort of thing in the dump file. It seems that every single quote that's encountered is represented: \'\'...instead of just: \' But still, mysql should simply import 2 quotes where there should have been one, right? I don't know. I'm starting to get confused. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordinal number within a table
Kapoor, Nishikant wrote:
[Sorry for cross-posting.]
This is in continuation with the above mentioned subject - I am trying to
find the 'display data order' for the returned resultset. The following
thread very well answers my question:
http://lists.mysql.com/mysql/185626
a) SET @row=0;
b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY
empno;
+-++---+
| row | ename | empno |
+-++---+
| 1 | SMITH | 7369 |
| 2 | ALLEN | 7499 |
| 3 | WARD | 7521 |
| 4 | JONES | 7566 |
| 5 | MARTIN | 7654 |
+-++---+
However, I am trying to use it in a perl script instead of from command
line, and I am not sure how exactly to do it. I need to execute both
statements a b together ...
You cannot execute them together. You must execute them one at a time, in the
same connection.
... or else I get
+-++---+
| row | ename | empno |
+-++---+
|NULL | SMITH | 7369 |
|NULL | ALLEN | 7499 |
|NULL | WARD | 7521 |
|NULL | JONES | 7566 |
|NULL | MARTIN | 7654 |
+-++---+
If you are getting this, you've made a mistake in your perl code. It's hard
to say what, though, as you haven't shown us your code.
Hmmm. User variables are connection specific. Are you making the mistake of
opening and closing a connection for each query? That's unneccessary, and it
adds a lot of overhead.
How can I execute both (a) and (b) in my perl script?
The same way you would execute any two statements, one at a time. Something
like:
$conn-do('SET @row=0');
my $sql = 'SELECT @row:[EMAIL PROTECTED] as row, fields FROM tables WHERE
...';
my $sth = $conn-prepare($sql);
$sth-execute();
return $sth-fetchall_arrayref( {} );
Thanks for any help.
Nishi
Mathias wrote:
Hi,
You dont need to use @row in perl,
just use :
$n=0;
while (fetch..) {
$n++;
print $n.$ename ...\n;
}
That would work.
Kapoor, Nishikant wrote:
I could, but I am assigning the entire resultset in one shot to another
construct as follows:
my $str = SELECT [EMAIL PROTECTED]:[EMAIL PROTECTED] as row, fields FROM
tables WHERE ...;
my $sth = $conn-prepare($st);
$sth-execute();
return $sth-fetchall_arrayref( {} );
Thanks,
-Nishi
You are returning an arrayref! One row in your results equals one row in your
array -- in the same order! Arrays are indexed, so display data order is
already built into your array. Display position = array position + 1. Why do
you need a redundant field in each row?
Harald Fuchs wrote:
Just change the last line to
my $n = 0;
return [ map { [ ++$n, @$_ ] } @{$sth-fetchall_arrayref} ];
What's the problem?
Are you sure? I get Can't coerce array into hash at
Mathias wrote:
Then alter your table to add an auto_increment column, update it and play
your query without @row.
Mathias
No, no, no! This is what we call using a shotgun to kill a gnat. It also
doesn't yield the order of the query results.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error importing from mysqldump output
Daniel Kasak wrote: Hi all. I'm testing out mysql-5.0.7. I dumped a database from 5.0.4 with the command: mysqldump --opt DB_NAME DB_NAME.sql -p Now I'm importing with: mysql DB_NAME DB_NAME.sql -p The data has a field which has some quotes in it. The field looks like this ( all quotes included ): '' '' ie 2x single quotes, a space, and 2x single quotes. Don't ask me why. I didn't put it there. Anyway, mysqldump has packaged this field as follows: '\'\' \'\'' I'll put it into context inside an SQL statement: insert into some_table ( some_field ) values ( '\'\' \'\'' ); When I mysql hits this line, I get: ERROR at line 895: Unknown command '\''. The line looks properly escaped to me. Should I file a bug report? Mysql reports the first thing it didn't understand, which isn't necessarily the first thing wrong. I note that it thought \' was a command, which implies it didn't see the preceding ' as the *start* of a string, which implies something went wrong earlier in the line. Of course, it's impossible to guess what. Could you post the entire line, and perhaps a line or two before? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error importing from mysqldump output
Michael Stassen wrote: Mysql reports the first thing it didn't understand, which isn't necessarily the first thing wrong. I note that it thought \' was a command, which implies it didn't see the preceding ' as the *start* of a string, which implies something went wrong earlier in the line. Of course, it's impossible to guess what. Could you post the entire line, and perhaps a line or two before? Unfortunately not, for a number of reasons. Firstly, in the meantime I've been doing search replace on the dump file to get rid of the duplicated \'\' stuff. Secondly, the dump file is HUGE, and I'm not really sure what part it had a problem with. I probably *should* be able to narrow it down to the table, by opening the dump file in a text editor and going to the line number mentioned in the error, but I've tried that a couple of times and whatever editor I use just locks up ... the file's far too big. I let gedit run for 15 minutes before finally killing it. Thirdly, if the error is where I think it is, the whole table has confidential stuff in it, and I'd have to mask every mention of companies / people. This wouldn't leave much. Anyway, my original search and replace seems to have been a stupid thing to do. Since I'm only testing things out ( trying to get stored procedures working ), I didn't think to keep a backup of the backup in case something happens. Frankly I'm not too concerned about it anyway. I'll start from scratch, importing the data via ODBC, make a new mysqldump file, and see if the problem persists. If it does, I'll be back, and I won't destroy the evidence this time... Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining tables - restricting selected records
Selon Michael Stassen [EMAIL PROTECTED]: Mathias wrote: Selon Russell Horn [EMAIL PROTECTED]: This must have come up before, but I've not found it using a google search. I have two tables customer and purchases customer: customerID customerName purchases: purchaseID customerID purchaseDate purchaseValue Is it possible in MySQL to join the tables so I only get the value of the latest purchase? Or is this something that's better done in PHP, say select all my customers and then one at a time do a query to select the value of their last purchase? Thanks! Russell. Hi , join the tables with max(purchase_date) in the select,and of course group by customer_id Hope that helps :o) Mathias Join with MAX(purchase_date)? Do you mean in a subquery? If so, that requires 4.1. This is a FAQ. Three solutions are given in the manual, a subquery solution for 4.1+, a temporary table solution for all versions, and a trick. http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] sorry if i wasn't clear. i mean not select puchase_date, but max(purshase_date), i.e. use having clause. The join field is certainly customerId, or There is not suffiscient info on tables. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordinal number within a table
Selon Michael Stassen [EMAIL PROTECTED]:
Kapoor, Nishikant wrote:
[Sorry for cross-posting.]
This is in continuation with the above mentioned subject - I am trying to
find the 'display data order' for the returned resultset. The following
thread very well answers my question:
http://lists.mysql.com/mysql/185626
a) SET @row=0;
b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY
empno;
+-++---+
| row | ename | empno |
+-++---+
| 1 | SMITH | 7369 |
| 2 | ALLEN | 7499 |
| 3 | WARD | 7521 |
| 4 | JONES | 7566 |
| 5 | MARTIN | 7654 |
+-++---+
However, I am trying to use it in a perl script instead of from command
line, and I am not sure how exactly to do it. I need to execute both
statements a b together ...
You cannot execute them together. You must execute them one at a time, in
the
same connection.
... or else I get
+-++---+
| row | ename | empno |
+-++---+
|NULL | SMITH | 7369 |
|NULL | ALLEN | 7499 |
|NULL | WARD | 7521 |
|NULL | JONES | 7566 |
|NULL | MARTIN | 7654 |
+-++---+
If you are getting this, you've made a mistake in your perl code. It's hard
to say what, though, as you haven't shown us your code.
Hmmm. User variables are connection specific. Are you making the mistake of
opening and closing a connection for each query? That's unneccessary, and it
adds a lot of overhead.
How can I execute both (a) and (b) in my perl script?
The same way you would execute any two statements, one at a time. Something
like:
$conn-do('SET @row=0');
my $sql = 'SELECT @row:[EMAIL PROTECTED] as row, fields FROM tables
WHERE ...';
my $sth = $conn-prepare($sql);
$sth-execute();
return $sth-fetchall_arrayref( {} );
Thanks for any help.
Nishi
Mathias wrote:
Hi,
You dont need to use @row in perl,
just use :
$n=0;
while (fetch..) {
$n++;
print $n.$ename ...\n;
}
That would work.
Kapoor, Nishikant wrote:
I could, but I am assigning the entire resultset in one shot to another
construct as follows:
my $str = SELECT [EMAIL PROTECTED]:[EMAIL PROTECTED] as row, fields
FROM tables WHERE
...;
my $sth = $conn-prepare($st);
$sth-execute();
return $sth-fetchall_arrayref( {} );
Thanks,
-Nishi
You are returning an arrayref! One row in your results equals one row in
your
array -- in the same order! Arrays are indexed, so display data order is
already built into your array. Display position = array position + 1. Why
do
you need a redundant field in each row?
Harald Fuchs wrote:
Just change the last line to
my $n = 0;
return [ map { [ ++$n, @$_ ] } @{$sth-fetchall_arrayref} ];
What's the problem?
Are you sure? I get Can't coerce array into hash at
Mathias wrote:
Then alter your table to add an auto_increment column, update it and play
your query without @row.
Mathias
No, no, no! This is what we call using a shotgun to kill a gnat. It also
doesn't yield the order of the query results.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No, no, no! This is what we call using a shotgun to kill a gnat. It also
doesn't yield the order of the query results.
sure that ${$ligne[$row]}{$n}:=$n with n perl operations can be faster !:)
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about field length for integer
[EMAIL PROTECTED] wrote: Eko Budiharto [EMAIL PROTECTED] wrote on 06/26/2005 11:02:30 AM: Hi, is there anyway that I can have more than 20 digits for integer (bigInt)? If not, what I can use for database index? BIGINT UNSIGNED can range from 0 to 18446744073709551615 (http://dev.mysql.com/doc/mysql/en/numeric-types.html) Are you actually saying that you have a database with more than 1.8e+19 records in it? I don't think you do. I think you are combining several pieces of information into something that looks like a number and it's exceeding the storage limits of even BIGINT. What you have is actually a good idea but you are physically limited by the capacity of the column types available. In this case if you cannot create all of your key values so that they look like numbers smaller than 18446744073709551615, it can't fit into a BIGINT UNSIGNED column. You do have some options: a) change the way you create your server keys so that they fit in the value allowed b) use a character-based column to store your server key values c) use some other value to identify your servers (IP address, for example) d) create a table of server keys: CREATE TABLE server ( ID int auto_increment , name varchar(25) not null , ip int unsigned , ... (any other fields you could define to describe this server) , PRIMARY KEY (ID) , UNIQUE(name) ) Then, refer to your servers using server.id instead of your composited key. e) ...? (I am sure there are more ideas from others on the list) To answer your literal question: No, MySQL cannot store integer values that contain more than 20 digits. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn, I believe character keys are slower than integer keys, so I'd go with d. Could you explain what you mean by good idea?I think combining several pieces of information into a single value is a bad idea. Each piece of information means something on its own. It answers some question. Inevitably, you will want to know the answer to a question about one or some of them indepently of the rest. I think if it answers a different question, it should go in a separate column. We get this all the time: My company makes widgets. Each widget is uniquely identified in the widget table by the primary key, wid, which looks like this, AAA123-xyz2756. The letters and numbers before the - are the part category and part number. The letters and numbers after the - are the plant code and production run. Now the boss wants a report of all the parts with numbers between 103 and 137 from production run 2334 at any plant. How do I do that? The answer is ugly and cannot use an index. It would have been so much simpler if the four pieces of information were each in their own columns. That answer could use an appropriate index. As a first pass, a better design would be CREATE TABLE widgets ( id INT UNSIGNED NOT NULL PRIMARY KEY, cat CHAR(3), part_num TINYINT UNSIGNED, plant CHAR(3), run INT UNSIGNED, UNIQUE (cat, part_num, plant_id, run) ); This is option d with the name broken into its separate components. It is trivial to have the output of a query combine the pieces so as to present the familiar name: SELECT CONCAT(cat, part_num, '-', plant, run) AS 'wid', ... Of course, this design can still be normalized. cat and plant should be replaced by cat_id and plant_id, for example, which point to the appropriate rows of the category and plant tables. But you get the idea. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining tables - restricting selected records
Mathias wrote: sorry if i wasn't clear. i mean not select puchase_date, but max(purchase_date), i.e. use having clause. The join field is certainly customerId, or There is not sufficient info on tables. Hope that helps :o) Mathias How would you do that with HAVING? I believe the subquery version would work something like this SELECT c.customerName, p1.customerID, p1.purchaseID, p1.purchaseDate, p1.purchaseValue FROM customer c JOIN purchases p1 ON c.customerID = p1.customerID WHERE p1.customerID = (SELECT MAX(p2.purchaseDate FROM purchases p2 WHERE p1.customerID = p2.customerID); As I said, that requires mysql 4.1+. With earlier versions, you use a temporary table, as described in the manual page I referenced. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisam parameters in innodb db
Gleb Paharenko wrote: Hello. Use skip-innodb, this should prevent MySQL from InnoDB initialization. Remigiusz Soko$owski [EMAIL PROTECTED] wrote: Hello! Do You know if myISAM-specific settings have any impact on database performance, if the only MyISAM database is mysql? I know that there are some default settings and I wonder if for example memory buffers are allocated even if they are not used? Is it necessary to disable those settings? Regards Remigiusz You've misunderstood. All his tables are InnoDB, except in the mysql db. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Issues on Debian-AMD64 - looking for ideas or help.
I'm compiling my mysql server from source. I have started noticing this as of 4.1.10 - in fact, I was running 4.1.12 and it's changed the LinuxThreads detection (since LT does not exist on amd64) - and 4.1.12 kept locking up after only a few minutes of uptime consistently. Once I figured this out, I rolled back to my 4.1.10 with a small patch to the configure script to force the calling of CFLAGS=$CFLAGS -DUSE_MUTEX_INSTEAD_OF_RW_LOCKS -DPTHREAD_ADAPTIVE_MUTEX_INITIALIZER_NP I'm wondering though - am I missing something here? What would be the best threading library to call (or command line switch when compiling mysql) on a single processor amd64 machine - as well as a dual processor (SMP) amd64 machine? I have both... and would like to ensure maximum performance on both! This is my existing configure line: ./configure --prefix=/usr/local/mysql --enable-assembler --without-debug --with-mysqld-user=mysql --without-innodb --without-isam --enable-thread-safe-client --without-extra-tools For example, right now i have 4 mysql servers running, and this is what my process list looks like. mysql 634 29.9 0.7 45676 16400 ? Sl Jun15 5517:59 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mys mysql 640 41.7 1.2 52836 26696 ? Sl Jun15 7693:42 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mys mysql 647 13.6 1.0 69316 21920 ? Sl Jun15 2513:04 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mys mysql 650 33.9 1.6 72804 33064 ? Sl Jun15 6240:30 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mys they're not all running that intensively, so i'm not sure why they're all trying to hog as much CPU as possible. (yes, they're all pointing to separate datadirs, socket files, port numbers, etc. - the config hasn't changed since a properly running version in the past) Can anyone shed some light - perhaps someone else who compiles from source on Debian-amd64 and has tweaked settings either in the my.cnf or on the configure line... I'm willing to take any suggestions! Or perhaps somebody knows how the debian-amd64 mysql apt package is compiled? I would normally try using it, but it has an oddball setup - requiring a new user to be added and some other things that I don't want to have to maintain - and my existing setup [besides for this resource issue] has been rock solid and great. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LocK Manager in MySql
Hi, I am a Computer Science student here at IIIT hybd and our team is going to implement a speculative lock manager in MySql 4.0 or higher. Can someone Plz tell if the mysql code shipped with FC2 has Two Phase Locking? or does any other version has it? or if any other commercial version has it and what might be the cost. I went through the Documentation but couldnt make out if the table level locking for ISAM , MyISAM tables and page level for BDB tables and similar stuff is the same as a Two Phase Locks. -- When the going gets tough only the tough gets going -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
