Mysql – String templating technique

database-designMySQL

I am working on a fitness app that is going to deliver notifications to the user. I intend using MySQL events to populate the necessary tables, and then using an Ajax timer or Websockets or whatnot to deliver them to the User FaceBook style.

The problem is that I am going to have a whole class of notifications eg. "Spongebob you have not taken a class in 2 weeks, stop slacking", and "You are xyz% away from reaching your goal weight, don't stop now!" etc etc.

I was thinking of a structure like this:

**Notification**
--Id
--Message

**UserNotification**
--Id
--UserId
--NotificationId
--ProcessedMessage
--Seen?

My question is, so I will have placeholders in the Notification message eg. "{{name}} you have reached your goal weight! Congratulations", but these placeholders will be different depending on the message.

I was thinking of "compiling" (via string replace) the messages with the MySQL event when I have all the relevant data on hand e.g. I will create an event for getting users who have not done any exercise in 3 weeks and so on, and then saving the value in UserNotification. This lets me update the message with time, whilst keeping history intact, but I lose the ability to fix spelling errors etc. in one place only and having it automatically propagating.

If I don't compile the message in the MySQL event, then I will have to do a custom columns solution in UserNotification so I can generate the message later.

Is there a better way to doing this?

Best Answer

Custom columns {{shudder}}.

There's not One True Way® that comes to mind, but what about one or more rows in the new UserNotificationMetadata table, which has a MetaKey VARCHAR(x) not null (with a small but practical "x") and primary key of (NotificationId,MetaKey) and a MetaValue TINYTEXT not null (or a comparably suitable data type)?

This is one of the relatively few cases where a sort of EAV-style structure is arguably not a case of urDoinItWrong™ since the data, here, isn't part of your core schema and there's (again, arguably) not a need to be able to impose constraints on the data... a number or date stored here is just a string of characters that's going into a message.

For each (yes, that is also a little bit procedural-sounding) row matching the UserNotification, take CONCAT('{{',MetaKey,'}}') and use REPLACE() to substitute it with MetaValue in the message.

I would also suggest that 'seen' should be a timestamp, rather than a boolean.

Related Solutions

Mysql – Looking for an efficient way to fix “Could not parse relay log event entry…” error

There is more stable approach you can try

Here is something to remember

Whenever you run CHANGE MASTER TO, it will erase every relay log you have. You do not want to keep relay logs of commands you have not executed any SQL on as of yet

The following is an excerpt taken from a post I made back on Feb 03, 2012 : How to resolve the master server shut down/unavailability in mysql with master - slave replication :

Please notice that there are two sets of replication coordinates from the Master

(Master_Log_File,Read_Master_Log_Pos)

(Relay_Master_Log_File,Exec_Master_Log_Pos)

There is a major difference between them

(Master_Log_File,Read_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs.

(Relay_Master_Log_File,Exec_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs THAT IS NEXT TO BE EXECUTED ON THE SLAVE.

What you want are two things:

Erase Every Binary Log You Have
Start Collecting Binary Log Entries From the Last SQL You Successfully Executed.

In your case, you must use the second set of Replication Coordinates

Relay_Master_Log_File
Exec_Master_Log_Pos

It is easy to distrust a corrupt relay log as shown in the error message. The one that hurts the most is a corrupt Master Log. You will have to jump through hoops if that is the case. On the other hand, if one of the other situations was the reason for the corrupt relay log, the simplest and most concise approach is what I stated.

To make sure, whatever is reported for Relay_Master_Log_File, if that particular binary log still exists on the Master, perform a mysqlbinlog on it. If it dumps in its entirety without corrupt characters, go ahead and use the second set of replication coordinates.

From my same earlier post

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

notice that the Replication Coordinates from SHOW SLAVE STATUS\G for what was last executed are (mysql-bin.000254,858190247). The CHANGE MASTER TO command in this case would be:

CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;

Give it a Try !!!

UPDATE 2012-09-14 16:38 EDT

If you worried about the stockpiling relay logs, just throttle the relay logs. In SHOW SLAVE STATUS\G, there is a field called Relay_Log_Space. That gives you the sum of all relay sizes in bytes. Did you know you could put a cap on that number ?

The option is called relay_log_space_limit.

For example, if you want to cap the total number of bytes to 10G, do the following

STEP 01) Add this to /etc/my.cnf on the Slave

[mysqld]
relay_log_space_limit = 10G

STEP 02) Run service mysql restart on the Slave

and that's it !!!

When the oldest relay has all its entries processed, it is deleted and a new relay log is created. That gets filled until all relay logs add up to 10G. That's the only way to control runaway relay log space issues.

UPDATE 2012-09-14 18:10 EDT

SUGGESTION : If you make mysqldump backups of the data on the Slave every midnight, you could set up the following to restrict having 1TB of binary logs:

STEP 01) Add this to /etc/my.cnf on the Master

[mysqld]
expire_logs_days = 14

STEP 02) Run this query on the Master

mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) - INTERVAL 14 DAY;

STEP 03) service mysql restart on the Master

STEP 04) Add a mysqldump backup script to a crontab on the Slave

This will make the Slave more useful and would control having excess binary logs to worry about

MySQL Master/Slave. Actual use of Slave Machine

Can I simply point my office web server to the Slave MySQL server? Will they be able to write to the slave?

Short answer: Yes, but they probably shouldn't.

Depending on how your application works, it is possible (even likely) that writing to the slave will break replication. Even if it doesn't, over time it's likely that your databases will develop split brain - for example, someone could change some data on your slave that remains undetected for a long period of time.

If you are able to maintain two MySQL servers in your office, I would suggest the following:

Master server #1 on your trailer
Replicant server #2 in your office, acting as slave to #1
"Working" server #3 in your office

Server #3 would be the server that your staff connect to. Set up a job - maybe one that runs each night - which takes a backup of server #2 and then restores it over the top of server #3. This way staff can work with fairly recent data without the risk of affecting replication.

You could even make server #3 a slave to server #2 if it looked like that might work, though I suspect this might cause more problems than it solves.

Public cell and wifi networks are unlikely to be as secure as you like - I'm assuming that you will secure the connection to your office via VPN. If not, MySQL can secure communications via SSL and you may want to consider that.