StoneDB is an Open-Source MySQL HTAP and MySQL-Native DataBase for OLTP, Real-Time Analytics, a counterpart of MySQLHeatWave. (https://stonedb.io)
When updating tianmu, records with primary key conflicts will be skipped and subsequent update operations will be performed. For example:
CREATE TABLE t1 (id int(11) NOT NULL auto_increment, parent_id int(11) DEFAULT '0' NOT NULL, level tinyint(4)
DEFAULT '0' NOT NULL, PRIMARY KEY (id)) engine=tianmu;
INSERT INTO t1 VALUES (3,1,1),(4,1,1);
Executing the update ignore t1 set id=id+1; statement will ignore the update of PK=3, because the updated primary key will conflict with PK=4. Then continue to execute the update of pk=4, and the updated PK=5.
mysql> CREATE TABLE t1 (id int(11) NOT NULL auto_increment, parent_id int(11) DEFAULT '0' NOT NULL, level tinyint(4)
-> DEFAULT '0' NOT NULL, PRIMARY KEY (id)) engine=tianmu;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (3,1,1),(4,1,1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update t1 set id=id+1;
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
mysql> select * from t1;
+----+-----------+-------+
| id | parent_id | level |
+----+-----------+-------+
| 3 | 1 | 1 |
| 4 | 1 | 1 |
+----+-----------+-------+
2 rows in set (0.00 sec)
mysql> update ignore t1 set id=id+1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t1;
+----+-----------+-------+
| id | parent_id | level |
+----+-----------+-------+
| 3 | 1 | 1 |
| 5 | 1 | 1 |
+----+-----------+-------+
2 rows in set (0.00 sec)
When stonedb is used as the primary database, the load statement will be executed on the backup database in the form of “insert into”.
mysql> select GROUP_CONCAT(t.id) from sequence t;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(t.id) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 3000000000010000,3000000000010001,3000000000010002,3000000000010003,3000000000010004,3000000000010005,3000000000010006,3000000000010007,3000000000010008,3000000000010009,3000000000010010 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select id from tt union all select 2222 c1 from dual;
+------+
| id |
+------+
| 1111 |
| 2222 |
+------+
mysql> select id from tt union all select 2222 ;
+------+
| id |
+------+
| 1111 |
| 2222 |
+------+
-- PS：select 111(from dual) appears in a position other than that of the first clause
group_concat function in tianmu by @RingsC in https://github.com/stoneatom/stonedb/pull/1852
Full Changelog: https://github.com/stoneatom/stonedb/compare/5.7-v1.0.3-GA...5.7-v1.0.4-alpha
.gitignore by @Nliver in https://github.com/stoneatom/stonedb/pull/1789
Full Changelog: https://github.com/stoneatom/stonedb/compare/8.0-v1.0.1-alpha...8.0-v1.0.1-beta
nproc by @zsp108 in https://github.com/stoneatom/stonedb/pull/677
order by command (#715) by @DandreChen in https://github.com/stoneatom/stonedb/pull/716
group by command to MySQL8.0.(#718) by @DandreChen in https://github.com/stoneatom/stonedb/pull/721
Full Changelog: https://github.com/stoneatom/stonedb/compare/5.7-v1.0.1-GA...8.0-v1.0.1-alpha
Compared to 1.0.2, StoneDB 1.0.3 has done many feature improvements and bug fixes, mainly including：
Syntax:
## At global level:
mysql>set global sql_mode='NO_KEY_ERROR';
## At session level:
mysql>set session sql_mode='NO_KEY_ERROR';
## Configuration file my.cnf:
[mysqld]
sql_mode='NO_KEY_ERROR'
Better adapted to the ecosystem to display the version number of StoneDB.
Improved the primary/secondary synchronization performance. #1213
The following bugs are fixed:
ALTER TABLE statement is executed to add a TIMESTAMP field. #1327
ALTER TABLE statement. #1253
ALTER TABLE statement is executed to change the data type of a field. #752
Full Changelog: https://github.com/stoneatom/stonedb/compare/5.7-v1.0.2-GA...5.7-v1.0.3-GA
Compared to 1.0.2, StoneDB 1.0.3 has done many feature improvements and bug fixes, mainly including：
Syntax:
## At global level:
mysql>set global sql_mode='NO_KEY_ERROR';
## At session level:
mysql>set session sql_mode='NO_KEY_ERROR';
## Configuration file my.cnf:
[mysqld]
sql_mode='NO_KEY_ERROR'
Better adapted to the ecosystem to display the version number of StoneDB.
Improved the primary/secondary synchronization performance. #1213
The following bugs are fixed:
ALTER TABLE statement is executed to add a TIMESTAMP field. #1327
ALTER TABLE statement. #1253
ALTER TABLE statement is executed to change the data type of a field. #752
Full Changelog: https://github.com/stoneatom/stonedb/compare/5.7-v1.0.2-GA...5.7-v1.0.3-beta
Release date： January 15,2023
# Global level
mysql>set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,MANDATORY_TIANMU';
# Session level
mysql>set session sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,MANDATORY_TIANMU';
#Set my.cnf parameter file
[mysqld]
sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,MANDATORY_TIANMU'
The following bugs are fixed：
Using the Shell script for rapid deployment of StoneDB as an analytical database for MySQL, parameter sql_mode default add value of MANDATORY_TIANMU to enable the mandatory TIANMU engine
timestamp union timestamp with UNION of non-matching columns (#880) by @isredstar in https://github.com/stoneatom/stonedb/pull/1010
Full Changelog: https://github.com/stoneatom/stonedb/compare/5.7-v1.0.1-GA...5.7-v1.0.2-GA
Changes in StoneDB_5.7_v1.0.2 (2023-01-14, Release Candidate)
Big features:
User-Defined Functions(UDFs) to handle the dynamic SQL:create function function_name();
create function function_name(args...);
create function function_name(args...) returns varchar(50)
begin
SET @sql= select * from where id= args;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
end $$
ESCAPE Keyword;% :
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE '';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n';
_ :
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\_' ESCAPE '\\';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan_' ESCAPE '';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n_' ESCAPE '\n';
primary key and index:
Add primary keyALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARYKEY;
CREATE TABLE `table_name` (`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主表id',`name` string NOT NULL COMMENT '名称') ;
Drop primary key
DROP INDEX PRIMARYON t;
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
Add index
ALTER TABLE tbl_name ADD {FULLTEXT|SPATIAL} [INDEX|KEY][index_name]
CREATE TABLE t1 (age INT, INDEX USING BTREE (age)) ENGINE = Tianmu;
Drop index
DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
Rename index
ALTER TABLE tbl_name RENAME {INDEX|KEY} old_index_nameTOnew_index_name
Change character set of table:
ALTER TABLE tablename convert to character set utf8;
Change character set of table field:
ALTER TABLE tablename MODIFY latin1_text_col TEXT CHARACTERSET utf8;
ALTER TABLE tablename MODIFY latin1_varchar_col VARCHAR(M) CHARACTERSET utf8;
ALTER TABLE tablename MODIFY latin1_text_col CHAR CHARACTERSET utf8;
ALTER TABLE tablename CHANGE colname colname TEXT CHARACT
ER SET utf8;
ALTER TABLE tablename CHANGE colname colname VARCHAR(M) CACTER SET utf8;
ALTER TABLE tablename CHANGE colname colname CHAR CHARACTER SET utf8;
BIT Data Type:CREATE TABLE tablename(colname BIT(8));
ALTER TABLE tablename add colname BIT(8) comment '';
ALTER TABLE tablename modify column colname BIT(4);
ALTER TABLE tablename modify column colname varchar(20) ;
replace into SQL Syntax；REPLACE INTO tablename VALUES(1,'new','2013-07-22 10:30:00');
REPLACE INTO tablename VALUES(1,'Old','2014-08-20 18:47:00');
unsigned and zerofill:CREATE TABLE tablename (colname int(4) unsigned zerofill);
ALTER TABLE tablename change column colname colname int(4) unsigned zerofill;
ALTER TABLE tablename change column colname colname int(4);
set global sql_mode='MANDATORY_TIANMU';
session
set sql_mode='MANDATORY_TIANMU';
my.cnf
[mysqld]
sql_mode ='MANDATORY_TIANMU'
startup script
./mysqld --sql-mode='MANDATORY_TIANMU'
Accessibility Notes
Docs: The manual has been updated as the code was modified. ( #doc)
Changes in StoneDB_5.7_v1.0.1 (2022-10-24, General Availability) ● Functionality Added or Changed ● Compilation Notes ● Document Notes ● Bugs Fixed Functionality Added or Changed ● Tianmu: From StoneDB_5.7 v1.0.1, you can use delete statement to clear some data you don't need.
delete from table1;
delete from table1, table2, ...;
delete from table1 where ...;
delete from table1, table2, ... where ...;
● Tianmu: From StoneDB_5.7 v1.0.1, you can use alter table statement to modify the table structure as you need.
alter table tablename
● Tianmu: Binlog replication supported ROW format；
binlog_format = ROW
● Tianmu: Added temporary table function；
CREATE TEMPORARY TABLE IF NOT EXISTS tablename
● Tianmu: From StoneDB_5.7 v1.0.1, you can create a trigger. The trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.
create trigger triggername
● Tianmu: Added Create table AS... union... statement； ● Tianmu: The Tianmu engine improved the performance of subqueries； ● Tianmu: Added gtest module； ● Tianmu: Added some mtr test cases； Compilation Notes ● Added cmake parameter configuration for build
cmake .. -DWITH_MARISA -DWITH_ROCKSDB
Document Notes ● The manual has been updated as the code was modified. ( # address) Bugs Fixed ● fix some inherited mtr from MySQL ● fix Tianmu bug: #282,#274,#270,#663,#669,#670,#675,#678,#682,#487,#426,#250,#247,#569,#566,#290,#736,#567,#500,#300,#289,#566,#279,#570,#571,#580,#581,#586,#589,#674,#646,#280,#301,#733 et. al.
WITH_ROCKSDB and WITH_MARISA (#12) by @RingsC in https://github.com/stoneatom/stonedb/pull/377
Full Changelog: https://github.com/stoneatom/stonedb/compare/5.7-v1.0.0-beta...5.7-v1.0.1-GA
Changes in StoneDB_5.7_v1.0.0 (2022-08-31, General Availability)
Support for MySQL 5.7
Functionality Added or Changed
mysql> show engines;
+----------------+---------+--------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+----------------+---------+--------------------------+--------------+------+------------+
| TIANMU | DEFAULT | Tianmu storage engine | YES | NO | NO |
+----------------+---------+--------------------------+--------------+------+------------+
Tianmu: Improved the aggregation capabilities of the decimal data type.
Tianmu: Improved the readability of code. The code does not spererate logically, or the variables name can not be understooed by the literal meaning. To refactor code make it more readable to anyone who are the first to read that. For example: Changes int DoGetSomething(); to int GetSomethingXXX();, int GetNoNulls() to int GetNumOfNulls().
Tianmu: The date-related functions (such as DATE_ADD, DATE_SUB) can be queried (DATE_ADD|DATE_SUB) when creating view using the date func.(BUG #342)
Compilation Notes
The version of the Boost library for server builds is now 1.66.0.
The version of the Rocksdb for server builds is now 6.12.6.
Configuration Notes
Important Change: Changed default config file stonedb.cnf to my.cnf. (feature #182)
Important Change: Use tianmu as the default storage engine. (feature #255)
Document Notes
Bugs Fixed