error 1238 (hy000): variable 'log_bin' is a read only variable - 腾讯云开发者社区

文章/答案/技术大牛

发布

MySQL中的2个小问题

01 错误信息无法显示问题描述： ERROR 1238 (HY000): Unknown error 1238 我们知道，当我们在MySQL中执行一个SQL命令的时候，如果我们的命令写错了，MySQL...[ERROR] Can't read from messagefile '/usr/local/mysql-5.5.19-linux2.6-x86_64/share/english/errmsg.sys...--------------------------------------+---------------------------------------------------------+ | Variable_name...(HY000): Unknown error 很明显，不让直接修改，而且报错信息依旧只有个Unknow error 1238，于是我在配置文件中添加了一个记录： lc-messages-dir...(HY000): Variable 'lc_messages_dir' is a read only variable 02 字符串截取的一个小方法上面是错误信息的问题，再来看第二个问题，

1.7K1 0

MySQL timestamp NOT NULL插入NULL的问题

show variables like '%explicit_defaults_for_timestamp%'; +---------------------------------+-------+ | Variable_name...值变为当前时间，并没有被NOT NULL所限制且该值是无法动态修改的，必须重启库才可以变更 mysql> set global explicit_defaults_for_timestamp=0; ERROR...1238 (HY000): Variable 'explicit_defaults_for_timestamp' is a read only variable 我们在my.cnf修改该参数后并重启库后...11:45:46 | +---------------------+ 2 rows in set (0.00 sec) mysql> insert into helei values(null); ERROR...1048 (23000): Column 'a' cannot be null mysql> insert into helei values(NULL); ERROR 1048 (23000): Column

3.9K4 0

您找到你想要的搜索结果了吗？

是的

没有找到

技术译文 | MySQL 8 持久化系统变量

这使得 PERSIST_ONLY 适合配置只能在服务器启动时设置的只读系统变量。...mysql> SET PERSIST innodb_log_file_size=50331648*2; ERROR 1238 (HY000): Variable 'innodb_log_file_size...' is a read-only variable mysql> set persist_only innodb_log_file_size=50331648*2; Query OK, 0 rows affected...max_connections; Query OK, 0 rows affected (0.00 sec) mysql> RESET PERSIST innodb_max_dirty_pages_pct; ERROR...3615 (HY000): Variable innodb_max_dirty_pages_pct does not exist in persisted config file mysql> mysql

1.4K2 0

MySQL8.0+数据库参数修改后重启不再失效的秘籍

SET PERSIST_ONLY：只写入文件，不立即生效（适用于不能动态修改的参数）。...-------+------------+ 1 row in set (0.00 sec) mysql> set global innodb_log_file_size=2*1073741824; ERROR...1238 (HY000): Variable 'innodb_log_file_size' is a read only variable mysql> set persist_only innodb_log_file_size...:29.571048Z mysqld_safe mysqld restarted mysql> show global variables like 'innodb_log_file_size'; ERROR...2013 (HY000): Lost connection to MySQL server during query No connection.

6491 0

MySQL事务学习-->隔离级别

begin -> ; Query OK, 0 rows affected (0.00 sec) mysql> insert into a select 100000; ERROR...1665 (HY000): Cannotexecute statement: impossible to write to binary log since BINLOG_FORMAT =STATEMENT...ERROR 1665 (HY000): Cannotexecute statement: impossible to write to binary log since BINLOG_FORMAT =STATEMENT...----------------+ 1 row in set (0.00 sec) mysql> set global innodb_locks_unsafe_for_binlog=1; ERROR...1238 (HY000): Variable 'innodb_locks_unsafe_for_binlog' is a readonly variable mysql> 此参数是只读模式

8963 0

MySQL中的大小写敏感

mysql> show variables like '%case%'; +------------------------+-------+ | Variable_name | Value...mysql> select id,name from TEST_TAB; ERROR 1146 (42S02): Table 'test.TEST_TAB' doesn't exist 而在列级别，MySQL...mysql> set global lower_case_table_names=on; ERROR 1238 (HY000): Variable 'lower_case_table_names' is...a read only variable 重启后，test_TAB和原来的test_tab就是一个意思了。

3.7K6 0

线上MySQL复制错误排查一例

Master_Port: 6054 Connect_Retry: 60 Master_Log_File: mysql-bin.021323 Read_Master_Log_Pos.../MLoiYLtT' (Errcode: 28 - No space left on device), Error_code: 3; Error writing file '/dev/shm/MLoiYLtT...' (errno: 28 - No space left on device), Error_code: 1026; handler error HA_ERR_RBR_LOGGING_FAILED;...(none)>show variables like "%tmp%"; +----------------------------------+-----------+ | Variable_name...(none)>set global tmpdir='/tmp'; ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable 手工修改配置文件

1.5K1 0

MySQL设置数据库为只读

10.6K1 0

pt-online-schema-change报错一例

下面我们测试一下，当开启二进制日志后，如果变量log_bin_trust_function_creators为OFF，那么创建或修改存储函数就会报“ERROR 1418 (HY000): This function...)”这样的错误，如下所示：mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+...---------------+-------+| log_bin | ON |+---------------+-------+1 row in set (0.00 sec) mysql...1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration...1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration

5360 0

debezium采集MySQL CDC指南

='log_bin'; // for MySql 8.x SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::" FROM performance_schema.global_variables...WHERE variable_name='log_bin'; 在执行上述命令时如果出现如下报错： ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES...SELECT variable_value FROM information_schema.global_variables WHERE variable_name='server_id'; log_bin...='log_bin'; // for MySql 8.x mysql> SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::" FROM...performance_schema.global_variables WHERE variable_name='log_bin'; 得到：开启GTIDs 全局事务标识符 (GTID) 唯一标识集群内服务器上发生的事务

1.7K4 0

MySQL断电恢复的一点简单分析

[Note] InnoDB: Highest supported file format is Barracuda. 2017-09-13 16:05:14 7f73450a97e0 InnoDB: Error...mysql> set global innodb_force_recovery=2; ERROR 1238 (HY000): Variable 'innodb_force_recovery' is a...read only variable 在此假设我们设置为2，再次复现这个问题问题，你就会发现，数据库暂时是可以启动的，但是数据只能查询，DML操作都会抛错。...mysql> select *from test; Empty set (0.00 sec) mysql> mysql> insert into test values(1,'a'); ERROR 1030...(HY000): Got error -1 from storage engine 按照这个影响的范围来评估force_recovery的值，我们就可以做相应的取舍了。

2.6K10 0

mariadb 内存占用优化

怎么设 show global status like 'key_read%'; +------------------------+-------------+ | Variable_name |...这个值不用分配太大，通常设置16Ｍ够用了，默认8M，如果设置的内存大小不够，InnoDB 会自动申请更多的内存，并在 MySQL 的 Error Log 中记录警告信息。...{要改的key} = {值}; （立即生效重启后失效）试验 mysql> set @@global.innodb_buffer_pool_size=4294967296; ERROR 1238 (HY000...): Variable 'innodb_buffer_pool_size' is a read only variable mysql> set @@global.thread_stack=262144...; ERROR 1238 (HY000): Variable 'thread_stack' is a read only variable mysql> set @@global.binlog_cache_size

7.3K11 0

开启和查看mysql的bin-log日志

[root@VM_0_7_centos data]# grep log_bin /etc/my.cnf [root@VM_0_7_centos data]# grep log-bin /etc/my.cnf...log-bin=/opt/mysql/data/mysql-bin mysql> show variables like '%log_bin%'; ERROR 2006 (HY000): MySQL...Current database: wordpress +---------------------------------+---------------------------------+ | Variable_name...| +---------------------------------+---------------------------------+ | log_bin

2.2K6 0

MYSQL临时表导致根分区爆满问题分析

MYSQL产生临时表目录，线上怎么设置的: mysql> show variables like 'tmpdir'; +---------------+----------------------+ | Variable_name...nock@app-ali-prod-db1 ~]# sudo mkdir -pv /data/tmp/mysqlmysql> set global tmpdir='/data/tmp/mysql'; ERROR...1238 (HY000): Variable 'tmpdir' is a read only variable 通过提示可知tmpdir参数只是一个只读变量不能动态修改指定，那看来只能通过在配置文件my.cnf...mysqld reload 查看效果： mysql> show variables like 'tmpdir'; +---------------+----------------------+ | Variable_name

1.4K2 0

MySQL 5.7 vs 8.0，哪个性能更牛？

innodb_buffer_pool_size 128M innodb_log_buffer_size 64M innodb_log_file_size 48M binlog_format ROW log_bin...64M innodb_log_file_size 48M binlog_format ROW log_bin ON transaction_isolation REPEATABLE-READ...，oltp_read_only，oltp_write_only）下的性能表现 sysbench 测试时间为60s，测试的表数量为20 测试分别在双1模式（安全性）和0 2模式（高性能）下进行 ?...双1模式下 SHOW GLOBAL VARIABLES WHERE Variable_name IN('sync_binlog','innodb_flush_log_at_trx_commit');...oltp_read_only oltp_write_only" sysbench_test_info_path=/tmp/sysbench-test function red_echo () {

2K1 0

MySQL 5.7 vs 8.0，哪个性能更牛？

innodb_buffer_pool_size 128M innodb_log_buffer_size 64M innodb_log_file_size 48M binlog_format ROW log_bin...64M innodb_log_file_size 48M binlog_format ROW log_bin ON transaction_isolation REPEATABLE-READ...，oltp_read_only，oltp_write_only）下的性能表现 sysbench 测试时间为60s，测试的表数量为20 测试分别在双1模式（安全性）和0 2模式（高性能）下进行双1模式下...SHOW GLOBAL VARIABLES WHERE Variable_name IN('sync_binlog','innodb_flush_log_at_trx_commit'); +----...oltp_read_only oltp_write_only" sysbench_test_info_path=/tmp/sysbench-test function red_echo () {

7211 0

【MySQL】MySQL服务端的系统变量操作

mysql> set GLOBAL version=10; ERROR 1238 (HY000): Variable 'version' is a read only variable 作用域问题在上面的设置语法中...0 rows affected (0.00 sec) mysql> show variables like '%autocommit%'; +---------------+-------+ | Variable_name...mysql> show GLOBAL variables like '%autocommit%'; +---------------+-------+ | Variable_name | Value |...row in set (0.00 sec) mysql> show SESSION variables like '%autocommit%'; +---------------+-------+ | Variable_name...mysql> SHOW STATUS LIKE '%thread%'; +------------------------------------------+-------+ | Variable_name

9041 0

read_only和super_read_only参数的区别

show variables like '%read_only%'; 2+-----------------------+-------+ 3| Variable_name | Value...-----+ 17| Variable_name | Value | 18+-----------------------+-------+ 19| innodb_read_only...1mysql> show variables like '%read_only%'; 2+-----------------------+-------+ 3| Variable_name...set (0.00 sec) 11 12mysql> 13mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 14ERROR...1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this

5.3K5 0

MySQL 5.7和8.0性能测试

innodb_buffer_pool_size 128M innodb_log_buffer_size 64M innodb_log_file_size 48M binlog_format ROW log_bin...64M innodb_log_file_size 48M binlog_format ROW log_bin ON transaction_isolation REPEATABLE-READ...，oltp_read_only，oltp_write_only）下的性能表现 sysbench 测试时间为60s，测试的表数量为20 测试分别在双1模式（安全性）和0 2模式（高性能）下进行参数可选值...IN('sync_binlog','innodb_flush_log_at_trx_commit'); +--------------------------------+-------+ | Variable_name...oltp_read_only oltp_write_only" sysbench_test_info_path=/tmp/sysbench-test function red_echo () {

2.4K2 0

MySQL 5.7 vs 8.0，哪个性能更牛？网友吵开了锅！

innodb_buffer_pool_size 128M innodb_log_buffer_size 64M innodb_log_file_size 48M binlog_format ROW log_bin...64M innodb_log_file_size 48M binlog_format ROW log_bin ON transaction_isolation REPEATABLE-READ...，oltp_read_only，oltp_write_only）下的性能表现 sysbench 测试时间为60s，测试的表数量为20 测试分别在双1模式（安全性）和0 2模式（高性能）下进行参数可选值...IN('sync_binlog','innodb_flush_log_at_trx_commit'); +--------------------------------+-------+ | Variable_name...oltp_read_only oltp_write_only" sysbench_test_info_path=/tmp/sysbench-test function red_echo () {

3.1K0 0

点击加载更多

MySQL中的2个小问题

MySQL timestamp NOT NULL插入NULL的问题

技术译文 | MySQL 8 持久化系统变量

MySQL8.0+数据库参数修改后重启不再失效的秘籍

MySQL事务学习-->隔离级别

MySQL中的大小写敏感

线上MySQL复制错误排查一例

MySQL设置数据库为只读

pt-online-schema-change报错一例

debezium采集MySQL CDC指南

MySQL断电恢复的一点简单分析

mariadb 内存占用优化

开启和查看mysql的bin-log日志

MYSQL临时表导致根分区爆满问题分析

MySQL 5.7 vs 8.0，哪个性能更牛？

MySQL 5.7 vs 8.0，哪个性能更牛？

【MySQL】MySQL服务端的系统变量操作

read_only和super_read_only参数的区别

MySQL 5.7和8.0性能测试

MySQL 5.7 vs 8.0，哪个性能更牛？网友吵开了锅！

相关资讯

热门标签

活动推荐

运营活动

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐