Fix – The selected file was generated by mysqldump and cannot be restored by this application.

MYSQL

How to fix the-selected-file-was-generated-by-mysqldump-and-cannot-be-restored-by-this-application error.

This post is here more of a reminder to myself. However I’m hoping that others will find it useful also.

I was testing out a piece of code which needed a new MYSQL InnoDB restored.

I had a slight issue ad when I attempted to restore the DB via the MySQL Administrator tool I got the following error message.

“The selected file was generated by mysqldump and cannot be restored by this application.”

This error is given usually when the Backup was taken via a batch job (or just a user who is using the command line).

Because of this the only way to restore the DB is again via the command line.

So carry out the following steps.

Firstly make sure that your .SQL backup file has the Database at the top

so it should read

Use DataBaseName;

then

find the MySQL.exe file, (this will usually be stored in the installation bin folder)

then run the following using the command line.

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql.exe -uusername -ppassword   < “C:\DB Backups\nameofbackupfile.sql”

The -uusername should be something like -uroot

and the -ppassword should be something like -psecret

Hope it helps.

Martin H

MySQL Search for text inside all Stored Procedures

MySQL Logo

This little piece of MySQL Code I wrote a month or so ago.

I had a defect and was trying to provide some decent feedback to the developer  on Test-Track (our defect logging system).

I knew that the defect was in a stored procedure and to attempt to run profiler in Mysql can be a bit of a pain so I figured that I’d work out a way to search in every Stroed Proc for the bit of code causing the error.

I knew that the error was caused by the peice of code calling “Videos”.

I looked around on the net but I couldn’t find anything that suited.  I did find one other piece of sample code but I found that that only search inside the first 256 chars of the Stored Procedure and I needed something that would search inside the whole of the procedure no matter how long it was.

Once again it’s a nice and simple piece of code.

– Author : Martin Hall
– Date 09th April 2010
– Search for Text inside a MySQL Stored Proc
– MYSQL Version
– See the Like Query for an example of Use.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE “%video%”
Order by Routine_Name;

Hope you find it useful.

any issues then let me know.

Martin H

MYSQL Search for Table or Column names in a Database

mysql logo

You may remember that about three weeks ago I posted a handy script to search for the names of Tables and Columns in a Microsoft SQL database.

Well now today it’s the turn of MySql.

Just a nice and simple script as before however this time there are two seperate scripts one for tables and one for columns names.

– Author : Martin Hall
– Date 09th April 2010
– Search Table or Column for text
– MYSQL Version
– Top query for Table Names and bottom query for column names

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME like ‘%users%’
Order by table_name asc;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_NAME like ‘%video%’
Order by column_name asc;

Enjoy.

Martin H

Search Stored Procedures for string/text

SQL Code

OK so just like the post below about searching for columns or table names. This post is all about how to search inside Stored Procedures for strings / text.

This can be really useful especially if you know a piece of code is called but your not sure which stored proc calls it.

As before we are going to use the ‘users’ string to keep consistency.

I had searched high and low for this code on the internet but couldn’t find anything. All of the examples I found only searched in the first 255 characters of stored procedure which is OK if its a declaration your looking for however not too good if your stored proc has unions and has declarations half way down the procedure. So I decided to write my own.

Anyway on with the script. (really nice and simple)

– Author : Martin Hall
– Date 17th February 2010
– Search Stored Procedures for Text. (see Definition like)

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE ‘%users%’

Hope you Enjoy

Check if Column or Table exists in SQL DB

SQL Code

This is a very useful script which I use all of the time. Especially if there are issues around Database Schema maps being kept up to date.

This SQL script will take a string as an input and output all of the column names and table names where that string is used.

So for instance you have a column called username however its not in the obvious place which would be the users table.  You know the column exists as you’ve seen it trace outputs or the developer has said to you “check the users column.

You would run the below script and would get a list of any Tables or Columns with a name like ‘users’

– Author : Martin Hall
– Date 12th June 2009
– Search Table or Column for text
– To search for a TableName then just change the “where syscolumns” to “where sysobjects”)

SELECT     syscolumns.name AS [Col Name], sysobjects.name AS [Table Name]
FROM         syscolumns INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id
WHERE     (syscolumns.name LIKE N’%users%’)
Order by sysobjects.name

Hope you find it useful.

Tags: SQL search Column Names

Increase All Data in an SQL DateTime Column by a Year.

I was recently testing a rarely used piece of functionality on one of the sites which belong to the company I work for. The test DB was very old as releases for this part of the system come very few and far between.

The system is written in such a way that data is only returned to the end user for data for a the last 12 months , (1 Year). The database in question had not been updated for about 18 months and it would have taken about 5 days for me to get a decent backup from the live system restored to test.

The data in the Test Database was valid it was just a little too old to be returned via the front User Interface (GUI).

What I needed was a nice and easy way to bring the date column in the database forward by year.

Obviously for good practice I also requested the live DB dump to be sent over just to completeness of testing. But to test this piece of functionality it wasn’t needed.

The following is the SQL query (Transact-SQL) I cobbled together to get the job done.

It will do a select first to see if you have any data in a particular financial year (2009 and 2010).

It will then update all 2009 data to read 2010. So 01-Feb-2009 becomes 01-Feb-2010 and 23rd June 2009 becomes 23rd June 2010.

It will then carry out the same select query it did before. If all has gone well the 2009 data should be blank and the 2010 field should be populated.

In any update script I write I always input a select before the update and the same select after the update. This way I and any other users using my script can see if the update has worked.

Now the code.

SELECT     YEAR(ColumnName) AS ColumnNameYear
FROM         TableName
GROUP BY YEAR(ColumnName)
HAVING      (YEAR(ColumnName) = 2010) OR
(YEAR(ColumnName) = 2009)

UPDATE    TableName
Set  ColumnName = dateadd(yy,1, ColumnName)
WHERE     DATEPART(year, ColumnName) = ’2009′

SELECT     YEAR(ColumnName) AS ColumnNameYear
FROM         TableName
GROUP BY YEAR(ColumnName)
HAVING      (YEAR(ColumnName) = 2010) OR
(YEAR(ColumnName) = 2009)

Hope you find it useful.

And I’m sure there are many more ways of coding this much simplier but I’m a tester and not a coder and for me it works.