[MDEV-5259] Can not restore SphinxSE table using binlog_format row or mixed Created: 2013-11-08  Updated: 2014-05-04  Resolved: 2013-11-11

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.2.14, 5.3.12, 5.5.33a
Fix Version/s: 10.0.4

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: sphinx
Environment:

Centos OS 5.6

Issue Links:
Relates
relates to MDEV-6195 sphinxse UPDATEs to rt indexes are in... Closed

 Description   

Restoring a dump for a SphinxSE failed with error message

822 – Dumping data for table `iv3article`
9823 –
9824
9825 LOCK TABLES `iv3article` WRITE;
9826 /*!40000 ALTER TABLE `iv3article` DISABLE KEYS */;
9827 /*!40000 ALTER TABLE `iv3article` ENABLE KEYS */;
9828 UNLOCK TABLES;
9829
9830

ERROR 1661 (HY000) at line 9825: Cannot execute statement: impossible to write to binary log since both row-incapable engines and statement-incapable engines are involve

 
in ha_sphinx.cc
 
 
 
 
 
#if MYSQL_VERSION_ID<50100
 
handlerton sphinx_hton =
 
{
 
	#ifdef MYSQL_HANDLERTON_INTERFACE_VERSION
 
	MYSQL_HANDLERTON_INTERFACE_VERSION,
 
	#endif
 
	sphinx_hton_name,
 
	SHOW_OPTION_YES,
 
	sphinx_hton_comment,
 
	DB_TYPE_SPHINX_DB,
 
	sphinx_init_func_for_handlerton,
 
	0,							// slot
 
	0,							// savepoint size
 
	sphinx_close_connection,	// close_connection
 
	NULL,	// savepoint
 
	NULL,	// rollback to savepoint
 
	NULL,	// release savepoint
 
	NULL,	// commit
 
	NULL,	// rollback
 
	NULL,	// prepare
 
	NULL,	// recover
 
	NULL,	// commit_by_xid
 
	NULL,	// rollback_by_xid
 
	NULL,	// create_cursor_read_view
 
	NULL,	// set_cursor_read_view
 
	NULL,	// close_cursor_read_view
 
	HTON_CAN_RECREATE
 
};
 
#else
 
static handlerton * sphinx_hton_ptr = NULL;
 
#endif
 
 
 

 
-- --------------------------------------------------------
 
-- Host:                         alpinix5.sdv.fr
 
-- Server version:               5.2.8-MariaDB-log - (MariaDB - http://mariadb.com/)
 
-- Server OS:                    Linux
 
-- HeidiSQL version:             7.0.0.4053
 
-- Date/time:                    2013-11-08 10:15:30
 
-- --------------------------------------------------------
 
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 
/*!40101 SET NAMES utf8 */;
 
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
 
 
 
-- Dumping structure for table iv3.iv3article_rt
 
DROP TABLE IF EXISTS `iv3article_rt`;
 
CREATE TABLE IF NOT EXISTS `iv3article_rt` (
 
  `id` int(10) unsigned NOT NULL,
 
  `weight` int(11) NOT NULL,
 
  `query` blob NOT NULL,
 
  `group_id` int(11) DEFAULT NULL,
 
  KEY `query` (`query`(3072))
 
) ENGINE=SPHINX DEFAULT CHARSET=utf8 CONNECTION='sphinx://alpinix6.sdv.fr:8101/rtIv3Article';
 
 
 
-- Data exporting was unselected.
 
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 
 
 
 
 
Engine	Support	Comment	Transactions	XA	Savepoints
 
SPHINX	YES	Sphinx storage engine 0.9.9	NO	NO	NO



 Comments   
Comment by VAROQUI Stephane [ 2013-11-08 ]

Can not reproduce when the dump is done on a 5.5.33a. The dump triggering the error was produce on a 5.2.8. So may be this is already fixed and just documentation required

Comment by Elena Stepanova [ 2013-11-11 ]

I don't think it has anything to do with the dump format, the problem is reproducible on 5.x with plain SQL, LOCK TABLE causes the error. The MTR test case is below.

However, it seems to have been fixed in 10.0.

Stephane,

Do you need it to be fixed in 5.5, or is it okay to have it in 10.0 only?

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

MTR test case (to be placed in suite/sphinx, although the issue is equally reproducible if sphinx is not running, creating a Sphinx SE table is enough).

--source include/have_log_bin.inc

eval create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:$SPHINXSEARCH_PORT/*";
lock table ts write;
unlock tables;
drop table ts;

Comment by VAROQUI Stephane [ 2013-11-11 ]

It does not look critical for the client as he is planning a move in 10.0 very soon. For now --force is doing the rest .

Stéphane

Comment by Elena Stepanova [ 2013-11-11 ]

As discussed in the previous comments, closing since it's fixed in 10.0 (specifically in 10.0.4). The problem remains in 5.5 versions.

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