Upload problem to mysql database

[John Ralls]

On Jan 21, 2011, at 1:39 PM, Erwin Winkel wrote:

> John,
> thank you for your reply. Inspired by your answer I decided to test the
> procedure manually. So, manually I executed the DROP TABLE command for all
> tables. After that the database had 0 tables, so that went ok ;-)
> 
> Then the import of the GnuCash data. After a while I got the same message
> that data could not be read. However, when I checked the mysql database I
> saw to my surprise that all the tables were created. When this was true for
> the tables then what about the data?
> Surprise, surprise, also the data was uploaded.... Strange. I checked the
> endfigures from the xml file with the endfigures of the mysql file and they
> match so I assume that it has gone alright.
> 
> It seems as if the upload works, but for some reason the error message is
> fired. On the other hand, how can one be sure that everything has been
> uploaded? Checking hundreds of records manually is not an appealing job....
> 
> So, for the time being I think it works, although the error message is a
> mystery.
> 
> Regards,
> erwin
> 
> 
> 2011/1/21 John Ralls <jralls at ceridwen.us>
> 
>> 
>> On Jan 21, 2011, at 11:02 AM, Erwin Winkel wrote:
>> 
>>> Hello list,
>>> I use GC 2.4.0 and would like to use a mysql database to store the data.
>> I
>>> have uploaded the existing data succesfuly once. However, when I try to
>>> upload a new version of this data, the upload fails with an error
>> message.
>>> First there is a question if the existing data should be overwritten and
>>> when
>>> this is confirmed the error message pops up stating that there is bad or
>>> unreadable data.
>>> This is hardly to believe because in the xml-structure everything works
>>> fine.
>>> I tried this both with compressed and uncompressed xml settings.
>>> 
>>> Has someone experienced this as well? What is going wrong here?
>>> 
>> 
>> It sounds like you're trying to use an XML file as  your primary storage
>> and to periodically run File>Save As to save it to a MySql database. That's
>> unlikely to work unless you have set up your user account in MySql to have
>> DROP TABLE permissions. The same would be true of Postgres. SQLite doesn't
>> have such security features, and it's writing a local file anyway, so it
>> should work.
>> 
>> In any case, it's not the intended use of the sql backend, which is that
>> you will convert your XML file to a SQL database and then use the database
>> with Gnucash. Saving to XML periodically will work, because the XML file is
>> always rewritten anyway and because you have write authority in your local
>> directory. You won't have the automatic backups (you'll have to rename your
>> old XML files yourself outside of Gnucash... or use a different file name
>> every time). It's probably better, though, to use MySql's backup facilities.
>> 

Please file a bug against the sql backend. Attach an image of the error dialog box and the last 30 lines or so of /tmp/gnucash.trace (assuming a Linux box; it lives elsewhere on OSX and Windows), copied before you dismiss the error dialog box.

Regards,
John Ralls