How to Fix MySQL Database (MyISAM / InnoDB)

So... your shiny MySQL database is no longer running and you want to fix it?

You've come to the right place!

I've assembled a list of 7 ways to fix your MySQL database when a simple restart doesn't do the trick, or when you have corrupt tables.

Simple MySQL restart:

/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
/usr/local/mysql/bin/mysqld_safe &

1. Corrupt MyISAM tables

MySQL database allows you to define a different MySQL storage engine for different tables. The storage engine is the engine used to store and retrieve data. Most popular storage engines are MyISAM and InnoDB.

MyISAM tables -will- get corrupted eventually. This is a fact of life.

Luckily, in most cases, MyISAM table corruption is easy to fix.

To fix a single table, connect to your MySQL database and issue a:

repair TABLENAME

To fix everything, go with:

/usr/local/mysql/bin/mysqlcheck --all-databases -uUSERNAME -pPASSWORD -r

A lot of times, MyISAM tables will get corrupt and you won't even know about it unless you review the log files.

I highly suggest you add this line to your /etc/my.cnf config file. It will automatically fix MyISAM tables as soon as they become corrupt:

[mysqld]
myisam-recover=backup,force

If this doesn't help, there are a few additional tricks you can try.

2. Multiple instances of MySQL

This is pretty common. You restart MySQL and the process immediately dies.

Reviewing the log files will tell you another instance of MySQL may be running.

To stop all instances of MySQL:

/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
killall mysql
killall mysqld

Now you can restart the database and you will have a single running instance

3. Changed InnoDB log settings

Once you have a running InnoDB MySQL database, you should never ever change these lines in your /etc/my.cnf file:

datadir = /usr/local/mysql/data
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880

InnoDB log file size cannot be changed once it has been established. If you change it, the database will refuse to start.

4. Disappearing MySQL host tables

I've seen this happen a few times. Probably some kind of freakish MyISAM bug.

Easily fixed with:

/usr/local/bin/mysql_install_db

5. MyISAM bad auto_increment

If the auto_increment count goes haywire on a MyISAM table, you will no longer be able to INSERT new records into that table.

You can typically tell the auto_increment counter is malfunctioning, by seeing an auto_increment of -1 assigned to the last inserted record.

To fix - find the last valid auto_increment id by issuing something like:

SELECT max(id) from tablename

And then update the auto_increment counter for that table

ALTER TABLE tablename AUTO_INCREMENT = id+1

6. Too many connections

Your database is getting hit with more connections than it can handle and now you cannot even connect to the database yourself.

First, stop the database:

/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown

If that doesn't help you can try "killall mysql" and "killall mysqld"

Once the database stopped, edit your /etc/my.cnf file and increase the number of connections. Don't go crazy with this number or you'll bring your entire machine down.

On a dedicated database machine we typically use:

max_connections = 200
wait_timeout = 100

Try restarting the database and see if that helps.

If you're getting bombarded with queries and you need to be able to connect to the database to make some table changes, set a different port number in your /etc/my.cnf file, start the database, make any changes, then update the port back to normal (master-port = 3306) and restart.

7. Corrupt InnoDB tables

InnoDB tables are my favorite. Transactional, reliable and unlike MyISAM, InnoDB supports concurrent writes into the same table.

InnoDB's internal recovery mechanism is pretty good. If the database crashes, InnoDB will attempt to fix everything by running the log file from the last timestamp. In most cases it will succeed and the entire process is transparent.

Unfortunately if InnoDB fails to repair itself, the -entire- database will not start. MySQL will exit with an error message and your entire database will be offline. You can try to restart the database again and again, but if the repair process fails - the database will refuse to start.

This is one reason why you should always run a master/master setup when using InnoDB - have a redundant master if one fails to start.

Before you go any further, review MySQL log file and confirm the database is not starting due to InnoDB corruption.

There are tricks to update InnoDB's internal log counter so that it skips the queries causing the crash, but in our experience this is not a good idea. You lose data consistency and will often break replication.

Once you have corrupt InnoDB tables that are preventing your database from starting, you should follow this five step process:

Step 1: Add this line to your /etc/my.cnf configuration file:

[mysqld]
innodb_force_recovery = 4

Step 2: Restart MySQL. Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored.

Step 3: Dump all tables

Step 4: Shutdown database and delete the data directory. Run mysql_install_db to create MySQL default tables

Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)

Step 6: Restore everything from your backup

Mike Peters, 10-03-2008
Recently I was faced with the daunting task of reparing an InnoDB database gone bad. The database would not start due to corruption.

First step was turning-on InnoDB force-recovery mode, where InnoDB starts but ignores all UPDATEs and INSERTs.

Add this line to /etc/my.cnf:

innodb_force_recovery = 2

Now we can restart the database:

/usr/local/bin/mysqld_safe &

(Note: If MySQL doesn't restart, keep increasing the innodb_force_recovery number until you get to innodb_force_recovery = 8)

Save all data into a temporary alldb.sql (this next command can take a while to finish):

mysqldump --force --compress --triggers --routines --create-options -uUSERNAME -pPASSWORD --all-databases > /usr/alldb.sql

Shutdown the database again:

mysqladmin -uUSERNAME -pPASSWORD shutdown

Delete the database directory. (Note: In my case the data was under /usr/local/var. Your setup may be different. Make sure you're deleting the correct directory)

rm -fdr /usr/local/var

Recreate the database directory and install MySQL basic tables

mkdir /usr/local/var
chown -R mysql:mysql /usr/local/var
/usr/local/bin/mysql_install_db
chown -R mysql:mysql /usr/local/var

Remove innodb_force_recovery from /etc/my.cnf and restart database:

/usr/local/bin/mysqld_safe &

Import all the data back (this next command can take a while to finish):

mysql -uroot --compress < /usr/alldb.sql

And finally - flush MySQL privileges (because we're also updating the MySQL table)

/usr/local/bin/mysqladmin -uroot flush-privileges

-

Note: For best results, add port=8819 (or any other random number) to /etc/my.cnf before restarting MySQL and then add --port=8819 to the mysqldump command. This way you avoid the MySQL database getting hit with queries while the repair is in progress.

From : softwareprojects.com

Read More... Read more...

Connection VB6.0 to MS SQL SERVER 2000

This is a simple code to connect vb6.0 with sql server, learn more...


First, make sure that your computer have been installed vb6.0 and ms SQL SERVER. Make a new project in vb6.0, Select Menu (Project => Reference) and check the Microsoft ActiveX Data Objects library (Sample : Microsoft ActiveX Data Objects 2.5 library) then click OK button.

Second, add a module (Select menu Project => Add Module) and paste code bellow :


Public Conn As New ADODB.Connection
Public Rec As New ADODB.Recordset 'berfungsi sebagai variabel penyimpan sementara
Public SQL As String ' variabel untuk menampung kode atau query sql
Public Server,Database,UserName,Password as String

Public Sub OpenConn()
On Error GoTo Handle
Set Conn = New Connection
Set Rec = New Recordset
With Conn
.ConnectionString = "Data Source=" & Server & ";Initial Catalog=" & Database & ";User Id=" & UserName & ";Password=" & Password
.Provider = "SQLOLEDB"
.Open
End With
Rec.Open SQL, Conn
Handle:
Msgbox"Any Problems !"
End Sub

Public Sub CloseConn()
Conn.Close
Set Conn = Nothing
End Sub


Try and enjoy it....

Any problems ? comment this article.

Read More... Read more...

Building SQL Server Applications Overview

Application Programming Interfaces (APIs) are the mechanisms used by applications to access resources on the local computer or available through a network. Microsoft® SQL Server™ 2000 supports several classes of APIs that applications can use to access SQL Server resources:

* General database access APIs allow applications to work with the data in a relational database. The APIs present results to applications in one of two forms:
o Tabular result sets, which some APIs call rowsets.

o XML documents, which are the preferred way of representing data in Internet applications.
* SQL Server database services APIs allow applications to administer and configure the services included with the relational database engine, such as replication and Data Transformation Services (DTS).

* The Analysis Services API gives applications access to the OLAP and data mining facilities of Analysis Services. For more information, see Programming Analysis Services Applications.

* The Meta Data Services API gives applications access to the repository of SQL Server meta data stored in Meta Data Services. For more information, see Programming Meta Data Services Applications.

* The English Query API provides applications the ability to pass customer questions, written in English, about information in a database or OLAP cube to the English Query engine. The engine returns a Transact-SQL statement or MDX query that can be executed to answer the question. For more information, see Developing and Deploying English Query Applications.


For information about additional considerations regarding the use of the APIs supported by SQL Server 2000, see Application Development Architecture.
General Database Access APIs

Database applications generally deal with data in one of two formats:

* Tabular result sets, which are sometimes called rowsets. The application uses a database API to execute a Transact-SQL statement and process any result sets that may be returned. These APIs support result set processing: ADO, OLE DB, ODBC, Embedded SQL for C, and DB-Library.

* XML documents. The application uses an API or Universal Resource Locator (URL) to execute a Transact-SQL statement or XPath query. The application then retrieves any XML document that is returned. These access methods support XML documents: ADO, URLs, OLE DB.

While result set and XML processing is typically discussed in relation to retrieving the results of a command, result sets and XML documents can both be used as the source of data for modifications of database tables:

* An application using tabular result sets can open a cursor over a result set, and use data from the cursor to modify data in tables.

* An application using XML documents can use sp_xml_preparedocument to add a document to the database, and then use OPENXML to retrieve data from the document. The retrieved data can be used to modify data in tables.

Most of the general database APIs supported by SQL Server are of two types:

* An object database API uses an object model comprised of objects, properties, and interfaces an application uses to connect to a database, pass commands to the database, and retrieve results.

* A C database API is a set of C functions an application calls to connect to a database, pass commands to the database, and retrieve results.

In addition, SQL Server 2000 can be accessed from URLs in Internet applications. URLs are formatted strings, or stream objects, that Internet applications use to access resources available through the Internet or an enterprises intranet. SQL Server 2000 supports URLs that specify Transact-SQL statements, query templates, or XPath queries.

Any SQL commands sent to SQL Server 2000 through the database APIs or URLs must comply with the Transact-SQL language. Transact-SQL complies with the Entry Level of the SQL-92 standard, and in addition, supports powerful extensions to SQL-92.The SQL Server OLE DB provider and SQL Server ODBC driver also support the ODBC SQL specification. For more information, see Transact-SQL Overview.

These are the general database APIs supported by SQL Server 2000.

Programming ADO SQL Server Applications
(Microsoft ActiveX® Data Objects)
COM API recommended as the primary API for accessing data from general business applications, such as human resources, accounting, and marketing applications. ADO encapsulates the OLE DB API in a simplified object model that reduces application development and maintenance costs. The SQL Server OLE DB provider is the preferred provider to use in ADO applications that access SQL Server. ADO, similar to OLE DB, can access data from many sources, not just SQL databases. In SQL Server 2000, ADO supports XML document processing in addition to relational result set processing.

URL Access
Formatted strings or stream objects used by Internet applications to access resources available on the Internet or intranet. SQL Server 2000 supplies an ISAPI .dll that Internet Information Services (IIS) supports references to SQL Server 2000 from URLs.

OLE DB and SQL Server
Strategic, low-level, COM API for accessing data. OLE DB is recommended for developing tools, utilities, or low-level components that need high performance. The SQL Server OLE DB provider is a native, high performance provider that accesses the SQL Server TDS protocol directly. In SQL Server 2000, OLE DB supports XML document processing in addition to relational result set processing.

Programming ODBC SQL Server Applications
(Open Database Connectivity)
Open C API designed to access data in SQL databases. The SQL Server ODBC driver is a native, high-performance driver that directly accesses the SQL Server TDS protocol.

Programming Embedded SQL for C
Standard API defined for accessing SQL databases from C or COBOL applications.

Programming DB-Library for C
Legacy C API designed to work with SQL Server.

Warning While the DB-Library and Embedded SQL for C APIs are still supported in Microsoft SQL Server 2000, no future versions of SQL Server will include the files needed to do programming work on applications that use these APIs. Connections from existing applications written using DB-Library and Embedded SQL for C will still be supported in the next version of SQL Server, but this support will also be dropped in a future release. When writing new applications, avoid using these components. When modifying existing applications, you are strongly encouraged to remove dependencies on these technologies. Instead of DB-Library or Embedded SQL for C, you can use ADO, OLE DB, or ODBC to access data in SQL Server.

Through its support of ODBC, SQL Server 2000 also supports applications written to the Remote Data Objects (RDO) and Data Access Objects (DAO) APIs. These are object APIs that encapsulate ODBC. They are not discussed further in SQL Server Books Online; programmers using RDO and DAO should refer to ODBC and SQL Server 2000 for implementation details for the SQL Server ODBC Driver.

SQL Server Books Online topics about ADO, OLE DB, and ODBC do not cover the full functionality of those APIs. The topics cover only the issues specific to those APIs when you are using the SQL Server OLE DB provider or the SQL Server ODBC driver. They assume that you are familiar with the general concepts for the API you are using, and that you have access to the documentation for the API. You can download the documentation for ADO, OLE DB, and ODBC at Microsoft Web site.

Microsoft Distributed Transaction Coordinator (MS DTC) is a component that allows applications to define distributed transactions. Distributed transactions protect the integrity of a series of updates made against multiple servers. SQL Server 2000 database applications can initiate distributed transactions themselves by calling the MS DTC API directly, but the SQL Server database engine can also call MS DTC to implement the functionality required by distributed Transact-SQL statements executed by applications. For more information, see MS DTC Distributed Transactions
SQL Server Database Services APIs

SQL Server 2000 supports APIs that allow applications to configure and administer the DTS and replication components of SQL Server. Applications can use the same administration and configuration API, SQL-DMO, which the SQL Server tools use when managing instances of SQL Server.

DTS Programming Reference
(Data Transformation Services)
Set of COM interfaces (based on OLE DB) for defining and executing complex data transformations between OLE DB data providers.

MS DTC Distributed Transactions
Component that allows applications to define distributed transactions that protect the integrity of a series of updates made against multiple servers. Applications use the transaction commands of an API or Transact-SQL, the API or SQL Server 2000 interface with MS DTC to implement the distributed transactions.

Programming Extended Stored Procedures
C API for writing SQL Server extended stored procedures.

Getting Started with Replication Programming
Set of COM interfaces for defining and managing replication between instances of SQL Server databases. You can also replicate data from heterogeneous third-party databases to SQL Server.

Developing SQL-DMO Applications
(SQL Distributed Management Objects)
Set of COM interfaces for managing and administering SQL Server 2000.

For information about additional considerations regarding the use of the APIs discussed, see Application Development Architecture.

Through its support of ODBC, SQL Server 2000 also supports applications written to the Remote Data Objects (RDO) and Data Access Objects (DAO) APIs. These are object APIs that encapsulate ODBC. They are not discussed further in SQL Server Books Online; programmers using RDO and DAO should refer to ODBC and SQL Server 2000 for implementation details for the SQL Server ODBC Driver.

SQL Server Books Online topics about ADO, OLE DB, and ODBC do not cover the full functionality of those APIs. The topics cover only the issues specific to those APIs when you are using the SQL Server OLE DB provider or the SQL Server ODBC driver. They assume that you are familiar with the general concepts for the API you are using, and that you have access to the documentation for the API. You can download the documentation for ADO, OLE DB, and ODBC at Microsoft Web site.

By : http://msdn.microsoft.com

Read More... Read more...