Tidb: ORM and application Support

Created on 21 Sep 2015 · 30Comments · Source: pingcap/tidb

Python:

[ ] SQLAlchemy
[ ] Peewee
[ ] django
[ ] ponyorm

Go:
Orm:

[x] xorm: https://github.com/go-xorm/xorm
[x] beego: https://github.com/astaxie/beego/tree/master/orm
[x] go-sql-test: https://github.com/bradfitz/go-sql-test
[ ] upper.io: https://upper.io/db.v1/mysql

Application:

[x] Gogs: https://github.com/gogits/gogs
[ ] Dalga: https://github.com/cenkalti/dalga

Java：

[x] Mybatis
[ ] Jdbctemplate
[ ] Hibernate
[ ] Play

PHP:

[ ] phabricator: http://phabricator.org/
[x] phpmyadmin
[x] wordpress
[ ] yii: http://www.yiiframework.com/doc/guide/1.1/en/database.ar
[ ] discuz!: http://www.discuz.net/
[ ] drupal: https://www.drupal.org/
[ ] [Eloquent](https://laravel.com/docs/5.5/eloquent)

Dotnet:

[ ] [Entity Framework Core](https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql)

statuTODO statuWIP typcompatibility

Source

shenli

Most helpful comment

pls add django orm to the list

lotaku on 8 Sep 2017

👍2

All 30 comments

@shenli Any update ?

ngaut on 29 Mar 2016

I will go through the list tomorrow. @ngaut

shenli on 29 Mar 2016

sqlalchemy unit test:
25 failed, 404 passed, 51 skipped in 34.78 seconds
I will fix the failed cases.

shenli on 30 Mar 2016

pls add ponyorm for python to the list

catroot on 2 Feb 2017

Does tidb support sqlalchemy now?

I change my python application from mysql to tidb, it will report the error:
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1105, 'line 0 column 34 near " (allow_create_user IN (0, 1))" (total length 64)') [SQL: 'ALTER TABLE applications ADD CHECK (allow_create_user IN (0, 1))']

I know tidb doesn't support check constraint, but can let sqlalchemy ORM auto deal with tidb non-support features?

starplanet on 9 Jul 2017

👍1

pls add django orm to the list

lotaku on 8 Sep 2017

👍2

Hello, please add Eloquent from laravel, it a big player, deserve be on here

kocoten1992 on 24 Sep 2017

👍1

@kocoten1992 @lotaku @starplanet @catroot Thanks for your advice! I added them to the list.

shenli on 25 Sep 2017

please add entity framework core/https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql to the list

zaaack on 16 Oct 2017

@zaaack Done! Thanks!

shenli on 16 Oct 2017

@shenli Here is the error message from ado.net , maybe support " level read committed; start transaction;" is enough, although I only tried some very simple crud options.

DapperExtensions.Mapper.AutoClassMapper`1[T]
IsGenericTypeDefinition: true

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: line 1 column 25 near " level read committed; start transaction;" (total length 66) ---> MySql.Data.MySqlClient.MySqlException: line 1 column 25 near " level read committed; start transaction;" (total length 66)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at MySql.Data.MySqlClient.Results.ResultSet.<ReadResultSetHeaderAsync>d__1.MoveNext()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet)
   at MySql.Data.MySqlClient.MySqlDataReader.<ReadFirstResultSetAsync>d__65.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlDataReader.<CreateAsync>d__64.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.CommandExecutors.TextCommandExecutor.<ExecuteReaderAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.CommandExecutors.TextCommandExecutor.<ExecuteNonQueryAsync>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlConnection.<BeginDbTransactionAsync>d__7.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
   at DapperExtensions.Database.BeginTransaction(IsolationLevel isolationLevel)
   at ServerCode.Program.testDb() in /home/z/Projects/Strawberry/strawberry-backend/src/Server/Program.fs:line 64
   at ServerCode.Program.main(String[] args) in /home/z/Projects/Strawberry/strawberry-backend/src/Server/Program.fs:line 72

zaaack on 18 Oct 2017

@zaaack Thanks!

shenli on 20 Oct 2017

@zaaack May I have your tidb's version? You can get it by select tidb_version();.

shenli on 20 Oct 2017

@shenli

$ mysql -h 127.0.0.1 -P 4000 -u root --prompt="tidb> "
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.1-TiDB-0.9.0 MySQL Community Server (Apache License 2.0)

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

tidb> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: 0.9.0
Git Commit Hash: ca048c058297e6826f5eb975d3ff963942cd876a
Git Branch: master
UTC Build Time: 2017-10-05 02:40:50 |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

zaaack on 20 Oct 2017

Should each ORM open their own issue then link to this issue, cause thing gonna get messy real quick

kocoten1992 on 20 Oct 2017

@kocoten1992 Good advice, I just opend a new issue here: https://github.com/pingcap/tidb/issues/4840

zaaack on 20 Oct 2017

2017/12/13 04:55:55.933 session.go:732: [warning] [10] parse error:
line 1 column 60 near "" (total length 60)
ALTER TABLE `content_type` DROP COLUMN `name` CASCADE
2017/12/13 04:55:55.933 conn.go:437: [warning] [10] dispatch error:
id:10, addr:127.0.0.1:36496 status:2, collation:latin1_swedish_ci, user:root
"ALTER TABLE `content_type` DROP COLUMN `name` CASCADE"
line 1 column 60 near "" (total length 60)
/home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/parser/yy_parser.go:106:
/home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session.go:733:
/home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/conn.go:757:
2017/12/13 04:55:55.935 server.go:297: [info] [10] close connection

"ALTER TABLE table_name DROP COLUMN column_name CASCADE" isn`t supported yet?

catroot on 13 Dec 2017

@catroot Yes, we do not support it yet.

lamxTyler on 13 Dec 2017

@lamxTyler, is there in plans to implement this?

catroot on 13 Dec 2017

@catroot Currently, we do not have any plan to implement it.

lamxTyler on 13 Dec 2017

I'm using eloquent, the MySQL JSON format field value cannot query out, the same table and table structure works well on my local machine (MySQL 5.7.20), but it won't work on TiDB:

// TiDBModel < BaseModel < \Illuminate\Database\Eloquent\Model 
class TiTransactionPlus extends TiDBModel
{
    protected static $table_name = 'transaction_plus';

    public $incrementing = false;

    protected $primaryKey = 'trans_id';

    protected $guarded = [];
    protected $casts = [
        'trans_metadata' => 'array',
        'trans_extra' => 'array',
    ];
}

The trans_extra should print out as array, but it print out NULL in TiDB. If I change MySQL field format from JSON to varchar/text, it work.

phoenixg on 18 Dec 2017

@phoenixg Can you provide us the query SQL or code and your tidb version so that we can reproduce this problem?

lamxTyler on 18 Dec 2017

@lamxTyler

TiDB version:
Release Version: 1.0.0
Git Commit Hash: 1a175b562bc679694f606c336131a9373064b324
Git Commit Branch: release-1.0
UTC Build Time: 2017-10-19 02:38:02

SQL code:
I use TiTransactionPlus::db()->getQueryLog() to print out SQL executed:

select * from transaction_plus where trans_id = ? limit 1;

It's totally OK in MySQL client(Sequel Pro is OK), so I think it's framework compatibility problem. Here is my composer.json dependency:

"require": {
        "php": ">=5.6.0",
        "illuminate/database": "~5.2.37",
        "illuminate/events": "~5.2.37",
        "illuminate/pagination": "~5.2.37",
        "doctrine/dbal": "~2.5"
    }

phoenixg on 19 Dec 2017

@phoenixg Is it correct if we do not cast the trans_extra? Can you give an example of trans_extra?

lamxTyler on 19 Dec 2017

@lamxTyler Not correct if I don't cast the trans_extra, the value is any JSON data, eg:

{"user":"[email protected]"}

phoenixg on 20 Dec 2017

@phoenixg I think maybe there is something wrong when you insert the JSON. Can the SQL generated by framework be inserted into tidb successfully? Can the inserted value be queried out successfully? If not, please provide an example of the SQL.

lamxTyler on 20 Dec 2017

I've created a small library that extends the Eloquent's MySQL connection class and should improve compatibility with Laravel:
https://packagist.org/packages/evvo/tidb-laravel

Migrations should be working now.

evvo on 11 Nov 2018

@evvo Cool!

shenli on 11 Nov 2018

https://github.com/bitnami/bitnami-docker-phabricator/issues/123

It looks like tidb can't be used in phabricator

Xuanwo on 26 Jul 2019

I am going to close this issue as stale. Compatibility has improved a lot in the last two years, and if there are any specific problems discovered, it is best to create a specific issue.