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 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…
mysql_connect('localhost', 'root', 'root');
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
I had to use PDOs for my project and the following articles are just more than enough for getting started
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
CREATE database testdb;
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)
(‘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;
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.
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)
logic goes here
Example: simple procedure to collect rows of a table:
Lets create a table first:
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:
CREATE PROCEDURE selectAll()
SELECT * FROM member;
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?
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
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
First access the files of the database (MYI, MYD, FRM) from the existing database by going to
/var/lib/mysql/(your database) – This path holds true for ubuntu
Then go to the new server and put it in the same place
restart your server and mysql then view it through phpmyadmin.
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.