Has anyone ever told you that your SQL has an error?

Quote: The real environment of online operation is always unpredictable, obviously you are in the local test when all kinds of situations are OK do not want to, perhaps you still in a place for yourself cool effect and magical design excited! However, after the line, will occasionally be like convulsions, there will be someone with you, there is a problem, there is a problem! I was wondering, how could it be a problem? Haha, this is perhaps most of the siege Lions most often send out the sigh!

So, today we're going to talk about how to find your mistakes and solve some of the errors quickly, rather than wait until the user comes back to you, because it might have been wrong too long! What we are going to do is to find the mistake in the first place, settle in the bud, and make a summary afterwards to avoid making a similar mistake again! Because, wrong is not terrible, terrible is always make the same mistake, then, you and the novice what difference?

Explain the points:

1. This article mainly solves the problem of SQL-related errors;

2. This article is based on PHP micro-view; (Mom, who's calling me to be in PHP development?) ）

3. The problem solved in this paper is, how to find the problem, and how to solve the problem;

4. Welcomes queries and additions;

How to find a problem?

What to test, then needless to say, who would not dare to test directly on-line? If that's true, all I can say is, you're awesome! Testing is a very important defense, in general, after the function after testing, on-line, the bug will not be too much, or not too obvious! Well, I'll just assume that the test doesn't have a problem, so let's go online! In fact, after the on-line, we have no bottom in mind, especially in some have not yet a complete set of deployment system, before and after the online will always come out of all kinds of strange. In the end development and users are two levels of people, ghosts know what will happen? We are hanging a heart, unexpectedly to rely entirely on the user's operation, user feedback? Oh, no, that's too passive!

proactively identify problems.  First, after the line, you can no longer test, you are now an ordinary user, then, you have to operate is necessary, a probably go down, the basic function can be determined, OK, next, the real to the user! Second, if there is no hand, it is really completely to the user, then you are too low, because, the necessary monitoring measures are must have! This refers to the monitoring is the program level, that is, the so-called error. How to record the error message? How do you know the error? All said, I am engaged in the development of PHP. PHP has a record error log function, error_reporting, give this to open, specify the error log location, level, you can record PHP errors, but be sure to close the wrong page display, otherwise, users see such errors, you are doomed! Since this article is talking about SQL errors, it should not be here, right. But I would like to say that when the SQL error, PHP has also issued an error, usually a warning level of errors, and this error is often associated with the occurrence, such as the following statement depends on the query above, and the above has been reported, then the follow-up will follow the error. PHP knows the error, but it's just about, what's wrong with SQL? The only thing he knows is that the record will be given to him. The general principle is that, in the query error, the error log, error log main record information is: Error message, file location (traced back to the file until the entrance), the following is a sample of error information to log the PHP code for reference:

<?PHP//Logging SQL error logs    Privatefunction LogError ($msg ="")    {        if(Isset ($ This-_logfile)) {            if(!$msg) {                if(!Mysql_errno ()) {                    return; } $msg="Mysql_errno:". Mysql_errno ()."\nmysql_error:". Mysql_error (); } file_put_contents ($ This->_logfile,'['. Date'y-m-d h:i:s') ."] $msg \ n", File_append); $trace=Debug_backtrace (); foreach($trace as$call) {                if(Empty ($call ['file']) && Empty ($call [' Line']))                {                        Continue; } file_put_contents ($ This->_logfile,"{$call [' file '} on line {$call [' line ']}\n", File_append); } file_put_contents ($ This->_logfile,"\ n", File_append); }}

In this way, you have a reference to the thing, when the change, you just go to see the document indicates the place, you probably know what is wrong, know what is wrong, I think, to solve the problem, it should be just a matter of time, but also a short time problem!

Topic two, how to solve the problem?

In fact, I have already said that, since the root of the problem has been found, to solve the problem is only a short time, but, I will give some solutions for reference, after all, everyone is so busy, which has time to crack your rotten code! The following is a frequently occurring error:

1. Error: [2002] Unable to connect because the target computer was actively refused. Parse: The database connection information is wrong, you may have deployed the test environment connection to the line, this problem is very big, quickly recover it? Note: If there is no error because your online machine can connect the test library, the problem is also very big;

2. Error: [1146] Table ' bbbq ' doesn ' t exist. Analysis: The table does not exist, quickly check, is not this new addition of the table has not been added to the line, or the table name is wrong? Note: Generally over the test is not likely to be the table name is wrong!

3. Error: [1054] Unknown column ' column_x ' in ' Field List '. Parse: The specified column does not exist, or the library is not, or write the wrong, quickly check! Note: Ibid.

4. Error: [1366] Incorrect string value: ' \xa9\x96 ' for column ' X_name ' at row 1. Parsing: Character set problems, such as a GBK word by the field set to gb2312 receive this problem will occur, quickly change back! Note: Please try to use UTF-8 encoding, code and warehousing are convenient!

5. Error: [1364] Field ' pid ' doesn ' t has a default value.   Parse: Specifies that the field does not have a default value, verify that the value that should be obtained is not obtained and thereby null, specifying the database default for some unnecessary fields. Note: If you have an index relationship, you must set the NOT NULL option, otherwise the index may fail!

6. Error: [1366] Incorrect integer value: ' For column ' Townid ' at row 1.  Parse: The given value does not conform to the field type requirements, before the storage to confirm what type of the field, you can do the appropriate casting, and then storage. Note: Some versions of MySQL can handle this problem by self-casting type, but the result may be more than you expect!

...

Wait, still have to feed the family, rush to work, later add! Follow-up question keyword tip: LOAD DATA INFILE, REPLACE ...

Has anyone ever told you that your SQL has an error?