Can't run XAMPP - MySql

 This is as a result of some files in C:\xampp\mysql\data\mysql getting corrupted.

Solution:

1. Back up C:\xampp\mysql\data
2. Copy all file C:\xampp\mysql\backup
3. Paste and replace existing file in: C:\xampp\mysql\data, except for the ibdata1 file.
   Leaving ibdata1 will help against table does not exist error.

How to export a mysql database using Command Prompt?

First check if your command line recognizes mysql command. If not go to command & type in:

set path=c:\wamp\bin\mysql\mysql5.1.36\bin

Then use this command to export your database:

mysqldump -u YourUser -p YourDatabaseName > wantedsqlfile.sql

You will then be prompted for the database password.

This exports the database to the path you are currently in, while executing this command

How to update a JOINed tables using Codeigniter's Active Record?

Codeigniter active record doesn't allow to update a joined tables. 
After trying various method and searching the solution, I have found the following solution which does the exactly same thing i.e. update the multiple join tables. By using following method, you can update multiple table using codeigniter active record.

$this->db->set('a.firstname', 'Pekka');
$this->db->set('a.lastname', 'Kuronen');
$this->db->set('b.companyname', 'Suomi Oy');
$this->db->set('b.companyaddress', 'Mannerheimtie 123, Helsinki Suomi');

$this->db->where('a.id', 1);
$this->db->where('a.id = b.id');
$this->db->update('table as a, table2 as b');

Increase or Change the phpMyAdmin Session Timeout

Did a little grepping and found that you can override this setting, even though it’s not documented anywhere but in teh codes (so far as I can tell). To override it, just open up config.inc.php in the root phpMyAdmin directory and add this setting (anywhere):
file located 
eg:
C:\wamp\apps\phpmyadmin3.5.1\libraries\config.default.php

$cfg['LoginCookieValidity'] = <your_new_timeout>;

Where <your_new_timeout> is some number larger than 1800. Personally, I chose 28800, which is 8 hours.  :P

CodeIgniter's Transactions

Transactions

CodeIgniter's database abstraction allows you to use transactions with databases that support transaction-safe table types. In MySQL, you'll need to be running InnoDB or BDB table types rather than the more common MyISAM. Most other database platforms support transactions natively.

If you are not familiar with transactions we recommend you find a good online resource to learn about them for your particular database. The information below assumes you have a basic understanding of transactions.

CodeIgniter's Approach to Transactions

CodeIgniter utilizes an approach to transactions that is very similar to the process used by the popular database class ADODB. We've chosen that approach because it greatly simplifies the process of running transactions. In most cases all that is required are two lines of code.

Traditionally, transactions have required a fair amount of work to implement since they demand that you to keep track of your queries and determine whether tocommit or rollback based on the success or failure of your queries. This is particularly cumbersome with nested queries. In contrast, we've implemented a smart transaction system that does all this for you automatically (you can also manage your transactions manually if you choose to, but there's really no benefit).

Running Transactions

To run your queries using transactions you will use the $this->db->trans_start() and $this->db->trans_complete() functions as follows:

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
$this->db->trans_complete();

You can run as many queries as you want between the start/complete functions and they will all be committed or rolled back based on success or failure of any given query.

Strict Mode

By default CodeIgniter runs all transactions in Strict Mode. When strict mode is enabled, if you are running multiple groups of transactions, if one group fails all groups will be rolled back. If strict mode is disabled, each group is treated independently, meaning a failure of one group will not affect any others.

Strict Mode can be disabled as follows:

$this->db->trans_strict(FALSE);

Managing Errors

If you have error reporting enabled in your config/database.php file you'll see a standard error message if the commit was unsuccessful. If debugging is turned off, you can manage your own errors like this:

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->trans_complete();

if ($this->db->trans_status() === FALSE)
{
    // generate an error... or use the log_message() function to log your error
}

Enabling Transactions

Transactions are enabled automatically the moment you use $this->db->trans_start(). If you would like to disable transactions you can do so using $this->db->trans_off():

$this->db->trans_off()

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->trans_complete();

When transactions are disabled, your queries will be auto-commited, just as they are when running queries without transactions.

Test Mode

You can optionally put the transaction system into "test mode", which will cause your queries to be rolled back -- even if the queries produce a valid result. To use test mode simply set the first parameter in the $this->db->trans_start() function to TRUE:

$this->db->trans_start(TRUE); // Query will be rolled back
$this->db->query('AN SQL QUERY...');
$this->db->trans_complete();

Running Transactions Manually

If you would like to run transactions manually you can do so as follows:

$this->db->trans_begin();

$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');

if ($this->db->trans_status() === FALSE)
{
    $this->db->trans_rollback();
}
else
{
    $this->db->trans_commit();
}

Note: Make sure to use $this->db->trans_begin() when running manual transactions, NOT $this->db->trans_start().

Error Number: 1366 (Incorrect integer value:

When a custom field is created with Field Type: Text Input, Field Content: Integer, Required Field: No, and I create a new entry, while leaving this field blank, the error

A Database Error Occurred

Error Number: 1366

Incorrect integer value: ‘’ for column ‘field_id_10’ at row 1

is displayed.

solution:

Is your server running in MySQL “Strict” mode, comment that line as follows.

#sql-mode=“STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

After changing the my.ini file and restarting the mySQL server, the problem disappeared.

The Server Wizard should mention this as a warning/error, shouldn’t it?

MySQL - Connect to your database remotely

Getting your IP address

You will need to know what the IP address you are connecting from. To find this you can go to one of the following sites:

• http://www.ipchicken.com
• http://www.whatismyip.com

Granting Access

Granting access to a user from a remote host is fairly simple and can be accomplished from just a few steps. First you will need to login to your MySQL server as the root user. You can do this by typing the following command:

# mysql -u root -p

This will prompt you for your MySQL root password.

Once you are logged into MySQL you need to issue the GRANT command that will enable access for your remote user. In this example we will be creating a brand new user (fooUser) that will have full access to the fooDatabase database.

Keep in mind that this statement is not complete and will need some items changed. Please change 1.2.3.4 to the IP address that we obtained above. You will also need to change my_password with the password that you would like to use for fooUser.

mysql> GRANT ALL ON fooDatabase.* TO fooUser@'1.2.3.4' IDENTIFIED BY 'my_password';

This statement will grant ALL permissions to the newly created user fooUser with a password of 'my_password' when they connect from the IP address 1.2.3.4.

Testing Remotely

Now you can test your connection remotely. You can access your MySQL server from another Linux server:

# mysql -u fooUser -p -h 44.55.66.77
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.45 Source distribution

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

mysql> _

Note that the IP of our MySQL server is 44.55.66.77 in this example.

Notes

There are a few things to note when setting up these remote users:

• When setting up users a local user is not the same thing as a remote user. For instance fooUser@localhost is not the same as fooUser@1.2.3.4. You will have to duplicate permissions if you want them to have the same permissions.
• Granting ALL permissions is not advised. Using GRANT SELECT,INSERT,UPDATE,DELETE is a wise alternative for a normal user.
• If you would like to grant only to a specific table you can use database.table instead of database.*. In respect to our example above you could put fooDatabase.fooTable.
• If you are using iptables you will need to make an entry in your firewall for TCP port 3306. When creating your firewall rule you can simply use the name 'mysql' for the port number. Search our wiki for iptables and you will find a list of common rule sets which include an entry for MySQL.

MYSQL : "THE USER SPECIFIED AS A DEFINER ('ROOT'@'%') DOES NOT EXIST"

The source code for the stored procedures that you have been loading probably contain "DEFINER=root@'%'" as part of the definition - looking a bit like this:

create definer='root'@'%' procedure sp_test() begin end;

The problem here is that you do not have an account on your system for 'root'@'%'. This can be easily demonstrated. From the MySQL command line:

show grants for 'root'@'%';

I expect that this will come back with an error message:

ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'

The fix is to alter the source of your stored procedures, or to create the missing account:

grant all on *.* to 'root'@'%' identified by 'password' with grant option;

It is not generally a good idea to have such a high-powered account accessible from anywhere, but that is another story.