RE: VC++ 2008 / MySQL debug / Unhandled exception
if Memory serves the LIBCMTD.lib library that you're excluding IS the MultiThreaded Library? Generally if you can run it thru debug you can get the CodeStack and InstructionPointer which will at least point to the exact location where it abended Saludos Cordiales desde EEUU!Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Sun, 11 Jan 2009 12:04:30 +0530 From: saf...@gmail.com To: renit...@gmail.com; mysql@lists.mysql.com Subject: Re: VC++ 2008 / MySQL debug / Unhandled exception Are you sure that VS2008 support Mysql6.0, since mysql 6 is in alpha stage. -- Sharique On 1/10/09, Miguel Cardenas renit...@gmail.com wrote: Hello list I have a problem debugging a program that uses MySQL. The program itself does not have problems, it runs perfectly when run in the console (command prompt), but if it is executed inside the Visual C++ 2008 debugger it causes an error: Unhandled exception at 0x004b1560 in MyProgram.exe: 0xC005: Access violation reading location 0x when it reaches mysql_real_connect() I'm using mysql-6.0.8-alpha-win32 binary with setup.exe installer. Note that I don't try to enter inside mysql functions, even if no breakpoints are setup and I let the program to run freely it aborts there with the unhandled exception. My VC++ 2008 configuration is this: Includes: C:\Program Files\MySQL\MySQL Server 6.0\include Libraries: C:\Program Files\MySQL\MySQL Server 6.0\lib\debug Code generation: Multi-threaded /MT Precompiled headers: NO Aditional dependencies: wsock32.lib mysqlclient.lib libmysql.lib mysys.lib Ignore specific library: (as found in a forum post) LIBCMTD.lib Debugging: YES /DEBUG I based my configuration on this post for VC++ 6.0 http://forums.mysql.com/read.php?45,49606,49606#msg-49606 I guess it may be due to the ignored LIBCMTD.lib (debug version of MT?), but if don't ignore it the compilation fails at link time with lots of redefined symbols. My concrete question: is there something wrong with the configuration shown in that post that I adapted to VC++ 2008? are VC++ programs with MySQL unable to run inside VC++ debugger? I don't want to debut my MySQL code it is working already for *NIX, but there are other parts of the program that may require debug and it aborts when calling a mysql function. Thanks for any comment or help-- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) http://safknw.blogspot.com/ Peace is the Ultimate thing we want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Windows Live™: Keep your life in sync. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_012009
Re: VC++ 2008 / MySQL debug / Unhandled exception
I seem to recall the issue with the debug library, but don't recall the fix. Do you get the same permissions (access) error with the release library? Pat... - Original Message - From: Miguel Cardenas renit...@gmail.com To: mysql@lists.mysql.com Sent: Saturday, January 10, 2009 10:22 AM Subject: VC++ 2008 / MySQL debug / Unhandled exception Hello list I have a problem debugging a program that uses MySQL. The program itself does not have problems, it runs perfectly when run in the console (command prompt), but if it is executed inside the Visual C++ 2008 debugger it causes an error: Unhandled exception at 0x004b1560 in MyProgram.exe: 0xC005: Access violation reading location 0x when it reaches mysql_real_connect() I'm using mysql-6.0.8-alpha-win32 binary with setup.exe installer. Note that I don't try to enter inside mysql functions, even if no breakpoints are setup and I let the program to run freely it aborts there with the unhandled exception. My VC++ 2008 configuration is this: Includes: C:\Program Files\MySQL\MySQL Server 6.0\include Libraries: C:\Program Files\MySQL\MySQL Server 6.0\lib\debug Code generation: Multi-threaded /MT Precompiled headers: NO Aditional dependencies: wsock32.lib mysqlclient.lib libmysql.lib mysys.lib Ignore specific library: (as found in a forum post) LIBCMTD.lib Debugging: YES /DEBUG I based my configuration on this post for VC++ 6.0 http://forums.mysql.com/read.php?45,49606,49606#msg-49606 I guess it may be due to the ignored LIBCMTD.lib (debug version of MT?), but if don't ignore it the compilation fails at link time with lots of redefined symbols. My concrete question: is there something wrong with the configuration shown in that post that I adapted to VC++ 2008? are VC++ programs with MySQL unable to run inside VC++ debugger? I don't want to debut my MySQL code it is working already for *NIX, but there are other parts of the program that may require debug and it aborts when calling a mysql function. Thanks for any comment or help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL 5.1 queries 1000x slower than 5.0
Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to 5.1.30 because after all, it was Saturday and what else is there to do on a Saturday? I'm running it on XP Pro with 3gb ram and used the my.ini for very large system. I decided not to tweak any of these settings except for the datadir and I commented out skip-networking since my old my.ini file didn't have it either. I'm using only MyISAM tables so skip-innodb is enabled. Well to make a sad story short, I ran my application last night and returned this morning only to find it still running. Select queries that would run on 1 table to return 1 row should take under a second, now takes over an hour. An Explain shows that it is using the index. The query goes something like this: select purch_date from items where prod_code='ABC' and ((store_id='A' and purch_date'2007-01-01') or (store_id='B' and purch_date'2007-01-05') or (store_id='C' and purch_date'2007-01-09')) and (col1 is null or col2 is null or col3 is null or col4 is null or col5 is null or col6 is null) order by purch_date limit 1; There are 2 compound keys: prod_code,purch_date,store_id and prod_code,purch_date,store_id The table items has approx 30 million rows in it and there are approx 5,000 rows for 'ABC'. I copied these tables over from the MySQ 5.0 data directory to the MySQL 5.1 data directory. My question is this. Has the table or index structure changed in 5.1? Do I have to optimize all of the tables before using 5.1? Did I overlook something when installing 5.1 that would account for this 1000x speed slowdown? I thought I'd bounce this question off the group before I start tearing things apart here to find out what is going on. I'm running a check now on the tables to see if they are damaged but I'd be grateful if someone could chime in and let me know if I overlooked something simple so I could have a d'uh moment and put this behind me. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL 5.1 queries 1000x slower than 5.0
On Sun, Jan 11, 2009 at 2:24 PM, mos mo...@fastmail.fm wrote: Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to 5.1.30 because after all, it was Saturday and what else is there to do on a Saturday? I'm running it on XP Pro with 3gb ram and used the my.ini for very large system. I decided not to tweak any of these settings except for the datadir and I commented out skip-networking since my old my.ini file didn't have it either. I'm using only MyISAM tables so skip-innodb is enabled. Why didn't you re-use your my.ini from your 5.0 system? Well to make a sad story short, I ran my application last night and returned this morning only to find it still running. Select queries that would run on 1 table to return 1 row should take under a second, now takes over an hour. An Explain shows that it is using the index. The query goes something like this: select purch_date from items where prod_code='ABC' and ((store_id='A' and purch_date'2007-01-01') or (store_id='B' and purch_date'2007-01-05') or (store_id='C' and purch_date'2007-01-09')) and (col1 is null or col2 is null or col3 is null or col4 is null or col5 is null or col6 is null) order by purch_date limit 1; There are 2 compound keys: prod_code,purch_date,store_id and prod_code,purch_date,store_id The table items has approx 30 million rows in it and there are approx 5,000 rows for 'ABC'. We have a client in a similar situation but I'm still waiting for access to the server to investigate the problem. I suspect that EXPLAIN is lying, in the client's case, but I can't know until I get on the box and see. In your case I can't comment. It sounds like the same thing but it might not be. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Lookup record with same id and match it's status
You need to use a join. Specifically, a left outer join. I won't explain this in great detail; figuring out how this works can be your research topic ;) but try this: select a.id, a.from, a.to, a.message, a.insertdate, b.insertdate from messages as a left outer join messages as b on a.id = b.id where a.status='SENT'; On Tue, Dec 23, 2008 at 7:28 PM, sangprabv sangpr...@gmail.com wrote: Hi, I have a table which stores log traffic. The table contains these fields: transaction_id, from, to, message, status, insertdate For example there is a message from A send to B, when the message sent to B it will insert new record. And when the message is read by B, it will also insert new record. So the records should be something like this: transaction_id, from, to, message, status, insertdate 20081224001, A, B, stest, SENT, 2008-12-24 01:01:01 20081224001, A, B, NULL, READ, 2008-12-24 01:01:03 My question is if I want to lookup 20081224001 and expect the result to be like this: transaction id, from, to, message, sent, received 20081224001, A, B, stest, 2008-12-24 01:01:01, 2008-12-24 01:01:03 How to build the query then? Please help and TIA. Willy Soap and education are not as sudden as a massacre, but they are more deadly in the long run. -- Mark Twain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ba...@xaprb.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: crosstab with percent change between years
I would create the query as you've shown for the current year, and then the same for the previous year, and join the two, like this: select columns, math expressions to create percentages from ( this year's query ) as this_year inner join ( last year's query ) as last_year using (year, month); This is probably something a little like what you are trying to do, logically; but you may need a left outer join, and you may need to do something else entirely to make it work efficiently if your data is large. On Sat, Jan 3, 2009 at 8:27 PM, Eudald Lerga eud...@digitalecologies.com wrote: Hi guys: I am quite new in SQL and I need to build a crosstab based in two tables using Mysql, but it is becoming quite dificult. So far I got a crosstab with the ocurrences for all months in every year working: select YEAR(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y')) as Year, SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=1) AS '1', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=2) AS '2', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=3) AS '3', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=4) AS '4', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=5) AS '5', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=6) AS '6', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=7) AS '7', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=8) AS '8', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=9) AS '9', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=10) AS '10', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=11) AS '11', SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=12) AS '12', count(*) AS total FROM Forc as F inner join Parc P on F.OBJECTID=P.OBJECTID where F.rectype like 'LP%' group by Year this is the result: Year 1 2 3 4 5 6 7 8 9 1011 12 total 200527 36 52 42 34 28 28 40 23 31 16 8 365 200638 47 44 40 33 38 44 49 49 53 56 56 547 200775 54 72 68 59 80 71 82 75 84 6 0 726 200878 87 90 74 76 74 65 70 78 85 32 0 809 What I have been unsuccesfully trying to accomplish is to have a column next to each month with the percent change respect the previous year. (lastYear-previousYear)*100/previousYear . It should look like this: Year 1 % 2% 200527 36 200638 40.7% 47 30.5% 200775 97.4% 54 14.8% 200878 11.1% 87 61.1% Any ideas on how to keep going? Thanks in advance. Eudald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ba...@xaprb.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL 5.1 queries 1000x slower than 5.0 - OPTIMIZER BUG
At 03:25 PM 1/11/2009, you wrote: On Sun, Jan 11, 2009 at 2:24 PM, mos mo...@fastmail.fm wrote: Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to 5.1.30 because after all, it was Saturday and what else is there to do on a Saturday? I'm running it on XP Pro with 3gb ram and used the my.ini for very large system. I decided not to tweak any of these settings except for the datadir and I commented out skip-networking since my old my.ini file didn't have it either. I'm using only MyISAM tables so skip-innodb is enabled. Why didn't you re-use your my.ini from your 5.0 system? I wanted to see what the default settings would do. Well to make a sad story short, I ran my application last night and returned this morning only to find it still running. Select queries that would run on 1 table to return 1 row should take under a second, now takes over an hour. An Explain shows that it is using the index. The query goes something like this: select purch_date from items where prod_code='ABC' and ((store_id='A' and purch_date'2007-01-01') or (store_id='B' and purch_date'2007-01-05') or (store_id='C' and purch_date'2007-01-09')) and (col1 is null or col2 is null or col3 is null or col4 is null or col5 is null or col6 is null) order by purch_date limit 1; There are 2 compound keys: prod_code,purch_date,store_id and prod_code,purch_date,store_id The table items has approx 30 million rows in it and there are approx 5,000 rows for 'ABC'. We have a client in a similar situation but I'm still waiting for access to the server to investigate the problem. I suspect that EXPLAIN is lying, in the client's case, but I can't know until I get on the box and see. In your case I can't comment. It sounds like the same thing but it might not be. I discovered the problem I'm having is the query optimization in 5.1! MYSQL 5.01 had no problem with these queries. My MyISAM table checked out ok and I optimized it to rebuild the indexes under 5.1. So there is nothing wrong with the table. I started playing with the SQL and noticed if I take out either the Limit 1 clause or the Order by clause, it runs in about a second. So it works fine if *either* the Limit 1 or Order by clause is used, but NOT BOTH! If both are used the query time goes from under 1 second to around 1 hour. This is odd because it looks like it is doing a full table scan and ignoring where prod_code='ABC' which should limit the rows returned to 14,000 rows. So sorting on 14,000 rows and putting a limit on it should not make the query run 1000x slower. I started tweaking the SQL and took out most of the Where Clause to get: select purch_date from items where prod_code='ABC' order by purch_date limit 1; and it takes 10 seconds. There are 14,000 rows where prod_code='ABC'. This is pretty slow since it is returning only 1 row. An Explain show it is using the ix_Date Key (columns Purch_Date,Store_Id,Prod_Code) because of the sort clause. It is using the index for sorting so it has to scan a lot more rows because now it is scanning all products and not just prod_code='ABC'. So the Order By clause is superseding the Where clause! If I remove the Order By clause to get select purch_date from items where prod_code='ABC' limit 1; then the Explain uses the ix_Main index (columns Prod_Code, Store_Id, purch_date) and gets the results in 0 ms. I am resetting the query cache between tests to make sure the results are accurate. I then went back to the original SQL statement and forced MySQL not to use the date index for sorting. So instead of taking more than an hour to complete it now completes in 90ms. I may have to do the same with other SQL statements if this problem crops up there too. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL 5.1 queries 1000x slower than 5.0 - OPTIMIZER BUG
On Sun, Jan 11, 2009 at 9:31 PM, mos mo...@fastmail.fm wrote: At 03:25 PM 1/11/2009, you wrote: On Sun, Jan 11, 2009 at 2:24 PM, mos mo...@fastmail.fm wrote: Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to 5.1.30 because after all, it was Saturday and what else is there to do on a Saturday? I'm running it on XP Pro with 3gb ram and used the my.ini for very large system. I decided not to tweak any of these settings except for the datadir and I commented out skip-networking since my old my.ini file didn't have it either. I'm using only MyISAM tables so skip-innodb is enabled. Why didn't you re-use your my.ini from your 5.0 system? I wanted to see what the default settings would do. Well, then you're not comparing apples to apples! I discovered the problem I'm having is the query optimization in 5.1! MYSQL 5.01 had no problem with these queries. Then you should file a bug report against this. It sounds like you have a pretty easily reproduceable test case, and that's what they need to fix bugs. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
