Build Sequel Pro from source in XCode 10

Sequel Pro is a great GUI MySQL tool in macOS (it’s open source and of course totally FREE) !

But from last year, their automated build system has been broken, so their built versions are outdated :

  • Stable version 1.1.2 (Apr 3, 2016)
  • Nightly version 3477d22387 (Sep 6, 2017)

UPDATED : Nightly versions have been released from Oct 29, check it at Test Builds

Even their codebase bas been updated every week but we can’t use the latest build within a lot of bugfixes. So here is the guide to build SequelPro from source using XCode 10

Step 1 : Clone source code

1
2
$ git clone https://github.com/sequelpro/sequelpro.git --depth=1
$ cd sequelpro

Step 2 : Change build config to Release

1
$ sed -i '' -e 's/Debug/Release/g' Makefile

Step 3 : Remove i386 (32 bits) from ARCHS environment variable

1
$ find . -type f -name "*.pbxproj" -exec sed -i '' -e 's/ARCHS_STANDARD_32_64_BIT/ARCHS_STANDARD_64_BIT/g' {} +

Step 4 : Build

1
$ make

Step 5 : Copy to Application folder

1
$ cp -R build/Release/Sequel\ Pro.app /Applications/Sequel\ Pro.app

ENJOY !!! 😎

#TIL : The safest way to reset root password of MySQL Server

When you get stucked in this error message “Access denied for user ‘[email protected]’ …”, you search the way to reset the root password on the Internet, but life is Hard ! (No answer makes you feel it’s right way, even some do not work)

So to solve this problem, we need to understand MySQL Authentication

Step 1 : Disable MySQL Authentication by skip loading grant-tables on loading MySQL server

Open MySQL server config file, it might be in /etc/mysql/mysql.conf.d/mysqld.cnf. Add this line to section mysql

1
2
[mysqld]
skip-grant-tables

DANGER : BE CAREFULL ! AFTER RESETTING SERVER, YOUR MYSQL SERVER ALLOWS ANY CONNECTION FROM ANY USER FROM ANY HOST BY ANY PASSWORD

So safe way is to make sure that you are the only one connect MySQL, by

  • change to listening port of the server
1
2
3
4
[mysqld]
skip-grant-tables
port=6033
bind-address = 127.0.0.1
  • disable access through MySQL socket
1
$ sudo chmod 400 /var/run/mysqld/mysqld.sock

Step 2 : Restart the MySQL server

1
$ sudo systemctl restart mysql

Step 3 : Connect to mysql server by mysql cli, now you can connect free

1
$ mysql -h 127.0.0.1 -P 6033

Step 4 : Analyze mysql.user table

1
2
3
4
5
6
7
8
9
mysql> use mysql              
Database changed
mysql> select Host, User, plugin, password_expired, account_locked from user where User = 'root';
+-----------+------------------+-----------------------+------------------+----------------+
| Host | User | plugin | password_expired | account_locked |
+-----------+------------------+-----------------------+------------------+----------------+
| % | root | mysql_native_password | N | N |
+-----------+------------------+-----------------------+------------------+----------------+
4 rows in set (0.00 sec)

These fields meaning :

  • Host : allowed client host name or IP address
    • 127.0.0.1 : allow local clients connect via TCP
    • localhost : allow local clients connect via local UNIX socket file /var/run/mysqld/mysqld.sock
    • % : any wildcard, allow from all hosts
  • User : allowed user name
    • root : allow root user
  • plugin :
    • mysql_native_password : use hashing function of MySQL PASSWORD('YOURPASSWORD'), stored in authentication_string field (MySQL 5.7+) or password field (MySQL 5.6 or older)
    • auth_socket : use socket
  • password_expired :
    • Y : password is expired
    • N : password is not expired (still working)
  • account_locked :
    • Y : account is locked
    • N : account is not locked (still working)

Step 5 : Reset your password

Rewrite your sql command by replacing NEWPASSWORD and WHERE statement to match account we analyze in Step 4

MySQL 5.7+

1
mysql> update user set plugin = 'mysql_native_password', authentication_string = PASSWORD('NEWPASSWORD'), password_expired = 'N', account_locked = 'N' where Host = '%' and User = 'root';

MySQL 5.6 or older

1
mysql> update user set plugin = 'mysql_native_password', password = PASSWORD('NEWPASSWORD'), password_expired = 'N', account_locked = 'N' where Host = '%' and User = 'root';

Make sure that we changed 1 row by checking the result log : Query OK, 1 rows affected (0.00 sec)

Step 6 : Flushing privileges

1
2
mysql> flush privileges;
mysql> quit;

Step 7 : Rollback all config changes

Update your mysql server config file, make sure to comment out skip-grant-tables

1
2
3
4
[mysqld]
# skip-grant-tables
port=3306
bind-address = 127.0.0.1
1
$ sudo systemctl restart mysql

Trying to connect to MySQL server with your new password

1
$ mysql -u root -h 127.0.0.1 -p

If anything works perfectly, last step is enabling access to socket file

1
$ sudo chmod 777 /var/run/mysqld/mysqld.sock

HOPE IT HELP ! WE SOLVE PROBLEMS BY UNDERSTANDING IT !

#TIL : SQL Wildcard Characters

SQL databases support 2 main wildcards :

  • % : represents zero, one, or multiple characters
  • _ : represents a single character

#TIL : Use temporarily data from another database in SQLite

Sometimes, we need to use temporarily data from another database file. There is simple and fast way to achieve that without transfering data from file X to file Y.

We connect to main database

1
$ sqlite3 main.sqlite

Then using the ATTACH command to attach another database as an alias in main database

1
> ATTACH another_db.sqlite as TEMP;

Let listing the tables

1
2
3
4
5
6
> .tables
TEMP.users
TEMP.posts
users
admins
categories

As you can see, we got a list of tables, which are prefixed by defined alias TEMP we used in ATTACH command. So you only need to use these tables as normal tables.

Example of copying data between 2 tables (same structure) :

1
> INSERT INTO users SELECT * FROM TEMP.users;

#TIL : Indexes on multiple columns

Let’s say you have an indexes on 2 columns (A, B) of the table (X). So this is three use cases happen :

  1. You query data based on both of 2 columns => Indexes will be considered
  2. You query data based on (A) => Indexes will be considered
  3. You query data based on (B) => Indexes will be ignored because database indexes your data by B-tree algo. So it can’t search node via a B => If you want, just create another indexes on B column

I said will be considered because it depends on your query and your data (query optimizer will decide it !)

#TIL : Basics about sqlite command line tool

We can use sqlite3 command line tool to run SQL statement in sqlite3 file.

View all table : .tables

Truncate table : delete from [table_name]; then run vacuum; to clear space

Close : press Ctrl ^ D to escape

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ sqlite3 database.sqlite
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .tables
auth_group backend_church
auth_group_permissions backend_masstime
auth_permission django_admin_log
auth_user django_content_type
auth_user_groups django_migrations
auth_user_user_permissions django_session
backend_area
sqlite> select * from auth_user;
1|pbkdf2_sha256$30000$QQSOJMiXmNly$mWUlYwZnaQGsv9UVZcdTb29P7IHrgnd7ja3T/uwFqvw=|2017-03-25 15:06:40.528549|1|||[email protected]|1|1|2017-03-25 15:06:23.822489|admin
sqlite> describe auth_user;
Error: near "describe": syntax error
sqlite> select * from django_session;
4nmyjqpw292bmdnb5oxasi74v9rdhzoc|MzcwZDMxMzk5MGZkZTg2MjY4YWYyNmZiMzRkNWQwOTVjYzczODk5OTp7Il9hdXRoX3VzZXJfaGFzaCI6IjhlZTZjM2NhOGJjNWU4ODU0ZGE3NTYzYmQ4M2FkYzA0MGI4NTI4NzgiLCJfYXV0aF91c2VyX2JhY2tlbmQiOiJkamFuZ28uY29udHJpYi5hdXRoLmJhY2tlbmRzLk1vZGVsQmFja2VuZCIsIl9hdXRoX3VzZXJfaWQiOiIxIn0=|2017-04-08 15:06:40.530786
sqlite> delete from django_session;
sqlite> vacuum;
sqlite> ^D

#TIL : Persistent connection to MySQL

When a PHP process connects to MySQL server, the connection can be persistent if your PHP config has mysql.allow_persistent or mysqli.allow_persistent. (PDO has the attribute ATTR_PERSISTENT)

1
$dbh = new PDO('DSN', 'KhanhDepZai', 'QuenMatKhauCMNR', [PDO::ATTR_PERSISTENT => TRUE]);

Object destruction

PHP destruct an object automatically when an object lost all its references.

Example code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<?php

$x = null;

function klog($x) {
echo $x . ' => ';
}

class A {
private $k;
function __construct($k) {
$this->k = $k;
}

function b() {
klog('[b]');
}

function __destruct() {
klog("[{$this->k} has been killed]");
}
}

function c($k) {
return new A($k);
}

function d() {
c('d')->b();
}

function e() {
global $x;
$x = c('e');
$x->b();
klog('[e]');
}

function f() {
klog('[f]');
}

d();
e();
f();

Result:

1
[b] => [d has been killed] => [b] => [e] => [f] => [e has been killed] =>

Reducing PDO persistent connections in PHP long-run process (connect to multiples databases)

Instead of using a service object, we should use a factory design pattern for each job (each connection). PHP will close MySQL connection because it destructs object PDO. Then we can reduce the number of connections to MySQL at a same time.

I learned this case when implement a web-consumer (long-run process) to run database migration for multiples databases.

Before fixing this, our MySQL server had been crashed because of a huge opened connections.

Now, everything works like a charm !

Bring it on