Create and import a MySQL dump

Dumping a MySQL database is that simple:

 /path/to/mysqldump -u$DBUSER -p$DBPASSWD -h$DBHOST $DBNAME > $FILENAME  

Importing the dump back into a MySQL DB looks like this:

 /path/to/mysql -u $DBUSER -p$DBPASSWD --database=$DBNAME < $FILENAME  

If the database to import is encoded with UTF8 it might be necessary to use the option
--default_character_set

 /path/to/mysql -u $DBUSER -p$DBPASSWD --default_character_set utf8 --database=$DBNAME < $FILENAME  

Howto create a DB field with a reserved name using Hibernate and JPA

I stumbled upon this problem when I switched from a SQLServer 2008 database to a MySQL database 5.5 using Hibernate in Version 3.6.6 with JPA implementation 2.0.

The goal

Generating a MySQL database schema or updating a database schema automatically using Hibernate's hbm2ddl.auto feature.

The problem

Using SQLServer for a long time everything worked fine. When switching to MySQL the schema generation failed. In particular, one single table was not created and I couldn't find a hint in Hibernate's log statements.

The solution

My persistence.xml file uses the auto update of a database schema using this property

      <property name="hibernate.hbm2ddl.auto" value="update" />

So far so good. Nothing special here.
I changed the Hibernate dialect to use the MySQL dialect (org.hibernate.dialect.MySQLDialect) and when deploying the application it generates the database schema and it should populate the database with default values. When populating starts it immediately fails with the following Hibernate error message:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval) values (0, '10', 9, 4, 7, 1)' at line 1"

After a few minutes it came to my mind that the column name "interval" might be a keyword in MySQL. So googling around I found out that I was right. "interval" is a keyword in MySQL (but not in SQLServer).

Ok, reason found. But what is the best way to fix this issue?
Stackoverflow is your friend :-)
Just read this answer from Stackoverflow and you are done.

To summarize shortly:
With Hibernate as JPA 1.0 provider you have to annotate the entity field with enclosing backticks like this:

@Column(name="`interval`")

and when using JPA 2.0 the syntax was standardized you have to escape the column name like this

@Column(name="\"interval\"")

This is mentioned in chapter 2.13 (Naming of database objects) of the JPA 2.0 specification:

"It is possible to specify on a per-name basis that a name for a database object is to be inter-
preted as a delimited identifier as follows:
• Using annotations, a name is specified as a delimited identifier by enclosing the name
within double quotes, whereby the inner quotes are escaped, e.g.,
@Table(name="\"customer\"").
• When using XML, a name is specified as a delimited identifier by use of double
quotes, e.g.,
<table name="&quot;customer&quot;"/>"

Using the first (but deprecated) solution you can force Hibernate to quote an identifier in the generated SQL by enclosing the table or column name in backticks in the mapping document. Hibernate will use the correct quotation style for the SQL Dialect. This is usually double quotes, but the SQL Server uses brackets and MySQL uses backticks.

The second solution is the modern and standardized way of forcing your JPA 2.0 and above provider to quote the identifier.

MySQL Server only accessible from localhost

Yesterday I spent some time figuring out why my MySQL Server doesn't accept remote connections anymore. I was not able to connect from any other machines in my local network except from the machine running the server (localhost).
It turns out to be a problem in the MySQL configuration file. The option bind-address was set to 127.0.0.1, which means the server was only listening locally.
The solution is to comment out the "bind-address" line and restart the server. This may be a security risk, because the server is now accessible from any host.
I assume this modification in the MySQL config file was made during a Debian update process.

Serendipity Installation Problem solved (MySQL Database Setup)

I was trying to install Serendipity 1.0-beta3 and was facing problems with my MySQL database setup. The installer complained about a missing database module.
On my Debian system the debian package of the PHP Mysql module "php4-mysql" was installed into the directory
/usr/lib/php4/20050606
My "php.ini" file had the "extension_dir" set to "./"
So the Mysql module could not be found.
Solution:

1. I created a new directory /usr/local/share/php/extensions and switched into it.
2. I linked the mysql.so to the original location using the command:
1. ln -s /usr/lib/php4/20050606/mysql.so mysql.so
3. I set the "extension_dir" variable in the file "php.ini" to "/usr/local/share/php/extensions"
4. Restart of my Apache2 webserver.

Yep, it's working now