mysql

Play Framework: An SBT build.sbt file for JDBC, MySQL, and Anorm

As a note to self, if you want to use JDBC, MySQL, and Anorm with the Play Framework 2.6, you’ll need a libraryDependencies entry like this in your SBT build.sbt file:

libraryDependencies ++= Seq(
    guice,
    "org.scalatestplus.play" %% "scalatestplus-play" % "3.1.2" % Test,
    jdbc,
    "mysql" % "mysql-connector-java" % "5.1.46",
    "com.typesafe.play" %% "anorm" % "2.5.3"
)

 

How to speed up JDBC PreparedStatement MySQL batch inserts

If you ever need to batch-insert a lot of records into a MySQL/MariaDB database using the JDBC PreparedStatement (as in preparedStatement.executeBatch()) be sure to change the MySQL URL to use the rewriteBatchedStatements, as shown here:

"jdbc:mysql://localhost:8889/DATABASE?rewriteBatchedStatements=true"

For a recent project I needed to batch-insert about eleven million records into a MySQL database, and the runtime was about 55 minutes. Once I added rewriteBatchedStatements=true to the MySQL URL, the batch-insert time was reduced down to only three minutes. That one little change made all the difference.

How to reset the MySQL root password on an Ubuntu server

As a brief note to self, if you forget the MySQL root password again, these are the steps you followed to create a new MySQL root password on an existing MySQL server running on Ubuntu 16.x:

How to search for a string in all fields of every table in a MySQL database

Here’s a cool tip: if you want to search for a text string in all fields of all tables of a MySQL database, you can use phpMyAdmin to do this very easily. Here are the steps to search every MySQL/MariaDB database table.

1) Select the desired database

The first step is to select the database you want to search. Don’t select a table — just select the database you want to search. (If you select a table you’ll get a different search form in Step 2.)

Help, my Drupal 8 database is huge

Earlier today I ran into a problem making a Drupal 8 database backup. I ran the usual mysqldump backup command, and when it kept running for a long time I decided to kill it, and then began investigating the problem. I knew that I had recently deleted all of the log records, and the Drupal cron task was running correctly, so something else was going on. In short, I found that my Drupal 8 database was huge.

A Drupal 8 XML Sitemap generating PHP script alvin May 23, 2016 - 5:25pm

I’m not going to comment on the following code too much or provide support for it, but (a) if you need to create an XML Sitemap for a Drupal 8 website, and (b) you don’t like the Drupal 8 sitemap modules that are available, then (c) this PHP script can serve as a starting point for you.

How to find all MySQL database tables that have specific column names

MySQL FAQ: How can I find all MySQL database tables that have specific column names?

I found the solution on this SO page. Here’s my take on it.

First, assuming that you want to copy and paste those column names after you get them, I recommend starting the MySQL command line client like this: