From the manual: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

For example, to convert a table to be an `InnoDB` table, use this statement:
    
    ALTER TABLE t1 ENGINE = InnoDB;

The outcome of attempting to change a table&#39;s storage engine is affected by whether the desired storage engine is available and the setting of the `NO_ENGINE_SUBSTITUTION` SQL mode, as described in [Section 5.1.11][1], “Server SQL Modes”.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_engine_substitution

When you create the table do you get any warning about the Engine type being unavailable?


  [1]: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_engine_substitution

It&#39;s not obvious. If you edit the table and then **select the column tab** the engine widget is not immediately visible. On the upper right of the edit window you will see two down pointing chevrons. **Select the arrow once** and additional widgets will appear. In the upper right hand corner **there will now be widgets** for the schema and engine.

[![Result][1]][1]


  [1]: http://i.stack.imgur.com/HI9Yh.png

When I create a WebClient to consume some RESTful xml, I can specify the unicode encoding 2 ways:

	WebClient wc = new WebClient ();
	wc.Encoding = Encoding.UTF8;
	wc.Encoding = UTF8Encoding.UTF8;

Which is correct/better ?


WebClient Unicode - Which UTF8?

Is there a way that I can let mouse events pass through to controls behind?

Is there a way to make controls transparent to mouse events in WPF?

I am using mysql and mysql workbench. I created 5 tables with innodb engine. I checked their engine and it was innodb before I insert data into them. I inserted data from 5 MyISAM tables and now my innodb tables are MyISAM. I can&#39;t change them. I used the alter table engine=innodb but it doesn&#39;t work.


Changing Table Engine in MySQL

<p>I am using mysql and mysql workbench. I created 5 tables with innodb engine. I checked their engine and it was innodb before I insert data into them. I inserted data from 5 MyISAM tables and now my innodb tables are MyISAM. I can't change them. I used the alter table engine=innodb but it doesn't work.</p>


I was wondering if there is a way to do this purely in sql:

    q1 = SELECT campaign_id, from_number, received_msg, date_received 
         FROM `received_txts` WHERE `campaign_id` = &#39;8&#39;;
    INSERT INTO action_2_members (campaign_id, mobile, vote, vote_date)    
        VALUES(q1.campaign_id, q1.from_number, q1.received_msg, q1.date_received);

&lt;strong&gt;Note: &lt;/strong&gt;q1 would return about 30k rows.

Is there any way to do what I am attempting above in straight sql?
To just pull the data straight from one table (basically a raw data table) and insert into another table (basically a processed data table)?


mySQL :: insert into table, data from another table?

I need to create an index on a ~5M rows MySQL table. It is a production table, and I fear a complete block of everything if I run a CREATE INDEX statement... 

Is there a way to create that index without blocking inserts and selects?

Just wondering I have not to stop, create index and restart my system!


Create an index on a huge MySQL production table without table locking

&gt; **Possible Duplicate:**  
&gt; [How to get the insert ID in JDBC?](https://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc)  

&lt;!-- End of automatically inserted text --&gt;

Hi, I&#39;m using JDBC to connect on database through out Java. 

Now, I do some insert query, and I need to get the id of last inserted value (so, after a &lt;code&gt;stmt.executeUpdate&lt;/code&gt;).

I don&#39;t need something like &lt;code&gt;SELECT id FROM table ORDER BY id DESC LIMIT 1&lt;/code&gt;, because I may have concurrency problems. 

I Just need to retrieve the id associated to the last insertion (about my instance of the Statement).

I tried this, but seems it doesn&#39;t work on JDBC :

    public Integer insertQueryGetId(String query) {
        Integer numero=0;
        Integer risultato=-1;
        try {
            Statement stmt = db.createStatement();
            numero = stmt.executeUpdate(query);

            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()){
                risultato=rs.getInt(1);
            }
            rs.close();

            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            errore = e.getMessage();
            risultato=-1;
        }
      return risultato;
    }

In fact, every time &lt;code&gt;risultato = -1&lt;/code&gt;, and I get &lt;code&gt;java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement().&lt;/code&gt;

How can I fix this problem? Thanks Stackoverflow People :)

MySQL &amp; Java - Get id of the last inserted value (JDBC)

My team is working with a third party CMS that uses Solr as a search index.  I&#39;ve noticed that it seems like the authors are using Solr as a database of sorts in that each document returned contains two fields:

 1. The Solr document ID (basically a classname and database id)
 2. An XML representation of the entire object

So basically it runs a search against Solr, download the XML representation of the object, and then instantiate the object from the XML rather than looking it up in the database using the id.

My gut feeling tells me this is a bad practice.  Solr is a search index, not a database... so it makes more sense to me to execute our complex searches against Solr, get the document ids, and then pull the corresponding rows out of the database.

Is the current implementation perfectly sound, or is there data to support the idea that this is ripe for refactoring?

**EDIT:** When I say &quot;XML representation&quot; - I mean one stored field that contains an XML string of all of the object&#39;s properties, not multiple stored fields.

Using Solr search index as a database - is this &quot;wrong&quot;?

Does any one know the equivalent to this TSQL in MySQL parlance?

I am trying to capitalize the first letter of each entry.


    UPDATE tb_Company SET CompanyIndustry = UPPER(LEFT(CompanyIndustry, 1))
    + SUBSTRING(CompanyIndustry, 2, LEN(CompanyIndustry))







 



Capitalize first letter. MySQL

What is the difference between logical data model and conceptual data model?

At the moment if I am doing a query on the database that should only return one row, using:

    ...query stuff...
    $query = $this-&gt;db-&gt;get();
    $ret = $query-&gt;result();
    return $ret[0]-&gt;campaign_id;

Is there a CodeIgniter function to return the first row?
something like `$query-&gt;row();`

Or even better would be the ability to, if there was only one row,
to just use the query object directly.

e.g. `$query-&gt;campaign_id;`

CodeIgniter - return only one row?

I need to export a mysql table, but it has like 5gb of entries, so I only want the structure. I am trying to do it from a simple php doing a sql query, how can I do that?

How to dump mysql table structure without data with a SQL query?

I need to list all triggers in SQL Server database with table name and table&#39;s schema.

I&#39;m almost there with this:

    SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj),
      isupdate = OBJECTPROPERTY( id, &#39;ExecIsUpdateTrigger&#39;), isdelete = OBJECTPROPERTY( id, &#39;ExecIsDeleteTrigger&#39;),
      isinsert = OBJECTPROPERTY( id, &#39;ExecIsInsertTrigger&#39;), isafter = OBJECTPROPERTY( id, &#39;ExecIsAfterTrigger&#39;),
      isinsteadof = OBJECTPROPERTY( id, &#39;ExecIsInsteadOfTrigger&#39;),
      [disabled] = OBJECTPROPERTY(id, &#39;ExecIsTriggerDisabled&#39;) 
    FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
    WHERE type = &#39;TR&#39;

I just need to get the table&#39;s schema also.

Need to list all triggers in SQL Server database with table name and table&#39;s schema

In MySQL, there is no way to specify a storage engine for a certain database, only for single tables.  However, you can specify a storage engine to be used during one session with:

    SET storage_engine=InnoDB;

So you don&#39;t have to specify it for each table.

**How do I confirm, if indeed all the tables are using InnoDB?**

How do I know if a mysql table is using myISAM or InnoDB Engine?

Let me start by saying I have looked at many similar questions asked, but all of them relate to `Timestamp` and `DateTime` field type without indexing. At least that is my understanding. 

As we all know, there are certain advantages when it comes to DateTime. Putting them aside for a minute, and assuming table&#39;s engine is `InnoDB` with `10+ million records`, which query would perform faster when criteria is based on:

&gt;1. DateTime with index
&gt;2. int with index

In other words, it is better to store date and time as `DateTime` or UNIX timestamp in `int`? Keep in mind there is no need for any built-in MySQL functions to be used.

**Update**

Tested with MySQL 5.1.41 (64bit) and 10 million records, initial testing showed significant speed difference in favour of `int`. Two tables were used, `tbl_dt` with `DateTime` and `tbl_int` with `int` column. Few results:

    SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt`;
    +----------+
    | COUNT(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (2 min 10.27 sec)

    SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int`;
    +----------+
    | count(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (25.02 sec)

    SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt` WHERE `created` BETWEEN &#39;2009-01-30&#39; AND &#39;2009-12-30&#39;;
    +----------+
    | COUNT(*) |
    +----------+
    |   835663 |
    +----------+
    1 row in set (8.41 sec)

    SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int` WHERE `created` BETWEEN 1233270000 AND 1262127600;
    +----------+
    | COUNT(*) |
    +----------+
    |   835663 |
    +----------+
    1 row in set (1.56 sec)


I&#39;ll post another update with both fields in one table as suggested by [shantanuo][1].

**Update #2**

Final results after numerous server crashes :) Int type is significantly faster, no matter what query was run, the speed difference was more or less the same as results above.

&quot;Strange&quot; thing observed was execution time was more or less the same when two both field types are stored in the same table. It seems MySQL is smart enough to figure out when the values are the same when stored in both DateTime and int. Haven&#39;t found any documentation on the subject, therefore is just an observation.

 [1]: https://stackoverflow.com/users/139150/


MySQL Integer vs DateTime index

I want to start using table relations in a new project.

After some googling I got 2 tables set up as InnoDB:

The keys I want to link are

-&gt;users-&gt;userid (primary)
-&gt;sessions-&gt;userid (index)

The only thing that I don&#39;t understand in this process is what the different settings for &quot;On update&quot; and &quot;On delete&quot; do

The options here are:

- -- (nothing?)
- Cascade (???)
- Set Null (sets everything to null?)
- No action (well duh...)
- Restrict (???)

I basically want the data in sessions to be deleted when a user is completely deleted
This since the sessions will only be deleted when the expiration is detected by my session manager...

So if anyone can tell me what these options do it would be much appreciated.

Setting up table relations what do &quot;Cascade&quot;, &quot;Set Null&quot; and &quot;Restrict&quot; do?

I am trying to increase the size of the innodb_buffer_pool_size in MySQL 5.1 as I keep running into the following error indicating I have run out of space for the table locks. 

    ERROR: The total number of locks exceeds the lock table size
    Error
    Code: 1206

I have gone through the documentation and from what I gather, I need to update innodb_buffer_pool_size in the /etc/my.cnf file. My current value is 8M. However, even after creating that file and adding the following line to set the value it is not updating in MySQL. 

    set-variable=innodb_buffer_pool_size=256M

Does have any advice on how I can adjust this value in MySQL on my mac? Any other advice or suggestions? 

How to change value for innodb_buffer_pool_size in MySQL on Mac OS?

In my production error logs I occasionally see:

&gt; SQLSTATE[HY000]: General error: 1205
&gt; Lock wait timeout exceeded; try
&gt; restarting transaction

I know which query is trying to access the database at that moment but is there a way to find out which query had the lock at that precise moment?

How to debug Lock wait timeout exceeded on MySQL?

Here is a gross oversimplification of an intense setup I am working with. `table_1` and `table_2` both have auto-increment surrogate primary keys as the ID. `info` is a table that contains information about both `table_1` and `table_2`.

    table_1 (id, field)  
    table_2 (id, field, field)
    info ( ???, field)



I am trying to decided if I should make the primary key of `info` a composite of the IDs from `table_1` and `table_2`. If I were to do this, which of these makes most sense?    
*( in this example I am combining ID 11209 with ID 437 )* 

`INT(9)` 11209437   *(i can imagine why this is bad)*  
`VARCHAR (10)`    11209-437   
`DECIMAL (10,4)` 11209.437   

Or something else? 

Would this be fine to use this as the Primary Key on a MYSQL MYISAM DB?


How to properly create composite primary keys - MYSQL

In some cases, running an UPDATE statement in production can save the day. However a borked update can be worse than the initial problem.

Short of using a test database, what are options to tell what an update statement will do before running it?

How to test an SQL Update statement before running it?

Below query I&#39;m executing in Ubuntu 12, MySQL 5.1 version and receiving error as mentioned:

    CREATE TABLE mantis_config_table (
        config_id VARCHAR(64) NOT NULL,
        project_id INTEGER NOT NULL DEFAULT 0,
        user_id INTEGER NOT NULL DEFAULT 0,
        access_reqd INTEGER DEFAULT 0,
        type INTEGER DEFAULT 90,
        value LONGTEXT NOT NULL,
        PRIMARY KEY (config_id, project_id, user_id)
    ) TYPE=MyISAM;

&gt; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;TYPE=MyISAM&#39; at line 9

Can anyone suggest what&#39;s wrong? 

mysql error &#39;TYPE=MyISAM&#39;

I understand that this question has been asked before, but most of the time it is asked in relation to a specific database or table. I cannot find an answer on this site that describes the two engines and their differences without respect to someones specific database. 

I want to be able to make more informed decisions in the future with respect to designing a table or database, so am looking for a comprehensive answer on the differences between the two storage engines.

What&#39;s the difference between [MyISAM][1] and [InnoDB][2], and what should I be looking for when trying to decide between one or the other?


  [1]: http://en.wikipedia.org/wiki/MyISAM
  [2]: http://en.wikipedia.org/wiki/InnoDB

Content Type	Original Author	Original Content on Stackoverflow
Question	AliBZ	View Question on Stackoverflow
Solution 1 - Mysql	Matt Healy	View Answer on Stackoverflow
Solution 2 - Mysql	ClearCrescendo	View Answer on Stackoverflow

Changing Table Engine in MySQL

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Is there a way to make controls transparent to mouse events in WPF?

WebClient Unicode - Which UTF8?

Attributions