[Asterisk-Users] Realtime + MYSQL

[Rollin Weeks]

Damon,

You may be querying the wrong table, because the following fields in your 
Select statement do not exit
in the table, voicemail_users, that you created:

category,
var_name,
var_val,
cat_metric,
filename,
commented

Every item mentioned in a Select query must exist in the table that is being 
queried.

Rollin Weeks




On 8/10/05, Damon Estep <damon at suburbanbroadband.net> wrote:
> 
> 
> > I'm having a few issues with the MySQL realtime configuration in
> > CVS-HEAD. I tested it initially with realtime extensions (realtime_ext
> > => mysql,asterisk,extensions) and a realtime switch in extensions.conf
> > and that works fine, So I though I'd go back and test a static
> > configuration mapping.
> >
> > I used the table structure from the asterisk guru postgres howto to
> > create something similar in MySQL (shown below) and included the
> > following in extconfig;
> >
> > voicemail.conf => mysql,asterisk,voicemail_users
> >
> > The result is that app_voicemail fails to load and it appears from the
> > debug that it is not happy with the table structure... however the
> names
> > it has for the fields seem strange (to me that is :))
> >
> > If anyone has gone through the process of creating the correct tables
> in
> > MySQL and doesn't mind sharing I would be most appreciative.
> >
> > Regards,
> >
> > Nathan.
> >
> >
> > ####MySQL Table####
> > CREATE TABLE voicemail_users (
> > id int NOT NULL auto_increment,
> > customer_id varchar(255) NOT NULL default '0',
> > context varchar(255) NOT NULL default '',
> > mailbox varchar(255) NOT NULL default '',
> > password varchar(4) NOT NULL default '0',
> > fullname varchar(50) NOT NULL default '',
> > email varchar(50) NOT NULL default '',
> > pager varchar(50) NOT NULL default '',
> > stamp datetime NOT NULL default '0000-00-00 00:00:00',
> > PRIMARY KEY (`id`)
> > );
> > #######
> >
> > ####res_mysql.conf####
> > [general]
> > dbhost = localhost
> > dbname = asterisk
> > dbuser = asterisk
> > dbpass = ********
> > dbport = 3306
> > dbsock = /var/run/mysqld/mysqld.sock
> > ####
> >
> > ####Debug Log####
> > Aug 11 01:16:23 DEBUG[1028] res_config_mysql.c: MySQL RealTime: Static
> > SQL: SELECT category, var_name, var_val, cat_metric FROM
> voicemail_users
> > WHERE filename='voicemail.conf' and commented=0 ORDER BY filename,
> > cat_metric desc, var_metric asc, category, var_name, var_val, id
> > Aug 11 01:16:23 DEBUG[1028] res_config_mysql.c: MySQL RealTime:
> > Everything is fine.
> > Aug 11 01:16:23 DEBUG[1028] res_config_mysql.c: MySQL RealTime: Query:
> > SELECT category, var_name, var_val, cat_metric FROM voicemail_users
> > WHERE filename='voicemail.conf' and commented=0 ORDER BY filename,
> > cat_metric desc, var_metric asc, category, var_name, var_val, id
> > Aug 11 01:16:23 DEBUG[1028] res_config_mysql.c: MySQL RealTime: Query
> > Failed because: Unknown column 'category' in 'field list'
> > ####
> > _______________________________________________
> 
> This works for voicemail in CVS-HEAD
> 
> CREATE TABLE `voicemail` (
> `uniqueid` int(11) NOT NULL auto_increment,
> `customer_id` int(11) NOT NULL default '0',
> `context` varchar(50) NOT NULL default '',
> `mailbox` varchar(10) NOT NULL default '0',
> `password` varchar(4) NOT NULL default '0',
> `fullname` varchar(50) NOT NULL default '',
> `email` varchar(50) NOT NULL default '',
> `pager` varchar(50) NOT NULL default '',
> `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> PRIMARY KEY (`uniqueid`),
> KEY `mailbox_context` (`mailbox`,`context`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> _______________________________________________
> Asterisk-Users mailing list
> Asterisk-Users at lists.digium.com
> http://lists.digium.com/mailman/listinfo/asterisk-users
> To UNSUBSCRIBE or update options visit:
> http://lists.digium.com/mailman/listinfo/asterisk-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-users/attachments/20050811/cd2fe9e5/attachment.htm