Messages

31

MySQL / ERROR 1040 (00000): Too many connections

« on: March 11, 2011, 11:11:41 PM »

If you got this error:
ERROR 1040 (00000): Too many connections
you need to increase your max_connections as:

Code: [Select]

mysql> set @@global.max_connections=5000;
if you want to see its value run:

Code: [Select]

select @@global.max_connections;

32

Java Script / How to set focus on the next input text field

« on: March 08, 2011, 01:43:45 PM »

This function can set the next input text field focused:

Code: [Select]

function SetNextCellFocus(source) {
    var lastFocusCellName = source.name;

    var els = document.getElementsByTagName("INPUT");
    var lLength = els.length;
    for (var i = 0; i < lLength; i++) {
        if (els[i].name != null) {
            if (els[i].name == lastFocusCellName && i + 1 < lLength) {
                try {
                    els[i + 1].focus();
                    els[i + 1].select();
                }
                catch (err) { }
                finally {
                    break;
                }
            }
        }
    }


}

33

SQL Server / How to Alter column with a Default constraint

« on: March 08, 2011, 10:24:41 AM »

1- Drop the constraint first.
2- Alter the column.
3- recreate the constraint again.

Code: [Select]

ALTER TABLE dbo.TPURENTRY
DROP CONSTRAINT TPURENTRY_ADDITIONNALINFO_DF
GO

ALTER TABLE dbo.TPURENTRY
ALTER COLUMN ADDITIONNALINFO VARCHAR(MAX) NULL
GO

ALTER TABLE dbo.TPURENTRY 
ADD CONSTRAINT TPURENTRY_ADDITIONNALINFO_DF DEFAULT '' FOR ADDITIONNALINFO
GO

34

C/C++ & Visual C++ / Re: Six Fast Steps to Programming in C++

« on: February 15, 2011, 12:32:59 PM »

Thanks David, nice topic.

35

MySQL / Re: How To Set Up Database Replication In MySQL 5.0

« on: February 09, 2011, 12:23:03 AM »

To see the replication state in the slave run under Mysql:

Code: [Select]

SHOW SLAVE STATUS\G

36

Lunix & Unix / MOVED: How To Set Up Database Replication In MySQL 5.0

« on: February 07, 2011, 01:17:42 PM »

This topic has been moved to MySQL.

http://developers-heaven.net/forum/index.php?topic=589.0

37

MySQL / How To Set Up Database Replication In MySQL 5.0

« on: February 07, 2011, 01:16:30 PM »

This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

1- Setting the Replication Master Configuration
On a replication master, you must enable binary logging and establish a unique server ID. If this has not already been done, this part of master setup requires a server restart.
Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible.
Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group, and must be a positive integer between 1 and (232)–1. How you organize and select the numbers is entirely up to you.
To configure the binary log and server ID options, you will need to shut down your MySQL server and edit the my.cnf or my.ini file. Add the following options to the configuration file within the [mysqld] section. If these options already exist, but are commented out, uncomment the options and alter them according to your needs. For example, to enable binary logging using a log file name prefix of mysql-bin, and configure a server ID of 1, use these lines:

Code: [Select]

[mysqld]
log-bin=mysql-bin
server-id=1After making the changes, restart the server.

Code: [Select]

/etc/rc.d/init.d/mysql restart

2- Then we log into the MySQL database as root and create a user with replication privileges:

Code: [Select]

mysql -u root -p
Enter password:Now we are on the MySQL shell.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The last command will show something like this:

Code: [Select]

+---------------+----------+--------------+------------------+
| File                | Position   | Binlog_do_db  | Binlog_ignore_db   |
+---------------+----------+--------------+------------------+
| mysql-bin.006  | 183         |                    |                          |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Write down this information, we will need it later on the slave!

If the master has been running previously without binary logging enabled, the log file name and position values displayed by SHOW MASTER STATUS or mysqldump --master-data will be empty. In that case, the values that you need to use later when specifying the slave's log file and position are the empty string ('') and 4.

You now have the information you need to enable the slave to start reading from the binary log in the correct place to start replication.
If you have existing data that needs be to synchronized with the slave before you start replication, leave the client running so that the lock remains in place and then proceed to Creating a Data Snapshot Using mysqldump”. The idea here is to prevent any further changes so that the data copied to the slaves is in synchrony with the master.
If you are setting up a brand new master and slave replication group, you can exit the first session to release the read lock.

So open a new session while keeping the lock and Creating a Data Snapshot Using mysqldump.
4- Creating a Data Snapshot Using mysqldump
To obtain a snapshot of the data using mysqldump:
1.If you have not already locked the tables on the server to prevent statements that update data from executing:
Start a session on the server by connecting to it with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

Code: [Select]

mysql> FLUSH TABLES WITH READ LOCK;Remember to use SHOW MASTER STATUS and record the binary log details for use when starting up the slave. The point in time of your snapshot and the binary log position must match.
2.In another session, use mysqldump to create a dump either of all the databases you want to replicate, or of selected individual databases. For example:

Code: [Select]

shell> mysqldump --all-databases --lock-all-tables >dbdump.dbAn alternative to using a bare dump, is to use the --master-data option, which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process.

Code: [Select]

shell> mysqldump --all-databases --master-data >dbdump.db3.In the client where you acquired the read lock, release the lock:

Code: [Select]

mysql> UNLOCK TABLES;Alternatively, exit the first session to release the read lock.
When choosing databases to include in the dump, remember that you will need to filter out databases on each slave that you do not want to include in the replication process.
You will need either to copy the dump file to the slave, or to use the file from the master when connecting remotely to the slave to import the data.


5- Setting the Replication Slave Configuration
On a replication slave, you must establish a unique server ID. If this has not already been done, this part of slave setup requires a server restart.
If the slave server ID is not already set, or the current value conflicts with the value that you have chosen for the master server, you should shut down your slave server and edit the configuration to specify a unique server ID. For example:
[mysqld]
server-id=2
After making the changes, restart the server.

Code: [Select]

/etc/rc.d/init.d/mysql restart
6- restore master data on the slave
If you are setting up a new replication environment using the data from a different existing database server, you will now need to run the dump file generated from that server on the new master. The database updates will automatically be propagated to the slaves:

Code: [Select]

shell> mysql -h master < dbdump.db
7- Setting the Master Configuration on the Slave
To set up the slave to communicate with the master for replication, you must tell the slave the necessary connection information. To do this, execute the following statement on the slave, replacing the option values with the actual values relevant to your system

Code: [Select]

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;


MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

8- Start the slave
Now all that is left to do is start the slave. Still on the MySQL shell we run

Code: [Select]

START SLAVE;
quit;

38

Lunix & Unix / Re: How to install PEAR Mail Package?

« on: January 26, 2011, 11:07:03 PM »

If you get error like:

Code: [Select]

WARNING: channel "pear.php.net" has updated its protocols, use "channel-update pear.php.net" to update pear/Mail requires PEAR Installer (version >= 1.5.6), installed version is 1.5.0 No valid packages found install failed

update PEAR as:

Code: [Select]

pear channel-update "pear.php.net"
That should fix the warning.

Probably then, if you will feel comfortable, you can also upgrade to a latest version of PEAR with,

Code: [Select]

pear upgrade-all

The upgrade is just to make sure all components you have latest changes in PEAR with them.

if you still get error:

Code: [Select]

pear/Mail requires PEAR Installer (version >= 1.5.6), installed version is 1.4.9
No valid packages found
install failed
Do:
First:

Code: [Select]

pear upgrade --force pear
Then:

Code: [Select]

pear install --alldeps Mail

39

Lunix & Unix / Adding alternate SMTP port with Postfix

« on: January 26, 2011, 10:12:42 PM »

Seeing as how many of the big ISPs block attempts to connect to port 25 on any servers but their own, those of you running Postfix for remote users will probably want to accept connections on an alternate port. All that’s needed is to add the following line to /etc/postfix/master.cf:

Code: [Select]

587 inet n - n - - smtpd
Of course, you can choose any available port on your system that you would like to accept connections on.

Then you must restart the mail server:

Code: [Select]

/etc/init.d/postfix restart
After that you can check the new port by telnet:

$ telnet mail.example.com 587
Trying 192.168.100.11...
Connected to 192.168.100.11.
Escape character is '^]'.
220 scallop.seaglass.com ESMTP Postfix
EHLO localhost
250-scallop.seaglass.com
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-STARTTLS
250-XVERP
250 8BITMIME
quit
You type the emphasized lines. Use your own mail server hostname instead of mail.example.com, of course.

40

Lunix & Unix / Recover MySQL root Password

« on: January 26, 2011, 12:14:46 PM »

You can recover MySQL database server password with following five easy steps.


Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password i.e. reset mysql password.

Step # 5: Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

Code: [Select]

# /etc/init.d/mysql stopOutput:

Stopping MySQL database server: mysqld.Step # 2: Start to MySQL server w/o password:

Code: [Select]

# mysqld_safe --skip-grant-tables &Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: startedStep # 3: Connect to mysql server using mysql client:

Code: [Select]

# mysql -u rootOutput:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>Step # 4: Setup new MySQL root user password

Code: [Select]

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quitStep # 5: Stop MySQL Server:

Code: [Select]

# /etc/init.d/mysql stopOutput:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tablesStep # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p

41

Lunix & Unix / How To Enable Remote Access To MySQL Database Server?

« on: January 25, 2011, 11:06:03 PM »

By default remote access to MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server.

MySQL Remote Access
You need type the following commands which will allow remote connections.

Step # 1: Login Using SSH (if server is outside your data center)
First, login over ssh to remote MySQL database server:

ssh user@mysql.nixcraft.iStep # 2: Edit my.cnf File
Once connected you need to edit the MySQL server configuration file my.cnf using a text editor such as vi.

If you are using Debian Linux file is located at /etc/mysql/my.cnf location
If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf
Edit /etc/my.cnf, run:
# vi /etc/my.cnf
Step # 3: Once file opened, locate line that read as follows
[mysqld] Make sure line skip-networking is commented (or remove line) and add following line

bind-address=YOUR-SERVER-IPFor example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows:

Code: [Select]

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    = 65.55.55.2
# skip-networking
....
..
....
Where,

bind-address : IP address to bind to.
skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.
Step# 4 Save and Close the file
Restart the mysql server, enter:

Code: [Select]

# /etc/rc.d/init.d/mysqld restartStep # 5 Grant access to remote IP address
Connect to mysql server:

Code: [Select]

$ mysql -u root -p mysqlGrant access to a new database
If you want to add a new database called foo for user bar and remote IP 202.54.10.20 then you need to type the following commands at mysql> prompt:mysql>

Code: [Select]

CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';How Do I Grant Access To An Existing Database?
Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database, enter:

Code: [Select]

mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';Step # 5: Logout of MySQL
Type exit command to logout mysql:mysql> exit
Step # 6: Open port 3306
You need to open TCP port 3306 using iptables or BSD pf firewall.

A sample iptables rule to open Linux iptables firewall
/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPTOR only allow remote connection from your web server located at 10.5.1.3:

/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPTOR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPTFinally save all rules:
# service iptables save
A sample FreeBSD / OpenBSD pf rule ( /etc/pf.conf)
pass in on $ext_if proto tcp from any to any port 3306OR allow only access from your web server located at 10.5.1.3:

pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306  flags S/SA synproxy stateStep # 7: Test it
From your remote system or your desktop type the following command:
$ mysql -u webadmin –h 65.55.55.2 –p
Where,

-u webadmin: webadmin is MySQL username
-h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
-p : Prompt for password
You can also use telnet to connect to port 3306 for testing purpose:
$ telnet 65.55.55.2 3306

42

PHP & Perl / Create image in runtime PHP: imagegif()

« on: January 24, 2011, 01:34:07 PM »

Sometimes we need to create an image in runtime without saving, just to display text or numbers that is usually used in the human verification.
it's very easy to use the following code and save it in a php file, say "image.php" then you can use this file as an image in your HTML code.

Code: [Select]

<?php

// Create a new image instance
$im = imagecreatetruecolor(100, 100);

// Make the background white
imagefilledrectangle($im, 0, 0, 99, 99, 0xFFFFFF);

// Draw a text string on the image
imagestring($im, 3, 40, 20, 'GD Library', 0xFFBA00);

// Output the image to browser
header('Content-type: image/gif');

imagegif($im);
imagedestroy($im);

?>

43

Lunix & Unix / How to install PEAR Mail Package?

« on: January 24, 2011, 01:23:31 PM »

Hello,

If you want to send PHP mail() with SMTP Authentication then you need to install PEAR Mail Package. This could be done by the following statement:

Code: [Select]

pear install --alldeps Mail

44

Lunix & Unix / Problem with exim and SMTP: Invalid response code received from server

« on: January 24, 2011, 01:13:50 PM »

If you have a problem in sending email via smtp with pear.

and got this error message :

Code: [Select]

[message] =>  authentication failure [SMTP: Invalid response code received from server (code: 454, response: TLS currently unavailable)]
You need to change the user and group of exim.cert and exim.key to be mail:

Code: [Select]

chown mail:mail /etc/exim.cert
chown mail:mail /etc/exim.key

45

PHP & Perl / Create a Basic Web Service Using PHP, MySQL, XML, and JSON

« on: December 04, 2010, 12:23:00 AM »

Web services are taking over the world. I credit Twitter's epic rise to the availability of a simple but rich API. Why not use the same model for your own sites? Here's how to create a basic web service that provides an XML or JSON response using some PHP and MySQL.

The PHP / MySQL

Code: [Select]

/* require the user as the parameter */
if(isset($_GET['user']) && intval($_GET['user'])) {

/* soak in the passed variable or set our own */
$number_of_posts = isset($_GET['num']) ? intval($_GET['num']) : 10; //10 is the default
$format = strtolower($_GET['format']) == 'json' ? 'json' : 'xml'; //xml is the default
$user_id = intval($_GET['user']); //no default

/* connect to the db */
$link = mysql_connect('localhost','username','password') or die('Cannot connect to the DB');
mysql_select_db('db_name',$link) or die('Cannot select the DB');

/* grab the posts from the db */
$query = "SELECT post_title, guid FROM wp_posts WHERE post_author = $user_id AND post_status = 'publish' ORDER BY ID DESC LIMIT $number_of_posts";
$result = mysql_query($query,$link) or die('Errant query:  '.$query);

/* create one master array of the records */
$posts = array();
if(mysql_num_rows($result)) {
while($post = mysql_fetch_assoc($result)) {
$posts[] = array('post'=>$post);
}
}

/* output in necessary format */
if($format == 'json') {
header('Content-type: application/json');
echo json_encode(array('posts'=>$posts));
}
else {
header('Content-type: text/xml');
echo '<posts>';
foreach($posts as $index => $post) {
if(is_array($post)) {
foreach($post as $key => $value) {
echo '<',$key,'>';
if(is_array($value)) {
foreach($value as $tag => $val) {
echo '<',$tag,'>',htmlentities($val),'</',$tag,'>';
}
}
echo '</',$key,'>';
}
}
}
echo '</posts>';
}

/* disconnect from the db */
@mysql_close($link);
}
With the number of persons hitting your web service (hopefully), you'll need to do adequate validation before attempting to connect to the database to avoid injection attacks. Once we get the desired results from the database, we cycle through the results to populate our return results array. Depending upon the response type desired, we output the proper header and content in the desired format.

Take the following sample URL for example:

Code: [Select]

http://mydomain.com/web-service.php?user=2&num=10Now, we can take a look at the possible results of the URL.

The XML Output

Code: [Select]

<posts>
<post>
<post_title>SSLmatic SSL Certificate Giveaway Winners</post_title>
<guid>http://davidwalsh.name/?p=2304</guid>
</post>
<post>
<post_title>MooTools FileManager</post_title>
<guid>http://davidwalsh.name/?p=2288</guid>
</post>
<post>
<post_title>PHPTVDB: Using PHP to Retrieve TV Show Information</post_title>
<guid>http://davidwalsh.name/?p=2266</guid>
</post>
<post>
<post_title>David Walsh: The Lost MooTools Plugins</post_title>
<guid>http://davidwalsh.name/?p=2258</guid>
</post>
<post>
<post_title>Create Short URLs Using U.Nu</post_title>
<guid>http://davidwalsh.name/?p=2218</guid>
</post>
<post>
<post_title>Create Bit.ly Short URLs Using PHP</post_title>
<guid>http://davidwalsh.name/?p=2194</guid>
</post>
<post>
<post_title>Represent Your Repositories Using the GitHub Badge!</post_title>
<guid>http://davidwalsh.name/?p=2178</guid>
</post>
<post>
<post_title>ZebraTable</post_title>
<guid>http://davidwalsh.name/?page_id=2172</guid>
</post>
<post>
<post_title>MooTools Zebra Table Plugin</post_title>
<guid>http://davidwalsh.name/?p=2168</guid>
</post>
<post>
<post_title>SSLmatic: Quality, Cheap SSL Certificates and Giveaway!</post_title>
<guid>http://davidwalsh.name/?p=2158</guid>
</post>
</posts>
Take this next sample URL for example:

Code: [Select]

http://mydomain.com/web-service.php?user=2&num=10&format=json
Now, we can take a look at the possible results of the URL.

The JSON Output

Code: [Select]

{"posts":[{"post":{"post_title":"SSLmatic SSL Certificate Giveaway Winners","guid":"http:\/\/davidwalsh.name\/?p=2304"}},{"post":{"post_title":"MooTools FileManager","guid":"http:\/\/davidwalsh.name\/?p=2288"}},{"post":{"post_title":"PHPTVDB: Using PHP to Retrieve TV Show Information","guid":"http:\/\/davidwalsh.name\/?p=2266"}},{"post":{"post_title":"David Walsh: The Lost MooTools Plugins","guid":"http:\/\/davidwalsh.name\/?p=2258"}},{"post":{"post_title":"Create Short URLs Using U.Nu","guid":"http:\/\/davidwalsh.name\/?p=2218"}},{"post":{"post_title":"Create Bit.ly Short URLs Using PHP","guid":"http:\/\/davidwalsh.name\/?p=2194"}},{"post":{"post_title":"Represent Your Repositories Using the GitHub Badge!","guid":"http:\/\/davidwalsh.name\/?p=2178"}},{"post":{"post_title":"ZebraTable","guid":"http:\/\/davidwalsh.name\/?page_id=2172"}},{"post":{"post_title":"MooTools Zebra Table Plugin","guid":"http:\/\/davidwalsh.name\/?p=2168"}},{"post":{"post_title":"SSLmatic: Quality, Cheap SSL Certificates and Giveaway!","guid":"http:\/\/davidwalsh.name\/?p=2158"}}]}
Creating a basic web service is very simple and encourages your users to spread the word about your website or service. Want more traffic? Want your website to grow without you putting in all the effort? Create a web service!

At the end may you need to know how to parse the output XML, so you can check the following article:
http://developers-heaven.net/forum/index.php/topic,561.0.html

Note: if you will get strings in  $_GET don't forget to use mysql_real_escape_string to prevent SQL Injection.