Web Technology Experts Notes

Showing posts with label MYSQL. Show all posts

Tuesday 7 April 2020

How to check active connections in MySQL?

Question: How to check active connections in mysql?

show status where `variable_name` = 'Threads_connected';

Question: How to check the limit of MySQL active connections?

SHOW VARIABLES LIKE "max_connections";

Question: Display the list of active MySQL Process list?

show processlist;

Question: How to check the max no of connections used?

SHOW STATUS WHERE `variable_name` = 'Max_used_connections';

Question: MySQL set the max no of connections used (Temporarily/Quick)?

SET GLOBAL max_connections = 512;

Question: MySQL set the max no of connections used (Temporarily/Quick)?
Open my.cnf or my.ini in MySQL

max_connections = 512

service mysqld restart

Question: MySQL check number of connections throughout history?

show status like 'Connections%';

Sunday 29 March 2020

General error: 1364 Field city_id doesn't have a default value

I have upgraded the MySQL from 5.5 to 5.6 and then getting below type of errors.
General error: 1364 Field 'city_id' doesn't have a default value

(I was not getting such type of error, it must come after upgrading my MySQL Version from 5.5 to 5.6)

Solution: It have 3 Solutions

In Your Code (PHP), always set a default value (if no value) before save.

$objSaveData->saveData(
    array(
        'id'=>111,
        'name'=>'New user',
        'city_id'=>0, //default value
    );
);

Create fields with default values set in datatable. It could be empty string, which is fine for MySQL server running in strict mode.
like below:
```
ALTER TABLE `users` CHANGE `city_id` `city_id` INT(10) UNSIGNED NOT NULL DEFAULT '0';
```
Disable MySQL Strict Mode (Most appropriate)
Disable it by setting your own SQL_MODE in the my.cnf file, then restart MySQL.

Look for the following line:
```
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
```
Change it to:
```
sql-mode="" 
```
Restart the MySQL Service.

Thursday 12 December 2019

How to get the sizes of the tables in mysql database?

Question: How to get the sizes of the tables in mysql database?

SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC

(Replace mydb with Your Database Name)

Output

Question: Get record counts for all tables along with size in MySQL database?

SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)", SUM(TABLE_ROWS) AS "Total Record(S)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC

(Replace mydb with Your Database Name)

Output

Get record counts for all tables in MySQL database

Saturday 12 October 2019

How to install and Connect to MySQL in NodeJS?

Hope you have already installed nodeJS, If not Please install first.
https://nodejs.org/en/download/

Question: How to install new package in nodejs?

npm install packagename

Question: How to install MySQL?

npm install mysql

Question: How to update MySQL?

npm update mysql

Question: How to include MySQL?

var mysql = require("mysql");

Question: How to connect to MySQL in nodeJS?

var mysql = require("mysql");
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "mydb"
});
con.connect(function(err) {
    if (err) {
        console.log('Error connecting to database');
        return;
    }
    console.log('Connection established Successfully');
});

Question: How to connect to MySQL in single string in nodeJS?

var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/database'); 
con.connect(function(err) {
    if (err) {
        console.log('Error connecting to database');
        return;
    }
    console.log('Connection established Successfully');
});

Question: How to get records from MySQL database in node.js?

var mysql = require("mysql");
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "enterprisedev2"
});

con.query('SELECT * FROM admin_members where id<=10 ', function(err, rows) {
    for (var i = 0; i < rows.length; i++) {
        console.log(rows[i]);
    }
});

Question: How to insert record in database?

var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/mydb');
var post = {
    'userID': "1",
    'userName': 'username',    
    'device': 'web',
    'stream_time': "0",
    'user_display_name': "arun kumar gupta",
    'user_photo': "hello"
};
con.query('INSERT INTO messages set ?', post, function(err, result) {
    if (err) {
        console.log(err.message);
    } else {
        console.log('record added in db');
    }
});

Question: How to print the query while inserting record?

var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/mydb');
var post = {
    'userID': "1",
    'userName': 'username',    
    'device': 'web',
    'stream_time': "0",
    'user_display_name': "arun kumar gupta",
    'user_photo': "hello"
};
var mysqlProfiler = con.query('INSERT INTO messages set ?', post, function(err, result) {});
console.log(mysqlProfiler.sql);

Question: Given another way to insert data in MySQL Database?

var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/enterprisedev2');

/* data insert in db */
var postData = {
    userID: "1",
    userName: "arunkg",
    userRole: "1",
    text: 'kkkkkkkkkkkkkkkk',
    status: "1",
    vid: '21'
};

var mysqlProfiler = con.query('INSERT INTO `messages` (`userID`, `userName`, `userRole`,`text`,`status`,`vid`) VALUES (' + [postData.userID, mysql.escape(postData.userName), postData.userRole, mysql.escape(postData.text), postData.status, postData.vid] + ')', function(err, result) {
    if (err) {
        //console.log(err.message);
    } else {
        console.log('record added in db');
    }
});

Thursday 3 October 2019

What is Super Key, Candidate keys and Alternate Key?

Super Key
A Super key is any combination of fields within a table that uniquely identifies each record within that table.

Example:
managers (table)

ManagerID (unique)
Name
Title
DepartmentID

ManagerID+Name=Super Key
ManagerID+Title=Super Key

Candidate keys
Candidate keys are those keys which is candidate for primary key of a table. Candidate keys are such keys which full fill all the requirements of primary key which is not null and have unique records.

Example:

Suppose we have a table named Employee which has two columns EmpID and EmpMail, both are not null and have unique value.
Both Field are Candidate keys.

Alternate Key
If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table.

Example:

Suppose we have a table named Employee which has two columns EmpID and EmpMail, both are not null and have unique value.
So both columns are treated as candidate key.
IF we make EmpID as a primary key, then EmpMail is known as alternate key.

Referential integrity
Referential integrity is a database concept that ensures that relationships between two table remain consistent.

Suppose one table has a foreign key to another table. Referential integrity makes ensure the both have valid record.

Example:

products (table)
    id (primary key)
    name 
    price
    sale_price
    size

reviews (another table)
    id (primary key),    
    product_id (foreign key)
    review by
    title
    start_rating

One Product can have one Or multiple reviews.
Referential integrity make sure following:

You can't delete record from product table, till review exist in reviews table for same product Id.
You can't add record in reviews table, till you have not added record in product table for same product Id.
If you delete the record from product table, reviews will be deleted automatically using cascade for the same product Id.