"Duplicate entry" errors between mb and mB publicIDs?

[jordi-domingo]

Hi all,

I've just found out that MySQL is case insensitive in some cases (i.e.: select). Could it be that this affects the saving of automatic mb/mB magnitudes in the database? Because the public IDs are the same, except for the capitalization of the 'b':

orgID#netMag.mb orgID#netMag.mB

We were doing some tests, using both mb and mB in automatic magnitudes (scmag), and there was something weird with the preferred magnitude. Although there is a preferred magnitude ID ("orgID#netMag.mb") of type mb, neither scolv or scxmldump show that mb magnitude. There is, however, a mB magnitude associated to the event, as expected, which is not preferred.

[jsaul]

Hello, I recommend to make an XML dump (using scxmldump) of the event and see if there is anything unusual.

[gempa-jabe]

Check the database table collation. It must not be ci_ or _ci. Use:

show create table Magnitude;

or

show table status where name='Magnitude';

[jordi-domingo]

It must not be ci_ or _ci

It is actually "latin1_swedish_ci", which is the default value. Shall I change it? Only for Magnitude table, or all tables?

I recommend to make an XML dump

See attached a dump of one of the events with this behaviour: knmi2017wuhj.xml.txt Note the following:

$ grep netMag knmi2017wuhj.xml
      <magnitude publicID="Origin#20171121042322.388532.283313#netMag.MLv">
      <magnitude publicID="Origin#20171121042322.388532.283313#netMag.mB">
      <magnitude publicID="Origin#20171121042322.388532.283313#netMag.Mw(mB)">
      <magnitude publicID="Origin#20171121042322.388532.283313#netMag.Mwp">
      <magnitude publicID="Origin#20171121042322.388532.283313#netMag.Mw(Mwp)">
      <magnitude publicID="Origin#20171121042649.055482.283448#netMag.MLv">
      <magnitude publicID="Origin#20171121042649.055482.283448#netMag.mB">
      <magnitude publicID="Origin#20171121042649.055482.283448#netMag.Mw(mB)">
      <magnitude publicID="Origin#20171121042649.055482.283448#netMag.Mwp">
      <magnitude publicID="Origin#20171121042649.055482.283448#netMag.Mw(Mwp)">
      <preferredMagnitudeID>Origin#20171121042649.055482.283448#netMag.mb</preferredMagnitudeID>

[gempa-jabe]

It is actually "latin1_swedish_ci", which is the default value. Shall I change it? Only for Magnitude table, or all tables?

Yes, change it for all tables. seiscomp setup would have created the correct collation.

[jordi-domingo]

I've changed the charset/collation for all tables to utf8/utf8_bin.

It seems to work well now, as it stores correctly both mb and mB magnitudes, and the preferred one is properly selected.

Thanks for the solution!