Fresh 0.6.5 error

[John Adams]

LE, using the SF zip package only, not SVN, I am getting an error updating revive_points table due to some collation issue? Never seen that before.

Code: Select all

[Error] Error in updating tables query '
update revive_points, zones set zone_id=zones.id where zones.description = zone_name': #1267: Illegal mix of collations latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='

[Error]         Attempting to update database table 'revive_points' ... FAILED!

Can you check to see that this is not going to be a problem for anyone else on a 0.6.5 release EXE?

Thanks

[LethalEncounter]

Try turning off your strict mode stuff. I never got that error.

[John Adams]

Bah! Of course, being a loosey goosey everything works perfectly.

I am trying to run worst-case-scenario here to ensure I catch everything that we should consider to be a problem. Here is the verbose query, if you are interested.

Code: Select all

[Error] Login reports that the latest version of the 'revive_points' is 65000, you are using 0.  The query that will run to update your table is:
drop table if exists revive_points

create table revive_points (id int(11) unsigned not null primary key auto_increment, location_name varchar(64) not null, zone_name varchar(64) not null default 'antonica', safe_x float not null default 0, safe_y float not null default 0, safe_z float not null default 0)
ALTER TABLE `revive_points` ADD `heading` FLOAT DEFAULT '0' NOT NULL AFTER `safe_z`
ALTER TABLE `revive_points` ADD `respawn_zone` VARCHAR(64) DEFAULT 'antonica' NOT NULL AFTER `zone_name`
alter table `revive_points` add column `zone_id` int(10) UNSIGNED DEFAULT '12' NOT NULL after `zone_name`, add column `respawn_zone_id` int(10) UNSIGNED DEFAULT '12' NOT NULL after `respawn_zone`

update revive_points, zones set zone_id=zones.id where zones.description = zone_name

update revive_points, zones set respawn_zone_id=zones.id where zones.description = respawn_zone

[Error] Error in updating tables query '
update revive_points, zones set zone_id=zones.id where zones.description = zone_name': #1267: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='
[Error]         Attempting to update database table 'revive_points' ... FAILED!
[Status]        Table Update complete.
[Status]        Processing Data Updates.
[Error] The following queries will be inserted into your database to bring it up
 to date:
replace into revive_points (`id`,`location_name`,`zone_name`,`zone_id`,`respawn_zone`,`respawn_zone_id`,`safe_x`,`safe_y`,`safe_z`,`heading`)  values(1,'Near North Qeynos Gates','Antonica',12,'antonica',12,436.2,-37.5,819.56,0)

[Status]        Attempting to update database table 'revive_points' data ... SUCCESS!
[Status]        Data Update complete.

And no, I won't turn off Strict just to make it work lol... that would be cheating =)

Does this just mean your collation at setup was different than mine? If so, we can just document it in the Wiki that collations and STRICT mode need to be massaged together, since we cannot ever predict what everyones collation or server setups will be.

[John Adams]

Ahh I see it. Some of the tables are created with latin_swedish_ci, some with latin_general_ci, and my server is set to latin_swedish_ci... so any insert into's from a conflicting collation will whine and snivel.

That's easy to fix, stand down, big guy. It's a DB problem.

Fix:

Code: Select all

alter table `revive_points`  Engine=InnoDB checksum=1 auto_increment=1 comment='' delay_key_write=1 row_format=dynamic charset=latin1 collate=latin1_swedish_ci ;

[LethalEncounter]

I think what that is saying is that the collations of the zones and revive_points tables are not the same. One is latin1_swedish_ci and the other is IMPLICIT (whatever that is).

[LethalEncounter]

Ahh I see it. Some of the tables are created with latin_swedish_ci, some with latin_general_ci, and my server is set to latin_swedish_ci... so any insert into's from a conflicting collation will whine and snivel.

That's easy to fix, stand down, big guy. It's a DB problem.

Of course it is

[John Adams]

Implicit (I believe) means "you are not telling me what collation to use, so I am using the one I get from MySQL ini for this instance".

Implied.

Anyway, don't you start! We've had 1 DB bug in a year. But who's counting ~runs~

[Cynnar]

Flagged to be archived.