Problem creating a table in SQL question

Problem creating a table in SQL question

by Mark Stern -
Number of replies: 9

I am trying to create a question where the user needs to create a table. If the user gets the syntax wrong, it correctly displays the error. However, if the user gets the syntax right, I see a 404 error. Any idea what I am doing wrong?

In reply to Mark Stern

Re: Problem creating a table in SQL question

by Richard Lobb -
No idea. Can you attach an XML export of the question, please?
In reply to Richard Lobb

Re: Problem creating a table in SQL question

by Mark Stern -
It is attached. This is a much simpler than the final question will (hopefully) be, but I need to get the basic stuff working first.
In reply to Mark Stern

Re: Problem creating a table in SQL question

by Richard Lobb -
I'm not sure what the expected right answer is, as the question doesn't seem to have an answer filled in. But of more immediate concern is the test.db file within your question, which should be the initial database. If I open it in sqlite3 and try to understand what's in it, I get output like the following:

sqlite> .tables
demo
sqlite> .schema demo
CREATE TABLE demo (ID integer primary key, Name varchar(20), Hint text );
sqlite> select * from demo;
1|SQL Online|for Data Science
2|Kirill N.|https://www.linkedin.com/in/sqliteonlinecom
3|Twitter|https://twitter.com/SqliteOnlineCom
4|Chart|LINE-SELECT name, cos(id), sin(id) FROM demo;
5|Short CODE|s* tableName => SELECT * FROM tableName
sf tableName => SELECT columns FROM tableName
sc tableName => SELECT count(*) FROM tableName
sl tableName => SELECT * FROM tableName ORDER BY key DESC LIMIT 100;
ct => CREATE TABLE
ii tableName => INSERT INTO
us tableName => UPDATE SET
uw tableName => UPDATE SET WHERE
df tableName => DELETE FROM
dw tableName => DELETE FROM WHERE
6|SqLite 3.36.0|SQL OnLine on JavaScript
7|[RightClick] mouse|Opens many additional features
8|Left-Panel, Table|[RightClick] mouse "Context menu"
9|Tabs|mouse: [RightClick] , [MiddleClick] , [Wheel] , [LeftClick]
10|SQL Editor|autocomplete: [Ctrl-Space] or [Alt-Space]; run: [Shift-Enter]
11|Size table|Fast scroll million rows
12|Share|Create public link DB
13|ai.Url|https://sqliteonline.com/LICENSE
14|ai.Color|#9393ad
15|ai.Image|Blob - png, jpg, gif or String(base64) [DbClick] row
16|SQL|Syntax example library
17|CREATE|CREATE TABLE table_name (col1, col2)
18|SELECT|SELECT * FROM table_name
19|INSERT|INSERT INTO table_name (col1, col2) VALUES ("example","test")
20|UPDATE|UPDATE table_name SET col1="work" WHERE col2="test"
21|DELETE|DELETE FROM table_name
sqlite>

I'm not sure why there's a table called 'demo' in the database, and its contents looks very strange. I see the 'Hint' column of that table contains a couple of URLs. Is this intended? My guess is that somehow sqlite3 is attempting to access those URLs though I've no idea why.

If the student's first task is to create a table, I'd suggest as a starting point that you provide them with a database that doesn't contain any tables. Then work from there.


In reply to Richard Lobb

Re: Problem creating a table in SQL question

by Mark Stern -
As I tried to explain, this is the start of what should be a much more complex question. The user creates the necessary tables and then CodeRunner runs a number of tests to confirm that the tables have been created as expected. At the moment, the expected result is that a table is created. When that is working, I will add more checks to the test.

I do not remember where I got that test file. I initially tried using an empty file, but Moodle would not let me upload that, so I found a file containing just one table. I will have another go at creating a db file with no tables. Alternatively, I may have a go at tweaking the Python to create and use an empty file.

The Hint column is just a text column, so your guess that sqlite3 is trying to access the URLs sounds far-fetched to me.

Thanks, anyway.
In reply to Mark Stern

Re: Problem creating a table in SQL question

by Richard Lobb -
Yes, I agree it would be bizarre if that text column were somehow causing a 404, but a 404 error is itself bizarre and I was clutching at straws.

Let's start over. You say "if the user gets the syntax right, I see a 404 error". It shouldn't be possible to get a 404 error when submitting an answer to a coderunner question, so something very strange is happening. My reason for asking for the exported question was to see if I could replicate the error. However, the question's answer field is empty. So could you tell me what output you enter into the answer box that gives rise to a 404, please?

Certainly if I just enter an arbitrary simple CREATE TABLE command as an answer to your question I don't get a 404. For example:



Note that the Got column of the result table is hidden because there is no output from a create table command.
In reply to Richard Lobb

Re: Problem creating a table in SQL question

by Mark Stern -
In reply to Mark Stern

Re: Problem creating a table in SQL question

by Richard Lobb -
Interesting. Tim Hunt may know what might cause a question preview to fail like this, but I've never seen it before.

I'm wondering if your Moodle server uses an old version of PHP and the result table rendering in the non-error case happens to use PHP code that isn't supported on your server. Do other CodeRunner question types work OK, for example a simple Hello world Python question? 

I don't suppose you have access to the PHP error log do you? I suspect there might be some interesting messages logged when the question fails. Could a system administrator check the error log if you gave them the exact time of a 404-generating submission?

One simple experiment you could do that might help close in on the problem is the following. Check the 'Customise' checkbox in your question and inspect the template code in the Customisation section. Starting around line 34 you should see code like the following:

with open('commands') as cmd_input:
    text_input = cmd_input.read()
    try:
        output = subprocess.check_output(['sqlite3', db_working], input=text_input,
                 universal_newlines=True)
        print(output)
    except Exception as e:
        raise Exception("sqlite3 error: " + str(e))
 

If you replace the 'print(output)' line there with the line

        raise Exception("Output from run: " + output)
does the 404 error go away, giving you instead an extended error message (probably with the output from the run being empty)? [In case you're not a Python programmer, please be aware that the indentation of the replacement line must be exactly the same as the line it replaces, namely 8 spaces.]

If the 404 does go away, put back the original line but modified to 

        print("Output: " + output)

This is to see if the empty column case is causing the problem.

And yes, I admit I'm still clutching at straws, but hopefully these ones are more plausible?

In reply to Richard Lobb

Re: Problem creating a table in SQL question

by Mark Stern -
I have a Python question that works fine. If I raise an exception instead of printing the output, as you suggested, it makes no obvious difference - I still get 404.
I do not have access to the PHP error log, although I might be able to get it. I will let you know if it shows up anything interesting.
In reply to Mark Stern

Re: Problem creating a table in SQL question

by Mark Stern -
We found the problem. There are security settings in Moodle which, if tuned on, causes it to look for certain SQL keywords in order to protect against SQL injection. We turned them off.