title: Why can't MySQL trigger variables handle special characters?
body: <p>I've got a table where all fields are set to 'utf8_general_ci'. There's a trigger on the table which captures data changes and inserts them into a 'log' table.</p>
  
  <p>The trigger has a variable (var_row_data) which stores the updated row's old data, and that variable is then inserted into the log table. However, somehow MySQL throws an exception each time a special character (e.g ł and ń) is part of the data stored in the trigger variable. </p>
  
  <p>Am I missing something about trigger variables and their encoding? Connections are using utf8, tables are utf8, webpages are utf8. In short; MySQL handles the special characters just fine but once the trigger tries to store the special characters it all goes wrong.</p>
  
  <p>Does anyone have any experience with this? There's apparently something going on with the trigger variables, but I just don't understand how to get around it.</p>
  
  <p>For the record, here's the trigger:</p>
  
  <pre><code>DROP TRIGGER IF EXISTS `SYM_ON_U_FOR_VRBLS_WBPRD`//
  CREATE TRIGGER `SYM_ON_U_FOR_VRBLS_WBPRD` AFTER UPDATE ON `variables`
   FOR EACH ROW begin
                                    DECLARE var_row_data mediumtext;
                                    DECLARE var_old_data mediumtext;
                                    if 1=1 and @sync_triggers_disabled is null then
                                      set var_row_data = concat(
            if(new.`id` is null,'',concat('"',cast(new.`id` as char),'"')),',',
            if(new.`variable` is null,'',concat('"',replace(replace(new.`variable`,'\','\\'),'"','\"'),'"')),',',
            if(new.`value` is null,'',concat('"',replace(replace(new.`value`,'\','\\'),'"','\"'),'"')));
                                      set var_old_data = concat(
            if(old.`id` is null,'',concat('"',cast(old.`id` as char),'"')),',',
            if(old.`variable` is null,'',concat('"',replace(replace(old.`variable`,'\','\\'),'"','\"'),'"')),',',
            if(old.`value` is null,'',concat('"',replace(replace(old.`value`,'\','\\'),'"','\"'),'"')));
                                      if 1=1 then 
                                          insert into trackerdatabase_production_synch.sym_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)
                                          values(
                                            'variables',
                                            'U',
                                            18,
                                            concat(
            if(old.`id` is null,'',concat('"',cast(old.`id` as char),'"'))
                                             ),
                                            var_row_data,
                                            var_old_data,
                                            'default', trackerdatabase_production_synch.sym_transaction_id(), @sync_node_disabled,
                                            null,
                                            CURRENT_TIMESTAMP
                                          );
                                      end if;
                                    end if;
                                  end
  //
  </code></pre>