How to set max_connections in MySQL Programmatically

You can change max_connections while MySQL is running via SET:

mysql> SET GLOBAL max_connections = 5000;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+
1 row in set (0.00 sec)

Error: Duplicate entry ‘1-‘ for key ‘whosonline’

Backup your database then try doing a repair and optimize on your database and see if that helps. If you do not know how ask your Host to do it.

PhpMyAdmin Tutorial: Repair and Optimize Database

Sometimes the database tables get crashed. Also, they should be optimized from time to time. In this part of our PhpMyAdmin tutorial we will show how to repair and optimize database tables through PhpMyAdmin.

How to Repair MySQL Database Tables?

Open the database which should be repaired.

Select the tables that need repair and pick the Repair table action from the drop-down menu located below the tables’ list.

The tables will be repaired and you will get a confirmation screen.

How to Optimize MySQL Database Tables?

Open the database which you want to optimize.

Select the tables that need optimization.

From the drop-down menu pick the Optimize table option.

After the tables’ optimization a confirmation message with a list of the optimized tables will be visualized on the screen.

example for create sequence and trigger in oracle

create table test_b(
u_id integer not null primary key,
u_name varchar(30) not null
)

CREATE SEQUENCE test_b_seq
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
CACHE 100;

create or replace TRIGGER trigger_airports
BEFORE INSERT ON airports
FOR EACH ROW
BEGIN
SELECT airport_seq_id.nextval
INTO :new.u_id
FROM dual;
END;

insert into test_b(u_name)values(‘bapu2’);

create or replace TRIGGER test_after_insert AFTER INSERT ON test_b
FOR EACH ROW
BEGIN
insert into test_emp_backup values (:new.u_id, :new.u_name);
END;

select * from test_b;

select * from test_emp_backup;

Explain the differece between mysql connect and pconnect.

mysql_connect() and mysql_pconnect() both are working for database connection but with little difference. In mysql_pconnect(), ‘p’ stands for persistance connection.

When we are using mysql_connect() function, every time it is opening and closing the database connection, depending on the request .

But in case of mysql_pconnect() function,
First, when connecting, the function would try to find a (persistent) connection that’s already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.
Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the connection will remain open for future use (mysql_close() will not close connection established by mysql_pconnect()).

mysql_pconncet() is useful when you have a lot of traffice on your site. At that time for every request it will not open a connection but will take it from the pool. This will increase the efficiency of your site. But for general use mysql_connect() is best.

_______________________________________________________________________________

  1. mysql_pconnect() is deprecated as of PHP 5.5.0, and will be removed in the future because it has several disadvantage.
  2. Major disadvantage is that mysql_close() didn’t support connection created by mysql_pconnect(), connection remains open for future use.
  3. When we establish connection with mysql_pconnect() then it will search previous existing connection with same hostname, username and password.If old active connection found then it will not recreate new connection.
  4. In case of PHP most of the sites hosted with DB and PHP on same machine, so connection time is almost negligible, in that way persistant connection is useless.
  5. For Web-Request there is no need to make persistant connection, because HTTP is sateless protocol.
  6. Connection will automatically close when we refresh page, because it assumes every request as new request.
  7. Persistant connection need too much resource, that’s why persistant connection is Highly Discouraged.
  8. Normally Temp Tables dropped when connection close, but persistant connection do not close so Temp tables are no more temporary.

Explain advantages of MyISAM over InnoDB?

Much more conservative approach to disk space management – each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.