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

Mon Aug 7 12:17:48 UTC 2006

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
>