# QGIS crashes when adding vector layer from mysql or testing connection to mysql

For a while I have been adding layers from my mySql database and all went well.

Then I got crashes when adding new vector layers.

Then I only got crashes and it is not possible to add layers from the mySql database any more (crash) or create a connection, then test it (crash). Sporadically I can get to the list of the tables in the mySql database after creating a new connection but usually QGIS crashes.

I tried deinstalling and reinstalling QGIS. Did not help.

I am using the menu "layer" > "add vector layer" > "database" (in German, so maybe a little different).

The table I am trying to add is named "tblTeilPopMassnPolygon", contains an id "TPopMassnPolygonId" with Primary Key and Auto Increment. Plus a polygon field "TPopMassnPolygonPolygon" and a field named "TPopMassnGuid", varchar 40, to save GUID's in. There are 6000 datasets in the table. I had tested them with the polygon data before and it worked. So there is probably nothing wrong with the data itself.

I tried using shorter names for table and fields but that didn't help.

How can I get QGIS to connect to my mySql tables again?

-
Alex, you mentioned in a prior thread that some of your tables (maybe this one?) had multiple geometry columns.. I'm wondering if this could be creating trouble for Q? For the sake of testing, would you mind renaming TPopMassnPolygonPolygon (which I assume is of type GEOMETRY--I don't think GEOGRAPHY is available in MySQL?) to just SHAPE and see if Q behaves any different? –  elrobis Dec 22 '12 at 2:18
Hi. I finally got to working on it again. No table has multiple geometry columns anymore. I have two tables having one a point and the other a polygon column. They dont seem to create a problem. I have three other tables: One with a point column, one with a line column and one with a polygon column. I use them to create either points, lines or polygons for a certain table. I have isolated the problem so far: If these three tables are in the database QGIS crashes when connecting. When I remove them, QGIS works. I am very baffled as I can tell absolutely no problem in these tables –  Alex Dec 25 '12 at 22:11
More information: When I leave the three tables in the database but delete all data from them I can connect to the database. But when trying to open one of these tables QGIS crashes –  Alex Dec 25 '12 at 22:16
O.k.: I deleted the three problematic tables and left the one with the shorter names. This seems to work. I really don't know what is happening and feeling bad about this project, afraid that it can break any moment. But right now it seems to work. Thanks for helping and I would still be very glad to know the reasons behind this behaviour –  Alex Dec 25 '12 at 22:27

I have two thoughts.. and I think I favor the second one..

1. Applying some OGR defaults: When you manually create the spatial tables, use OGR_FID int(11) PK NN UQ AI and SHAPE geometry NN for the id and shape fields. Make these the first and second fields, respectively. This isn't any fixed convention I am aware of, but if I import a shapefile into MySQL using ogr2ogr, the table structure always has these traits---think of them as defaults.

2. Start with empty shapefiles, then import them into MySQL: Start all of your tables as empty shapefiles (in QGIS: Layer > New > Shapefile layer), built for whatever field structure you desire. (Caveat, you'll hate the limitations imposed by the underlying DB4 attribute format, but the goal is QGIS-safe spatial tables.) Next, use ogr2ogr to import these "shell" shapefiles into MySQL. This approach should result in sanitary, QGIS-safe spatial tables.

There is a reason that I like option 2. When ogr2ogr imports shapefiles into MySQL, it automatically does three things--- 1) it creates spatial indices for the feature geometries, 2) inserts into the geometry_columns table, and 3) inserts into the spatial_ref_sys table.

If you're interested in the second approach, you can get ogr2ogr as a component in FWTools---but this advice comes with the warning that FWTools is no longer maintained, thus it doesn't have the latest GDAL/OGR revision. However, FWTools is a simple setup-and-get-rolling solution, and that version of ogr2ogr will do a SHP to MySQL import, no problem.

Also, here is an example ogr2ogr command to import a shapefile into MySQL (it assumes your DB schema already exists):

ogr2ogr -f "MySQL" MySQL:"dbName,user=root,host=localhost,password=pass" -lco engine=MYISAM "C:\path\to\shapefile.shp" -nlt "geometry"

-
thanks a lot, great input. Will try it out! –  Alex Dec 28 '12 at 21:57