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

Question

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?

Answer 1

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"