Cannot find .frm file – mysql error

Especially when you move mysql from Window [see related] to Linux using the data file copy, you might face such and error telling you one of your table’s .frm file is not found.
In this case either of the two can solve the problem.
1. be aware that the .frm, for that matter both MDI and MYI files are case sensitive to linux – as it is for all files. So, if on windows you have test.frm and your application access them as TesT.frm, you may not have problem, but it is a major one on Linux.
2. The other is group and ownership issue, make sure the files are under mysql on both ownership and group – unless you have other configuration for that.

TO change ownership use
sudo chmod -R mysql /path/to/data/files
and for group

sudo chgrp -R mysql /path/to/data/files

Hope it would help somebody.

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

A connection attempt failed error

It looks on windows php5.3 has some kind of confusion on localhost :)

I have been using the earlier version without any problem and the moment I update to the newer version.

I got error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

As a remedy, just change the localhost with 127.0.0.1 and it will work like a charm…

Prev –

mysql_connect('localhost', 'root', 'root'); 

To

mysql_connect('127.0.0.1', 'root', 'root'); 

Click here to See how you can upgrade mysql

While you are here, I would recommend for you:

Do you know how to add scroll bar to mysql terminal?

How I setup LAMP on AWS step by step

Learn nodeJs with AngularJS in a complete project step by step

Inserting data to table reading from another table in mysql

OK, we can use INSERT INTO statement to insert fresh data to mysql. But how about if the need lies on reading data from another table?
Here is how we do it in mysql.
Let us create the tables for illustration:
Log into your mysql and create the following queries
Creating database
CREATE database testdb;
USE testdb;

Creating tables

CREATE TABLE testtbl1 (id int(10) auto_increment, fname varchar(20), lname(20), primary key(id));
CREATE TABLE testtbl2 (id int(10) auto_increment, firstname varchar(20), lastname(20), age int, primary key(id));

Lets add some data to table 2 now
INSERT INTO testtbl2 (firstname, lastname, age)
VALUES
(‘ftest1’, ‘ftest1’, 35),
(‘ftest2, ‘ftest2’, 21),
(‘ftest3, ‘ftest3’, 25),
(‘ftest4’, ‘ftest4’, 38)

Now lets add data to the first table filtering persons with age less than 30

INSERT INTO testtbl1 (fname, lname) SELECT firstname, lastname FROM testtbl2 WHERE age <= 30;

Adding auto_increment after table creation

Say the table is created like this,

mysql> CREATE TABLE customer (id int, fname varchar(20));

The above table would be created without having an index. If there is a need to make id primary key, then

mysql> ALTER TABLE customer ADD PRIMARY KEY (id);

Then adding auto_increment would be as:

mysql> ALTER TABLE customer CHANGE id id int NOT NULL AUTO_INCREMENT;

Trying to assign auto_increment before making it a primary key – if it is not already done during creation, might produce an error. Actually trying to drop the primary key before neutralizing the auto_increment would also might create an error.
The following query would take out the auto_increment part from the table

ALTER TABLE customer CHANGE id id int not null;

And, of course, after this it is possible to drop primary key.

Stored Procedure in MySQL

Using MySQL had one setback – lack of stored procedures. But, we had celebrated the farewell of this hindrance after welcoming MySQL 5.0.
Stored procedure is a collection of valid SQL statements residing in the data server. It may or may not return value based on our need.
Stored procedure should be our first choice when we have a choice to play with them – because:

  • Our application would be fast due to decreased use of network
  • Logics can be used more than once
  • Maintenance is simple – just edit at one place… and even more

I will try to use simple example to demonstrate how to use stored procedure in MySQL. I will use MySQL CLI for the illustration.

Syntax: CREATE PROCEDURE procname(parameters)
BEGIN
logic goes here

END
Example:  simple procedure to collect rows of a table:

Lets create a table first:
USE dbTest;
CREATE TABLE member (Id int not null auto_increment primary key, first_name nvarchar(12), last_name nvarchar(12) );

Say we want to collect all rows from the above table using stored procedure:
DELIMITER **
CREATE PROCEDURE selectAll()
BEGIN
SELECT * FROM member;
END
DELIMITER ;
The above snippet would create a stored procedure named selectAll() on the dbTest database.
DELIMITER ** would change the default delimiter of the CLI which is ‘;’ to **. I have to change the delimiter from ‘;’ to ‘**’ or any other character except ”. Otherwise, when the execution gets ‘;’ in the middle of execution, it will halt – and finally we would return the default delimiter at the end.
Using the stored procedure would be:
CALL selectAll(); or CALL selectAll; –> which will list all the rows of the table.
What about when we have parameters?

Listing Databases and Tables in MySQL

To list databases from terminal
1. Go to terminal and type mysql -uroot -proot –> taking user name as root and password as root to log as administrator
2. type show databases; –> this will list all databases in the server.

To list tables in the given database:
1. First move to the database by typing: use databasename where databasename is the name of the database whose tables would be listed
2. type show tables;
To see the make up of the table:
1. First move to the database
2. Type describe tablename or desc tablename

Table read only – update error in mysql

When you move a database from other server and tried to update it, it might generate an error saying: Table tbl is read only
The very likely case for this is mysql is not able to access the tables due to ownership.
Here is a workaround for it:
1. Go to Terminal – Application->Accessories->Terminal
2. Change the the ownership to mysql using this command
sudo chown mysql:mysql -R /var/lib/mysql/your_db_name
This command will change the owner (chown) of the db to mysql

How to change default MySQL directory

Originally it will be located at /var/lib/mysql

Say you want to change it to /home/myApps/mysql

First if the mysql is running stop it: sudo /etc/init.d/mysql stop
and stop the server too: sudo/etc/init.d/apache2 stop

Go to /etc/mysql/my.cnf and edit:
datadir = /var/lib/mysql to datadir = /home/myApps/mysql

Copy files from existing path to the new path
cp -R /var/lib/mysql* /home/myApp/mysql

Grant ownership to new directory for mysql

sudo chown -R mysql:mysql /home/myApp/mysql

then start up your server and mysql – voila.