Partitions not showing in MariaDB 10.3

toonces67's profile image toonces67 posted 4 years ago in General Permalink

We upgraded our MariaDB from version 10.1 to version 10.3. After doing so the Partitions tab in HeidiSQL does not show any valid partitions for the table. The CREATE code tab is missing the partition creates as well. The developer was using HeidiSQL version 9.4 and I am using version 10. portable of Heidi with the same result. Any advice?

ansgar's profile image ansgar posted 4 years ago Permalink

Is it a nightly build or the official 10.3 release?

Please also post the result of SHOW CREATE TABLE yourtable here, so I can try to reproduce.

toonces67's profile image toonces67 posted 4 years ago Permalink

Official release, I believe.

This was taken from MySQP Workbench as the table option at the end is not in HeidiSQL.

CREATE TABLE `PKG_INFO` (
  `PKG_INFO_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DAT_SRC_ID` varchar(20) NOT NULL,
  `PHMCY_ID` varchar(45) DEFAULT NULL,
  `SRC_FILL_ID` varchar(45) DEFAULT NULL,
  `FILL_TYP_CD` varchar(12) DEFAULT NULL,
  `PKG_NM` varchar(45) DEFAULT NULL,
  `CURR_PKG_STUS` varchar(45) DEFAULT NULL,
  `PKG_TYP` varchar(45) DEFAULT NULL,
  `PKG_CT` int(11) DEFAULT NULL,
  `NDC_ID` varchar(45) DEFAULT NULL,
  `FDB_MEDCN_ID` int(11) DEFAULT NULL,
  `PROD_ID` varchar(45) DEFAULT NULL,
  `DRUG_NM` mediumtext DEFAULT NULL,
  `LEGL_DRUG_CLS` varchar(45) DEFAULT NULL,
  `NB_OF_BUBL_TX` varchar(45) DEFAULT NULL,
  `PILL_PER_BUBL_TX` varchar(45) DEFAULT NULL,
  `NXT_PKG_STUS` varchar(45) DEFAULT NULL,
  `SRC_DLVRY_RTE_ID` varchar(45) DEFAULT NULL,
  `ORDR_RUN_TM` varchar(45) DEFAULT NULL,
  `ORDR_SHIP_DT` date DEFAULT NULL,
  `ORDR_RUN_TM_UTC` varchar(45) DEFAULT NULL,
  `ORDR_SHIP_DT_UTC` date DEFAULT NULL,
  `SUB_PKG_ID` varchar(25) DEFAULT NULL,
  `SRC_FILL_DTL_ID` varchar(25) DEFAULT NULL,
  `DLVRY_PRTY_CD` varchar(45) DEFAULT NULL,
  `FILL_MTHD_CD` varchar(45) DEFAULT NULL,
  `PKG_BTCH_ID` varchar(45) DEFAULT NULL,
  `NW_ADMT_IN` varchar(4) DEFAULT NULL,
  `DSPNG_SYS_IN` varchar(12) DEFAULT NULL,
  `HSC_DT` datetime DEFAULT NULL,
  `HSC_USR_ID` varchar(80) DEFAULT NULL,
  `HSU_DT` datetime DEFAULT NULL,
  `HSU_USR_ID` varchar(80) DEFAULT NULL,
  `ORDR_FILL_ID` int(11) DEFAULT NULL,
  `ARHVD_FLAG` char(1) DEFAULT NULL,
  `HSC_JOB_DT` varchar(150) DEFAULT NULL,
  `HSC_JOB_END_DT` datetime DEFAULT NULL,
  `SRC_HSC_DT` datetime DEFAULT NULL,
  `VER_NB` int(11) DEFAULT 0,
  PRIMARY KEY (`PKG_INFO_ID`,`DAT_SRC_ID`),
  UNIQUE KEY `IDX21_UNQ_DTL_FILL` (`DAT_SRC_ID`,`SRC_FILL_ID`,`SRC_FILL_DTL_ID`,`SUB_PKG_ID`),
  KEY `IDX1_ORDR_FILL` (`ORDR_FILL_ID`),
  KEY `IDX2_NXT_PKG_STUS` (`NXT_PKG_STUS`),
  KEY `PKG_INFO_IDX11` (`DLVRY_PRTY_CD`),
  KEY `PKG_INFO_IDX12` (`PHMCY_ID`),
  KEY `PKG_INFO_IDX13` (`DAT_SRC_ID`),
  KEY `PKG_INFO_IDX14` (`ORDR_SHIP_DT_UTC`),
  KEY `PKG_INFO_IDX15` (`ORDR_RUN_TM_UTC`),
  KEY `PKG_INFO_IDX16` (`SRC_DLVRY_RTE_ID`),
  KEY `PKG_INFO_IDX17` (`SRC_FILL_DTL_ID`),
  KEY `PKG_INFO_IDX18` (`SUB_PKG_ID`),
  KEY `PKG_INFO_IDX21` (`SRC_FILL_ID`),
  KEY `PKG_INFO_IDX22` (`FILL_TYP_CD`),
  KEY `PKG_INFO_IDX32` (`PKG_BTCH_ID`,`NW_ADMT_IN`),
  KEY `PKG_INFO_IDX31` (`LEGL_DRUG_CLS`),
  KEY `PKG_INFO_IDX33` (`PROD_ID`),
  KEY `IDX_SUMM_RPT` (`DAT_SRC_ID`,`PHMCY_ID`,`ORDR_SHIP_DT`,`ORDR_RUN_TM`,`SRC_DLVRY_RTE_ID`,`DLVRY_PRTY_CD`)
) ENGINE=InnoDB AUTO_INCREMENT=144532449 DEFAULT CHARSET=utf8
 PARTITION BY LINEAR KEY (`DAT_SRC_ID`)
PARTITIONS 50;
arkoko's profile image arkoko posted 3 years ago Permalink

Using HeidiSQL (version 11.3) with MariaDB 10.5 partitions does not shown as well. For example with the next SHOW CREATE TABLE:

CREATE TABLE `messages` (
  `time` datetime(6) NOT NULL DEFAULT current_timestamp(6),
  `file` int(10) unsigned DEFAULT NULL,
  `pos` int(10) unsigned NOT NULL,
  `src_ip` int(10) unsigned DEFAULT NULL,
  `src_port` smallint(5) unsigned DEFAULT NULL,
  `method` tinyint(4) DEFAULT NULL,
  `response` smallint(6) DEFAULT NULL,
  `ua` int(10) unsigned DEFAULT NULL,
  `uri` int(10) unsigned NOT NULL,
  `contact` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`time`,`pos`) USING BTREE,
  KEY `ix_messages_ua` (`ua`),
  KEY `ix_messages_contact` (`contact`),
  KEY `ix_messages_uri` (`uri`),
  KEY `ix_messages_ip` (`src_ip`) USING BTREE,
  KEY `response` (`response`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
 PARTITION BY RANGE  COLUMNS(`time`)
(PARTITION `2021_05_30` VALUES LESS THAN ('2021-05-31') ENGINE = InnoDB,
 PARTITION `2021_05_31` VALUES LESS THAN ('2021-06-01') ENGINE = InnoDB,
 PARTITION `2021_06_01` VALUES LESS THAN ('2021-06-02') ENGINE = InnoDB)
lozin's profile image lozin posted 2 years ago Permalink

Hi, is there anything known about this topic? I am using MariaDB 10.5.8 and HeidSQL version 11.3 (I also tested in NIGHTLY 1.3.0.6460)

arkoko's profile image arkoko posted 2 years ago Permalink

It seems, that nobody care about this bug or missed feature. Two issues are opened quite long time ago:

  • github.com / HeidiSQL/HeidiSQL/issues/187
  • github.com / HeidiSQL/HeidiSQL/issues/1521

I've switched to the DBeaver instead...

Mitchell Lee's profile image Mitchell Lee posted 2 years ago Permalink

Hi, ansgar. Is this fixed that partitioning showing? since Im considering that my db partitioning. Any progress?

ansgar's profile image ansgar posted 2 years ago Permalink

Not yet. Will check that.

ansgar's profile image ansgar posted 2 years ago Permalink

Next builds will have a better detection of partition clauses, which works on both MySQL and MariaDB. See issue #1521 for more details.

Issue #187 is for the bigger part of making partition support more complete.

Mitchell Lee's profile image Mitchell Lee posted 2 years ago Permalink

sound great!

Please login to leave a reply, or register at first.