Update to 19.1 - Conversion of multiline history to diff fails

ckan 2019-09-17 09:25:57 UTC #1

Hello, we (me and @asigalas) are trying to upgrade version 17.1 to 19.1 through api_upgrade17_9_001()
and the database update script fails ( time out ).
We currently have over 2.900.000 records in the table egw_history_log.
Do you have any suggestions on how to proceed ?

RalfBecker 2019-09-17 10:11:32 UTC #2

Hi Alexander,

depends with what error it fails?

The update runs in chunks of 200 history-entrys, to not hit the memory_limit:

github.com

EGroupware/egroupware/blob/master/api/setup/tables_update.inc.php#L574


*
* Benchmark on Ralf's Laptop (OS X)
* - auto/Native: 66k rows in 15mins
*
* @return string new version
*/
function api_upgrade17_9_001()
{
	$renderer = new Horde_Text_Diff_Renderer_Unified();
	$start = microtime(true);
	$junk_size = 200;	// 2*200*160KB = 64MB
	$total = $saved = 0;
	do {
		$n = 0;
		foreach($GLOBALS['egw_setup']->db->select('egw_history_log', 'history_id,history_new_value,history_old_value', array(
			'history_old_value != '.$GLOBALS['egw_setup']->db->quote(Api\Storage\Tracking::DIFF_MARKER),
			// if one is empty, no need to store diff
			"(LENGTH(history_new_value) > 0 AND LENGTH(history_old_value) > 0)",
			"(history_status LIKE '%description' OR history_status='De' OR history_status='note'".
				" OR LENGTH(history_new_value) > 200 OR LENGTH(history_old_value) > 200)",
		), __LINE__, __FILE__, 0, 'ORDER BY history_id', false, $junk_size) as $row)

On my laptop I was able to convert 66k entries in 15min, so 3million should run in 50*15min ~ 12h.

Did you wait long enough or did it fail after max_excution_time (which it should not, as setup should switch that off)?

Did you run the update via the web-interface, or setup/setup-cli.php like the packages. The later is not affected by any of the above mentioned limits, thought time-wise there will be no difference to the above estimated 12h.

Ralf

ckan 2019-09-18 06:05:33 UTC #3

Thank you for your reply, the cli version completed successfully and the update is ready.

Chris

RalfBecker 2019-09-18 06:58:20 UTC #4

How long did the 2.9 million history-log entries take to process?

Do you recall with what error the attempted update via the web-interface failed?

Ralf

AlexS 2019-09-18 09:49:25 UTC #5

Hello Ralf,
we ran the cli update on a second system with approximately 2.7 million records in history. I think it took less than three hours to complete.
Via the web interface, the screen turned blank, while in the server log we found messages from Maria DB… too many connections…
Best regards
Alex

AlexS 2019-09-19 05:57:25 UTC #6

Hello Ralf,

I did the update yesterday once again and noticed that the original history_timestamp of the changed records of api_upgrade17_9_001() gets also updated and replaced by the current timestamp.
This results in a corrupted history log.

I found that this happens because of the design of the ‘egw_history_log’ table:

‘egw_history_log’ => array(
‘fd’ => array(
…
‘history_timestamp’ => array(‘type’ => ‘timestamp’,‘nullable’ => False,‘default’ => ‘current_timestamp’),
…
),

I my opinion this is an important issue and should be fixed ASAP.

Best regards,

Alexander

RalfBecker 2019-09-19 09:23:28 UTC #7

Hi Alexander,

thanks for reporting.

I fixed the update to not touch the timestamps, and added an update for everyone who already run the (unfixed) update. In the later case we replace the broken timestamp of the diffed row with the one of the preceeding row.

In most cases, where something else was updated too, you have again the correct time, if it was the only update, you definitiv have a “better” / more fitting time after the update.

Ralf