AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

MySQL - Incorrect datetime value: '0000-00-00 00:00:00' for column 'DateTime' at row 1: Getting error "Incorrect datetime value: '0000-00-00 00:00:00' for column 'DateTime' at row 1", while trying to ALTER TABLE. When we try to ALTER table, MySQL rebuilt table and checks validity of the data. If DateTime column has '0000-00-00 00:00:00' value then it does not allow to ALTER the table, and generates error - Incorrect datetime value: '0000-00-00 00:00:00' for column 'DateTime' at row 1: We have two options to get rid off this error: 01. Update zero dates with valid value 02. Disable the NO_ZERO_DATE SQL modes mode Disable the NO_ZERO_DATE SQL modes mode: SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_DATE'); Now you will be able to ALTER TABLE

MySQL reset root user password: Check MySQL status: strace -e stat64 mysqld --print-defaults > /dev/null When you don't know mysql user password and would like to reset password: https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html # service mysql stop # mysqld_safe --skip-grant-tables & # mysql -u root mysql> show databases; mysql> use mysql mysql> UPDATE mysql.user SET authentication_string = PASSWORD('mysql'), password_expired = 'N' WHERE User = 'root' AND Host = 'localhost'; mysql> flush privileges; mysql> quit # service mysql stop # service mysql start Get temporary password for mysql user root, after installation: # grep 'temporary password' /var/log/mysqld.log [Server] A temporary password is generated for root@localhost: #=lkkq7kpuFj $ mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.11 Copyright (c) 200

Troubleshoot Access denied for user 'root'@'localhost': You might have faced Access denied for user root error message and observer following errors in MySQL error log ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [ERROR] [FATAL] InnoDB: Tablespace id is 585 in the data dictionary but in file ./mysql/engine_cost.ibd it is 20! [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure [ERROR] Native table 'performance_schema'.'replication_connection_status' has the wrong structure Current locks: lock: 0x1f45ae0: lock: 0x1f45a00: lock: 0x1f45920: Solution:     01. Shutdown MySQL Daemon, and backup DATA_DIR 02. Create a new mysql instance in a temporary directory     #mkdir /tmp/mysqltmp     #mysqld --no-defaults --datadir=/tmp/mysqltmp --initialize  

Connect MySQL Docker container using Work Bench | SQL Yog and Other tool: To connect MySQL Docker container using Workbench OR SQLyog require to run MySQL Docker container with Host Network, and detached (in the background)mode. Explore more about Docker detached mode - https://severalnines.com/database-blog/mysql-docker-containers-understanding-basics Following example run MySQL Docker container in detached mode, mounting volume - /mysql/mysql_data_1/data_1 outside contanier, with name - mysql_1, and MySQL server 8.0.18  #docker run -d --network host \ --volume=/mysql/mysql_2/data_1:/var/lib/mysql \ --name=mysql_1 mysql/mysql-server:8.0.18 But it has limitations: You can have only one host network per machine host, means you can not create more. You can't link other container using --link Port mapping is not supported, means you can access MySQL at port 3306 only Explore more about Docker Host Network: https://severalnines.com/database-blog/single-host-networking-mysql-