SQL Tables missing

FreePBX Installation / Upgrade
1

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!

2

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'
3

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;

4