Sql error on tracker table update

Sebastian_Steinhube1 2012-11-22 17:19:58 UTC #1

Hi,

after updating trunk from 40911 to 40934, I had to update tracker to
1.9.008.
The browser interface gave me following error message from mysql,
repeated three times:

"AlterColumnSQL(‘egw_tracker_escalations’,‘tr_priority C(55) NOTNULL
DEFAULT ‘0’’,‘esc_id I AUTOINCREMENT NOTNULL PRIMARY, tr_tracker C(55)
NOTNULL DEFAULT ‘0’, cat_id I4 NOTNULL NOQUOTE DEFAULT 0, tr_version I4
NOTNULL NOQUOTE DEFAULT 0, tr_status C(255) NOTNULL DEFAULT ‘0’,
tr_priority C(55) NOTNULL DEFAULT ‘0’, esc_title C(128) NOTNULL,
esc_time I4 NOTNULL, esc_type I1 NOTNULL NOQUOTE DEFAULT 0,
esc_tr_assigned C(255), esc_add_assigned L, esc_tr_tracker I4,
esc_cat_id I4, esc_tr_version I4, esc_tr_status I4, esc_tr_priority I4,
esc_reply_message X, esc_reply_visible I1, esc_match_repeat I4 NOQUOTE
DEFAULT 0, esc_notify C(15), esc_limit I1’) sql=Array ( [0] => ALTER
TABLE egw_tracker_escalations MODIFY COLUMN tr_priority VARCHAR(55)
NOT NULL DEFAULT ‘0’ )

Specified key was too long; max key length is 1000 bytes"

Btw, the script didn’t catch the error above, I would assume that a kind
of rollback and/or retry would be possible, too.
The collation of egw_tracker_escalations is utf8_general_ci (the default).
Is there something I can do manually now? I assume that some types and
lengths of some columns have to be changed, but can’t get the meaning of
C(55), C(255), I4, …
… column tr_priority has type INT and length 11 now.

Thanks.

Sebastian

Monitor your physical, virtual and cloud infrastructure from a single
web console. Get in-depth insight into apps, servers, databases, vmware,
SAP, cloud infrastructure, etc. Download 30-day Free Trial.
Pricing starts from $795 for 25 servers or applications!
http://p.sf.net/sfu/zoho_dev2dev_nov

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

ngegw 2012-11-26 15:16:02 UTC #2

Hi,

after updating trunk from 40911 to 40934, I had to update tracker to
1.9.008.
The browser interface gave me following error message from mysql,
repeated three times:…
Specified key was too long; max key length is 1000 bytes.

Thanks, I’ll look into that.

Btw, the script didn’t catch the error above, I would assume that a kind
of rollback and/or retry would be possible, too.
The collation of egw_tracker_escalations is utf8_general_ci (the default).
Is there something I can do manually now? I assume that some types and
lengths of some columns have to be changed, but can’t get the meaning of
C(55), C(255), I4, …
… column tr_priority has type INT and length 11 now.

C(55) = varchar(55)
I believe everything should still be OK, just a missing index. If I figure
out how to fix it, it should be fixed automatically for all.

Nathan

ngegw 2012-11-26 17:34:43 UTC #3

This appears to be caused by some confusion (in MySQL) between a character
and a byte. I’m not sure why sometimes it works for me, sometimes not.
The easy solution is to drop the unique constraint. An alternative is to
generate a unique hash and index on that.

Ralf, can you give me some direction on how to solve this?

Ralf_Becker_Stylite 2012-11-26 21:19:04 UTC #4

Hi Nathan,

if EGroupware charset is utf-8 MySQL needs 3 bytes for each character in an index, unique or not does not matter. Maybe your table is for some reason not utf-8, as it does not matter for englisch content. Other cause could be, index creation simply failed without stalling update and you did not notice.

Anyway only way to solve that, is to limit how much of an column get indexed. By default the whole column get indexed, thought index works nearly equally well if its limited to something like 32 chars ~ 100 byte.

Notsure eTemplate db-tools can specify such an index, but API / schema proc can, just write: column(length) instead of just column in tables_current and _update/create index.

Ralf

Ralf Becker
Director Software Development

Stylite AG

Morschheimer Strasse 15 | Tel. +49 6352 70629 0
D-67292 Kirchheimbolanden | Fax. +49 6352 70629 30

Email: rb@stylite.de

www.stylite.de | www.egroupware.org

Managing Directors: Andre Keller | Ralf Becker | Gudrun Mueller
Chairman of the supervisory board: Prof. Dr. Birger Leon Kropshofer

VAT DE214280951 | Registered HRB 31158 Kaiserslautern Germany

ngegw 2012-11-26 21:35:30 UTC #5

Thanks Ralf.
db-tools can’t, so I’ll try in the file directly.

Nathan

ngegw 2012-11-26 22:29:33 UTC #6

Columns are now limited to 32, so that shouldn’t happen any more.
SVN r40946.

You may need to run the upgrade again after setting the version back one:
update egw_applications set app_version = ‘1.9.007’ where app_name =
‘tracker’

You should see errors about duplicate column names match_count &
tr_resolution the second time because they’re already there, but nothing
about the index.

Nathan

Sebastian_Steinhube1 2012-11-27 11:46:23 UTC #7

Columns are now limited to 32, so that shouldn’t happen any more.
SVN r40946.

You may need to run the upgrade again after setting the version back one:
update egw_applications set app_version = ‘1.9.007’ where app_name =
‘tracker’

You should see errors about duplicate column names match_count &
tr_resolution the second time because they’re already there, but nothing
about the index.

Nathan

This works for me. Thanks for investigating and provisioning,

Sebastian

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers