Problem with 0.5
| Project: | XML sitemap |
| Version: | 6.x-1.0-rc1 |
| Component: | xmlsitemap.module |
| Category: | support request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
after utpdating module I get this error:
user warning: 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 '5, PRIMARY KEY (lid), INDEX link_module (module(25)), INDEX link_language (la' at line 11 query: CREATE TABLE xmlsitemap ( `lid` INT NOT NULL auto_increment, `loc` VARCHAR(255) NOT NULL DEFAULT '', `module` VARCHAR(255) NOT NULL DEFAULT '', `type` VARCHAR(32) NOT NULL DEFAULT '', `id` INT unsigned NOT NULL DEFAULT 0, `sid` INT unsigned NOT NULL DEFAULT 0, `language` VARCHAR(12) NOT NULL DEFAULT '', `changed` INT unsigned NOT NULL DEFAULT 0, `changefreq` INT unsigned NOT NULL DEFAULT 0, `priority` FLOAT NOT NULL DEFAULT 0,5, PRIMARY KEY (lid), INDEX link_module (module(25)), INDEX link_language (language), INDEX link_changed (changed), INDEX link_priority (priority) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /home2/skopenac/public_html/includes/database.inc on line 517. user warning: Table 'skopenac_oa.xmlsitemap' doesn't exist query: SELECT COUNT(xsm.loc) FROM xmlsitemap xsm INNER JOIN system s ON s.name = xsm.module WHERE s.type = 'module' AND s.status = 1 AND xsm.changefreq <> 0 AND xsm.changed <> 0 AND (xsm.priority >= 0 AND xsm.priority <= 1) in /home2/skopenac/public_html/sites/all/modules/xmlsitemap/xmlsitemap.module on line 283.
I found that problem is in float value 0.5 that Drupal convert to slovak native value "0,5"
solution is simple I was using string value in code '0.5' and all works fine. I am not sure that is the problem of this module or Drupal itself.
#1
It is a problem with Drupal. XML sitemap implements a hook to let Drupal know which database schema it uses, and it is then a Drupal core function that builds the necessary SQL query to create the table.
The database schema uses this definition for the priority field:
<?php'priority' => array(
'description' => 'The priority of this node in the sitemap.',
'type' => 'float',
'not null' => TRUE,
'default' => 0.5,
?>
#2
Which version were you updating from?
The bug would be Drupal's since we define the schema and then just call a drupal_install_schema() function or maybe higher than Drupal and PHP converts the floating to your native value. I don't know. But if modifying the schema by simply adding quotes to the default works then that's certainly an easy workaround.
Patch needs review.
#3
The patch works if the database engine uses the floating value put between quotes as a floating number. I am not sure that is always the case, or it's not the case at all.
#4
Here is the problem, and there is nothing that XML sitemap can do because this will pop up anywhere else that this same situation happens. Somewhere in your Drupal install, something has called the PHP function setlocale(), which can automatically convert numbers to a localized version, like in French instead of a decimal point, a comma is used, which is exactly what is happening in this case. This causes *SEVERE* problems with other modules as well and must *NOT* be used.
See:
#193214: %f is locale dependent in db_query
#389452: setlocale() calls in Gallery wrongly transform float numbers in SQL
#290538: SQL error: operand should contain 1 column(s)
http://www.google.com/search?hl=en&safe=off&q=+site:drupal.org+drupal+se...
Also marked #509764: Trying to help for bug search... sitemap.xml not populating as a duplicate of this issue. This is technically a duplicate of #290538: SQL error: operand should contain 1 column(s) since we have encountered this before.
#5
Thanks for the detail Dave. I thought maybe it was something like this and the other issues seemed to fall in line. I just wasn't aware of what could have caused it at the PHP level.
#6
Coming from #509764: Trying to help for bug search... sitemap.xml not populating...
Looking at the links, they are all dead ends, duplicates or wont fix.
What are you saying Dave? That I should fill a bug report on php.net about this?
Would this bug already there? http://bugs.php.net/search.php?search_for=setlocale&boolean=1&limit=30&o...
That's large... let's check it out...
.../drupal-6.13$ grep setlocale -R *
includes/unicode.inc: setlocale(LC_CTYPE, 'C');
Would that be the problem? unicode.inc? Should I fill a bug report with Drupal?
Could Drupal do a pre-check or a patch to bypass this bug?
There is a bug, and we need to fix it somehow... :)
Help me, and I'll help you :)
#7
setlocale(LC_CTYPE, 'C')would set the local used by C language, which means the decimal separator would be the point, not the comma.#8
@alexandreracine:
#9
According to Database schema reference:
#10
@earnie:
PHP 5.2.9
No, the link to the PHP bugs.php.... was a search for all active bugs containing "setlocale" in PHP version 5.
I am using MySQL 5.0.77
I don't think so...
Sure, I could test that, let me get back to you. But it does sound odd to test it like that...
#11
So, I did this on my site
-Delete the result of "SELECT * FROM `system`WHERE `name` LIKE 'xmlsitemap%' LIMIT 0 , 30"
-Delete all xmlsitemap* tables
-Upload the module xml...rc1
-List the modules, all are there.
-Comment out the line " // setlocale(LC_CTYPE, 'C');" in the /includes/unicode.inc file and save
-Install the XML sitemap module...
-Same error then here http://drupal.org/node/509764#comment-1787042
After a while, I setup a DEV site witch is a restore of the prod site. Installing the xmlsitemap works..., but there are a few differences.
Those modules are deactivated
-AddThis
-Ping
-Update status
-Gallery
-Simplenews
-Google Analytics
I'll do more tests tomorrow...
#12
@Kiam: I think even the dumbest SQL engine converts types but I may be wrong.
@alexandreracine: Thanks for the information and testing. I wanted to eliminate any possibility that Drupal core was "at fault" (relatively) with the issue. At least you have a base line in your dev setup to work with now. Probably one or more of the modules you mention is causing the issue or perhaps an environment setting between the two environments.
#13
What I reported says that "the PHP type of the value matters"; that means the problem is on what Drupal does.
#14
Everybody:
1.
setlocale(LC_CTYPE, 'C');in unicode.inc is the perfectly ok because it only attempts to convert strings (LC_CTYPE = character classification and conversion, for example strtoupper()). Drupal core is not at fault.2. There is nothing wrong with XML sitemap. Uninstalling and reinstalling is not going to help.
3. What is going wrong is that you have another Drupal module somewhere, calling
setlocale(LC_NUMERIC)orsetlocale(LC_ALL)which automatically converts any numbers used in Drupal. You need to find where this happens and comment it out. Otherwise it can and will cause major problems with many more modules that try to insert decimal/float values into the database.#15
And hey, guess what. It's probably the Gallery module. We would have known that if you had read #389452: setlocale() calls in Gallery wrongly transform float numbers in SQL which I had linked to in #5.
Marking this again as won't fix.
#16
As what reported is not a XML sitemap bug, the category should be changed.
#17
Automatically closed -- issue fixed for 2 weeks with no activity.
#18
Just to update this. I deactivated gallery, installed the last xmlsitemap, updated everything and it works. After all was done, I reactivated the gallery module and everything is still fine.