Arxiv

Archive for the ‘MySQL’ Category

Copying Tablespaces to Another Server (Transportable Tablespaces)

May 17, 2014 2 şərh

Bugünkü mövzumuz MySQL 5.6 > versiyalar nəzərdə tutulub odur ki, test etməzdən əvvəl mütləq əmin olun ki, sizin MySQL versiyanlz müvafiqdir.
İndiki hald mən 5.6.17 versiya MySQL-i aşağıdakı opsiyalarla start etmişəm.
QEYD: Transportable Tablespaces-ə nail olmağınız üçün MySQL-i aşağıdakı dəyişənlərlə start etməlisiniz

[mysqld]
innodb_file_per_table          = 1
lower_case_table_names         = 1

Daha sonra test məqsədi ilə, bir kifayət qədər böyük cədvəl tapın.
Məndə artıq biri var təqribən 2.4 gb-dır.
O cədvəli rename edək:

[root@linuxsrv3 xxx]# ls -lthr
total 2,4G
-rw-rw----. 1 mysql mysql   65 2014-05-07 11:05 db.opt
-rw-rw----. 1 mysql mysql  20K 2014-05-12 12:07 test_table.frm
-rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table.ibd

Cədvəlimizi test_table-dan test_table2-yə rename edək.:

[root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table to xxx.test_table2'
Enter password: 

real	0m1.973s
user	0m0.002s
sys	0m0.004s

Bu cəmi 1.9 saniyə çəkdi.

Rename-in başqa istifadə yeri də var. Deyək ki, biz bir database-dən cədvəli digərinə move etmək istəyirik bu zaman cədvəlin backup-ını alıb, digər database-də import etməyə ehtiyac yoxdur, aşağıdakı şəkildə bunu edə bilərik:

[root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table2 to test.test_table2'
Enter password: 

real	0m1.876s
user	0m0.005s
sys	0m0.003s

Və bizim cədvəlimiz artıq, test database-indədir:

[root@linuxsrv3 test]# ls -lthr
total 2,4G
-rw-rw----. 1 mysql mysql 8,4K 2014-05-12 11:32 ff.frm
-rw-rw----. 1 mysql mysql  96K 2014-05-12 11:33 ff.ibd
-rw-rw----. 1 mysql mysql   65 2014-05-12 11:34 db.opt
-rw-rw----. 1 mysql mysql  20K 2014-05-12 12:07 test_table2.frm
-rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table2.ibd

Deməli gördüyünüz kimi, bir cədvəli bir database-dən digərinə move etməyin sürətli və asan yolunu göstərmiş olduq.
Lakin belə bir sual yaranır, bəs biz bir cədvəli digər server-dəki database-ə copy edə bilərik mi?
Transportable Tablespaces məhz burda bizim köməyimizə gəlir.
Bir daha xatırlatmaqda fayda var ki, buna nail olmanız üçün my.cnf faylında yazının əvvəlində göstərdiyim kimi, dəyişənləri qeyd edib, MySQL-i start etmək lazımdır.
Həmçinin, sizin remote server-də artıq eyni adlı database və eyni adlı (strukturlu) cədvəliniz olmalıdır.

Bizim serverlər aşağıdakılardır:

source server : linuxsrv3
Destination server : linuxsrv4

Destination server-də artıq eyni adlı database və cədvəlimiz var.

Destination server-də biz mövcud boş cədvəlimizin tablespace-ini discard edirik:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table test_table2 discard tablespace;
Query OK, 0 rows affected (0,05 sec)

Əgər yoxlasanız görərsiniz ki, .ibd faylı databse direktoriyamızda yoxdur.

[root@linuxsrv4 test]# ls
db.opt  test_table2.frm

Source serverimizdə:

mysql> use test;
Database changed
mysql> FLUSH TABLES test_table2 FOR EXPORT;
Query OK, 0 rows affected (0,00 sec)

.ibd faylını destination server-ə copy edirik:

[root@linuxsrv3 test]# scp test_table2.ibd root@192.168.1.88:/var/lib/mysql/test
root@192.168.1.88's password: 
test_table2.ibd                       100% 2444MB  15.4MB/s   02:39 

Source server-də:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0,00 sec)

destination server-də:

[root@linuxsrv4 test]# ls
db.opt  test_table2.frm  test_table2.ibd

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table test_table2 IMPORT TABLESPACE;
ERROR 1146 (42S02): Table 'test.test_table2' doesn't exist

Əgər bu error-u gördünüzsə demək ki, owner:

[root@linuxsrv4 test]# ls -l
total 2502684
-rw-rw----. 1 mysql mysql         65 2014-05-12 15:03 db.opt
-rw-rw----. 1 mysql mysql      20166 2014-05-12 15:05 test_table2.frm
-rw-r-----. 1 root  root  2562719744 2014-05-12 15:16 test_table2.ibd

Owner-i mysql user-ə dəyişək:

[root@linuxsrv4 test]# chown mysql:mysql test_table2.ibd
[root@linuxsrv4 test]# ls -l
total 2502684
-rw-rw----. 1 mysql mysql         65 2014-05-12 15:03 db.opt
-rw-rw----. 1 mysql mysql      20166 2014-05-12 15:05 test_table2.frm
-rw-r-----. 1 mysql mysql 2562719744 2014-05-12 15:16 test_table2.ibd

İndi isə bir daha sınayaq:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> alter table test_table2 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (2 min 29,38 sec)

And checking :

mysql> select idvisit from test_table2 order by idvisit desc limit 1;
+---------+
| idvisit |
+---------+
| 7016951 |
+---------+
1 row in set (0,04 sec)

Bununla da gördüyünüz kimi 2.4 gb-lik bizim cədvəl artıq başqa bir server-də eyni data-larla mövcuddur. Bu əməliyyat toplamda 2 dəqiqə çəkir.
Təşəkkürlər.

Advertisements

Overview of Physical Database Structure in MySQL

Oracle Physical Structure oxuyarkən MySQl ilə müqayisə etmək qərarına gəldim.
Ümumiyyətlə RDBMS-i müqayisəli şəkildə öyrənmək çox faydalıdır.
Bu yazımızda MySQL 5.6.15-in Fiziki data strukturu haqqında danışacıq.
Default MySQL installation-dan sonra Linux-da aşağıdakı struktur yaranır:
MySQL Installation Directory:

[root@linuxsrv1 ~]# which mysql
/usr/bin/mysql

MySQL Configuration File:

[root@linuxsrv1 ~]# locate my.cnf
/etc/my.cnf

MySQL Data Directory:

[root@linuxsrv1 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

Bu 3 Directory-dən bizə lazım olan təbii ki, configuration faylı və Data Directory-dir. Biraz təriflərlə izah edək.
Data Directory — MySQL-in data file-larının saxladığı ərazi, direktoriya.
Data File-ın izahını mövzu irəlilədikcə verəcik.

Data Directory-yə gedək:

[root@linuxsrv1 ~]# cd /var/lib/mysql
.
.
[root@linuxsrv1 mysql]# ls -l
total 176144
-rw-rw----. 1 mysql mysql       56 Jan  4 02:53 auto.cnf
-rw-rw----. 1 mysql mysql 79691776 Feb  1 21:45 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Feb  1 21:45 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Jan  4 02:53 ib_logfile1
drwx------. 2 mysql mysql     4096 Jan  4 02:53 mysql
srwxrwxrwx. 1 mysql mysql        0 Feb  1 21:13 mysql.sock
drwx------. 2 mysql mysql     4096 Jan  4 02:53 performance_schema

1. auto.cnf — bu fayl MySQL installation zamanı avtomatik olaraq yaranır və daxilində server üçün Unique İD saxlayır.
[auto]
server-uuid=ecf27935-74c9-11e3-bc6a-080027f84458

2. mysql, performance_schema — bu iki database system database-ləridir, hər installation zamanı yaranır və System məlumatlarını özlərində saxlayırlar. Drop olunması qətiyyən.

3. ibdata1 — Tərcümə etmədən Dokumentasiyadan birbaşa bura paste edirəm:
A set of files with names such as ibdata1, ibdata2, and so on, that make up the InnoDB system tablespace. These files contain metadata about InnoDB tables, (the data dictionary), and the storage areas for the undo log, the change buffer, and the doublewrite buffer. They also can contain some or all of the table data also (depending on whether the file-per-table mode is in effect when each table is created). When the innodb_file_per_table option is enabled, data and indexes for newly created tables are stored in separate .ibd files rather than in the system tablespace.

4. ib_logfile0 və ib_logfile1 — Redo Log fayllar.


Ümumiləşdirmə N1,
ibdata1 faylı logical olaraq System Tablespace-idir. Həmçinin default MySQL configuration faylından istifadə etdikdə, bu fayl həm də cədvəl data və index-lərini saxlayır. Dolayısı ilə ibdata1 data file-dır.

Ümumiləşdirmə N2,
Yuxarıdakı tərifə əsasən default MySQL configuration ilə işlədikdə, bütün cədvəllər System Tablespace-də saxlanılır.

Bütün danışdıqlarımıza əsasən Yeri gəlmişkən, System Tablespace haqqında izah verək.

System Tablespace — Data File-ların toplusudur ki (ibdata1, ibdata2, ibdata3 və.s), özündə saxlayır:
Data Dictionary,
Undo Log,
Change Buffer,
Doublewrite Buffer.

Bütün bunları yadda saxladıqdan sonra System Tablespace-də yerləşən yuxarıda adı çəkilən hissələri izah edək.
Data Dictionary — table,column və index-lər haqqında metadata məlumatlarını saxlayır.
Undo Log — A storage area that holds copies of data modified by active transactions. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from this storage area. Ümumi olaraq Rollback Segment.
Change Buffer — Daha ərtaflı: Change Buffer
Doublewrite Buffer — Daha ətraflı: Doublewrite Buffer

ib_logfile0 və ib_logfile1 fayllarının Redo Log olduğunu demişdik.
Redo Log haqqında daha ərtaflı:
Redo Log

Ümumiləşdirmə N3,
MySQL-də default config faylı ilə start olunduqda yalnız və yalnız 1 ümumi TableSpace olacaq. System TableSpace.
1 tablespace və 1 data file.

Gəlin bu halı ətraflı araşdıraq. İlk öncə MySQL-i innodb_file_per_table = 0 olaraq start edək. Bu məqsədlə,
my.cnf faylını açırıq və [mysqld] kataloqu altında aşağıdakını yazırıq. Həmçinin hər bir commit-dən sonra dərhal data fayla flush olunması üçün biz əlavə 2 parameter də təyin edirik:

[root@linuxsrv1 ~]# nano /etc/my.cnf
.
.
[mysqld]
innodb_file_per_table          = 0
innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit = 1 

Save edirik və daha sonra start\stop:

[root@linuxsrv1 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Və yoxlayırıq Bizdə System Database-lərdən başqa Database yoxdur və bütün cədvəllər də məhz system cədvəlləridir. Dolayısı ilə:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

[root@linuxsrv1 ~]# cd /var/lib/mysql/
[root@linuxsrv1 mysql]# ls -l
total 176144
-rw-rw----. 1 mysql mysql       56 Jan  4 02:53 auto.cnf
-rw-rw----. 1 mysql mysql 79691776 Feb  2 12:20 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Feb  2 12:20 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Jan  4 02:53 ib_logfile1
drwx------. 2 mysql mysql     4096 Jan  4 02:53 mysql
srwxrwxrwx. 1 mysql mysql        0 Feb  2 12:18 mysql.sock
drwx------. 2 mysql mysql     4096 Jan  4 02:53 performance_schema

Metadata-ya sorğu göndərdikdə də nəticə gözləniləndir çünki MySQL-də başqa cədvəl yoxdur:

SELECT count(*) tables,
       concat(round(sum(table_rows)/1000000,2),'M') rows,
       concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
       concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
       concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
       round(sum(index_length)/sum(data_length),2) idxfrac
       FROM information_schema.TABLES
       WHERE  table_schema not in ('mysql','information_schema','performance_schema');

+--------+------+------+------+------------+---------+
| tables | rows | data | idx  | total_size | idxfrac |
+--------+------+------+------+------------+---------+
|      0 | NULL | NULL | NULL | NULL       |    NULL |
+--------+------+------+------+------------+---------+
1 row in set (0.00 sec)

Bir daha xatırladıram ki biz server-i innodb_file_per_table = 0 halda start etmişik, yəni bütün cədvəl və dataları yerləşir bizim 1 datafile-ımızda = System Tablespace-imizdə.

[root@linuxsrv1 mysql]# ls -l | grep ibdata*
-rw-rw----. 1 mysql mysql 79691776 Feb  2 12:20 ibdata1

Size = 79691776 = 76 MB

Bu həcmi yadda saxlayıb Yeni database və Cədvəllər yaradaq. MƏn sample Database olan Employees-den istifadə etdim.
Database import-dan sonra yoxlayaq:

mysql> show databases like 'emp%';
+-----------------+
| Database (emp%) |
+-----------------+
| employees       |
+-----------------+
1 row in set (0.00 sec)

Dərhal System Tablespace və onun istifadə etdiyi ibdata1 data faylının həcmini yenidən yoxlayaq:

[root@linuxsrv1 mysql]# ls -l | grep ibdata1 
-rw-rw----. 1 mysql mysql 281018368 Feb  2 15:30 ibdata1

Yeni size = 281018368 = 268 MB

Gördüyümüz kimi, yeni yaratdığımız cədvəllər ibdata1 = System TableSpace-də saxlandı. İddia edirik ki, yeni yaradılmış cədvəllərin toplam həcmi TƏQRİBƏN 268 – 76 = 192 MB-dir.
Metadata-dan öyrənək:

SELECT count(*) tables,
       concat(round(sum(table_rows)/1000000,2),'M') rows,
       concat(round(sum(data_length)/(1024),2),'K') data_K,
       concat(round(sum(index_length)/(1024),2),'K') idx_K,
       concat(round(sum(data_length+index_length)/(1024),2),'K') total_size_K,
       concat(round(sum(data_length)/(1024*1024),2),'M') data_M,
       concat(round(sum(index_length)/(1024*1024),2),'M') idx_M,
       concat(round(sum(data_length+index_length)/(1024*1024),2),'M') total_size_M,
       concat(round(sum(data_length)/(1024*1024*1024),2),'G') data_G,
       concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx_G,
       concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size_G,
       round(sum(index_length)/sum(data_length),2) idxfrac
       FROM information_schema.TABLES
       WHERE  table_schema not in ('mysql','information_schema','performance_schema');
+--------+-------+------------+-----------+--------------+---------+--------+--------------+--------+-------+--------------+---------+
| tables | rows  | data_K     | idx_K     | total_size_K | data_M  | idx_M  | total_size_M | data_G | idx_G | total_size_G | idxfrac |
+--------+-------+------------+-----------+--------------+---------+--------+--------------+--------+-------+--------------+---------+
|      6 | 3.91M | 138496.00K | 54464.00K | 192960.00K   | 135.25M | 53.19M | 188.44M      | 0.13G  | 0.05G | 0.18G        |    0.39 |
+--------+-------+------------+-----------+--------------+---------+--------+--------------+--------+-------+--------------+---------+
1 row in set (0.00 sec)

188.44 MB Təqribi qiymət düzgün gəldi. Bu da bir daha sübut etdi ki, Default olaraq MySQL-də hər şey System Tablespace-də və 1 data file-da saxlanılır.

Araşdırmamızı davam etdirək.
İkinci bir anlayışı irəli sürək.
Tərif:
MySQL-də 2 növ TableSpace var. System TableSpace-i ki, biz artıq onun haqqında danışdıq və “Hər Cədvəlin Öz TableSpace”-i. Buna biz innodb_file_per_table = 0 yox, məhz innodb_file_per_table = 1 etməklə nail oluruq.
Dolayısı ilə default olmayan config faylı ilə MySQL-li start etməklə biz per-table-tablespace əldə etmiş oluruq

Əyani surətdə göstərək:
İlk öncə test database-imizi drop edək:

mysql> drop database employees;
Query OK, 6 rows affected (0.85 sec)

Daha sonra my.cnf faylında dəyişiklik edək və MySQL restart edək.

[root@linuxsrv1 ~]# nano /etc/my.cnf
.
.
innodb_file_per_table          = 1

[root@linuxsrv1 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Employee database-i yenidən import edək. Bu andan etibarən employee directory-nin daxilində bəzi digər faylların yarandığını görürük:

[root@linuxsrv1 mysql]# cd /var/lib/mysql/employees/
[root@linuxsrv1 employees]# ll -h | grep .ibd
-rw-rw----. 1 mysql mysql 112K Feb  2 16:13 departments.ibd
-rw-rw----. 1 mysql mysql  29M Feb  2 16:13 dept_emp.ibd
-rw-rw----. 1 mysql mysql 128K Feb  2 16:13 dept_manager.ibd
-rw-rw----. 1 mysql mysql  22M Feb  2 16:13 employees.ibd
-rw-rw----. 1 mysql mysql 140M Feb  2 16:15 salaries.ibd
-rw-rw----. 1 mysql mysql  40M Feb  2 16:14 titles.ibd

Məhz bu .ibd fayllar hər cədvəlin öz TableSpace\Data File-ıdır. Faktiki olaraq burdan belə nəticə çıxır ki, innodb_file_per_table = 1 halında hər cədvəlin öz DATA File-ı olur.

Bunu biz Metadata-dan da öyrənə bilərik.
information_schema-ya məxsus 3 cədvəl var ki, bizim üçün faydalı məlumatları özündə saxlayır:

INNODB_SYS_TABLES , INNODB_SYS_DATAFILES, INNODB_SYS_TABLESPACES

Məsələn, yuxarıda iddia etdiyimiz kimi, hər cədvəlin öz data file-ı olduğunu sübut etmək üçün:

mysql> select * from information_schema.INNODB_SYS_DATAFILES where path like './emp%';
+-------+------------------------------+
| SPACE | PATH                         |
+-------+------------------------------+
|    52 | ./employees/employees.ibd    |
|    53 | ./employees/departments.ibd  |
|    54 | ./employees/dept_manager.ibd |
|    55 | ./employees/dept_emp.ibd     |
|    56 | ./employees/titles.ibd       |
|    57 | ./employees/salaries.ibd     |
+-------+------------------------------+
6 rows in set (0.00 sec)

System Tablespace-də olmayan cədvəllərin siyahısını öyrənmək üçün:

SELECT name, flag, space
  FROM information_schema.INNODB_SYS_TABLES
 WHERE space != 0;
+----------------------------+------+-------+
| name                       | flag | space |
+----------------------------+------+-------+
| employees/departments      |    1 |    53 |
| employees/dept_emp         |    1 |    55 |
| employees/dept_manager     |    1 |    54 |
| employees/employees        |    1 |    52 |
| employees/salaries         |    1 |    57 |
| employees/titles           |    1 |    56 |
| mysql/innodb_index_stats   |    1 |     2 |
| mysql/innodb_table_stats   |    1 |     1 |
| mysql/slave_master_info    |    1 |     4 |
| mysql/slave_relay_log_info |    1 |     3 |
| mysql/slave_worker_info    |    1 |     5 |
+----------------------------+------+-------+
11 rows in set (0.00 sec)

TableSpace haqqından maraqlı məlumatları əldə etmək üçün isə:

mysql> SELECT *
    ->   FROM information_schema.INNODB_SYS_TABLESPACES
    ->  WHERE name LIKE 'emp%';
+-------+------------------------+------+-------------+----------------------+-----------+---------------+
| SPACE | NAME                   | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE |
+-------+------------------------+------+-------------+----------------------+-----------+---------------+
|    52 | employees/employees    |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    53 | employees/departments  |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    54 | employees/dept_manager |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    55 | employees/dept_emp     |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    56 | employees/titles       |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    57 | employees/salaries     |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
+-------+------------------------+------+-------------+----------------------+-----------+---------------+
6 rows in set (0.00 sec)

General Difference Oracle vs. MySQL (part 1) : USER\SCHEMA\DATABASE

Oracle, MySQL Physical Data Structure və Logical Data Structure mövzularına girməzdən əvvəl bir sıra ümumi fərqlikləri ayrıca yazılar şəklində yazmağı məqsədə uyğun hesab edirəm…

Ilkin olaraq insanları çaşdıran məqamlardan biri , USER\SCHEMA\Database anlayışlarıdır.
Bu anlayışlar 2 RDBMS-də fərqlidir..Dolayısı ilə araşdırmaqda fayda var.

Bütün digər fərqlilikləri yazmazdan əvvəl mənim üçün əsas olan, DATABASE anlayışını izah edək:
Oracle-da:

database

Organized collection of data treated as a unit. The purpose of a database is to store and retrieve related information. Every Oracle database instance accesses only one database in its lifetime.

instance

The combination of the system global area (SGA) and background processes. An instance is associated with one and only one database.

Qısa olaraq:

Instance = Background Processes + Memory Region called SGA (System Global Area)
Database = Data files + Control Files + Online Redolog files

Database daimidir. Diskdə yerləşir.
Instance müvəqqətidir. RAM-da yerləşir.

Database aktiv deyil. Sadəcə disk-də yerləşən fayllar toplusudur.
Instance aktivdir. Əməliyyat sistemində hər hansı digər program kimi prosess şəklində fəaliyyət göstərir. Dolayısı ilə instance OS process-dir.

Oracle-da bir başa Database-ə qoşulma yoxdur, siz instance-a qoşulursuz, Database-i instance idarə edir.

İnstance-sı “startup” və “shutdown” edə bilərsiniz. Database-i isə “mount” və “open” , həmçinin “dismount” və “closed” edə bilərsiniz. Dolayısı ilə bir kimsə Oracle üçün “Oracle Start olundu” və yaxud “Oracle shutdown oldu” dedikdə çox güman ki, birinci halda “İnstance start olundu və Database open oldu” ikinci halda isə “Database closed oldu və İnstance shutdown oldu” demək istəmişdir.

Müqayisəli şəkildə irəliləyək:
Əyani şəkildə Oracle Database Start-ı göstərək:

-- 1
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 26 02:42:59 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> 

--2 
SQL> startup nomount 
ORACLE instance started.

Total System Global Area  826753024 bytes
Fixed Size                  2217872 bytes
Variable Size             583010416 bytes
Database Buffers          239075328 bytes
Redo Buffers                2449408 bytes

Əgər diqqətlə output-a baxsaq görərik ki, ORACLE instance started.. Yani hələ database yoxdur.
Davam edək:

SQL> alter database mount;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DB01      MOUNTED

Indi isə gördüyünüz kimi, Databse mount olundu.
Daha sonra da:

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DB01      READ WRITE

Ən sonda da Database open olundu və istifadəyə hazırdır.
Burda daha bir anlayışı irəli sürək ki, 1 instance yalnız 1 database-ə connect ola bilər. Dolayısı ilə instanc-a qoşulmuş hər hansı user öz object-ini məhz o Database-də yaradacaq.
Yəni USER-in object-ləri məhz connect olduğu Database-də saxlanılacaq. MySQL-də isə bu belə deyildir. Gəlin araşdıraq.

MySQL-də Database anlayışına gəldikdə orda da həmçinin, Oracle-ın database tərifi geçərlidir. Yəni ki, MySQL-də də database-də fiziki məlumatlar saxlanılır. Diskdə saxlanılır və.s Lakin prinsipial fərqlərə gəlin birlikdə baxaq.
Əsas etibarilə onu qeyd etməliyik ki, MySQL-də Oracle-ın İnstance anlayışı yoxdur həmçinin MySQL-də İnstance start olunmur, Database OPEN və yaxud CLOSE olunmur.

MySQL OS-də Service kimi start olunur. Bu START-ı siz Oracle-ın yuxardakı addımlarının toplusu olaraq qəbul edə bilərsiniz.
MySQL service Start olunduqdan sonra USER session açır Session Thread, Server Thread-ə connect olur və işləməyə başlayır.
Daha ətraflı izah üçün gəlin əyani olaraq baxaq:

[root@localhost ~]# service mysqld start
Starting mysqld:                                           [  OK  ]

Bu bizim həm bir növü Oracle-dakı İnstance start və Database open-umuzdur 😉

Daha sonra connect oluruq:

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Default SUPER user olan root-la qoşulduq. İndi isə hər hansı USER yaradaq:

mysql> create user xxx identified by '12345';
Query OK, 0 rows affected (0.00 sec)

Disconnect olaq və yeni yaranmış USER-lə connect olaq:

mysql> exit
Bye
[root@localhost ~]# mysql -u xxx -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

İndi isə hər hansı əməliyyat etməyə çalışaq:

mysql> create table t1(x date);
ERROR 1046 (3D000): No database selected


ERROR 1046 (3D000): No database selected
— ERROR onu göstərir ki, cədvəlimizin hansı Database-də yerləşəcəyini biz seçməmişik.

Davam edək, MySQL Server-də hansı Database-lərin olduğuna baxaq:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

Bizim yeni yaradılmış USER-imiz Metadata (information_schema adından da göründüyü kimi) database-indən başqa heçnəyi görmür. Dolayısı ilə yeni yaradılmış USER heç bir əməliyyat yerinə yetirə bilməyəcək.
İndi isə eyni əməliyyatları Oracle-da edək:

SQL> create user xxx identified by 12345;

User created.

SQL> grant create session to xxx;

Grant succeeded.

SQL> grant create table to xxx;

Grant succeeded.

SQL> connect xxx/12345
Connected.
SQL> create table t1(x date);

Table created.

İndi isə Ümumiləşdirək. Əsas Fərqləndirmə:
Diqqət yetirdinizsə MySQL-də USER-in connect olması üçün hər hansı Database-in varlığına ehtiyac yoxdur, connect olduqdan sonra mövcud sonsuz sayda database-lərdən birini seçib işləyə bilər. Yəni MySQL user connect olduqdan sonra database-ini özü seçir.
Oracle-da isə əgər diqqət yetirdinizsə, Oracle install olunduqda və yaxud daha sonra Database create olunub, İnstance start-dır , Database open-dır. Və yeni yaradılmış bu xxx USER-i artıq məhz həmin Database-də fəaliyyət göstərir. Müvafiq GRANT-lar verdikdən sonra, session aça bilir cədvəlini create edə bilir.

Yenidən MySQL ilə davam edək…Super USER ilə connect olaq və database yaradaq:

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.15 MySQL Community Server (GPL)

mysql> create database blog_test;
Query OK, 1 row affected (0.00 sec)

Yeni yaratdığımız USER-ə bu database-dən istifadə üçün GRANT verək:

mysql> create database blog_test;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on blog_test.* to xxx;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for xxx;
+----------------------------------------------------------------------------------------------------+
| Grants for xxx@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xxx'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |
| GRANT ALL PRIVILEGES ON `blog_test`.* TO 'xxx'@'%'                                                 |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Grant-dan göründüyü kimi, xxx user-ini artıq blog_test database-indən istifadə hüququ vardır.
xxx useri ilə qoşulaq:

[root@localhost ~]# mysql -u xxx -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog_test          |
+--------------------+
2 rows in set (0.00 sec)

mysql> use blog_test;
Database changed
mysql> create table t1(x date);
Query OK, 0 rows affected (0.23 sec)

DATABASE məntiqini izah etdikdən sonra istərdim ki, USER anlayışına da qısaca izah verək.
USER Oracle-da gördüyümüz kimi, connect olur və hər hansı object-ini yaratdıqda , həmin object-lər evvelceden connect olduğu database-də yaranaraq həmin USER tərəfindən OWN olur. Yəni hər USER öz objectinin OWNER-idir.
MySQL-də isə USER hər hansı object-in OWNER-i deyil hətta, OWNER-lik anlayışı yoxdur.

Əyani olaraq göstərək.
Oracle-da USER + objects = SCHEMA. Dolayısı ilə digər SCHEMA-dan (USER-in OWN etdiyi obyektlərdə) hər hansı məlumat oxumaq istəyək:
Bu məqsədlə ikinci nümunə user yaradaq:

SQL> create user xxx2 identified by 12345;

User created.

SQL> grant create session to xxx2;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO xxx;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO xxx2;

Grant succeeded.

Daha sonra xxx2 USER-lə connect olub cədvəl yaradaq və məlumat daxil edək:

SQL> create table t2(x date);

Table created.
SQL> insert into t2(x) values((select sysdate from dual));

1 row created.

SQL> select * from t2;

X
---------
26-JAN-14

Həmçinin xxx user-lə connect olub daha əvvəl yaratdığımız cədvələ məlumat daxil edək:

SQL> connect xxx/12345
Connected.
SQL> insert into t1(x) values((select sysdate from dual));

1 row created.

SQL> select * from t1;

X
---------
26-JAN-14

xxx user ilə xxx2 user-in cədvəlinə sorğu göndərək. SYS-lə müvafiq GRANT verək:

SQL> grant select on xxx2.t2 to xxx;

Grant succeeded.

xxx-lə connect olaq və select verək:

SQL> select * from xxx2.t2;

X
---------
26-JAN-14

Gördüyümüz kimi Oracle-da 1 Database-də bir neçə SCHEMA olur. SCHEMA isə hər bir USER və onun objectləri deməkdir.

Burda da MySQL fərqlənir…MySQL-də DATABASE = SCHEMA. Dolayısı ilə sonsuz sayda USER sonsuz sayda DATABASE\SCHEMA-dakı müvafiq GRANT verməklə oxuya bilər. Yəni bir daha qeyd edirəm ki, USER-in object-ləri yoxdur MySQL-də. Database-lərdə yerləşir bütün məlumatlar və hər hansı USER digər DATABASE\SCHEMA-dan məlumat istəyirsə ona bunun privilige-i olmalıdır.

mysql> create user xxx2 identified by '12345';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on blog_database.* to xxx2;
Query OK, 0 rows affected (0.00 sec)

İndi isə hər 2 user-lə connect olaq və DATABASE\SCHEMA-dan nümunə cədvələ select verək:
xxx user ilə:

[root@localhost ~]# mysql -u xxx -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> insert into blog_test.t1 values((select curdate()));
Query OK, 1 row affected (0.09 sec)

mysql> select * from blog_test.t1;
+------------+
| x          |
+------------+
| 2014-01-26 |
+------------+
1 row in set (0.00 sec)

xxx2 useri ilə:

[root@localhost ~]# mysql -u xxx2 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from blog_test.t1;
+------------+
| x          |
+------------+
| 2014-01-26 |
+------------+
1 row in set (0.00 sec)

Gördüyümüz kimi 2 müxtəlif USER eyni SCHEMA-dan select etdi.
Ümumiləşdirsək:
MySQL-də DATABASE = SCHEMA
Oracle-da USER + objects = SCHEMA

Ümumi və əsas fərqlilikləri başa düşmək vacib məsələdir.
Bu yazıda sizlərlə , Oracle və MySQL-də DATABASE ,USER və SCHEMA anlayışlarına baxdıq.
Ümidvaram ki, faydalı oldu.

Təşəkkürlər 😉

Exploring Sequence in Oracle and MySQL

Bu yazıda hər 2 RDBMS-də Sequence (ardıcıllıq) anlayışlarına baxacıq.
İstifadə olunan versiyalar Oracle 11g R2, MySQL 5.6.x.
İlk öncə biraz teorik məlumatlar.
Sequence avtomatik olaraq bizim yerimizə, DB tərəfindən yaradılan unique rəqəmlər ardıcıllığıdır.
Çox zaman Primary Key (id) column-lar üçün istifadə olunan bu rəqəmlər, bizə təkrarsız ardıcıllıq verir ki, PK anlayışı da elə bundan ibarətdir.
Dolayısı ilə çox faydalıdır və demək olar ki əhəmiyyətli bütün cədvəllərdə istifadə olunur.
Ilk öncə Oracle-dan başlayacıq. Nümunə cədvəlimiz:

-- Oracle

create table t1(
id numeric,
firstname varchar2(25),
lastname varchar2(25),
primary key(id)
);

Gördüyümüz kimi, id bizdə PK-dır və biz istəyirik ki, hər dəfə ardıcıl ədədlər insert olunsun.
Sequence yaradaq:

create sequence id_seq;
>>>
sequence ID_SEQ created.

Yuxarıdakı create-imiz faktiki olaraq default olaraq aşağıdakı kimi icra olundu:

CREATE SEQUENCE  
"HR"."ID_SEQ"  
MINVALUE 1
MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 
START WITH 1 
CACHE 20 
NOORDER  
NOCYCLE ;

Bunu biraz izah edək. Default sequence creation:
* “HR”.”ID_SEQ” = burdan təbii ki, aydın olur ki, bizim sequence owner-i HR-dır.
* MINVALUE 1 = yəni bizim sequence-in minimum qiyməti 1 ola bilər.
* MAXVALUE 9999999999999999999999999999 = maximum qiymət
* INCREMENT BY 1 = yəni 1-1 artır.
* START WITH 1 = artırmağa 1-dən başla
* CACHE 20 = hər növbəti 20 ardıcıllığı cache-də saxla
* NOORDER = only relevant to clustered database
* NOCYCLE = dövr etmə. MINVALUE və yaxud MAXVALUE-ya çatdıqda error verəcək.

Və çox zaman elə bizə lazım olan göstəricilərlə sequence yaradıldı. Təkcə performance üçün CACHE size-ı artıra bilərsiniz.

* QEYD N1:
Sequence heç bir cədvələ təhkim olunmur, rollback və yaxud commit-ə tabe deyildir, hər hansı transaction-dan azaddır müstəqil olaraq artır.
*

Yuxarıdakı qeyddən belə nəticə çıxır ki, əgər 1 cədvəl üçün 10 ardıcıl rəqəm generate olunubsa 2ci bir cədvəl bundan sonra sequence-dən istifadə etsə 11-dən başlamalı olacaq.
Praktiki istifadəsinə baxaq. Sequence-dən ardıcıl rəqəmi əldə etmək məqsədilə nextval-dan istifadə etmək lazımdır:

insert into t1(id,firstname,lastname) values(id_seq.nextval,'shahriyar','rzayev');

Məlumata baxırıq:

SQL> select * from t1;

	ID FIRSTNAME		     LASTNAME
---------- ------------------------- -------------------------
	 1 shahriyar		     rzayev

Eyni datanı 5 dəfə daha insert edək.

SQL> select * from t1;

	ID FIRSTNAME		     LASTNAME
---------- ------------------------- -------------------------
	 1 shahriyar		     rzayev
	 2 shahriyar		     rzayev
	 3 shahriyar		     rzayev
	 4 shahriyar		     rzayev
	 5 shahriyar		     rzayev
	 6 shahriyar		     rzayev

6 rows selected.

Dahiyanə işləyir 😉

Sequence creation üzərində işləyərək artımın hansı ardıcıllıqla nədən başlayacağını və.s təyin edə bilərik.

Məsələn aşağıdakı sequence 10-dan 5-5 arta-arta davam edir.

-- 1
create sequence id_seq2 start with 10 increment by 5;

-- 2
truncate table t1;

-- 3
insert into t1(id,firstname,lastname) values(id_seq2.nextval,'shahriyar','rzayev');
insert into t1(id,firstname,lastname) values(id_seq2.nextval,'shahriyar','rzayev');
insert into t1(id,firstname,lastname) values(id_seq2.nextval,'shahriyar','rzayev');
-- 4

SQL> select * from t1;

	ID FIRSTNAME		     LASTNAME
---------- ------------------------- -------------------------
	10 shahriyar		     rzayev
	15 shahriyar		     rzayev
	20 shahriyar		     rzayev

* QEYD N2:
Sequence sıfırlamaq mümkün deyildir, bunun yeganə üsulu sequenci droplamaq və yenidən yaratmaqdır.
*

Burdan belə nəticə çıxır ki, hər hansı cədvəldə delete-dən yaranmış gap-ləri reorder etməklə doldurmaq mümkün deyil.
Yəni, 1, 2, 3, 4, 5, 20, 50 — gəlirsə deməli elə də gedəcək.

İndi isə MySQL-də sequence anlayışına baxaq..
AUTO_İNCREMENT = SEQUENCE.
MySQL-də fərqlər:
* AUTO_İNCREMENT hər cədvələ təhkim olunur.
* PK olmayan column-da create oluna bilmir.
* insert etdikdə auto_increment column-u qeyd etməyə ehtiyac yoxdur.
* NULL insert etdikdə increment olunaraq yeni value insert olunur.
* Ardıcıllığı reorder etmək olur.
və.s

Digər fərqlərə də baxacıq.
Nümunə cədvəlimiz:

-- MySQL

create table t1(
id int not null auto_increment,
firstname varchar(25),
lastname varchar(25),
primary key(id)
);

Gördüyümüz kimi, PK olan İD column-a cədvəli yaradarkən qeyd olunur.
İnsertlər edək:

insert into t1(firstname,lastname) values('shahriyar','rzayev');
insert into t1(firstname,lastname) values('shahriyar','rzayev');
insert into t1(firstname,lastname) values('shahriyar','rzayev');
insert into t1(firstname,lastname) values('shahriyar','rzayev');
insert into t1(firstname,lastname) values('shahriyar','rzayev');

mysql> select * from t1;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | shahriyar | rzayev   |
|  2 | shahriyar | rzayev   |
|  3 | shahriyar | rzayev   |
|  4 | shahriyar | rzayev   |
|  5 | shahriyar | rzayev   |
+----+-----------+----------+
5 rows in set (0.00 sec)

İnsert zamanı İD column-u qeyd etmədik.
İndi isə İD 3 və 4 olan sətrləri silək:

-- 1

delete from t1 where id=3 or id=4;

-- 2

mysql> select * from t1;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | shahriyar | rzayev   |
|  2 | shahriyar | rzayev   |
|  5 | shahriyar | rzayev   |
+----+-----------+----------+
3 rows in set (0.00 sec)

1, 2, 5 = biz istəyirik ki, 1, 2, 3 kimi sıralansın.

-- 1
ALTER TABLE t1 DROP `id`;

-- 2
ALTER TABLE t1 AUTO_INCREMENT = 1;

-- 3
ALTER TABLE t1 ADD `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

mysql> select * from t1;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | shahriyar | rzayev   |
|  2 | shahriyar | rzayev   |
|  3 | shahriyar | rzayev   |

Çox gözəl məqsədə çatdıq.
Oracle-da göstərdiyimiz kimi, MySQL-də də sequence-in hansı ədəddən başlıyacağını və nə qədər artacağını göstərə bilərik. Burda maraqlı məqam ondan ibarətdir ki, Global və Session olaraq verə bilərik. Global olaraq verildikdə RDBMS-də bütün cədvəllərdə olan auto_increment-lərə şamil olunacaq. Session based isə təbii ki, yalnız aktiv sessiya üçün geçərlidir.

-- 1 

mysql> set @@session.auto_increment_increment=10;
Query OK, 0 rows affected (0.01 sec)

-- 2

mysql> set @@session.auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)

-- 3
drop table t1;

-- 4

create table t1(
id int not null auto_increment,
firstname varchar(25),
lastname varchar(25),
primary key(id)
);

-- 5

mysql> insert into t1(firstname,lastname) values('shahriyar','rzayev'),('shahriyar','rzayev'),('shahriyar','rzayev'),('shahriyar','rzayev');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 6

mysql> select * from t1;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  5 | shahriyar | rzayev   |
| 15 | shahriyar | rzayev   |
| 25 | shahriyar | rzayev   |
| 35 | shahriyar | rzayev   |
+----+-----------+----------+
4 rows in set (0.00 sec)

Beləliklə auto_increment_incrementauto_increment_offset system dəyişənlərinin köməyi ilə biz 5-dən başlayaraq 10-10 artım əldə etdik.

Düşünürəm ki, ümumi mövzumuzu yekunlaşdırdıq.

Təşəkkürlər 😉

Constraints in Oracle and MySQL

Constraint-lər bizə business qaydalarımızı cədvəllərimizdə təyin etməyə kömək edən vasitələrdir.
Sadə dillə izah etməyə çalışsaq məsələn,
Deyək ki, bizim bussines qaydamız tələb edir ki, bütün soyadlar təkrarsız olsun, bütün date-lər yalnız 2013-cü ildən böyük olsun, və yaxud da bütün istifadəçi yaşları yalnız 23-dən böyük olsun və.s
Bu kimi istəklərimizi cədvəldə constraintlər yaratmaqla həll etmiş oluruq. və yalnız bizim bu business rule-a uyğun gələn data-lar cədvələ daxil edilə biləcək.

Oracle-da aşağıdakı constraint-lər var:

UNİQUE
NOT NULL
PRİMARY KEY
FOREİGN KEY
CHECK

UNİQUE:

UNİQUE constraint adından da göründüyü kimi, ya 1 column ya da column qruplarının unikal məlumatlarla doldurulmasını təmin edir. Dolayısı ilə 1 dəfə daxil edilən məlumat 2ci dəfə ora düşə bilməz. Öz işinə görə PRİMARY KEY-ə bənzəyən UNİQUE-ın yeganə fərqi NULL datanın daxil edilməsinə icazə verməsidir. Burdan belə nəticə çıxır ki, NULL unikal sayılmır, həmçinin sonsuz sayda NULL insert etmək olar.
Burdan daha bir nəticə çıxır ki, PK(primary key) = unique + not null.

Digər tərəfdən onu da qeyd etməliyik ki, unique constraint təyin edilmiş column-lar index-li olur. Yəni, unique constraint yaradıldıqda, Oracle column-da index-in olub olmadığına baxacaq. Əgər index tapılmasa onu avtomatik olaraq yaradacaq.
Daha bir dolayı nəticəmiz bundan ibarətdir ki, unique = index + constraint.

UNİQUE ilə bağlı daha bir məlumatı da verməliyik ki, B*TREE index-lər NULL-u əhatə etmir.
Burdan belə nəticə çıxarırıq ki əgər biz cədvəldə NULL məlumat axtarsaq index istifadə olunmayacaq bu da full table scan-a gətirib çıxardacaq. Full scan ise performance killer hesab olunur.

Teorik məlumatdan sonra keçək praktikaya:

-- Oracle

-- UNİQUE

-- Syntax 1

create table t20(
id number,
firstname varchar2(15),
lastname varchar2(15),
unique key(id)
);

>>>
Error report -
SQL Error: ORA-00906: missing left parenthesis
00906. 00000 -  "missing left parenthesis"
*Cause:    
*Action:

-- Syntax 2

create table t20(
id number,
firstname varchar2(15),
lastname varchar2(15),
unique(id)
);

>>>
table T20 created.

-- Syntax 3

create table t20(
id number unique,
firstname varchar2(15),
lastname varchar2(15)
);

>>>
table T20 created.

Yuxarıdakı 3 syntax-dan 2si Oracle-da dəstəklənir.
İndi isə yaranmış constraint haqqında məlumatları əldə etmək üçün kiçik sorğudan istifadə edək.

— Oracle

select 
constraint_name,
constraint_type
from USER_CONSTRAINTS where table_name='T20';

>>>
SYS_C0011585	U

SYS_C0011585 = unique constraint-in internal generate olunan adıdır.

Əgər spesifik ad vermək istəyiriksə ki bu çox faydalı hesab olunur:

create table t20(
id number,
firstname varchar2(15),
lastname varchar2(15),
constraint id_unikal unique(id)
);

select 
constraint_name,
constraint_type
from USER_CONSTRAINTS where table_name='T20';

>>>
ID_UNIKAL	U

İndi isə eyni testləri MySQL-də edək.

-- MySQL

-- Syntax 1

mysql> create table t20(
    -> id int,
    -> firstname varchar(15),
    -> lastname varchar(15),
    -> unique key(id)
    -> );
Query OK, 0 rows affected (0.09 sec)

-- Syntax 2

mysql> create table t20(
    -> id int,
    -> firstname varchar(15),
    -> lastname varchar(15),
    -> unique(id)
    -> );
Query OK, 0 rows affected (0.11 sec)

-- Syntax 3

mysql> create table t20(
    -> id int unique,
    -> firstname varchar(15),
    -> lastname varchar(15)
    -> );
Query OK, 0 rows affected (0.11 sec)

Hər 3 syntax dəstəklənir.
Oracle-da constraint metadatanı oxumağı göstərdik indi də MySQL üçün göstərək.

-- MySQL

-- 1ci usul 

mysql> show create table t20;
>>>
CREATE TABLE `t20` (
  `id` int(11) DEFAULT NULL,
  `firstname` varchar(15) DEFAULT NULL,
  `lastname` varchar(15) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-- 2ci usul

mysql> select constraint_name, constraint_type from information_schema.table_constraints where table_schema='join_test' and table_name='t20';
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| id              | UNIQUE          |
+-----------------+-----------------+
1 row in set (0.00 sec)

Və təbii ki yenə Oracle-da olduğu kimi özümüz constraint name verə bilərik.

-- MySQL

 
create table t20(
id int,
firstname varchar(15),
lastname varchar(15),
constraint id_unikal unique key(id)
);


-- metadata

mysql> select constraint_name, constraint_type from information_schema.table_constraints where table_schema='join_test' and table_name='t20';
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| id_unikal       | UNIQUE          |
+-----------------+-----------------+
1 row in set (0.00 sec)

Düşünürəm ki, UNİQUE constraint haqqında bu qədər kifayətdir davam edirik.

CHECK

-- Oracle

-- Syntax 1

create table t20(
id number,
firstname varchar2(15),
lastname varchar2(15),
check (id in(1,2,3,4))
);

>>>
table T20 created.

-- insert edek

insert into t20(id,firstname,lastname) values(5,'shahriyar','rzayev');

>>>
insert into t20(id,firstname,lastname) values(5,'shahriyar','rzayev')
Error report -
SQL Error: ORA-02290: check constraint (HR.SYS_C0011140) violated
02290. 00000 -  "check constraint (%s.%s) violated"
*Cause:    The values being inserted do not satisfy the named check
           
*Action:   do not insert values that violate the constraint.

-- Syntax 2

create table t20(
id number,
firstname varchar2(15),
lastname varchar2(15),
constraint id_check check (id in(1,2,3,4))
);
>>>
table T20 created.

2 Syntax dəstəklənir. Bundan əlavə gördüyümüz kimi, id CHECK constraint-ə uyğun olaraq yalnız 1, 2, 3, 4 daxil etməyə icazə verir biz 5 insert etməyə çalışdığımız üçün error çıxır. Çox gözəl.

Constraint siyahısına baxaq:

-- Oracle

select 
constraint_name,
constraint_type
from USER_CONSTRAINTS where table_name='T20';

>>>
ID_CHECK	C

CHECK-dan danışdığımız üçün NOT NULL-U qeyd etməliyik. Faktiki olaraq Oracle-da NOT NULL elə CHECK constraintdir.

-- Oracle

-- firstname-e not null verek

create table t20(
id number,
firstname varchar2(15) not null,
lastname varchar2(15),
constraint id_check check (id in(1,2,3,4))
);

-- constraint siyahısına baxaq

select 
constraint_name,
constraint_type
from USER_CONSTRAINTS where table_name='T20';

>>>
SYS_C0011142	C
ID_CHECK	C

Bizim nümunədə SYS_C0011142 = NOT NULL-un necə deyərlər adıdır və Diqqət yetirsək görərik ki,
her iki constraint-in type-ı C-dir yəni CHECK.
Çox kədərli hal kimi onu qeyd etməliyik ki, MySQL-də CHECK constraint dəstəklənmir dolayısı ilə nəzərə alsaq ki, NOT NULL = CHECK o zaman burdan belə bir nəticə çıxartmaq olar ki, NOT NULL MySQL-də ayrıca constraint kimi götürülmür gəlin test edək.

-- MySQL

create table t20(
id int,
firstname varchar(15) not null,
lastname varchar(15),
constraint id_check check (id in(1,2,3,4))
);

Cədvəl definition-nunda not null və check olmağına baxmayaraq MySQL üçün bunlar constraint hesab olunmur.
Datadictionary-dən constraint list-ə baxmaq istədikdə də bunun şahidi oluruq.

mysql> select constraint_name, constraint_type from information_schema.table_constraints where table_schema='join_test' and table_name='t20';
Empty set (0.00 sec)

Dokumentasiyada da bu açıq şəkildə qeyd olunub:

The CHECK clause is parsed but ignored by all storage engines.

Çox təəssüf.

PRİMARY KEY:

-- Oracle

-- Syntax 1

create table t20(
id number,
firstname varchar2(15),
lastname varchar2(15),
primary key(id)
);

select 
constraint_name,
constraint_type
from USER_CONSTRAINTS where table_name='T20';

>>>
SYS_C0011147	P


-- Syntax 2

create table t20(
id number,
firstname varchar2(15),
lastname varchar2(15),
constraint pk_id primary key(id)
);

select 
constraint_name,
constraint_type
from USER_CONSTRAINTS where table_name='T20';

>>>
PK_ID	P

Ümumi olaraq Burdan aşağıdakı nəticə çıxır:
Oracle-da PK yaradarkən ayrıca olaraq NOT NULL qeyd etməyə ehtiyac yoxdur.
Suggested by Adigozalov Qurban:
From Oracle DOC:
A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

Həmçinin yuxarıdakı nümunədə də göstərildiyi kimi, PK-ya name verə bilirik.
indi isə eyni testləri MySQL-də edək:

-- MySQL

-- Syntax 1

create table t20(
id int,
firstname varchar(15) not null,
lastname varchar(15),
primary key(id)
);

-- Metadata

mysql> select constraint_name, constraint_type from information_schema.table_constraints where table_schema='join_test' and table_name='t20';
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| PRIMARY         | PRIMARY KEY     |
+-----------------+-----

-- Syntax 2

create table t20(
id int,
firstname varchar(15) not null,
lastname varchar(15),
constraint pk_id primary key(id)
);

-- Metadata

mysql> select constraint_name, constraint_type from information_schema.table_constraints where table_schema='join_test' and table_name='t20';
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| PRIMARY         | PRIMARY KEY     |
+-----------------+-----------------+
1 row in set (0.00 sec)

Yuxarıdan belə nəticə çıxarırıq ki, MySQL-də Primay Key constraint-in adını dəyişmək olmur.

From Documentation:
The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.

Foreign Key-lə bağlı əlavə yazı olacaq.

Təşəkkürlər 😉

Altering Table Definitions after Creation Oracle vs. MySQL

Bu yazıda sizlərlə ALTER statement-lərə baxacıq.
1 nümunə Oracle-dan daha sonra da 1 nümunə MySQL-dən.

Mövcud cədvəlimiz hər 2 DB-də eynidir.

Adding Columns:

-- Oracle

-- Syntax 1

alter table t1 add column address varchar2(25) not null;
>>>
Error starting at line : 3 in command -
alter table t1 add column address varchar2(25) not null
Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


-- Syntax 2

alter table t1 add address varchar2(25) not null;
>>>
Error starting at line : 5 in command -
alter table t1 add address varchar2(25) not null
Error report -
SQL Error: ORA-01758: table must be empty to add mandatory (NOT NULL) column
01758. 00000 -  "table must be empty to add mandatory (NOT NULL) column"
*Cause:    
*Action:

Burdan çıxan nəticə budur ki, 1ci Syntax növünü Oracle dəstəkləmir. 2-ci olaraq isə NOT NULL constraint-li column-u məlumatlar olan cədvələ daxil etmək mümkün deyil. ERROR-dan da aydın görünür:

table must be empty to add mandatory (NOT NULL) column

Eyni testler MySQL-də:

-- MySQL

-- Syntax 1

mysql> alter table t1 add column address varchar(25) not null;
Query OK, 3 rows affected (0.45 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- Syntax 2

mysql> alter table t1 add address2 varchar(25) not null;
Query OK, 3 rows affected (0.28 sec)
Records: 3  Duplicates: 0  Warnings: 0

Gördüyümüz kimi hər 2 syntax tipi dəstəklənir və hətta boş olmayan cədvələ not null column əlavə edə bilirik. Möcüzəli şəkildə NOT NULL olan column-a 0 uzunluqlu boş məlumat daxil edilib:

mysql> select char_length(address),char_length(address2) from t1;
+----------------------+-----------------------+
| char_length(address) | char_length(address2) |
+----------------------+-----------------------+
|                    0 |                     0 |
|                    0 |                     0 |
|                    0 |                     0 |
+----------------------+-----------------------+
3 rows in set (0.00 sec)

Ən sonda onu qeyd edək ki, Oracle düzgün syntax zamanı column əlavə etdikdə , həmin column dataları NULL olur:

-- Oracle

select nvl(address,'null məlumat') from t1;
>>>
null məlumat
null məlumat
null məlumat

Dropping Columns:

-- Oracle

alter table t1 drop column address;

-- MySQL

alter table t1 drop column address;

Eyni syntax eyni funksionallıq.

Renaming Columns:

-- Oracle

alter table t1 rename column address to address2;
>>>
table T1 altered.

desc t1;
>>>
Name     Null Type         
-------- ---- ------------ 
ID1           NUMBER       
NAME1         VARCHAR2(25) 
NAME_ID       NUMBER       
ADDRESS2      VARCHAR2(25) 

Rename olunan zaman column definition olduğu kimi qalır.
MySQL-də rename syntax yoxdur onun əvəzinə change var:

-- MySQL

alter table t1 change address address2 varchar(25) not null;

Marking Columns as unused

-- Oracle

alter table t1 set unused column address2;

desc t1;

Name    Null Type         
------- ---- ------------ 
ID1          NUMBER       
NAME1        VARCHAR2(25) 
NAME_ID      NUMBER

Həqiqətən də unused etdi. Hətta describe-da belə görsənmədi bu column.

MySQL-də belə bir syntax yoxdur.

Making table as read-only:


-- Oracle

alter table t1 read only;

MySQL-də belə bir syntax yoxdur. Onun əvəzinə USER-ə yalnızca SELECT grant verməklə cədvəli privilege vasitəsilə read only edə bilərsiniz.

Modifying columns:
Daha bir maraqlı halı göstərək. Oracle-da boş olmayan column uzunluğunu azaltmaq istədikdə ERROR verərək bunun qarşısını alır.

Deyək ki bizim cədvəlimiz aşağıdakı kimidir:

-- Oracle

desc t1;

Name    Null Type         
------- ---- ------------ 
ID1          NUMBER       
NAME1        VARCHAR2(30) 
NAME_ID      NUMBER 

Və NUMBER tipin uzunluğunu azaltmağa çalışaq:

-- Oracle

alter table t1 modify id1 number(4,0);
>>>
Error starting at line : 3 in command -
alter table t1 modify id1 number(4,0)
Error report -
SQL Error: ORA-01440: column to be modified must be empty to decrease precision or scale
01440. 00000 -  "column to be modified must be empty to decrease precision or scale"
*Cause:    
*Action:

Eyni halı MySQL-də test edək.

-- MySQL

mysql> desc t1;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(25)      | YES  |     | NULL    |                |
| name_id | int(11)          | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table t1 modify id tinyint not null auto_increment;
Query OK, 3 rows affected (1.23 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc t1;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | YES  |     | NULL    |                |
| name_id | int(11)     | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Query OK..MySQL-də Heç bir error çıxmadı dolayısı ilə Bu anda çox diqqətli olmaq lazımdır mövcud halda, data səssizcə truncate oluna bilər.
Lakin maraqlı hal bundan ibarətdir ki, Oracle varchar tipində error çıxartmır MySQL kimi.

-- Oracle
-- 1
alter table t1 modify name1 varchar2(25);

desc t1;
>>>
Name    Null Type         
------- ---- ------------ 
ID1          NUMBER       
NAME1        VARCHAR2(25) 
NAME_ID      NUMBER    

-- 2

alter table t1 modify name1 varchar2(15);

desc t1;
>>>
Name    Null Type         
------- ---- ------------ 
ID1          NUMBER       
NAME1        VARCHAR2(15) 
NAME_ID      NUMBER  

Indi isə mövcud data uzunluğundan daha aşağı bir uzunluq ilə cədvəlimizi alter etməyə çalışaq:

-- Oracle

select length(name1) from t1;
>>>
9
5
5

-- 9-dan kiçik qiymət ilə alter

alter table t1 modify name1 varchar2(7);
>>>

alter table t1 modify name1 varchar2(7)
Error report -
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"
*Cause:    
*Action:

Oracle çox gözəl şəkildə hər hansı data-nın itirilməsinin qarşısını alır.
Eyni testi MySQL-də edək.

-- MySQL

mysql> select * from t1;
+----+-----------+---------+
| id | name      | name_id |
+----+-----------+---------+
|  1 | shahriyar |       1 |
|  2 | orxan     |       2 |
|  3 | elvin     |       3 |
+----+-----------+---------+
3 rows in set (0.00 sec)


mysql> alter table t1 modify name varchar(7);
Query OK, 3 rows affected, 1 warning (1.22 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+---------+---------+
| id | name    | name_id |
+----+---------+---------+
|  1 | shahriy |       1 |
|  2 | orxan   |       2 |
|  3 | elvin   |       3 |
+----+---------+---------+
3 rows in set (0.00 sec)

Çox təəssüf ki data-mız itirildi. Default MySQL installation işlədikdə bu kimi hallara diqqət yetirmək lazımdır.
Yuxarıdakı halın qarşısını almaq üçün my.cnf faylında sql_mode-u təyin etmək lazımdır. Ya da ki, session olaraq dəyişə bilərsiz.

mysql> set @@sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 modify name varchar(7);
ERROR 1265 (01000): Data truncated for column 'name' at row 1

Ümumiyyətlə MySQL-i install etdikdən sonra onu strict mode-a keçirmək məsləhətdir.

Təşəkkürlər 😉

Kateqoriyalar: MySQL, Oracle, Oracle SQL

Create table using Subqueries Oracle vs. MySQL

*Oracle 11g R2 , MySQL 5.5.34*

Hazır cədvəl üzərindən onunla eyni cədvəli yaratmaq məqsədilə istifadə olunan bu üsul 2 RDBMS arasında fərqliliklər görsənir.

-- MySQL

-- Numune cedvel

create table sub_test(
id int not null auto_increment,
firstname varchar(25) not null,
primary key(id),
unique(firstname)
);

-- Syntax 1

create table sub_test2 as select * from sub_test;

-- Syntax 2

create table sub_test3 select * from sub_test;

2 Syntax-ın 2-si də doğrudur.
Bir qayda olaraq subquery ilə subquery ilə yaradılan cədvəllərdə PK, FK , Unique constraints-lər yer almırlar.
Sübut:

-- MySQL

CREATE TABLE `sub_test2` (
  `id` int(11) NOT NULL DEFAULT '0',
  `firstname` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `sub_test3` (
  `id` int(11) NOT NULL DEFAULT '0',
  `firstname` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

İndi isə eyni testi Oracle-da edək:

-- Oracle

create table sub_test(
id numeric not null,
firstname varchar2(25) not null,
constraint pk_id primary key(id),
constraint un_id unique(firstname)
);

>>>
table SUB_TEST created.

-- Syntax 1
create table sub_test2 as select * from sub_test;
>>>
table SUB_TEST2 created.

-- Syntax 2
create table sub_test3 select * from sub_test;
>>>
Error starting at line : 18 in command -
create table sub_test3 select * from sub_test
Error at Command Line : 18 Column : 24
Error report -
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:

Çox gözəl. Oracle yalnız CTAS dəstəkləyir.
Və daha əvvəl də dediyimiz kimi subquery-li create zamanı yuxarıda dediyim constraint-lər yaranmır. Oracle üçün sübut:

-- Oracle

-- sub_test cedveli

select constraint_name from user_cons_columns where table_name = 'SUB_TEST';
>>>

SYS_C0011578 -- not null
SYS_C0011579 -- not null
PK_ID        -- Primary Key   
UN_ID        -- Unique Key

-- sub_test2 cedveli

select constraint_name from user_cons_columns where table_name = 'SUB_TEST2';
>>>
SYS_C0011582 -- not null
SYS_C0011583 -- not null

Lakin belə bir hal ola bilər ki, bizə lazım olsun ki cədvəli digər cədvələ əsasən yaradaq və bütün constraintlər də həmin ikinci cədvəldə olsun.

Oracle-da bu funksiya yoxdur. Düşünürəm ki, bu Constraint və İndex yanaşmasının fərqliliyindəndir. Ya da ki ekspertlər izah etsin ))

-- MySQL

create table sub_test4 like sub_test;

>>>

CREATE TABLE `sub_test4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `firstname` (`firstname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Düşünürəm ki, mövzunu izah etdik.

Təşəkkürlər ))