Updated: Comment #118
Problem/Motivation
There are a number of significant issues with installing and running Drupal 8 on PostgreSQL.
Proposed resolution
Identify the major issues and implement workarounds or fixes. This should now be done in each individual related issue rather than tacking onto a huge patch.
These issues are listed in the Related Issues block to the right.
Comments #1 - #117 are related to this issue pre-meta status. The latest "full patch" is from Comment #109. This should be used as a started point for the sub-tasks to split off code into new patches.
- In transaction context, the first failure makes the transaction abort, and subsequent operations, even if they would normally succeed, also fail with the error "current transaction is aborted, commands ignored until end of transaction block". This differs from the behavior of MySQL. The workaround is to wrap all queries that occur inside a transaction with SAVEPOINT.
- Cache tables are missing at the end of the installation. Ensure cache tables exist when deleteAll/DeleteMultiple is called.
- Some things such as node/add/page fail because queries such as "select * from users where (uid IN ('autocomplete'))" which succeeds in MySQL, causes an exception in PostgreSQL because 'autocomplete' is not an integer. menu_get_item() needs to be fixed to get the correct route for user/autocomplete.
- Toolbar menu links do not appear because of the way a orderBy() override for pgsql adds fields in front of fields expected by Entity\Query. The workaround is to add a tag to the query to disable the special features of the override in specific cases.
- Prevent pgsql driver from trying to implode primary key fields that are defined by an array (column, key_length) as that syntax is not supported.
(Update Feb. 2014. There is work in progress to completely remove menu_get_item() from core, so this issue might get fixed by #2177031: Remove menu_get_item() and every use of it. )
Remaining tasks
- Create patches for known issues in their respective issue.
- Create issues and relate those issues to this task as new bugs are discovered.
Related Issues
#2010368: Installer can't create new database on PostgreSQL
#2056627: Form API autocomplete is broken for routes
#1003788: PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID
#2111979: PostgreSQL is unable to create a new database
Original report by hosef
When one installs Drupal 8 on a PostgreSQL database, the installation will hang while installing the Image module. You can get around the install hang by using the minimal profile, however, when you go view a page on the site(after trying either profile) you will get:
Page not found
The requested page "/" could not be found.
There don't appear to be any relevent entries in the Apache log, however the following shows up in the PostgeSQL log for each page load:
2013-05-22 22:05:01 PDT STATEMENT: DELETE FROM cache_menu
2013-05-22 22:05:01 PDT ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-05-22 22:05:01 PDT STATEMENT: SELECT 1 FROM information_schema.tables WHERE (table_catalog = 'drupalcore') AND (table_schema = 'public') AND (table_name = 'cache_menu')
Comment | File | Size | Author |
---|---|---|---|
#128 | pg_after_install.png | 135.36 KB | bzrudi71 |
#107 | interdiff-2001350-91-107.txt | 1007 bytes | InternetDevels |
#107 | 2001350-pgsql-fixes-107.patch | 8.63 KB | InternetDevels |
#83 | Screen Shot 2013-10-14 at 4.18.41.png | 558.43 KB | dlu |
#76 | 2001350-pgsql-fixes-76.patch | 8.16 KB | mradcliffe |
Comments
Comment #1
Anonymous (not verified) CreditAttribution: Anonymous commentedManaged to reproduce the issue. And found the source of the issue. Working on a patch.
Comment #2
dcrocks CreditAttribution: dcrocks commentedCould you give a general indication of what the source is? I am having a similar problem with SQLite.
Comment #3
Anonymous (not verified) CreditAttribution: Anonymous commentedDoh! Sorry about that. The culprit seems to be in \Drupal\Core\Database\Connection.php:533-541.
While installing filters, one of the arguments being substituted into the SQL is an array. Since MySQL doesn't pass the query in as a StatementInterface, this isn't a problem because the connection calls
$this->expandArguments(...)
and is able to explode the array into the appropriate placeholders and replacement values.PgSQL and SQLite, on the other hand, appear to pass statements as instances of StatementInterface. This route, the arguments never get expanded, so eventually (on \Drupal\Core\Database\Driver\pgsql\Insert.php:48) this results in a situation where
$stmt->bindParam(...)
is receiving an Array even though the default third argument tells it it should expect a string.I think that both of these issues will probably be resolved in much the same way.
Comment #4
Anonymous (not verified) CreditAttribution: Anonymous commentedWon't be able to work on this issue for a few days and haven't made significant progress past the previous post. Anyone else is free to grab this.
Comment #5
mradcliffeI'm getting more failures like this, but earlier in the installation process in Pg 9.2.4. Looking into it a bit tonight.
Comment #6
mradcliffeFilterPluginManager calls CacheDecorator inappropriately, which causes a query to fail in the same way.
I split that into #2008644: FilterPluginManager::__construct calls CacheDecorator with incorrect parameters (expires).
I also am having issues with several cache tables not installing, specifically cache_menu, cache_page, and cache_views_results.
Comment #7
PanchoConfirm that there is a number of bugs. Issue is critical per Priority levels of issues.
If the database doesn't preexist and is to be created, I also get a
Might have something to do with each other. I'll be digging into this right now.
[edit:] no that's not connected but a separate issue, see #2010368: Installer can't create new database on PostgreSQL
Comment #8
steinmb CreditAttribution: steinmb commentedPHP 5.4.13 (cli) (built: Apr 16 2013 11:46:33)
PostgreSQL 9.2.4
Confirm this bug. Installation will complete If you, like mention above, use the minimal profile but you end up with a broken site. Got this warning after the installation, though I do not think this is the issue.
None of the registered routes works and cache_menu table is missing.
Manually creating the table allow me to navigate the site though some errors is still found i postgres. At least I'm able to navigate.
Create missing cache_menu
Attaching the complete log from postgreSQL during a minimal installation.
EDIT: Added info about what happen if you manually create cache_menu.
Comment #9
PanchoThanks, @steinmb, for the log!
Unassigning though because I don't have time for it this weekend.
Comment #10
fvideon CreditAttribution: fvideon commentedComment #11
fvideon CreditAttribution: fvideon commentedI don't feel this patch is ready for prime time, but it at least shows the issue and one possible fix. The reason some of the cache_* tables are not being created during setup is tied to having the database transaction support enabled. By design, cache operations are allowed on cache bins that don't yet have the backing cache_* table and exceptions are caught as needed. The set() method is the one that creates the table. This happens inside a catch clause after the first attempt to write to the table fails. (See core/lib/Drupal/Core/Cache/DatabaseBackend.php around line 147).
The problem is that in the transaction context, the first failure makes the transaction abort, and subsequent operations, even if they would normally succeed, also fail with the error "current transaction is aborted, commands ignored until end of transaction block". Most of the cache_* tables are created not in transaction context, but a few such as cache_menu are, and these fail to be created.
The fix I drew up just checks if it's in a transaction, and if so takes an appropriate action to find out if the table exists, and thus avoids aborting the transaction.
One drawback is that there are more queries needed to do this checking compared to just using the try/catch. A possible mitigation would be to use a static array of bool to keep track of which bins have been created, so it would only need to check once for each.
I'm setting this back to unassigned now since I won't be working on it over the weekend. I can probably work on it more next week unless someone else has a better fix, or gets to it first.
Comment #12
Damien Tournoud CreditAttribution: Damien Tournoud commentedThis is so interesting. I wonder how we could have missed that before.
MySQL (or more precisely, InnoDB) basically does an implicit SAVEPOINT before each transaction. From browsing around, it doesn't seem that any of the behavior is configurable, so we will have to either perform an explicit rollback on error (on MySQL) or add a SAVEPOINT before every statement on PostgreSQL.
Comment #13
PanchoIndeed fascinating that we didn't hit this before!
In SQLite this is nicely configurable, defaulting to "ON CONFLICT ABORT" and says this was "the behavior specified by the SQL standard." So even though it seems complicated to handle, by default it seems to match InnoDB's behaviour, and that's why this code works fine on SQLite and only fails on PostgreSQL.
Oracle IMHO also implicitely sets savepoints.Don't know about SQL Server.
So while PostgreSQL's, also non-configurable, "all or nothing" rollback decision is definitely more predictable, it seems to be quite an exception. This might be an argument for adding explicit savepoints for PostgreSQL
On the other side, as long as we don't have PostgreSQL testbots, it might be better to simulate PostgreSQL's behaviour on MySQL (inserting explicit rollbacks) and SQLite (using "ON CONFLICT ROLLBACK").
I guess, we should probably figure out both ways and see how they turn out to work, before making the final decision.
In any case, this should be generally solved, so I'm setting this back to needs work.
Comment #14
Damien Tournoud CreditAttribution: Damien Tournoud commentedI suspect that we are hitting this now because PostgreSQL recently (in version 9, if I remember correctly) got support for transactional DDL. As a consequence the DDL statements that previously caused the previous (aborted) transaction to be cleared out do not anymore.
We should turn to PostgreSQL users. Given that PDO supports multi-statement queries, inserting a SAVEPOINT before every query will not have an impact on latency, which is a good thing, but might have an impact on the performance of execution of queries in general.
Note that adding a SAVEPOINT is what the psql command line tool does when ON_ERROR_ROLLBACK is enabled:
Comment #15
steinmb CreditAttribution: steinmb commentedA shot in the dark, but is this related to #1907230: PostgreSQL - duplicate key value violates unique constraint “drupal_cache_block_pkey” ? Damien should remember those..
Comment #16
Damien Tournoud CreditAttribution: Damien Tournoud commented@steinmb: without looking at this in detail, I would say those are probably not related.
Comment #17
fvideon CreditAttribution: fvideon commentedWorking on the approach of putting a savepoint before every query..
Comment #18
fvideon CreditAttribution: fvideon commentedThis uses the strategy of wrapping most everything with a savepoint (save for other savepoint operations).
I didn't see a way to do the savepoint release or rollback and return the correct results while putting everything in one PDO query. If someone knows a way to do that, we should use it. For now the release or rollback operation is handled as a separate query.
Cheers, Fred
Comment #19
Damien Tournoud CreditAttribution: Damien Tournoud commentedShooting in the dark, but I assume that:
... would work as it should? If the query fails, the rest of the batch will be ignored and an exception will get thrown, and in the exception handle we do the
ROLLBACK TO SAVEPOINT
stuff.Comment #20
fvideon CreditAttribution: fvideon commentedAFAIK the results of the compound query are the results of the last statement. If it ends with SAVEPOINT RELEASE, fetchAll always returns an empty array. Do you know a way around that?
Comment #21
Damien Tournoud CreditAttribution: Damien Tournoud commentedTricky.
I assume that something like this should work, but I don't know the performance implications:
... and we most likely have to CLOSE the cursor at one point.
I guess this is where it would start to make sense to subclass PDOStatement for PostgreSQL.
Comment #22
fvideon CreditAttribution: fvideon commentedGood idea to try a cursor. I just did a quick test using this form:
"SAVEPOINT sp; DECLARE acursor CURSOR FOR " .$sql . "; RELEASE SAVEPOINT sp; FETCH ALL acursor"
It works with a valid SELECT query and returns the correct results. With a bogus query it appears to do the right thing and throws a PDO Exception. The problem was that if I call it with the bogus query followed by the valid query, the valid query throws the dreaded
"ERROR: current transaction is aborted, commands ignored until end of transaction block".
So it appears that the exception on the bogus query happens on the FETCH which is already outside of the SAVEPOINT, so it still aborts the outer transaction.
Comment #23
fvideon CreditAttribution: fvideon commentedAn update on #22: I was mistaken -- the CURSOR actually does appear to work as we'd want for SELECT. My initial test did not handle the ROLLBACK correctly, but once that was done, the valid query following the bogus query did succeed.
I will take another look at this.
Comment #24
fvideon CreditAttribution: fvideon commentedAnd on to the next quandary: We can handle DELETE, UPDATE and INSERT using this compound query form, but then the PDOStatement rowCount() method always returns zero.
'SAVEPOINT sp; ' . $sql . '; RELEASE SAVEPOINT sp;'
Any thoughts about how to handle that better?
Comment #25
fvideon CreditAttribution: fvideon commentedFor this iteration the strategy using CURSOR is employed for SELECT queries to allow RELEASE to occur in the same operation. Queries other than SELECT still RELEASE as a separate operation since they can't use CURSOR, and we assume we may sometimes need a valid rowCount, etc.
The strategy employed for closing cursors is to let COMMIT or ROLLBACK do the implicit CLOSE, and just issue a unique name for each cursor.
Comment #26
fvideon CreditAttribution: fvideon commentedComment #27
PanchoNice work. Certainly needs testing, and probably also profiling on PostgreSQL.
Also, I hope this doesn't cover failing transactions on PostgreSQL, which are not even tested by our bots. What was the failing query again in this case?
Need to check if this potentially works out for other dbas just as well, such as Oracle, SQL Server etc.
In the end I'm still not sure we shouldn't mimic PostgreSQL's behaviour on MySQL instead, but @Damien, I trust in your experience.
Comment #28
Damien Tournoud CreditAttribution: Damien Tournoud commentedIt seems like the ODBC driver for PostgreSQL does exactly the same thing. We could look into how they implemented it.
Comment #29
PanchoOh, that would certainly be an argument for this approach...
Where did you find this documented, so you don't necessarily have to investigate this yourself?
Comment #30
Damien Tournoud CreditAttribution: Damien Tournoud commentedNot sure where it is documented, but I have seen several log extracts that show that it does. For example, this confused user.
Comment #31
PanchoThis is where it seemed to be introduced to ODBC, and it seems this problem is why psqlodbc introduced savepoint support at all.
Didn't really find much in the Changelog, so we need to dig into the sourcecode.
Comment #32
PanchoHere's the initial commit for savepoint support in psqlodbc.
Don't know if it is already actually implemented here. No time for looking into it more closely now, but possibly later tonight.
Comment #33
steinmb CreditAttribution: steinmb commentedTook #25 for a spin, my test environment is identical to what I used in #8. No comments to the approach taken to solve the issue then I'm out of my depth and you need a deep postgres knowledge say anything useful, but I can help out test the issue. Attaching log from Postgres during installation.
#25 improve stuff. We are able finish a standard install but Drupal is still not working properly after the installation is done.
Keep up the good work.
Comment #34
steinmb CreditAttribution: steinmb commentedThe issue also need a proper summary.
Comment #35
fvideon CreditAttribution: fvideon commentedIt appears to me (as described in #11) that the errors like 'relation "cache_*" does not exist' are normal, 'by design' behavior, since the cache tables are not created until the first set() method call, but may be queried before that.
Comment #36
steinmb CreditAttribution: steinmb commentedThat might be so, but then perhaps set() never gets called or it fails in creating the missing tables. The error in #33 (except the log file) is from trying to use Drupal after the installation is finished. Menu and the toolbar both where missing menu links.
Putting the summary tag back, we should follow https://drupal.org/issue-summaries
Comment #37
fvideon CreditAttribution: fvideon commentedSorry about deleting the summary update tag -- that was unintentional.
A couple of new things:
I was looking at why node/add/page was throwing a PDO Exception on the freshly installed postgresql site, and it came down to a call to drupal_valid_path with argument 'user/autocomplete' which resulted in a query of the form:
select * from users where (uid IN ('autocomplete'))
Oddly (or so it seems to me) this works fine on Mysql, and it returns the row with uid = 0. On Postgresql it throws a PDO exception because 'autocomplete' is not of type integer.
The workaround implemented does a check in user_load() to make sure UID looks like a uint.
Secondly there was a problem with fetching the menu entries for the admin toolbar. This issue ended up being caused by the way the orderBy override in Driver/pgsql/Select.php is adding all the orderBy attributes to the query ahead of those added by Entity\Query. When processing results, Entity\Query expects specific attributes at indices 0 and 1 which are not there. The workaround was to add a tag to the query that causes the orderBy override to skip the step of adding fields. This works as long as Entity\Query does add all the fields it is using for sorting, and as far as I've seen it seems to.
Comment #38
fvideon CreditAttribution: fvideon commentedUpdated summary
Comment #38.0
fvideon CreditAttribution: fvideon commentedUpdates to reflect current status
Comment #39
steinmb CreditAttribution: steinmb commentedPHP 5.4.13 (cli) (built: Apr 16 2013 11:46:33)
PostgreSQL 9.2.4
Attached PostgreSQL log from the installation.
Sorry for the delay...
Hmmm, toolbar is still not working properly. Getting simular error like this after the installation:
Toolbar broken
Another thing I notice is that breadcrumb is not working. Not sure if it is related or not.
Breadcrumb not working
Comment #40
fvideon CreditAttribution: fvideon commentedVery interesting. I am getting different results. The standard install succeeds for me, and the toolbar and breadcrumbs seem to be working correctly.
Here is more detail about my environment and test procedure. Maybe you can spot the significant difference.
PHP 5.4.15 (cli) (built: May 8 2013 21:26:53)
PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 32-bit
I started with freshly pulled code from 8.x master from about an hour ago with the patch from #37 applied and a one line fix to allow the database creation to work as discussed here: https://drupal.org/node/2010368
I started with no settings.php and no database. I chose a standard install, chose to use PostgreSQL and otherwise used defaults.
When setup completed, the site appears to be working as far as I can tell. I can see the database has 60 tables including cache_menu and cache_toolbar, but not cache_page.
When I use the site, I see these in the PostgreSQL log:
PHP.ini settings that I changed from default settings include:
My PostgreSQL log from setup is attached.
Comment #41
mradcliffeI had the same experience as fvideon in #40. Although same version of postgresql 9.2.4, Mac OSX 10.7, Running MAMP PHP 5.4.4, Apache 2.2.2.
I think the toolbar issue is an unfixed bug. Is that global still used..? I cannot find a follow-up issue for that @todo, but it's from #1137920: Fix toolbar on small screen sizes and redesign toolbar for desktop, which is a 300+ comment mega-issue that I don't really want to read through at the moment.
Comment #42
steinmb CreditAttribution: steinmb commentedReally strange.... Our setups and way to test is more and less identical. I'll fire up my vagrant machine and test on Linux and PostgreSQL 9.1.8.
Comment #43
steinmb CreditAttribution: steinmb commentedUbuntu 12.04 LTS
Apache 2.2.22
PHP 5.4.15
PostgreSQL 9.1.8
Looking good! Standard installation, breadcrumb and toolbar all works as expected. Created a node and got these that prob. are unrelated to this issue.
I'll re-test with postgres 9.2.4. Perhaps there was something with head or my db. I'll drop it and recreate it and not only drop the tables.
Comment #44
steinmb CreditAttribution: steinmb commentedOS X 10.8.x
Apache 2.2.22
PHP 5.4.15
PostgreSQL 9.2.4
Re-tested on PostgreSQL 9.2.4 and this time did it work just fine, so go figure. So where do we move from here? Not sure how to profile this issue.
Comment #45
mradcliffeI think the node translation query should be spun off into a new issue, and I think it's fairly major.
I think this issue should try to get installation issues solved for the most part. I don't think a consensus was reached about the performance implications of the approach in #25.
Comment #46
fvideon CreditAttribution: fvideon commentedHere is a straw man approach to profiling that I think would give fairly realistic metrics:
Thoughts?
Comment #47
JimmyAx CreditAttribution: JimmyAx commentedFailed to install for me.
Debian 7
PostgreSQL 9.1
Drupal 8 from Git
On line 221 in menu.module we got
$link->plid = $system_link->id();
. Avar_dump
on$system_link
right afterentity_load_multiple_by_properties
gives an empty array.Comment #48
mradcliffeRe: #46 @fvideon: Yes, that makes sense to me, but I'm not an expert at profiling either. :|
Comment #49
JimmyAx CreditAttribution: JimmyAx commentedThis might be related to this issue: #1060476: Multiple issues when PostgreSQL is used with non-public schema
And for #43 (the invalid integer syntax): #1003788: PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID
Comment #50
steinmb CreditAttribution: steinmb commented@JimmyAx: did you try the patch?
Comment #51
JimmyAx CreditAttribution: JimmyAx commentedThe one in #37? That one did not apply so I had to reroll it. This one is without the
user_load()
fix as that belongs to #1003788: PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar IDThe patch is not an acceptable solution. While it appears to be working (I got through the entire installation) it generates a lot of errors in the PostgreSQL log due to a lot of tables missing (mostly cache_*). I'm attaching the log from the start of the installation until the first page of the site.
Comment #52
fvideon CreditAttribution: fvideon commentedNote that if you run through the D8 install with MySQL and look at PDOExceptions, you will see a lot of them in the form of
SQLSTATE[42S02]: Base table or view not found: 1146 Table '<tablename>' doesn't exist
I didn't see a way to make MySQL log these. One difference with PostgreSQL is that they do get logged with the default logging configuration.
Comment #53
Josh Waihi CreditAttribution: Josh Waihi commentedRe @JimmyAx last comment (#51)
I found this issue came from Drupal attempting to find the install task. It doesn't even check to see if the table exists before querying it, it assumes the failure means that Drupal is not installed yet. Which is true. But its still a dirty hack. See install_verify_completed_task
I also get the same sort of issues, primarily with cache table. If I change the default cache handler to memory instead, I can install Drupal, but I still get a partially installed interface (missing menu items etc).
Comment #54
steinmb CreditAttribution: steinmb commented@Josh sounds correct. #51 contain some code that allow (at least it did) you to install Drupal. You even end up with a working installation. It's not perfect and needs work. I'm not even sure if it's the right solution....
Comment #55
Josh Waihi CreditAttribution: Josh Waihi commentedAFAIK, PostgreSQL has always had transactional DDL statements so I don't think that is the underlying cause.
IMO, transactional statements isn't preferable either. Why should a statement fail and still allow the transaction to commit?
Comment #56
Josh Waihi CreditAttribution: Josh Waihi commentedI recorded all the postgres statements from an attempted install of Drupal 8. I parsed out all CREATE, ERROR, BEGIN & ROLLBACK statements and have attached them.
There are 10 tables that generate query failures: key_value, cache_config, cache, cache_bootstrap, cache_views_info, cache_field, cache_entity, cache_block, cache_views_results and cache_menu.
In most cases, it appears that Drupal attempts to query these tables before they've been actually created in the database. However, the cache_block table simply does not get created. Nor is there an error from PostgreSQL explaining why. This would suggest that Drupal opted not to install the table for some other reason.
Comment #57
xjmComment #58
xjmComment #59
xjmComment #60
mradcliffeah ha!
Drupal\Core\Database\Driver\pgsql\Schema.php is missing. pgsql driver does not implement Schema.php. I am confused about how tables are being created without it, but that's the problem with cache and probably other issues as well.
I'll work on this today.
Comment #61
mradcliffeWait, my git directory was all messed up. Welp.
Comment #62
mradcliffeI added a band-aid fix in DatabaseBackend.php which ensures that the cache tables exist if the method fails. This gets me through the install again.
Sorry for the false alarm re: Schema.php.
Comment #63
mradcliffe#autocomplete_path is calling the following query and is uncaught PDOException in Database\Statement.php:
I'm not familiar enough to trace through to where this is. I know it's an access check, maybe called somewhere in url() and the access check behind UrlGenerator.
Comment #64
mradcliffeAutocomplete issue identified in #2056627: Form API autocomplete is broken for routes.
Comment #64.0
mradcliffesummary-related-issues issue link changed
Comment #64.1
mradcliffeAdd related blocker (?)
Comment #64.2
mradcliffeAdd to resolution section
Comment #65
mradcliffeThe cache issues are related to #2067429: Fix installer to not use Drupal\Core\Cache\DatabaseBackend, which probably points out an incompatibility with invoking cache backends.
Comment #66
mradcliffeThis patch adds comments to the quick fix in \Drupal\Core\Cache\DatabaseBackend.php.
Discussed with @effulgentsia, @alexpott, @msonnabaum, @xjm, and myself, and got buy-in for the quicker fix as long as this is passes testing (manual too) even if we don't know the exact root cause yet.
@msonnabaum mentioned that it would be interesting to create a separate cache backend that uses hstore in the future.
Comment #67
burningdog CreditAttribution: burningdog commentedI can't comment on the patch, because I don't understand the code, but after applying it installation works for me.
Postgres: 9.2.4
PHP: 5.3.27
Mac OS 10.6.8
Thank you! The only error I have in the drupal error log is generated by cron at http://drupal8.local/core/install.php?langcode=en&profile=standard and reads:
Comment #68
mradcliffe@burningdog, this issue looks like it was introduced by #2003482: Convert hook_search_info to plugin system, which was a commit from last week, but in fact it is also a bug in Drupal 7 node_update_index(). The code was taken pretty much straight into the new NodeSearch class.
I did not find any current issue when I searched for node_update_index or search. Created #2087169: NodeSearch::updateIndex does not include order by column in select.
Comment #69
terikon CreditAttribution: terikon commentedI confirm that #62 solved for me following installation error, on Windows and MySQL (not PostgreSQL):
SQLSTATE[42S02]: Base table or view not found: 1146 Table '***.cache_field' doesn't exist.
Comment #70
thekevinday CreditAttribution: thekevinday commentedI can confirm that the problem happens with 8.0-alpha3, but ...
As of the October 7, 2013, this issue does not appear in postgresql. I have not applied the patch here and the sight is now functional.
I do not know when the problem went away, but the last commit I pulled was:
- http://drupalcode.org/project/drupal.git/commit/04f662ff72c3030615e7e454...
Comment #71
webchickInteresting! Can anyone else confirm?
Comment #72
mradcliffeUnfortunately I cannot confirm this with my current HEAD (commit ae7c20c7b5f1d65e66592328946a27eed808c434)
I haven't reached any transaction stoppage due to cache table creation, but comment_entity_statistic table creation borked and failed the install.
Postgres 9.2.4
Comment #73
mradcliffeI did a bisect and stepped back to 5089f20fc96295330aefb8587ca06c300b070de6, but that also failed. That commit didn't do anything. I checked out the commit above @thekevinday mentioned
commit 04f662ff72c3030615e7e454eeada7277a72e360 (HEAD)
I ran into the same exact issue in the previous comment under the standard install.
I tried a minimal install with the same commit, and this installed
correctly. @thekevinday, did you try the standard install or just the minimal install?Edit: Notice: A non well formed numeric value encountered in Drupal\menu_link\MenuLinkStorageController->save() (line 219 of core/modules/menu_link/lib/Drupal/menu_link/MenuLinkStorageController.php).
Edit 2: I enable menu, path, and toolbar. No menus appear in toolbar similar to previous comments in this issue
Comment #74
mradcliffeSetting back status.
I re-rolled #66 and ran into the same issue. It looks like some other commit from the last month killed support for comment_entity_statistics.
I've uploaded the re-rolled patch after fixing a conflict that occurred when rebasing to origin/8.x on my local branch. Doesn't need to be tested.
Comment #75
mradcliffeIt looks like postgres database driver in Drupal doesn't support this, which was introduced in 539 comment MONSTER MONSTER MONSTER Issue #731724
Edit: mysql and sqlite drivers have a createKeySql() implementation, but pgsql driver does not. Introduced in commit f4a58c3f3af1b5cb475ad427ab1dd69df926515f, which even has a title of "Postgres" :(
Comment #76
mradcliffeOkay, fixed the issue with comment_entity_statistics by writing another helper function to basically ignore the schema syntax. I guess fillfactor could be used, but that's up to debate.
I ran into this posting a comment, which seems to me that core still has weird menu / routing issues.
So pretty much the state of postgres just got worse in the last couple of weeks :*(
Comment #77
mradcliffeI think menu_get_item() needs to be solved completely before any more work can be done i.e. kill it with fire. I can't find all of the tasks that nuke it, but the one we're interested in now is the call in menu_get_custom_theme().
Is there a meta task for menu_get_item or an issue for menu_get_custom_theme yet?
Comment #78
mradcliffeComment issue is not related to this issue because it does not prevent Drupal 8 from being installed. Needs follow-up created.
I'm setting this to needs review as #76 is back to installable state.
Comment #79
thekevinday CreditAttribution: thekevinday commentedSorry, its clear that I miscommunicated with my last post.
What I meant was up to the said commit in the repository and now things work.
Not that specific commit.
As for the install I used, I am using minimal install.
Okay, so I managed to identify where things started to work in the 8.x branch:
- http://drupalcode.org/project/drupal.git/commit/ef2e45b0e86fc6863b8ee6d3...
- With this exact commit, things started to work in drupal 8 on postgresql.
- All commits prior to this commit on the 8.x branch are applied in my testing.
I tested by doing the following:
-
git reset ef2e45b --hard
from the 8.x branch and then installing drupal 8 in minimal install mode.-
git reset 61fcb6f --hard
from the 8.x branch and then installing drupal 8 in minimal install mode.(61fcb6f is the commit immediately before the working commit of ef2e45b in the 8.x branch).
I have not done any testing in standard install mode.
Comment #80
mradcliffeUpdated title.
Comment #81
mradcliffeActually, sorry for the spam, but I'm going to remove that title change. My testing with the minimal install profile revealed that no menus were available after installing the toolbar module.
Comment #82
thekevinday CreditAttribution: thekevinday commentedI just tested #76 and it seems to fix the remaining issues/warnings/errors.
Update:
- Prior to the patch in #76, menus do not appear.
- After applying the patch in #76, menus do appear.
Comment #82.0
thekevinday CreditAttribution: thekevinday commentedAdded related issue #1003788
Comment #83
dlu CreditAttribution: dlu commentedAttempted to install D8 this morning (fresh pull of HEAD, not patches) and got the attached error. Not sure if this is the right place to post it. I'll look into it more this afternoon.
Comment #84
mradcliffeThe patch in #76 addresses that issue, dlu. Can you test?
Comment #85
dlu CreditAttribution: dlu commentedI'll test it this afternoon. I'm working on https://drupal.org/node/1013034, thanks for this insight.
Comment #86
fvideon CreditAttribution: fvideon commentedI saw the error in #83 last week even with the patch from #76. I noticed that it happened consistently when installing the taxonomy module. This was the configuration:
Windows 8
PHP 5.5 x64 TS (Binary marked "experimental" on php.net)
Apache 2.4 x64
The problem went away when I switched to:
PHP 5.4.20 x86
Apache 2.4 x86
PostgreSQL 9.2 was the same in both cases.
I attributed it to running the experimental PHP build.
Comment #86.0
dlu CreditAttribution: dlu commentedUpdated resolution with comment_entity_statistics primary key issue, and removed general testing. At this point I think general testing is done.
Comment #87
dlu CreditAttribution: dlu commentedI've successfully installed today's version of D8 (as of 0f8b4e794e1e4c453185f2856b768aaeaa86f83b) with the patch from #76 using PostgreSQL (9.3.1) and PHP 5.5.4 (Homebrew) on OS X 10.9. I'm working on https://drupal.org/node/1013034 and would be happy to do anything that would help with testing along the way. I didn't see any problems as the Taxonomy module installed.
Not sure if this means it can go to RTBC or not. [edit: nope, should have read the issue summary more closely.]
Comment #88
dlu CreditAttribution: dlu commentedOne more problem. Currently can't create a database, these patches only work if you're using an existing database. See #2111979: PostgreSQL is unable to create a new database.
Comment #89
JimmyAx CreditAttribution: JimmyAx commentedThis also affects 7.x as I was unable to install the latest dev snapshot to debug an issue.
Without the patch I get
SQLSTATE[42P07]: Duplicate table: 7 ERROR: relation "cache_tags" already exists
. With the patch I get the exact same error message so it looks like things have changed.Comment #89.0
JimmyAx CreditAttribution: JimmyAx commentedAdded #2111979: PostgreSQL is unable to create a new database to list of related issues.
Comment #90
mradcliffeI fetched and rebased my local branch to 8.x, but was not able to reproduce any oddities. I still have a working installation. Adding a re-roll shortly.
Comment #91
mradcliffeRe-roll.
Comment #92
mgifford91: 2001350-pgsql-fixes-91.patch queued for re-testing.
Comment #94
mradcliffeI ran this test locally after fetching and rebasing to origin/8.x on my postgres branch, and it passed with flying colors. I did a git diff origin/8.x and the resulting patch was the same as patch in #91.
Let's try the test again.
Comment #95
mradcliffe91: 2001350-pgsql-fixes-91.patch queued for re-testing.
Comment #97
mradcliffeI also confirmed that the test passes locally in MySQL via MAMP.
Not sure why the heck this is happening. Currrently HEAD is failing tests anyway, but not this test.
Comment #98
mgiffordI'm not sure.. I should be able to find this here, right:
$ locate ValidationTest.php | grep form
I can't write unit tests, but maybe someone else can translate this error:
The test did not complete due to a fatal error.
Completion check
ValidationTest.php
195
Drupal\system\Tests\Form\ValidationTest->testCustomRequiredError()
Comment #99
dcrocks CreditAttribution: dcrocks commentedThe testing log showed
Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 268435456 bytes) in /var/lib/drupaltestbot/sites/default/files/checkout/core/lib/Drupal/Core/Database/Connection.php on line 336
FATAL Drupal\system\Tests\Form\ValidationTest: test runner returned a non-zero error code (255).
Is this from the testing environment?
Comment #100
mradcliffe91: 2001350-pgsql-fixes-91.patch queued for re-testing.
Comment #101
mradcliffeYes, that's from the test environment. The test fail in HEAD (not ValidationTest) is passing now so let's try again.
Comment #103
bzrudi71 CreditAttribution: bzrudi71 commentedTo hopefully get any progress on PostgreSQL fixing I created a new META issue queue for PostgreSQL, please see #2157455: [Meta] Make Drupal 8 work with PostgreSQL or remove support from core before release for details.
Regarding patch in #76 - It currently still applies to HEAD and PostgreSQL can be installed using standard profile. (And works afterwards :-))
However, PostgreSQL log still shows some errors with current patch and PostgreSQL Version 9.3 which should be fixed...
Comment #104
bzrudi71 CreditAttribution: bzrudi71 commentedComment #105
deimos CreditAttribution: deimos commented91: 2001350-pgsql-fixes-91.patch queued for re-testing.
Comment #107
InternetDevels CreditAttribution: InternetDevels commentedRe-roll with little fixes according to latest changes in core.
Comment #108
mradcliffeI probably shouldn't be assigned, forgot to do this since September.
Comment #109
deimos CreditAttribution: deimos commentedHi there guys,
previously I was working on Drupal 8 database driver for Oracle DB (#2037119: Roadmap for Drupal 8 oracle driver) and got a lot "funny" situation with Drupal database layer.
As result, I spent few hours to analyse this issue, all it's comments and pending patches.
Main idea of the issue (Drupal 8 installation on PostgeSQL) is fixed and installation on existing database works fine after applying the patch.
If apply #76 patch from #203955: Create database at installation time then installation works without existing database.
According to issue summary for me:
1. Transaction issue is solved.
2. All needed cache tables are creating.
3. Problem with autocomplete doesn't appears.
4. Toolbar links are present and breadcrumbs works are working corretly.
5. Problem with primary key fields that are defined by an array isn't appeared. comment_entity_statistics table was created with all defined indexes during installation.
I attached default postgresql log.
About errors in log-file: I am agree with #52. All errors is related to queries to missing tables and it isn't problem of PostgreSQL database driver because MySQL driver causes the same errors but just doesn't write them to log.
And we realy need this changes (patch from comment #107) to be committed to core because currently installation on PostgreSQL doesn't work at all. So, I set RTBC.
Comment #110
deimos CreditAttribution: deimos commentedOops, log-file is empty, attach correct one.
Comment #111
webchickGreat work on this, folks!!! So happy to see this green. :)
I was going to commit this today, but there's quite a bit of DB internals here that I don't quite understand, so assigning it to Crell for a lookover.
Comment #112
webchickTwo small things to factor into the next re-roll:
We don't abbreviate variable names; should be spelled out "_cursor_counter".
This function needs docs.
Comment #113
Crell CreditAttribution: Crell commentedWhat Angie said. Also, default to using protected, not private.
I don't know PostgreSQL at all, so I'm not entirely sure what's going on here. However, string parsing SQL is what we try extremely hard to avoid. In what case is this even necessary, and why not do it in a more structured fashion?
As above.
Ugh, I didn't even realize we had this in here.
It looks like a lot of this code here and above is tucking multiple query statement into a single query string. I don't know PostgreSQL enough to know if that's the "right" way to do things, but I do know that all this string parsing is not the "right" way in Drupal.
If there's a need to flag and track before/after concatenated queries, then we need an object that will do so. That is, we probably need to add a Postgres-specific set of methods (with their own interface and possibly trait or composable class) to the various query objects, and possibly even wrap the query string into an object to track internally. That way at least we can just deal with a few booleans that get compiled down rather than all this string parsing.
As an aside, can someone explain to me why this is necessary? I mean I see the comment, but I don't know what triggered this to be necessary in the first place. (Again, Postgres illiterate here.)
Methods should never have a leading underscore. That's PHP 4 legacy that we have long since left behind. (If this is extending another method somewhere, that's a bug that should be fixed.)
There should be no DB-specific code outside of the drivers themselves. Client code should never have to care what DB its on.
Comment #114
andypostRelated bugfix needs reviews asap - #2167507: Fix rowCount query usage in pgsql and sqlite drivers
Comment #115
andypostThe
rowCount()
change caused by #2146733: Select queries should not use rowCount() to calculate number of rows should be fixed in #2167507-16: Fix rowCount query usage in pgsql and sqlite driversComment #116
webchickHey there folks, I just committed #2167507: Fix rowCount query usage in pgsql and sqlite drivers so we can factor that code out of the next re-roll.
Comment #117
Crell CreditAttribution: Crell commentedComment #118
mradcliffeI've changed this into a meta issue to address the four (or more) unfixed issues referenced in the issue summary separately.
I think some of those issues can be fixed and committed knowing that install state is still "broken". It's broken now so it won't be any worse off.
Comment #119
mradcliffeProgress so far on sub-tasks:
Comment #120
mradcliffeAll sub issues are currently "Needs Review" and waiting on code review + manual testing. The manual testing part requires integrating ALL patches because they're all necessary to get through the installation.
My process for manual testing is as follows:
git checkout 8.x; git checkout -b postgres-ISSUENUM; curl -L PATCHURL | git apply; git commit -a
git checkout 8.x; git checkout -b postgres-merge-test
git merge postgres-ISSUENUM
Comment #121
bzrudi71 CreditAttribution: bzrudi71 commentedThanks mradcliffe for splitting the patch for better overview! I will do manual testing and review in the next days to see where we are. The 'old' patch worked for months, so I think the new issues will do also, but let's see...
Comment #122
bzrudi71 CreditAttribution: bzrudi71 commentedAdded note about #2177031: Remove menu_get_item() and every use of it.
Comment #123
bzrudi71 CreditAttribution: bzrudi71 commentedAs all required patches to make D8 install on PG again are in, I'm going to close this one as fixed :-)
All other remaining issues we have to take care of are now in #2157455: [Meta] Make Drupal 8 work with PostgreSQL or remove support from core before release. If one disagrees with that, please change status accordingly...
Comment #124
mradcliffeThe testing bot uses testing profile, not standard profile.
#2181291: Prevent a query from aborting the entire transaction in pgsql still blocks installing on the standard profile. It's critical in and of itself. Do you want to keep this issue closed?
Comment #125
bzrudi71 CreditAttribution: bzrudi71 commented@mradcliffe Hmmh, D8 installs fine locally even in standard profile without #2181291: Prevent a query from aborting the entire transaction in pgsql on PG 9.3 for me. To make sure I'm not working with any testing code I did a git reset --hard origin/8.x first and it still works. All I can see from the PG logs is that the related error about the aborted transactions exists, but does no longer prevent from installing.
Can you crosscheck please. If it fails for you we need for sure leave this open ;-)
Comment #126
mradcliffeDo you have a working site afterward with menus?
Comment #127
mradcliffeI successfully installed Drupal without it, and I do have menus. :-)
Adjusted issue summary.
Comment #128
bzrudi71 CreditAttribution: bzrudi71 commentedIn short, yes! Site is working fine (screenshot attached)... BTW The broken menu was fixed in #2181285: Fix orderBy override in pgsql driver for toolbar menu links
Comment #129
ILMostro CreditAttribution: ILMostro commentedI'm struggling to get drupal7 installed with postgresql9.3 on Fedora20. Searching for ERROR: relation "variable" does not exist at character 19 led me to this discussion; I don't see if this is resolved or whether it was even pin-pointed to a particular file, code base, function. Again, during install pg_log shows:
ERROR: relation "variable" does not exist at character 19
STATEMENT: SELECT value FROM variable WHERE name = 'install_task'
Installation fails, and "The requested page could not be found" with the Drupal Header showing on page.
Comment #130
cilefen CreditAttribution: cilefen commented