StoneDB 企业版 8.0v2.2.0 初体验

2023年 12月 6日 65.7k 0

前言

前段时间已经体验过 StoneDB 5.7 ,对这款石原子数据库也有了初步印象。尤其是 StoneDB 5.7 开源版本未适配 Rocky Linux 9 的情况下,编译安装遇到了若干问题,本文来看看 StoneDB 8.0 企业版在 Rocky 9 上是否安装顺利。

  • 三天三夜的三更半夜! StoneDB on Rocky 9
  • StoneDB 初体验 | StoneDB-5.7-v1.0.4 安装及新特性速览

介绍

StoneDB 是由石原子科技公司自主设计、研发的国内首款基于 MySQL 内核打造的开源 HTAP(Hybrid Transactional and Analytical Processing)融合型数据库,可实现与 MySQL 的无缝切换。StoneDB 具备超高性能、实时分析等特点,为用户提供一站式 HTAP 解决方案。

StoneDB 100% 兼容 MySQL 5.6、5.7、8.0 协议和 MySQL 生态等重要特性,支持 MySQL 常用的功能及语法,支持 MySQL 生态中的系统工具和客户端,如 Navicat、Workbench、mysqldump、mydumper。由于 100% 兼容 MySQL,因此 StoneDB 的所有工作负载都可以继续使用 MySQL 数据库体系运行。

StoneDB 专门针对 OLAP 应用程序进行了设计和优化,支持百亿数据场景下进行高性能、多维度字段组合的复杂查询,相对比社区版的 MySQL,其查询速度提升了十倍以上。

StoneDB 采用基于知识网格技术和列式存储引擎,该存储引擎为海量数据背景下 OLAP 应用而设计,通过列式存储数据、知识网格过滤、高效数据压缩等技术,为应用系统提供低成本和高性能的数据查询支持。

Release Notes

11月30日,石原子科技正式发布 StoneDB-8.0-V2.2.0 企业版。

Release Notes for StoneDB-ee-8.0-v2.2.0

  1. New Futures
  • 支持自定义函数create/drop function
  • 支持存储过程create/drop procedurecall procedure
  • 支持触发器create/drop trigger
  • 支持alter table rename table_name2
  • 支持select into out file
  • 支持unsiged singed zerofill等列属性
  • Read Committed 隔离级实现
  1. Bug Fixes
  • 主从功能对NULL处理错误
  • Release 版本 init db 失败
  • 存储过程执行后,查询导致 core
  • drop table 不能正确删除
  • Item::send 对 decimal 处理不完全,导致 decimal(5,3) 结果显示 null
  • 使用 with as 导致 core
  • 部分子查询导致 core
  • 主从复制中,double 数据插入错误
  • TPC-H Q8 error
  • 备库同步失败导致 core
  • 备库同步,切换db后,建表失败
  • 传入错误的指针,导致 slave mode stop
  1. Refactors
  • 重构对 MySQL 标识符`的支持
  • 重构 create table 语句的 SQL 支持
  1. 性能优化
  • 备库insert同步性能提升1-2倍

环境信息

本文所使用的是最新的 StoneDB 8.0 v2.2.0 企业版,目前 StoneDB-8.0-V2.2.0 企业版正在免费公测中,下载地址为: StoneAtom 软件下载中心

这里选择 【8.0-v2.2.0 RPM】: https://stoneatom-static.oss-cn-hangzhou.aliyuncs.com/stonedb-ee-8.0-v2.2.0.el8.x86_64.rpm

可以看到这个 RPM 包适配的是 el8,理论上也是可以安装在 Rocky 9 上的,我们来尝试一下。

主机安装的系统为 Rocky Linux 9.3,关于 Rocky 9 的安装,可参考文章:

  • 【Rocky 9】Step by Step 安装 Rocky Linux 9.2 系统
  • 【Rocky 9】Rocky Linux 9.2 升级 9.3,及新特性实践

Q1: OS 适配

如果操作系统是 EL 系,除了 RPM 格式,还有 TAR 格式的安装包可以选,

【8.0-v2.2.0 TAR】: https://stoneatom-static.oss-cn-hangzhou.aliyuncs.com/stonedb-ee-8.0-v2.2.0.el7.x86_64.tar.gz

可以看到 RPM 适配的是 el8 系统,而 TAR 包适配的是 el7。
这里建议提供不同版本操作系统不同格式的包,比如,

stonedb-ee-8.0-v2.2.0.el9.x86_64.rpm
stonedb-ee-8.0-v2.2.0.el8.x86_64.rpm
stonedb-ee-8.0-v2.2.0.el7.x86_64.rpm

stonedb-ee-8.0-v2.2.0.el9.x86_64.tar.gz
stonedb-ee-8.0-v2.2.0.el8.x86_64.tar.gz
stonedb-ee-8.0-v2.2.0.el7.x86_64.tar.gz

安装 StoneDB

安装 RPM

直接通过 yum 进行安装 RPM 包:

[shawnyan@rocky9 stonedb]$ ls
stonedb-ee-8.0-v2.2.0.el8.x86_64.rpm
[shawnyan@rocky9 stonedb]$ sudo yum install ./stonedb-ee-8.0-v2.2.0.el8.x86_64.rpm
Last metadata expiration check: 1:31:49 ago on Tue 05 Dec 2023 01:02:18 PM UTC.
Dependencies resolved.
================================================================================
Package Architecture Version Repository Size
================================================================================
Installing:
stonedb_2.0 x86_64 2.2.0-1.el8 @commandline 434 M

Transaction Summary
================================================================================
Install 1 Package

Total size: 434 M
Installed size: 1.1 G
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Error: Transaction test error:
file /usr/lib64/libsnappy.so.1 from install of stonedb_2.0-2.2.0-1.el8.x86_64 conflicts with file from package snappy-1.1.8-8.el9.x86_64

这里遇到了冲突,这里先将 snappy 卸载。

[shawnyan@rocky9 stonedb]$ sudo yum remove snappy-1.1.8-8.el9.x86_64
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Removing:
snappy x86_64 1.1.8-8.el9 @anaconda 63 k
Removing dependent packages:
cockpit x86_64 300.1-1.el9_3 @baseos 58 k
cockpit-storaged noarch 300.1-1.el9_3 @appstream 882 k
cockpit-system noarch 300.1-1.el9_3 @baseos 5.2 M
kexec-tools x86_64 2.0.26-8.el9 @baseos 1.3 M
snappy-devel x86_64 1.1.8-8.el9 @devel 44 k
Removing unused dependencies:
clevis x86_64 18-112.el9 @appstream 122 k
clevis-luks x86_64 18-112.el9 @appstream 71 k
device-mapper-multipath x86_64 0.8.7-22.el9 @baseos 320 k
device-mapper-multipath-libs x86_64 0.8.7-22.el9 @baseos 876 k
dracut-network x86_64 057-44.git20230822.el9 @baseos 190 k
dracut-squash x86_64 057-44.git20230822.el9 @baseos 3.2 k
iscsi-initiator-utils x86_64 6.2.1.4-3.git2a8f9d8.el9 @anaconda 1.5 M
iscsi-initiator-utils-iscsiuio
x86_64 6.2.1.4-3.git2a8f9d8.el9 @anaconda 189 k
isns-utils-libs x86_64 0.101-4.el9 @anaconda 488 k
jose x86_64 11-3.el9 @AppStream 134 k
jq x86_64 1.6-15.el9 @appstream 408 k
libblockdev-lvm x86_64 2.28-7.el9 @appstream 68 k
libjose x86_64 11-3.el9 @AppStream 154 k
libluksmeta x86_64 9-12.el9 @AppStream 46 k
luksmeta x86_64 9-12.el9 @AppStream 32 k
oniguruma x86_64 6.9.6-1.el9.5 @AppStream 746 k
python3-dasbus noarch 1.4-5.el9 @AppStream 329 k
setroubleshoot-plugins noarch 3.3.14-4.el9 @AppStream 2.5 M
setroubleshoot-server x86_64 3.3.32-1.el9 @appstream 1.3 M
squashfs-tools x86_64 4.4-8.git1.el9 @anaconda 420 k
tpm2-tools x86_64 5.2-3.el9 @baseos 1.4 M
udisks2-iscsi x86_64 2.9.4-9.el9 @appstream 56 k
udisks2-lvm2 x86_64 2.9.4-9.el9 @appstream 104 k

Transaction Summary
================================================================================
Remove 29 Packages

Freed space: 19 M
Is this ok [y/N]: y
...
Removed:
clevis-18-112.el9.x86_64
clevis-luks-18-112.el9.x86_64
cockpit-300.1-1.el9_3.x86_64
cockpit-storaged-300.1-1.el9_3.noarch
cockpit-system-300.1-1.el9_3.noarch
device-mapper-multipath-0.8.7-22.el9.x86_64
device-mapper-multipath-libs-0.8.7-22.el9.x86_64
dracut-network-057-44.git20230822.el9.x86_64
dracut-squash-057-44.git20230822.el9.x86_64
iscsi-initiator-utils-6.2.1.4-3.git2a8f9d8.el9.x86_64
iscsi-initiator-utils-iscsiuio-6.2.1.4-3.git2a8f9d8.el9.x86_64
isns-utils-libs-0.101-4.el9.x86_64
jose-11-3.el9.x86_64
jq-1.6-15.el9.x86_64
kexec-tools-2.0.26-8.el9.x86_64
libblockdev-lvm-2.28-7.el9.x86_64
libjose-11-3.el9.x86_64
libluksmeta-9-12.el9.x86_64
luksmeta-9-12.el9.x86_64
oniguruma-6.9.6-1.el9.5.x86_64
python3-dasbus-1.4-5.el9.noarch
setroubleshoot-plugins-3.3.14-4.el9.noarch
setroubleshoot-server-3.3.32-1.el9.x86_64
snappy-1.1.8-8.el9.x86_64
snappy-devel-1.1.8-8.el9.x86_64
squashfs-tools-4.4-8.git1.el9.x86_64
tpm2-tools-5.2-3.el9.x86_64
udisks2-iscsi-2.9.4-9.el9.x86_64
udisks2-lvm2-2.9.4-9.el9.x86_64

Complete!

然后再次尝试安装。

[shawnyan@rocky9 stonedb]$ sudo yum install ./stonedb-ee-8.0-v2.2.0.el8.x86_64.rpm
Last metadata expiration check: 1:32:37 ago on Tue 05 Dec 2023 01:02:18 PM UTC.
Dependencies resolved.
================================================================================
Package Architecture Version Repository Size
================================================================================
Installing:
stonedb_2.0 x86_64 2.2.0-1.el8 @commandline 434 M

Transaction Summary
================================================================================
Install 1 Package

Total size: 434 M
Installed size: 1.1 G
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: stonedb_2.0-2.2.0-1.el8.x86_64 1/1
Installing : stonedb_2.0-2.2.0-1.el8.x86_64 1/1
Running scriptlet: stonedb_2.0-2.2.0-1.el8.x86_64 1/1
removed '/opt/stonedb_v2/install/bin/monetdb'
removed '/opt/stonedb_v2/install/bin/monetdbd'
removed '/opt/stonedb_v2/install/bin/mclient'
removed '/opt/stonedb_v2/install/bin/mserver5'
removed '/opt/stonedb_v2/install/bin/monetdb_mtest.sh'
/var/tmp/rpm-tmp.AzSlx4: line 15: upx: command not found

Verifying : stonedb_2.0-2.2.0-1.el8.x86_64 1/1

Installed:
stonedb_2.0-2.2.0-1.el8.x86_64

Complete!
[shawnyan@rocky9 stonedb]$

可以看到安装成功。

Q2: 移除 monetdb

从上面的安装日志中可以看到有这么一段:

removed '/opt/stonedb_v2/install/bin/monetdb'
removed '/opt/stonedb_v2/install/bin/monetdbd'
removed '/opt/stonedb_v2/install/bin/mclient'
removed '/opt/stonedb_v2/install/bin/mserver5'
removed '/opt/stonedb_v2/install/bin/monetdb_mtest.sh'

不太清楚为什么要删掉 monetdb 的相关命令,导致后面无法启动 monetdb,如果真的不想要可以在打包时就过滤掉。

Q3: upx

从上面的安装日志中可以看到有这么一段:

/var/tmp/rpm-tmp.AzSlx4: line 15: upx: command not found

如果 StoneDB 需要使用 upx 来压缩文件,更加优雅的方式是在写 spec 的时候放到依赖里,这样安装 StoneDB 的同时安装 upx

环境变量

安装完成后,切换到 mysql 用户,配置 Libary/PATH 路径:

export LD_LIBRARY_PATH=/opt/stonedb_v2/install/lib:/usr/local/lib64:$LD_LIBRARY_PATH
export PATH=/opt/stonedb_v2/install/bin:$PATH

检查动态库依赖:

[mysql@rocky9 ~]$ ldd /opt/stonedb_v2/install/bin/mysqld
linux-vdso.so.1 (0x00007ffe5b778000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f2099825000)
librt.so.1 => /lib64/librt.so.1 (0x00007f2099820000)
libssl.so.1.1 => /usr/local/lib64/libssl.so.1.1 (0x00007f209978c000)
libcrypto.so.1.1 => /usr/local/lib64/libcrypto.so.1.1 (0x00007f2099400000)
libprotobuf-lite.so.3.19.4 => /opt/stonedb_v2/install/bin/../lib/private/libprotobuf-lite.so.3.19.4 (0x00007f20996ef000)
libmonetdbe.so.26 => /opt/stonedb_v2/install/bin/../lib/private/libmonetdbe.so.26 (0x00007f20993db000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f20996e8000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f20993d6000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f2099000000)
libm.so.6 => /lib64/libm.so.6 (0x00007f20992fb000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f20992e0000)
libc.so.6 => /lib64/libc.so.6 (0x00007f2098c00000)
libmonetdbsql.so.13 => /opt/stonedb_v2/install/lib64/libmonetdbsql.so.13 (0x00007f2098800000)
libmonetdb5.so.33 => /opt/stonedb_v2/install/lib64/libmonetdb5.so.33 (0x00007f2098400000)
libmapi.so.26 => /opt/stonedb_v2/install/lib64/libmapi.so.26 (0x00007f20992c1000)
libbat.so.26 => /opt/stonedb_v2/install/lib64/libbat.so.26 (0x00007f2097a00000)
libstream.so.26 => /opt/stonedb_v2/install/lib64/libstream.so.26 (0x00007f209929a000)
/lib64/ld-linux-x86-64.so.2 (0x00007f2099839000)
libbz2.so.1 => /lib64/libbz2.so.1 (0x00007f2099287000)
liblz4.so.1 => /lib64/liblz4.so.1 (0x00007f2099263000)
libsnappy.so.1 => /lib64/libsnappy.so.1 (0x00007f2097600000)
[mysql@rocky9 ~]$

可以看到二进制文件 mysqld 所有动态库依赖都能找到,其中一部分指向了 StoneDB 自带的库文件。

编辑配置文件 my.cnf

编辑配置文件 /etc/my.cnf,增加如下内容:

[client]
port = 3306
socket = /opt/stonedb_v2/install/data/mysql.sock

[mysqld]
port = 3306
server-id = 1
basedir = /opt/stonedb_v2/install/
datadir = /opt/stonedb_v2/install/data/
pid-file = /opt/stonedb_v2/install/data/mysqld.pid
socket = /opt/stonedb_v2/install/data/mysql.sock
tmpdir = /opt/stonedb_v2/install/tmp/
log-error = /opt/stonedb_v2/install/log/mysqld.log
slow-query-log = TRUE
slow-query-log-file = /opt/stonedb_v2/install/log/slow.log
log-bin = /opt/stonedb_v2/install/binlog/BINLOG

主要将文件路径指向到安装目录。

Q4: 帮助信息

在查看帮助信息时,遇到了 mysqld got signal 11 报错,报错信息如下:

[mysql@rocky9 ~]$ mysqld --verbose --help
mysqld Ver 8.0.33 for Linux on x86_64 (Source distribution)
BuildID[sha1]=150f81d4108e038e942961c325806ba3d1479f58
Copyright (c) 2000, 2023, StoneAtom and/or its affiliates.

StoneDB is a registered trademark of StoneAtom Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/stonedb_v2/install/etc/my.cnf ~/.my.cnf
The following groups are read: mysql_cluster mysqld server mysqld-8.0
The following options may be given as the first argument:
...
To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.
2023-12-05T14:57:05Z UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=150f81d4108e038e942961c325806ba3d1479f58
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x100000
/opt/stonedb_v2/install/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x236c4fe]
/opt/stonedb_v2/install/bin/mysqld(print_fatal_signal(int)+0x377) [0x1047637]
/opt/stonedb_v2/install/bin/mysqld(handle_fatal_signal+0x95) [0x10476d5]
/lib64/libc.so.6(+0x54db0) [0x7fecd2854db0]
/opt/stonedb_v2/install/bin/mysqld(Mondetdb_Adaptor::Mondetdb_Adaptor()+0x407) [0x1004f37]
/opt/stonedb_v2/install/bin/mysqld(Mondetdb_Adaptor::instance()+0x35) [0x10051c5]
/opt/stonedb_v2/install/bin/mysqld() [0xd957a4]
/opt/stonedb_v2/install/bin/mysqld() [0xd9c4f7]
/opt/stonedb_v2/install/bin/mysqld() [0xda3f98]
/opt/stonedb_v2/install/bin/mysqld(mysqld_main(int, char**)+0x2454) [0xdab194]
/lib64/libc.so.6(+0x3feb0) [0x7fecd283feb0]
/lib64/libc.so.6(__libc_start_main+0x80) [0x7fecd283ff60]
/opt/stonedb_v2/install/bin/mysqld(_start+0x2e) [0xd8a46e]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
[mysql@rocky9 ~]$

初始化 StoneDB

使用如下命令进行初始化:

mysqld --initialize-insecure

初始化成功后,日志显示如下。

[mysql@rocky9 log]$ cat mysqld.log
2023-12-05T15:18:55.368727Z 0 [System] [MY-013169] [Server] /opt/stonedb_v2/install/bin/mysqld (mysqld 8.0.33) initializing of server in progress as process 402224
2023-12-05T15:18:55.393904Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-05T15:19:07.595251Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-05T15:21:50.409230Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[mysql@rocky9 log]$

Q5:推荐配置

在文档 服务器推荐配置 中,建议开发、测试环境使用 2c+ 2g+ 配置,

可是,本文一开始使用的环境是 2c4g,初始化时只成功了一次,而且耗费较长时间,后面再试几次均未成功,而且尝试过程中,内存打满,swap被大量占用。

推荐配置已无法满足 StoneDB 8.0。

运行 StoneDB

启动数据库

mysqld_safe &

Q6: InnoDB 初始化

每次启动数据库时,日志中都会打印 InnoDB 初始化,不知道这里是哪个环节打印的信息。

2023-12-05T15:07:14.654886Z mysqld_safe Logging to '/opt/stonedb_v2/install/log/mysqld.log'.
2023-12-05T15:07:14.882598Z mysqld_safe Starting mysqld daemon with databases from /opt/stonedb_v2/install/data
2023-12-05T15:07:15.559446Z 0 [System] [MY-010116] [Server] /opt/stonedb_v2/install/bin/mysqld (mysqld 8.0.33) starting as process 48880
2023-12-05T15:07:15.597722Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-05T15:07:16.044637Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-05T15:07:19.543799Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-12-05T15:07:19.543948Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-12-05T15:07:19.647474Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2023-12-05T15:07:19.649315Z 0 [System] [MY-010931] [Server] /opt/stonedb_v2/install/bin/mysqld: ready for connections. Version: '8.0.33' socket: '/opt/stonedb_v2/install/data/mysql.sock' port: 3306 Source distribution.

简单 CRUD 测试

连接数据库,并进行常规 CRUD 测试。

[mysql@rocky9 log]$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.33 Source distribution

Copyright (c) 2000, 2023, StoneAtom and/or its affiliates.

StoneDB is a registered trademark of StoneAtom Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> s
--------------
mysql Ver 8.0.33 for Linux on x86_64 (Source distribution)

Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.33 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /opt/stonedb_v2/install/data/mysql.sock
Binary data as: Hexadecimal
Uptime: 1 min 35 sec

Threads: 2 Questions: 5 Slow queries: 0 Opens: 120 Flush tables: 3 Open tables: 36 Queries per second avg: 0.052
--------------

mysql> create schema s1;
Query OK, 1 row affected (0.02 sec)

mysql> use s1;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert t1 select 1;
ERROR 4164 (HY000): exec sql err: ParseException:SQLparser:42000!syntax error, unexpected IDENT, expecting INTO in: "insert t1"

mysql> insert into t1 select 1;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

这里遇到了语法解析异常,也印证 StoneDB 的语法解析部分已经和 MySQL 有所不同。

mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)

mysql> show create table t1G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> update t1 set id = 2 where 1=1;
ERROR 4164 (HY000): exec sql err: SQLException:sql.execute:25005!Current transaction is aborted (please ROLLBACK)

mysql> update t1 set id = 2 where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

这里再次遇到了 SQL 异常,更新语句条件带有 1=1 时,抛异常。

mysql> select * into outfile 't2.sql' from t2;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

尝试导出数据到文件时,提示需要开启 --secure-file-priv 选项,重启数据库。

[mysql@rocky9 log]$ mysqld --secure-file-priv=/tmp &

再次尝试导数。

mysql> select * into outfile '/tmp/t2.sql' from t2;
ERROR 3136 (42000): ParseException:SQLparser:42000!syntax error, unexpected STRING, expecting SCOLON in: "select * into outfile '/tmp/t2.sql'"

然鹅,依旧提示报错,提示语法错误,而且会生成空文件。

[root@rocky9 ~]# ll /tmp/t2.sql
-rw-r-----. 1 mysql mysql 0 Dec 5 23:24 /tmp/t2.sql

尝试几次无果,也没找到相关文档说明,暂时先这样。

mysql> SELECT id INTO OUTFILE '/tmp/t2.txt'
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY 'n'
-> FROM t2;
ERROR 3136 (42000): ParseException:SQLparser:42000!syntax error, unexpected STRING, expecting SCOLON in: "select id into outfile '/tmp/t2.txt'"

创建 FUNCTION ,第一次和第二次执行遇到的报错不同,去掉 CONCAT 之后,创建成功。

mysql> CREATE FUNCTION hello (s CHAR(20))
-> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
ERROR 4164 (HY000): exec sql err: SQLException:sql.execute:25005!Current transaction is aborted (please ROLLBACK)

mysql> CREATE FUNCTION hello (s CHAR(20))
-> RETURNS CHAR(50)
-> RETURN CONCAT('Hello, ',s,'!');
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
mysql>
mysql>
mysql> CREATE FUNCTION hello (s CHAR(20))
-> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
ERROR 4164 (HY000): exec sql err: ParseException:SQLparser:42000!SELECT: no such operator 'concat'(char(7), char(20), char(1))
mysql>
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN 'Hello!';
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT hello('');
+-----------+
| hello('') |
+-----------+
| Hello! |
+-----------+
1 row in set (0.00 sec)

存储引擎

StoneDB 5.7 中引入了自己的存储引擎 Tianmu。

在创建表时,您可以显式指定表的存储引擎,如创建表指定 engine=innodb。如果 StoneDB 采用主从架构部署,在 StoneDB 备库配置了 sql_mode 强制 tianmu 引擎的变量:MANDATORY_TIANMU。那么在主库创建时即使指定了 engine=innodb 的表,备库表结构同步也会自动变为engine=Tianmu。数据从主节点同步至从节点的过程中,会自动完成行列组织格式的转换。在优先级上,sql_mode =‘MANDATORY_TIANMU’ > default_storage_engine。[1]

但是,在 StoneDB 8.0 中默认的存储引擎为 InnoDB。

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)

而且找不到任何关于 Tianmu 的变量,无法创建 Tianmu 引擎的表。

mysql> show variables like 'tianmu_%';
Empty set (0.00 sec)
mysql> create table student(id int(11) primary key, name varchar(10)) engine=tianmu;
ERROR 1286 (42000): Unknown storage engine 'tianmu'

文档中没找到相关信息,个人推测是引入了 MonetDB 来取代 Tianmu 作为新的列存引擎。

Q7: 路径问题

有两个隐藏文件里,里面指向的路径不存在。

[mysql@rocky9 dbfarm]$ pwd
/opt/stonedb_v2/install/var/monetdb5/dbfarm
[mysql@rocky9 dbfarm]$ tail -n 2 .merovingian_properties
logfile=/var/opt/stonedb_v2/install/log/monetdb/merovingian.log
pidfile=/var/run/opt/stonedb_v2/install/monetdb/merovingian.pid
[mysql@rocky9 dbfarm]$

另外,文件 mysqlrouter-log-rotate 建议移动到 support-files 路径下。

[mysql@rocky9 install]$ pwd
/opt/stonedb_v2/install
[mysql@rocky9 install]$ ll mysqlrouter-log-rotate
-rw-r--r--. 1 mysql mysql 1623 Nov 30 14:36 mysqlrouter-log-rotate
[mysql@rocky9 install]$ tree support-files/
support-files/
├── mysqld_multi.server
├── mysql-log-rotate
└── mysql.server

0 directories, 3 files

总结

2c4g 着实有点难为 StoneDB 了,还是要多给点资源才能跑的动,后来在 8c16g 环境下才运行起来,只是内存使用接近 6g。

希望企业版的体验会更好,而不是叫企业版的原型。

StoneDB 的迭代速度还是蛮快的,期待下个版本。

  1. https://www.stoneatom.com/StonedbDocs?id=352 ↩︎

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论