SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query doesnt work on mysql 3.28

    Does anyone have an idea what this query doesnt work in mysql 3.28

    DELETE session_users, session_music
    FROM session_users
    LEFT OUTER JOIN session_music
    ON session_users.id=session_music.user_id
    WHERE session_users.last_access < subtime(now(), '01:00:00.0')

    It works on mysql 5 on my system, but when i upload it to the server i get an error.. Here is the following when i run it in an ssh connection to the server:

    mysql> DELETE session_users, session_music FROM session_users
    -> LEFT OUTER JOIN session_music ON session_users.id=session_music.user_id
    -> WHERE session_users.last_access < subtime(now(), '01:00:00.0');
    ERROR 1064: You have an error in your SQL syntax near 'session_users, session_music FROM session_users
    LEFT OUTER JOIN session_music ON' at line 1

    I'm guessing its either the subtime function or the multiple delete causing the problem. Is there another way to write it..

    It seems like it would make more sense to write
    DELETE FROM session_users, session_music...

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    specifying multiple tables in a delete statement requires mysql4.

    as an alternative, you can create a temporary table, insert the primary ids of the rows you want to delete, then delete the rows by joining to the temporary table.

    but you really need to get your server upgraded to mysql 4.1

  3. #3
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah i know.. I'm looking into it right now..
    I'm a bit hesitant though to mess with it though.. Initially i was set on upgrading to mysql 5 even though it is still in beta.. well lets say my attemps were unsuccessfull, and i damn near screwed the whole server up.
    The installatio of v5 had an error and would not start. The server has plesk installed on it and supposedly its not compatible with mysql 5, i figured plesk was pretty much just a web based client and wouldnt matter, but godaddy says it doesnt support mysql 5.. Luckily i was able to get the original mysql back very easily.. However it scared the crap out of me because I had no idea if i would be able to get mysql working right with all the dependancies and everything, and his email system and everything worked through plesk/mysql. Of course i at least had made full backups of all tables before performing the upgrade, but it was still a bit naive to just try it.. So i'll look into it, but i may just use a php alternative and write all the id's that need to get deleted to an array, and then delete them from both tables.. not elegant, but for this site i don't think it will matter as he'll probably never have more than a couple people on his site at a time.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL 4.1 is the latest stable release version and it came out in October of 2004. There is no such version as 3.28 check the docs on the MySQL site or better yet when you are running mysql do a version() and you will find out what version you are running. 3.23.58 (the latest released version in the 3.x series was released two years ago next month).

  5. #5
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    your right, version 3.23.58, the standard for most linux installations.. the server is rh9 and uses plesk.. plesk is upgradeable to 4.1 but i believe it takes some special steps
    i'm not sure if its worth the trouble to upgrade.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    my opinion is that it is most definitely worth upgrading. mysql 3 is incredibly limited. multiple table deletes is just one major feature. mysql 4 adds better support for joins, subqueries and derived queries, improved fulltext searching, prepared statement, opengis extensions, more useful insert and update commands, plus more.

  7. #7
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I definitely agree.. if were MY server, i would upgrade in a minute. I dont care if it screwed up my whole computer.. My computer i do all the testing on.. But unfortunately this server is not mine. Its a virtual dedicated server from godaddy, of which i'm not paying for, but rather a client of mine is. I'm not a professional php/mysql coder, this was my first project ever.

    If i could guarentee an upgrade would work correctly that would be great.. But if i screw up the server its my a$$ that has to fix it.. and after seeing this:
    http://atomicrocketturtle.com/module...rder=0&thold=0

    I'm not sure if i'll be able to get those dependancies.. and then i've read from other people after that although the installation was fine, their email was not working..

    I think Plesk is a pretty cool control panel to have for the server.. Especially for those who have no clue how to manually configure dns, email and so forthr. On the downside.. it introduces so many dependancies and conflicts that it looks like it could be a huge disaster if i try since the email is completely dependant on it.. And my client is hosting like 10 virtual hosts on his server, and several of those have payment systems that need email.
    I believe postgre sql is installed on the server.. not sure which version, but since all my queries go through a database wrapper, it shouldnt be too hard to change it over to postgre, that may be a better option.. I have a few queries that may not be compatible.. I got rid of fulltext so i dont need that.. And i think pretty much all the other queries on my site are fairly simple..

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    interesting. i'm going to have to look in to godaddy and plesk.

  9. #9
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    godaddy + plesk = disaster waiting to happen..
    hehe nah, they arent that bad really but not the best tech support (or so i've heard, never bothered to call).

    Thanks though for your help. If you find any good information, let me know.

  10. #10
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well i havent yet worked on getting my site to work with Postgre, instead, i just tried changing the query above. Instead of trying to do a multiple delete, i tried this query
    PHP Code:
    $which_users "SELECT id FROM session_users WHERE last_access < subtime(now(), '01:00:00.0')";
                
    $handle db_query($which_users);
                while (
    $row db_fetch_array($handle)) {
                    
    db_query ("DELETE FROM session_users WHERE id = $row[0]");
                    
    db_query("DELETE FROM session_music WHERE user_id = $row[0]");
                } 
    However, i'm getting an error.. i tried running just
    SELECT id FROM session_users WHERE last_access < subtime(now(), '01:00:00.0')
    from a terminal on my own computer with the exact same database, and it runs just fine. I tried the same query on the server and i get an error..
    I'm guessing the subtime function is the problem. I'm not sure if its the now() part or if its just the syntax in general. Anyone have another way of writing this query so that i can delete all users who have a last access time of say 1 day before the current time?

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    try date_sub(now(), interval 1 hour)

  12. #12
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Edit:



    thanks.. looks like that works!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •