[MCOL-1654] Querystats table is broken Created: 2018-08-16  Updated: 2019-01-31  Resolved: 2019-01-31

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.0.14, 1.1.5
Fix Version/s: 1.0.16, 1.1.7

Type: Bug Priority: Major
Reporter: Andrew Hutchings (Inactive) Assignee: Zdravelina Sokolovska (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MCOL-1666 Comparisation with non latin chars in... Closed
Duplicate
duplicates MCOL-1141 command not found logged during insta... Closed
Sprint: 2018-20, 2018-21, 2019-01

 Description   

The querystats table doesn't get installed, this is because dbcon/mysql/install_calpont_mysql.sh which tries to install it using a shell command has "rows" in backticks. This causes BASH to try and execute a command called "rows" which causes an error.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-09-06 ]

Workaround:

Create the table manually by doing:

CREATE TABLE IF NOT EXISTS infinidb_querystats.querystats
 
(
 
  queryID bigint NOT NULL AUTO_INCREMENT,
 
  sessionID bigint DEFAULT NULL,
 
  host varchar(50),
 
  user varchar(50),
 
  priority char(20),
 
  queryType char(25),
 
  query varchar(8000),
 
  startTime timestamp NOT NULL,
 
  endTime timestamp NOT NULL,
 
  `rows` bigint,
 
  errno int,
 
  phyIO bigint,
 
  cacheIO bigint,
 
  blocksTouched bigint,
 
  CPBlocksSkipped bigint,
 
  msgInUM bigint,
 
  msgOutUm bigint,
 
  maxMemPct int,
 
  blocksChanged bigint,
 
  numTempFiles bigint,
 
  tempFileSpace bigint,
 
  PRIMARY KEY (queryID)
 
);

Comment by Patrick LeBlanc (Inactive) [ 2018-11-05 ]

Looks like escaping the backticks works. will check that in soon.

Comment by Patrick LeBlanc (Inactive) [ 2018-11-05 ]

The way to verify this works is, after a fresh install, there should be an infinidb.querystats table. I think it was not being created at all before this.

Comment by Zdravelina Sokolovska (Inactive) [ 2018-11-06 ]

checked on v1.2.1-1 after pure install

MariaDB [(none)]> show variables like '%version%' ;
-------------------------------------------------------------------------+

Variable_name Value

-------------------------------------------------------------------------+

innodb_version 10.3.10
protocol_version 10
slave_type_conversions  
system_versioning_alter_history ERROR
system_versioning_asof DEFAULT
version 10.3.10-MariaDB-log
version_comment Columnstore 1.2.1-1
version_compile_machine x86_64
version_compile_os Linux
version_malloc_library system
version_source_revision f74223b8b4e0c1cb79d76dfee128c021b51ad51a
version_ssl_library OpenSSL 1.0.2k-fips 26 Jan 2017

-------------------------------------------------------------------------+
12 rows in set (0.001 sec)

MariaDB [(none)]> show databases ;
---------------------

Database

---------------------

calpontsys
columnstore_info
infinidb_querystats
infinidb_vtable
information_schema
mysql
performance_schema
test

---------------------
8 rows in set (0.000 sec)

MariaDB [(none)]> use infinidb_querystats ;
Database changed
MariaDB [infinidb_querystats]> show tables ;
-------------------------------

Tables_in_infinidb_querystats

-------------------------------

priority
querystats
user_priority

-------------------------------
3 rows in set (0.000 sec)

MariaDB [infinidb_querystats]> show create table infinidb_querystats.querystats ;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table Create Table

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

querystats CREATE TABLE `querystats` (
`queryID` bigint(20) NOT NULL AUTO_INCREMENT,
`sessionID` bigint(20) DEFAULT NULL,
`host` varchar(50) DEFAULT NULL,
`user` varchar(50) DEFAULT NULL,
`priority` char(20) DEFAULT NULL,
`queryType` char(25) DEFAULT NULL,
`query` varchar(8000) DEFAULT NULL,
`startTime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`endTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`rows` bigint(20) DEFAULT NULL,
`errno` int(11) DEFAULT NULL,
`phyIO` bigint(20) DEFAULT NULL,
`cacheIO` bigint(20) DEFAULT NULL,
`blocksTouched` bigint(20) DEFAULT NULL,
`CPBlocksSkipped` bigint(20) DEFAULT NULL,
`msgInUM` bigint(20) DEFAULT NULL,
`msgOutUm` bigint(20) DEFAULT NULL,
`maxMemPct` int(11) DEFAULT NULL,
`blocksChanged` bigint(20) DEFAULT NULL,
`numTempFiles` bigint(20) DEFAULT NULL,
`tempFileSpace` bigint(20) DEFAULT NULL,
PRIMARY KEY (`queryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

MariaDB [infinidb_querystats]>

Comment by Zdravelina Sokolovska (Inactive) [ 2018-11-09 ]

checked on 1.2.1-1;
todo: check on v1.1.7, 1.0.16

Comment by Zdravelina Sokolovska (Inactive) [ 2019-01-22 ]

infinidb_querystats.querystats is not created on 1.1.7

root@columnstore ~]# mcsadmin getSoftwareInfo
 
getsoftwareinfo   Tue Jan 22 14:59:12 2019
 
 
 
Name        : mariadb-columnstore-platform
 
Version     : 1.1.7
 
Release     : 1
 
Architecture: x86_64
 
Install Date: Tue 22 Jan 2019 02:44:16 PM EET
 
Group       : Applications/Databases
 
Size        : 113542903
 
License     : Copyright (c) 2016 MariaDB Corporation Ab., all rights reserved; redistributable under the terms of the GPL, see the file COPYING for details.
 
Signature   : (none)
 
Source RPM  : mariadb-columnstore-platform-1.1.7-1.src.rpm
 
Build Date  : Mon 17 Sep 2018 07:34:57 PM EEST
 
Build Host  : ip-10-0-0-7.ec2.internal
 
Relocations : (not relocatable)
 
Vendor      : MariaDB Corporation Ab
 
URL         : http://mariadb.org
 
Summary     : MariaDB-Columnstore software
 
Description :
 
MariaDB Columnstore: a very fast and robust SQL database server
 



MariaDB [(none)]> show tables from infinidb_querystats;
 
+-------------------------------+
 
| Tables_in_infinidb_querystats |
 
+-------------------------------+
 
| priority                      |
 
| user_priority                 |
 
+-------------------------------+
 



/usr/local/mariadb/columnstore/mysql/install_calpont_mysql.sh: line 27: rows: command not found
 
ERROR 1305 (42000) at line 20: FUNCTION mysql.caldisablepartitions does not exist
 
ERROR 1305 (42000) at line 21: FUNCTION mysql.calenablepartitions does not exist
 
ERROR 1305 (42000) at line 22: FUNCTION mysql.caldroppartitions does not exist
 
ERROR 1305 (42000) at line 23: FUNCTION mysql.calshowpartitions does not exist
 
ERROR 1305 (42000) at line 24: FUNCTION mysql.caldroppartitionsbyvalue does not exist
 
ERROR 1305 (42000) at line 25: FUNCTION mysql.caldisablepartitionsbyvalue does not exist
 
ERROR 1305 (42000) at line 26: FUNCTION mysql.calenablepartitionsbyvalue does not exist
 
ERROR 1305 (42000) at line 27: FUNCTION mysql.calshowpartitionsbyvalue does not exist
 
ERROR 1064 (42000) at line 63: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'bigint,
 
  errno int,
 
  phyIO bigint,
 
  cacheIO bigint,
 
  blocksTouched bigint,
 
 ' at line 12

Comment by Patrick LeBlanc (Inactive) [ 2019-01-22 ]

Zdravelina, could you attach the engine commit id you tested? I see the fix in develop-1.0 and develop-1.1, and it is the same as in develop-1.2 which works. If this fix works in one it should work in all.

Comment by Andrew Hutchings (Inactive) [ 2019-01-22 ]

winstone you are using an ancient build of 1.1.7, from September. That won't have the fix in. Please try a recent build.

Comment by Zdravelina Sokolovska (Inactive) [ 2019-01-22 ]

rechecked with the latest dev 1.1.7 build and pure install 

getsoftwareinfo   Tue Jan 22 16:12:26 2019
 
 
 
Name        : mariadb-columnstore-platform
 
Version     : 1.1.7
 
Release     : 1
 
Architecture: x86_64
 
Install Date: Tue 22 Jan 2019 03:51:40 PM EET
 
Group       : Applications/Databases
 
Size        : 113742950
 
License     : Copyright (c) 2016 MariaDB Corporation Ab., all rights reserved; redistributable under the terms of the GPL, see the file COPYING for details.
 
Signature   : (none)
 
Source RPM  : mariadb-columnstore-platform-1.1.7-1.src.rpm
 
Build Date  : Thu 17 Jan 2019 06:16:39 AM EET
 
Build Host  : ip-10-0-0-129.ec2.internal
 
Relocations : (not relocatable)
 
Vendor      : MariaDB Corporation Ab
 
URL         : http://mariadb.org
 
Summary     : MariaDB-Columnstore software
 
Description :
 
MariaDB Columnstore: a very fast and robust SQL database server
 
 
 
It is GPL v2 licensed, which means you can use the it free of charge under the
 
conditions of the GNU General Public License Version 2 (http://www.gnu.org/licenses/).
 
 
 
MariaDB documentation can be found at https://mariadb.com/kb
 
MariaDB bug reports should be submitted through https://jira.mariadb.org
 



 
 
MariaDB [(none)]> show create table infinidb_querystats.querystats ;
 
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
 
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| querystats | CREATE TABLE `querystats` (
 
  `queryID` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `sessionID` bigint(20) DEFAULT NULL,
 
  `host` varchar(50) DEFAULT NULL,
 
  `user` varchar(50) DEFAULT NULL,
 
  `priority` char(20) DEFAULT NULL,
 
  `queryType` char(25) DEFAULT NULL,
 
  `query` varchar(8000) DEFAULT NULL,
 
  `startTime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  `endTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `rows` bigint(20) DEFAULT NULL,
 
  `errno` int(11) DEFAULT NULL,
 
  `phyIO` bigint(20) DEFAULT NULL,
 
  `cacheIO` bigint(20) DEFAULT NULL,
 
  `blocksTouched` bigint(20) DEFAULT NULL,
 
  `CPBlocksSkipped` bigint(20) DEFAULT NULL,
 
  `msgInUM` bigint(20) DEFAULT NULL,
 
  `msgOutUm` bigint(20) DEFAULT NULL,
 
  `maxMemPct` int(11) DEFAULT NULL,
 
  `blocksChanged` bigint(20) DEFAULT NULL,
 
  `numTempFiles` bigint(20) DEFAULT NULL,
 
  `tempFileSpace` bigint(20) DEFAULT NULL,
 
  PRIMARY KEY (`queryID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

Comment by Zdravelina Sokolovska (Inactive) [ 2019-01-22 ]

todo: check on 1.0.16

Comment by Zdravelina Sokolovska (Inactive) [ 2019-01-31 ]
  1. mcsadmin getSoftwareInfo
    getsoftwareinfo Thu Jan 31 12:46:36 2019

Name : mariadb-columnstore-platform
Version : 1.0.16
Release : 1
Architecture: x86_64
Install Date: Thu 31 Jan 2019 12:37:51 PM EET
Group : Applications/Databases
Size : 10178950
License : Copyright (c) 2016 MariaDB Corporation Ab., all rights reserved; redistributable under the terms of the GPL, see the file COPYING for details.
Signature : (none)
Source RPM : mariadb-columnstore-platform-1.0.16-1.src.rpm
Build Date : Fri 11 Jan 2019 02:49:25 AM EET
Build Host : ip-10-0-0-107.ec2.internal
Relocations : (not relocatable)
Vendor : MariaDB Corporation Ab
URL : http://mariadb.org
Summary : MariaDB-Columnstore software
Description :
MariaDB Columnstore: a very fast and robust SQL database server

It is GPL v2 licensed, which means you can use the it free of charge under the
conditions of the GNU General Public License Version 2 (http://www.gnu.org/licenses/).

MariaDB documentation can be found at https://mariadb.com/kb
MariaDB bug reports should be submitted through https://jira.mariadb.org

MariaDB [(none)]> show create table infinidb_querystats.querystats ;
 
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
 
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| querystats | CREATE TABLE `querystats` (
 
  `queryID` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `sessionID` bigint(20) DEFAULT NULL,
 
  `host` varchar(50) DEFAULT NULL,
 
  `user` varchar(50) DEFAULT NULL,
 
  `priority` char(20) DEFAULT NULL,
 
  `queryType` char(25) DEFAULT NULL,
 
  `query` varchar(8000) DEFAULT NULL,
 
  `startTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 
  `endTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `rows` bigint(20) DEFAULT NULL,
 
  `errno` int(11) DEFAULT NULL,
 
  `phyIO` bigint(20) DEFAULT NULL,
 
  `cacheIO` bigint(20) DEFAULT NULL,
 
  `blocksTouched` bigint(20) DEFAULT NULL,
 
  `CPBlocksSkipped` bigint(20) DEFAULT NULL,
 
  `msgInUM` bigint(20) DEFAULT NULL,
 
  `msgOutUm` bigint(20) DEFAULT NULL,
 
  `maxMemPct` int(11) DEFAULT NULL,
 
  `blocksChanged` bigint(20) DEFAULT NULL,
 
  `numTempFiles` bigint(20) DEFAULT NULL,
 
  `tempFileSpace` bigint(20) DEFAULT NULL,
 
  PRIMARY KEY (`queryID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 

Generated at Thu Feb 08 02:30:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.