Subscribe to this thread
Home - General / All posts - MySQL read-write
atomek

422 post(s)
#09-Jan-17 10:31

Hi,

I'm having issues making linked tables to accept writes (no geoms, just table). Here is a detailed description of what I'm doing to get the MySQL connection running and what issues I encounter. Basically the problem is inability to get editable linked tables.

Manifold build 8.0.29.0

MySQL version 5.5.52 for Linux (x86_64) using readline 5.1 installed on Amazon EC2.

First I tried ODBC

  1. (Main window) Go to 'Tools > Database Console'
  2. (Database Console window) 'Press [...]'
  3. (Data Source window) Press 'Add Data Source'
  4. (Connect To window) In 'Files of type' dropdown I select 'ODBC Data Sources()' / 'ADO .NET ODBC Data Sources()'
  5. (Select Data Source window) 'New...'
  6. (Create New Data Source window) I select 'My SQL ODBC 5.3 ANSI Driver' / 'My SQL ODBC 5.3 Unicode Driver' and press 'Next'
  7. (Create New Data Source window) I navigate to a folder of choice and write filename I want to save the connection to 'ec2mysql-manifold_connection1' and press 'Next'
  8. (Create New Data Source window) No options here so I press 'Finish'
  9. (MySQL Connector/ODBC Data Source Configuration window) TCP/IP Server: [url to my database], Port:3306, User: [user], Password: [password], Database: [database], I press 'Test' and 'Test Result' pops-up with message 'Connection Successful' so I close it with 'OK' and 'OK' again in the window where I inputed database information
  10. (Select Data Source window) The file where connection is saved appears in the selection window so I pick it and press 'OK'
  11. (MySQL Connector/ODBC Data Source Configuration window) It's exactly the same window as before so I re-type my password as it's the only thing missing here and 'Test' again, again with success so I confirm it with 'OK'
  12. (Connect To window) Now I have my data source 'ODBC Data Source' available to select so I pick it and press 'OK'
  13. (Database Console window) Now I see my tables and their columns here, all seems fine
  14. (Database Console window) To test it, I type in and run a simple query: INSERT INTO table VALUES (10,2,3,4,5) and get a message saying 'Command completed (1 row(s) affected)'
  15. (Database Console window) I select the table where I inserted the value and click 'Link' button and a linked table appears in Project Pane
  16. I inspect the linked table to see that the value I inserted indeed is there but the whole table is grayed out so I can't edit nor add values
  17. (Database Console window) To test it again, I type in and run a simple query: INSERT INTO table VALUES (11,2,3,4,5) and get a message saying 'Command completed (1 row(s) affected)'
  18. I refresh the linked table to see that the value I inserted appeared but the whole table is still grayed out so I can't edit nor add values

Now let's try OLE DB

  1. (Main window) Go to 'Tools > Database Console'
  2. (Database Console window) 'Press [...]'
  3. (Data Source window) Press 'Add Data Source'
  4. (Connect To window) In 'Files of type' dropdown I select 'OLE DB Data Sources()'
  5. (Data Link Properties window) As provider I select 'Microsoft OLE DB Provider for ODBC Drivers' and press 'Next'
  6. (Data Link Properties window) For connection I use connection string and press 'Build...'
  7. (Select Data Source window) Here I press 'New...'
  8. (Create New Data Source window) I select 'My SQL ODBC 5.3 ANSI Driver' / 'My SQL ODBC 5.3 Unicode Driver' and press 'Next'
  9. (Create New Data Source window) I navigate to a folder of choice and write filename I want to save the connection to 'ec2mysql-manifold_connection1' and press 'Next'
  10. (Create New Data Source window) No options here so I press 'Finish'
  11. (MySQL Connector/ODBC Data Source Configuration window) TCP/IP Server: [url to my database], Port:3306, User: [user], Password: [password], Database: [database], I press 'Test' and 'Test Result' pops-up with message 'Connection Successful' so I close it with 'OK' and 'OK' again in the window where I inputed database information
  12. (Select Data Source window) The file where connection is saved appears in the selection window so I pick it and press 'OK'
  13. (MySQL Connector/ODBC Data Source Configuration window) It's exactly the same window as before so I re-type my password as it's the only thing missing here and 'Test' again, again with success so I confirm it with 'OK'
  14. Approach A - (Data Link Properties window) I press 'Test Connection' as get response 'Test connection succeeded' so I close the window with 'OK'
  15. Approach B - (Data Link Properties window) I fill in 'User name', 'Password' and 'Enter the initial catalog to use:' and press 'Test Connection' as get response 'Test connection succeeded' so I close the window with 'OK'
  16. (Connect To window) Now I have my data source 'OLE DB Data Source' available to select so I pick it and press 'OK'
  17. (Database Console window) Immediately after I see this window a pop-up appears saying 'Can't establish connection to data source: error binding to data source.'

In total I have tested 8 approaches made by different combinations of: ODBC/OLE DB/ADO .NET ODBC, ANSI/Unicode drivers, entering and leaving blank the user credentials in Data Link Properties window for OLE DB.

Can anyone advise how to get editable linked tables from MySQL into Manifold?

Thanks,

Tom

yves61
438 post(s)
#09-Jan-17 17:53

atomek,

I am not a SQL wizard but I see that you do not mention in which fields the values need to be inserted.

you use :

INSERT INTO table VALUES (11,2,3,4,5)

should it not be

INSERT INTO table (field1, field2, field3, field4, field 5) VALUES (11,2,3,4,5)

atomek

422 post(s)
#09-Jan-17 18:04

This step works fine, it's just a sense check if the connection even works.

But in terms of query syntax as long as the number of values is equal to the number of fields it is ok.

May not be very relevant for the issue I'm facing but just in case - in working in Windows 10

mechalas

839 post(s)
#27-Jan-17 16:54

I don't believe Manifold supports read/write tables via ODBC connections. I have monitored the SQL conversation that Manifold makes when linking a drawing vs linking a table and I don't see anything in the query process that makes me think that the tables in MySQL are problematic. It's just that Manifold treats tables differently, and doesn't set them up as writable. Probably because it assumes there is not an ID field that it can use to read and write to the data.

If you link a drawing, you get read/write. If you link the table underlying a drawing, you get read-only.

This may be a safety mechanism. When using a generic database via ODBC without specific spatial support, the drawing and the spatial index are stored as separate tables. Because of this, Manifold can't really tell the difference between a table that is "just a table" and a table that is connected to a drawing. They both show up as tables in the database system. Yes, there is metadata associated with the drawing component that it can use to identify a drawing, but there is nothing stopping you from linking to directly to a table...even once that belongs to a drawing. And if you modify the table underlying a drawing component directly (as a table instead of as a drawing), then the drawing table and the spatial index table can get out of sync.

My guess here is that, rather than try to add a bunch of complex logic to figure out if your table is just a table or a table associated with a drawing (or worse, a spatial index table), Manifold goes the safe route and forces table links to read-only.

This isn't a huge limitation for interactive use. It's actually easier and faster for me to use a DB front-end client to manipulate data in tables than to use Manifold. I can have Manifold link the table read-only, and refresh the data after making changes to the table on the DB server. But if you are trying to build a server application that needs to manipulate data in tables, then it's not going to fly. You'll need MS-SQL Server.

Dimitri


7,413 post(s)
#28-Jan-17 08:16

I don't believe Manifold supports read/write tables via ODBC connections.

? Not true, and way, way off the mark.

You can see for yourself 8 has no problem reading and writing tables by creating an ODBC connection to an MDB. I suggest an MDB as an easy means for anyone to see read/write via ODBC is no problem for 8. It's a simple test case that avoids misconfigurations and the usual "do what I mean and not what I tell you to do" configuration problems that can happen in more complex data sources. Once that's clear people can move on to figuring out what really is going on in a particular case.

The usual problem is permissions, user roles, data source configuration, misc security features etc that are not configured correctly for what you want to do. You can't write the table because the software has been told, via roles or whatever, not to allow you to do that. Sort all that out and you're fine.

In terms of spatial info in tables in various data sources that opens up yet another can of worms. Lots of detail to consider there but a generic inability to write tables is not one of them.

It's actually easier and faster for me to use a DB front-end client to manipulate data in tables than to use Manifold.

Depends on what you want to do. I agree if you have a client that you know well and know how to connect to a given source in a routine way, sure, that will be easier than if you need to learn a different way in a different tool, such as Manifold.

But then you don't get the power of Manifold's table tools or other features. How many DB front-end clients have the power of Manifold's token manipulation in tables, for example? The ability to do things like this is extremely useful.

mechalas

839 post(s)
#29-Jan-17 04:53

Thanks for the clarification and correction, Dimitri. Must be an issue with the MySQL ODBC connector, then. Since I can link a drawing component read/write but not a table (nor can I get a r/w link of the table behind the drawing), that suggests that the ODBC connector isn't providing something that Manifold is looking for when specifically linking to a table.

atomek

422 post(s)
#29-Jan-17 11:04

Then how can we explain that Database Console insert query works fine while inserts via interactive table edits (opened from linked table in Project Pane) don't work (table contents are grayed out)?

As per my step 14, using ODBC:

(Database Console window) To test it, I type in and run a simple query: INSERT INTO table VALUES (10,2,3,4,5) and get a message saying 'Command completed (1 row(s) affected)'

I assumed both approaches would utilise the same connector.

mechalas

839 post(s)
#29-Jan-17 23:47

The database console is just sending raw SQL. When linking a table, though, Manifold must be looking for something about the table structure that it's not getting from the ODBC connector.

I suppose it's also possible that it's not the ODBC connector, but rather whatever it is that Manifold is looking for in the table that MySQL doesn't support.

adamw


10,447 post(s)
#31-Jan-17 07:12

I suppose it's also possible that it's not the ODBC connector, but rather whatever it is that Manifold is looking for in the table that MySQL doesn't support.

Yes, this is it.

The solution (for Manifold) is to be more flexible regarding the interface provided by the database.

mechalas

839 post(s)
#30-Jan-17 14:11

I spent some time on this early this morning because I used to do this sort of thing for a living, and though I learned something interesting it didn't reveal any solutions. I turned on debugging in the ODBC connector for MySQL, and also monitored the TCP stream in Wireshark, just to see what was going on and when.

When you link a table, Manifold makes this sequence of SQL statements:

-- Query logging

--

--  Driver name: MySQL ODBC 5.3 Driver  Version: 05.03.0007

-- Timestamp: 170130  5:31:00

1485783060:SELECT * FROM `Foo`;

1485783060:Using direct execution;

1485783060:query has been executed;

1485783060:SHOW KEYS FROM `Foo`;

1485783060:SHOW KEYS FROM `Foo`;

Now, the obvious question is, since a Drawing component will link read/write but a Table component won't, what's the difference? Well, first, when linking a Drawing Manifold will query MFD_META for information about the spatial index. That's how you get the Area of Interest prompt before it brings in the table:

1485783672:SELECT `PROP`, `VAL` FROM `MFD_META` WHERE `OBJ` = 'POI';

1485783672:Using direct execution;

1485783672:query has been executed;

1485783672:Using prepared statement;

1485783672:SELECT `VAL` FROM `MFD_META` WHERE `OBJ` = 'POI' AND `PROP` = 'SpatialIndex';

1485783672:Using direct execution;

1485783672:query has been executed;

1485783672:Using prepared statement;

1485783672:SELECT `VAL` FROM `MFD_META` WHERE `OBJ` = 'POI' AND `PROP` = 'SpatialIndexXMin';

[rest omitted]

Then, when you link, it fetches the table contents, just as if you were linking a Table component:

1485783672:SELECT * FROM `POI`;

1485783672:Using direct execution;

1485783672:query has been executed;

1485783672:SHOW KEYS FROM `POI`;

1485783672:SHOW KEYS FROM `POI`;

But what you don't see in the ODBC debug log is that Manifold is doing this to get just one row of the table using SELECT * (it shows up in the TCP stream):

set @@sql_select_limit=1

SELECT * FROM `POI`;

Then it fetches the entire table by SELECTING using explicit column names:

1485783672:SELECT `POI`.`OID`, `POI`.`Version`, `POI`.`SourceID`,

`POI`.`Geometry`, `POI`.`Cat`, `POI`.`Name`, `POI`.`Country` FROM `POI`;

It doesn't do it this way when you link a Table component. Presumably, it selects one row to get the list of columns, but I don't know. Anyway, after this, it then preparses a bunch of SQL statements on the server for use during update, with OID as the primary key.

What happens if I select the POI drawing as a Table instead of as a Drawing? Sure enough, it uses the "table" execution sequence and not the "drawing" execution sequence. From the raw TCP stream:

set @@sql_select_limit=DEFAULT

SELECT * FROM `POI`

SHOW KEYS FROM `POI`

SHOW KEYS FROM `POI`

So Manifold really does act differently if you are fetching a table as a Table component vs. as a Drawing component. And I don't mean the spatial indexing: how it actually fetches the table is different. And whether or not that table has an index doesn't seem to matter since the POI table definitely has a primary key: the OID column, which is used when it's linked as a Drawing component.

So something is amiss here. But I don't know if it's worth pursuing, with the next major release just around the corner.

Dimitri? What do you think? Is it worth reporting this as a potential bug in 8 or not?

Dimitri


7,413 post(s)
#30-Jan-17 16:55

Dimitri? What do you think? Is it worth reporting this as a potential bug in 8 or not?

No, it's something I'd look at with Radian and if there is a problem dig into that.

The Radian experience has been interesting because when there are so many different data sources in play that unexpected results are often an integration issue where there is legitimate reason to doubt whether something should be called a bug or not.

For example, in the beta forum there was a puzzling issue with a query that took data from postgresql and put it into spatial lite with unexpected results. It's cool one can use Radian for such things as if everything was local but the opportunities for integration misalignment are obvious. Here's what adamw had to say:

Regarding the geometry from PostgreSQL coming as NULLs in SQLite.

We found the issue. It is related to the difference in WKB formats used by PostgreSQL and SQLite - PostgreSQL uses EWKB, SQLite does not understand it. Unfortunately for us, SQLite not just does not understand the extension to WKB used by PostgreSQL, it rejects it silently without throwing an error, so our code thinks inserting data went fine while it did not.

I don't know about anybody else but I didn't know PostgreSQL used a different WKB format than SQLite. I wouldn't say that's a bug on anybody's part, just an integration issue where if you dug deep enough into all the manuals you might discover the possibility of such an issue and say, "need a different way to get there from here than that path..." No problem. We can guard against such thinks with a small tweak in Radian. If that is what needs to be done in your case, that's what will happen. The important thing is to identify with technical precision what the issue is.

mechalas

839 post(s)
#30-Jan-17 18:02

Thanks, Dimitri. I will give this a go in Radian. I see it appears to have a native MySQL connector which may render the point moot, but it's probably still worth looking at.

Agreed that with integration the devil is always in the details, and sometimes small details matter a great deal. I'll be as thorough as I can when I run through it.

volker

1,086 post(s)
#30-Jan-17 19:04

In the past i use Mfd 8.0 + MySql without Read & Write problems.

But on my current PC i only use MS SQLServer..

So far i know i did the connection with the MS OLE DB Provider for ODBC-Drivers.

-------------------------------------------

MFD DataSource Dialog:

Provider=MSDASQL.1;

PersistSecurityInfo=False;

User ID=root; (that was my personal use)

Data Source=MySQL;

Initial Catalog=mysql;

--------------------------------------------

In the MFD folder was the myodbc3.dll driver...

User: root (thats me)

Host: localhost:3306

Hope this helps...


http://www.thegisservicesector.de

mechalas

839 post(s)
#30-Jan-17 21:26

Aha. This jogged my memory enough that I was able to find this note from Manifold in my mail archives:

Both Manifold and the Microsoft OLE DB Provider for ODBC only consider an ODBC data source read-write if it exposes a read-write cursor that matches certain requirements. Manifold's requirements are stricter than those of the Microsoft OLE DB Provider and so some ODBC data sources are considered to be read-only when accessed directly from Manifold and read-write when accessed through the Microsoft OLE DB Provider. We are planning to relax the requirements we use to evaluate the cursors exposed by ODBC data sources in future versions of Manifold.

It's still a mystery why Drawings come through r/w while Tables come through r/o, but perhaps the requirement was relaxed for the former and not the latter?

adamw


10,447 post(s)
#31-Jan-17 07:07

In Manifold 8 changes to geometry data are handled differently from changes to other data, that's the reason for the discrepancy. We fixed it in Radian.

atomek

422 post(s)
#31-Jan-17 07:51

when using last Radian test build (157) and connecting with ODBC connector (so not utilising the native MySQL connector) I am successful with adding records to my database

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.