buy tb500

Archive for the ‘DataBase’ Category

How to fix if MySQL cannot telnet from outside the server

Friday, June 17th, 2011

If you unable to telnet to the port 3306 which is for MySQL from outside the server, firstly you need to check the
firewall. You need to make sure the firewall already allow port 3306.
If the firewall already allow the port, another thing to do is to comment the line below in my.cnf file:
[mysqld] #bindaddress=
127.0.0.1
After that you need to restart the MySQL service for it’s to take effect by using the command below:
root@server [/var/lib/mysql]# service mysql restart
Shutting down MySQL. … SUCCESS!
Starting MySQL. SUCCESS!

Table ‘mysql.servers’ doesn’t exist

Friday, June 17th, 2011

If you see the error above when try to add or delete the database from your control panel, kindly follow the steps
below to fix it:
[root@server ]# mysql_fix_privilege_tables –user=admin
–password=<admin pass> –verbose
You will see the output as below:
This script updates all the mysql privilege tables to be usable by
the current version of MySQL
You can safely ignore all ‘Duplicate column’ and ‘Unknown column’ errors
because these just mean that your tables are already up to date.
This script is safe to run even if your tables are already up to date!
ERROR 1060 (42S21) at line 132: Duplicate column name ‘File_priv’
ERROR 1060 (42S21) at line 138: Duplicate column name ‘Grant_priv’
ERROR 1060 (42S21) at line 139: Duplicate column name ‘Grant_priv’
ERROR 1060 (42S21) at line 140: Duplicate column name ‘Grant_priv’
ERROR 1060 (42S21) at line 151: Duplicate column name ‘ssl_type’
ERROR 1061 (42000) at line 161: Duplicate key name ‘Grantor’
ERROR 1054 (42S22) at line 188: Unknown column ‘Type’ in ‘columns_priv’
ERROR 1060 (42S21) at line 210: Duplicate column name ‘type’
ERROR 1060 (42S21) at line 220: Duplicate column name ‘Show_db_priv’
ERROR 1060 (42S21) at line 237: Duplicate column name ‘max_questions’ ERROR
1060 (42S21) at line 247: Duplicate column name ‘Create_tmp_table_priv’
ERROR 1060 (42S21) at line 250: Duplicate column name ‘Create_tmp_table_priv’
ERROR 1060 (42S21) at line 375: Duplicate column name ‘Create_view_priv’
ERROR 1060 (42S21) at line 378: Duplicate column name ‘Create_view_priv’
ERROR 1060 (42S21) at line 387: Duplicate column name ‘Show_view_priv’
ERROR 1060 (42S21) at line 390: Duplicate column name ‘Show_view_priv’
ERROR 1060 (42S21) at line 410: Duplicate column name ‘Create_routine_priv’
ERROR 1060 (42S21) at line 413: Duplicate column name ‘Create_routine_priv’
ERROR 1060 (42S21) at line 422: Duplicate column name ‘Alter_routine_priv’
ERROR 1060 (42S21) at line 425: Duplicate column name ‘Alter_routine_priv’
ERROR 1060 (42S21) at line 431: Duplicate column name ‘Execute_priv’
ERROR 1060 (42S21) at line 434: Duplicate column name ‘Execute_priv’
ERROR 1054 (42S22) at line 603: Unknown column ‘Event_priv’ in ‘where clause’
ERROR 1060 (42S21) at line 619: Duplicate column name ‘sql_mode’
ERROR 1060 (42S21) at line 658: Duplicate column name ‘originator’
ERROR 1060 (42S21) at line 662: Duplicate column name ‘time_zone’
ERROR 1060 (42S21) at line 665: Duplicate column name ‘character_set_client’
ERROR 1060 (42S21) at line 671: Duplicate column name ‘collation_connection’
ERROR 1060 (42S21) at line 677: Duplicate column name ‘db_collation’
ERROR 1060 (42S21) at line 683: Duplicate column name ‘body_utf8’
ERROR 1054 (42S22) at line 693: Unknown column ‘Trigger_priv’ in ‘where clause’ done
Now you should able to add or delete the database.

Import an SQL file directly via the MySQL command-line

Tuesday, May 24th, 2011

If you have a very large database (totaling 10’s or 100’s of megabytes), then uploading via HTTP is not suitable. Instead, it will be quicker and more efficient to import the file directly into the database using the mysql command-line utility on the server.

However, to do this you will need SSH access to the server. If you do not have SSH access, please see our article:

To import an SQL file:

  • First, upload the file via FTP onto your account (into your private/ directory is best as no-one else can access this).
  • Using the SSH client of your choice, log onto the server. You will start at your home directory (/var/www/vhosts/example.com, where example.com is the domain the SSH account is associated with).
  • Run the following command line:
mysql -u username -p database < filename
  • where username is the username created for database. Finally, filename is the filename (and path, if required) to the file you just uploaded.
  • For example, if you created the database forum, with the username forum (and the password f0rum), and uploaded backup.sql to the private/ directory of your domain, you can run the following command:
mysql -u forum -p forum < private/backup.sql
Enter password:

If you see the error “Access denied for user 'username'@'localhost'“, check that you are using the correct username and password – this means that either one of them (or both of them) failed.

Depending on the size (and complexity) of the SQL file, this can take from a few seconds to a few minutes to complete. If you want to cancel command while it is running, press Ctrl & C at the same time.

Also, if you have a zipped or gzipped file, you will need to de-compress it before using it (the mysql command line utility will not understand the compressed file). To do this, run the following:

unzip path/to/file.zip

or

gunzip path/to/file.gz

If you still do not know how to do then just send email email
you can find my email in contact us
Thank You

Import an SQL file via DB WebAdmin in PleskImport an SQL file via DB WebAdmin in Plesk

Tuesday, May 24th, 2011

Over some of the recent major revisions in phpMyAdmin (which is used to manage the MySQL databases), the location of the import utility has changed. In the version currently installed with both Plesk 7.5.x Reloaded and Plesk 8.0.x is can be found in two places:

  • Log onto Plesk using your username and password.
  • Click on the name of the domain the database belongs to.
  • Click on Databases.
  • Click on the name of the database you want to import the file to.
  • Click on DB WebAdmin.
  • Either:
    • Click on the name of the database (above the list of tables on the left) and on the next page click the SQL tab. Or;
    • Click on the SQL box icon above the name of the database (on the left-hand-side) and click on the Import Files tab.
  • In both cases, the import file field will be shown and you can upload an SQL file to be run against the database.

This may take some time. If the browser has stopped responding, then the upload may have failed – try the method below if this keeps happening.

If you have a large file (more than a few megabytes) then this method is not suitable. The next method will work better.

If you need further assist just drop me email

Starting MySQLCouldn’t find MySQL manager (/var/lib/bin/mysqlmanager) or server (/var/lib/bin/mysqld_safe) or mysql has failed, please contact the sysadmin (result was “mysql has failed”).

Sunday, April 17th, 2011

The solution for the above error is below

1- ssh to the server

2- go to /etc/my.cnf

find basedir=/var/lib and remove it or commet it by     #basedir=/var/lib

3- save what you did

4- restart mysql then the issue will be fix

i have done that by my self in linux (cpanel server) and fixed the issue

References is below

http://forums.cpanel.net/f5/starting-mysqlcouldnt-find-mysql-manager-server-52517.html

Thank you.

reset mysql root password windows

Saturday, April 9th, 2011

1- stop mysql database from the services

2- save below code in this file mysql-init.txt in mysql directory under BIN

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;
3-
C:\mysql\bin\mysql --init-file=C:\\mysql-init.txt

make sure the directory is mysql directory, may be the above directroy is not correct ,

Reference

http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html

Mysql load

Wednesday, January 12th, 2011

to detect the user that caused mysql to be high load

mysqladmin processlist

Thank you.

how to know MSSQL 2008 Edition

Sunday, August 29th, 2010

YOu need to run the command below then you will get the edition

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

Thank you.

JSP connect to mysql

Saturday, June 12th, 2010

refer ot the url below

http://www.roseindia.net/jsp/connect-jsp-mysql.shtml

i have test it and it is working fine