#1  
Old 14th May 2013, 13:48
radim_h radim_h is offline
Senior Member
 
Join Date: Jan 2007
Location: Prague, Czech
Posts: 420
Thanks: 33
Thanked 24 Times in 19 Posts
Send a message via ICQ to radim_h
Default sys_datalog problem?

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 ?

Last edited by radim_h; 14th May 2013 at 13:51.
Reply With Quote
Sponsored Links
  #2  
Old 14th May 2013, 14:07
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,814
Thanks: 821
Thanked 5,340 Times in 4,189 Posts
Default

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.

Quote:
I think sys_datalog is supposed to delete old records after one moth?
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!
	 */
Quote:
2.
sys_datalog table on all slaves is empty.
is it supposed work this way ?
Yes, as all slaves read the datalog from the master and use it to insert records in their database tables.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #3  
Old 14th May 2013, 15:03
radim_h radim_h is offline
Senior Member
 
Join Date: Jan 2007
Location: Prague, Czech
Posts: 420
Thanks: 33
Thanked 24 Times in 19 Posts
Send a message via ICQ to radim_h
Default

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
Reply With Quote
  #4  
Old 14th May 2013, 15:52
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,814
Thanks: 821
Thanked 5,340 Times in 4,189 Posts
Default

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.

Quote:
How does master server recognize that entries was processed? i'd like to understand this mechanism
He stores the ID of the last processed record in the updated column of the server table.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #5  
Old 14th May 2013, 17:19
radim_h radim_h is offline
Senior Member
 
Join Date: Jan 2007
Location: Prague, Czech
Posts: 420
Thanks: 33
Thanked 24 Times in 19 Posts
Send a message via ICQ to radim_h
Default

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...
Reply With Quote
  #6  
Old 14th May 2013, 19:01
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,814
Thanks: 821
Thanked 5,340 Times in 4,189 Posts
Default

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.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
The Following User Says Thank You to till For This Useful Post:
radim_h (14th May 2013)
  #7  
Old 14th May 2013, 20:50
radim_h radim_h is offline
Senior Member
 
Join Date: Jan 2007
Location: Prague, Czech
Posts: 420
Thanks: 33
Thanked 24 Times in 19 Posts
Send a message via ICQ to radim_h
 
Default

as i split single server installation to multiple servers year ago, server 0 records will probably be not relevant anyway...
Reply With Quote
Reply

Bookmarks

Tags
sys_datalog

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sending mails: fine > recive: bad Moundy General 1 19th June 2012 23:40
Courier Imap is not working HellMind General 5 12th April 2012 01:04
Strange email problem for one of my domains... any help appreciated paulrobert_a Installation/Configuration 5 9th August 2010 14:15
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 13:58
BIG Problem Postfix issue admins Installation/Configuration 11 13th November 2009 10:05


All times are GMT +2. The time now is 07:32.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.