Workbench_moderation is declaring a column 'current' in table 'workbench_moderation_node_history'.
'current' is a reserved word in SQL-99 standard ( see http://developer.mimer.com/validator/sql-reserved-words.tml)
Drupal coding standard bans usage of reserved words (see https://drupal.org/node/2497#reserved-words)
Conclusion: this is bad!
The issue is currently minored by the facts that:
- It is not affecting Drupal installation using MySQL database in a ordinary usage of the module.
- It is affecting Drupal installation using:
- Microsoft SQL Server database with the specific drupal driver project (sqlrv) only before the fix of #1900840: If field name is a keyword, query fail in this project (This fix prevent the problem from happening by quoting more systematically all application-defined names). This fix has not been delivered yet in a stable release of the sqlrv project (02/10/2013).
- Oracle 11g
The fix proposal is consisting in renaming 'current' wherever it is used by 'is_current', Especially in the schema. Implication for existing installations is the renaming of the column in database...
Below an example of the error that is happening when installing the module Workbench Moderation on a MsSQL-based drupal installation:
PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'current'.: CREATE TABLE [{workbench_moderation_node_history}] ( [hid] int NOT NULL IDENTITY, [vid] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_vid_df DEFAULT 0 CHECK (vid >= 0), [nid] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_nid_df DEFAULT 0 CHECK (nid >= 0), [from_state] nvarchar(255), [state] nvarchar(255), [uid] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_uid_df DEFAULT 0 CHECK (uid >= 0), [stamp] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_stamp_df DEFAULT 0 CHECK (stamp >= 0), [published] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_published_df DEFAULT 0 CHECK (published >= 0), [current] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_current_df DEFAULT 0 CHECK (current >= 0), CONSTRAINT {workbench_moderation_node_history}_pkey PRIMARY KEY CLUSTERED ([hid]) ); Array ( ) in db_create_table() (line 2657 of C:\www\includes\database\database.inc).
Comments
Comment #1
Dave ReidIt appears that 'current' is in fact a SQL-99 reserved word: http://developer.mimer.com/validator/sql-reserved-words.tml.
We need to address this.
Comment #2
Dave ReidComment #3
pomgod CreditAttribution: pomgod commentedfollowing! Having the same problem.
Comment #4
robeano CreditAttribution: robeano commentedI'll take this one.
Comment #5
pomgod CreditAttribution: pomgod commentedRobeano, I've took the "dirty road" and changed all 'current' to 'iscurrent' in every file of the module. This seems to work (for now). Not yet sure if I'm losing functionality yet but at least it does install and it does give the impression of functioning well...
Comment #6
robeano CreditAttribution: robeano commentedAdding a patch that changes the workbench_moderation_node_history table column 'current' to 'is_current'. I see some other columns I'd like to change too stamp but I will handle that in a separate issue.
Comment #8
stevectorComment #9
ben.edwards CreditAttribution: ben.edwards commentedAnother follower, having same problem. Would really love to see this module work for SQL Server and SQL99 databases. Workbench is awesome for workflow. Since it's been a couple of months since there was any action on this issue, what are my options, realistically (well, besides reverting to MySQL)?
Comment #10
stevectorBen, please review the patch in #6 and post back any changes you make.
Comment #11
ben.edwards CreditAttribution: ben.edwards commentedNo luck.
I reverted to a VM snapshot (database included) from before I installed the Workbench Moderation module. I copied the module files, applied the patch, did a spot check on the patched code, enabled the Workbench Moderation module, clicked "Save configuration", and the Module page just sort of blinks. If I click "Save configuration" again, I just get a blank page with the URL of .../admin/modules/list/confirm. The workbench_ database tables were not created. Tried clearing Drupal cache, rebooting server, etc. I can un-enable the module and save configuration just fine.
The changes in the patch look reasonable, but I'm new to PHP and Drupal so it could be anything at this point.
The starting code was 7.x-1.1. Does it need to be the dev version?
I jus tried the dev version, same behavior.
Comment #12
designingsean CreditAttribution: designingsean commentedAttached is my attempt at the same fix #6 was speaking of. This is against 1.x-dev. I have a similar setup (SQL Server) and the following changes seemed to have fixed this issue for me.
Comment #13
designingsean CreditAttribution: designingsean commentedDisregard #12, and go with this one instead. I realized this morning that I had forgotten to include a function to update current installs.
Comment #14
ixionx CreditAttribution: ixionx commentedI'm getting
when trying to apply this patch to the dev release.
Any ideas? Looking in the 'views' folder the file certainly appears to exist!
Comment #15
designingsean CreditAttribution: designingsean commentedNot sure what the issue is. This is my first time creating a patch, so I very well could have screwed something up. I did follow some instructions on http://drupal.org/node/1319154 for testing the patch on my machine, and it was applied without errors.
I am attaching a new patch, which deals with a bug I ran in to at one point. I have also noticed that Drupal wants to run the database update, but the column name on my install is already correct. If someone wants to tweak my patch (or tell me how to fix that), that would be great.
Comment #16
gabriel.achille CreditAttribution: gabriel.achille commentedpatch #15 worked for me on MS SQL 2008.
module patched before installation.
Thanks
Comment #17
hass CreditAttribution: hass commenteddisp-width is not in the api and mysql specific...
Comment #18
gabriel.achille CreditAttribution: gabriel.achille commented@hass: You're right (disp-width). feel free to open another issue for that. But this is not the purpose of this one and furthermore this is not blocking in this patch (Is this optional parameter is simply ignore by the sqlsrv driver ?...)
Comment #19
hass CreditAttribution: hass commentedNo. It need to be removed from the patch. Drupal api does not support this.
Comment #20
gabriel.achille CreditAttribution: gabriel.achille commentedI just wanted to say that AFAIK disp-width has NOT been added in this patch. It is a part of the module since long time. But If you think it should be linked to the "current" issue, then ok, I can work on this. I'm new to drupal.
Comment #21
hass CreditAttribution: hass commentedPlease review the patch. Update 7008 adds disp-width. The line need to be removed and than the fix needs review.
Comment #22
hass CreditAttribution: hass commentedMarked #1458366: Error 500 with drafts and needs review pages as duplicate. This issue may contains some additional info that require some work on the patch here.
Copy of issue summary:
Comment #23
hass CreditAttribution: hass commented#15: workbench_moderation-is_current-1260112-15.patch queued for re-testing.
Comment #24
hass CreditAttribution: hass commentedRemove this line.
Comment #26
katannshaw CreditAttribution: katannshaw commentedMy setup: Drupal 7.19 / IIS 7.5 / SQL Server 2008
I ran into the same issue today, but these steps worked for me along with patch #13 as noted above:
Thanks for the patch.
Comment #27
katannshaw CreditAttribution: katannshaw commentedUPDATE:
After adding moderation to the Basic Page content type and selecting the following options:
...I received the following notices:
Has this happened to anyone else? Does anyone have any ideas on what this means and how I can fix this issue? It's nice to work with the entire Workbench Suite with the SQL Server Driver now, but having little bugs still worries me.
Comment #28
sylus CreditAttribution: sylus commentedAttaching update to latest dev sha: d196989
Also removed disp-width as per comment: http://drupal.org/node/1260112#comment-6959480
Comment #30
hass CreditAttribution: hass commented#28: workbench_moderation-is_current-1260112-28.patch queued for re-testing.
Comment #31
katannshaw CreditAttribution: katannshaw commentedI tried installing it twice and when I tried to enable the module, it unfortunately crashed my site with the following error both times:
Comment #32
gabriel.achille CreditAttribution: gabriel.achille commented@ jayhawkfan75: it is a weird behavior indeed, i wasn't expected that. The correction has been made in the
workbench_moderation_node_history
notworkbench_moderation_transitions
.You may have another error due to an old version of ms-sql driver. I'm running
sqlsrv_client_info(...)
:Do you have a way to update the driver ? (see http://drupal.org/node/1686872#comment-6246446)
Comment #33
hass CreditAttribution: hass commentedYaj: can you verify if the transition table exists in the database, please? I see no reason why schema install should fail.
Comment #34
katannshaw CreditAttribution: katannshaw commentedFor me this is a complete fresh install attempt of the Workbench Moderation module that fails with the error I posted on comment #31. The main Workbench, Workbench Access, and Workbench Media were installed first and are all working fine.
None of the moderation tables get created for me with this attempted install, including "workbench_moderation_transitions.". The only workbench tables in that database are the one's related to the working Workbench modules already mentioned.
My version of SQLSrv Driver module: 7.x-1.2, which is the most-recent version as of Jan 13, 2012.
Where else should I check to make sure that everything's up-to-date? Shouldn't that Sqlsrv Driver module take care of that? Thanks for your help.
Comment #35
hass CreditAttribution: hass commented@jayhawkfan75: Please open a new issue. This looks not related to this case and you need to debug the schema install on your server.
http://api.drupal.org/api/drupal/modules%21system%21system.api.php/funct...
If something goes wrong in hook_schema all other stuff must fail. You may give #1918234: Remove non-standard MySQL disp-width attribute from schema definition a try. I'm not sure what the effect of the MySQL attribute
disp-width
is on MsSQL.Comment #36
katannshaw CreditAttribution: katannshaw commentedAs noted in my #27 comment, I did receive the same error as the original poster when trying to use this module. After I applied the patch from #28, this new error appeared as I noted on comment #31.
I've started a new issue concerning this new error.
Please keep in mind that I'm still a newbie, so "debugging the schema install of your server" is above my head. I'll need help from you and/or the community on getting this fixed. Thanks.
Comment #37
hass CreditAttribution: hass commentedWithout having it tested I think it need to mean
$handler->display->display_options['filters']['is_current'][*] = 'is_current';
, but I'm not 100% sure. Normally this 'current' is the field name.Comment #38
hass CreditAttribution: hass commentedRe #36: Just my personal suggestion - Don't use Drupal with MsSQL or Oracle if your are a newbie. Run it on MySQL only :-)
Comment #39
katannshaw CreditAttribution: katannshaw commented@hass: I unfortunately don't have that option, as I work in a government that's a Microsoft-only shop at this point. So I've got to work with the SQLSrv Driver.
I heard that MySQL will eventually be going away. If that's the case, what's going to replace it? PostgreSQL?
Another important note is that I've read from several Drupal sources that Drupal 7+ was changed so that module creators and maintainers would work with a non-database specific language so that these sorts of issues wouldn't happen. But it appears from my recent experiences that this is not happening as it should have. I think that focusing only on MySQL paints module creators into a corner, as there are several users who use other options like Oracle, MS SQL Server, and PostgreSQL.
Although I love Drupal for countless reasons, that's got to be the most disappointing thing about working with Drupal 7 at this point. It's direction doesn't seem clear to us newbies out there.
Comment #40
hass CreditAttribution: hass commentedPatch with changed view field names.
Comment #41
hass CreditAttribution: hass commentedWho told you this bullsh**? MS? If Oracle try this you move on to MariaDB that is a MySQL server.
If you need to use MsSQL you are better a very good developer of Drupal or your are alone in the dark! *G* When I started with Drupal I was also a MsSQL fan and we are still developing all our ColdFusion applications on MsSQL... years ago I wished there would be MsSQL support in Drupal, but there wasn't at all.
The problem about lacking MsSQL support in PHP is mostly Microsoft. I guess they understood this in last 2 years as they released the PDO drivers for PHP. They have not provided them in the past and this is still the source of a lot of troubles. They also have no LIMIT in MsSQL that causes a lot of performance issues with large range queries. Theory and practice about DB independence is not the same.
SQL2012 may be a good server, however licensing costs are now soooo crazy high with the new 2 core only licensing that it's now finally time to move away from MS. Installing MySQL is free... you don't need to ask anyone about giving you money for it. Just download and install it and you are free of these headaches. There are still enough others to handle... :-)
Comment #42
katannshaw CreditAttribution: katannshaw commentedNo, MS never told me that. I've heard that from a coding friend who says he thinks PostgreSQL will be taking over MySQL's place with open-source database users, and I've seen several people discussing it online. It could quite possibly be because users are worried that because Oracle bought it, there will be eventual consequences for users. Maybe people are worried that users would have to pay at some point, I don't know. But I'm just repeating what I'm hearing others say.
Just Googling "will mysql stay free" gets you these results: http://goo.gl/zajiy. There are a lot of worried MySQL users out there. We'll have to wait and see, and hope that Oracle keeps MySQL open-source.
Here's just one article that I've read before about Drupal wanting to expand to other database types using their new database API with version 7+:"Simplify your code with Drupal 7's Database API". I even remember watching a video on it at Drupalcon I believe. There are a vast amount of similar sources of information on this subject out there, which is one major reason why I chose Drupal over other options with my environment.
FYI: Users like me don't have the option to use MySQL or another open-source option, so we work with what we have to work with. Thankfully the MSSQL Driver does a pretty good job of that. So far it's been very successful for me, except for a few tweaks.
As I mentioned, per your suggestion I've created a new issue report from #31, and would appreciate you or any others input on it if you have time.
Comment #43
hass CreditAttribution: hass commentedIn case Oracle play games with us. d.o runs on MariaDB as I know. However... all OT here. Let us stop here :-)
Comment #44
katannshaw CreditAttribution: katannshaw commentedI've heard about MariaDB as well...sounds like a good option for MySQL users.
I absolutely agree; lets stop here. Thanks for the interesting conversation hass;-)
Comment #45
gabriel.achille CreditAttribution: gabriel.achille commented@jayhawkfan75
I'm suspecting that you are running an out-of-date version of php-mssql driver, could you please run this script on your server (The same machine that is currently running your drupal installation). Just replace before USERNAME, MY_PASSWORD and DATABASE_NAME by your own information. If your MS-SQL server is not on the same server, you will have to change the "servername" variable too.
Copy the script content to a file:
test-sql-server.php
, copy this file into your drupal root folder temporarly and access to the file with your browser: http://my-drupal-server/test-sql-server.php This should give you precise information about your SQL-server installation. Please paste that information here. (see my comment above)Comment #46
katannshaw CreditAttribution: katannshaw commented@gabriel.achille: Thanks for trying to help me out with this issue. I followed your instructions, and this is what the page displays:
DriverDllName: sqlncli10.dll
DriverODBCVer: 03.52
DriverVer: 10.50.2500
ExtensionVer: 2.0.1802.200
CurrentDatabase: Drupal
SQLServerVersion: 10.50.2500
SQLServerName: MYSERVERNAME
Is this all how it should be, or is something out-of-date? I work at staying current with the newest release of Drupal core, PHP, all modules, and the SQL Server Driver, so I'm not sure what could be out-of-date. Your help is much appreciated.
Comment #47
gabriel.achille CreditAttribution: gabriel.achille commentedOk I see.
You may run the latest version of drupal+php+modules (including the drupal SQL Server Driver), you have to update Microsoft Drivers for PHP for SQL Server. This is one layer below drupal and the drupal SQL server driver. It allows the PHP (drupal in this situation) to communicate with the SQL Server.
Can you try to upgrade to the 3.0.x version of the PHP driver for SQL Server, choose the file SQLSRV30.EXE and install it on the machine that is running drupal.
Once it is done, you should see the output more similar to my comment #32 (ExtensionVer: 3.0.XXXX.X) when running the script
test-sql-server.php
.If it is ok, please test again the patch.
Comment #48
jaypark CreditAttribution: jaypark commentedam running sqlsrv with same driver version info at #32.
patch at #40 worked for me... grazie!
Comment #49
katannshaw CreditAttribution: katannshaw commented@gabriel.achille: We're working on getting the newer drivers installed, but I was hoping that you could help me with something.
Right now the two driver filesnames are:
php_sqlsrv.dll
php_pdo_sqlsrv.dll
But the new download has these two possible file combinations listed in the Help's installation section:
Driver file: php_sqlsrv_53_nts.dll and php_pdo_sqlsrv_53_nts.dll
PHP version: 5.3
Thread safe? no
Use with PHP .dll: php5.dll
or
Driver file: php_sqlsrv_53_ts.dll and php_pdo_sqlsrv_53_ts.dll
PHP version: 5.3
Thread safe? yes
Use with PHP .dll: php5ts.dll
Do you know which set we should install? I'm thinking the 2nd one, but I'm not for sure. Thanks.
Comment #50
gabriel.achille CreditAttribution: gabriel.achille commented@jayhawkfan75
I think it depends what you are already running on your server: look inside your PHP folder (could be something like C:\Program Files (x86)\PHP\v5.3):
Most of the installations run the normal version (ie the first option): without the thread safe.
Comment #51
katannshaw CreditAttribution: katannshaw commentedGood to know gabriel.achille. The first option is the that we have indeed. I'll get it upgraded and then retry the patch, and I'll let you know of the outcome. Thanks for all of your help with upgrading my drivers.
UPDATE:
My Network Administrator installed the drivers but now my site is giving this message: 404 - File or directory not found. The resource you are looking for might have been removed, had its name changed, or is temporarily unavailable. So I'm not sure how to troubleshoot this issue. I tried putting a phpinfo() file within the PHP directory, but it just runs in a loop without getting displayed when I try to open it in a browser window. Do you have any suggestions?I think that my My Network Administrator installed wrong version of drivers, so I'll let you know if this gets fixed. Thanks.
Comment #52
katannshaw CreditAttribution: katannshaw commentedSo I just successfully updated the drivers and this is what I see:
DriverDllName: sqlncli11.dll
DriverODBCVer: 03.80
DriverVer: 11.00.2100
ExtensionVer: 3.0.3421.0
CurrentDatabase: Drupal
SQLServerVersion: 10.50.2500
SQLServerName: MYSERVERNAME
I then tried downloading, patching and installing the module and received the same error as I had before on comment #31. I did see a message that it was only a partial patch, so that could be why.
But just as before, no new tables were created for this module on this attempt. I did see it within the system table and withing sites/all/modules, but that was it. So unfortunately I'm back to square one. Bummer.
I'll update the issue report that I created after posting to this one. If you have any advice, please let me know. Thanks.
Comment #53
gabriel.achille CreditAttribution: gabriel.achille commentedtricky...
Comment #54
nitram2000 CreditAttribution: nitram2000 commentedThere is a patch published at http://drupal.org/node/1208258 which fixes quoting-problems within the sqlsrv-driver. After applying the patch you will be able to install workbench_moderation.
Best regards,
Martin
Comment #55
katannshaw CreditAttribution: katannshaw commented@nitram2000: I just applied the patch to schema.inc for sqlsrv and re-installed Workbench Moderation with much success...yea!!! Hopefully other users can try this patch out and verify that it works for them as well.
Thank you so much for all of your help on this. I love this community!
Comment #56
gabriel.achille CreditAttribution: gabriel.achille commented@jayhawkfan75
Cool! So it is a "good to go" for you about this particular patch as well ?
Comment #57
katannshaw CreditAttribution: katannshaw commented@gabriel.achille: The SQLSrv patch that nitram2000 suggested on #54 fixed this issue for me. After the patch, I was able to install Workbench Moderation without any errors, and I was able to configure and use it successfully on a content type. So it appears that I'm good-to-go.
I also ran the patch on #40 to see what it would do, and it caused that error to reappear. So for me it seems that the patch on comment #40 works for non-SQL Server users, and the SQLSrv patch works for SQL Server 2008 DB users.
So I'm not sure what that means for #40 being committed to the full module release if it won't work for SQLSrv users.
Hope it helps.
Comment #58
Sheldon Rampton CreditAttribution: Sheldon Rampton commentedBased on the comments by @jayhawkfan75, I don't know if the patch at #40 is ready to be committed, but in the meantime another patch has been committed to the 7.x-1.x branch which already adds a workbench_moderation_update_7008() function, thus creating a conflict with this patch. I'm therefore rerolling patch #40 to change the hook_update_n() function to workbench_moderation_update_7009().
Comment #59
gabriel.achille CreditAttribution: gabriel.achille commentedjust reviewed the issue summary with recent updates. + tag sqlsrv
Comment #59.0
gabriel.achille CreditAttribution: gabriel.achille commentedsummary of the issue perimeter (especially impact of recent fix in sqlsrv)
Comment #60
drikus CreditAttribution: drikus commentedI have the same problem installing "workbench moderation" using sqlsrv.
Here the message :
PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'current'.: CREATE TABLE [{workbench_moderation_node_history}] ( [hid] int NOT NULL IDENTITY, [vid] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_vid_df DEFAULT 0 CHECK (vid >= 0), [nid] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_nid_df DEFAULT 0 CHECK (nid >= 0), [from_state] nvarchar(255), [state] nvarchar(255), [uid] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_uid_df DEFAULT 0 CHECK (uid >= 0), [stamp] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_stamp_df DEFAULT 0 CHECK (stamp >= 0), [published] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_published_df DEFAULT 0 CHECK (published >= 0), [current] int NOT NULL CONSTRAINT {workbench_moderation_node_history}_current_df DEFAULT 0 CHECK (current >= 0), CONSTRAINT {workbench_moderation_node_history}_pkey PRIMARY KEY CLUSTERED ([hid]) ); Array ( ) in db_create_table() (line 2717 of D:\DrupalExhibitions\includes\database\database.inc).
Do you think the module will release an update for this issue..?
Comment #61
_randy CreditAttribution: _randy commentedSame issue here. #58 works for me.
Comment #62
msdevweb CreditAttribution: msdevweb commentedDear all,
While this is a specific SQL Server related issue, it does also occur on the Oracle 11g database also.
This should be a Critical level bug as it does block entirely the module functionality. We are using it in an European Institution project and it would be great to see this fixed, but due to this issue the module functionalities are entirely compromised and it becomes unusable.
Patch from comment #40 solves this issue, but leaves us concerned about any future module updates, if they will include this patch or not. Please merge it on the code as soon as possible in order to have it on the next module binary update.
Kind Regards,
Mario Santos
Comment #63
gabriel.achille CreditAttribution: gabriel.achille commentedupdate the summary: adding Oracle
Comment #64
colanNeeded to increment the update ID again.
Comment #66
colanComment #67
colanForgot to set this up for forward porting.
Comment #68
das-peter CreditAttribution: das-peter commentedDoes not apply to the 2.x branch, it uses State Machine / State Flow which does this handling for us.
Comment #70
nerdoc CreditAttribution: nerdoc as a volunteer commentedJust wanted to add that this fix breaks any view that uses this table. Broke mine.
This is bad as you do not expect it.
changing the API is not really nice without increasing the major version number.
Eventually the update routine could check views that use that "current" field and rename it in the view?
Comment #71
richard.thomas CreditAttribution: richard.thomas commentedYeah I've run into this breaking a bunch of our existing views as well, did we have to rename the views handler just because the column changed? The "real field" property of the handler looks like it can be used to point it to a different column. But I guess at this point we can't switch back as it will break any views created with the current version. At the very least this needs a release note or something to warn people.
Comment #72
coredumperror CreditAttribution: coredumperror commentedAgreed about this API change needing a much more prominent note. It took me quite a while to figure out why some custom DB-facing code I wrote a year ago, which is used rarely enough that it didn't crash until yesterday, was suddenly broken. The fact that an API change wasn't mentioned in the equivalent of bold, capital letters at the top of the 1.4 release notes is very much not good.