SQL Tables missing

BirdyB's picture
Posted February 11th, 2012 by BirdyB (tadpole)

Hi everybody,

i just installed asterisk 1.8 via yum on a Centos 5.2 vServer. Asterisk is starting up without problems.
Now i tried to install freepbx and i got an error that ./install_amp was unable to connect to the database.
So i created the database "asterisk" manually... Now the install-script connects to the database but i get the following error:

Upgrading to 1.10.007beta1..
-> Running SQL script /usr/src/freepbx-2.9.0/upgrades/1.10.007beta1/tables.sql
[FATAL] ALTER TABLE `sip` CHANGE `id` `id` BIGINT( 11 ) DEFAULT "-1" NOT NULL [nativecode=1146 ** Table 'asterisk.sip' doesn't exist]" while running tables.sql

How can i solve this problem? is there anything to import to the mysql database? Please help me!


__________________


‹ FreePBX Distro V1.86.29.55 Install Problem - Dell OptiPlex 390 System Codec g729 for xeon processor ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

You need to follow the

SkykingOH's picture
On February 11th, 2012 SkykingOH (Leap Frog) said:

You need to follow the installation procedure and create the databases. I have quoted the install information below.

cd /usr/src/freepbx-2.9.0
mysqladmin create asterisk
mysqladmin create asteriskcdrdb
mysql asterisk < SQL/newinstall.sql
mysql asteriskcdrdb < SQL/cdr_mysql_table.sql

They also need to be secured.  FreePBX will prompt you for a database username/password when you do the install. You need to pick that now. We'll assume that you've picked 'asteriskuser' and 'amp109' - you probably shouldn't use these, as they are well known passwords for Freepbx.  If you use these well know defaults and your server is not firewalled make sure to set bind-address = 127.0.0.1 further down in this procedure so that MySQL only listens to localhost.  Or better yet do both.

mysql

mysql> GRANT ALL PRIVILEGES ON asteriskcdrdb.* TO asteriskuser@localhost IDENTIFIED BY 'amp109';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON asterisk.* TO asteriskuser@localhost IDENTIFIED BY 'amp109';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

Now, after all of this, you need to pick a root 'mysql' password. We'll make it 'abcdef' just for this example.  You should use a reasonably strong password. If you need to do anything else with mysql, you'll need to provide this password.
mysqladmin -u root password 'abcdef'

try

roger8's picture
On February 23rd, 2012 roger8 (tadpole) said:

try this

/usr/src/freepbx-2.9.0/upgrades/1.10.007beta1# mysql -u root -p
enter root mysql password

mysql> use asterisk;
Database changed
mysql> ALTER TABLE `sip` CHANGE `id` `id` BIGINT( 11 ) DEFAULT "-1" NOT NULL;
ERROR 1146 (42S02): Table 'asterisk.sip' doesn't exist
mysql> DROP TABLE IF EXISTS `sip`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `sip` (
-> `id` varchar(20) NOT NULL default '-1',
-> `keyword` varchar(30) NOT NULL default '',
-> `data` varchar(255) NOT NULL default '',
-> `flags` int(1) NOT NULL default '0',
-> PRIMARY KEY (`id`,`keyword`)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `sip` CHANGE `id` `id` BIGINT( 11 ) DEFAULT "-1" NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE `iax` CHANGE `id` `id` BIGINT( 11 ) DEFAULT "-1" NOT NULL;
ERROR 1146 (42S02): Table 'asterisk.iax' doesn't exist
mysql> CREATE TABLE `iax` (
-> `id` varchar(20) NOT NULL default '-1',
-> `keyword` varchar(30) NOT NULL default '',
-> `data` varchar(255) NOT NULL default '',
-> `flags` int(1) NOT NULL default '0',
-> PRIMARY KEY (`id`,`keyword`)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE `iax` CHANGE `id` `id` BIGINT( 11 ) DEFAULT "-1" NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE `extensions` CHANGE `context` `context` VARCHAR( 45 ) DEFAULT 'default' NOT NULL;
ERROR 1146 (42S02): Table 'asterisk.extensions' doesn't exist
mysql> CREATE TABLE `extensions` (
-> `context` varchar(45) NOT NULL default 'default',
-> `extension` varchar(45) NOT NULL default '',
-> `priority` varchar(5) NOT NULL default '1',
-> `application` varchar(45) NOT NULL default '',
-> `args` varchar(255) default NULL,
-> `descr` text,
-> `flags` int(1) NOT NULL default '0',
-> PRIMARY KEY (`context`,`extension`,`priority`)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE `extensions` CHANGE `context` `context` VARCHAR( 45 ) DEFAULT 'default' NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS `zap` (`id` bigint(11) NOT NULL default '-1',`keyword` varchar(20) NOT NULL default '',`data` varchar(150) NOT NULL default '',`flags` int(1) NOT NULL default '0',PRIMARY KEY (`id`,`keyword`)) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `queues` (`id` bigint(11) NOT NULL default '-1',`keyword` varchar(20) NOT NULL default '',`data` varchar(150) NOT NULL default '',`flags` int(1) NOT NULL default '0',PRIMARY KEY (`id`,`keyword`,`data`)) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `ampusers` (`username` varchar(20) NOT NULL default '',`password` varchar(20) NOT NULL default '',`extension_low` varchar(20) NOT NULL default '',`extension_high` varchar(20) NOT NULL default '',`deptname` varchar(20) NOT NULL default '',`sections` varchar(255) NOT NULL default '',PRIMARY KEY (`username`)) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> DELETE FROM `globals` WHERE variable = 'DIRECTORY_OPTS';
ERROR 1146 (42S02): Table 'asterisk.globals' doesn't exist
mysql> show tables;
+--------------------+
| Tables_in_asterisk |
+--------------------+
| ampusers |
| extensions |
| iax |
| queues |
| sip |
| zap |
+--------------------+
6 rows in set (0.00 sec)

mysql> CREATE TABLE `globals` (
-> `variable` varchar(255) NOT NULL default '',
-> `value` varchar(255) NOT NULL default '',
-> PRIMARY KEY (`variable`)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `globals` WHERE variable = 'DIRECTORY_OPTS';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `globals` VALUES ('DIRECTORY_OPTS', '');
Query OK, 1 row affected (0.00 sec)

mysql> exit;