CODEWITHSUNDEEP
mysqlubuntu-16.04
Bhuwan Bhandari
Bhuwan Bhandari

Reputation: 25

How to run MySQL query in fixed database in MySQL at startup of MySql service in Ubuntu 16.04?

I have to run these query in MySQL everyday in database ABC.

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Is there any way to run these query in MySQL in UBUNTU except runninf from cronjob?

Upvotes: 0

Views: 98

Answers (2)

Bhuwan Bhandari
Bhuwan Bhandari

Reputation: 25

First, we find out which configuration file our MySQL installation prefers. For that, we need the binary’s location:

$ which mysqld /usr/sbin/mysqld

Then, we use this path to execute the lookup:

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf First, we find out the current sql mode:

mysql -u homestead -psecret -e "select @@sql_mode"

+-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ We open the configuration file we decided on before (/etc/mysql/my.cnf) and add the following line into the [mysqld] section:

# ... other stuff will probably be here

[mysqld]

sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Save, exit, and restart MySQL:

sudo service mysql restart The SQL mode is permanently changed and I can continue developing the legacy project until I need some additional strictness.

Upvotes: 0

vikram sahu
vikram sahu

Reputation: 179

You can directly execute this from MySQL console

mysql -umyuser databasename -p -A

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

If you want to do directly you can add it in mysql configuration file follow the steps here

Upvotes: 2

Related Questions