MYSQL DB Agent generating extra blank lines.

Discussion created by PatrickHiggins607207 on Jan 12, 2018
Latest reply on Jan 29, 2018 by PatrickHiggins607207

Like • Show 0 Likes0
Comment • 10

Running AE 11.2.2 with MYSQL DB Agent v1.0
20180110/111828.963 - U02000000 Started program Agent 'MYSQL', version '11.2.2+build.622', changelist '7627775'.
20180110/111828.979 - U02000232 Build Date: '2016-06-20', '21:27:52'
20180110/111828.994 - U02000037 Started Agent with INI file 'C:\uc4\Agents\sql\bin\ucxjsqlx_MYSQL.ini'.
20180110/111829.010 - U02000090 Java Runtime Environment version: '1.8.0_131'
20180110/111829.010 - U02000091 Java Runtime Environment vendor: 'Oracle Corporation'
20180110/111829.088 - U02000192 Operating system: 'Windows Server 2008 R2', version: '6.1'.
20180110/111829.088 - U02000193 JVM Architecture: 'amd64'
20180110/111829.088 - U02000110 Maximum Heap Memory: '228' MB

We are trying to use the DB Agent to extract data from a MYSQL DB and pass that to an external user in a csv formatted file.

We are consistently getting a blank / null line after every select statement and another blank line at the end of the job.

When running the selects from a mysql command line, these extra lines are not generated.

Has anyone else seen this? Is there a way to stop it?

Outcomes

Peter WIRFS Jan 12, 2018 5:34 PM

We don't run the MYSQL agent so I can't test this. But have you considered working around the problem by running your SELECT from a VARA.SEC_SQL object instead?

Actions

Dat_Nguyen-Tat_9196 Jan 15, 2018 3:30 AM

Do you have any problem with some example query like select * from yourtable ?
Is there any error show up?

Actions

Wolfgang_Brueckler_1288 Jan 16, 2018 11:07 AM

Hi Patrick_Higgins_6715

just stumbled over this in documentation:

newline=

Output of blank lines after tables that are used in reports.

Allowed values: "0" and "1" (default value)

"0" - The agent does not insert a blank line after a table.
"1" - A blank line is inserted after each table.

https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11.2/All%20Guides/help.htm#ucackg.htm

would be worth a check :-)

cheers, WOlfgang

Actions

PatrickHiggins607207 Jan 17, 2018 4:52 PM

Wolfgang,
Thanks for the suggestion. I have to get someone else to change the INI and bounce the agent.

Is there a way to make that work using the Connection String or Connection Properties in the Job definition? -- that would allow different behavior for different jobs without restarting the agent
I tried "newline" and "0"in Connection String and that failed the 'Test connection'
I tried "newline" and "0"in Connection Properties, that passed 'Test connection' but did not change the behavior.

I tried various combinations of values in the addition parameter section of the Connection object without change in the job behavior. either it does not pass the 'test' or it does nothing.

Actions

Wolfgang_Brueckler_1288 Jan 17, 2018 7:14 PM

I am afraid thats possible in INI file only.

so if you need both you need to start 2 Agents (or Agent instances) to get your desired choice :-)

cheers, Wolfgang

Actions

Wolfgang_Brueckler_1288 Jan 17, 2018 7:18 PM

Just a silly Idea - you could use PREP_PROCESS_REPORT in Postscript and add the empty line manually afterwards :-)

Actions

PatrickHiggins607207 Jan 22, 2018 9:56 PM

Added the value of NEWLINE=0 in the configuration file for the agent, restarted the agent and that eliminated the blank line after each select, but still has the blank line at the end. The user of the file is ok with that behavior. Very limited use of the MYSQL agent at this time, so this did not adversely affect other jobs.

A change in a POST process or the PREP_PROCESS_REPORT could eliminate that one as well if needed.

Actions

PatrickHiggins607207 Jan 22, 2018 9:57 PM

Wolfgang,
Do you have any information on what values and behaviors ARE available if using the additional parameter section of the Connection object? I could not find anything in the doc.

Actions

Peter WIRFS Jan 22, 2018 10:43 PM

It looks to me like the "Additional Parameters" section of a database connection object are for database-specific settings, so the answer would depend upon what type of database you are connecting to. If you are still curious for your case, you'd probably want to contact your local DBA?

https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucactb.htm%3FTocPath%3DUser%2520Guide%7CObjects%7CAlphabetical%2520Listing%7CConnection%7C_____6

Actions

PatrickHiggins607207 Jan 29, 2018 5:09 PM

Pete,

As in the title of this post, it is a MYSQL DB.

I have tried working with our MYSQL DBA. We tried various settings that change the MYSQL session behavior when invoked from a command line, but they either fail completely or make no difference in behavior when used in a connection object and job from Automic.
Obviously there is a specific syntax required, that is what I need from Automic or someone that has actually used these options with MYSQL.

Pat

Actions

10 Replies

Peter WIRFS Jan 12, 2018 5:34 PM
We don't run the MYSQL agent so I can't test this. But have you considered working around the problem by running your SELECT from a VARA.SEC_SQL object instead?
Like • Show 0 Likes0
Actions
Dat_Nguyen-Tat_9196 Jan 15, 2018 3:30 AM
Do you have any problem with some example query like select * from yourtable ?
Is there any error show up?
Like • Show 0 Likes0
Actions
Wolfgang_Brueckler_1288 Jan 16, 2018 11:07 AM
Hi Patrick_Higgins_6715

just stumbled over this in documentation:

newline=
Output of blank lines after tables that are used in reports.

Allowed values: "0" and "1" (default value)

"0" - The agent does not insert a blank line after a table.
"1" - A blank line is inserted after each table.
https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11.2/All%20Guides/help.htm#ucackg.htm

would be worth a check :-)

cheers, WOlfgang
Like • Show 1 Like1
Actions
PatrickHiggins607207 Jan 17, 2018 4:52 PM
Wolfgang,
Thanks for the suggestion. I have to get someone else to change the INI and bounce the agent.

Is there a way to make that work using the Connection String or Connection Properties in the Job definition? -- that would allow different behavior for different jobs without restarting the agent
I tried "newline" and "0"in Connection String and that failed the 'Test connection'
I tried "newline" and "0"in Connection Properties, that passed 'Test connection' but did not change the behavior.

I tried various combinations of values in the addition parameter section of the Connection object without change in the job behavior. either it does not pass the 'test' or it does nothing.
Like • Show 0 Likes0
Actions
Wolfgang_Brueckler_1288 Jan 17, 2018 7:14 PM
I am afraid thats possible in INI file only.

so if you need both you need to start 2 Agents (or Agent instances) to get your desired choice :-)

cheers, Wolfgang
Like • Show 0 Likes0
Actions
Wolfgang_Brueckler_1288 Jan 17, 2018 7:18 PM
Just a silly Idea - you could use PREP_PROCESS_REPORT in Postscript and add the empty line manually afterwards :-)
Like • Show 0 Likes0
Actions
PatrickHiggins607207 Jan 22, 2018 9:56 PM
Added the value of NEWLINE=0 in the configuration file for the agent, restarted the agent and that eliminated the blank line after each select, but still has the blank line at the end. The user of the file is ok with that behavior. Very limited use of the MYSQL agent at this time, so this did not adversely affect other jobs.

A change in a POST process or the PREP_PROCESS_REPORT could eliminate that one as well if needed.
Like • Show 0 Likes0
Actions
PatrickHiggins607207 Jan 22, 2018 9:57 PM
Wolfgang,
Do you have any information on what values and behaviors ARE available if using the additional parameter section of the Connection object? I could not find anything in the doc.
Like • Show 0 Likes0
Actions
Peter WIRFS Jan 22, 2018 10:43 PM
It looks to me like the "Additional Parameters" section of a database connection object are for database-specific settings, so the answer would depend upon what type of database you are connecting to. If you are still curious for your case, you'd probably want to contact your local DBA?

https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucactb.htm%3FTocPath%3DUser%2520Guide%7CObjects%7CAlphabetical%2520Listing%7CConnection%7C_____6
Like • Show 0 Likes0
Actions
PatrickHiggins607207 Jan 29, 2018 5:09 PM
Pete,

As in the title of this post, it is a MYSQL DB.

I have tried working with our MYSQL DBA. We tried various settings that change the MYSQL session behavior when invoked from a command line, but they either fail completely or make no difference in behavior when used in a connection object and job from Automic.
Obviously there is a specific syntax required, that is what I need from Automic or someone that has actually used these options with MYSQL.

Pat
Like • Show 0 Likes0
Actions

MYSQL DB Agent generating extra blank lines.

Outcomes

Related Content

Recommended Content