error: Error 1242: Subquery returns more than 1 row

[smyrman]

I get the error described in the header when I try to run xo against my MySQL schema. Is there any flags/env vars to print debug info, e.g. so I could finding out which query that crashes it?

I found:
http://stackoverflow.com/questions/12597620/1242-subquery-returns-more-than-1-row-mysql
which might be related to the issue.

[kenshaw]

Could you copy and paste the actual command line you issue and the actual error output by xo? Also, could you provide more information about the schema design you are using? I don't need the entire schema, just some basics.

The queries that generate the MySQL are in the project root's ./gen.sh. It's likely the 'mysql columns query' which uses some sub queries. Without seeing the schema it would be difficult for me to track this down.

I will make a point to enable a 'verbose' flag to output the SQL queries as they are being run by xo. Give me a few hours to write this code, as I have something that is already 'half-finished' but still need to roll it out / test it.

[kenshaw]

If you pull the latest code, I have 'fixed' the --verbose/-v option on xo command line:

xo -v mysql://user:pass@localhost/dbname

If you could retry this and copy the specific SQL statement that is occurring right before the error, I can figure out the issue and likely fix it, depending on your schema.

Additionally, when using the generated code, you can set a logging func XOLog, a la this:

models.XOLog = func(s string, p ...interface{}) {
    fmt.Printf("> SQL: %s -- params: %v\n", s, p)
}

BTW -- I'm fairly certain of where the error is already, but I need to figure out a clever way to fix the MySQL query while still producing valid info for use by the templates.

[smyrman]

Thanks for the verbose flag.

Anyway. The original command line output was just the tile of this ticket, nothing else.

My setup is using Docker. I found the issue only happens if the mysql version is 5.5 for me -- more or less exactly the same database within a mysql:5.7 container causes no issues. Maybe it's not critical to support mysql as old as 5.5?

The schema of the whole DB is a pretty big django generated DB.

Here is command output when using the new verbose flag:

% docker run --net myproject_default --link myproject_db_container:db --rm -v $PWD/myapp/:/mnt/  kaizen/go-xo xo mysql://kaizen:test@db/db_name -o /mnt -v --schema db_name
SQL:
SELECT SCHEMA()
PARAMS:
[]

SQL:
SELECT table_name AS table_name, column_name AS enum_type, SUBSTRING(column_type, 6, CHAR_LENGTH(column_type) - 6) AS enum_values FROM information_schema.columns WHERE data_type = 'enum' AND table_schema = ? ORDER BY table_name, column_name
PARAMS:
[django_db]

SQL:
SELECT c.column_name, c.table_name, IF(c.data_type = 'enum', c.column_name, c.column_type) AS data_type, c.ordinal_position AS field_ordinal, IF(c.is_nullable, true, false) AS is_nullable, IF(c.column_key <> '', true, false) AS is_index, IF(c.column_key IN('PRI', 'UNI'), true, false) AS is_unique, IF(c.column_key = 'PRI', true, false) AS is_primary_key, COALESCE((SELECT s.index_name FROM information_schema.statistics s WHERE s.table_schema = c.table_schema AND s.table_name = c.table_name AND s.column_name = c.column_name), '') AS index_name, COALESCE((SELECT x.constraint_name FROM information_schema.key_column_usage x WHERE x.table_name = c.table_name AND x.column_name = c.column_name AND NOT x.referenced_table_name IS NULL), '') AS foreign_index_name, COALESCE(IF(c.column_default IS NULL, true, false), false) AS has_default, COALESCE(c.column_default, '') AS default_value, COALESCE(c.column_comment, '') AS comment FROM information_schema.columns c LEFT JOIN information_schema.tables t ON t.table_schema = c.table_schema AND t.table_name = c.table_name WHERE t.table_type = ? AND c.table_schema = ? ORDER BY c.table_name, c.ordinal_position
PARAMS:
[BASE TABLE django_db]

error: Error 1242: Subquery returns more than 1 row

[kenshaw]

Thanks -- could you connect and do the following on mysql?

SHOW INDEXES FROM django_db;

I'm assuming that's django, so I'll try to find the schema online and create it and debug from this end.

Unfortunately, I don't have any "complex" schemas to test against unlike in PostgreSQL/Oracle, so I really appreciate the assistance.

[kenshaw]

Quick update -- I installed the basic django installation and am working with the schemas now. I should have this figured out "soon", but it's the end of the work day for me here.

[kenshaw]

I found what the issue is / where the mistake in the code is. I'll need some time to fix it.

[smyrman]

Cool -- sorry for my late response.

I'll need some time to fix it.

That's no problem. Sorry for my late response time. Currently I am testing XO on my own time, and not at work.

[smyrman]

I'm assuming that's django, so I'll try to find the schema online and create it and debug from this end.
django_db is actually the name of our DB, so I am afraid you won't find the schema for (all of this at least) online all the tables generated from Django model code.

In the command I posted earlier, the "django_db" is actually db_name - it contains a larger collection of tables generated from Django 1.8 models. Some of the other variables (myproject, myproject_db_container) is not the actual values I used for our project.

docker run --net myproject_default --link myproject_db_container:db --rm -v $PWD/myapp/:/mnt/ kaizen/go-xo xo mysql://kaizen:test@db/django_db -o /mnt -v --schema django_db

I might be able to see if I can reproduce the issue with a schema I can actually share on my own time.

[kenshaw]

You don't need to track the issue down. The issue is exactly where I originally thought it was, which is multiple index names being returned for the same column. This is also an issue in the postgres code. The oracle code that I haven't released yet builds indexes differently, and the sqlite code that I pushed out yesterday also builds indexes differently. I was trying to be "too clever" with the postgres/mysql code. Anyway, I know how to change it, but it's somewhere between trivial and hard as I need to rewrite quite a bit of code for it to be correct. It'll get done very quickly, however. I'm glad that you had this issue with Django, as its a complex enough base schema to test against for a variety of databases, and seems to be relatively well-designed schema. I'm now making a point to get xo to work with all the django schema out of the box.

[smyrman]

Ok thx.

[kenshaw]

Quick update: I'm in the process of fixing both the PostgreSQL and MySQL code to fix the issue with multiple indexes. It's ending up a pretty long rewrite, as there's a lot of code that needs to be refactored, and I have not had as much time as I had hoped to work on it. This will likely be done in the next few days, but can't promise anything at the moment.

[kenshaw]

The latest code works correctly, however I still need do some tweaking with the way names are generated for indexes and add some options on the cli to toggle/control that. I tested the django schema with postgres, mysql, and sqlite and it works as expected for all three. The oracle code is still being rewritten for the new version, and hopefully I'll have that finished in the next couple days. When it is, I hope to add unit tests to test the django schema against all four databases that xo supports.

[kenshaw]

Closing this, as I believe it's now fixed.