Fix DB exception when indexing with stale index data [#1346846]

Comment	File	Size	Author
#16	fix-indexing-of-removed-fields-1346846-16.patch	1.15 KB	klaasvw
#7	1346846--empty-tables-used-7.patch	1.52 KB	drunken monkey

Comment #1

drunken monkey

he/him

German

Vienna, Austria

commented 19 November 2011 at 19:07

Could you please find out the whole query (probably best from the MySQL server log) and maybe on which line the error occurred?

Log in or register to post comments

Comment #2

matthieu_collet commented 21 November 2011 at 17:51

I created an issue with the same problem in the search api issues :
http://drupal.org/node/1348416

perhaps more intersting to merge them ?

Log in or register to post comments

Comment #3

adamgerthel commented 21 November 2011 at 22:09

I haven't been able to test Search API with any other server index solutions than this module, and I couldn't find anything in the Search API issue queue regarding this, so I figured the issue belonged here.

@drunken monkey:

I'm not that familiar with looking into MySQL logs. I'm using MySQL on OSX installed via installer. I use Sequel Pro as UI. Any idea where I should look? This is the ConsoleLog if that helps (taken right after pushing "index now"):


/* 22:47:01 Local Dev */ SHOW VARIABLES;
/* 22:47:07 Local Dev */ SHOW /*!50002 FULL*/ TABLES;
/* 22:47:07 Local Dev */ SELECT * FROM information_schema.routines WHERE routine_schema = 'sandbox2' ORDER BY routine_name;
/* 22:53:06 Local Dev */ SHOW /*!50002 FULL*/ TABLES;
/* 22:53:06 Local Dev */ SELECT * FROM information_schema.routines WHERE routine_schema = 'mysql' ORDER BY routine_name;
/* 22:53:20 Local Dev */ SHOW TABLE STATUS LIKE 'general_log';
/* 22:53:20 Local Dev */ SHOW CREATE TABLE `general_log`;
/* 22:53:22 Local Dev */ SELECT * FROM `general_log` LIMIT 0,1000;
/* 22:53:22 Local Dev */ SHOW TABLES IN information_schema LIKE 'ENGINES';
/* 22:53:22 Local Dev */ SELECT Engine, Support FROM `information_schema`.`engines` WHERE SUPPORT IN ('DEFAULT', 'YES');
/* 22:53:22 Local Dev */ SELECT * FROM `information_schema`.`character_sets` ORDER BY `character_set_name` ASC;
/* 22:53:22 Local Dev */ SELECT * FROM `information_schema`.`collations` WHERE character_set_name = 'utf8' ORDER BY `collation_name` ASC;
/* 22:53:24 Local Dev */ SHOW TABLE STATUS LIKE 'help_category';
/* 22:53:24 Local Dev */ SHOW CREATE TABLE `help_category`;
/* 22:53:24 Local Dev */ SELECT * FROM `help_category` LIMIT 0,1000;
/* 22:53:26 Local Dev */ SHOW TABLE STATUS LIKE 'help_keyword';
/* 22:53:26 Local Dev */ SHOW CREATE TABLE `help_keyword`;
/* 22:53:26 Local Dev */ SELECT * FROM `help_keyword` LIMIT 0,1000;
/* 22:53:34 Local Dev */ SHOW TABLE STATUS LIKE 'help_relation';
/* 22:53:34 Local Dev */ SHOW CREATE TABLE `help_relation`;
/* 22:53:34 Local Dev */ SELECT * FROM `help_relation` LIMIT 0,1000;
/* 22:53:35 Local Dev */ SHOW TABLE STATUS LIKE 'help_topic';
/* 22:53:35 Local Dev */ SHOW CREATE TABLE `help_topic`;
/* 22:53:35 Local Dev */ SELECT * FROM `help_topic` LIMIT 0,1000;
/* 22:53:38 Local Dev */ SHOW TABLE STATUS LIKE 'ndb_binlog_index';
/* 22:53:38 Local Dev */ SHOW CREATE TABLE `ndb_binlog_index`;
/* 22:53:38 Local Dev */ SET NAMES 'latin1';
/* 22:53:38 Local Dev */ SELECT * FROM `ndb_binlog_index` LIMIT 0,1000;
/* 22:53:38 Local Dev */ SELECT * FROM `information_schema`.`collations` WHERE character_set_name = 'latin1' ORDER BY `collation_name` ASC;
/* 22:53:41 Local Dev */ SET NAMES 'utf8';
/* 22:53:41 Local Dev */ SHOW TABLE STATUS LIKE 'slow_log';
/* 22:53:41 Local Dev */ SHOW CREATE TABLE `slow_log`;
/* 22:53:41 Local Dev */ SELECT * FROM `slow_log` LIMIT 0,1000;
/* 22:53:42 Local Dev */ SHOW TABLE STATUS LIKE 'tables_priv';
/* 22:53:42 Local Dev */ SHOW CREATE TABLE `tables_priv`;
/* 22:53:42 Local Dev */ SELECT * FROM `tables_priv` LIMIT 0,1000;
/* 22:53:43 Local Dev */ SHOW TABLE STATUS LIKE 'slow_log';
/* 22:53:43 Local Dev */ SHOW CREATE TABLE `slow_log`;
/* 22:53:43 Local Dev */ SELECT * FROM `slow_log` LIMIT 0,1000;
/* 22:53:43 Local Dev */ SHOW TABLE STATUS LIKE 'servers';
/* 22:53:43 Local Dev */ SHOW CREATE TABLE `servers`;
/* 22:53:43 Local Dev */ SELECT * FROM `servers` LIMIT 0,1000;
/* 22:53:43 Local Dev */ SHOW TABLE STATUS LIKE 'proxies_priv';
/* 22:53:43 Local Dev */ SHOW CREATE TABLE `proxies_priv`;
/* 22:53:43 Local Dev */ SELECT * FROM `proxies_priv` LIMIT 0,1000;
/* 22:53:44 Local Dev */ SHOW TABLE STATUS LIKE 'procs_priv';
/* 22:53:44 Local Dev */ SHOW CREATE TABLE `procs_priv`;
/* 22:53:44 Local Dev */ SELECT * FROM `procs_priv` LIMIT 0,1000;
/* 22:53:44 Local Dev */ SHOW TABLE STATUS LIKE 'proc';
/* 22:53:44 Local Dev */ SHOW CREATE TABLE `proc`;
/* 22:53:44 Local Dev */ SELECT * FROM `proc` LIMIT 0,1000;
/* 22:53:44 Local Dev */ SHOW TABLE STATUS LIKE 'plugin';
/* 22:53:44 Local Dev */ SHOW CREATE TABLE `plugin`;
/* 22:53:45 Local Dev */ SELECT * FROM `plugin` LIMIT 0,1000;
/* 22:53:45 Local Dev */ SHOW TABLE STATUS LIKE 'ndb_binlog_index';
/* 22:53:45 Local Dev */ SHOW CREATE TABLE `ndb_binlog_index`;
/* 22:53:45 Local Dev */ SET NAMES 'latin1';
/* 22:53:45 Local Dev */ SELECT * FROM `ndb_binlog_index` LIMIT 0,1000;
/* 22:53:45 Local Dev */ SET NAMES 'utf8';
/* 22:53:45 Local Dev */ SHOW TABLE STATUS LIKE 'host';
/* 22:53:45 Local Dev */ SHOW CREATE TABLE `host`;
/* 22:53:45 Local Dev */ SELECT * FROM `host` LIMIT 0,1000;
/* 22:53:45 Local Dev */ SHOW TABLE STATUS LIKE 'help_topic';
/* 22:53:45 Local Dev */ SHOW CREATE TABLE `help_topic`;
/* 22:53:45 Local Dev */ SELECT * FROM `help_topic` LIMIT 0,1000;
/* 22:53:46 Local Dev */ SHOW TABLE STATUS LIKE 'help_relation';
/* 22:53:46 Local Dev */ SHOW CREATE TABLE `help_relation`;
/* 22:53:46 Local Dev */ SELECT * FROM `help_relation` LIMIT 0,1000;
/* 22:53:46 Local Dev */ SHOW TABLE STATUS LIKE 'help_keyword';
/* 22:53:46 Local Dev */ SHOW CREATE TABLE `help_keyword`;
/* 22:53:47 Local Dev */ SELECT * FROM `help_keyword` LIMIT 0,1000;
/* 22:53:47 Local Dev */ SHOW TABLE STATUS LIKE 'help_category';
/* 22:53:47 Local Dev */ SHOW CREATE TABLE `help_category`;
/* 22:53:47 Local Dev */ SELECT * FROM `help_category` LIMIT 0,1000;
/* 22:53:47 Local Dev */ SHOW TABLE STATUS LIKE 'general_log';
/* 22:53:47 Local Dev */ SHOW CREATE TABLE `general_log`;
/* 22:53:47 Local Dev */ SELECT * FROM `general_log` LIMIT 0,1000;
/* 22:53:48 Local Dev */ SHOW TABLE STATUS LIKE 'func';
/* 22:53:48 Local Dev */ SHOW CREATE TABLE `func`;
/* 22:53:48 Local Dev */ SELECT * FROM `func` LIMIT 0,1000;
/* 22:53:48 Local Dev */ SHOW TABLE STATUS LIKE 'event';
/* 22:53:48 Local Dev */ SHOW CREATE TABLE `event`;
/* 22:53:48 Local Dev */ SELECT * FROM `event` LIMIT 0,1000;
/* 22:53:48 Local Dev */ SHOW TABLE STATUS LIKE 'db';
/* 22:53:48 Local Dev */ SHOW CREATE TABLE `db`;
/* 22:53:49 Local Dev */ SELECT * FROM `db` LIMIT 0,1000;
/* 22:53:49 Local Dev */ SHOW TABLE STATUS LIKE 'columns_priv';
/* 22:53:49 Local Dev */ SHOW CREATE TABLE `columns_priv`;
/* 22:53:49 Local Dev */ SELECT * FROM `columns_priv` LIMIT 0,1000;
/* 22:53:54 Local Dev */ SHOW /*!50002 FULL*/ TABLES;
/* 22:53:54 Local Dev */ SELECT * FROM information_schema.routines WHERE routine_schema = 'sandbox2' ORDER BY routine_name;

Log in or register to post comments

Comment #4

drunken monkey

he/him

German

Vienna, Austria

commented 22 November 2011 at 12:11

I haven't been able to test Search API with any other server index solutions than this module, and I couldn't find anything in the Search API issue queue regarding this, so I figured the issue belonged here.

Yes, it's the correct place (as far as I can tell).

I'm not that familiar with looking into MySQL logs. I'm using MySQL on OSX installed via installer. I use Sequel Pro as UI. Any idea where I should look? This is the ConsoleLog if that helps (taken right after pushing "index now"):

I'm not familiar with OS X, or installer. There should be a my.conf file that lets you specify logging files.

The correct query will contain the WHERE (item_id = '10') bit from above, and would probably have the mentioned error message below it.

Log in or register to post comments

Comment #5

adamgerthel commented 22 November 2011 at 14:08

I've looked around but I don't know really where to find it. Last week I turned off bin logging in MySQL because I don't use/need logging in the local dev environment and it ate up 25gb of disk space. I still have a couple of them (mysql-bin.00000XX) but I couldn't find anything useful in them.

Log in or register to post comments

Comment #6

Fidelix commented 24 November 2011 at 18:12

Here it is.
I've removed Watchdog queries from this log, but they're essentially like this:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (item_id = '4')' at line 1

mysql.log

FROM 
users base
WHERE  (base.uid IN  ('1'))
		  271 Query	SELECT r.rid, r.name, ur.uid FROM role r INNER JOIN users_roles ur ON ur.rid = r.rid WHERE ur.uid IN ('1')
		  271 Query	SELECT base.fid AS fid, base.uid AS uid, base.filename AS filename, base.uri AS uri, base.filemime AS filemime, base.filesize AS filesize, base.status AS status, base.timestamp AS timestamp
FROM 
file_managed base
WHERE  (base.fid IN  ('0'))
		  271 Query	SELECT cid, data, created, expire, serialized FROM cache_field WHERE cid IN ('field:user:1')
		  271 Query	START TRANSACTION
		  271 Query	DELETE FROM search_api_db_default_node_index_type 
WHERE  (item_id = '1')
		  271 Query	INSERT INTO search_api_db_default_node_index_type (item_id, value) VALUES ('1', 'webform')
		  271 Query	DELETE FROM search_api_db_default_node_index_title 
WHERE  (item_id = '1')
		  271 Query	INSERT INTO search_api_db_default_node_index_title (item_id, word, score) VALUES ('1', 'general', '5'), ('1', 'contact', '5')
		  271 Query	DELETE FROM search_api_db_default_node_index_promote 
WHERE  (item_id = '1')
		  271 Query	INSERT INTO search_api_db_default_node_index_promote (item_id, value) VALUES ('1', '0')
		  271 Query	DELETE FROM search_api_db_default_node_index_sticky 
WHERE  (item_id = '1')
		  271 Query	INSERT INTO search_api_db_default_node_index_sticky (item_id, value) VALUES ('1', '0')
		  271 Query	DELETE FROM search_api_db_default_node_index_created 
WHERE  (item_id = '1')
		  271 Query	INSERT INTO search_api_db_default_node_index_created (item_id, value) VALUES ('1', '1319991548')
		  271 Query	DELETE FROM search_api_db_default_node_index_changed 
WHERE  (item_id = '1')
		  271 Query	INSERT INTO search_api_db_default_node_index_changed (item_id, value) VALUES ('1', '1319992990')
		  271 Query	DELETE FROM search_api_db_default_node_index_author 
WHERE  (item_id = '1')
		  271 Query	INSERT INTO search_api_db_default_node_index_author (item_id, value) VALUES ('1', '1')
		 
		  271 Query	DELETE FROM  
WHERE  (item_id = '1')
		  271 Query	rollback
		  
		  271 Query	START TRANSACTION
		  271 Query	DELETE FROM search_api_db_default_node_index_type 
WHERE  (item_id = '2')
		  271 Query	INSERT INTO search_api_db_default_node_index_type (item_id, value) VALUES ('2', 'static_page')
		  271 Query	DELETE FROM search_api_db_default_node_index_title 
WHERE  (item_id = '2')
		  271 Query	INSERT INTO search_api_db_default_node_index_title (item_id, word, score) VALUES ('2', 'why', '5'), ('2', 'advertise', '5'), ('2', 'SOMESITE', '5')
		  271 Query	DELETE FROM search_api_db_default_node_index_promote 
WHERE  (item_id = '2')
		  271 Query	INSERT INTO search_api_db_default_node_index_promote (item_id, value) VALUES ('2', '0')
		  271 Query	DELETE FROM search_api_db_default_node_index_sticky 
WHERE  (item_id = '2')
		  271 Query	INSERT INTO search_api_db_default_node_index_sticky (item_id, value) VALUES ('2', '0')
		  271 Query	DELETE FROM search_api_db_default_node_index_created 
WHERE  (item_id = '2')
		  271 Query	INSERT INTO search_api_db_default_node_index_created (item_id, value) VALUES ('2', '1321028358')
		  271 Query	DELETE FROM search_api_db_default_node_index_changed 
WHERE  (item_id = '2')
		  271 Query	INSERT INTO search_api_db_default_node_index_changed (item_id, value) VALUES ('2', '1321028358')
		  271 Query	DELETE FROM search_api_db_default_node_index_author 
WHERE  (item_id = '2')
		  271 Query	INSERT INTO search_api_db_default_node_index_author (item_id, value) VALUES ('2', '1')
		  
		  271 Query	DELETE FROM  
WHERE  (item_id = '2')
		  271 Query	rollback
		 
		  271 Query	START TRANSACTION
		  271 Query	DELETE FROM search_api_db_default_node_index_type 
WHERE  (item_id = '4')
		  271 Query	INSERT INTO search_api_db_default_node_index_type (item_id, value) VALUES ('4', 'static_page')
		  271 Query	DELETE FROM search_api_db_default_node_index_title 
WHERE  (item_id = '4')
		  271 Query	INSERT INTO search_api_db_default_node_index_title (item_id, word, score) VALUES ('4', 'work', '5'), ('4', 'SOMESITE', '5'), ('4', 'com', '5')
		  271 Query	DELETE FROM search_api_db_default_node_index_promote 
WHERE  (item_id = '4')
		  271 Query	INSERT INTO search_api_db_default_node_index_promote (item_id, value) VALUES ('4', '0')
		  271 Query	DELETE FROM search_api_db_default_node_index_sticky 
WHERE  (item_id = '4')
		  271 Query	INSERT INTO search_api_db_default_node_index_sticky (item_id, value) VALUES ('4', '0')
		  271 Query	DELETE FROM search_api_db_default_node_index_created 
WHERE  (item_id = '4')
		  271 Query	INSERT INTO search_api_db_default_node_index_created (item_id, value) VALUES ('4', '1321449855')
		  271 Query	DELETE FROM search_api_db_default_node_index_changed 
WHERE  (item_id = '4')
		  271 Query	INSERT INTO search_api_db_default_node_index_changed (item_id, value) VALUES ('4', '1321450272')
		  271 Query	DELETE FROM search_api_db_default_node_index_author 
WHERE  (item_id = '4')
		  271 Query	INSERT INTO search_api_db_default_node_index_author (item_id, value) VALUES ('4', '1')
		  
		  271 Query	DELETE FROM cache_form 
WHERE  (cid = 'form_form-7SLCGxecV-sbjPKtuSUA5H0klrwjzgG3n4BX72xY1WQ')
		  271 Query	DELETE FROM cache_form 
WHERE  (cid = 'form_state_form-7SLCGxecV-sbjPKtuSUA5H0klrwjzgG3n4BX72xY1WQ')
		  271 Query	START TRANSACTION
		  271 Query	SELECT 1 AS expression

Log in or register to post comments

Comment #7

drunken monkey

he/him

German

Vienna, Austria

commented 24 November 2011 at 23:44

Status	File	Size
new	1346846--empty-tables-used-7.patch	1.52 KB

DELETE FROM  
WHERE  (item_id = '2')

Oh. Not good.

Thanks a bunch for finding this out!
Please try the following:
- Resave the index's „Fields“ form.
- If that doesn't help, apply the attached patch.

Log in or register to post comments

Comment #8

drunken monkey

he/him

German

Vienna, Austria

commented 24 November 2011 at 23:45

Version:	7.x-1.0-beta2	» 7.x-1.x-dev
Status:	Active	» Needs review

Log in or register to post comments

Comment #9

Fidelix commented 25 November 2011 at 17:06

Yeah, resaving the "Fields" form does fix it.

Do you want me to test the patch anyway?

Log in or register to post comments

Comment #10

adamgerthel commented 25 November 2011 at 17:08

Same here- resaving fixed it

Log in or register to post comments

Comment #11

drunken monkey

he/him

German

Vienna, Austria

commented 26 November 2011 at 12:32

Category:	bug	» support
Status:	Needs review	» Fixed

Well, once it's fixed there is really nothing to test with the patch. Was probably some temporary version weirdness, I guess. Better just let the affected users re-save that form instead of introducing a fix for a problem that doesn't exist anymore.

Log in or register to post comments

Comment #12

Fidelix commented 26 November 2011 at 13:44

@drunken monkey, this will still affect users that do not save the fields form.
That's really confusing.

Maybe it's enough if you add an warning telling the user to save the fields form?

Log in or register to post comments

Comment #13

matthieu_collet commented 5 December 2011 at 15:24

"Resave the index's „Fields“ form." didn't work for me, but the patch did !

thank you !

Log in or register to post comments

Comment #14

19 December 2011 at 15:30

Status:

Fixed

» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Log in or register to post comments

Comment #15

dhayles commented 6 February 2012 at 18:59

None of the above is working for me, I'm still getting the error "Couldn't index items. Check the logs for details."

I have saved and changed the fields like a hundred times, and I can't get the patch to work.

I've upgraded to the dev version and still no luck, has anyone got any other solutions?

Log in or register to post comments

Comment #16

klaasvw commented 8 February 2012 at 08:23

Title:	Can't index default node index	» Syntax error or access violation when trying to index content after modifying the index fields
Category:	support	» bug
Status:	Closed (fixed)	» Needs review

Status	File	Size
new	fix-indexing-of-removed-fields-1346846-16.patch	1.15 KB

Re-saving the index fields form didn't work for me but the patch did.

I've rerolled the patch for the latest dev and did some very minor cleanup so the patch relates to this issue only.

Also changed the title to better reflect the issue.

Log in or register to post comments

Comment #17

dhayles commented 9 February 2012 at 19:08

Sorry but that patch didn't work either, I have tried everything, 1st patch, 2nd patch, saving the fields, re-saving the fields, clean drupal installation but I still get the following error:

Couldn't index items. Check the logs for details.

Could this line in my log shine any light into the problem:

SEVERE: org.apache.solr.common.SolrException: [doc=default_node_index-1] missing required field: entity_type

I have checked the field list and can confirm that there is no existing field named entity_type.

Log in or register to post comments

Comment #18

klaasvw commented 10 February 2012 at 14:31

dhayles, I think your issue is unrelated to search_api_db. The error is clearly coming from apache solr so your issue probably belongs in the issue queue of the search_api_solr module.

Log in or register to post comments

Comment #19

lucascaro commented 20 March 2012 at 12:39

I was using 7.x-1.0-beta and re saving the fields form didn't work. I installed 7.x-1.x-dev, re saved the form, cleared the index, cleared the cache, re indexed and got the same error message.

After that, I've applied the patch in #16 and fixed the error message. Thanks!

Log in or register to post comments

Comment #20

askibinski commented 26 April 2012 at 14:28

Saving the fields page (/admin/config/search/search_api/index/default_node_index/fields) did the trick for me.

using:
search api 7.x-1.0-rc
search api db 7.x-1.0-beta2
no patch.

Log in or register to post comments

Comment #21

drunken monkey

he/him

German

Vienna, Austria

commented 26 May 2012 at 16:29

For those for whom re-saving didn't work (and who actually use the database search): does re-saving the "Fields" form also not fix the problem with the latest dev versions of both this module and the Search API?
Then I guess we'll really have to commit this.

Log in or register to post comments

Comment #22

Sinan Erdem commented 29 June 2012 at 07:59

The patch on #16 worked for me and nothing else. I am using thre latest stable versions of both modules.

Log in or register to post comments

Comment #23

drunken monkey

he/him

German

Vienna, Austria

commented 1 July 2012 at 14:36

#1414138-10: Notice: Undefined index: search_api_access_node might be related – could you please also test the patch there, and then see whether re-saving the Fields form works?

Log in or register to post comments

Comment #24

Sborsody commented 7 July 2012 at 00:47

I used the patch in #16 on FuzzySearchService->itemIndex() and no longer get:

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (item_id = '4')' at line 1"

Log in or register to post comments

Comment #25

drunken monkey

he/him

German

Vienna, Austria

commented 7 July 2012 at 10:56

Could you please also test the patch in #1414138-10: Notice: Undefined index: search_api_access_node and try re-saving the form?
The patch in #16 is only a last ressort.

Log in or register to post comments

Comment #26

Sborsody commented 7 July 2012 at 17:54

OK, but I wasn't getting that error. What I found happening on my system was that with both the default fuzzysearch index and a custom index the fulltext fields were not being indexed. Their database tables were created but empty. The only error was the SQL one found in dblog.

Log in or register to post comments

Comment #27

Sborsody commented 7 July 2012 at 18:16

Here's what I did.

- Apply the patch from #25 to search_api
- Disable then uninstall fuzzysearch
- Enable fuzzysearch
- Go to status page and index (doesn't index anything)
- Go to the fields form for the default fuzzysearch index and hit save
- Go to status page and index
- I see the database table for title field being populated.

So in addition to this there needs to be something changed in fuzzysearch so that it's default index works out of the box.

Log in or register to post comments

Comment #28

ludo.r

Brussels

commented 14 September 2012 at 11:01

Priority:

Normal

» Major

I have the same issue :

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (item_id = '4')' at line 1

I just enabled the Search API module on a fresh standard Drupal installation.
Saving fields form fixed this issue.

However, it doesn't work when I try this in my existing project with a custom Search index.
Using the Default node index works (after saving fields form).

I believe I have to figure out which fields in my custom index are throwing this error.

EDIT : it seems "Entity HTML output" is messing up the index. When I disable it, index works correctly.

Log in or register to post comments

Comment #29

acouch commented 7 January 2013 at 04:33

Status:

Needs review

» Reviewed & tested by the community

Re-saving the form fixes this for me.

However I'm building a distro and this bug interferes with the site installation by spitting out a number of warning messages mentioned above. #16 fixes this for the distro as well.

Log in or register to post comments

Comment #30

drunken monkey

he/him

German

Vienna, Austria

commented 11 March 2013 at 16:04

Title:	Syntax error or access violation when trying to index content after modifying the index fields	» Fix DB exception when indexing with stale index data
Component:	Miscellaneous	» Code
Status:	Reviewed & tested by the community	» Fixed

OK, committed this now, I hope this fixes the problems.

I'm very sorry for letting you all wait so long. I just thought this would have to be solvable at the root of the problem, instead of this safety code. But I guess having this fixed in some way is better than waiting perhaps indefinitely to find the real cause.

Log in or register to post comments

Comment #31

25 March 2013 at 16:10

Status:

Fixed

» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Log in or register to post comments

Comment #32

summit commented 10 August 2013 at 19:59

Status:

Closed (fixed)

» Active

Hi,
Having this same error still on latest RC2 version, so I hope it is ok to set this to active again.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (item_id = '10100182')' at line 1.

May be does it help that I think these are Drupal Commerce Product Id's.

Greetings, Martijn

Log in or register to post comments

Comment #33

drunken monkey

he/him

German

Vienna, Austria

commented 14 August 2013 at 08:03

Yes, it's OK, thanks.
Are you using the latest version of the Search API module as well?
And does the error occur when indexing or when searching? Did you re-save the index's Fields form? Are you able to reproduce the error from a clean install?

Log in or register to post comments

Comment #34

summit commented 26 August 2013 at 11:47

Hi,
I use the commerce product display search index. I think the error occurs using that.
I need to be able to search through my commerce products..

I use latest .dev of all search api modules. The error occurs when searching I think. I got the error back from many different pages and a seach box is in the right sidebar.
Yes I resaved the index field form. Because I use drupal commerce and different other modules I am not able to reproduce the error from a clean install.

Greetings, Martijn

Log in or register to post comments

Comment #35

drunken monkey

he/him

German

Vienna, Austria

commented 26 August 2013 at 13:52

Priority:

Major

» Normal

Is there anything in the site log, saying whether it occurs in a search or during indexing? Does it occur repeatedly when visiting pages, or just now and again? Does it always occur on the same pages? And also on admin pages?

One possible solution might be to keep on removing fields from the index and seeing if this error stops to occur at some point, then maybe a specific field would be to blame.
But right now, I haven't really got an idea what could be the problem, there's just too little information.

Log in or register to post comments

Comment #36

summit commented 27 August 2013 at 21:14

Hi,
Made a new issue from it: https://drupal.org/node/2075485
Could I not somehow with a debugstatement let show where it is going wrong, instead of this error?
Greetings, Martijn

Log in or register to post comments

Comment #37

drunken monkey

he/him

German

Vienna, Austria

commented 27 August 2013 at 21:27

Status:

Active

» Closed (fixed)

OK, then let's discuss this in the new issue.

Log in or register to post comments

Fix DB exception when indexing with stale index data

Comments

mysql.log