[TYPO3] Typo3 / DBAL / ADODB / MSSQL: Violation of primary Key

[SassoNet - Stefan Gruendel]

Hi,

I am still struggling with the combination of Typo3, DBAL, ADODB and MSSQL,
running on an IIS server.

After setting up everything and defining a new page from fresh (which all
works without major problems), I am getting the following messages once I am
viewing the new page:

Warning: mssql_query(): message: Violation of PRIMARY KEY constraint
'PK__cache_hash__37A5467C'. Cannot insert duplicate key in object
'cache_hash'. (severity 14) in
C:\...\typo3\typo3conf\ext\adodb\adodb\drivers\adodb-mssql.inc.php on line
701

Warning: mssql_query(): Query failed in
C:\...\typo3\typo3conf\ext\adodb\adodb\drivers\adodb-mssql.inc.php on line
701

These are the facts of the installation:
OS: MS Windows Server 2003 Std Edition (NT 5.2 - 3790)
MSSQL: SQL Server Std Edition Version 9.00.194 (RTM), English MS Internet
Information Services 6.0 PHP 4.4.1 MS Internet Explorer 6.0

I am trying to clarify some things you asked for / mentioned:

> What does your connection string look like?

I am connecting to the MSSQL server using the pipe name (name of pipe is
"PIPE_SQL_INTRANET"). I am entering this pipe name as host within the
installation tool of typo3, and typo3 connects to this server and the
selected database fine (without any warning/error messages).

> did you set you database access to utf8?

I am not sure where to do this. I am not a real expert in MSSQL
administration. I have made some PHP applications working together with the
same MSSQL server but there was never the need to get deeper into the MSSQL
administration stuff. It would be nice if you could give me a hint what to
check or change here.

> Can you enable the DBAL (query) logging?
> Try to find the INSERT statement that produces the error.

I have enabled the debug mode in DBAL and have found the following SQL
statement in the table tx_dbal_debuglog (this is the last INSERT INTO
cache_hash statement that can be found in that table):

INSERT INTO "cache_hash"
				(
					"hash",
					"content",
					"ident",
					"tstamp"
				) VALUES (
					'1a72ae4ad2a532804d40be11df1e3fae',
					null,
					'SHORT TC',
					'1149841058'
				) WITH 1 BLOB FIELDS: "content"
				

The log table has captured 25 SQL statements thereafter (according to the
uid number) which are these:

				
SELECT  top 1 *
			FROM "sys_template"
			WHERE
				 "pid" = 2 AND "deleted" = 0 AND "hidden" =
0 AND ("starttime" <= 1149841056) AND ("endtime" = 0 OR "endtime" >
1149841056)
			ORDER BY "sorting"
			
SELECT "content"
			FROM "cache_pagesection"
			WHERE
				 "page_id" = 2 AND "mpvar_hash" = 222419149
				 
SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 54

SELECT  top 1 *
			FROM "sys_template"
			WHERE
				 "pid" = 2 AND "deleted" = 0 AND "hidden" =
0 AND ("starttime" <= 1149841057) AND ("endtime" = 0 OR "endtime" >
1149841057)
			ORDER BY "sorting"

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 23

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 54

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 23

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 43

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 43

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 45

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 46

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 45

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 44

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 46

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 68

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 44

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 57

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 68

SELECT "content"
			FROM "cache_hash"
			WHERE
				 "hash" = '3e7cec8583620e9c3476b59035a50550'

SELECT *
			FROM "static_template"
			WHERE
				 "uid" = 57

DELETE FROM "cache_pagesection"
				WHERE
					 "page_id" = 2 AND "mpvar_hash" =
222419149

SELECT "content"
			FROM "cache_hash"
			WHERE
				 "hash" = '3e7cec8583620e9c3476b59035a50550'

INSERT INTO "cache_pagesection"
				(
					"page_id",
					"mpvar_hash",
					"content",
					"tstamp"
				) VALUES (
					'2',
					'222419149',
					null,
					'1149841056'
				) WITH 1 BLOB FIELDS: "content"
				
DELETE FROM "cache_pagesection"
				WHERE
					 "page_id" = 2 AND "mpvar_hash" =
222419149

INSERT INTO "cache_pagesection"
				(
					"page_id",
					"mpvar_hash",
					"content",
					"tstamp"
				) VALUES (
					'2',
					'222419149',
					null,
					'1149841057'
				) WITH 1 BLOB FIELDS: "content"


> That would mean you cannot work at all (just try creating more than one
pages or content elements).

I can create more pages or more content elements without any problems. The
message appears only when I try to view the results as front end web site.


To give you the complete picture, I have done the following steps before:

1 - Installation of Typo3 4.0 package, with Dummy
2 - Copy DBAL & ADODB extensions in /typo3conf/ext
3 - Configure DBAL localconf.php (dbal/handlerCfg) according to DBAL
documentation (part 1.4): DBAL Type = "adodb" / Driver = "mssql"
4 - Change /typo3/install/index.php (inactivate row which prevents install
script to be executed)
5 - Create new MSSQL database with name "typo"
6 - With MSSQL Enterprise Manager change properties of new database "typo":
Set options to "Use quoted identifiers" and "ANSI NULL default"
7 - Create new MSSQL user with name "typo"; give all access rights on the
new database to this user
8 - Run typo3 installation script from browser (/typo3/install/index.php)
9 - Enter "Basic Configuration", enter username, password and host (MSSQL
pipe) for database, confirm with "update localconf.php"
10 - Back to "Basic Configuration", all databases on host are shown in drop
down list, database "typo" is selected
11 - Enter Database Analyzer, it says "connected to SQL database
successfully, database typo has 0 tables". Click on "COMPARE" to update
required tables, a lot of "CREATE TABLE" statements are displayed,
automatically ticked and confirmed with "Write to database"
12 - "Changing fields" is proposed, but not done as I think this might be a
problem between typo3 and MSSQL
13 - Database Analyzer: Select "IMPORT" on Dump static data; select tick box
"Import the whole file CURRENT_STATIC directly", confirm with "Write to
database"
14 - Database Analyzer: Create admin user (message: "User created")
15 - Login to backend of typo3 with freshly created admin user (working
fine)
16 - Create new page (using wizard) and new page content on that page
(regular text element)
17 - Select "Template", typo3 says "No Template"; select "template;
BUSINESS" from drop down list, confirm with "Create template for a new
site". "Are you sure?" is confirmed with OK, next page is confirmed with
update
18 - Select "View", getting the mentioned error messages.


Thanks for your help again,

Stefan