Need to understand the log cleanup in mysql

krishnanhcl · December 30, 2020, 4:22pm

Hi,

My my.cnf is

innodb_log_file_size = 100M

And

Also /var/lib/mysql

-rw-rw---- 1 mysql mysql 104857600 Nov 6 12:03 ib_logfile0
-rw-rw---- 1 mysql mysql 104857600 May 21 2012 ib_logfile1

I understand the log file is reached its maximum allocated size. I want to know if i delete it simply by “rm -rf ib_logfile0” and "rm -rf “ib_logfile0” will be any impact to the mysql functionality. Kindly advice.

I am very new to mysql, stuck with one issue, our sos berlin jobscheduler is not running due to mysql issue. Please help me.

Thanks in Advance!
Bala…

scott.nemes · December 30, 2020, 4:32pm

Hi krishnanhcl;

You should not have to touch/delete those files; MySQL handles all the logic it needs to read and write from those files; they never “fill up” in the way you are thinking. The innodb_log_file_size variable controls the size of those files; meaning if you set it to 512MB, then each of those files will automatically be 512MB, and will remain that way.

Removing the ib_logfiles from a running server is likely to result in data loss and/or corruption depending on how lucky or unlucky you are. Then when MySQL restarts (if it restarts), it will re-create those files. So do not do that.

What is the actual problem you are seeing with your application related to MySQL? Is there an error in the MySQL logs?

-Scott

krishnanhcl · December 30, 2020, 4:32pm

Dear Scott,

Thanks a lot for your reply!

We have SOS Berlin Job Scheduler instance in our production environment for which we use MySQL database. when we stop and start scheduler using script in cmd line we found it is not coming up instead, it through below error.

"// 2014-12-17 05:35:59.717 Job Scheduler -id=jsxprd host=(removed) port=4007

SCHEDULER-363 Error when creating SQL table SCHEDULER_VARIABLES: Z-JAVA-105 Java exception com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException(“Table ‘SCHEDULER_VARIABLES’ already exists”), method=executeUpdate [] / sos::scheduler::database::Database::create_table_when_needed
//

Kindly advice me.

Thanks
Bala…

scott.nemes · December 30, 2020, 4:32pm

Hi krishnanhcl;

According to your Java error message, the table “SCHEDULER_VARIABLES” already exists. So that is not an issue with your database; it is an issue with your code. You need to modify your code so that it handles that error and/or so it only attempts to create that table if it does not exist (i.e. check to see if the table is there first, or use the CREATE TABLE IF NOT EXISTS sql format).

-Scott

krishnanhcl · December 30, 2020, 4:32pm

Dear Scott,

We also doubt mysql DB when found the below syntax error. Also application team confirm it is not the problem with application. kindly help.

.17 05:35:59.631 0 28016.F7E336D0 {scheduler} [WARN] (Database) Z-JAVA-105 Java exception com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException(“Unknown table engine 'InnoDB’”), method=executeQuery []
.17 05:35:59.632 1 28016.F7E336D0 {scheduler} [info] (Database) SCHEDULER-909 Creating database table SCHEDULER_VARIABLES
.17 05:35:59.632 0 28016.F7E336D0 {scheduler} sos::scheduler::database::Transaction::execute COMMIT (sos::scheduler::database::Database::create_table_when_needed)
.17 05:35:59.632 0 28016.F7E336D0 {scheduler} sos::scheduler::database::Transaction::execute CREATE TABLE SCHEDULER_VARIABLES (name varchar(100) not null,wert integer,textwert varchar(250),primary key ( name )) ENGINE=InnoDB (sos::scheduler::database::Database::create_table_when_needed)
.17 05:35:59.632 0 28016.F7E336D0 [xc.insert 1, “com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException”]
.17 05:35:59.632 0 28016.F7E336D0 [xc.insert 2, “Table ‘SCHEDULER_VARIABLES’ already exists”]
.17 05:35:59.632 0 28016.F7E336D0 [xc.insert 3, “executeUpdate”]
.17 05:35:59.632 0 28016.F7E336D0 [xc.append “sos::scheduler::database::Database::create_table_when_needed”]
.17 05:35:59.632 0 28016.F7E336D0 [ERROR SCHEDULER-363 Error when creating SQL table SCHEDULER_VARIABLES: Z-JAVA-105 Java exception com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException(“Table ‘SCHEDULER_VARIABLES’ already exists”),

Thanks,
Bala…

scott.nemes · December 30, 2020, 4:32pm

Hi krishnanhcl;

To see if InnoDB is actually enabled and available, run the below from the MySQL command line interface (CLI):

SHOW ENGINES\G

If it is enabled, try to create a simple InnoDB table from the MySQL CLI. If that works, then you know the problem is at the application layer. If that does not work, then you know there is something wrong at the database layer.

So if InnoDB is not enabled and/or you cannot create an InnoDB table from the MySQL CLI, post your MySQL error log (not Java error log), my.cnf, and MySQL version (edit out any private info you do not want shared with the public).

-Scott

krishnanhcl · December 30, 2020, 4:32pm

Hi Scott,

Thanks a lot for your assistance.

We found our my.cnf file was rename with diff name. We stopped MySQL and renamed to correct one (my.cnf) and started MySQL. It works now. Application also works. Since we do not have necessary access to the server, we depend on vendor for any action.

Now i confirm we can close this issue. Thanks again for your inputs, it’s create a great hope for me.

-Bala…

scott.nemes · December 30, 2020, 4:32pm

Hi krishnanhcl;

Nice work! Glad you got it figured out. =)

-Scott