Hello I have just been playing with the export database part of Therion again. The file outputted was imported into some application fine, but most would not work phpMyAdmin being one of the ones that I could not import with and the one that my ISP forces me to use. phpMyAdmin was giving the error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1 After a bit of searching I found this http://dev.mysql.com/doc/refman/5.0/en/commit.html (there is a similar page for MySQL4 and 3.23 that seem to say the same) So editing the exported file first line from begin transaction; to begin; and last line from commit transaction; to commit; then it worked Not sure if this is a bug or my lack of understanding of the .sql file format Andrew
I think it should be corrected to begin/commit only as afaik most of the sql servers use this syntax. L. Dne středa, 24. července 2013, Andrew Atkinson napsal(a):
Hello
I have just been playing with the export database part of Therion again.
The file outputted was imported into some application fine, but most would not work phpMyAdmin being one of the ones that I could not import with and the one that my ISP forces me to use.
phpMyAdmin was giving the error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1
After a bit of searching I found this
http://dev.mysql.com/doc/refman/5.0/en/commit.html
(there is a similar page for MySQL4 and 3.23 that seem to say the same)
So editing the exported file first line
from
begin transaction;
to
begin;
and last line
from
commit transaction;
to
commit;
then it worked Not sure if this is a bug or my lack of understanding of the .sql file format
Andrew _______________________________________________ Therion mailing list Therion@speleo.sk <javascript:;> http://mailman.speleo.sk/mailman/listinfo/therion
In most databases, transactions are not needed for SQL imports. Their purpose is to ensure that a partial operation does not get committed to the database. For example if a system was transferring money from one account to another you would not want the withdrawl of an amount from the paying account to occur without being guaranteed that the payment was credited to the receiving account. The transaction begin/commit statements provide this guarantee. Either everything inside the transaction begin/end will be committed to the database, or none of it will. For SQL import of Therion data it would seem unnecessary unless it was really important to you that if you stopped the import part way through then all the import was rolled back and none of the data written into the database. More commonly with large data imports of complete tables you do not want to use transactions as this slows down the operation, and if it was to fail for some reason (like an error in the SQL script) then you can just drop and recreate the tables to reset the database ready to try again. Footleg On 24 July 2013 21:18, Andrew Atkinson <andrew@wotcc.org.uk> wrote:
Hello
I have just been playing with the export database part of Therion again.
The file outputted was imported into some application fine, but most would not work phpMyAdmin being one of the ones that I could not import with and the one that my ISP forces me to use.
phpMyAdmin was giving the error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1
After a bit of searching I found this
http://dev.mysql.com/doc/refman/5.0/en/commit.html
(there is a similar page for MySQL4 and 3.23 that seem to say the same)
So editing the exported file first line
from
begin transaction;
to
begin;
and last line
from
commit transaction;
to
commit;
then it worked Not sure if this is a bug or my lack of understanding of the .sql file format
Andrew _______________________________________________ Therion mailing list Therion@speleo.sk http://mailman.speleo.sk/mailman/listinfo/therion
Typically, SQL servers use transaction with autocommit on each command, and wrap a large block of commands in a single transaction on the contrary improves the performance 26.07.2013 04:07, Footleg пишет:
More commonly with large data imports of complete tables you do not want to use transactions as this slows down the operation, and if it was to fail for some reason (like an error in the SQL script) then you can just drop and recreate the tables to reset the database ready to try again.
-- WBR, Vasily
participants (4)
-
Andrew Atkinson -
Footleg -
Ladislav Blažek -
Vasily Vl. Suhachev