Update entire body content using MySQL query

[Dario]

Hi community,

I would need to update BODYCONTENT table using update query. Specifically I would need to replace the entire body field value.

I had to change in some pages one plugin to another and create headings. So, I exported the values from body field and I modified using bash script. Now I have a problem trying to upload the content.

When I try the following update query I get the error message: 

/bin/mysql: Argument list too long

update BODYCONTENT from body = '[all xhtml code]' where contentid = XXXXX;

Do you know how to do it? I am using Confluence 7.19.9 and MySQL 8. 

Thank you in advance.

Kind regards,

Dario

[Dario]

Good morning Andrii,

Thank you for your response.

When I use the SQL query, the part 'DATA_HERE' is too long so I get the error message /bin/mysql: Argument list too long

My question is there would be another way to do it? If there is not, how could I to exchange one plugin to another in several pages automatically?

i.e:

The idea is modify this part of text:

<ac:macro ac:name="expand and print">
<ac:parameter ac:name="customLevel">Title 2</ac:parameter>
<ac:parameter ac:name="customTitle">1. General</ac:parameter>
<ac:rich-text-body>

For this:

<ac:macro ac:name="panel">
<ac:parameter ac:name="customLevel">Title 2</ac:parameter>
<ac:rich-text-body>
<h2>1. General<h2>

Or

<h2>1. General<h2>
<ac:macro ac:name="panel">
<ac:parameter ac:name="customLevel">Title 2</ac:parameter>
<ac:rich-text-body>

The main problem is the header should have the same level and the same text. So one colleague created a bash script to to these changes and it worked but now I would like to import again the pages, but I am unable to.

Thank you in advance.

[Andrii Maliuta]

@Dario ,

Usually, it is not a good option to modify the Database directly, as a lot of things and services are connected and synchronized on data levels.

If you need to change (find/replace) bodies of pages, it can be better to use REST API or JAVA API (scripts) to do this.

As for the SQL, seems like it is incorrect. e.g. to update an entry by ID:

UPDATE Confluence.BODYCONTENTS set body = 'DATA_HERE' where ID = X;

[Nic Brough -Adaptavist-]

If you're going to do this (as Andrii says, it's a bad idea), make sure that you stop Confluence, take a full backup of the database, run your SQL, restart Confluence, and run a full re-index before letting your people back in.