I have the following [migration](https://guides.rubyonrails.org/active_record_migrations.html) file `db\migrate\20100905201547_create_blocks.rb`

How can I specifically rollback that migration file? 

How to rollback a specific migration?

How do I see if my database has any indexes on it?

How about for a specific table?

How to see indexes for a database or table in MySQL?

From the [MySQL](https://www.mysql.com/) console, what command displays the schema of any given table?

How do I show the schema of a table in a MySQL database?

Is there a difference between a schema and a database in MySQL?  In SQL Server, a database is a higher level container in relation to a schema.

I read that `Create Schema` and `Create Database` do essentially the same thing in MySQL, which leads me to believe that schemas and databases are different words for the same objects.

Difference Between Schema / Database in MySQL

Actually what do they mean? All articles I find about them don&#39;t give me an idea, or my knowledge is too insufficient to understand it.

Will some one give me some resources with which I can learn this from scratch.

What are OLTP and OLAP. What is the difference between them?

Is there a fast way of getting all column names from all tables in `MySQL`, without having to list all the tables?

How to get all columns&#39; names for all the tables in MySQL?

I am trying to create a container with a MySQL database and add a schema to these database.

My current Dockerfile is:

    FROM mysql
    MAINTAINER  (me) &lt;email&gt;

    # Copy the database schema to the /data directory
    COPY files/epcis_schema.sql /data/epcis_schema.sql

    # Change the working directory
    WORKDIR data

    CMD mysql -u $MYSQL_USER -p $MYSQL_PASSWORD $MYSQL_DATABASE &lt; epcis_schema.sql

In order to create the container I am following the documentation provided on Docker and executing this command:

    docker run --name ${CONTAINER_NAME} -e MYSQL_ROOT_PASSWORD=${DB_ROOT_PASSWORD} -e MYSQL_USER=${DB_USER} -e MYSQL_PASSWORD=${DB_USER_PASSWORD} -e MYSQL_DATABASE=${DB_NAME} -d mvpgomes/epcisdb

But when I execute this command the Container is not created and in the Container status it is possible to see that the CMD was not executed successfully, in fact only the `mysql` command is executed.

Anyway, is there a way to initialize the database with the schema or do I need to perform these operations manually?

How can I initialize a MySQL database with schema in a Docker container?

What&#39;s the difference between a Database and a Schema in SQL Server? Both are the containers of tables and data.

If a Schema is deleted, then are all the tables contained in that schema also deleted automatically or are they deleted when the Database is deleted?


Difference between database and schema

Is it possible? Can i specify it on the connection URL? How to do that?

Is it possible to specify the schema when connecting to postgres with JDBC?

How can I set a unique constraints on two columns? 

    class MyModel extends Migration {
      public function up()
      {
        Schema::create(&#39;storage_trackers&#39;, function(Blueprint $table) {
          $table-&gt;increments(&#39;id&#39;);
          $table-&gt;string(&#39;mytext&#39;);
          $table-&gt;unsignedInteger(&#39;user_id&#39;);
          $table-&gt;engine = &#39;InnoDB&#39;;
          $table-&gt;unique(&#39;mytext&#39;, &#39;user_id&#39;);
        });
      }
    }
    
    MyMode::create(array(&#39;mytext&#39; =&gt; &#39;test&#39;, &#39;user_id&#39; =&gt; 1);
    // this fails??
    MyMode::create(array(&#39;mytext&#39; =&gt; &#39;test&#39;, &#39;user_id&#39; =&gt; 2);



schema builder laravel migrations unique on two columns

I have two tables in MySQL. Table Person has the following columns:

id | name | fruits
---|------|-------

The `fruits` column may hold null or an array of strings like (&#39;apple&#39;, &#39;orange&#39;, &#39;banana&#39;), or (&#39;strawberry&#39;), etc. The second table is Table Fruit and has the following three columns:

fruit_name | color  | price
-----------|--------|------
apple      | red    | 2
orange     | orange | 3
-----------|--------|------


So how should I design the `fruits` column in the first table so that it can hold array of strings that take values from the `fruit_name` column in the second table? Since there is no array data type in MySQL, how should I do it?

How to store arrays in MySQL?

I do not have much experience in table design. My goal is to create one or more product tables that meet the requirements below:

- Support many kinds of products (TV, Phone, PC, ...). Each kind of product has a different set of parameters, like:

  - Phone will have Color, Size, Weight, OS...

  - PC will have CPU, HDD, RAM...

- The set of parameters must be dynamic. You can add or edit any parameter you like.

How can I meet these requirements without a separate table for each kind of product?

How to design a product table for many kinds of product where each product has many parameters

I have a windows forms application written in VS2010 with C# and get the following errors in the `app.config` file:

&lt;!-- language: none --&gt;

    Message	4	Could not find schema information for the attribute &#39;name&#39;
    Message	8	Could not find schema information for the attribute &#39;name&#39;
    Message	12	Could not find schema information for the attribute &#39;name&#39;
    Message	5	Could not find schema information for the attribute &#39;serializeAs&#39;
    Message	15	Could not find schema information for the element &#39;CCP_Utility.Settings1&#39;
    Message	2	Could not find schema information for the element &#39;CCP_Utility.Properties.Settings&#39;
    Message	3	Could not find schema information for the element &#39;setting&#39;
    Message	1	Could not find schema information for the element &#39;userSettings&#39;
    Message	6	Could not find schema information for the element &#39;value&#39;

What do I have to change in the code to fix this issue? Where can I edit what&#39;s in `CCP_Utility.Settings1` and `CCP_Utility.Properties.Settings` ?

Here&#39;s the `app.config` code: 

&lt;!-- language: xml --&gt;

    &lt;configuration&gt;
    &lt;configSections&gt;
        &lt;sectionGroup name=&quot;userSettings&quot; type=&quot;System.Configuration.UserSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&quot; &gt;
            &lt;section name=&quot;CCP_Utility.Properties.Settings&quot; type=&quot;System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&quot; allowExeDefinition=&quot;MachineToLocalUser&quot; requirePermission=&quot;false&quot; /&gt;
            &lt;section name=&quot;CCP_Utility.Settings1&quot; type=&quot;System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&quot; allowExeDefinition=&quot;MachineToLocalUser&quot; requirePermission=&quot;false&quot; /&gt;
        &lt;/sectionGroup&gt;
      &lt;/configSections&gt;
    &lt;userSettings&gt;
      &lt;CCP_Utility.Properties.Settings&gt;
        &lt;setting name=&quot;SourceDir&quot; serializeAs=&quot;String&quot;&gt;
          &lt;value /&gt;
        &lt;/setting&gt;
        &lt;setting name=&quot;TargetDir&quot; serializeAs=&quot;String&quot;&gt;
          &lt;value /&gt;
        &lt;/setting&gt;
        &lt;setting name=&quot;CorpID&quot; serializeAs=&quot;String&quot;&gt;
          &lt;value /&gt;
        &lt;/setting&gt;
      &lt;/CCP_Utility.Properties.Settings&gt;
        &lt;CCP_Utility.Settings1&gt;
            &lt;setting name=&quot;sourceDir&quot; serializeAs=&quot;String&quot;&gt;
                &lt;value /&gt;
            &lt;/setting&gt;
            &lt;setting name=&quot;targetDir&quot; serializeAs=&quot;String&quot;&gt;
                &lt;value /&gt;
            &lt;/setting&gt;
        &lt;/CCP_Utility.Settings1&gt;
    
       &lt;/userSettings&gt;
    &lt;/configuration&gt;



How to fix Error: &quot;Could not find schema information for the attribute/element&quot; by creating schema

MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations. If your read to write(insert|update) ratio is less than 15% its better to use MyISAM.


InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data. It supports transaction and fault tolerance

above differences is correct between MyISAM and InnobDB? **please guide if any other limitations are there for MYISAM and InnobDB. when should i use MyiSAM or when Innodb?** Thank you!

When to use MyISAM and InnoDB?

I&#39;d like to know how to design a monthly subscription software system in a database.
These systems are broadly used all over internet, though I can&#39;t find lots of stuff about database design.

In my case, these elements (and maybe some others I forgot) have to be included:

 - Client
 - Plan (like &#39;basic&#39;/&#39;premium&#39;). Each plan has a monthly price, and an amount of credits (for instance: basic plan provides 30 credits per month and premium plans unlimited credits).
 - Credits are a virtual money that is spent within the application.
 - Subscription/Unsubscriptions
 - Payments (Note that the price actually paid could be different from the base price of the plan because of discount, etc.)
 - ... ?

In addition of database design, there could be triggers that need to be set to do this (?).

**My pain points:**
 
 - I can&#39;t see in a general manner what is the global design of this
 - Which one should be a row in the DB: the month_susbscrition (ie 1 row per client per month) or the subscription itself (ie 1 row per client)?
 - How would you handle the automatic renewal of the monthly subscription?
 - How would you handle payments designing if you foresee to use services like Paypal to handle automatic monthly payments?

**Note**

I voluntarily don&#39;t expose my needs in details because that way, the debate can stay generic and can be more useful to other people.

Thanks for help.

Good practices for designing monthly subscription system in database

I have a MySQL database and I am trying to find a way to export its structure only, without the auto increment values. `mysqldump --no-data` would almost do the job, but it keeps the auto_increment values. Is there any way to do it without using PHPMyAdmin (that I know it can do it)?

mysqldump - Export structure only without autoincrement

What is the purpose of the `ConcurrencyStamp` column in the `AspNetUsers` table in the new ASP.NET MVC 6 identity?

This is the database schema of the `AspNetUsers` table:

[![enter image description here][1]][1]

It is also there in the `AspNetRoles` table:

[![enter image description here][2]][2]

As I remember it wasn&#39;t there in the ASP.NET MVC 5 identity.

What I&#39;ve noticed so far is that it seems to have GUID values as it is defined with the following code:

    /// &lt;summary&gt;
    /// A random value that must change whenever a user is persisted to the store
    /// &lt;/summary&gt;
    public virtual string ConcurrencyStamp { get; set; } = Guid.NewGuid().ToString();

But this documentation is not sufficient for me to understand in which situations it is used.


  [1]: http://i.stack.imgur.com/Zr319.png
  [2]: http://i.stack.imgur.com/6Lbwx.png

What is the purpose of the ConcurrencyStamp column in the AspNetUsers table in the new ASP.NET MVC 6 identity?

How do I instruct mysqldump to backup with column names in insert statements?  
In my case I didn’t a normal back up with insert sql’s resulting in  

    LOCK TABLES `users` WRITE;
    /*!40000 ALTER TABLE `users` 
    INSERT INTO `users` VALUES (1

structure.  

Now I went ahead and **removed a column** from the schema in users. After this when I run the backup sql’s I get a **column number mismatch error**.  

To fix this how do I go about instructing mysqldump to write column names too? 
Here is how I do it now

    mysqldump --host=${dbserver} --user=${dbusername} --password=${dbpassword} \
              --no-create-db --no-create-info --extended-insert --single-transaction \
              --compress tablename  

On a broader level, what’s the best practice to manage these schema changes? 




Mysqldump: create column names for inserts when backing up

I want to find all column names in all tables **in all databases**. Is there a query that can do that for me?

How to find column names for all tables in all databases in SQL Server

I have a PostgreSQL database for my Rails application. In the schema named &#39;public&#39; the main Rails models tables are stored etc. I have created a &#39;discogs&#39; schema which will have tables with names that are sometimes the same as in the &#39;public&#39; schema - which is one of the reasons that I&#39;m using schemas to organize this.

How would I setup models from the &#39;discogs&#39; schema in my app? I will be using Sunspot to let Solr index these models as well. I&#39;m unsure of how you would do this.

Using multiple PostgreSQL schemas with Rails models

According to [this blog post](http://cpratt.co/migrating-production-database-with-entity-framework-code-first/) most companies using EF Migrations are supposedly not updating the database schema of production databases with EF migrations. Instead the blog post&#39;s author recommends to use Schema update scripts as part of the deployment process. 

I&#39;ve used Schema update scripts for a few years now and while they work, I was planning to use EF migrations instead in the future for the following reasons: 

- Faster deployment, less downtime
- A simpler deployment procedure
- Much easier migration of existing data than it would be possible with T-SQL
- A more comprehensible syntax of the changes waiting to be applied (DbMigration class with clean C# syntax vs. clunky T-SQL Migration script in a traditional environment).
- There is an easy and fast downgrade path to the old db schema if the deployment of the new software version should fail

One reason I can think of that would prohibit the use of EF to migrate a production DB would be if the DB schema was only altered by the DBAs as opposed to the Developers. However, I am both DBA and Developer, so this does not matter in my case.

So, what are the risks of updating a production database using EF?

Edit: I would like to add that, as solomon8718 already suggested, I am always pulling a fresh copy of the production database to my staging server and test the EF Migrations to be applied on the staging server before applying them to a production server. IMO this is essential for any schema update to a production system, whether I&#39;m using EF migrations or not. 




Is it OK to update a production database with EF migrations?

I am working on a PHP application that intends to ease company workflow and project management, let&#39;s say something like [Basecamp][1] and [GoPlan][2].

I am not sure on what the best approach is, database-wise. Should I use a single database and add client-specific columns to each of the tables, or should I create a database for each new client? An important factor is automation: I want it to be dead simple to create a new client (and perhaps opening the possibility to signing up for yourself).

Possible cons I can think of using one database:

  - Lack of extensibility
  - Security problems (although bugs *shouldn&#39;t be there in the first place*)
  

What are your thoughts on this? Do you have any ideas what solution the above companies are most likely to have chosen? 


  [1]: http://www.basecamphq.com/
  [2]: http://goplan.org

Should I use a single or multiple database setup for a multi-client application?

Is there any way to copy database structure without data in MySQL, so the new database will be the same as it is copied from, but with empty tables.

After getting some suggestions I tried the command, but I am getting syntax error, my `username = root` and `password = nothing`. I guess the default one. I am trying following command, 

    mysqldump -u root -p -d xyz_db | mysql -u root -p -Dnew_db

what I am missing or misplacing in command?

All Database Schema Solutions on Gang of Coders

Total of 23 Database Schema Solutions