jason310771 Posted January 21, 2014 Share Posted January 21, 2014 I have been having a few issues with mysql getting 4 random results from a table, the query works most of the time, but every now and then it fails causing the page to show errors.... This is the exact error unaltered. mysqli::query() [mysqli.query]: (HY000/6): Error on delete of '/tmp/#sql_b8142_0.MYD' (Errcode: 2) Can anyone suggest what this error relates to or what might be causing it to show up. I have checked the database and done a repair but still the error shows up. The query line I am using is like so... SELECT * FROM `places` ORDER BY RAND() LIMIT 4 There are currently 26682 entries in the table. But the query only asks for 4 random entries to be returned. Quote Link to comment Share on other sites More sharing options...
kicken Posted January 21, 2014 Share Posted January 21, 2014 OS error code 2: No such file or directory Basically mysql is trying to delete the files for a temporary file it created but is unable to, apparently because it's already been deleted. Make sure you don't have any other process going though and deleting the files. Make sure your /tmp directory has the proper permissions set. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted January 21, 2014 Share Posted January 21, 2014 (edited) Yeap, I agree with kicken. Some database developers believe that sorting should be done in the application (file) server, not in the database and I think they have a very good point. Most often, the presence of an ORDER BY clause requires the database server to write/read the results into a temporary table like in your example and this temporary table is then read back and sorted. Did you call a delete statement somewhere in the script before the select one? Or....you're running multiple queries in loops? Start, telling us the whole story. Edited January 21, 2014 by jazzman1 Quote Link to comment Share on other sites More sharing options...
jason310771 Posted January 21, 2014 Author Share Posted January 21, 2014 Thank you, I thoguht I'd try another suggestion from a friend of mine. By installing WAMPSERVER and seeing if the same error shows there, but so far after over an hour of running the same script I have yet to get the error. Maybe there is a script on the main server that is deleting the tmp folder content every so often and every few minutes if that. Seems that it is not deleting using a date in the file name method? As the file name suggests that it is not containing the MYD (Month/Year/Day) Does mysql use the MYD or am I going up the wrong path with that thought. If it can use this method. What changes should be made to the server ? My host may need to make some changes to make it delete files after a longer period. Would you know the normal time that a server would delete the tmp files for mysql from the tmp folder ? Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted January 21, 2014 Share Posted January 21, 2014 Most likely your application script doing something wrong dealing with DB server. Are you able to show us some part(s) of the script on the main server? Quote Link to comment Share on other sites More sharing options...
jason310771 Posted January 21, 2014 Author Share Posted January 21, 2014 What I have is 4 random phrases that are taken from the server and say I have 100 people all access the site then it would be running this query once per visit. After many visits the error shows itself. I have not got it running multiple times in the same run of the page, but for testing things I did and found the error showing itself every now and then. It seems that it could be that the server is deleting everything in the tmp folder say every minute or so and when a new person visits the site and the four random results are got the file is deleted moments after causing the script to fail as the file is gone before it has a chance to read the data to show the results on the page. I would have thought the server would use a timestamp method to make sure the files were only removed if older than an hour or something or say 10 minutes or 5 for that matter. not half a millisecond after it is created because the folder is cleared no matter if the file is finished with or not. I could be wrong with all this and have the wrong end of the stick, but logically this is what I think might be happening. How does the normal server deal with files and what is the point of having MYD if the file name does not contain these details to enable the server to effectively remove the older tmp files. Quote Link to comment Share on other sites More sharing options...
jason310771 Posted January 21, 2014 Author Share Posted January 21, 2014 Most likely your application script doing something wrong dealing with DB server. Are you able to show us some part(s) of the script on the main server? The script or query is what I put in my original post... SELECT * FROM `places` ORDER BY RAND() LIMIT 4 all it does is get 4 results and then display them. I am waiting to find out if my thoughts on how the server deals with the tmp files is right, as it could be the host has the wrong setting that deletes files or maybe a bug in the server software itself? in that it removes all files and not use a timestamp method. My host just comes back with 'nothing wrong this end all working fine' ! awaiting a reply on their settings when files are deleted and how they are remove, be it all or based on a timestamp. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted January 21, 2014 Share Posted January 21, 2014 (edited) No, the file server doesn't touch the temp directories/files of any applications, only the apps deal with them. So, I'm not sure where the problem exactly is, but I would suggest you to start sorting the random results using the application server instead the database server. Edited January 21, 2014 by jazzman1 Quote Link to comment Share on other sites More sharing options...
jason310771 Posted January 21, 2014 Author Share Posted January 21, 2014 application server... whoo now i am lost. I just googled this and still none the wiser. I would think that it is just another method of accessing the databases. I currently use mysqli methods with php. What is application server and would it be installed on reseller hosting servers, i.e. godaddy, hostgator and the like. Or is this only available on dedicated server ? Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted January 21, 2014 Share Posted January 21, 2014 Well, apparently Google doesn't know everything as I thought What I meant is to use php sorting the results. Quote Link to comment Share on other sites More sharing options...
jason310771 Posted January 22, 2014 Author Share Posted January 22, 2014 oh i see, you mean to get the results and then sort them randomly ? if so, this still does not help as the results are still blank, as the results are blank, none returned due to the tmp file being removed too early there is nothing for php to sort or randomize. Quote Link to comment Share on other sites More sharing options...
jason310771 Posted January 22, 2014 Author Share Posted January 22, 2014 ok how does mysqli work ? (I have been using mysqli for some time now after changing my script from mysql) what is the complete process that mysqli uses to access return results and all that. As I really need to figure out what is causing this to fail. I feel that there may even be a bug with the mysqli process still. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted January 22, 2014 Share Posted January 22, 2014 Do DESCRIBE to "places" table. Perhaps your PK is not auto_increment and there is a gap somewhere in the tables. Quote Link to comment Share on other sites More sharing options...
jason310771 Posted January 27, 2014 Author Share Posted January 27, 2014 ok I checked the whole table and there are no gaps with the Primary Key or ID when i eventually worked out how to get the columns in phpmyadmin i used this... SHOW COLUMNS FROM places in the sql query Field Type Null Key Default Extra id bigint(20) NO PRI NULL auto_increment place text NO NULL dummy_date datetime NO 0000-00-00 00:00:00 Quote Link to comment Share on other sites More sharing options...
jason310771 Posted January 29, 2014 Author Share Posted January 29, 2014 Ok I think I have something that may give some more clues as to why this is happening. I added some more code to my scripts so it would notify me via email when the error happened but I also have the IP of the visitor that had the error. It is 99% of the time Google. I added some more so it would log every visit for a day and google did not have an error all of the time. My IP had an error every now and then, but not as much as google did. Why would google have an error so many time but I do not? Would it be that google is visiting to many time in a short time period ? Some time it visits every minute for about 30 minutes or so and other times it would visit a few hundred times in an hour. There does not seem to be a time of the day this happens or a certain time frame or every hour that it has an error. Its just random. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.