Category Archives: MySQL

Use HTTPS protocol in WordPress 4.4+ for srcset attribute

I’ve started using “Let’s Encrypt” in multiple sites I manage. Just after installing it for this blog I noticed that all images didn’t load because they were originally linked with HTTP.

mixed-content-loaded-over-https-insecure-script

This was easy to fix with a simple SQL update:

UPDATE wp_posts SET post_content = replace(post_content, 'http://tech.enekochan.com', 'https://tech.enekochan.com');

But I still got the “Mixed Content” error messages. Looking at the generated page source code I found that WordPress 4.4+ adds a responsive image attribute to images img tags: srcset. And the problem was that the links there were using HTTP. Adding this code in functions.php solved the issue:

/*
 * Force URLs in srcset attributes into HTTPS scheme.
 * This is particularly useful when you're running a Flexible SSL frontend like Cloudflare
 */
function ssl_srcset( $sources ) {
  foreach ( $sources as &$source ) {
    $source['url'] = set_url_scheme( $source['url'], 'https' );
  }

  return $sources;
}
add_filter( 'wp_calculate_image_srcset', 'ssl_srcset' );

Ref: https://wordpress.org/support/topic/responsive-images-src-url-is-https-srcset-url-is-http-no-images-loaded#post-7767555
https://make.wordpress.org/core/2015/11/10/responsive-images-in-wordpress-4-4/

Temporary avoid MySQL “Cannot delete or update a parent row: a foreign key constraint fails”

mysql> SET foreign_key_checks = 0;
mysql> DROP table ...;
mysql> SET foreign_key_checks = 1;

Ref: http://stackoverflow.com/questions/2300396/force-drop-mysql-bypassing-foreign-key-constraint

Fix “Communications link failure” in Java applications with MySQL

You may have problems connecting to MySQL from Java applications using Connector/J (the Java connector for MySQL) after upgrading/migrating MacPorts to a newer version. It happened to me after updating Mac OS X to El Capitan and upgrading MacPorts to work with it. Everything worked fine with phpMyAdmin and other applications using PHP but Java applications wouldn’t connect:

Exception in thread "main" java.lang.IllegalStateException: Cannot connect the database!
	at Main.main(Main.java:19)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:983)
	at com.mysql.jdbc.MysqlIO.(MysqlIO.java:339)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2252)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2285)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2084)
	at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795)
	at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at Main.main(Main.java:16)
Caused by: java.net.ConnectException: Connection refused
	at java.net.PlainSocketImpl.socketConnect(Native Method)
	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:345)
	at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
	at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
	at java.net.Socket.connect(Socket.java:589)
	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:214)
	at com.mysql.jdbc.MysqlIO.(MysqlIO.java:298)
	... 15 more

You can test a simple connection in Java with this code in a file named Main.java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/database_name";
        String username = "username";
        String password = "password";

        System.out.println("Connecting database...");

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Database connected!");
        } catch (SQLException e) {
            throw new IllegalStateException("Cannot connect the database!", e);
       }
    }
}

And running it like this (you’ll have to download the Java MySQL connector and place it in the same path with the .java file):

$ javac Main.java
$ java -cp .:./mysql-connector-java-5.1.37-bin.jar Main

I could connect to MySQL databases from the console using mysql so my server was running. I checked the usual configurations in my.cnf and didn’t see nothing wrong. The binding address was correct (it was 0.0.0.0 so connections from any IP could be done) and the socket file was also fine.

# Use default MacPorts settings
!include /opt/local/etc/mysql55/macports-default.cnf

[client]
port            = 3306
socket          = /opt/local/var/run/mysql55/mysqld.sock

[mysqld]
port            = 3306
socket          = /opt/local/var/run/mysql55/mysqld.sock
bind-address    = 0.0.0.0
max_allowed_packet = 256M

And then I saw it. Look at the include line. The problem was that the /opt/local/etc/mysql55/macports-default.cnf file in that include had been rewritten when MacPorts was updated and it had the skip-networking option enabled.

skip-networking

So the problem was solved by just commenting it out and restarting MySQL server.

Fix “Configuration of pmadb… not OK” in phpMyAdmin

You may get this message in pmpMyAdmin:

phpmyadmin-pmadb

To fix this you have to import/create the phpmyadmin database with it’s tables. An SQL script called create_tables.sql should be in the scripts, examples or sql folder of your phpMyAdmin installation.

mysql -u root -p < create_tables.sql

Then create a user called pma and give it permissions to the phpmyadmin database:

CREATE USER 'pma'@'localhost' IDENTIFIED BY 'pmapass';
GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'pma'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Now edit config.inc.php and configure/uncomment those lines:

/*
 * phpMyAdmin configuration storage settings.
 */

/* User used to manipulate with storage */
$cfg['Servers'][$i]['controlhost'] = 'localhost';
// $cfg['Servers'][$i]['controlport'] = '';
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'pmapass';

/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';

Finally logout and login again to phpMyadmin.

Ref: http://stackoverflow.com/questions/21033059/pma-database-not-ok-in-phpmyadmin-upgrade
https://wiki.phpmyadmin.net/pma/Configuration_storage

Doctrine ORM: Exception “Duplicate entry ‘2147483647’”

I was storing Facebook and Twitter profile IDs and I got this error:

  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2147
  483647' for key 'PRIMARY'

  [PDOException]
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2147
  483647' for key 'PRIMARY'

When I went back to my data there was no ID with 2147483647 value. Then I realized that 2147483647 is the maximun value you can store in MySQL with a Doctrine integer type. So I just changed integer to bigint in my entities and the problem was solved.

class TwitterProfile
{
    /**
     * @ORM\Id
     * @ORM\Column(type="bigint")
     */
    protected $id;
    ...
}

doctrine-mysql-duplicate-entry-2147483647