New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MariaDB integer field can not be set to 0 by default,and the comment can not be set successfully #68

Closed
ajisheng opened this Issue Jan 3, 2018 · 11 comments

Comments

Projects
None yet
3 participants
@ajisheng

ajisheng commented Jan 3, 2018

MariaDB integer field can not be set to 0 by default,and the comment can not be set successfully.
My MariaDB version is 10.2.8
My HeidiSQL version is 9.5.0.5209

@ansgarbecker

This comment has been minimized.

Show comment
Hide comment
@ansgarbecker

ansgarbecker Jan 3, 2018

Collaborator

The issue template should not be just removed, but filled out :)
You could at least explain what's exactly happening when you try to set a default "0". For example whether there is some error message.

Collaborator

ansgarbecker commented Jan 3, 2018

The issue template should not be just removed, but filled out :)
You could at least explain what's exactly happening when you try to set a default "0". For example whether there is some error message.

@ajisheng

This comment has been minimized.

Show comment
Hide comment
@ajisheng

ajisheng Jan 5, 2018

heidisql
phpmyadmin

The same table in phpmyadmin and heidisql difference
Integer field
When set to 0 by default, it will be converted to null
When default is null the comment is displayed
When default is 0 comments are not displayed

And the timestamp type is also affected

ajisheng commented Jan 5, 2018

heidisql
phpmyadmin

The same table in phpmyadmin and heidisql difference
Integer field
When set to 0 by default, it will be converted to null
When default is null the comment is displayed
When default is 0 comments are not displayed

And the timestamp type is also affected

@ansgarbecker

This comment has been minimized.

Show comment
Hide comment
@ansgarbecker

ansgarbecker Jan 5, 2018

Collaborator

Thanks for the self-explanatory screenshots!

I have difficulties to reproduce that though. Could you post the result of SHOW CREATE TABLE ds_department code here?

Collaborator

ansgarbecker commented Jan 5, 2018

Thanks for the self-explanatory screenshots!

I have difficulties to reproduce that though. Could you post the result of SHOW CREATE TABLE ds_department code here?

@ajisheng

This comment has been minimized.

Show comment
Hide comment
@ajisheng

ajisheng Jan 6, 2018

CREATE TABLE `ds_department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `org_id` int(11) DEFAULT 0 COMMENT '从属学校/机构的id',
  `parent_id` int(11) DEFAULT 0 COMMENT '上级部门id',
  `chief_userid` int(11) DEFAULT 0 COMMENT '负责人userid',
  `deputy_userid` int(11) DEFAULT 0 COMMENT '副负责人userid',
  `building_id` int(11) DEFAULT 0 COMMENT '楼宇id',
  `floor_id` int(11) DEFAULT 0 COMMENT '楼层id',
  `room_id` int(11) DEFAULT 0 COMMENT '房间id',
  `tel` varchar(15) DEFAULT '' COMMENT '电话号',
  `tel_ext` int(6) DEFAULT NULL COMMENT '电话分机',
  `fax` varchar(15) DEFAULT NULL COMMENT '传真',
  `fax_ext` int(6) DEFAULT NULL COMMENT '传真分机',
  `remark` varchar(200) DEFAULT '' COMMENT '备注',
  `create_time` timestamp NULL DEFAULT current_timestamp() COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp() COMMENT '更新时间',
  `create_userid` int(11) DEFAULT 0 COMMENT '创建人userid',
  `create_ip` char(15) DEFAULT '' COMMENT '创建ip',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='部门表';

ajisheng commented Jan 6, 2018

CREATE TABLE `ds_department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `org_id` int(11) DEFAULT 0 COMMENT '从属学校/机构的id',
  `parent_id` int(11) DEFAULT 0 COMMENT '上级部门id',
  `chief_userid` int(11) DEFAULT 0 COMMENT '负责人userid',
  `deputy_userid` int(11) DEFAULT 0 COMMENT '副负责人userid',
  `building_id` int(11) DEFAULT 0 COMMENT '楼宇id',
  `floor_id` int(11) DEFAULT 0 COMMENT '楼层id',
  `room_id` int(11) DEFAULT 0 COMMENT '房间id',
  `tel` varchar(15) DEFAULT '' COMMENT '电话号',
  `tel_ext` int(6) DEFAULT NULL COMMENT '电话分机',
  `fax` varchar(15) DEFAULT NULL COMMENT '传真',
  `fax_ext` int(6) DEFAULT NULL COMMENT '传真分机',
  `remark` varchar(200) DEFAULT '' COMMENT '备注',
  `create_time` timestamp NULL DEFAULT current_timestamp() COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp() COMMENT '更新时间',
  `create_userid` int(11) DEFAULT 0 COMMENT '创建人userid',
  `create_ip` char(15) DEFAULT '' COMMENT '创建ip',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='部门表';

@ansgarbecker

This comment has been minimized.

Show comment
Hide comment
@ansgarbecker

ansgarbecker Jan 6, 2018

Collaborator

After copying your CREATE code and running it on my local mysql 5.7.9 server, I can see all column comments:
table-editor-chinese-comments

Probably this issue is related to some charset setting on your side. Could you please fire the following query on your server and post the results here (rightclick -> export grid rows -> copy to clipboard in markdown format):

SHOW VARIABLES LIKE '%character_set%'

Here are my results for the that query:

Variable_name Value
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8
character_set_filesystem binary
character_set_results utf8mb4
character_set_server latin1
character_set_system utf8
character_sets_dir C:\Wamp\mysql-5.7.9-winx64\share\charsets\
Collaborator

ansgarbecker commented Jan 6, 2018

After copying your CREATE code and running it on my local mysql 5.7.9 server, I can see all column comments:
table-editor-chinese-comments

Probably this issue is related to some charset setting on your side. Could you please fire the following query on your server and post the results here (rightclick -> export grid rows -> copy to clipboard in markdown format):

SHOW VARIABLES LIKE '%character_set%'

Here are my results for the that query:

Variable_name Value
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8
character_set_filesystem binary
character_set_results utf8mb4
character_set_server latin1
character_set_system utf8
character_sets_dir C:\Wamp\mysql-5.7.9-winx64\share\charsets\
@Gemorroj

This comment has been minimized.

Show comment
Hide comment
@Gemorroj

Gemorroj Jan 6, 2018

Maybe problem reproduced only in mariadb
I use mariadb 10.2.12
Hidesql 9.0.5.5196 / 9.0.5.5217 (beta)

SHOW CREATE TABLE orders;

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `facebook_id` bigint(20) unsigned NOT NULL COMMENT 'Facebook ID',
  `date_create` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Дата создания записи',
  `currency` char(3) NOT NULL COMMENT 'Валюта. Справочник ISO 4217',
  `total_cost` decimal(12,2) unsigned NOT NULL COMMENT 'Общая стоимость заказа',
  `is_legal_person` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Юридическое лицо',
  `attributes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT 'Произвольные атрибуты в JSON',
  PRIMARY KEY (`id`),
  CONSTRAINT `orders_attributes_json_valid` CHECK (json_valid(`attributes`))
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='Заказы'

hidesql
character

Gemorroj commented Jan 6, 2018

Maybe problem reproduced only in mariadb
I use mariadb 10.2.12
Hidesql 9.0.5.5196 / 9.0.5.5217 (beta)

SHOW CREATE TABLE orders;

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `facebook_id` bigint(20) unsigned NOT NULL COMMENT 'Facebook ID',
  `date_create` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Дата создания записи',
  `currency` char(3) NOT NULL COMMENT 'Валюта. Справочник ISO 4217',
  `total_cost` decimal(12,2) unsigned NOT NULL COMMENT 'Общая стоимость заказа',
  `is_legal_person` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Юридическое лицо',
  `attributes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT 'Произвольные атрибуты в JSON',
  PRIMARY KEY (`id`),
  CONSTRAINT `orders_attributes_json_valid` CHECK (json_valid(`attributes`))
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='Заказы'

hidesql
character

@ajisheng

This comment has been minimized.

Show comment
Hide comment
@ajisheng

ajisheng Jan 8, 2018

Only exists in MariaDB,you can try use MariaDB.

ajisheng commented Jan 8, 2018

Only exists in MariaDB,you can try use MariaDB.

@ansgarbecker ansgarbecker removed the question label Jan 8, 2018

@ansgarbecker

This comment has been minimized.

Show comment
Hide comment
@ansgarbecker

ansgarbecker Jan 8, 2018

Collaborator

I can finally reproduce that, on MariaDB 10.2.12 - curious

Collaborator

ansgarbecker commented Jan 8, 2018

I can finally reproduce that, on MariaDB 10.2.12 - curious

@ansgarbecker

This comment has been minimized.

Show comment
Hide comment
@ansgarbecker

ansgarbecker Jan 8, 2018

Collaborator

Should work now. Please check that in the new nightly build.

Collaborator

ansgarbecker commented Jan 8, 2018

Should work now. Please check that in the new nightly build.

@Gemorroj

This comment has been minimized.

Show comment
Hide comment
@Gemorroj

Gemorroj Jan 8, 2018

HeidiSQL 9.5.0.5219 works well on my case.
Except that doesn't work CONSTRAINT. see #75

Gemorroj commented Jan 8, 2018

HeidiSQL 9.5.0.5219 works well on my case.
Except that doesn't work CONSTRAINT. see #75

@ajisheng

This comment has been minimized.

Show comment
Hide comment
@ajisheng

ajisheng Jan 9, 2018

HeidiSQL 9.5.0.5219,The problem has been solved, thank you very much!

ajisheng commented Jan 9, 2018

HeidiSQL 9.5.0.5219,The problem has been solved, thank you very much!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment