Table Schame for raddacct & radacctold(Both have the same structure just the engine is different)
CREATE TABLE `radacctold` (
`RadAcctId` bigint(21) NOT NULL,
`AcctSessionId` varchar(128) default NULL,
`AcctUniqueId` varchar(32) NOT NULL default '',
`UserName` varchar(64) NOT NULL default '',
`Realm` varchar(64) default '',
`NASIPAddress` varchar(15) NOT NULL default '',
`NASPortId` int(12) default NULL,
`NASPortType` varchar(32) default NULL,
`AcctStartTime` datetime NOT NULL default '0000-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL default '0000-00-00 00:00:00',
`AcctSessionTime` int(12) default NULL,
`AcctAuthentic` varchar(32) default NULL,
`ConnectInfo_start` varchar(32) default NULL,
`ConnectInfo_stop` varchar(32) default NULL,
`AcctInputOctets` int(12) unsigned default NULL,
`AcctOutputOctets` bigint(12) default NULL,
`CalledStationId` varchar(50) NOT NULL default '',
`CallingStationId` varchar(50) NOT NULL default '',
`AcctTerminateCause` varchar(32) NOT NULL default '',
`ServiceType` varchar(32) default NULL,
`FramedProtocol` varchar(32) default NULL,
`FramedIPAddress` varchar(15) NOT NULL default '',
`AcctStartDelay` int(12) default NULL,
`AcctStopDelay` int(12) default NULL
) ENGINE = ARCHIVE;
CREATE PROCEDURE radius.archive_acct()
BEGIN
INSERT INTO radacctold
SELECT * FROM radacct
WHERE acctstoptime > 0
AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);
DELETE FROM radacct
WHERE acctstoptime > 0
AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);
END
it return with error
CREATE PROCEDURE radius.archive_acct()
BEGIN
INSERT INTO radacctold
SELECT * FROM radacct
WHERE acctstoptime > 0
AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 3 queries: 0.000 sec. */
It looks like you didn't change the DELIMITER
Use of multiple statements requires that a client is able to send statement strings containing the ; statement delimiter. In the mysql command-line client, this is handled with the delimiter command. Changing the ; end-of-statement delimiter (for example, to //) permit ; to be used in a program body.
Within MySql client you should define your procedure like this
DELIMITER $$
CREATE PROCEDURE archive_acct()
BEGIN
INSERT INTO radacctold
SELECT *
FROM radacct
WHERE acctstoptime > 0
AND DATE(acctstarttime) < CURDATE() - INTERVAL 3 MONTH;
DELETE
FROM radacct
WHERE acctstoptime > 0
AND DATE(acctstarttime) < CURDATE() - INTERVAL 3 MONTH;
END$$
DELIMITER ;
Other than that your procedure compiles without other syntax errors.
Here is SQLFiddle demo