Yes, it is correct:

&gt; **NO ACTION**: [...] InnoDB
&gt; rejects the delete or update operation
&gt; for the parent table.
&gt;
&gt; **RESTRICT**: Rejects the delete or update
&gt; operation for the parent table.
&gt; Specifying RESTRICT (or NO ACTION) is
&gt; the same as omitting the ON DELETE or
&gt; ON UPDATE clause. [...]

Apparently `NO ACTION` and `RESTRICT` are synonymous. Additionally, since they are used whenever there is no `ON DELETE / UPDATE` clause, this is the default behavior.

&gt; **SET NULL**: Delete or update the row from the parent table and set the
&gt; foreign key column or columns in the
&gt; child table to NULL. [...]

The foreign column is set to NULL, provided it is not declared as NOT NULL (or InnoDB will not allow deletion or update).

&gt; **CASCADE**: Delete or update the row from the parent table and
&gt; automatically delete or update the
&gt; matching rows in the child table.
&gt; [...]

Cascade deletes (or updates) the foreign column.

&gt; **SET DEFAULT**: This action is recognized
&gt; by the parser, but InnoDB rejects
&gt; table definitions containing ON DELETE
&gt; SET DEFAULT or ON UPDATE SET DEFAULT
&gt; clauses.

So basically you cannot use that option.


What are some good patterns for error handling in VBA?

In particular, what should I do in this situation:

```lang-none
... some code ...
... some code where an error might occur ...
... some code ...
... some other code where a different error might occur ...
... some other code ...
... some code that must always be run (like a finally block) ...
```

I want to handle both errors, and resume execution after the code where the error may occur. Also, the finally code at the end must *always* run - no matter what exceptions are thrown earlier. How can I achieve this outcome?




What are some good patterns for VBA error handling?

I&#39;m looking to create a form where pressing the enter key causes focus to go to the &quot;next&quot; form element on the page. The solution I keep finding on the web is...

     &lt;body onkeydown=&quot;if(event.keyCode==13){event.keyCode=9; return event.keyCode}&quot;&gt;

Unfortunately, that only seems to work in IE. So the real meat of this question is if anybody knows of a solution that works for FF and Chrome? Additionally, I&#39;d rather not have to add *onkeydown* events to the form elements themselves, but if that&#39;s the only way, it will have to do.

This issue is similar to [question 905222][1], but deserving of it&#39;s own question in my opinion.

Edit: also, I&#39;ve seen people bring up the issue that this isn&#39;t good style, as it diverges from form behavior that users are used to. I agree! It&#39;s a client request :(


  [1]: https://stackoverflow.com/questions/905222/javascriptenter-key-press-event

Enter key press behaves like a Tab in Javascript

I&#39;m trying to parse the MySQL [docs](http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html). They could be clearer. What they seem to be saying is that there are five possibilities: SET NULL, NO ACTION, RESTRICT, CASCADE, and SET DEFAULT.

NO ACTION and RESTRICT do the same thing (prevent any DB change that breaks an FK) and that thing is the default so if you omit an ON DELETE clause you&#39;re saying NO ACTION (or RESTRICT -- same thing).

SET NULL allows a parent row deletion, sets the FK to NULL.

CASCADE deletes child row.

SET DEFAULT should just never be used.

Is this more or less correct?

What is MySQL&#39;s default ON DELETE behavior?

I'm trying to parse the MySQL <a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html" target="_blank" rel="noopener noreferrer">docs</a>. They could be clearer. What they seem to be saying is that there are five possibilities: SET NULL, NO ACTION, RESTRICT, CASCADE, and SET DEFAULT.
NO ACTION and RESTRICT do the same thing (prevent any DB change that breaks an FK) and that thing is the default so if you omit an ON DELETE clause you're saying NO ACTION (or RESTRICT -- same thing).
SET NULL allows a parent row deletion, sets the FK to NULL.
CASCADE deletes child row.
SET DEFAULT should just never be used.
Is this more or less correct?

&gt; **Possible Duplicate:**  
&gt; [What&amp;#39;s the difference between utf8_general_ci and utf8_unicode_ci](https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci)  

&lt;!-- End of automatically inserted text --&gt;

I&#39;ve got two options for unicode that look promising for a mysql database.

    utf8_general_ci unicode (multilingual), case-insensitive
    utf8_unicode_ci unicode (multilingual), case-insensitive

Can you please explain what is the difference between utf8_general_ci and utf8_unicode_ci? What are the effects of choosing one over the other when designing a database?

What are the differences between utf8_general_ci and utf8_unicode_ci?

I am starting to use MySQL with JDBC.

    Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
    conn = DriverManager.getConnection(&quot;jdbc:mysql:///x&quot;, &quot;x&quot;, &quot;x&quot;);
    stmt = conn.createStatement();
    stmt.execute( &quot;CREATE TABLE amigos&quot; +
                &quot;(&quot;+
                &quot;id          int AUTO_INCREMENT          not null,&quot;+
                &quot;nombre      char(20)                    not null,&quot;+
                &quot;primary key(id)&quot; +
                &quot;)&quot;);


I have 3-4 tables to create and this doesn&#39;t look good.

Is there a way to run a .sql script from MySQL JDBC?


Running a .sql script using MySQL with JDBC

When I open a MySQL connection in PHP with just PHP&#39;s built-in MySQL functions, I do the following:

    $link = mysql_connect($servername, $username, $password);
    mysql_select_db($dbname);
    //queries etcetera
    mysql_close($link);

When I open a connection with PDO, it looks like this:

    $link = new PDO(&quot;mysql:dbname=$dbname;host=$servername&quot;,$username,$password);
    //prepare statements, perform queries

Do I have to explicitly close the connection like I do with `mysql_connect()` and `mysql_close()`?  If not, how does PHP know when I&#39;m done with my connection?

TIA.

Do SQL connections opened with PDO in PHP have to be closed

What is the (default) charset for:

- MySQL database

- MySQL table

- MySQL column



How do I see what character set a MySQL database / table / column is?

I want to compare two dates with time, I want all the results `from tbl where date1 &gt; date2`

    Select * From temp where mydate &gt; &#39;2009-06-29 04:00:44&#39;;

but it is just comparing dates not time. it is giving me all the result set of today&#39;s date

    &#39;2009-06-29 11:08:57&#39;
    &#39;2009-06-29 11:14:35&#39;
    &#39;2009-06-29 11:12:38&#39;
    &#39;2009-06-29 11:37:48&#39;
    &#39;2009-06-29 11:52:17&#39;
    &#39;2009-06-29 12:12:50&#39;
    &#39;2009-06-29 12:13:38&#39;
    &#39;2009-06-29 12:19:24&#39;
    &#39;2009-06-29 12:27:25&#39;
    &#39;2009-06-29 12:28:49&#39;
    &#39;2009-06-29 12:35:54&#39;
    &#39;2009-06-29 12:36:54&#39;
    &#39;2009-06-29 12:49:57&#39;
    &#39;2009-06-29 12:58:04&#39;
    &#39;2009-06-29 04:13:20&#39;
    &#39;2009-06-29 04:56:19&#39;
    &#39;2009-06-29 05:00:23&#39;
    &#39;2009-06-29 05:04:26&#39;
    &#39;2009-06-29 05:08:17&#39;
    &#39;2009-06-29 05:26:57&#39;
    &#39;2009-06-29 05:29:06&#39;
    &#39;2009-06-29 05:32:11&#39;
    &#39;2009-06-29 05:52:07&#39;

Thanks in advance!

Mysql Compare two datetime fields

I have one MySQL table using the InnoDB storage engine; it contains about 2M data rows. When I deleted data rows from the table, it did not release allocated disk space.  Nor did the size of the ibdata1 file reduce after running the `optimize table` command.

Is there any way to reclaim disk space from MySQL?

I am in a bad situation; this application is running in about 50 different locations and now problem of low disk space is appearing at almost all of them.

MySQL InnoDB not releasing disk space after deleting data rows from table

I&#39;m developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly.

I was originally planning on using MyISAM for the tables (for the built-in [fulltext search capabilities][1]), but the thought of the *entire table* being locked due to a single write operation makes me shutter. Row-level locks make so much more sense (not to mention InnoDB&#39;s other speed advantages when dealing with huge tables). So, for this reason, I&#39;m pretty determined to use InnoDB.

The problem is... InnoDB doesn&#39;t have built-in fulltext search capabilities.

Should I go with a third-party search system? Like [Lucene(c++)][2] / [Sphinx][3]? Do any of you database ninjas have any suggestions/guidance? &lt;del&gt;LinkedIn&#39;s [zoie][4] (based off Lucene) looks like the best option at the moment&lt;/del&gt;... having been built around realtime capabilities (which is pretty critical for my application.) I&#39;m a little hesitant to commit yet without some insight...

(FYI: going to be on EC2 with high-memory rigs, using PHP to serve the frontend)

  [1]: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html
  [2]: http://sourceforge.net/projects/clucene/
  [3]: http://www.sphinxsearch.com/
  [4]: http://code.google.com/p/zoie/

Fulltext Search with InnoDB

How can I get Hibernate (using JPA) to create MySQL InnoDB tables (instead of MyISAM)? I have found solutions that will work when using Hibernate to generate an SQL file to create the tables, but nothing that works &quot;on the fly&quot;.

Hibernate: Create Mysql InnoDB tables instead of MyISAM

I&#39;m currently working on creating an environment to test performance of an app; I&#39;m testing with MySQL and InnoDB to find out which can serve us best. Within this environment, we&#39;ll automatically prepare the database (load existing dumps) and instrument our test tools.

I&#39;m preparing to test the same data dump with MySQL and InnoDB, but I&#39;m already failing to bring the initial import to an usable speed for the InnoDB part. The initial dump took longer, but that didn&#39;t concerned me yet:

    $ for i in testdb_myisam testdb_innodb; do time mysqldump --extended-insert $i &gt; $i.sql; done
    
    real    0m38.152s
    user    0m8.381s
    sys     0m2.612s
    
    real    1m16.665s
    user    0m6.600s
    sys     0m2.552s

However, the import times were quite different:

    $ for i in  testdb_myisam testdb_innodb; do time mysql $i &lt; $i.sql; done
    
    real    2m52.821s
    user    0m10.505s
    sys     0m1.252s
    
    real    87m36.586s
    user    0m10.637s
    sys     0m1.208s

After research I came over https://stackoverflow.com/questions/457060/changing-tables-from-myisam-to-innodb-make-the-system-slow and then used `set global innodb_flush_log_at_trx_commit=2`:

    $ time mysql testdb_innodb &lt; testdb_innodb.sql
    
    real    64m8.348s
    user    0m10.533s
    sys     0m1.152s

IMHO still shockingly slow. I&#39;ve also disabled `log_bin` for these tests and here&#39;s a list of &lt;a href=&quot;http://my-serve.rs/tmp/mysql-variables.txt&quot;&gt;all mysql variables&lt;/a&gt;.

Do I&#39;ve to accept this long InnoDB times or can they be improved? I&#39;ve full control over this MySQL server as it&#39;s purely for this test environment.

I can apply special configurations only for initial import and change them back for applications tests so they better match production environments.

**Update:**

Given the feedback, I&#39;ve disabled autocommit and the various checks:

    $ time ( echo &quot;SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;&quot; \
    ; cat testdb_innodb.sql ; echo &quot;COMMIT;&quot; ) | mysql testdb_innodb;date
    
    real    47m59.019s
    user    0m10.665s
    sys     0m2.896s

The speed improved, but not that much. Is my test flawed?

**Update 2:**

I was able to gain access to a different machine were imports only took about 8 minutes. I compared the configurations and applied the following settings to my MySQL installation:

    innodb_additional_mem_pool_size = 20971520
    innodb_buffer_pool_size = 536870912
    innodb_file_per_table
    innodb_log_buffer_size = 8388608
    join_buffer_size = 67104768
    max_allowed_packet = 5241856
    max_binlog_size = 1073741824
    max_heap_table_size = 41943040
    query_cache_limit = 10485760
    query_cache_size = 157286400
    read_buffer_size = 20967424
    sort_buffer_size = 67108856
    table_cache = 256
    thread_cache_size = 128
    thread_stack = 327680
    tmp_table_size = 41943040

With these settings I&#39;m now down to about 25 minutes. Still far away from the few minutes MyISAM takes, but it&#39;s getting more usable for me.

InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes

I have  a set of `InnoDB` tables that I periodically need to maintain by removing some rows  and inserting others.  Several of the tables have foreign key constraints referencing other tables, so this means that the table loading order is important.  To insert the new rows without worrying about the order of the tables, I use:

    SET FOREIGN_KEY_CHECKS=0;

before, and then:

    SET FOREIGN_KEY_CHECKS=1;

after.  

When the loading is complete, I&#39;d like to check that the data in the updated tables still hold referential integrity--that the new rows don&#39;t break foreign key constraints--but it seems that there&#39;s no way to do this.

As a test, I entered data that I was sure violated foreign key constraints, and upon re-enabling the foreign key checks, mysql produced no warnings or errors.

If I tried to find a way to specify the table loading order, and left the foreign key checks on during the loading process, this would not allow me to load data in a table that has a self-referencing foreign key constraint, so this would not be an acceptable solution.

Is there any way to force InnoDB to verify a table&#39;s or a database&#39;s foreign key constraints?

Content Type	Original Author	Original Content on Stackoverflow
Question	Ethan	View Question on Stackoverflow
Solution 1 - Mysql	Anax	View Answer on Stackoverflow

What is MySQL's default ON DELETE behavior?

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Enter key press behaves like a Tab in Javascript

What are some good patterns for VBA error handling?

Attributions