[Asterisk-Users] Realtime + MYSQL

[Damon Estep]

> 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;