Re: Invalidate Content Error

Tue, 31 Jan 2017 11:33:31 -0800 

Continuing the investigation of this issue, I have found what I believe to
be an inconsistency in the code that handles content invalidation.
To the best of my understanding, the bug exists in 1.7.x, and in the latest
1.8.x as well, and was only fixed in master, as part of the move to
postgresql.

The bug prevents you from adding a job, you get an immediate error in the
UI.

Question: Can anyone attest to Content Invalidation working in a fresh
install of TC 1.7 or 1.8 ?

Another question: Given that the problem probably does not exist in
'master', do we care about fixing it in the 1.8.x train (Or a following
train, non-postgresql) ?
If so, I will issue a JIRA.

The details:

I believe there is an inconsistency between the code that creates the TO DB
during a clean install, and the code that depends on it.

Creating the TO DB is done by traffic_ops/app/db/create_tables.sql. In the
mysql version (1.7 and 1.8), it creates the 'job_agent' table and the
'job_status' table with table options AUTO_INCREMENT=2
and AUTO_INCREMENT=5, respectively:

*CREATE TABLE `job_agent`* (
  `id` int(11) NOT NULL AUTO_INCREMENT,
...
) ENGINE=InnoDB *AUTO_INCREMENT=2* DEFAULT CHARSET=latin1;

*CREATE TABLE `job_status`* (
  `id` int(11) NOT NULL AUTO_INCREMENT,
...
) ENGINE=InnoDB *AUTO_INCREMENT=5* DEFAULT CHARSET=latin1;

This means that when the script that populates these tables
(traffic_ops/app/db/migrations/20151208000000_add_job_status.sql) is run,
it creates entries with an id that begins with 2 and 5, respectively.

However, the code that attempts to add an entry to the 'job' table, assumes
that the id numbers in both tables, begins at 1.

>From traffic_ops/app/lib/UI/Job.pm:

sub newjob {
...
my $*status = 1*;
...
my $insert = $self->db->resultset('Job')->create(
{
*agent               => 1,*
object_type         => $object_type,
object_name         => $object_name,
entered_time        => $entered_time,
keyword             => $keyword,
parameters          => $parameters,
asset_url           => $org_server_fqdn,
asset_type          => $asset_type,
*status              => $status*,
job_user            => $user,
start_time          => $start_time_gmt,
job_deliveryservice => $ds->id,
}

As you can see, both the 'agent' and the 'status' fields are set to a
hard-coded value of 1, which cannot exist in tables that are set to have an
auto-incremented value of 'id' that begins with 2 or 5.

When looking at the traffic_ops/app/db/create_tables.sql script in
'master', which is now adapted to postgresql, it seems that it is totally
different code, but the inconsistency is now gone, because the numbering
starts at 1:

CREATE TABLE job_agent (
    id bigint NOT NULL,
    name text,
    description text,
    active integer DEFAULT 0 NOT NULL,
    last_updated timestamp with time zone DEFAULT now()
);

CREATE SEQUENCE job_agent_id_seq
*    START WITH 1*
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE job_agent_id_seq OWNED BY job_agent.id;

Regards, Oren.

On Thu, Jan 26, 2017 at 5:36 PM, Dave Neuman <[email protected]> wrote:

> Good to hear.  It's too bad that we have some routes requiring a specific
> ID from the database, when you come across those can you open a Jira issue
> so we know they need to be fixed?
> Thanks,
> Dave
>
> On Thu, Jan 26, 2017 at 5:17 AM, Naama Shoresh <[email protected]> wrote:
>
> > Hi Dave,
> >
> > This is the next error I got. Already fixed the DB accordingly.
> > Traffic Ops fatal error occurred while processing your request.
> > ------------------------------
> > Error at line 146 ( my $insert = $self->db->resultset('Job')->create()
> > ------------------------------
> > DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st
> > execute failed: Cannot add or update a child row: a foreign key
> constraint
> > fails (`traffic_ops_db`.`job`, CONSTRAINT `fk_job_status_id1` FOREIGN KEY
> > (`status`) REFERENCES `job_status` (`id`) ON DELETE NO ACTION ON UPDATE
> NO
> > ACTION) [for Statement "INSERT INTO job ( agent, asset_type, asset_url,
> > entered_time, job_deliveryservice, job_user, keyword, object_name,
> > object_type, parameters, start_time, status) VALUES ( ?, ?, ?, ?, ?, ?,
> ?,
> > ?, ?, ?, ?, ? )" with ParamValues: 0=1, 1="file", 2="
> > http://images1.ynet.co.il/foo/.*";, 3='2017-01-24 09:16:46', 4='311',
> > 5='59', 6="PURGE", 7=undef, 8=undef, 9="TTL:48h", 10='2017-01-24
> 09:05:26',
> > 11=1] at /opt/traffic_ops/app/lib/UI/Job.pm line 146
> >
> > What happened was that TO post_install script failed the first time I ran
> > it, and I ran it again. As a result, the records in job_status &
> job_agent
> > were added, removed, and added again, and their ids were offset. I guess
> > there are several routes in the code counting on a specific record id in
> > the DB.
> >
> > After fixing the DB, there was another error, the parameter snapshot_dir
> > was missing from regex_revalidate.config profile.
> > When I added the missing parameter,"Invalidate content" worked as
> expected.
> >
> > Thanks a lot for your help.
> >
> > Naama
> >
> >
> >
> > On Tue, Jan 24, 2017 at 5:16 PM, Dave Neuman <[email protected]> wrote:
> >
> > > can you send the new error?
> > >
> > >
> > > On Tue, Jan 24, 2017 at 2:18 AM, Naama Shoresh <[email protected]>
> wrote:
> > >
> > > > actually, I didn't look closely enough. It is now complaining about a
> > > > different table.
> > > > Thanks!
> > > >
> > > > On Tue, Jan 24, 2017 at 11:13 AM, Naama Shoresh <[email protected]>
> > > wrote:
> > > >
> > > > > I checked. The exact same error.
> > > > > It seemed weird to me, too.
> > > > >
> > > > > On Tue, Jan 24, 2017 at 11:12 AM, Oren Shemesh <[email protected]>
> > > wrote:
> > > > >
> > > > >> [1:1]
> > > > >>
> > > > >> R U sure that the exact same error repeats ?
> > > > >> It seems that now the constraint is satisfied, so if there is an
> > > error,
> > > > it
> > > > >> will be a different one ...
> > > > >>
> > > > >> (Not that I know SQL, but my sqenglish is good enough :-)
> > > > >>
> > > > >> On Tue, Jan 24, 2017 at 10:57 AM, Naama Shoresh <[email protected]
> >
> > > > wrote:
> > > > >>
> > > > >> > I tried adding a row to the job_agent table:
> > > > >> > mysql> select * from job_agent;
> > > > >> > +----+-------+----------------------------+--------+--------
> > > > >> -------------+
> > > > >> > | id | name  | description                | active |
> last_updated
> > > > >>   |
> > > > >> > +----+-------+----------------------------+--------+--------
> > > > >> -------------+
> > > > >> > |  1 | PURGE | Description of Purge Agent |      1 | 2017-01-24
> > > > >> 08:50:22 |
> > > > >> > |  2 | dummy | Description of Purge Agent |      1 | 2017-01-01
> > > > >> 10:28:43 |
> > > > >> > +----+-------+----------------------------+--------+--------
> > > > >> -------------+
> > > > >> > 2 rows in set (0.00 sec)
> > > > >> >
> > > > >> > But the error repeats.
> > > > >> > Any ideas?
> > > > >> >
> > > > >> > Thanks,
> > > > >> > Naama
> > > > >> >
> > > > >> > On Tue, Jan 24, 2017 at 12:17 AM, Dave Neuman <
> [email protected]>
> > > > >> wrote:
> > > > >> >
> > > > >> > > The error message says that you are trying to submit a job
> with
> > an
> > > > >> Agent
> > > > >> > id
> > > > >> > > of 1, which doesn't exist.
> > > > >> > > ```INSERT INTO job ( agent, asset_type, asset_url,
> > > > >> > > entered_time, job_deliveryservice, job_user, keyword,
> > object_name,
> > > > >> > > object_type, parameters, start_time, status) VALUES ( ?, ?, ?,
> > ?,
> > > ?,
> > > > >> ?,
> > > > >> > ?,
> > > > >> > > ?, ?, ?, ?, ? )" with ParamValues: 0=1, 1="file", 2="
> > > > >> > > http://s2.ipcamlive.com/.*/7464537492280autoresize.jpg";,
> > > > >> 3='2017-01-22
> > > > >> > > 13:04:53', 4='312', 5='59', 6="PURGE", 7=undef, 8=undef,
> > > > 9="TTL:48h",
> > > > >> > > 10='2017-01-22 13:05:22', 11=1] at
> /opt/traffic_ops/app/lib/UI/
> > > > Job.pm
> > > > >> > line
> > > > >> > > 146```
> > > > >> > > I am not sure how that agent is supposed to be created, this
> > might
> > > > be
> > > > >> a
> > > > >> > > bug.  Can you please submit an issue?
> > > > >> > > The workaround would be to create an agent with id = 1.
> > > > >> > >
> > > > >> > > Thanks,
> > > > >> > > Dave
> > > > >> > >
> > > > >> > > On Mon, Jan 23, 2017 at 3:05 PM, Naama Shoresh <
> > [email protected]>
> > > > >> wrote:
> > > > >> > >
> > > > >> > > > No, only this one:
> > > > >> > > > (2,'dummy','Description of Purge Agent',1,'2017-01-04
> > 13:16:30')
> > > > >> > > >
> > > > >> > > > What does that mean?
> > > > >> > > >
> > > > >> > > > Thanks,
> > > > >> > > > Naama
> > > > >> > > >
> > > > >> > > > On Mon, Jan 23, 2017 at 5:27 PM, Dave Neuman <
> > [email protected]
> > > >
> > > > >> > wrote:
> > > > >> > > >
> > > > >> > > > > Do you have an agent in your job_agent table with the id
> of
> > 1?
> > > > >> > > > >
> > > > >> > > > >
> > > > >> > > > > On Mon, Jan 23, 2017 at 5:42 AM, Naama Shoresh <
> > > > [email protected]>
> > > > >> > > wrote:
> > > > >> > > > >
> > > > >> > > > > > Hi,
> > > > >> > > > > >
> > > > >> > > > > > When trying to invalidate content from the TO UI, I get
> > the
> > > > >> > following
> > > > >> > > > > fatal
> > > > >> > > > > > error message:
> > > > >> > > > > >
> > > > >> > > > > > Traffic Ops fatal error occurred while processing your
> > > > request.
> > > > >> > > > > > ------------------------------
> > > > >> > > > > > Error at line 146 ( my $insert =
> > > $self->db->resultset('Job')->
> > > > >> > > create()
> > > > >> > > > > > ------------------------------
> > > > >> > > > > > DBIx::Class::Storage::DBI::_dbh_execute(): DBI
> Exception:
> > > > >> > > > DBD::mysql::st
> > > > >> > > > > > execute failed: Cannot add or update a child row: a
> > foreign
> > > > key
> > > > >> > > > > constraint
> > > > >> > > > > > fails (`traffic_ops_db`.`job`, CONSTRAINT
> > `fk_job_agent_id1`
> > > > >> > FOREIGN
> > > > >> > > > KEY
> > > > >> > > > > > (`agent`) REFERENCES `job_agent` (`id`) ON DELETE
> CASCADE
> > ON
> > > > >> UPDATE
> > > > >> > > NO
> > > > >> > > > > > ACTION) [for Statement "INSERT INTO job ( agent,
> > asset_type,
> > > > >> > > asset_url,
> > > > >> > > > > > entered_time, job_deliveryservice, job_user, keyword,
> > > > >> object_name,
> > > > >> > > > > > object_type, parameters, start_time, status) VALUES ( ?,
> > ?,
> > > ?,
> > > > >> ?,
> > > > >> > ?,
> > > > >> > > ?,
> > > > >> > > > > ?,
> > > > >> > > > > > ?, ?, ?, ?, ? )" with ParamValues: 0=1, 1="file", 2="
> > > > >> > > > > > http://s2.ipcamlive.com/.*/7464537492280autoresize.jpg
> ",
> > > > >> > > 3='2017-01-22
> > > > >> > > > > > 13:04:53', 4='312', 5='59', 6="PURGE", 7=undef, 8=undef,
> > > > >> > 9="TTL:48h",
> > > > >> > > > > > 10='2017-01-22 13:05:22', 11=1] at
> > > > /opt/traffic_ops/app/lib/UI/
> > > > >> > > Job.pm
> > > > >> > > > > line
> > > > >> > > > > > 146
> > > > >> > > > > >
> > > > >> > > > > > In traffic_ops log file I see this message:
> > > > >> > > > > > [2017-01-23 12:31:47,834] [ERROR]
> > > DBIx::Class::Storage::DBI::_
> > > > >> > > > > > dbh_execute():
> > > > >> > > > > > DBI Exception: DBD::mysql::st execute failed: Cannot add
> > or
> > > > >> update
> > > > >> > a
> > > > >> > > > > child
> > > > >> > > > > > row: a foreign key constraint fails
> > (`traffic_ops_db`.`job`,
> > > > >> > > CONSTRAINT
> > > > >> > > > > > `fk_job_agent_id1` FOREIGN KEY (`agent`) REFERENCES
> > > > `job_agent`
> > > > >> > > (`id`)
> > > > >> > > > ON
> > > > >> > > > > > DELETE CASCADE ON UPDATE NO ACTION) [for Statement
> "INSERT
> > > > INTO
> > > > >> > job (
> > > > >> > > > > > agent, asset_type, asset_url, entered_time,
> > > > job_deliveryservice,
> > > > >> > > > > job_user,
> > > > >> > > > > > keyword, object_name, object_type, parameters,
> start_time,
> > > > >> status)
> > > > >> > > > > VALUES (
> > > > >> > > > > > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" with ParamValues:
> > 0=1,
> > > > >> > > 1="file",
> > > > >> > > > > 2="
> > > > >> > > > > > http://images1.ynet.co.il/foo/.*";, 3='2017-01-23
> > 12:31:47',
> > > > >> > 4='311',
> > > > >> > > > > > 5='59', 6="PURGE", 7=undef, 8=undef, 9="TTL:48h",
> > > > 10='2017-01-23
> > > > >> > > > > 12:32:22',
> > > > >> > > > > > 11=1] at /opt/traffic_ops/app/lib/UI/Job.pm line 146
> > > > >> > > > > > [2017-01-23 12:31:47,835] [ERROR]
> > > > >> > > > > >
> > > > >> > > > > >
> > > > >> > > > > > Any idea why this happens?
> > > > >> > > > > >
> > > > >> > > > > > Thanks,
> > > > >> > > > > > Naama
> > > > >> > > > > >
> > > > >> > > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > > > --
> > > > >> > > > *Naama Shoresh*
> > > > >> > > > Qwilt | Work: +972-72-2221706 <+972%2072-222-1706> | Mobile:
> > > > >> +972-52-3401999 <+972%2052-340-1999> |
> > > > >> > > > [email protected]
> > > > >> > > >
> > > > >> > >
> > > > >> >
> > > > >> >
> > > > >> >
> > > > >> > --
> > > > >> > *Naama Shoresh*
> > > > >> > Qwilt | Work: +972-72-2221706 | Mobile: +972-52-3401999 |
> > > > >> > [email protected]
> > > > >> >
> > > > >>
> > > > >>
> > > > >>
> > > > >> --
> > > > >>
> > > > >> *Oren Shemesh*
> > > > >> Qwilt | Work: +972-72-2221637| Mobile: +972-50-2281168 |
> > > > [email protected]
> > > > >> <[email protected]>
> > > > >>
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > *Naama Shoresh*
> > > > > Qwilt | Work: +972-72-2221706 <+972%2072-222-1706> | Mobile:
> > > > > +972-52-3401999 <+972%2052-340-1999> | [email protected]
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > *Naama Shoresh*
> > > > Qwilt | Work: +972-72-2221706 | Mobile: +972-52-3401999 |
> > > > [email protected]
> > > >
> > >
> >
> >
> >
> > --
> > *Naama Shoresh*
> > Qwilt | Work: +972-72-2221706 | Mobile: +972-52-3401999 |
> > [email protected]
> >
>



-- 

*Oren Shemesh*
Qwilt | Work: +972-72-2221637| Mobile: +972-50-2281168 | [email protected]
<[email protected]>

Reply via email to