Problem with the creation of MySQL triggers

[Cédric Bonhomme]

Hello,

I just updated my instance on the master branch and the creation of the 4 MySQL triggers is always failing.
https://github.com/photo/frontend/blob/master/src/configs/upgrade/db/mysql/mysql-4.0.1.php

For example, with this code:
"""
CREATE TRIGGER update_album_counts_on_delete AFTER DELETE ON op_elementAlbum
FOR EACH ROW
BEGIN
  SET @countPublic=(SELECT COUNT(*) FROM op_photo AS p INNER JOIN op_elementAlbum AS ea ON p.id = ea.element WHERE ea.owner=OLD.owner AND ea.album=OLD.album AND p.owner=OLD.owner AND p.permission='1');
  SET @countPrivate=(SELECT COUNT(*) FROM op_photo AS p INNER JOIN op_elementAlbum AS ea ON p.id = ea.element WHERE ea.owner=OLD.owner AND ea.album=OLD.album AND p.owner=OLD.owner);
  UPDATE op_album SET countPublic=@countPublic, countPrivate=@countPrivate WHERE owner=OLD.owner AND id=OLD.album;
END
"""
MySQL returns:
"""
#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 '' at line 4
"""

I tried by replacing p.permission='1' by p.permission=1. But this changes nothing. Isn't "p.permission" supposed to be an integer?

Any idea?


For the rest all is working fine except the fact that when I click the link (New Feature On This Page), nothing happens.

thanks,
Cédric

[Jaisen Mathai]

Here's the trigger code, https://gist.github.com/jmathai/5165811

In our upgrade tests we were able to run them in the upgrade without problems (cc'ing James).

MySql lets you put quotes around integers, so that's not the problem.

The "New Feature On This Page" just means you're not loading the "Tutorial" plugin which we're now making mandatory. You can fix it by adding "Tutorial" to your list of activePlugins in your conf file.

--
You received this message because you are subscribed to the Google Groups "OpenPhoto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openphoto+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Cédric Bonhomme]

Thanks, the tutorial plugin is really nice!

There is still the problem as you can see with the herewith screen shot. I'll test with the other triggers from your gist.

[James Walker]

Hey there,

Can you please tell me what version of MySQL you are using?

Thanks!

James

[Cédric Bonhomme]

Hello,

This is the version 5.1.39-1.
And MySQL web server: 5.1.49

Cédric

[Jaisen Mathai]

You need the following (and associated) line(s)....

DELIMITER |

I suggest just pasting the entire gist in ...

[Cédric Bonhomme]

Thank you. I know have the following error message:
"""
#1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
"""
With the entire gist.


[Jaisen Mathai]

Run SHOW TRIGGERS and delete them all first?

[Cédric Bonhomme]

It is now working, thanks!
I'll compare the structure of my database with this version: https://github.com/photo/frontend/blob/master/src/configs/upgrade/db/mysql/mysql-base.php. Since I always have the same kind of problems.
I suppose that this php file creates the database for a first installation.

[Jaisen Mathai]

That's correct.