Database

Yii 2 Query Builder with strange backticks

$query = (new Query()) ->select([‘itinerary.id, itinerary.from_vertex_id, itinerary.to_vertex_id, v1.name AS from_name, v2.name AS to_name, CONCAT(v1.name, v2.name) AS it_name ‘]) ->from(‘itinerary’) ->innerJoin(‘vertex v1’, ‘itinerary.from_vertex_id = v1.id’) ->innerJoin(‘vertex v2’, ‘itinerary.to_vertex_id = v2.id’) ->orderBy(‘itinerary.id’); Solution: The select method above must always be passed with an array ( [] ), using it with string puts strange backticks around column names.

0 comments

Start mysql in maintenance mode

mysql -u debian-sys-maint -p In another terminal run the following to get password cat /etc/mysql/debian.cnf

0 comments

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

Run the mysql instance as: /usr/sbin/mysqld –defaults-file=/etc/mysql/my.cnf –basedir=/usr –datadir=/var/lib/mysql –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock

0 comments

Ubuntu reinstall MySQL

First remove MySQL: sudo apt-get remove –purge mysql-server mysql-client mysql-common sudo apt-get autoremove sudo apt-get autoclean Then reinstall: sudo apt-get update sudo apt-get install mysql-server sudo mysql_install_db sudo /usr/bin/mysql_secure_installation

0 comments

MySQL Users and Privileges

┬áCheck MySQL User Privileges   If you are running a multi-user MySQL database, handy commands that show a list of all existing MySQL users and their privileges may be on your cheat sheet. To find out all MySQL users and the permissions granted to each user, log in to your MySQL server, and run the […]

0 comments

SOLVED- MySQL – start: Job failed to start

Some time when you upgrade mysql you may notice this error “start: Job failed to start”, in my case I have big databases on my vps server and causes this problem most often. The problem is that the files ‘/var/lib/mysql/ib_logfile0’ and ‘/var/lib/mysql/ib_logfile1’ are bigger in size than mysql is expecting. The solution is very simple […]

0 comments

How to check if a field from MySql is null, and insert null field in MySql??

In PHP to check if a field from MySQL is null use the keyword is_null like: is_null($row[‘fld’]) In PHP to insert a Null value into MySQL field use the trick: $date = is_null($row[‘date’]) ? “null”: “‘” . $row[‘date’] “‘”; The following will not work: $date = is_null($row[‘date’]) ? NULL: “‘” . $row[‘date’] “‘”; This is […]

0 comments