Errors in duplication and copying Neatline items with geometry / WKB

[AntjeAnderson]

I have attempted in every way I can think of to copy either a Neatline Exhibit or copy Neatline Records into a new exhibit--partly just to create a backup copy, partly because I need to be able to export a Neatline Exhibit (if possible) to another Omeka website rather than recreating my map on that site. But

a) the duplication error noted by others but apparently not resolved resulted in hundreds of empty exhibits being created.

and b) every time I go into the database with MyPhP and try to copy a Neatline Record, I get an error regarding the geometry. It either cannot find any and tells me "Cannot get geometry item from data you send to the GEOMETRY field" or, if I choose any WKB geometry (the only option apart from text), it gives me an error message about a wrongly placed \ near the geometry. I cannot remove that . I have tried to edit the geometry in various ways, but I simply do not know what other parameters I need to change.

If there are instructions for this, or if there is another way of copying / duplicating the records, please let me know. I have also tried to create a CSV export file to at least have backups of my data, but the geometry column is unreadable gobbledigook. Even just understanding what I would need to do with the WKT/WKB data that is being stored to make it useable would be helpful.

Thank you.

[jeremyboggs]

@AntjeAnderson I'm looking at this now, and hope to send you an update by the end of the day, if not sooner. Can you share some more system information with us? Click on the "System Information" link in the footer of your Omeka admin panel. That will help me try to recreate the environment, with the same versions of PHP and MySQL and such, so I can see if I encounter the issue as well.

[AntjeAnderson]

Jeremy, thank you so much. Here is a screen shot of the system information:

[image: Screen Shot 2019-10-10 at 8.09.32 AM.png]

I started working with Omeka on Reclaim Hosting this past August, so I am very new to ALL aspects of it. I taught myself Omeka basics and Neatline from the tutorials, but I have no programming experience. I was shown how to use the MyPhPAdmin tab in Reclaim hosting to look at the SQL database tables, and the error about copying resulted from using the Copy function to copy individual records, and that is when I got these errors.

For what it's worth, I have also attached the CSV that I exported so you can see what the Geometry turns into. We also tried to export the entire SQL database, and looking at it as text shows the geometry/coordinates underlaid in black, which I understand to mean (with my totally limited knowledge) that it cannot read it as text.

Thank you so much for your help.

Antje

Antje Anderson, Ph.D. Graduate Student in Art History at UNL Former Professor of English at Hastings College

On Thu, Oct 10, 2019 at 7:56 AM Jeremy Boggs notifications@github.com wrote:

@AntjeAnderson https://github.com/AntjeAnderson I'm looking at this now, and hope to send you an update by the end of the day, if not sooner. Can you share some more system information with us? Click on the "System Information" link in the footer of your Omeka admin panel. That will help me try to recreate the environment, with the same versions of PHP and MySQL and such, so I can see if I encounter the issue as well.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/scholarslab/Neatline/issues/477?email_source=notifications&email_token=AND4HRDKELGHZU47C6RRCRDQN4RABA5CNFSM4I7G3NV2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEA4FPXA#issuecomment-540563420, or unsubscribe https://github.com/notifications/unsubscribe-auth/AND4HRADFBN6WGF7QLZAANLQN4RABANCNFSM4I7G3NVQ .

[AntjeAnderson]

I didn't realize the "themes" part was cut off from the system information screenshot. I highly doubt you need it, but here it is:

[image: Screen Shot 2019-10-10 at 8.23.43 AM.png]

Best,

Antje Anderson

On Thu, Oct 10, 2019 at 8:20 AM Antje Anderson aanderson@hastings.edu wrote:

Jeremy, thank you so much. Here is a screen shot of the system information:

[image: Screen Shot 2019-10-10 at 8.09.32 AM.png]

I started working with Omeka on Reclaim Hosting this past August, so I am very new to ALL aspects of it. I taught myself Omeka basics and Neatline from the tutorials, but I have no programming experience. I was shown how to use the MyPhPAdmin tab in Reclaim hosting to look at the SQL database tables, and the error about copying resulted from using the Copy function to copy individual records, and that is when I got these errors.

For what it's worth, I have also attached the CSV that I exported so you can see what the Geometry turns into. We also tried to export the entire SQL database, and looking at it as text shows the geometry/coordinates underlaid in black, which I understand to mean (with my totally limited knowledge) that it cannot read it as text.

Thank you so much for your help.

Antje

Antje Anderson, Ph.D. Graduate Student in Art History at UNL Former Professor of English at Hastings College

On Thu, Oct 10, 2019 at 7:56 AM Jeremy Boggs notifications@github.com wrote:

@AntjeAnderson https://github.com/AntjeAnderson I'm looking at this now, and hope to send you an update by the end of the day, if not sooner. Can you share some more system information with us? Click on the "System Information" link in the footer of your Omeka admin panel. That will help me try to recreate the environment, with the same versions of PHP and MySQL and such, so I can see if I encounter the issue as well.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/scholarslab/Neatline/issues/477?email_source=notifications&email_token=AND4HRDKELGHZU47C6RRCRDQN4RABA5CNFSM4I7G3NV2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEA4FPXA#issuecomment-540563420, or unsubscribe https://github.com/notifications/unsubscribe-auth/AND4HRADFBN6WGF7QLZAANLQN4RABANCNFSM4I7G3NVQ .

[jeremyboggs]

@AntjeAnderson I"m sorry, the image isn't displaying. Can you try again, or just email it to me directly at jeremy@virginia.edu?

[jeremyboggs]

@AntjeAnderson From the screenshot you shared, it appears your host is using MySQL server 10.2.26, which is rather startling, because I'm not sure that version is even available from MySQL itself. I have to look around further. If you can confirm with your host that your hosting does indeed provide 10.2.26, and if that's some database other than MySQL, that might help.

[jeremyboggs]

@AntjeAnderson Can you confirm in your Reclaim Hosting adminstrative panel that you're using MySQL, or using MariaDB? I suspect the latter, because the latest version of MySQL is 8.0 but MariaDB is 10.4. If so, you should see if you can switch to MySQL. We've never developed or testing Neatline on MariaDB. I'll see if I can get a test instance running with it, and see if I can replicate the problem you're having. Right now, I can't, using PHP 7.2 and MySQL 5.7

[AntjeAnderson]

Given the info that I just found, you seem to be correct. The server type is MariaDB. But it says that the database is MySQL? I have no idea what the difference is and how I would switch to MySQL within Reclaim Hosting, which is what my institution suggested I use for a host. I apologize; this is all just very confusing to me.

[image: Screen Shot 2019-10-10 at 9.22.47 AM.png] Antje Anderson, Ph.D. Graduate Student in Art History at UNL Former Professor of English at Hastings College 100 N. 12th St. # 1004 Lincoln, NE 68508 U.S.A. Cell: [001] 402-601-6850

On Thu, Oct 10, 2019 at 9:25 AM Jeremy Boggs notifications@github.com wrote:

@AntjeAnderson https://github.com/AntjeAnderson Can you confirm in your Reclaim Hosting adminstrative panel that you're using MySQL, or using MariaDB? I suspect the latter, because the latest version of MySQL is 8.0 but MariaDB is 10.4. If so, you should see if you can switch to MySQL. We've never developed or testing Neatline on MariaDB. I'll see if I can get a test instance running with it, and see if I can replicate the problem you're having. Right now, I can't, using PHP 7.2 and MySQL 5.7

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/scholarslab/Neatline/issues/477?email_source=notifications&email_token=AND4HREHT2MZY4FAD5ERXKDQN43OJA5CNFSM4I7G3NV2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEA4RRHI#issuecomment-540612765, or unsubscribe https://github.com/notifications/unsubscribe-auth/AND4HRB4U4PIGLYSP4NHJYTQN43OJANCNFSM4I7G3NVQ .

[techgique]

Hi all. @karindalziel asked me to take a look at this issue. I assume by now y'all have learned that MariaDB is a fork of MySQL by its creator a while after Oracle took control of the project. It's supposed to be drop-in replacement compatible with MySQL. It's possible that newer versions of the two have diverged a little, but that may or may not be relevant here.

I have a couple general ideas though. It's possible this is a phpMyAdmin bug or lacking feature where its interface / functionality isn't compatible with these fields. Are you able to export to a .sql file and reload / import that into a new database or table? Generally .sql files are the most reliable for backing up MariaDB/MySQL data in plain text for later restoration, rather than a CSV. You may see these files referred to as a "logical backup".

My other idea (which seems like more of a stretch based on the error message @AntjeAnderson reported) is the possibility that records from multiple tables are necessary to export / migrate Neatline records. If there are multiple records relying on each other, its possible that could be failing upon trying to load a single one of the records without the others. This would involve disabling foreign key constraints temporarily while importing data.

I'm unfamiliar with WKB and GEOMETRY records so that could be way off base. I see that Neatline uses the GeoPHP library to work with them, and that library hasn't been updated since 2014. This makes me a little more skeptical that newer versions of MariaDB and MySQL would have broken compatibility. Especially if the Neatline functionality is generally working through Omeka other than this record duplication / export work.

I'm not sure if any of this is helpful, but I hope it gives you some more ideas. :v:

[AntjeAnderson]

Thank you! This clarifies some things for me, and is and I will take the next few days to process it and try to see whether I can work around this problem. That said, I am unsure how I could solve or fix this, because I am very inexperienced when it comes to databases, regardless of whether it’s MySQL or MariaDB, and had not even heard of WKB and WKT until I started working on maps in Neatline. I am willing to learn, though.

NB—An attempt to back up the data in an sql file within Reclaim hosting/from the C-panel resulted in a text file with gobbledigook (random characters, not base 64 style) where the geometry should be.

I will report back if I get anywhere through some trial and error.

Antje

(James, FYI)

Best, Antje Anderson.

On Fri, Oct 18, 2019 at 11:11 AM Greg Tunink notifications@github.com wrote:

Hi all. @karindalziel https://github.com/karindalziel asked me to take a look at this issue. I assume by now y'all have learned that MariaDB is a fork of MySQL by its creator a while after Oracle took control of the project. It's supposed to be drop-in replacement compatible with MySQL. It's possible that newer versions of the two have diverged a little, but that may or may not be relevant here.

I have a couple general ideas though. It's possible this is a phpMyAdmin bug or lacking feature where its interface / functionality isn't compatible with these fields. Are you able to export to a .sql file and reload / import that into a new database or table? Generally .sql files are the most reliable for backing up MariaDB/MySQL data in plain text for later restoration, rather than a CSV. You may see these files referred to as a "logical backup".

My other idea (which seems like more of a stretch based on the error message @AntjeAnderson https://github.com/AntjeAnderson reported) is the possibility that records from multiple tables are necessary to export / migrate Neatline records. If there are multiple records relying on each other, its possible that could be failing upon trying to load a single one of the records without the others. This would involve disabling foreign key constraints temporarily while importing data.

I'm unfamiliar with WKB and GEOMETRY records so that could be way off base. I see that Neatline uses the GeoPHP library to work with them, and that library hasn't been updated since 2014. This makes me a little more skeptical that newer versions of MariaDB and MySQL would have broken compatibility. Especially if the Neatline functionality is generally working through Omeka other than this record duplication / export work.

I'm not sure if any of this is helpful, but I hope it gives you some more ideas. ✌️

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/scholarslab/Neatline/issues/477?email_source=notifications&email_token=AND4HRFXT3G6QRFU6G7SC5LQPHN2PA5CNFSM4I7G3NV2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEBU7HAY#issuecomment-543814531, or unsubscribe https://github.com/notifications/unsubscribe-auth/AND4HRAQHPVMKUJ566ZO4WTQPHN2PANCNFSM4I7G3NVQ .

-- Antje Anderson, Ph.D. Graduate Student in Art History at UNL Former Professor of English at Hastings College 100 N. 12th St. # 1004 Lincoln, NE 68508 U.S.A. Cell: [001] 402-601-6850

[techgique]

The "random gobbledigook" is probably binary data which won't look like base64 when viewing the text file. That must just be how the database software stores the info, so it will require a database client such as phpMyAdmin to read it. Even then, it may be formatted in such a way that only the GeoPHP and underlying GEOS libraries can read it and produce a human-readable display.

I'm not very familiar with this type of data, but these are my guesses as to what is going on.

[timmmmyboy]

For what it's worth this is happening to us on a server running MySQL 5.7 attempting to import from another server running MySQL 5.5, so seems the issue is not related to MariaDB at all. We'd love to know if there is another way to export/import Neatline records when this fails.