Untitled

-- Setting Global format Barracuda for UTF8mb4
--SET @@GLOBAL.innodb_file_format = 'Barracuda';

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_format_max = "Barracuda";
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;     -- optional (if you also need wide indexes)
SET GLOBAL innodb_strict_mode = "ON";


-- IF USE LARAVEL, ADD LINES ON config/databases.php
-- MySQL
-- 'engine' => null
--  ||
--  \/
-- 'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',


---------------------------------------
-- EXPLAIN ABOUT FORMAT TO BARRACUDA --
---------------------------------------

-- Show version MariaDB / MySQL
SELECT version();
-- +------------+
-- | version()  |
-- +------------+
-- | 5.5.21-log |
-- +------------+
-- 1 row in set (0.00 sec)


-- verify current setting engine DB
SHOW variables like "%innodb_file%";
-- +--------------------------+----------+
-- | Variable_name            | Value    |
-- +--------------------------+----------+
-- | innodb_file_format       | Antelope |
-- | innodb_file_format_check | ON       |
-- | innodb_file_format_max   | Antelope |
-- | innodb_file_per_table    | ON       |
-- +--------------------------+----------+
-- 4 rows in set (0.00 sec)


---------------------------
-- SETTINGS TO BARRACUDA --
---------------------------

-- SET GLOBAL INNODB FILE FORMAT BARRACUDA
SET GLOBAL innodb_file_format = barracuda;
-- Query OK, 0 rows affected (0.00 sec)

-- Verify current setting engine DB
SHOW variables like "%innodb_file%";
-- +--------------------------+-----------+
-- | Variable_name            | Value     |
-- +--------------------------+-----------+
-- | innodb_file_format       | Barracuda |
-- | innodb_file_format_check | ON        |
-- | innodb_file_format_max   | Antelope  |
-- | innodb_file_per_table    | ON        |
-- +--------------------------+-----------+
-- 4 rows in set (0.00 sec)

-- SET GLOBAL INNODB FILE FORMAT MAX BARRACUDA
SET GLOBAL innodb_file_format_max = barracuda;
-- Query OK, 0 rows affected (0.00 sec)

-- Verify current setting engine DB
SHOW variables like "%innodb_file%";
-- +--------------------------+-----------+
-- | Variable_name            | Value     |
-- +--------------------------+-----------+
-- | innodb_file_format       | Barracuda |
-- | innodb_file_format_check | ON        |
-- | innodb_file_format_max   | Barracuda |
-- | innodb_file_per_table    | ON        |
-- +--------------------------+-----------+
-- 4 rows in set (0.00 sec)
--
-- I had observed a single line logged in Error Log file :
--
-- [root@dhcppc0 Desktop]# tail -1 /usr/local/mysql/data/dhcppc0.err
-- 120402 11:26:52 [Info] InnoDB: the file format in the system tablespace is
-- now set to Barracuda.
--
-- After switching to barracuda file format, I could also access my Database
-- and tables without any error :

--------------------
-- SHOW DATABASES --
--------------------

SHOW DATABASES;
-- +--------------------+
-- | Database           |
-- +--------------------+
-- | information_schema |
-- | mysql              |
-- | opentaps1          |
-- | performance_schema |
-- | test               |
-- +--------------------+
-- 5 rows in set (0.00 sec)

USE opentaps1;

-- Database changed

SELECT COUNT(*) FROM product;

-- +----------+
-- | count(*) |
-- +----------+
-- |     3244 |
-- +----------+
-- 1 row in set (0.42 sec)

SHOW engines;

-- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
-- | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
-- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
-- | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
-- | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
-- | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
-- | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
-- | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
-- | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
-- | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
-- | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
-- | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
-- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
-- 9 rows in set (0.00 sec)

SHOW engine innodb status\G

-- *************************** 1. row ***************************
-- Type: InnoDB
-- Name:
-- Status:
-- =====================================
-- 120402 11:36:29 INNODB MONITOR OUTPUT
-- =====================================
-- Per second averages calculated from the last 18446744073709534037 seconds
-- -----------------
-- BACKGROUND THREAD
-- -----------------
-- srv_master_thread loops: 12 1_second, 12 sleeps, 1 10_second, 2 background,
-- 2 flush
-- srv_master_thread log flush and writes: 12
-- ----------
-- SEMAPHORES
-- ----------
-- OS WAIT ARRAY INFO: reservation count 5, signal count 5
-- Mutex spin waits 2, rounds 60, OS waits 2
-- RW-shared spins 3, rounds 90, OS waits 3
-- RW-excl spins 0, rounds 0, OS waits 0
-- Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
-- ------------
-- TRANSACTIONS
-- ------------
-- Trx id counter F01
-- Purge done for trx's n:o < 0 undo n:o < 0
-- History list length 0
-- LIST OF TRANSACTIONS FOR EACH SESSION:
-- ---TRANSACTION F00, not started
-- MySQL thread id 1, OS thread handle 0x7f38309f9710, query id 28 localhost
-- root
-- show engine innodb status
-- --------
-- FILE I/O
-- --------
-- I/O thread 0 state: waiting for completed aio requests (insert buffer
-- thread)
-- I/O thread 1 state: waiting for completed aio requests (log thread)
-- I/O thread 2 state: waiting for completed aio requests (read thread)
-- I/O thread 3 state: waiting for completed aio requests (read thread)
-- I/O thread 4 state: waiting for completed aio requests (read thread)
-- I/O thread 5 state: waiting for completed aio requests (read thread)
-- I/O thread 6 state: waiting for completed aio requests (write thread)
-- I/O thread 7 state: waiting for completed aio requests (write thread)
-- I/O thread 8 state: waiting for completed aio requests (write thread)
-- I/O thread 9 state: waiting for completed aio requests (write thread)
-- Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
-- ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
-- Pending flushes (fsync) log: 0; buffer pool: 0
-- 554 OS file reads, 7 OS file writes, 7 OS fsyncs
-- -0.01 reads/s, 16384 avg bytes/read, -0.00 writes/s, -0.00 fsyncs/s
-- -------------------------------------
-- INSERT BUFFER AND ADAPTIVE HASH INDEX
-- -------------------------------------
-- Ibuf: size 1, free list len 0, seg size 2, 0 merges
-- merged operations:
-- insert 0, delete mark 0, delete 0
-- discarded operations:
-- insert 0, delete mark 0, delete 0
-- Hash table size 276707, node heap has 15 buffer(s)
-- -0.15 hash searches/s, -0.12 non-hash searches/s
-- ---
-- LOG
-- ---
-- Log sequence number 221536390
-- Log flushed up to   221536390
-- Last checkpoint at  221536390
-- 0 pending log writes, 0 pending chkp writes
-- 10 log i/o's done, -0.00 log i/o's/second
-- ----------------------
-- BUFFER POOL AND MEMORY
-- ----------------------
-- Total memory allocated 137363456; in additional pool allocated 0
-- Dictionary memory allocated 3476070
-- Buffer pool size   8192
-- Free buffers       7635
-- Database pages     542
-- Old database pages 220
-- Modified db pages  0
-- Pending reads 0
-- Pending writes: LRU 0, flush list 0, single page 0
-- Pages made young 0, not young 0
-- -0.00 youngs/s, -0.00 non-youngs/s
-- Pages read 542, created 0, written 1
-- -0.01 reads/s, -0.00 creates/s, -0.00 writes/s
-- Buffer pool hit rate 980 / 1000, young-making rate 0 / 1000 not 0 / 1000
-- Pages read ahead -0.00/s, evicted without access -0.00/s, Random read ahead
-- -0.00/s
-- LRU len: 542, unzip_LRU len: 0
-- I/O sum[0]:cur[238], unzip sum[0]:cur[0]
-- --------------
-- ROW OPERATIONS
-- --------------
-- 0 queries inside InnoDB, 0 queries in queue
-- 1 read views open inside InnoDB
-- Main thread process no. 2937, id 139879303665424, state: waiting for server
-- activity
-- Number of rows inserted 0, updated 0, deleted 0, read 3244
-- -0.00 inserts/s, -0.00 updates/s, -0.00 deletes/s, -0.18 reads/s
-- ----------------------------
-- END OF INNODB MONITOR OUTPUT
-- ============================
-- 1 row in set (0.00 sec)
-- a

-- Setting change format on table
ALTER TABLE <table_name> ROW_FORMAT=DYNAMIC;

-- Convertir formato barracuda TODAS TABLAS
 USE INFORMATION_SCHEMA;
 SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE ENGINE='innodb' AND ROW_FORMAT != 'DYNAMIC' AND ROW_FORMAT !='COMPRESSED';