Closed (fixed)
Project:
Drupal core
Version:
5.1
Component:
database system
Priority:
Normal
Category:
Support request
Assigned:
Reporter:
Created:
30 Mar 2007 at 19:43 UTC
Updated:
19 Feb 2008 at 16:04 UTC
Jump to comment: Most recent file
Comments
Comment #1
Watcherq commentedOk, I tried the patch on a fresh drupal-5.1 download. There was no error in the patching. Yay!
The first time I tried the install page, I came up with the form to key in the info. After that, the system hit 100% with the CPU going to the SQLServer process. However, no table was created, and after a short while (about 1 minute), the load drops back to zero.
I tried running it again, but could never get back that page. I've restarted the web server, and sql server but again it didn't work. I noticed that the sites\default\settings.php was changed with the correct login details.
I tried again by blowing away the drupal directory and trying it again. No go; I've got a blank page again for index.php and install.php. When I tried deleting the directory, the following files were locked by the webserver and I had to restart the webserver. The webserver restarted easily (ie no need for kiling it on TaskMgr), and the files were released. They are:
includes\bootstrap.inc
includes\install.inc
To repeat my config:
- Win2k3 SP2 with IIS 6.0
- PHP 5.2.1 with updated ntwdblib.dll
- Drupal 5.1 with the patch above.
- SQL 2005 Express
Comment #2
Watcherq commentedOooops, forgot to add that install.php was also locked
Comment #3
Souvent22 commentedTry this patch. The first patch was just an old one I had. I tested this one, and it installed fine for me. Let me know if you have any issues installing. I know that this patch still needs tweaking though.
Comment #4
Souvent22 commentedHumph. didn't get attached. Here ya go.
One issue I did see was that on the "Congrats. Drupal has been installed" screen, the linke to "Click here to go to you drupal site" wasn't there. I'll look into this later. But it does install fine. I created some stories, users, and comments to test. I'm going to merge some changes from my production db_layer engine from the client into this one in the next few days also for a more stable patch/db_layer.
Comment #5
Souvent22 commentedok, we'll try this again. my attachment won't post.
Comment #6
Souvent22 commentedah, finally got it.
Comment #7
Souvent22 commentedMerged in driver-core changes from HEAD into 5.x patch
Comment #8
Watcherq commentedHi Souvent,
Thanks for all the effort so early (?) in the morning. Ok, I've tried 5_1 and 5_2 patch, all patched fine but they still doesn't work. In both cases, I get a blank browser, the install.php locks up and requires the IIS to be restarted to be deleted. Not even sites\default\settings.php was set. No DB connection was establised as well.
I sense it is very close. If you want, I can put up the drupal source (post patch) to you to check.
Comment #9
Souvent22 commentedHm. Yes, could you email me that, or attach it as a zip? I just did the following and it worked for me:
1. download drupal 5-1
2. apply patch
3. go to site
4. select 'mssql' as database
5. set db options (db name, username, pass, port)
6. click install
7. site is up and running
You're using SQL-Server 2005 correct? and have the database created before installing?
It should catch errors though and not hang.
Oh, i've done this on both apache 2.2 and IIS 6 (running as fast-cgi). i'll try it as ISAPI also and get back to you.
Comment #10
Watcherq commentedHi Souvent,
You can download it at http://203.117.183.229/drupal-5.1.zip I have put in php.ini as well.
Comment #11
pcorbett commentedThanks for posting the 5 patch, Souvent. The patch worked fine for me on a clean install of 5.1, however, I get a lot of cron_run errors when I "run cron manually." It throws these errors after saying that cron ran successfully:
user warning: Invalid object name 'cron_runs'. query: SELECT COUNT(*) FROM cron_runs in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.and
user warning: Invalid object name 'cron_run_info'. query: UPDATE cron_run_info SET run_status = 0, run_stop = 1175547597 WHERE riid = 0 in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.I am using SQL Server 2000.... so that may have something to do with it. Anyone else?
Comment #12
pcorbett commentedThere is also this error that shows up on the status report page where I believe there is an error with the IF statement - Is this a shorthand if/else statement? I have never seen that syntax before.
Comment #13
Souvent22 commentedpcorbett,
Ah. I apologize to everyone; I seem to of gotten my "multi-cron" patch merged in with the "sql-server" patch. Darn patches are running amuck! :). I'll clean it up and re-post a clean patch for sql-server only. Sorry about that. That's why you are getting "cron_runs" error, because it's a table that is created if you use the multi-cron patch.
Comment #14
Souvent22 commentedRe-rolled patch taking out my multi-thread changes (sorry 'bout that).
Watchrq, i still haven't been able to re-create your issue. I tried to download your zip file, but link it dead. I had the zip fire previously, but I totally re-formatted my laptop today and have thus lost it. :-/.
Comment #15
Watcherq commentedI've lost access to that server as well. Let me patch later with your latest patch and get back to you very soon. Thanks for all your help.
Comment #16
Souvent22 commentedI think the "IF" statement is a seperate issue and not because of the patch being dirty. I'll have a look into that also.
Comment #17
pcorbett commentedCan anyone else confirm that the latest patch fixed the cron_run issue? I'm still getting the following errors when running cron manually:
I'm not sure if this is causing any issues necessarily, but I also receive this error when running install.php for the first time:
Comment #18
pcorbett commentedOn the "list users" page, the following error occurs:
I believe this is caused because the DISTINCT keyword appears after the TOP clause. A quick fix for this can be made in database.mssql.inc around line 423:
This will also become an issue, I imagine, once I have a list of users long enough to page at which point code would need to be added to that section as well. I'm not sure if you'd agree with my particular solution, Souvent22, but I think something should be done to account for the occasional DISTINCT that comes through in a query.
Comment #19
pcorbett commentedSorry, I left in a ! in front of 'strpos' (part of my debugging). Below is the correct code I used to patch the bug:
Comment #20
Souvent22 commentedpcorbett,
Hey, i'm looking into the TOP/Distinct issue now. But as for the cron_runs, not sure. If you open the patch up, and do a search for "cron_runs", nothing is found; did you blow away your 5.x and patch a fresh one?
Comment #21
Souvent22 commentedok, i've got a fix. but i need verification on something. those who have the patch working, if you could run this query:
and then run this created:
Please tell me your results. here are mine:
Query 1: 25 seconds
Query 2: 0 seconds
Moral of the story...we can not order on a non-indexed field (notice that in query 2, the order by is on uid, not the created field). SQL Server is very fast...but very picky about searching on non-indexed fields, and even more picky on sorting on non-indexed fields. One can read more about this by referencing the MSDN, and reading about clustered and non-clustered indexes and indexes in general in SQLServer.
It's better if we tell it to not order by at all (e.g.: )
(code time: 0 seconds)
I only have 2 users in the DB. I'm hoping to be able to implement my
_db_mssql_locate_table_indexfunction which will find a better index if the field userd is not indexed. Oh, for reference, here is the original query from the user.module:Comment #22
Souvent22 commentedK, here's an updated patch.
Added a nice function that looks through the system tables, and creates a nice little table for us of all the tables.columns that are text types so I can use it as an easy reference later is we need to handle text columns in a special manner.
Futher more, more SQL problems. If you go to "Top page not founds", there is this query:
Message is a text field, so MSSQL throws an instant error because you can't "distinct" a text field (e.g. BLOB). Perhaps we can change the message field to a varchar(255) ? that's waht i currently did...but I know there is that PHP bug under windows in which varchars are limited to 255 even though you may set it to 4000. I'm thinking perhaps i can use the table i made and do a "CONVERT" on those fields when being fetched. e.g.:
but i'm thinking it'll go back and throw the other type of err....perhaps there's a better way we can find the top page not found errors all togather...hmm.....thoughts?
Comment #23
pcorbett commentedSouvent,
I think patch 5.4 is for the devel module and not for SQL Server enhancements...
Comment #24
pcorbett commentedThe cron_run issue I was having earlier is fixed now. I must have not have tested from a clean install. Sorry.
Comment #25
pcorbett commentedWhen I added a "footer" block in the blocks menu and then clicked on "configure" the following warning appeared:
I fixed this by editing line 362 in block.module and adding the "bl" prefix to "title":
I couldn't find any existing bugs or threads about this, so perhaps it's particular to SQL SERVER (maybe MySQL is smarter and assumes title refers to the blocks table since all of the boxes columns are already being selected).
Can anyone else confirm this?
Comment #26
Souvent22 commentedConfirmed. I had to "clean" the blocks code for the clients site. It's got some hairy stuff in it. As a rule of thumb though, all column tables should be name-spaced. It makes it easier to read the code also when looking at something the first time, and debugging. I'd open a ticket.
Comment #27
Souvent22 commentedSorry, that comment reads funny:
All columns should to name-spaced... is how it should read.
Comment #28
pcorbett commentedAlso, I think the 5.4 patch you posted was incorrect or possibly a patch for the devel module.
Comment #29
pcorbett commentedWhen I make any changes to the User Access page or perform a search, I receive the following warning:
However, it does only seem to be a warning, as both functions work, it's just that the warning is thrown as well.
Comment #30
pcorbett commentedWhen I edit a page (and sometimes other content types as well) I receive the following warning:
Can anyone else confirm this using SQL 2000 / MS SQL / Drupal 5.1 / IIS 6.0 - or anything else for that matter?
Comment #31
pcorbett commentedIn mssql_install_driver_merged_5_3.patch, the primary key of the contact table is not set to auto increment and is instead set with a default value of '0':
This prevents one from adding more than one contact list / category.
A fix would be for line 388:
Comment #32
pcorbett commentedBack to the GREATEST function issue when viewing Administer > Logs > Status Report > Run Cron Manually:
The issues I see with this is that the IF statement is not correct, and the wrong number of parameters are being passed to the GREATEST function (usually only 2 instead of 3). I adjust it to read the following way, but tell me if I'm way off:
I simply adjusted the IF statement to an ISNULL statement (which I think you're were trying to do) and added a 3rd parameter of zero (0) when the GREATEST function was called. Not sure if this is accomplishing what the original was, but it works.
Comment #33
pcorbett commentedThe following fields are declared incorrectly (at least for SQL 2000) in mssql_install_driver_merged_5_3.patch. They are set to default as null (e.g, search_index table : "score float default NULL", but never actually set as nullable fields as they should be (e.g., score float NULL default NULL:
search_total.count
aggregator_item.timestamp
accesslog.title, .path, .url, .hostname
search.type
search_index.type, .fromtype, .score
profile_fields.title, .name, .explanation, .category, .page, .type
Can anyone confirm this? Does this work in SQL SERVER 2005?
Comment #34
pcorbett commentedUpdate on the DISTINCT issue concerning database.mssql.inc line 424. I ran into a case when viewing a taxonomy category that cased an error. The original code was using something like "SELECT TOP X DISTINCT" and I had suggested it be changed to " SELECT DISTINCT TOP X ...." Now, a case came by where the query looked like this "SELECT TOP X DISTINCT(x), y, z" which caused an issue because I was only finding and replacing instances of "DISTINCT " (note the blank space). I adjust the code to search for both a blank space and a "(" after DISTINCT... Open to suggestions on how to best do this:
Comment #35
Souvent22 commentedpcorbett,
Thanks for all the updates. I'll try and roll all these into a patch, unless you can do a unified diff of your current setup. but i'll hopefully roll a patch and the end of this week.
Comment #36
pcorbett commentedOn line 603 of database.mssql.inc, the syntax "SHOW TABLES" is used and I don't believe it is available in any release of SQL Server. As a result, attempting an install of CCK threw the following error:
The line currently reads:
I suggest the following fix:
Comment #37
pcorbett commentedThe above may be different for SQL 2005. I think the tables that store this information are a bit different.
Comment #38
pcorbett commentedHere is a patch for database.mssql.inc. I can do others if you want, but keep in mind some of my edits were because I'm stuck with SQL 2000... I'm not terribly familiar with how to roll patches and upadates, etc. but any suggestions you have are much welcomed. I'm willing to help you out as much as I can. I'm in the middle of an install and setup right now, which is the reason for so many posts lately - hope they weren't too overwhelming!
Comment #39
pcorbett commentedThe issue discussed earlier in this thread about the 255 character limit PHP (php_mssql.dll) places on queries pulling varchar data from SQL Server is still and issue as well. Supposedly freeTDS is supposed to work using a windows-ported version of php_dblib.dll. I tried it and even contacted the freeTDS team, but have not made it work so far. If anyone wants to try, email me and I can help.
The "quick fix" is to change the column in sql server from a varchar to a TEXT data type (assuming it's over 255 characters) and do what Souvent22 suggested earlier:
This could probably be done on a larger scale with one of the db query functions that would just look for a select on a varchar field over 255 and insert the convert in there. So what this does is two things:
1. Allows you to grab all the data from the column (because it was converted to a TEXT field that isn't affected by the 255 char limit)
2. Allows you to SELECT DISTINCT because you can't do that with a TEXT field (hence, the CONVERT(VARCHAR(XXXX))
Make sense?
Comment #40
Souvent22 commentedOk, so announcment 1, I have found a time window to do some hard-core patching on the 5.1 branch of the sql-server module. Getting 5.1 stable will lead to 6.0.
pcorbett, i'll be going back, and rolling your patch into 1 unifying patch.
Everyone, we are in phase 3 of the clinets roll-out, and we are in the sql-audit phase. So, I get to go through drupal and analyze each and every sql-statement in core....look for a list of sql-statmets that I will be proposing patches for to speed up and/or change so that they are faster, better, and hopefully will make sql-server integration much easer than it currently is.
pcorbett, i'll need some feedback and testing from you after i implement the reg-ex to do the replace for varchar fields to text.
also, i'm still considering branching the patch futher into mssql7, and mssql9 (2000, 2005)...however, i say for now we'll keep with the current patch, and hopefully we'll just need a "if" or 2 in order to handel both 2000 and 2005.
....and so off to the dungeon for me...hopeuflly i come out alive
Comment #41
pcorbett commentedGood news, Souvent22. I'm here to test whatever you'd like. I have another possible issue. For the user-defined GREATEST function, I have to reference the function in my code by specifying the owner of the function: "dbo.GREATEST(.....)" otherwise, it doesn't recognize the function. Again, might just be a SQL Server 2000 thing, but thought I'd bring it up.
Comment #42
pcorbett commentedDoes searching work for anyone? I've updated my index and run cron, but still it doesn't seem to be finding any terms, even ones I see that exist in the search_index table. I'll look into it some more see what I can find.
Comment #43
pcorbett commentedI vote for IF statements rather than another branch of the patch to account for 2000/2005 compatibility. Let me know if you need help knowing were those statements might need to go.
Comment #44
pcorbett commentedMaybe my own setup, but can anyone using mssql access the page anonymously? I keep getting access denied (and not the welcome screen because I have a post that was set to post to the front page).
Comment #45
pcorbett commentedUpdate. I'm listing below all of the issues I have run into w/ a note if it may be a MSSQL 2000-only issue.
Suspected SQL Server 2000???
Misc
I also have CCK running well in addition to LDAP Integration & TinyMCE if anyone is interested.
Comment #46
pcorbett commentedUpdate #2
One last thing I promise....
I forget where this was an issue but I remember mentioning that the GREATEST function took 3 parameters and in some places only 2 where specified. I think I simply added a '' as the last param being run into the function. Oh, it's mentioned in post #32
Comment #47
abhi.pandey commentedHi everybody,
I was trying to install Drupal with
IIS 5.x with XP
PHP Installed with ISAPI support on IIS
5.3 patch of MS SQL server 2005
After applying patch it keeps on saying for many files - 1 on 1 hunk ignored - and finishes fine. However when I used 5.4 patch version - it used to exit after generating an error. When I tried to reach the folder from iis (i.e. localhost) it either says no input file specified or earlier it was saying, there's no database configured to be used with IIS.
When I try with Apache, it says the username and password didnt' work for login. I have checked it numerous times and it's correct.
Can anybody help me out on this issue please?
Comment #48
pcorbett commentedI don't think the 5.4 patch is working. How did you patch with 5.3 ... Try patch -p0 < [patch file] It should patch with no errors. Also, make sure you are patching on a clean install of Drupal 5.1.
Comment #49
abhi.pandey commentedYes! the patching worked. I was underestimating the powers of -p0!!!!
Now the new problem is on IIS that tells me this -
Your web server does not appear to support any common database types. Check with your hosting provider to see if they offer any databases that Drupal supports.
How do I solve this problem? I get this problem whether I use drupal as a virtual directory or whether I put it inside wwwroot folder.
Comment #50
pcorbett commentedHm. If it didn't automatically bring you there, have you tried going to http://[your server]/install.php? There it should ask you for what type of database you are using. If not, you can also manually do it in your sites/default/settings.php file.
Comment #51
pcorbett commentedWhat happened here?
Souvent22 - March 30, 2007 - 19:43
Project: Drupal
Version: 5.1
Component: database system
Category: support
Priority: normal
Assigned: abhi.pandey ???????
Status: active
Attachment: mssql_install_driver_merged_5_0.patch (60.16 KB)
Comment #52
pcorbett commentedIn user.module, I'm not sure how this got changed using the 5.3 patch (maybe I modified this myself???) but line 362 has the following:
But i'm not sure why the DISTINCT or the CONVERT are there. I'm removing both for now because neither seem to make a difference that I can see.
Souvent, I didn't see this in your patch so maybe I made this change in my sleep or something. No clue. If CONVERT/DISTINCT need to be there, I would suggest putting " AS perm" before FROM so that the later calculations will work. Otherwise this results in the anonymous user not being able to access content.
Let me know what you think about this one....
Comment #53
abhi.pandey commentedI figured out it is better to work with MySql in my case, so I will keep to that. Thanks for all the help.
As per the Assigned-to mess, I am sorry about it. I didn't know it changes whole thing. Now that I've already made a mistake, I can only set it back to Unassigned, should I do that?
Comment #54
pcorbett commentedNo big deal. Just wondering. Strange that it lets anyone change who something is assigned to at the root level... hmm. Good luck.
Comment #55
pcorbett commentedWhile trying to get the search to work, I came across the use of the POW function in SQL. It can be found on line 878 of node.module. Since POW doesn't exist, at least for MSSQL 2000, I changed it to POWER in order to work:
Comment #56
pcorbett commentedTo get pagination working with SQL SERVER 2000, I added the following to database.mssql.inc around line 471:
// pcorbett 5/1/07
// $query = "SELECT * FROM (
// SELECT
// ROW_NUMBER() OVER($order_by) AS rownumber,
// $select
// FROM $qfrom
// $where
// ) as drupal_range_query
// WHERE rownumber BETWEEN $from AND $to;";
$query = "SELECT TOP $to $select FROM $qfrom $where";
}
_db_query_callback($args, TRUE);
$query = db_prefix_tables($query);
$query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
// pcorbett 5/1/07
// return _db_query($query);
$last_result = _db_query($query);
if ($from > 0) {
mssql_data_seek($last_result, $from);
return $last_result;
} else {
return _db_query($query);
}
}
Comment #57
pcorbett commentedStarting on line 157 in database.mssql.inc, I believe this is around where the search query gets wiped out:
If you see... a query using relevance is calculated from the search page and then run through this function. It gets completely cleared due to the next to last line above because $query_new was UNSET in the chunk of code above.
As the TODO says, I'm not sure what this is :)
Any ideas or progress so far?
I'll let you know if I find anything.
Comment #58
pcorbett commentedSearch is working now.
To get it to work so far, I've done the following:
$ranking[] = '%d * POWER(2, (CONVERT(int,dbo.GREATEST(n.created, n.changed, c.last_comment_timestamp)) - %d) * 6.43e-8)';
That's it. Obviously, there is more work to be done to streamline this a bit. But for now, it works.
Comment #59
pcorbett commentedI was getting the following error often:
This can be remedied a couple ways in database.mssql.inc in the db_fetch_array() function:
if ($result) {
return mssql_fetch_array($result, MSSQL_ASSOC);
}
I'm not sure why the function differs that much from mysql.database.inc... any reason for the foreach()????
Comment #60
pcorbett commentedSorry, in the above: in_array is supposed to be "is_array"
Comment #61
Souvent22 commentedCatching up and reading. I'm thinking this patch may have to stay as just that..a patch. As I've had to hack many of the core modules to get their SQL up to par. But, i'm hoping to post a "conslidation" patch soon.
Comment #62
pcorbett commentedWhich modules did you have to hack? I'm using most with no issues unless already noted.
Comment #63
pcorbett commentedWhich modules did you have to hack? I'm using most with no issues unless already noted.
Comment #64
Souvent22 commentedI'll list them in a comment in a bit. just changing the title of the thread back :)
Comment #65
pcorbett commentedI'm assuming SELECT DISTINCT on a MySQL column works, but as we know for SQL Server does not allow this. Earlier we were throwing around ideas about how to address the 255 character php length limit for SELECTing from a varchar data-type column. In reference to the user.module use of this in the user_access function (around line 365) I think my quick fix is wrong.
First, the permission.perm col doesn't need to be converted to VARCHAR because the col is already a TEXT type, which is not effected by the 255 char limitation in PHP. However, that col has a SELECT DISTINCT, which doesn't work in SQL Server. For now, I took out the DISTINCT all together and things seem to be working, but it's a hack.
I was thinking maybe we could use a temp table or some kind of array sort that would remove any dupes from it.
Anyway... yeah.
Souvent22 - what's your e-mail? We should chat or IM about this project if you have time - we might be able to get things done faster.
Comment #66
pcorbett commentedFYI. I had modified CCK to work with SQL Server 2000. One issue that I have to address is when a column is deleted. What happens is that if there is a default value associated with the column, SQL Server throws an error saying that there are objects associated/dependent on this column and that the column can't be deleted. I'm assuming in MySQL, it just deletes all associated constraints when the column is dropped (makes more sense to me).
So, I think the way to address this is to drop the default constraint before the column is dropped. I'll post with more specific instructions later. Maybe this is something that should be part of _db_query... some kind of monitor that waits for a DROP COLUMN and then checks for constraints?
Comment #67
Souvent22 commentedpcorbett,
Ping me on email: earnest [dot] berry at da gmail to the com. (yeah, it's cryptic, you can use my contact form also :) ). I'd like to try and get a day squared away and dedicate a few days to it. Meaning like work on it in direct contact on like a Monday, gather current issues, decided a path, re-visit on like a thrusday, something like that. nothing "intense", but I'm sure you're schedule, like mine, it's hard to get in even like 20 min. in sync with someone :). But yeah, ping me.
Comment #68
pcorbett commentedI wanted to let everyone know that development on this has not ended. A considerable amount of work must be done now to address the various bugs posted here and get things more organized with a CVS/SVN. Hopefully soon we'll have another patch out soon. Anyone interested in helping test/debug is welcome to do so.
Comment #69
antgiant commentedsubscribing
Comment #70
ricabrantes commentedNo activity, Closed..