Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problem in table changes [CORE5019] #5307

Closed
firebird-automations opened this issue Nov 16, 2015 · 9 comments
Closed

Problem in table changes [CORE5019] #5307

firebird-automations opened this issue Nov 16, 2015 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Workdev Tecnologia da Informação LTDA (workdev)

Is related to CORE1518

CREATE TABLE SM_CD_MO_MOVIMENTADOR_CL_V (
EMPRESA INTEGER,
COD INTEGER,
COLABORADOR INTEGER,
REGIAO INTEGER,
REGIAO_SEQUENCIA INTEGER DEFAULT 0 NOT NULL,
ROTA INTEGER,
ROTA_SEQUENCIA INTEGER DEFAULT 0 NOT NULL,
ROTA_DIA_DA_SEMANA SMALLINT DEFAULT 0 NOT NULL,
ROTA_FREQUENCIA_SEMANA SMALLINT DEFAULT 0 NOT NULL,
LATITUDE NUMERIC(18,0) DEFAULT 0 NOT NULL,
LONGITUDE NUMERIC(18,0) DEFAULT 0 NOT NULL,
TABELA_PRECO INTEGER,
TRANSPORTADOR INTEGER,
TIPO_FRETE SMALLINT DEFAULT 0 NOT NULL
);

ALTER TABLE SM_CD_MO_MOVIMENTADOR_CL_V DROP LATITUDE;
ALTER TABLE SM_CD_MO_MOVIMENTADOR_CL_V DROP LONGITUDE;

ALTER TABLE SM_CD_MO_MOVIMENTADOR_CL_V
ADD PRACA INTEGER,
ADD EMPRESA_NEW INTEGER NOT NULL,
ADD COD_NEW INTEGER NOT NULL,
ADD LATITUDE_NEW VARCHAR(18) CHARACTER SET NONE DEFAULT '' NOT NULL,
ADD LONGITUDE_NEW VARCHAR(18) CHARACTER SET NONE DEFAULT '' NOT NULL;

can't format message 13:393 -- message file C:\Windows\SYSTEM32\firebird.msg not found.
unsuccessful metadata update.
Cannot make field EMPRESA_NEW of table SM_CD_MO_MOVIMENTADOR_CL_V NOT NULL because there are NULLs present.
Cannot make field COD_NEW of table SM_CD_MO_MOVIMENTADOR_CL_V NOT NULL because there are NULLs present.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Can`t reproduce:

create table sm_cd_mo_movimentador_cl_v (
empresa integer,
cod integer,
colaborador integer,
regiao integer,
regiao_sequencia integer default 0 not null,
rota integer,
rota_sequencia integer default 0 not null,
rota_dia_da_semana smallint default 0 not null,
rota_frequencia_semana smallint default 0 not null,
latitude numeric(18,0) default 0 not null,
longitude numeric(18,0) default 0 not null,
tabela_preco integer,
transportador integer,
tipo_frete smallint default 0 not null
);

alter table sm_cd_mo_movimentador_cl_v drop latitude;
alter table sm_cd_mo_movimentador_cl_v drop longitude;

alter table sm_cd_mo_movimentador_cl_v
add praca integer,
add empresa_new integer not null,
add cod_new integer not null,
add latitude_new varchar(18) character set none default '' not null,
add longitude_new varchar(18) character set none default '' not null;

show table sm_cd_mo_movimentador_cl_v;
EMPRESA INTEGER Nullable
COD INTEGER Nullable
COLABORADOR INTEGER Nullable
REGIAO INTEGER Nullable
REGIAO_SEQUENCIA INTEGER Not Null default 0
ROTA INTEGER Nullable
ROTA_SEQUENCIA INTEGER Not Null default 0
ROTA_DIA_DA_SEMANA SMALLINT Not Null default 0
ROTA_FREQUENCIA_SEMANA SMALLINT Not Null default 0
TABELA_PRECO INTEGER Nullable
TRANSPORTADOR INTEGER Nullable
TIPO_FRETE SMALLINT Not Null default 0
PRACA INTEGER Nullable
EMPRESA_NEW INTEGER Not Null
COD_NEW INTEGER Not Null
LATITUDE_NEW VARCHAR(18) Not Null default ''
LONGITUDE_NEW VARCHAR(18) Not Null default ''

show version;
ISQL Version: WI-V3.0.0.32157 Firebird 3.0 Release Candidate 1
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-V3.0.0.32157 Firebird 3.0 Release Candidate 1"
Firebird/Windows/Intel/i386 (remote server), version "WI-V3.0.0.32157 Firebird 3.0 Release Candidate 1/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-V3.0.0.32157 Firebird 3.0 Release Candidate 1/tcp (csprog)/P13"
on disk structure version 12.0

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

PS. Did you add some data after CREATE and before 1st ALTER table statement ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Workdev Tecnologia da Informação LTDA (workdev)

table set up at another time and contains data

create table sm_cd_mo_movimentador_cl_v (
empresa integer,
cod integer,
colaborador integer,
regiao integer,
regiao_sequencia integer default 0 not null,
rota integer,
rota_sequencia integer default 0 not null,
rota_dia_da_semana smallint default 0 not null,
rota_frequencia_semana smallint default 0 not null,
latitude numeric(18,0) default 0 not null,
longitude numeric(18,0) default 0 not null,
tabela_preco integer,
transportador integer,
tipo_frete smallint default 0 not null
);

INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 23, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);
INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 24, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);
INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 25, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);
INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 26, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);
INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 27, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);
INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 28, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);
INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 29, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);
INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 30, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);
INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 31, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);
INSERT INTO SM_CD_MO_MOVIMENTADOR_CL_V (EMPRESA, COD, COLABORADOR, REGIAO, REGIAO_SEQUENCIA, ROTA, ROTA_SEQUENCIA, ROTA_DIA_DA_SEMANA, ROTA_FREQUENCIA_SEMANA, LATITUDE, LONGITUDE, TABELA_PRECO, TRANSPORTADOR, TIPO_FRETE)
VALUES (1, 32, NULL, NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, 0);

COMMIT WORK;

made modifications

alter table sm_cd_mo_movimentador_cl_v drop latitude;
alter table sm_cd_mo_movimentador_cl_v drop longitude;

alter table sm_cd_mo_movimentador_cl_v
add praca integer,
add empresa_new integer not null,
add cod_new integer not null,
add latitude_new varchar(18) character set none default '' not null,
add longitude_new varchar(18) character set none default '' not null;

That time is giving error

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

As designed. Adding NOT NULL columns over a populated table is not allowed anymore, because such operations violate the NOT NULL constraint. You may forget to update the new fields with non-NULL values and get unrestorable backup as a result. Now you must specify DEFAULT clauses for all the new columns added this way. You correctly do that for "latitude_new" and "longitude_new" columns, but not for "empresa_new" and "cod_new" ones.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE1518 [ CORE1518 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

See also CORE1518.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant