MySQL知识点技能点总结贴--个人版！

mchdba

mysql5.1.56版本，加varchar字段分区，报错：ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function


mysql> DROP TABLE tkc;
ERROR 1051 (42S02): Unknown table 'tkc'
mysql> CREATE TABLE `tkc` (
    ->    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '自增长序列',
    ->   `cr` CHAR(1) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB   CONNECTION=' table "tkc", user "msandbox", password "msandbox" '
    -> PARTITION BY KEY (id,cr)
    -> PARTITIONS 20;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql>
mysql>
mysql> DROP TABLE tkc;
ERROR 1051 (42S02): Unknown table 'tkc'
mysql> CREATE TABLE `tkc` (
    ->    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '自增长序列',
    ->   `cr` CHAR(1) DEFAULT NULL,
    ->   PRIMARY KEY (`id`,cr)  -- 主键中必须包含分区字段。
    -> ) ENGINE=INNODB   CONNECTION=' table "tkc", user "msandbox", password "msandbox" '
    -> PARTITION BY KEY (cr)
    -> PARTITIONS 20;
Query OK, 0 rows affected (1.16 sec)
mysql>

yangi1314

mysql小白留名慢慢学习

ofurn

看贴回贴能拿分，看帖回帖是美德！美德啊

unixfiregs

楼主继续。写的很好。

mchdba

[root@slave_30_40 com]# (time sysbench --mysql-user=xxx--mysql-password=xxxxx--mysql-port=3307 --test=oltp --mysql-host=10.xx.xx.xxx--oltp-test-mode=complex --mysql-table-engine=ndbcluster  --oltp-table-size=20000000 --mysql-db=ndb200  --oltp-table-name=test3 --num-threads=2000 --max-requests=50000  run) 1>/root/com/ndb2001.log 2>/root/com/ndb2002.log

报错如下：
FATAL: Error fetching result: `���`
ALERT: failed to execute mysql_stmt_execute(): Err1297 Got temporary error 245 'Too many active scans' from NDBCLUSTER
(last message repeated 1 times)
FATAL: database error, exiting...
(last message repeated 1 times)
ALERT: failed to execute mysql_stmt_execute(): Err1297 Got temporary error 245 'Too many active scans' from NDBCLUSTER
FATAL: database error, exiting...
ALERT: failed to execute mysql_stmt_execute(): Err1297 Got temporary error 245 'Too many active scans' from NDBCLUSTER
FATAL: database error, exiting...
ALERT: MySQL error: Got temporary error 245 'Too many active scans' from NDBCLUSTER

mchdba

sysbench 2000并发读写 报错如下：
FATAL: mysql_stmt_prepare() failed
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"
FATAL: thread#1638: failed to prepare statements for test
查看值
mysql> show global status like 'com_stmt%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Com_stmt_close          | 15000    |
| Com_stmt_execute        | 11024051 |
| Com_stmt_fetch          | 0        |
| Com_stmt_prepare        | 124200   |
| Com_stmt_reprepare      | 0        |
| Com_stmt_reset          | 0        |
| Com_stmt_send_long_data | 0        |
+-------------------------+----------+
mysql> set global Com_stmt_close=124000;
ERROR 1193 (HY000): Unknown system variable 'Com_stmt_close'
mysql> show global variables like 'com_stmt%';      
Empty set (0.00 sec)

mysql> show global variables like '%stmt%';   
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| binlog_stmt_cache_size     | 32768                |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_prepared_stmt_count    | 16382                |
+----------------------------+----------------------+
3 rows in set (0.00 sec)

mysql> set global max_prepared_stmt_count=124000;
Query OK, 0 rows affected (0.00 sec)

然后执行sysbench 2000并发读写，正常运行结束。

幕南风

不错

dongmin173

精华啊

xike2002

支持连载，继续关注

mchdba

information_schema很有用，大家可以去这里面查看一些db的基础信息。

use information_schema;  -- mysql的基础信息库
select * from ENGINES ;  -- 后台存储引擎信息数据的表
show engines;   -- mysql窗口界面的命令

select * from GLOBAL_STATUS ;   --  当前运行的全局的status参数信息数据
show status;  -- 命令行查看


select * from GLOBAL_VARIABLES ;   -- 当前的全局的参数存储的数据
show variables;  -- 命令行查看