Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
Cor, You can set the enclosing character with the ENCLOSED BY parameter. So something like ENCLOSED BY '' will remove those quotes. - Jorrit C.R.Vegelin wrote: Thanks Paul, Yes, I've tried IFNULL() to map NULL values to empty strings. But then I get 1;2;;4;;2;9 in stead of 1;2;;4;;2;9 So building CSV files with 1;2;;4;;2;9 output is not possible ? Maybe an idea to extend the FIELDS options to enable this ... Regards, Cor - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Saturday, April 01, 2006 6:11 PM Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ? At 13:29 +0100 4/1/06, C.R.Vegelin wrote: Hi everyone, I am struggling to make a CSV file, with rows like: 1;2;;4;;2;9 where NULL values are suppressed in the CSV file. I tried the following alternatives: a) Select ... Into Outfile ... Fields Terminated By ';' Escaped By '' Lines Terminated By '\r\n' ... but this generates output like: 1;2;NULL;4;NULL;2;9 b) Select ... Into Outfile ... Fields Terminated By ';' Lines Terminated By '\r\n' ... but this generates output like: 1;2;\N;4;\N;2;9 Any idea how to get CSV rows like: 1;2;;4;;2;9 ? Thanks for your time and effort. Regards, Cor You could use IFNULL() to map NULL values to the empty string: mysql set @x = null, @y = 1; Query OK, 0 rows affected (0.00 sec) mysql select ifnull(@x,''), ifnull(@y,''); +---+---+ | ifnull(@x,'') | ifnull(@y,'') | +---+---+ | | 1 | +---+---+ 1 row in set (0.00 sec) You'll need to apply this to each column that might contain NULL values. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
Thanks Jorrit, Yes, it is a combi of Paul's suggestion to use IFNULL() with the ENCLOSED BY '' option. So when using: Select IFNULL(Jan,''), IFNULL(Feb,''), ... Into Outfile ... Fields Terminated By ';' Enclosed By '' Escaped By '' Lines Terminated By '\r\n' ... I do get results like 1;2;;4;;2;9 without NULLs or quotes. Regards, Cor - Original Message - From: Jorrit Kronjee [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, April 02, 2006 11:33 AM Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ? Cor, You can set the enclosing character with the ENCLOSED BY parameter. So something like ENCLOSED BY '' will remove those quotes. - Jorrit C.R.Vegelin wrote: Thanks Paul, Yes, I've tried IFNULL() to map NULL values to empty strings. But then I get 1;2;;4;;2;9 in stead of 1;2;;4;;2;9 So building CSV files with 1;2;;4;;2;9 output is not possible ? Maybe an idea to extend the FIELDS options to enable this ... Regards, Cor - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Saturday, April 01, 2006 6:11 PM Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ? At 13:29 +0100 4/1/06, C.R.Vegelin wrote: Hi everyone, I am struggling to make a CSV file, with rows like: 1;2;;4;;2;9 where NULL values are suppressed in the CSV file. I tried the following alternatives: a) Select ... Into Outfile ... Fields Terminated By ';' Escaped By '' Lines Terminated By '\r\n' ... but this generates output like: 1;2;NULL;4;NULL;2;9 b) Select ... Into Outfile ... Fields Terminated By ';' Lines Terminated By '\r\n' ... but this generates output like: 1;2;\N;4;\N;2;9 Any idea how to get CSV rows like: 1;2;;4;;2;9 ? Thanks for your time and effort. Regards, Cor You could use IFNULL() to map NULL values to the empty string: mysql set @x = null, @y = 1; Query OK, 0 rows affected (0.00 sec) mysql select ifnull(@x,''), ifnull(@y,''); +---+---+ | ifnull(@x,'') | ifnull(@y,'') | +---+---+ | | 1 | +---+---+ 1 row in set (0.00 sec) You'll need to apply this to each column that might contain NULL values. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- 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 4.0.18 on Mac OS X 10.2.8 won't start
For some reason, MySQL 4.0.18 has suddenly stopped running and will not start anymore. It has been running for several weeks until about 5 days ago. When I tried to reach it, I discovered that it is no longer running. However, attempting to start it via the mysqld_safe script simply returns with: Starting mysqld daemon with databases from /usr/local/mysql/data 060402 18:49:55 mysqld ended [1]Done ./mysqld_safe --user mysql To confirm, the /tmp/mysql.sock file does not exist at this point. Any ideas on how to get this running again? Sachin -- Sachin Petkar Email : [EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
- Original Message - From: Ferindo Middleton Jr [EMAIL PROTECTED] To: Ferindo Middleton Jr [EMAIL PROTECTED] Cc: Hank [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, March 31, 2006 7:30 PM Subject: Re: TIMESTAMP field not automatically updating last_updated field Ferindo Middleton Jr wrote: Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo I'm running 5.0.19-nt. I haven't had a chance to test it but should it make any difference if I say: last_updated TIMESTAMP, than if I say all this: last_updatedTIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, I think this may be the difference in why some tables are auto incrementing and others aren't. Ferindo I'm using 4.1.11 on Solaris. I've explicitly created a table with one timestamp field and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL I haven't tried an explicit update (useless to me) but an insert...on duplicate key update does not update the timestamp field. Have you, Ferindo, had any success yet? Does anyone have any further thoughts? (I just realized I need this also) --Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select Sum with union, tricky question perhaps not for you
Hi, i have a problem to select sum from same table using UNION. The key question is there a way of combining two questions in one so the resulting objectid in query1 is only used in query 2 in a smart way. Eg. if only one object is avaliable in a search first year then only check that objectid for next year and append the sum in the question. I have 4 tables companyobjects that contains the key (companyid,objectid,name etc) and i have calendar the table looks similar to this (objectid,year,day1,day2.day365) price that looks similar to this (objectid,year,day1,day2.day365) reservation that looks similar to this (objectid,year,day1,day2.day365) I have tried as follows below, as you can see i want to calculate price discount and amount to pay from same table but from 2 years. I have tried removed all my where clause below and the result is not correct it is not from both querys. Maybe you know an easier way, totaly diffrent way that i have not thought of perhaps. Thanks in advance /Henrik SELECT `companyobjects`.`objectid`, Sum(`objectprice`.`d362` +`objectprice`.`d363` +`objectprice`.`d364`+`objectprice`.`d365`), Sum((`objectprice`.`d362` +`objectprice`.`d363` +`objectprice`.`d364`+`objectprice`.`d365`)*0.1), Sum((`objectprice`.`d362` +`objectprice`.`d363` +`objectprice`.`d364`+`objectprice`.`d365`)*0.9) FROM `companyobjects` Inner Join `objectprice` ON `companyobjects`.`objectid` = `objectprice`.`objectid` Inner Join `objectreservation` ON `companyobjects`.`objectid` = `objectreservation`.`objectid` Inner Join `objectcalendar` ON `companyobjects`.`objectid` = `objectcalendar`.`objectid` WHERE `companyobjects`.`companyid` = 'C050319112022656' AND `companyobjects`.`maxnrofguests` = '1' AND `objectprice`.`year` = '2006' AND `objectreservation`.`year` = '2006' AND `objectcalendar`.`year` = '2006' GROUP BY `companyobjects`.`objectid` UNION ALL SELECT `companyobjects`.`objectid`, Sum(`objectprice`.`d1` +`objectprice`.`d2`), Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.1), Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.9) FROM `companyobjects` Inner Join `objectprice` ON `companyobjects`.`objectid` = `objectprice`.`objectid` Inner Join `objectreservation` ON `companyobjects`.`objectid` = `objectreservation`.`objectid` Inner Join `objectcalendar` ON `companyobjects`.`objectid` = `objectcalendar`.`objectid` WHERE `companyobjects`.`companyid` = 'C050319112022656' AND `companyobjects`.`maxnrofguests` = '1' AND `objectprice`.`year` = '2007' AND `objectreservation`.`year` = '2007' AND `objectcalendar`.`year` = '2007' GROUP BY `companyobjects`.`objectid` I _ Hitta rätt på nätet med MSN Search http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
Do you have some sort of visual process manager for Mac OS X that can tell you at a glance if mysqld_safe and mysql server are actually running in memory? Under linux I use a program called qps. http://www.student.nada.kth.se/~f91-men/qps/ You may already have a similar utility to view running processes under Mac OS X. Or there may be something similar you can download for free off the net. You really need some way of verifying that mysqld is actually running in memory, before attempting to connect to it. This is handy for showing running multiple servers, ie when upgrading to a newer version. You can see the port and socket each mysqld is listening to, plus other server directives such as the data directory and PID. I start mysqld directly with a bash shell script: #! /bin/sh # # start the MySQL database server /usr/local/mysql-5.0.18/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.18/my.cnf \ --port=7000 \ --socket=/var/lib/mysql/mysql.sock \ --pid=/var/lib/mysql/laptop.pid \ --user=mysql \ --datadir=/var/lib/mysql and stop it with: #! /bin/sh # # stop the MySQL database server /usr/local/mysql-5.0.18/bin/mysqladmin shutdown \ -uXX -pXX --socket=/var/lib/mysql/mysql.sock If I don't use the script to pass parameters to mysqld but add them to my.cnf, they will not appear in qps process manager. I have noticed that sometimes mysqld_safe script would start, and be in memory, but the mysqld server was not being loaded into memory for some reason, which obviuosly meant I could not connect to the mysql server. For that reason I no longer use mysqld_safe to start mysqld. HTH Keith In theory, theory and practice are the same; in practice they are not. On Sun, 2 Apr 2006, Sachin Petkar wrote: To: mysql@lists.mysql.com From: Sachin Petkar [EMAIL PROTECTED] Subject: MySQL 4.0.18 on Mac OS X 10.2.8 won't start For some reason, MySQL 4.0.18 has suddenly stopped running and will not start anymore. It has been running for several weeks until about 5 days ago. When I tried to reach it, I discovered that it is no longer running. However, attempting to start it via the mysqld_safe script simply returns with: Starting mysqld daemon with databases from /usr/local/mysql/data 060402 18:49:55 mysqld ended [1]Done ./mysqld_safe --user mysql To confirm, the /tmp/mysql.sock file does not exist at this point. Any ideas on how to get this running again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
Here are some screen snapshots of qps showing mysql server running on my machine. I tries to post these to the list, but they went over the file size limit for the mailing list. Showing mysql running in memory without using mysqld_safe script: http://www.karsites.net/KAR/websites/pub/computing/mysql-running/mysqld1.jpg First part of command-line parameters passed to mysqld: http://www.karsites.net/KAR/websites/pub/computing/mysql-running/mysqld2.jpg Second part of command-line parameters passed to mysqld: http://www.karsites.net/KAR/websites/pub/computing/mysql-running/mysqld3.jpg Regards Keith In theory, theory and practice are the same; in practice they are not. On Sun, 2 Apr 2006, Sachin Petkar wrote: To: mysql@lists.mysql.com From: Sachin Petkar [EMAIL PROTECTED] Subject: MySQL 4.0.18 on Mac OS X 10.2.8 won't start For some reason, MySQL 4.0.18 has suddenly stopped running and will not start anymore. It has been running for several weeks until about 5 days ago. When I tried to reach it, I discovered that it is no longer running. However, attempting to start it via the mysqld_safe script simply returns with: Starting mysqld daemon with databases from /usr/local/mysql/data 060402 18:49:55 mysqld ended [1]Done ./mysqld_safe --user mysql To confirm, the /tmp/mysql.sock file does not exist at this point. Any ideas on how to get this running again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
Sachin Petkar wrote: For some reason, MySQL 4.0.18 has suddenly stopped running and will not start anymore. It has been running for several weeks until about 5 days ago. When I tried to reach it, I discovered that it is no longer running. However, attempting to start it via the mysqld_safe script simply returns with: Starting mysqld daemon with databases from /usr/local/mysql/data 060402 18:49:55 mysqld ended [1]Done ./mysqld_safe --user mysql To confirm, the /tmp/mysql.sock file does not exist at this point. There are several possible reasons for this. One of the most common is inappropriate permissions on the files in /usr/local/mysql/data (they need to be readable and writable by the user under which mysql is run, usually mysql) The first thing you should do is check the error log file in /usr/local/mysql/data, on Mac OS X, usually named hostname.err In a terminal window, typing: tail /usr/local/mysql/data/example.com.err will give you the last few lines of this file and likely tell you what the specific problem is. It's possible you may need to be root or use sudo command to get permissions to read this file. Another poster suggested verifying that MySQL is not running. In your case it has clearly stopped, but you can always verify that by using the ps command: ps auwx | grep mysql If it's running, you will see an item with /usr/local/mysql/bin/mysqld in the list. It was also suggested to us another utility other than safe_mysqld to start the mysql server. In most cases it is better to use safe_mysqld. Any special options you need can be specified in /etc/my.cnf. But it sounds like you are using the default installation, so everything should just work. Eric -- Eric Braswell Web Manager MySQL AB Cupertino, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 28 from table handler
Hi, I am getting the following error when executing a simple SELECT query which used to work: 1030 : Got error 28 from table handler I did not found that in the manual. When googleing it it seems it may be related to disk space ... The server hosting my application is running MySQL 4.0.23. My db uses MyISAM tables Any idea of what might cause that, and what should be done to correct this situation? -- Jacques -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stunningly slow query
The problem with Load Data is the larger the table, the slower it gets because it has to keep updating the index during the loading process. Um, thanks. I'm not sure how Load Data got involved here, because that's not what's going on. It's a MyISAM table. Are there separate logs files? If so, where? I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on separate drives. Log files usually default to the mysql data directory, eg. /var/lib/mysql/ As I said, I don't think there are any log files for a MyISAM table. InnoDB has separate logs. Putting the database files on seperate drives may slow things down alot too - unless others know better. .frm is the database definition file. .MYI is the index file, and .MYD is the data file. There is one each of these files for each myisam table in the database. I may be wrong, but I would have thought it better if these are all together on the same disk and partition for each table in the database? This is counter-intuitive. Separating .MYI and .MYD means that I can overlap the i/o. This is a standard strategy for other databases (Oracle, in particular). I would be really surprised if this was causing my problem. This feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. Can you post your show create table tbl_name statement for these tables that involve slow queries? | old_crumb |CREATE TABLE `old_crumb` ( `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', `actual_Time` datetime NOT NULL default '-00-00 00:00:00', `actual_TZ` varchar(30) default NULL, `reported_Time` datetime default NULL, `reported_TZ` varchar(30) default NULL, `speed_Format` int(11) default NULL, `speed` float default NULL, `direction` char(2) default NULL, `compass` int(11) default NULL, `speed_NS` float default NULL, `speed_EW` float default NULL, `distance` decimal(10,0) default NULL, `duration` decimal(10,0) default NULL, `latitude` decimal(10,5) default NULL, `longitude` decimal(10,5) default NULL, `report_Landmark` varchar(255) default NULL, `report_Address` varchar(255) default NULL, `report_Cross` varchar(255) default NULL, `report_City` varchar(255) default NULL, `report_State` char(2) default NULL, `report_Zip` varchar(10) default NULL, `report_County` varchar(255) default NULL, `category` int(11) default NULL, `speed_Limit` int(11) default NULL, `street` varchar(255) default NULL, `city` varchar(255) default NULL, `state` char(2) default NULL, `zip` varchar(10) default NULL, `county` varchar(255) default NULL, `match_Name` tinyint(1) default NULL, `name_Matched` tinyint(1) default NULL, `last_Modified` datetime default NULL, PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), KEY `old_crumb_ix_reported_Time` (`reported_Time`), KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=10 COMMENT='List of breadcrumbs already rolled up.' INDEX DIRECTORY='/var/mysql_idx/trimble/' | This is the other link_area | CREATE TABLE `link_area` ( `link_ID` bigint(20) NOT NULL default '0', `dir_Travel` char(1) NOT NULL default '', `area_ID` int(11) NOT NULL default '0', PRIMARY KEY (`link_ID`,`dir_Travel`), KEY `link_area_ix_area_ID` (`area_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area table' INDEX DIRECTORY='/var/mysql_idx/landsonar/' | Inserts into the link_area were going very very slowly while data was being moved into old_crumb. old_crumb is large - my suspicion at this point is that the process of looking for key conflicts was slowing things down and starving other query traffic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
Thanks to everyone, MySQL is back up and running. This was definitely enlightening! Sachin On 4/3/06, Eric Braswell [EMAIL PROTECTED] wrote: Sachin Petkar wrote: For some reason, MySQL 4.0.18 has suddenly stopped running and will not start anymore. It has been running for several weeks until about 5 days ago. When I tried to reach it, I discovered that it is no longer running. However, attempting to start it via the mysqld_safe script simply returns with: Starting mysqld daemon with databases from /usr/local/mysql/data 060402 18:49:55 mysqld ended [1]Done ./mysqld_safe --user mysql To confirm, the /tmp/mysql.sock file does not exist at this point. There are several possible reasons for this. One of the most common is inappropriate permissions on the files in /usr/local/mysql/data (they need to be readable and writable by the user under which mysql is run, usually mysql) The first thing you should do is check the error log file in /usr/local/mysql/data, on Mac OS X, usually named hostname.err In a terminal window, typing: tail /usr/local/mysql/data/example.com.err will give you the last few lines of this file and likely tell you what the specific problem is. It's possible you may need to be root or use sudo command to get permissions to read this file. Another poster suggested verifying that MySQL is not running. In your case it has clearly stopped, but you can always verify that by using the ps command: ps auwx | grep mysql If it's running, you will see an item with /usr/local/mysql/bin/mysqld in the list. It was also suggested to us another utility other than safe_mysqld to start the mysql server. In most cases it is better to use safe_mysqld. Any special options you need can be specified in /etc/my.cnf. But it sounds like you are using the default installation, so everything should just work. Eric -- Eric Braswell Web Manager MySQL AB Cupertino, USA -- Sachin Petkar Email : [EMAIL PROTECTED]
Re: stunningly slow query
On Sun, 2 Apr 2006, Chris Kantarjiev wrote: To: mysql@lists.mysql.com From: Chris Kantarjiev [EMAIL PROTECTED] Subject: Re: stunningly slow query The problem with Load Data is the larger the table, the slower it gets because it has to keep updating the index during the loading process. Um, thanks. I'm not sure how Load Data got involved here, because that's not what's going on. It's a MyISAM table. Are there separate logs files? If so, where? I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on separate drives. Log files usually default to the mysql data directory, eg. /var/lib/mysql/ As I said, I don't think there are any log files for a MyISAM table. InnoDB has separate logs. Putting the database files on seperate drives may slow things down alot too - unless others know better. .frm is the database definition file. .MYI is the index file, and .MYD is the data file. There is one each of these files for each myisam table in the database. I may be wrong, but I would have thought it better if these are all together on the same disk and partition for each table in the database? This is counter-intuitive. Separating .MYI and .MYD means that I can overlap the i/o. This is a standard strategy for other databases (Oracle, in particular). I would be really surprised if this was causing my problem. OK - something new I've just learnt Chris. This feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. Can you post your show create table tbl_name statement for these tables that involve slow queries? | old_crumb |CREATE TABLE `old_crumb` ( `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', `actual_Time` datetime NOT NULL default '-00-00 00:00:00', `actual_TZ` varchar(30) default NULL, `reported_Time` datetime default NULL, `reported_TZ` varchar(30) default NULL, `speed_Format` int(11) default NULL, `speed` float default NULL, `direction` char(2) default NULL, `compass` int(11) default NULL, `speed_NS` float default NULL, `speed_EW` float default NULL, `distance` decimal(10,0) default NULL, `duration` decimal(10,0) default NULL, `latitude` decimal(10,5) default NULL, `longitude` decimal(10,5) default NULL, `report_Landmark` varchar(255) default NULL, `report_Address` varchar(255) default NULL, `report_Cross` varchar(255) default NULL, `report_City` varchar(255) default NULL, `report_State` char(2) default NULL, `report_Zip` varchar(10) default NULL, `report_County` varchar(255) default NULL, `category` int(11) default NULL, `speed_Limit` int(11) default NULL, `street` varchar(255) default NULL, `city` varchar(255) default NULL, `state` char(2) default NULL, `zip` varchar(10) default NULL, `county` varchar(255) default NULL, `match_Name` tinyint(1) default NULL, `name_Matched` tinyint(1) default NULL, `last_Modified` datetime default NULL, PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), KEY `old_crumb_ix_reported_Time` (`reported_Time`), KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=10 COMMENT='List of breadcrumbs already rolled up.' INDEX DIRECTORY='/var/mysql_idx/trimble/' | I'm no DB expert Chris but do you really need to create a primary key index over 4 columns? What about something simple and possibly faster like adding a seperate ID primary key column to the table like: | old_crumb |CREATE TABLE `old_crumb` ( `ID` int unsigned not null auto_increment `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', snip PRIMARY KEY (`ID`), snip An unsigned int will take an extra 4 bytes of storage space per row, and will give you an index range of 0 - 4294967295. If that is not enough range, an unsigned bigint will take an extra 8 bytes of storage space, and will give you an index range of 0 - 18446744073709551615. Although this will increase the amount of storage space required in the .MYD file, it may also decrease the amount of space required in the .MYI index file, as you would not be needing to store multi-column indexes. This is the other link_area | CREATE TABLE `link_area` ( `link_ID` bigint(20) NOT NULL default '0', `dir_Travel` char(1) NOT NULL default '', `area_ID` int(11) NOT NULL default '0', PRIMARY KEY (`link_ID`,`dir_Travel`), KEY `link_area_ix_area_ID` (`area_ID`) ) ENGINE=MyISAM DEFAULT
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
On Monday, 3 April 2006 at 2:29:48 +0530, Sachin Petkar wrote: On 4/3/06, Eric Braswell [EMAIL PROTECTED] wrote: Sachin Petkar wrote: For some reason, MySQL 4.0.18 has suddenly stopped running and will not start anymore. ... There are several possible reasons for this. ... Thanks to everyone, MySQL is back up and running. This was definitely enlightening! Did you establish what the cause of the problem was? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgp7OhHs3uSkl.pgp Description: PGP signature
Re: Select Sum with union, tricky question perhaps not for you
H L [EMAIL PROTECTED] wrote on 04/02/2006 11:51:48 AM: Hi, i have a problem to select sum from same table using UNION. The key question is there a way of combining two questions in one so the resulting objectid in query1 is only used in query 2 in a smart way. Eg. if only one object is avaliable in a search first year then only check that objectid for next year and append the sum in the question. I have 4 tables companyobjects that contains the key (companyid,objectid,name etc) and i have calendar the table looks similar to this (objectid,year,day1,day2.day365) price that looks similar to this (objectid,year,day1,day2.day365) reservation that looks similar to this (objectid,year,day1,day2.day365) I have tried as follows below, as you can see i want to calculate price discount and amount to pay from same table but from 2 years. I have tried removed all my where clause below and the result is not correct it is not from both querys. Maybe you know an easier way, totaly diffrent way that i have not thought of perhaps. Thanks in advance /Henrik SELECT `companyobjects`.`objectid`, Sum(`objectprice`.`d362` +`objectprice`.`d363` +`objectprice`.`d364`+`objectprice`.`d365`), Sum((`objectprice`.`d362` +`objectprice`.`d363` +`objectprice`.`d364`+`objectprice`.`d365`)*0.1), Sum((`objectprice`.`d362` +`objectprice`.`d363` +`objectprice`.`d364`+`objectprice`.`d365`)*0.9) FROM `companyobjects` Inner Join `objectprice` ON `companyobjects`.`objectid` = `objectprice`.`objectid` Inner Join `objectreservation` ON `companyobjects`.`objectid` = `objectreservation`.`objectid` Inner Join `objectcalendar` ON `companyobjects`.`objectid` = `objectcalendar`.`objectid` WHERE `companyobjects`.`companyid` = 'C050319112022656' AND `companyobjects`.`maxnrofguests` = '1' AND `objectprice`.`year` = '2006' AND `objectreservation`.`year` = '2006' AND `objectcalendar`.`year` = '2006' GROUP BY `companyobjects`.`objectid` UNION ALL SELECT `companyobjects`.`objectid`, Sum(`objectprice`.`d1` +`objectprice`.`d2`), Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.1), Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.9) FROM `companyobjects` Inner Join `objectprice` ON `companyobjects`.`objectid` = `objectprice`.`objectid` Inner Join `objectreservation` ON `companyobjects`.`objectid` = `objectreservation`.`objectid` Inner Join `objectcalendar` ON `companyobjects`.`objectid` = `objectcalendar`.`objectid` WHERE `companyobjects`.`companyid` = 'C050319112022656' AND `companyobjects`.`maxnrofguests` = '1' AND `objectprice`.`year` = '2007' AND `objectreservation`.`year` = '2007' AND `objectcalendar`.`year` = '2007' GROUP BY `companyobjects`.`objectid` I _ Hitta rätt på nätet med MSN Search http://search.msn.se/ The solution is to redesign your tables. You need to split into separate columns the values you want to maintain. You do not want to keep the flat file design you are currently trying to use. CREATE TABLE calendar ( objectid, year, dayofyear, ... other fields... ) CREATE TABLE price ( objectid, year, dayofyear, price ) Having a separate column for each day of the year may make sense to a person but as you have discovered, it is extremely difficult to use for any kind of ad-hoc querying. A more normalized data structure will be almost as efficient in space usage but 1000s of times more efficient for querying. There is no simple way to write a query that spans years with the table structures you currently have. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
I created the my.cnf file (for some reason, it was nowhere to be found) and passed it as an argument to mysqld_safe . That's it! Works beautifully now. Thanks, Sachin On 4/3/06, Greg 'groggy' Lehey [EMAIL PROTECTED] wrote: On Monday, 3 April 2006 at 2:29:48 +0530, Sachin Petkar wrote: On 4/3/06, Eric Braswell [EMAIL PROTECTED] wrote: Sachin Petkar wrote: For some reason, MySQL 4.0.18 has suddenly stopped running and will not start anymore. ... There are several possible reasons for this. ... Thanks to everyone, MySQL is back up and running. This was definitely enlightening! Did you establish what the cause of the problem was? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ -- Sachin Petkar Home : +91 80 4120 8542 Email : [EMAIL PROTECTED]
Re: stunningly slow query
[EMAIL PROTECTED] wrote on 04/02/2006 05:35:59 PM: snip Can you post your show create table tbl_name statement for these tables that involve slow queries? | old_crumb |CREATE TABLE `old_crumb` ( `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', `actual_Time` datetime NOT NULL default '-00-00 00:00:00', snip `last_Modified` datetime default NULL, PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), KEY `old_crumb_ix_reported_Time` (`reported_Time`), KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=10 COMMENT='List of breadcrumbs already rolled up.' INDEX DIRECTORY='/var/mysql_idx/trimble/' | I'm no DB expert Chris but do you really need to create a primary key index over 4 columns? What about something simple and possibly faster like adding a seperate ID primary key column to the table like: | old_crumb |CREATE TABLE `old_crumb` ( `ID` int unsigned not null auto_increment `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', snip PRIMARY KEY (`ID`), snip An unsigned int will take an extra 4 bytes of storage space per row, and will give you an index range of 0 - 4294967295. If that is not enough range, an unsigned bigint will take an extra 8 bytes of storage space, and will give you an index range of 0 - 18446744073709551615. Although this will increase the amount of storage space required in the .MYD file, it may also decrease the amount of space required in the .MYI index file, as you would not be needing to store multi-column indexes. Keith, Your method won't guarantee that there are no rows where the combination of the values in those four columns fails to repeat in any other row. To do that would require an EXTRA four-column unique index of type UNIQUE. Your proposal would actually make the situation worse as now there would be two indexes to maintain to achieve the same effect as the previous single PK. This is the other link_area | CREATE TABLE `link_area` ( `link_ID` bigint(20) NOT NULL default '0', `dir_Travel` char(1) NOT NULL default '', `area_ID` int(11) NOT NULL default '0', PRIMARY KEY (`link_ID`,`dir_Travel`), KEY `link_area_ix_area_ID` (`area_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area table' INDEX DIRECTORY='/var/mysql_idx/landsonar/' | Inserts into the link_area were going very very slowly while data was being moved into old_crumb. old_crumb is large - my suspicion at this point is that the process of looking for key conflicts was slowing things down and starving other query traffic. The same could be applied to the link_area table: Do you need dir_travel as part of the primary key? If dir_travel is part of what makes each row different than every other row, then YES he needs that column as part of his primary key. link_area | CREATE TABLE `link_area` ( `link_ID` bigint(20) NOT NULL default '0', `dir_Travel` char(1) NOT NULL default '', `area_ID` int(11) NOT NULL default '0', PRIMARY KEY (`link_ID`), KEY `link_area_ix_area_ID` (`area_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area table' INDEX DI$ Regards Keith Your suggestions were well intended. However, this seems to me that his key caches are just not large enough to keep the whole key in memory. It may be possible for him to maintain a smaller current or daily table that is then batch merged into the larger historic copy of his old-crum table. I believe he is correct in guessing that his insert traffic to old_crum is interfering with the inserts into link_area and that the most likely cause is the need to both validate the new rows against the PK and add the new rows into the PK. In the spirit of Keith's suggestion, is there any reason why you cannot make a hash or lookup table of all of your (`customer_ID`,`source_ID`,`vehicle_ID`) triplets and replace those columns in old_crum (and it's PK) with the single value? That way you don't lose your row uniqueness but gain space in your PK. You could also reduce your actual_time column to an integer value (instead of a date value) so that you are comparing against a numeric value when you compare against the PK? When you are dealing with 10s of millions of rows like you are, these little changes can make some big differences. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
