PDA

View Full Version : Can't Create "Tracks" Table with MySQL



indybrett
2006-02-13, 14:28
I tried switching over to MySQL. When the createdb.sql script is ran, it creates all of the tables except for the "tracks" tables.

I get the following error:

ERROR 1073: BLOB column 'multialbumsortkey' can't be used in key specification with the used table type

This is with MySQL version 3.23.58.13 on Redhat Fedora 3.

I suspect that I need to upgrade to MySQL version 4.X, but that is a major pain in the arse with RPM. There are a zillion dependencies.

Any ideas? Workarounds? FYI, I'm experienced with Linux, but a newbie with SQL.

Thanks,

Brett

mherger
2006-02-13, 14:46
> I get the following error:
>
> ERROR 1073: BLOB column 'multialbumsortkey' can't be used in key
> specification with the used table type
>
> This is with MySQL version 3.23.58.13 on Redhat Fedora 3.

I think this is an issue with older MySQL versions - I experienced the
same with my e-smith/contribs.org Linux. What I did is use MyISAM instead
of the default InnoDB table type. I haven't experience any problem with
this change.

--

Michael

-----------------------------------------------------------
Help translate SlimServer by using the
StringEditor Plugin (http://www.herger.net/slim/)

jimwillsher
2006-02-13, 14:56
Yes, I suspect it's a MySql issue.

Out of curiosity, are you able to create the table using a different table type (e.g. MyISAM). I suspect you'll get the same issue, but it's worth a try.

The SQL to create it manually (e.g. in phpMyAdmin) is



CREATE TABLE `tracks` (
`id` int(10) unsigned NOT NULL auto_increment,
`url` text NOT NULL,
`title` varchar(255) default NULL,
`titlesort` varchar(255) default NULL,
`titlesearch` varchar(255) default NULL,
`album` int(10) unsigned default NULL,
`tracknum` int(10) unsigned default NULL,
`ct` varchar(255) default NULL,
`tag` int(10) unsigned default NULL,
`age` int(10) unsigned default NULL,
`fs` int(10) unsigned default NULL,
`size` int(10) unsigned default NULL,
`offset` int(10) unsigned default NULL,
`year` smallint(5) unsigned default NULL,
`secs` float unsigned default NULL,
`cover` varchar(255) default NULL,
`covertype` varchar(255) default NULL,
`thumb` varchar(255) default NULL,
`thumbtype` varchar(255) default NULL,
`vbr_scale` varchar(255) default NULL,
`bitrate` float unsigned default NULL,
`rate` int(10) unsigned default NULL,
`samplesize` int(10) unsigned default NULL,
`channels` tinyint(1) unsigned default NULL,
`blockalign` int(10) unsigned default NULL,
`endian` tinyint(1) unsigned default NULL,
`bpm` smallint(5) unsigned default NULL,
`tagversion` varchar(255) default NULL,
`tagsize` int(10) unsigned default NULL,
`drm` tinyint(1) unsigned default NULL,
`rating` tinyint(1) unsigned default NULL,
`disc` tinyint(1) unsigned default NULL,
`playCount` int(10) unsigned default NULL,
`lastPlayed` int(10) unsigned default NULL,
`audio` tinyint(1) unsigned default NULL,
`remote` tinyint(1) unsigned default NULL,
`lossless` tinyint(1) unsigned default NULL,
`lyrics` text,
`moodlogic_id` int(10) unsigned default NULL,
`moodlogic_mixable` tinyint(1) unsigned default NULL,
`musicbrainz_id` varchar(40) default NULL,
`musicmagic_mixable` tinyint(1) unsigned default NULL,
`replay_gain` float default NULL,
`replay_peak` float default NULL,
`multialbumsortkey` text,
PRIMARY KEY (`id`),
KEY `trackTitleIndex` (`title`),
KEY `trackAlbumIndex` (`album`),
KEY `ctSortIndex` (`ct`),
KEY `trackSortIndex` (`titlesort`),
KEY `trackSearchIndex` (`titlesearch`),
KEY `trackRatingIndex` (`rating`),
KEY `trackPlayCountIndex` (`playCount`),
KEY `trackAudioIndex` (`audio`),
KEY `trackRemoteIndex` (`remote`),
KEY `trackLosslessIndex` (`lossless`),
KEY `trackSortKeyIndex` (`multialbumsortkey`(255)),
KEY `urlIndex` (`url`(255))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4054 ;

where ENGINE= needs to be the table-type.

Jim

indybrett
2006-02-14, 04:40
It complained about these entries:

url text NOT NULL,
multialbumsortkey text,

I changed them as follows:

url varchar(255),
multialbumsortkey varchar(255),

I have no idea if this is going to cause a problem later on, but the table created successfully, and Slimserver is now working with MySQL.

Edit: I had already done this before reading your replies. I think I'll create a second database to test your ideas as well. Thanks.

indybrett
2006-02-14, 09:12
OK, I created a new database and changed the table type to MyISAM instead of the default InnoDB table type, as suggested.

That worked.

Could you tell me the difference between the two in a way a non-DBA can understand?

Thanks again,

Brett

mherger
2006-02-14, 09:57
> Could you tell me the difference between the two in a way a non-DBA can
> understand?

Hmm... I'm no DBA neither... I think Berkeley offers advanced methods to
protect your data's integrity in complex transactions where several tables
are used. MyISAM on the other hand is said to be very fast reading its
data (partly because it is lacking the expensive integrity checks?). As I
wouldn't estimate Slimserver a business critical application for me, I
don't care too much about data integrity :-).

--

Michael

-----------------------------------------------------------
Help translate SlimServer by using the
StringEditor Plugin (http://www.herger.net/slim/)

indybrett
2006-02-15, 04:28
One more important thing. When I changed the script, I gave it a new name. You need to keep the name as dbcreate.sql because the Slimserver will apparently run the script again when you do a "clear library and rescan". I did that and the slimserver.pl daemon crashed. I looked at the database and the "tracks" table was missing again. After I renamed the new script to be dbcreate.sql, everything worked fine, and no more crashes.

Thanks again,

Brett

jimwillsher
2006-02-15, 09:19
MyISAM is a memory-resident database, which is very fast. InnoDB is by a third party, and offers more functionality (especially in a critical situation), as it offers full consistency support, e.g. transaction control. Slim is far from critical - if the tables go titsup you just rescan your folder.

Inevitably there's differeces in syntax/features between databases, and I think that's what you've experienced here. There should be no ongoing implications though, as I would imaging that the SEELCT, ISNERT, DELETE and UPDATE syntax will be pretty constant between InnoDB and MyISAM.


Jim

mherger
2006-02-15, 09:49
> MyISAM is a memory-resident database, which is very fast.

If you mean all-in-memory than you're wrong. There's a table type MEMORY
(HEAP) which is run in memory. But MyISAM is disk based.

"The data is stored in a .MYD file and all index are stored in a .MYI
file."

(http://tinyurl.com/9smos)

--

Michael

-----------------------------------------------------------
Help translate SlimServer by using the
SlimString Translation Helper (http://www.herger.net/slim/)

jimwillsher
2006-02-15, 09:59
Okay my mistake. But it illustrates the point that different DB implementations have different syntax and features, and that's how this scenario arose.