Devs, please read...Re: Collection scanner fails for some sampler CDs

Jeff Mitchell kde-dev at emailgoeshere.com
Mon Aug 7 12:45:38 UTC 2006 

Andrew--

A learning experience all around indeed.

Compounding my panic last night was the fact that the code wasn't all 
consistent (still creating indices on the labels table, etc) which led to 
different sorts of errors, leading me in the end to figure that our database 
code was just seriously borked and making it hard to figure out exactly where 
the problem lay in terms of past commits.  I'm glad that the fixes turned out 
to be relatively simple though...if only kde-commits had behaved like you 
thought it would   :-)

--Jeff

On Monday 07 August 2006 08:17, Andrew Turner wrote:
> Jeff,
>
> This never was the reason for listing the MySQL version at 5. As far
> as I understand it, Martin just didn't realise that it wouldn't work
> in MySQL4, especially since it worked fine in SQLite, which is usually
> less capable than MySQL.
>
> The annoying thing is that I did identify this problem before it
> affected you, having been the author of the exactTextColumnType and
> researching what MySQL could do. However, I stupidly replied to
> kde-commits instead of the list, hoping it might get forwarded, which
> it didn't:
> http://lists.kde.org/?l=kde-commits&m=115488906330401&w=2
>
> A learning experience all round, I think.
>
> Andrew
>
> On 07/08/06, Jeff Mitchell <kde-dev at emailgoeshere.com> wrote:
> > Andrew, Martin--
> >
> > Thanks for fixing this.  I knew that the problem was that the length was
> > too long, I just figured that we always wanted to be able to keep it at
> > 1024, so I was attempting to hack my way around the bug.
> >
> > I gather this is the reason that we had the dependency for MySQL listed
> > at version 5 for a short time.
> >
> > Sorry for making you go through the trouble of reverting my hack, but I
> > was attempting to at least get it into a creatable mode yesterday and
> > didn't have much time in between other things to really look at it.
> >
> > --Jeff
> >
> > On Monday 07 August 2006 06:44, Andrew Turner wrote:
> > > Wait. It's too early. I mean the labels table, not lyrics.
> > > Sorry for all this list spam.
> > >
> > > Andrew
> > >
> > > On 07/08/06, Andrew Turner <andrewturner512 at googlemail.com> wrote:
> > > > Also, the lyrics table is never used and is meant to have been
> > > > removed. If there are any traces of lyrics left over (such as the
> > > > index creation), then that's my fault.
> > > >
> > > > Andrew
> > > >
> > > > On 07/08/06, Andrew Turner <andrewturner512 at googlemail.com> wrote:
> > > > > Jeff,
> > > > >
> > > > > I reckon at least part of this is caused by the changing the
> > > > > default length of the exactTextColumnType up to 1024 so that URLs
> > > > > can hold the full path (commit 570151). According to the MySQL
> > > > > docs, that bumps the required MySQL version to 5.0.3.
> > > > >
> > > > > I've tried to talk to aumuell about it, but he wasn't around when I
> > > > > tried yesterday.
> > > > >
> > > > > Andrew
> > > > >
> > > > > On 07/08/06, Jeff Mitchell <kde-dev at emailgoeshere.com> wrote:
> > > > > > I'm going to commit a temporary fix that will cause the database
> > > > > > upgrade code to be skipped if the tables have just been created,
> > > > > > i.e. isValid() returned false initially.  Note that sometimes
> > > > > > isValid returns false even if the tables are there and should be
> > > > > > upgraded; so this is not a permanent fix, and neither is dropping
> > > > > > the tables first and then recreating them to ensure that things
> > > > > > are up-to-date (that would bring back the old losing-stats bug).
> > > > > >
> > > > > > For now, however, this keeps the database upgrade code from
> > > > > > screwing up a fresh database creation.  This may be able to be
> > > > > > fixed by applying the fixes I did to other places where tables
> > > > > > are dropped/created/indexes created, but I'm *really* out of time
> > > > > > for tonight, and I don't know that it's the best idea anyways.
> > > > > >
> > > > > > --Jeff
> > > > > >
> > > > > > On Sunday 06 August 2006 22:41, Jeff Mitchell wrote:
> > > > > > > Another problem--
> > > > > > >
> > > > > > > If tables don't exist and they are created fresh, the database
> > > > > > > upgrade code then runs on it, which as of right now doesn't do
> > > > > > > the same checks as thee create() functions.  But I'm not sure
> > > > > > > that creating them and populating them with the correct admin
> > > > > > > values is really a good idea.
> > > > > > >
> > > > > > > Anyone know if when a create table function is run we should
> > > > > > > automatically skip the upgrade code?
> > > > > > >
> > > > > > > --Jeff
> > > > > > >
> > > > > > > On Sunday 06 August 2006 22:15, Jeff Mitchell wrote:
> > > > > > > > Some more things:
> > > > > > > >
> > > > > > > > It seems that creating the label table only happened in one
> > > > > > > > place...I've added it to the generic createPersistentTables
> > > > > > > > code.
> > > > > > > >
> > > > > > > > Creating the lyrics_url index was taking place in
> > > > > > > > createIndices, which is called for the non-persistent tables
> > > > > > > > -- even though lyrics is a persistent table and wasn't
> > > > > > > > created by that point. I've changed that.
> > > > > > > >
> > > > > > > > An index for deviceid_label was getting created even though
> > > > > > > > no such column existed.  I've added that column.
> > > > > > > >
> > > > > > > > There's still a problem with the line:
> > > > > > > >     query( "CREATE INDEX devices_rshare ON devices(
> > > > > > > > servername(255), sharename(255) );" );
> > > > > > > > in createPersistentTables().  I think it is complaining
> > > > > > > > becauase the text size is 1024, and it won't create an index
> > > > > > > > on something that large even if you pass in a size.  Don't
> > > > > > > > know if that's really the case or what.
> > > > > > > >
> > > > > > > > Please see rev 570507, because I really don't know if this
> > > > > > > > code causes more problems than it solves, but I don't know
> > > > > > > > how to really solve it either, nor have the time right now. 
> > > > > > > > If this code causes more problems than it solves, feel free
> > > > > > > > to revert it.
> > > > > > > >
> > > > > > > > --Jeff
> > > > > > > >
> > > > > > > > On Sunday 06 August 2006 21:19, Jeff Mitchell wrote:
> > > > > > > > > I'm not sure what has changed, but I do know what's causing
> > > > > > > > > the problem. It'd be instructive to read
> > > > > > > > > http://www.mysqlfreaks.com/errors/275.php
> > > > > > > > >
> > > > > > > > > Right now I'm going through and updating some code...I'm
> > > > > > > > > getting it to the point where I can successfully create
> > > > > > > > > tables again, limiting the size of the index.  I don't know
> > > > > > > > > if this is necessary for only MySQL or not, or if it is
> > > > > > > > > even valid syntax on Postgres/SQLite...
> > > > > > > > >
> > > > > > > > > I'm kind of flying by the seat of my pants, so any dev (or
> > > > > > > > > other person on this list) that knows about databases,
> > > > > > > > > please look at this problem. I'll post the revision number
> > > > > > > > > when I commit.  I'm hoping I don't break Postgres/SQLite,
> > > > > > > > > but checks can be added in if necessary to only pass in a
> > > > > > > > > length argument (which is 255 max by default, it's a
> > > > > > > > > compile-time value apparently) if it's MySQL.
> > > > > > > > >
> > > > > > > > > Also, for now at least I don't have the time to look at
> > > > > > > > > conversion code, I'm just looking at code starting from no
> > > > > > > > > tables.
> > > > > > > > >
> > > > > > > > > --Jeff
> > > > > > > > >
> > > > > > > > > On Sunday 06 August 2006 19:06, Jeff Mitchell wrote:
> > > > > > > > > > I also just updated and am finding that the code that is
> > > > > > > > > > supposed to create database tables if they don't exist is
> > > > > > > > > > failing.  I'm checking it out.
> > > > > > > > > >
> > > > > > > > > > --Jeff
> > > > > > > > > >
> > > > > > > > > > On Sunday 06 August 2006 16:36, Martin Burnicki wrote:
> > > > > > > > > > > Hi all,
> > > > > > > > > > >
> > > > > > > > > > > I've not yet received any reply to my original email,
> > > > > > > > > > > but here's some additional info. From the amarok
> > > > > > > > > > > console output:
> > > > > > > > > > >
> > > > > > > > > > > amarok:     [CollectionDB] MYSQL QUERY FAILED: You have
> > > > > > > > > > > an error in your SQL syntax; check the manual that
> > > > > > > > > > > corresponds to your MySQL server version for the right
> > > > > > > > > > > syntax to use near '%4, 1125850911 )' at line 1 amarok:
> > > > > > > > > > > FAILED QUERY: REPLACE INTO directories_temp ( dir,
> > > > > > > > > > > deviceid, changedate ) VALUES (
> > > > > > > > > > > './pub/music/martin/cd/UFO/Time To Rock (CD12f2)', %4,
> > > > > > > > > > > 1125850911 );
> > > > > > > > > > >
> > > > > > > > > > > I'm not quite familiar with mysql, but from what I see
> > > > > > > > > > > I'd think the '%4' statement in the SQL query should
> > > > > > > > > > > have been replaced with some real value.
> > > > > > > > > > >
> > > > > > > > > > > Also, at startup I see the following mys1ql errors:
> > > > > > > > > > >
> > > > > > > > > > > amarok:       [CollectionDB] MYSQL QUERY FAILED: Table
> > > > > > > > > > > 'amarok.statistics' doesn't exist
> > > > > > > > > > > amarok: FAILED QUERY: SELECT COUNT( url ) FROM
> > > > > > > > > > > statistics LIMIT 1 OFFSET 0; amarok:      
> > > > > > > > > > > [CollectionDB] MYSQL QUERY FAILED: Table
> > > > > > > > > > > 'amarok.podcastchannels' doesn't exist amarok: FAILED
> > > > > > > > > > > QUERY: SELECT COUNT( url ) FROM podcastchannels LIMIT 1
> > > > > > > > > > > OFFSET 0;
> > > > > > > > > > > amarok:       [CollectionDB] MYSQL QUERY FAILED: Table
> > > > > > > > > > >   'amarok.podcastepisodes' doesn't exist
> > > > > > > > > > > amarok: FAILED QUERY: SELECT COUNT( url ) FROM
> > > > > > > > > > > podcastepisodes LIMIT 1 OFFSET 0;
> > > > > > > > > > >
> > > > > > > > > > > I.e., the tables 'statistics', 'podcastchannels', and
> > > > > > > > > > > 'podcastepisodes' don't seem to be created when the
> > > > > > > > > > > amarok DB is created.
> > > > > > > > > > >
> > > > > > > > > > > Regards,
> > > > > > > > > > >
> > > > > > > > > > > Martin
> > > > > > > > > >
> > > > > > > > > > _______________________________________________
> > > > > > > > > > Amarok mailing list
> > > > > > > > > > Amarok at kde.org
> > > > > > > > > > https://mail.kde.org/mailman/listinfo/amarok
> > > > > > > > >
> > > > > > > > > _______________________________________________
> > > > > > > > > Amarok mailing list
> > > > > > > > > Amarok at kde.org
> > > > > > > > > https://mail.kde.org/mailman/listinfo/amarok
> > > > > > > >
> > > > > > > > _______________________________________________
> > > > > > > > Amarok mailing list
> > > > > > > > Amarok at kde.org
> > > > > > > > https://mail.kde.org/mailman/listinfo/amarok
> > > > > > >
> > > > > > > _______________________________________________
> > > > > > > Amarok mailing list
> > > > > > > Amarok at kde.org
> > > > > > > https://mail.kde.org/mailman/listinfo/amarok
> > > > > >
> > > > > > _______________________________________________
> > > > > > Amarok mailing list
> > > > > > Amarok at kde.org
> > > > > > https://mail.kde.org/mailman/listinfo/amarok
> > >
> > > _______________________________________________
> > > Amarok mailing list
> > > Amarok at kde.org
> > > https://mail.kde.org/mailman/listinfo/amarok
> >
> > _______________________________________________
> > Amarok mailing list
> > Amarok at kde.org
> > https://mail.kde.org/mailman/listinfo/amarok
>
> _______________________________________________
> Amarok mailing list
> Amarok at kde.org
> https://mail.kde.org/mailman/listinfo/amarok

More information about the Amarok mailing list