Interested in improving this site? Please check the To Do page.
mysql.connect
Connect to a MySQL server.
Syntax
mysql.connect (host, user, password, database, port)
Params
The hostname, user id, password, existing database name, and port
Returns
A MySQL object handle (also called a database connection ID) or a scriptError
Examples
on connect () {
local (id, result);
try {
id = mysql.connect("127.0.0.1", "root", "frontier", "frontier", 3306)}
else {
dialog.alert(mysql.getErrorMessage(id));
dialog.alert(mysql.getErrorNumber(id))};
result = mysql.close(id);
return result};
local (i);
mysql.init();
for i = 1 to 1000000 {
msg( i + ": " + connect() + " (" + sys.memavail() + ")")};
mysql.end()
Notes
You should call inside a try statement. MySQL allows you to connect to a server without specifying a database or a port. However, in the Frontier implementation, we require both a database name and a port. This helps prevent the inevitable confusion over trying to access a database without having specified it first.
This function is used to connect to a specific MySQL server. It is possible to have multiple mysql.connect calls active at once, if connecting to different servers. Just make sure the ID returned by those different connections is stored in different variables. Also, no matter how many servers you connect to, you should call mysql.init and mysql.end only once per Frontier session.
Windows Performance Note
When we first ran the above example, designed to see if there was any memory loss opening and closing a million connections, we found that MySQL errored out after a few thousands calls. After doing some digging in the MySQL documentation, we found the following instructions, which easily fixed the problem.
The following instructions are taken from the MySQL documentation.
Connection to MySQL Server Failing on Windows
When you’re running a MySQL server on Windows with many TCP/IP connections to it, and you’re experiencing that quite often your clients get a Can’t connect to MySQL server error, the reason might be that Windows doesn’t allow for enough ephemeral (short-lived) ports to serve those connections.
By default, Windows allows 5000 ephemeral (short-lived) TCP ports to the user. After any port is closed it will remain in a TIME_WAIT status for 120 seconds. This status allows the connection to be reused at a much lower cost than reinitializing a brand new connection. However, the port will not be available again until this time expires.
With a small stack of available TCP ports (5000) and a high number of TCP ports being open and closed over a short period of time along with the TIME_WAIT status you have a good chance for running out of ports. There are two ways to address this problem:
- Reduce the number of TCP ports consumed quickly by investigating connection pooling or persistent connections where possible
- Tune some settings in the Windows registry (see below)
IMPORTANT: The following procedure involves modifying the Windows registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, view the following article in the Microsoft Knowledge Base: http://support.microsoft.com/kb/256986/EN-US/.
- Start Registry Editor (Regedt32.exe).
- Locate the following key in the registry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
- On the Edit menu, click Add Value, and then add the following registry value:
Value Name: MaxUserPort
Data Type: REG_DWORD
Value: 65534
This sets the number of ephemeral ports available to any user. The valid range is between 5000 and 65534 (decimal). The default value is 0×1388 (5000 decimal).
- On the Edit menu, click Add Value, and then add the following registry value:
Value Name: TcpTimedWaitDelay
Data Type: REG_DWORD
Value: 30
This sets the number of seconds to hold a TCP port connection in TIME_WAIT state before closing. The valid range is between 0 (zero) and 300 (decimal). The default value is 0×78 (120 decimal).
- Quit Registry Editor.
- Reboot the machine.
Note: Undoing the above should be as simple as deleting the registry entries you’ve created.
