MySQL Error 1236 - Reconfigure slave

In case replication fails with error 1236 when reading data from binary log, slave can be reconfigured to skip the wrong binlog file and start with the next one.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 172.18.16.62
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000077
          Read_Master_Log_Pos: 712511169
               Relay_Log_File: mysqld-relay-bin.000216
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000077
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 712429781
              Relay_Log_Space: 83596
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000077' at 712511169, the last event read from './mysql-bin.000077' at 712511169, the last byte read from './mysql-bin.000077' at 712511488.'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

mysql> change master to master_log_pos=0;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_log_file='mysql-bin.000078';
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.16.62
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000079
          Read_Master_Log_Pos: 15734059
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 15339130
        Relay_Master_Log_File: mysql-bin.000079
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 15338984
              Relay_Log_Space: 32551481
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 75
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

To automatically purge binary log files, there can be used 2 methods:

• periodically use command PURGE BINARY LOGS
•  mysql –uroot –p –e “purge binary logs before date_sub(now(),interval N day);”         // where N = number of days to keep logs
• set varibale expire_log_days
• SET GLOBAL expire_log_days = N;    // where N = number of days to keep logs

Oracle TOPS

Oracle 11gR2 upgrade presentation (step-by-step procedure)
Oracle RAC 11gR2 SCAN details

ORA-01882 - SQL Developer

Oracle SQL Developer Version 3.2.20.09
Windows 7 (x64)

When trying to configure a database connection following error appears:

ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found

According to metalink note 1477929.1 it is a SQL Developer configuration issue. The SQL Developer cannot recognize the correct timezone.
The workaround is to add the following line into %SQLDEVELOPER%/sqldeveloper/bin/sqldeveloper.conf file (make a backup of this file first).

AddVMOption -Duser.timezone="+02:00"

Save the file and then restart SQL Developer.

SQL: Aggregate data for each user, day, month as SUM

Just the other day I received a request to aggregate the sum for each user_id for each month as a sum of all counters for all previous days starting from the beginning of the  month.
The table used was:


create table test_report
(
user_id number,
curr_date date,
total_day number,
total_month number,
constraint pk_report primary key (user_id,curr_date));

The test data in this table had the total_day=10 for each day for each user_id.
The view created to retrieve the data in the requested format, where dynamic_total_month is the automatic generated sum:

CREATE VIEW test_report_view AS

SELECT user_id,

  curr_date,

  total_day,

  SUM(SUM(total_day)) over (partition BY user_id extract(MONTH FROM curr_date),extract(YEAR FROM curr_date) order by curr_date) dynamic_total_month,

  total_month total,

  extract(MONTH FROM curr_date) month_ex,

  extract(YEAR FROM curr_date) year_ex

FROM test_report

GROUP BY user_id,

  curr_date,

  total_day,

  total_month,

  extract(MONTH FROM curr_date),

  extract(YEAR FROM curr_date);

Here is a sample output:

select  * from test_report_view

where user_id=1 and curr_date between '25-dec-2013' and '5-jan-2014' order by curr_date;

1 25-DEC-13 01.17.24 10 250  12 2013

1 26-DEC-13 01.17.25 10 260  12 2013

1 27-DEC-13 01.17.25 10 270  12 2013

1 28-DEC-13 01.17.26 10 280  12 2013

1 29-DEC-13 01.17.26 10 290  12 2013

1 30-DEC-13 01.17.26 10 300  12 2013

1 31-DEC-13 01.17.27 10 310  12 2013

1 01-JAN-14 01.17.27 10 10  1 2014

1 02-JAN-14 01.17.27 10 20  1 2014

1 03-JAN-14 01.17.28 10 30  1 2014

1 04-JAN-14 01.17.28 10 40  1 2014

MySQL Cluster utilities

During a night action I had the opportunity to reconfigure an MySQL Cluster that had a storage node failed. At start time all storega nodes were stuck in Phase 0. The status of all storage nodes can be view from management client.

# ndb_mgm
ndb_mgm> all status

Also the status of the entire cluster can be view with the command

# ndb_mgm -e show
or from management client
ndb_mgm> show

Memory used on each NDB storage node can be view with:

ndb_mgm> all report memory

From this i learned a lesson: on storage nodes the firewall should be disabled.
Disabling firewall on linux:


# service iptables stop
Flushing firewall rules:                                   [  OK  ]
Setting chains to policy ACCEPT: filter                    [  OK  ]
Unloading iptables modules:                                [  OK  ]
# service iptables status
Firewall is stopped.

# chkconfig iptables off

NDB specific command can be run from storage nodes. These command gives you some details about the data distribution on NDB nodes.

To see all objects stored in NDB engine storage:
# ndb_show_tables
# ndb_show_tables | grep UserTable  // to see only the tables

To see the definitioan of a table and the data distribution for this table in partitions on NDB nodes:
# ndb_desc [table_name] -d [database_name] -p

To get the parameters used for NDB storage nodes:

# ndb_config --type=ndbd --query=id,host,datamemory,indexmemory,datadir -f ' : ' -r '\n'

This ndb_config command check only the data nodes (--type) and shows for each node the values for node's id, hostname, DataMemory, IndexMemory and DataDir parameters.

To get all nodes type in the cluster:

# ndb_config --query=id,type --fields=':' --rows='\n'

To get the row counting for a NDB table:


# ndb_select_count -d [database_name] [table_name1] [table_name2] ...

Delete last lines from .bash_history

history - View command history from ~/.bash_profile file

history -d $((HISTCMD-2)) && history -d $((HISTCMD-1))

This command will hide the history command and the previous command.

 history -d $((HISTCMD-1))

This command will delete from history only the record of this command.

history -d [line_number]

Deletes command from line [line_number]

MySQL datatypes

Integer data types

Type      Storage Required Signed Range                           Unsigned Range
TINYINT   1 byte           –128 to 127                          0 to 255    
SMALLINT  2 bytes          –32,768 to 32,767                    0 to 65,535
MEDIUMINT 3 bytes          –8,388,608 to 8,388,607              0 to 16,777,215
INT       4 bytes          –2,147,683,648 to 2,147,483,647                          0 to 4,294,967,295
BIGINT    8 bytes          –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807                                       0 to 18,446,744,073,709,551,615

Floating point data types

FLOAT represents single-precision floating-point values that require four bytes each for storage.

DOUBLE represents double-precision floating-point values that require eight bytes each
for storage.

Fixed point data types


DECIMAL uses a fixed-decimal storage format: All values in a DECIMAL column have the same
number of decimal places and are stored exactly as given when possible. DECIMAL values are
not processed quite as efficiently as FLOAT or DOUBLE values (which use the processor’s native
binary format), but DECIMAL values are not subject to rounding error, so they are more accurate.
The NUMERIC data type in MySQL is a synonym for DECIMAL.

BIT data type


The BIT data type represents bit-field values. BIT column specifications take a width indicating
the number of bits per value, from 1 to 64 bits.

String data types


Type Description
CHAR Fixed-length non-binary string
VARCHAR Variable-length non-binary string
TEXT Variable-length non-binary string
BINARY Fixed-length binary string
VARBINARY Variable-length binary string
BLOB Variable-length binary string
ENUM Enumeration consisting of a fixed set of legal values
SET Set consisting of a fixed set of legal values

Non Binary sting data types


Type Storage Required Maximum Length
CHAR(M) M characters 255 characters
VARCHAR(M) L characters plus 1 or 2 bytes 65,535 characters (subject to
limitations)
TINYTEXT L characters + 1 byte 255 characters
TEXT L characters + 2 bytes 65,535 characters
MEDIUMTEXT L characters + 3 bytes 16,777,215 characters
LONGTEXT L characters + 4 bytes 4,294,967,295 characters

Binary string data types


Type Storage Required Maximum Length
BINARY(M) M bytes 255 bytes
VARBINARY(M) L bytes plus 1 or 2 bytes 65,535 bytes (subject to limitations)
TINYBLOB L + 1 bytes 255 bytes
BLOB L + 2 bytes 65,535 bytes
MEDIUMBLOB L + 3 bytes 16,777,215 bytes
LONGBLOB L + 4 bytes 4,294,967,295 bytes

ENUM and SET data types


ENUM is an enumeration type. An ENUM column definition includes a list of allowable values;
each value in the list is called a “member” of the list.
The SET data type, like ENUM, is declared using a comma-separated list of quoted strings that

define its valid members. But unlike ENUM, a given SET column may be assigned a value consisting
of any combination of those members.

Temporal data types


Type Storage Required Range
DATE 3 bytes ‘1000-01-01’ to ‘9999-12-31’
TIME 3 bytes ‘-838:59:59’ to ‘838:59:59’
DATETIME 8 bytes ‘1000-01-01 00:00:00’ to
‘9999-12-31 23:59:59’
TIMESTAMP 4 bytes ‘1970-01-01 00:00:00’ to
mid-year 2037
YEAR 1 byte 1901 to 2155 (for YEAR(4)),
1970 to 2069 (for YEAR(2))

How to reset current value for sequence

This is one interesting issue regarding altering a sequence to reset CURRENT VALUE.
Because this current value is not specified in CREATE SEQUENCE statement, it can not be reset with ALTER SEQUENCE statement. Instead you have the following methods:

1. Drop and recreate the sequence. Take care of the object affected by this sequence (triggers, procedures, etc.), because they will be invalidated.

2. Use ALTER SEQUENCE command to change some sequence limits and the generate new values

- review your sequence DDL

select 'create sequence '||sequence_name||
       ' increment by '||increment_by||
       ' start with '||last_number||
       ' maxvalue '||max_value||
       decode(cycle_flag,'N',' NOCYCLE ',' CYCLE ')||
       decode(cache_size,0,'NOCACHE ','CACHE '||cache_size)
 from user_sequences where sequence_name = 'SEQ_TEST';


- view current value for this sequence
select seq_test.currval from dual;
5

- change the maximum value of the sequence equal to current value and make the sequence to cycle if maxvalue is reach
alter sequence seq_test maxvalue 5 cycle nocache;


- generate new value for the sequence
select seq_test.nextval from dual;

- check that the new value is 0

select seq_test.currval from dual;
0

- use ALTER statement to change the sequence parameters like they use to be

alter sequence seq_test nomaxvalue nocycle cache 20;

2012 Oracle events in Bucharest

Even if we've just entered in 2013, here is the summary of two events that took place in Bucharest in October and November in Bucharest: Oracle Day 2012 and MySQL workshop. Oracle Day was the opportunity to get some details about the announcements made at Oracle Open World 2012.
Those 2 events highlighted the main directions for Oracle in developing and improving his 2 databases: Oracle Database and MySQL Database.

Oracle Day 2012Oracle Open World announcements:
•              Infrastructure as a Service – IaaS
•             Complete Cloud Offering 
◦                     SaaS,
◦                     PaaS,
◦                     IaaS
•             Exadata
•             Exalogic 

2. Oracle Private Cloud

3. Oracle Database 12c
Oracle Database 12c into Oracle Cloud – Multitenant database
•         multitenancy, enables users to create multiple "pluggable" databases that reside within a single database container
•         "heat map“ tracks how frequently different chunks of data are used
.•         Database upgrades and patches are made simpler than in the past thanks to the pluggable database concept.
•         Backup and recovery efforts benefit as well.

4. EXADATA X3 & EXALOGIC X3
•         Exadata X3 (www.youtube.com/watch?v=vcA71QGEhzI)
•          Database In-Memory Machine
◦              26 TB in DRAM (4) in Flash (22) in one rack
◦              X3H2M2 (X3 Heuristic Hierachical Mass Memmory) – Automatically keeps all active data in memory
•          All Active Data in Memory
•          Exadata Database Cloud
•          New Entry-level Eighth Rack
•          Exalogic X3
•          Extreme Performance For Java Applications
•          Extreme Performance For Oracle Business Applications
•          Natively Leverages Exadata
•          Best Consolidation Platform
•          Entry-level Eighth Rack
http://cloud.oracle.com 

Parallel sessions
•         Cloud
•          Instrastructure as a Service
•          Database as a Service
•          Midleware as a Service
•         Customer Experience•          Talent management (Oracle Taleo Cloud Service)
•          Customer experience (Oracle RightNow CX Cloud Service)
•         Data Center•          Exadata, Exalogic, Exalitycs
•         Enterprise Applications•          Business Intelligence
•          Business Process Modelling

MySQL workshop
•         MySQL 5.6 RC (Release Candidate)
•          MySQL Enterprise
•          What’s new in MySQL 5.6
•         MySQL Cluster 7.3 DMR1 (Developemnt Milestone Release)
•          What’s new

 MySQL 5.6 Enterprise Edition
•         MySQL Support
•         MySQL External Authentication
•         MySQL Enterprise Audit
•         MySQL Thread Pool
•         MySQL Enterprise Monitor
•         MySQL Enterprise Backup

 MySQL 5.6 RC
•         InnoDB engine
•          SSD optimization
◦              Page size from 4K to 64K
◦              Separate tablespaces for InnoDB Undo Log
•          Innodb_log_file_size (redo) 4GB limit dropped
•          Online DDL operations
◦              CREATE/DROP INDEX, ADD/DROP FOREIGN KEY
◦              Add, drop, rename, reorder, chane NULL/NOT_NULL for COLUMN
•          Persistent optimizer statistics
•          Optimized for READ ONLY workloads
◦              Autocommit=1: automatic
◦              Autocommit=0: START TRANSACTION READ ONLY
•          NoSQL solution for MySQL - delivered via memcached
•          Full Text Search 

MySQL 5.6 RC
•         Replication
•          Global Transaction IDs
◦              Unique identifier for each transaction written to the Binlog
•          Multi Threaded Slaves
◦              Great for systems which isolate application data using databases – e.g. multi-tenant
•          Optimized row base replication
◦              Binlog-row-image=minimal
•          Crash-safe Slaves
◦              Binlog and table data are transactionally consistent
•          Replication event checksum
 
MySQL 5.6 RC
•         Performance schema
•         Security
•          Password strength policies
•          Stronger password hashing
•         Other
•          Default optimized configuration
•          TIME/TIMESTAMP/DATETIME - fractional second precision
•          Improved partition


MySQL Cluster 7.3
•         MySQL Cluster is designed for
•          Short transactions
•          Many parallel transactions
 
MySQL Cluster
•         MySQL Cluster 7.2 GA
•          Adaptive Query Localization (AQL) – ndb_join_pushdown=on
•          Distribution Aware App
•          Connection pooling between MySQL Servers and data node
◦              ndb-cluster-connection-pool > 1 in my.cnf 
 •          NoSQL API
•         What’s new in MySQL 7.3 (EA)•          Autoinstaller
•          Support for Foreign Keys
•          Integration with the latest MySQL Server
•          New NoSQL APIs;
  

ORA-14060 - Partition key can not be changed

When you want to partition a table be careful about the datatype and length of tahat column, because you can not issue an ALTER command to modify it.
For example, when trying to change the partition column data type from data to timestamp, following error appears:

ALTER TABLE PARTITION_TABLE MODIFY (create_time TIMESTAMP)

Error report:
SQL Error: ORA-14060: data type or length of a table partitioning column may not be changed
14060. 00000 -  "data type or length of a table partitioning column may not be changed"
*Cause:    User issued ALTER TABLE statement attempting to modify
           data type and/or length of a column used to partition the
           table named in ALTER TABLE statement, which is illegal
*Action:   Avoid modifying data type and/or length of table
           partitioning column(s)