Re: UPDATE from monthly to yearly rows
Thanks Peter, I didn't know that MySQL has no UPDATE ... SELECT command. I followed your advice and made the following query UPDATE Data AS db INNER JOIN (SELECT myKey,Year, SUM(IF(Month= 1,Cell,Null)) AS `Jan`, ... SUM(IF(Month=12,Cell,Null)) AS `Dec` FROM Updates GROUP BY myKey, Year) AS sq ON (db.myKey=sq.myKey AND db.Year=sq.Year) SET db.Jan = sq.Jan, ..., db.Dec = sq.Dec; This works fine and fast. Thanks again ! Regards, Cor - Original Message - From: Peter Brawley To: C.R.Vegelin Cc: mysql@lists.mysql.com Sent: Tuesday, February 28, 2006 8:40 PM Subject: Re: UPDATE from monthly to yearly rows Cor, I need to put all available monthly Values from Updates to 1 Data record where MyKey and Year are equal. IOW you want to save the results of the business end of a crosstab (pivot table) query. The crosstab analysis will require a full query. MySQL has an INSERT ... SELECT command, but no UPDATE ... SELECT command, so this will be a two-step. If I understand your description correctly, you want to aggregate by month and report by mykey and year, so your crosstab would look something like this (not tested)... CREATE TEMPORARY TABLE crosstab SELECT d.myKey, d.year, SUM(IF(u.month=1 ,u.value,0)) AS jan, SUM(IF(u.month=2 ,u.value,0)) AS feb, ... etc ... SUM(IF(u.month=12,u.value,0)) AS dec) FROM data AS d INNER JOIN updates AS u USING (myKey) GROUP BY mykey,year; aggregating updates to one row per mykey per year. Then update the data table with something like ... UPDATE Data AS d INNER JOIN crosstab AS c ON d.myKey = c.myKey AND d.year = c.year SET d.Jan = c.jan ... etc ... PB - C.R.Vegelin wrote: Hi List, Please help me with the following problem in MySQL 5.0.15. I have 2 MyISAM tables like: - table Updates with fields myKey, Year, Month, Value where Month has the values 1 .. 12 - table Data with fields myKey, Year, Jan, Feb, ... Dec I need to put all available monthly Values from Updates to 1 Data record where MyKey and Year are equal. I tried the following query: UPDATE Data AS db INNER JOIN Updates AS U ON db.myKey = U.myKey SET db.Jan = IF(U.Month = 1, U.Value, db.Jan), db.Feb = IF(U.Month = 2, U.Value, db.Feb), ... db.Dec = IF(U.Month=12, U.Value,db.Dec); But this query takes only the first available Month in Updates, and ignores the other months per myKey / Year combination. I would appreciate your help. TIA, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
help regarding writing a query
HI all, This is venugopal.Being a silent member of this group.I am having a doubt regarding writing a query.I can explain it with an example Consider i have a table called GetParents The table consists of two fieds called num as Integer ParentNum as Integer The table consists of the following data num ParentNum 1 4 2 1 3 5 6 7 4 2 Now the output should be as follows if i have given input as 4 then output should be as num ParentNum 1 4 2 1 4 2 Explaination of Output: As the given input is 4.Input is compared with num and we get 4- 2.Now ParentNum is taken and checked in the table for 2 being the num and compared and 2-1 is obtained and now again the comparision is taken and 1-2 is given as ouput NOw the problem.Can we get the result by writing only one query or we have to write more no of queries.If it is possible to get this with one query then can u please give me the query. Can u give me the query or tell me the source where i can get examples of such queries. It will be very much helpful for me. Thanks in advance, Regards, venu.
Re: Elementary replication
You can do with one line: replicate-do-table = db.table1, db.table2, db.table3 - Original Message - From: Rob Gormley [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 27, 2006 10:35 PM Subject: Elementary replication Hi, Not having a lot of luck with replication. Have 2 4.1x boxes Slave is configured, via my.cnf, to only replicate three tables: replicate-do-table = db.table1 replicate-do-table = db.table2 replicate-do-table = db.table3 SHOW SLAVE STATUS; (sanitised) output is as follows: mysql show slave status\G *** 1. row *** Slave_IO_State: Master_Host: db0.xxx Master_User: xxx Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001305 Read_Master_Log_Pos: 10165261 Relay_Log_File: db2-relay-bin.02 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.001305 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: db.table1,db.table2,db.table3 Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 10165261 Relay_Log_Space: 4 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec) Master position was set with CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001305', MASTER_LOG_POS = 10165261 to deal with restoring a mysqldump, and using a running server (I'd really like to avoid shutting down the master) Replication has never been run on this machine. Where can I go from here to get it running? Rob -- 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]
Getting every other value in a select
This might be a bit odd, but here we go.. I have some data in a table that has the following structure: CREATE TABLE `gaugereportinglist` ( `Updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `StartTimeAndDate` datetime NOT NULL, `Time_Offset` double default NULL, `OutgoingPcntGgeDev` float default NULL, `IncomingPcntGgeDev` float default NULL, `MillSpeed` float default NULL, `PassNumber` int(2) default NULL, KEY `STADIndex` (`StartTimeAndDate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The data is logged once a second. The StartTimeAndDate will be the same for the particular workpiece that I am interested in. I pull out the data with a select statement such as select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 This can give me, say, 3 to 4 thousand rows. I am using BIRT (www.eclipse.org/birt) to do my reporting. These value are shown on a graph. However 3000 points on a small graph on a web page is a little over the top, and takes a long time to plot. Is there any way to select say, every 10th point without doing anything on the client side?
Re: help regarding writing a query
I'm not sure but the situation you are describing sounds like the so-called Bill of Materials Problem, sometimes called BOM for short. I'm certain that some databases provide SQL to handle BOM problems; for example, DB2 which I know quite well, provides for BOMs. However, I just did a search in the MySQL 5.0 manual and could not find a single hit on Bill of Materials or BOM so I suspect that MySQL does not support this, although it might some day. Someone once told me about another database that supports BOMs; it might have been Oracle but I can't be sure. He and I discussed BOMs and I showed him the SQL used by DB2 to handle them; he said the SQL for BOMs in the other database was quite different but didn't show it to me. In short, I suspect that solving your problem in MySQL will be difficult or maybe impossible. Solving it in DB2 or some other database should be possible if using another database is an option for you. But don't give up yet! I may have misunderstood your requirement and it really isn't a BOM at all: after all, a BOM usually proceeds downwards from parents to children to grandchildren but you seem to want to go upwards; that may require a somewhat different approach. I just did a search on Bill of Materials in the MySQL archives and found some useful information, particularly this article by Peter Brawley: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#parts_explosion It describes a way to do a BOM (also known as a Parts Explosion) in MySQL and also gives many links to articles about BOMs which might be helpful to you. Also, if you do a Google search on Bill of Materials MySQL in Google the way I just did, you'll find over 200,000 hits, some of which will point to tools or techniques that might help you with your problem. -- Rhino - Original Message - From: VenuGopal Papasani [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 5:24 AM Subject: help regarding writing a query HI all, This is venugopal.Being a silent member of this group.I am having a doubt regarding writing a query.I can explain it with an example Consider i have a table called GetParents The table consists of two fieds called num as Integer ParentNum as Integer The table consists of the following data num ParentNum 1 4 2 1 3 5 6 7 4 2 Now the output should be as follows if i have given input as 4 then output should be as num ParentNum 1 4 2 1 4 2 Explaination of Output: As the given input is 4.Input is compared with num and we get 4- 2.Now ParentNum is taken and checked in the table for 2 being the num and compared and 2-1 is obtained and now again the comparision is taken and 1-2 is given as ouput NOw the problem.Can we get the result by writing only one query or we have to write more no of queries.If it is possible to get this with one query then can u please give me the query. Can u give me the query or tell me the source where i can get examples of such queries. It will be very much helpful for me. Thanks in advance, Regards, venu. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting every other value in a select
[EMAIL PROTECTED] wrote on 03/02/2006 06:55:14 AM: This might be a bit odd, but here we go.. I have some data in a table that has the following structure: CREATE TABLE `gaugereportinglist` ( `Updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `StartTimeAndDate` datetime NOT NULL, `Time_Offset` double default NULL, `OutgoingPcntGgeDev` float default NULL, `IncomingPcntGgeDev` float default NULL, `MillSpeed` float default NULL, `PassNumber` int(2) default NULL, KEY `STADIndex` (`StartTimeAndDate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The data is logged once a second. The StartTimeAndDate will be the same for the particular workpiece that I am interested in. I pull out the data with a select statement such as select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 This can give me, say, 3 to 4 thousand rows. I am using BIRT (www.eclipse.org/birt) to do my reporting. These value are shown on a graph. However 3000 points on a small graph on a web page is a little over the top, and takes a long time to plot. Is there any way to select say, every 10th point without doing anything on the client side? You can do it if you store your current results into a temporary table with an auto_increment column in it. Then you can run a query on your temporary table looking for rows where MOD(auto_inc_column_name,10) =0 The MOD() operator returns the remainder that comes from dividing the first parameter by the second. http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html If you wanted to get every 20th term, just change the 10 to a 20. See the pattern? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
TimeStamp issue
Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema
Re: Getting every other value in a select
[EMAIL PROTECTED] wrote: This might be a bit odd, but here we go.. I have some data in a table that has the following structure: [SNIP] The data is logged once a second. The StartTimeAndDate will be the same for the particular workpiece that I am interested in. I pull out the data with a select statement such as select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 This can give me, say, 3 to 4 thousand rows. I am using BIRT (www.eclipse.org/birt) to do my reporting. These value are shown on a graph. However 3000 points on a small graph on a web page is a little over the top, and takes a long time to plot. Is there any way to select say, every 10th point without doing anything on the client side? A cheap solution, with a user variable: select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 and (@count := coalesce( @count, 0) + 1 ) % 10 = 0 ; ciao gmax -- _ _ _ _ (_|| | |(_| The Data Charmer _| http://datacharmer.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump backup on filters
Hello. We have a database driven system that serves multiple clients. We have a single database for this purpose. The data(rows) in some of the tables are specific to individual clients. In all such tables, we have a field FLD_CLIENT_ID whose value depicts the client to whom that row of information corresponds to. All other tables in the system, are agnostic to client information; but are required for the system to function as a whole. I would want to take seperate backups for individual clients. I try to use mysqldump to generate a dump file by filtering on the FLD_CLIENT_ID column. However, since not all tables contain the FLD_CLIENT_ID column, mysqldump fails. I use mysqldump from the commandline as --- ?mysqldump --single-transaction -u root clientdb --where=FLD_CLIENT_ID=1 client1_dbbackup.sql --- The error that is thrown is --- mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `ACCOUNT` WHERE FLD_CLIENT_ID=1': Unknown column FLD_CLIENT_ID' in 'where clause' (1054) --- which means thats the dump can't be created as the table ACCOUNT does not contain the column FLD_CLIENT_ID. Is it possible to write the where clause such that it should apply the FLD_CLIENT_ID filter only if the column exists in the table; and if not, dump the data anyways. I am on RHEL - MySQL 4.1.11 - Storage engine INNoDB Regards, Rithish.
apostrophe char problem
Hello I use mysql 4.1.16 and php4.3.10 and phpmyadmin2.7 and apache-2.0.53 on FreeBSD5.3 I add any text as data with phpadmin on web. it works well. But I have a problem. As example if I add a text to mysql table with phpadmin. My text contains as below; Halid 's car is expensive I will see above the sentence as Halid ''s car is expensive on web (internet explorer) That is, I see that double ' apostrophe character out What shall I do ? Thanks
Re: apostrophe char problem
Something is double escaping the strings. Check if GPC magic string is enabled (apache's auto escaping). Also check if your code manually escapes the strings. Halid Faith wrote: Hello I use mysql 4.1.16 and php4.3.10 and phpmyadmin2.7 and apache-2.0.53 on FreeBSD5.3 I add any text as data with phpadmin on web. it works well. But I have a problem. As example if I add a text to mysql table with phpadmin. My text contains as below; Halid 's car is expensive I will see above the sentence as Halid ''s car is expensive on web (internet explorer) That is, I see that double ' apostrophe character out What shall I do ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: apostrophe char problem
Halid, I think this is a php/phpmyadmin problem and not really a mysql problem. PHP has an option called magic quotes that may be causing this. http://us2.php.net/manual/en/function.get-magic-quotes-gpc.php I have run into this before - I think what happens is that magic quotes will change a single quote ' to a double '' so its mysql safe. Then when it reads it back in from the database and renders the HTML, it also displays the doubles. If you look in the database iteself from the mysql command line client do the double quotes show? -- George Law -Original Message- From: Halid Faith [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 11:51 AM To: mysql@lists.mysql.com Subject: apostrophe char problem Hello I use mysql 4.1.16 and php4.3.10 and phpmyadmin2.7 and apache-2.0.53 on FreeBSD5.3 I add any text as data with phpadmin on web. it works well. But I have a problem. As example if I add a text to mysql table with phpadmin. My text contains as below; Halid 's car is expensive I will see above the sentence as Halid ''s car is expensive on web (internet explorer) That is, I see that double ' apostrophe character out What shall I do ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeStamp issue
rtroiana wrote: Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. Why not? It is not 2037 yet. Timestamp is designed to record when records are updated, not for storing arbitrary dates and times. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump backup on filters
Your table is missing. Try this: ?mysqldump --single-transaction -u root clientdb table --where=FLD_CLIENT_ID=1 client1_dbbackup.sql -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 1:23 PM To: MySQL general mailing list Subject: mysqldump backup on filters Hello. We have a database driven system that serves multiple clients. We have a single database for this purpose. The data(rows) in some of the tables are specific to individual clients. In all such tables, we have a field FLD_CLIENT_ID whose value depicts the client to whom that row of information corresponds to. All other tables in the system, are agnostic to client information; but are required for the system to function as a whole. I would want to take seperate backups for individual clients. I try to use mysqldump to generate a dump file by filtering on the FLD_CLIENT_ID column. However, since not all tables contain the FLD_CLIENT_ID column, mysqldump fails. I use mysqldump from the commandline as --- ?mysqldump --single-transaction -u root clientdb --where=FLD_CLIENT_ID=1 client1_dbbackup.sql --- The error that is thrown is --- mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `ACCOUNT` WHERE FLD_CLIENT_ID=1': Unknown column FLD_CLIENT_ID' in 'where clause' (1054) --- which means thats the dump can't be created as the table ACCOUNT does not contain the column FLD_CLIENT_ID. Is it possible to write the where clause such that it should apply the FLD_CLIENT_ID filter only if the column exists in the table; and if not, dump the data anyways. I am on RHEL - MySQL 4.1.11 - Storage engine INNoDB Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dumping results of a select
Is there a way with mysqldump to instead of dumping the contents of a table dump the contents of a select so if you import that sql back in you will get a new table that looks like the select? -- 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: dumping results of a select
[snip] Is there a way with mysqldump to instead of dumping the contents of a table dump the contents of a select so if you import that sql back in you will get a new table that looks like the select? [/snip] I don't think so, but you could use INTO OUTFILE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: dumping results of a select
mysqldump has a -w option: -w, --where= dump only selected records; QUOTES mandatory! -- George -Original Message- From: 2wsxdr5 [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 12:30 PM To: 'MySQL general mailing list' Subject: dumping results of a select Is there a way with mysqldump to instead of dumping the contents of a table dump the contents of a select so if you import that sql back in you will get a new table that looks like the select? -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Want mysql to return tablename.fieldname format
I am working with an existing compilcated query someone wrote years ago. When I dump the data from the query to try to figure out why I'm getting unexpected data, I have three fields named id. Is there anyway to tell mysql to name the fields with the table name when they are returned, so they show up as ads.id, track.id, etc? -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dumping results of a select
2wsxdr5 [EMAIL PROTECTED] wrote on 03/02/2006 12:30:17 PM: Is there a way with mysqldump to instead of dumping the contents of a table dump the contents of a select so if you import that sql back in you will get a new table that looks like the select? -- 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 I don't think so but if all you wanted to do was to create a new table out of the results you could say CREATE TABLE newtable SELECT ... I use it all the time to great effect. Documentation is near the bottom of this page: http://dev.mysql.com/doc/refman/4.1/en/create-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Want mysql to return tablename.fieldname format
Yes. You can add on SELECT syntax the AS operation, like this: SELECT ads.id AS 'ads.id', track.id AS 'track.id' FROM . -Original Message- From: Ryan Stille [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 2:42 PM To: mysql@lists.mysql.com Subject: Want mysql to return tablename.fieldname format I am working with an existing compilcated query someone wrote years ago. When I dump the data from the query to try to figure out why I'm getting unexpected data, I have three fields named id. Is there anyway to tell mysql to name the fields with the table name when they are returned, so they show up as ads.id, track.id, etc? -Ryan -- 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: Want mysql to return tablename.fieldname format
Ryan Stille [EMAIL PROTECTED] wrote on 03/02/2006 12:42:01 PM: I am working with an existing compilcated query someone wrote years ago. When I dump the data from the query to try to figure out why I'm getting unexpected data, I have three fields named id. Is there anyway to tell mysql to name the fields with the table name when they are returned, so they show up as ads.id, track.id, etc? -Ryan I am not aware of that being an option unless you provide your own aliases in the SELECT clause of your query. Select c.id as customer.id, c.name as customer.name, ct.name as contact.name FROM customer c INNER JOIN contact ct on ct.customer_id = c.id; Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: create/restore database without binary logging
Rithish, It's possible to do this by turning off binary logging, restarting the server, importing, turning on binary logging, and restarting. -Sheeri On 2/27/06, Rithish Saralaya [EMAIL PROTECTED] wrote: Hello. I was going to recreate a database of size 35 GB from sql dump file. Wanted to know if it is possible to do it without mysql writing into the binary log. If yes, how? Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with transfer databases from different Mysql versions
It helps if you provide the error messages. Sincerely, -Sheeri On 2/26/06, Xiaobo Chen [EMAIL PROTECTED] wrote: Hi, all I have installed a portal server and which has Mysql 4.0.15-nt with it. Before I installed this server, I have Mysql 4.1 in my PC. But thing is that they will conflict and I have to delete the old Mysql service. So I used mysqldump to export the databases from the 4.1 version to the sql files. But when I use source to retrieve those database in the 4.0.15-nt version, it reports errors. Can anyone tell me how I should tackle this problem? I am also concerned what the -nt here means? Thanks a lot. Xiaobo -- 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: create/restore database without binary logging
Put SET SQL_LOG_BIN=0 at the top of your dump file. That will turn off logging just for your session. -Ryan -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 11:56 AM To: Rithish Saralaya Cc: MySQL general mailing list Subject: Re: create/restore database without binary logging Rithish, It's possible to do this by turning off binary logging, restarting the server, importing, turning on binary logging, and restarting. -Sheeri On 2/27/06, Rithish Saralaya [EMAIL PROTECTED] wrote: Hello. I was going to recreate a database of size 35 GB from sql dump file. Wanted to know if it is possible to do it without mysql writing into the binary log. If yes, how? Regards, Rithish. -- 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: CONCAT() returns not correct character set
Hi there,
I tried a few other queries:
first, confirm that what you think is a binary is indeed a binary:
mysql SELECT CHARSET(_binary'Binary');
+--+
| CHARSET(_binary'Binary') |
+--+
| binary |
+--+
1 row in set (0.00 sec)
check the regular text (for completeness' sake)
mysql select charset ('binary');
++
| charset ('binary') |
++
| latin1 |
++
1 row in set (0.00 sec)
OK, so concatenating the 2 should result in a binary according to the manual:
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hrm, I got the same result you did.
However,
mysql SELECT CHARSET(CONCAT(_binary'Binary','foo'));
+--+
| CHARSET(CONCAT((_binary'Binary'),'foo')) |
+--+
| binary |
+--+
1 row in set (0.00 sec)
works just fine. Why are you converting the text to latin1? It's
already there. That conversion seems to be messing things up.
Perhaps someone can explain why the conversion messes things up --
seems like a bug to me.
-Sheeri
On 2/26/06, Hirofumi Fujiwara [EMAIL PROTECTED] wrote:
Dear MySQL fans,
I tested CONCAT() with binary strings and I got strange result.
Manual says:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
If the arguments include any binary strings, the result is a binary
string.
But the following test says:
bianry + latin1 latin1 (not bianry)
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World
[EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html
[EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/
My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/
--
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]
Fwd: browsing in search-results
What makes you think that temporary tables would have better performance than LIMIT? I'm not saying they do, or don't, just wondering what makes you say that. Each query, yes, would make a new temporary table. However, once that session disconnects, the temporary table goes away. Something to think about if you're using stateless connections (like you have to in PHP4). -Sheeri On 2/26/06, Jochen Kaechelin [EMAIL PROTECTED] wrote: I make a fulltext-search and store the results in a temporary table so the user can browser the temporary table (5 results per page...). I think this is of better performance instead of setting LIMIT $start,$elements. correct? what would happen if several visitors of the website make a search: each user a temorary table?? Thanx. -- fvgi242ss - Webmaster wlanhacking.de http://mail.wlanhacking.de/cgi-bin/mailman/listinfo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Hyperthreading
Hi Hiro, we have noticed severe performance loss with HT enabled under
FreeBSD but not with MySQL. However, we could be having problems with
MySQL as well without knowing as we haven't done any testing as it is
operating fine. What operating system are you running?
We now disable HT on all servers wherever possible..
Atle
-
Flying Crocodile Inc, Unix Systems Administrator
On Thu, 2 Mar 2006, Hiro Yoshioka wrote:
Hi,
We found a severe performance degradation when Hyperthreading is on
and thread_concurrency=20.
We are using OSDL DBT-1 as the benchmark and got about 200 to 250
BT (bogotransactions per second) HT is OFF normal case but 30 to
50 BT on HT is ON.
innodb_thread_concurrency=20
So we did profile (using oprofile tool) and got the following profiling
data. My impression is that mutex_spin_wait (and ut_delay) is
something wrong if HT is ON. (Spin-wait loop is too expensive if it is
hyperthreading.)
I added the following code but it does not help it.
$ diff -pu ut0ut.c.orig ut0ut.c
--- ut0ut.c.orig2005-10-17 10:27:43.0 +0900
+++ ut0ut.c 2006-02-28 11:59:16.777840496 +0900
@@ -290,6 +290,13 @@ ut_delay(
j = 0;
for (i = 0; i delay * 50; i++) {
+ /* When executing a spin-wait loop on the Hyper-Threading
+ processor, the processor can suffer a severe performance
+ penalty. The pause instruction provides a hint to the
+ processor. Please refer IA-32 Intel Architecture
+ Software Developers Manual, Vol 3. */
+ __asm__ __volatile__(
+ pause; \n);
j += i;
}
What do you think? Is there any hints?
HT is OFF
CPU: P4 / Xeon, speed 2793.26 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not
stopped) with a unit mask of 0x01 (mandatory)
count 10
samples %image name app name symbol
name
13159082 8.8445 libc-2.3.4.solibc-2.3.4.somemcpy
12565549 8.4456 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_trylock
11387363 7.6537 mysqld mysqld
rec_get_offsets_func
9631916 6.4738 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_unlock
8794484 5.9110 mysqld mysqld
btr_search_guess_on_hash
4949248 3.3265 mysqld mysqld
row_search_for_mysql
4022481 2.7036 mysqld mysqld ut_delay
3754265 2.5233 mysqld mysqld
cmp_dtuple_rec_with_match
2535190 1.7040 mysqld mysqld
row_sel_store_mysql_rec
2520957 1.6944 mysqld mysqld
btr_cur_search_to_nth_level
HT is ON
CPU: P4 / Xeon with 2 hyper-threads, speed 2793.26 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not
stopped) with a unit mask of 0x01 (mandatory)
count 10
samples %image name app name symbol
name
53221317 21.4225 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_lock
25743323 10.3621 mysqld mysqld ut_delay
12345146 4.9691 vmlinux vmlinux do_futex
12066038 4.8568 mysqld mysqld
mutex_spin_wait
10395391 4.1843 vmlinux vmlinux
LKST_ETYPE_PROCESS_SCHED_ENTER_HEADER_hook
9247281 3.7222 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_unlock
7407229 2.9815 vmlinux vmlinux
futex_requeue
5921454 2.3835 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_trylock
5484279 2.2075 vmlinux vmlinux
LKST_ETYPE_PROCESS_WAKEUP_HEADER_hook
4846067 1.9506 vmlinux vmlinux
__switch_to
Regards,
Hiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Want mysql to return tablename.fieldname format
I don't understand what you want. If you have the original query, it should be apparent from it where each 'id' column originated. If you're not sure how to read the query, post it and we can help you figure out which table provided each 'id' column. -- Rhino - Original Message - From: Ryan Stille [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 12:42 PM Subject: Want mysql to return tablename.fieldname format I am working with an existing compilcated query someone wrote years ago. When I dump the data from the query to try to figure out why I'm getting unexpected data, I have three fields named id. Is there anyway to tell mysql to name the fields with the table name when they are returned, so they show up as ads.id, track.id, etc? -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeStamp issue
If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issue Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeStamp issue
In fact, no time values in MySQL are fractional (yet). All times are stored to the nearest second regardless of which date-time-like storage type you use. They way Rhino phrased his answer, it sounded as though TIMSTAMP would save fractional seconds. It doesn't. He is spot on about needing a separate column to store any values that represent fractions of seconds. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 03/02/2006 01:25:36 PM: If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issue Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Qyery help - pass string to stored procedure for IN clause
I have a SELECT query that looks similar to the following:
SELECT
FirstName,
LastName
FROM
myTable
WHERE
LastName IN ('PRICE', 'SMITH');
What I want to do is create a stored procedure for this SELECT query
similar to the following:
CREATE PROCEDURE spGetNames (IN strNames VARCHAR(255))
BEGIN
SELECT
FirstName,
LastName
FROM
myTable
WHERE
LastName IN (strNames);
END
And then I would like to call it by passing in a list of names. None of
the following calls work:
CALL spGetNames ('PRICE,SMITH');
CALL spGetNames ( 'PRICE', 'SMITH' );
CALL spGetNames ( ''PRICE'', ''SMITH'' );
My question is how to format the string parameter so this query works.
Thanks,
Randall Price
VT.SETI.IAD.MIG:Microsoft Implementation Group
http://vtmig.vt.edu
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Want mysql to return tablename.fieldname format
The query has a number of joins and it was selecting * from all the tables, so it was not apparent where each 'id' field was from. It was just a poorly written query all around. I've fixed the problem by narrowing down the number of fields it selects and giving the ambiguous fields specific names (ads.id as adid). Thanks, -Ryan Rhino wrote: I don't understand what you want. If you have the original query, it should be apparent from it where each 'id' column originated. If you're not sure how to read the query, post it and we can help you figure out which table provided each 'id' column. -- Rhino - Original Message - From: Ryan Stille [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 12:42 PM Subject: Want mysql to return tablename.fieldname format I am working with an existing compilcated query someone wrote years ago. When I dump the data from the query to try to figure out why I'm getting unexpected data, I have three fields named id. Is there anyway to tell mysql to name the fields with the table name when they are returned, so they show up as ads.id, track.id, etc? -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: TimeStamp issue
I'm putting this back on the list where it belongs; that enables everyone to benefit from the discussion, both now and in the future via the archives. -- Sorry, you're right, I didn't read your entire question thoroughly. You set the default value for a DATETIME column (or any other type for that matter) in the CREATE TABLE statement. For example: create table if not exists dates03 (id smallint not null default 99, my_datetime datetime not null default '2006-03-02 12:34:56', primary key(id)); Please note that a datetime value can be expressed in several different formats. The different formats are explained here: http://dev.mysql.com/doc/refman/5.0/en/datetime.html. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: 'Rhino' [EMAIL PROTECTED] Sent: Thursday, March 02, 2006 1:29 PM Subject: RE: TimeStamp issue Thanks for the reply. I'm using DATETIME instead of TIMESTAMP now. Although all I wanted to know was if there's any way I can set default values to DATETIME column. ++Reema -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 1:26 PM To: rtroiana; mysql@lists.mysql.com Subject: Re: TimeStamp issue If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issue Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication from multiple masters?
Sorry for the top post, just saying thanks, that's what I thought Back to the drawing board... Jeff -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 01, 2006 18:13 To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Re: Replication from multiple masters? Good point about the bin-logs. Yup - that would sink it. If mysql used individual binary logs per master database, it would work. Ya, if someone was silly enough to have two different databases with the same name, it would be bad, even with separate binary logs for each database. If you have two mysql instances on a single slave, you'll need more memory, faster CPUs, more disk space, etc. But it could be a viable option if the machine is just being used to provide a hot-standby. David [EMAIL PROTECTED] wrote: MySQL cannot handle more than one incoming binlog at a time. The facilities are just not in the code. You also run into a nightmare if a database exists on BOTH masters (same name on both systems) and the PK values of any tables (also with matching names) overlap. If both masters update the same row at appx the same time, we could run into deadlocking in the slave that didn't happen on either master. It also means that the slave and at least one of the masters will become out of sync (because the other master's changes remain in the database) and replication is considered broken at that point. It's a serious can of worms to handle multi-master replication. Your two instances on one matching replicating to two separate masters is not a multi-master replication (more than one master replicating with a single slave) it's two single-master slave setups running on the same machine. Close but not quite what the original post was looking for (I don't think). Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Griffiths [EMAIL PROTECTED] wrote on 03/01/2006 04:34:26 PM: That's not entirely true. You can have two instances of mysql running on the slave, and dbA connects to one instance, and dbB connects to the other. Jeff, when you say, different databases, do you mean that each master has a single mysql instance, and if you typed on M1, show databases you'd see (for example), dbA and if you did the same on M2, you'd see, dbB? If so, I wonder if there is another way to get around it: - create a virtual IP address that represents both masters. Use that virtual master in the my.cnf on the slave; each master has to have an identical replication account - put dbA and dbB on the slave - restrict replication from each master to their respective databases - dbA and dbB - ie don't replicate changes to the mysql database. The two masters appear as one (which overcomes the single-IP-address in the slave's my.cnf file), and each master has a different database inside the mysql instance, they aren't stepping on each others toes. Just my 2 cents. David. Greg Donald wrote: On 3/1/06, Jeff [EMAIL PROTECTED] wrote: Does anyone know if it's possible to replicate to a single slave from different databases on different masters? For instance: M1:dbAM2:dbB \ / rep rep \ / Slave http://dev.mysql.com/doc/refman/5.1/en/replication-features.ht ml snipMySQL only supports one master and many slaves./snip -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.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]
MyISAM Backup
Currently I backup my MyISAM tables every night by running a shell script that does the following: Run: Mysqlanalyze, mysqlrepair, mysqloptimize on all the tables Then shutdown mysql Then tar all the .MYI, .MYD and .frm files from the database's directory to a backup director Start MySQL again. Later on an outside backup device connects and backs up the tar file for archiving. Question: Is it safe to do this without actually shutting down the mysql db? Perhaps putting a write lock on all the tables first so that they can be read but not written to during the tar. Does it matter if I'm tar'ing a file while mysql has it open? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeStamp issue
Thanks for keeping me honest! I'd forgotten that MySQL timestamps don't keep the fractional parts of seconds either; I mostly use DB2 which keeps the fractional parts (microseconds) and forgot about this quirk of MySQL. -- Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino Cc: mysql@lists.mysql.com ; rtroiana Sent: Thursday, March 02, 2006 1:42 PM Subject: Re: TimeStamp issue In fact, no time values in MySQL are fractional (yet). All times are stored to the nearest second regardless of which date-time-like storage type you use. They way Rhino phrased his answer, it sounded as though TIMSTAMP would save fractional seconds. It doesn't. He is spot on about needing a separate column to store any values that represent fractions of seconds. Shawn GreenDatabase AdministratorUnimin Corporation - Spruce Pine "Rhino" [EMAIL PROTECTED] wrote on 03/02/2006 01:25:36 PM: If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: "rtroiana" [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issueHi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that "TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0." Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use "CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message.Checked by AVG Free Edition.Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM Backup
Jeff wrote: Currently I backup my MyISAM tables every night by running a shell script that does the following: Run: Mysqlanalyze, mysqlrepair, mysqloptimize on all the tables Then shutdown mysql Then tar all the .MYI, .MYD and .frm files from the database's directory to a backup director Start MySQL again. Later on an outside backup device connects and backs up the tar file for archiving. Question: Is it safe to do this without actually shutting down the mysql db? Perhaps putting a write lock on all the tables first so that they can be read but not written to during the tar. Does it matter if I'm tar'ing a file while mysql has it open? Thanks, Jeff Look at mysqlhotcopy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: TimeStamp issue
Thanks to all of you for replying. I'm using DATETIME instead of TIMESTAMP now. Although I still haven't find the answer for my second question. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? _ From: Rhino [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 3:50 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; rtroiana Subject: Re: TimeStamp issue Thanks for keeping me honest! I'd forgotten that MySQL timestamps don't keep the fractional parts of seconds either; I mostly use DB2 which keeps the fractional parts (microseconds) and forgot about this quirk of MySQL. -- Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino mailto:[EMAIL PROTECTED] Cc: mysql@lists.mysql.com ; rtroiana mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 1:42 PM Subject: Re: TimeStamp issue In fact, no time values in MySQL are fractional (yet). All times are stored to the nearest second regardless of which date-time-like storage type you use. They way Rhino phrased his answer, it sounded as though TIMSTAMP would save fractional seconds. It doesn't. He is spot on about needing a separate column to store any values that represent fractions of seconds. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 03/02/2006 01:25:36 PM: If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issue Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006
Re: TimeStamp issue
On 3/2/06, rtroiana [EMAIL PROTECTED] wrote: Thanks to all of you for replying. I'm using DATETIME instead of TIMESTAMP now. Although I still haven't find the answer for my second question. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Sure. You could run a trigger on an insert statement to update the DATETIME field to the contents of SELECT NOW(); -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Hyperthreading
Hi Atle,
We are using Asianux 2.0 (RHEL 4 compatible, linux kernel 2.6.9)
MySQL is the latest one. 5.0.x
Thanks in advance,
Hiro
From: Atle Veka [EMAIL PROTECTED]
Subject: Re: MySQL and Hyperthreading
Date: Thu, 2 Mar 2006 10:12:09 -0800 (PST)
Message-ID: [EMAIL PROTECTED]
Hi Hiro, we have noticed severe performance loss with HT enabled under
FreeBSD but not with MySQL. However, we could be having problems with
MySQL as well without knowing as we haven't done any testing as it is
operating fine. What operating system are you running?
We now disable HT on all servers wherever possible..
Atle
-
Flying Crocodile Inc, Unix Systems Administrator
On Thu, 2 Mar 2006, Hiro Yoshioka wrote:
Hi,
We found a severe performance degradation when Hyperthreading is on
and thread_concurrency=20.
We are using OSDL DBT-1 as the benchmark and got about 200 to 250
BT (bogotransactions per second) HT is OFF normal case but 30 to
50 BT on HT is ON.
innodb_thread_concurrency=20
So we did profile (using oprofile tool) and got the following profiling
data. My impression is that mutex_spin_wait (and ut_delay) is
something wrong if HT is ON. (Spin-wait loop is too expensive if it is
hyperthreading.)
I added the following code but it does not help it.
$ diff -pu ut0ut.c.orig ut0ut.c
--- ut0ut.c.orig2005-10-17 10:27:43.0 +0900
+++ ut0ut.c 2006-02-28 11:59:16.777840496 +0900
@@ -290,6 +290,13 @@ ut_delay(
j = 0;
for (i = 0; i delay * 50; i++) {
+ /* When executing a spin-wait loop on the Hyper-Threading
+ processor, the processor can suffer a severe performance
+ penalty. The pause instruction provides a hint to the
+ processor. Please refer IA-32 Intel Architecture
+ Software Developers Manual, Vol 3. */
+ __asm__ __volatile__(
+ pause; \n);
j += i;
}
What do you think? Is there any hints?
HT is OFF
CPU: P4 / Xeon, speed 2793.26 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not
stopped) with a unit mask of 0x01 (mandatory)
count 10
samples %image name app name symbol
name
13159082 8.8445 libc-2.3.4.solibc-2.3.4.somemcpy
12565549 8.4456 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_trylock
11387363 7.6537 mysqld mysqld
rec_get_offsets_func
9631916 6.4738 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_unlock
8794484 5.9110 mysqld mysqld
btr_search_guess_on_hash
4949248 3.3265 mysqld mysqld
row_search_for_mysql
4022481 2.7036 mysqld mysqld ut_delay
3754265 2.5233 mysqld mysqld
cmp_dtuple_rec_with_match
2535190 1.7040 mysqld mysqld
row_sel_store_mysql_rec
2520957 1.6944 mysqld mysqld
btr_cur_search_to_nth_level
HT is ON
CPU: P4 / Xeon with 2 hyper-threads, speed 2793.26 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not
stopped) with a unit mask of 0x01 (mandatory)
count 10
samples %image name app name symbol
name
53221317 21.4225 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_lock
25743323 10.3621 mysqld mysqld ut_delay
12345146 4.9691 vmlinux vmlinux do_futex
12066038 4.8568 mysqld mysqld
mutex_spin_wait
10395391 4.1843 vmlinux vmlinux
LKST_ETYPE_PROCESS_SCHED_ENTER_HEADER_hook
9247281 3.7222 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_unlock
7407229 2.9815 vmlinux vmlinux
futex_requeue
5921454 2.3835 libpthread-2.3.4.so libpthread-2.3.4.so
pthread_mutex_trylock
5484279 2.2075 vmlinux vmlinux
LKST_ETYPE_PROCESS_WAKEUP_HEADER_hook
4846067 1.9506 vmlinux vmlinux
__switch_to
Regards,
Hiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't select to outfile
I'm using mysql 3.23.58 on linux. I recently upgraded from RedHat 9 to Fedora 3 and now I am unable to write to an outfile. If I have started mysql with the simple mysql I get 'access denied' even if I am trying to put it into my home directory. mysql select lname,fname,mname,addr,city,stzip from 1950class3 into outfile '/usr/class3-2'; ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) mysql If I have started with mysql -u root -p and then a password, I get 'Can't create file.' mysql select lname, fname, addr,city,stzip from 1950class2 into outfile '/usr/database3-2'; ERROR 1: Can't create/write to file '/usr/database3-2' (Errcode: 13) mysql bruce has all privileges on the table. How can I get my data out? bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't select to outfile
You have to enable the file_permission for the user in order to create file. On 3/3/06, Bruce Bales [EMAIL PROTECTED] wrote: I'm using mysql 3.23.58 on linux. I recently upgraded from RedHat 9 to Fedora 3 and now I am unable to write to an outfile. If I have started mysql with the simple mysql I get 'access denied' even if I am trying to put it into my home directory. mysql select lname,fname,mname,addr,city,stzip from 1950class3 into outfile '/usr/class3-2'; ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) mysql If I have started with mysql -u root -p and then a password, I get 'Can't create file.' mysql select lname, fname, addr,city,stzip from 1950class2 into outfile '/usr/database3-2'; ERROR 1: Can't create/write to file '/usr/database3-2' (Errcode: 13) mysql bruce has all privileges on the table. How can I get my data out? bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Building Client Program
Hi all, I am building client program on MySQL 4.0.16 with C API. But I always get error like: make all-recursive gcc -I. -I/usr/home/vpopmail/include -I/usr/local/include/mysql -g -O2 -c user.c user.c: In function `delalert': user.c:357: syntax error before `*' user.c:358: `conn' undeclared (first use in this function) user.c:358: (Each undeclared identifier is reported only once user.c:358: for each function it appears in.) user.c:360: syntax error before `*' user.c:365: `res_set' undeclared (first use in this function) user.c:366: syntax error before `unsigned' user.c:367: `numrows' undeclared (first use in this function) user.c:372: `row' undeclared (first use in this function) user.c:382: syntax error before `char' user.c:384: `token' undeclared (first use in this function) . Here is my piece of code: #include /usr/local/include/mysql/mysql.h . MYSQL *conn; conn = mysql_init(NULL); mysql_real_connect(conn,host,username,password,database,0,NULL,0); MYSQL_RES *res_set; MYSQL_ROW row; unsigned int i; sprintf(mysqlquery,SELECT fromDomain,fromAddress FROM user WHERE userName=\'[EMAIL PROTECTED]',Username,Domain); mysql_query(conn,mysqlquery); res_set = mysql_store_result(conn); . Can anyone tell me, how can I compile this code. BR, Baynaa.
Re: CONCAT() returns not correct character set
Hi,
Is there any difference between _binary'Binary' and CONVERT('Binary' USING
binary)
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
mysql SELECT CHARSET(CONCAT(CONVERT('Bianry' USING binary),CONVERT('abc' USING
latin1)));
+-+
| CHARSET(CONCAT(CONVERT('Bianry' USING binary),CONVERT('abc' USING latin1))) |
+-+
| binary |
+-+
1 row in set (0.00 sec)
In Japan, we have to use many kinds of character set.
eucjpms,ujis for unix
cp932,sjis for Windows
utf8 for Java, MySQL meta data
So, it's a very complicated world.
Hi there,
I tried a few other queries:
first, confirm that what you think is a binary is indeed a binary:
mysql SELECT CHARSET(_binary'Binary');
+--+
| CHARSET(_binary'Binary') |
+--+
| binary |
+--+
1 row in set (0.00 sec)
check the regular text (for completeness' sake)
mysql select charset ('binary');
++
| charset ('binary') |
++
| latin1 |
++
1 row in set (0.00 sec)
OK, so concatenating the 2 should result in a binary according to the manual:
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hrm, I got the same result you did.
However,
mysql SELECT CHARSET(CONCAT(_binary'Binary','foo'));
+--+
| CHARSET(CONCAT((_binary'Binary'),'foo')) |
+--+
| binary |
+--+
1 row in set (0.00 sec)
works just fine. Why are you converting the text to latin1? It's
already there. That conversion seems to be messing things up.
Perhaps someone can explain why the conversion messes things up --
seems like a bug to me.
-Sheeri
On 2/26/06, Hirofumi Fujiwara [EMAIL PROTECTED] wrote:
Dear MySQL fans,
I tested CONCAT() with binary strings and I got strange result.
Manual says:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
If the arguments include any binary strings, the result is a binary
string.
But the following test says:
bianry + latin1 latin1 (not bianry)
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World
[EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html
[EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/
My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/
--
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]
rpm mysql5.0 for fedora4
Hi, I found that there are 2 versions of mysql 5.0 rpm for download. Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads Linux x86 generic RPM (dynamically linked) downloads What is the difference between them? How to determine what version should I use for fedora4? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: create/restore database without binary logging
Thanks Sheeri. Regards, Rithish. -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 11:26 PM To: Rithish Saralaya Cc: MySQL general mailing list Subject: Re: create/restore database without binary logging Rithish, It's possible to do this by turning off binary logging, restarting the server, importing, turning on binary logging, and restarting. -Sheeri On 2/27/06, Rithish Saralaya [EMAIL PROTECTED] wrote: Hello. I was going to recreate a database of size 35 GB from sql dump file. Wanted to know if it is possible to do it without mysql writing into the binary log. If yes, how? Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump backup on filters
Your table is missing. Try this: ?mysqldump --single-transaction -u root clientdb table --where=FLD_CLIENT_ID=1 client1_dbbackup.sql Yes. The tables are missing. That's because I want the backup of all the tables in the db, and those tables that have the column FLD_CLIENT_ID, they should be filtered by the where clause. This is because, if we are to reproduce the system for a client, then all we need to do is to run this backup on a new db. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL Administrator 1.1.9 warning message
I am running mySQL database version 3.23.58, but I'm using mySQL Administrator 1.1.9 for scheduled backups. When connecting to the database I get a message saying that this version of database is not supported and I might get unexpected behaviour. Am I OK to use this version of mySQL Administrator to backup my database and what unexpected behaviour can I expect ? Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
