Case mismatch on table names

[TuemmlerKon]

Hey There,

I am using Bacula Version 5.2.6 and latest Reportula. Cron and userfrontend displaying following error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bacula.client' doesn't exist (SQL: select * from `client`) 

My tables all starts with an uppercase letter (e.g. my client table is Client). I don't want to change the table names because I don't know what kind of problems that causes on bacula itself. Is there any idea or fix for that available?

[oliveiraped]

Hi this is not the best solution but it works in app/models/CLient.php model file change " protected $table = 'client'; " to "protected $table = 'Client';".

or the other thing you do is change on MySql database settings this:

Try adding/editing "lower_case_table_names = 2" in my.ini or my.cnf .

You can google'it to see more information about the issue.

Before do that backup you database.

[TuemmlerKon]

Is that my fault that my table names always starting uppercase or is this a bacula default?

[oliveiraped]

I had that problem when i migrated from MySQL database to PostgreSQL, database on Reportula 1 development. On Reportula 2 i used Laravel framework that has ORM component, that abstracts you from the database. I don't think it's your fault. May be it's Bacula database creation script it different from MySQL to PostgreSQL, but i am not sure.

[TuemmlerKon]

Okay thanks! I fixed the table names in the different models. But now I get the following error:

[k0ssi]

same error here, i tried to edit Tablenames manualy in .php files but im unable to find each string. Setting "lower_case_table_names = 2" want work in my case.

[ricardo777]

Same issue for me with clean bacula installation on Ubuntu 14.04 setting "lower_case_table_names = 2" does not work for me.

Then I get the error in mysql SQL show index from BaseFiles failed : Table 'bacula.basefiles' doesn't exist

[oliveiraped]

Open terminal and edit /etc/mysql/my.cnf sudo nano /etc/mysql/my.cnf

Underneath the [mysqld] section.add: lower_case_table_names = 1

Restart mysql sudo /etc/init.d/mysql restart

Then check it here: mysqladmin -u root -p variables

Try with 1, if does not work you have to change o table names on the models app files !

[westphala]

1 also do not work for me. do you plan to create a patch of the code for that?

[asyd]

Hi,

imho the application should be aware of table case name, since Bacula create 'Client', (and not 'client'), reportula should use 'Client' by default.

Changing a global server parameter for an application sounds a bit stange.

[oliveiraped]

Yes its true, but in my case i have my infrastruture in Portgresql Database, and i am developing based on postgres database, on mysql i did that workaround.

Anyone can contribute to solve that problem.

[asyd]

Ok.

Well, sounds like changing model is not enough, after change every bacula related table names in models, i still have an exception:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bacula.file' doesn't exist (SQL: select count(*) AS filesNumber fromfile)

Why the name defined in protected table is not used?

[oliveiraped]

http://stackoverflow.com/questions/20881700/laravel-4-case-sensitive-database-columns

can check this maybe that will helps

[westphala]

Same here i changed protected $table = 'client'; to protected $table = 'Client'; and get still this error: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bacula.client' doesn't exist (SQL: select * from Client) do i must change this entry also in other files as under app/models?

[oliveiraped]

My Mysql Database

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 674 Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)

My Postgres Database

                                                  version                                                      

---

PostgreSQL 8.4.21 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit (1 row)

[asyd]

I think you have lower_case_table_names = 1 when you create the bacula database, right?

[oliveiraped]

Problably i had but i am not certain ...

[asyd]

hmm, which version of bacula are you using?

[oliveiraped]

The latest comunity version 7.0.4, but i am using bacula since Version 3 só i am being upgrading the database since then.

[asyd]

Is mysql support was tested? I create tables with lower_case_table_names = 1, then start to use bacula, but after only two jobs, when hitting the dashboard I have:

ErrorException Undefined index: jobfiles

in reportula/vendor/laravel/framework/src/Illuminate/Support/helpers.php

[Raphiusks]

Hi there.

I'm having the exact same issue as asyd reported. Do you happen to have any suggestions?

Was really looking forward to trying reportula. Obrigado!

[zenjive]

I'm having the same issue on Ubuntu 14.04. I tried modifying the app/models files, and found that more needed modifying in app/controllers and app/command/BaculaStatsCommand.php. I never could get it to fully work, but what I have seen that does work is great! I eventually decided to revert it back to the original and try setting lower_case_table_names = 1. This really made Bacula unhappy.

Fortunately, this is on my home machine, so I have free will to make changes. If I were to set lower_case_table_names = 1 and then start with a fresh Bacula db, would Bacula work ok then? It seems like it would, since Bacula can run with case-insensitive tables on Windows.

According to https://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html, it seems that you cannot use lower_case_table_names = 2 on a case-sensitive filesystem (i.e. Unix/Linux). 0 is default on Linux, but a setting of 1 is valid. When setting to 1, it says you have to rename tables, but I think that will break an existing Bacula installation. So, that's why I think changing the setting to 1 and then either reinstalling Bacula or refreshing the db might work.

Can anyone confirm this? I've been using a combination of bacula-web and bconsole in an ssh session to the server. I'd really like to use Reportula, instead.

[oliveiraped]

Convert your Database to Postgres its better for Bacula and for Reportula

[zenjive]

I have other DBs in mysql that I don't want to (or can't) move to pgsql. Fortunately, pgsql and mysql can run side by side. Also, there's a tutorial on bacula.org for migrating from mysql to pgsql. So, I'll give it a try...

[zenjive]

Ok, I reinstalled Bacula with pgsql support and now Reportula works just fine. I decided to start with a fresh Bacula db instead of migrating the existing one from mysql. It only had been running for a week or two, so it wasn't a hassle to start fresh. The stats script is giving me some errors, though:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "18.65" (SQL: select * from "hoursstats" where "data" = 2014-09-27 and "server" = myserver and "bytes" = 201207964102 and "starttime" = 2014-09-26 22:00:02 and "endtime" = 2014-09-27 16:39:37 and "timediff" = 18:39:31 and "hoursdiff" = 18.65 and "hourbytes" = 10788630782.9491 limit 1)

I think I saw another post about the script problem, so I'll go look there...

[oliveiraped]

Did you already colected some statistics ?

[zenjive]

It wasn't updating, but I have corrected the settings and everything appears to be in order.

Thanks!

[oliveiraped]

Can put wath you corrected on github for the comunity to see. Thank you

[zenjive]

Yes, of course, sorry... I tweaked my retension settings following the Bacula manual, from these chapters: http://www.bacula.org/5.2.x-manuals/en/main/main/Using_Bacula_catalog_grab.html http://www.bacula.org/5.2.x-manuals/en/main/main/Basic_Volume_Management.html http://www.bacula.org/5.2.x-manuals/en/main/main/Catalog_Maintenance.html#SECTION004210000000000000000

Your own settings may vary, but it is important to set your Job retention period to longer than your File retention period. Also, the Statistics Retention period does not affect the ability to restore files, it is strictly for stats. File and Job retention does affect restores, so plan carefully! It is also important to add the stats update commands to your catalog backup job, mentioned at the end of the statistics chapter.

Once I had everything set properly and restarted the daemons, I ran "update stats" manually from bconsole. I left off the "days=xxx" setting in the command, which seems to update stats from all jobs in the db. After the stats were updated, I manually ran the Reportula stats command and now there is a nice graph in Reportula!

[oliveiraped]

Okay Thank You i will put that on Reportula Manual !

[oliveiraped]

Case mismatch on table names its finally resolved on Reportula version 2.0.9

[kahun]

Hi

I'm still getting the same error with Reportula 2.0.10

[zenjive]

I realize it's only a workaround, but switching to PostgreSQL for the Bacula DB not only stops the case mismatch problem, but it seems that Bacula runs a little smoother. I still use MySQL for other applications on the same machine with no problems.

[oliveiraped]

Você foi convidado!

O seu amigo Pedro permitiu-lhe ganhar €42 na sua primeira viagem pela Airbnb, a melhor maneira de viajar. Não se esqueça de lhe agradecer!

Valor mínimo de reserva €169.

Reivindique o seu crédito de €42 http://www.airbnb.pt/c/poliveira51?euid=c7678de2-aa06-f708-4bfa-152115bafe97&ri=11803655&s=1

Obrigado, A Equipa da Airbnb

P.O. Box 410807, San Francisco, CA 94121

Cancelar subscrição https://www.airbnb.com/email/unsubscribe?token=eyJlbWFpbF9hZGRyZXNzIjoicmVwbHkraS0zMTA2NTUzNi0wNzI2ZTU5YjRmYTBiNTZlYjA5YTI5MTE3YjFmYzFmNTI3NGI3ZmY0LTY3NjAwMEByZXBseS5naXRodWIuY29tIiwiY2F0ZWdvcnkiOiJyZWZlcnJhbHMiLCJ0ZW1wbGF0ZSI6Imludml0YXRpb24iLCJ1dWlkIjoiYzc2NzhkZTItYWEwNi1mNzA4LTRiZmEtMTUyMTE1YmFmZTk3In0=&mac=lf9JgkU5HoL59sA4aXPCqBLn9cI=

Airbnb