Archive for November 21st, 2007

How to increase an InnoDB log file size

Written by belle
Posted November 21, 2007 at 4:10 am

(MySQL 5.0)
Had a question from a student:

Problem:
He is trying to increase the innodb log file size by altering his .ini file.
After he makes the changes to the .ini file, he attempts to restart the server but fails.

Error messages indicate:
-ib_logfile0 has a different size
-can’t initialize database

Solution:
The solution was posted by Jay Pipes in the MySQL forum (http://forums.mysql.com/read.php?22,32004,32014#msg-32014). Essentially MySQL tries to look for the log file that had the original size, doesn’t find it, aborts the restart.

Solution is to rename the log files. MySQL will recreate the appropriate log files with the appropriate sizes.

#assuming you have a Linux system and you installed MySQL using an RPM#otherwise your MySQL install might be in /usr/local#if you can't figure out where your install folder it, "find" is your friend $/etc/init.d/mysql stop$mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak$mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak$/etc/init.d/mysql start$

Rating: 10.0/10 (1 vote cast)

Rating: +1 (from 1 vote)

No Comments

Filed under: MySQL

How to call one stored procedure from another stored procedure without displaying previous stored procedure’s results

Written by belle
Posted at 3:32 am

(SQL Server)
Had a question from a student:

Problem:
He needs to create a temporary script while they migrate from an old database to SQL Server. He needs to call a sproc (SP1) from another sproc (SP2). SP1 returns a result set; SP2 must return only a scalar value (a COUNT, an AVG).

Problematic Query:

CREATE PROC SP1
AS
SELECT au_lnameFROM authors
GO
CREATE PROC SP2
AS
DECLARE @count INT
--this is problematic because this EXEC 
--displays the results of SP2
EXEC SP1
SELECT @count=COUNT(*)FROM authorsGO

Workable Solution:

CREATE PROC SP1
AS
SELECT au_lname FROM authors
GO
CREATE PROC SP2
AS -- don't show number of rows affected, we don't need it
SET NOCOUNT ON
--create a temporary table
--for purposes of my student's issue, this is fine
--you need to be careful when creating 
--temporary tables in sprocs, though, you need to 
--remember there are performance tradeoffs
CREATE TABLE #tmp 
(   
   au_lname VARCHAR(20)
)
--do an INSERT..EXEC
INSERT #tmp (au_lname)
EXEC SP1
--display number of records in the temporary table
SELECT COUNT(*) FROM #tmp
GO
--to test, execute SP2EXEC SP2

Rating: 0.0/10 (0 votes cast)

Rating: -1 (from 1 vote)

No Comments

Filed under: Uncategorized