Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 989
  • Last Modified:

MySQL - error 1064

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
currentdb
Asked:
currentdb
  • 12
  • 9
2 Solutions
 
johanntagleCommented:
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
 
johanntagleCommented:
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
 
currentdbAuthor Commented:
In fact I'm using myPHPAdmin to access the database created on the hosting server via MySQLAdmin.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
johanntagleCommented:
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
 
currentdbAuthor Commented:
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
 
johanntagleCommented:
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
 
currentdbAuthor Commented:
I forgot to attach a sample...
phpmyadmin.png
0
 
currentdbAuthor Commented:
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
 
johanntagleCommented:
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
 
currentdbAuthor Commented:
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
 
johanntagleCommented:
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
 
currentdbAuthor Commented:
Yes, they are using MySQL 5.0.91
0
 
currentdbAuthor Commented:
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
 
johanntagleCommented:
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
 
currentdbAuthor Commented:
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
 
K VDatabase ConsultantCommented:
"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
 
johanntagleCommented:
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
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
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
 
johanntagleCommented:
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
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now