Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Wednesday, April 15, 2020

MySQL JDBC Driver - Time Zone Issue

MySQL JDBC Driver - timezone issue or the server timezone value 'unknown' is unrecognized or represents more than one value, etc. occurred then following step would help to resolve: 

Just add the following line end of JDBC connection string,
?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
Complete connection string below,
jdbc:mysql://hostname_or_ip:port/db_name?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

MySQL InnoDB ib_logfile0, ib_logfile1 & ibdata1 file size issue


Following steps would solve the issue:

Step-1: MySQLDump all databases into a SQL text file (e.g. SQLDataBackup.sql)

Step-2: Drop all databases (except mysql, information_schema and performance_schema schemas)

Step-3: Shutdown MySQL

Step-4: Add the following lines to (/etc/my.cnf if linux) or (C:\xampp\mysql\data\my.ini if xampp in windows)

[mysqld]
innodb_file_per_table 
innodb_flush_method=O_DIRECT 
innodb_log_file_size=1G 
innodb_buffer_pool_size=4G 
innodb_data_file_path=ibdata1:10M:autoextend 

Note: Whatever you set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

Step-5: Delete ibdata1, ib_logfile0 and ib_logfile1  

There should only be the mysql schema in data location.

Step-6: Restart MySQL

This will recreate ibdata1 at 10MB (do not configure the option) , ib_logfile0 and ib_logfile1 at 1G each

Step-7: Reload SQLDataBackup.sql into MySQL

ibdata1 will grow but only contain table metadata and intermittent MVCC data. Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into path/mysql/data, you will see two files representing the table

mytable.frm (Storage Engine Header) 
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable) 
ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in (/etc/my.cnf if linux) or (C:\xampp\mysql\data\my.ini if xampp in windows), you can run OPTIMIZE TABLE mydb.mytable OR ALTER TABLE mydb.mytable ENGINE=InnoDB; and the file will actually shrink.

Wednesday, January 14, 2015

Simple solution for Crystal Report SP12 with Visual Studio 2013 on 64 bit OS

First of all, many many thanks to Don Williams (http://scn.sap.com/people/don.williams)

Issue: 
An unhandled exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll

Additional information: Could not load file or assembly 'file:///C:\Program Files (x86)\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Common\SAP BusinessObjects Enterprise XI 4.0\win32_x86\dotnet1\crdb_adoplus.dll' or one of its dependencies. The system cannot find the file specified.

Solution: 
Simply add useLegacyV2RuntimeActivationPolicy="true" in startup tag in app.config file.

example: <startup useLegacyV2RuntimeActivationPolicy="true">

Monday, August 11, 2014

Error 10061 or 1067 when starting MySQL Service

Steps to mitigate…
1. stop the MySQL service
2. Delete both ib_logfile0 and ib_logfile1
3. restart MySQL service
both the files ib_logfile0 & ib_logfile1 are loated in MySQL data folder…

This is just work-around solution, assuming there are no databases uisng InnoDB engine.
Otherwise, those databases will not work. Please take proper backup/sql dump before applying above procedure.