In my opinion, the job agent id should have been passed to the newjob() subroutine rather than depending on a hard-coded value...actually, it looks like that was the intent but the $agent variable never got used.
https://github.com/apache/incubator-trafficcontrol/blob/master/traffic_ops/app/lib/UI/Job.pm#L42 Also, I don't think the concept of jobs, job agents, etc was ever fully fleshed out (somebody correct me if i'm wrong), therefore, it would be my opinion that we eventually kill these tables: - job_agent - job_status - job_result this would eliminate the job_agent FK from the job table and then this problem of potentially using an job agent ID that doesn't exist goes bye bye... also, fyi, i believe the job table really only contains "purge" aka "invalidate content" jobs...(defined by keyword=purge). Jeremy On Tue, Jan 31, 2017 at 12:42 PM, Dave Neuman <[email protected]> wrote: > Thanks for the investigation. Sounds like there is definitely a bug > there. I would vote don't worry about fixing the issue in 1.8 since it is > fixed in master. We should, however, make a release note or something > explaining the issue and the work around. > > --Dave > > On Tue, Jan 31, 2017 at 12:32 PM, Oren Shemesh <[email protected]> wrote: > > > 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]> > > >
![http://s2.ipcamlive.com/.*/7464537492280autoresize.jpg"](http://s2.ipcamlive.com/.*/7464537492280autoresize.jpg"){kind=link}