Wildfire 3.2 / AD / Asterisk-im 1.2.2 db erro

Openfire Plugins Asterisk-IM
1

Hy, I get an error with astersik-im ; I the plugin I have “no server configured” and I can not open the phone mappings page.

On the error log :

java.sql.SQLException: Table not found in statement

I have found in the post

http://www.igniterealtime.org/forum/thread.jspa?threadID=24323&tstart=0

but also the info log file says

Database update successful.

Found old database version 0 for asterisk-im. Upgrading to version 2…

I think asterisk-im do nothing because I delete all files in the database folder, reboot wildfire and I alway see the upgrade message.

Do you know if an older realse solved that or if there are a solution in the next release ?

The database account don’'t have write rights on the AD because I think it to bad.

Excuse me for my english.

2

check that you have ‘‘phoneServer’’ table in your wildfire database. If not, create it using following snippet or create it manually using phpMyAdmin or webmin. You shouldthen be able to add your server(s).

–

– Table structure for table phoneServer

–

DROP TABLE IF EXISTS phoneServer;

CREATE TABLE phoneServer (

serverID bigint(20) NOT NULL default ‘‘0’’,

serverName varchar(255) collate utf8_unicode_ci NOT NULL default ‘’’’,

hostname varchar(255) collate utf8_unicode_ci NOT NULL default ‘’’’,

port int(11) NOT NULL default ‘‘0’’,

username varchar(255) collate utf8_unicode_ci NOT NULL default ‘’’’,

password varchar(255) collate utf8_unicode_ci NOT NULL default ‘’’’,

PRIMARY KEY (serverID),

UNIQUE KEY serverName (serverName)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

–

– Dumping data for table phoneServer

–

3

I think you are right, but my real question is :

do you think I can create a new table in my db.

I’'m using Active Directory db to get all my user already set in wildfire

I use wildfire active directory mode with database login connexion which do not have write right on it.

Anyone use wildfire / ad and asterisk-im ?

Thank for your answer.

Tim

4

What database backend are you using?

5

I use wildfire whith active directory database. That the reason why I can not update table, but I don’'t know if the active directory mode in wildfire have an other db.

Thank you for your help

Tim

6

Open your openfire.xml file.

Search for <driver>

What text do you have in between <driver> </driver>

7

Sorry, I do not have any openfire.xml file but wildfire.xml. I can read that about settings of my database :

I don’‘t know if it help you. I don’'t understand where asterisk-im table may be create using an active directory db ?

Tim

8

Can you post your entire wildfire.xml and remove any passwords or network sensitive info.

9

Ok, this is my wildfire.xml file :







Thank you for your help.

Tim

10

Ok, you are using the embedded database.

If you are using windows, check this out

[

http://www.igniterealtime.org/forum/thread.jspa?messageID=131628&#131628|http:// http://www.igniterealtime.org/forum/thread.jspa?messageID=131628&#131628|http:// www.igniterealtime.org/forum/thread.jspa?messageID=131628&#131628]

If not search this forum for the word “embedded”.

11

Ok, I have just upgrade to openfire 3.3 with asterisk-im 1.3 but I have already this message :

java.sql.SQLException: Table not found in statement

In the Info log, I have seen that the database schema is not correct so it will be upgrade, I have check the db sql script but I have the same problem.

Can you help me again ?

Tim

12

Is openfire installed on linux or windows?

13

Openfire is installed on a debian 2.6.

Tim

14

Taken from http://www.igniterealtime.org/forum/thread.jspa?messageID=131628&#131628

EDIT: Err, I’‘m not sure how to make a DB backup, but it’'d probably be best to make one before attempting this

Hi Guys,

I was able to manually update the DB to get 1.2.0beta2 working on the

embedded db last night. These are the basic steps I followed:

  1. Shut down wildfire

  2. Went into the HSQL Database Manager (embedded db admin interface)

(Run /opt/openfire/bin/extra/embedded-db-viewer.sh)

  1. Dropped the phone databases from an old asterisk-im install (I

didn’'t have any configuration data to save). For me, I had to "DROP

TABLE phoneuser" and “DROP TABLE phonedevice”- PLEASE NOTE this will

probably DELETE all your current asterisk-im settings.)

  1. Tried to run the MySQL Script, but errors were given stating that

the “unique” keyword was not supported (the script is in C:\Program

Files\Wildfire\plugins\asterisk-im\database on windows)

  1. Removed “unique” keyword (twice) from MySQL script, resulting in the

script below. You will also notice the last line from the MySQL script

is also missing (to be manually updated in step 8)

create table phoneServer (

serverID bigint not null,

serverName varchar(255) not null,

hostname varchar(255) not null,

port int not null,

username varchar(255) not null,

password varchar(255) not null,

primary key(serverID)

);

create table phoneDevice (

deviceID bigint not null,

device varchar(255) not null,

extension varchar(255) not null,

callerId varchar(255),

isPrimary int not null,

userID bigint,

serverID bigint not null,

primary key (deviceID)

);

create table phoneUser (

userID bigint not null,

username varchar(255) not null,

primary key (userID)

);

  1. Ran the script to add the updated tables back

  2. Ran “SELECT * from JIVEVERSION” and noticed the version of

‘‘asterisk-im’’ was set to “0” (for me atleast, since I was using an old

version)

  1. Ran the following SQL to manually update ‘‘asterisk-im’’ to version 2

(to prevent wildfire attempting to upgrade the DB automatically, since

we just did it manually):

UPDATE JIVEVERSION

SET VERSION=2

WHERE NAME=’‘asterisk-im’’ (must be single-quotes here)

9- Verified version updated correctly by running “SELECT * FROM JIVEVERSION”

10- Close out of HSQL Database Manager

11- Wait a minute and restart Wildfire

12- Asterisk-im 1.2.0b2 should work now!

15

Thank you a lot for your help. It is working now for my pleasure and my compagny to

Tim

16

No problem. Don’'t forget to mark this as answered for other people.