Open Software Solutions

To increment or decrement and existing number in a table (you do not need to read the value first)  please read on : Increment the value 'timer' by one for the row in table 'movies' where 'movie_id' is '24', use: UPDATE movies SET timer=timer+1 WHERE movie_id=24 Note  to decrement use timer=timer-1 Enjoy Read more..

We will  add a column called email to the mycontacts table created in Create a basic MySQL table with a datatype of VARCHAR(90) To do this use the following SQL statements: 1.  this statement will add the column at the bottom of the table  ALTER TABLE mycontacts ADD email VARCHAR(90); 2.  this statement will add the column after a specific column ( such as name ) ALTER TABLE mycontacts ADD email VARCHAR(90) AFTER name; 3. this statement will add the column at the begining of ... Read more..

Quick Status: mysql> \s -------------- /usr/local/bin/mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686) Connection id: 642 Current database: Current user: prog@localhost SSL: ... Read more..

First step is to select a database , after that you can view the tables  1 . view all of the tables in the selected database : SHOW TABLES; 2.  view all of the tables in a different database (not selected): SHOW TABLES IN different_db; 3.  view a specific table in the selected  database : SHOW TABLES IN newDB LIKE '%time'; The above command will find all tables that end in "time"  from the database newDB Read more..

This is how to delete the column my_thing from the table your_table ,  (use the following SQL command: ) ALTER TABLE 'your_table' DROP 'my_thing' enjoy Read more..

Function CONCAT allows the SELECT statements to act like printf commands by ataching text results to query results and so on Example : Generate a list of links in HTML from a table 'mylinks' with the columns 'urls' (http://www.ossmall.info) with 'title' (Open Software Solutions) The output will look like this : <a href="http://www.ossmall.info">Open Software Solutions</a><br /> To do this you must use a select statement like: SELECT CONCAT('<a href="', urls, '">', title, '</a><br />') FROM mylinks; Read more..

Transactions: Not all table types support transactions. BDB and INNODB type do support transactions. Assuming the server has NOT been started with --skip-bdb or --skip-innodb the following should work: mysql> create table tran_test (a int, b int) type = InnoDB; mysql> begin; mysql> insert into tran_test (a,b) values (1,2); ... Read more..

You do not need to enter mysql for this , here is the exact sintax you must use : mysqldump -u [username] -p [database_name] > /path/to/file.sql  example mysqldump -u mario  -p marioDB > /tmp/marioDB.sql Read more..

This is how to create a user youruser with password YourPassword  and grant them full access to the  database named yourdatabase, connect to the database with mysql and issue the command: grant all on yourdatabase.* to user@localhost identified by 'YourPassword'; Read more..

MySQL Replace(), here's the syntax. update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]'); Read more..

"error: 1016: Can't open file:" If it's from an orphaned file, not in the database but on disk, then, the disk file may need to be deleted. myisamchk can help with damaged files. It's best to stop the database. # su - # mysqladmin shutdown ... Read more..

To change the MySQL root password to DifferentPass , use: mysqladmin -u root password DifferentPass Keep in mind , root is a powerfull user in mysql , use Hard Passwords ! Read more..

Recovering / Changing Your MySQL Root Password Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. The steps you need are: 1) Stop MySQL [root@bigboy tmp]# service mysqld stop Stopping MySQL:  [  OK  ] [root@bigboy tmp]# 2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords. [root@bigboy tmp]# mysqld_safe --skip-grant-tables --skip-networking & [1] 13007 [root@bigboy tmp]# Starting mysqld daemon with databases from /var/lib/mysql [root@bigboy tmp]# Note: In Fedora ... Read more..

Installation: configured for encryption, C API, and user defined functions. ./configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags=-rdynamic The --with-openssl is very helpful for creating your own password file. Also, if doing C API, having thread safe calls "could" come in handly...it's what I use. See (TIP 27) for user defined functions. Complete ... Read more..

Print defaults for the current client connection $ my_print_defaults client mysql --port=3306 --socket=/tmp/mysql.sock --no-auto-rehash Note, for client connections, you can put setting in the user's .my.cnf file. ... Read more..

Remove duplicate entries. Assume the following table and data. CREATE TABLE IF NOT EXISTS dupTest ( pkey int(11) NOT NULL auto_increment, a int, ... Read more..

Clean up binary log files. For a default install they may be in /usr/local/var/ or /var/lib/mysql/ with names ending in -bin.000001,-bin.000002,.. The following command may help find out where the logs are located. mysql> show variables like '%home%'; ... Read more..

MERGE: Several tables can be merged into one. CREATE TABLE log_01 ( pkey int(11) NOT NULL auto_increment, a int, b varchar(12), timeEnter timestamp(14), ... Read more..

Creating tables in databases is the  first step to store your data To create a table you must describe the columns and their atributes. We will create a table to hold contact information with four columns: contact_id, name, email, and birthdate. contact_id column =  integer number that is 20 decimal places long ( it is created with an INT(20) datatype). name column holds the full name of a contact, which we  will will set be no longer than 50 characters long, so the ... Read more..

If you want to change the  max_allowed_packet fast, without modifying any confis or restart mysql ? Follow this howto : mysql> show variables like 'max%' ; +-----------------------+------------+ | Variable_name | Value | +-----------------------+------------+ | max_allowed_packet | 1048576 | ... mysql> set max_allowed_packet = 1500000; Query OK, 0 rows affected (0.03 sec) mysql> show variables like 'max%' ; +-----------------------+------------+ | Variable_name | Value ... Read more..