Application not responding on startup with 2.33 core version upgrade

[link2xt]

Here is a relevant Android issue:
deltachat/deltachat-android#4116

I suspect that this is caused by some recently added SQL transaction that happens to be slow and because it happens synchronously, Android kills the app after not responding for 15 seconds.

We need to look at all migrations added since 2.25.0 and before 2.31.0. Migration version for 2.25.0 was 138, and 143 for 2.33.0, so we need to review migrations 138::143.

1. Migration 138 creates an empty table.
2. Migration 139 tweaks bcc_self config, changing at most one row.
3. Migration 140 adds transport column to imap table, recreating the whole table. This could be slow if imap table is large.
4. Migration 141 adds index to imap table, it could be slow if existing table is large.
5. Migration 142 adds column to transports table, this should be cheap.
6. Migration 143 fills columnname_normalized for chats and contacts. (opened a PR fix: do not set normalized name for existing chats and contacts in a migration #7603 to speedup it)

For migration 143 when in doubt I think we can just remove the code that sets name_normalized for existing chats and contacts:

core/src/sql/migrations.rs

Lines 1457 to 1505 in bf72b3a

	inc_and_check(&mut migration_version, 143)?;
	if dbversion < migration_version {
	let trans_fn = |t: &mut rusqlite::Transaction| {
	t.execute_batch(
	"
	ALTER TABLE chats ADD COLUMN name_normalized TEXT;
	ALTER TABLE contacts ADD COLUMN name_normalized TEXT;
	",
	)?;
	let mut stmt = t.prepare("UPDATE chats SET name_normalized=? WHERE id=?")?;
	for res in t
	.prepare("SELECT id, name FROM chats LIMIT 10000")?
	.query_map((), |row| {
	let id: u32 = row.get(0)?;
	let name: String = row.get(1)?;
	Ok((id, name))
	})?
	{
	let (id, name) = res?;
	if let Some(name_normalized) = normalize_text(&name) {
	stmt.execute((name_normalized, id))?;
	}
	}
	let mut stmt = t.prepare("UPDATE contacts SET name_normalized=? WHERE id=?")?;
	for res in t
	.prepare(
	"
	SELECT id, IIF(name='', authname, name) FROM contacts
	ORDER BY last_seen DESC LIMIT 10000
	",
	)?
	.query_map((), |row| {
	let id: u32 = row.get(0)?;
	let name: String = row.get(1)?;
	Ok((id, name))
	})?
	{
	let (id, name) = res?;
	if let Some(name_normalized) = normalize_text(&name) {
	stmt.execute((name_normalized, id))?;
	}
	}
	Ok(())
	};
	sql.execute_migration_transaction(trans_fn, migration_version)
	.await?;
	}

This does not affect new users, when chat names are updated, the problem will fix itself, it was already like this before so there is no regression. I think we should just do it to exclude the possibility of this being slow e.g. if the user has tons of chats for some reason.

Migration 140 might also be a problem.

Not exactly related, but there is currently no way to get logs from account manager before it fully initializes, so we don't know which migration got stuck and if this was even a problem. I think this can be fixed by changing the API so UI creates "event emitter" (essentially a channel first) that can already be listened to, then calls dc_accounts_new_with_event_emitter or something like that (I'm talking about CFFI because this is what Android uses), passing already created "event emitter" and can retrieve info events already before dc_accounts_t is returned. For this we need to open a separate issue before closing this one. (EDIT: moved this to #7606)

[r10s]

For migration 143 when in doubt I think we can just remove the code that sets name_normalized for existing chats and contacts

tho chats/contacts tables are usually small (compared to messages), i fully agree, to exclude an error case, we can remove that migration

[link2xt]

I did something for migration 143: #7603

[link2xt]

Current situation is that #7603 did not speed things up much, it is likely imap migration 140 that is also slow. On old Android phone upgrading from 2.25.0 to 2.33.0 migrations still take a while, but finish eventually. For new Android that kills the app we found a workaround in developer options: deltachat/deltachat-android#4116 (comment)
For Android the plan is to turn "background ANR" into "foreground ANR" somehow, this should be tracked still in deltachat/deltachat-android#4116

For the next time: we could have avoided migration 140 being slow by renaming old table and keeping it, then migrating it gradually in housekeeping. But we cannot change this migration this way now as it is already released, so not going to do anything about it.