Distinct Mysql error - help

[[deleted]]

Hi everyone,

do you guys have any idea what this error mean?

Database error: Invalid SQL: select distinct * from status where id='64' AND date='2003-01-13' AND status='0'
SQL Error: 1 (Can't create/write to file '/tmp/#sql7cf7_1c_1.MYI' (Errcode: 13))
Session halted.

i didn't encounter this error from my development server, but when i tranferred it from other server.. i got this 'distinct' error... all my sql statement that have 'distinct' command have this kind of error.. please help.. 😕

thanks u in advance.

[chads2k2]

Hey your problem lies in your SQL statement :.:

"select distinct * from status where id='64' AND date='2003-01-13' AND status='0' "

&&=AND
|| = OR

SELECT DISTINCT * FROM status WHERE id='64'&&date='2003-01-13'&&status='0';

I don't have an idea why it flew with your test server but my hosting servers never let you use AND in yoru statements. Just try that and also that error just says it is having trouble accessing the .MYI file...which is where the data is stored in a mysql data center. There is MYI and MYD ... MYD is stuructures and MYI is information. It is just having trouble either finding or accessing the MYI. Just check that after you check if its not the first problem. || if all else fails just try a simple "SELECT * from status;" See if that generates anything. If it does not then reinstall MySQL from source and try it again.

Hope I could help.

[ultraslacker]

Date is a reserved word - do not use reserved words for column/table names.

There is MYI and MYD ... MYD is stuructures and MYI is information.

MYI contains indexing information, frm contains structure, and MYD is the data.

[chads2k2]

Sorry had my MIY and MYD's backwords. MySQL should have kicked out an error when creating the table showing that date is a reserved word. Atleast all of mine do. I do think his/her problem spans back to the AND comments. I cannot find it in the documents from mysql.com but I am pretty sure that is what's causing the holdups. Though writing to the file of MYI is also a bit confusing. It should have said something about a illegal select statement or sql query.