Mysql Import db dump Warnings/ Errors write in a file.

Hi

Im importing my database from mysql server version 5.0.4 to new mysql 5.5.8.

I have some 300 tables & I have used mysqldump to get the full dbSQL files.

Now in the new mysql server console Im choosing database & importing data from full sql files like this.

mysql>  source ./fulldump.sql

While doing this I'm getting a lot of warnings particularly in the middle of import.
But since this import runs fast in console window & the final lines of import has no warnings.
So I couldn't see the full warnings/messages of the import.(Cant issue show warnings command at right place)
It doesn't contain errors, but it has ONLY warnings.

HOW SHOULD I REDIRECT THE CONTENT OF WARNINGS TO A LOG FILE WHILE IMPORTING ?
BToTheAToTheBABAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

theGhost_k8Database ConsultantCommented:
before using SOURCE use tee

mysql>tee `file_name' ;
after you finish ur SOURCE command,
mysql>notee;
0
UmeshMySQL Principle Technical Support EngineerCommented:
Hi,

Instead of mysql prompt most of the people prefer shell prompt while importing SQL(logical dumps)/Text(load files) ....prefered mostly on UNIX* like systems..which applies to CYGWIN too.. Just to ensure everything is logged incase mysql prompt hangs

/path/to/mysql -uUserName -pPassword DbName < /path/to/filename >>/path/to/error_warn.log 2>&1

## Change the /path/to/ to reflect actual paths in above command
0
BToTheAToTheBABAAuthor Commented:
hi,

Thank you for the reply.

This method

/mysql  < sqlfile >> /logfile

Prints only the number of warnings.

The actual warning message is viewed by the command
mysql> show warnings;

When ever a warning occurs I want to issue a "show warnings" command & redirect the output to log file.
How to do this ?

Thanks again.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

UmeshMySQL Principle Technical Support EngineerCommented:
Giving a try on this.. let u know the same
But As per my knowledge, there isn't any settings in MySQL which logs SQL statement warnings/errors to any log file(ofcourse mysql does provde a option to create a group in optional file to include [warnings] but it actually logs aborted connects etc warnings and not the SQL statement errors encountered whil loading the data)
0
theGhost_k8Database ConsultantCommented:
I assume warnings will be logged in to error logs by default...
0
ziyconCommented:
That is all that should be needed, it will output any text to the logfile that is created during running on the current command.

/mysql dbname < sqlfile >> logfile.txt
0
UmeshMySQL Principle Technical Support EngineerCommented:
Okay, here the testcase which does logs all the warnings...


/path/to/mysql --show-warnings -uUserName -pPassword DbName < /path/to/filename >>/path/to/error_warn.log 2>&1

## Change the /path/to/ to reflect actual paths in above command

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UmeshMySQL Principle Technical Support EngineerCommented:
@theGhost_k8:

Yes, MySQL logs all the warnings to the error log but only with respect to the server utlilities or config file related matters such as if you include any deprcated syntax in optional file or the settings related to replication such as if you forget to give relay file/index details etc.

Unfortuately, MySQL doesn't record on file any warnings occured at the time of data load(logical or text etc)... Long back I saw MySQL reporting even failed SQL attempts on Update query log(now General query).
0
theGhost_k8Database ConsultantCommented:
@ushastry sorry I overlooked that !!
0
UmeshMySQL Principle Technical Support EngineerCommented:
@theGhost_k8:  

:-)... np!
0
BToTheAToTheBABAAuthor Commented:
Thank You
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.