Re: fields separator
sed -r s/ +/\t/g infile.txt or perl -e s/\s+/\t/g and print $_.\\n\ while infile.txt -- Chris Wagner CBTS GE Aircraft Engines [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [mysql]Concurrency with inserts
The default engine is configurable: default-storage-engine=INNODB in my.ini. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 27, 2006 10:45 PM To: Henda Carvalho Cc: mysql@lists.mysql.com Subject: Re: [mysql]Concurrency with inserts In the last episode (Sep 28), Henda Carvalho said: One more question, What kind of tables does mysql create by default? innodb or myisam? The default is myisam. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
Section 3.6.2 of the 4.1 manual has this example using a subselect: SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); I use this basic syntax with max(date) alot. - Original Message - From: João Cândido de Souza Neto [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 28, 2006 8:39 AM Subject: Re: Need to find last price and date product was sold Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA 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: Mmultiple languages in the MySQL database
We have multiple Western languages in our UTF-8 data base, without a problem. Right now I'm working on adding Chinese data, but my major problem is reading back what's in there to find out if it went in correctly :( Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: krishna [mailto:[EMAIL PROTECTED] Sent: Thursday, September 28, 2006 1:36 AM To: mysql@lists.mysql.com Subject: Mmultiple languages in the MySQL database How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-databas e-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.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: Mmultiple languages in the MySQL database
On 9/28/06, Jerry Schwartz [EMAIL PROTECTED] wrote: We have multiple Western languages in our UTF-8 data base, without a problem. Right now I'm working on adding Chinese data, but my major problem is reading back what's in there to find out if it went in correctly :( One thing I've done in the past to verify this exact issue, is to simply run something like: # mysql -e select fields from table output.txt - making sure that the the output contains some non-ASCII characters - preferably non-Latin too. Then open output.txt in a browser and set the character encoding to UTF-8. [View-Character Encoding-UTF-8 in Firefox]. If the content renders properly [provided you've got the right fonts obviously], then you can be sure that the content went in properly. ~mm Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- Wrom: DULHPQQWOYIYZUNNYCGPKYLEJGDGVCJVTLBXFGGMEPYO Sent: Thursday, September 28, 2006 1:36 AM To: mysql@lists.mysql.com Subject: Mmultiple languages in the MySQL database How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-databas e-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.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]
RE: Mmultiple languages in the MySQL database
That's a good suggestion. As it happens, I have a web-based application that should display the data, but it doesn't. Without going into detail, all of the Chinese data is in one account and nothing shows in that account in the web application even though I can see the records with the MySQL client. Now I'm chasing that hare. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Michael Monaghan [mailto:[EMAIL PROTECTED] Sent: Thursday, September 28, 2006 9:58 AM To: Jerry Schwartz Cc: krishna; mysql@lists.mysql.com Subject: Re: Mmultiple languages in the MySQL database On 9/28/06, Jerry Schwartz [EMAIL PROTECTED] wrote: We have multiple Western languages in our UTF-8 data base, without a problem. Right now I'm working on adding Chinese data, but my major problem is reading back what's in there to find out if it went in correctly :( One thing I've done in the past to verify this exact issue, is to simply run something like: # mysql -e select fields from table output.txt - making sure that the the output contains some non-ASCII characters - preferably non-Latin too. Then open output.txt in a browser and set the character encoding to UTF-8. [View-Character Encoding-UTF-8 in Firefox]. If the content renders properly [provided you've got the right fonts obviously], then you can be sure that the content went in properly. ~mm Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- Wrom: DULHPQQWOYIYZUNNYCGPKYLEJGDGVCJVTLBXFGGMEPYO Sent: Thursday, September 28, 2006 1:36 AM To: mysql@lists.mysql.com Subject: Mmultiple languages in the MySQL database How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-databas e-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.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]
Re: Need to find last price and date product was sold
Mike, What I need to do is find the last price_sold for each product_code. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND t1.price_sold t2.price_sold WHERE t2.product_code IS NULL ORDER BY t1.product_code; There's a bit of discussion at http://www.artfulsoftware.com/queries.php#7/ PB - mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Requesting help with subquery
Thanks, again, for folks who suggested solutions to my problem. To help users searching the archives, I've pasted in a working solution at the end of this message. Also, I'd like to ask if there is a more efficient or better way of checking if the language version exist than the six lines I repeated 8 times below. Thanks, again. -Kevin =\ I originally wrote: I have a database of publications in different languages. main categories are organized into sub categories with baseitems of publications. Each baseitem can be printed in one or more of eight languages. My SQL query so far is: snip Working solution: [EMAIL PROTECTED]:~$ cat OrderDB-requested.sql SELECT m.title AS Main Category, s.title AS Sub Category, b.partno AS Part Number, (SELECT lv.title FROM langversion AS lv WHERE lv.langid = 1 # English = 1 AND b.baseitemid = lv.baseitemid ) as English Title, CONCAT( IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 1 # 1 = English AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'E', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 2 # 2 = French AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'F', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 3 # 3 = Spanish AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'S', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 4 # 4 = Portuguese AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'P', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 5 # 5 = Arabic AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'A', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 6 # 6 = Swahili AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'W', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 7 # 7 = Russian AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'R', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 8 # 8 = Turkish AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'T', '-') )AS Lang Avail FROM maincategory AS m JOIN subcategory AS s ON m.maincatid=s.maincatid JOIN baseitem AS b ON s.subcatid=b.subcatid WHERE b.available = Y ORDER BY m.title, s.title; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
making varchar field to act like numeric field
I am looking for any suggestions to this problem. I have a table with a varchar field. This field can hold textual or numeric data, but it is stored in a varchar field so the database sees it all as text. I need to be able to search and sort this field as if it were numeric. For example, here is some sample data 2.5 4 2 6 7 6.2 3.4 6 I need to be able query the table to get the rows within a certain range, for example, between 4 and 7: select * from table where field1=4 and field1=7 This doesn't work because the column is not a numeric data type. Is there anyway to dynamically cast the data to a numeric format so I can use MySQL's numeric sorting? I can't change the field's data type because it also needs to be able to hold textual data. Thank you for your help. -- Steve Musumeche CIO, Internet Retail Connection -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: making varchar field to act like numeric field
Hi Steve, Try select * from table where field1=4% and field1=7% instead. Sincerely, Raj Mehrotra [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 27, 2006 11:24 PM To: mysql@lists.mysql.com Subject: making varchar field to act like numeric field I am looking for any suggestions to this problem. I have a table with a varchar field. This field can hold textual or numeric data, but it is stored in a varchar field so the database sees it all as text. I need to be able to search and sort this field as if it were numeric. For example, here is some sample data 2.5 4 2 6 7 6.2 3.4 6 I need to be able query the table to get the rows within a certain range, for example, between 4 and 7: select * from table where field1=4 and field1=7 This doesn't work because the column is not a numeric data type. Is there anyway to dynamically cast the data to a numeric format so I can use MySQL's numeric sorting? I can't change the field's data type because it also needs to be able to hold textual data. Thank you for your help. -- Steve Musumeche CIO, Internet Retail Connection -- 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: making varchar field to act like numeric field
You can use CAST or CONVERT to see the data as a numeric type.
If the table is very big and you're going to be querying it
intensely, you might want to create a separate column to store the
numeric data.
mysql select cast('34' AS decimal);
+---+
| cast('34' AS decimal) |
+---+
| 34.00 |
+---+
1 row in set (0.00 sec)
mysql select cast('hi' AS decimal);
+---+
| cast('hi' AS decimal) |
+---+
| 0.00 |
+---+
1 row in set, 1 warning (0.00 sec)
Douglas Sims
[EMAIL PROTECTED]
On Sep 27, 2006, at 10:24 PM, [EMAIL PROTECTED] wrote:
I am looking for any suggestions to this problem. I have a table
with a
varchar field. This field can hold textual or numeric data, but it is
stored in a varchar field so the database sees it all as text.
I need to be able to search and sort this field as if it were numeric.
For example, here is some sample data
2.5
4
2
6
7
6.2
3.4
6
I need to be able query the table to get the rows within a certain
range,
for example, between 4 and 7:
select * from table where field1=4 and field1=7
This doesn't work because the column is not a numeric data type.
Is there
anyway to dynamically cast the data to a numeric format so I can use
MySQL's numeric sorting?
I can't change the field's data type because it also needs to be
able to
hold textual data. Thank you for your help.
--
Steve Musumeche
CIO, Internet Retail Connection
--
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: Need to find last price and date product was sold
Neat-o. I think that's much better than the query I proposed with the subselect. However, it doesn't give you price from the last sale of the product, instead it gives you highest price the product was sold for. Also, it can give you multiple rows for each product_code if there are multiple sales at the same price. Here is a small modification to Peter's query which will give you exactly one row for each product code showing the price at the last sale of that product. (Assuming you have a synthetic key, perhaps an autoincrement field, called id) Also, an index on the product_code field will help the speed of this query a lot. (I don't understand why the subselect query is still faster - I don't think it should be.) SQL is rather fun. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND (t1.date_sold t2.date_sold OR (t1.date_sold=t2.date_sold AND t1.idt2.id) WHERE t2.product_code IS NULL ORDER BY t1.product_code; Douglas Sims [EMAIL PROTECTED] On Sep 28, 2006, at 10:12 AM, Peter Brawley wrote: Mike, What I need to do is find the last price_sold for each product_code. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND t1.price_sold t2.price_sold WHERE t2.product_code IS NULL ORDER BY t1.product_code; There's a bit of discussion at http://www.artfulsoftware.com/ queries.php#7/ PB - mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- 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: making varchar field to act like numeric field
Hi Steve, Correction: Use: select * from table where field1 like '4%' or like '5%' or like '6%' or field1 like '7%'; Sincerely, Raj Mehrotra [EMAIL PROTECTED] -Original Message- From: Rajesh Mehrotra Sent: Thursday, September 28, 2006 11:42 AM To: '[EMAIL PROTECTED]'; mysql@lists.mysql.com Subject: RE: making varchar field to act like numeric field Hi Steve, Try select * from table where field1=4% and field1=7% instead. Sincerely, Raj Mehrotra [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 27, 2006 11:24 PM To: mysql@lists.mysql.com Subject: making varchar field to act like numeric field I am looking for any suggestions to this problem. I have a table with a varchar field. This field can hold textual or numeric data, but it is stored in a varchar field so the database sees it all as text. I need to be able to search and sort this field as if it were numeric. For example, here is some sample data 2.5 4 2 6 7 6.2 3.4 6 I need to be able query the table to get the rows within a certain range, for example, between 4 and 7: select * from table where field1=4 and field1=7 This doesn't work because the column is not a numeric data type. Is there anyway to dynamically cast the data to a numeric format so I can use MySQL's numeric sorting? I can't change the field's data type because it also needs to be able to hold textual data. Thank you for your help. -- Steve Musumeche CIO, Internet Retail Connection -- 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]
Partitioning to_hour
I know we can partition to_day using 5.1. Are there plans to implement range partitioning to_hour as well? I'm in need of this granularity. I'm currently partitioned to_day then sub partitioned x6 and split the Data and Indexes to different HDs for disk speed. Starting with an empty table, we were able to bulk insert 101 Million rows in about 2 hours. It then took the next 12 hours to load the next 125 Million rows... It's progressively getting slower, we're now at 22 minutes to add 15 rows. (Which I kind of expected, we Index 5 columns) Does anyone have any ideas? I've tried a bunch of things including killing the indexes, loading the data then rebuilding the index (horrible idea... Increased load to 45Min Plus for 15 rows). We're attempting to validate that MySQL can handle the load. I'm in a catch 22, I'd buy a support contract if I can prove MySQL can work. I can't prove MySQL can work without speaking to an engineer, which they won't do (and rightly so) without a contract. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql pushing data to client
Thanks for pointing that out, I'll go with James solution. David Godsey David Godsey wrote: I am looking for a way to write a client program that will wake up when there is new data in the database, much like replication. So instead of my client pulling the database on some fixed interval, I would like the mysql daemon to push the data to my client when there is new data. I assume this is possible given that it is done with replication. Does anybody have information on how to implement a client program like this? I'm using PHP, but I'm not opposed to using C or C++ for this kind of functionality. James Neff wrote: I'm doing something similar now and I just have my clients (a java app) periodically check a small table that indicates theres data ready for them. It's a simple SELECT count(*) ... that is fairly inexpensive and the does this once every 5 seconds. I don't know if there is a built-in solution for mysql but this gets the job done for me. Let me know if you find a better way to do this. David Godsey wrote: That is a good work around, I appreciate the idea. The optimal would be a client that listens on the mysql thread the pushes data to the database. But if there is no way to tap into that thread, the select count(*) would do. There must be a way to do it though, since it is done with replication. Except that it isn't. The Master writes to its local binlog. The slave pulls updates from the master periodically. See the manual for all the details http://dev.mysql.com/doc/refman/4.1/en/replication-intro.html. You could write a daemon to run on the mysql server, which would watch for local changes and push them to your client, but that sounds overly complex to me compared to James' solution. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where url = 'x' with url a TEXT field
Thanks.. the problem is I'm running MySQL 4.1.16 I might try FULLTEXT... On 9/27/06, Dan Buettner [EMAIL PROTECTED] wrote: You can create FULLTEXT indexes on text fields - but that may not be the best solution for your situation since you are querying for an exact match. In MySQL 5.0.3 and later, you can create VARCHAR columns of up to 65,535 character lengths: http://dev.mysql.com/doc/refman/5.0/en/char.html I seem to recall you were asking about storing URLs up to the practical limit, 2083 characters. I've never used a varchar column that long before; I'm not sure what the performance implications might be, or how effective a database index on a field that length would be. Still, worth a try. Could be that it will work smashingly. HTH, Dan On 9/27/06, Peter Van Dijck [EMAIL PROTECTED] wrote: Hi, since urls can be longer than 255 chars, I made the url field a TEXT field. The problem is, I can't make an index on it, so doing 'WHERE url='xxx'' becomes a very sloow query. Any ideas for solutions? Am I mistaken in the idea that I can't make an index on a TEXT field? Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where url = 'x' with url a TEXT field
Peter Van Dijck wrote: Thanks.. the problem is I'm running MySQL 4.1.16 I might try FULLTEXT... U can create normal indexes on text columns if u specify a prefix length. -- Chris Wagner CBTS GE Aircraft Engines [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dir /w mysql
How do I use windows command line to do the similiar following: dir *.txt /b /o:n 1 C:\Documents and Settings\ScottHam\Desktop\current.tmp 2nul into mysql with this table: mysql desc files; +---+-+--+-+---++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+---++ | ID| int(11) | NO | PRI | NULL | auto_increment | | fname | varchar(14) | NO | UNI | || | cdate | timestamp | YES | | CURRENT_TIMESTAMP || +---+-+--+-+---++ -- `Twas brillig, and the slithy toves Did gyre and gimble in the wabe: All mimsy were the borogoves, And the mome raths outgrabe.
update old id to new id query
Hi all, I have a pretty complex query going on... In 'transfertable' we have oldid and newid. The old id's are mapped to new id's. In table2 we have the id. To make things more interesting, the id isn't a primary key in table2, the primary key consists of 3 fields... We need to adjust table2 so that every id (which is the old id) is replaced with the newid according to 'transfertable'. I've been trying to do this, but I'm hitting the limits of my mysql wizardry.. any suggestions? I don't think we can just do update table2 set id = select newid from transfertable where oldid = id (that wouldn't work anyways?) I'm stuck, any help is very welcome! Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dir /w mysql
perl -e chomp and print \INSERT INTO FILES SET `fname` = '$_'\n while current.tmp -- Chris Wagner CBTS GE Aircraft Engines [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fields separator
At 7:19 + 9/27/06, [EMAIL PROTECTED] wrote: hi, everyone! I have a text file like this: 10:10:00 0 0 1 99 10:20:00 0 0 1 99 10:40:00 11 3 4 83 11:00:00 1 1 2 97 11:05:00 2 1 1 96 I need to load this file into a table, but I cannot figure out the syntax of LOAD DATA INFILE command. As you can see, the fields are separated by a variable number of blank spaces (not tabs). How can I do this? LOAD DATA doesn't allow for variable-width separator. You can preprocess you data to convert runs of spaces to a single space (or some other character that doesn't appear in your data), and the load the preprocessed result with LOAD DATA. -- 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: update old id to new id query
Peter, how about if you ran a query like this: SELECT CONCAT(UPDATE table2 SET id=, tr.newid, WHERE id=, t2.id, ;) FROM transfertable tr, table2 t2 WHERE tr.oldid = t2.id if you put the above query into a file id-update.sql, you could perhaps even do something like this: mysql id-update.sql | mysql I'd highly recommend running it against a test copy of your data first, but I think it ought to work. Dan On 9/28/06, Peter Van Dijck [EMAIL PROTECTED] wrote: Hi all, I have a pretty complex query going on... In 'transfertable' we have oldid and newid. The old id's are mapped to new id's. In table2 we have the id. To make things more interesting, the id isn't a primary key in table2, the primary key consists of 3 fields... We need to adjust table2 so that every id (which is the old id) is replaced with the newid according to 'transfertable'. I've been trying to do this, but I'm hitting the limits of my mysql wizardry.. any suggestions? I don't think we can just do update table2 set id = select newid from transfertable where oldid = id (that wouldn't work anyways?) I'm stuck, any help is very welcome! Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- 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]
Result codes for insert...on duplicate key update
Hi, I'm using DBI and a dsn with 'mysql_client_found_rows=0' appended. A normal update returns 0E0 if no data has changed. The update part of insert...on duplicate key update always returns 2, whether data has changed or otherwise, plus the timestamp column is not automatically updated. Is there any way to get a different result code for unchanged data? (And an updated timestamp?) I assume not, but have to ask. I'm using 4.1 but see that 5.0 and 5.1 manuals say the same. Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dir /w mysql
U can install perl for windows from ActiveState. U can also get sed for windows from Cygwin. These tools should be considered mandatory. Scott Hamm wrote: Sorry, I'm using Windows cmd shell, no perl, no linux stuff. :( -- Chris Wagner CBTS GE Aircraft Engines [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making varchar field to act like numeric field
Rajesh Mehrotra wrote: Hi Steve, Correction: Use: select * from table where field1 like '4%' or like '5%' or like '6%' or field1 like '7%'; This would not have the desired result as it would return things like 500 or 50 or 5,000,000 etc. You must use the cast as another reply suggested. However I'm unclear as to why you would store numeric data in a var char field. If at all possible it would be better to store it in a numeric field. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making varchar field to act like numeric field
I think this method will work, however, when trying these queries, I get
a SQL syntax error.
mysql select cast('34' AS decimal);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'decimal)' at line 1
Steve Musumeche
CIO, Internet Retail Connection
[EMAIL PROTECTED]
Douglas Sims wrote:
You can use CAST or CONVERT to see the data as a numeric type.
If the table is very big and you're going to be querying it intensely,
you might want to create a separate column to store the numeric data.
mysql select cast('34' AS decimal);
+---+
| cast('34' AS decimal) |
+---+
| 34.00 |
+---+
1 row in set (0.00 sec)
mysql select cast('hi' AS decimal);
+---+
| cast('hi' AS decimal) |
+---+
| 0.00 |
+---+
1 row in set, 1 warning (0.00 sec)
Douglas Sims
[EMAIL PROTECTED]
On Sep 27, 2006, at 10:24 PM, [EMAIL PROTECTED] wrote:
I am looking for any suggestions to this problem. I have a table with a
varchar field. This field can hold textual or numeric data, but it is
stored in a varchar field so the database sees it all as text.
I need to be able to search and sort this field as if it were numeric.
For example, here is some sample data
2.5
4
2
6
7
6.2
3.4
6
I need to be able query the table to get the rows within a certain
range,
for example, between 4 and 7:
select * from table where field1=4 and field1=7
This doesn't work because the column is not a numeric data type. Is
there
anyway to dynamically cast the data to a numeric format so I can use
MySQL's numeric sorting?
I can't change the field's data type because it also needs to be able to
hold textual data. Thank you for your help.
--Steve Musumeche
CIO, Internet Retail Connection
--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: Need to find last price and date product was sold
At 07:39 AM 9/28/2006, you wrote: Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code Unfortunately that doesn't guarantee that the price_sold will match the row with the max(date_sold). Someone gave me the solution via email using a subselect that works well. It goes something like this: select t1a.account, maxdate, amount from (select account, max (date_xact) maxdate from transactions t1 group by account) t1a left join transactions t2 on t1a.account=t2.account and maxdate=t2.date_xact order by t1a.account; Mike mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA 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: making varchar field to act like numeric field
In the last episode (Sep 28), Steve Musumeche said:
I think this method will work, however, when trying these queries, I get
a SQL syntax error.
mysql select cast('34' AS decimal);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'decimal)' at line 1
The CAST function in mysql 4.1 and older can't cast to DECIMAL. 5.0
and newer can. One easy way to convert strings to numbers in older
mysql's is to add 0 to them:
mysql select (1.10 + 0);
++
| 1.10 + 0 |
++
|1.1 |
++
--
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: making varchar field to act like numeric field
Dan,
Thank you, that works! FYI, you can treat the field like a number,
including sorting, numeric functions, etc.
For example,
select * from table order by (text_field+0.0)
Steve Musumeche
CIO, Internet Retail Connection
[EMAIL PROTECTED]
Dan Nelson wrote:
In the last episode (Sep 28), Steve Musumeche said:
I think this method will work, however, when trying these queries, I get
a SQL syntax error.
mysql select cast('34' AS decimal);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'decimal)' at line 1
The CAST function in mysql 4.1 and older can't cast to DECIMAL. 5.0
and newer can. One easy way to convert strings to numbers in older
mysql's is to add 0 to them:
mysql select (1.10 + 0);
++
| 1.10 + 0 |
++
|1.1 |
++
comparing two databases
Is there a program out there that I can use to compare two databases? Just the structure, not the content. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing two databases
In the last episode (Sep 28), Steve Buehler said: Is there a program out there that I can use to compare two databases? Just the structure, not the content. How about something simple like: mysqldump -d db1 db1.txt mysqldump -d db2 db2.txt diff -u db1.txt db2.txt -- 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: comparing two databases
When I need to do so, I use mysqldump -d to dump the schemas to files, then use diff on the files. It's pretty good at highlighting the differences. -Original Message- From: Steve Buehler [mailto:[EMAIL PROTECTED] Sent: Thursday, September 28, 2006 3:06 PM To: mysql Subject: comparing two databases Is there a program out there that I can use to compare two databases? Just the structure, not the content. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If you are not an intended recipient of this message, or an agent responsible for delivering it to an intended recipient, you are hereby notified that you have received this message in error, and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you received this message in error, please notify the sender immediately, delete the message, and return any hard copy print-outs. This message has been scanned for viruses by McAfee's Groupshield. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing two databases
I use SQLBalance for MySQL. It's got a couple of minor bugs, but it's been great for us. http://www.dswsoft.com/sqlbalance.php Steve Buehler [EMAIL PROTECTED] wrote: Is there a program out there that I can use to compare two databases? Just the structure, not the content. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing two databases
Steve Is there a program out there that I can use to compare two databases? Just the structure, not the content. Here is a query that you might be able to twist into giving you what you want. Given two dbs @db1 @db2, it lists structure diffs between them: SELECT MIN(table_name) as TableName, table_catalog,table_schema,table_name,column_name, ordinal_position,column_default,is_nullable, data_type,character_maximum_length,character_octet_length, numeric_precision,numeric_scale,character_set_name, collation_name,column_type,column_key, extra,privileges,column_comment FROM ( SELECT 'Table a' as TableName, table_catalog,table_schema,table_name,column_name, ordinal_position,column_default,is_nullable, data_type,character_maximum_length,character_octet_length, numeric_precision,numeric_scale,character_set_name, collation_name,column_type,column_key, extra,privileges,column_comment FROM information_schema.columns c1 WHERE [EMAIL PROTECTED] UNION ALL SELECT 'Table a' as TableName, table_catalog,table_schema,table_name,column_name, ordinal_position,column_default,is_nullable, data_type,character_maximum_length,character_octet_length, numeric_precision,numeric_scale,character_set_name, collation_name,column_type,column_key, extra,privileges,column_comment FROM information_schema.columns c1 WHERE [EMAIL PROTECTED] ) AS tmp GROUP BY tablename, table_catalog,table_schema,column_name, ordinal_position,column_default,is_nullable, data_type,character_maximum_length,character_octet_length, numeric_precision,numeric_scale,character_set_name, collation_name,column_type,column_key, extra,privileges,column_comment HAVING COUNT(*) = 1 ORDER BY tablename,column_name; PB - Steve Buehler wrote: Is there a program out there that I can use to compare two databases? Just the structure, not the content. Thanks Steve -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mmultiple languages in the MySQL database
One thing I've done in the past to verify this exact issue, is to simply run something like: # mysql -e select fields from table output.txt - making sure that the the output contains some non-ASCII characters - preferably non-Latin too. Then open output.txt in a browser and set the character encoding to UTF-8. [View-Character Encoding-UTF-8 in Firefox]. If the content renders properly [provided you've got the right fonts obviously], then you can be sure that the content went in properly. Or alternatively there's a nice Solaris 10 utility - auto_ef [auto encoding-finder], to which you pass a file as a parameter and it will tell you the encoding. http://docs.sun.com/app/docs/doc/819-2246/6n4i34qee?a=view#DESCRIPTION ~mm ~mm Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- Wrom: DULHPQQWOYIYZUNNYCGPKYLEJGDGVCJVTLBXFGGMEPYO Sent: Thursday, September 28, 2006 1:36 AM To: mysql@lists.mysql.com Subject: Mmultiple languages in the MySQL database How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-databas e-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.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]
Results Order Question
I'm running into a problem with a ratings script I'm writing. The overview is that a user can rate an item from 1-10. What I want it to do is display the highest rated item, with 10 being the highest rating. In case there are multiple items sharing the same score, the item that has been rated the most should be displayed. Here's a bit of the code: $albums=mysql_query(SELECT albumRating,totalRatings WHERE albumType='Album' ORDER BY albumRating,totalRatings DESC); The problem I'm running into is that it always displays the item with a 9 rating and not the 10. I'm figuring it's because mysql is seeing the order as 9,8,7,6,5,4,3,2,10,1 Am I going to have to store the numbers as 01,02,03,etc... in order to get this to work right or is there a better way to structure the query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Results Order Question
Tom, it should only be doing that if the column is set up as a string column (char, varchar). If possible, convert it to a numeric column (int, tinyint, etc), and the sort order should then be correct. If not possible, then you might explore use of the CAST function. Dan On 9/28/06, Tom Ray [Lists] [EMAIL PROTECTED] wrote: I'm running into a problem with a ratings script I'm writing. The overview is that a user can rate an item from 1-10. What I want it to do is display the highest rated item, with 10 being the highest rating. In case there are multiple items sharing the same score, the item that has been rated the most should be displayed. Here's a bit of the code: $albums=mysql_query(SELECT albumRating,totalRatings WHERE albumType='Album' ORDER BY albumRating,totalRatings DESC); The problem I'm running into is that it always displays the item with a 9 rating and not the 10. I'm figuring it's because mysql is seeing the order as 9,8,7,6,5,4,3,2,10,1 Am I going to have to store the numbers as 01,02,03,etc... in order to get this to work right or is there a better way to structure the query? -- 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: Transactions in MySQL.
Hi, All locking in *MySQL* is deadlock-free. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The --external-locking and --skip-external-locking options explicitly enable and disable external locking. The LOCK TABLES and UNLOCK TABLES statements use internal locking, so you can use them even if external locking is disabled. A call to LOCK TABLES tries to lock any tables you list so that the current thread can work with it without interference. A call to UNLOCK TABLES releases any locks that this thread holds.There are two main types of locks: read and write. When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue. External locking is a part of configuration and Internal locking is a part of query. Thanks ViSolve DB Team - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, September 25, 2006 11:42 AM Subject: Transactions in MySQL. Hi All, How transactions and locking are handled in MYSQL? Is it a part of configuration? Or a query (lock tables, Unlock tables) for each set of queries? Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: comparing two databases
I'm using navicat (www.navicat.com) which is a great tool (has a 30 day trial and not that high a cost. Regards, Aleksander Dye -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: 28. september 2006 22:27 To: Steve Buehler; mysql@lists.mysql.com Subject: Re: comparing two databases Steve Is there a program out there that I can use to compare two databases? Just the structure, not the content. Here is a query that you might be able to twist into giving you what you want. Given two dbs @db1 @db2, it lists structure diffs between them: SELECT MIN(table_name) as TableName, table_catalog,table_schema,table_name,column_name, ordinal_position,column_default,is_nullable, data_type,character_maximum_length,character_octet_length, numeric_precision,numeric_scale,character_set_name, collation_name,column_type,column_key, extra,privileges,column_comment FROM ( SELECT 'Table a' as TableName, table_catalog,table_schema,table_name,column_name, ordinal_position,column_default,is_nullable, data_type,character_maximum_length,character_octet_length, numeric_precision,numeric_scale,character_set_name, collation_name,column_type,column_key, extra,privileges,column_comment FROM information_schema.columns c1 WHERE [EMAIL PROTECTED] UNION ALL SELECT 'Table a' as TableName, table_catalog,table_schema,table_name,column_name, ordinal_position,column_default,is_nullable, data_type,character_maximum_length,character_octet_length, numeric_precision,numeric_scale,character_set_name, collation_name,column_type,column_key, extra,privileges,column_comment FROM information_schema.columns c1 WHERE [EMAIL PROTECTED] ) AS tmp GROUP BY tablename, table_catalog,table_schema,column_name, ordinal_position,column_default,is_nullable, data_type,character_maximum_length,character_octet_length, numeric_precision,numeric_scale,character_set_name, collation_name,column_type,column_key, extra,privileges,column_comment HAVING COUNT(*) = 1 ORDER BY tablename,column_name; PB - Steve Buehler wrote: Is there a program out there that I can use to compare two databases? Just the structure, not the content. Thanks Steve -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- 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]
