Tuesday, May 29, 2012

LibreOffice Base Embedded hsqldb Conversion to MySQL backend and Base Front end

LibreOffice Database with mySQL

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
  • automysqlbackup  -- OK, this one makes sense based on my recent lost data episode... however found out later that I got the same functionality from mysql-server
  • mysql-query-browser --sounds good, I can do a little raw sql queries
  • libreoffice-mysql-connector  --hmm, I thought this was already in place, but this lists my version of libreoffice 3.5.3, I but will wait till after testing before deciding to install....... OK In the end needed to install this to connect directly to MySQL without ODBC or JDBC wrapper.

Initial Steps - Connecting to MySQL on my computer
  • Opened mySQL Administrator and tried to enter connection
    Of course this did NOT work right off the bat... would have been too easy for unprofessional database program.
    • hostname = localhost
    • port = default of 3306
    • username = myuser
    • password = mine
  • Added my user to the group mysql
  • ran the following commands
    • sudo /etc/init.d/mysql restart
      this command returns: mysql staart/running, process 9312
    • sudo mysql -u root -p
      this command returns: Access denied for user 'root@'localhost'
  • Retried to open in mySQL Administrator
    returns Access denied for user
  • made a password for root - just to test, will take away root access if not needed
    sudo passwd root  -- this did not help, so disabled -- sudo passwd -l root
  • Still get the same message when trying to connect to SQL from administrator console or terminal
  • http://dev.mysql.com/doc/refman/5.5/en/postinstallation.html
    read this
  • mySQL  HAS IT'S OWN ROOT ACCOUNT.... now I get it
    • ON INSTALLATION OF MYSQL, DEFAULT MYSQL ROOT ACCOUNT PASSWORD IS [BLANK]
  • Open mySQL Administrator and enter the following:
    • hostname = localhost
    • port = 3306
    • username = root
    • password = [blank]
      SUCCESS
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  - Attempt failed due to current bug  (see Below)
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: http://user.services.openoffice.org/en/forum/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/wiki/Database/Drivers/MySQL_Native/Known_Problems#Copying_tables_does_not_work_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. http://user.services.openoffice.org/en/forum/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 - Another Approach (I did not use)
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 - New Approach Export then Import SQL dump files
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
    • Tools/SQL
    • 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)
      • Worked:  
        • 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
    • Success!
  • Actual SQL statement modifications I made for CREATE TABLE statements
    • remove CACHED from between CREATE TABLE
    • changed LONGVARCHAR to VARCHAR(255)
    • changed GENERATED BY DEFAULT AS IDENTITY (START WITH 0) to AUTO_INCREMENT
  • 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
Loading Data: INSERT INTO statements  - first attempts
  • Create tables works fine... it is inserting data into them that may prove to be a challenge if we use this method as in my test, each insert into statement must be modified, to remove the quotes around the table name.  Also each set of data must be coma separated without "insert into" statement between the sets. This could work if you only had a small amount of data, but for my principle tables with transaction data, would be untenable.  SEE FIX ABOVE to enable double quotes around table and column names in MySQL.
    • Did not work:
      • INSERT INTO "CategoryDef" VALUES('Capital','Capitalized expenditures')
      • INSERT INTO "CategoryDef" VALUES('Deposit','Deposit that is not income')
      • INSERT INTO "CategoryDef" VALUES('Expense','Cash Expense items')
      • INSERT INTO "CategoryDef" VALUES('Income','Cash Income Items')
      • INSERT INTO "CategoryDef" VALUES('Tax','For tax reporting only, typically entered as expense in another form')
    • Worked:
      • INSERT INTO CategoryDef VALUES('Capital','Capitalized expenditures'), ('Deposit','Deposit that is not income'), ('Expense','Cash Expense items'), ('Income','Cash Income Items'), ('Tax','For tax reporting only, typically entered as expense in another form'), ('Utility','Specialized expense for utilities only')
    • After enabling ANSI quotes (see above), The quotes around column names was not a problem, only multiple lines
      • Copy a single INSERT INTO statement for the newly created table from the .sql dump file
      • Success!  works without error
    • Multiple statements - to insert many rows at once
      • multiple statements produce error when paste into query window
      • Open new script Tab via file/new scritp tab
        • past multiple statements here
        • when execute, states done, but no records appear in table
        • NEED ; AFTER EACH STATEMENT even the last one
        • Success after putting semi-colon after each statement
        • OK, so importing 10,000 records like this if you need to add semi-colons after statements will not work well.  Maybe another product does not require the semi-colons or maybe there is a setting somewhere.....
      • ONE SOLUTION: So, I can copy sections of the INSERT INTO statements (say one table at a time) into LibreOffice Writer, then search for ")" and replace with ");"  This only works because I know I have no end parenthesis within my data.  Otherwise I would need to be more clever.
DATA IMPORT -Actual step by step
  • 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.
    • Steps
      • 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.
        • Success!
  • Copy and Paste into Existing Table -- Success -- Second Easiest ApproachThanks 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)
      • Success!
  • MySQL Query Browser: Modified Data Via Find and Replace --Works
    http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=54143&p=236529#p236529
    • 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.
    • Success!
  • MySQL Command Line: Load Data Local Infile  --Stumped
  • http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html
    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 http://forge.mysql.com/wiki/MySQL_User_Guide_Part_VI_Tutorials
    • 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

  • Queries
  • Forms
    • 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

  • Reports

Moving Macros over & 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...




References:
Backup Utilities...

Sunday, May 27, 2012

Unsuccessful: Convirt LibreOffice Database to HSQLDB 2.x back end from embedded

After my Open office .odb database crashed and I lost about a day of work, I decided to convert my database from embedded to the more robust front end - back end configuration.  http://user.services.openoffice.org/en/forum/viewtopic.php?p=162653#p162653
I found help sources like the above and a few others, but it seemed not all the information was put together neatly for Linux Mint / Ubuntu /Debian users, so I thought I would document my own conversion attempt.

!!  FINAL WORD FROM ME  !!  Moving onto another approach for now.  Got stuck on Java Heap Space errors.     In the End:  I changed to MySQL backend with LibreOffice Base front end.  I've been running solid for over a year with this new configuration, and I love it.... Runs well and MySQL is forgiving of me turning my computer on and off daily without taking special steps.  Speed Now is much FASTER!  Here are my notes:  http://techtips-tom.blogspot.com/2012/05/libreoffice-base-embedded-hsqldb.html

I will either use database as embedded until OpenOffice/LibreOffice implements full support of HSQLDB 2.x or I will Install MySQL server and use it as the back end.... look for upcoming posts :)

I use Linux Mint 11, 64bit Gnome with LibreOffice 3.5.3.2 Build 2

First step is to setup the database program to allow multi-mode
http://www.oooforum.org/forum/viewtopic.phtml?p=396523#396523
Being on Linux Mint 11, I need to follow the 'Manual "multi-mode" database setup:' section of the above reference.  Here are my steps precisely
  • Make a couple copies of my database and place them in other directories and back up devices for safe keeping away from harm!
  • Open my database and check Tools/Options/Java
    • Use a Java runtime environ IS already checked
    • Sun Microsystems Inc. 1.6.0_26 is selected
    • I suppose this is recent enough, though I have a later version downloading in case I need to install it.
  • Could not find hsqldb.jar on my system anywhere, searched many forums and help documents with no good reference on what to do other than install HSQL 2.x.  In searching for help, I found it very wanting, so I stumbled along.... and happened to get it right the first time:   I went to http://hsqldb.org/ and downloaded 2.2.8 file as a .zip file.
    • Downloaded hsqldb-2.2.8.zip
    • unzip this file
    • transfer the hsqldb directory from inside this directory into my Home folder /home/UserName/hsql
    • renamed it with . in front so it is hidden directory... /home/UserName/.hsqldb
    • the hsqldb.jar file is located in the /lib subdirectory and the help document states it was designed to work with java 1.6, so the java runtime I have should work fine.
  • Back to Base....
    • Open Base
    • Open tools/options/java and push the Class Path button
    • Add Archive... and select /home/UserName/.hsqldb/lib/hsqldb.jar
  • Creating a blank two part database  (in this example I will use 'MyAccounts' as the database name
    • create an empty new directory for your database like: /home/UserName/Accounting/MyAccounts
    • Select Connect to an existing database
    • Select JDBC and hit next
    • Datasource URL: jdbc: hsqldb:file:/home/UserName/Accounting/MyAccounts/MyAccounts;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
      • there are no spaces in the above
      • the first MyAccounts is the residing directory name
      • the second MyAccounts is for the created file name
      • the other parameters I don't really understand yet, though one can guess at some obvious interpretation
    • JDBC driver class = org.hsqldb.jdbcDriver
      • no idea why it is that, but it works
    • UserName = SA
      • unclear if that was needed or just preference of the original blog poster
    • Keep both defaults of register the database and save it
    • this created a new database, but asked me to save it, so I put in the same name, MyAccounts in the MyAccounts directory and it seemed to work.
    • So what I have now is a totally blank database named MyAccounts in the /home/UserName/Accounting/MyAccounts directory.  Besides the .odb file there are a few other files which we will replace in the next step.
Next convirt my existing database into the new file:  
To one of your copies of your original database:
  • Rename the dabase to [itsName.zip] .... that is change .odb to .zip
  • Open the zip with a zip tool and extract the contents of the 'database' folder into your MyAccounts folder.
  • in that folder, delete the existing MyAccounts.script, MyAccounts.properties, MyAccounts.data files if they exist
  • Do NOT delete the MyAccounts.odb file   :)
  • Rename the extracted files (script, properties, data) to MyAccounts.script, MyAccounts.properties and MyAccounts.data
  • Now when I open my database, MyAccounts.db, I see the tables from my old database, but none of the queries, forms or reports
Now Copy Queries, Forms and Reports
The above items need to be copied from the old database.
  • For Forms and Reports, it is as easy as opening your old database at the same time as your new one and dragging each Form and Report from the old database into the new Database front end.
  • For Queries, I ran into a problem that I got an error whenever I tried to drag a query over:
    • Error given:
      • The connection to the data source "NAME of database" could not be established.
      • file input/output error
      • java.lang.NullPointerException.properties /path/of/database/
    • This error is apparently caused by the later, incompatible version of hsqldb.jar being used.  I was able to correctly open older databases when I removed the reference to the new hsqldb.jar file in the LibreOffice Tools/Options/java/Class Path  menu area.  So I could then open the older database file and access tables and queries, but not the new separated one (of course).
    • The solution was to keep the reference to the new hsqldb.jar, then in the old database, Right click each Query, select Copy, then acknowledge the error message, then in the new database, right click in the Query area and select paste.  Simple work around once discovered.
Persistent Java Heap Space Error
OK, so now when I open a large table or query I get an out of memory Java Heap space error.  So far unable to work around even with parameters passed to java -Xms1024m -Xmx2048m.  I tried many things here, but in the end was still stuck.  I then got thinking, maybe it is better to wait until hsqldb 2.x is officially supported in Base, otherwise I may struggle with this with every updated (assuming I could work through it).  I could end up with problems bigger than I currently have.

FINAL WORD FROM ME:  Moving onto another approach for now.  Got stuck on Java Heap Space errors.

Copying Macros  ... I did this, but no bother sharing detailed steps, here is other help copied from someone else's online post..... Macros require export/import within the Base IDE (Integrated Development Environment) "organize" functions (more on this as needed). 
NOTE: table column Field Properties workaround: Right-click your table(s) to Edit or Copy them. The Edit function is now limited to Add/Delete table columns/rows. The Copy function is now used to adjust existing table column/row properties. Simply Copy the table to a newly named table; adjust column properties as desired using the popup copy-table Wizard; after confirming the results simply delete the original table; then repeat the table Copy to reuse/reset the table to its original name; reset any global table relationships. Of course, those proficient with SQL can simply bypass the GUI workaround and perform these adjustments directly with ALTER TABLE ALTER COLUMN and/or ALTER TABLE RENAME TO commands. In any case, it's a good practice to issue the CHECKPOINT DEFRAG command using 'Tools>SQL...' after adjusting tables in this manner. And don't forget to use View > Refresh Tables to see your changes when working directly with SQL to alter table structures.

Autonumber malfunction: http://www.oooforum.org/forum/viewtopic.phtml?p=398884#398884
http://www.oooforum.org/forum/viewtopic.phtml?p=396523#396523

Other helpful references:


Memory issues:
https://confluence.atlassian.com/display/JIRA/Increasing+JIRA+Memory#IncreasingJIRAMemory-guidance
http://www.techpaste.com/2012/04/java-lang-outofmemoryerror-class-allocation-error-weblogic-jrockit-jdk-r28/
http://www.oooforum.org/forum/viewtopic.phtml?p=391946#391946

Friday, May 18, 2012

Ubuntu 12.04 KVM server

To get my main server up to date, decided to push everything to the backup server and start fresh with 12.04 KVM server which I will manage remotely via virt-manager, libvirt, SSH, etc.

Hardware

HP DL365 with Two dual-core AMD Opteron 2x 1.8 GHz processors, 8 Gig ram, Raid hard drives

Base Install

Basically, get the core OS up from newly burned Ubuntu 12.04 Server x64 disk

  • Partitioning: Guided - use entire disk and set up LVM
  • Security updates automatically
  • Select software to install:
    • OpenSSH server
    • Virtual Machine host
  • Reboot
  • logon remotely from terminal on main computer
    • ssh user@ipaddressofserver
  • apply any updates
    • sudo apt-get update
    • sudo apt-get upgrade
  • peg DHCP address on home router so I always have the same address then do the following
    • sudo /etc/init.d/networking restart
Virsh and Virt-manager

Getting Virsh and Virt-manager working
  • Test at this point to see if I can list virtual machines
  • Check for and install missing packages
    • sudo apt-get install ubuntu-vm-builder
    • virsh -c qemu:///system list
      • still error
    • Performed the following to fix group permissions, then signed out and back in
      • sudo adduser [user] kvm (not sure if needed)
      • sudo adduser [user] libvirtd
    • virsh -c qemu:///system list
      • this shows (an empty) list of machines and their state, so everything appears to be in place
    • Install virt-manager locally on the server to allow ssh windowing into it.  This would not be needed for simply accessing from virt-manager installed on another machine.
      • sudo apt-get install virt-manager
Bridge Networking
  • Use the following to show bridges
    • brctl show
      • shows virbr0, but this is not the bridge I need to be connected to eth0
  • setup proper bridged network as follows:
    • sudo nano /etc/network/interfaces
    • change the following lines
      • auto eth0
      • iface eth0 inet dhcp
        • to
      • auto eth0
      • iface eth0 inet manual
    • add the following lines to the file
      • auto br0
      • iface br0 inet dhcp
        • bridge_ports eth0
        • bridge_fd 0
        • bridge_stp off
        • bridge_maxwait 0
  • Save file then test as follows
    • sudo /etc/init.d/networking restart
    • ifconfig
      • shows proper bridge setup as expected

Second NIC
The second physical NIC is not showing up when I run ifconfig and I am unable to configure it.   That is I see eth0, but no eth1.  (answer, was just command it up via: sudo ifconfig eth1 up)

  • run command: sudo lshw
    • it shows first NIC as follows:
 *-network
                description: Ethernet interface
                product: NetXtreme II BCM5708 Gigabit Ethernet
                vendor: Broadcom Corporation
                physical id: 0
                bus info: pci@0000:0a:00.0
                logical name: eth0
                version: 12
                serial: 00:1b:78:ce:e6:88
                size: 100Mbit/s
                capacity: 1Gbit/s
                width: 64 bits
                clock: 66MHz
                capabilities: pcix pm vpd msi bus_master cap_list rom ethernet physical tp 10bt 10bt-fd 100bt 100bt-fd 1000bt-fd autonegotiation
                configuration: autonegotiation=on broadcast=yes driver=bnx2 driverversion=2.1.11 duplex=full firmware=bc 1.9.6 latency=64 link=yes mingnt=64 multicast=yes port=twisted pair speed=100Mbit/s
                resources: irq:49 memory:f8000000-f9ffffff memory:fc000000-fc000
    • Second NIC as:
*-network DISABLED
                description: Ethernet interface
                product: NetXtreme II BCM5708 Gigabit Ethernet
                vendor: Broadcom Corporation
                physical id: 0
                bus info: pci@0000:0c:00.0
                logical name: eth1
                version: 12
                serial: 00:1b:78:ce:e6:8a
                capacity: 1Gbit/s
                width: 64 bits
                clock: 66MHz
                capabilities: pcix pm vpd msi bus_master cap_list rom ethernet physical tp 10bt 10bt-fd 100bt 100bt-fd 1000bt-fd autonegotiation
                configuration: autonegotiation=on broadcast=yes driver=bnx2 driverversion=2.1.11 duplex=half firmware=bc 1.9.6 latency=64 link=no mingnt=64 multicast=yes port=twisted pair
                resources: irq:28 memory:fa000000-fbffffff memory:fc100000-fc1007ff
  • the command: sudo lspci     gives the following lines
0a:00.0 Ethernet controller: Broadcom Corporation NetXtreme II BCM5708 Gigabit Ethernet (rev 12)
0b:00.0 PCI bridge: Broadcom EPB PCI-Express to PCI-X Bridge (rev c3)
0c:00.0 Ethernet controller: Broadcom Corporation NetXtreme II BCM5708 Gigabit Ethernet (rev 12)
0d:00.0 PCI bridge: Broadcom EPB PCI-Express to PCI-X Bridge (rev b4)
0e:04.0 PCI bridge: Broadcom BCM5785 [HT1000] PCI/PCI-X Bridge (rev b2)

  • sudo ifconfig eth1 up   ... to try and start eth1
    • success, now eth1 is shown in list of interfaces with ifconfig with it's MAC address, no networking yet, so edit interfaces as follows:
  • Network need for DRBL/Clonezilla server:  need br1 to connect physical nic 2 (eth1) to internal bridge and allow DRBL/Clonezilla server to act as DHCP server.   I believe this requires me to setup eth1 as DHCP client or pegged IP address.... I will first try to setup as DHCP client and see if br1 can get IP address from virtual computer (versus external DHCP server).  If this works it would be option A below, otherwise I would need something like option B, but with a different ip address on the same subnet and no DHCP server stuff. 
  • OPTION A: Setup eth1 and br1 as DHCP client
    , so I will use the br1 to connect DRBL DHCP server to external clients on seperate physical switch connected to nic 2
    • Quick test is to setup eth1 and br1 just as I have eth0 and br0
    • sudo nano /etc/network/interfaces  -- add the following lines
      • # secondary network interface (nic 2)
      • auto eth1
      • iface eth1 inet manual

      • #Bridged network for eth1
      • auto br1
      • iface br1 inet dhcp
        • bridge_ports eth1
        • bridge_fd 0
        • bridge_stp off
        • bridge_maxwait 0
    • sudo /etc/init.d/networking restart  -- to restart interfaces
    • sudo ifconfig  -- to view interfaces
      • looks correct with br1 getting ip address from my DHCP server and eth1 being linked with the same MAC address
  • OPTION B: Setup eth 1 and br1 as DHCP server    -- not doing for now since I now recall DRBL/Clonezilla prefers to have DHCP server setup directly on second interface, so I do not need DHCP service from KVM server
    • I want to have nic 2 of the server be on a natted subnet for purposes such as testing and clonezilla reformatting of computers, so the setup will look a bit different from the primary interface.....
    • sudo nano /etc/network/interfaces     -- modify to add eth1 as second bridged interface so I can us it from virtual machines and connect a switched physical subnet as well -- add the lines:
      • # secondary network interface (nic 2)
      • auto eth1
      • iface eth1 inet manual

      • #Bridged network for eth1
      • auto br1
      • iface br1 inet static
        • address 172.30.50.1
        • network 172.30.50.0
        • netmask 255.255.255.0
        • broadcast 172.30.50.1
        • gateway 172.30.50.1
        • bridge_ports eth1
        • bridge_fd 9
        • bridge_hello 2
        • bridge_maxage 12
        • bridge_stp off
    • sudo /etc/init.d/networking/restart  -- 
      • gives error: Failed to bring up br1
      • RTNETLINK aswers: File exists
    • tried to fix as follows:
      • sudo ifdown eth1
      • sudo /etc/init.d/networking restart (still gives same error)
      • sudo ifup eth1 (states already up)
    • sudo ifconfig  
      • shows that things look as they should:
      • br1 and eth1 have same MAC address
      • br1 has address given, etc
    • OK, so now I need to install DHCP server and define it
      •  (install dhcp3-server)
      • configure /etc/default/dhcp3-server)
Adding Storage 
FreeNas Server setup - Setup NFS shares on freenas server to be shared with KVM servers

  • log into FreeNas web interface using the admin password
  • Go to Services -- NFS
    • Settings
      • set number of servers (depending on maximum concurrent clients)
    • Shares - add new  -- this director for isos of install disks
      • Path = /mnt/store/isosserver (wherever you have the files you want to share)
      • Map all users to root = whatever you need
      • Authorized network = portion of my internal network I want to have access
      • comment = isoserver
      • all dirs = unchecked
      • Read only = what you need
      • Quiet = unchecked
    • Shares - add new   --this directory for existing virtual machine images 
      • Path = /mnt/store/vms
      • comment = virtual machines
      • other settings same as above
    • click on apply changes button !

Virt-manager Settings - adding shared drive

  • Right click a KVM host and select details
  • Select the Storage tab
  • click the plus icon in lower left of window to add a new pool
    • Name = shared-virtual-machines
    • Type = netfs: Network Exported Directory
    • Target Path = keep default = /var/lib/libvirt/images/shared-virtual-machines
    • Format = auto
    • Host Name: [IP address of server]
    • Source Path = /mnt/store/isosserver
  • repeat for /mnt/store/vms


Move VMs from other Servers
I have some virtual machines on older serves I want to move here.  Following are steps I took:







.........


Testing Out

Connecting from Another Machine


  • Do I need to do this???????  I did for 11.04 server
    • on Server
      • log into server
      • sudo apt-get install nfs-common   =installs nfs-common which was not installed on my default server setup
Good References:


Wednesday, May 16, 2012

Zentyal Server for Home

Outlines my install of Zentyal for my Home Internet and phone server
 - plus documentation of various test configs


Zentyal Gateway setup: As DHCP server, typical install
  • Start with a simple Gateway setup with the following modules
    • Network, Firewall, DHCP, DNS, Backup, Events, Logs, Monioring
  • Follow more or less this guide: http://trac.zentyal.org/wiki/Documentation/Community/HowTo/GatewaySetup
  •  Enable the above listed modules
  • Network -- Interfaces
    • set eth0 to be external interface, in my case I will be using behind the router (for now) so set method to DHCP and selected External (WAN).
    • set internal interface which to Static, in my case I am using eth4 and 172.30.40.1 with netmask of 255.255.255.0
    • I will use the other ethernet ports at a later point for further testing.
  • Network -- Gateways
    • In my casey this should be setup automatically based on DHCP information received as my External interface is DHCP.
    • In my setup it shows my router's IP address through eth0, enabled, the default, with a weight of 1.  It is the only Gateway listed.
    • This can be manually adjusted if neededor desired.  Also, infocan be added for Proxy server configuration and traffic balance and WAN failover settings for multiple External connection environments.
  • Software Management -- Zentyal Components.  
    • Ensure DHCP and DNS services are enabled. 
  • Network -- DNS
    • DNS service acts as a caching server, which improves DNS lookup times.
    • The first listing should be 127.0.0.1 which references Zentyal's DNS service.
    • Additional DNS servers can be added after this entry as needed.
    • My setup works correctly only when I add my router (WAN DHCP server) listed as a second DNS server. 
  • DHCP (under Infrastructure)
    • In my case I am setting up eth4
      • default gateway: Zentyal
      • Search domain: none
      • Primary nameserver: Zentyal DNS (to use Zentyal as a cache DNS server)
      • Secondary nameserver: [blank]
      • NTP server: none
      • WINS server: none
    • setup a range of IP addresses available to be used by the DHCP server, name the range whatever you want
      • I set a range named "default"
  • HTTP Proxy -- General
    • HTTP Proxy allows the following:
      • Web Caching, allowing repeat web content to be reused rather than using additional bandwidth, for example if 10 people go to Gmail or E-week all the non users specific information will be loaded from local storage rather than from a server on the Internet, thus reducing bandwidth. 
      • Allows filtering and banning of certain content.
    • Setup as follows
      • Transparent proxy checked. This allows for Zentyal to be used as a proxy server without changing settings on local computers.  This requires that Internet traffic comes in the External interface on the Zentyal server and out via one or more internal interface(s).
      • Ad Blocking: undecided whether to check or not, will not for now, then check it later to see if I have problems with anything.
      • Port: I kept the default of 3128, since I checked transparent proxy, all the Internet traffic requested on internal  network automatically gets re-directed from port 80 to port 3128 of the Zentyal server.   
      • Added an appropriate cache file size. The larger the size, the more information will be saved on the server.  If you prefer to always be sure you have the latest info, set this low, if you want to conserve bandwidth, set it high.  I set it at 2 Gig for now.
      • Default Policy: This is the filter policy which is applied if incoming traffic doesn't meet a specific Object or Group policy.  I set this to "filter", but in a high security environment, you might prefer "always deny".  If you have users and groups setup you can use an "authorize & ____" option.
      • Cache exemptions: these are for adding web sites that you never want to cache, like up to the minute news web sites.  I added netflix and facebook here.
    • Module Status
      • Enable Users and Groups
      • Enable HTTP Proxy
  • HTTP Proxy --  Filter Profiles
    • You can add various filter profiles here, each one with its own rules
    • default filter group is used if the default policy set above is "filter" and there are no specific policies invoked via Object or Group policy sections
    • Object policy and Group policy is where you setup rules for which filter policy to use for specific groups of people or objects (i.e. computers via IP addressing schema).
    • Tested setup and use of default filter as follows
      • Click on Configure Default filter button
      • Add Domain and URL rule of always deny yahoo.com
      • Went to another web browser, tried accessing yahoo.com and was served a Zentyal - Access Denied page
      • WORKS
    • Now, modify default filter profile how you like, then create new filter profiles as desired and finally create groups of users or objects and tie them to your desired filter profile for that group.
      • As an example, for your young child's computer you can block social web sites by creating a "young" filter policy, then create a "young" object (or user) group and adding that computer (or user) to that group and tying the group to the
      • You could then let everyone else use the "default" filter and block hate, scam and porn web sites, or whatever you wish.
  • Antivirus:  
    • Enable via Module Status
      • This will check downloads for viruses and block infected content
  • Firewall - I will configure this at a later point in my testing

Adding Bridged Networking
In order to roll out Zentyal to the rest of my network, I decided to try out Bridged networking, thus keeping my main router as DHCP server

  • Network -- Interfaces
    • eth3 changed 
      • Method = Bridged
      • Bridge = new
    • save so br1 shows up
    • br1 changed
      • Method = DHCP
    • eth3 changed
      • Bridge = br1
      • External = unchecked
    • eth0 changed
      • Method = Bridged
      • External = checked
      • Bridge = br1
    • change and save changes
  • plugged in office part of my house to eth3 and tested with the following observations
    • DHCP service from router on External interface of Zentyal worked well
    • filtering worked as expected
    • Network -- Gateways now shows br1 Interface
    • Computers connencted via eth3 do not show up on dashboard
    • communication between hosts on lan side of Zentyal worked without problems
    • communicating between Zentyal's lan and wan of Zentyal (even though computers on same sub-net as far as router is concerned) required firewall rules as follows: [I think this is a fairly unusual and poor configuration, only existing because of intermediate testing, but I am documenting required firewall rules anyway.]
      • Firewall rules to allow a certain port from wan to lan side computer
        • firewall -- packet filter -- filtering from external to internal  (this rule was not needed and did not work alone, port forward rule was better approach)
          • decision = accpet
          • source = any
          • destination = any
          • service = Minecraft (added service for ports 25565 & 25566)
          • description = Minecraft allow
        • firewall -- port forward
          • interface = br1
          • original destination = [IP of Minecraft Server]
          • Protocol = tcp/ip
          • original destination port = port range 25565 to 25566
          • source = any
          • destination ip = [IP of Minecraft server]
          • Port = same
          • replace source address = not checked
          • log = not checked
          • description = Minecraft
  • Go live - bridged and DHCP servered mixed environment
    • all house now on Zentyal lan 
    • All wired connections via bridged networking to eth3
    • Wireless via fixed/DHCP server on eth4
      • this keeps my wireless guests on separate sub-net with default firewall stops
    • Ran into some problems - after a couple hours some of the computers could not reach the DHCP server.  no time to fix now, so reverted back to only wireless internal to Zentyal.  Will problem solve later
    • Could be GATEWAY ISSUE  -- CONCLUSION: was a problem but did not solve this particular problem
      • while looking through setup, I noticed that the Gateway settings were changed in that my router IP address was no longer listed as the default gateway (no checkbox).  Verified the following settings:
        • Enabled = checked
        • Name = name give by zentyal
        • interface = br1 (in my case)
        • Weight = 1
        • default =  checked
      • not sure when this got un-checked in the first place, probably with some setup problem
      • Fixed, but problem still exists of bridged ports not accessing DHCP server/router
    • Could be a WAN SETTING issue -- CONCLUSION: not problem, was correct
      • Interface for eth0 may need to be taken off of WAN setting (box un-checked) for DHCP and DNS services to flow through properly. (Though perhaps we could create firewall rules to allow such traffic.)
      • Some documentation did not show WAN setting checked for connection to router, so tried to change and run, but as I suspected, this did not work properly:  After de-selecting WAN for eth0, I failed to get access to the Internet on my wireless (using eth4 with DHCP from Zentyal)
    • Could be FIREWALL ISSUE.  Add a rule as follows for next time I switch over to live:
      • CONCLUSION - this works, particularly the DHCP return service and rule was needed
      • Firewall -- Packet Filter -- External networks to Internal Networks
        • Add new
          • Decision = Accept
          • Source = any
          • Destination = any
          • Service = dhcp
          • Description = Allow dhcp
      • TEST - still not connecting to DHCP service, maybe I need port forwarding rule.... or DHCP return port rule as follows
      • DHCP service on port 67 was already setup, added service definition for port 68
      • Network -- Services    Add new service definition
        • Add new
          • Service name = dhcp return
          • Description = dhcp request return
        • Configuration - Add new
          • Protocol = TCP/UDP
          • Source port = Any
          • Destination port = Single port = 68
      • Firewall -- Packet Filter - External to Internal     New firewall rule
        • Add new
          • Decision = Accept
          • Source = any
          • Destination = any
          • Service = dhcp return
          • Description = Allow dhcp return
      • So far network working correctly!
Enable Other Modules - sometimes need to reboot Zentyal for proper functionality after enabling modules

  • Module Status : Enable the following modules
    • Enabled the modules: IDS, VPN, Traffic Shaping, Users & Groups, VoIP, Bandwidth Monitor, FTP, Jabber, File Sharing & Printer Sharing
    • Backup Configuration locally and remote via System -- Import/Export Configuration
  • Software Management -- Zentyal Components
    • Select Virtualization Manager to install
    • Apply changes and Save
  • Module Status : Enable Virtual Machines and save changes

VoIP Configuration
It seems to me that Zentyal is either in transition or has two very different methods of registering phones.  Prior version of Zentyal had all phone configuration through the User and Groups settings, now it seems the best and most complete registration of telephones is done via VoIP -- Phones settings.   This new interface allows you to setup phones independent of setting up users.  That is you don't need to create dummy users for phones for which you don't want to associate a real user.

Problem I am having is in setting up phones which are provisioned via the Users and Groups section.  I set everything up according to documentation, then I can make outgoing calls, but when I try to call that exentsion I get the message: "The person at extension .... is unavailable".  These extensions also no longer register in the VoIP users section of the Dashboard.  Below I outline the setting I have for these.
  • VoIP -- General (mostly defaults)
    • General Configuration
      • Enable demo extensions  = checked
        • These are different than what is listed in the help file
          • *4 for Music on Hold
          • *6 for Echo test
      • Enable outgoing calls = not checked (for now)
      • VoIP domains = zentyal (default for now)
    • SIP provider
      • leave all blank for now
    • NAT configuration
      • none in my config, so leave default value of no
    • Local Networks
      • none for now.  This is important to identify if non-natted (i.e. routed) networks are available and will be use for phones
  • VoIP -- Phones     WORKING
    Phones seem to be allotted extension only in the range 4000 to 7999,  Prior ranges of 1000 to 3999 do not seem to work at this time and seem to be designated for use in the Users and Groups VoIP setup, which appears broken.   Following is how I correctly setup VoIP phones
    • New Phone
      • Enabled = checked
      • Extension = 4001 [any # between 4000 and 7999]
      • Voicemail = 5001
      • Email notified = blank for now ---- need to test
      • Description = [left blank]
    • Tried setting up extension in the 1000 to 1999 range and would get a busy signal when trying to call the phone.  I did this as part of a test trying to get Users and Groups defined phone configurations working.  See my notes below.  The result that calls can be made from the phone, but not to the phone is similar, but the result of another person trying to call this phone varies depending on where it was defined.
  • Users and Groups   NOT WORKING
    This is my attempt to get Users for VoIP phones working.  So far this is unsuccessful.
    I have set up the following Users to try and get VoIP working for users
    • Phone 2
      • first name = phone
      • last name = 1012
      • comment = [blank]
      • user quota = 100 [default]
      • password = password I used in the phone setup
      • User groups = voip [a group I setup for voip users]
      • Asterisk user account = enabled
      • Extension = 1012
      • remaining settings are not important
    • phone1 
      • same as above, but with extension = 1001 as well as last name = 1001
    • Users and Groups -- Groups: VoIP
      • Asterisk Group queue = Enabled
      • Exetension = 9001
      • Sharing directory Name = voip
    • Results 
      • This user can make calls to other phones and test extensions, but can not receive calls .... Attempts result in caller getting the message "the person at extension .... is unavailable".
      • Tried setting up replicated settings in VoIP -- Phones: with same extension and password.  Got the same results, no change.
  • VoIP AND Firewall Issues  (no longer needed after abandoned pass-through config)
    For setu643p in pass through configuration, need to setup Firewall rule to allow VoIP from external network to internal network if you want phones with IP addresses assigned by your router external to Zentyal to be able to communicate with Zentyal server and phones on Internal networks.  Warning: firewall rule defined below may be too loose for your safety needs.
    • Firewall -- Packet Filter -- from external to internal networks
      • Decision = Accept
      • Source = Any
      • Destination = Any
      • Service = VoIP
      • Description = "VoIP from external"
    • Firewall -- Packet Filter -- from external network to Zentyal (may also need)
      • Decision = Accept
      • Source = Any
      • Service = VoIP
    • ALSO ... Added rule on external router to route traffic headed to Internal Zentyal network (172.30.40.1) to Zentyal's External IP address
  • VoIP -- Meetings
    Meetings work as group extensions......

  • VoIP tricks
    • Demo extensions
      • *6 is echo test
      • *4 is Music on Hold
  • Music on Hold setup
  • Voicemail setup
Notes on phones:
  • Web setup at http://[ipaddress]
  • Default Grandstream web access password: admin 
  • Default Grandstream phone access password: 123
    • GXP285 setup
      • Basic Settings
        • dynamically assigned DHCP
      • Advanced settings
        • nothing unusual
      • Account
        • Account Name:
        • SIP Server: 172.30.40.1:5060
        • Outbound Proxy: 172.30.40.1:5060
        • SIP User ID: 4001
        • Authenticate ID: 4001
        • Authenticate Password: 4001123 = [account#]123   (for testing)
        • Name: 4001
    • GXP201 setup
      • same except account use 4002 for name, and ID fields


Minecraft Port Configuration 
I want to open up the Minecraft game port to be allowed.

The following configuration is for Minecraft server located within Zentyal's Internal network, but given IP address from router external to Zentyal (pass through configuration).  A port forward was given at the router level to accept this traffic to the router's external address and forward it to the IP Address of Minecraft Server
  • Network -- Services
    • Add New
      • Service name = "Minecraft"
      • Description = 25565
      • Configuration
        • Protocol = TCP/UDP
        • Source port = port range = from 25565 to 25566  (any should also work)
        • Destination port = port range = from 25565 to 25566
  • Pass Through Configuration
    • Firewall -- Port Forwarding
      • Add new
        • Interface = br1
        • Original destination = IP Address of Minecraft Server
        • Protol = TCP/UDP
        • Original destination port = single port = 25565
        • Source = Any
        • Destination IP = IP Address of Minecraft Server
        • Port = Same
        • Replace source address = NOT checked
    • Firewall -- Packet Filter -- from external to internal networks
      • Add new
        • Decision = accpet
        • Source = any
        • Destination = any
        • Service = Minecraft (I configured above)
        • Description = "Allow Minecraft"
  • New Configuration with all network internal to Zentyal
    • Changes to my Router
      • remove Route to my internal IP addresses
      • Add Port forward from enternal my router to Zentyal 
        • using port 25565 for traffic to primary Minecraft server
        • using port 25566 for traffic to secondary Minecraft server
        • Accept rule for each friend we allow to our Minecraft servers.. individually directed to Zentyal IP address at one of the above ports depending on where they are playing
    • Firewall -- Port Forwarding
      • Add new
        • Interface = eth0
        • Original destination = Zentyal
        • Protol = TCP/UDP
        • Original destination port = single port = 25565
        • Source = Any
        • Destination IP = IP Address of Primary Minecraft Server
        • Port = Same
        • Replace source address = NOT checked
    • Firewall -- Port Forwarding
      • Add new
        • Interface = eth0
        • Original destination = Zentyal
        • Protol = TCP/UDP
        • Original destination port = single port = 25566
        • Source = Any
        • Destination IP = IP Address of Secondary Minecraft Server
        • Port = 25565
        • Replace source address = NOT checked
FTP Configuration 
I want to open up FTP to be accessible by anyone for now
  • Annonymos access = Read/Write
  • SSL Support = Allow SSL
  • Tried to Connect from laptop using FileZilla on 172.30.40.1 port 22
    • Get authentication failed (hmm, so much for Annonymous)
    • Added username and password for administrator, now works fine
  • Looking for where to upload OS install images and Hard Drive images for use by Virtual Machine module.... for now used /home/[user]/Downloads
  • Default libvirt storage location of Disk images is /var/lib/libvirt/images, but I don't see any new images created here.  I think I will upload a Linux Mint LXDE machine I already have into here.

Converting From Bridged to Standard Configuration
To better keep track of registered computers and control more of my network from Zentyal, I decided to use Zentyal for DHCP server on my principle network.  Setup as follows

  • Network -- Interfaces
    • eth2 (the interface I will switch the cable to after configured)
      • method = Static
      • External = NOT checked
      • IP address = 173.30.44.1
      • Netmask = 255.255.255.0
  • Network -- Objects
    This is where I want to configure the computers and servers I always want to give the same IP address to.  As an EXAMPLE
    • Add New
      • Name = HP Printer
        • Members -- add new
          • Name = HP Printer
          • IP address = 172.30.44.9
          • MAC address = [device MAC with colons]
  • DHCP  -- Choose interface eth2
    • Default gateway = Zentyal
    • Search domain = None
    • Primary nameserver = local Zentyal DNS
    • Secondary nameserver = [blank]
    • NTP Server = None
    • WINS Server = None
    • Ranges ... Add New
      • Name = free range
      • from = 172.30.44.64
      • to = 172.30.44.127  (this equals 172.30.44.64/26)
    • Fixed addresses .. Add new
      • Object = HP Printer
      • Description = [ip address] -- this is for quick reference and can be blank or whatever
  • Went live with this change... no problems so far... slight change is that I switched eth2 and eth4 so eth4 is for wired and eth2 being used for wireless.  not of consequence here.
  • Removing Firewall Rules that were needed for Bridged Mode, but no longer needed:
    • Firewall -- Packet Filter - External to internal
      • remove Minecraft service allow (now have port forward rule)
      • Remove eBox administration allow (for now only accessing from internal)
      • Remove VOIP allow (all phones internal to Zentyal)
      • Remove DHCP allow (no longer need to access External DHCP server)
      • Remove DHCP return allow (see above)
      • (this leaves no rules in this section)
    • Firewall -- Packet Filter -- External to Zentyal
      • (no changes)





Virtual Machines
Virtual Machines on Zentyal still seem to be a work in progress.  Here are my notes on trying them out (unsuccessfully so far).
  • Virtual Machines : Add New
    • Name LM-lxde
    • Auto start = not checked
    • System Settings: 
      • OS = i686 compatible
      • Base Memory = 512
    • Network Settings:
      • Add New Bridged to br1
    • Device Settings:
      • Add New Hard Drive
        • Drive type = Hard Disk
        • Action = create new disk
        • Name= LM-lxde
        • Size = 8000 MB (no thin provisioning option in the GUI)
      • Add New CD drive
        • Drive type = CD/DVD
        • Path =/home/[user]/Downloads/linuxmint-11-lxde-cd-32bit.iso
    • Starting it... Lights come up and all, but I cannot view the virtual machine in the console.  Hmmmm.....
    • Tried connecting from Virt-manager on my computer
      • signed into server with standard username and password
      • can see virtual machines, edit them, control them
      • can NOT view them in console... errors with the message "Error: viewer connection to hypervisor host got refused or disconnected!"
    • Install virt-manager on server and try
      • connect to ssh session with server
        • sudo ssh [user]@[serverip]
        • sudo apt-get install virt-manager
      • connect to virt-manager session on server as follows in a new terminal window
        • ssh -X user@serverip  (ssh with X session enabled)
        • virt-manager (to open virt-manager)
      • This allows me to see virtual machine, pause it, and presumably manipulate it in other ways
      • Can NOT view in console.  I am asked for a password then given "tcp/ip error: VNC connection to hypervisor host got refused or disconnected!"
      • searching online, this seems to be a broader issue with Ubuntu, though I do not experience it on my ubuntu server, anyway, trying virtual box for now as follows
  • Alternate is to install Virtualbox as described here:  http://forum.zentyal.org/index.php/topic,8969.msg42447.html#msg42447
    • Deleted Virtual machine I created
    • in Zentyal: Software management -- Zentyal Components -- delete
      • selected Virtualization manager and chose delete button
    • in SSH command line session: (from terminal: ssh user@zentyalip)
      • sudo apt-get remove virt-manager
      • sudo apt-get remove libvirt-bin
      • sudo apt-get install virtualbox-ose
        • get Fail message stating "No suitable module for running kernel found"

  • General notes:
    • On my computer images are stored at: /var/lib/libvirt/images
    • copied images from my computer to user download directory of Zentyal as follows
      • ssh ipaddress
      • scp root@ipaddress:/var/lib/libvirt/images/MineOS-CRUX.img /home/chenier/Downloads/
Misc Items
  • HTTPS service for firewall rules
    • to make it easier to create firewall rules to block certain https traffic, create a service definition as follows
    • Network -- Services  add new
      • service name = https
      • save and click configuation
        • Protocol = TCP/UDP
        • Source Port = any
        • Destination Port = Single port 443
    • save configuration
  • Needed firewall rule to logon to Zentyal from computer on bridged network.  I setup the following rules after which I was alble to login.
    • Firewall -- Packet Filter -- Internal networks to Zentyal
      • Add New
        • Source = Accept
        • Source = Any
        • Service = https
    • Firewall -- Packet Filter -- external Networks to Zentyal
      • Add New
        • Decision = Accept
        • Source = Source IP = IP of my computer
        • Service = https
    • Firewall -- Packet Filter -- external to internal networks
      • Add New (maybe not needed)
        • Decistion = Accept
        • Source = any
        • Destination = any
        • service = eBox administration

Updating
Decided to update with community updates for for now instead of purchasing subscription.
  • Software Management -- Zentyal Components
    • Software Management -- Settings
      • Enable Automatic security updates daily at 2:38 am
    • Software Management -- System Updates
      • select all and choose to update
    • Software Management -- Zentyal components -- Update
      • select 
        • Core 2.2.4 to 2.2.7
      • click on update and confirm
      • click on save changes
      • select (after everything finishes)
        • Antivirus 2.2 to 2.2.1
        • Backup 2.2.2 to 2.2.4
        • Common Library 2.2.2 to 2.2.3
        • Monitor 2.2.1 to 2.2.3
        • Software Management 2.2.2 to 2.2.4
        • VPN Service 2.2 to 2.2.1
        • (save other updates for another day :)
      • click on update and confirm
      • click on save changes
    • Reboot server just to be sure everything is straightened out

Identify  and Peg Network Objects
In order to peg DHCP addresses to specific computers/appliances while using dynamic DHCP on device, need to identify and peg devices on the server side.  This is a two step process.  A) Define Network Object for the unique MAC address with defined single IP address then B)Make static route in DHCP server.  Details follow for one example on my network:
  • Network -- Objects  : Add new
    • Name = phone 4001
      • members (Add new)
        • Name = phone 4001
        • IP address = CIDR = 172.30.40.41/32  (this is single IP address)
        • MAC address = (Mac for phone, copied and pasted from Dashboard)
        • (obviously, MAC address is required when desiring to peg specific devise to single IP address)
  • DHCP --  choose static interface (eth4 in my example)
    • Fixed addresses (at bottom of page)  -- Add new
      • Object = phone 4001
      • Description = (optional, but I put the pegged IP address for reference)


Configure Storage and Printers
Configure Virtual Machines
Configure Users
Configure phone advanced features