Fix Error #551 in phpMyAdmin 18.04

Error message containts :

Warning in ./libraries/plugin_interface.lib.php#551
count(): Parameter must be an array or an object that implements Countable

Solution:

You can also fix this by editing the library itself.

  1. Make a backup first
    sudo cp /usr/share/phpmyadmin/libraries/plugininterface.lib.php /usr/share/phpmyadmin/libraries/plugininterface.lib.php.bak
  2. Edit the library
    sudo nano /usr/share/phpmyadmin/libraries/plugin_interface.lib.php
  3. Search for “if (! is_null($options) && count($options) > 0) {”
    or if not found then search for “if ($options != null && count($options) > 0) {”

Then replace it with “if ($options != null) {”

To search in Nano editor press CTRL and W

  1. Save the file (CTRL and O)
  2. Refresh the phpMyAdmin page and try agin.

Backup Stored Procedures and Routines

We need to specify --routines to take backup of stored procedures with data and tables.

The following command will take backup of entire database including stored procedures. For example, your database name is “mydb”.

mysqldump -u root -p –routines mydb > mydb.sql

To take backup of only Stored Procedures and Triggers (Exclude table and data ) use the following command.

mysqldump –routines –no-create-info –no-data –no-create-db –skip-opt mydb > mydb.sql

Reference:
https://tecadmin.net

Mencopot MySQL sepenuhnya

Uninstall MySQL dengan apt-get remove terkadang tidak lah cukup, karena tidak menghapus jejak-jejak sepenuhnya.

Berikut perintah-perintah yang lebih ‘gahar’, tapi mohon hati-hati ya, jangan sampai data Anda hilang kalau belum di backup :

sudo -i
service mysql stop
killall -KILL mysql mysqld_safe mysqld
apt-get --yes purge mysql-server mysql-client
apt-get --yes autoremove --purge
apt-get autoclean
deluser --remove-home mysql
delgroup mysql
rm -rf /etc/apparmor.d/abstractions/mysql /etc/apparmor.d/cache/usr.sbin.mysqld /etc/mysql /var/lib/mysql /var/log/mysql* /var/log/upstart/mysql.log* /var/run/mysqld
updatedb
exit

 

MySQL Create Database with UTF8MB4 Character Set Syntax

Create database

I usually forgot the MySQL’s command line to create database with UTF8 character set syntax, so here it is:

CREATE DATABASE 'mydb' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
GRANT ALL ON 'mydb'.* TO 'username'@localhost IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES;

or

CREATE SCHEMA 'mydb' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
GRANT ALL ON 'mydb'.* TO 'username'@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Create user

Create user and give previledge to it

create user 'username'@localhost identified by 'password';
grant all privileges on *.* to username@localhost;

or

GRANT [type of permission] ON [database name].[table “” not found /]
TO ‘[username]’@'localhost’;

Change user’s password

Replace the password with the password that you want to use.

MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

Here is a short list of other common possible permissions that users can enjoy.

  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the Select command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

Reference from :

  • http://www.euperia.com/development/mysql/mysql-create-database-with-utf8-character-set-syntax/1064
  • https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

Access MySQL remotely

Object : MySQL versi 5.5 on Ubuntu 12.10

Terkadang kita memiliki database MySql yang tidak menyatu IP nya dengan server aplikasi, maka server database perlu dipersiapkan agar dapat diakses secara remote.
Yang perlu kita konfigurasi adalah file my.cnf yang biasanya terletak di /etc/mysql/.
Bukalah file tersebut lalu cari dan ganti :
bind-address = 127.0.0.1
menjadi
bind-address = 0.0.0.0
Simpanlah file tersebut, kemudian restart server MySQL nya :

sudo /etc/init.d/mysql restart

Untuk memastikan bahwa MySQL server sudah bisa diakses dari jaringan, coba cek dengan perintah :

netstat -anp | grep 3306

jika Anda menemukan teks seperti di bawah ini :

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN     -

Ini artinya MySQL kita sudah bisa diakses dari jaringan.
Kemudian langkah terakhir adalah memberikan kewenangan pada user siapa saja akses ini bisa gunakan, dengan cara mengatur previleges user :
mysql> GRANT ALL PRIVILEGES ON *.* TO <username>@'%' IDENTIFIED BY '<password>';
Gantilah <username> dan <password> dengan yang sesuai Anda inginkan.
Semoga berhasil, Tetap Semangat! TS!

Search Path di PostgreSQL

Search Path (search_path) adalah daftar schema yang menjadi patokan bagi PostgreSQL untuk mencari objek yang digunakan, apakah itu table, view, dan sebagainya. Urutan daftar ini juga akan menentukan prioritas pencarian. Untuk menampilkan current search path :

#show search_path;
ossystem=# show search_path;
           search_path           
---------------------------------
 sys, public, helpdesk, hrd, sym
(1 row)

Untuk membentuk search_path kita mengunakan statement SET, contoh  :

SET search_path TO my_schema, public;

 

How To Create a New User and Grant Permissions in MySQL

I always forget the MySQL create database with UTF8 character set syntax, so here it is:

CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL ON `mydb`.* TO `username`@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Alternatively, you can use ‘CREATE SCHEMA’ instead of ‘CREATE DATABASE’:

CREATE SCHEMA `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL ON `mydb`.* TO `username`@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

I hope this helps someone else too!

Reset user root MySql

Login sebagai user root system :

$ sudo -i
[sudo] password for username\:
[root@server ~]#

Matikan service MySQL :

[root@server ~]# service mysql stop
Shutting down MySQL… SUCCESS!

Hidupkan kembali service MySQL dengan melepas authentication/grant

[root@server ~]# service mysql start –skip-grant-tables
Starting MySQL… SUCCESS!

Masuk ke MySQL sebagai user ‘root’ dan masuk ke database ‘mysql’:

[root@server ~]# mysql -u root mysql
mysql>

Ubah password user ‘root’ :

mysql> update user set Password=PASSWORD(‘passwordbaru’) where user=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4  Changed: 0  Warnings: 0

Sekarang user : root dari MySQL sudah memiliki pasword baru, selanjutnya restart kembali MySQL ke mode normal:

[root@server ~]# service mysql start
Starting MySQL… SUCCESS!