Re: Missing database file names
Jesse F. Hughes je...@phiwumbda.org writes: Jesse F. Hughes je...@phiwumbda.org writes: r...@pw:/var/lib/mysql/mythconverg# myisamchk -o recorded.MYI Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Could these problems be that the dead machine was 64 bit and the working machine 32 bit? I'm rebuilding the dead machine now. When it's got mysql loaded on it, I'll try to run myisamchk on a 64-bit machine. Well, I'm sorry to say that I did not successfully rebuild my recorded (or any other) database index. Again, the issue could be that I'm using a different mysql version, but I think I'll have to throw in the towel and start the mythtv database from scratch. Consequently, I'll have to throw away a terabyte worth of recordings, too. Oh well. Losing the recordings isn't that big a deal. Losing the database of recording rules and list of shows previously recorded matters a bit more to me. I appreciate all the help anyway. Thanks, guys. -- Quincy, would you rather do epistemology or conceptual analysis? You know what? I'd rather fight on an aircraft carrier And Mama and Baba (Papa) would fight on an aircraft carrier, too. -- Quincy P. Hughes, age 3 1/2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Missing database file names
Is it possible to build a .MYI file from scratch? I have found a file that I believe is recorded.MYD. I have the .frm file as well, but I don't think that recorded.MYI survived the file system event. -- No feeling sympathy for mathematicians who start marching with signs like 'Will work for food' in the future... I will not show mercy going forward. I was trained as a soldier in the United States Army after all... We play to win. --James Harris, feel his wrath! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Missing database file names
(I emailed this to Martin and Jaime rather than to the discussion group, so I'm re-sending it.) Martin Gainty mgai...@hotmail.com writes: Jesse ..please keep us apprised on your progress..we would like to know how fubar the db can be before it becomes 'unrecoverable' Well, I'm not having much luck so far. I have a file that I'm certain is recorded.frm. I also have a file that I'm almost certain is recorded.MYD. I stuck an arbitrary .MYI file in as recorded.MYI, in the hopes that it would be overwritten with good data. Unfortunately, here's the result: r...@pw:/var/lib/mysql/mythconverg# myisamchk -o recorded.MYI Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 - recovering (with keycache) MyISAM-table 'recorded.MYI' Data records: 0 Key 1 - Found wrong stored record at 0 Found link that points at 19054226382932 (outside data file) at 36708 Found link that points at 13556668244051 (outside data file) at 44344 Found link that points at 13673551504478720 (outside data file) at 59948 Found link that points at 5996055336546156 (outside data file) at 59956 Found link that points at 17916077434656 (outside data file) at 64864 Found block with too small length at 69120; Skipped and so on. The result is an empty database, of course. Now, someone mentioned that I need to be sure I have the same versions of MySQL. I thought that I had the same versions, since I thought that I had the same versions of Ubuntu on my working and non-working machines. Unfortunately, it looks like the non-working machine had 5.0.67-0ubuntu6, while the working machine has 5.1.30really5.0.75-0ubuntu10.3 (referring here to Ubuntu package versions, but I think the mysql versions are 5.0.67 and 5.0.75, resp.). Perhaps that's the problem? Or perhaps I'm wrong about the .MYD file and it isn't really recorded.MYD? -- So how do you go on? [...] How will you keep moving for the next few weeks or months until you are known for what you are, the story becomes huge all over the world, and you have reporters at your schools asking you, why? -- Another JSH mystery -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Missing database file names
Jesse F. Hughes je...@phiwumbda.org writes: r...@pw:/var/lib/mysql/mythconverg# myisamchk -o recorded.MYI Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Could these problems be that the dead machine was 64 bit and the working machine 32 bit? I'm rebuilding the dead machine now. When it's got mysql loaded on it, I'll try to run myisamchk on a 64-bit machine. -- Jesse F. Hughes Well, if I can get [my proof of FLT accepted], then I hopefully get a book deal down the road, and maybe I get to go on 'Oprah'. James Harris, on the rewards of mathematical endeavours. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Missing database file names
Dan Nelson dnel...@allantgroup.com writes: In the last episode (Jun 02), Jesse F. Hughes said: After a recent hard drive kerfluffle and the results of fsck, I'm left with a slew of jumbled database files. The file command can tell me the file types, like so: #15901614: MySQL table definition file Version 10 #15901615: MySQL MISAM compressed data file Version 1 #15901617: MySQL table definition file Version 10 #15901618: MySQL MISAM compressed data file Version 1 #15901620: MySQL table definition file Version 10 #15901621: MySQL MISAM compressed data file Version 1 These files are, I'm pretty sure, from my mythtv database. I'm rebuilding my box and it would be nice if I can keep the mythtv database the same as before, but how can I tell which file is which? table definition files are the .frm files; MISAM compressed data files are .MYI files. Unfortunately, you're missing the .MYD files in that list, which don't have a header (sometimes 'file' thinks they're dbase format). You may be in luck, though. The filenames are the inode numbers of the original files, and it looks like they're in sequential order, so the tables were probably created all at once, one after the other. File #15901616 (if it exists) is probably the .MYD file that corresponds to #15901614 (.frm) and #15901615 (.MYI). The numerical order of the files should match the order the mythtv setup script creates its tables. If they aren't in order for some reason, then you can rename the groups of files into a.frm, a.MYI, a.MYD, b.frm, b.MYI, b.MYD, etc, then run show create table a etc to determine the table layout and match them up to known mythtv tables. Brilliant! I'll look into it as soon as I can. And I'll be sure to complain if it doesn't work. Thanks. -- [I want to] stand at the pinnacle of human achievement with no one else in all of history even close, no human being having faced what I have--and survived. Because when all is said and done, make no mistake, the simple truth is, I am better. --James S. Harris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Missing database file names
Dan Nelson dnel...@allantgroup.com writes: In the last episode (Jun 02), Jesse F. Hughes said: After a recent hard drive kerfluffle and the results of fsck, I'm left with a slew of jumbled database files. The file command can tell me the file types, like so: #15901614: MySQL table definition file Version 10 #15901615: MySQL MISAM compressed data file Version 1 #15901617: MySQL table definition file Version 10 #15901618: MySQL MISAM compressed data file Version 1 #15901620: MySQL table definition file Version 10 #15901621: MySQL MISAM compressed data file Version 1 These files are, I'm pretty sure, from my mythtv database. I'm rebuilding my box and it would be nice if I can keep the mythtv database the same as before, but how can I tell which file is which? table definition files are the .frm files; MISAM compressed data files are .MYI files. Unfortunately, you're missing the .MYD files in that list, which don't have a header (sometimes 'file' thinks they're dbase format). You may be in luck, though. The filenames are the inode numbers of the original files, and it looks like they're in sequential order, so the tables were probably created all at once, one after the other. File #15901616 (if it exists) is probably the .MYD file that corresponds to #15901614 (.frm) and #15901615 (.MYI). The numerical order of the files should match the order the mythtv setup script creates its tables. If they aren't in order for some reason, then you can rename the groups of files into a.frm, a.MYI, a.MYD, b.frm, b.MYI, b.MYD, etc, then run show create table a etc to determine the table layout and match them up to known mythtv tables. Ouch. I've found the time to look into the problem a little more closely. I do have a lot of DBase3 files listed, with varying number of records. Unfortunately, they *aren't* in sequential order with the .MYI and .frm files. I probably used a MySQL dump file to create them, so I suppose that the previous files were unlinked and new ones created. Except that the dbase files I see have *lower* inode numbers than the .MYIs and .frms! Is there some way to match the .MYI to its corresponding .MYD? I have only 33 to 47 .MYD files (judging from the output of file and their contiguity), but 74 .MYI files. Thanks. -- Jesse F. Hughes This post marks the end of an era in the world of mathematics. -- James S. Harris and the demise of Galois theory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Missing database file names
After a recent hard drive kerfluffle and the results of fsck, I'm left with a slew of jumbled database files. The file command can tell me the file types, like so: #15901614: MySQL table definition file Version 10 #15901615: MySQL MISAM compressed data file Version 1 #15901617: MySQL table definition file Version 10 #15901618: MySQL MISAM compressed data file Version 1 #15901620: MySQL table definition file Version 10 #15901621: MySQL MISAM compressed data file Version 1 These files are, I'm pretty sure, from my mythtv database. I'm rebuilding my box and it would be nice if I can keep the mythtv database the same as before, but how can I tell which file is which? Any ideas? Thanks much. -- Jesse F. Hughes Me: Quincy, there's only *one* Truth, isn't there? Quincy (age 4): Yeah, and it's *mine*. -- A lesson in postmodernism goes awry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Need help with query optimization
I have the following query: SELECT TAP.ID, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID=TAP.CurrentMemberID JOIN Chapters C On C.ID=M.ChapterID JOIN Schools S On S.ID=C.SchoolID JOIN (SELECT AchievementID,Sum(Points) As TotPoints FROM TorchAwardSelAct TASA WHERE LocalApproveStatus='A' GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.ID WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints The TorchAwardParticipants table has about 84,000 records in it. The query takes almost 40 seconds to return the data, which is only 51 rows. An EXPLAIN returns the following: ++-+++-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+++-+---+-+-++-+ | 1 | PRIMARY | derived2 | ALL| NULL| NULL | NULL| NULL| 4382 | Using temporary; Using filesort | | 1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID | PRIMARY | 4 | Pts.AchievementID | 1 | Using where | | 1 | PRIMARY | M | eq_ref | PRIMARY,IX_Members_3| PRIMARY | 4 | bpa.TAP.CurrentMemberID | 1 | | | 1 | PRIMARY | C | eq_ref | PRIMARY,IX_Chapters_1 | PRIMARY | 4 | bpa.M.ChapterID | 1 | | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | bpa.C.SchoolID | 1 | | | 2 | DERIVED | TASA | index | NULL| AchievementID | 5 | NULL| 161685 | Using where | ++-+++-+---+-+-++-+ What is the best way to optimize this query so that it doesn't take 40 seconds to return the dataset? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
What is unusually high for the # of connections to MySQL?
I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else suggested this string, but after implementing it and re-starting the server, we still had the same problem. My plan is to move the app to a WS03 server tonight in hopes that the issue is the O/S. Can anyone fill me in? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is unusually high for the # of connections to MySQL?
Thank you so much for the reply. I think we may have stepped outside of the MySQL realm now, but here is what I know: * At least a couple times, recycling the application pool started things right up, but that did not always work. * When this is going on, I cannot even get to a page itself, let alone execute a function that runs a query. * One time when this happened, we moved the entire app to an OLD WS03 server. It had only 2 GB, I believe, and it ran like champ after that. Due to circumstances beyond our control, we had to move it back to the WS08 server, and here we are again with the same problem. * I can log on to the server, no problem. I can also log on to MySQL and run queries. I would think that if the database server were the problem, I would not be able to do that. * Do do frequently get errors when this is occurring. These are asp.net errors. here are a few of those: MySql.Data.MySqlClient.MySqlException: error connecting: Timeout expired System.IndexOutOfRangeException: Could not find specified column in results Object reference not set to an instance of an object System.IO.IOException: Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host 42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SHOW VARIABLE'' Key cannot be null The list goes on. As you can see, the errors are all over the board. Some make sense, some do not. For instance, the you have an error in your sql does not, because this same area of code works perfectly Many times throughout the day, and I or no one else has changed it. Plus, the one stating ''SHOW VARIABLE'' makes no sense at all. I have not executed such a function in my code. Thanks, Jesse - Original Message - From: Claudio Nanni To: Jesse Cc: mysql@lists.mysql.com Sent: Thursday, February 25, 2010 6:28 PM Subject: Re: What is unusually high for the # of connections to MySQL? It depends, but 100 is not strange at all, particularly if you have sleeping connections (usually due to slow page loading (ajax?) and/or persistent connections from the app) and any number of connections cannot crash a server, can make it slow or unusable, but not crash it. Watch the app, you could have for loops banging the database, a not optimized app can kill cause a D.O.S.(=bad) of MySQL. Anyway the point is another. I think you cant afford guessing, it will take a huge amount of effort to try to guess why it crashes. Find the more information you can enabling all the logging possible, put server parameters under graphing, the more information you have on the crash, the less you will need to guess. Watch, cpu(load, context switches), ram(usage,swapping), IO. Guess less, know more. Claudio 2010/2/26 Jesse j...@msdlg.com I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else suggested this string, but after implementing it and re-starting the server, we still had the same problem. My plan is to move the app to a WS03 server tonight in hopes that the issue is the O/S. Can anyone fill me in? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
MySQL server has gone away...
We are running MySQL Server version 5.0.67-community-nt-log on a WS03 server. It seems like every once in a while (sometimes once or twice a week, sometimes more), something will happen, then I'll start getting a lot of errors: [MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]MySQL server has gone away This is driving me absolutely nuts. I don't see any errors in the Event Viewer, or the MySQL error log.Does anyone know of any reasons that this might happen? Jesse -- 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 server has gone away...
Persistent connections that are dropped when the service is restarted OR the idle timeout has passed. if this is a connection pool and it hasn't been used in a long time the server can drop the connection but the pool will still thinkbits open and pass it out. Thus the server has gone away message. To my knowledge, the service was not re-started (That's usually logged in the Windows Event Log, and there is nothing there). If it were idle time issues, I could understand one or two, but when this happens, I usually get 20 or 30 errors at one time. This is a web app, and users are either connected at the time, or attempting to connect. Jesse -- 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 server has gone away...
Go into the my.cnf and increase the connection timeout and see if that fixes it. Remember. Connection pooling is about reuse and sometimes when the server is using less connections its using the same ones over and over again. Then you get a burst and then connections that haven't been used are then reissued and that's when the problem arrises. Personally I done use the odbc drive for mysql for similar reasons. I use the .net component with connection pooling disabled and manage them myself. (I have specific other reasons for this as well -- custom data cache classes) I normale use the .net connecter too, but this is a classic .asp application, so ODBC seemed to be my only choice. I'll try increasing the connection time out and see if that helps. Does the IIS memory pooling have anything to do with this, or is it a completely separate thing? Thanks, Jesse -- 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 server has gone away...
An update on this issue. It just happened again, and the first error was: [MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]Lost connection to MySQL server during que Then, after that, I got a BUNCH of [MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]MySQL server has gone away errors. I'm pretty sure that the 2nd errors were caused by the first error. Again, there is nothing strange in the IIS logs, or Windows Event log The database server is on the same machine as the web server, so the network should not be involved here. Why would it suddenly loose connection? Jesse - Original Message - From: Jesse j...@msdlg.com To: mysql@lists.mysql.com Sent: Tuesday, March 24, 2009 11:41 AM Subject: MySQL server has gone away... We are running MySQL Server version 5.0.67-community-nt-log on a WS03 server. It seems like every once in a while (sometimes once or twice a week, sometimes more), something will happen, then I'll start getting a lot of errors: [MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]MySQL server has gone away This is driving me absolutely nuts. I don't see any errors in the Event Viewer, or the MySQL error log.Does anyone know of any reasons that this might happen? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub...@msdlg.com -- 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 server has gone away...
I thought that the # of connections might be a problem at some point too. The last time this happened, there were a lot of connections. Right now, there are 19 connections. How do I tell what the TTL is? I'm not too familiar with perfmon. How do I set it up to watch MySQL connections? Thanks, Jesse - Original Message - From: Gary Smith g...@primeexalia.com To: Jesse j...@msdlg.com; mysql@lists.mysql.com Sent: Tuesday, March 24, 2009 2:55 PM Subject: Re: MySQL server has gone away... Netstat -an. How many 3306 entries do you have in there. What's the TTL on them. Once the pool issues the bad connection multiple issuances of the same connection will probably result in the same error.if yoi birst to 20 connections then drop to 10 for the next 24 hours then burst to 11 that 11th might have been dropped. Next asp request gets 11. Next one gets 11. And so on until the active requests drop to the point where the connections are still active. Does this make sense? It doesn't round robin them (at least to the best of my knowledge) so some may go stayle. When testing the odbc connection some time ago I had connections stay in the pool for a day whereas my timeout was 120 minutes. Anyway. Watch the connection count with the windows perfmon and see if there is a corrolation. Sent via BlackBerry by ATT -Original Message- From: Jesse j...@msdlg.com Date: Tue, 24 Mar 2009 14:47:30 To: Jessej...@msdlg.com; mysql@lists.mysql.com Subject: Re: MySQL server has gone away... An update on this issue. It just happened again, and the first error was: [MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]Lost connection to MySQL server during que Then, after that, I got a BUNCH of [MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]MySQL server has gone away errors. I'm pretty sure that the 2nd errors were caused by the first error. Again, there is nothing strange in the IIS logs, or Windows Event log The database server is on the same machine as the web server, so the network should not be involved here. Why would it suddenly loose connection? Jesse - Original Message - From: Jesse j...@msdlg.com To: mysql@lists.mysql.com Sent: Tuesday, March 24, 2009 11:41 AM Subject: MySQL server has gone away... We are running MySQL Server version 5.0.67-community-nt-log on a WS03 server. It seems like every once in a while (sometimes once or twice a week, sometimes more), something will happen, then I'll start getting a lot of errors: [MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]MySQL server has gone away This is driving me absolutely nuts. I don't see any errors in the Event Viewer, or the MySQL error log.Does anyone know of any reasons that this might happen? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub...@msdlg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Optimizing IN queries?
I have an app that joins results from a MySQL query with the results of a lookup against an external search engine, which returns its results in the form of primary-key id's of one of the tables in my database. I handle this by adding these results with an IN query. (My impression had been that this is faster than a long chain of OR's.) In the simplest case, if I'm _only_ searching against these results, the query will look something like this (I've removed some columns from the SELECT list for readability): SELECT me.id, me.quotation_id, quotation.qt, part.id, part.cit FROM cwGroup me JOIN quotation ON ( quotation.id = me.quotation_id ) JOIN part ON ( part.id = quotation.part_id ) WHERE ( quotation.id IN (107037, 304650, 508795, 712723, 1054653)) When I ran this on a query that generated a moderate number of results (over 1000, but not millions), it took MySQL 26 seconds to reply on my dev box. Can someone suggest what I can look at to speed this up? The section of the manual that talked about optimizing range queries spent a lot of time explaining how they work but very little on how to speed them up. The EXPLAIN didn't really help--only one column got a lot of results, and it's not clear to me why MySQL would take 26 seconds to fetch 1214 records. The EXPLAIN looks like this: --- *** 1. row *** id: 1 select_type: SIMPLE table: me type: range possible_keys: quotation_id key: quotation_id key_len: 4 ref: NULL rows: 1214 Extra: Using where *** 2. row *** id: 1 select_type: SIMPLE table: quotation type: eq_ref possible_keys: PRIMARY,part_id key: PRIMARY key_len: 4 ref: rqs_incs.me.quotation_id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: part type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: rqs_incs.quotation.part_id rows: 1 --- Thanks very much. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Stopping using a server as a slave
I have a server that I've been using as a MySQL Slave for a while now. I want to change it over to a regular server now. I could simply stop the slave (STOP SLAVE), however, I'm concerned that if I re-boot the server, then it'll re-start the slave. What else do I need to do to stop using a server as a slave? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: Got error 139 from storage engine
I was running the 'perror' command from the bash command line of a Linux system. That explains it. This is a Windows server, not Linux. A column of type text will allow a maximum of 65'535 characters being stored, but this could be less when a multibyte character set is used. Not using a multi-character character set. This is regular old english (latin, I think it's called?) What storage engine type are you using? I found some issues with InnoDB regarding this error. Please check the .err log of mysqld as well. InnoDB is the engine I'm using, and searcing the net, I found that there was a problem with InnoDB and this error, but nothing about how to prevent it. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing MySQL Twice
We have a web server that am using solely as a mysql replication server right now, so it's not really being used that much. I'd like to use it as a backup web server as well, but I don't want to mess with the replication that I've got going on. I believe I've heard that you can install MySQL twice on a server, right? How does one go about doing that? This is a Windows 2003 server. I realize that as an alternative, I can install a Virtual Machine and some flavor of Linux (Ubuntu would be my choice, most likely), and that would probably solve the problem, but would it be easier to just install MySQL twice on this machine? Obviously, when I install, I'd want to install to a different directory, put the data in a different directory, and use a different port. How would Windows handle having the MySQL service running twice, would that be a problem? Are there any got 'cha's in this situation? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: Got error 139 from storage engine
prompt perror 139 MySQL error code 139: Too big row Seems you are exceeding some limit. Where did you run the perror command from? I tried to run that in the MySQL command line utility and got an error? At any rate, the field in question is a Text field. My understanding is that the limit of a Text field is about 64K, right? I guess it's possible that limit was exceeded, but not very likely. I'll have to do some more checking. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error: Got error 139 from storage engine
I have an ASP.net web application running on a WS03 server using MySQL 5.0.67-community-nt-log. I have a form that allows the customer to use a visual HTML editor to input text that will appear on a web page. So, the text contains HTML tags. When it tries to save the text to the table in the database, I get the error, #HY000Got error 139 from storage engine. Does anyone know what this is and how to fix it? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: access denied error
Sorry to have bothered you. I believe I found the problem. I had a 1 (One) in the password instead of a l (L). it was working from the command prompt, because I was typing it in the same way, but from the application, it was stored. So, basically, I had the password wrong. Thanks, Jesse - Original Message - From: Jim Lyons To: Jesse Cc: MySQL List Sent: Friday, October 10, 2008 8:28 AM Subject: Re: access denied error What is the PHP connect string? Are you connecting to the database you have access to? are you connecting as the same user (myuser)? Do other php applications work, so you know it's not a php-mysql issue? On Fri, Oct 10, 2008 at 6:59 AM, Jesse [EMAIL PROTECTED] wrote: I have a PHP application that I inherited. I have it running on a Ubuntu 8.04 virtual machine. I have set up a user to access the MySQL database, and from the command prompt, I can access it just fine. For instance, from the Ubuntu prompt, if I type MySQL -umyuser -p, it asks me for the password and after typing it in, I'm in. I can also see the only database I've given that user access to, and I tried a simple SELECT and it worked fine. However, when I access the database through the PHP application, I get the error, Access denied for user 'trucksma_db'@'localhost' (using password: YES) Can anyone tell me what the issue could be here? Why would I have access denied through the PHP application, but not through the MySQL client? Thanks, Jesse -- 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
access denied error
I have a PHP application that I inherited. I have it running on a Ubuntu 8.04 virtual machine. I have set up a user to access the MySQL database, and from the command prompt, I can access it just fine. For instance, from the Ubuntu prompt, if I type MySQL -umyuser -p, it asks me for the password and after typing it in, I'm in. I can also see the only database I've given that user access to, and I tried a simple SELECT and it worked fine. However, when I access the database through the PHP application, I get the error, Access denied for user 'trucksma_db'@'localhost' (using password: YES) Can anyone tell me what the issue could be here? Why would I have access denied through the PHP application, but not through the MySQL client? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1 error
I'm trying to get our new Windows Server 2008 running. I believe I heard that I have to install 5.1 on there to get it running. I think I'm aout to find out, because I'm giong to uninstall 5.1 and install 5.0.xxx At any rate, here's what's going on: I bring up the Query Browser GUI, and log in. So far, no problem. I double-click on a database to open it up. There is a short delay, then it shows me all the tables. I then try to double-click on a table to view it's contents, but I can't. It seems to be disabled. After a short delay, it finally allows me to double-click on it, but then the Execute button is disabled. I checked the service, and it has been stopped. I then check the server.err file, and here's what is in there: 080927 9:42:25 - mysqld got exception 0xc005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=135266304 read_buffer_size=65536 max_used_connections=2 max_threads=800 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 393089 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x1ff9e660 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... 005BEC6Fmysqld.exe!??? 1FFC0550 Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 1FFAA9B0=SHOW FUNCTION STATUS thd-thread_id=5 thd-killed=NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 080927 9:46:03 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 080927 9:46:08 InnoDB: Started; log sequence number 0 273250418 080927 9:46:08 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=Naboo-relay-bin' to avoid this problem. 080927 9:46:09 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted 080927 9:46:09 [ERROR] mysql.user has no `Event_priv` column at position 29 080927 9:46:09 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. 080927 9:46:09 [Note] D:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections. Version: '5.1.28-rc-community' socket: '' port: 3323 MySQL Community Server (GPL) 080927 9:46:36 - mysqld got exception 0xc005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=135266304 read_buffer_size=65536 max_used_connections=1 max_threads=800 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 393089 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x20246c18 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... 005BEC6Fmysqld.exe!??? 2024F530 Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 2027E300=SHOW FUNCTION STATUS thd-thread_id=1 thd-killed=NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 080927 9:53:04 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 080927 9:53:05 InnoDB: Started; log sequence number 0 273250418 080927 9:53:05 [Warning] Neither --relay-log
MySQL 5.1 Function Creation
I'm trying to use existing functions from a restored database from 5.0xx to 5.1, and get an error about the mysql.proc table is missing or corrupt. The mysql.proc table appears to be there, and does not appear to be corrupt. I did a grant select on mysql.proc to user, and that did not make any difference, as it has in the past. So, I decided that I'd delete the function from the database, and try to add it back in, and when I do, I get an error, Failed to CREATE FUNCTION. The code that I'm trying to execute is as follows: CREATE DEFINER = 'root'@'localhost' FUNCTION `Age`(dob DATE) RETURNS int(11) DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE today DATE; SELECT CampStartDate INTO today FROM config; RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0; END; Any ideas what's going on? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error stops replication
Where can I find a general log if I've got one running? What's it's file name, and where it is usually located? Thanks, Jesse - Original Message - From: Ananda Kumar To: Jesse Cc: MySQL List Sent: Tuesday, August 26, 2008 8:03 AM Subject: Re: Error stops replication general log is different from machine.err log, it stores all logs of the db. On 8/26/08, Jesse [EMAIL PROTECTED] wrote: tsa is actually the database. It's running the function AddSchool(), and it appears that something went wrong in that situation. The actual table affected inside the function is school, and that table does exist on both master and slave. If by general log, you mean the log named machine.err, I've checked that file on both master and slave, and there is no reference there at all to a AddSchool( function error. Other than that, I don't see that any other logs are activated (other than the binary logs, of course). Jesse - Original Message - From: Ananda Kumar To: Jesse Cc: MySQL List Sent: Tuesday, August 26, 2008 12:55 AM Subject: Re: Error stops replication does tsa table exists on both master and slave. If you have enabled general log, then u can see most of the errors. I am suspecting that this table is not present in slave On 8/26/08, Jesse [EMAIL PROTECTED] wrote: I have been getting an error which stops replication on my slave server. The error is as follows: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'tsa'. Query: 'SELECT `tsa`.`AddSchool`(_latin1'Grenada High School',_latin1'2',' The thing is that I'm not getting an error on the master server, to my knowledge. When I check the error log, there is no such error in there. Does the Machine.err file log these sort of errors? If not, where can I look to see if it is happening on the master server? I'm running version 5.0.67-community-nt on the slave and version 5.0.51a-nt-log on the master. If anyone has any ideas on what might be causing this, please let me know what you think. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error stops replication
tsa is actually the database. It's running the function AddSchool(), and it appears that something went wrong in that situation. The actual table affected inside the function is school, and that table does exist on both master and slave. If by general log, you mean the log named machine.err, I've checked that file on both master and slave, and there is no reference there at all to a AddSchool( function error. Other than that, I don't see that any other logs are activated (other than the binary logs, of course). Jesse - Original Message - From: Ananda Kumar To: Jesse Cc: MySQL List Sent: Tuesday, August 26, 2008 12:55 AM Subject: Re: Error stops replication does tsa table exists on both master and slave. If you have enabled general log, then u can see most of the errors. I am suspecting that this table is not present in slave On 8/26/08, Jesse [EMAIL PROTECTED] wrote: I have been getting an error which stops replication on my slave server. The error is as follows: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'tsa'. Query: 'SELECT `tsa`.`AddSchool`(_latin1'Grenada High School',_latin1'2',' The thing is that I'm not getting an error on the master server, to my knowledge. When I check the error log, there is no such error in there. Does the Machine.err file log these sort of errors? If not, where can I look to see if it is happening on the master server? I'm running version 5.0.67-community-nt on the slave and version 5.0.51a-nt-log on the master. If anyone has any ideas on what might be causing this, please let me know what you think. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error stops replication
I have been getting an error which stops replication on my slave server. The error is as follows: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'tsa'. Query: 'SELECT `tsa`.`AddSchool`(_latin1'Grenada High School',_latin1'2',' The thing is that I'm not getting an error on the master server, to my knowledge. When I check the error log, there is no such error in there. Does the Machine.err file log these sort of errors? If not, where can I look to see if it is happening on the master server? I'm running version 5.0.67-community-nt on the slave and version 5.0.51a-nt-log on the master. If anyone has any ideas on what might be causing this, please let me know what you think. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL compatibility with Windows Server 2008
We will be purchasing a server pretty soon with Windows Server 2008 on it. I have heard that MySQL is not compatible with WS2008. Is that true? I've also seen instructions for installing MySQL 5.1 on WS2008. Is 5.1 compatible, but earlier versions are not? We run ASP and ASP.Net applications on our servers now, and the new server will be used for the same. So, what about the MySQL .Net connector and the ODBC Driver? Are they compatible with WS2008? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL compatibility with Windows Server 2008
.NET connector is fine. ODBC should be ok but I'm not 100% sure. How about MySQL itself? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for user 'debian-sys-maint'@'localhost'
That was it. Once I removed PASSWORD, it went through, and I'm able to restart MySQL now. Thanks for your help. Jesse - Original Message - From: Ian Simpson To: Jesse Cc: MySQL List Sent: Tuesday, July 22, 2008 4:48 AM Subject: Re: Access denied for user 'debian-sys-maint'@'localhost' Hi Jesse, If you're specifying the password in plain text, you shouldn't put the PASSWORD directive in there; you only use PASSWORD if you're using the hashed password that MySQL will actually store.
Access denied for user 'debian-sys-maint'@'localhost'
OK. This is driving me Nutz 8-p Any time I try to restart mysql, I get the error, Access denied for user 'debian-sys-maint'@'localhost' My understanding is that the password for the debian-sys-maint user is found in /etc/mysql/debian.cnf So, I edit that, and note the password. I then execute the following in MySQL (with the correct password, of course): GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD 'LongPasswordHere' WITH GRANT OPTION To test it out, I try a mysql -u debian-sys-maint -p, type in the password and get the Access denied error again. What's going on? Why can't I get this to work? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accessing remote machine (Ubuntu) from Window
Do you have skip networkin in my.cnf ? No, no skip-networking is in the my.cnf file. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Accessing remote machine (Ubuntu) from Window
I have a Ubuntu 8.04 Server VM set up that I'm using for replication for a WS2003 machine (which is the master).I have used a checksum utility that someone recommended to check if the tables are in sync, but I don't have a GUI installed on Ubuntu, so trying to interpret the selected columns that wrap in the text mysql command prompt is nearly impossible. So, what I'd like to do is connect to the Ubuntu server from one of my other windows machines where I do have a GUI, and can get much better output. So, that being the case, I've made a couple changes to try to get this working. First, I've changed the bind-address setting to refer to the ip address of the machine, not 127.0.0.1. Second, I've done a GRANT ALL ON * TO [EMAIL PROTECTED] identified by 'mypassword'; When I try to connect via the mysql command utility in windows, I'm told ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.128' (10061) The firewall should be an issue in this case, because I'm connecting directly to the machine, and not going through a firewall. To my knowledge, there is not a firewall installed on the Ubuntu server, because I have it connecting to my server for replication, and that appears to be working fine. Are there some additional steps I need to take to make this work? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accessing remote machine (Ubuntu) from Window
Obvious question: Did you restart MySQL? netstat -l should show you what's listening for connections. you'll want to see if its listening on port 3306. Yes. When that didn't work, I re-started the whole server. netstat -l tells me that 192.168.1.128:mysql is listening. It lists a foreign address of *.* When I re-issue the command with -n, I can seee that it is, indeed, listening on port 3306. Can you think of any other reasons why the connection would fail? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accessing remote machine (Ubuntu) from Window
I was wondering how I get it to listed on all ports. When I try 0.0.0.0, and try to restart, the restart fails, and when it tries to start again, I get the error, /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password:YES)' This has been happening for a day or so, and I don't know if it's related to my problem or not. After changing the grant to use an IP address, I still cannot log in. Jesse - Original Message - From: Curtis Maurand [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Thursday, July 17, 2008 4:31 PM Subject: Re: Accessing remote machine (Ubuntu) from Window I cannot. Are you trying to connect via the localhost. It may still be trying to connect to localhost. You might try changing the bind-address statement to bind-address = 0.0.0.0 so that it also listens to localhost as well. Also if you're trying to connect to an ip address instead of a named host, I've found that you have to code your grant statement to have an ip address instead of a hostname. Curtis Jesse wrote: Obvious question: Did you restart MySQL? netstat -l should show you what's listening for connections. you'll want to see if its listening on port 3306. Yes. When that didn't work, I re-started the whole server. netstat -l tells me that 192.168.1.128:mysql is listening. It lists a foreign address of *.* When I re-issue the command with -n, I can seee that it is, indeed, listening on port 3306. Can you think of any other reasons why the connection would fail? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help figuring out mk-table-checksum
I want to make sure that my master and slave machines are in sync. From what I hear, mk-table-checksum is a good way to do that. So, I downloaded and installed it on my Linux box (the slave). So, here's what I've got: WS2003 Master (IP 192.168.1.10) Ubuntu 8.04 Linux Slave (IP 192.168.1.128) I log into the Ubuntu box, and execute the following command: perl mk-table-checksum -replicate=test.checksum --user root --password mypassword -replcheck 192.168.1.10 localhost After a little while of churning away, I check the test.checksum table, and there is nothing in there. I'm hoping that means that it worked, and that there are no record differences, am I correct? Is there a better way to be sure? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accessing remote machine (Ubuntu) from Window
My settings are the same, except I have the bind-address set to my local IP of 192.168.1.128. I tried 0.0.0.0, and I still cannot connect from other machines. Jesse - Original Message - From: Curtis Maurand [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Thursday, July 17, 2008 5:41 PM Subject: RE: Accessing remote machine (Ubuntu) from Window I just checked my ubuntu config and I have: Port = 3306 Bind-address = 0.0.0.0 Pid-file = /var/run/mysqld/mysqld.pid Socket = /var/run/mysqld/mysqld.sock Nothig is in upper case. My phone is doing that for me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
you not ADD Binary, you need to remove BINARY ... ;-) Sorry, I misunderstood. convert the string to latin1 or utf8 o.s.s. LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a non-binary string did you tried? Well, I thought I had tried this, but with all the other things that I'd tried, I guess I had everything mixed up. I started with a clean routine, used Convert(... using UTF8), and it works perfectly now. That's what it was. Thanks for the help! Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function Still Not Working
Sorry for posting this again, but I got only one response last time, and I'm
still having the problem. I spent HOURS the other day manually going
through the data and Properizing these things by hand. I don't want to do
that again if I can avoid it. If anyone has any clues on this one, I would
appreciate it.
The only difference in this and what I have now is that someone suggested
changing it to Deterministic, which I did, and that didn't change the
output. I also changed SQL SECURITY DEFINER to SQL SECURITY INVOKER,
and that didn't make a difference either.
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When I do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a problem
with the older version that would cause this function not to work properly?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
I am curious about that, as well. It brings to mind a discussion that happened on this list last week (I believe) about case sensitive/insensitive use of LIKE. I believe the synopsis was that tables are either created as case-insensitive, or the search needs to be specified as case sensitive (with BINARY). Could this be a similar issue, perhaps? One table is specifically case-insensitive with regard to the function, and the other is not? In this particular case, we're not dealing with any tables. I have also tried adding BINARY to the mix as well, and it didn't make any difference. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
does it work outside the function?
Yes, If I run:
select CONCAT(UPPER(SUBSTRING('JESSE',1,1)),LOWER(SUBSTRING('JESSE',2)));
replacing cInput with 'JESSE', it returns Jesse as it should.
did you tried SUBSTRING(cInput, 2)?
Tried replacing SUBSTRING(cInput FROM 2) with SUBSTRING(cInput, 2) and it
didn't make any difference.
did you tried with converting?
I have had issues with this in other areas before, but didn't think about it
this time. However, I tred CONVERT with UTF8 and latin1 as you suggested.
LOWER() (and UPPER()) are ineffective when applied to binary strings
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the
string to a non-binary string:
mysql SET @str = BINARY 'New York';
mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
I converted the function over to use a variable, and got the same results.
Here's the new function:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
Declare str Text;
Declare cReturn Text;
Set @str=BINARY cInput;
Set @cReturn =
CONCAT(UPPER(SUBSTRING(@str,1,1)),LOWER(SUBSTRING(@str,2)));
RETURN @cReturn;
END;
Still doesn't work. This is driving me NUTz 8-p
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function Not Working
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When I do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a problem
with the older version that would cause this function not to work properly?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication still stopping...
A couple of thoughts. Do you have slaves with duplicated server IDs? That seems most likely to me. Nope. I've got one master, and one slave. The server ID is set to 1 on the master, and it's set to 2 on the slave. If that's not it, is the max_packet_size mismatched on the master and slave? I don't find max_packet_size in the My.ini file on either server, and when I do a show variables on both, max_packet_size is not listed on either of them. Can you connect to the master and view the binary log event at the position it's trying to read, with SHOW BINLOG EVENTS? That's where things get squirley. The position it reports always seems to be incorrect. For instance, when this was happening previously, I know that it had made it to a later position in the log. However, when replication stopped, it reported a position earlier in the file. This one, for instance, reports position 195. the Nearest one I have starts at position 98 and ends at position 1032. This is an update statement. If my logic is not flawed, I'm thinking that I should follow starting at 98 out until I get to position 195. When I do that, I come to: RegOpenDate = '2007-11-05 00:00:00', which is part of the udpate statement. This appears normal to me. I've checked, and it is a DateTime field, and it is exactly the same on both the master and slave. Can you use the mysqlbinlog tool to verify that the binary log isn't corrupted on the master? I've dumped the log to a text file. What, exactly, should I look for? The only suspicious thing I see is the first entry: # at 4 #071020 15:45:34 server id 1 end_log_pos 98Start: binlog v 4, server v 5.0.17-nt-log created 071020 15:45:34 at startup # Warning: this binlog was not closed properly. Most probably mysqld crashed writing it. ROLLBACK; Don't know why it would do this. However, I set the master_log_pos to 98 before re-starting the slave after re-setting it last time. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] Memory Usage on Windows? Re: Replication still stopping...
as i can see you are running mysql on windows. If i start my db server (5.0.45/innodb/win2k) the server uses about ~80K handles (as seen in taskmgr) and memory usage increases around 1g. Taskmgr.exe says that there is some swapping (the box has only 1gb ram). The DB itself is small (~50mb or so). My Question is, did you have the same things on your box? Did you have performace issues which resultes from the memory usage? I can't even keep it running for longer that 24 hours, and I don't know why I haven't even started looking into memory issues or performance. When it is runnning, as a test, I change a record on the master, and I notice that almost immediately, the same change is made on the slave. Works perfectly for a few hours, then it just stops working. It almost appears to be a network related issue, but I can't seem to track it down. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication still stopping...
I tried posting this on the Replication list, and got no response. Maybe someone here can help... OK. Still battling this issue after weeks of working with it. I'm racking my brains. I re-set the slave again on Saturday, and got replication started again. It was working fine until this afternoon some time. Before starting things up, I cleaned the error log out completely, so it would be clean before I started. Here is my error log in total: 071020 14:43:51 InnoDB: Started; log sequence number 0 142497221 071020 14:43:51 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.45-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 071020 14:43:51 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.06' at position 98, relay log 'C:\Program Files\MySQL\MySQL Server 5.0\Data\dlgsrv-relay-bin.02' position: 235 071020 14:43:52 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.06' at position 98 071020 15:43:32 [Note] Slave: received end packet from server, apparent master shutdown: 071020 15:43:32 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.06' position 98 071020 15:43:33 [ERROR] Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'webserver' (10061)' errno: 2003 retry-time: 60 retries: 86400 071020 15:45:56 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.06' at position 98 071021 15:02:21 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.07' at position 195 I checked periodically on the server, and everything seemed to be working. The last time I checked was this morning sometime around 8:00 pr so. Still running. As you can see, however, it juststopped processing at 15:02:21 this afternoon. The master server was not down. I was in and out of web sites that use the MySQL database on the master several times, and it always worked just fine, and never gave me an error. It almost appears as though the slave cannot communicate with the master. It looks like it tried 86,400 times, which I guess took almost a day to do, and just gave up. Why would it be able to connect initially to the server, then suddenly not be able to connect any more? Any help or suggestions anyone can offer is greatly appreciated! Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROP TRIGGER IF EXISTS throws error??
I'm attempting to restore a couple of backups, and part way through, I get the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS alumni' at line 1 Query is: DROP TRIGGER IF EXISTS `alumni`; Seems perfectly valid to me. If I remove the IF EXISTS part, then I get the error, trigger doesn't exist. What's going on? This usually works fine. I'm running version 5.0.45-community-nt Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Manager Issue
Right click in query editor window, select Properties from popup menu, Select Quick Code in Editor options tree. On the right side of the dialog window in Automatic features group deselect Code completion and/or Code Parameters checkboxes. Thanks so much for the reply. I usually right-click on everything before I post a message, but there were so many things in that menu, I missed the properties option. However, I just went in there and checked, and Code completion was already unchecked. Code parameters WAS checked, and I unchecked it. I then started typing a query select * from i... and was interrupted with a table pick pop-up box. Interestingly enough, after trying it again, that box didn't pop-up. I then continued my query ...where transferdate=, and was interrupted by a pop-up function pick box. Again, interestingly enough, when I tried this again, it didn't do that. In fact, I can't get it to do it again. Maybe it's turned off. We'll see. If not, maybe I'll try the ticket system if it's working now. Thanks for your help. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP TRIGGER IF EXISTS throws error??
Are you sure that's the exact query and error message? The query has a backtick; the error message has none, which is unusual for a purely syntactic error. It makes me suspicious that the error is coming from something else: maybe a subtly mangled file. I found the problem. When I ran the MySQL Administrator to start the restore, I didn't notice that I was connected to another server. In face, I was connecting to the server from which the backup came from originally, but from another machine. Once I caught this, and changed it to the local machine, the restore (and the DROP TRIGGER command) went through fine. It may simply be that the user I used to connect to the other server didn't have access rights to do that, I'm not sure, but the error message indicated a syntactical error (as you noted), not an access error. So, it's a mystery to me why the message didn't seem to indicate it properly, but I've got it working now. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Manager Issue
Sorry for posting this here, but 1) I didn't see a 3rd party MySQL list, and 2) I tried several times to post a support ticket on the SQLManager.net site, and it simply would not save my ticket. There doesn't seem to be any other way to request support from them either, except through their broken Support Ticket system. Anywhere, here's my question. I'm hoping that someone out there uses the MySQL Manager 2007 tool in Windows to visually manager their MySQL Databases, and can answer this question. When I'm typing in a query, it is constantly annoying me by popping up function, table, or field suggestions as I type. I have to constantly press the escape key to get rid of the box. Is there some way to turn this feature off that anyone's aware of? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Issues
I posted this message on the Replication list, but have got no responses, so, I figured I'd try here. I just set up Replication recently with a Web Server 2003 server being the master and a Windows 2000 server machine being the slave. When I got done, I did a few tests, and all appeared to be fine. Sunday Morning, I checked the event logs on the slave machine, and there was an entry in there that said Slave SQL thread exiting, replication stopped in log 'mysql-bin.01' at position 190 this occurred almost exactly 2 hours after I had finished setting up the slave. I checked, and sure enough, replication no longer appeared to be working when I tried to change a value on the master, it did not seem to replicate to the slave (yes, I realize there can be some time delay, but the machines were not busy at all). I did a SHOW SLAVE STATUS on the slave, and it came back with Waiting for master to send event. But, it wasn't not working. So, I stopped the MySQL Server on the slave machine, and re-started it. It seemed to start alright, but immediately gave an error in the event log about a foreign key violation. I re-sync'd that database (I'm going to post a separate message about that), and continued on. I checked again this morning (Monday),and it appears to have stopped again, because some new records that were added to the master have not been added to the slave. My question is this; What caused the slave to just stop replicating? There was no explanation at all that I could tell. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Proper way to Re-cync in Replication
I posted this on the Replication list, but have received no reply, so I figured I'd try here. When the databases get out of sync during replication, what is the best way to re-sync them on a Windows system? The only way I know of right now is to do it late at night, and pray that no one gets in while you're doing it. Stop the slave, do a backup (using MySQL Administrator, or mysqldump), note the log position on the master, restore the data on the slave, change the log position on the slave, then re-start the slave. Is this the best way to do it? My issue has always been that I need the master MySQL running all the time. I might be able to stop it for a minute or two, and I'm sure that'll generate errors, but what can I do. But, in order to run a backup, it needs to be running. I've tried the copy the physical files thing, and that has never worked for me. Don't know why, but it doesn't. Any ideas? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issues
I can't think of anything other than looking deeper into the MySQL log files. I'm not sure where those are on Windows, but I imagine it's not all going to the event log. Maybe there is something in c:\program files\mysql 5\ (or wherever you installed MySQL). Try looking for a file named mysql.err or mysqld.err. I found an error log named dlgsrv.err. In it is a similar entry to the original error, I got: 070929 17:49:51 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.01' at position 192 It looks like that may be the point at which it just stopped. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issues
What is the event at that position in the binlog? Use the mysqlbinlog tool to see, or on the master you can run SHOW BINLOG EVENTS (check the manual for the full syntax). If I'm reading this right (and I'm probably not), the log goes from position 98 to position 557 Position 98 has a DELETE FROM query on the mysql.db table. Position 557 has a FLUSH PRIVILEGES command. The position doesn't make any sense to me. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Issues
Hopefully that's enough info for you to decide whether you read it right. But it looks like your slave had some issue with that. Do you have a lot of privileges? One of the Google engineers told me he's sometimes seen FLUSH PRIVILEGES break replication with a lot of privileges. Whoever was doing that query should have been using GRANT and REVOKE instead -- or DROP USER. I'm the only one that gets in and does anything with the privileges. I always do it with the root user as well, and most of the time, I just use the MySQL Administrator to do that. However, because I was following directions on setting up replication from the help file, I was just using the MySQL Command line. But, I did not issue these commands, it may be that I went into MySQL Administrator to look around or something, and ended up changing something, but I'm not sure what it would have been. I don't recall doing a DROP USER either. In fact, all of the user issues that I did, I did on Friday night, and I haven't touched the users that I'm aware of. I wasn't even in the system at all Saturday Night (I don't think). Anyway. It's all a big mess and jumble. I think I'm going to attempt re-syncing things and re-starting things tonight and trying to start fresh again. I'll see what happens tomorrow. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Foreign Key Issue
Check the output of SHOW INNODB STATUS. You should see a more helpful error message there. This shows the following information: LATEST FOREIGN KEY ERROR 070519 15:15:28 Error in foreign key constraint of table woodturners/#sql-2dc_1e: FOREIGN KEY (`ParticipantID`) REFERENCES `participants` (`ID`) ON DELETE CASCADE: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html for correct foreign key definition. However, I found the problem. When it's stated the the types of fields need to be exactly the same, they're not kidding. I found that in one table the ID field was Unsigned, but in the other, it was not unsigned. I changed the other one to unsigned, and it's adding a foreign key now. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Disabled?
I'm running version 5.0.22-community-nt of MySQL. For some reason, InnoDB is disabled. I have it installed on my XP Pro machine, and it's working fine, and one on a Windows 2003 server, which is working fine, and another on a Windows 2003 server, which is the one with a problem. At one point, this server was a replication slave. I recently stopped the replication process so I could use it as a separate server. I didn't notice any issues with it until this one. When I try to make a table an InnoDB, it's not available as a selection in SQL Manager. When I do a SHOW ENGINES, InnoDB is listed as DISABLED. when I edit My.ini and try to make InnoDB the default engine, then the MySQL Service will not restart. I've come to rely on InnoDB, because it allows foreign keys. Otherwise, I have to write queries to remove child records manually Does anyone have any suggestions? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Disabled?
Found the problem. After searching for a while, I found where someone deleted the ib_logfile*.* in the data directory. I did that, and that cleaned it up. Jesse - Original Message - From: Jesse [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Saturday, May 19, 2007 4:22 PM Subject: InnoDB Disabled? I'm running version 5.0.22-community-nt of MySQL. For some reason, InnoDB is disabled. I have it installed on my XP Pro machine, and it's working fine, and one on a Windows 2003 server, which is working fine, and another on a Windows 2003 server, which is the one with a problem. At one point, this server was a replication slave. I recently stopped the replication process so I could use it as a separate server. I didn't notice any issues with it until this one. When I try to make a table an InnoDB, it's not available as a selection in SQL Manager. When I do a SHOW ENGINES, InnoDB is listed as DISABLED. when I edit My.ini and try to make InnoDB the default engine, then the MySQL Service will not restart. I've come to rely on InnoDB, because it allows foreign keys. Otherwise, I have to write queries to remove child records manually Does anyone have any suggestions? Thanks, Jesse -- 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]
Create Foreign Key Issue
Currently using MySQL version 5.0.22-community-nt on a Windows XP Pro box (my development machine). When I attempt to add a Foreign Key to one of my tables, I get the error, Can't create table '.\woodturners\#sql-2dc_8.frm' (errno: 150). I have checked through all of the standard issues: * Both tables are InnoDB * The field types in both tables are exactly the same, Int(11) * There is an index in both tables for the referencing fields. * There are no orphaned records in the child table. In fact, I tried to create a foreign key on another table that I had just created, which had no records what-so-ever, and ran into the same error. Which makes it look like an issue with the Parent table. I'm not aware of anything else that would cause this. The parent table also has a foreign key on it, making for a 3 layer cascading delete process if necessary, but I've done this before w/o problems. What else can I check to make this work? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join Error
I'm running the following query: SELECT S.Name As School,S.State, CASE WHEN Unfinished.Cnt IS NULL THEN 'Yes' ELSE 'bNo/b' END As AllSubmitted, COALESCE(Part.Cnt,0) As StudentCount, COALESCE(Adv.Cnt,0) As AdvisorCount FROM InvHead I JOIN Schools S On S.ID=I.ChapterID LEFT OUTER JOIN (SELECT I2.ChapterID,Count(*) As Cnt FROM InvHead I2 WHERE I2.RegFinishedDate IS NULL GROUP BY I2.ChapterID) AS Unfinished ON Unfinished.ChapterID=S.ID LEFT OUTER JOIN (SELECT ChapterID,Count(*) As Cnt FROM Participants P JOIN StatusCodes S ON S.Code=P.Status WHERE S.PersonType='S' GROUP BY ChapterID) AS Part ON Part.ChapterID=S.ID LEFT OUTER JOIN (SELECT ChapterID,Count(*) As Cnt FROM Participants P JOIN StatusCodes S ON S.Code=P.Status WHERE S.PersonType='A' GROUP BY ChapterID) AS Adv ON Adv.ChapterID=S.ID GROUP BY S.State, S.Name ORDER BY S.State,S.Name When I run this through my asp.net application, I get the error, #42000The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay When I refresh, ir just backup and try again, it runs fine. I've tried setting SQL_BIG_SELECTS=1 in my.ini, but MySQL won't re-start in services when I do that. MAX_JOIN_SIZE is set to 4294967295. That seems pretty huge to me, But, I guess I can change it, but what do I change it to? Or, is there a better way to do this by changing my query? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ROLLUP and Text on total lines
Using ROLLUP in my GROUP BY, I have been able to get MySQL to insert sub-total lines, which is great. However, it would be even better if I could determine the text on those subtotal lines. Here's my query: SELECT Sc.State, St.Description, Count(*) FROM InvHead I JOIN Schools Sc on Sc.ID=I.ChapterID JOIN Participants P ON P.InvNo=I.InvNo JOIN StatusCodes St ON P.Status=St.Code GROUP BY Sc.State, St.Description WITH ROLLUP I have seen examples that use (I believe) MS SQL and the Grouping() function to determine if the row is an inserted subtotal or total row. Is there a way to determine if the row is a subtotal or total row, and change the text to indicate which it is? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with query
The following query works just fine: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum However, I would like to add the to have a total of the adults and kids on the bus, so I change my query as follows: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity, Kids + Adults As GT FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum When I try to execute this, I get the error, Unknown column 'Kids' in 'field list' How do I properly add these together to get a total column? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Duuuh. Why didn't I think of that. What is MySQL's issue with referring to the variables (As assignments, whatever you want to call them)? I've had issues like this in situations like this one, when trying to use them in the ORDER BY clause, and other places. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible Replication Issue?
I'm running MySQL version 5.0.22-community-nt on a Windows 2003 server. I a noticing A LOT of errors in there like the following: C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Incorrect information in file: '.\nfcamp\societies.frm' This spans multiple databases and tables. I am using replication, but I thought that I was replication only one of the databases. It could be that it's attempting to replicate these other databases that I don't want replicated. It's been a while since I set up the replication, so I don't remember where I need to look for this. Does anyone have any pointers? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stopping Replication
I'm trying to stop replication between two servers that I've got here. I set it up originally, to play with it and see how it works. However, I now want to use these two servers separately. I have edited the my.ini file on both the server and slave machines, and removed the server-id from them. I have also executed a stop slave on the slave server. However, it appears that when I stop and re-start the server that was the slave, it tries to re-start replication again, because I get the error, Failed to create slave threads in the event log. What else do I need to do to stop the replication process? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stopping Replication
Thanks,
I've removed these files. Also, there are a BUNCH of files named
webserver2-relay-bin.{some#} in the data folder. Can I delete all of
these (including the .index file)?
Thanks,
Jesse
- Original Message -
From: Brown, Charles [EMAIL PROTECTED]
To: Michael Dykman [EMAIL PROTECTED]; Gerald L. Clark
[EMAIL PROTECTED]
Cc: Jesse [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com
Sent: Tuesday, April 24, 2007 4:31 PM
Subject: RE: Stopping Replication
(Read ref manual: 6.4)
Here's the best kept secret:
The Slave has two files called master. info and relay-log.info. The
slave uses these two files to keep track of how much of the master's
binary log it has processed and all pertinent info about the master( i.e
userid, password, hostname) are all documented in the master.info file.
Did you say you would like to stop replication - PERIOD! Delete these
files or use CHANGE MASTER TO statement to change replication
parameters.
Why: The contents of master.info overrides some of the server options
specified on the command line or in my.cnf. (Read ref manual: 6.4)
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 24, 2007 2:35 PM
To: Gerald L. Clark
Cc: Jesse; MySQL List
Subject: Re: Stopping Replication
you mill also do well to eliminate the master.nfo file from your data
directory.
On 4/24/07, Gerald L. Clark [EMAIL PROTECTED] wrote:
Jesse wrote:
I'm trying to stop replication between two servers that I've got
here.
I set it up originally, to play with it and see how it works.
However,
I now want to use these two servers separately.
I have edited the my.ini file on both the server and slave machines,
and
removed the server-id from them. I have also executed a stop slave
on
the slave server. However, it appears that when I stop and re-start
the
server that was the slave, it tries to re-start replication again,
because I get the error, Failed to create slave threads in the
event
log.
What else do I need to do to stop the replication process?
Thanks,
Jesse
You have to remove the master information from the slave's my.ini
file.
--
Gerald L. Clark
Supplier Systems Corporation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.
If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.
Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Event Log Error
I'm running MySQL version 5.0.22-community-nt on a Windows 2003 server. I a noticing A LOT of errors in there like the following: C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Incorrect information in file: '.\nfcamp\societies.frm' This spans multiple databases and tables. I am using replication, but I thought that I was replication only one of the databases. It could be that it's attempting to replicate these other databases that I don't want replicated. It's been a while since I set up the replication, so I don't remember where I need to look for this. Does anyone have any pointers? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query to return Multiple values in a field?
Using MySQL 5.0.22-community-nt-log Is there a way to compose a query that would show multiple values in field? In other words, I have a table structure like this: Activity 1 Section 1 Schedule 1 Schedule 2 Section 2 Schedule 1 Activity 2 Section 1 Schedule 1 Schedule 2 So, I would like to return a query that shows the activity name, section #, and in a single field, all the schedule entries. Just to see if it would work, I did the following query: SELECT S.*, CONCAT(C.FirstName,' ',C.LastName) AS CounselorName, (SELECT Count(*) FROM CamperActivitySelections WHERE SectionID=S.ID) AS Cap, (SELECT StartDateTime FROM SectionSchedule SS WHERE SS.SectionID=S.ID) As Sh FROM Sections S LEFT JOIN Counselors C ON C.ID=S.CounselorID WHERE ActivityID=65 This works fine if there is only one schedule entry. However, when there are two schedule entries or more, it returns an empty data set. If there were some way to get the results and add them all together, separated by a br, that would be perfect. Just make that the field value, which I could then display. The only other alternative is to put this into a loop in my program and create a query for each row to get the schedule information. Any help is appreciated. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to return Multiple values in a field?
I'm not sure this will work for you, but look at the GROUP BY clause and the GROUP_CONCAT() function. That was exactly what I needed! I converted my query as follows, and I'm getting exactly what I was looking for: SELECT S.*, CONCAT(C.FirstName,' ',C.LastName) AS CounselorName, (SELECT Count(*) FROM CamperActivitySelections WHERE SectionID=S.ID) AS Cap, CONVERT(GROUP_CONCAT(StartDateTime ORDER BY StartDateTime SEPARATOR 'br'),Char) As Sh FROM Sections S LEFT JOIN Counselors C ON C.ID=S.CounselorID LEFT JOIN (SELECT SectionID,StartDateTime FROM SectionSchedule SS) As SS ON SS.`SectionID`=S.ID WHERE ActivityID=65 GROUP BY S.ID THANKS FOR THE HELP! Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE / not UPDATE??
When I run the query: UPDATE InvHead I JOIN (SELECT InvNo,Sum(Rate + CASE WHEN AttendingAcademy=1 THEN 30 ELSE 0 END) AS InvTot FROM Participants P GROUP BY InvNo) AS PartSum ON PartSum.InvNo=I.InvNo SET I.Total=PartSum.InvTot WHERE (I.InvoiceType='CL' OR I.InvoiceType='CSS') AND I.RegFinishedDate IS NOT NULL It initially updates several rows. However, when I run it again, it updates no rows at all. If I then go in and manually change the Total to an incorrect value, then run it again, it updates that one row. Does MySQL check a value before updating it, and if it is the same as the value that it's updating it with, it doesn't bother updating it again? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE / not UPDATE??
Don't know? 5.0.22-community-nt-log Win XP Pro InnoDB Jesse - Original Message - From: sol beach To: Jesse Sent: Monday, April 02, 2007 4:22 PM Subject: Re: UPDATE / not UPDATE?? Might this behavior be version dependent; which you neglected to provide. Same for OS name version; plus underlying storage engine type? On 4/2/07, Jesse [EMAIL PROTECTED] wrote: When I run the query: UPDATE InvHead I JOIN (SELECT InvNo,Sum(Rate + CASE WHEN AttendingAcademy=1 THEN 30 ELSE 0 END) AS InvTot FROM Participants P GROUP BY InvNo) AS PartSum ON PartSum.InvNo=I.InvNo SET I.Total=PartSum.InvTot WHERE (I.InvoiceType='CL' OR I.InvoiceType='CSS') AND I.RegFinishedDate IS NOT NULL It initially updates several rows. However, when I run it again, it updates no rows at all. If I then go in and manually change the Total to an incorrect value, then run it again, it updates that one row. Does MySQL check a value before updating it, and if it is the same as the value that it's updating it with, it doesn't bother updating it again? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not Sorting Correctly
make an alias for the field you want as sort key and use that. you don't need to do the calculation twice. I would not be surprised if the sort started to behave. Didn't help. The results are exactly the same. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not Sorting Correctly
Strange. I'm running the same exact version, and it's not the same. What
field types are you using? Mine are as follows:
RoomNo VarChar(10)
LastName VarChar(25)
FirstName VarChar(25)
the values that I put into Room No are 1,2,3, etc. I'm not storing
001,002,003, etc in there.
Jesse
- Original Message -
From: Zhaowei [EMAIL PROTECTED]
To: Jesse [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, March 23, 2007 5:03 AM
Subject: Re: Not Sorting Correctly
Hi, Jesse,
I did a small test and found it was in order. My version is
+-+
| version() |
+-+
| 5.0.22-community-nt-log |
+-+
select right(concat('000',text_id),3) AS text,name from
an order by right(concat('000',text_id),3),name;
+--+-+
| text | name|
+--+-+
| 001 | cat |
| 001 | dog |
| 001 | monkey |
| 001 | rat |
| 001 | wolf|
| 002 | cat |
| 002 | whale |
| 003 | lax |
| 003 | penguin |
| 006 | ostrich |
+--+-+
10 rows in set (0.00 sec)
On 3/23/07, Jesse [EMAIL PROTECTED] wrote:
When I run the following query:
SELECT RIGHT(CONCAT('000',RoomNo),3),LastName,FirstName
FROM ConfHotelDet
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY RIGHT(CONCAT('000',RoomNo),3), LastName, FirstName
I get the following result:
001AndersonKayla
002BartonGreg
003BeatyBrooke
001BrownPaige
002BynumWesley
008ClarkAndrew
008ClarkRamsey
Etc...
As you can see, it's out of order.
Jesse
- Original Message -
From: Ales Zoulek [EMAIL PROTECTED]
To: Jesse [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, March 19, 2007 9:06 PM
Subject: Re: Not Sorting Correctly
pls, post result of:
SELECT RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName FROM
Ales
On 3/19/07, Jesse [EMAIL PROTECTED] wrote:
I have an app that I've converted to MySQL from MS SQL. I used to use
the
following to force a Alpha field to sort as if it were numeric (I
know,
perhaps it is better if I made the field numeric to begin with, but
it's
not, and I don't remember why, but that's not the question here):
ORDER BY RIGHT('000' + RoomNo,3),LastName,FirstName
I converted this to the following in MySQL:
ORDER BY RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName
In MS SQL, it would sort correctly:
1 Kayla Andre
1 Paige Brackon
1 Kasie Guesswho
1 Katelyn Hurst
2 Craig Bartson
2 Wesley Bytell
2 Kevin Peterson
2 Bryan Wilton
etc...
Now, the Above (RIGHT(CONCAT...)-MySQL Version), seems to ignore the
first
sort field, and simply sorts alphabatically:
1 Kayla Andre
2 Craig Bartson
1 Paige Brackon
2 Wesley Bytell
1 Kasie Guesswho
1 Katelyn Hurst
2 Kevin Peterson
2 Bryan Wilton
I finally ended up with:
ORDER BY CAST(RoomNo AS UNSIGNED), LastName, FirstName
Which works perfectly, but I'm just wondering why the first attempt
(right(concat...)) didn't work?? Any ideas?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
--
Ales Zoulek
NetCentrum s.r.o.
+420 739 542 789
+420 604 332 515
ICQ: 82647256
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Best Regards,
Yours Zhaowei
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not Sorting Correctly
When I run the following query:
SELECT RIGHT(CONCAT('000',RoomNo),3),LastName,FirstName
FROM ConfHotelDet
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY RIGHT(CONCAT('000',RoomNo),3), LastName, FirstName
I get the following result:
001AndersonKayla
002BartonGreg
003BeatyBrooke
001BrownPaige
002BynumWesley
008ClarkAndrew
008ClarkRamsey
Etc...
As you can see, it's out of order.
Jesse
- Original Message -
From: Ales Zoulek [EMAIL PROTECTED]
To: Jesse [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, March 19, 2007 9:06 PM
Subject: Re: Not Sorting Correctly
pls, post result of:
SELECT RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName FROM
Ales
On 3/19/07, Jesse [EMAIL PROTECTED] wrote:
I have an app that I've converted to MySQL from MS SQL. I used to use
the
following to force a Alpha field to sort as if it were numeric (I know,
perhaps it is better if I made the field numeric to begin with, but it's
not, and I don't remember why, but that's not the question here):
ORDER BY RIGHT('000' + RoomNo,3),LastName,FirstName
I converted this to the following in MySQL:
ORDER BY RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName
In MS SQL, it would sort correctly:
1 Kayla Andre
1 Paige Brackon
1 Kasie Guesswho
1 Katelyn Hurst
2 Craig Bartson
2 Wesley Bytell
2 Kevin Peterson
2 Bryan Wilton
etc...
Now, the Above (RIGHT(CONCAT...)-MySQL Version), seems to ignore the
first
sort field, and simply sorts alphabatically:
1 Kayla Andre
2 Craig Bartson
1 Paige Brackon
2 Wesley Bytell
1 Kasie Guesswho
1 Katelyn Hurst
2 Kevin Peterson
2 Bryan Wilton
I finally ended up with:
ORDER BY CAST(RoomNo AS UNSIGNED), LastName, FirstName
Which works perfectly, but I'm just wondering why the first attempt
(right(concat...)) didn't work?? Any ideas?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
--
Ales Zoulek
NetCentrum s.r.o.
+420 739 542 789
+420 604 332 515
ICQ: 82647256
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not Sorting Correctly
I have an app that I've converted to MySQL from MS SQL. I used to use the
following to force a Alpha field to sort as if it were numeric (I know,
perhaps it is better if I made the field numeric to begin with, but it's
not, and I don't remember why, but that's not the question here):
ORDER BY RIGHT('000' + RoomNo,3),LastName,FirstName
I converted this to the following in MySQL:
ORDER BY RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName
In MS SQL, it would sort correctly:
1 Kayla Andre
1 Paige Brackon
1 Kasie Guesswho
1 Katelyn Hurst
2 Craig Bartson
2 Wesley Bytell
2 Kevin Peterson
2 Bryan Wilton
etc...
Now, the Above (RIGHT(CONCAT...)-MySQL Version), seems to ignore the first
sort field, and simply sorts alphabatically:
1 Kayla Andre
2 Craig Bartson
1 Paige Brackon
2 Wesley Bytell
1 Kasie Guesswho
1 Katelyn Hurst
2 Kevin Peterson
2 Bryan Wilton
I finally ended up with:
ORDER BY CAST(RoomNo AS UNSIGNED), LastName, FirstName
Which works perfectly, but I'm just wondering why the first attempt
(right(concat...)) didn't work?? Any ideas?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY issue
I have an app that I've converted to MySQL from MS SQL. I used to use the
following to force a Alpha field to sort as if it were numeric (I know,
perhaps it is better if I made the field numeric to begin with, but it's
not, and I don't remember why, but that's not the question here):
ORDER BY RIGHT('000' + RoomNo,3),LastName,FirstName
I converted this to the following in MySQL:
ORDER BY RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName
In MS SQL, it would sort correctly:
1 Kayla Andre
1 Paige Brackon
1 Kasie Guesswho
1 Katelyn Hurst
2 Craig Bartson
2 Wesley Bytell
2 Kevin Peterson
2 Bryan Wilton
etc...
Now, the Above (RIGHT(CONCAT...)-MySQL Version), seems to ignore the first
sort field, and simply sorts alphabatically:
1 Kayla Andre
2 Craig Bartson
1 Paige Brackon
2 Wesley Bytell
1 Kasie Guesswho
1 Katelyn Hurst
2 Kevin Peterson
2 Bryan Wilton
I finally ended up with:
ORDER BY CAST(RoomNo AS UNSIGNED), LastName, FirstName
Which works perfectly, but I'm just wondering why the first attempt
(right(concat...)) didn't work?? Any ideas?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Export Results?
Is there a way to export results of a query to a file? In other words, if I do a select * from somefile, is there a phrase like send output to somefile.txt, or something? I've searched the help file, and I guess I don't know what I'm looking for, if it even exists. I'm using MySQL version 5 Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export Results?
Thanks, Exactly what I was looking for. It helps to know what you are looking for when browsing the help file. Jesse - Original Message - From: Chris White [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, February 15, 2007 4:34 PM Subject: Re: Export Results? Jesse wrote: Is there a way to export results of a query to a file? In other words, if I do a select * from somefile, is there a phrase like send output to somefile.txt, or something? I've searched the help file, and I guess I don't know what I'm looking for, if it even exists. I'm using MySQL version 5 You can use SELECT INTO OUTFILE: The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file See: http://dev.mysql.com/doc/refman/5.0/en/select.html for more details. Alternatively, you can run mysql command line client with the 'e' flag and redirect standard output to a file: mysql -e 'SELECT * FROM test;' sql_output -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication LOAD DATA INFILE
I've just performed a LOAD DATA INFILE on the master server, and I've waited a while now, and the data has not shown up in the SLAVE. Does Replication not handle LOAD DATA INFILE? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication not working?
Show Slave Status returns a bunch of stuff that I can't view in the command line utility. When I use a visual tool, it shows me a bunch of different fields, but I didn't see anything that looked like an error. Slave_IO_State = Connecting to master. Last_Errno is 0. So, from that perspective, everything looks OK. The last entry in the log was as follows: 070130 17:34:43 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'Anchorman' (10061)' errno: 2003 retry-time: 60 retries: 86400 I think that is the problem. When setting up a replication user account, it asks for host, so I put my local server's name in there. Actually, that is host you will be making the connection from. So, I changed it to that machine's name, and I've been abel to connect using that user name and password now. I don't know if this has now stopped replication. If so, how do I restart it? Thanks, Jesse - Original Message - From: Michael DePhillips [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Tuesday, January 30, 2007 7:57 PM Subject: Re: Replication not working? Should be almost instantaneous. Do a show slave status on the slavealso check the error log. Jesse wrote: I'm playing with setting up replication on two MySQL Servers that I have set up here. I set it up according to the instructions found on the MySQL web site, then I went in on the master and added a new record to one of the tables, then checked the slave about 30 minutes later, and the record has not appeared. How often does the slave check for new records on the master? Also, is there some location that I can check for errors? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication not working?
Also, given the error I got: - Original Message - From: Michael DePhillips [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Tuesday, January 30, 2007 7:57 PM Subject: Re: Replication not working? Should be almost instantaneous. Do a show slave status on the slavealso check the error log. Jesse wrote: I'm playing with setting up replication on two MySQL Servers that I have set up here. I set it up according to the instructions found on the MySQL web site, then I went in on the master and added a new record to one of the tables, then checked the slave about 30 minutes later, and the record has not appeared. How often does the slave check for new records on the master? Also, is there some location that I can check for errors? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication not working?
Sorry, I hit send before I was actually done. So Given the error I am getting: 070131 11:53:55 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'Anchorman' (10061)' errno: 2003 retry-time: 60 retries: 86400 There are a couple of issues hwere. Primary is that it's trying to connect on port 3306. I'm actually using 3307, but can't seem to find where I change this setting. Have any suggestions? Thanks, Jesse - Original Message - From: Michael DePhillips [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Tuesday, January 30, 2007 7:57 PM Subject: Re: Replication not working? Should be almost instantaneous. Do a show slave status on the slavealso check the error log. Jesse wrote: I'm playing with setting up replication on two MySQL Servers that I have set up here. I set it up according to the instructions found on the MySQL web site, then I went in on the master and added a new record to one of the tables, then checked the slave about 30 minutes later, and the record has not appeared. How often does the slave check for new records on the master? Also, is there some location that I can check for errors? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication not working?
I'm playing with setting up replication on two MySQL Servers that I have set up here. I set it up according to the instructions found on the MySQL web site, then I went in on the master and added a new record to one of the tables, then checked the slave about 30 minutes later, and the record has not appeared. How often does the slave check for new records on the master? Also, is there some location that I can check for errors? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ASP.NET Access to MySQL BIT field????-Help
Since no one seemed able to answer my question, I went looking elsewhere, and found the solution. Just wanted to share with you, in case anyone else is having the problem. I realize now (too late), that I should have posted this on the MySQL .NET list. I'm sure someone would have answered there. Anyway... Apparently, MySQL Stores a BIT value as a character. Chr(0) = False, and Chr(1) = True. Once I discovered this, I was easily able to do a FirstTime.Checked = (RS(FirstTime) = Chr(1)), and it worked perfectly. Jesse - Original Message - From: Jesse [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Wednesday, January 17, 2007 5:23 PM Subject: ASP.NET Access to MySQL BIT field-Help OK. I don't recall having a problem with this before, but, how do I assign a variable to a MySQL Bit field? I'm trying to set up a CheckBox that is on my form, and I've tried variations of the following: FirstTime.Checked = CBool(RS(FirstTime)) FirstTime.Checked = CBool(RS(FirstTime).ToString) FirstTime.Checked = RS(FirstTime) FirstTime.Checked = Convert.ToBoolean(RS(FirstTime)) I believe that all of them give the error, String was not recognized as a valid Boolean. When I view the field value in SQL Manager 2005 Lite, It presents a check box for the field value. When I view it through the MySQL Query Browser, it presents the value as b'1' Any hints? I'm sure this is a basic issue, but I just can't seem to find a way to do it. Thanks, Jesse -- 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]
ASP.NET Access to MySQL BIT field????-Help
OK. I don't recall having a problem with this before, but, how do I assign a variable to a MySQL Bit field? I'm trying to set up a CheckBox that is on my form, and I've tried variations of the following: FirstTime.Checked = CBool(RS(FirstTime)) FirstTime.Checked = CBool(RS(FirstTime).ToString) FirstTime.Checked = RS(FirstTime) FirstTime.Checked = Convert.ToBoolean(RS(FirstTime)) I believe that all of them give the error, String was not recognized as a valid Boolean. When I view the field value in SQL Manager 2005 Lite, It presents a check box for the field value. When I view it through the MySQL Query Browser, it presents the value as b'1' Any hints? I'm sure this is a basic issue, but I just can't seem to find a way to do it. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Assigning Variable to a BIT Field
OK. I don't recall having a problem with this before, but, how do I assign a variable to a MySQL Bit field? I'm trying to set up a CheckBox that is on my form, and I've tried variations of the following: FirstTime.Checked = CBool(RS(FirstTime)) FirstTime.Checked = CBool(RS(FirstTime).ToString) FirstTime.Checked = RS(FirstTime) FirstTime.Checked = Convert.ToBoolean(RS(FirstTime)) I believe that all of them give the error, String was not recognized as a valid Boolean. When I view the field value in SQL Manager 2005 Lite, It presents a check box for the field value. When I view it through the MySQL Query Browser, it presents the value as b'1' Any hints? I'm sure this is a basic issue, but I just can't seem to find a way to do it. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need SUPER Privilidge for UPDATE?
I have an ASP.NET app where I'm doing an update, and the user name that I'm using has the access to do an update. When I execute this command, I get the error, #42000Access denied; you need the SUPER privilege for this operation. Following is my query: UPDATE Families SET LastName='a',FathersFirstName='a',MothersFirstName='a',Address1='a', Address2='',City='a',State='FL',Zip='a', Donation=0,HomePhone='a',FathersWorkPhone='',MothersWorkPhone='', FathersCell='',MothersCell='',EMail='[EMAIL PROTECTED]', UserName='a',Password='a' WHERE ID=157 I thought that the Password field might be an issue, but I've changed it to `password`, and that didn't make any difference. Neither did Families.Password, or Families.`Password`. Any ideas why I would be getting this error? This same exact query works fine on another database with the same structure and every thing. Using version 5.0.22-community-nt. with InnoDB tables. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need SUPER Privilidge for UPDATE?
Sounds like you have triggers on the table, see the DEFINER clause within this section of the manual: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Triggers within 5.0 require the user (within the DEFINER clause, or executing the statement against the table when using CURRENT_USER) to have the SUPER privilege. Within 5.1 this moves to the TRIGGER privilege. That is probably the case, because I DO have 2 triggers on this table. However, I don't get the definer thing. Because, on the other database that this works on, there is no definer clause on the trigger that I can tell, and I don't know how to tell if I've given the user name that I'm using on the other database the SUPER privilege or not. How do I give a user the SUPER privilege? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Linux Installation
I have a Debian Linux server, and I would like to install MySQL on there. Potentially to use it in a Cluster configuration, but for now, just as a play thing. However, I'm very green when it comes to Linux. I can get to a command prompt, and list files, but the extent of my Linux knowledge is not much more than that. I believe there is an old version of MySQL on there already (4.1 or something). 1) How do I remove the old version? 2) How to I install and start the latest version? 3) As mentioned, eventually, I would like to use this machine in a cluster configuration. Is it possible to use different operating systems in a cluster? Here is basically what I would have: 1. On one network located away from where I am (different location all-together), there would be a Windows Server 2003 Machine, and a Windows 2000 Server machine. 2. On my network, there would be located a Windows Server 2000 machine, and a Debian Linux machine. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Date Issues
OK, I'm about to pull my hair out with this one. I know it's simple, but I can't find a way to do this other than switching it to a string and parsing it out manually (something I should have to be forced to do). I've got a simple MySQL Table with a DateTime field in it. I want to display it as separate fields in a DataGrid, so I've got a column like this in there: asp:TemplateColumn HeaderTemplatebDate/b/HeaderTemplate ItemTemplate %# Container.DataItem(StartDateTime) % /ItemTemplate /asp:TemplateColumn It displays a blank. Also, I've got code where the user clicks an Edit link and it brings up the date and time part separately. When I try to run the following code: StartDate.Text = FormatDateTime(RS(StartDateTime),2) StartTime.Text = FormatDateTime(RS(StartDateTime),3) I get the error, Cast from type 'MySqlDateTime' to type 'Date' is not valid.. I change change the above code to this: StartDate.Text = FormatDateTime(RS(StartDateTime).ToString,2) StartTime.Text = FormatDateTime(RS(StartDateTime).ToString,3) and it works... However, what can I do with the DataGrid column above? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
How many messages are we talking here? Could be as many as 5,000 at one time. Perhaps create a server side script, that sends the emails in chunks, then sleeps a little, and sends another chunk. I hadn't thought of that, I may have to try to write something like this I would also try to unorder the email addresses, so that large groups of yahoo and aol etc emails do not hit the outbound queue at the same time. They tend to look down on that activity, and throttle you back, making it take even longer to get your messages out. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
An equal probability is that you're actually generating INDIVIDUAL messages (one per recipient), rather than messages with a BCC: recipient list, in which case, my advise would be to switch to BCC: addressing, but if that isn't an option, look at a cascaded MTA queue (messages which don't deliver on the first try get sent to a secondary queue which won't retry right away - and those which fail to deliver from that queue get moved to one that takes even LONGER) - a fairly typical (read: default Sendmail setup) queue retry is every 15 minutes, 24/7 --- if you've got a few hoser domains in there, they can stuff you up quick. I hadn't considered the BCC option. That would definitely speed the process up as well. However, we're dealing with potentially 5,000 emails at one time. That is a lot of addresses to put in the BCC field. Can it hold that? Also, I'm passing this as a variable to an e-mail function that I have. I'm not sure what the capacity of a variable is in ASP. It may not be able to handle a variable that large. Check your server logs to see if the domains you're emailing to are trying to perform callbacks (GTE and it's affiliated telco domains are/were doing this for some time - this is the same bunch of idiots who've blocked many european IP ranges from sending mail to them, and such sites have to relay through other hosts in order to deliver to GTE customers). Any domain that does this might be a candidate for being added to a special case handler to be shuttled to a low priority queue right off the bat. Might I suggest you set up a database for the special conditions and the queues you'd place them in? g Wow, this is getting complicated! All I want to do is send e-mails... Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
Good comments, also, there is the option of simply interfacing mysql and your scripts with mailman, which is really one of the better ways to send mass emails, list serves pretty much have it down these days. mailman? Not sure what this is. Do you have any web sites where I could research this at? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mass E-mail Options
Again, I know this is not necessarily a MySQL specific question, and sorry for getting off target, but this is a pretty smart and experienced bunch, and I'm sure that someone out there will be able to help me. We have a web application in which we have a mass e-mail function. It allows the user to compose a simple text-only e-mail and send to everyone in our database (MySQL). Problem is that our e-mail server seems to be getting overloaded, and it just shuts down, causing an error. We're using ArgoSoft Mail server, which works very well for our normal needs. We do not want to change to Microsoft's Exchange Server. I hear it's expensive, and difficult to set up and get working properly. I was wondering if anyone knows of any alternative mass e-mail options. I don't want to use servers that spammers use, because first, and foremost, this is NOT spam, and second, some recipients may have these servers black listed. What other alternatives are there? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Zip Code Distance
This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Web Seminar Events
Why is it that the Newsletter goes out after the Web Seminar's have already occurred. There have been several that I would have liked to see, but I got the news letter a day after the event occurred. It would be nice to know at least a day ahead of time when these things are going to happen. Is there another list somewhere of scheduled Web Seminars? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outputting text in a
You still get two lines, it's just that one of the lines is blank, and the other says, text to the screen. Jesse - Original Message - From: Wai-Sun Chia [EMAIL PROTECTED] To: Dan Buettner [EMAIL PROTECTED] Cc: Jesse [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Thursday, August 24, 2006 2:14 AM Subject: Re: Outputting text in a On 8/24/06, Dan Buettner [EMAIL PROTECTED] wrote: Sure - in your sql script, put in SELECT text to the screen; Then you'll get 2 copies... Try this: SELECT text to the screen AS ''; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Outputting text in a
Is it possible to output text to the screen from a .sql script? If so, how? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
I have the following query which works in MS SQL Server, but I'm sure the syntax is different for Myself. I'm sure it's in the area of the LIKE clause. I don't know how to do this with Myself. Can anyone point me in the right direction?: SELECT LA.FirstName,LA.LastName,LA.EMailAddress, LA.UserName, U.Password FROM LocalAdvisors LA JOIN Users U ON U.UserName=LA.UserName WHERE EMailAddress '' AND EMailAddress IS NOT NULL AND (EMailPermission is NULL or EMailPermission=1) AND LA.LastName LIKE '[A-E]%' ORDER BY LastName,FirstName Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
