[MDEV-9621] Insert delayed bug - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1.9, 10.1.11
Fix Version/s: 10.1.14
Component/s: Data Manipulation - Insert
Labels:
None
Environment:
Centos 5 custom version.

Description

The mariadb installed are:

# rpm -qa | grep Maria

MariaDB-shared-10.1.11-1

MariaDB-connect-engine-10.1.11-1

MariaDB-client-10.1.11-1

MariaDB-compat-10.1.11-1

MariaDB-common-10.1.11-1

MariaDB-server-10.1.11-1

I don't see this in mysql version I was using (5.1), haven't tried on later version.
The reason was am planning migration of the database so I am testing all commands individually. This command is from radius server doing a insert a session to the session table radacct. The values are not important, its just for testing of the command.
I am checking the performance of myisam vs innodb for this table. So I need to old way of inserting to do a proper comparing.

MariaDB [server1]> INSERT DELAYED INTO radacct             (acctsessionid,    acctuniqueid,     username,           groupname,              realm,            nasipaddress,     nasportid,              nasporttype,      acctstarttime,    acctstoptime,              acctsessiontime,  acctauthentic,    connectinfo_start,              connectinfo_stop, acctinputoctets,  acctoutputoctets,              calledstationid,  callingstationid, acctterminatecause,              servicetype,      framedprotocol,   framedipaddress,              acctstartdelay,   acctstopdelay,      acctupdatetime     )           VALUES ("aasda1","ascac","ascasc","assfa","reasas","ascac","123","asc",now(),now(),12312,"testa","123asc",10,10,"asca","ascasc","teast","ascasc","ascasc","aasca","teast",12312,123123,now());

Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [server1]> INSERT DELAYED INTO radacct             (acctsessionid,    acctuniqueid,     username,           groupname,              realm,            nasipaddress,     nasportid,              nasporttype,      acctstarttime,    acctstoptime,              acctsessiontime,  acctauthentic,    connectinfo_start,              connectinfo_stop, acctinputoctets,  acctoutputoctets,              calledstationid,  callingstationid, acctterminatecause,              servicetype,      framedprotocol,   framedipaddress,              acctstartdelay,   acctstopdelay,      acctupdatetime     )           VALUES ("aasda1","ascac","ascasc","assfa","reasas","ascac","123","asc",now(),now(),12312,"testa","123asc",10,10,"asca","ascasc","teast","ascasc","ascasc","aasca","teast",12312,123123,now());

ERROR 1110 (42000): Column 'acctupdatetime' specified twice

Note the two delayed insert are identical.

The table structure is as follows:

CREATE TABLE `radacct` (

  `radacctid` bigint(20) NOT NULL AUTO_INCREMENT,

  `acctsessionid` varchar(64) NOT NULL DEFAULT '',

  `acctuniqueid` varchar(32) NOT NULL DEFAULT '',

  `username` varchar(64) NOT NULL DEFAULT '',

  `groupname` varchar(64) NOT NULL DEFAULT '',

  `realm` varchar(64) DEFAULT '',

  `nasipaddress` varchar(15) NOT NULL DEFAULT '',

  `nasportid` varchar(15) DEFAULT NULL,

  `nasporttype` varchar(32) DEFAULT NULL,

  `acctstarttime` datetime DEFAULT NULL,

  `acctstoptime` datetime DEFAULT NULL,

  `acctsessiontime` int(11) DEFAULT NULL,

  `acctauthentic` varchar(32) DEFAULT NULL,

  `connectinfo_start` varchar(50) DEFAULT NULL,

  `connectinfo_stop` varchar(50) DEFAULT NULL,

  `acctinputoctets` bigint(20) DEFAULT NULL,

  `acctoutputoctets` bigint(20) DEFAULT NULL,

  `calledstationid` varchar(50) NOT NULL DEFAULT '',

  `callingstationid` varchar(50) NOT NULL DEFAULT '',

  `acctterminatecause` varchar(32) NOT NULL DEFAULT '',

  `servicetype` varchar(32) DEFAULT NULL,

  `framedprotocol` varchar(32) DEFAULT NULL,

  `framedipaddress` varchar(15) NOT NULL DEFAULT '',

  `acctstartdelay` int(11) DEFAULT NULL,

  `acctstopdelay` int(11) DEFAULT NULL,

  `acctupdatetime` datetime DEFAULT NULL,

  `deleted` char(1) DEFAULT '',

  `objectclass` varchar(64) DEFAULT 'AAA-SessionInfo',

  PRIMARY KEY (`radacctid`),

  KEY `username` (`username`),

  KEY `framedipaddress` (`framedipaddress`),

  KEY `acctsessionid` (`acctsessionid`),

  KEY `acctsessiontime` (`acctsessiontime`),

  KEY `acctuniqueid` (`acctuniqueid`),

  KEY `acctstarttime` (`acctstarttime`),

  KEY `acctstoptime` (`acctstoptime`),

  KEY `nasipaddress` (`nasipaddress`),

  KEY `acctupdatetime` (`acctupdatetime`),

  KEY `deleted` (`deleted`),

  KEY `objectclass` (`objectclass`)

) ENGINE=MyISAM AUTO_INCREMENT=7117 DEFAULT CHARSET=latin1

Regards,

Michael

Attachments

Activity

Ascending order - Click to sort in descending order

Michael Van Der Beek created issue - 2016-02-24 04:45

Elena Stepanova made changes - 2016-02-24 16:03

Field	Original Value	New Value
Status	Open [ 1 ]	Confirmed [ 10101 ]

Elena Stepanova made changes - 2016-02-24 16:04

Description

Hi All,
I am having a problem with insert delayed option with the above mariadb.
Its is returning weird errors.
The max_delayed_threads = 40
I am doing this on a test system. There are no other insert delayed into the queue.
So it cannot be a delayed thread queue problem. The error doesn't make sense even.
Sorry if this has been reported before. Not sure how to search for such a specific error.

The mariadb installed are:
# rpm -qa | grep Maria
MariaDB-shared-10.1.11-1
MariaDB-connect-engine-10.1.11-1
MariaDB-client-10.1.11-1
MariaDB-compat-10.1.11-1
MariaDB-common-10.1.11-1
MariaDB-server-10.1.11-1

I don't see this in mysql version I was using (5.1), haven't tried on later version.
The reason was am planning migration of the database so I am testing all commands individually. This command is from radius server doing a insert a session to the session table radacct. The values are not important, its just for testing of the command.
I am checking the performance of myisam vs innodb for this table. So I need to old way of inserting to do a proper comparing.

MariaDB [server1]> INSERT DELAYED INTO radacct (acctsessionid, acctuniqueid, username, groupname, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, acctupdatetime ) VALUES ("aasda1","ascac","ascasc","assfa","reasas","ascac","123","asc",now(),now(),12312,"testa","123asc",10,10,"asca","ascasc","teast","ascasc","ascasc","aasca","teast",12312,123123,now());
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [server1]> INSERT DELAYED INTO radacct (acctsessionid, acctuniqueid, username, groupname, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, acctupdatetime ) VALUES ("aasda1","ascac","ascasc","assfa","reasas","ascac","123","asc",now(),now(),12312,"testa","123asc",10,10,"asca","ascasc","teast","ascasc","ascasc","aasca","teast",12312,123123,now());
ERROR 1110 (42000): Column 'acctupdatetime' specified twice

Note the two delayed insert are identical.

The table structure is as follows:
CREATE TABLE `radacct` (
  `radacctid` bigint(20) NOT NULL AUTO_INCREMENT,
  `acctsessionid` varchar(64) NOT NULL DEFAULT '',
  `acctuniqueid` varchar(32) NOT NULL DEFAULT '',
  `username` varchar(64) NOT NULL DEFAULT '',
  `groupname` varchar(64) NOT NULL DEFAULT '',
  `realm` varchar(64) DEFAULT '',
  `nasipaddress` varchar(15) NOT NULL DEFAULT '',
  `nasportid` varchar(15) DEFAULT NULL,
  `nasporttype` varchar(32) DEFAULT NULL,
  `acctstarttime` datetime DEFAULT NULL,
  `acctstoptime` datetime DEFAULT NULL,
  `acctsessiontime` int(11) DEFAULT NULL,
  `acctauthentic` varchar(32) DEFAULT NULL,
  `connectinfo_start` varchar(50) DEFAULT NULL,
  `connectinfo_stop` varchar(50) DEFAULT NULL,
  `acctinputoctets` bigint(20) DEFAULT NULL,
  `acctoutputoctets` bigint(20) DEFAULT NULL,
  `calledstationid` varchar(50) NOT NULL DEFAULT '',
  `callingstationid` varchar(50) NOT NULL DEFAULT '',
  `acctterminatecause` varchar(32) NOT NULL DEFAULT '',
  `servicetype` varchar(32) DEFAULT NULL,
  `framedprotocol` varchar(32) DEFAULT NULL,
  `framedipaddress` varchar(15) NOT NULL DEFAULT '',
  `acctstartdelay` int(11) DEFAULT NULL,
  `acctstopdelay` int(11) DEFAULT NULL,
  `acctupdatetime` datetime DEFAULT NULL,
  `deleted` char(1) DEFAULT '',
  `objectclass` varchar(64) DEFAULT 'AAA-SessionInfo',
  PRIMARY KEY (`radacctid`),
  KEY `username` (`username`),
  KEY `framedipaddress` (`framedipaddress`),
  KEY `acctsessionid` (`acctsessionid`),
  KEY `acctsessiontime` (`acctsessiontime`),
  KEY `acctuniqueid` (`acctuniqueid`),
  KEY `acctstarttime` (`acctstarttime`),
  KEY `acctstoptime` (`acctstoptime`),
  KEY `nasipaddress` (`nasipaddress`),
  KEY `acctupdatetime` (`acctupdatetime`),
  KEY `deleted` (`deleted`),
  KEY `objectclass` (`objectclass`)
) ENGINE=MyISAM AUTO_INCREMENT=7117 DEFAULT CHARSET=latin1

Regards,

Michael

Hi All,
I am having a problem with insert delayed option with the above mariadb.
Its is returning weird errors.
The max_delayed_threads = 40
I am doing this on a test system. There are no other insert delayed into the queue.
So it cannot be a delayed thread queue problem. The error doesn't make sense even.
Sorry if this has been reported before. Not sure how to search for such a specific error.

The mariadb installed are:
{noformat}
# rpm -qa | grep Maria
MariaDB-shared-10.1.11-1
MariaDB-connect-engine-10.1.11-1
MariaDB-client-10.1.11-1
MariaDB-compat-10.1.11-1
MariaDB-common-10.1.11-1
MariaDB-server-10.1.11-1
{noformat}

I don't see this in mysql version I was using (5.1), haven't tried on later version.
The reason was am planning migration of the database so I am testing all commands individually. This command is from radius server doing a insert a session to the session table radacct. The values are not important, its just for testing of the command.
I am checking the performance of myisam vs innodb for this table. So I need to old way of inserting to do a proper comparing.

{code:sql}
MariaDB [server1]> INSERT DELAYED INTO radacct (acctsessionid, acctuniqueid, username, groupname, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, acctupdatetime ) VALUES ("aasda1","ascac","ascasc","assfa","reasas","ascac","123","asc",now(),now(),12312,"testa","123asc",10,10,"asca","ascasc","teast","ascasc","ascasc","aasca","teast",12312,123123,now());
Query OK, 1 row affected, 1 warning (0.00 sec)
{code}
{code:sql}
MariaDB [server1]> INSERT DELAYED INTO radacct (acctsessionid, acctuniqueid, username, groupname, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, acctupdatetime ) VALUES ("aasda1","ascac","ascasc","assfa","reasas","ascac","123","asc",now(),now(),12312,"testa","123asc",10,10,"asca","ascasc","teast","ascasc","ascasc","aasca","teast",12312,123123,now());
ERROR 1110 (42000): Column 'acctupdatetime' specified twice
{code}

Note the two delayed insert are identical.

{code:sql}
The table structure is as follows:
CREATE TABLE `radacct` (
  `radacctid` bigint(20) NOT NULL AUTO_INCREMENT,
  `acctsessionid` varchar(64) NOT NULL DEFAULT '',
  `acctuniqueid` varchar(32) NOT NULL DEFAULT '',
  `username` varchar(64) NOT NULL DEFAULT '',
  `groupname` varchar(64) NOT NULL DEFAULT '',
  `realm` varchar(64) DEFAULT '',
  `nasipaddress` varchar(15) NOT NULL DEFAULT '',
  `nasportid` varchar(15) DEFAULT NULL,
  `nasporttype` varchar(32) DEFAULT NULL,
  `acctstarttime` datetime DEFAULT NULL,
  `acctstoptime` datetime DEFAULT NULL,
  `acctsessiontime` int(11) DEFAULT NULL,
  `acctauthentic` varchar(32) DEFAULT NULL,
  `connectinfo_start` varchar(50) DEFAULT NULL,
  `connectinfo_stop` varchar(50) DEFAULT NULL,
  `acctinputoctets` bigint(20) DEFAULT NULL,
  `acctoutputoctets` bigint(20) DEFAULT NULL,
  `calledstationid` varchar(50) NOT NULL DEFAULT '',
  `callingstationid` varchar(50) NOT NULL DEFAULT '',
  `acctterminatecause` varchar(32) NOT NULL DEFAULT '',
  `servicetype` varchar(32) DEFAULT NULL,
  `framedprotocol` varchar(32) DEFAULT NULL,
  `framedipaddress` varchar(15) NOT NULL DEFAULT '',
  `acctstartdelay` int(11) DEFAULT NULL,
  `acctstopdelay` int(11) DEFAULT NULL,
  `acctupdatetime` datetime DEFAULT NULL,
  `deleted` char(1) DEFAULT '',
  `objectclass` varchar(64) DEFAULT 'AAA-SessionInfo',
  PRIMARY KEY (`radacctid`),
  KEY `username` (`username`),
  KEY `framedipaddress` (`framedipaddress`),
  KEY `acctsessionid` (`acctsessionid`),
  KEY `acctsessiontime` (`acctsessiontime`),
  KEY `acctuniqueid` (`acctuniqueid`),
  KEY `acctstarttime` (`acctstarttime`),
  KEY `acctstoptime` (`acctstoptime`),
  KEY `nasipaddress` (`nasipaddress`),
  KEY `acctupdatetime` (`acctupdatetime`),
  KEY `deleted` (`deleted`),
  KEY `objectclass` (`objectclass`)
) ENGINE=MyISAM AUTO_INCREMENT=7117 DEFAULT CHARSET=latin1
{code}

Regards,

Michael

Elena Stepanova made changes - 2016-02-24 16:11

Fix Version/s		10.1 [ 16100 ]
Assignee		Michael Widenius [ monty ]

Elena Stepanova made changes - 2016-02-24 16:11

Affects Version/s

10.1.9 [ 20301 ]

Michael Widenius made changes - 2016-04-06 11:40

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Michael Widenius made changes - 2016-04-07 17:40

issue.field.resolutiondate

2016-04-07 17:40:11.0

2016-04-07 17:40:11.085

Michael Widenius made changes - 2016-04-07 17:40

Fix Version/s		10.1.14 [ 21804 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 74115 ]

MariaDB v4 [ 150152 ]

People

Assignee:: Michael Widenius

Reporter:: Michael Van Der Beek

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2016-02-24 04:45

Updated:: 2016-04-07 17:40

Resolved:: 2016-04-07 17:40

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration