#856 Export: catch MySQL error messages
so here is what happened:
i was coding a fulltext search for a webpage that was
supposed to get results from a specific table using the
search conditions sent via GET. the thing itself works
well with other tables (ive used methods described on
http://www.phpfreaks.com/tutorials/129/2.php\) but as i
got to one specific, it didnt return any results even
tho it should (logically). so i opened up my
localhost-based PMA and went to check the table. first
thing i wanted to do was recreating the fulltext index
for that table. but as i was removing the old one, i
got MySQL error #1005 with "cant create table
#sql_something".
then i tried to Repair Table but i got MySQL error
#1016 with "cant open file tablename.MYI" instead.
since then, this error appears every time i try to
access this table from the tables list, with no table
data displayed.
the last thing i tried to do was exporting the database
structure and rebuild the specific table from scratch.
but even tho the database structure went ok (without
errors displayed), after i opened up the exported .sql
file, i noticed that instead of the specific table
structure, the file contained the xhtml code that is
produced by PMA when error occurs.
now im completely clueless.. i do have a backup of the
whole database, but id like to know what/where the
problem is, in case it appears again in the future.
ideas?
thanks in advance
Logged In: YES
user_id=210714
Can you attach here a file containing the XTHML code
produced into your .sql file?
Logged In: YES
user_id=640996
yes, here u go
Logged In: YES
user_id=210714
Moved to support. In the XHTML we see "can't open file
tsk_competitions.MYI", this file would be the index of table
tsk_competitions that seems to be missing. Looks like a
server problem to me.
Logged In: YES
user_id=640996
yeah, but
1. there should be no xhtml in the .sql anyway right? i
guess if there's an error with the MYI file, it should be
output before the export is made, not as it was done in this
case
2. the problem with the impossibility of reading the MYI
file has been solved. i moved the db files from windows into
my linux box as root user, so the mysql user couldnt really
manipulate with them. i guess the mysql error should go more
like "the mysql user doesnt have a read access to the
xyz.MYI file".. it would have been easier to locate the problem.
Logged In: YES
user_id=210714
Moved to RFE.
Currently this part is handled by first sending download headers to the client's browser and then outputting the corresponding data directly into the file. That's why everything including MySQL errors gets printed into the same file.
As a part of my GSoC 2014 project, I tried to implement this feature. I first fetched all the data from MySQL and held it into a variable on server. Then checked for any errors. If everything was fine, I set the download headers and outputted that variable else I terminated the export by displaying the error. Same can be seen in [0].
Then Marc Delisle (my mentor) pointed out that this approach won't scale up for big exports, which is very much true. So I tried to find a different solution. I also tried asking on stackoverflow.com [1] but failed to find a solution to this.
[0] - https://github.com/phpmyadmin/phpmyadmin/pull/1261
[1] - http://stackoverflow.com/questions/24549439/catch-mysql-errors-while-creating-file-download-in-php/