MariaDB SQL Mode对数据库语法的影响

 数据库  No Responses »
7 月 012020
 

MariaDB SQL Mode对数据库操作语法的影响

https://mariadb.com/kb/en/sql-mode/#setting-sql_mode

自MariaDB 10.2.4版本后的默认SQL模式

STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

当SQL模式中配置NO_AUTO_CREATE_USER启用，执行GRANT授权时只有同时指定验证信息才能成功创建用户账户，否则就需要使用CREATE USER单独创建用户账户。

https://mariadb.com/kb/en/grant/#implicit-account-creation
https://mariadb.com/kb/en/create-user/
https://mariadb.com/kb/en/set-password/

查看当前数据库系统的SQL MODE设置

MariaDB [mysql]> select @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mysql]> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mysql]>

错误用法

MariaDB [mysql]> create database example;
Query OK, 1 row affected (0.000 sec)

MariaDB [mysql]> grant all on example.* to test_user@localhost;
ERROR 1133 (28000): Can't find any matching row in the user table
MariaDB [mysql]>

正确用法一

MariaDB [mysql]> grant all on example.* to test_user@localhost identified by 'testpwd';
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]>

正确用法二

MariaDB [mysql]> create user test_user@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> set password for test_user@localhost=password('testpwd');
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> grant all on example.* to test_user@localhost;
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]>

使用timedatectl查询和调整系统时间日期

 未分类  No Responses »
7 月 012020
 

查看系统时间日期配置

[centos@dev ~]$ sudo timedatectl
[sudo] password for centos:
               Local time: Wed 2020-07-01 09:21:55 UTC
           Universal time: Wed 2020-07-01 09:21:55 UTC
                 RTC time: Wed 2020-07-01 09:21:55
                Time zone: UTC (UTC, +0000)
System clock synchronized: yes
              NTP service: active
          RTC in local TZ: no
[centos@dev ~]$
[centos@dev ~]$ sudo timedatectl --help
timedatectl [OPTIONS...] COMMAND ...

Query or change system time and date settings.

  -h --help                Show this help message
     --version             Show package version
     --no-pager            Do not pipe output into a pager
     --no-ask-password     Do not prompt for password
  -H --host=[USER@]HOST    Operate on remote host
  -M --machine=CONTAINER   Operate on local container
     --adjust-system-clock Adjust system clock when changing local RTC mode
     --monitor             Monitor status of systemd-timesyncd
  -p --property=NAME       Show only properties by this name
  -a --all                 Show all properties, including empty ones
     --value               When showing properties, only print the value

Commands:
  status                   Show current time settings
  show                     Show properties of systemd-timedated
  set-time TIME            Set system time
  set-timezone ZONE        Set system time zone
  list-timezones           Show known time zones
  set-local-rtc BOOL       Control whether RTC is in local time
  set-ntp BOOL             Enable or disable network time synchronization

systemd-timesyncd Commands:
  timesync-status          Show status of systemd-timesyncd
  show-timesync            Show properties of systemd-timesyncd
[centos@dev ~]$

修改系统时区 

[centos@dev ~]$ sudo timedatectl set-timezone Asia/Hong_Kong
[centos@dev ~]$ date
Wed Jul  1 17:22:59 HKT 2020
[centos@dev ~]$
[centos@dev ~]$ sudo timedatectl
               Local time: Wed 2020-07-01 17:24:20 HKT
           Universal time: Wed 2020-07-01 09:24:20 UTC
                 RTC time: Wed 2020-07-01 09:24:20
                Time zone: Asia/Hong_Kong (HKT, +0800)
System clock synchronized: yes
              NTP service: active
          RTC in local TZ: no
[centos@dev ~]$