Tag: MySQL

MariaDB 10.7 Column Store Engine on Ubuntu 20.4 – Community Edition

Leave a comment

MariaDB 10.7 Column Store Engine on Ubuntu 20.4 – Community Edition

MariaDB 10.7 Column Store Engine on Ubuntu 20.4 – Community Edition

Step 1. Virtual Environment Setup using either VMWare Viewer or Oracle VM Box

Download VMWare Player – Evaluation Learing Purpose
https://www.vmware.com/products/workstation-player/workstation-player-evaluation.html

Step 2. Download the respective VM Image
https://www.linuxvmimages.com/images/ubuntu-2004/#ubuntu-20044

Download VM Image for Ubuntu 20.4
03/22/2022 01:11 PM 2,599,524,771 Ubuntu_20.04.4_VM.7z

Uncompress the above file

03/08/2022 03:46 AM 8,607,367,168 Ubuntu_20.04.4_VM_LinuxVMImages.COM.vmdk
03/08/2022 03:46 AM 3,071 Ubuntu_20.04.4_VM_LinuxVMImages.COM.vmx

Step 3. Import VM Images in VMware Workstation (VMDK File)
Step 3.1: Go to the downloads directory of your system (default one) or to the location where you have saved the VM image.
Step 3.2: Extract the compressed file using the 7-Zip.
Optionally, move the extracted folder to where you have your other VMs.
Step 3.3: Open VMware Workstation and go to File » Open.
Import Virtual Appliance
Step 3.4: Browse to the location where you have extracted the VM image. Select the image and then click on Open.
Select VM Image
Step 3.5: In a second or two, you should see a new Virtual Machine in the VMware Workstation.
Virtual Machine
Step 3.6: If required, you can change the virtual machine’s resources like CPU, Memory, Network, and other settings by Edit virtual machine settings.
Edit Virtual Machine Settings
Step 3.7: You can now power on the virtual machine to start working on it.
Power On Virtual Machine
Step 3.8: Click on I Copied It.
Copy Virtual Machine

Step 4. Download the MariaDB respective version with ColumnStore

MariaDB 10.7 With ColumnStore – Download and Install – With Internet

Step 4.1: Start the New VM with any Name
ubuntu/ubuntu
Network Setting change to Bridged so that we can have the IP address same as our desktop to SCP

Step 4.2: Create a mysql user (from ubuntu)
sudo adduser mysql

Step 4.3: Give Sudo access to mysql user (from ubuntu)
sudo usermod -aG sudo mysql

Logout and login confirm
sudo whoami
(should return root)

Step 4.4: Download the MariaDB Community Server with ColumnStore

sudo mkdir /db_dumps
sudo chown mysql:mysql /db_dumps

D:\Data-Thanam\Download>scp mariadb-10.7.3-ubuntu-focal-amd64-debs.tar mysql@192.168.3.24:/db_dumps

Step 4.5: Install the MariaDB with ColumnStore
cd /db_dumps
tar -xf mariadb-10.7.3-ubuntu-focal-amd64-debs.tar
cd mariadb-10.7.3-ubuntu-focal-amd64-debs/
sudo ./setup_repository

sudo su –

apt-get update && apt-get install mariadb-server mariadb-backup libmariadb3 mariadb-client mariadb-plugin-columnstore

Step 6: Check and Confirm Columstore

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_LIBRARY LIKE ‘ha_columnstore%’;

CREATE DATABASE IF NOT EXISTS test;

CREATE TABLE IF NOT EXISTS test.contacts (first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100)) ENGINE=ColumnStore;

INSERT INTO test.contacts VALUES (“Walker”, “Percy”, “w.percy@example.com”), (“Flannery”, “O’Connor”, “flan@example.edu”);

select * from test.contacts;

show create table test.contacts\G;

Step 7. Confirm the MariaDB and ColumnStore Services Stop and Start

MariaDB Service Stop and Start
systemctl stop mariadb
systemctl start mariadb
systemctl restart mariadb

Note: When MariaDB service is stopped both of them are not available

ColumnStore Service Stop and Start

systemctl stop mariadb-columnstore
systemctl start mariadb-columnstore
systemctl restart mariadb-columnstore

Note: When ColumnStore service is stopped DB is available but the Table and schemas related with ColumnStore engine may not be accessible.

MariaDB [test]> select * from contacts;
ERROR 1815 (HY000): Internal error: DBRM is not responding. Cannot accept queries
MariaDB [test]>

How to ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s)

Leave a comment

mysql> select user from mysql.user;

ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

mysql> SELECT CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)) FROM mysql.global_priv where user in (‘root’,’mariadb.sys’)\G;
* 1. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): mariadb.sys@localhost => {
“access”: 0,
“plugin”: “mysql_native_password”,
“authentication_string”: “”,
“account_locked”: true,
“password_last_changed”: 0
}
* 2. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): root@localhost => {
“access”: 1073741823,
“plugin”: “mysql_native_password”,
“authentication_string”: “XXXXXXXX”,
“password_last_changed”: 1633918260
}
* 3. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): root@127.0.0.1 => {
“access”: 1073741823,
“plugin”: “mysql_native_password”,
“authentication_string”: “XXXXXXXXX”,
“password_last_changed”: 1633918260
}
* 4. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): root@::1 => {
“access”: 1073741823,
“plugin”: “mysql_native_password”,
“authentication_string”: “*XXXXXX”,
“password_last_changed”: 1633918260
}

mysql > select * from mysql.tables_priv where User=’mariadb.sys’;
Empty set (0.001 sec)

mysql > INSERT INTO tables_priv (Host, Db, User, Table_name, Grantor, Timestamp, Table_priv, Column_priv) VALUES (‘localhost’,’mysql’,’mariadb.sys’,’global_priv’, ‘root@localhost’,’0000-00-00 00:00:00′,’Select,Update,Delete’,”);
ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column mysql.tables_priv.Timestamp at row 1

mysql > select now();
+———————+
| now() |
+———————+
| 2022-02-07 01:09:07 |
+———————+
1 row in set (0.001 sec)

mysql > INSERT INTO tables_priv (Host, Db, User, Table_name, Grantor, ,Column_priv`) VALUES (‘localhost’,’mysql’,’mariadb.sys’,’global_priv’,’root@localhost’,current_timestamp(),’Select,Up
Query OK, 1 row affected (0.017 sec)

mysql > select * from user;
ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view

mysql -P 3306 -e “SET global innodb_fast_shutdown = 1;”

Stop MySQL

Start MySQL

mysql > select user from mysql.user where user=’root’;
+——+
| User |
+——+
| root |
+——+
1 rows in set (0.001 sec)

How to rename a column in MariaDB

Leave a comment 
mysql > desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empno | int(11)     | YES  |     | NULL    |       |
| ename | varchar(15) | YES  |     | NULL    |       |
| dob   | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

mysql > alter table emp change column empno eno int(11);
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eno   | int(11)     | YES  |     | NULL    |       |
| ename | varchar(15) | YES  |     | NULL    |       |
| dob   | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

mysql >

How to collect log for mariadb issue investigation

Leave a comment

When you have an incident of database crash or any issue we may need to collect detailed log for further investigation. Will be good to have these details

You can run from the unix host. This command has been tested and confirmed on Centos Linux. Make sure you are able to login to mysql instance using “mysql -P 3306” in case it is not working you have to change the command accordingly

Set filename

outfile=”CrashLog-$(hostname)-$(date +%Y%m%d)-globvar-info.out”
date>>$outfile
echo>>$outfile

Global Variables

echo “Global Variables:”>>$outfile
mysql -P 3306 -ABNe “show global variables;” | sort | sed ‘s/; /;~\&/g’ | tr -s ‘~’ ‘\n’ | tr -s ‘\&’ ‘\t’ >>$outfile

Global Status x2 60s apart

echo >>$outfile
echo “Global Status x2:”>>$outfile
mysql -P 3306 -ABNe “show global status; select sleep(60); show global status;” | sort >>$outfile

Replication Status

echo >>$outfile
echo “Replication Status:”>>$outfile
mysql -P 3306 -ABNe “show master status;” >>$outfile
echo >>$outfile
mysql -Ae “show all slaves status\G” >>$outfile

Plugins

echo >>$outfile
echo “Plugins:”>>$outfile
mysql -P 3306 -ABNe “show plugins;”>>$outfile

Tables with no primary key

echo >>$outfile
echo “Tables with no Primary Key:”>>$outfile
mysql -P 3306 -ABNe “select t.table_schema, t.table_name from information_schema.tables as t left join information_schema.key_column_usage as c \
on ( t.table_name = c.table_name and c.constraint_schema = t.table_schema and c.constraint_name = ‘PRIMARY’ ) \
where t.table_schema not in (‘information_schema’, ‘performance_schema’, ‘mysql’) and t.table_type <> ‘VIEW’ and c.constraint_name IS NULL \
order by t.table_schema, t.table_name;” >>$outfile

Data size

echo >>$outfile
echo “Dataset Size:”>>$outfile
mysql -P 3306 -ABNe “select ifnull(B.engine,’Total’) \”Storage Engine\”, concat(lpad(format( \
B.DSize/power(1024,pw),3),17,’ ‘),’ ‘,substr(‘ KMGTP’,pw+1,1),’B’) \”Data Size\”, \
concat(lpad(format(B.ISize/power(1024,pw),3),17,’ ‘),’ ‘, \
substr(‘ KMGTP’,pw+1,1),’B’) \”Index Size\”,concat(lpad(format(B.TSize/ \
power(1024,pw),3),17,’ ‘),’ ‘,substr(‘ KMGTP’,pw+1,1),’B’) \”Table Size\” \
from (select engine,sum(data_length) DSize, \
sum(index_length) ISize,SUM(data_length+index_length) TSize from information_schema.tables \
where table_schema not in (‘mysql’,’information_schema’,’performance_schema’) AND \
engine is not null group by engine with rollup) B,(SELECT 2 pw) A order by TSize;” >>$outfile

Service relevant values

echo >>$outfile
echo “systemctl service timeouts:”>>$outfile
systemctl show mariadb | grep “^Timeout” >>$outfile

Kernel

echo >>$outfile
uname -r >>$outfile

Available RAM

echo >>$outfile
echo “MemInfo and CPU Core Count”>>$outfile
cat /proc/meminfo | grep MemTotal >>$outfile

CPU cores

echo >>$outfile
echo “CPU cores” >>$outfile
cat /proc/cpuinfo | egrep “core id|physical id” | tr -d “\n” | sed s/physical/\nphysical/g | grep -v “^$” | sort | uniq | wc -l >>$outfile

Disk space

echo >>$outfile
echo “Disk space” >>$outfile
df -h >>$outfile

Installed packages

echo >>$outfile
echo “Installed packages” >>$outfile
yum list installed | egrep -i “mariadb|percona” >>$outfile