Hello, I have two questions regarding sys_datalog table 1. i have multiserver setup: 1.ispc gui+dns 2. web server 3. email server 4.db server I found today that sys_datalog table in dbispconfig db on master serer has ca 99.000 records, all in "pending" state starting from year 2010 as was the intial installation As i found there is "pending" as default record for status colums. On new installations of ispc, default status is "ok". I tried to truncate whole table sys_datalog and set "ok" as default records for "status" column . But then synchonization between servers stopped. So i put back backup of sys_datalog table it's working again I think sys_datalog is supposed to delete old records after one moth? Should be pending status cause of not deleting old records? What i should do to fix it ??? 2. sys_datalog table on all slaves is empty. is it supposed work this way ?
1) This column is not used anymore, so the value of the updated column does not matter. The default is pending, we changed it to ok at some time because people that read the data in the database were asking why its still pending. In general, yes. But ist a bit more complicated. I copied the command which explains the requirements for records to be deleted from the sourcecode file: Code: /* * The sys_datalog is more difficult. * 1) We have to keet ALL entries with * server_id=0, because they depend on ALL servers (even if they are not * actually in the system (and will be insered in 3 days or so). * 2) We have to keey ALL entries which are not actually precessed by the * server never mind how old they are! * 3) We have to keep the entry with the highest autoinc-id, because mysql calculates the * autoinc-id as "new value = max(row) +1" and does not store this in a separate table. * This means, if we delete to entry with the highest autoinc-value then this value is * reused as autoinc and so there are more than one entries with the same value (over * for example 4 Weeks). This is confusing for our system. * ATTENTION 2) and 3) is in some case NOT the same! so we have to check both! */ Yes, as all slaves read the datalog from the master and use it to insert records in their database tables.
Thanks for quick reply, Can your help me with main main questions ? 1.How can i prune the sys_datalog table? 2.Why synchronizations stop when i truncate table? Will it help when i delete all records and keep just that one with highest autoinc ?? =========================== To your explanation " * 1) We have to keet ALL entries with * server_id=0, because they depend on ALL servers (even if they are not * actually in the system (and will be insered in 3 days or so). " Don't uderstand. When there is no server 0 (which is weird as first serer is always0) other servers will have always higher number... --- 2) We have to keey ALL entries which are not actually precessed by the * server never mind how old they are! How does master server recognize that entries was processed? i'd like to understand this mechanism
1) You can do it by following the rules that I posted above from the source file: - Delete only records that dont have server_id = 0 - Never delete the first record Thats what ispconfig is doing. 2) Truncating the table will stop replication ans mysql renumbers the data records. He stores the ID of the last processed record in the updated column of the server table.
Thank you... one last question So i really cant delete records with server id 0 even if they are 3 years old.. ? about 95.000 of records are with server 0, actually records with server id 1234 started 5/5/2013 i think after udpate from 3.0.4 to 3.0.5.2 version...
You can remove records with server_id 0, but in case that you add a new server in your cluster at a later time, it might be that some global data like the client data can not be synced then and you might have to dump the ata manually and insert it into the database of the new slave server.
as i split single server installation to multiple servers year ago, server 0 records will probably be not relevant anyway...
