Strange error when creating new resource with MSSQL database

44 Posts

javadecaf Reply #1, 13 years, 5 months ago

Using MODx 2.2.7, with MS SQL Server 2008 (not my first DB choice, but client's application required a bridge to legacy .dbf files).

Strange error shows up when I try to save a new resource. In the manager, the popup window states "An error occurred while trying to save the resource." In the error log, this is what it looks like (with site and resource names privatized):

[2013-04-22 22:33:23] (ERROR @ /site_name/connectors/resource/index.php) Error 23000 executing statement:
INSERT INTO [modx_site_content] ([type], [contentType], [pagetitle], [longtitle], [description], [alias], [link_attributes], [published], [pub_date], [unpub_date], [parent], [isfolder], [introtext], [content], [richtext], [template], [menuindex], [searchable], [cacheable], [createdby], [createdon], [editedby], [editedon], [deleted], [deletedon], [deletedby], [publishedon], [publishedby], [menutitle], [donthit], [privateweb], [privatemgr], [content_dispo], [hidemenu], [class_key], [context_key], [content_type], [uri], [uri_override], [hide_children_in_tree], [show_in_tree]) VALUES ('document', 'text/html', 'Document Name', '', '', '', '', 0, 0, 0, 0, 0, '', '', 1, 1, 2, 1, 1, 1, 1366662802, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 'modDocument', 'web', 1, '', 0, 0, 1)
Array
(
[0] => 23000
[1] => 515
[2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert the value NULL into column 'id', table 'site_name.dbo.modx_site_content'; column does not allow nulls. INSERT fails.
)

I have to admit that I am far from an expert in MODx/DB interaction - I don't even know if 'id' should be null in this query or not. Can someone more knowledgeable point me in the right direction?

44 Posts

javadecaf Reply #2, 13 years, 5 months ago

Well, as usual, all I had to do was post the problem, and then I found a solution (sort of). I think this is probably a bug, because manually putting an auto-increment (which SQL Server calls an "identity") on the 'id' column made the error go away, and the save is successful. Tested creating a new snippet, and found the same problem and solution. Anyone have any explanation for this, other than a bug?

175 Posts

stalemate resolution associate Reply #3, 13 years, 5 months ago

Hi javadecaf,

tl;dr: I'm almost positive it's a bug, and I think that your fix was the right thing to do.

I'm much more familiar with MSSQL than I am with MySQL and I've also used MSSQL to do MODX development. I agree that this would have to be a bug... my offhand guess is that when the installation took place, xPDO created the tables and when it went to either add the IDENTITY column (or alter the table to make that happen), the Identity creation failed.

Out of interest, which version did you initially install, and did you upgrade subsequently to another version?

Regards,
Tom

44 Posts

javadecaf Reply #4, 13 years, 5 months ago

Tom,

Thanks for weighing in - do you mean which version of MODx, or which version of MSSQL? I've always been on SQL Server 2008 (that I remember) because that's the latest version that comes with the ODBC driver for DBF, which is a feature this particular application had to have. I did upgrade yesterday from an earlier version of MODx to 2.2.7, but the problem was present in both versions.

Since you are a MODx / MSSQL developer, do you have any suggestions on how to fix the broken tables en masse? That is, as opposed to manually editing each table that throws an error, and adding the IDENTITY column (which is what I've been doing)?

Zach

175 Posts

stalemate resolution associate Reply #5, 13 years, 5 months ago

Hi Zach,

In terms of identifying the tables which don't have an identifier, you could write a query like the following:
select o.name, c.name, c.is_identity
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
order by o.name, c.colid

I'm not sure if the query syntax is correct. The idea should be that most if not all modx tables should have an identity column, so if you see a table that doesn't have one, you probably need to add one.

Run this query against the database where you have MODX installed (i.e. not master).

Hope this helps,
Tom

MODX Staff
10,725 Posts

opengeek Reply #6, 13 years, 5 months ago

Can you confirm that the tables are being created without the identity columns on a clean install of MODX 2.2.7 on SQL Server? Is there an install log containing any errors in your core/cache/logs/ directory?

NOTE: I do not have a Windows environment, let alone SQL Server or IIS, to test this configuration any more. I could REALLY use some volunteers to assist with debugging and contributing patches specifically for SQL Server.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

175 Posts

stalemate resolution associate Reply #7, 13 years, 5 months ago

Hi opengeek,

I'll try an install tonight at home with MODX 2.2.7 with Windows SQL Server 2008 and will let you know what I come up with.

Please add me to your list of volunteers for SQL Server; I've worked with SQL server on a daily basis since 2004, so if there is any way I can help, please let me know.

Regards,
Tom

44 Posts

javadecaf Reply #8, 13 years, 5 months ago

Quote from: stalemate at Apr 23, 2013, 08:40 PM

In terms of identifying the tables which don't have an identifier, you could write a query like the following:
select o.name, c.name, c.is_identity
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
order by o.name, c.colid

Thanks very much for the tip. I guess I was hoping to find a query that would let me set the IDENTITY column, rather than just locating the tables that don't have one. (I really doubt that any of them do, at this point, except the ones I manually fixed.) To do this, of course (if it's even possible) I'd have to know which tables need an IDENTITY column, and which column it should be. I'm guessing that it would be 'id' on everything, but I'd have to confirm.

Quote from: stalemate at Apr 24, 2013, 08:11 AM

I'll try an install tonight at home with MODX 2.2.7 with Windows SQL Server 2008 and will let you know what I come up with.

If your clean install proves not to have this bug, could you run a modified version of the above query and find any tables that don't use 'id' as the IDENTITY column?

44 Posts

javadecaf Reply #9, 13 years, 5 months ago

Quote from: opengeek at Apr 24, 2013, 07:58 AM

Can you confirm that the tables are being created without the identity columns on a clean install of MODX 2.2.7 on SQL Server? Is there an install log containing any errors in your core/cache/logs/ directory?

NOTE: I do not have a Windows environment, let alone SQL Server or IIS, to test this configuration any more. I could REALLY use some volunteers to assist with debugging and contributing patches specifically for SQL Server.

Since Tom was kind enough to volunteer, and is no doubt far more proficient in MSSQL than I, I'll defer to him on the clean install test. I do have some error logs which I could email you, if you think they would prove enlightening - just give me an email address if you'd like to look at them.

Also, as long as you're on this thread, let me run something else by you. Due to some rather unique requirements of the particular application I'm building (using MODx as a framework) I need to use MSSQL linked server syntax in my SQL queries. In order to make SQL Server accept "LINKED_SERVER...TABLE" as a table name, I had to hack the escape() method in xpdo.class.php to exclude any string with "..." in the middle from being escaped. What I wondered was 1) whether the whole escape() method could somehow be overridden in the DB driver and 2) whether a special driver (duplicated from the packaged MSSQL driver but with my special escape () method) could somehow be employed.

While my particular situation is no doubt quite exceptional, I could imagine that others might have special string-escaping requirements that would go beyond simply specifying an escape character. (That, by the way, was the first thing I tried, but it didn't work in my case.) I can also imagine that someone out there might like to use MODx with an unsupported DB of some other stripe, and might benefit from the ability to specify a custom-coded DB driver. So, are either of these features currently available, or in the works for MODx?

175 Posts

stalemate resolution associate Reply #10, 13 years, 5 months ago

Hi Zach and Jason,

I just did a complete from scratch install of the latest version of modx using SQL server. During the installation, I had some errors pop up (as seen below the post), but when I ran this query:

select o.name, c.name, c.is_identity
from sys.objects o 
inner join sys.columns c 
on o.object_id = c.object_id
where c.is_identity = 1
order by o.name, c.column_id

I got a list of 48 modx tables which had the identity set.

modx_access_actiondom
modx_access_actions
modx_access_category
modx_access_context
modx_access_elements
modx_access_media_source
modx_access_menus
modx_access_permissions
modx_access_policies
modx_access_policy_template_groups
modx_access_policy_templates
modx_access_resource_groups
modx_access_resources
modx_access_templatevars
modx_actiondom
modx_actions
modx_actions_fields
modx_categories
modx_class_map
modx_content_type
modx_dashboard
modx_dashboard_widget
modx_document_groups
modx_documentgroup_names
modx_fc_profiles
modx_fc_sets
modx_lexicon_entries
modx_manager_log
modx_media_sources
modx_member_groups
modx_membergroup_names
modx_property_set
modx_register_queues
modx_register_topics
modx_site_content
modx_site_htmlsnippets
modx_site_plugins
modx_site_snippets
modx_site_templates
modx_site_tmplvar_access
modx_site_tmplvar_contentvalues
modx_site_tmplvars
modx_transport_providers
modx_user_attributes
modx_user_group_roles
modx_user_messages
modx_users
modx_workspaces

As for the usage of Linked Server tables, I've used them at a client, but this wasn't a website project, so I'm not sure as to how XPDO would handle them. If all else were to fail, you could always just put it into a chunk with the linked server name as a replacement. I've had to do that before, when nothing else would work in a reasonable amount of time.

Hope this helps.

Regards,
Tom

[2013-04-25 11:08:14] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not create index content_ft_idx: CREATE INDEX [content_ft_idx] ON [modx_site_content] ([pagetitle],[longtitle],[description],[introtext],[content]) Array
(
    [0] => 42000
    [1] => 1919
    [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'introtext' in table 'modx_site_content' is of a type that is invalid for use as a key column in an index.
)

[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not copy C:/testo/modx-2.2.7-pl/core/packages/core/modContext/30d8087d41cec4d968824496e1163b26/0/ to C:/testo/modx-2.2.7-pl/index.php
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not copy C:/testo/modx-2.2.7-pl/core/packages/core/modContext/30d8087d41cec4d968824496e1163b26/1/ to C:/testo/modx-2.2.7-pl/ht.access
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not copy C:/testo/modx-2.2.7-pl/core/packages/core/modContext/c14eb1256ab6c95f844ced3addae4840/0/ to C:/testo/modx-2.2.7-pl/manager/assets
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not copy C:/testo/modx-2.2.7-pl/core/packages/core/modContext/c14eb1256ab6c95f844ced3addae4840/1/ to C:/testo/modx-2.2.7-pl/manager/controllers
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not copy C:/testo/modx-2.2.7-pl/core/packages/core/modContext/c14eb1256ab6c95f844ced3addae4840/2/ to C:/testo/modx-2.2.7-pl/manager/templates
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not copy C:/testo/modx-2.2.7-pl/core/packages/core/modContext/c14eb1256ab6c95f844ced3addae4840/3/ to C:/testo/modx-2.2.7-pl/manager/min
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not copy C:/testo/modx-2.2.7-pl/core/packages/core/modContext/c14eb1256ab6c95f844ced3addae4840/4/ to C:/testo/modx-2.2.7-pl/manager/ht.access
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not copy C:/testo/modx-2.2.7-pl/core/packages/core/modContext/c14eb1256ab6c95f844ced3addae4840/5/ to C:/testo/modx-2.2.7-pl/manager/cache.manifest.php
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not copy C:/testo/modx-2.2.7-pl/core/packages/core/modContext/c14eb1256ab6c95f844ced3addae4840/6/ to C:/testo/modx-2.2.7-pl/manager/index.php
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/b0b51b3583ca173467fabdbeaa1decd7/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/11eb7f1b1806b425deec2b5a6020765c/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/66428576487f8655f07fdfcdc200ca13/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/28dae9a6e8aec06333819bb98e752fbf/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/de88dcb037d57b0a7fa195dc36539502/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/f0f2e1b9a45a10da2bf7a7269401962b/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/72e6f8436958588d58829e48881e7e1c/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/710b8328e84fab41c05dea7e4b9b7a6d/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/c6718c080f0c04a23fac3ad374562e11/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/94937aaf214017bdf5ef179afe743849/ to C:/testo/modx-2.2.7-pl/connectors/
[2013-04-25 11:08:17] (ERROR @ /test/modx-2.2.7-pl/setup/index.php) Could not install files from C:/testo/modx-2.2.7-pl/core/packages/core/xPDOFileVehicle/71f0917701a6ed771630b71a48060383/ to C:/testo/modx-2.2.7-pl/connectors/