[Asterisk-Users] Realtime + MYSQL

[Damon Estep]

Rollin,

 

My real-time works fine, Nathan was the original poster o this message.
I simply added the table structure for real-time voicemail :-)

 

 

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/bdc2e807/attachment.htm