$ apt-get install mariadb-server
$ cd /etc/mysql/mariadb.conf.d
$ joe 50-server.cnf
# started vi and replaced 127.0.0.1 with 0.0.0.0
$ service mysql restart

And of course allow the user accounts to access mariadb remotely over the network.

Attention! I'm only doing this locally on a testing server! It's not a great idea to run mariadb connections remotely if you do not have to.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

Tuesday, 14 April 2020

Backup & Restore of Mariadb Server

Hello, again!

This is just a small blog post on how to backup and restore a MariaDB server, without using MysqlDump, but by directly copying the files in /var/lib/mysql.

Backing up

First of course we need to shutdown mariadb.

# systemctl status mariadb

# systemctl stop mariadb

Copying files:

tar zcvf database_backup_`date +%F`.tgz /var/lib/mysql

Restoring

When putting things back, things start to go wrong¹.

Copying the files to a new host, causes this:

2020-04-14 10:45:32 0 [ERROR] InnoDB: Operating system error number 13 in a file operation. 2020-04-14 10:45:32 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory. 2020-04-14 10:45:32 0 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions

So, if the below code works, than SELinux is causing the issue:

sudo semanage permissive -a mysqld_t

Now you added mysql_t to permissive, as shown by the below printout:

]# semanage permissive -l

Builtin Permissive Types

Customized Permissive Types

mysqld_t

Turning it back to enforcing, means running:

# semanage permissive -d mysqld_t
libsemanage.semanage_direct_remove_key: Removing last permissive_mysqld_t module (no other permissive_mysqld_t module exists at another priority).

We need to relabel the new /var/lib/mysql directory:

sudo semanage fcontext -a -t mysqld_db_t "/var/lib/mysql(/.*)?"
sudo restorecon -Rv /var/lib/mysql

Checking the current file contexts:

$ ls --directory --context /var/lib/mysql
unconfined_u:object_r:mysqld_db_t:s0 /var/lib/mysql

MariaBackup

I need to check out MariaBackup^{2 3}, as a better solution for creating backups compared to mysqldump.

References

[1] Security-Enhanced Linux with MariaDB: https://mariadb.com/kb/en/selinux/
[2] Backing up and restoring databases: https://mariadb.com/kb/en/backing-up-and-restoring-databases/
[3] MariaBackup: https://mariadb.com/kb/en/mariabackup/
[4] Full Backup and Restore with MariaBackup: https://mariadb.com/kb/en/full-backup-and-restore-with-mariabackup/

Thursday, 30 January 2020

MariaDB issues

I ran into some issues, and I thought I'd document them here.

Not able to detect platform for vendor name [MariaDB1010.3.17-MariaDB]. Defaulting to [org.eclipse.persistence.platform.database.DatabasePlatform]. The database dialect used may not match with the database you are using. Please explicitly provide a platform using property "eclipselink.target-database".]]

So changed my persistence.xml and added:

Also:

Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.4.payara-p2): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=16) Table 'mmud.SEQUENCE' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_IDENTITY" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)

As the vendor name was not detected, eclipselink switched to a default implementation. The default implementation requires SEQUENCES for the IDENTITY definition.

It turns out the MariaDB implementation of Sequences is not compatible with the standard SQL way of creating sequences.

So I had to add the following property to the JDBC Connection pool:

useMysqlMetadata = true

References

JIRA MariaDB : since 2.4.0 j-connector throws sequence errors via JPA/ eclipselink on @GeneratedValue(strategy = GenerationType.IDENTITY) columns: https://jira.mariadb.org/browse/CONJ-702
Java Persistence API (JPA) Extensions Reference for EclipseLink, Release 2.4 : target-database: https://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/p_target_database.htm
Eclipse JIRA : Bug 462196 - Add support for MariaDB: https://bugs.eclipse.org/bugs/show_bug.cgi?id=462196

Thursday, 23 January 2020

Using SQL to generate JSON output

I recently read [1], and it had a very interesting notion.

The idea is to let the database generate JSON, and provide it straight into your client.

So I decided to find out if MariaDB had some support for this as well.

It does².

So, it basically was nothing more then calling a NativeQuery on the EntityManager, and returning a concatted resultset with a '['prefix and a ']'postfix and a comma-delimiter and away we go.

The native query looked like the following:

It worked flawlessly!

Caveat: of course, this is only in the case where your middleware (as in this example) really doesn't need to do anything with the result.

I mean, you are going to have to do all checks in the database query.

I think this example shows its strength when you just really want to read a lot of data, and do not need to process it.

References

[1] Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead: https://blog.jooq.org/2019/11/13/stop-mapping-stuff-in-your-middleware-use-sqls-xml-or-json-operators-instead/
[2] MariaDB - starting with 10.2.3 - JSON Functions: https://mariadb.com/kb/en/library/json-functions/
MariaDB - JSON with MariaDB Platform: What Is JSON and Why Use It – With Examples: https://mariadb.com/resources/blog/json-with-mariadb-10-2/
MariaDB - Relational and Semi-structured Data: https://mariadb.com/database-topics/semi-structured-data/
MariaDB - Function Differences Between MariaDB 10.4 and MySQL 8.0: https://mariadb.com/kb/en/library/function-differences-between-mariadb-104-and-mysql-80/#present-in-mysql-only

Thursday, 15 March 2018

Comparing NULLs in SQL

Consider the following table "mm_mailtable", containing the following data:

id	name	toname	whensent	subject	haveread	newmail
27999	William	Joe	2018-03-13 12:41:25	Golf next week?	1	0
28000	William	Linda	2018-03-13 12:41:25	I'm out!	1	0
28001	Linda	William	2018-03-13 12:41:25	Okay!	1	0
28002	Joe	William	2018-03-13 12:41:25	Sure thing!	1	1
28003	Jim	William	2018-03-13 12:41:25	The house	1	NULL

Also consider the following query I found in our source code somewhere.

SELECT *
FROM mm_mailtable
WHERE toname = :NAME
AND newmail = COALESCE(:NEWMAIL, newmail);

Let's say we try the query out with "William" as NAME and 0 as NEWMAIL.

We get an old mail to William with "Okay!" as subject.

Let's say we try the query out with "William" as NAME and 1 as NEWMAIL.

We get a new mail to William with "Sure thing!" as subject.

Let's say we try the query out with "William" as NAME and NULL as NEWMAIL.

Now I would expect to get all three entries returned, but the one containing the NULL value for "newmail" is not returned.

It takes some getting used to, but comparing null values is not possible in most databases, as it is undefined (a.k.a.. NULL).

See for more and better explanations the references below.

P.S. in this case, in our database, the column "newmail" should have been defined as "NOT NULL" and given a "DEFAULT" value, to prevent this sort of thing. Apparently it was forgotten.

Rewriting the query

This should work:

SELECT *
FROM mm_mailtable
WHERE toname = :NAME
AND (:NEWMAIL is null OR :NEWMAIL = newmail);

References

StackOverflow - why is null not equal to null false: https://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false
Baron Schwartz's Blog - Why NULL never compares false to anything in SQL: https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/

Wednesday, 28 February 2018

Removing HTML tags from Strings via SQL statements

I was just looking for a way to remove tags from strings in a database. MySQL has no support for regular expressions, so I fell back to the old way.

Just writing it down, as I think I might need it later too.

The first one is to determine which ones are to be changed. The second one changes nothing, but outputs the new result. The third one actually changed the data.

select id, adject3, adject1, adject2, adject2 from mm_items where name like '%<%' or adject1 like '%<%' or adject2 like '%<%' or adject3 like '%<%';

select adject3, concat(substring(adject3, 1, locate('<', adject3)-1), substring(adject3, locate('>', adject3) + 1)) from mm_items where adject3 like '%<%>%';

update mm_items set adject3 = concat(substring(adject3, 1, locate('<', adject3)-1), substring(adject3, locate('>', adject3) + 1)) where adject3 like '%<%>%';

Saturday, 2 December 2017

Automated executing of MySQL/MariaDB scripts

I am running MariaDB and I wish to execute sql scripts without all this hassle of entering my password. Of course this carries severe security risks with it, that we need to be aware of and, if possible, mitigate.

Via the commandline

It is possible to execute sql scripts via the commandline¹, but the problem here is that the password you use is visible in the process list. So this is a security risk.

Let's not do this.

mysql_config_editor

I firstly checked out mysql_config_editor², which enables you to put the password and other options into an encrypted configuration file. But it turns out that MariaDB does not come with that specific tool. The encryption used seems quite weak, and there's an article about the security issues at [3]. There is also the blogpost at [4] giving some details.

So now what?

Well, there is always the plan to use the configuration file .my.cnf⁵, and you can store your mysql or mariadb password in there and everything would be hunky-dory.

The .my.cnf looks like this:

[client]
password=topsecretpassword

You are no doubt aware that the password is stored in cleartext.

The following security measures should be in place:

always make sure the permissions on the file are set to -rw-------
create a user in your database with only those permissions that are required by your scripts. In most cases, this is select/update/delete/insert statements.
when you are finished with your scripts, it might be a good idea to remove the password from the conf file. I understand that with cron jobs and batch scripts this might not be possible.

There is an example of a my.cnf containing every possible configuration option at /usr/share/mysql/my-large.cnf when you install MariaDB.

References

[1] StackOverflow - How to execute a MySQL command from a shell script?: https://stackoverflow.com/questions/8055694/how-to-execute-a-mysql-command-from-a-shell-script
[2] MysqlManual 5.7 - mysql_config_editor: https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html
[3] MariaDb Blog -: https://mariadb.com/resources/blog/mysql-56-security-through-complacency
[4] Todd's MySQL Blog - Understanding mysql_config_editor’s security aspects: http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/
[5] MariaDB - Configuring MariaDB with my.cnf: https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/

Friday, 8 August 2014

Mysql JDBC Issue

Hammered myself into a wall here. Turns out when I create my Java Entities from a Database Table (using NetBeans Wizards), I get the following:

@Entity
@Table(name = "mm_log", catalog = "mmud", schema = "")
@NamedQueries(
{
    @NamedQuery(name = "Log.findAll", query = "SELECT l FROM Log l"),
    @NamedQuery(name = "Log.findByName", query = "SELECT l FROM Log l WHERE l.name = :name"),
    @NamedQuery(name = "Log.findByMessage", query = "SELECT l FROM Log l WHERE l.message = :message")
})
public class Log implements Serializable

Notice the catalog and the schema in the annotation for Table?

Now, it turns out¹ that "schema" is unsupported in MySQL, and "catalog", apparently, is just a fancy way in MySQL of saying "database".

So, if I create a brand new database (call it "newmmud"), create a nice JDBC Connection Pool to it in my Glassfish server, yet this blasted Entity (Log, in the example) will still refer to the database "mmud"!

That's what I call confusing!

Here's the message, for posterity's sake:

“You're correct. For legacy reasons (including compatibility with ODBC, and SQL Server), JDBC's concept of "catalog" maps to MySQL's concept of "databases".

Starting in MySQL-4.1, "schema" is an alias for "database", but it doesn't act like a SQL-standard schema, so we don't support the concept in the JDBC driver.”

- Mark Matthews

References

[1] Forum - Re: catalog versus schema: http://forums.mysql.com/read.php?39,137564,137629#msg-137629

Sunday, 27 July 2014

From Hibernate to EclipseLink

I've decided to try porting my little application from Hibernate to EclipseLink.

I am not currently using a lot of Hibernate specific functionality. The ones that occur to me at the moment is:

Hibernate Filters
Hibernate DELETE_ORPHAN

Filters

Filters are, as far as I know, currently not a part of the JPA Specification. Every ORM has its own implementation of dealing with it.

Below are the two different (very different!) implementations for Hibernate and EclipseLink. I find the Hibernate one to be more powerful.

Hibernate

Definition of the filter:

@FilterDef(name = "activePersons", defaultCondition="active = 1")
package mmud.database.entities;

Using the Filter on Entities:

import org.hibernate.annotations.Filter;
import org.hibernate.annotations.Filters;

@Entity
@Filters({ @Filter(name = "activePersons") })
public class Person implements Serializable { ...

@Entity
public class Room implements Serializable {

   @OneToMany(cascade = CascadeType.ALL, mappedBy = "room")
   @Filter(name = "activePersons")
   private Set<Person> persons = new HashSet<>();

Enabling the Filter, upon starting a session:

// Hibernate specific
Session session =
((org.hibernate.ejb.EntityManagerImpl)em.getDelegate()).getSession();// JPA1.0
// Session session = getEntityManager().unwrap(Session.class); // JPA2.0
session.enableFilter("activePersons");

EclipseLink

EclipseLink uses the AdditionalCriteria^{2 3} annotation on Entity level.

@Entity
@AdditionalCriteria("(:activePersonFilter <> 1 or this.active = 1)")
public class Person implements Serializable { ...
...
...
getEntityManager().setProperty("activePersonFilter", 1); // turns filter on

Unfortunately, I cannot not set it, as that will trigger the following:

org.eclipse.persistence.exceptions.QueryException.missingContextPropertyForPropertyParameterExpression(QueryException.java:260)

So I am obligated to turn the filter on or off at the boundary where the entityManager (or EntityManagerFactory) is first called.

And there is not a convenient way of turning a filter on or off. Right now, the turning on/off is part of the subquery.⁴

However, I do like the fact that my AdditionalCriteria suddenly works everywhere once I turn it on. I do not have to set specifics on the fields of an Entity. Of course, this does limit the flexibility, but in my case it is not an issue.

Deleting Orphans

Well, Hibernate was one of the first to implement the Delete-orphan functionality and it took a while for it to become meanstream in the JPA specification. But it's there now, and should be supported by all ORMs.

Hibernate

@OneToMany(cascade = CascadeType.ALL, mappedBy = "belongsto")
@Cascade({ org.hibernate.annotations.CascadeType.DELETE_ORPHAN })
private Set<item> items;

JPA 2.0

@OneToMany(cascade = CascadeType.ALL, mappedBy = "belongsto", orphanRemoval = true)
private Set<item> items;

Sha1

Hibernate

Apparently JPA doesn't have an sha1 function, yet mysql does. Hibernate had no problems with it, so I looked for a solution from EclipseLink⁵.

@NamedQuery(name = "User.authorise", query = "select p from Person p WHERE p.name = :name and p.password = sha1(:password)")

JPA 2.1

It turns out JPA⁵ has a specific reserved word called "FUNCTION" to support this, that I quite like. It prevents me from having to write a specific MySQL Dialect.

Of course, it does tie me to the MySQL implementation, but you cannot have everything.

@NamedQuery(name = "User.authorise", query = "select p from Person p WHERE p.name = :name and p.password = FUNCTION('sha1', :password)")

Notes

I found EclipseLink to be very strict with the interpretation of JPQL.

Issue 1 - References

For example, it complains about:

SELECT max(id) FROM ItemDefinition i

Wanting something more along the lines of:

SELECT max(i.id) FROM ItemDefinition i

Issue 2 - Like statements

Then there's an issue with the following query statement:

SELECT s FROM SillyName s WHERE :name like s.name

That gave me the error message "You have attempted to set a value of type class java.lang.String for parameter name with expected type of class java.lang.Boolean". I had to do some voodoo magic to make it work:

SELECT s FROM SillyName s WHERE concat('',:name) like s.name

Yikes!

Issue 3 - Update statements

Furthermore, I got a warning when creating the following update statement:

UPDATE Item i SET i.container = :container WHERE i = :item and i.belongsto = :person

The warning was:

“Input parameters can only be used in the WHERE clause or HAVING clause of a query.”

Apparently JPQL doesn't allow named parameters (or parameters of any kind) outside a WHERE or HAVING clause.

This was only a warning and the thing does work, but I should heed it.

It is described in Chapter 10.2.5.4. JPQL Input Parameters of the JPQL Language Spec⁷

Netbeans

I especially liked the fact that in Netbeans, I can mouse over the NamedQueries in the Entities and it tells me in a popup what is wrong.

References

[1] The Java EE 7 Tutorial - Introduction to the Java Persistence API: http://docs.oracle.com/javaee/7/tutorial/doc/persistence-intro001.htm
[2] AdditionalCriteria: http://wiki.eclipse.org/EclipseLink/Development/AdditionalCriteria
[3] AdditionCriteria: https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Mapping/Additional_Criteria
[4] StackOverflow - Is there a way to disable additionalcriteria in EclipseLink?: http://stackoverflow.com/questions/15847792/is-there-a-way-to-disable-additionalcriteria-in-eclipselink
[5] StackOverflow - JPA How to persist column with sha1 encryption: http://stackoverflow.com/questions/7868939/jpa-how-to-persist-column-with-sha1-encryption
[6] EclipseLink UserGuide - Support for Native Database Functions: http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Support_for_Native_Database_Functions#FUNC
[7] Oracle - JPQL Language References: http://docs.oracle.com/cd/E17904_01/apirefs.1111/e13946/ejb3_langref.html