mysql

Play Framework: An SBT build.sbt file for JDBC, MySQL, and Anorm alvin May 13, 2018 - 5:30pm

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 alvin May 6, 2018 - 7:39pm

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 alvin April 29, 2018 - 3:31pm

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:

ScalikeJdbc: An example of SQL SELECT query to create a Scala List alvin April 9, 2018 - 6:26pm

As a brief note today, this Scala example code shows how I make a SQL query using ScalikeJdbc to return results as a List, in this case making a SQL SELECT query to create a Scala list of PhotoNode instances:

Scala: A ScalikeJdbc example (including SQL select and update statements) alvin April 1, 2018 - 8:53pm

If you want to see a relatively simple ScalikeJdbc example that performs SQL SELECT and UPDATE commands, I hope this example is helpful.

The background for this example goes like this:

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 alvin July 17, 2016 - 1:28pm

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: