[Gambas-user] gb.db.form: Program hangs after erasing content in a DataControl and clicking into a DataBrowser

[Tobias Boege]

On Fri, 12 Sep 2014, Christof Thalhofer wrote:
> Hi Tobi,
> 
> Am 11.09.2014 um 19:57 schrieb Tobias Boege:
> 
> > attached is a project which shows odd behaviour. At first glance, the source
> > code seems OK to me, so it might be a bug.
> > 
> > It needs:
> >  - MySQL running, a user named "test" without password but with the ability
> >    to create a database.table "Kontakte.kontakte".
> > 
> > To reproduce:
> >  - (If you start the application without the above-mentioned table, it will
> >     automatically be created and filled with sample data.)
> >  - Select a record,
> >  - Go to the DataControl for the "Vorname" field and delete its contents,
> >  - Click anywhere into the DataBrowser,
> >  - Application hangs.
> 
> By a look at "create_table.sql" I see you have not so much sense of
> databases ;-)
> 

This is not my code. I'm just forwarding a bug report to the list.

> 1st: Use backticks in column names only if you want to name with
> "reserved words" like "if", "select" or names with commas or blancs. A
> name for a column like "nachname" needs no backticks.
> 
> 2nd: ("ACHTUNG ... ") Primary key is unique per table. To use "Nachname"
> as PK prevents you from inserting "Schmidt" more than one time in a
> table. Usually one uses a column with long integer for PK as a unique
> number per tuple (datensatz). That means a unique number for example per
> customer or invoice.
> 
> Now to the code:
> 
>     sSQL_Anweisung = "INSERT INTO `kontakte` VALUES "
>     sSQL_Anweisung &= "('1','Arno','Adler','Arneburg','39606','Am Hafen
> 3','03937864322'),"
> ...
> 
> could be eventually dangerous. You should define the columns where you
> want to insert your data:
> 
> "insert into kontakte (id, vorname, ...) values (1, 'Tobias', ...) (2,
> 'Albert', ...)"
> 
> etc.
> 

That's how I learned it in school, too. And guess what: the material I
learned it from was from the same person which did it wrong here :-)

> Ok, I do not use DataBrowser, so I do not know how it works and why
> MySQL hangs. Normally a database "hang" is a deadlock. This means two
> connections try to write the same tuple at the same time.
> 
> You could see this by increasing verbosity of the database logging and
> watching the database logfiles with tail -f ...
> 
> It seems that you use 2 connections at the same time in your application
> that want to write the same tuple (or table, MyISAM engine  does table
> level locking IMHO).
> 

Umm, I don't think there are two Connections involved here... Where do you
see that?

> BTW: Table locking is bad (only makes sense for single user). Good
> database engines do only row or even column locking for write (or delete).
> 
> Remove that code that creates a table from the connect function (that is
> really bad).
>

This piece of code there I recognise. It's from the PictureDatabase example
delivered with Gambas. So you suggest keeping table creation somewhere else?

Rumour has it that you want to contribute to Gambas, so changing the
PictureDatabase example to be less "bad" would be a good start!

> Use one connection which you create in a module (not
> class), so you get a singleton and do writing over this one connection
> one after the other.
> 
> Then you get no deadlocks.
> 

Have you tested that? Do you have a patch for the project?

Regards,
Tobi

-- 
"There's an old saying: Don't change anything... ever!" -- Mr. Monk