String lengths in the SQL backend

Roland Roberts roland at
Wed Nov 12 12:15:12 EST 2008

Phil Longstaff wrote:
> For those on the gnucash user list, a new SQL backend is in development.  An 
> issue has arisen, and I need to get input from users, not just developers.
> Rolf Leggewie has run into some problems with the SQL backend 
> ( - he wants to add another 
> SQL table to key off the account 'code' field.  However, the 'code' field is 
> varchar(2048) which is too big to be a primary key in mysql.  I responded that 
> earlier discussion on this mailing list (mainly/only by Derek) had been that 
> strings should be unlimited, and since SQL requires *some* limit, I chose 
> 2048.
> For those who don't know SQL, for a variable length string (varchar), the 
> database engine will only allocate enough space to hold the value, so allowing 
> 2048 and storing only 10 chars uses about the same disk space as allowing 128 
> and storing the same 10.  The problem in this case is that one user wants to 
> be able to index some more information by the account code, but the fields used 
> as the index is too large.
> I'd have to look back, but I think Derek's reply was the only one.  I'd like 
> to open the topic again, because of Rolf's problem.  Can anyone think of a 
> reason that account code size limit cannot be reduced to a smaller value (e.g. 
> 32)?   Will anyone ever enter an account code longer than that?
I confess I haven't been paying attention to the SQL backend in a 
while.  I'm one who would very much like to have this working since my 
wife and I often run into conflicts having to serialize our data entry 
(neither of us has much time to do data entry until the kids are asleep 
and then we both rush to get everything done)....

Anyway, I have to agree with Mike Novack, at some point I might well 
bump into the 32 character limit although its not likely.  And then I 
would end up annoyed if I can't bump it up somehow.  I have often bumped 
into the 32-character limit on Oracle entity names (which is really a 
30-character limit...).

While Paul Schwartz is correct that I probably could work around the 
limit, if a larger number is still doable I would just pick something 
larger.   32 characters is just a bit short for my comfort zone, but 
once you get above 50-60, I can't imagine typing that much.



		       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland at                            6818 Madeline Court
roland at                           Brooklyn, NY 11220

More information about the gnucash-devel mailing list