Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's wrong with my SQL Fiddle query?

Take a look at this simple query in SQL fiddle: http://sqlfiddle.com/#!2/c1eb6/1. This is a super-simple update query, why is it producing an error? I wonder if could it be a bug in that site?

Build Schema:

create table your_table (some_column varchar(50));

insert into your_table set some_column = '30# 11';
insert into your_table set some_column = '30# 12.00';
insert into your_table set some_column = '30# 13.5';
insert into your_table set some_column = 'abdfs';
insert into your_table set some_column = '0000000';

Query:

UPDATE your_table
SET some_column = 1;
like image 984
jeffery_the_wind Avatar asked Jun 19 '12 18:06

jeffery_the_wind


1 Answers

A bit of background for those interested in some of the arcane issues I've been dealing with on SQL Fiddle:

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state (dba.se)

Essentially, I am trying to ensure that the fiddles always remain in a consistent state, even as people play with them. One thing I've worried about is people intentionally messing with the databases, breaking them for the other people that might be working with them (this has happened before, but not often fortunately).

I've found methods of keeping things clean for each of the database platforms, but interestingly each method is completely different for each platform. Unfortunately, for MySQL I had to resort to the worst option - only allowing SELECTs on the right-hand side. This is because there are too many ways to write queries that include implicit commits, and there is no way that I've found to prevent that from happening short of outright denial on the query side. This is quite unfortunate, I realize, but it seems to be required.

Anyhow, this particular bug was a result from a change I had recently made in the logic for MySQL. It is fixed now, and now reports the expected error message:

DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel.

like image 173
Jake Feasel Avatar answered Oct 18 '22 11:10

Jake Feasel