Solved

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

Posted on 2011-02-20
11
3,721 Views
Last Modified: 2013-11-15
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 ?
0
Comment
Question by:BToTheAToTheBABA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 34940991
before using SOURCE use tee

mysql>tee `file_name' ;
after you finish ur SOURCE command,
mysql>notee;
0
 
LVL 26

Expert Comment

by:ushastry
ID: 34941149
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
 

Author Comment

by:BToTheAToTheBABA
ID: 34941169
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 26

Expert Comment

by:ushastry
ID: 34941238
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
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 34941457
I assume warnings will be logged in to error logs by default...
0
 
LVL 4

Expert Comment

by:ziycon
ID: 34941573
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
 
LVL 26

Accepted Solution

by:
ushastry earned 500 total points
ID: 34941605
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
 
LVL 26

Expert Comment

by:ushastry
ID: 34941619
@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
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 34941639
@ushastry sorry I overlooked that !!
0
 
LVL 26

Expert Comment

by:ushastry
ID: 34941681
@theGhost_k8:  

:-)... np!
0
 

Author Closing Comment

by:BToTheAToTheBABA
ID: 34942026
Thank You
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do uses indexes to maximize MySQL Searches 14 69
best imaging disk tool 15 60
moto 4g plus phone lockout time 4 36
Read some really old .pic files??? 18 62
If your app took Google’s lash recently, here are the 5 most likely reasons.
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question