[PHPBB3-12012] DB Tools should correctly remove columns that are part of indexes

Details

Type: Bug
Status: Unverified Fix
Priority: Blocker
Resolution: Fixed
Affects Version/s: 3.1.0-a1
Fix Version/s: 3.1.0-b3
Component/s: Database Abstraction Layer (DBAL)
Labels:
None

GitHub Pull Request URL:
https://github.com/phpbb/phpbb/pull/2288

Description

The index must be removed before the column can be dropped.
The best would be if the migrator/db_tools would check this, so you don't have to specify them in your migration.

The idea from PHPBB3-11922 was that when dropping an column you specify what happens to the indexes (whether they are recreated without the column, are dropped completly, ...)

Issue Links

is related to

PHPBB3-11922 Migrator fails to remove columns on MSSQL when they have/had an index

Unverified Fix

obsoletes

PHPBB3-9639 MSSQL and db tools sql_index_drop

Closed

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Joas Schilling added a comment - 15/Nov/13 12:13 AM

MSSQL can also not change the column type when the column has a default value (constraint).
The constraint needs to be removed before changing the column

Show

Joas Schilling added a comment - 15/Nov/13 12:13 AM MSSQL can also not change the column type when the column has a default value (constraint). The constraint needs to be removed before changing the column

Hide

Permalink

EXreaction [X] (Inactive) added a comment - 04/Dec/13 10:03 PM

I don't think this needs to be a blocker as it doesn't affect any current code.

Show

EXreaction [X] (Inactive) added a comment - 04/Dec/13 10:03 PM I don't think this needs to be a blocker as it doesn't affect any current code.

Hide

Permalink

Joas Schilling added a comment - 04/Dec/13 10:41 PM - edited

It blocks updating 3.0 to 3.1 on mssql,
so yes I want this to be a blocker for b1

Show

Joas Schilling added a comment - 04/Dec/13 10:41 PM - edited It blocks updating 3.0 to 3.1 on mssql, so yes I want this to be a blocker for b1

Hide

Permalink

EXreaction [X] (Inactive) added a comment - 04/Dec/13 10:43 PM

I thought that issue was resolved by fixing the migrations that had issues?

Show

EXreaction [X] (Inactive) added a comment - 04/Dec/13 10:43 PM I thought that issue was resolved by fixing the migrations that had issues?

Hide

Permalink

EXreaction [X] (Inactive) added a comment - 03/Feb/14 9:10 PM

Can MSSQL installs be updated from 3.0 to 3.1 using the existing migrations?

If not, what are the failing points?

Show

EXreaction [X] (Inactive) added a comment - 03/Feb/14 9:10 PM Can MSSQL installs be updated from 3.0 to 3.1 using the existing migrations? If not, what are the failing points?

Hide

Permalink

EXreaction [X] (Inactive) added a comment - 06/Mar/14 12:49 AM

Bump

Show

EXreaction [X] (Inactive) added a comment - 06/Mar/14 12:49 AM Bump

Hide

Permalink

Patrick Webster added a comment - 11/Mar/14 3:12 AM

It still fails with the same problem (trying to modify a column with a default constraint/value).

Updating database to latest stable release

Database type :: mssqlnative
Previous version :: 3.0.12
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_1_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_2_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_2_rc2
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_2
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_3_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_3
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_4_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_4
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_5_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_5_rc1part2
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_5
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6_rc2
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6_rc3
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6_rc4
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_7_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_7_rc2
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_7
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_7_pl1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_8_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_8
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9_rc2
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9_rc3
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9_rc4
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_10_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_10_rc2
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_10_rc3
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_10
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_11_rc1
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_11_rc2
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_11
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_12_rc1
Installed Schema: \phpbb\db\migration\data\v30x\local_url_bbcode; Time: 0.00 seconds
Installed Data: \phpbb\db\migration\data\v30x\local_url_bbcode; Time: 0.02 seconds
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_12_rc2
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_12_rc3
Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_12
Installed Schema: \phpbb\db\migration\data\v310\extensions; Time: 0.02 seconds
Installed Data: \phpbb\db\migration\data\v310\extensions; Time: 0.67 seconds
Installed Schema: \phpbb\db\migration\data\v310\style_update_p1; Time: 0.37 seconds
Installed Data: \phpbb\db\migration\data\v310\style_update_p1; Time: 0.14 seconds
Installed Schema: \phpbb\db\migration\data\v310\style_update_p2; Time: 1.80 seconds
Installed Data: \phpbb\db\migration\data\v310\style_update_p2; Time: 0.00 seconds

General Error
SQL ERROR [ mssqlnative ]

SQLSTATE: 42000 code: 5074 message: [Microsoft][SQL Server Native Client 11.0][SQL Server]The object 'DF_phpbb_useuser__2C538F61' is dependent on column 'user_timezone'. SQLSTATE: 42000 code: 4922 message: [Microsoft][SQL Server Native Client 11.0][SQL Server]ALTER TABLE ALTER COLUMN user_timezone failed because one or more objects access this column. [4922]

SQL

ALTER TABLE [phpbb_users] ALTER COLUMN [user_timezone] [varchar] (100) NOT NULL

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 803
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mssqlnative.php
LINE: 144
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/tools.php
LINE: 1458
CALL: phpbb\db\driver\mssqlnative->sql_query()

FILE: [ROOT]/phpbb/db/tools.php
LINE: 2504
CALL: phpbb\db\tools->_sql_run_sql()

FILE: [ROOT]/phpbb/db/tools.php
LINE: 769
CALL: phpbb\db\tools->sql_column_change()

FILE: (not given by php)
LINE: (not given by php)
CALL: phpbb\db\tools->perform_schema_changes()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 455
CALL: call_user_func_array()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 401
CALL: phpbb\db\migrator->run_step()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 240
CALL: phpbb\db\migrator->process_data_step()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 202
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 202
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 202
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 153
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/install/database_update.php
LINE: 222
CALL: phpbb\db\migrator->update()

That is using the latest develop.

Show

Patrick Webster added a comment - 11/Mar/14 3:12 AM It still fails with the same problem (trying to modify a column with a default constraint/value). Updating database to latest stable release Database type :: mssqlnative Previous version :: 3.0.12 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_1_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_2_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_2_rc2 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_2 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_3_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_3 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_4_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_4 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_5_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_5_rc1part2 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_5 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6_rc2 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6_rc3 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6_rc4 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_6 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_7_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_7_rc2 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_7 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_7_pl1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_8_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_8 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9_rc2 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9_rc3 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9_rc4 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_9 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_10_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_10_rc2 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_10_rc3 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_10 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_11_rc1 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_11_rc2 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_11 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_12_rc1 Installed Schema: \phpbb\db\migration\data\v30x\local_url_bbcode; Time: 0.00 seconds Installed Data: \phpbb\db\migration\data\v30x\local_url_bbcode; Time: 0.02 seconds Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_12_rc2 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_12_rc3 Migration already effectively installed (skipped): \phpbb\db\migration\data\v30x\release_3_0_12 Installed Schema: \phpbb\db\migration\data\v310\extensions; Time: 0.02 seconds Installed Data: \phpbb\db\migration\data\v310\extensions; Time: 0.67 seconds Installed Schema: \phpbb\db\migration\data\v310\style_update_p1; Time: 0.37 seconds Installed Data: \phpbb\db\migration\data\v310\style_update_p1; Time: 0.14 seconds Installed Schema: \phpbb\db\migration\data\v310\style_update_p2; Time: 1.80 seconds Installed Data: \phpbb\db\migration\data\v310\style_update_p2; Time: 0.00 seconds General Error SQL ERROR [ mssqlnative ] SQLSTATE: 42000 code: 5074 message: [Microsoft][SQL Server Native Client 11.0][SQL Server]The object 'DF_ phpbb_use user __2C538F61' is dependent on column 'user_timezone'. SQLSTATE: 42000 code: 4922 message: [Microsoft][SQL Server Native Client 11.0][SQL Server]ALTER TABLE ALTER COLUMN user_timezone failed because one or more objects access this column. [4922] SQL ALTER TABLE [phpbb_users] ALTER COLUMN [user_timezone] [varchar] (100) NOT NULL BACKTRACE FILE: (not given by php) LINE: (not given by php) CALL: msg_handler() FILE: [ROOT]/phpbb/db/driver/driver.php LINE: 803 CALL: trigger_error() FILE: [ROOT]/phpbb/db/driver/mssqlnative.php LINE: 144 CALL: phpbb\db\driver\driver->sql_error() FILE: [ROOT]/phpbb/db/tools.php LINE: 1458 CALL: phpbb\db\driver\mssqlnative->sql_query() FILE: [ROOT]/phpbb/db/tools.php LINE: 2504 CALL: phpbb\db\tools->_sql_run_sql() FILE: [ROOT]/phpbb/db/tools.php LINE: 769 CALL: phpbb\db\tools->sql_column_change() FILE: (not given by php) LINE: (not given by php) CALL: phpbb\db\tools->perform_schema_changes() FILE: [ROOT]/phpbb/db/migrator.php LINE: 455 CALL: call_user_func_array() FILE: [ROOT]/phpbb/db/migrator.php LINE: 401 CALL: phpbb\db\migrator->run_step() FILE: [ROOT]/phpbb/db/migrator.php LINE: 240 CALL: phpbb\db\migrator->process_data_step() FILE: [ROOT]/phpbb/db/migrator.php LINE: 202 CALL: phpbb\db\migrator->try_apply() FILE: [ROOT]/phpbb/db/migrator.php LINE: 202 CALL: phpbb\db\migrator->try_apply() FILE: [ROOT]/phpbb/db/migrator.php LINE: 202 CALL: phpbb\db\migrator->try_apply() FILE: [ROOT]/phpbb/db/migrator.php LINE: 153 CALL: phpbb\db\migrator->try_apply() FILE: [ROOT]/install/database_update.php LINE: 222 CALL: phpbb\db\migrator->update() That is using the latest develop.

People

Assignee:

Joas Schilling

Reporter:

Joas Schilling

Votes:

0 Vote for this issue

Watchers:

3 Start watching this issue

Dates

Created:

09/Nov/13 12:45 PM

Updated:

06/May/14 8:17 PM

Resolved:

25/Apr/14 4:55 PM

Details

Description

Issue Links

Activity

People

Dates

Development