[how to] Move from Embedded to MySQL Backend

Discuss the database features

[how to] Move from Embedded to MySQL Backend

Postby chenier » Thu May 31, 2012 7:02 am

After losing data from a power failure while using Base as Embedded, I decided to convert to Front end-back end configuration. I was unable to move to hsqldb 2.x implementation for technical reasons, so converted to MySQL back end. Here are my notes for anyone else planning this migration. Feel free to add additional info as comments! By the way, I really like my new setup! MUCH faster than my database was while embedded. I have setup automatic backups via MySQL Administrator (writes a cron job in my case) which drops data backups into my DropBox folder. After further trial, I will likely install the MySQL server on my always on Zentyal server. Nuff said, I hope my notes can help.

I use LinuxMint 11 Gnome 64, LO

Install MySQL server and admin stuff. Using Software Manager

    mysql-server --this is a metapackage for all the required server programs for latest build
    mysql-admin --this is a GUI admion tool for mysql
    mysql-query-browser --used to enter queries and scripts directly
    libreoffice-mysql-connector --needed to connect directly to MySQL without ODBC or JDBC wrapper.

Initial Steps - Connecting to MySQL on my computer
Open mySQL Administrator and enter the following:
    hostname = localhost
    port = 3306
    username = root
    password = [blank]

MySQL Administrator - Next Steps
    create password for root account
    create new user - same as my normal user
    Create a new Schemata
      Right click in Schemata area
      Select create schema
      Name it GVaccounts
    Go to my user and add all Schema Privileges to GVaccounts

Connect from OpenOffice
    Now I want to connect to this new schema from LibreOffice Base and save as a new Database
    Open LibreOffice Base
    Connect to an existing database
    Select MySQL and press next
    Asks for ODBC or JDBC Connectivity.... OK time to install the libreoffice-mysql-connector package from software manager to see if I can get native support
    Awesome, restarted base and now get a third option to Connect directly! Choose that one :)
    Database name: GVaccounts
    Server/Port = localhost 3306
    Saved ODF file as GVaccounts in appropriate directory
    got an error that could not connect (using password = no).... need to figure out how to enter password
    Do this in Edit/Database/Properties

Transfer Tables -- Method 1 - Copy and Paste (I did not Use, but would work fine)
This method may be faster than the method I used, but does require that you pay close attention to Field formats, writing down the format for each field, then selecting the proper format as part of the Import procedure. You will also may need to set your key fields after you import the data. The steps are the same as the Data Import - Copy and Paste into Existing Table section below

    ... except after you right click in the Table area of the new MySQL connected base file,
    you type the name of the new table you are creating,
    then you select to import definition and data
    then you need to setup your field data types
    and setup your keys (maybe after importing data)?
    I quickly tested this method and it seems to work fine, but I personally imported my table definitions from the sql dump instead.

Transfer Tables -- Method 2 - drag over (was BROKEN for me, but documenting for completeness)
    Now that I can connect to MySQL database, copy the tables over
    Open the new MySQL pointing database side by side with old copy of my database and attempt to drag and drop tables
      I get the message "unknown database root@localhost when logged to server by root
      I get the message "create command denied by User..." when logged to server by my new user
        seems like it is not pointing to specific database... also the system database mysql shows in the Tables
        I may need to change Server parameter from localhost to something that specifies the exact database...... looking into it
        OR... the Database name I give of "GVaccouts" is missing something
      TEST appending data
      as mentioned, I see GVaccounts and mysql databases
      So I create a table in the GVaccounts database
    in MySQL Administrator Tools/manage connections create new connections
      Tried to create a new connection to a specific database using another port, but did not work
    It may be that current native MySQL connections don't have Schema unique connectors... see related answer at bottom of this post: viewtopic.php?f=40&t=32741
    OpenOffice Bug reports including copying tables into Base connected to a MySQL database not working properly... This exactly describes my problem.
    http://wiki.services.openoffice.org/wik ... k_properly
    There are some report of dragging to copy working with JDBC based connection (not native), but the above report suggest this is also broken. Here is the report where one users stated success. viewtopic.php?t=6924
    Not sure I want to also setup a jdbc connectoion to MySQL while I am not sure it will work either, so I think I will try the next approach on my radar...

Transfer Tables -- Method 3 - Export sql dump file import definitions (I actually used)
Because dragging and dropping tables was not working, try a more round about method of Exporting from the old database.
    Export database
      Open my old database
      Enter command
        SCRIPT '/home/UserName/pathiwant/database.sql'
      click on Execute
      Status reports "Command successfully executed." --seemed way to fast for the amount of data I have.
    View generated SQL statement
      Right click file and select Open with Text Editor (gedit)
      Amazing, Table create statements exist for each table, plus Insert statements for all data! Looks great so far.
    Import Tables one at a time
      keep database.sql file open in text editor
      Open MySQL Query Browser
      copy a create table statement over and execute
        getting a syntax error, studying syntax
      Modifications based on SQL syntax allowed:
        CACHED (from CREATE CACHED TABLE) apparently not allowed
        Remove quotes from around table and field names... not correct
        longvarchar is not an acceptable data type
        primary key must be specified as separate item, separated by comas e.g. ", PRIMARY KEY(field), "
      Syntax guides - for ful reference
      Example of query that did not work at first, then how I modified it so it did work:
        Did not work:
        CREATE CACHED TABLE "CategoryDef"("Category" VARCHAR(50) NOT NULL PRIMARY KEY,"CategoryDetails" LONGVARCHAR)
        CREATE TABLE CategoryDef(Category VARCHAR(50) NOT NULL, PRIMARY KEY(Category), CategoryDetails VARCHAR(255))
    FIX: Enable ANSI quotes in MySQL as described here: http://dev.mysql.com/doc/refman/5.6/en/identifiers.html plus modes described here:http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html
    I will change to ANSI quotes mode via MySQL Administrator as follows.
      Open MySQL Administrator, connecting to the correct data source
      Navigate to Startup Paramaters, then the Advanced tab
      check the box "Use ANSI sql" about 2/3 way down page
      click the save changes button at the bottom right of the window
        I get an editing right error
        bottom of form tells me my config file is at /etc/mysql/my.cnf
        this means I need root privelages to make this change
        look at menu item to figure out how to launch from command line
      Close, then restart MySQL Administrator from command line as follows:
      gksudo /usr/bin/mysql-admin
        make the change described above. I suggest when you get to the check box you first click revert changes, then check the ANSI sql box, then save changes. (when I was scrolling down, I accidentally changed another value on the form when I scrolled over it... and the value scrolled to another value). The revert button changes anything back to how it was until you hit save.
      go to Service control in MySQL Administrator, Stop the server, then restart.
    Re-open MySQL Query Browser -- CREATE TABLE
      paste another of the CREATE TABLE statements which was output from Base into it.
      remove the CACHED from CREATE CACHED TABLE
      fix any mis-mached data types (this time there were none)
      Query browser now accepts how the primary key is defined, so no need to change it
    Actual SQL statement modifications I made for CREATE TABLE statements
      remove CACHED from between CREATE TABLE
      changed LONGVARCHAR to VARCHAR(255)
    Table type = InnoDB: For more rigorous data security, I want to modify the Data Storage Engine from MyISAM to InnoDB. As I read it, InnoDB is more hardened to improper shut downs, etc and less likely to be corrupted from unexpected issues.
      Open MySQL Administrator
      Select Catalogs, then the schemata desired
      Select a table, then choose the button "Edit Table"
      On Table Options Tab, in the Sotrage Engine area, choose InnoDB from the drop down box.
      Click Apply Changes, then execute.
      Repeat for each table.
    ALTER TABLE statement in the sql dump are not needed (so ignored) as they were related to the "GENERATE BY DEFAULT..." statement we replaces with AUTO_INCREMENT. MySQL will auto-increment based on the largest value+1.
    CREATE VIEW statements... not sure yet if I need these views with MySQL backend. I will insert them for now... by copying one CREATE VIEW statement at a time into Query Browser.
      casewhen does not exist in MySQL, converted statement to CASE WHEN... ELSE...END statements
      You can see the new views in MySQL administrator via Catalogs/YourSchemata/Views tab
      And I can see the Views in Base in the Table browser section


Copy and Paste (append) into Existing Table Base (old) to Base (new)
    After trying to copy whole tables including definitions without success, I gave up on the Copy from One Base (embedded-old) to the other Base (MySQL connected-new). Turns out I gave up too soon. Discovered you can Copy and Paste if you have the Table Definition and only Append the Data.
    I did have a data definition conflict on one table which I couldn't figure out how to resolve, so ended up copying data over for that table via the next method.
    This is much like the following procedure, but much quicker.
      Verify your table definitions exist properly in the new Database/MySQL
      In the old Base program Right click a table and select copy
      In the new Base program Right click the corresponding table and select paste
        Select Append data
        Ensure the table name is correct as SchemaName.TableName
        click next then make sure all the source table fields are selected and the orders fo the fields are the same
        Click Create and verify your data was imported.

Copy and Paste into Existing Table -- Success -- Second Easiest Approach
Thanks to: http://user.services.openoffice.org/en/forum/viewtopic.php?f=5&t=38908
    Register your old database as a data source in LibreOffice
      Tools/Options/LibreOffice Base/Databases/New
      Give the data source a name and point to the file :)
    If your table has lots of data, to avoid data loss, or computer crashes, you may need to create queries and separate your data into smaller chunks via simple select queries. You would then copy data to the spreadsheet from the query instead of the table, so as to work with a manageable sub-set of data.
    Import data into spreadsheet
      Open a Blank spreadsheet (or new tab)
      Press F4 or select View/Datasources
      In the new data section above the spreadsheet, Navigate to the Table of the old database you want to import
      When the table data shows to the right, click in the upper left box of the data to select all data (or select all or a subset any other way)
      Copy data down to the spreadsheet by copy/paste or by dragging
    Copy data from spreadsheet into Database
      Highlight the data in the spreadsheet, including headers
      Navigate to you new MySQL connected database
      Right click on the Table name you want to append this data into and choose paste
      Choose "Append data" and "Use first line as column names", verify the table name is correct as YourSchemaName.TableName and press Next
      Make sure the box is selected for the Source table field of each field you want to import (each one in this case)
      Make sure the field order between the Source and Destination tables are the same, use the arrows to modify if needed (shouldn't be necessary if you created tables exactly the same)
      Select the Create button, then open the table to verify your data is there (may need to wait a sec for large amounts of data)

MySQL Query Browser: Modified Data Via Find and Replace --Works
    Open the .sql dump file in a text editor
    Locate a desired set of INSERT INTO statements (perhaps a single table, perhaps all your tables).
    Copy and paste into a blank document in a text editor of your choice. I am using LibreOffice Writer 3.5
    Find and replace to place semi-colons at the end of each line.
      Find statement:
        If you are certain that your data has no end parenthesis within it ")", then you can simply use that.
        Some text editors allows you to use "&" for end of line, mine did, but skipped lines. Finding on this was enabled via selecting "regular expressions" find
        if your last field was always text you could use ') or NULL)
      Replace everthing you find plus a semi-colon. If you use the & find, see help link above for re-inserting end of line code
      My example:
        position at the top of the document
        Edit/find and replace
          ensure "regular expressions" is not selected in "more options" section
          Search for = )
          Replace with = );
          select replace all
    Open MySQL Query Browser and connect to your database
    Select File/New Script tab
    Copy and paste your modified INSERT INTO statements into the New Script tab
    Click on Execute button
    Open your database and verify the records have been inserted.

MySQL Command Line: Load Data Local Infile --Stumped
syntax: http://dev.mysql.com/doc/refman/5.6/en/load-data.html
    Open the .sql dump file in a text editor
    Locate a desired set of INSERT INTO statements (perhaps a single table, perhaps all your tables).
    Copy and paste into a blank document in a text editor of your choice. I am using LibreOffice Writer 3.5
    File/Save as text format (.txt)
    Open mysql command line (open terminal in Linux)
      open terminal (Linux)
      mysql -u DBUser -h [localhost or servername or ip] -p
      "-h localhost" is optional on local machine
    Connect to your database
      connect YourDB
    Load File...... OK not working because it is looking for a data file only, not sql statements.
    This would work well if you had data in a spreadsheet then saved it as a delimited txt output file.
    I could modify my SQL dump to remove the extra stuff with find and replace, but what's the point... could load into MySQL Query browser just as easily... see next section
      for fun, I did this... but didn't follow through to complete success (unsure how to quote a single quote successfully) The statement I got to, then decided it was unnecessary effort and no more fun was this:
      LOAD DATA LOCAL INFILE '/home/chenier/Documents/GreenView/GVaccount/DataTemp/HouseDef2.txt' INTO TABLE HouseDef FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY ' LINES TERMINATED BY '\r\n';
      error on the FIELDS ENCLOSED BY

Export to Spreadsheet, Create delimited file, then import into SQL --Not Tried, but I know this would work
    This is much like the section above, but with exporting data into txt file via spreadsheet save as instead of directly from sql dump.

MySQL Command Line run SQL Statement --Stumped

Queries, Forms and Reports Importing
    Open the old database and the new database side by side
    Drag and drop (or copy/paste) each object you want to keep from one to the other

Modifications Needed to Queries, Forms and Reports for MySQL
This is not a comprehensive list, just the items I ran across
      Change Table Names whenever referenced
        Form Properties/Data tab => Content and Filter properties
        Change table name to include Schema name in front
          e.g. Filter to GVaccounts.Filter
          Remove Double quotes from around Table name whenever exist

Moving Macros over and Modifications
    In the new Database, Create modules to correspond to those in the old database
      Tools/Macros/Organize Macros/LibreOffice Basic
      In your database, navigate to the correct location and select new
    Copy and paste macros from within the modules in the old database to within the same modules on the new database
    Rename the macro calls if you need to
    Changes within the macros
      none yet...


    LibreOffice Base FAQ - lots of info https://wiki.documentfoundation.org/Faq#Base
    MySQL Connector/OpenOffice.org -- Thorough reference
    http://dev.mysql.com/doc/refman/5.6/en/ ... r-ooo.html
    and similar reference from mysql web site: http://forge.mysql.com/wiki/Connector_OpenOffice
    OpenOffice native MySQL reference
    http://wiki.services.openoffice.org/wik ... ection_URL
    Lists connection URL as: sdbc:mysql:mysqlc:[host[:port]]/database
    And gives details
    C connection API for MySQL (extra info)
    http://dev.mysql.com/doc/refman/5.6/en/ ... nnect.html
    X-driver (Openoffice Wrapper) connection info: (extra info)
    http://www.openoffice.org/api/docs/comm ... ml#connect
    OpenOffice Bug reports including copying tables not working propertly
    http://wiki.services.openoffice.org/wik ... k_properly
    OpenOffice Bugs - general for MySQL Native Driver
    http://wiki.services.openoffice.org/wik ... SQL_Native
    Importing MySQL Dumpfile (something.sql)
    http://www.cyberciti.biz/faq/import-mys ... -database/
    http://crazytoon.com/2007/11/28/mysql-h ... ll-script/
    MySQL statement syntax, enabling ANSI quotes!: http://dev.mysql.com/doc/refman/5.6/en/identifiers.html
    explains and lists modes: http://dev.mysql.com/doc/refman/5.6/en/ ... -mode.html
    MySQL INSERT INTO syntax guide: http://dev.mysql.com/doc/refman/5.6/en/insert.html
    Techotopia guide about INSERT INTO http://www.techotopia.com/index.php/Ins ... L_Database
    Export sql dump file from Base
    This one is presented simply enough: http://www.oooforum.org/forum/viewtopic.phtml?t=32333
    example given: SCRIPT 'C:\xtrasns.sql' in the Tools/SQL section, then open file and past individual statements rather than the whole thing for importing
    Exporting OpenOffice Files into other OpenOffice Programs via Copy and Paste or F4, once you have registered your database... not useful for importing data into Base, but very useful for outputing Query results to spreadsheets, etc.
    http://www.ehow.com/how_5914884_export- ... table.html
    Installing MySQL on Ubuntu: http://blog.sudobits.com/2011/09/25/how ... ntu-11-10/
    MySQL postinstallation setup and testing (general) http://dev.mysql.com/doc/refman/5.5/en/ ... ation.html
    Getting Started with MySQL http://dev.mysql.com/tech-resources/art ... intro.html
    MySQL Administrator Documentation: http://dev.mysql.com/doc/administrator/en/
    MySQL Workbench GUI tool..... hmmm http://www.mysql.com/products/workbench/
    This product looks sharp and is said to replace both MySQL Administrator and MySQL Query browser plus much more including visual Database Design..... Worth checking into sometime. Workbench area has SQL Development, plus Data Modeling and Server Administration. Visual Database Design allows Table layout and connections representation. SQL Editor is just that but with code snippet saving and reusing, visual SQL code help and cues. Workbench also has reverse engineering of existing databases, change management and database documentation.
libreoffice LinuxMint 11 Gnome
Posts: 5
Joined: Mon May 28, 2012 5:02 pm

Re: [how to] Move from Embedded to MySQL Backend

Postby Villeroy » Thu May 31, 2012 1:10 pm

Within a few minutes you can convert your embedded HSQLDB to a stand-alone HSQLDB which is just a very fine database engine.
1) Download and extract the current version from http://hsqldb.org/ andpoint the Java class path in Tools>Options>Java to the new hsqldb.jar
 Edit: No, do NOT point the global class path to another hsqldb.jar because this will break your embedded databases. 

2) Install this macro extension to configure a hsqldb.jar for the individual database.
3) Extract the database folder from your embedded database and rename the files data, properties, script to name.data name.properties, name.script where "name." is an arbitrary name prefix.
4) Use the above mentioned macro extension for the connection and the driver assignment. It will convert your embedded database into a database frontend connected to the extracted database backend.
4) Copy queries, forms and reports from the old database over to the new one.

For multi-user access you can write one or two simple configuration files and start up hsqldb.jar in server mode. The downloaded package comes with some excellent documentation.
Last edited by Villeroy on Tue Dec 08, 2015 12:00 am, edited 3 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Posts: 23195
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [how to] Move from Embedded to MySQL Backend

Postby DACM » Thu May 31, 2012 2:36 pm

Wow. Data migration is often tedious, but that was extreme. :crazy:

Avoiding data migration is always best when possible. That's why Villeroy (above) suggested a simple 'port' from the "embedded database" container file-format to a "split database" supported by the native (built-in) HSQLDB 1.8.0 engine with Base.

chenier wrote:I use LinuxMint 11 Gnome 64, LO[MySQL has proven] MUCH faster than my database was while embedded.

That's odd considering you're using LO 3.5.x which supposedly fixed the Linux+Base+Java bug. Are you comparing apples-to-apples here (both running LO 3.5.x)?

Otherwise, a database engine running embedded eliminates some communication overhead, so embedded databases (like HSQLDB) generally run several times faster (~5x) than server-mode databases (like MySQL, or even HSQLDB in server-mode). This plays out in benchmarks as well as my un-scientific use of the timer in SQL Workbench.

So you may be encountering a lingering issue with the Linux+Base+Java bug. Or, it could be a difference in automatic/manual indexing. And it's always possible that a particular Query/View is better optimized by one, or the other, query planner. This is an area of ongoing refinement for both these engines, so there's no doubt that HSQLDB 1.8.0 (released 2005-07-05) has fallen behind in this respect. Users can also tune queries to a particular query optimizer.

chenier wrote:I was unable to move to hsqldb 2.x implementation for technical reasons...

    changed LONGVARCHAR to VARCHAR(255)
    casewhen does not exist in MySQL, converted statement to CASE WHEN... ELSE...END statements

As I suspected, your unsuccessful automatic migration/upgrade from HSQLDB 1.8.0 to HSQLDB 2.2.x may have been data-type related. HSQLDB 2.2.x would also need these manual, data-type changes.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: [how to] Move from Embedded to MySQL Backend

Postby chenier » Wed Jun 06, 2012 5:40 am

Thanks guys for your reply. I will try again to move to hsqldb 2.x when I get another chance. Sounds like a great product. I did however want to put something out there for others who, for whatever reason, found themselves migrating their back end to MySQL.
libreoffice LinuxMint 11 Gnome
Posts: 5
Joined: Mon May 28, 2012 5:02 pm

Re: [how to] Move from Embedded to MySQL Backend

Postby TobiSan » Sat Jun 09, 2012 8:21 am

Thank you very much for your guide, it will become very handy when I migrate from embeddetd to MySQL (which I plan to do). I am currently developing an order system for my small construction/services firm, and as soon as it is up and running, I will implement it on a Jelastic server, hence the choice of MySQL contra HSQLDb. (Maybe HSQLDb would be feasable as well, as Jelastic is a Java server? But then, I would loose the advantage of the in-build db click-n-run capability.)

Anyhow, I just wanted to point out that there might be another way of migrating. As I started my project I already had a "Warehouse" spreadsheet, which I could open in LO and drag and drop to a pre-configured table in Base, i.e. with the same headings and data-types. I guess this could work for an existing connection to an MySQL Db as well? I will try it later.
OpenOffice 3.4 on Windows XP
OpenOffice 3.4 on Ubuntu 12.xx
Posts: 3
Joined: Thu May 31, 2012 12:21 am

Re: [how to] Move from Embedded to MySQL Backend

Postby tx42 » Thu Feb 13, 2014 1:28 am

I've looked around and the most straightforward option to a stand-alone (split file) database is Villeroy's suggestion. It should be noted that hsqldb is top-notch and easily in league with (or perhaps superior to) mySQL and postgreSQL.

To make the transition go smoothly, Villeroy's instructions should be supplemented with the following:

  • Read this page carefully before doing anything: https://wiki.openoffice.org/wiki/FAQ_%28Base%29#HSQLDB
  • To "extract" the database files, note that the .odb file is a zip file. E.g. in UNIX-like environments:

    Code: Select all   Expand viewCollapse view
    unzip MyDatabase.odb

    The files mentioned by Villeroy are in the "database" directory extracted by unzipping.
  • It is advisable to use the hsqldb provided by the OO or LibreOffice used to create the original database to reduce the chance for problems. This is discussed the the wiki page linked above.
  • To point to the OO/LibreOffice hsqldb in OS X, you will probably need to make a link to the file because the file dialog can't look inside ".app" directories. The best way to do this is in the Terminal application under /Applications/Utilities:

    Code: Select all   Expand viewCollapse view
    ln -s /Applications/LibreOffice.app/Contents/MacOS/classes/ ~/LibreOffice-Classes

    This makes the classes directory navigable (at YourHomeFolder/LibreOffice-Classes). OO/LibreOffice will not use the link to point to the driver (i.e. it points directly to the folder in LibreOffice.app), so you can move the link to the Trash when done.
  • Copying forms won't work by dragging for some reason. Use keyboard cut-and-paste (e.g. ctrl-C/ctrl-V in UNIX-like/Windows, cmd-C/cmd-V in Mac).
NeoOffice 3.2 on OS X 10.6
Posts: 3
Joined: Thu Oct 04, 2012 10:56 pm

Return to Base

Who is online

Users browsing this forum: No registered users and 10 guests