use of wildcards or regular expressions in IFNULL, how to create a view that substitutes NULL by 0?
Dear MySQL specialists, this is a MySQL-newbie question: I want to create a view of a table, where all NULL-values are substituted by 0. Therefore I tried: SELECT *, IFNULL(*,0) FROM table Unfortunately IFNULL seems not to accept any wildcards like * as placeholder for the column-name. REGEXP didn't work either - well maybe I made a mistake in the syntax? Everything works fine, when I write an IFNULL-command for every column in my table: SELECT *, IFNULL(b1,0) AS b1, IFNULL(b2,0) AS b2, IFNULL(b3,0) AS b3, ... But beside causing a lot of writing-work, this solution has the problem, that it doesn't reflect new columns in the original table in the view, as there is no corresponding IFNULL-command in the view. This is not acceptable in my case. So is there a way to use wildcards/regular expressions in IFNULL? Is there another way to create a view that substitutes every NULL-value with 0? I'd appreciate any kind of help very much! Kind regards and greetings from Munich, Felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF Question
Hello, I am planning to write a UDF (User Defined Function) that acts like a server side client. This UDF is called by a client first. After that the UDF spwans a thread then exits. Within this spawned thread it will get work from a network socket. After that it will start executing SQL statements against some database tables in the server. What are the ramifications of the above model within the MySql Server. Thanks, -Alex
Re: my.cnf optimization
Here's all the buffer variables: mysql show variables like '%buffer%'\G *** 1. row *** Variable_name: bulk_insert_buffer_size Value: 8388608 *** 2. row *** Variable_name: innodb_buffer_pool_awe_mem_mb Value: 0 *** 3. row *** Variable_name: innodb_buffer_pool_size Value: 8388608 *** 4. row *** Variable_name: innodb_log_buffer_size Value: 1048576 *** 5. row *** Variable_name: join_buffer_size Value: 131072 *** 6. row *** Variable_name: key_buffer_size Value: 402653184 *** 7. row *** Variable_name: myisam_sort_buffer_size Value: 67108864 *** 8. row *** Variable_name: net_buffer_length Value: 16384 *** 9. row *** Variable_name: preload_buffer_size Value: 32768 *** 10. row *** Variable_name: read_buffer_size Value: 67104768 *** 11. row *** Variable_name: read_rnd_buffer_size Value: 67104768 *** 12. row *** Variable_name: sort_buffer_size Value: 67108856 12 rows in set (0.00 sec) I'll bump innodb_buffer_pool_size to 2G and see how that goes. Thanks for the tips, if there's additional innodb tuning parameters folks tend to hit first I'd be glad to try them as well. -- Ryan Schwartz On Sep 4, 2008, at 8:16 AM, Johnny Withers wrote: If you do have a fair about of innodb tables you can increase performance by increasing the size of innodb_buffer_pool_size. According to your status output, you are currently using the entire buffer pool: *** 137. row *** Variable_name: Innodb_buffer_pool_pages_free Value: 0 It seems to be set small anyway: Variable_name: Innodb_buffer_pool_pages_data Value: 501 It also seems that you do have alot of innodb data: *** 151. row *** Variable_name: Innodb_data_read Value: 27743085907968 Again, i don't know what you have innodb_buffer_pool_size set to, but you have plenty of RAM, I'd set it to about 4.5GB and see if that helps. I also don't know mucha bout OS X and your hardware.. is it 64bit? If it is not 64bit, you probably can't use 4.5GB as the size of your buffer pool. -johnny On 9/3/08, Ryan Schwartz [EMAIL PROTECTED] wrote: All, We're seeing a huge surge in our qps and I'd like to make sure we're tuned as well as we can be. I'm wondering if I've got some variables maybe set too large (is that even possible?) ? We do have a fair bit of innodb, so perhaps I should add some non-defaults there, but I'm not so sure where to start with that. Hardware is an Apple Xserve, 2x Quad-Core Intel @ 3Ghz, 32GB RAM, 3x 280 GB SAS drives in Raid-5 config, OS is Mac OS X 10.5.4 and here's my my.cnf: [billie:~] admin$ egrep -v '^$|^#' /etc/my.cnf [client] port= 3306 socket = /var/mysql/mysql.sock [mysqld] port= 3306 socket = /var/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 50M table_cache = 2048 sort_buffer_size = 64M read_buffer_size = 64M read_rnd_buffer_size = 64M myisam_sort_buffer_size = 64M thread_cache_size = 100 query_cache_size = 64M thread_concurrency = 16 skip-thread-priority max_connections = 750 old-passwords innodb_file_per_table innodb_flush_log_at_trx_commit=1 sync_binlog=1 log-slow-queries long_query_time=2 log_queries_not_using_indexes log-bin=mysql-bin server-id = 4 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout SHOW STATUS\G output follows my sig below... My devs are adding indexes where the slow query log is pointing them, but any suggestions on how better to tune things up would be much appreciated. I'm not sure what else to tune here but we're getting bursts of 1200+ queries per second regularly and seeing things slow down significantly. Best, -- Ryan Schwartz mysql SHOW STATUS\G *** 1. row *** Variable_name: Aborted_clients Value: 1656 *** 2. row *** Variable_name: Aborted_connects Value: 3 *** 3. row *** Variable_name: Binlog_cache_disk_use Value: 276 *** 4. row
Re: my.cnf optimization
On Thu, Sep 4, 2008 at 12:15 AM, Ryan Schwartz [EMAIL PROTECTED] wrote: We're seeing a huge surge in our qps and I'd like to make sure we're tuned as well as we can be. I'm wondering if I've got some variables maybe set too large (is that even possible?) ? We do have a fair bit of innodb, so perhaps I should add some non-defaults there, but I'm not so sure where to start with that. It's not really possible to give good tuning advice without knowing about how you use the database and how your machine is currently responding. However, you can get some good started advice from the sample my.cnf files that come with MySQL and you can get a copy of the High Performance MySQL book for a good primer on what to look for. You can also find conference presentations by Peter Zaitsev that summarize some of the advice in the book. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large Query Question.
At 02:49 PM 9/3/2008, Jim Leavitt wrote: Hi Mike, Yes sometimes, the application is an online book selection tool with about 1 million titles in it. Now the queries which return 100,000 rows would be something like returning all titles from a given publisher. Most of the common searches are fairly quick (1-5 sec). But this was a specific example given to me. As you can imaging we're joining on many tables to pull author data, publication data, etc and displaying it all on a detail page. An example query is. (note: this is on a development box with nothing else on it) SELECT p.Title FROM products AS p LEFT JOIN productcontributors AS pc ON p.RecordReference = pc.RecordReference WHERE pc.rowtype = PR8 AND p.feedid = 5 GROUP BY p.id LIMIT 0,10; returns 10 rows in set (42.12 sec). (Total of 194557 rows found.) Now we've never dealt with anything like this before, but there are other sites returning similar counts fairly quickly. The only thing I can think of is hardware. What hardware upgrades would you recommend? Would it even help? Would clustering be an option here? Any advice is greatly appreciated. Thanks much. Jim, The problem is likely your index is not defined properly. Use an Explain in front of the query to see if it can use just one index from each table. I would try building a compound index on Products: (RecordReference, FeedId) ProductContributors: (RecordReference, RowType) This should get it to execute the join and where clause using just one index from each table. Give that a try and see if it speeds things up. :) Mike On 3-Sep-08, at 3:02 PM, mos wrote: Jim, Retrieving 100,000 rows will always take some time. Do you really need to return that many rows? Are you selecting just the columns you need? What are the slow queries? Mike At 12:05 PM 9/3/2008, Jim Leavitt wrote: Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (http://www.treefrog.cawww.treefrog.ca) Bringing the Internet to Life -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]http://lists.mysql.com/[EMAIL PROTECTED] Jim Leavitt Developer Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca) Bringing the Internet to Life ph: 905-836-4442 ext 104 fx: 905-895-6561 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large Query Question.
It's highly unlikely hardware upgrades are needed unless you're on a really underpowered machine. How similar are the queries on the other machines? The limit clause won't reduce the time taken to do the join and grouping, it will only reduce the amount of output. Also, I assumeyou have indexes on p.RecordReference, pc.RecordReference, pc.rowtype, and p.feedid, otherwise you'll be doing table scans. Are the indexes up-to-date, ie have you run analyze or optimize table to be sure they're balanced? I found that analyze out-of-date stats can make a HUGE difference in performance. Also, look at the memory set aside for joins in join_buffer_size. On Thu, Sep 4, 2008 at 9:38 AM, mos [EMAIL PROTECTED] wrote: At 02:49 PM 9/3/2008, Jim Leavitt wrote: Hi Mike, Yes sometimes, the application is an online book selection tool with about 1 million titles in it. Now the queries which return 100,000 rows would be something like returning all titles from a given publisher. Most of the common searches are fairly quick (1-5 sec). But this was a specific example given to me. As you can imaging we're joining on many tables to pull author data, publication data, etc and displaying it all on a detail page. An example query is. (note: this is on a development box with nothing else on it) SELECT p.Title FROM products AS p LEFT JOIN productcontributors AS pc ON p.RecordReference = pc.RecordReference WHERE pc.rowtype = PR8 AND p.feedid = 5 GROUP BY p.id LIMIT 0,10; returns 10 rows in set (42.12 sec). (Total of 194557 rows found.) Now we've never dealt with anything like this before, but there are other sites returning similar counts fairly quickly. The only thing I can think of is hardware. What hardware upgrades would you recommend? Would it even help? Would clustering be an option here? Any advice is greatly appreciated. Thanks much. Jim, The problem is likely your index is not defined properly. Use an Explain in front of the query to see if it can use just one index from each table. I would try building a compound index on Products: (RecordReference, FeedId) ProductContributors: (RecordReference, RowType) This should get it to execute the join and where clause using just one index from each table. Give that a try and see if it speeds things up. :) Mike On 3-Sep-08, at 3:02 PM, mos wrote: Jim, Retrieving 100,000 rows will always take some time. Do you really need to return that many rows? Are you selecting just the columns you need? What are the slow queries? Mike At 12:05 PM 9/3/2008, Jim Leavitt wrote: Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (http://www.treefrog.cawww.treefrog.ca) Bringing the Internet to Life -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] http://lists.mysql.com/[EMAIL PROTECTED] Jim Leavitt Developer Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca) Bringing the Internet to Life ph: 905-836-4442 ext 104 fx: 905-895-6561 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: Large Query Question.
-Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2008 5:35 PM To: Jim Leavitt Cc: mysql@lists.mysql.com Subject: Re: Large Query Question. That's a lot of data to return, make sure you factor in data load and transfer time. You may try breaking your query into smaller parts and recombining the results in a scripting language. If you are searching on a range (i.e. date range), break the range into smaller parts and run multiple queries. Divide and conquer, it will scale better. [JS] I'm considering changing one of my programs so that it leaves the result set on the server and pulls one record at a time. Do you have any sense of how much that might hurt me? We're talking about less than 100,000 records but they are relatively chunky. In this case, it's the memory usage for the result set that is a concern. I have to keep increasing the amount of memory available for PHP. Brent Baisley On Wed, Sep 3, 2008 at 1:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote: Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (www.treefrog.ca) Bringing the Internet to Life -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for someone to give a talk at http://www.pgcon.us/ on MySQL
Hello, I am looking for someone to give a talk on MySQL at the upcoming PostgreSQL Conference in October. Something like Why I chose MySQL over PostgreSQL: A Technical analysis (or similar). Any takers? http://www.pgcon.us/west08/talk_submission/ Sincerely, Joshua D. Drake -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf optimization
Ryan Schwartz wrote mysql show variables like '%buffer%'\G *** 1. row *** *** 3. row *** Variable_name: innodb_buffer_pool_size Value: 8388608 *** 4. row *** Variable_name: innodb_log_buffer_size Value: 1048576 I'll bump innodb_buffer_pool_size to 2G and see how that goes. Thanks for the tips, if there's additional innodb tuning parameters folks tend to hit first I'd be glad to try them as well. -- Ryan Schwartz Hi ryan. As pointed by Johnny, it is difficult to give optimization advise without exactly knowing the performance of your machine. I'm assuming you are using the machine as Database Server and not running application (Web/other) on the same. (And you are using InnoDB as engine) I would suggest keeping innodb_buffer_pool_size pretty high (+20G) Please read up here : http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ Also if possible get a copy of High performance MySQL and go through it as it covers many good techniques for high performance MySQL setup. Some of the default InnoDB settings are horribly wrong from high performance point of view. Can you post your complete my.cnf on pastebin or somewhere ? Regards, Ranjeet Walunj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL crash (negative mmapped regions)
mysqldump. There are other users, but the behavior recurs even if the database is only being used by mysqldump. Thanks, LA Michael Dykman wrote: How are you performing the backup? What tools are involved? Are there any ther users of the database while you are doing this? - michael dykman On Wed, Sep 3, 2008 at 4:43 PM, L'argent [EMAIL PROTECTED] wrote: I've been trying to backdown a production database for some time and can't seem to get around MySQL crashing at about 1GB of backup data. (the database is about 400GB). I have a page corruption, but it isn't found when I do a CHECK TABLE on *any* of the tables. InnoDB is running in super-safe (double-write) mode. The most recent crash spit out this memory status: Memory status: Non-mmapped space allocated from system: 541413376 Number of free chunks: 7121 Number of fastbin blocks: 0 Number of mmapped regions:18 Space in mmapped regions: -2063269888 Maximum total allocated space:0 Space available in freed fastbin blocks: 0 Total allocated space: 478166624 Total free space:63246752 Top-most, releasable space: 749360 Estimated memory (with thread stack):-1327869952 --- All those negative numbers make me believe its a MySQL bug rather than a data corruption issue. The server is now running 5.0.67 (redhat x86_64) Community. The server has 16 GB of ram and 8 cores and 6 RAID 1 arrays with the InnoDB files split amongst each. Any suggestions on where to look to get this figured out? thanks in advance, LA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf optimization
On Sep 4, 2008, at 1:48 PM, Ranjeet Walunj wrote: Hi ryan. As pointed by Johnny, it is difficult to give optimization advise without exactly knowing the performance of your machine. I'm assuming you are using the machine as Database Server and not running application (Web/other) on the same. (And you are using InnoDB as engine) I would suggest keeping innodb_buffer_pool_size pretty high (+20G) This is a dedicated MySQL server - nothing else running on it at all, so all that RAM is up for grabs. Mysqld is running in 64 bits, and after bumping innodb_buffer_pool_size to 4G our performance concerns are completely gone - I'll ramp that up after doing a bit more research on InnoDB tuning. Please read up here : http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ Also if possible get a copy of High performance MySQL and go through it as it covers many good techniques for high performance MySQL setup. I'll have to crack open my copy - haven't read through it in a while, and quite honestly I had forgot to make any adjustments on the InnoDB side of things because when I inherited the old MySQL server we were on the devs were mostly using MyISAM tables. Some of the default InnoDB settings are horribly wrong from high performance point of view. Can you post your complete my.cnf on pastebin or somewhere ? http://pastebin.com/m2ebec4f6 includes everything in my.cnf but comments and blank lines, SHOW STATUS\G, SHOW INNODB STATUS\G, AND SHOW VARIABLES\G All your help is much appreciated - I just wonder if there's not been a simple script set up by someone to autogen my.cnf based on system variables like available RAM, etc? Surely there's some general recommendations depending on those specific system things, rather than just copy my-huge.cnf and modify... -- Ryan Schwartz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large Query Question.
Jim, I've re-posted your message to the list so others can join in the fray. :) Mike At 10:50 AM 9/4/2008, you wrote: Hi Mike, I do believe we have done the indexing properly. Please advise if we can make any adjustments. Here is the output from the explain statements; 16634be.png Thanks, Jim On 3-Sep-08, at 10:02 PM, mos wrote: At 02:49 PM 9/3/2008, Jim Leavitt wrote: Hi Mike, Yes sometimes, the application is an online book selection tool with about 1 million titles in it. Now the queries which return 100,000 rows would be something like returning all titles from a given publisher. Most of the common searches are fairly quick (1-5 sec). But this was a specific example given to me. As you can imaging we're joining on many tables to pull author data, publication data, etc and displaying it all on a detail page. An example query is. (note: this is on a development box with nothing else on it) SELECT p.Title FROM products AS p LEFT JOIN productcontributors AS pc ON p.RecordReference = pc.RecordReference WHERE pc.rowtype = PR8 AND p.feedid = 5 GROUP BY p.id LIMIT 0,10; returns 10 rows in set (42.12 sec). (Total of 194557 rows found.) Now we've never dealt with anything like this before, but there are other sites returning similar counts fairly quickly. The only thing I can think of is hardware. What hardware upgrades would you recommend? Would it even help? Would clustering be an option here? Any advice is greatly appreciated. Thanks much. Jim, The problem is likely your index is not defined properly. Use an Explain in front of the query to see if it can use just one index from each table. I would try building a compound index on Products: (RecordReference, FeedId) ProductContributors: (RecordReference, RowType) This should get it to execute the join and where clause using just one index from each table. Give that a try and see if it speeds things up. :) Mike On 3-Sep-08, at 3:02 PM, mos wrote: Jim, Retrieving 100,000 rows will always take some time. Do you really need to return that many rows? Are you selecting just the columns you need? What are the slow queries? Mike At 12:05 PM 9/3/2008, Jim Leavitt wrote: Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (http://www.treefrog.cawww.treefrog.ca) Bringing the Internet to Life -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]http://lists.mysql.com/[EMAIL PROTECTED] Jim Leavitt Developer Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca) Bringing the Internet to Life ph: 905-836-4442 ext 104 fx: 905-895-6561 Jim Leavitt Developer Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca) Bringing the Internet to Life ph: 905-836-4442 ext 104 fx: 905-895-6561 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large Query Question.
On Thu, Sep 4, 2008 at 10:38 AM, mos [EMAIL PROTECTED] wrote Jim, The problem is likely your index is not defined properly. Use an Explain in front of the query to see if it can use just one index from each table. I would try building a compound index on Products: (RecordReference, FeedId) ProductContributors: (RecordReference, RowType) This should get it to execute the join and where clause using just one index from each table. Give that a try and see if it speeds things up. :) Mike I concur. The SELECT time is going to resemble something like: K_1 * F_1(number_of_records_in_database) + K_2 * F_2(number_of_records_selected) If the indices are effective, F_1 = log(N), but if the indices are not effective, F_1 = N. One thing you may want to try to narrow down the problem is just retrieving 100 records (the COUNT clause of a query) and see how that affects the speed, then try the full set and see how it is different. If they aren't very different, then it is a F_1 problem. But if they are different, then it is a K_2 / F_2 problem. As far as K_2 or F_2 problems ... Another possibility is that you are using ORDER BY on a large result set that isn't indexed for an effective sort. Try dropping the ORDER BY and see what happens. My view of how MySQL might work internally is perhaps naive. But sorting can be worst case O(N**2). Dave. On 3-Sep-08, at 3:02 PM, mos wrote: Jim, Retrieving 100,000 rows will always take some time. Do you really need to return that many rows? Are you selecting just the columns you need? What are the slow queries? Mike At 12:05 PM 9/3/2008, Jim Leavitt wrote: Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (http://www.treefrog.cawww.treefrog.ca) Bringing the Internet to Life -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] http://lists.mysql.com/[EMAIL PROTECTED] Jim Leavitt Developer Treefrog Interactive Inc. (http://www.treefrog.ca/www.treefrog.ca) Bringing the Internet to Life ph: 905-836-4442 ext 104 fx: 905-895-6561 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Large Query Question.
I concur. The SELECT time is going to resemble something like: K_1 * F_1(number_of_records_in_database) + K_2 * F_2(number_of_records_selected) If the indices are effective, F_1 = log(N), but if the indices are not effective, F_1 = N. One thing you may want to try to narrow down the problem is just retrieving 100 records (the COUNT clause of a query) and see how that affects the speed, then try the full set and see how it is different. If they aren't very different, then it is a F_1 problem. But if they are different, then it is a K_2 / F_2 problem. As far as K_2 or F_2 problems ... Another possibility is that you are using ORDER BY on a large result set that isn't indexed for an effective sort. Try dropping the ORDER BY and see what happens. My view of how MySQL might work internally is perhaps naive. But sorting can be worst case O(N**2). Dave. Addendum: I misremembered the SQL keywords. It isn't COUNT. It is (I think) LIMIT. Also, ORDER BY might be GROUP BY. Oopsie.
Re: my.cnf optimization
On Thu, Sep 4, 2008 at 3:23 PM, Ryan Schwartz [EMAIL PROTECTED] wrote: I'll have to crack open my copy - haven't read through it in a while If you have the first edition, I recommend getting the newer one. It has a lot more tuning info. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
innodb/myisam performance issues
Good afternoon, I have recently converted a large table from MyISAM to InnoDB and am experiencing severe performance issues because of it. HTTP response times have gone from avg .25 seconds to avg 2-3 seconds. Details follow: PHP/MySQL website, no memcached, 3 web nodes that interact with DB, one that serves images, one master DB that serves all reads/writes, backup DB that only serves for backup/failover at this time (app being changed to split reads/writes, not yet). The one table that I converted is 130M rows, around 10GB data MyISAM to 22GB InnoDB. There are around 110 tables on the DB total. My.cnf abbreviated settings: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer= 3G sort_buffer_size = 45M max_allowed_packet = 16M table_cache = 2048 tmp_table_size= 512M max_heap_table_size = 512M myisam_sort_buffer_size = 512M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 thread_cache_size = 300 query_cache_type = 1 query_cache_limit = 1M query_cache_size = 600M thread_concurrency = 8 max_connections = 2048 sync_binlog = 1 innodb_buffer_pool_size = 14G innodb_log_file_size = 20M innodb_flush_log_at_trx_commit=1 innodb_flush_method = O_DIRECT skip-innodb-doublewrite innodb_support_xa = 1 innodb_autoextend_increment = 16 innodb_data_file_path = ibdata1:40G:autoextend We're seeing a significantly higher percentage of IO wait on the system, averaging 20% now with the majority of that being user IO. The system is not swapping at all. Any ideas for what to check or modify to increase the performance here and let MyISAM and InnoDB play better together? The plan is to convert all tables to InnoDB which does not seem like a great idea at this point, we're considering moving back to MyISAM. Thanks! Josh Miller, RHCE -- 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
Hello Josh, why you moved your table to InnoDB? Your description doesn't sound like the tables rows are accessed concurrently and need to be locked? Are you sure you need InnoDB for this table? If you need InnoDB you probably need to redesign your queries and table structure to get them more convenient for InnoDB. With kind regards, TomH -Original Message- From: Josh Miller [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2008 10:27 PM To: mysql@lists.mysql.com Subject: innodb/myisam performance issues Good afternoon, I have recently converted a large table from MyISAM to InnoDB and am experiencing severe performance issues because of it. HTTP response times have gone from avg .25 seconds to avg 2-3 seconds. Details follow: PHP/MySQL website, no memcached, 3 web nodes that interact with DB, one that serves images, one master DB that serves all reads/writes, backup DB that only serves for backup/failover at this time (app being changed to split reads/writes, not yet). The one table that I converted is 130M rows, around 10GB data MyISAM to 22GB InnoDB. There are around 110 tables on the DB total. My.cnf abbreviated settings: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer= 3G sort_buffer_size = 45M max_allowed_packet = 16M table_cache = 2048 tmp_table_size= 512M max_heap_table_size = 512M myisam_sort_buffer_size = 512M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 thread_cache_size = 300 query_cache_type = 1 query_cache_limit = 1M query_cache_size = 600M thread_concurrency = 8 max_connections = 2048 sync_binlog = 1 innodb_buffer_pool_size = 14G innodb_log_file_size = 20M innodb_flush_log_at_trx_commit=1 innodb_flush_method = O_DIRECT skip-innodb-doublewrite innodb_support_xa = 1 innodb_autoextend_increment = 16 innodb_data_file_path = ibdata1:40G:autoextend We're seeing a significantly higher percentage of IO wait on the system, averaging 20% now with the majority of that being user IO. The system is not swapping at all. Any ideas for what to check or modify to increase the performance here and let MyISAM and InnoDB play better together? The plan is to convert all tables to InnoDB which does not seem like a great idea at this point, we're considering moving back to MyISAM. Thanks! Josh Miller, RHCE -- 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: innodb/myisam performance issues
Tom Horstmann wrote: Hello Josh, why you moved your table to InnoDB? Your description doesn't sound like the tables rows are accessed concurrently and need to be locked? Are you sure you need InnoDB for this table? If you need InnoDB you probably need to redesign your queries and table structure to get them more convenient for InnoDB. Hi Tom, The rows in this table are accessed concurrently as any activity on the site is recorded/added/updated to this table. We have several others which serve similar purposes, (sessions, totaltraffic, etc...). I don't disagree, the application needs to be written to perform better and use MySQL more efficiently. I need to find a way to make it work better in the interim :) Thanks! Josh Miller, RHCE -- 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
The rows in this table are accessed concurrently as any activity on the site is recorded/added/updated to this table. We have several others which serve similar purposes, (sessions, totaltraffic, etc...). Is the performance lag occurring with read-only queries and updates/inserts to the InnoDB table? Is the table mostly read or more written? You could set innodb_flush_log_at_trx_commit=2 if you may loose the latest InnoDB writes in case of a MySQL crash. It should give you much less IO for writes on your InnoDB tables. Please see http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html for a detailed description. Please also read about innodb_flush_method at this site and possibly try other settings. TomH -- 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
Addendum.. Please also try increasing your innodb_log_file_size to a much higher value if you have lots of writes/transactions. Maybe 250MB is a good first try. You need to delete/move the InnoDB logs before restart. Not sure about this, but please also set innodb_log_buffer_size. Try something between 16-32MB if you have many transactions. TomH -Original Message- From: Tom Horstmann [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2008 11:15 PM To: 'Josh Miller' Cc: mysql@lists.mysql.com Subject: RE: innodb/myisam performance issues The rows in this table are accessed concurrently as any activity on the site is recorded/added/updated to this table. We have several others which serve similar purposes, (sessions, totaltraffic, etc...). Is the performance lag occurring with read-only queries and updates/inserts to the InnoDB table? Is the table mostly read or more written? You could set innodb_flush_log_at_trx_commit=2 if you may loose the latest InnoDB writes in case of a MySQL crash. It should give you much less IO for writes on your InnoDB tables. Please see http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html for a detailed description. Please also read about innodb_flush_method at this site and possibly try other settings. TomH -- 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: Erro 1406 Data too long
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
--
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 Thu, Sep 4, 2008 at 4:26 PM, Josh Miller [EMAIL PROTECTED] wrote: We're seeing a significantly higher percentage of IO wait on the system, averaging 20% now with the majority of that being user IO. The system is not swapping at all. O_DIRECT may not be the best setting for your hardware. You might want to go back to the default. Any ideas for what to check or modify to increase the performance here and let MyISAM and InnoDB play better together? What you really need to do is look at which queries are slow and run EXPLAIN plans for them. Most big performance problems like you're describing are due to index issues, so that's where you should be looking. Server tuning comes lat - Perrin -- 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
Tom Horstmann wrote: Addendum.. Please also try increasing your innodb_log_file_size to a much higher value if you have lots of writes/transactions. Maybe 250MB is a good first try. You need to delete/move the InnoDB logs before restart. Not sure about this, but please also set innodb_log_buffer_size. Try something between 16-32MB if you have many transactions. Ok, we've increased the innodb_log_file_size to 500M, and that has not changed the IO wait at all so far (after 1 hour). Thanks! Josh Miller, RHCE -- 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
Perrin Harkins wrote: What you really need to do is look at which queries are slow and run EXPLAIN plans for them. Most big performance problems like you're describing are due to index issues, so that's where you should be looking. Server tuning comes lat We definitely need to work on re-designing the queries and indexes. We have a less than 50% index usage rate which is disastrous. We'd like to prove InnoDB and move onto that storage engine for the transaction support, MVCC, etc.. but we're finding that performance is poor. Thanks! Josh Miller, RHCE -- 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
Perrin said it right. If your app needs InnoDB (transaction, row level locks...) write it that way. Don't expect performance from a MyIsam compliant app when using InnoDB. TomH -Original Message- From: Josh Miller [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2008 12:42 AM To: Tom Horstmann Cc: mysql@lists.mysql.com Subject: Re: innodb/myisam performance issues Tom Horstmann wrote: Addendum.. Please also try increasing your innodb_log_file_size to a much higher value if you have lots of writes/transactions. Maybe 250MB is a good first try. You need to delete/move the InnoDB logs before restart. Not sure about this, but please also set innodb_log_buffer_size. Try something between 16-32MB if you have many transactions. Ok, we've increased the innodb_log_file_size to 500M, and that has not changed the IO wait at all so far (after 1 hour). Thanks! Josh Miller, RHCE -- 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: innodb/myisam performance issues
On Thu, Sep 4, 2008 at 6:43 PM, Josh Miller [EMAIL PROTECTED] wrote: We'd like to prove InnoDB and move onto that storage engine for the transaction support, MVCC, etc.. but we're finding that performance is poor. Well, thousands of large InnoDB database users prove that the engine itself has good performance, so I'd say you're really at the stage of working on your own indexes now. You probably don't need to change your queries, just the indexes. - Perrin -- 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
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 and good luck, -Aaron On Thu, Sep 4, 2008 at 1:26 PM, Josh Miller [EMAIL PROTECTED]wrote: Good afternoon, I have recently converted a large table from MyISAM to InnoDB and am experiencing severe performance issues because of it. HTTP response times have gone from avg .25 seconds to avg 2-3 seconds. Details follow: PHP/MySQL website, no memcached, 3 web nodes that interact with DB, one that serves images, one master DB that serves all reads/writes, backup DB that only serves for backup/failover at this time (app being changed to split reads/writes, not yet). The one table that I converted is 130M rows, around 10GB data MyISAM to 22GB InnoDB. There are around 110 tables on the DB total. My.cnf abbreviated settings: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer= 3G sort_buffer_size = 45M max_allowed_packet = 16M table_cache = 2048 tmp_table_size= 512M max_heap_table_size = 512M myisam_sort_buffer_size = 512M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 thread_cache_size = 300 query_cache_type = 1 query_cache_limit = 1M query_cache_size = 600M thread_concurrency = 8 max_connections = 2048 sync_binlog = 1 innodb_buffer_pool_size = 14G innodb_log_file_size = 20M innodb_flush_log_at_trx_commit=1 innodb_flush_method = O_DIRECT skip-innodb-doublewrite innodb_support_xa = 1 innodb_autoextend_increment = 16 innodb_data_file_path = ibdata1:40G:autoextend We're seeing a significantly higher percentage of IO wait on the system, averaging 20% now with the majority of that being user IO. The system is not swapping at all. Any ideas for what to check or modify to increase the performance here and let MyISAM and InnoDB play better together? The plan is to convert all tables to InnoDB which does not seem like a great idea at this point, we're considering moving back to MyISAM. Thanks! Josh Miller, RHCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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, (C) 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))
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
Re: Erro 1406 Data too long
In the INSERT, I used the CHAR function, rather than the ASCII, sorry
for the mistake.
Thanks again
Roland
Roland Kaber wrote:
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, (C) 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))
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
