Automatically set value on child rows when deleting parent row? Description

We have a table and data below :

Version: PostgreSQL 9.3
Table name: tree_data (id int, code text, name text, parent_id int)
Primary key: id
Foreign key: parent_id (refer to id)

and data:

insert into tree_data (id, code, name, parent_id) values (1, 'aaa','aaa', null);
insert into tree_data (id, code, name, parent_id) values (2, 'bbb','bbb', 1);
insert into tree_data (id, code, name, parent_id) values (3, 'ccc','ccc', 1);

 id | code | name | parent_id
 1    aaa    aaa      null
 2    bbb    bbb      1
 3    ccc    ccc      1

Here our query and result we want, it means: when deleting id = 1 (parent row), table will automatically set parent_id = null in child rows (first level) .

delete from tree_data where id = 1 ;
----> rows after deleting:
 id | code | name | parent_id
 2    bbb    bbb      null
 3    ccc    ccc      null

Our questions:

Can we use postgresql constraints to do it ?  If not, how we can do ?

create table tree_data
(
   id integer primary key,
   code text,
   name text,
   parent_id integer,
   constraint fk_parent
      foreign key (parent_id)
      references tree_data(id)
      on delete set null
);

SQLFiddle example: http://sqlfiddle.com/#!15/b9a62/1


Which is the redundant relationship in this ER Diagram and why? Which is the redundant relationship in this ER Diagram and why? A few of my friends and I have been trying to figure it out but keep going in circles.

where the attributes of each entity are as follows:

Employee: Employee_No, Name, Address and Telephone_No.
Department: Department_No, Name and Location.
Training Course: Course Code, Title, Start_Date and Description.

None of them are redundant. It's ok to have a loop.

You seem to be missing a many-many relationship (Employee Course) table, though.


How can a blocking_session_id be minus 2? I have a case where a query I'm running is taking a long time.  When I checked sys.dm_exec_requests the blocking_session_id is a negative value, -2 to be specific.  The wait is a LCK_M_X and the blocked command is a DELETE statement.  To the best of my knowledge nothing else is touching that table at the moment.  In fact the only active requests are the blocked connection and the query checking sys.dm_exec_requests.  When I look at sys.dm_tran_session_transactions the blocked session is the only one showing up.

Take a look at this blog post by Bob Dorr: How It Works: Orphan DTC Transaction (Session/SPID = -2)

As Bob says, it's a bit of a misnomer that it's an orphaned transaction by DTC, but that is usually how it represents itself.  It's due to (as per Bob's words) "NO ENLISTED SESSIONS on the SQL Server but the transaction is active yet".  You should take a look at that link in it's entirety.

Oftentimes what happens with DTC transactions is that there is no commit/rollback and it does indeed pose a problem, as MSDTC may not reconnect to clean it up, release locks, etc.

See the BOL reference on sys.dm_tran_locks:

  Distributed transactions that are not associated with a session ID value are orphaned transactions and are assigned the session ID value of -2. For more information, see KILL (Transact-SQL).

Yes, BOL uses the terminology "orphaned transaction", as in my experience that is what it usually ends up being (although you can't make that blanket statement without proper investigation that a distributed transaction coordinator hasn't indeed forgotten about the transaction holding everything up).  In that case, after a proper orphaned determination, it is common to then KILL it based off of the unit of work (UOW).


how to replace dbms_reputil.from_remote in oracle 12.2.0.1 since All objects removed except REPLICATION_IS_ON, _OFF and _ON how to replace dbms_reputil.from_remote in oracle 12.2.0.1  since All objects removed except REPLICATION_IS_ON, _OFF and _ON

Desupport of Advanced Replication

  Starting in Oracle Database 12c release 2 (12.2), the Advanced
  Replication feature of Oracle Database is desupported.

  The Oracle Database Advanced Replication feature is desupported in its
  entirety. The desupport of this feature includes all functionality
  associated with this feature: multimaster replication, updateable
  materialized views, hierarchical materialized views, and deployment
  templates. Read-only materialized views are still supported with basic
  replication.

  Oracle recommends that you replace your use of Advanced Replication
  with Oracle GoldenGate.


Good way to build strong index in MySQL We have a huge database on work, and i think the combinasion of bad hardware and bad indexing are the reason for our backend are not working so perfect as we work on its shut do.

So are there are perfect or great way to work with index and query optimizion in MySQL and not only use the slow-log and test every single query out?

hope there are way around so we can find all the bad querys and fix them.

Quick and Cheap

The quickest method for query review is indeed the slow query log, combined with a tool called pt-query-digest

An example process is to first set long_query_time=1. Set it to 0 for most accurate results, but it might affect IO depending on load of the system.

Let the slow query log run for a period of time, such as 1 week, to capture a significant number of query samples.

Then run pt-query-digest against the slow log.

This tool is free to use.

Continual process

To use a similar process, but have it behind a GUI to know when queries showed up or have been checked, you can set up Anemometer

An enterprise solution

The previous tools mentioned come without much cost, but only focus on queries.

A tool like VividCortex will provide you a great view of the performance of your system, and help you drill down into the queries in many different ways.

The method VividCortex uses to capture queries is lower impact than enabling the slow query log.


How to stop MySQL replicating users on slave I have set --replicate-ignore-db=mysql in MySQL slave, but still it is replicating any users I create on the master.

Is there a way to stop the slave from replicating users?

For statement-based replication(the default for mixed binary logging format), --replicate-ignore-db=foo_db works only when foo_db is explicitly used as in USE foo_db. In a GRANT or REVOKE statement, USE mysql is not executed, as can be seen from the binary log.

For row-based replication, if --replicate-ignore-db=foo_db, any direct change to a table in foo_db would not be replicated to the slave. But MySQL always uses statement-based logging for statements that indirectly change the tables of mysql database(GRANT, REVOKE, SET PASSWORD, ALTER, DROP, CREATE), regardless of the value of binlog_format.

So, to stop replicating any changes in mysql database to slave, you can set binlog-ignore-db=mysql in your master's configuration file to stop logging any changes in mysql database. You can also temporarily turn off sql_log_bin for your session and CREATE/ALTER the user before turning the variable back on again.


SQL server 2012 restore wizard freezes I have a development laptop with SSMS Express 2012 with a 2012 db instance and a 2008 db instance.  Have been using this configuration for over a year.  Suddenly I am unable to use the restore wizard.  The wizard will pick the backup file but when I select the "Files" option on the upper left to designate the location of the MDF and LDF the dialog hangs.  I tried a repair, no luck.

@Pat I have been facing the same issue for a long time, but a few minutes ago found way around it.

First of all DO NOT try to restore by right-clicking on an empty database. What you need to do is right-click on Databases and from the menu select Restore Database. In that UI you can use the Files option and the UI won't freeze.

Note: By doing this SQL will create your DB and restore into it in one go.

Hope it helps.


In Postgres, how do I cast character varying to an enum type? I have a Postgres 8.4 table with columns that are of type "character varying", but I want to convert them to an enum type.

When trying to alter the table

ALTER TABLE logs ALTER COLUMN interface_type TYPE interface_types
USING interface_type::text::interface_types;

I get an error

default for column "interface_type" cannot be cast to type interface_types

The default value for the column IS a text version of a value in the enum types. So it should be possible, though I've had no luck googling for the solution.

I assume that I need to define a cast for it. How?

I think I've found the answer: I need to drop the default first, and then re-add it.

ALTER TABLE logs ALTER COLUMN interface_type DROP DEFAULT;
ALTER TABLE logs ALTER COLUMN interface_type TYPE interface_types
  USING interface_type::text::interface_types;
ALTER TABLE logs ALTER COLUMN interface_type SET DEFAULT 'button';


mongodb setFeatureCompatibilityVersion command not found I'm trying to upgrade my mongodb from 3.2 to 3.4 and I try to set the compatibility version by:
db.adminCommand( { setFeatureCompatibilityVersion: "3.2" } )

But it says

{
        "ok" : 0,
        "errmsg" : "no such command: 'setFeatureCompatibilityVersion', bad cmd: '{ setFeatureCompatibilityVersion: \"3.2\" }'",
        "code" : 59
}

I'm not sure why the command is not found. Can somebody point me out in whats wrong?

Here is the mongodb version info from db.serverStatus().version

  3.2.20

P.S: I'm running the command on admin database and authenticated on it as a user with root role.

The documentation for setFeatureCompatibilityVersion shows it being new as of MongoDB 3.4, so it can't be run on a 3.2 server process:
https://docs.mongodb.com/v3.4/reference/command/setFeatureCompatibilityVersion/

The upgrade steps describe that running this command should be done after the server processes have been upgraded to 3.4.


Create rows when selected rows exist I haven't spent much time with SQL Server, and I can't seem to figure something out.

I have 3 tables; user, usergroup, group.

usergroup is a table that contains both a group id and a user id, where the group table just contains it's id and a name column.  I am attempting to add a new row in the usergroup table for lets say if the user is already paired with name = x in the group table I want to also pair him with y in the group table.

I'm sorry if that doesn't make too much sense I am not really sure how to explain it but the query I've come up with so far is:

 Insert into usergroup (user_id, group_id, default_group)
 Select user_group.user_id from [user_group]
      join [group] g
      on user_group.group_id = g.id
      where g.name = 'someName';

This should return all the user ids that I want to add a specific group_id/default value to, but I am unsure how to insert them into the table I tried to do something like VALUES ( (Select statement above), "group_id_I_want_to_specify, 'false'));

Your INSERT statement is malformed; you may want to try something like:

Insert into usergroup (user_id, group_id, default_group)
Select ug.user_id, 1234 AS group_id, ug.default_group
from [user_group] ug
  join [group] g on ug.group_id = g.id
where g.name = 'someName';

Replace 1234 in the query above with the id of the group you wish to add for each user who is a member of someName.

A fully flushed out test script for this, including the NOT EXISTS clause you referenced:

USE tempdb;

CREATE TABLE dbo.groups
(
    GroupID INT CONSTRAINT PK_groups
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , GroupName VARCHAR(255)
);

CREATE TABLE dbo.usergroup
(
    UserID int
    , GroupID int
        CONSTRAINT FK_usergroup_groups__group_id
        FOREIGN KEY REFERENCES dbo.groups(GroupID)
    , DefaultGroup bit
        CONSTRAINT DF_usergroup__default_group DEFAULT ((0))
    , CONSTRAINT PK_usergroup PRIMARY KEY CLUSTERED (UserID, GroupID)
);

INSERT INTO dbo.groups (GroupName)
VALUES ('test 1');

INSERT INTO dbo.groups (GroupName)
VALUES ('test 2');

INSERT INTO dbo.usergroup (UserID, GroupID, DefaultGroup)
VALUES (1, 1, 1);

DECLARE @NewGroupID INT;
DECLARE @ExistingGroupName VARCHAR(255);
SET @ExistingGroupName = 'test 1';
SET @NewGroupID = 2;

INSERT INTO dbo.usergroup (UserID, GroupID, DefaultGroup)
SELECT ug.UserID, @NewGroupID AS GroupID, ug.DefaultGroup
FROM dbo.usergroup ug
  INNER JOIN dbo.groups g ON ug.GroupID = g.GroupID
WHERE g.GroupName = @ExistingGroupName
    AND NOT EXISTS (
        SELECT 1
        FROM dbo.usergroup ug1
        WHERE ug1.UserID = ug.UserID
            AND ug1.GroupID = @NewGroupID);

As you may notice in the above code, I've used two-part naming to include the schema name when referencing table names.  Also, I've added two variables to the INSERT INTO portion of code to simplify making changes.

I've added USE tempdb; - once SQL Server is restarted tempdb is automatically recreated; effectively dropping the tables I create in this code.

I'm also not calling the group table group, since that is a reserved word.  Using it in code will be a recipe for confusing down-the-road.  I've named it groups in my code above, since that more accurately reflects the content of the table.

Also, I've renamed several of your columns to more accurately reflect the content of those columns.  For instance, you have an id column in the group table.  I've renamed it GroupID, and used that nomenclature everywhere.  This simplifies debugging.


Min function returning too many rows Apologies in advance if I am asking in the wrong forum.
I am trying to get the first attribute value for each ID in my table based on the ID's starting week. When using the min() function on weekkey, my query is returning more than one value if an ID has more than one attribute.
Drop table if Exists table1

CREATE TABLE table1 (
  IDNumber INT NOT NULL,
  Attribute VARCHAR(30) NOT NULL,
  WeekKey INT NOT NULL,
  );

INSERT INTO table1
    (IDNumber, Attribute, WeekKey)
VALUES
    (1,'a',1),
    (1,'a',2),
    (1,'b',3),
    (2,'c',1),
    (3,'a',1),
    (3,'c',2);

Select IDNumber, Attribute, Min(WeekKey) StartWeek
From Table1
Group by IDNumber, Attribute
Order by IDNumber, Attribute

My goal output would be:
1  a  1
2  c  1
3  a  1

My current output is:
1  a  1
1  b  3
2  c  1
3  a  1
3  c  2

Does anyone have an idea how to accomplish my goal without too many extra steps?
Thanks,
Taylor

You need to use a window function like ROW_NUMBER() to generate a unique ID sorted on the WeekKey partitioned by IDNumber.
WITH CTE_Table1_Sorted AS -- CTE of the table1 dataset with a SortId generated from the order of the WeekKey field
(
   SELECT IDNumber, Attribute, WeekKey, ROW_NUMBER() OVER (PARTITION BY IDNumber ORDER BY WeekKey) AS SortId -- Generates a unique ID for every record, in the order of WeekKey, from least to greatest
   FROM table1
)

SELECT IDNumber, Attribute, WeekKey AS StartWeek
FROM CTE_Table1_Sorted
WHERE SortId = 1 -- Filter out every other record except the one with the least WeekKey, per IDNumber
Order by IDNumber


Slow INSERT/UPDATE on InnoDB I have a problem with the performance of my MySQL server, let me explain:

I instaled a MySQL server, version 5.5.20

  Server version: 5.5.20-log Source distribution

SELECT queries runs fast on every table but INSERT/UPDATES queries run very slow, for example, for this table:

CREATE TABLE `sessions` (
  `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `session_expires` int(11) NOT NULL DEFAULT '0',
  `session_data` varchar(10000) COLLATE utf8_unicode_ci NOT NULL,
  `session_current_application` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'okn',
  PRIMARY KEY (`session_id`),
  KEY `INDEX_SessionExpires` (`session_expires`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I get this results:

mysql> insert into sessions values ('qweasd',1400,'data','test');
Query OK, 1 row affected (0.14 sec)

mysql> show profile;
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000057 |
| checking permissions         | 0.000005 |
| Opening tables               | 0.000016 |
| System lock                  | 0.000006 |
| init                         | 0.000008 |
| update                       | 0.000045 |
| Waiting for query cache lock | 0.000003 |
| update                       | 0.000008 |
| end                          | 0.000002 |
| query end                    | 0.140482 |
| closing tables               | 0.000021 |
| freeing items                | 0.000044 |
| logging slow query           | 0.000002 |
| cleaning up                  | 0.000003 |
+------------------------------+----------+
14 rows in set (0.00 sec)

Are this timestamps normal for a simple INSERT query?

The server has 16GB of RAM and 6 CPUs so i don't think the hardware is the problem.

What i tried:

Well, i read a lot about performance, and i check innodb_flush_log_at_trx_commit (https://stackoverflow.com/questions/6937443/query-end-step-very-long-at-random-times) i tried with 0 and 1 and times are similar, shorter with 0 but not much (from 0,13 to 0,09), and i think the insert should be faster.
Based on this thread i check my RAID but is a software RAID 1, so doesn't seem my problem comes from this.
Deactivate the query_cache, when i did it, the querys are even slower...

Query cache:

mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 67108864  |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 536870912 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

I'm totally lost and don't know what to do... any help will be greatly appreciated

PS: This thread could be related with this one

As you can see, the process that takes most time is "query end". There is an interlocking problem when multiple threads want to write the file at the same time, this way the log will be flushed every second:

innodb_flush_log_at_trx_commit = 0

in the /etc/my.cnf file

Also, you should try Optimize InnoDB as explained above for future query improvement.


SQL Server changes execution plan I have SQL code that is executing slow

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE @ccode

Why is SQL Server using nested loops?

This code is working fine, it uses only index scan:

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The slow query is executing 5.5 seconds, without the parameter it's 1.8 seconds.

This query is also working fine:

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE @ccode
OPTION (recompile)

The Seek
When the LIKE specification is in a variable:
WHERE DESCRIPTOR_TEXT LIKE @ccode

...SQL Server needs to build a plan that will work whatever the variable contains each time the cached execution plan is executed. It would also like to be able to seek if the variable contents are compatible. The solution is to use a dynamic seek, as I explain in the following article:

Dynamic Seeks and Hidden Implicit Conversions

This can result in a much better plan if the variable contains something that can be transformed to an efficient seek, which usually means no leading wildcards. If there is a leading wildcard, the dynamic seek may turn out to be less efficient than a simple scan with a residual LIKE predicate. For example, a full scan may issue more, and larger, read-ahead reads.
To be clear, a dynamic seek can still be used if the variable contains a leading wildcard, but the start and end points of the seek will cover the whole structure.
The Scan
Making the leading-wildcard explicit in a string literal makes it clear that a seek cannot be used, so you get a scan:
WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The downside is that this plan cannot be reused for different string literals, which may cause excessive plan cache usage.
The combination
Using a variable with the recompile option:
WHERE DESCRIPTOR_TEXT LIKE @ccode
OPTION (RECOMPILE)

...means SQL Server can 'sniff' the value of the variable at runtime, embed the value it finds into the query, and produce a plan optimized for that specific value.
The resulting execution plan will not be cached, so there is no possibility of plan reuse, but this also means you will not suffer from plan cache bloat either. There will be a small overhead on each call for the fresh compilation, but this is often a trade-off that makes sense: If the variable contains some seekable, you'll get a seek plan; otherwise, a scan. More details in my article:

Parameter Sniffing, Embedding, and the RECOMPILE Options


Why would a SQL Server 2008 R2 database disable a table index when upgraded to SQL Server 2012 I've restored a database backup in a new SQL Server 2012 server from a .bak made in SQL Server 2008 R2, and one of the tables has it's primary key index disabled. This doesn't happen when I restore to a 2008R2 server.

The structure of the table is:

CREATE TABLE RouteSegment (
   RouteSegmentID bigint IDENTITY(1,1) NOT NULL,
   Representation geography            NOT NULL,
   LastTime                            int NOT NULL,
   SourceStop1ID                       bigint NULL,
   SourceStop2ID                       bigint NULL,
   Length  as ([Representation].[STLength]()) PERSISTED,
   CONSTRAINT PK_RouteSegment PRIMARY KEY CLUSTERED (RouteSegmentID ASC)
 )

Is this a known problem in SQL Server 2012, or is there something I've missed?

I know that I can fix the problem with an index rebuild, I ask because it's surprising behavior, and want to know how to prevent it.

This has to do with any table which contains a geospatial field. I'm not sure why yet (is it a bug or a feature lol), but that's the cause.

Update: the problem only appears when there is a geospatial field and a PERSISTED column that uses it.


Default Schema Problems on server When i connect to SSMS on my server through remote desktop and query my database using:

SELECT * FROM [sys].[database_principals] WHERE [principal_id] = USER_ID()

I am getting the correct results (my name is DOMAIN\Glen and the type is WINDOWS_USER)

However when i connect to SSMS as a Administrator (right clicking and selecting run as administrator) i get the following results (name is dbo and type is SQL_USER)

This is a problem for me as in the users section under DOMAIN\Glen i have the default schema set. So when i run as administrator it is not picking up the correct tables.

Plus i am a little confused on why i am connecting as a SQL_USER when my authentication is set to Windows Authentication only.

The main reason i am trying to solve this is because when i run SSMS from my local machine it is connnecting in the same way as if i run as an administrator on the server and therefore i am unable to access my tables within the defined schema. I can not add this onto my procedures as they are all hard coded and there are over 1000.

Any help would be great.

Sorry to say it, but best practice is to always qualify object names with the schema anyway.

This is mandatory for things like schemabound views.

Why is it best practice? See this by Tibor Karaszi or this by Midnight DBA or just trust me or the MS SQL Server Best Practice Analyzer

After comment...

Have you considered synonyms to make OtherSchema.mycode point to [DOMAIN\Glen].MyCode?

CREATE SYNONYM OtherSchema.mycode FOR [DOMAIN\Glen].MyCode


Split 100 million row table into multiple tables I have a SQL Server 2012 database. There is one table that has more than 100 million rows. I want to split that table into multiple similar tables, with 1 million rows in each table. What is the best way to achieve this quickly?

I have tried paging, but it takes too much time, and sometimes gives an out of memory exception in SSMS.

There are 12 columns. 11 are varchar and one is datetime. There is no primary key available in the table, and no ID column. It is a very old database table that we migrated to SQL Server 2012.

Its size is around 20 GB. There are no indexes available. I don't want more complexity, I just need to split it.

Yes, it is possible to split this 100 million row table into any number of tables, even without it having any indexes. Now, you might could do it purely in T-SQL but that would require loading each of the split tables via individual insert statements in a transaction, and that can be a bit heavy on the tran log, especially if splitting to tables of 2 million rows each.
But, thanks to the totally awesome awesomeness of the fully streamed API for Table-Valued Parameters (TVPs), a small (and relatively simple) C# / VB.Net (or any .Net language) app can handle this, and do so without using more than 1 row's worth of memory (on the app side; buffer pool on the DB is what it is), and do so using a minimally-logged operation! The trick is to:

call a stored procedure that accepts a TVP for the rows to move, and
pass the rows into the TVP by setting the SqlParameter value to a method that returns IEnumerable<SqlDataRecord>. This will allow for incrementally moving through a SqlDataReader that is reading the source records.
Using this method, the size of the source table shouldn't matter: splitting a 1 billion row table should only require more time (and the obvious disk space required to duplicate the table).

Database Objects

First: You need a User-Defined Table Type

CREATE TYPE dbo.SplitTable AS TABLE
(
   CreateDate DATETIME NOT NULL,
   StringField01 VARCHAR(500) NULL,
   StringField02 VARCHAR(500) NULL,
   ...
   StringField10 VARCHAR(500) NULL,
   StringField11 VARCHAR(500) NULL
);
GO

GRANT EXECUTE ON TYPE::[dbo].[SplitTable] TO [user_or_role];
GO

Second: Use the UDTT as an input param to an import proc. Hence "Tabled-Valued Parameter" (TVP)

-- DROP PROCEDURE dbo.ImportChunk
CREATE PROCEDURE dbo.ImportChunk (
   @TableNumber   INT,
   @SplitTable    dbo.SplitTable READONLY
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX),
        @CurrentTableName sysname;

BEGIN TRY

   -- Empty TVPs are not allowed, and can't send DbNull.Value when using
   -- IEnumerable<SqlDataRecord>, so send in a single-row with a field to test that would
   -- otherwise never have that value. Only need to test 1 row as real sets of rows
   -- won't have this value so no need to test the whole set.
   IF ((SELECT TOP (1) [CreateDate] FROM @SplitTable) = CONVERT(DATETIME, 0))
   BEGIN
      RETURN;
   END;

   BEGIN TRAN;

   SET @CurrentTableName = N'dbo.SplitTableName_'
                           + RIGHT(N'000' + CONVERT(NVARCHAR(20), @TableNumber), 4);
   PRINT 'CurrentTableName: ' + @CurrentTableName;

   SET @SQL = N'CREATE TABLE '
              + @CurrentTableName
              + N' (
CreateDate DATETIME NOT NULL,
StringField01 VARCHAR(500) NULL,
StringField02 VARCHAR(500) NULL,
...
StringField10 VARCHAR(500) NULL,
StringField11 VARCHAR(500) NULL
);';

   EXEC(@SQL);

   -- Use a SYNONYM as we are dealing with dynamic table names which requires Dynamic SQL
   -- but the TVP is a table variable and cannot be referenced in Dynamic SQL. However,
   -- a SYNONYM can be created in Dynamic SQL and is a consistent object name that can
   -- be used in the static / main portion of this code where the TVP can be used.
   -- http://msdn.microsoft.com/en-us/library/ms177544.aspx (CREATE SYNONYM)
   SET @SQL = N'CREATE SYNONYM CurrentImportTable FOR '
              + @CurrentTableName;

   EXEC(@SQL);

   -- The WITH (TABLOCK) hint is required for this query to be considered minimally logged.
   -- http://msdn.microsoft.com/en-us/library/ms174335.aspx (INSERT)
   INSERT INTO CurrentImportTable WITH (TABLOCK)
      (CreateDate, StringField01, StringField02, --...,
      StringField10, StringField11)
      SELECT tmp.CreateDate,
             tmp.StringField01,
             tmp.StringField02,
             ...
             tmp.StringField10,
             tmp.StringField11
      FROM   @SplitTable tmp;

   DROP SYNONYM CurrentImportTable; -- Clean up.

   COMMIT TRAN;

END TRY
BEGIN CATCH

   IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK;
   END;

   DECLARE @Message NVARCHAR(4000);
   SET @Message = ERROR_MESSAGE();
   RAISERROR(@Message, 16, 1);

END CATCH;
GO

GRANT EXECUTE ON dbo.ImportData TO [user_or_role];

C# Code

Third: Open a SqlDataReader to a simple select on the table in question (essentially a cursor). The trick is to implement a method that will

read a set number of rows rather than until the end of the result set
pass each row, as it is read, to the TVP

Then just call the stored procedure shown above passing in the loop counter (so we can name the new table appropriately) and the method that returns only that set number of rows via IEnumerable. Keep calling the procedure until there are no more records in the result set / SqlDataReader.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

namespace TableSplitter
{
    class Program
    {
        // these first two variables are static as their values need to be passed back to
        // the main loop but iterators (i.e. returning IEnumerable) cannot have "ref" parameters
        private static SqlDataReader _RowsToSend;
        private static int _RowsCopied;

        private static int _RowsPerSplit;

        private static IEnumerable<SqlDataRecord> SendRows()
        {
            SqlMetaData[] _TvpSchema = new SqlMetaData[]
            {
                new SqlMetaData("CreateDate", SqlDbType.DateTime),
                new SqlMetaData("StringField01", SqlDbType.VarChar, 500),
                new SqlMetaData("StringField02", SqlDbType.VarChar, 500),
                //...
                new SqlMetaData("StringField10", SqlDbType.VarChar, 500),
                new SqlMetaData("StringField11", SqlDbType.VarChar, 500)
            };

            SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);
            object[] _AllFields = new object[12];

            // read a row, send a row
            for (_RowsCopied = 0; _RowsCopied < _RowsPerSplit; _RowsCopied++)
            {
                if (!_RowsToSend.Read())
                {
                    break;
                }
                _RowsToSend.GetValues(_AllFields);
                _DataRecord.SetValues(_AllFields);

                yield return _DataRecord;
            }

            if (_RowsCopied == 0)
            {
                _DataRecord.SetDateTime(0, new DateTime(1900, 1, 1));
                yield return _DataRecord;
            }
        }

        public static void SplitTable(string ConnectionString)
        {
            _RowsCopied = _RowsPerSplit; // seed value to enter the loop

            int _TableNumber = 1;

            SqlConnection _SourceConnection = new SqlConnection(ConnectionString);
            SqlCommand _SourceCommand = new SqlCommand(
                "ALTER DATABASE [Test] SET RECOVERY BULK_LOGGED;",
                _SourceConnection);
            _SourceCommand.CommandType = CommandType.Text;

            SqlConnection _DestinationConnection = new SqlConnection(ConnectionString);
            SqlCommand _DestinationCommand = new SqlCommand(
                "dbo.ImportChunk",
                _DestinationConnection);
            _DestinationCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter _TVParam = new SqlParameter();
            _TVParam.ParameterName = "@SplitTable";
            _TVParam.SqlDbType = SqlDbType.Structured;
            _DestinationCommand.Parameters.Add(_TVParam);

            SqlParameter _TableNumParam = new SqlParameter();
            _TableNumParam.ParameterName = "@TableNumber";
            _TableNumParam.SqlDbType = SqlDbType.Int;
            _DestinationCommand.Parameters.Add(_TableNumParam);

            try
            {
                _SourceConnection.Open();
                _SourceCommand.ExecuteNonQuery();

                _SourceCommand.CommandText =
                    @"SELECT CreateDate, StringField01, StringField02, --...,
                    StringField10, StringField11 FROM dbo.TableToSplit;";
                _RowsToSend = _SourceCommand.ExecuteReader();

                _DestinationConnection.Open();

                while (_RowsCopied == _RowsPerSplit)
                {
                    _TableNumParam.Value = _TableNumber;
                    _TVParam.Value = SendRows(); // method return value is streamed data

                    _DestinationCommand.ExecuteNonQuery();

                    _TableNumber++;
                }
            }
            catch (Exception _Exception)
            {
                System.Console.WriteLine("\n\n" + _Exception.Message + "\n\n");
            }
            finally
            {
                _RowsToSend.Close();
                _DestinationConnection.Close();

                _SourceCommand.CommandText =
                    "ALTER DATABASE [Test] SET RECOVERY SIMPLE;"; // or FULL
                _SourceCommand.ExecuteNonQuery();

                _SourceConnection.Close();
            }

            return;
        }

        static void Main(string[] args)
        {
            System.Diagnostics.Stopwatch _ElapsedTime = new System.Diagnostics.Stopwatch();
            _ElapsedTime.Start();

            _RowsPerSplit = 1000000;

            SplitTable("Trusted_Connection = yes; Database = Test;");

            _ElapsedTime.Stop();

            System.Console.WriteLine("\n\n\tElapsed Millseconds: {0}\n\n",
                                      _ElapsedTime.ElapsedMilliseconds);
        }
    }
}

I ran this on my laptop with a sample table consisting of:

5 fields: 1 DATETIME and the other 4 are VARCHAR
3 million rows
205 GB

I ran it several times and it took about 19 - 24 seconds to split into 3 tables of 1 million rows each.


Is it possible to perform clustering when short of free space on disk? According to v12 docs

Cluster with index scan requires

  free space on disk at least equal to the sum of the table size and the
  index sizes

and cluster with sequential scan and sort requires

  as much as double the table size, plus the index sizes

Is there any way out if I do not have that much free space? i.e. can it be run in portions somehow?

If I run CLUSTER command and it fails because of insufficient free disk space will the performed work be entirely rolled back (when run with or without any parameter if it matters)?

yes postgresql would roll the transaction back.

Almost all commands in Postgresql are run in transaction so if something goes wrong the command will be rolled back.

If the Postgresql system tables and WAL are on the same disk do not run this command if disk space is this limited.  Postgresql would panic and shutdown until space is freed up.

A Few things to keep in mind,

Postgresql will not keep the table in that order so if the table is heavily updated. The physical sort order will be lost very quickly due to MVCC
Have to run the cluster command on a regular bases to keep the table's physical order which recreates the limited disk space problem.
The advantage for clustering is for read only queries that select record set in the order of the cluster.

If disk space is limited and the table is updated/inserted regularly in a short period of time the table and indexes will grow to a point the cluster can not be rerun and vacuum will not be able run

looks like its time to start planning on adding disk space and moving these tables to new TableSpaces


What is the difference between Completion and Success in SQL Server Maintenance Plans? Using SQL Server 2008/R2 Maintenance Plans to setup some automated backups/cleanups and notifications. One thing that I can't figure out is what is the difference between Success and Completion?

The way I think about it, is that completion doesn't care if the job failed or succeeded, but if it failed, then obviously it didn't complete maybe??

Perhaps its left there in case of scripts where there might not be a way to tell what the actual outcome was, just that the script completed, but this still feels like its the same as success and failed.

I couldn't find much documentation for it either.

The execution result, in your case, success, completion, or failure is a precedence link that can create a relationship between two tasks. From BOL

  A precedence link between two tasks establishes a relationship between the tasks. The second task (the dependent task) executes only if the execution result of the first task (the precedent task) matches specified criteria. Typically the execution result specified is Success, Failure, or Completion

If you wanted to say, have your plan execute a statement that was not designed around success or failure you could use completion to establish a relationship and proceed to the next task.

Example: Purge all records of type 'A' from database [ABC], then execute a database backup
 - You don't care how many records, if any, were found or deleted. Just run the statement. When it is done, back up the database

Example: Reorganize the index's of database [ABC] only after successfully checking the integrity. If the integrity check fails, do not reorganize the index's

Maint. Plans

Precedence Constraints


Oracle Enterprise Manager font problem, some text is displayed in Chinese and with squares In Oracle Enterprise Manager 11g database control in the following section:

  Setup->Monitoring Templates->"Choose template name and click Edit
  button"->Policies tab

Texts are displayed in Chinese language and squares.

Like this:

  䅵摩琠䙩汥⁄敳瑩湡瑩潮

  䝲慮瑩湧⁓䕌䕃吠䅎夠呁䉌䔠偲楶楬敧

  䑩獫*⁇*牯異⁃潮瑡楮猠䑩獫猠潦⁓楧湩晩捡湴汹⁄楦晥牥湴⁓楺敳

How to solve? I want these words to see in English. Please help.

  I found now that some words contains question marks

Thank you.

Oracle Enterprise Manager Grid Control Advanced Installation and Configuration Guide 11g has the following section:

  How can you change the language used in Enterprise Manager Grid Console?

  By default, the Enterprise Manager Grid Control Installation Wizard
  installs Enterprise Manager Grid Control console with English
  language. If you want to view them in another language, then select
  the required languages in the Select Languages screen of the
  installation wizard.

    Note: The language you select here changes the language used in the
    Enterprise Manager Grid Control console screens only; it does NOT
    change the language used in the installation wizard screens. The
    language of the installation wizard screens is based on the operating
    system language.

  When you select additional languages to install, the installation
  wizard also installs fonts required to display the languages.

  For some components, languages are installed only if you select them
  during installation. In this case, if you access the application in a
  language that is not available, it reverts to the locale language.

  For other components, available languages are installed regardless of
  what you select during installation. In this case, however, fonts are
  installed only for the languages that are explicitly selected. When
  you access the application, it uses text in your language because the
  language was installed. However, if you do not have the appropriate
  fonts to render the text, the text appears as square boxes. This
  usually applies to the Chinese, Japanese, and Korean languages.


Create GIN index in a schema with the pg_trgm extension I'm migrating entities from the public schema into a new one, let's call it myschema. Originally, the pg_trgm extension was installed and the index was created in a schema migration with:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX mytable_gin_index_on_mycolumn
    ON mytable USING gin (mycolumn gin_trgm_ops);

To do the same within myschema I modified the migration to look like this:
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA myschema;
CREATE INDEX mytable_gin_index_on_mycolumn
    ON mytable USING gin (mycolumn gin_trgm_ops);

However, index creation fails with the following error:

operator class "gin_trgm_ops" does not exist for access method "gin"

I tried namespacing the operator class:
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA myschema;
CREATE INDEX mytable_gin_index_on_mycolumn
    ON mytable USING gin (mycolumn myschema.gin_trgm_ops);

but got the same error:

operator class "myschema.gin_trgm_ops" does not exist for access method "gin"

I also tried to install the extension without specifying the schema, which yields the same error.
What is the right way to use the pg_trgm extension in a schema other than public?

You can adjust the search_path before running the DDL statements:
set search_path = myschema,public;


SQL Server 2008 Script Changes from Database Diagram I have a database diagram which I have made several changes to using SSMS, however instead of committing the changes I want to generate a SQL script to apply them instead. Is there a simple way to do this using the GUI?

I have done a Google search, but could not find the answer.

More Google searching did not help, but after fiddling around with SSMS I managed to figure it out. Thought I'd answer my own question for anyone else who might stumble across this.

To script all of the changes at once, simply right-click a blank area of the diagram and click 'Select All' (or press Ctrl+A). Then right-click one of the selected tables and choose 'Generate Change Script...' at the bottom.


How can I perform a full-text CONTAINS call across a list of phrases? I have a list of words/phrases in a table phrases. I have another table with a full-text catalog indexing the description column.

I want to populate a table results with a record for each phrase, and the number of times that phrases was found in the description:

INSERT INTO results(phrase, foundcount)
SELECT  phrase,
    (SELECT COUNT(*) FROM table WHERE CONTAINS(table.description, phrase))
FROM    phrases;

No matter how I try, this results in the error:

Incorrect syntax near `phrase`. Expecting STRING, TEX_LEX, or VARIABLE

I would really rather not use a cursor just so I can assign each phrase to a variable. Is that really the only option?

Try this:

go
create function dbo.fn_GetCountForPhrase (@phrase varchar(500))
    returns  int
as
begin
    declare @count int
    declare @localphrase varchar(8000)
    set @localphrase='"' + @phrase + '"'
    SELECT @count=COUNT(*) FROM table  a WHERE CONTAINS(table.description,   @localphrase)
    return @count
end
go
SELECT
    phrase,
    dbo.fn_GetCountForPhrase(phrase)
FROM phrases


Does MVC architecture design pattern effect MySQL database performance? Our current site is entirely developed in the cakePHP framework with the MVC architecture. If we notice more than around 19-20 users viewing the site at once, it begins to crawl to a hault and begin to time out.

Could this be a performance issue with MySQL? Possibly related to MVC - as the site relies heavily on communication with the SQL database. If so, would increasing the overall RAM help solve the issue?

Thanks.

Config:

open-files-limit                = 20000

#sql-mode                       = TRADITIONAL

#event-scheduler                = 1

### Cache
thread-cache-size               = 16
table-open-cache                = 2048
table-definition-cache          = 512

query-cache-size                = 32M
query-cache-limit               = 1M

### Per-thread Buffers
sort-buffer-size                = 1M
read-buffer-size                = 1M
read-rnd-buffer-size            = 8M
join-buffer-size                = 1M

### Temp Tables
tmp-table-size                  = 64M
max-heap-table-size             = 64M

### Networking
back-log                        = 100
max-connections                 = 300
max-connect-errors              = 10000
max-allowed-packet              = 16M
interactive-timeout             = 600
wait-timeout                    = 60
net_read_timeout        = 30
net_write_timeout       = 30
# This value is the size of the listen queue for incoming TCP/IP connections.
back_log            = 128

#### Storage Engines
#default-storage-engine         = InnoDB

## Makes sure MySQL does not start if InnoDB fails to start. This helps
## prevent ugly silent failures.
innodb                          = FORCE

### MyISAM
key-buffer-size                 = 64M
myisam-sort-buffer-size         = 128M

#InnoDB
innodb-buffer-pool-size         = 16M
innodb-log-buffer-size          = 4M
innodb-log-files-in-group      = 2

server-id                       = 1

[mysqld-safe]
log-error                       = /var/log/mysqld.log

[mysqldump]
max-allowed-packet      = 16M

We use cake for some internal applications.  And yes it does at times, pardon my french, suck.  This isn't anything to do with mysql per se.  Once you start defining more than basic models and go down the "has many"/"has many belongs to many" relationships, the underlying queries it generates can be less than sub par.

Do you have table with multi column primary keys? Forget about it.

Frankly there are just times you need to break the molding your framework is supposed to shield you from and write your sql more directly :-\

That being said, as with anything else interacting with your DB you'll want to ensure you have proper indexes and appropriate buffer pool/key buffer.


comparing two time values with 2 column respectively after converting in 24 hour format Hi I have two varchar column with time values store in 12 hour format. I want to get the output like rows that which don't fall in 10:00 am to 12:00 pm

here

id  |time1     | time2
____|__________|__________
1   |01:00 pm  | 10:00 pm
2   |11:00 am  | 01:00 pm
3   |09:00 am  | 11:00 am
4   |05:00 pm  | 11:59 pm

output will be

1   |01:00 pm  | 10:00 pm
4   |05:00 pm  | 11:59 pm

here is the link of sqlfiddle to test
http://sqlfiddle.com/#!9/b6bf48/2

Based on the example output I'm assuming the requirement is to display rows where the time1-time2 range falls completely outside of the desired 10:00am-12:00pm range.

Assumption:

time2 >= time1

This is going to be easier if we convert the strings to times and let the database do the time comparisons for us.

In MySQL we can convert the given strings to (date)times via the following construct:

str_to_date(<string_value>,'%h:%i %p')

The rows we're looking for should match one of the following tests:

time1 > 12:00 pm
time2 < 10:00 am

One possible solution:

select time1,
       time2
from   splash
where  str_to_date(time1,'%h:%i %p') > str_to_date('12:00 pm','%h:%i %p')
or     str_to_date(time2,'%h:%i %p') < str_to_date('10:00 am','%h:%i %p')
order by 1,2;

time1      time2
========   ========
01:00 pm   10:00 pm
05:00 pm   11:59 pm

Was having some timeout issues with the OPs fiddle so here's a rextester fiddle for the above solution.


Migrate SSRS Reports to Power BI Report Server I have to servers: one with the SSRS Report Server and a second server with the Power BI Report Server.

The issue is I'm trying to migrate the SSRS Reports to the Power BI Report Server instance on a third server that I plan to unify both report databases.

As far as I understood based on a Microsoft article (Migrate a report server installation), it's possible to migrate the SSRS database to the Power BI Report Server as shown in the picture to the same instance and service (Power BI Report Server in this case):

I've created the backup of the SSRS databases (and encryption key) and restored on the PBI server, but it's not working. If I restore only the PBI database (and its encryption key) it works fine on the destination server. On the Report Server Configuration Manager I can only set the ReportServerPBI (that I restored from the server that already runs the PBI) to have it working. If I set the ReportServer database that I've had made the backup it doesn't work.

Am I mistaken or is there any way that I can put these services in the same server/database and running under the Power BI Report Server?

Will I have to keep separate instances to have both dashboard types running?

...is there any way that I can put these services in the same
  server/database and running under the Power BI Report Server?

You can have SSRS and PBIRS on the same Server, but they will require their own ReportServer databases in order to run them both.

If you want to migrate your SSRS reports into PBIRS, then you need to follow the steps in the MS article. 1. Restore a copy of the SSRS database to a new server, and then 2. install PBI RS. PBI RS will update the database and retain the reports within it.

I think the article gets a little confusing as most people reading it will be migrating from SSRS to PBIRS on a different SQL Server. There isn't a lot of info about running both side by side. But it can be done.


How to sync MySQL's time with system time without mysql restart? On my machine I have a service to synchronise the SYSTEM time (including Time zone), and would like the DB will synchronize with the machine time once in a while.

When I change SYSTEM time, MySQL keep giving the old time when I use CURRENT_TIMESTAMP, It synchronize it only when I restart the MySQL service.

I would like to ask: is it possible, by update some system variable, or anything else, to set the DB time, to be equal to the SYSTEM time, or any other time, without a restart of the service?

I don't want to stop the DB at all. I think that maybe a sceduled task, which run once in a while, can be set, but what should it do?

To accept answer, it must pass the following test:

# change system time, date or timezone on the machine
select CURRENT_TIMESTAMP from dual; #--still show old time
call sync_time; #or some sync date + time + timezone commands...
select CURRENT_TIMESTAMP from dual; #--will show updated time

I just need the sync_time procedure, that will cause the last select to show the machine date and local time.

@rathishDBA answer didn't helped at all, it only changed the MySQL variables, not sync the DB time. I tried it as the 'root' user and both first and last select gave me the same unsynced result.

For example see this picture:

System time = 2:07
CURRENT_TIMESTAMP = 11:37

This solution worked for me, I hope it will work for you too:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-10-26 15:13:16 |
+---------------------+
1 row in set (0.07 sec)

mysql> SET GLOBAL time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.07 sec)

mysql> show global variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | SYSTEM              |
+------------------+---------------------+
2 rows in set (0.18 sec)

mysql>

You need SUPER privilege to set this configuration value on run time to avoid system restart.


PDO returning 0 rows on WHERE with enum I am trying to build a query for a report. The report will have a few fields that can be filtered, one of which is a jobStatus field, it is an enum.

Here is my query

SELECT * FROM jobs j
LEFT JOIN property p ON j.propertyID = p.propertyID
LEFT JOIN property_buildings b ON b.buildingID = j.buildingID
LEFT JOIN departments d ON j.dptID = d.dptID
LEFT JOIN jobs_timeframe jt ON j.jobTimeframeID = jt.jobTimeframeID
LEFT JOIN jobs_type t ON j.jobTypeID = t.jobTypeID
LEFT JOIN floors f ON f.floorID = j.floor
LEFT JOIN (
    SELECT group_concat(a.assetNo) AS assets, ja.jobID
    FROM jobs_assets ja
    INNER JOIN assets a ON a.assetID = ja.assetID
    GROUP BY jobID
    ORDER BY assetNo ASC
) ja ON ja.jobID = j.jobID
LEFT JOIN (
    SELECT group_concat(o.obsNo) AS observations, oj.jobID
    FROM observationJobs oj
    INNER JOIN observation o ON o.obsID = oj.obsID
    GROUP BY jobID
    ORDER BY obsNo ASC
) jo ON jo.jobID = j.jobID
WHERE j.ClientID = :clientID AND j.jobStatus = :status
LIMIT :limit OFFSET :offset

Here are my bound parameters:
:clientID => 14,
:status => 'Draft',
:limit => 25,
:offset => 0

If i exclude the j.jobStatus it works fine and shows 19 results with a mix of jobStatus' including Draft, Finished and Approved If i include try and filter by j.jobStatus at all using PDO i get a successful query but with zero rows (should be 14 rows for Draft, 1 for Approved, 3 for Finished and 1 null)

if i unsafely just put the variable for :status in the query
(like this WHERE j.ClientID = :clientID AND j.jobStatus = '.$status.' LIMIT :limit OFFSET :offset)
it works fine and shows the 14 expected results. I can not for the life of me figure out why.

The only thing I can think of is it is something to do with it being an enum, but i was under the impression that they are treated as strings for everything except the actual storage.

EDIT:
binding code:

$stmt->bindParam(':limit', $lim, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':status', $params[':status'], PDO::PARAM_STR);
$stmt->bindParam(':clientID', $params[':clientID'], PDO::PARAM_INT);

Turns out it was the foreach loop i was using to bind the parameters as seen in this issue on stackoverflow

for anyone who comes across a similar issue in the future the following will fail:

foreach($params AS $k => $v){
    $stmt->bindParam($k, $v);
}

as bindParam does not actually bind it when you call it, it just binds a link to $v so it will always be the last item it the loop. to overcome this, you need to pass the argument by reference as follows:

foreach($params AS $k => &$v){
    $stmt->bindParam($k, $v);
}

notice the & before $v in the foreach, cause it to work as expected.


MySQL detect duplicate human names I've been tasked with identifying a solution to help identify possible duplicates when inserting new user records into a MySQL database. Does anyone know of any cheap, preferably free, solutions?

The sort of duplicates I need to detect are common forms of shortened English names - such as Bill for William, Bob for Robert etc - along with misspellings as well as plain duplicates. I've read a bit about using Lucene but it seems to be more for full text searching and I'm unsure if it supports the dupliatcae name matching I'm after?

This might be better placed on stack overflow.  This isn't something you'll accomplish with MySQL.  What you're talking about is referred to as 'stemming' in search.  Similar to matching different conjugations of a regular word e.g. run => runs ,ran.

I don't know of any such applications for proper names off hand but when you find one that will sit alongside your primary application to "normalize" the name before inserting the record into your database.  Mysql, sqlserver, mongo, whatever.  The DB technology is irrelevant as your task is out side the scope of storing data/documents.

Lucene would be a better tool for your task.  But I couldn't speak to it's prepackaged ability to stem names like you want.

Edit

After thinking about it I think I misspoke when I said Lucene would be a "better" approach in of itself for what you want.  My understanding is stemmers exist outside of core lucene and then proxy a search for "bob" into ("bob" or "robert") to feed into the lucene engine.


Store billions of rows with no relation I'm going to receive a large group of CSV files daily. Additionally I have 1 billion records of data (from those CSV files) but it's not a fixed number (1b records daily). They're going to be growing and I need to store them in a DB. Also there are some extra points:

There's no update
There's no join and relationship
Select a bunch of rows and group by
Write intensive is 9 times more than read (we don't need to read data it must be a storage of data)
I don't need normalisation

I had a bench between MySQL InnoDB and MyISAM. MyISAM was more better than InnoDB (because I have no normalisation) but MySQL is not a good approach because I have no relation.

Also I've checked MongoDB finally it took 50GB of data and used 150Gb of storage disk!

So I think I need a NoSQL DB which can do distributed write and support above extra points. What do you prefer?

I know I can use CSV files but I need a db approach not a file approach. I have a lot of tools and advantages like version update, bug fixes, security stuff, read and write performance, replica, etc in a database. I cannot have something like group by (or where, ...) in CSV files and I have no time to implement them with CSV files.

I did something like this with MySql and you can save directly CSV files in MySQL. I think Apache Cassandra is a good choice for you

  CQL3 is very similar SQL, but with some limitations that come from    the scalability (most notably: no JOINs, no aggregate functions.)
  CQL3 is now the official interface. Don't look at Thrift, unless    you're working on a legacy app. This way, you can live without
  understanding ColumnFamilies, SuperColumns, etc.
  Querying by key, or key range (secondary indices are also available)
  Tunable trade-offs for distribution and replication (N, R, W)
  Data can have expiration (set on INSERT).
  Writes can be much faster than reads (when reads are disk-bound)
  Map/reduce possible with Apache Hadoop
  All nodes are similar, as opposed to Hadoop/HBase
  Very good and reliable cross-datacenter replication
  Distributed counter datatype.
  You can write triggers in Java.

You see the difference between NoSQL databases here
http://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis


Filtering records not present with specific conditions in another table I have a table (let's call it extra_info) in MySQL that has various extra information/labels on user accounts:

account_id / data_key
11 / 'test'
11 / 'blah'
12 / 'coupon a'
12 / 'coupon b'
15 / 'whatever'
18 / 'test'

And so on.

I am building reports on other tables but need to filter out records that are present in the extra_info table as 'test'.

So in the example above, records 1-10, 13-14, 16-17 are fine because they are not in extra_info, 11 needs to be filtered out (it is 'test'), 12 is fine (but obviously just need to have it once even though is has 2 labels, 15 is fine (not 'test'), and 18 needs to be removed.

I have done this with subquery in the WHERE clause:

WHERE
    -- filter out test labels
    a.id NOT IN (SELECT ei.account_id
                 FROM extra_info AS ei
                 WHERE ei.data_key = 'test')

This does its job, however, I have a suspicion that a regular join would have worked faster. What's the right syntax for it?

If I just join and filter out the ones with data_key='test', #11 would still be there - so this is not a solution. Grouping by account_id will probably has performance implications and basically same impact as the current code, right?

Any ideas?

One option would look something like this:

SELECT a.* FROM account a
  LEFT JOIN extra_info ei ON ei.account_id = a.id AND ei.data_key = 'test'
 WHERE ei.account_id IS NULL;

This might seem counter-intuitive, since "obviously" er.account_id is never actually NULL sitting at rest in the table, so I'll explain:

The LEFT JOIN of course means all rows from account and only the matching rows from extra_data... and by "matching rows" we mean those having an identical account_id and a value of 'test' in data_key.

Every row in a result set has, at some point during execution, either a value (or a NULL) for every column from all tables, even if those columns aren't listed in the SELECT list... so, in a LEFT JOIN where the table on the right has no corresponding rows, the columns in the result set that "came from" the right-hand table are NULL... so, WHERE ei.account_id IS NULL filters out the rows with a value for ei.account_id (the ones with matching account_id and 'test' in data_key) leaving you with a result set containing the rows that you want.

If it's not apparent now, it makes more sense when you remember that the WHERE clause is not telling the server which rows to find in this case... it's specifying which rows not to eliminate.

Now, if you need other data from extra_info in your report, you will need to [left] join that same table again in your query with different aliases in addition to ei in order to pluck out the other values.

@ypercube's advice on indexes still stands... in the case of the query in the question, an index on (data_key,account_id) would probably be more useful, since the subquery isn't correlated to anything in the outer query, while in the LEFT JOIN query, this index would be what you'd want:

[UNIQUE] KEY(account_id,data_key)


How to select events based an adjacent times? I have a table rooms which contains information regarding events that take in rooms. This is the definition of the table:

create table room(
  sdate date,--start date
  stime time,--start time
  rno int,--room no
  bno int,--building no
  eid int,--event id
  duration interval,
  primary key(sdate, stime, rno, bno, eid)
);

I want to find all events that are adjacent to one another, that is if event x finishes at 10:00 and event y starts at 10:00 (provided they're in the same room, building and date of course) these events are adjacent in terms of time.

I'm new to SQL and I wrote a query to find such events using 5 subqueries and one union which seems to me to be over the top. I'd like to learn the best practices therefore is there a better, more concise way to perform such a query?

This is the query:

with
  --calculate end times (etime)
  schedule_w_etime(sdate, stime, rno, bno, etime) as (
    select sdate, stime, rno, bno, (stime + duration) as etime from schedule
  ),
  --find adjacent events
  result(sdate, stime, rno, bno, etime) as (
    select * from schedule_w_etime as S
    where S.etime in (select stime from schedule_w_etime as T where S.sdate=T.sdate
                                                                    and S.rno=T.rno
                                                                    and S.bno=T.bno)
  ),
  --get the last event from result
  max_time(date, start_time, room_no, build_no, end_time) as (
    select * from result
    where etime=(select max(etime) from result)
  ),
  --lookup the start time of the last event
  lookup_max_time(sdate, stime, rno, bno, etime) as (
    select * from schedule_w_etime as S, max_time as M
    where S.stime=M.end_time and S.bno=M.build_no and S.rno=M.room_no
  ),
  --format lookup_max_time to only relevant columns
  last_event_final(sdate, stime, rno, bno) as (
    select sdate, stime, rno, bno from lookup_max_time
  )
  --union of the last event and all the other events from result
  (select * from last_event_final)
    union
  (select sdate, stime, rno, bno from result)

As you can see my main problem is with displaying the last event because in the result view doesn't contain the start time of the last event.

You may simply do a self-join, like:

SELECT r1.sdate, r1.rno, r1.bno, r1.eid,
       r2.* -- choose the r2 fields that are needed
FROM room r1 JOIN room r2
 ON (r1.rno=r2.rno and r1.bno=r2.bno and r1.sdate=r2.sdate
   and r1.stime + r1.duration = r2.stime);

This answers the query: what are the adjacent events?, by returning event x and event y where y follows x, as a relationship.

The proposed SQL statement in your question answers a question that seems slightly different, like: which events are involved in an adjacency relationship? The structure of the result in this case would be one row per such event, then.

One way of getting that is to build it on top of the above query and extract the r1's and r2's in separate rows, UNIONing the result (including the deduplication that UNION does). For example:

WITH list AS (
  SELECT r1.sdate, r1.rno, ...
         r2.sdate AS r2_sdate, r2.rno AS r2_rno, ...
  FROM room r1 JOIN room r2
   ON (r1.rno=r2.rno and r1.bno=r2.bno and r1.sdate=r2.sdate
     and r1.stime + r1.duration = r2.stime)
)
SELECT sdate, rno, ... FROM list
UNION
SELECT r2_sdate, r2_rno, ... FROM list


How to restore a MySQL file to SQL Server 2008 I have a MySQL file (apple.sql) that I need to restore to a SQL server 2008 database or at least be able to access the database through mySQL. I have created a linked server mySQL, created an empty database on mySQL. I am not sure how to restore the backup file (apple.sql) to the empty database?? Any ideas?

You should download and install MySQL MSI on the Windows machine

By default, root@localhost has no password

Once mysqld.exe appears in the Taks Manager's process list, run the following

C:\> net start mysql
C:\> mysql -uroot -e"CREATE DATABASE apple"
C:\> mysql -uroot -Dapple < apple.sql

Database will be loaded in MySQL

You can check everything afterwards. First, login to mysql

C:\> mysql -uroot

Then run these client commands

mysql> use apple
mysql> show tables

You can set up DTS or any other mechanism for migrating from MySQL to SQL Server


Handle concurrency on Sql Server to modify specific column I have table that suffers from racing condition and I don't know how to fix it. I can't solve it 100% in code, so I need the server to help me.

The scenario

Admins of a system may accept or reject an appointment.
This can be done by email (via token etc) or by logging in the system and doing it via admin panel.

The problem appears when two admin do it at the same time:

t = 0:
admin A clicks accept from the system email;
admin B clicks reject from the admin panel;

t = 1:
back-end checks was appointment already handled?
This happens at the same time for both admins, so they both
       pass the test -> appointment is waiting for approval...

t = 2:
back-end A sends query "update status to accepted"
back-end B sends query "update status to rejected"

t = 3: both sqls hit the server.
The server than changes the appointment to accepted, then proceeds to execute the second query, and changes the appointment to rejected.

This table has heavy traffic, so locking it is not an option.

What I think could solve my problem, is to:
first: have a query lock the row;
second: check the the status column to see if it is already set;
third: if not set, then set the value;
fourth: release the lock;

meanwhile, the second query would keep trying to acquire the lock. After it succeeded, it would then proceed to check whether the value was already set, and then it would find that indeed it was, and would return an error or something.

How can I go about doing something like this on Sql Server?

The flow would look something like this:

while (can't read) {

    try to acquire lock;
}

// lock acquired.
read appointment from table;
if value is null, set the value;
release lock;

I figured out my framework is using READ_COMMITTED_SNAPSHOT as default.

We had a similar requirement and so added an extra where clause to the query. E.g.

UPDATE AppointmentTable
SET STatus = [Approved/Rejected]
WHERE AppointmentID = @ID AND Status = Pending

Then afterwards, check how many rows were updated. If there were 1 rows updated then report back to the user that their action was recorded. If 0 rows were updated then report back to the user that someone else has already done the work.

  Isn't there a chance the queries will be executed "at the same time"

Nope. If they execute "at the same time" one of them will still get the update lock first. The other will have to wait for the update lock. When the 2nd gets the lock it still has to check the where clause to validate it's still true. If it's no longer true then it skips that row and no update happens (@@rowcount = 0). The key is to have the old value in the where clause.


Managing Permissions on a server with new DBs being added I'm currently dealing with a database where multiple databases will be added in the future. Right now, I'm working on creating some users with specific permissions and I'm struggling with how to ensure that they can also cover the databases that aren't yet added. Complicating things, most of these permissions are needed only databases ending in a specific suffix.

For example, a user needs to be created that can add assemblies to all the current and future databases that end in ABC.

Is there a way to make this easier to manage, or will it involve always ensuring after a new set of DBs are added that the user has access to the ones ending in ABC?

One possible option would be to:

Create a Database Role in each DB (i.e. this should be in your template / source DB) that contains the desired set of permissions.
Create an Instance / Server -level DDL Trigger that fires on the CREATE_DATABASE event and does the following:

Checks the name of the Database
If new DB name ends with ABC then:

Adds the Login to the new Database as a User
Add the new User to the Database Role that has the desired permissions

This will likely require some Dynamic SQL, but it should work. And honestly, I'm not sure what I think of using a DDL Trigger in this manner, but it certainly does automate it ;-).


List chronological pageviews without adjacent duplicates in PostgreSQL I've got a log of pageviews. Multiple pageviews with the same path may occur next to each other if say the user refreshes the page.

To see the user's journey through the site I'd like to select pageviews for that user but eliminating duplicate adjacent pageviews for clarity.

Example simplified table (time is really a timestamp):

CREATE TABLE pageviews(user_id, time, path)
AS VALUES
  ( 1, 1, '/home' ),
  ( 2, 2, '/home' ),
  ( 1, 3, '/profile' ),
  ( 1, 4, '/profile' ),
  ( 2, 5, '/dashboard' ),
  ( 1, 6, '/home' ),
  ( 2, 7, '/profile' ),
  ( 1, 8, '/profile' );

Now assume there's going to be a WHERE user_id = 1 clause and ORDER BY time ASC so we have:

( 1, 1, '/home' ),
( 1, 3, '/profile' ),
( 1, 4, '/profile' ),
( 1, 6, '/home' ),
( 1, 8, '/profile' );

But what I'd like is the duplicate adjacent /profile to be ignored like so (a count of the "grouped" paths would be nice too):

| user_id | time | path     | count |
| ------- | ---- | -------- | ----- |
| 1       | 1    | /home    | 1     |
| 1       | 4    | /profile | 2     |
| 1       | 6    | /home    | 1     |
| 1       | 8    | /profile | 1     |

A simple GROUP BY wouldn't do it as /profile is still in there twice, I only want to "group" adjacent equal values of path.

Also I'm not sure whether the first or last of the equal values should be returned, I don't really mind so whichever makes sense / makes for the simplest query. I'll probably flip it to be DESC in real use but this is how I've written the tables.

Reference I found this post that does basically what I want (included below for permanence). So I'm interested to hear 2 improvements:

Can that query be simplified (for PG 9.4)? One comment mentions DENSE_RANK and WITHIN GROUP to eliminate subquery but I couldn't figure out how to do that.
Can a count of the "grouped" values be added easily (possibly using a window function)?

So any elegant query that can achieve both of those things would be ⭐ I couldn't find any other articles or questions about this but I feel it must be a common thing to do so any other links welcome.

SELECT w1.day, w1.rainy
FROM (SELECT
    w2.day, w2.rainy,
    lead(w2.rainy) OVER (ORDER BY w2.day DESC) as prev_rainy
    FROM weather w2
    ORDER BY w2.day DESC
) as w1
WHERE w1.rainy IS DISTINCT FROM w1.prev_rainy
ORDER BY w1.day DESC;

SELECT user_id, max(time) AS time, path, count(*)
FROM (
  SELECT *,
    count(is_reset) OVER (PARTITION BY user_id ORDER BY time) AS grp
  FROM (
    SELECT *,
      CASE WHEN path <> lag(path) OVER (PARTITION BY user_id ORDER BY time) THEN 1 END AS is_reset
    FROM pageviews
  ) AS t1
) AS t2
GROUP BY user_id, grp, path
ORDER BY user_id, grp;

First what you need to do is create something you can group by, for this we compute is_resets. For more information about why it's in a CASE, see this question In what case is a count(x or null) needed in Gaps and Islands?

SELECT *,
  CASE WHEN path <> lag(path) OVER (PARTITION BY user_id ORDER BY time) THEN 1 END AS is_reset
FROM pageviews
 user_id | time |    path    | is_reset
---------+------+------------+----------
       1 |    1 | /home      |
       1 |    3 | /profile   |        1
       1 |    4 | /profile   |
       1 |    6 | /home      |        1
       1 |    8 | /profile   |        1
       2 |    2 | /home      |
       2 |    5 | /dashboard |        1
       2 |    7 | /profile   |        1
(8 rows)

Then we count them to create groups.

SELECT *,
  count(is_reset) OVER (PARTITION BY user_id ORDER BY time) AS grp
FROM (
  SELECT *,
    CASE WHEN path <> lag(path) OVER (PARTITION BY user_id ORDER BY time) THEN 1 END AS is_reset
  FROM pageviews
) AS t1
 user_id | time |    path    | is_reset | grp
---------+------+------------+----------+-----
       1 |    1 | /home      |          |   0
       1 |    3 | /profile   |        1 |   1
       1 |    4 | /profile   |          |   1
       1 |    6 | /home      |        1 |   2
       1 |    8 | /profile   |        1 |   3
       2 |    2 | /home      |          |   0
       2 |    5 | /dashboard |        1 |   1
       2 |    7 | /profile   |        1 |   2
(8 rows)

Now we group by the user_id, and grp. We select the max(time) because you didn't specify which time to choose in your question.

SELECT user_id, max(time) AS time, path, count(*)
FROM (
  SELECT *,
    count(is_reset) OVER (PARTITION BY user_id ORDER BY time) AS grp
  FROM (
    SELECT *,
      CASE WHEN path <> lag(path) OVER (PARTITION BY user_id ORDER BY time) THEN 1 END AS is_reset
    FROM pageviews
  ) AS t1
) AS t2
GROUP BY user_id, grp, path
ORDER BY user_id, grp;

 user_id | time |    path    | count
---------+------+------------+-------
       1 |    1 | /home      |     1
       1 |    4 | /profile   |     2
       1 |    6 | /home      |     1
       1 |    8 | /profile   |     1
       2 |    2 | /home      |     1
       2 |    5 | /dashboard |     1
       2 |    7 | /profile   |     1
(7 rows)

  Can that query be simplified (for PG 9.4)? One comment mentions DENSE_RANK and WITHIN GROUP to eliminate subquery but I couldn't figure out how to do that.

This problem can also be solved with dense_rank, as noted. You may want to see my answer to this question for a good write up on how to do that.

Solving “Gaps and Islands” with row_number() and dense_rank()?


what does || (Parallel sign) mean in DB2 Sql? What does || mean in this db2 syntax?


MySQL begin end from documentation doesn't work I am using XAMPP. I simply tired this begin..end -

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;

statement and got error. This happens with every begin..end or while that I try. Is it because of my mysql version ? XAMPP version is v3.2.2 and mysql is Version information: 4.7.9. I am really confused. The error is :

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

Thank you in advance!

While creating procedure, function or another compound statement consisted from more than 1 command, You MUST use DELIMITER statement.

The reason is simple - command to create procedure is one command. But it consists from a lot of common commands. When You enter its code, server has no any marker, what kind of command is terminated by any delimiter - simple statement or compound statement. To avoid it You must use separate delimiters for that aims. Because standard delimiter will be used when server executes procedure, You must NOT alter default delimiter used for common commands. So You must alter delimiter used for fixed compound statement.

You must update Your code:

DELIMITER @@; -- Alter delimiter to 3-symbol one
              -- not contained in compound statement we want to create

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5; -- Std delimiter, terminates single statement
  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;
@@;  -- Altered delimiter terminated compound statement

DELIMITER ; -- restore delimiter to standard semicolon


How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline? MySQL Server 1 is running as Master.
MySQL Server 2 is running as Slave.

With both DBs online, they are in "perfect sync". If Slave goes offline, there's no problem if Master still online; they will go back sync once the Slave is online again.

Besides the server configuration, I redirected the connection (with JSP code) for the Slave DB if the Master goes offline (I tested of course with /etc/init.d/mysqld stop).

When the Master goes back online, is there any automatic method to sync the Master with Slave updates?

One good way to pull off something of that nature is to set up Master-Master Replication or Circular Replication. This is not to be confused with MultiMaster Replciation.

Setting up Circular Replication is actaully very easy if you have setup  Master-Slave Replication. Here is what you need to do in order to configure it.

For this example, we will assume Master-Slave Replication is active but you will experience a bit of downtime (1-2 minutes):

Step 1) Add this line to /etc/my.cnf on the Master.

log-slave-updates

Step 2) Add these line to /etc/my.cnf on the Slave:

log-bin=mysql-bin (or have whatever the master has for this)
log-slave-updates

WARNING : Here is the brief moment of downtime !!!

Step 3) On the Slave, service mysql restart

This will activate binary logs on the Slave

Step 4) On the Master, service mysql stop

Step 5) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master.

WARNING : Here is the longer moment of downtime !!!

Step 6) On the Slave, service mysql stop

Step 7) On the Slave, find out the last binary log

Step 8) On the Slave, find out the filesize of the last binary log

Step 9) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master. This should be a faster copy.

Step 10) On the Master, edit
Line 2 of master.info with the last binary log of the Slave.
Line 3 of master.info with the filesize of the last binary log of the Slave.
Line 4 of master.info with the IP of the Slave.
Line 5 is the userid of replication user (DO NOT TOUCH)
Line 6 is the password of replication user (DO NOT TOUCH)

Step 11) Delete all binary logs and binary log index file of the Master.

Step 12) On the Slave, service mysql start, wait 15 seconds

Step 13) On the Master, service mysql start

Step 14) On the Master, run STOP SLAVE; SHOW MASTER STATUS;

Step 15) On the Slave, run CHANGE MASTER TO MASTER_HOST='IP of Slave',MASTER_USER='userid of replication user from Step10',MASTER_PASSWORD='password of replication user from Step10',MASTER_LOG_FILE='binary log from Step14',MASTER_LOG_POS=LogPos from Step14.

Step 16) On the Slave, run START SLAVE;

Step 17) On the Master, run START SLAVE;

I performed steps similar to this for another StackExchange question I answered.

Give it a Try !!!


three tables relation order is correct but values are false i have those tables

     table1
|  id  |  name  |
|  1   |  axe   |
|  2   |  bow   |
|  3   |  car   |
|  4   |  dart  |

        table2                                        table3
|  t1_id  |  number  |                        |  t1_id  |  letter  |
|  1      |  5       |                        |  1      |  a       |
|  1      |  6       |                        |  1      |  b       |
|  1      |  2       |                        |  1      |  c       |
|  2      |  2       |                        |  2      |  a       |
|  2      |  2       |                        |  2      |  c       |
|  2      |  3       |                        |  2      |  r       |
|  3      |  8       |                        |  3      |  y       |
|  3      |  3       |                        |  3      |  i       |
|  3      |  1       |                        |  3      |  a       |
|  4      |  8       |                        |  4      |  a       |
|  4      |  9       |                        |  4      |  b       |
|  4      |  10      |                        |  4      |  c       |

and table1(id) is linked with table2(t1_id), table3(t1_id)

i run it to get them order by the highest letter_count match then by the highest average_number match to get this proper result http://www.sqlfiddle.com/#!9/69086b/8/0

SELECT
  t1.id,
  t1.name

FROM
  table1 t1

INNER JOIN
  table2 t2
    ON t2.t1_id = t1.id

LEFT JOIN
  table3 t3
    ON t3.t1_id = t1.id
      AND t3.letter IN ('a', 'b', 'c')

GROUP BY
  t1.id

ORDER BY
  COUNT(t3.letter) DESC,
  AVG(t2.number) DESC

|  id  |  name  |
|  4   |  dart  |
|  1   |  axe   |
|  2   |  bow   |
|  3   |  car   |

and everything is working ok

but when i wanted to check if there is any problems with the query i decided to check the letter_count and avg_number so i used this query

SELECT
  t1.id,
  t1.name,
  COUNT(t3.letter) AS letter_count,
  AVG(t2.number) AS avg_number

FROM
  table1 t1

INNER JOIN
  table2 t2
    ON t2.t1_id = t1.id

LEFT JOIN
  table3 t3
    ON t3.t1_id = t1.id
      AND t3.letter IN ('a', 'b', 'c')

GROUP BY
  t1.id

ORDER BY
  letter_count DESC,
  avg_number DESC

what i expected the result to be was

|  id  |  name  |  letter_count  |  avg_number   |
|  4   |  dart  |  3             |  9            |
|  1   |  axe   |  3             |  4.3333333333 |
|  2   |  bow   |  2             |  2.3333333333 |
|  3   |  car   |  1             |  4            |

but the result i got was http://www.sqlfiddle.com/#!9/69086b/3/0

|  id  |  name  |  letter_count  |  avg_number   |
|  4   |  dart  |  9             |  9            |
|  1   |  axe   |  9             |  4.3333333333 |
|  2   |  bow   |  6             |  2.3333333333 |
|  3   |  car   |  3             |  4            |

am i supposed to be worried on how the letter_count is multiplied three times on the future or does this just happens when i want to select the values and nothing bad is going to happen if i just kept it in ORDER BY?

i just want them to be ordered correctly which is what i got but checking the values showed me that letter_count is multiplied many times so i am confused about its performance or the letter_count can be ignored and the performance wont be affected?

It is common multiplying in multi-table source. Each record in table3 is joined to 3 (in above sample data, really it can differ, of course) records from table2.

If table3(t1_id,letter) is unique (by index) then COUNT(DISTINCT t3.letter) will help.

If not then the only solution is in calculate this COUNT() in subquery by table3 separately and join the result to table1.


Get row value inside a subquery I need to count days between dates. I have this query, that gets the date when an issue is resolved. The subquery selects the date when a issue is created.

The problem is that the subquery the dates of all opened issues instead of getting the one that has the same INCIDENCIA.ID_INCIDENCIA:

SELECT
  INCIDENCIA.ID_INCIDENCIA AS Incidencia,
  INCIDENCIA.DESCRIPCION,
  SITUACION.FECHA,
  SITUACION.FECHA - Inicio.Fecha AS Dias
FROM
  INCIDENCIA,
  SITUACION,
  (SELECT SITUACION.FECHA AS Fecha
    FROM SITUACION, INCIDENCIA
    WHERE SITUACION.ID_ESTADO = '1' AND INCIDENCIA.ID_INCIDENCIA = SITUACION.ID_INCIDENCIA) Inicio
WHERE
  SITUACION.ID_ESTADO = '3' AND
  INCIDENCIA.ID_INCIDENCIA = SITUACION.ID_INCIDENCIA
ORDER BY
  INCIDENCIA.ID_INCIDENCIA ASC

The expected result is:

╔═══╦══════════╦═══════════════════════╦═════╗
║ 1 ║ 13/10/16 ║ Averia en router      ║  30 ║
║ 2 ║ 16/11/16 ║ Fallo red             ║  28 ║
║ 3 ║ 22/10/16 ║ Solicitud de sala     ║  37 ║
║ 4 ║ 19/10/16 ║ Averia ordenador      ║   0 ║
║ 5 ║ 29/10/16 ║ Averia sin determinar ║   1 ║
║ 6 ║ 10/01/17 ║ Averia conexion       ║ 136 ║
╚═══╩══════════╩═══════════════════════╩═════╝

But instead I got:

╔═══╦═══════════════════════╦══════════╦═════╗
║ 1 ║ Averia en router      ║ 13/10/16 ║  30 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  -6 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  28 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  -6 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║ -15 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  47 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  64 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  28 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  62 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  28 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  19 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  81 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  39 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║   3 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  37 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║   3 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  -6 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  56 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  36 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║   0 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  34 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║   0 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  -9 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  53 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  46 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  10 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  44 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  10 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║   1 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  63 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║ 119 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║  83 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║ 117 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║  83 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║  74 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║ 136 ║
╚═══╩═══════════════════════╩══════════╩═════╝

Your subquery is not correlated to the outer query, so it multiplies the rows by the number of rows it returns. Also, in my opinion, using the same table names multiple times without aliases at multiple query levels is bad practice, as it may cause some unexpected "surprises". Your query should be something like this:

SELECT
  i1.ID_INCIDENCIA AS Incidencia,
  i1.DESCRIPCION,
  s1.FECHA,
  s1.FECHA - Inicio.Fecha AS Dias
FROM
  INCIDENCIA i1,
  SITUACION s1,
  (SELECT i2.ID_INCIDENCIA as Inicio_ID_INCIDENCIA, s2.FECHA AS Fecha
    FROM SITUACION s2, INCIDENCIA i2
    WHERE s2.ID_ESTADO = '1' AND i2.ID_INCIDENCIA = s2.ID_INCIDENCIA) Inicio
WHERE
  s1.ID_ESTADO = '3' AND
  i1.ID_INCIDENCIA = s1.ID_INCIDENCIA
  AND Inicio.Inicio_ID_INCIDENCIA = i1.ID_INCIDENCIA
ORDER BY
  i1.ID_INCIDENCIA ASC

Added aliases everywhere + ID_INCIDENCIA inside the subquery, so it can be joined to the outer query based on that column.


Passing value of datatype interval in parametrized query The context is connecting to a Postgres db from a rest server.

To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is older/newer than an arbitrary value.

In the example query below, the table structure is simple - name is of type text, and creation_date is of type timestamp. So when I do something like

server_pg_module:query("select name from new_table where
current_timestamp - creation_date < '6 days'")

it works nicely. But what I really want to do is get that value of 6 days from the server. So I try something like

server_pg_module:query("select name from new_table where
current_timestamp - timestamp < $1", ["6 days"]

it throws an error. I tried '6 days', "'6 days'" and a few other concoctions, all throw errors. So to check I added a new column interval of type interval and tried a query like

server_pg_module:query("insert into new_table (name, interval) values ($1, '3 day')", ["fooo"]).

which works, but

server_pg_module:query("insert into new_table (name, interval) values ($1, $2)", ["fooo", "3 days"]).

breaks. For good measure, in addition to the concoctions like "'3 days'" mentioned above I also tried $2::interval (which I am not sure is legit), but it doesn't work.

Thus I believe it might either have something to do with expressing an interval in a param query, or something peculiar about the module I am using. Any ideas on what causes the trouble and how to do this sort of thing would be appreciated. Or may be it can be narrowed down that the problem is not with pg but with the module, then I have to address it elsewhere.

Postgres version: 10.x

The module I am using is pgo (for the Erlang programming language) https://github.com/SpaceTime-IoT/pgo. The error message I get (when I pass "2 days" or "'2 days'" as the query parameter) looks like:

{error,{pgsql_error,#{code => <<"08P01">>,file => <<"pqformat.c">>,
                          line => <<"575">>,
                          message => <<"insufficient data left in message">>,
                          routine => <<"pq_copymsgbytes">>,severity => <<"ERROR">>,
                          {unknown,86} => <<"ERROR">>}}}

And when I pass '2 days' as the parameter, it throws a badarg error.

TLDR: Skip to chapter "Superior query" below.
You didn't disclose the module you are working with, but the problem is obviously one of type casting. Looks like your parameter is passed as typed value, and I assume text or varchar. There is no implicit type cast for text -> interval:

SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_cast
WHERE  casttarget = 'interval'::regtype;

castsource             | casttarget | castcontext
:--------------------- | :--------- | :----------
time without time zone | interval   | i
interval               | interval   | i

db<>fiddle here
If my assumptions are correct you should see an error message like:

ERROR:  operator does not exist: interval < text

I am pretty confident your undisclosed module has ways to pass a different data type or an untyped string literal. Postgres does offer this functionality.
You also state:

I also tried $2::interval

This is odd, because an explicit type cast should also work.
Demo

-- interval typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < interval '6 days'; -- works

| ?column? |
| :------- |
| f        |

--untyped string literal
SELECT current_timestamp - timestamp '2018-05-04 18:40' < '6 days'; -- works

| ?column? |
| :------- |
| f        |

-- text typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < text '6 days'; -- fails!

ERROR:  operator does not exist: interval < text

LINE 2: ... current_timestamp - timestamp '2018-05-04 18:40' < text '6 ...
^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

-- text typed value, with explicit cast
SELECT current_timestamp - timestamp '2018-05-04 18:40' < ('6 days'::text::interval); -- works

| ?column? |
| :------- |
| f        |

The same is true for prepared statements:

PREPARE fooplan_typed_interval(interval) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_interval('6 days');

| ?column? |
| :------- |
| f        |

PREPARE fooplan_untyped AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_untyped('6 days');

| ?column? |
| :------- |
| f        |

PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_text('6 days');  -- only this one fails!

ERROR:  operator does not exist: interval < text

LINE 3: ...ELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;
^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1::interval;

EXECUTE fooplan_typed_text('6 days');

| ?column? |
| :------- |
| f        |

db<>fiddle here
Superior query
All this aside, neither of your queries can use an index (not "sargable").
Use something like this instead!
server_pg_module:query("select name from new_table
where creation_date > localtimestamp - interval '1 day' * $1", [6])

You can multiply an interval with integer.
Or if you figure out the problem with passing typed parameters:
server_pg_module:query("select name from new_table
where creation_date > localtimestamp - $1::interval", ['6 days'])

Using localtimestamp to point out that the "current time" depends on your current time zone setting with the type timestamp. Details:

How to get the difference in days between 2 timestamps in PostgreSQL
Ignoring time zones altogether in Rails and PostgreSQL


Should I disable triggers, timestamps in a slave in mysql? We're using MySQL. We have a master that eventually will have 2 slaves. There are triggers in the db that execute when data changes in certain tables. I am wondering whether to disable the triggers in the slaves. It seems to me that I should. It also seems like things like timestamps should be disabled in the slaves because otherwise the data will be different on the slaves than the masters.

I'm not a DBA, I'm a developer. We don't have a DBA at my company so the ops admin and I are figuring it out as we go along. We already have the master and one slave set up and replicating and we are getting duplicate entry error on the slave.

We were thinking of not halting replication for duplicate replication errors, as in this post: http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/. We don't know whether this is a good idea. I do feel that this masks the problem, but neither of us knows how to fix the problem.

If we should disable the triggers and/or set the slave not to insert timestamps, how do we go about doing that? Or does establishing a db as a slave automagically do these things anyway?

Do not disable the triggers on your slaves, and do not configure your slaves to skip errors.
When replication is set up correctly with consistent data sets and identical configurations, including tables and their data, views, triggers, stored functions, and stored procedures, it just works.  If it isn't "just working," then it was already not consistent in one of these ways.  The duplicate key errors are a red flag that your data is not identical, and you don't want to suppress this.
If you aren't already, you should be using binlog_format = MIXED unless you have a specific reason not to.  The default value prior to MySQL 5.6 was STATEMENT but this was largely for legacy reasons.
Using mixed logging allows the optimizer on the master to choose the best way to log each change to the master's data -- either by:

logging the literal and exact query your client sent to the master, so that the slaves can execute that same query, thereby modifying their data (statement based logging), or
logging the specific rows that were changed on the master so that the slave can change those same rows in the affected table without regard to the actual query that caused the changes (row based-logging).

If a statement is determined to be "unsafe" for statement-based logging (meaning that it could potentially cause the data to diverge from its initial identical state), it will be automatically logged as row-based, if you're using MIXED mode.  Examples of this would be statements involving calls to non-deterministic functions like UUID() which would return different values on master and slave.  Row-based logging handles this without a problem, since it passes the literal values across.
You might think NOW() might also be such a function, and that a similar limitation might apply to automatic timestamp columns, if the server clocks are not in sync, but that's not the case -- because with each binary (replication) log entry, the master logs what its current timestamp was at the time the query was executed -- allowing the slave to have a "pseudo-system clock" to actually fudge-in the master's clock's value at the time that statement was executed when needed.  This log entry, incidentally, is how the slave calculates "seconds behind master," by comparing its system clock to the logged timestamps.  (The exception to this is when the slave has executed everything in its relay logs, it always shows "0" seconds behind, because it doesn't know whether the former logs were delayed by clock skew or by an actual replication lag -- so if it has run out of things to execute, it is no longer technically "behind").
If a query is logged using row-based logging, then any data changed by triggers are also logged as row-based log entries, and the triggers on the slave will automatically not fire.
On the other hand, if the same query is logged using statement-based logging, then replication depends on the identical triggers being present on the slave, because the master will not log what the trigger did -- it only logs the query issued, and depends on the slave triggers firing exactly as they did on the master, assuming you want the data on all servers to be consistent.

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.
This behavior is by design. [...]

http://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html
The documentation often speaks of replication in what sounds like terms of either/or (statement or row) but for each query executed MIXED logging will choose the logging method on a per-query basis.  You may also find statements to the effect that row-based logging is "required" in some conditions but this only means that it must be available (i.e., via MIXED); it does not mean that the server must be configured for ROW.  DDL, of course, is always logged as statements, regardless of the binlog format.