Selecting the author name in my bibliography list, it retruns the following two SQL error messages:

user warning: You have an error in your SQL syntax near 'BOOLEAN MODE) ' at line 1 query: SELECT COUNT(*) FROM node n left join biblio b on n.vid=b.vid WHERE n.type='biblio' AND MATCH(b.biblio_authors) AGAINST('AuthorName?sort=author' IN BOOLEAN MODE) in MyPathHere/www/html/includes/database.mysql.inc on line 172.

user warning: You have an error in your SQL syntax near 'BOOLEAN MODE) ORDER BY b.biblio_year DESC, b.biblio_date DESC, SUBSTRING(n.title' at line 1 query: SELECT * FROM node n left join biblio b on n.vid=b.vid WHERE n.type='biblio' AND MATCH(b.biblio_authors) AGAINST('AuthorName?sort=author' IN BOOLEAN MODE) ORDER BY b.biblio_year DESC, b.biblio_date DESC, SUBSTRING(n.title,1,1) ASC, b.biblio_type ASC LIMIT 0, 25 in MyPathHere/www/html/includes/database.mysql.inc on line 172.

No authors are found. I'm running Drupal 5.1 with the latest biblio.module. Ain't checked the SQL/PHP version my hosting server's running, but if this may help I'm on Mediatemple and usually they are quite up to date with their software. Others Drupal 5.x modules are up and running with no problems at all, all the core ones and the not so many others installed.

BTW the module is great (and useful), thanks for your help.

Comments

rjerome’s picture

rjerome commented

That is strange indeed, I haven't see anything like that before. I suspect that is has something to do with the way you entered author names. Ideally they should be entered Lastname, Firstname I. and if there is more than one author, separate them with semicolons.

kurren’s picture

kurren commented

I'm afraid the way name have been entered has nothing to do with it, since they were entered exactly like that (Name, FirstName). Also what's occurring is instead of the lable Publisher, the displayed label is Source while the related data are those referring to the publisher. No idea where that source thing comes from.

The Full Text field is displayed even if there's no full text entered, and conseguently it doesn't have any date returned. I've already installed AND uninstalled the module a few times, exporting the whole biblio and importing it again. Deleting the module via ftp, dropping tables and manually/automatically remonting them. Alas, no results.

Any suggestion would be greatly apreciated.

Thanks again

rjerome’s picture

rjerome commented

I have to admit, I'm a bit baffled by this one. It sounds to me like the biblio.module file might be corrupted. These are massive errors which I'm sure I would have noticed, and I can't reproduce them.

Can you tell me what the version line says at the top of the file? It should be "biblio.module,v 1.36.2.33 2007/02/09".

Have you tried downloading the .gz file from the server again?

rjerome’s picture

rjerome commented

I forgot to ask, did you have the module working at one time and it stopped or has it always given you these errors?

kurren’s picture

kurren commented

I checked the module file and it's the latest update. Actually every updated version of the module I upload is the correct one. The module always gave me that error. It's only the author linked page that has (this) problem. Sorting by year, author, type, and name are working.

I understand it's quite difficult to replicate these errors; the module always acted like that in my blog.

kurren’s picture

kurren commented

Forgot to say I always run the drupal update.php after each and every last updated module upload. The update process reports no error, drop and mount of new tables are instead correctly reported.

Thanks again for the attention.

ptamas’s picture

ptamas commented
Title: Selecting the author link returns SQL error » opening ~/biblio returns SQL error
Version: 5.x-1.x-dev » 4.7.x-2.x-dev

first up
thank you THANK YOU for the taxonomy tie-in...a huge help...I can now set up for views...if I get it working....

I'm getting a similar error...on 4.7
here is what I did
I replaced the biblio folder in the modules folder of my drupal instal...going from

1.13.2.38 2006/12/07
to
1.13.2.42.2.7 2007/02/08

at the same time fantastico upgraded my drupal installation....so I didn't test the old biblio module on the new drupal installation

when I click on ~/biblio
this is what I get
user warning: Unknown column 'b.vid' in 'on clause' query: SELECT COUNT(*) FROM node n left join biblio b on n.vid=b.vid WHERE n.type='biblio' in /home/rethinki/public_html/cms/includes/database.mysql.inc on line 121.
user warning: Unknown column 'b.vid' in 'on clause' query: SELECT * FROM node n left join biblio b on n.vid=b.vid WHERE n.type='biblio' ORDER BY b.biblio_year DESC, b.biblio_date DESC, SUBSTRING(n.title,1,1) ASC, b.biblio_type ASC LIMIT 0, 50 in /home/rethinki/public_html/cms/includes/database.mysql.inc on line 121.

puzzled I
deactivated the module
went into phpmyadmin and stripped out all the biblio_* tables
removed all taxonomy references to biblio
removed the biblio module from the server.

then I reinstalled the new version of biblio
I used the *.mysql table to create the sql tables
activated the module
and got exactly the same error.

hum......
so I repeated the process and installed the old version of the biblio module....
found that somehow what I'd done broke the auto-install routine
so I used phpmyadmin to load the *.mysql tables

and found that the module worked again
but that
somehow
the 3 sequences of deleting everything named biblio_* in phpmyadmin
had not managed to fully delete the entries that I had loaded....I am left with a rather broken old biblio install....

the upshot
the old version of biblio does work fine on 4.7.4
the new version doesn't seem to work at all
all my exploring left me with a butchered biblio database...what table do I need to delete to clean this up?

thank you

rjerome’s picture

rjerome commented

It sounds like the update.php may not have been run (or did not compete properly).

The best way to completely clean the db and start from scratch is to drop all the biblio_ tables AND delete the row in the system table where "name" = "biblio". Once that is done, you should be able to go to the admin/modules page and re-enable the module, which will reinstall the tables. Let me know if that doesn't work.

Ron.

kurren’s picture

kurren commented

I've tried all the extreme measures, deleted all the drupal installation, deleting everything from the FTP.

Dropped all tables from the database and installed the whole thing again, fres with no content. Uploaded last biblio module and created a brand new biblio content. Nothing. Bugs still the same as above.

rjerome’s picture

rjerome commented

Ok, I have to admit I haven't tried it with 5.1, so I'll do a clean install to make sure it's not related to that. Other than that, I'm running out of ideas. Can you find out what version of PHP and MySQL are being used?

Ron.

rjerome’s picture

rjerome commented

Sorry I guess you are using 4.7, so I just tried a clean install with 4.7 and it all seem to work fine.

Ron.

kurren’s picture

kurren commented

Ok, so this is my system:

  • Drupal version: 5.1
  • Server software: Apache/2.0.46 (Red Hat)
  • PHP Version: 4.4.1
  • MySQL version: 3.23.58
  • biblio.module version: last updated
  • modules enabled (core-required modules not listed): Comment, Menu, Path, Help, Book, Taxonomy, Markxsmary, biblio, Tagadelic

Hope this may help.

rjerome’s picture

rjerome commented

You've probably already done this, but have you tried it without any other contributed modules enabled?

Ron.

kurren’s picture

kurren commented

Indeed, tested as the only contributed module selected but still occurring.

rjerome’s picture

rjerome commented

Ok, I think I know what the problem is now (actually a couple of them, one of them being a dreadfully old version of MySQL :-), and the other is my doing) and I'm working on a fix.

Will keep you posted.

Ron.

kurren’s picture

kurren commented

Great!

Thanks Ron.

rjerome’s picture

rjerome commented

I just committed what I think should fix this problem. Let me know if it works.

Ron.

kurren’s picture

kurren commented

Ok, now things are just slightly better, there's no error message anymore but:

  • Selecting an author give 0 results found even if, obviously since you clikcen on the author's name, the author actually exists
  • Still appears Source: sometimes just the pubblication year, sometimes the full biblio item data
  • Still appears Full Text: and blank space, no full text info were inserted neither the full text fiedl was selected as a compulsory field
rjerome’s picture

rjerome commented

Can you check what type of tables you have (InnoDB, MyISAM), the biblio table needs to be MyISAM type for fulltext searching to work.

kurren’s picture

kurren commented

Hi Ron,
All db tables are MyISAM. Full Text field is showing even when no full text was inserted.

Cheers.

rjerome’s picture

rjerome commented

If it makes you feel any better... I know of at least one other person who is having a similar problem (Full Text showing up when it's empty). I'm working on this one, but since I can't duplicate it on my own systems, it's not easy to track down. Since you are both using different versions of MySQL, I'm beginning to suspect it's some PHP setting, I have no idea what at this point. In the code, I'm checking to see if the fulltext field has any content, and it's only output if it does, so the code seems to think there is something in that field even though I know there isn't.

Right now it seems to defy all logic, but I'm sure there is something staring me in the face that I just can't see.

jouke.postma’s picture

jouke.postma commented

I have the same problem. With older versions I had always trouble with iconv() tried now I have the user warning: Unknown column 'vid' in 'field list' query: ...

Drupal 4.7.2, 2006-06-01
mysql Ver 12.22 Distrib 4.0.20, for mandrake-linux-gnu (i586)
PHP Version 4.3.8

rjerome’s picture

rjerome commented

Sounds like you need to run the update.php script in order to update the database tables.

catdevrandom’s picture

catdevrandom commented
Status: Active » Closed (fixed)