MySQL issue - Windows vs. Linux - Table name case seems to matter

RT Users
1

I’m looking to move all of my MySQL servers from Windows XP systems to
Linux. I’m doing testing now & so far all is well, except with RT. I
currently am running RT 4.0.17 on Ubuntu 12.04LTS with a MySQL 5.5x
database on Windows XP SP3. I am able to successfully backup & restore
the database on the Windows machine. However, when I restore it to the
new Linux box, I get the following error:
root@starbug:/home/admsjc/rt-4.0.17# service apache2 start

  • Starting web server apache2
    [Tue Aug 13 18:40:45 2013] [warning]: DBD::mysql::st execute failed:
    Table ‘rtdb.Users’ doesn’t exist at
    /usr/local/share/perl/5.14.2/DBIx/SearchBuilder/Handle.pm line 589.
    (/usr/local/share/perl/5.14.2/DBIx/SearchBuilder/Handle.pm:589)
    [Tue Aug 13 18:40:45 2013] [warning]: RT::Handle=HASH(0xb8986ab4)
    couldn’t execute the query ‘SELECT * FROM Users WHERE Name = ?’ at
    /usr/local/share/perl/5.14.2/DBIx/SearchBuilder/Handle.pm line 602.

DBIx::SearchBuilder::Handle::SimpleQuery(‘RT::Handle=HASH(0xb8986ab4)’,
‘SELECT * FROM Users WHERE Name = ?’, ‘RT_System’) called at
/usr/local/share/perl/5.14.2/DBIx/SearchBuilder/Record.pm line 1234

DBIx::SearchBuilder::Record::_LoadFromSQL(‘RT::CurrentUser=HASH(0xb895ab
10)’, ‘SELECT * FROM Users WHERE Name = ?’, ‘RT_System’) called at
/usr/local/share/perl/5.14.2/DBIx/SearchBuilder/Record.pm line 1153

DBIx::SearchBuilder::Record::LoadByCols(‘RT::CurrentUser=HASH(0xb895ab10
)’, ‘Name’, ‘RT_System’) called at
/usr/local/share/perl/5.14.2/DBIx/SearchBuilder/Record/Cachable.pm line
120

DBIx::SearchBuilder::Record::Cachable::LoadByCols(‘RT::CurrentUser=HASH(
0xb895ab10)’, ‘Name’, ‘RT_System’) called at
/opt/rt4/sbin/…/lib/RT/Record.pm line 375
RT::Record::LoadByCols(‘RT::CurrentUser=HASH(0xb895ab10)’,
‘Name’, ‘RT_System’) called at
/usr/local/share/perl/5.14.2/DBIx/SearchBuilder/Record.pm line 1098

DBIx::SearchBuilder::Record::LoadByCol('RT::CurrentUser=HASH(0xb895ab10)
', ‘Name’, ‘RT_System’) called at /opt/rt4/sbin/…/lib/RT/User.pm line
479
RT::User::Load(‘RT::CurrentUser=HASH(0xb895ab10)’, ‘RT_System’)
called at /opt/rt4/sbin/…/lib/RT/Handle.pm line 238
RT::Handle::CheckIntegrity(‘RT::Handle’) called at
/opt/rt4/sbin/rt-server line 99
require /opt/rt4/sbin/rt-server called at (eval 6) line 3
eval 'package Plack::Sandbox::_2fopt_2frt4_2fsbin_2frt_2dserver;
{
my $app = do $_file;
if ( !$app && ( my $error = $@ || $! )) { die $error; }
$app;
}

;’ called at /usr/share/perl5/Plack/Util.pm line 118
Plack::Util::_load_sandbox(‘/opt/rt4/sbin/rt-server’) called at
/usr/share/perl5/Plack/Util.pm line 155
Plack::Util::load_psgi(‘/opt/rt4/sbin/rt-server’) called at
/usr/share/perl5/Plack/Handler/Apache2.pm line 32
Plack::Handler::Apache2::load_app(‘Plack::Handler::Apache2’,
‘/opt/rt4/sbin/rt-server’) called at
/usr/share/perl5/Plack/Handler/Apache2.pm line 24
Plack::Handler::Apache2::preload(‘Plack::Handler::Apache2’,
‘/opt/rt4/sbin/rt-server’) called at
/etc/apache2/sites-enabled/000-default line 20
eval 'package
Apache2::ReadConfig::etc::apache2::sites_enabled::000_default::line_19;
#line 19 /etc/apache2/sites-enabled/000-default
use Plack::Handler::Apache2;
Plack::Handler::Apache2->preload(“/opt/rt4/sbin/rt-server”);

;’ called at /opt/rt4/sbin/rt-server line 0
(/usr/local/share/perl/5.14.2/Carp.pm:103)
[Tue Aug 13 18:40:45 2013] [warning]: Use of uninitialized value in
concatenation (.) or string at /opt/rt4/sbin/…/lib/RT/Handle.pm line
240. (/opt/rt4/sbin/…/lib/RT/Handle.pm:239)

RT couldn’t connect to the database where tickets are stored.
If this is a new installation of RT, you should visit the URL below
to configure RT and initialize your database.

If this is an existing RT installation, this may indicate a database
connectivity problem.

The error RT got back when trying to connect to your database was:

Couldn’t find RT_System user in the DB ‘’
(more error messages after this)

I am able to use the MySQL client tools on the Ubuntu Server running RT
to successfully access the RT database. There is something funny I
noticed. If I try to execute: SELECT * FROM Users WHERE Name = ?’

I get: ERROR 1146 (42S02): Table ‘rtdb.Users’ doesn’t exist

If I then go into the RT database & change the “user” table to “User”,
RT goes further & has the same error for the ticket tables. Did I do
something wrong with my backup? Or is this a Windows/Linux issue I’ve
never seen before?

Stephen Cena
QVII
MIS/IT Dept
850 Hudson Ave.
Rochester, NY. 14620
585-544-0450 x300
sjc@qvii.com
“Thank you for helping us help you help us all.”
–For email related issues, please contact postmaster@qvii.com

2

Stephen,

MySQL does not maintain internal dictionary where it could keep the list of
tables (but InnoDB does for its own purpose!). Instead, MySQL relies on the
information from the underlying file system. Since Windows does not care
about case of file names, ‘users.frm’, ‘Users.frm’ and ‘UsErS.frm’ are all
the same file, in MySQL ‘users’, ‘Users’ and ‘USERS’ will all be the same
table. On the other hand, Linux has case sensitive file names, so
‘users.frm’ and ‘Users.frm’ are two different files. In this case ‘users’
and ‘Users’ also become two different tables.

Your problem is easy to fix, but the solution will depend on how the MySQL
server on Windows was configured and how the tables were created both in
MySQL and on disk. Typically either performing dump & restore or setting
lower_case_table_names=1 option (or both) on Linux is enough, however in
certain circumstances it may not be.

Please refer to this page
http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html as
it will most likely contain the answers you need.

Maciek