Hi,
lower_case_table_names is a static variable, which means you need to set it on the server startup (either in the cnf file or on the command line). Did you try that?
Hi,
yes, i set it in the my.cnf, but it has no effect - even after a restart.
lower_case_table_names=0 is the correct value for my system.
If i delete some rows with foreign-constraints, i don't get errors.
Only on truncate tables the foreign-key-constraints fails.
Maybe it is only a problem with truncate tables
I see.
So, it worked on MySQL 5.1 but stopped working on MariaDB 5.5? Or did you not have to run truncate on 5.1?
Could you please provide SHOW CREATE TABLE output for one of such tables, ls -l for the table-related files on the disk, and the error message from the TRUNCATE command?
Thanks.
i created a new database and then the tables with:
CREATE TABLE `Invoices` (
|
`ID` int(11) NOT NULL AUTO_INCREMENT,
|
`Code` varchar(255),
|
PRIMARY KEY (`ID`)
|
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
|
|
|
CREATE TABLE `InvoicedItems` (
|
`ID` int(11) NOT NULL AUTO_INCREMENT,
|
`InvoiceID` int(11) DEFAULT NULL,
|
`Quantity` decimal(40,9) DEFAULT NULL,
|
`PTSingleAmount` decimal(40,9) DEFAULT NULL,
|
`ATSingleAmount` decimal(40,9) DEFAULT NULL,
|
`InvoiceText` varchar(255),
|
`ATTotalAmount` decimal(40,9) DEFAULT NULL,
|
`PTTotalAmount` decimal(40,9) DEFAULT NULL,
|
PRIMARY KEY (`ID`),
|
KEY `fk_InvoicedItems_Invoices` (`InvoiceID`),
|
CONSTRAINT `fk_InvoicedItems_Invoices` FOREIGN KEY (`InvoiceID`) REFERENCES `Invoices` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
|
|
|
then truncate the table:
MariaDB [moped]> TRUNCATE TABLE Invoices;
|
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`moped`.`InvoicedItems`, CONSTRAINT `fk_InvoicedItems_Invoices` FOREIGN KEY (`InvoiceID`) REFERENCES `moped`.`Invoices` (`ID`))
|
the result of ls for the tables:
drwx------ 2 _mysql admin 170 8 Okt 10:18 .
|
drwxrwxrwx 70 _mysql admin 2618 8 Okt 09:47 ..
|
-rw-rw---- 1 _mysql admin 8874 8 Okt 10:18 InvoicedItems.frm
|
-rw-rw---- 1 _mysql admin 8586 8 Okt 10:18 Invoices.frm
|
-rw-rw---- 1 _mysql admin 61 8 Okt 09:47 db.opt
|
Thanks in advance
Thank you.
Indeed, I also observe the same error on 5.5 and higher (both MariaDB and MySQL), while on 5.1 TRUNCATE goes smoothly.
I don't think however that it's related to lower/upper case names, is there any reason you suspect that?
I will try to look up for the reason of the error.
I was a little bit wrong, the bug doesn't depend on lower_case_table_names settings or case-sensitive filesystem.
"DELETE FROM table WHERE TRUE" works fine for me.
Thank you for your help.
So, MySQL manual for 5.1 says (http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html):
"In MySQL 5.5 and higher, TRUNCATE TABLE is not allowed for InnoDB tables referenced by foreign keys. For ease of upgrading, rewrite such statements to use DELETE instead."
However, the 5.5 manual says (http://dev.mysql.com/doc/refman/5.5/en/truncate-table.html):
"TRUNCATE TABLE fails for an InnoDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted."
The latter seems to be your case, the foreign key constraints between columns of the same table, but it fails. At least it looks like a documentation issue, or maybe it's actually a code problem if it's supposed to work as described in the 5.5 manual. We normally share the information with MySQL team by refiling bugs in their system. Do you want to create a bug report at bugs.mysql.com or should we do it on your behalf?
(It might well be a known bug, but it's hard to find due to the generic context.)
No, i don't want to create a new ticket.
I think my issue is fixed by reading the doc more in deep 
Thank you very much for your help.
Klaus
Right, and of course my note about your case having only one table was wrong – it actually has two, so it works as described in the manual. Closing as documented behavior.