Which schema is better for a shopping project? I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized properties for different products.

I'm looking for a reasonable solution for handling different types of products like cell phones and air conditioners. For example a cell phone has a property called CPU whereas an air condition doesn't have that property. Also an air condition has a property called BTU whereas a cell phone doesn't have a property like BTU.

The thing I want to do is a classic category based product system. Users will be able to create categories that contains different fields like CPU of BTU and when they want to create a product, they will be able to enter values for these fields to that product.

The SQL world, I see that many projects use a schema like this one:

products -> (id, name, brand_id, description, category_id)
categories -> (id, name)
category_fields -> (id, category_id, field_name, field_type)
product_fields -> (product_id, category_field_id, value)

However I wonder that if there is a better solution for this problem. There are two different solutions came into my mind.

products -> (id, name, brand_id, description, category_id, (json) category_values)
categories -> (id, name)
category_fields -> (id, category_id, field_name, field_type)

As you may noticed there is a column called category_values in products table. This column is JSON type (which Postgresql has) and I will index the keys that I need to search.

The other schema:

products -> (id, name, brand_id, description, category_table)
products_cellphones -> (product_id, memory, cpu, gps ...)
products_...

In this schema, when a user creates a category the system will create a new table with its fields on database in runtime. And I will create a view that generates the sql selects on the fly and executes them.

Which way should I choice, or do you have any other solutions?

You are much better off using the EAV (entity-attribute-value) approach for a product feature catalog.  The approach is transportable to any SQL platform as it doesn't rely on a particular feature of Postgresql (JSON columns).

All of the usual arguments against EAV don't apply to a product feature catalog, since product feature values are generally only regurgitated into a list or worst case into a comparison table.

Using a JSON column type takes your ability to enforce any data constraints out of the database and forces it into your application logic.  Also, using one attributes table for every category has the following disadvantages:

It doesn't scale well if you eventually have hundreds of categories (or more).
If you change the allowable attributes on a category you have to change a table definition instead of just adding or removing rows in category_fields
You may still end up with sparsely populated tables if the product has many potential features only a small subset of which are known.

It is not especially difficult to retrieve data about a product with specific features.  It is arguably easier to create a dynamic SQL using the EAV model than it would be using the table-per-category model.  In table-per-category, you need reflection (or your JSON) to find out what the feature column names are.  Then you can build a list of items for a where clause.  In the EAV model, the WHERE X AND Y AND Z becomes INNER JOIN X INNER JOIN Y INNER JOIN Z, so the query is a little more complicated, but the logic to build the query is still totally table-driven and it will be more than scalable enough if you have the proper indexes built.

There are a lot of reasons not to use EAV as a general approach.  Those reasons don't apply to a product feature catalog so there is nothing wrong with EAV in this specific application.


How far to go when Validating a SQL Service Pack install? When applying a Service Pack to a production SQL server, I usually have a planned downtime window of around 30 minutes.

Having been bitten before, and discovering that a required precondition like a patch is not in place, that can add several minutes to your downtime window (and maybe taking you over the window, or forcing a reschedule).

Now when the update is in planning stage, I move the Service Pack to the server and test it through "Check Files in Use" or "Ready to update".  I cancel the update at that point, and feel as confident as I can that during the update, I will not run over the scheduled downtime window.

You can cancel from either screen, but as the "Update" button on "Ready to update" is in the same location as the "Next >" button on "Check Files in Use", an accidental double click could accidentally start an update, while validating.

My question:

Should I stop validation at "Check Files in Use" or "Ready to update"? Have I validated everything I can by the end of "Check Files in Use"?  Does going to "Ready to update" add value to the validation?

The ideal situation is to have a server that, in terms of OS and other software, exactly matches the production environment, upon which you can perform the update first. That way you know everything that is required.

This would preferably be a VM that you could snapshot, so if you encounter and fix a problem you can revert to the snapshot and start again with your updated procedure. Repeat this until the upgrade procedure works and you know the reboot requirements and such, then plan to repeat the process in production.

One of your development/testing VMs might be ideal for this if you have them (i.e. if your dev/test/release process isn't just "smash code together and throw directly into production"!). This way you are essentially treating the service pack the same as you would one of your own bug fix or feature releases meaning you can perform a full regression test on your application after the service pack is applied to the test environment (to make sure MS haven't introduced any bugs or changes to undefined behaviour that your application depends upon - or that they haven't fixed a bug that your code is depending on!).

Obviously this "ideal" could be more time consuming than other options...


Data Join with offset I have below situation:

Table1

Field1  Field2
123     test1
234     test2

Table2

Field1  Field2
12345   test2
23245   test3
123945  test6

I want to join these two tables i.e. Table1 and Table2 where Field1 of table1 matches with first three characters of field1 of table2.

So results here should be:

Table1-field1 Table1-field2 Table2-field1 Table2-field2
123           Test1         12345         Test2

To do this, I did the following:

CREATED the two TABLEs

CREATE TABLE table1
(
  t1_field1 VARCHAR (20),
  t1_field2 VARCHAR (20)
);

CREATE TABLE table2
(
  t2_field1 VARCHAR (20),
  t2_field2 VARCHAR (20)
);

And populated it with data:

INSERT INTO table1 VALUES ('123', 'test1');
INSERT INTO table1 VALUES ('234', 'test2');

INSERT INTO table2 VALUES ('12345', 'test2');
INSERT INTO table2 VALUES ('23245', 'test3');
INSERT INTO table2 VALUES ('123945', 'test6');

And then ran the following SQL:

SELECT t1.t1_field1, t1.t1_field2, t2.t2_field1, t2.t2_field2
FROM table1 t1, table2 t2
WHERE CAST(t1.t1_field1 AS VARCHAR(3)) = CAST(t2.t2_field1 AS VARCHAR(3));

Giving the result:

 t1_field1 | t1_field2 | t2_field1 | t2_field2
-----------+-----------+-----------+-----------
       123 | test1     |     12345 | test2
       123 | test1     |    123945 | test6
(2 rows)

This SQL works if t1_field1 and t2_field1 are both VARCHARs or INTEGERs (and if one is VARCHAR and one is INTEGER).

This SQL works if they are both VARCHARs -

SELECT t1.t1_field1, t1.t1_field2, t2.t2_field1, t2.t2_field2
FROM table1 t1, table2 t2
WHERE LEFT(t1.t1_field1, 3) = LEFT(t2.t2_field1, 3);

I don't have S4 HANA to hand, but this is standard SQL and according to the documentation I Googled, it should work for Sybase which might be related?


Why intended integrity constraint cannot be enforced? What does the following integrity constraint try to enforce? (integrity constraint is primary key and check in this case but I couldn't understand what I should really write).

Explain why the intended integrity constraint cannot be enforced?

CREATE TABLE Sailors
( sid INTEGER,
  sname CHAR(10),
  rating INTEGER,
  age REAL,
  PRIMARY KEY (sid),
  CHECK
    ( (SELECT COUNT (S.sid) FROM Sailors S)
    + (SELECT COUNT (B.bid) FROM Boats B) < 100 )
) ;

We are studying SQL at school.

Sabancı bilgisayara selamlar @yguney

(Note: you have declared 2 constraints on this table, a PRIMARY KEY constraint and a CHECK constraint. The answer is about the second one.)
The CHECK constraint is trying to enforce that:

the number of rows in both tables should be less than 100

So, it should - if it worked - allow one table have 50 and the other 49 rows, or 1 and 98 or 5 and 10 or ... but none should have 100 or more. If one has 90, the other should have no more than 9 and any transaction trying to insert more rows, should be denied (raising a constraint error).
At least that's what it looks like it's trying to do. Now the problem or rather the two problems of the constraint:

A CHECK constraint is a row constraint, in almost all implementations. So it is checked for each row inserted or updated. This is an implementation issue and no current DBMS has implemented constraints that involve subqueries (except Firebird which claims to do so, but I haven't tested it).
There are a few DBMS that allow functions to be used in CHECK constraints (and the functions could contain subqueries), so the limitation can be "worked around" but that leads to all kinds of concurrency issues and the problem is not really solved. The advice, even in those DBMS, is not to have subqueries in CHECK constraints.
In my opinion, a constraint that involves many rows of the table (all of them in this case), should be an ASSERTION, not a CHECK constraint.

Second and more serious is that a CHECK constraint is a constraint on one table. It is not an ASSERTION (those are constraints that can span across multiple tables).
So even if there was an implementation that allowed CHECK constraints with subqueries, the result would be what one would expect. One way to understand why is that the constraint would only be checked when a row in Sailors would be inserted or updated.  This would lead to these problematic situations:
We insert 200 rows in the Boats table. The constraint would not be checked so that would be allowed. Then we try to insert a row in Sailors. the constraint is checked and the row is forbidden. But have we managed to enforce our intention? No, because the total of the two tables is 200 rows, which is more than 100.
We insert 5 rows in Sailors. No problem so far. Then 200 rows in the Boats table. The constraint would again, as before, not be checked so that would be allowed. Then we try to update a row in Sailors. The constraint is checked and we get an error. The UPDATE fails. But we still have 205 rows in total, so why should the update fail?

As a conclusion, there are two issues with this constraint. First, that current implementations do not allow subqueries in CHECK constraints and second that it should be an ASSERTION and not a CHECK constraint, as it involves more than 1 table.
And note that none DBMS has implemented ASSERTION so far (at least those SQL based, there are a few based on Tutorial D that have.)


Script to create multiple databases I need to create 90 databases. Something like this. Do you have any idea?

My script:

declare @db_name nvarchar(100)
set @db_name = 'name';

CREATE DATABASE @db_name
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'@db_name', FILENAME = N'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\@db_name.mdf' , SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'@db_name_log', FILENAME = N'L:\MSSQL11.MSSQLSERVER\MSSQL\Log\@db_name_log.ldf' , SIZE = 512KB , FILEGROWTH = 256KB )
GO
ALTER DATABASE @db_name SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE @db_name SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE @db_name SET ANSI_NULLS OFF
GO
ALTER DATABASE @db_name SET ANSI_PADDING OFF
GO
ALTER DATABASE @db_name SET ANSI_WARNINGS OFF
GO
ALTER DATABASE @db_name SET ARITHABORT OFF
GO
ALTER DATABASE @db_name SET AUTO_CLOSE OFF
GO
ALTER DATABASE @db_name SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE @db_name SET AUTO_SHRINK OFF
GO
ALTER DATABASE @db_name SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE @db_name SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE @db_name SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE @db_name SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE @db_name SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE @db_name SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE @db_name SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE @db_name SET  DISABLE_BROKER
GO
ALTER DATABASE @db_name SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE @db_name SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE @db_name SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE @db_name SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE @db_name SET  READ_WRITE
GO
ALTER DATABASE @db_name SET RECOVERY FULL
GO
ALTER DATABASE @db_name SET  MULTI_USER
GO
ALTER DATABASE @db_name SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE @db_name SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE @db_name
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE @db_name MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

This piece of SQL can be used as a template:

DECLARE @sql nvarchar(max);
DECLARE @query nvarchar(max);
DECLARE @params nvarchar(500);

SET @params = N'@SQL nvarchar(max), @db_name sysname';
SET @sql= N'SET @SQL=REPLACE(@SQL, ''db_name'', @db_name); print @sql;'
--SET @sql = N'SET @SQL=REPLACE(@SQL, ''db_name'', @db_name); EXEC sp_executesql @SQL;';
SET @query = N'
CREATE DATABASE [db_name]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N''db_name'', FILENAME = N''D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_name.mdf'' , SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N''db_name_log'', FILENAME = N''L:\MSSQL11.MSSQLSERVER\MSSQL\Log\@db_name_log.ldf'' , SIZE = 512KB , FILEGROWTH = 256KB )
GO
ALTER DATABASE @db_name SET COMPATIBILITY_LEVEL = 110
GO
'

@sql is the piece of SQL that will be executed once for each db
@params is the parameter used by @sql
@query is a string with the piece of SQL use to create a new DB

While playing with @query, be really careful with single and double quotes.

First SET @sql... only prints the query. The second one --SET @sql... will execute it if uncommented.

Then this template can be followed by one more more of the below lines and the whole batch can be executed:

-- template
--
...
EXEC sp_executesql @sql, @params, @query = @query, @db_name = 'xxx';
EXEC sp_executesql @sql, @params, @query = @query, @db_name = 'yyy';
EXEC sp_executesql @sql, @params, @query = @query, @db_name = 'zzz';
...

If db_names are already in a table, this query will output all the EXEC lines:

SELECT N'EXEC sp_executesql @sql, @params, @query = @query, @db_name = '''+col_db_name+''';;'
FROM db_list;

You can then copy some or all of them after the template and execute the whole batch.

Or without a table, this can simply be used:

SELECT N'EXEC sp_executesql @sql, @params, @query = @query, @db_name = '''+col_db_name+''';'
FROM (values
    ('xxx')
    , ('yyy')
    , ('zzz')
    , (...)
    , ...
) as dblist(col_db_name);


MySql - partition by range and unique key i'm trying to create the following table:

CREATE TABLE `s_relations_with_partition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_persona_id` int(11) NOT NULL,
  `relation_type` int(11) NOT NULL,
  `message_id` int(11) DEFAULT NULL,
  `reply_to_message_id` int(11) DEFAULT NULL,
  `reshare_of_message_id` int(11) DEFAULT NULL,
  `target_object_id` int(11) DEFAULT NULL,
  `target_persona_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `sent_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`sent_at`),
  UNIQUE KEY `unique_target_persona` (`source_persona_id`,`relation_type`,`message_id`,`target_persona_id`),
  UNIQUE KEY `unique_target_object` (`source_persona_id`,`relation_type`,`message_id`,`target_object_id`),
  KEY `message_id_index` (`message_id`),
  KEY `reshare_of_message_id_index` (`reshare_of_message_id`),
  KEY `reply_to_message_id_index` (`reply_to_message_id`),
  KEY `source_and_target_object_index` (`source_persona_id`,`target_object_id`),
  KEY `source_target_persona_index` (`source_persona_id`,`target_persona_id`),
  KEY `target_persona_relation_type_message_id_index` (`target_persona_id`,`relation_type`,`message_id`),
  KEY `sent_at_index` (`sent_at`),
  KEY `source_persona_sent_at_index` (`source_persona_id`,`sent_at`),
  KEY `target_persona_sent_at_index` (`target_persona_id`,`sent_at`),
  KEY `target_object_sent_at_index` (`target_object_id`,`sent_at`)
) ENGINE=InnoDB
PARTITION BY RANGE (sent_at) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-02-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-03-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-04-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-05-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-06-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-08-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-09-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-10-01 00:00:00') ),
    PARTITION p10 VALUES LESS THAN (MAXVALUE)
);

and i get the following error:

A UNIQUE INDEX must include all columns in the table's partitioning function

adding sent_at (unix timestamp) to the unique index is not possible.

any ideas on how i can implement a date range partition on that table?

Then error message itself explains the problem. Please, read the manual,
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-k...:

"The rule is : All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
In simple words, every unique key on the table must use every column in the table's
partitioning expression."


MaxDOP Limits SQL Server 2016 Standard I have SQL 2016 Standard Edition instance with 40 logical processors in 2 nodes. (2 physical processors x 20 cores = 40 cores total)
Per Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server I want to set MaxDOP to 8

Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

BUT per max degree of parallelism Option I can only set it 4, but it looks like that applies to 2008 and I am at 2016

The maximum value for the degree of parallelism setting is controlled by the edition of SQL Server,

In reality, I changed it to 8, SQL did not complain and the value stayed at 8.  I set it back to 0 while continuing to research.
My concern is that if I set to 8, I might really be setting it to 4.  Before I make any changes I want to know that 8 = 8. I have searched and not found anything else saying that MaxDOP limits in newer versions of SQL is edition based.
Is the MaxDOP limit in SQL 2016+ Edition based?
EDIT Soft NUMA is currently setting to 4 nodes at startup. still learning but If I understand correctly I think this means a single node with 10 logic processors is the most that could be applied to any one statement (query) so setting the MaxDOP to 8 in order to preserver two processors is probably not worth the effort, without some existing problem to solve.
EDIT2 Nodes do not limit parallelism

Place threads on multiple nodes;
This is the most common decision made by SQL Server.  The decision spreads the workers across multiple nodes as required.  The design is similar to full except the starting position is based on the saved, next node, global enumerator.
source

SQL Server 2016 Enterprise Edition allows you to set maxdop to any value up to 64.  However, SQL Server 2016 Standard Edition has limits around parallelism, per this docs page:

Partitioned table parallelism is not supported.
Standard Edition is not NUMA Aware and does not support Large Page Memory and Buffer Array Allocations
The degrees of process parallelism (DOP) for an index build is limited to 2 DOP for the Standard Edition
Limited to lesser of 4 sockets or 24 cores


Querying XML with Unicode data I'm trying to select attribute value from XML column using XPath with a parameter that can be a Unicode string. Could you please tell me how to make these two examples work?

create table #test
(
    Document xml
);

insert into #test (Document) values
('<FormDocument xmlns="http://mynamespace.org/Schemas">
  <Fields>
    <Field Id="a" Value="qwerty" />
    <Field Id="ą" Value="123" />
  </Fields>
</FormDocument>')

-- works
declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output,N'@p0 nvarchar(4000)',
N'select t.Document.value(''declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value'', ''nvarchar(255)'') as Col1
from #test t',@p0=N'a'
go

-- doesn't work
declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output,N'@p0 nvarchar(4000)',
N'select t.Document.value(''declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value'', ''nvarchar(255)'') as Col1
from #test t',@p0=N'ą'
go

-- works
declare @p0 nvarchar(4000) = N'a'
select t.Document.value('declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value', 'nvarchar(255)') as Col1
from #test t
go

-- doesn't work
declare @p0 nvarchar(4000) = N'ą'
select t.Document.value('declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value', 'nvarchar(255)') as Col1
from #test t
go

drop table #test;

Add the N prefix to the INSERT statement to preserve the XML as Unicode, eg

    insert into #test (Document) values
(N'<FormDocument xmlns="http://mynamespace.org/Schemas">
  <Fields>
    <Field Id="a" Value="qwerty" />
    <Field Id="ą" Value="123" />
  </Fields>
</FormDocument>')


How to synchronize a Windows database for Linux? I have some database (PostgreSQL 9.5) on a Windows server, and I would like this data to be synchronized to a Linux server, that is connected in an application.

Is it possible to synchronize?

A single time syncron can be done by simply dumping the database on the Windows, and piping into a command line postgresql client to your Linux machine:

pg_dump --host=your.windows.host --port=5432 --user=you|\
psql --host=your.linux.host --port=5432 --user=you

If you are thinking on some type of replication cluster, then it is hard to answer here, but you could read the docs.

There is no major OS-dependence in the storage areas and protocols in PostgreSQL, but these are enough to make the cross-os physical replication impossible (ref). From PostgreSQL 10, you could use logical replication, or use some third party solution.


Data archival strategy that handles schema changes? I am working with a legacy application that has about ten years of customer data. A majority of this data is not used in day-to-day operations but there is a business requirement to have the data available for the customer until his retirement from the system.

We are exploring archiving the data to a copy of the existing database and then purging the records from production past a certain point in time.

My concern is that the database undergoes a substantial schema change every quarter due to development efforts.

If I were to archive a mirror copy of the data, would I also need to apply every single change script that goes against production?

Are there any alternative strategies? It seems like no matter what form of storage you choose (i.e. database, flat files, xml), you will always need some way of mapping older schemas to newer ones.

You need to define your requirements more specifically before even thinking about a solution:

Why is archiving necessary? It sounds like the system already handles old data, so what is the business need to separate out this data? Performance?
Is archive data a read-only snapshot, or are historical data changes possible? If changes are possible, which types of changes will be supported (insert, update, delete, or some combination of those)?
Is the database multi-tenant, and if so, do you require the ability for each tenant to be able to archive at different points in time?
Does your application need to run with the archive data as a data source? I'm assuming yes since you mention synchronizing schema changes.
What is the minimum version/edition of the DBMS you need to support? This will determine which features are available to use in your strategy.
How much time do you have to implement archiving? Archiving is a very low-level design issue that ideally should be baked in right from the start; adding it on later can potentially take a significant amount of time to redesign and implement.

Now having said all that, one thing I would advise you, having done it here (SQL Server), is this: avoid multiple databases if at all possible, particularly if you require the ability to edit historical data.

While I can't give away our IP, I'll tell you that the process involved with shuffling data from the "live" database to the "archive" database is extremely complex if you take a dynamic approach like we did for ~700 tables. Depending on the state of your database schema, that kind of thing may not even be possible to accomplish, or result in data discrepancies you don't expect. If you don't have very many tables (< 200) and the schema is in rough shape, quite honestly, do not take a dynamic approach, or wait until it's cleaned up to a decent state. With a large number of tables and a rough schema, multiple databases is not a viable solution.
As you mentioned, you do have to run update scripts against multiple databases, and you have to keep track of all the databases somehow. It's really easy for things to become desynchronized, i.e., an archive database is moved to a different server or instance and your configuration database or table holds the old information. The choice is either to always synchronize the schema, or write applications to always be backwards compatible. (Hint: it's far easier to synchronize the schema.)

While I certainly don't recommend multiple databases, it is a possible solution depending on your requirements.


How to permanently enable Read Access for MongoDB Secondary Node? Upon trying to connect mongodb secondary node from application :

08:11:2018 16:01:00.552 - error: A hook (orm) failed to load!
08:11:2018 16:01:00.558 - error:  Error: Failed to connect to MongoDB.  Are you sure your configured Mongo instance is running?
 Error details:
 MongoError: no valid seed servers in list

I can access the secondary DB by manually entering the command rs.slaveOk(); , but the same wont suffice as I need the application to have dynamic read access everytime the DB is bieng queried

08:11:2018 16:01:00.552 - error: A hook (orm) failed to load!
  08:11:2018 16:01:00.558 - error: Error: Failed to connect to MongoDB.
  Are you sure your configured Mongo instance is running? Error details:
  MongoError: no valid seed servers in list

As per your it seems like that your mongod server has not started.

As per MongoDB documentation here All read preference modes except primary may return stale data because secondaries replicate operations from the primary with some delay. [1] Ensure that your application can tolerate stale data if you choose to use a non-primary mode.

MongoDB drivers support five read preference modes.

Read Preference Mode    Description

primary                 Default mode. All operations read from the current replica set primary.
                        Multi-document transactions that contain read operations must use read preference primary.

                        All operations in a given transaction must route to the same member.

primaryPreferred        In most situations, operations read from the primary but if it is unavailable, operations read from secondary members.

secondary               All operations read from the secondary members of the replica set.

secondaryPreferred      In most situations, operations read from secondary members but if no secondary members are available, operations read from the primary.

nearest                Operations read from member of the replica set with the least network latency, irrespective of the member’s type.

As MongoDB documented setSlaveOk()  This allows the current connection to allow read operations to run on secondary members. See the readPref() method for more fine-grained control over read preference in the mongo shell.

db.getMongo().setSlaveOk()

For further your ref here, here and here


SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes Attempting to install Drupal 7.16 (also attempted 7.12) onto my local machine, I get the following error at the database stage.

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Looking at the Drupal database with PHPMyAdmin, I see the "variables" table is created, together two columns; that is all that gets created prior to the error.

I can't find a single solution to this.

This is probably due to the index limitations. From the MySQL docs: CREATE INDEX Syntax

  Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.

You probably have a VARCHAR column with length more than 767 or a TEXT column.


Connect to SQL Server over Internet I want to connect to SQL Server via Internet:

I turned off the firewall
I enabled the the DMZ in the router, host address is 192.168.1.24 which is the SQL Server LAN IP
I open port in the router for both SQL Server and SQL Server Browser (1433 - 1434)
I checked the open port via (canyouseeme) website, and the result is success for 1433 and error for 1434  (I don't know the reason for the error)
I made the necessary configuration for the TCP protocols
I checked that the server is allowed remote connection

But I failed to connect to the server via Internet.

My LAN IP is 192.168.1.24
The instance is default instance
My public IP is 95.218.156.146 (for example)

When I type (192.168.1.24) in the server name text-box I succeed to connect to the server,
but I fail to connect when I type 195.218.156.146  or 95.218.156.146 ,1433

Note:

When I put the public IP in the brower then the router page appears (meaning  that the public IP is correct)
I didn't find port forwarding in my router, but I have configured the DMZ and port mapping

The recommended way to do this is to set up a VPN between the two sites.  The steps to do this vary depending on the particular firewall/router being used.  If a VPN cannot be used, the following information is provided for how to set up a connection with port mapping.  If port-mapping is used, it should only be used in conjunction with additional firewall rules to restrict what source IP addresses can route through the mapped port so that every hacker on earth doesn't have access to your SQL Server.

1. Server IP Configuration

Check your Windows Server's IP configuration by running IPCONFIG / ALL. Your results can contain both IP addresses, but at least your internal IP:
- 192.168.1.24
- 95.218.133.168

2. Check your routing

Start a traceroute with the following command: TRACERT <external IP of server>. Verify that you receive the route to your server.

  Important do the same from your LAN and from the INTERNET.
  The results should look like similar to these for the INTERNET check:

 1    <1 ms    <1 ms    <1 ms  <IP of your router>
 2    <1 ms    <1 ms    <1 ms  <IP of your ISP>
 3    <1 ms    <1 ms    <1 ms  <Another IP connecting to your company>
 4     2 ms     1 ms     1 ms  outside.yourcompany.com [<public IP of your company>]
 5     3 ms     1 ms     1 ms  dmz.yourcompany.com [<IP of DMZ component>]
 6     3 ms     1 ms     1 ms  router.yourcompany.com [<IP of router>]
 7     3 ms     1 ms     1 ms  sqlserver.yourcompany.com [<IP of server>]

  Depending on your firewall settings, you might not get past your company's firewall, but if tracert is going in the right direction, then it "knows" the way. Your firewall is just not telling anybody what your network looks like and might not be forwarding the port request to your SQL Server (See Router Configuration later on)

3. Check your SQL Server IP Configuration

In the SQL Server Configuration Manager open up the branch for SQL Server-network configuration and select your instance. (E.g. Protocols for 'MSSQLSERVER')
Right click and open up the properties. Verify that you don't have any settings that could inhibit a connection. Close the setting when you have finished.
In the right hand pane for the protocol settings verify that the TCP/IP protocol is 'Enabled'.
Now right click the TCP/IP setting and open up the properties. In the Protocol tab...
a) Verify again that Enabled is set to Yes.
b) Verify that the setting Listen All is set to Yes.
c) The screen should look like this:

Switch to the IP-Addresses tab and verify for each IPn that ...
a) Active is set to Yes
b) Enabled is set to Yes
c) IP address is your external IP address (or your internal address if you are unable to assign the external address to your SQL Server, because you only have one public IP.)
d) TCP Dynamic Ports is set to  (No value/empty)
e) TCP port is set to 1433
f) The screen should look a bit like this, but with your IP address:

An then verify the settings for the IPAll portion, by checking that ...
a) TCP Dynamic Ports is set to  (No value/empty)
b) TCP Port is set to  (No value/empty)
c) The screen should look like this:

4. Check your router configuration

Your public IP is not the server's, it's the router's. If your server is behind the router then you have to ensure your router is forwarding the request to your SQL Server. This configuration setting can vary from router to router. Here a few examples for configuring routers:
 - Port Forwarding (Zyxel)
 - Setting up static port sharing (Fritz AVM)
 - How do I configure Port Forwarding on my router? (D-Link)

With the provided tips and tricks you should be able to set your SQL Server to accept connections from the Internet.

  DISLCAIMER: It is not really recommended to allow connections via the Internet to a database in your LAN. You risk being attacked.


Dropping a constraint (index) on a column How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had dependencies on the column (which turned out to be indexes).

I was able to easily get around this locally by right clicking the index and scripting a drop, but I need to roll this out on other servers where I won’t have access to the index name.

Is there a way I can make a script that will drop any index, let me make that datatype change on the column then read the index? Thanks!

You can use sys.indexes view to get indexes. You can join this table to sys.tables, sys.columns and sys.index_column to get informations to create a dynamic drop index. You can use this simple select to generate your drop index. You can use the where clause to filter the tables and the columns. If you want to change Table1.Column1 you must use those name to filter the select to get the right delete statement

use [testdb]
go
declare @sqlDropIndex NVARCHAR(1000)

select @sqlDropIndex = 'DROP INDEX ' + idx.name + ' ON ' + tbl.name
from sys.indexes idx inner join
        sys.tables tbl on idx.object_id = tbl.object_id inner join
        sys.index_columns idxCol on idx.index_id = idxCol.index_id inner join
        sys.columns col on idxCol.column_id = col.column_id
where idx.type <> 0 and
        tbl.name = 'MyTableName' and
        col.name = 'MyColumnName'
group by idx.name, tbl.name
order by idx.name desc

print @sqlDropIndex
--exec sp_executeSql @sqlDropIndex
go

Hope this will help you


Can't accept pgbouncer 6432 port on PostgreSQL server pgbouncer server (440a:90a0:302:4525:lda0:1:2:3)

pgbouncer.ini

[databases]
app_db = host=pg_server port=5432 dbname=app_db

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser

Test connection:

-bash-4.2$ psql -p 6432 -h pg_server -U postgres app_db
psql: could not connect to server: Connection refused
    Is the server running on host "pg_server" (340b:c210:402:4545:3a1a:1:2:3) and accepting
    TCP/IP connections on port 6432?

Network ports:

sudo lsof -i -P -n | grep LISTEN | grep 6432
pgbouncer 160039 postgres    7u  IPv4 39990293      0t0  TCP 127.0.0.1:6432 (LISTEN)

postgresql server (340b:c210:402:4545:3a1a:1:2:3)

Telnet pgbouncer server with 6432 port:

$ telnet -6 440a:90a0:302:4525:lda0:1:2:3 6432
Trying 440a:90a0:302:4525:lda0:1:2:3...
telnet: connect to address 440a:90a0:302:4525:lda0:1:2:3: Connection refused

Try to connect to pgbouncer server 5432 port(postgresql server installed on pgbouncer server):

telnet -6 440a:90a0:302:4525:lda0:1:2:3 5432
Trying 440a:90a0:302:4525:lda0:1:2:3...
Connected to 440a:90a0:302:4525:lda0:1:2:3.
Escape character is '^]'.

Why can't accept 6432 port on postgresql server(340b:c210:402:4545:3a1a:1:2:3)?

Your PgBouncer is not listening on IPv6 address because this line tells it so:

listen_addr = 127.0.0.1

You should change it to something which includes IPv6 addresses, like

listen_addr = 127.0.0.1, ::1

or maybe (to listen on all interfaces):

listen_addr = *

Side note: you should probably not publish your real addresses, even if  "440a:90a0:302:4525:lda0:1:2:3" is not a valid ipv6 address :-)


Does ordering by auto-incrementing PK ensure chronological order? Is it safe to assume that ordering by an auto-incrementing PK in Postgres will order the records chronologically, from a creation standpoint? I have a many to many relationship, from which I don't need to track anything else except the relationship itself and some form of order of creation. I am trying to decide whether I need to incorporate a timestamp column for this, or whether I can reutilize the existing auto-incrementing PK column for that same purpose.

Assuming by "auto-incrementing" you mean the Postgres SERIAL pseudo-type, the short answer is "not always".

SERIAL columns are implemented using standard SQL sequences, which might generate out-of-order values when used by multiple concurrent sessions if the CACHE parameter is set to something more than 1. The manual states this:

  [A]lthough multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially.

Leaving CACHE value at 1 might hurt performance in an environment with high concurrency, so you will have to choose what is more important to you.

In my view, trying to repurpose SERIAL for tracking order of creation of records is not a good idea, because it potentially creates a maintenance problem: since the sequence's only promise is to provide some unique incrementing value to each nextval request, relying on its behaviour under one circumstance might cause your application to behave unpredictably when the situation changes, e.g. the sequence implementation details are modified in a future Postgres version, or changes in your workload require you to increase the CACHE setting. Using a proper timestamp for orderging is both safer and semantically clearer.


Data Migration from SQL Server to PostgreSQL I need to migrate database from SQL Server to PostgreSQL. Both databases are on different servers. I have about 20 tables and millions of rows.

What are the ways to accomplish this? I there any open source tools? Can I do this using some script?

I have access to the server that runs PostgreSQL database and I have connection parameters to the SQL Server. The OS on the server that runs PostgreSQL is ubuntu.

The SQL Server is still live, and will continue to be. Time is not a problem. I didn't understood you, sorry. Neither database is in a production so I guess they can be unavailable as long as needed.

There is no "silver bullet" to complete this migration. You will need to set up your database schema in the destination, then perform an ETL process to pull the data out of SQL Server and insert it into Postgres. There will be changes required in the schema (and transformations of the data) because there isn't a 1:1 mapping of data types between the two platforms.

But that just moves the data.

Then you have to migrate your stored procedures, triggers, and other code embedded in the database. Again, there's no "here, run this script" to magically convert everything. These will require more effort to convert than the raw data.

If there is development ongoing with this system, you'll need to port each of those changes from SQL Server to Postgres. If you want a "live" migration and data is still being modified (insert, update, delete, anything that changes the state of the data), you'll need to replicate those changes to Postgres. When your cutover date arrives, you'll have to quiesce SQL Server, ensure that the last of the data and other updates have been migrated, then shut down SQL Server and update your apps to point at Postgres.

And don't forget to test before you make the final switch!


PostgreSQL equivalent of MySQL query variables? Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL:

setting variables in MySQL like

set @aintconst = -333
set @arealconst = -9.999

It seems not.
Assigning variables from SELECT queries and using those variables subsequently in my SQL like:

 select @pfID := id from platform where bios like '%INTEL%'
 select @clientID := id from client where platformID = @pfID

I'd be very grateful for pointers, especially on (2).

This is easy to do inside a PL/pgSQL function (or a DO block):

create function myfunc() returns void language plpgsql as $$
  declare
    aintconst constant int = -333;
    arealconst constant real = -9.99;
    pfid int;
    clientid int;
  begin

    select id from platform where bios like '%INTEL%' into pfid;

    select id from client where platformID = pfid into clientid;

  end $$;

You can also use GUC variables:

--set a session variable
set mycustom.var = 'value';

--use it
select * from mytable where some_column = current_setting('mycustom.var');

Or you can use a CTE with a join:

with myvars as (
  select
    -333::int as aint,
    -9.99::real as areal
)

select
  a.*
from mytable a
join myvars on true
where
  a.thing = aint


SQL Server: I need to estimate execution time of a SELECT .. INTO .. statement To take backup of a big table (using SELECT .. INTO .. ) took me almost 4 hours in a machine with 4 CPUs and 16 GB of RAM. No external application/process were accessing the table during the operation.

The table size was 220 GB and the SELECT .. INTO was a simple one (i.e. SELECT * INTO BACKUP_TABLE FROM ORIGINAL_TABLE)

This was a test environment and based on that, I need to work out an estimation of the execution time for the same operation in the production environment. The production environment has 40 CPUs and 64 of RAM.

CPUs are identical and I/O systems for both systems are the same. (i.e. disk type and disk layouts are the same).

Is it realistic to estimate the production's SELECT .. INTO .. will be complete in less than an hour, considering the production server has 10 times more processing power?

If not possible to answer this question based on the above, shall I re-run the test and collect some metrics? If yes, what those metrics should be?

Thanks in advance, for providing your thoughts on this!

On SQL Server 2008 R2, SELECT...INTO queries are not eligible for parallelism (this was introduced in SQL Server 2014).  So the increased CPU count in production will not help your overall runtime unfortunately.

You could re-run your test with a pre-created BACKUP_TABLE, and then use INSERT INTO...SELECT to see how parallelism affects the test.

Regardless of parallelism, since you're copying a 220 GB table, it's likely that your main bottleneck is disk speed.  Specifically writing all of this to the transaction log.  I'd check to make sure that you aren't going to experience file growth events (pre-grow the production log file to whatever the size of the test instance ended up at).

I'd run your test again and measure wait stats to see how much you're hitting WRITELOG waits.  You can use Paul Randal's scripts for that:

Capturing wait statistics for a period of time

Run this multiple times while the insert is running, for 30, 60, or 90 second intervals for example, to get a feel for where the bottlenecks are.

You mentioned that your test was run on a system with no other concurrent activity against this table.  The other thing that might affect your test is if the SELECT INTO query gets blocked by other processes.


SQL Server 2016 high idle CPU and queries extremly slow I have a ~10 day old installation of WinServer2012R2 and SQL Server Express 2016 for testing. I am the only user on this machine. A database with a .bak from SQL Server 2005 of ~250MB is restored without any issues. After a reboot of the machine the process "SQL Server NT - 64 Bit" uses 0% CPU.

After a couple of minutes or hours and some simple queries (no updates/inserts!) from SSMS CPU usage of "SQL Server NT - 64 Bit" suddenly jumps to ~15% and stays there, even when idle. From that point on queries which usually take less than a second suddenly take 2 minutes. During an actual query the CPU usage does NOT increase. The server becomes virtually unusable in this state.

Only connecting SQL Server Profiler then takes >30sec. Beside my own queries I see only very few queries from SQLServerCEIP / SQLTELEMETRY, ~3 per minute.

Restarting SQL-Server does not solve it. CPU usage jumps right back to ~15%. Even after hours SQL-Server does not recover. Only rebooting the entire machine solves the issue.

As this is an "out of the box" installation, there is only a small database, virtually no queries, only me as a user and probably no locks, the many articles on regular SQL-Server performance issues talk about lot of things that don't really apply here. It seems as SQL-Server exclusively wants to concentrate on some internal task.

This is a virtual machine with 2GB RAM and dual Xeon at 2GHz. I also have VS2016 on it and it's really fast. No antivirus, not even Windows Defender. Already late here. I will try the sp_whoisactive tomorrow. I really wonder WHAT SQL-Server IS doing there... On the previous machine with 1 GB the same DB ran under SQLServer2005 for 10 years without issues...

I am not an SQL-Profiler expert. Where should I start looking?

Since your CPU utilization is low (and you have a great CPU set), I would check for memory pressure first. With SQL Express 2016 you are limited to 1410 MB of memory, but your VM only has 2GB. Give your VM 1GB more of memory and see if that helps. You can also check your SQL Server log if it is paging memory to file.

If that does not work, try setting the database setting LEGACY_CARDINALITY_ESTIMATION to ON. Reference: Using DB Compatibility Level 130 with Old CE in SQL Server 2016. We ran into this with some of our upgrades; results will vary with each database.


SQL Server: ODBC Linked Server to Oracle does not return all rows Let's say we have one SQL Server instance on one side:

SQL Server 2008 R2 SP3
Oracle 11.2 client installed with OLEDB Drivers for ODBC

.. and an Oracle database instance on another side:

Oracle Database 11.1.0.7 PSU 24
InstanceName: MERCURE

I created two database links on the SQL Server instance:

MERCURE (using ODBC Server DSN and MSDASQL Provider)
MERCURE_OLE (using OraOLEDB.Oracle)

On the Oracle instance (with instance name MERCURE), I created a schema and table as follows:

CREATE USER JEL_PERF_TST identified by "XXX";
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE to JEL_PERF_TST;
ALTER SESSION SET CURRENT_SCHEMA=JEL_PERF_TST;

-- select DEFAULT_TABLESPACE from dba_users where username='JEL_PERF_TST';
-- USERS

-- create a table for testing (Taken from https://use-the-index-luke.com/sql/example-schema/oracle/3-minute-test)
CREATE TABLE TestTbl (
  id          NUMERIC NOT NULL,
  date_column DATE,
  a           NUMERIC,
  b           NUMERIC,
  text        VARCHAR(255),
  state       CHAR(1),
  PRIMARY KEY (id)
);

 INSERT INTO TestTbl
 SELECT level
      , SYSDATE - level
      , MOD(level,1234)
      , TRUNC(DBMS_RANDOM.VALUE(1, 10))
      , DBMS_RANDOM.STRING('l', 20)
      , DECODE(MOD(level, 5), 'X', 'A')
   FROM dual
CONNECT BY level <= 50000;

So, basically, I have 50.000 rows in my table.
Now, let's run the following queries on SQL Server instance:

First, let's activate statistics:

set statistics io on;
set statistics time on;

Then, run a first query using OraOLEDB.Oracle provider:

SELECT * FROM [MERCURE_OLE]..[JEL_PERF_TST].[TESTTBL];

Here is what I got:

SQL Server parse and compile time:
   CPU time = 32 ms, elapsed time = 274 ms.

(50000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2199 ms,  elapsed time = 5769 ms.

Performances aren't pretty good (more tha 8.5 ms for a single row) but it doesn't matter here. We have 100% of rows returned.

Let's now run the query using OPENQUERY and same Linked Server:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 103 ms.

(50000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2542 ms,  elapsed time = 6716 ms.

Same here, performances are bad but we have 100% of rows returned.

Now, let's run the exact same query, but using MSDASQL provider:

select * from openquery(MERCURE,'select * from JEL_PERF_TST.TestTbl');

It results in:

 SQL Server parse and compile time:
   CPU time = 15 ms, elapsed time = 107 ms.

(**49901** row(s) affected)

 SQL Server Execution Times:
   CPU time = 1357 ms,  elapsed time = 3932 ms.

Timing is better, but where are my 99 missing rows ??

I've tried many changes to my ODBC connection descriptor (Enable/disable result sets, raise the Fetch Buffer Size, Use MSDTC or not, etc.). Nothing worked.

Any clue ?

Microsoft fixed this issue in Cumulative Update 4 for SQL Server 2014.

https://support.microsoft.com/help/2993262/

If you are unable to install the update, they provide a workaround:

  To work around this issue, use a nested loops join or a hash join by
  using the Option command instead of the merge join.


Availability Groups and Partition Swapping Is it possible to use SQL 2012 Availability Groups over two machines and perform partition swapping on the primary machine ?

If database that is being partition swapped is part of Availability Group, parition switch operation will be logged into the transaction log and it will be replicated to secondary replica.


How should I model a database structure to store information about words? I have an application where the user can click on any word in a text, which will bring back related information about the base word and the specific inflection (word form) of the word clicked. I've tried representing this in a multitude of ways, but now that I'm close to launch, I'm worried as hell about the best way to represent it (this is my first ever project with programming, so I have no idea what I'm in for).
Considerations
The way I had originally set this up was that there was a base word table that contained the base word, part of speech, and then an array of word forms for each base word.
I then learned that this was a bad idea in terms of normalization, and I also realized that I needed to store specific inflection information for the words (mood, tense, person, etc.), so I rearranged it.
In the second iteration, I had three tables, a base_word table that had the base_word and the part of speech, a word table that had the word and its inflection information, and then a “join” table between them that contained the base_word_id and the word_id.
However, I've been re-thinking this setup, because semantically speaking, a base word is also a word, just a specific type of word. So in this case, I feel like a “self-join” table might be most appropriate, although I could be completely overthinking this. In the “self-join” table, there would be an is_baseword boolean attribute, which would theoretically allow all the inflection columns to store NULL marks if it was set to true. Is this a better idea?

Note: I found these two similar questions which didn't seem to completely answer my questions, so I'm posting my twist on these here:

Table design for a dictionary that can have words with many different spellings

How can I represent a list of words and related words in a relational structure?

I believe your design for a one to many join with base_word in one table and word forms in another is fine. The reasons I make this judgement are:

The base_word, as an entity, is sufficiently different to word_forms as to represent a different entity. For instance, if you were to include the words origin language, this would also be only applicable to the base word entity, not its word forms.
Storing the data in the same table will have implications on the size of the tables due to having sparse columns with attributes that are associated with base_word OR word_form only, meaning they are NULL or empty for the others. You point this out with the is_baseword and inflection columns. Having a bunch of NULL values on a row which will never be changed is usually a reason to use the EAV model or split into different entities.
Performance considerations for queries should be taken into account. With the additional size consideration a self join to a sparsely populated table will likely not be as performant. You will probably find yourself indexing a single table for base_words OR word_forms separately. The indexes will be larger and more difficult to maintain for inserts/deletes etc. Better to have indexes on smaller tables and to have as few as possible on a table.

Don't get too concerned with perfect normalization. While a perfectly normalized system may adhere to strict information science principles, it is often better in the real world to have a slightly denormalized database for other reasons.


Providing extra foreign keys I am pretty new to databases and SQL.
Have following question, didn't figured out how to google this.

I am designing database structure for my application. Let's say I have following tables:

user

id (PK)
department_id (FK)
first_name
last_name

department

id (PK)
name

task

id (PK)
user_id (FK)
? department_id (FK)

Should I add department_id into task, or maybe user_id will be sufficient?
In case if I want to figure out user's department while selecting data from tasks, how do I build the query for it?

UPD:

Let's assume we don't plan to change task's owner through time. I consider extra foreign keys only as shortucts to avoid extra joins while selecting data.

Example: let's assume we have one more table:

session

ID (PK)
started_time
task_id (FK)
? user_id (FK)

If I won't add user_id, I will need to perform two extra join, first to get user_id from task_id and then, to get department_id from user_id.

The question is - is this generally normal or bad practice?

In case if I want to figure out user's department while selecting data from tasks, how do I build the query for it?

You'd first join user and then department.

SELECT *
       FROM task
            INNER JOIN user
                       ON user.id = task_user.id
            INNER JOIN department
                       ON department.id = user.department_id;

Such "chains" of joins are no problem for a DBMS in general, it's built to do that (but you should consider proper indexing to support it (but that's also true for queries not using joins)). The "shortcuts" you have in mind on the other hand bring a certain level of denormalization and with that a handful of problems.

Whether to include department_id in tasks depends on what you're trying to model.

If a task can be assigned to a whole department without a specific user from that department, you'll need it. Add a check constraint enforcing that one of both is null, to be able to distinguish between task assigned to only a user and those only assigned to a department.

If only users can have tasks assigned, you won't need it. You can query the department as shown above. Further more in that case it's dangerous to include it or at least it makes things more complicated. You have to check then, that the integrity isn't violated. Say if a user assigned to a task is in department A but the department assigned to the task is B. That obviously doesn't match. Unless users can be in several departments an a task should be assigned to a user in the role as a member of a specific department. That's one of the problems of denormalization I mentioned above.

(You see, there are many ifs here, you need to think through. Maybe even more than I just came up with!)

If you don't need the department_id because of the logic, I'd recommend not to use it in task. It most likely brings more problems and doesn't even solve another as joining usually is no problem.


Independent aggregation of related entities, while also sorting by related (in a single statement) I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named authorships.
My goal is to query for some subset of the books, and also get the set of related authorships, and the set (i.e. without duplicates) of related authors, each in some particular order. In essence I want to retain the normalized/separated structure of the records, I don't want to denormalize in any way (just sort).
Typically I think you'd do this using multiple statements, using a function or external code to provide the IDs to IN expressions or similar. However I've been able to use the following pattern in PostgreSQL to do it in a single statement:
WITH matched_books AS (
  SELECT id, title FROM books
  -- Could be any criteria:
  WHERE title LIKE 'The %'
),
related_authorships AS (
  SELECT authorships.id, book_id, author_id
  FROM authorships
  JOIN matched_books ON book_id = matched_books.id
),
related_authors AS (
  SELECT id, name
  FROM authors
  -- Could also use DISTINCT and do a join here, but I understand EXISTS is typically better for performance:
  WHERE EXISTS (SELECT 1 FROM related_authorships WHERE author_id = authors.id)
)
SELECT
  -- Scalar subqueries that each return a single JSON array of objects:
  -- JSON is completely fine for my purposes, but could also use array_agg.
  (SELECT json_agg(matched_books.* ORDER BY title) FROM matched_books) books,
  (SELECT json_agg(related_authorships.* ORDER BY id) FROM related_authorships) authorships,
  (SELECT json_agg(related_authors.* ORDER BY name) FROM related_authors) authors;

(Side note: In previous attempts I had used LEFT JOINs at the top level and json_agg(DISTINCT ...), but that left me unable to use ORDER BY meaningfully, and seemed messier/worse for performance.)
While this approach almost works great, I now want to order the books by information stored in their related authors and/or authorships. As an obvious example, let's say I want them to be sorted by the name of their author, or if any have multiple authors, use a column from authorships (it can just be the lowest integer id in this case) to determine which author should be used.
I can't think of an approach that will allow this while still returning the sets independently, at least not without some duplication of operations. How would you solve this?

I azm not sure do I undersgtand you correctly, but probably you need to group additional field from authors to have only one value for ORDER. It might be count of athours, or min / max for name or so:
WITH books AS (
  SELECT 1 AS id, 'The 1' AS title
   UNION ALL
  SELECT 2, 'The 2'
   UNION ALL
  SELECT 3, '3'
), authorships AS (
  SELECT 1 AS id, 1 AS book_id, 1 AS author_id
   UNION ALL
  SELECT 2, 1, 2
   UNION ALL
  SELECT 3, 1, 3
   UNION ALL
  SELECT 4, 2, 1
   UNION ALL
  SELECT 5, 3, 1
), authors AS (
  SELECT 1 AS id, 'name1' AS name
   UNION ALL
  SELECT 2, 'name2'
   UNION ALL
  SELECT 3, 'name3'
   UNION ALL
  SELECT 4, 'name4'
), filtered AS (
  SELECT book_id, title, ba.id, author_id, name
    FROM books AS b
    JOIN authorships AS ba ON ba.book_id = b.id
    JOIN authors AS a ON a.id = ba.author_id WHERE title LIKE 'The %'
)
SELECT (
         SELECT json_agg(b.* ORDER BY count)
           FROM (
                  SELECT book_id AS id, title, count(name)
                    FROM filtered AS f GROUP BY 1,2
                ) AS f
           JOIN LATERAL (SELECT id, title) AS b ON true)
       ) AS book

           json_agg
-----------------------------
 [{"id":2,"title":"The 2"}, +
  {"id":1,"title":"The 1"}]
(1 row)


Quick MySQL Backup (1 file per table) I asked a relevant question few months back and got answer with wide variety of options for backup.
But this time I have different scenario and I have got a database with 7 tables and 1 of them has exceeded 20GB (table1) and one another(table6) is 13GB while all other tables are well below 500MB.
In current scenario larger table (table1) is going to get even more data so I have a thought to create another table(table1-1) with same schema and insert all new data in that table. Because mostly there are INSERT calls and very few UPDATE calls are made to table1 and all data inserted are based on ID from table2. (I think its not very important to know though)
All tables are MYISAM and table6 is not going to grow in near future with same pace.
Now I want to ask few questions to gurus and few backup solutions.
First of all.. do you think as I have grown beyond 20GB and creating new table with same schema would help me because mostly there are INSERT calls so it will be light on system to insert in new place compared with inserting in a table which is already grown to 20GB+.
And all these tables are in single database so I want to backup whole database but not 1 big file rather it should be 1 file per table because it will reduce overhead of keeping local copy which now contains table1 all the time. Actually if table1-1 will be used then table1 will only be locally copied only once in a week while others are locally copied daily.
Please suggest me finest way to backup keeping in mind the data size and with minimal (only necessaary)downtime.

Last time, I answered your question by suggesting you perform a well-scripted parallel dump of all tables.

Since the tables are lopsided in size, parallel table dumps are not much help for the 20GB and 13GB tables. What can be done? You must try something out of the ordinary. The good thing here is that all the tables are MyISAM.

Have you ever considered running an rsync? How do you do that?

EXAMPLE

You have /var/lib/mysql as your datadir for a remote production database

Perform an rsync on /var/lib/mysql on prod server (IP Address 10.1.2.30) to /backups on a backup server. Just run a script something like this on the backup server:

INIT_TS=0
COMP_TS=0
DIFF_TS=61
RSYNC_THRESHOLD=60
DBSERVER_IP=10.1.2.30
FOLDER_TO_BACKUP=/var/lib/mysql
mkdir /backups/backup_temp

while [ ${DIFF_TS} -gt ${RSYNC_THRESHOLD} ]
do
    INIT_TS=`date +%s`
    rsync -arv user01@${DBSERVER_IP}:${FOLDER_TO_BACKUP}/ /backups/backup_temp
    CURR_TS=`date +%s`
    (( DIFF_TS = CURR_TS - PREV_TS ))
done

MYSQL_CONN="-h${DBSERVER_IP} -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 3
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

rsync -arv user01@${DBSERVER_IP}:/var/lib/mysql/ /backups/backup_temp

BACKUP_TIMESTAMP=`date +"%Y%m%d%H%M%S"`
mv /backups/backup_temp /backups/${BACKUP_TIMESTAMP}

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Here is what this basic backup script does

Step 01) rsync production's /var/lib/mysql (from remote server) to /backups on local server
Step 02) If running time of rsync is greater than 60 seconds, repeat Step 01
Step 03) Close all tables and issue global read lock
Step 04) Perform final rsync (SELECTs allowed only)
Step 05) Release global read lock

Notice the following:

the only mysql interaction is

issuing the global read lock
checking for the process ID that is holding the global read lock
releasing the global read lock

FOLDER_TO_BACKUP is the datadir. If you want to backup a specific database (say mydb), set FOLDER_TO_BACKUP to /var/lib/mysql/mydb
Actual downtime is from the acquisition of the SLEEP_ID to the processing killing of the SLEEP_ID

Give it a Try !!!

UPDATE 2012-02-02 11:47 EDT

The above script secures mysqld in such a way that it takes a disklevel copy of the database while allowing mysqld to perform INSERTs, UPDATEs, and DELETEs during the copy. This is necessary since the MyISAM Storage Engine does not have a natural mechanism for allowing a copying of itself. Even powerful products like MySQL Enterprise Backup and Percona XtraBackup perform full table locking on MyISAM tables just like this. My personal preference is to script it so I can see it, feel it, taste it, and touch it and just know for myself that MyISAM is fully protected.

The advantage of using my script does stems from the fact that I shorten the length of downtime. In the context of this anwser, downtime is really the period where the mysqld process will only allow SELECTs to all MyISAM tables during the final rsync.

An additional benefit is that all MyISAM tables copied will all have the same point-in-time should you wish to restore all MyISAM tables as of the same point-in-time. That point-in-time will be based, not on the start of the backup process, but when the global read lock as acquired.

If you need to copy only specific tables and you can guarantee no DB Connection will touch it, only then can you toss aside any failsafes and copy any MyISAM table to your heart's content.

UPDATE 2012-02-03 11:47 EDT

If the length of time the rsync takes concerns you, here is an additional suggestion:

You should also make nightly backups of the binary logs. By having nightly copies of binary logs, you would essentially have the incremental backups via those binary logs.

That being said, you could set up a weekly or monthly run of the backup script. Recovery to a specific point-in-time would be your responsibility from there, but you would have every piece of data necessary to do so.


CDC Capture Instance or State to ensure referential integrity I'm implementing an SSIS/CDC based data warehouse solution and have a question relating to referential integrity on the destination database.

As an example, I have INVENTORY_TRANSACTION in one table and PART in another table each with their own SSIS package for incremental loads.  I don't want to have a situation where I have INVENTORY_TRANSACTIONS in the data warehouse that don't have an associated parent PART record.

I've read quite a bit on the subject but I'm still unclear.

Should I be using the same capture_instance on the tables that have related data so they all target the same LSN chain?  Should I be using the same State name?  Or, am I missing something entirely?

From Microsoft docs.  Guess I just had to find the right doc...
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/cdc-flow-components#grouping-tables-for-cdc-processing

Grouping Tables for CDC Processing

Database projects range in size from several tables to many thousands of tables. When designing initial load and CDC packages, it is beneficial to group tables in much smaller groups for easier management and efficiency. This section lists various considerations that impact the sorting of tables into small groups, where the tables in each are initially loaded and then updated as a group.

The CDC patterns supported by the CDC components assume that this grouping is already determined. Each group defines a separate CDC context that is maintained separately from other groups. For each group, initial-load and trickle-feed update packages are created. Trickle-feed updates are scheduled for periodic runs based on the rate of change processing constraints (for example, CPU and IO consumption, impact on other systems) and the desired latency.

Tables are grouped based on the following considerations:

According to the target database. All tables that are written to different target databases or undergo different processing should be assigned to different CDC groups.

Tables that are related with referential integrity constraints should be assigned to the same group to avoid referential integrity problems at the target.

Tables for which higher latency can be tolerated can be grouped so they can be processed less frequently and reduce overall system load.

Tables for which there is a higher rate of change should be in smaller groups, and tables with a low rate of change can be grouped in larger groups.

The following two packages are created for each CDC group:

An Initial Load package, which reads the entire range of data from the source tables and applies it to the target tables.

A trickle-feed update package that reads changes made to the source tables and applies the changes to the target tables. This package should be executed on a regularly scheduled basis.

CDC State

Each CDC group has a state associated with it, which is represented by a string with a specific format. For more information, see CDC Control Task. The following table shows the possible CDC state values.

StateDescription0-(INITIAL)The state that exists before any packages are run on the current CDC group. This is also the state when the CDC state is empty.

For more information about CDC Control task operations, see CDC Control Task.1-ILSTART (Initial-Load-Started)This is the state that exists when the initial load package starts. This occurs after the MarkInitialLoadStartoperation call to the CDC Control task.

For more information about CDC Control task operations, see CDC Control Task.2- ILEND (Initial-Load-Ended)This is the state that exists when the initial load package ends successfully. This occurs after the MarkInitialLoadEnd operation call to the CDC Control task.

For more information about CDC Control task operations, see CDC Control Task.3-ILUPDATE (Initial Load Update)This is the state that exists after the first run of the Update package after the initial load while still processing the initial processing range. This occurs after the GetProcessingRangeoperation call to the CDC control task.

If using the _$reprocessing column, it is set to 1 to indicate that the package may be reprocessing rows already at the target.

For more information about CDC Control task operations, see CDC Control Task.4-TFEND (Trickle-Feed-Update-Ended)This is the state expected for regular CDC runs. It indicates that the previous run completed successfully and that a new run with a new processing range can be started.5-TFSTART (Trickle-Feed-Update-Started)This is the state that exists on subsequent runs of the Update package after the GetProcessingRangeoperation call to the CDC control task.

This indicates that a regular CDC run is started, but is not finished or has not yet finished, cleanly (MarkProcessedRange).

For more information about CDC Control task operations, see CDC Control Task.6-TFREDO (Reprocessing-Trickle-Feed-Updates)This is the state on a GetProcessingRangethat occurs after TFSTART. This indicates that the previous run did not complete successfully.

If using the __$reprocessing column, it is set to 1 to indicate that the package may be reprocessing rows already at the target.7-ERRORThe CDC group is in an ERROR state.

Here is the state diagram for the CDC components. An ERROR state is reached when a state is reached that is not expected. The expected states are illustrated in the following diagram. However the diagram does not show the ERROR state.

For example, at the end of an initial load package, when trying to set the state to ILEND, if the state is TFSTART then the CDC group is in an error state and the Trickle-Feed Update package does not run (the Initial Load package does run).

Once the Initial Load package runs successfully, the Trickle-Feed Update package runs repeatedly under a predetermined schedule to process changes to the source tables. Each run of the Trickle-Feed Update package is a CDC run.


sp_updatestats vs Update statistics Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)

  exec sp_updatestats vs update statistics tablename

updating the tables using sp_updatestats with default value NO will update the statistics with default sampling rate.

similarly updating statistics of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE) will also update the table statistics with default sampling.

So is there any difference between both methods? Am I missing anything here ?

Update :

I know that sp_updatestats runs on all the tables but using UPDATE STATISTICS we can update statistics of specific table.

Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)

Adding to what is already mentioned by @Gameiswar, the other difference which I know with sp_updatestats, when you run it for all tables of database it ONLY updates statistics of tables which has at least one row changed. BOL says

  For disk-based tables, sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

So when you run sp_updatestats and it says statistics for all tables have been updated this is incorrect/misleading only for tables which has at least one row changed was stats updated.

While going with UPDATE STATISTICS you have quite lot of options to use for stats update.

Plus when you rebuild the index with full scan or with default options the stats are updated for that index so no need to rebuild stats again for it.

PS: Whatever the difference may be, I would not use sp_updatestats as the mechanism it uses to update stats is not good, why should i update stats for table if just one row has changed and I have 100K rows in table, it will just consume resources and cause more issue.

EDIT:

If you want to update stats selectively and do not want to run sp_updatestas use below query to filter out outdated stats. This query uses DMF and would work from SQL Server 2008 R2 SP2 , SQL Server 2012 Sp1 and above, Copied from Erin Stellato's blog

NOTE: Realize that different tables may have different thresholds and you will need to tweak the query above for your databases. For some tables, waiting until 15% or 20% of the rows have been modified may be ok. But for others, you may need to update at 10% or even 5%, depending on the actual values and their skew.

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] ,
[ss].[name] AS [Statistic],
[sp].[last_updated] AS [StatsLastUpdated] ,
[sp].[rows] AS [RowsInTable] ,
[sp].[rows_sampled] AS [RowsSampled] ,
[sp].[modification_counter] AS [RowModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
[ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND [sp].[modification_counter] > 0--change accordingly
ORDER BY [sp].[last_updated] DESC;

If you want to avoid all such hassle you can also use Ola Hallengren update stats script which takes care of lot of such things.


How to insert several csv files into Elasticsearch? I have several csv files on university courses that all seem linked by an ID, that you can find here, and I wondered how to put them on Elasticsearch. I know, thanks to this video and Logstash, how to insert one sole file csv file to Elasticsearch. But do you know how to insert several such as those in the provided link ?

At the moment I started with a first .config file for a first csv file : ACCREDITATION.csv. But it would be painful to write them all...

The .config file is :

input{
    file{
        path =>"Users/mike/Data/ACCREDITATION.csv"
        start_position => "begining"
        sincedb_path => "/dev/null"
    }
}

filter{
    csv{
        separator => ","
        columns => ['PUBUKPRN', 'UKPRN', 'KISCOURSEID', 'KISMODE', 'ACCTYPE', 'ACCDEPEND', 'ACCDEPENDURL', 'ACCDEPENDURLW']

    }
    mutate{convert => ["PUBUKPRN","integer"]}
    mutate{convert => ["UKPRN","integer"]}
    mutate{convert => ["KISMODE","integer"]}
    mutate{convert => ["ACCTYPE","integer"]}
    mutate{convert => ["ACCDEPEND","integer"]}
}

output{
    elasticsearch{
        hosts =>"localhost"
        index =>"accreditation"
        document_type =>"accreditaiton keys"
    }
    stdout{}
}

Update May, 3rd

Without knowing how to use a .config file to implement csv files to Elasticsearch, I fell back to Elastic blog and tried to do a shell script importSVFiles for a first .csv file before trying to generalize the approach :

importCSVFiles :

#!/bin/bash
while read f1
do
   curl -XPOST 'https://XXX.us-east-1.aws.found.io:9243/courses/accreditation' -H "Content-Type: application/json" -u elastic:XXX -d "{ \"accreditation\": \"$f1\" }"
done < AccreditationByHep.csv

Yet I received a mapper_parsing_exception on the terminal :

mike@mike-thinks:~/Data/on_2018_04_25_16_43_17$ ./importCSVFiles
{"error":{"root_cause":
            [{"type":"mapper_parsing_exception","reason":"failed to parse"}],
          "type":"mapper_parsing_exception",
          "reason":"failed to parse",
          "caused_by":{"type":"i_o_exception","reason":"Illegal unquoted character ((CTRL-CHAR, code 13)):
              has to be escaped using backslash to be included in string value\n at [Source: org.elasticsearch.common.bytes.BytesReference$MarkSupportingStreamInputWrapper@e18584; line: 1, column: 88]"}
         },"status":400
}

I just had a look at the data in the Higher Education Statistics Agency (HESA) zipped file and the files are all different.

This means you will either have to create an individual .config file for each import or create a single .config file using conditions as described in the following article:

Reference: How to use multiple csv files in logstash (Elastic Discuss Forum)

Expanding on your first .config by one level:

input{
    file{
        path =>"Users/mike/Data/ACCREDITATION.csv"
        start_position => "begining"
        sincedb_path => "/dev/null"
    }
    file{
        path =>"Users/mike/Data/ACCREDITATION.csv"
        start_position => "begining"
        sincedb_path => "/dev/null"
    }

}

filter{
    # added condition for first file
    if [path] == "Users/mike/Data/ACCREDITATION.csv"{
        csv{
            separator => ","
            columns => ['PUBUKPRN', 'UKPRN', 'KISCOURSEID', 'KISMODE', 'ACCTYPE', 'ACCDEPEND', 'ACCDEPENDURL', 'ACCDEPENDURLW']

        }
        mutate{convert => ["PUBUKPRN","integer"]}
        mutate{convert => ["UKPRN","integer"]}
        mutate{convert => ["KISMODE","integer"]}
        mutate{convert => ["ACCTYPE","integer"]}
        mutate{convert => ["ACCDEPEND","integer"]}
    }
    # added condition for second file
    else if [path] == "Users/mike/Data/AccreditationByHep.csv"{
        csv{
            separator => ","
            columns => ['AccreditingBodyName', 'AccreditionType', 'HEP', 'KisCourseTitle', 'KiscourseID']
        }
    # ommitted mutations for second file
    }

}

output{
    # added condition for first file
    if [path] == "Users/mike/Data/ACCREDITATION.csv"{
        elasticsearch{
            hosts =>"localhost"
            index =>"accreditation"
            document_type =>"accreditaiton keys"
        }
    }
    # added condition for second file
    else if [path] == "Users/mike/Data/AccreditationByHep.csv"{
        elasticsearch{
            hosts =>"localhost"
            index =>"accreditationByHep"
            document_type =>"accreditaitonbyhep keys"
        }
    }
    stdout{}
}

document_type is a deprecated configuration option

You should be able to expand on this example on your own.


Issue on mysqldump in MySQL5.6 I did upgrade the MySQL server from 5.5 to 5.6
I am trying to take dump for a table like this
 mysqldump -uroot -p dba admin > D:\admin.sql

But it gets following error

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1':
You have an er  ror in your SQL syntax; check the manual that
corresponds to your MySQL server v  ersion for the right syntax to use
near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line  1 (1064)

edit:
mysqldump verison is 5.5.30.

Some information can be found in SET Syntax and this bug.

  The SET statement assigns values to different types of variables that affect the operation of the server or your client. Older versions of MySQL employed SET OPTION, but this syntax is deprecated in favor of SET without OPTION.

How to fix it

If the problem happens when you are creating the dump (your case):
Upgrade you MySQL client tools to the same version of your MySQL server (in your case, 5.6)
If the problem happens when you are restoring the dump: At the beginning of the file, you have some SET OPTION statements; edit your dump, and change all instances of SET OPTION to SET SESSION like described here


How to sync the data between two identical DB The thing is I've two identical databases, one is in my localhostserver at my laptop and the other one is on the office main server. Is there any automatic way or software that can synchronize the data between these 2 identical databases? I'm working with SQL Server 2008 R2.

SQL Server Replication can ensure that your data is synchronized.  SQLRockstar is correct in saying that you need to determine if policy will allow the data to be stored on your laptop.

Questions to think about before moving ahead with replication:

How often is your laptop on the same network as your main server?
Do data modifications need to be two-way?  Do you just want your laptop to mimic the office server, or will you be making changes that need to be pushed back to the main server?
How big is your database expected to grow?  Will you have enough room on the laptop?
What happens if your laptop is lost/stolen/destroyed?  Will your data be safe?  Do you need to investigate in hard-drive level encryption (i.e. do you need to use bitlocker)?
How quickly do changes in one database need to be propagated to the other?

You may want to start off by investigating MERGE replication.


Update field on field change trigger I'm trying to create a trigger that sets an updated flag when a field is changed. The reason I can't do it with a basic UPDATE query is because the change will come from a INSERT ON DUPLICATE UPDATE.

This is the trigger:

CREATE TRIGGER set_update_flag BEFORE UPDATE ON `reputation_email_score`
FOR EACH ROW
    BEGIN
        IF NEW.`score` <> OLD.`score` THEN
          SET NEW.`updated` = "yes";
        END IF;
    END;

When I try to run it, I get these errors:

Error in query (1064): Syntax error near '' at line 5
Error in query (1064): Syntax error near 'END IF' at line 1
Error in query (1064): Syntax error near 'END' at line 1

What is wrong in my SQL query?

You forget about DELIMITER:

DELIMITER $$

CREATE TRIGGER set_update_flag BEFORE UPDATE ON `reputation_email_score`
FOR EACH ROW
    BEGIN
        IF NEW.`score` <> OLD.`score` THEN
          SET NEW.`updated` = "yes";
        END IF;
END $$
DELIMITER ;

also ; after last END - not necessary


mySQL- large database I need guideline to setup DBMS(MYSQL or SQL Server 2008). The database size is expected upto 80-100GB in next few months. One main table will contain textual content like essays. Can MySQL handle such big size database and can  full text search really work for me for such big database. Should I consider MySQL or any other?

Thanks

Can MySQL handle such big size database and can full text search really work for me for such
  big database

What is big?

SERIOUSLY.

Let's have a look at modern hardware. Databases typcially are large when they are larger than the available RAM.

100gb - hm.

128gb memory was not particularly large last year.

Get a decent modern XEON, put 256GB memory into it.

Just checking - supermicro.com, website has a "featured motherboard". "Supports up to 256 GB DDR3 ECC". Ok, 128gb won't be that expensive.

I have a 64gb database server here that is 5 years old. I have a desktop server that has 64gb memory (micro atx board). Last time I checked, lower mid range servers (two sockets) go to 512gb memory.

That means that by all accounts, 100gb is not large.


Exporting database from SSMS to Azure is sitting on "enabling indexes" without finishing Having created an Azure SQL server resource, I'm now exporting a database from Management Studio 2014 to the Azure resource. It all seemed to be going well, but after going through all the tables, is now sitting on "enabling indexes" and has been for about 2 hours.

Deploy Database Screenshot

The database is relatively small, only 1.5GB mdf, with a 180MB log. I have also tried exporting from SSMS to a local .bacpac file, and that also ends up sitting there for over an hour after going through the tables, but before showing the "enabling indexes" item.

It seems like SSMS is having trouble with the database for some reason, however this is a backup from production and has been in use without issues for a long time.

I'm not sure what to look for at this point. Are there some tools to check the database for errors which I can use here, to see why SSMS isn't able to finish exporting the db?

It is always important to use the latest SSMS for better experience with SQL Azure. Intellisense is now supported and many bugs related to SQL Azure have been fixed. Please download the latest version from here.

For importing a database to an empty SQL Azure database, please use the Data Migration Assistant. Download it from here.


How to trace steps in SQL profiler for only particular application We are using a third party company's software that talks to a SQL Server database and retrieves and inserts all the information.

When a user uses this software sometimes it freezes at a particular step.
I need to troubleshoot it and find out why it happens.

Basically I want to use this UI software and at the same time see what query runs at each step I am performing.

For that I am using SQL Profiler to see what query causes this bottleneck.
But how would I trace steps only for that particular software and only for a particular user? I don't want to see all other information.

I am able to filter NTUserName, which I am assuming is just UserName, correct?

But how can I find out what is the ApplicationName?

If I ran this query I see 13 different applications, including Microsoft Office, Report Server etc. None of them sounds like the name of the software we use. (MGA Systems. Insurance Management System)

select distinct
    program_name
from sys.dm_exec_sessions
where is_user_process = 1;

The ApplicationName will be the value specified in the connection string, or if none is specified, a default value that varies depending on the client API. For example, SqlClient will use the generic ".Net SqlClient Data Provider'" value when Application Name is not specified.


Get percentage of group by I have this data:

CREATE TABLE tickets(user_id int NOT NULL);
INSERT INTO tickets VALUES (1);
INSERT INTO tickets VALUES (2);
INSERT INTO tickets VALUES (3); -- 3 times
INSERT INTO tickets VALUES (4); -- 10 times

Now I want to display the percentage of the number of tickets per user.

I tried this:

WITH number_of_tickets AS (
  SELECT user_id, COUNT(user_id) AS number_of_tickets_per_user
  FROM tickets
  GROUP BY user_id
)
SELECT
  number_of_tickets_per_user,
  ROUND((COUNT(user_id) * 100.0) / (SELECT COUNT(DISTINCT(user_id)) FROM tickets), 3) -- No no no no
FROM number_of_tickets
GROUP BY number_of_tickets_per_user
ORDER BY number_of_tickets_per_user;

But I probably don't handle well the percentage calculation. The result always show me 25% for each of number of ticket per user.

Thanks

WITH number_of_tickets AS (
  SELECT user_id, COUNT(user_id) AS number_of_tickets_per_user
  FROM tickets
  GROUP BY user_id
),
total_tickets AS (
  SELECT SUM(number_of_tickets_per_user) total_tickets
  FROM number_of_tickets
)
SELECT
  user_id,
  number_of_tickets_per_user,
  ROUND(number_of_tickets_per_user / total_tickets,3) percent
FROM number_of_tickets, total_tickets;


Strange Sybase / SQL Server linked server issue I have a sybase table which has a column note varchar(1000) null.

The following T-SQL will get the following error if there is a not-null value in note. (sybase is the linked server to the Sybase database)

select * from (sybase, 'select note from table')
select * from (sybase, 'select left(note, 257) note from table')
select * from (sybase, 'select left(note, 1000) note from table')

  OLE DB provider 'ASEOLEDB' for linked server 'sybase' returned data
  that does not match expected data length for column '[ASEOLEDB].note'.
  The (maximum) expected data length is 257, while the returned data
  length is 258.

However, it works if the length parameter is less and equal to 256.

select * from (sybase, 'select left(note, 256) note from table')

Also the following SQL works.

exec ('select note from table') at [sybase]

Is it a bug of the Sybase driver?

try

select * from OPENQUERY(sybase, 'select left(cast(note as varchar(1000)), 257) note from table')


Log all table updates for all tables in MySQL Is there anyway I can get the last updated time of a set of tables? By last updated time, I mean not only when the table's schema changed but when any of the rows changed or even when rows were created/deleted in the given set of tables.

I tried both SELECT * FROM information_schema.tables and SHOW TABLE STATUS FROM myDb and I only see the create_time which is only when table was created.

I can alternatively create triggers like:

CREATE TRIGGER tbl1_delete AFTER DELETE ON tbl1
FOR EACH ROW
BEGIN
  UPDATE table_updates SET update_time = now() WHERE table_name = tbl1
END

But, now I have to repeat this over and over for deletes, updates, creations and schema changes. And, I want to automate this for all my tables!

I got some interesting news for you. You cannot use information_schema.tables because it does not track changes to InnoDB tables.

The best way to find out when a table changed is to go to the OS and get the most recent timestamp of each table. Rather than plagiarize my own posts, here are my posts where I show you how to do this

Sep 25, 2014 : want to find out which databases are used in last 30 days or not
Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?
Apr 04, 2013 : How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?
Dec 21, 2011 : Fastest way to check if InnoDB table has changed

Give Them a Try !!!


How can I recover data from the LDF file? We are using SQL 2005 Express Edition. We want to read the data from the LDF file in order to restore some deleted records.

We tried using a trial version of ApexSQL which helped us a lot. Instead of going for third party software, we are trying to figure out how to read the log file ourselves.

How can we read and recover data from the LDF file?

You will need a previous Full Backup and any log backups taken since the last Full backup

1) Take a tail of the log backup

BACKUP LOG OldDB TO DISK='C:\OldDB_Tail.trn' WITH NO_TRUNCATE

2) Find Transaction for Deleted Records (Operation will be LOP_DELETE_ROWS for DELETEs and LOP_SET_BITS & LOP_MODIFY_ROW for a TRUNCATE TABLE)

SELECT * FROM fn_dblog(NULL,NULL) WHERE AllocUnitName = 'dbo.YourTableName'

3) Restore Previous Full Back and log backups plus the tail to a new
    database WITH NORECOVERY and STOPAT='Just Before Begin Time for Transaction'

RESTORE DATABASE NewDB
FROM DISK='C:\Previous_Full_Backup_Of_OldDB.bak'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM',
MOVE 'OldDB' TO 'C:\DATABASES\NewDB.mdf',
MOVE 'OldDB_Log' TO 'C:\DATABASES\NewDB.ldf'

RESTORE LOG NewDB
FROM DISK='C:\Previous_TranLog_Backup_Of_OldDB.trn'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM'

RESTORE LOG NewDB
FROM DISK='C:\OldDB_Tail.trn'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM'

-- This recovers the restored database and allows access
RESTORE DATABASE NewDB WITH RECOVERY

This blog post covers in more detail finding the time to STOPAT using fn_dblog.
This blog post has a go at recovering data directly from the log but this might be very time consuming compared to a point in time recovery.

Also this blog post by Robert L Davis has an example on combining STOP and STANDBY to allow you to query the state of the databases at different points in time.


Can't Grant Users Update permission There are several Active Directory user groups in my SQL Server database environment. I changed the users' permissions on all databases on the server. Several users wanted me to give them the update permission back. I used a command like this:

Grant Update To [DomainGroup\User]

But this did not work. Now no-one can update except me. How can I resolve this?

Here is the result of the query;

  principal_id  sid name    type    usage
  35  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A43140000  NETONE\ibrahim.tackin   WINDOWS LOGIN   GRANT OR DENY
  0   0x01050000000000090400000083741B006749C04BA943C02702F2A762  public  ROLE    GRANT OR DENY
  5   0x010500000000000904000000FD051D9B2622FA4790703C7DB2C405A8  db_executor ROLE    GRANT OR DENY
  6   0x0105000000000005150000008D555C5A9C99DF04AB5BAB3AF2380000  NETONE\sql-ict-ad-rms   WINDOWS GROUP   GRANT OR DENY
  7   0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A5B2B0000  NETONE\sql-ict-ad-rms-mod   WINDOWS GROUP   GRANT OR DENY
  28  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A292C0000  NETONE\SQLDDL   WINDOWS GROUP   GRANT OR DENY
  32  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A352A0000  NETONE\SQLQRY   WINDOWS GROUP   GRANT OR DENY
  33  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A5C410000  NETONE\SQLGelir-guvence WINDOWS GROUP   GRANT OR DENY
  34  0x0105000000000005150000008D555C5A9C99DF04AB5BAB3A5D410000  NETONE\SQLGelir-guvence-mod WINDOWS GROUP   GRANT OR DENY
  16384   0x01050000000000090400000000000000000000000000000000400000  db_owner    ROLE    GRANT OR DENY
  16390   0x01050000000000090400000000000000000000000000000006400000  db_datareader   ROLE    GRANT OR DENY
  16391   0x01050000000000090400000000000000000000000000000007400000  db_datawriter   ROLE    GRANT OR DENY

The user that has access problems is the member of many win groups,
even if he's a member of db_owner he has limited access if someone revoke/deny some permissions to him or to his group.

To find out restrictions you should use this query:

execute as user = 'NETONE\İbrahim.tackin';
select user_name(grantee_principal_id) as user_,
       permission_name,
       object_name(major_id) as obj_name,
       class_desc,
       state
from sys.database_permissions p
     join sys.user_token t
        on p.grantee_principal_id = t.principal_id
where state in ('D', 'R');
revert;

Here I limit the result to those roles/win groups of which problem user is a member of.

So your user has 3 DENY of UPDATE on all database:

NETONE\sql-ict-ad-rms UPDATE NULL DATABASE D
NETONE\SQLDDL UPDATE NULL DATABASE D
NETONE\SQLQRY UPDATE NULL DATABASE D

Your user will be unable to perform any UPDATE until you revoke these denies:

revoke update to [NETONE\İbrahim.tackin],[NETONE\SQLDDL],[NETONE\SQLQRY]

If you don't deny by error and the members of these 3 groups should not be able to update, you should exclude all your users that should be able to update from all these 3 groups.

Note you have same 3 deny to make a delete.

The permissions of any user is given by the "sum" of all the permissions that were granted/denied to him and his groups/roles.

Until your user is a member of win group that has update denied he will not be able to update. To make him  be able to update you should exclude him from grous with deny or revoke deny from these groups.