MYSQL commands in linux

First to connect to MySQL command line.

# mysql -u root -p
Password:


view all available databases.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test    
  honnikery          |                    |
+--------------------+

Select a Database in MySQL

user database-name;


Show Tabels in a Database

show tables;

Show MySQL Database Fields & Field Formats
describe table-name;


Convert MyISAM to INNODB
ALTER TABLE ENGINE=INNODB;

Repair Broken Table(s) in MySQL
repair table broke_table_name;

Getting mysql result from the last 30 days

select * from table
where date between date_sub(now(), interval 30 day) and now();

How can I use transactions in my MySQL stored procedure

wait

Create and Drop a Job SCHEDULER (DBMS_SCHEDULER AND DBMS_JOB) IN ORACLE DATABASE 10G

create  table honnikery as select * from emp where 1=2


select * from honnikery

select job,last_date,next_date,broken from dba_jobs



CREATE OR REPLACE PROCEDURE SCHEDULER_TEST
AS
BEGIN
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE honnikery';
END;
BEGIN
INSERT INTO honnikery
SELECT * FROM emp;
COMMIT;
END;
END;

declare

  l_jobno number;

begin

 dbms_job.submit(

  l_jobno,

   what=>'Proc_Test;',

   next_date=>sysdate, -- start now

   interval=>'sysdate+5/(24*60)'

 ); 

 commit;

end;

BEGIN 

  DBMS_SCHEDULER.create_job ( 

  job_name => 'SCHEDULER_JOB',

   job_type => 'PLSQL_BLOCK', 

   job_action => 'BEGIN SCHEDULER_TEST ;  END;', 

   start_date => SYSTIMESTAMP,

    repeat_interval => 'freq=hourly; byminute=5; bysecond=0;', 

    enabled => TRUE); 

    END;

  Drop a Job SCHEDULER

begin
dbms_scheduler.drop_job(job_name => 'SCHEDULER_JOB');
end;

Enable / Disable a job

BEGIN 
  DBMS_SCHEDULER.DISABLE('SCHEDULER_JOB');
END;

BEGIN 
  DBMS_SCHEDULER.ENABLE('SCHEDULER_JOB');
END;

Monitoring jobs 

 SELECT * FROM user_scheduler_jobs WHERE job_name = 'SCHEDULER_JOB';
 SELECT * FROM user_scheduler_job_log WHERE job_name = 'SCHEDULER_JOB';

ORA-00439: feature not enabled: Materialized view rewrite

   When trying to create a materialized view in SQL*Plus an error is returned:



 create materialized view mvprab
  refresh fast on commit
  enable query rewrite
  as
    select
     a    ,
   b  ,
     c ,
     ,
 
    from
      p, p1
    where
      p.a= p1.a
;

  Check the Materialized view rewrite option is enable or disable


PARAMETER                                              VALUE
------------------                                             -------------------
Materialized view rewrite                                FALSE
Materialized view warehouse refresh              FALSE


standard  edition not allow to use the Materialized view Query rewrite option

MySQL: how can I see ALL constraints on a table and database

select * from information_schema.table_constraints
where table_name='honni';





select column_name, constraint_name, referenced_column_name, referenced_table_name
from information_schema.key_column_usage
where table_name = 'table to be checked';



select *
from information_schema.table_constraints
where constraint_schema = 'honnikery'

Permissions for creating a trigger in mysql (normal user)

create trigger without SUPER privilege by adding the following line to my.cnf or my.ini    

 log_bin_trust_function_creators = 1

OR 

Give SUPER privilege on database 

grant super on *.* to 'honni'@'localhost';

revoke it

revoke super on *.* from 'my_user'@'localhost';