B4J Question [ABMaterial] MySQL DB location questions

Cableguy · Oct 21, 2018

Hi guys and gals,

While witing for AB to release his tuto on how to do a self-contained/self deployable RPI ABM hardware/software solution, I want to start creating my ABM based app...

To do so I plan to use a kind of "Portable" B4J version, dedicated exclusively to this project, in a 1TB HDD encosure. This should be more than enough for development and testing purposes.

My question is, Where should I put/create my DB file and how would the path to it look like, since it would not be a fixed letter drive.

My goal is to be able to do all development in this drive and then just ZIP from there and UNZIP to the definitive deployement machine (typically a RPI 3B booting from HDD)

Sorry if this seems a stupid question, and I must confess that I havent even started the seting up, but wanted to start on a solid base idea.

Thanks

Harris · Oct 21, 2018

Cableguy said:
DB file

Does this imply Data Base file?

Cableguy · Oct 21, 2018

Harris said:
Does this imply Data Base file?

Yeap, MySQL Database file

Harris · Oct 21, 2018

Ok, I see what you are trying to accomplish - which would make server update easier...

When I do it, Mysql is installed locally with default dir options - don't even recall a directory being an option...
When deployed to server, I use phpmyadim to "export data" locally and "import" on server to update the DB structure.

Wait for the DB guru's to wake up in their respective time zones... There may (hopefully) be a way.

keirS · Oct 21, 2018

Based on what you want to do I would say MySQL could well be the wrong solution for you. For a completely self contained deployment I would use the H2 RDBMS. This is a full DB server written in Java. To integrate the server in B4J is incredibly simple and it takes 5 lines of code.

B4X:

 #AdditionalJar: h2-1.4.196

Include the H2 DB in your project.

B4X:

Sub Process_Globals
    Public Const APP_NAME As String = "MYABAPP"
    Dim H2  As SQL

B4X:

H2DBFILE= "jdbc:h2:file:" &  File.DirData(APP_NAME) & "/MYABDB;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=9090
H2.Initialize("org.h2.Driver",H2DBFILE)

You now have a fully running H2 DB server.

Because H2 is written in Java you can include all your SQL scripts to create the database and tables using the files manager and run them from within the JAR.

B4X:

H2.ExecNonQuery("RUNSCRIPT FROM 'classpath:/Files/H2.sql'")

Harris · Oct 21, 2018

H2 RDBMS - works with jRDC2 as well? (I suspect YES).

keirS · Oct 21, 2018

Harris said:
H2 RDBMS - works with jRDC2 as well? (I suspect YES).

I have never tried it but it should work.

Harris · Oct 21, 2018

Also, (I forgot to ask) - is it MySQL (sql) syntax compatible (since my app uses this currently)?
Next Reports requires to specify the JDBC driver, wondering if works as well...
I guess a simple test would prove this (by me when time permits).

Thanks

OliverA · Oct 21, 2018

Harris said:
H2 RDBMS - works with jRDC2 as well? (I suspect YES).

Yes. It also has its own pooling. There is also hsqldb. I have a very modified jrdc2 that tests these db’s and the various pooling options out there. From my own testing, SQLite is actually a very viable option, even without pooling.

Cableguy · Oct 21, 2018

Guys, I apreciate the interest this thread has gotten from you, but I really would like just a clear answer to MY question, not that you send me on a different route…

It's like I am asking how to get to Paris and you telling me I should go to London instead!

All I need to know is posted very clearly (I think) in my first post!

OliverA · Oct 21, 2018

You need an embedded database if you want to zip something up. Your original premise needs a portable database. SQLite, h2, or hsqldb are some of the options you have for that. H2 and HSQLDB Support pooling, SQLite does not. In my testing, SQLite achieves impressive results, even without pooling.

Cableguy · Oct 21, 2018

I Don't think I will be needing pooling since the app will have a very limited number of users…

Until now I have only used MySQL… no experience whatsoever with other DB products, so …

Harris · Oct 21, 2018

Cableguy said:
but I really would like just a clear answer to MY question

Sorry dude, my bad....

OliverA said:
You need an embedded database if you want to zip something up

Seems this is your answer.... Perhaps not what you wanted to hear - but neither is "I'm pregnant" said the 16 year old to her 17 year old boy friend...

alwaysbusy · Oct 22, 2018

Cableguy said:
While witing for AB to release his tuto on how to do a self-contained/self deployable RPI ABM hardware/software solution

I'm not sure what you do expect. There is not much of a tutorial to write. You just run the jar on your raspberry pi with java on it. As for using a MySQL database, that is probably another beast. I did setup a portable version of MySQL once but on Windows. Maybe a similar thing can be done on a pi? I just installed a normal MySQL server first, and then adjusted the paths in my.ini to relative ones so I could run it from an USB stick. Looked something like this:

Folders:

B4X:

/ABMRun/2600 ' here is where the ABM app sits
/ABMRun/jre-9.0.4  ' a java runtime
/ABMRun/mysql-5.7.23-winx64  ' a normal mysql setup, with a changed my.ini (see further)

my.ini (make sure all the folders like data, logs and tmp do exist):

B4X:

[mysqld]
basedir = "/ABMRun/mysql-5.7.23-winx64"
datadir = "/ABMRun/mysql-5.7.23-winx64/data"
tmpdir = "/ABMRun/mysql-5.7.23-winx64/tmp"
plugin_dir = "/ABMRun/mysql-5.7.23-winx64/lib/plugin"

port = 3306
server_id = 1
max_connections = 30
sql_mode = ''

explicit_defaults_for_timestamp = 1

innodb_status_output = 1
innodb_status_output_locks = 1

character_set_server = utf8
collation_server = utf8_general_ci

general_log = 1
general_log_file = "/ABMRun/mysql-5.7.23-winx64/logs/general_query_all.log"

log_error = "/ABMRun/mysql-5.7.23-winx64/logs/mysqld_error.log"
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_syslog = 0
log_timestamps = SYSTEM
long_query_time = 3
slow_query_log = 1
slow_query_log_file = "/ABMRun/mysql-5.7.23-winx64/logs/slow_query.log"

[mysql]
default-character-set = utf8
show-warnings

prompt = "\u@\h [\d] > "

Then I have 2 bat files: one to start the mysql server and one to start the ABM app. Both are in the root of the /ABMRun folder:

startMySQL.bat:

B4X:

cd mysql-5.7.23-winx64
bin\mysqld --defaults-file=my.ini --console

startApp.bat

B4X:

cd 2600
..\jre-9.0.4\bin\java -jar 2600.jar

As specific for Pi (without mySQL that is), we just setup one Pi (maybe in your case with a MySQL?). Copy the java runtime and the whole ABM app folder to it. Test if everything works and then we clone the SD card to make setups for other clients.

Cableguy · Oct 23, 2018

Thanks @alwaysbusy, You have actually answered my question…

alwaysbusy said:
I'm not sure what you do expect. There is not much of a tutorial to write. You just run the jar on your raspberry pi with java on it. As for using a MySQL database, that is probably another beast. I did setup a portable version of MySQL once but on Windows. Maybe a similar thing can be done on a pi? I just installed a normal MySQL server first, and then adjusted the paths in my.ini to relative ones so I could run it from an USB stick. Looked something like this:

Folders:

B4X:

/ABMRun/2600 ' here is where the ABM app sits /ABMRun/jre-9.0.4 ' a java runtime /ABMRun/mysql-5.7.23-winx64 ' a normal mysql setup, with a changed my.ini (see further)

my.ini (make sure all the folders like data, logs and tmp do exist):

B4X:

[mysqld] basedir = "/ABMRun/mysql-5.7.23-winx64" datadir = "/ABMRun/mysql-5.7.23-winx64/data" tmpdir = "/ABMRun/mysql-5.7.23-winx64/tmp" plugin_dir = "/ABMRun/mysql-5.7.23-winx64/lib/plugin" port = 3306 server_id = 1 max_connections = 30 sql_mode = '' explicit_defaults_for_timestamp = 1 innodb_status_output = 1 innodb_status_output_locks = 1 character_set_server = utf8 collation_server = utf8_general_ci general_log = 1 general_log_file = "/ABMRun/mysql-5.7.23-winx64/logs/general_query_all.log" log_error = "/ABMRun/mysql-5.7.23-winx64/logs/mysqld_error.log" log_queries_not_using_indexes = 1 log_slow_admin_statements = 1 log_syslog = 0 log_timestamps = SYSTEM long_query_time = 3 slow_query_log = 1 slow_query_log_file = "/ABMRun/mysql-5.7.23-winx64/logs/slow_query.log" [mysql] default-character-set = utf8 show-warnings prompt = "\u@\h [\d] > "

Then I have 2 bat files: one to start the mysql server and one to start the ABM app. Both are in the root of the /ABMRun folder:

startMySQL.bat:

B4X:

cd mysql-5.7.23-winx64 bin\mysqld --defaults-file=my.ini --console

startApp.bat

B4X:

cd 2600 ..\jre-9.0.4\bin\java -jar 2600.jar

As specific for Pi (without mySQL that is), we just setup one Pi (maybe in your case with a MySQL?). Copy the java runtime and the whole ABM app folder to it. Test if everything works and then we clone the SD card to make setups for other clients.

now I can raise other questions…

First, as it seems I cannot Simply copy my MySQL DB File from the computer to the Pi, I now understand the point made by
@keirS and @OliverA …
So I may be turning myself to SQL-Lite...

DonManfred · Oct 23, 2018

Cableguy said:
I cannot Simply copy my MySQL DB File from the computer to the Pi

But you can easily backup your Mysql-Database to a SQL File (mysqldump) and import this in the rPI-MySQL-DB then (mysqlimport or mysql directly).

alwaysbusy · Oct 23, 2018

Cableguy said:
as it seems I cannot Simply copy my MySQL DB File from the computer to the Pi

But you could use an export and import no? Most exports can make .sql files which contain all the tables, indexes, views, etc queries + the data.

Cableguy · Oct 23, 2018

DonManfred said:
But you can easily backup your Mysql-Database to a SQL File (mysqldump) and import this in the rPI-MySQL-DB then (mysqlimport or mysql directly).

alwaysbusy said:
But you could use an export and import no? Most exports can make .sql files which contain all the tables, indexes, views, etc queries + the data.

Hummmm…. Interesting Idea….

Will play with it...

Harris · Oct 23, 2018

Harris said:
When deployed to server, I use phpmyadim to "export data" locally and "import" on server to update the DB structure.

@Cableguy wanted to "zip" the local DB and unzip it to deploy on Pi... Others provided potentiality satisfactory solutions based on minimal explanation of scope of project (number of web users / demand, size of DB, # of tables and relations, etc).

I consider SQLite to be a mobile (phones and tablets) DB system. Works great - and is part of the OS on devices (iOS and Android). From all the posts I have read, using SQLite for your DB on moderate server deployments (VPS or your Pi) is asking for trouble (debug and maintenance).

alwaysbusy said:
But you could use an export and import no?

I agree, Using MySQL - seems these are the rules... An extra step - yes, arduous - not really...

OliverA · Oct 23, 2018

Harris said:
From all the posts I have read, using SQLite for your DB on moderate server deployments (VPS or your Pi) is asking for trouble (debug and maintenance)

Where? SQLite (or another embedded DB) would pretty much give him a "portable" solution, where he can easily move the database to a new location (it's just 3 files in WAL mode for SQLite) and may only need to change a line of code (unless the DB is always located at the same relative location to the app or uses a configuration file that contains the pathname, then no code change is needed). Plus, with the meager resource of a Pi3B, you would not have to install another service (the DB server). Now, if you are going to serve a ton of users and run on a huge machine, MySQL may be the way to go (if you need sharding, user accounts, master/slave relationships, etc). Please note my signature message though.

B4J Question [ABMaterial] MySQL DB location questions

Expert

Expert

Expert

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency