mysqld just ending...
After almost a year of being up, my RH9 server lost power and rebooted. When it came back up, mysql was not running. I went in and ran safe_mysql to start it back up (I also tried the init.d script) but it failed to start. No errors, nothing in the log. After some investigation, it appears that the .pid file is never being created, so the safe_mysql ends immediately. The only entry in the log or error files in the a notice that mysqld ended, and that's it. The only clue I have is several month ago I was playing around with the mysql user and gave it a password. I can't imagine how that would cause a problem, but it's the only thing I can think of. If this is the problem, I'm not sure how to get the mysql user back to the default state. Is the problem the mysql user? All the permission for all the database directories are set correctly. I'm am puzzled that mysqld just ends, not messages. Any clues? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld just ending...
Found the problem. For some reason, error messages where being sent to hohup.out, once I found that I discovered I was using an option in my.cnf that is not supported on 3.23 (I use 4.1 on my other server). All is well now. Thanks for the help Ron On Apr 29, 2005, at 6:47 AM, Gleb Paharenko wrote: Hello. Check that you don't have problems with filesystem. Start mysqld directly, specify the corresponding variables in the command line. If it doesn't produce errors switch to the debug version of the server and use the trace files to find where the problem is. Ron Gilbert [EMAIL PROTECTED] wrote: After almost a year of being up, my RH9 server lost power and rebooted. When it came back up, mysql was not running. I went in and ran safe_mysql to start it back up (I also tried the init.d script) but it failed to start. No errors, nothing in the log. After some investigation, it appears that the .pid file is never being created, so the safe_mysql ends immediately. The only entry in the log or error files in the a notice that mysqld ended, and that's it. The only clue I have is several month ago I was playing around with the mysql user and gave it a password. I can't imagine how that would cause a problem, but it's the only thing I can think of. If this is the problem, I'm not sure how to get the mysql user back to the default state. Is the problem the mysql user? All the permission for all the database directories are set correctly. I'm am puzzled that mysqld just ends, not messages. Any clues? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Yet another LEFT JOIN question
If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. I am using 4.1. I tried to do a sub-query, but never got it run. Can you give me a quick example? Is the sub-query a better (faster) way to do this? Ron On Dec 6, 2004, at 6:19 AM, Bill Easton wrote: Ron, What's happening is that, when there are clicks and views for an ad, you are getting the number of clicks TIMES the number of views. A quick and dirty solution is to put a column, say id, in clicks which is different for each click, and similarly for views. Then, you can change your counts to count(distinct clicks.id) and count(distinct views.id). Note that, internally, MySQL will still find all of the (click, view) pairs, then sort them and remove duplicates--this may or may not be a problem, depending on usage. If you are using 4.1 or later, you could do a subquery to count the clicks, then left join that with the views. HTH Bill From: Ron Gilbert [EMAIL PROTECTED] Subject: Yet another LEFT JOIN question Date: Sat, 4 Dec 2004 12:08:43 -0800 I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Yet another LEFT JOIN question
I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list of every time a Ad was clicked on with the Ads ID, and 'Views' is a simple list of views that ad got, with the Ads ID. I am trying to SELECT a list of all the ads, with a count for clicks and a count for views, but my LEFT JOIN is not producing what I thought. If the click count is 0, then the view count is OK, but if not, then the Click count and view count are equal, but a much too large number. If I just SELECT for views or clicks, then it works OK, it's when they are combined that it falls apart. SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID LEFT JOIN Views V ON A1.ID = V.AdID group by A1.ID CREATE TABLE `Clicks` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Views` ( `AdID` int(10) NOT NULL default '0' [snip] ) CREATE TABLE `Ads` ( `ID` int(10) NOT NULL default '0' [snip] ) I have tried a lot of combinations for LEFT JOIN with no luck. I've read all the posts on this list and they don't seem to be doing what I am, or else I'm not seeing it. Thanks, Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.1 + debian
I just installed debian (test) and am trying to get mysql 4.1 working. The only deb package for 4.1 that I could find was experimental, which might explain the problem I am having. When I start the server, I get the following in my syslog: Oct 31 01:35:22 cove1 mysqld_safe[2523]: started Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [ERROR] bdb: unable to initialize mutex: Function not implemented Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [ERROR] bdb: process-private: unable to initialize environment lock: Function not implemented Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [ERROR] Can't init databases Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [ERROR] Aborting Oct 31 01:35:22 cove1 mysqld[2526]: Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [NOTE] /usr/sbin/mysqld: Shutdown complete I've googled for the mutex error and only find one hit with someone experiencing the same problem I am having and no solution. Any ideas? I'm new to debian, BTW. Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.1 + debian
No, I did not see the reply, sorry, I just saw the original message. I got 4.1 from the debian site, 4.1 that is on the mysql site is a RPM and I've had no luck installing rpm's on debian, even using alien. Ron On Oct 31, 2004, at 12:02 PM, Michael Stassen wrote: If you're talking about the earlier message on this list, did you read the reply http://lists.mysql.com/mysql/175003? It doesn't appear you've installed the official MySQL binary package, as it's not labeled experimental. I'd suggest downloading an official binary from the MySQL downloads page http://dev.mysql.com/downloads/mysql/4.1.html. Michael Ron Gilbert wrote: I just installed debian (test) and am trying to get mysql 4.1 working. The only deb package for 4.1 that I could find was experimental, which might explain the problem I am having. When I start the server, I get the following in my syslog: Oct 31 01:35:22 cove1 mysqld_safe[2523]: started Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [ERROR] bdb: unable to initialize mutex: Function not implemented Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [ERROR] bdb: process-private: unable to initialize environment lock: Function not implemented Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [ERROR] Can't init databases Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [ERROR] Aborting Oct 31 01:35:22 cove1 mysqld[2526]: Oct 31 01:35:22 cove1 mysqld[2526]: 041031 1:35:22 [NOTE] /usr/sbin/mysqld: Shutdown complete I've googled for the mutex error and only find one hit with someone experiencing the same problem I am having and no solution. Any ideas? I'm new to debian, BTW. Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DISTINCT and ORDER (not a new NBC TV show)
I am trying to run this query... select distinct E.Title, E.ID, C.DateAdded from Entries E, Comments C where C.EntryID = E.ID order by C.DateAdded desc limit 10 ...to get the last 10 entries that had comments added to them, but the DISTINCT is not returning distinct results. What I get is 10 entries that had the last 10 comments, but there are duplicates. For example, if entry A just had 3 comments added, it gets return as the first 3 results, not just once. I assume the DISTINCT is failing because of the ORDER. How should this be accomplished? I am running 3.23 Thanks, Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DISTINCT and ORDER (not a new NBC TV show)
Thanks, that did the trick. On Aug 17, 2004, at 12:34 PM, [EMAIL PROTECTED] wrote: mime-attachment -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API 3.23 to 4.1
I am going to upgrade my MySQL server from 3.23 to 4.1, but I have a C program that needs to continue to connect to the new server, and it can't be recompiled. Is the old API 100% backwards compatible with a 4.1 server? I assume the performance is the same? Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nested Records...
Here is my problem. I want to create a simple message posting system so users can post comments to news stories. I would like the comments to be nested, and not appear as a single long list. Is there away to retrived all the comments from a table order by the date the parents were added, but nested children under the parent comment, ordered by date, and so on...? Oracle has this CHILD command will do this, but it seems like this should be possible using GROUP BY. If it is, I can't figure it out. My Comment table looks like this: CREATE TABLE `Comments` ( `ID` int(10) unsigned NOT NULL auto_increment, `ArticleID` int(10) unsigned default '0', `ParentID` int(10) unsigned default '0', `DateAdded` datetime default '-00-00 00:00:00', `Body` mediumtest, PRIMARY KEY (`ID`), KEY `ID` (`ID`) ) What I would like to get back is: Comment 1 Comment 2 Comment 2a Comment 2b Comment 3 Comment 3a I can do this in PHP by issuing a seperate query for each parent comment to get the children, and then nesting through them, etc. Or I can get all the records back and sort/nest them once in PHP. I would rather have MySQL just return them all to me in the right order, if possible. Thanks, Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
passwords...
When you connect to a MySQL server remotely (from the C API, in my case), is your MySQL password sent in clear text? I've looked but I can find an answer to this question in the docs...maybe I'm missing it. Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.18 vs 4.1.1
I am going to upgrade from 3.23 and was wondering if 4.1.1 is stable enough? This is just for some personal websites, nothing mission critical, but on the other hand, I don't want to deal with endless problems. The reason that I'd like to go to 4.1.1 is for sub-selects, otherwise I'd stick to 4.0.18. What I would really like is go to 5.0 (for stored procedures), is 5.0 stable enough for casual use? Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.18 vs 4.1.1
If I switch to 5.0, are there any issues with PHP? Will my 4.2.2 version of PHP work just fine with 5.0? I also have a Windows C++ program that talked to mysql over the Internet via the C API, will it still work after the 5.0 (or the 4.1.1) upgrade? I'm not doing anything fancy with it. Other than whats in the docs, are there other issues to think about when upgrading from 3.23? Can I go straight from 3.23 to 5.0? According to the docs, it just seem to be the GRANT tables. Ron Josh Trutwin wrote: On Sun, 16 May 2004 13:51:29 -0700 Ron Gilbert [EMAIL PROTECTED] wrote: I am going to upgrade from 3.23 and was wondering if 4.1.1 is stable enough? This is just for some personal websites, nothing mission critical, but on the other hand, I don't want to deal with endless problems. The reason that I'd like to go to 4.1.1 is for sub-selects, otherwise I'd stick to 4.0.18. What I would really like is go to 5.0 (for stored procedures), is 5.0 stable enough for casual use? I've been using 5.0.0 for some sites and use it for an RDBMS class, nothing mission critical mind you, but it's worked very well in my opinion (running on SuSE Linux). There were a couple upgrade issues which are covered in the documentation, but nothing serious. YMMV Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimizing inserts
I have a table that is: CREATE TABLE GPSData ( ID int(10) unsigned NOT NULL auto_increment, Lat decimal(9,5) default '0.0', Lon decimal(9,5) default '0.0', TDate datetime default NULL, PRIMARY KEY (ID), UNIQUE KEY ID (ID), KEY ID_2 (ID) ) TYPE=MyISAM; When I insert a GPS log into the table, it is around 10,000 to 20,000 data points. I don't want duplicate entries, mostly due to sections of the log accidentally being uploaded twice. I am currently doing a SELECT on the Lat, Lon and TDate to see if it already exists, and if not, I do an INSERT. Is this the fastest way? I realize that I probably only need to check the TDate, not the Lat, Lon. Is it better to make the TDate UNIQUE and let the INSERT fail? Should the TDate be a INDEX? Would it be fast if the TDate was stored in UNIXTIME, rather then MySQL time? It currently takes 15 or 20 minutes to run though a 10K to 20K GPS track logs. This seems too long to me. I took out the INSERTS to just to make sure it wasn't my PHP scripts, and they run in a few seconds without the MySQL calls. Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DISTINCT DATETIME question
I am trying to get a distinct dates (not time) from a column. I am currently doing this: SELECT DISTINCT DATE_FORMAT(DateAdded, '%Y-%m-%d') FROM Bla Is this the best way to be doing this? Seems very inefficient do to the call to DATE_FORMAT for every row. Is there a way to have a query only pay attention to the DATE part of a DATETIME or TIMESTAMP column? Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT to DB access
Is there a way to create a GRANT for a DB so that only one user can access to the database? The only way I can see to do it involves taking every user and GRANT them access to every other database, but not this one. The problem I face is that I share a server with three friends, and we all create databases on the server, so everyone needs general super user privileges. I want to be able to create a database and keep the others from accidentally accessing it. Obviously they can just change the GRANTs if they really want to get to it, this is really to keep accidents from happening. Does any of this make sense? Am I missing something obvious? Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup strategy
I am wondering what the best backup strategy is for my database. The database is used to store a very large number of binary files, ranging from a few K to 20MB's. The database stores thousands of these files. I can not put this data on the file server, it needs to be in the database. Currently the database is about 1.7GB's and will grow over time to 4GB or higher. I created 20 identical tables to hold the binary data. I was worried about the 4GB/Tables limit, so figured I would spread it out over several tables, also there is no a single point of failure for loosing all my data. To do nightly backups (I don't need anything more frequent), I copy the whole database directory to another HD on the same server, then the files that changed are rsync'd to another server. One of the reason that I store the data in several tables is so only the tables that changed need to be rsync'd to the other machine. It is not on a local net, so it can take a while to do. In any given day, only 10 or so binary files are added, so not a lot changes from day to day, but it can be one some days When I move to 4.1 and start using InnoDB tables (or should I), will the same technique of copying the whole directory and sync'ing only that tables that changed still work? Is there a better way to be doing this given the huge amount of binary data I have? I am running MySQL v3.23.58, but will probably be upgrading to 4.1 if it makes sense. This is running on RH9, but will be moved to a FreeBSD server in the near future. Thanks, Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT to DB access
Is there a way to create a GRANT for a DB so that only one user can access to the database? The only way I can see to do it involves taking every user and GRANT them access to every other database, but not this one. The problem I face is that I share a server with three friends, and we all create databases on the server, so everyone needs general super user privileges. I want to be able to create a database and keep the others from accidentally accessing it. Obviously they can just change the GRANTs if they really want to get to it, this is really to keep accidents from happening. Does any of this make sense? Am I missing something obvious? Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
