How to automatically rebuild partitioned local index

Once in a while somebody gets a new job in a new company  and what's more every now and again it happens in your office !

This has been Sebastano's turn, a co-worker of mine. 

He went to England for a study leave and when he got there after a while he got a new job too.

Due to his leaving now I have to do also his previous work and rebuilding partitioned local index is one of those.

At first glance it seems to be a boring and time consuming task, but why on earth can't I automate this procedure ?

Following you'll find a complete PL/SQL Block to do it.

The PL/SQL block is going to ask only the degree of parallelism and what's more the schema name.

A "rebuild_local_index.sql" file will be produced by the sqlplus client later on.

You can use it to rebuild all your partitioned local index that are owned by a schema name.

PS: Who is writing doesn't know your environment so try this procedure out before using on a production database.

See you in my next blog !

Byeeeee

***************************************************

SET serveroutput on
set lin 300
accept vParamParallelism prompt "Degree: ";
accept vParamUtente prompt "SchemaName: ";

set echo off
set heading off
set feedback off
set term off
set lin 200
set verify off

spool rebuild_local_index.sql
select 'set echo on' from dual;
select 'set feedback on' from dual;

declare
vParallelismo varchar2(2);
vUtente varchar2(50);
vSQL varchar2(255);
vSQL_LOG_OFF varchar2(255);

begin
vParallelismo:='&vParamParallelism';
vUtente:='&vParamUtente';

for riga_cursore in (select 'alter index ' || INDEX_OWNER ||'.'|| INDEX_NAME || ' rebuild partition ' || PARTITION_NAME || ' nologging parallel '||vParallelismo||' ;' testo_sql FROM dba_ind_partitions i where index_owner=vUtente and status != 'USABLE' and partition_name !='PMAX' order by index_name)
loop
vSQL:=riga_cursore.testo_sql;
dbms_output.put_line(vSQL);
end loop;

for riga_cursore_log_off in (select 'alter index '|| vUtente ||'.'|| index_name||' logging noparallel;' testo_sql_log_off FROM dba_ind_partitions where index_owner=vUtente and LOGGING='NO' and partition_name !='PMAX' group by index_name)
loop
vSQL_LOG_OFF:=riga_cursore_log_off.testo_sql_log_off;
dbms_output.put_line(vSQL_LOG_OFF);
end loop;


end;
/

spool off
set term on

MySQL Cluster NDB Restore

Every now and again you can get a failure also with MySQL Cluster NDB and the only way to bring back online is with the restore command.


Let's look how , shall we ?

After the failure of either Data Nodes we look at the log files and following error we are going to find :

"Error while reading REDO log. from......"

Something put them out of use !

Following you'll find the commands to execute a complete restore process :


1) If other Nodes as Management or Sql Nodes are still online, bring them offline.

2) Bring online the Management Nodes : ndb_mgmd  --configdir=/home/mysql/mysqlCluster -f /home/mysql/mysqlCluster/config.ini

3) Bring online the Data Nodes :  ndbd --initial

Be careful !!  The option "--initial" is going to reset and destroy all information stored in the Data Nodes.
   
4) Leaving the Sql Nodes offline execute the following commands on both the data nodes:

On the first Data Nodes :

ndb_restore -r -m -b 1 -n 3 /home/mysql/mysqlClusterData/BACKUP/BACKUP-1

Backup Id = 1
Nodeid = 3
backup path = /home/mysql/mysqlClusterData/BACKUP/BACKUP-1
Opening file '/home/mysql/mysqlClusterData/BACKUP/BACKUP-1/BACKUP-1.3.ctl'
File size 21388 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.15 ndb-7.3.4
Stop GCP of Backup: 670480
Connected to ndb!!
Successfully restored table `test/def/ciccio`
Successfully restored table event REPL$test/ciccio
Create foreign keys
Create foreign keys done
Opening file '/home/mysql/mysqlClusterData/BACKUP/BACKUP-1/BACKUP-1-0.3.Data'
File size 1556696 bytes
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_sample(5) fragment 0
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_head(4) fragment 0
_____________________________________________________
Processing data in table: test/def/ciccio(10) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 0
Opening file '/home/mysql/mysqlClusterData/BACKUP/BACKUP-1/BACKUP-1.3.log'
File size 52 bytes
Restored 54663 tuples and 0 log entries
NDBT_ProgramExit: 0 - OK
   

On the second Data Node :

ndb_restore -r -b 1 -n 4 /home/mysql/mysqlClusterData/BACKUP/BACKUP-1

Backup Id = 1
Nodeid = 4
backup path = /home/mysql/mysqlClusterData/BACKUP/BACKUP-1
Opening file '/home/mysql/mysqlClusterData/BACKUP/BACKUP-1/BACKUP-1.4.ctl'
File size 21388 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.15 ndb-7.3.4
Stop GCP of Backup: 670480
Connected to ndb!!
Opening file '/home/mysql/mysqlClusterData/BACKUP/BACKUP-1/BACKUP-1-0.4.Data'
File size 1575096 bytes
_________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_sample(5) fragment 1
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_head(4) fragment 1
_____________________________________________________
Processing data in table: test/def/ciccio(10) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 1
Opening file '/home/mysql/mysqlClusterData/BACKUP/BACKUP-1/BACKUP-1.4.log'
File size 52 bytes
Restored 55338 tuples and 0 log entries
NDBT_ProgramExit: 0 - OK


Following you'll find some handy stuff about the option of ndb_restore command:

-m = restore meta
-r  = restore records
-b  = backup ID
-n  = node ID

1) On the Second Data Nodes we have executed the ndb_restore command without the "-m" option, because the metadata information has already been restored with the first execution on the first Data Node.

2) Due to native table partitioning method, that is used to divide rows between the Data Nodes, we have to execute the ndb_restore command on both the Data Nodes.

NB: Who is writing the Post doesn't know your environment so it's necessary to try it out in a test cluster database before.

This procedure ends up bringing back online the Data and the last step that we have to do is only to restart both Sql Nodes with the following command: mysqld_safe &


Feel free to leave a comment or ask for further queries regarding the Post.

See you in my next Blog !

Byeeeee

Load Data Infile vs Date Type

Every now and again lots of data rows have to be loaded using a csv text file and once in a while it isn't well formatted. In this case we have to manually modify the data but luckily we can use the options of the 'LOAD DATA INFILE' command.

Today I'm going to look into the 'SET' option.

Following you'll find a complete example. 

Let's start creating a table, shall we ?

create table table_ok (`subject` varchar(255), `data_appo` date, `prio` varchar(255), `taskkk` int(11),
`acco` varchar(255), `cont` varchar(255), `case_appo` varchar(255), `assign` varchar(255),  `last_mod` Date);

Here is the data row in detail :

"AAAAAA","6/5/2014","BBBBB","1","CCCCC","DDDDDD","1111111111","EEEEEEE","6/5/2014"

Looking at the second column we can see that the data type isn't properly formatted, indeed mysql server is used to saving and displaying date type using the following format "2014-06-05" that is completely different from the ones seen before.

Here is the solution in detail:

load data infile '/home/mysql/appo.txt'
into table table_ok
fields terminated by ','
optionally  enclosed by '"'
LINES TERMINATED BY '\n'
(subject, @first_data, prio, taskkk, acco, cont, case_appo, assign, @second_data )
set data_appo = str_to_date(@first_data,'%d/%c/%Y'), last_mod = str_to_date(@second_data,'%d/%c/%Y') ;
 
Feel free to leave a comment or ask any further queries regarding the Post.

See you in my next Blog.


Byeeeeee