Solved

MySQL - error 1064

Posted on 2012-03-28
22
958 Views
Last Modified: 2012-04-02
Dar experts,

I had a help from one experts with this code:

mysql> create table currentdb_table (record_id int not null auto_increment primary key, dict_name varchar(20), word_id varchar(20), video_name varchar(20));
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter |
mysql> create trigger currentdb_insert 
    -> before insert on currentdb_table 
    -> for each row begin   
    -> DECLARE temp_int integer; 
    -> DECLARE temp_word varchar(20); 
    -> set temp_int=(select ifnull(max(record_id),0)+1 from currentdb_table); 
    -> set temp_word=concat('A', 290000+temp_int); 
    -> set NEW.word_id=temp_word;  
    -> set NEW.video_name=concat(temp_word,'.mov'); 
    -> end;|
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;
mysql> insert into currentdb_table (dict_name) values ('able');
Query OK, 1 row affected (0.00 sec)

mysql> insert into currentdb_table (dict_name) values ('car');
Query OK, 1 row affected (0.00 sec)

mysql> select * from currentdb_table;
+-----------+-----------+---------+-------------+
| record_id | dict_name | word_id | video_name  |
+-----------+-----------+---------+-------------+
|         1 | able      | A290001 | A290001.mov |
|         2 | car       | A290002 | A290002.mov |
+-----------+-----------+---------+-------------+
2 rows in set (0.00 sec)                                      

Open in new window


I was able to create the table currentdb_table into MySQL but I had to remove (mysql>) because it did not work this way.

Second the other line with mysql> delimiter does not work hence the error 1064. Again here I removed the (mysql>) syntax as well as the "->", but still it does not work.

Any light on this would be appreciated.

Many thanks.
0
Comment
Question by:currentdb
  • 12
  • 9
22 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 250 total points
ID: 37775350
Of course you have to remove them.  What I gave you there was copy+pasted from mysql command line while I was working on the solution for your other question.  "mysql>" was the prompt, and "    -> " was the continuation prompt as I was typing a multiple-line command.

Here is the code for the trigger in bare form:

delimiter |
create trigger currentdb_insert 
before insert on currentdb_table 
for each row begin   
DECLARE temp_int integer; 
DECLARE temp_word varchar(20); 
set temp_int=(select ifnull(max(record_id),0)+1 from currentdb_table); 
set temp_word=concat('A', 290000+temp_int); 
set NEW.word_id=temp_word;  
set NEW.video_name=concat(temp_word,'.mov'); 
end;|
delimiter ;

Open in new window


What editor/IDE are you using to access MySQL?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37775356
Note that "delimiter |" is actually not part of the code.  That tells MySQL not to evaluate the command until it sees a "|", so that it will read the whole trigger code before compiling it.  Then "delimiter ;" puts back the semicolon as the standard delimiter.  Now it might be your editor/IDE is not accepting it, then we need to know what you are using in order to know how to make adjustments.
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775360
In fact I'm using myPHPAdmin to access the database created on the hosting server via MySQLAdmin.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37775397
It should work with phpmyadmin.  See attached screenshot - I deleted the trigger then recreated it on phpmyadmin using the exact bare code I gave above:
currentdb.png
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775410
It's weird. I still have this error:

Error

SQL query:

delimiter | CREATE trigger currentdb_insertbefore INSERT ON currentdb_table
FOR each
ROW BEGIN DECLARE temp_int integer;

MySQL said: Documentation
#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 'delimiter |
create trigger currentdb_insert
before insert on currentdb_table ' at line 1
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37775445
That's the problem - you entered it incomplete and wrong.  Can you please enter the whole code, lines 1-12 above?  Didn't you see from the screenshot that I entered the whole thing?

And please, do not just copy+paste code.  Try to understand it.  Google about it.  I already said above what "delimiter |" does, so you should already know that the trigger code does not end with the first ";"
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775449
I forgot to attach a sample...
phpmyadmin.png
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775462
From my sample, I had to create the database dict_vid.

And then, I created the table currentdb_table (just using your code..but only the first line. I had to execute each line step by step). The table was created successfully, but it was not the case when I came to the delimiter. The last lines, those for adding records worked without a problem.

Ok, I'll try to understand what happens here.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37775495
Looks like your phpmyadmin instance did not evaluate the "delimiter |" properly.  Please try to check phpmyadmin version.  Mine is 3.3.2.  And maybe request support from your hosting provider because it seems specific to their installation - you can show them my screenshot along with yours.
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775507
The version of phpMyAdmin is 2.8.0.1
Looks like you have a more advanced version. I have no idea if my hosting provider will change this version to the one as yours or leave it as it is. I have to ask and see what they can do.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37775523
Uh oh.  Are they at least on MySQL 5.x?  Because triggers are not yet supported prior to that.  If they are and phpmyadmin is the problem, maybe they can install the trigger for you.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:currentdb
ID: 37775529
Yes, they are using MySQL 5.0.91
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775583
I spoke with someone from my hosting provider. Looks like I am stuck with this version cause they are not planning to upgrade phpMyAdmin now....but in a near future :(
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37775594
But can you ask them if they can install the trigger for you?  That's the only thing you need because the rest are ordinary SQL's.  Or ask if there's command line MySQL available.
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775616
I'm still chatting with one of them, but they take time to reply me back...

And also it looks like they do not understand clearly what I am asking them to do. Upgrading cannot be done ok, but installing the trigger should not be a problem and still they can't do anything. And there's no MySQL command line available anywhere.

Is there a way to modify it to work under this "lower" version?
0
 
LVL 21

Assisted Solution

by:theGhost_k8
theGhost_k8 earned 250 total points
ID: 37775901
"Run SQL query/queries on server MYSQL-HOST-NAME: "
<Under this there is a textbox where you will paste the code for creating trigger>

After above section you have a place to specify the Delimiter. Write PIPE "|" there.
"[ Delimiter  ]  Show this query here again"

What you gotta do is use the delimiter option given in phpmyadmin and use below code:

create trigger currentdb_insert 
before insert on currentdb_table 
for each row begin   
DECLARE temp_int integer; 
DECLARE temp_word varchar(20); 
set temp_int=(select ifnull(max(record_id),0)+1 from currentdb_table); 
set temp_word=concat('A', 290000+temp_int); 
set NEW.word_id=temp_word;  
set NEW.video_name=concat(temp_word,'.mov'); 
end;|

Open in new window

0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37775915
installing the trigger should not be a problem and still they can't do anything

My solution to the above is to transfer hosts.  haha.

There's a possible solution which I just noticed this now on my phpmyadmin.  See attached.  In your instance, do you see an equivalent to the delimiter field I encircled?  If so put "|" there (without the quotations) then on the text area for SQL code paste only lines 2-11 in what I pasted above.  See if that works.delimiter field
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775933
Hi theGhost_k8,

I looked everywhere and there's no checkbox like the one you mentioned. The only checkbox I see is the one for the "Shop this query here again". You can see a snapshot of what it looks like at comment ID: 37775449.
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775941
johantangle,

Well, looks like your version is way too advanced. Sadly, I don't have this small textbox like the one you have. I only have a check box with "Show this query here again".

This hole is deeper than I thought. With nothing left, I don't see how else I can run your code :(
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37776000
Actually, my version is not that new, it's almost 2 years old.   It's your host's version that is too old - http://sourceforge.net/search/index.php?group_id=23067&type_of_search=news&q=2.8.0&posted_by=&posted_date_start=&posted_date_end=&form_submit=Search tells me 2.8.x was released way back in 2006!

Btw, can't you install a local instance of mysql?  The XAMPP package is pretty convenient if you're on windows (and there are other OS's supported).  See http://www.apachefriends.org/en/xampp.html
0
 
LVL 1

Author Comment

by:currentdb
ID: 37776019
2006 ?? Damn that's really too old. It's just been 2 years that I took this hosting server and never noticed that phpMyAdmin was that old :(
If I knew I would choose a different hosting server....:(

Yes I can install a local instance of mysql and my computer is on Windows, but I have no idea if it will work...
If yes, who knows, maybe the database can then be exported to phpMyAdmin and be of some use...
0
 
LVL 1

Author Comment

by:currentdb
ID: 37776056
Looks like XAMPP is not that easy to configure. I installed it with MySQL, but when I wanted to access the Admin interface (via MySQL), I ran into "HTTP Error 404.0 - Not Found".

I guess I have to read the technical manual and check what is not working correctly. I hope that my computer will not crash after this :(
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now