Red Web Turtle

Got this error while trying to import a large .sql file in MySQL Workbench: ERROR 1118 (42000) at line 171970: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size. Solution to the above error in XAMPP is as follows: In xampp\mysql\bin\my.ini file, set innodb_log_file_size = 128M (the default value was around 20M) Save the file and restart MySQL server.

This SQL helps you list the tables with a certain column name. As you can see in the script, you can also use comma-separated multiple column names. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('column_name1', 'column_name2') AND TABLE_SCHEMA='database_name' You may also use "COLUMN_NAME LIKE" to find a closer match of something you are looking for such as: SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'column_name%' AND TABLE_SCHEMA='database_name'

In Windows, MySQL database tables are saved with lowercase letters. When you import tables with case sensitive names, these get converted into lowercase names. If you want to maintain case sensitivity in MySQL database, do the following: In your MySQL installation folder, find my.ini file and open it. If you are using xampp, it's usually located in /mysql/bin/ directory. In [mysqld] section, modify the value of lower_case_table_names to 0. If this entry is not there already, simply add this line: lower_case_table_names = 0 Then restart your MySQL server.

In my new xampp 1.8.3 installation, I installed Opencart 1.5.6.1 and received a deprecated warning message such as: Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in ....\system\database\mysql.php on line 6 The quick way to resolve this issue is by making a small change to the config.php and admin/config.php files. In both these config.php files, look for the line define('DB_DRIVER', 'mysql'); Modify the above line to define('DB_DRIVER', 'mysqli'); The new line will simply connect to the database from system/database/mysqli.php file.

Delete records where date is less than a certain date: DELETE FROM `tablename` WHERE `date` < '2012-08-01'; You can also include intervals: DELETE FROM `tablename` WHERE `date`  < DATE_SUB(NOW(), INTERVAL 3 MONTH); DELETE FROM `tablename` WHERE `date`  < DATE_SUB(NOW(), INTERVAL 3 WEEK); DELETE FROM `tablename` WHERE `date`  < DATE_SUB(NOW(), INTERVAL 2 DAY);

Tested in v5.6.0.2. When you are writing table schema for a block in db.xml and have field name as "key", this generates MySQL syntax error while trying to install a package. Just rename the field name to something else. I think it's because "key" already represents a primary key attribute and using the same name again in field name somehow causes conflict.

When I was trying to import a large sql file (about 11MB) in phpMyAdmin, I got "Error 2006: MySQL server has gone away". Solution to the above error in XAMPP is as follows: In xampp\mysql\bin\my.ini file, set max_allowed_packet to something like 10M or 20M (larger than the sql file you are trying to import) Save the file and restart Apache and MySQL servers. That should do it. There is no need to make changes to php.ini in this case. However, if you need to import even larger file (say 4GB) , then you will need to modify both my.ini and php.ini file. For modifying xampp\mysql\bin\my.ini , follow the steps as above to set max_allowed_packet = 4GB. Then modify xampp\php\php.ini , set post_max_size = 4G upload_max_filesize = 4G Save the files and restart Apache and MySQL servers.

I tried installing MySQL Workbench 5.2 on Win 7 64-bit machine. I got this error message: The program can't start because MSVCR100.dll is missing from your computer. Try reinstalling the program to fix this problem . I uninstalled the program. I downloaded and installed Microsoft Visual C++ 2010 Redistributable Package (x86) from this link: http://www.microsoft.com/en-us/download/details.aspx?id=5555. Then I reinstalled MySQL Workbench and it started working! Just make sure you install MySQL Workbench 5.2 x86 (32-bit) and the Microsoft Visual C++ 2010 Redistributable Package (x86) 32-bit and not x64.

If the date is stored in the database in 10-digit Unix format such as "1203683582" and you wish to do comparision with other date formats, do something like this: SELECT * FROM mytable WHERE date BETWEEN UNIX_TIMESTAMP('2005-02-12') AND UNIX_TIMESTAMP('2010-01-01'); Remember to use the date within UNIX_TIMESTAMP() function in yyyy-mm-dd international standard (this is a default format used by MySQL anyway). Passing the date in dd/mm/yyyy format does not work. If you do not pass the time in the UNIX_TIMESTAMP(), the time defaults to 00:00:00.