I have a lot of email templates stored in MySQL database. Inside i have a table which stores the MJML code of the template. Now I want to update a few lines of code in all of my emails. And I want to do it inside MySQL because it would be fast. (hundreds of emails)
The table "mymjmltemplates" has three columns:
id int(10), email_id int(10), custom_mjml (longtext)
My query goes like this:
UPDATE mymjmltemplates
SET custom_mjml = REPLACE(
custom_mjml,
'<mj-column>
<mj-text>Content 1
</mj-text>
</mj-column>',
'<mj-column>
<mj-text>Content 2
</mj-text>
</mj-column>')
WHERE email_id = 1035;
Usually this query updates strings. But when I run it, it breaks somewhere and asks during the query:
Display all 2709 possibilities? (y or n)
Without entering something it finishes the query, and changes nothing.
So, it is escaping somehow inside the strings??? I don't find a connection.
This is how it looks like on my cli:
MariaDB [mydatabase]> UPDATE mymjmltemplates
-> SET custom_mjml = REPLACE(custom_mjml,
->
Display all 2709 possibilities? (y or n)
-> >
->
Display all 2709 possibilities? (y or n)
-> tent 1
->
Display all 2709 possibilities? (y or n)
? categories.description leads.state
ABS categories.id leads.storecode
ACOS categories.is_published leads.timezone
ACTION categories.modified_by leads.title
ADD categories.modified_by_user leads.treueaktion_gutschein_ein
ADDDATE categories.title leads.twitter
ADDTIME category_id leads.warenkorbwert_abbruch
AES_DECRYPT channel leads.website
AES_ENCRYPT channel_id leads.weitere_informationen
AFTER channel_url_trackables leads.zahlungsart
AGAINST channel_url_trackables.channel leads.zipcode
AGGREGATE channel_url_trackables.channel_id line_count
ALGORITHM channel_url_trackables.hits linkedin
ALL channel_url_trackables.redirect_id list_id
ALTER channel_url_trackables.unique_hits lists
ANALYZE charset load data from
AND checked_out locale
ANY checked_out_by lock tables
AREA checked_out_by_user log_counts_processed
AS city log_id
ASBINARY clear manually_added
ASC click_count manually_removed
ASCII click_details markenbotschafter
ASENSITIVE client_id mautic
ASIN client_info max_attempts
ASTEXT code media_id
ASWKB color media_path
ASWKT column_is_not_created merged_id
ATAN column_name message
ATAN2 column_type message_channels
AUTO_INCREMENT column_value message_channels.channel
AVG columns message_channels.channel_id
AVG_ROW_LENGTH comments message_channels.id
BACKUP companies message_channels.is_enabled
BDB companies.checked_out message_channels.message_id
BEFORE companies.checked_out_by message_channels.properties
BEGIN companies.checked_out_by_user message_id
BENCHMARK companies.companyaddress1 message_queue
BERKELEYDB companies.companyaddress2 message_queue.attempts
BETWEEN companies.companyannual_revenue message_queue.channel
BIGINT companies.companycity message_queue.channel_id
BIN companies.companycountry message_queue.date_published
BINARY companies.companydescription message_queue.date_sent
BINLOG companies.companyemail message_queue.event_id
BIT companies.companyfax message_queue.id
BIT_AND companies.companyindustry message_queue.last_attempt
BIT_COUNT companies.companyname message_queue.lead_id
BIT_LENGTH companies.companynumber_of_employees message_queue.max_attempts
BIT_OR companies.companyphone message_queue.options
BIT_XOR companies.companystate message_queue.priority
BLOB companies.companywebsite message_queue.scheduled_date
BOOL companies.companyzipcode message_queue.status
BOOLEAN companies.created_by message_queue.success
BOTH companies.created_by_user messages
BTREE companies.date_added messages.category_id
BY companies.date_modified messages.checked_out
BYTE companies.id messages.checked_out_by
CACHE companies.is_published messages.checked_out_by_user
CALL companies.modified_by messages.created_by
CASCADE companies.modified_by_user messages.created_by_user
CASCADED companies.owner_id messages.date_added
CASE companies.score messages.date_modified
CAST companies.social_cache messages.description
CEIL companies_leads messages.id
CEILING companies_leads.company_id messages.is_published
CENTROID companies_leads.date_added messages.modified_by
CHAIN companies_leads.is_primary messages.modified_by_user
CHANGE companies_leads.lead_id messages.name
CHANGED company messages.publish_down
CHAR company_id messages.publish_up
CHARACTER companyaddress1 meta_description
CHARACTER_LENGTH companyaddress2 metadata
CHARSET companyannual_revenue migrations
CHAR_LENGTH companycity migrations.executed_at
CHECK companycountry migrations.version
CHECKSUM companydescription mime
CIPHER companyemail mobile
CLIENT companyfax mobileSettings
CLOSE companyindustry modified_at
COALESCE companyname modified_by
CODE companynumber_of_employees modified_by_user
COERCIBILITY companyphone monitor_id
COLLATE companystate monitor_post_count
COLLATION companywebsite monitor_post_count.id
COLUMN companyzipcode monitor_post_count.monitor_id
COLUMNS conditions monitor_post_count.post_count
COMMENT connect monitor_post_count.post_date
COMMIT contact_id monitoring
COMMITTED contact_merge_records monitoring.category_id
COMPACT contact_merge_records.contact_id monitoring.checked_out
COMPRESS contact_merge_records.date_added monitoring.checked_out_by
COMPRESSED contact_merge_records.id monitoring.checked_out_by_user
CONCAT contact_merge_records.merged_id monitoring.created_by
CONCAT_WS contact_merge_records.name monitoring.created_by_user
CONCURRENT container_attr monitoring.date_added
CONDITION content monitoring.date_modified
CONNECTION copy_id monitoring.description
CONNECTION_ID country monitoring.id
-> >',
'>
Display all 2709 possibilities? (y or n)
'> >
'>
Display all 2709 possibilities? (y or n)
'> tent 2
'>
Display all 2709 possibilities? (y or n)
? categories.description leads.state
ABS categories.id leads.storecode
ACOS categories.is_published leads.timezone
ACTION categories.modified_by leads.title
ADD categories.modified_by_user leads.treueaktion_gutschein_ein
ADDDATE categories.title leads.twitter
ADDTIME category_id leads.warenkorbwert_abbruch
AES_DECRYPT channel leads.website
AES_ENCRYPT channel_id leads.weitere_informationen
AFTER channel_url_trackables leads.zahlungsart
AGAINST channel_url_trackables.channel leads.zipcode
AGGREGATE channel_url_trackables.channel_id line_count
ALGORITHM channel_url_trackables.hits linkedin
ALL channel_url_trackables.redirect_id list_id
ALTER channel_url_trackables.unique_hits lists
ANALYZE charset load data from
AND checked_out locale
ANY checked_out_by lock tables
AREA checked_out_by_user log_counts_processed
AS city log_id
ASBINARY clear manually_added
ASC click_count manually_removed
ASCII click_details markenbotschafter
ASENSITIVE client_id mautic
ASIN client_info max_attempts
ASTEXT code media_id
ASWKB color media_path
ASWKT column_is_not_created merged_id
ATAN column_name message
ATAN2 column_type message_channels
AUTO_INCREMENT column_value message_channels.channel
AVG columns message_channels.channel_id
AVG_ROW_LENGTH comments message_channels.id
BACKUP companies message_channels.is_enabled
BDB companies.checked_out message_channels.message_id
BEFORE companies.checked_out_by message_channels.properties
BEGIN companies.checked_out_by_user message_id
BENCHMARK companies.companyaddress1 message_queue
BERKELEYDB companies.companyaddress2 message_queue.attempts
BETWEEN companies.companyannual_revenue message_queue.channel
BIGINT companies.companycity message_queue.channel_id
BIN companies.companycountry message_queue.date_published
BINARY companies.companydescription message_queue.date_sent
BINLOG companies.companyemail message_queue.event_id
BIT companies.companyfax message_queue.id
BIT_AND companies.companyindustry message_queue.last_attempt
BIT_COUNT companies.companyname message_queue.lead_id
BIT_LENGTH companies.companynumber_of_employees message_queue.max_attempts
BIT_OR companies.companyphone message_queue.options
BIT_XOR companies.companystate message_queue.priority
BLOB companies.companywebsite message_queue.scheduled_date
BOOL companies.companyzipcode message_queue.status
BOOLEAN companies.created_by message_queue.success
BOTH companies.created_by_user messages
BTREE companies.date_added messages.category_id
BY companies.date_modified messages.checked_out
BYTE companies.id messages.checked_out_by
CACHE companies.is_published messages.checked_out_by_user
CALL companies.modified_by messages.created_by
CASCADE companies.modified_by_user messages.created_by_user
CASCADED companies.owner_id messages.date_added
CASE companies.score messages.date_modified
CAST companies.social_cache messages.description
CEIL companies_leads messages.id
CEILING companies_leads.company_id messages.is_published
CENTROID companies_leads.date_added messages.modified_by
CHAIN companies_leads.is_primary messages.modified_by_user
CHANGE companies_leads.lead_id messages.name
CHANGED company messages.publish_down
CHAR company_id messages.publish_up
CHARACTER companyaddress1 meta_description
CHARACTER_LENGTH companyaddress2 metadata
CHARSET companyannual_revenue migrations
CHAR_LENGTH companycity migrations.executed_at
CHECK companycountry migrations.version
CHECKSUM companydescription mime
CIPHER companyemail mobile
CLIENT companyfax mobileSettings
CLOSE companyindustry modified_at
COALESCE companyname modified_by
CODE companynumber_of_employees modified_by_user
COERCIBILITY companyphone monitor_id
COLLATE companystate monitor_post_count
COLLATION companywebsite monitor_post_count.id
COLUMN companyzipcode monitor_post_count.monitor_id
COLUMNS conditions monitor_post_count.post_count
COMMENT connect monitor_post_count.post_date
COMMIT contact_id monitoring
COMMITTED contact_merge_records monitoring.category_id
COMPACT contact_merge_records.contact_id monitoring.checked_out
COMPRESS contact_merge_records.date_added monitoring.checked_out_by
COMPRESSED contact_merge_records.id monitoring.checked_out_by_user
CONCAT contact_merge_records.merged_id monitoring.created_by
CONCAT_WS contact_merge_records.name monitoring.created_by_user
CONCURRENT container_attr monitoring.date_added
CONDITION content monitoring.date_modified
CONNECTION copy_id monitoring.description
CONNECTION_ID country monitoring.id
'> >')
-> WHERE email_id = 1035;
To me, it looks like some combinations of characters lead to escaping and then some weird things happen. What am I doing wrong? Why is this happening? I appreciate every help/idea