Re: conditional sum
i got closer but i can't figure out this: individually: A) select a.job, sum(b.money) from t1 as a left join t2 as b on a.account = b.account where a.job = "ca1" and b.money > 0; +--+---+ | job | sum(b.money) | +--+---+ | ca1| 1464 | +--+---+ B) select a.job, sum(b.money) from t1 as a left join t2 as b on a.account = b.account where a.job = "ca1" and b.money < 0; +--+---+ | job| sum(b.money) | +--+---+ | ca1 | -129 | +--+---+ BUT if i put both together: select a.job, sum(b.money), sum(c.money) from t1 as a left join t2 as b on a.account = b.account left join t2 as c on a.account = c.account where a.job = "ca1"and c.money < 0 and b.money > 0; i get: +--+---+---+ | job| sum(b.money) | sum(c.money) | +--+---+---+ | ca1 |180 | -129 | +--+---+---+ it seems the condition c.money < 0 is respected but the b.money > 0 is acting weired... i changed the order but that's not it. and the 1464 is the correct number for b.money... any ideas?!. thanks. kalin m wrote: hi... how do i do conditional sums? like: select a.job, sum(if b.amount > 0 then amount end if ) from t1 as a left join t2 as b on a.account=b.accoun where a.account = b.account group by a.job; or select a.job, if b.amount > 0 then sum(b.amount) end if from t1 as a left join t2 as b on a.account=b.accoun where a.account = b.account group by a.job; or something like it. it's be awesome if it can be done... thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting around a circular reference?
I'm trying to calculate glycemic index of all food items eaten in a day with a single SELECT. The problem is the calculation for glycemic index of each item requires a total of all items' carbs. It's like a circular reference. Here's what I'm trying: SELECT sum(fooditems.carb * mealitems.quantity) as sumcarbs, sum(fooditems.gi * ((fooditems.carb * mealitems.quantity) / sumcarbs)), sum(fooditems.gl * mealitems.quantity), sum(fooditems.cal * mealitems.quantity) FROM meals INNER JOIN mealitems ON meals.meal_id = mealitems.meal_id INNER JOIN fooditems ON mealitems.fooditem_id = fooditems.fooditem_id WHERE meals.user_id = '$user_id' AND meals.date = '$meal_date'"; It replies "Unknown column 'sumcarbs' in 'field list'". See what I'm trying to do? Is there a way to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Totaling from several tables away
Thanks, that was exactly what I needed. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEXING ALL COLUMNS
More details. CREATE TABLE mailer_student_status ( student_id decimal(22,0) NOT NULL default '0', param varchar(128) NOT NULL default '', value varchar(128) default NULL, PRIMARY KEY (student_id,param). KEY idx_value (value) ) SELECT VALUE FROM mailer_student_status WHERE student_id=586925 and VALUE = 0 SELECT VALUE FROM mailer_student_status WHERE PARAM = 'FIRST_MAILER_COUPON_CODE' and VALUE = 0 On Sat, Sep 6, 2008 at 3:04 AM, Aaron Blew <[EMAIL PROTECTED]> wrote: > We'd need more information on what the where clauses of the queries > look like to assist with this. > > -Aaron > > On 9/5/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi, > > > > What would you say about the below table . What can i do to make it more > > efficient. > > > > CREATE TABLE mailer_student_status ( > > student_id decimal(22,0) NOT NULL default '0', > > param varchar(128) NOT NULL default '', > > value varchar(128) default NULL, > > PRIMARY KEY (student_id,param). > > KEY idx_value (value) > > ) > > > > On Sat, Sep 6, 2008 at 1:16 AM, ewen fortune <[EMAIL PROTECTED]> > wrote: > > > >> Hi, > >> > >> Following on from what Mike mentioned, indexing all columns does not > >> really help as MySQL will at most use one index for a query, so its > >> important to pick your indexes carefully and consider constructing > >> composite indexes. An index on a single column may not even be used > >> due to poor cardinality. > >> > >> Ewen > >> > >> On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati > >> <[EMAIL PROTECTED]> wrote: > >> > Hi all, > >> > > >> > I am looking for, is there any specific reason for not indexing all > >> columns > >> > of a table. whats the impact on the performance. Although indexing is > >> meant > >> > for getting great performance. So, why indexing all columns is not > >> > feasible. (Read in docs that all columns should not be indexed) > >> > > >> > -- > >> > Krishna Chandra Prajapati > >> > > >> > > > > > > > > -- > > Krishna Chandra Prajapati > > > > -- > Sent from my mobile device > -- Krishna Chandra Prajapati
conditional sum
hi... how do i do conditional sums? like: select a.job, sum(if b.amount > 0 then amount end if ) from t1 as a left join t2 as b on a.account=b.accoun where a.account = b.account group by a.job; or select a.job, if b.amount > 0 then sum(b.amount) end if from t1 as a left join t2 as b on a.account=b.accoun where a.account = b.account group by a.job; or something like it. it's be awesome if it can be done... thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Wierd INSERT ... SELECT syntax problem
I have an existing data set - here is an example (the real one is more complex than this) LOC DATA - A 1 B 2 C 3 D 4 E 5 F 6 ... and I am looking to run some sort of INSERT ... SELECT on this to make a new table like this: LOC DATA - A0 10 A1 11 A2 12 A3 13 B0 20 B1 21 B2 22 B3 23 C0 30 C1 31 C2 32 C3 33 D0 40 D1 41 D2 42 D3 43 E0 50 E1 51 E2 52 E3 53 F0 60 F1 61 F2 62 F3 63 I basically want to take the data from each row, perform n number of operations on it and insert it into a new table. I could make a PHP script that does this but I figured there had to be a better way. Any ideas? Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEXING ALL COLUMNS
Hi, Well at first glance its hard to tell since "param" and "value" don't say a lot about the nature of the data. If this is innodb, you can have a PRIMARY KEY of student_id (assuming its unique) and a separate index on param, this is because of the way innodb is structure, the primary key is always implied in the makeup of any other index. You could perhaps consider how much of param and are interesting and create a composite index on them idx_param_value (param(10),value(10)) or something similar. Whats important for data types is what is going to be held, and whats important for indexes is how the data is going to be queried. Ewen On Fri, Sep 5, 2008 at 10:50 PM, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > Hi, > > What would you say about the below table . What can i do to make it more > efficient. > > CREATE TABLE mailer_student_status ( > student_id decimal(22,0) NOT NULL default '0', > param varchar(128) NOT NULL default '', > value varchar(128) default NULL, > PRIMARY KEY (student_id,param). > KEY idx_value (value) > ) > > On Sat, Sep 6, 2008 at 1:16 AM, ewen fortune <[EMAIL PROTECTED]> wrote: >> >> Hi, >> >> Following on from what Mike mentioned, indexing all columns does not >> really help as MySQL will at most use one index for a query, so its >> important to pick your indexes carefully and consider constructing >> composite indexes. An index on a single column may not even be used >> due to poor cardinality. >> >> Ewen >> >> On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati >> <[EMAIL PROTECTED]> wrote: >> > Hi all, >> > >> > I am looking for, is there any specific reason for not indexing all >> > columns >> > of a table. whats the impact on the performance. Although indexing is >> > meant >> > for getting great performance. So, why indexing all columns is not >> > feasible. (Read in docs that all columns should not be indexed) >> > >> > -- >> > Krishna Chandra Prajapati >> > > > > > -- > Krishna Chandra Prajapati > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEXING ALL COLUMNS
We'd need more information on what the where clauses of the queries look like to assist with this. -Aaron On 9/5/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > Hi, > > What would you say about the below table . What can i do to make it more > efficient. > > CREATE TABLE mailer_student_status ( > student_id decimal(22,0) NOT NULL default '0', > param varchar(128) NOT NULL default '', > value varchar(128) default NULL, > PRIMARY KEY (student_id,param). > KEY idx_value (value) > ) > > On Sat, Sep 6, 2008 at 1:16 AM, ewen fortune <[EMAIL PROTECTED]> wrote: > >> Hi, >> >> Following on from what Mike mentioned, indexing all columns does not >> really help as MySQL will at most use one index for a query, so its >> important to pick your indexes carefully and consider constructing >> composite indexes. An index on a single column may not even be used >> due to poor cardinality. >> >> Ewen >> >> On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati >> <[EMAIL PROTECTED]> wrote: >> > Hi all, >> > >> > I am looking for, is there any specific reason for not indexing all >> columns >> > of a table. whats the impact on the performance. Although indexing is >> meant >> > for getting great performance. So, why indexing all columns is not >> > feasible. (Read in docs that all columns should not be indexed) >> > >> > -- >> > Krishna Chandra Prajapati >> > >> > > > > -- > Krishna Chandra Prajapati > -- Sent from my mobile device -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Totaling from several tables away
Hi Brian, Try this. SELECT SUM(mi.calories) FROM Meal_Items as mi, People as P, Meals as m WHERE p.Person_ID = '5' AND p.Person_ID=m.Person_ID AND m.Date = '2009-09-04' AND m.Meal_ID = mi.Meal_id GROUP BY p.Person_ID Hi Gerald: This part is throwing me off " ON People.Name=Meals.Name" . But I do belief using Join isn't a bad alternative. On Fri, Sep 5, 2008 at 4:36 PM, Gerald L. Clark <[EMAIL PROTECTED]> wrote: > Brian Dunning wrote: >> >> How do I query "How many calories did Brian eat on 2009-09-04"? >> >> Table:People >> +---+---+ >> + Person_ID + Name | >> +---+---+ >> | 5 | Brian | >> +---+---+ >> >> Table:Meals >> +-+---+---++ >> | Meal_ID | Person_ID | Meal_Name | Date | >> +-+---+---++ >> | 3 | 5 | Breakfast | 2009-09-04 | >> | 4 | 5 | Lunch | 2009-09-04 | >> +-+---+---++ >> >> Table:Meal_Items >> +-+-+---+--+ >> | MealItem_ID | Meal_ID | Item_Name | Calories | >> +-+-+---+--+ >> | 16 | 3 | Banana| 100 | >> | 17 | 3 | Milk | 150 | >> | 18 | 4 | Cookie| 200 | >> +-+-+---+--+ >> >> >> > SELECT sum(calories) from People > INNER JOIN Meals ON People.Name=Meals.Name > INNER JOIN Meal_Items on Meals.Meal_ID=Meal_Items.Meal_ID > WHERE Name='Brian' AND Date='2009-09-04'; > > -- > Gerald L. Clark > Sr. V.P. Development > Supplier Systems Corporation > Unix since 1982 > Linux since 1992 > > -- > 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: INDEXING ALL COLUMNS
Hi, What would you say about the below table . What can i do to make it more efficient. CREATE TABLE mailer_student_status ( student_id decimal(22,0) NOT NULL default '0', param varchar(128) NOT NULL default '', value varchar(128) default NULL, PRIMARY KEY (student_id,param). KEY idx_value (value) ) On Sat, Sep 6, 2008 at 1:16 AM, ewen fortune <[EMAIL PROTECTED]> wrote: > Hi, > > Following on from what Mike mentioned, indexing all columns does not > really help as MySQL will at most use one index for a query, so its > important to pick your indexes carefully and consider constructing > composite indexes. An index on a single column may not even be used > due to poor cardinality. > > Ewen > > On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati > <[EMAIL PROTECTED]> wrote: > > Hi all, > > > > I am looking for, is there any specific reason for not indexing all > columns > > of a table. whats the impact on the performance. Although indexing is > meant > > for getting great performance. So, why indexing all columns is not > > feasible. (Read in docs that all columns should not be indexed) > > > > -- > > Krishna Chandra Prajapati > > > -- Krishna Chandra Prajapati
Re: Totaling from several tables away
Brian Dunning wrote: How do I query "How many calories did Brian eat on 2009-09-04"? Table:People +---+---+ + Person_ID + Name | +---+---+ | 5 | Brian | +---+---+ Table:Meals +-+---+---++ | Meal_ID | Person_ID | Meal_Name | Date | +-+---+---++ | 3 | 5 | Breakfast | 2009-09-04 | | 4 | 5 | Lunch | 2009-09-04 | +-+---+---++ Table:Meal_Items +-+-+---+--+ | MealItem_ID | Meal_ID | Item_Name | Calories | +-+-+---+--+ | 16 | 3 | Banana| 100 | | 17 | 3 | Milk | 150 | | 18 | 4 | Cookie| 200 | +-+-+---+--+ SELECT sum(calories) from People INNER JOIN Meals ON People.Name=Meals.Name INNER JOIN Meal_Items on Meals.Meal_ID=Meal_Items.Meal_ID WHERE Name='Brian' AND Date='2009-09-04'; -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Totaling from several tables away
How do I query "How many calories did Brian eat on 2009-09-04"? Table:People +---+---+ + Person_ID + Name | +---+---+ | 5 | Brian | +---+---+ Table:Meals +-+---+---++ | Meal_ID | Person_ID | Meal_Name | Date | +-+---+---++ | 3 | 5 | Breakfast | 2009-09-04 | | 4 | 5 | Lunch | 2009-09-04 | +-+---+---++ Table:Meal_Items +-+-+---+--+ | MealItem_ID | Meal_ID | Item_Name | Calories | +-+-+---+--+ | 16 | 3 | Banana| 100 | | 17 | 3 | Milk | 150 | | 18 | 4 | Cookie| 200 | +-+-+---+--+
Re: INDEXING ALL COLUMNS
Hi, Following on from what Mike mentioned, indexing all columns does not really help as MySQL will at most use one index for a query, so its important to pick your indexes carefully and consider constructing composite indexes. An index on a single column may not even be used due to poor cardinality. Ewen On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > Hi all, > > I am looking for, is there any specific reason for not indexing all columns > of a table. whats the impact on the performance. Although indexing is meant > for getting great performance. So, why indexing all columns is not > feasible. (Read in docs that all columns should not be indexed) > > -- > Krishna Chandra Prajapati > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEXING ALL COLUMNS
As your table grows your inserts will start to get slower and slower. You run into the issue of locking a table due to re-creating the indexes. Also wasted space for indexes On 9/5/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi all, > > I am looking for, is there any specific reason for not indexing all columns > of a table. whats the impact on the performance. Although indexing is meant > for getting great performance. So, why indexing all columns is not > feasible. (Read in docs that all columns should not be indexed) > > -- > > Krishna Chandra Prajapati >
INDEXING ALL COLUMNS
Hi all, I am looking for, is there any specific reason for not indexing all columns of a table. whats the impact on the performance. Although indexing is meant for getting great performance. So, why indexing all columns is not feasible. (Read in docs that all columns should not be indexed) -- Krishna Chandra Prajapati
Re: Need Help Migrating DB from MySQL 5.0.x to MySQL 4.0.x
Shaun Adams schrieb: > When I perform a dump in mysql5 to mysql 4 DB, I get the error (below). > Does anyone know how I can resolve this? > > > > QUERY (windows server from the cmd prompt) > > mysqldump --lock-tables --user=root [SOURCE DB] | mysql --user=[USERNAME] > --password=[PASSWORD] --host=[HOST] [TARGET DB] > > > > ERROR MESSAGE RETURNED > > ERROR 1193 (0) at line 23: Unknown system variable > 'character_set_client' > > mysqldump: Got errno 22 on write > try the mysqldump-option --compatible=mysql40 Werner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb/myisam performance issues
On Fri, Sep 5, 2008 at 12:55 PM, Josh Miller <[EMAIL PROTECTED]> wrote: > Aaron Blew wrote: >> >> Here are a couple ideas: >> * Decrease innodb_autoextend_increment to 8 or even 4. You may see >> additional IO wait because you're pre-allocating space in chunks >> disproportinate to what you immediately need, causing bursty performance. >> * If your remaining MyISAM tables don't need it, take 2GB of the >> key_buffer >> alocation and put it towards the innodb buffer pool >> >> What are the system's specs? What's it's underlying storage? What flags >> were used when you created the filesystem(s)? What OS/Version of MySQL >> are >> you running? Could you send us some iostat output? > > Thanks for all of your suggestions -- we've switched back to MyISAM until we > can test this better. > > * increasing the innodb_buffer_pool had no apparent effect on performance. > * System is a Dell PE2950 4 core, 32GB RAM, RAID-10 local disks. > * File system is plain ext3, 'mke2fs -j' > * Running RHEL 4.4, MySQL 5.0.66a-enterprise (open ticket with MySQL, > working all angles here). > * iostat output sample (iostat -x 5): > > avg-cpu: %user %nice %system %iowait %steal %idle > 5.110.003.37 23.440.00 68.08 > > Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn > sda 294.00 328.00 2560.00656 5120 > dm-0359.50 328.00 2560.00656 5120 > dm-1 0.00 0.00 0.00 0 0 > > avg-cpu: %user %nice %system %iowait %steal %idle > 14.270.006.63 22.280.00 56.82 > > Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn > sda 360.7055.72 4815.92112 9680 > dm-0456.2255.72 4815.92112 9680 > dm-1 0.00 0.00 0.00 0 0 > > avg-cpu: %user %nice %system %iowait %steal %idle > 34.080.00 23.60 15.860.00 26.47 > > Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn > sda 348.00 316.00 3304.00632 6608 > dm-0446.00 316.00 3304.00632 6608 > dm-1 0.00 0.00 0.00 0 0 > > avg-cpu: %user %nice %system %iowait %steal %idle > 29.590.00 27.84 15.230.00 27.34 > > Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn > sda 475.00 152.00 4284.00304 8568 > dm-0554.50 152.00 4284.00304 8568 > dm-1 0.00 0.00 0.00 0 0 > > avg-cpu: %user %nice %system %iowait %steal %idle > 23.280.00 15.77 18.150.00 42.80 > > Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn > sda 312.50 192.00 3044.00384 6088 > dm-0401.50 192.00 3044.00384 6088 > dm-1 0.00 0.00 0.00 0 0 > > > > Interesting note: when I switched to using the myisam version of the table > with the old configuration, we still had very poor performance with > significant CPU IO wait as you can see from the above iostat. This was > without any load on the InnoDB table at all. Once I restarted with the new > settings, the load and performance recovered immediately. You can see from > this iostat output where the restart occurred: > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz > avgqu-sz await svctm %util > sda 0.0061.68 260.88 610.58 2158.88 5384.43 8.66 > 78.96 90.60 1.05 91.52 > dm-0 0.00 0.00 261.08 673.05 2158.88 5384.43 8.08 > 85.99 92.06 0.98 91.54 > dm-1 0.00 0.000.000.00 0.00 0.00 0.00 > 0.000.00 0.00 0.00 > > avg-cpu: %user %nice %system %iowait %steal %idle > 3.500.002.05 19.450.00 75.00 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz > avgqu-sz await svctm %util > sda 0.0030.40 373.20 251.00 3340.80 2251.20 8.96 > 31.07 49.77 1.13 70.64 > dm-0 0.00 0.00 373.00 281.40 3340.80 2251.20 8.55 > 33.85 51.72 1.08 70.72 > dm-1 0.00 0.000.000.00 0.00 0.00 0.00 > 0.000.00 0.00 0.00 > > avg-cpu: %user %nice %system %iowait %steal %idle > 4.150.002.002.400.00 91.45 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz > avgqu-sz await svctm %util > sda 0.00 8.40 20.60 54.20 206.40 500.80 9.45 > 5.30 70.80 1.37 10.28 > dm-0 0.00 0.00 20.60 62.60 206.40 500.80 8.50 > 5.57 66.90 1.
Re: innodb/myisam performance issues
Aaron Blew wrote: Here are a couple ideas: * Decrease innodb_autoextend_increment to 8 or even 4. You may see additional IO wait because you're pre-allocating space in chunks disproportinate to what you immediately need, causing bursty performance. * If your remaining MyISAM tables don't need it, take 2GB of the key_buffer alocation and put it towards the innodb buffer pool What are the system's specs? What's it's underlying storage? What flags were used when you created the filesystem(s)? What OS/Version of MySQL are you running? Could you send us some iostat output? Thanks for all of your suggestions -- we've switched back to MyISAM until we can test this better. * increasing the innodb_buffer_pool had no apparent effect on performance. * System is a Dell PE2950 4 core, 32GB RAM, RAID-10 local disks. * File system is plain ext3, 'mke2fs -j' * Running RHEL 4.4, MySQL 5.0.66a-enterprise (open ticket with MySQL, working all angles here). * iostat output sample (iostat -x 5): avg-cpu: %user %nice %system %iowait %steal %idle 5.110.003.37 23.440.00 68.08 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 294.00 328.00 2560.00656 5120 dm-0359.50 328.00 2560.00656 5120 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 14.270.006.63 22.280.00 56.82 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 360.7055.72 4815.92112 9680 dm-0456.2255.72 4815.92112 9680 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 34.080.00 23.60 15.860.00 26.47 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 348.00 316.00 3304.00632 6608 dm-0446.00 316.00 3304.00632 6608 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 29.590.00 27.84 15.230.00 27.34 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 475.00 152.00 4284.00304 8568 dm-0554.50 152.00 4284.00304 8568 dm-1 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 23.280.00 15.77 18.150.00 42.80 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 312.50 192.00 3044.00384 6088 dm-0401.50 192.00 3044.00384 6088 dm-1 0.00 0.00 0.00 0 0 Interesting note: when I switched to using the myisam version of the table with the old configuration, we still had very poor performance with significant CPU IO wait as you can see from the above iostat. This was without any load on the InnoDB table at all. Once I restarted with the new settings, the load and performance recovered immediately. You can see from this iostat output where the restart occurred: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.0061.68 260.88 610.58 2158.88 5384.43 8.6678.96 90.60 1.05 91.52 dm-0 0.00 0.00 261.08 673.05 2158.88 5384.43 8.0885.99 92.06 0.98 91.54 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 3.500.002.05 19.450.00 75.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.0030.40 373.20 251.00 3340.80 2251.20 8.9631.07 49.77 1.13 70.64 dm-0 0.00 0.00 373.00 281.40 3340.80 2251.20 8.5533.85 51.72 1.08 70.72 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 4.150.002.002.400.00 91.45 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 8.40 20.60 54.20 206.40 500.80 9.45 5.30 70.80 1.37 10.28 dm-0 0.00 0.00 20.60 62.60 206.40 500.80 8.50 5.57 66.90 1.23 10.26 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 0.750.000.602.250
Need Help Migrating DB from MySQL 5.0.x to MySQL 4.0.x
When I perform a dump in mysql5 to mysql 4 DB, I get the error (below). Does anyone know how I can resolve this? QUERY (windows server from the cmd prompt) mysqldump --lock-tables --user=root [SOURCE DB] | mysql --user=[USERNAME] --password=[PASSWORD] --host=[HOST] [TARGET DB] ERROR MESSAGE RETURNED ERROR 1193 (0) at line 23: Unknown system variable 'character_set_client' mysqldump: Got errno 22 on write
RE: Erro 1406 Data too long
From: Roland Kaber [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 04, 2008 11:24 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Erro 1406 Data too long
It looks like it is really a character set conflict. The copyright character ©
is ascii 169 and is part of latin-1. However, there is a similar character, Ⓒ
the circled latin capital letter c which is not in the latin-1 character set. I
have found two solutions:
1. setting the column's character set to utf-8
2. keeping the latin 1 character set and changing the INSERT as follows:
INSERT INTO text_t (t) VALUES (ASCII(169))
[JS] I have one data feed that (only) sometimes put N-dashes (0x96) in their
data. It bedeviled me for a long time.
You need to be careful even if you set the character set to UTF-8. You should
probably SET NAMES utf8 as well. Our programs are written in PHP, and the ones
that cross platforms (Windows client, Linux server) seemed to need
mysqli_query($db_conn, $set_names_query)
in addition. I think the rules are different for the mysql interface, and
possibly for the PDO interface as well.
Browsers do something yet again.
If you search the message archives for my name, you should find a description
of the whole journey I went through.
So, thank very much you for your excellent suggestion.
Roland
Jerry Schwartz wrote:
It is a character set conflict between the source of the data and the
column. I run into this all of the time when using the CLI. Programmatically
it can be avoided.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Roland Kaber [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 04, 2008 4:21 PM
To: mysql@lists.mysql.com
Subject: Erro 1406 Data too long
Hello
I recently encountered the following problem. I changed the sql mode to
TRADITIONAL recently. Here is a test table for demonstration purposes.
CREATE TABLE `text_t` (
`t` text collate latin1_general_cs
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
The following INSERT returns: error 1406 Data too long for column 't';
INSERT INTO text_t (t) values ('©')
Why? A single character can't be too long.
After executing INSERT IGNORE INTO text_t (t) values ('©'), the special
character '©' is inserted with a warning that the string had to be
truncated?
I then changed sql_mode again: SET sql_mode = ''. Thereafter the initial
INSERT worked correctly.
A few additional remarks:
* The special character seems to be part of the problem. Any normal
character works fine.
* The problem occured on my local server, MySQL version 5.0.37.
* I run a MAC book pro.
* I issued the same statements to a MySQL server on a Windows XP
machine. The problem simply didn't occur even in traditional sql
mode.
Do you understand what is going on? Could it be a bug?
Thank you in advance for any help you can offer.
Roland K
DRBD Setup & Replication
I currently have a MySQL server in production and am considering protecting it with DRBD. The kicker is that it is a production database and so I can't take it offline, or can take it offline for only a very (several minutes max) short period of time. Is it feasible to get this working without much/any downtime on the primary server? How gracefully does replication behave off a DRBD pair when a failover occurs? It depends partially on just how the active server fails I suppose more than anything else. Does anyone have any experience with this and care to comment? Ben Wiechman
