The column must become a key column.

   1  alter table my_table add id int primary key auto_increment;

2147483647 is the maximum for an integer column in MySQL, so this error probably means you’ve exceeded this limit somewhere in your code.

Rails automatically detects the best type for your columns, so be sure to specify the correct limit when creating the column with migrations:

   1  # from activerecord-2.1.1/lib/active_record/connection_adapters/mysql_adapter.rb
   2          case limit
   3          when 1; 'tinyint'
   4          when 2; 'smallint'
   5          when 3; 'mediumint'
   6          when nil, 4, 11; 'int(11)'  # compatibility with MySQL default
   7          when 5..8; 'bigint'
   8          else raise(ActiveRecordError, "No integer type has byte size #{limit}")
   9          end

This Rails migration code would create a big integer column:

   1  t.integer :product_id, :null => false, :limit => 8

See the section on Numeric Types in the MySQL documentation for more information.

I’ve now packaged this into a Rails plugin called has_adjacent_finders

Problem

Finding the next and previous product is a common task on, for example, e-commerce sites.

Let’s say we have a table containing data having the following IDs:

   1  201
   2  202
   3  203
   4  204
   5  205
   6  206
   7  207
   8  208

How do we get the rows adjacent to row 205? We can rely on MySQL sorting—the primary key in this case— so these two queries will do the job for us:

   1  # Find previous row
   2  select id from products where id < 205 order by id desc limit 1
   3  
   4  # Find next row
   5  select id from products where id > 205 order by id asc limit 1

The two queries will return 204 and 206 respectively. You can also use other columns, not just ID…

This is a simple example of a MySQL stored procedure that has both an in and out parameter:

   1  DELIMITER |
   2  DROP PROCEDURE IF EXISTS category_for |
   3  CREATE PROCEDURE category_for (product_id int, OUT r_category_id INT)
   4   BEGIN
   5    DECLARE category_id INT;
   6    DECLARE products_cursor CURSOR FOR SELECT category_id FROM products where id = product_id;
   7    OPEN products_cursor;
   8     FETCH products_cursor INTO category_id;
   9    CLOSE products_cursor;
  10    SET r_category_id 	= category_id;
  11   END |
  12  DELIMITER ;

Note that we use ; inside the procedure so we have to set the delimiter to | temporarily.

To call the stored procedure use the following SQL commands:

   1  CALL category_for(202, @category_id);

The @category_id session variable will now contain the procedure’s output:

   1  select @category_id;
   2  +-------+
   3  | @category_id |
   4  +-------+
   5  | 3     | 
   6  +-------+

There are at least three reasons, that I know of, to why you could be seeing mysql error 1005

• Adding a foreign key constraint when column types don’t match
• Not enough privileges to execute the script
• Trying to delete an index that is needed by some other index or constraint (“error on rename of”)

Use “SHOW ENGINE INNODB STATUS ;” to view the error:

   1  ALTER TABLE videos ADD constraint fk_videos_channels_id FOREIGN KEY (channel_id) REFERENCES channels (id);
   2  SHOW ENGINE INNODB STATUS;