What's the mysql equivelant

[Alicia Pena]

This question is in reference to Atlassian Documentation: Update the corrupted Application Links

I need to clean up our database using this reference:  

https://confluence.atlassian.com/jirakb/update-the-corrupted-application-links-792635088.html

The search string works but the update string does not:

mysql> select * from propertyentry pe, propertystring ps where pe.id = ps.id and pe.property_key = 'applinks.global.application.ids';

mysql> update propertystring set propertyvalue = E'#java.util.List\n' where id = '27160';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''#java.util.List\n' where id = '27160'' at line 1

[Jon Bevan _Adaptavist_ · Accepted solution]

The uppercase E in front of the quoted string indicates that the \n should be escaped - I think its a Postgres-specific thing.

In the MySQL version of SQL you should remove that uppercase E so you get the following query: UPDATE propertystring SET propertyvalue = "#java.util.List\n" WHERE id = '27160';

[Alicia Pena]

Okay, thank you!  

I set up a test database and imported the sql data from JIRA to try it out.  It didn't like the double quotes either but you are correct that removing the E in front of the '#java did the trick.

mysql> UPDATE propertystring SET propertyvalue = “#java.util.List\n” where id = '27160';
-> ;
ERROR 1054 (42S22): Unknown column '“' in 'field list'

mysql> UPDATE propertystring SET propertyvalue = '#java.util.List\n' where id = '27160';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

I'll have to wait until I can schedule down time again before I can implement it in production.  But at least now I have the proper commands to do so.

Thanks for you help!