Forums

Hi, i recently came across an issue with creating triggers. I realize that is because users do not have full CREATE privelages on their databases, (we can only create a database using the built in function on this site). I also cannot grant these privelages to myself. Any workarounds?

ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you mi ght want to use the less safe log_bin_trust_function_creators variable)

I'll investigate the security issues with triggers in the new year. I'm assuming there's a good reason that they need the SUPER privilege so we'll have to be careful.

i think it has something to do with the fact that binary logging is enabled (which makes database replication easier across servers?)

However, there are certain binary logging issues that apply with respect to stored programs (stored procedures and functions, triggers, and events), if logging occurs at the statement level:

In some cases, it is possible that a statement will affect different sets of rows on a master and a slave.

Replicated statements executed on a slave are processed by the slave SQL thread, which has full privileges. It is possible for a procedure to follow different execution paths on master and slave servers, so a user can write a routine containing a dangerous statement that will execute only on the slave where it is processed by a thread that has full privileges.

If a stored program that modifies data is nondeterministic, it is not repeatable. This can result in different data on a master and slave, or cause restored data to differ from the original data.

-from mysql

In the meanwhile, does anyone know how to obtain trigger like functionality? I have an auto-incremented primary key, and upon an insert into a table, i need to immediately insert another row into a aggregate table with the same primary key. Since i don't know the primary key when i initiate the insert,the trigger syntax allowed me to reference the inserted row with the NEW. attribute. Basically i need a way to reference an inserted row in python since i cant do it at the database level.

does it have to be atomic? can you not just write the row, query back to get its pk, then write then row to the aggregate table? is there a race condition?

From what I understand, the SUPER privilege is no longer required for triggers as of MySQL version 5.1.6. At that point, a specific "Trigger" privilege was introduced. PAW runs on MySQL 5.1.57. In fact, executing "show privileges;" from the MySQL console shows the Trigger privilege. Since "show grants;" reveals that we are provided with "all" privileges, it seems strange that triggers don't work. I suppose it's a function of how the Amazon RDS instances are configured. RDS does provide trigger functionality to the master user, but I don't know how those privileges can be delegated.

If you're using MySQLdb you can use the insert_id() method of the connection object to obtain the ID of the most recently inserted row. With regards to atomicity, as raised by Harry's comment, as long as your tables use the InnoDB engine (which I would always recommend with MySQL) then all of your statements are implicitly executed within a transaction which will be rolled back unless an explicit commit() is called on the connection - this makes it easy to make any arbitrary sequence of statements atomic.

I would advise against relying on stored procedures, personally, as they make your application a real pain to install and maintain. They can be used as a last-ditch effort to improve performance on massive systems, but I've never been entirely convinced that the performance boost is that significant anyway. They can also be used as a band-aid to patch in functionality without requiring an application update, but I believe that's a false economy in the end as it all becomes more fragile. This Coding Horror post is fairly close to my opinion on the subject.

As an aside, if you are using InnoDB tables and multi-statement transactions, make very sure you commit. Rollback will happen automatically if you let the connection go out of scope without committing, so you rarely need to do that explicitly. Of course, if you're writing a web application then remember that they are always potentially multi-threaded and each request should have its own connection from a pool - if you share a connection between threads and use multi-statement transactions you'll have issues (the MySQLdb documentation explicitly advises against sharing a single connection between threads).

I understand that there are different opinions on the issue of stored procedures. And I really appreciate the hints on how to work without using them. But I think this would be a good time for the PythonAnywhere leadership to provide clarity on the direction with respect to support for stored procedures.

When I brought up the lack of MySQL triggers in a user feedback message a couple of months ago, I got a reply from Hansel that led me to believe that support for triggers under MySQL was scheduled as a future upgrade.

Has this goal changed? Do you anticipate supporting stored procedures under PostgreSQL, only? Or should we assume that neither RDBMS will provide this functionality on PythonAnywhere?

Thanks in advance, and Happy New Year to all.

Yes, stored procedures are certainly an issue which generates some debate in database circles - I suspect it probably depends whether you're used to a database being wrapped in an abstracted API which is considered "part of" the database to all intents and purposes, or whether you regard the database engine itself as a standalone component. It probably also depends to some extent on whether you regard the SQL language as something to be admired or tolerated... (^_^)

However, whether stored procedures are a Good Thing(tm) and whether they should be supported by a shared hosting service are almost orthogonal questions.

I'd say a "traditional" (for want of a better term) view of relational databases would regard stored procedures and triggers as being not too far behind foreign key constraints and transactions in terms of importance. A more "modern" (not to imply better or worse, just originating more recently) approach might consider that the database should be more lightweight and more work moved into the business logic, to ease the complexity of schema updates on horizontally scaled systems. I regard this as a small step on the path which eventually leads to NoSQL and the like.

I guess the nirvana of shared hosting would be to support as many approaches (or "paradigms" if you're making a sales pitch) as possible, so I guess it's a case of weighing up the potential benefits of stored procedures against the difficulty of getting them working securely and reliably on a scalable shared hosting architecture, and then prioritising the feature appropriately. It'll be interesting to see how it fares!

Of course, I'm sure NewSQL is The Future(tm) and will render everything else obsolete in a few years anyway... Well, at least until the next buzzword de jour comes along. (^_^)

Happy New Year everyone!

When I brought up the lack of MySQL triggers in a user feedback message a couple of months ago, I got a reply from Hansel that led me to believe that support for triggers under MySQL was scheduled as a future upgrade.

Has this goal changed? Do you anticipate supporting stored procedures under PostgreSQL, only? Or should we assume that neither RDBMS will provide this functionality on PythonAnywhere?

Supporting triggers is definitely possible, but it doesn't look trivial -- our biggest concern is that it might cause unexpected errors elsewhere, and that it would be hard to test thoroughly enough to be sure that it didn't cause any security holes. I guess all we can really say at this stage is that the more people ask for it, the sooner it will be done -- similarly for stored procedures. Right now they're quite low on the list, with just one upvote each.

Thanks for the clarification. I give them my upvote, but I won't hold my breath..

I have also encountered this error when trying to create triggers. Has this been dealt with in the last 3 years?

Hi there, we don't support triggers for mysql, and we don't have any immediate plans for doing so.

Our postgres service does offer you a lower-level access to the database, including superuser access, so that's probably the way to go if you really need triggers.

Is there any way at all we can use MySQL triggers? I am exploring PythonAnywhere for an existing application and for me the lack of MySQL triggers is a huge problem.

We currently do not have any plans to support MySQL triggers. You will have to upgrade to a postgres db for that to work.