Updating the Rate Sheet in the COGEXO Rating Engine

Rate table update on MySQL backed database

The concepts in this guide apply for the DB2 version of the rating engine.

Data Fromat

product_code_call,description,cost,call_type

Data Upload

For the MySQL back-end, use phpMyAdmin to upload the data:

      Log in to phpMyAdmin
      Select the rate table
      Click the import tab
      Select the file that has the comma delimited values (CSV)
      Set the number of lines to skip to 1
      Select CSV radio button
      Set the delimiter to “,”
      Check the replace data with file box
      Click Go

Upon successful import, a message diplaying the number of values imported, for example:
Import has been successfully finished, 1578 queries executed.


How to delete records with duplicate fields, such as dupplicate emails?

To delete records where only a field is duplicated, we can use a similar technique to the general duplicate removal technique:

CREATE TABLE temp AS SELECT DISTINCT ON (email) * FROM table_to_deduplicate;
DROP TABLE table_to_deduplicate;
ALTER TABLE temp RENAME TO table_to_deduplicate;

Be mindful of the fact that this process will not preserve the constraints on the original table. So if you have indexes, NOT NULL attributes, primary keys, foreign keys, you’ll have to recreate them.


How to delete dupplicate records in DB2, Oracle, MySQL, and PostgreSQL

To delete dupplicate records in SQL, the following sequence of commands will do the trick:

CREATE TABLE temp AS SELECT DISTINCT * FROM table_to_deduplicate;
DROP TABLE table_to_deduplicate;
ALTER TABLE temp RENAME TO table_to_deduplicate;

Be mindful of the fact that this process will not preserve the constraints on the original table. So if you have indexes, NOT NULL attributes, primary keys, foreign keys, you’ll have to recreate them.


Which MySQL database engine to pick for a given table?

MySQL allows to select a different king of engine on a per table basis at creation time of each table. Each engine has its advantages and caveats. Here is a brief summary:

  • MyISAM: fastest disk based, least space requirement, non-transactional, slow crash recovery
  • InnoDB: slowest engine, transactional, fastest crash recovery
  • HEAP:fastest overall engine, limited by live memory, limited attribute types, no crash recovery

References:


MySQL default values on INSERT

When inserting into regular database systems, like Oracle, DB2, and even PostgreSQL, the omitted attributes from an INSERT are automatically set to default. Not so with MyISAM tables on MySQL. The syntax required by MySQL for an implicit default when multiple attributes are inserted is DEFAULT. For example:

INSERT INTO my_table VALUES (?,?,DEFAULT,?,DEFAULT)

will set the third and fifth parameters to default values.

Note that this has been only tested with the JDBC driver, and other database drivers might behave differently.

References:


Increase maximum table space in MySQL

For those of us that are still forced to used 32bit MySQL, there is a table size limit of 4GB by default (even though the file size limit on those systems is 4TB on ext3 – 2TB on NTFS).  Here is what to do to lift that limit:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

You have to specify AVG_ROW_LENGTH only for tables with BLOB or TEXT columns; in this case, MySQL can’t optimize the space required based only on the number of rows.

If you want to make the default larger, then you can set the mysqld engine parameter myisam_data_pointer_size = 7 in /etc/my.cnf (or whereever your MySQL server config file is). The setting of 7 will allow 256TB table size.

References:

  1. MySQL 5.0 Reference Manual
  2. Overcoming MySQL’s 4GB Limit
  3. Vbulletin Community Forum

Drop Index in MySQL takes forever

Even though for our core systems we have migrated most databases to DB2, we still have to deal with MySQL for some side systems, such as CRM, Blog, etc.   One of the MySQL features that I recently noticed is that dropping indexes on sizable tables takes forever.  That is apparently due to a convoluted indexing process in MySQL.  The workaround was found here: MySQL General Discussion List

It goes as follows:

1) create table T1 like T;
This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4.
2) alter table T1 drop index ndx3;
This drops index ndx3 on the empty T1, which should be instantaneous.
3) insert into T1 select * from T;
This will populate table T and load all three(3) indexes for T1 in one pass.
4) drop table table T;
5) alter table T1 rename to T;

An alternative ugly hack is to start the drop index, kill the database server.  That will crash the table on which the drop index was.  The crashed table can be recovered by running mysiamchk.