MySQL HA_ERR_LOCK_WAIT_TIMEOUT ignoring innodb_lock_wait_timeout (deleting most of a table) I am issuing some long-running delete queries to some databases, and I am getting the following error:

Last_Error: Could not execute Write_rows event on table DATABASE.TABLENAME; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.005698, end_log_pos 91495479

I have set "innodb_lock_wait_timeout" to high value such as the query will never hit the timeout.

SELECT @@GLOBAL.innodb_lock_wait_timeout;
+-----------------------------------+
| @@GLOBAL.innodb_lock_wait_timeout |
+-----------------------------------+
|                           1000000 |
+-----------------------------------+

Any suggestions for how to avoid this error? I need to delete large amounts of data so I want to issue queries in a loop without supervision. It feels a bit like a mysql bug. MySQL version is 5.6.

EDIT: Here's the exact query. The data is sharded client-side based on the function shown (MOD(MOD(user_id,65536), shard_count) != shard_id). The majority of the data in the DBs shouldn't be there, so the delete is big, and it's cascading down to tables that are >300GB (where each user might have an inventory of 200 items, for example)

DELETE FROM User
WHERE account_type = "type1"
AND
user_id IN (
    SELECT b.user_id
    FROM (
        SELECT a.account_type, a.user_id
        FROM (
            SELECT account_type, user_id
            FROM User
            WHERE account_type = "type1"
            AND
            MOD(MOD(user_id,65536), 20) != 9
            LIMIT 1000000
        ) a
    ) b
)

EDIT: As requested, output of SHOW CREATE TABLE

mysql> SHOW CREATE TABLE User \G;
*************************** 1. row ***************************
       Table: User
Create Table: CREATE TABLE `User` (
  `account_type` varchar(20) NOT NULL DEFAULT '',
  `user_id` bigint(20) unsigned NOT NULL,
  `mod_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `team_id` int(10) unsigned DEFAULT NULL,
  `shard_key` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`account_type`,`user_id`),
  KEY `PLAY_SHKEY_I` (`shard_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

EDIT: I believe at this point that it was down to a programming error, because I wasn't calling commit() from the python client until a really big loop completed, so I think there was a huge ongoing transaction that did not commit ever (because it never made it all the way through the loop)

Do not use OFFSET when looping through to do DELETEs; it is likely to get slower and slower, and maybe hit even your increased timeout.

Try not to put all the deletes in a single transaction.  Instead make it so that if one delete dies, rerunning the deletes will still work 'correctly'.

Don't delete more than 1000 rows at a time.

More discussion of "big deletes":  MySQL Big DELETEs

Deleting 95% of a table

Now that you have explained more of what is going on, I recommend rebuilding the table by creating a new table and copying over the 5%.  (This technique applies for any percentage, but its benefits are dubious if copying more than 50%.)

Note that innodb_file_per_table needs to have been on before the bloated table was build, else you won't recoup the disk space.

If that would tie up the table too long, then walk through the PRIMARY KEY in chunks of 1K rows, deleting about 95% of each chunk.  (As discussed elsewhere in that link.)  You won't be able to return the disk space to the OS (without a locking OPTIMIZE TABLE), but the "freed" space will eventually be used by new INSERTs.


Nested select: insert values into table with two subqueries I'm developing a Stored procedure for SQL Server 2016.

I have to do this (the following sql statement doesn't work):

Insert into AggregationChildren (AggregationId, AggregationChildrenId, Position)
SELECT Parent, Serial, Position
FROM
    OPENJSON (@json, '$.Aggregations')
    WITH (  Parent nvarchar(20) '$.Parent',
            Children nvarchar(max) AS JSON )
        CROSS APPLY
            OPENJSON (Children)
            WITH ( Serial nvarchar(20), Position int)

My problem is that AggregationId and AggregationChildrenId are integers and Parent and Serial are nvarchar(20).

There is a relationship between Parent and AggregationId, and between Serial and AggregationChildrenId. I can get it using Code table.

I know how can I do it when I have to insert values for one column:

Insert into AggregationChildren (AggregationId)
    Select CodeId from Code Where Serial = (SELECT Parent
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int))

The data I'm trying to insert is:

{
    ...,
    "Aggregations": [{
        "Parent": "88962730000000004051",
        "Children": [{
            "Serial": "81861400000000020227",
            "Position": "1"
        }, {
            "Serial": "81861400000000033191",
            "Position": "2"
        }, {
            "Serial": "81861400000000046051",
            "Position": "3"
        },
        ...
        ]
    }, {
        "Parent": "88962730000000016653",
        "Children": [{
            "Serial": "81861400000001825849",
            "Position": "1"
        }, {
            "Serial": "81861400000001832643",
            "Position": "2"
        }, {
            "Serial": "81861400000001841911",
            "Position": "3"
        }, {
            "Serial": "81861400000001850803",
            "Position": "4"
        }, {
            "Serial": "81861400000001862474",
            "Position": "5"
        }, {
            "Serial": "81861400000001874774",
            "Position": "6"
        }, {
            "Serial": "81861400000001884159",
            "Position": "7"
        }, {
            "Serial": "81861400000001898352",
            "Position": "8"
        }, {
            "Serial": "81861400000001904764",
            "Position": "9"
        },
        ...
        ]
    }]
}

But, how can I do it if I have to use two Select CodeId from Code Where Serial = (SELECT ...?

I think I have to do something like this but without doing the same select three times:

 Insert into AggregationChildren (AggregationId, AggregationChildrenId, Position)
    Select CodeId from Code Where Serial = (SELECT Parent
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int)),
    Select CodeId from Code Where Serial = (SELECT Serial
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int)),
    SELECT Position
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int)


Index for date column for range query in Oracle? We have a lot:

select count(*) from TBL where XDATE between ? and ? and FLD = ?;

We expect to boost performance by tuning index on XDATE column (which have DATE type).

What type of indexes more suitable for DATE type and between condition?

Ranges - especially date ranges - can be hard to tune.  There is a word of difference between this ...

where xdate between date '2013-07-01' and date '2013-07-31'

... and this ...

where xdate between date '2003-07-01' and date '2013-07-31'

You would want radically different access paths for those two queries.  An index will help with the first one, but will probably be disastrous for the second (depending on the volumes of data involved).

The other snag with indexing dates concerns the time element.  If xdate contains the time element then potentially you have 86400 different values for each calendar day.  In most cases when we're doing a date range search we want all hits for the day regardless of time.

We can't give an absolute recommendation, because you haven't provided enough details but my general advice would be:

Build a function based index, removing the time element:  create index tbl_xdate_idx on tbl(trunc(xdate)).
Investigate whether index compression will give you a space saving.  Compressed indexes usually perform better too. But it's probably only worthwhile if the dates don't include times, another benefit of the FBI approach.  Oh, and you need Enterprise Edition.
Remember that searches which span long ranges may need to be handled differently.  How you do that depends on the peculiarities of your application.


Why are Oracle processes only using half the SGA set? I have an Oracle set up on a remote server that's maintained by a third party.  Recently it became corrupt and we replaced it with a new instance.  Since then it's been running slower when performing some searches and even timing out.  I've been told by the people that maintain the database that the memory allocation is set as follows:

SGA: 6GB
PGA: 4GB

However when I run top in Linux I can see that all except one of the processes only use ~2.5GB as indicated by the RES column.

I know that there are other instances of oracle running on the server however there's 32GB of physical memory in the system and so it should be able to handle it.

Is there any reason why processes wouldn't be using as much of memory as should be expected?

NB:  When the system was brought back up the people that maintained it put half the physical memory (16GB) in the server box that should have been put in.  This was rectified a month ago and another 16GB added but didn't fix the problem.

set SGA_max_size=30g (size is greater then sga_target value)..
you must set SGA_target=16777216k or 16384m or 16g

Now instance will allocate up to 16g
you can alter sga_target upto 30g
with out shut db..


Postgres - Only allow insert if TWO columns match with a separate table I have three tables: a users table, a books table, and a chapters table.

Each book has an id called identifier_id (for good business reasons that I cannot work around). This id is guaranteed to be unique per-user, i.e. no user will ever have two books with the same id. It is not, however, guaranteed to be globally unique. Each book also has a foreign key user_id.

Each chapter has user_id and book_id foreign keys - the book_id foreign key points to identifier_id on the books table.

When I add chapters to a book, I want to add a database-level contraint that a chapter insert will be rejected unless the user_id of the chapter matches the user_id of the book AND the book_id of the chapter matches a book_id in the books table.

Because the book_id is not globally unique, I cannot put a unique index on it. Is there a way to do this?

You can create a unique compound index

 CREATE UNIQUE INDEX "book_id_user" ON books (identifier_id,user_id);

And then you can create a F.K. constraint

 ALTER TABLE chapters
   ADD CONSTRAINT "FK_chapter_book"
     FOREIGN KEY (book_id,user_id)
       REFERENCES books (identifier_id,user_id);

If you have any bad data that will need to be corrected before the constraint can be created


Adding new user in Postgres I am using AWS RDS PostgreSQL and currently up and running. I would like to know how I can add a new user but only grant SELECT on an existing DB.

The way I connect to my RDS from my PC is by using a app called pgAdmin III (from Ubuntu).

I tried to create a group called rds_guest, assign a user (called webkul) and finally GRANT privileges (INSERT, SELECT, UPDATE etc) on the database I want for that particular group:

When I try to login using that user and try perform actions on the desired database I get an error saying:

  Permission denied for relation xxx_yyy

Can anyone help me with this ? I am relatively new with Postgres.

You must grant a user:

CONNECT on the database (if public doesn't have it by default)
USAGE on any schemas like the public schema (again, if the public role doesn't have it by default)
... and the rights you want on a given table.

I suspect you (or RDS) have revoked CONNECT on the database and/or USAGE on the schema(s) you're using, so the role doesn't have the rights to get to the point where it can use the table privileges.


Parameters in Activity Monitor To view "recent expensive queries" I use Activity Monitor. Some of the queries there are with parameters.

Is there a way to know the values?

You can right-click the query you are interested in and select Show execution plan:

Once you have the execution plan open, just right-click in an empty area and select Show Execution Plan XML.... Once you have that you will see a section in the XML <ParameterList>. In that node it will show you the ParameterCompiledValue:

    <ParameterList>
      <ColumnReference Column="@records" ParameterCompiledValue="(0)" />
      <ColumnReference Column="@traceid" ParameterCompiledValue="(2)" />
    </ParameterList>
  </QueryPlan>
</StmtSimple>

Another way would be to copy the execution plan XML out and paste it into Plan Explorer and view the Parameters tab:

Edit for the nerds

If you open up Activity Monitor, and then go into tempdb you will find all the temp objects (tables and all) that Activity Monitor is using. For the Expensive Queries it is using the object dbo.#am_get_querystats. You can open that procedure up and look at the exact query they use to pull it. Pretty cool. I did this for the activity section so I could get similar information without having to open up Activity Monitor all the time.


Weird result of CHARINDEX of SQL SERVER I am using SQL Server 2014. I have the following statement:

SELECT CHARINDEX('C', 'ABCDEFGC', 3);

The result I get is 3 instead of 8. I observe the same behavior with different strings. Is this expected behavior of this function?

It seems fine if the last parameter is any other number.

Thanks

That's not weird at all, that's how CHARINDEX works. The third parameter you pass in is the start position to start searching for your expression.

You'll have to deal with this a lot if you're using CHARINDEX along with SUBSTRING to find text in between two expressions.

If you start searching AT the CHARINDEX of C in your example, you get the same results.

SELECT CHARINDEX('C', 'ABCDEFGC', CHARINDEX('C', 'ABCDEFGC'));

Returns 3

You need to start searching for the CHARINDEX one position after to get the next one. Otherwise, it finds the searched expression in the first position it checks.

SELECT CHARINDEX('C', 'ABCDEFGC', CHARINDEX('C', 'ABCDEFGC') + LEN('C'));

Returns 8

If you start searching after the last occurrence, CHARINDEX will wrap back around.

SELECT CHARINDEX('C', 'ABCDEFGC', CHARINDEX('C', 'ABCDEFGC', 9));

Also returns 3.

Hope this helps!


SQL Server 2008 R2 SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file ' When I alter or create a stored procedure directly on production or QA database, after a few seconds I start experience timeouts and application becomes unavailable.

Log files shows this error:

  SQL Server has encountered 3 occurrence(s) of I/O requests taking
  longer than 15 seconds to complete on file [C:\Program Files\Microsoft
  SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QA_Database.ldf] in
  database [QA_Database] (9).  The OS file handle is 0x0000000000000568.
  The offset of the latest long I/O is: 0x0000002821a200

We have SQL Server 2008 R2 installed, including latest Service Pack.

I tried to reproduce it on QA, but to no avail. I have no clue what it could be.

Stored procedure execution time is around 1 second and it runs occasionally. It returns just few records. It happened with 2 different stored procedures and I think that changing, not running, a stored procedure triggered something that caused the issue.
Once I deleted it, issue was gone immediately.

Just a hunch... is that ldf growing when you get this error? I seen this error a few times and always been that someone set the growthrate in too big steps for the io subsystem to handle at same time as normal io within 15 seconds. Go and see if it happens to bet set to 10% and how big a step that would be. If it is GBs you might want to set it to a smaller size to grow and also in MB instead of percent.

Who knows I might get lucky on this one ;-)


Ingredients for SQL Server Version decision We are going the create our Sql-Servers new on new hardware / VM's and think about which Sql-Server Version to go with. We are actually using Sql Server 2008 R2. We have already done a project for testing everything for any compatibility issues between 2008 and 2014 - and have fixed all issues in application code as well as in the db schemas to be ready for 2014.

Now the question for simply dismissing 2014 and directly go to 2016 came up. We are not sure.

I have ran SQL 2016 Upgrade Advisor - it said "ready for upgrade (2016)" and raised around 500 warnings for deprecated ntext, text and image columns.

Are there any special additional downsides of going to 2016 instead of 2014?
Are there new compatibility issues? AFAIKS there have been no new deprecations in 2016 that are not yet in place of 2014...
What do we have to consider?

The is some information directly from MS that you might find useful: Breaking Changes to Database Engine Features in SQL Server 2016 and Discontinued Database Engine Functionality in SQL Server 2016, which indicate that officially there is very little by way of breaking changes. Those pages link to similar information about older versions too.

As a general rule I recommend going for the latest version for new installations once that version has been released long enough for any major bugs to have been found by someone else and fixed by MS, and SQL2016 had a large enough beta release that anything major should have been found already. But I would also recommend a full regression check of the applications to cover the possibility of:

reliance in your application on undocumented/undefined behaviour that may differ in the newer version: changes to officially undefined behaviour won't be listed in places like the pages linked to above.
performance issues due to changes in the query planner: the new carnality estimation method in 2014 generally lead to noticeable improvements but in a some use cases they had the opposite effect and hints are needed to push the query planner back towards the optimal plan, I'm not aware of such a significant change in that area in SQL2016 but there will no doubt be smaller changes that could have a detrimental effect for a minority of queries/statements.
new and interesting bugs as yet undiscovered.

As is sounds like you have recently performed a large scale regression test, you might not be able to justify (to the business) doing so again this soon in which case sticking with 2014 would be the safer option.


Get rows having different values for a column based on the duplicate values of combination of other 3 columns I want to get only rows having a different values in a column(column name DEF) based on the duplicate rows having unique combination of other 3 columns.

Example: In the below example first two rows has same value for first 3 columns.But they have different value for column DEF. So both these rows to be listed in output.

But rows 2 and 4 has unique combination for first 3 columns but they have same values in DEF column.So not to be listed in output.

rows 5 and 6 are not to be listed since they are single row with different values.

+----------+-------+--------+--------+
| dept     | role1 |role2   |DEF     |
+----------+-------+--------+--------+
| a        | abc   | er     | 0      |
| a        | abc   | er     | 1      |
| b        | qwer  | ty     | 0      |
| b        | qwer  | ty     | 0      |
| c        | der   | ui     | 1      |
| d        | nerr  | io     | 0      |
+----------+-------+--------+--------+
output

+----------+------+------+------+
| dept     | role1|role2 |DEF   |
+----------+------+------+------+
| a        | abc  | er   |0     |
| a        | abc  | er   |1     |
+----------+------+------+------+

I tried using distinct with having but not able to check the values of column DEF to get desired result.

Can anyone help me on this?

Using standard SQL on most RDBMS, there are various ways.

Using a subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM data d
INNER JOIN (
    SELECT dept, role1, role2
    FROM data
    GROUP BY dept, role1, role2
    HAVING COUNT(distinct DEF) > 1
) dup
    ON dup.dept = d.dept AND dup.role1 = d.role1 AND dup.role2 = d.role2
;

The subquery returns sets of dept/role1/role2 with more than 1 distinct DEF.

Using a correlated subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM @data d
WHERE EXISTS (
    SELECT 1
    FROM @data
    WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF <> d.DEF
);

The subquery return 0 to n rows. If at least one row exists, the row from the main table is returned.

Using CROSS APPLY:

SELECT d.dept, d.role1, d.role2, d.DEF
FROM @data d
CROSS APPLY (
    SELECT n=1
    FROM @data
    WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF <> d.DEF
) ca
;

CROSS APPLY works with Oracle or SQL Server.

Output:

dept    role1   role2   DEF
a       abc     er      0
a       abc     er      1


Priority Inbox Database Design I was just wondering, what would be the table structure for a messaging system similar to Gmail's priority inbox?
If you're unfamiliar with the system, it prioritises messages based on the type of messages you most often read and those to which you reply, then separates them from 'normal' messages.
In this hypothetical scenario, I'm basing the 'score' only on senders to which the user replies.

I was thinking that each user would have a table assigned to them that recorded the user ids of the top 10 people they responded to, each user name requiring 5 replies to be entered into said table. For each reply, the 'replied' value would increment by one.
Is that the best/most logical way of doing it?

I'm not familiar with 'NoSQL' databases, so my logic is based on a relational methodology. My apologies if I'm missing something obvious because of that.

I think the trick to this is that it doesn't have to be real time, just eventually consistent, in which case it's straightforward enough (using SQL Server, but this applies in any DB). First a trivial table and some sample data:

create table messages
(message_id integer, sender varchar(20), recipient varchar (20))
go

insert into messages values (1, 'Gaius', 'Octavian')
insert into messages values (2, 'Gaius', 'Octavian')
insert into messages values (3, 'Gaius', 'Octavian')
insert into messages values (4, 'Aurelius', 'Octavian')
insert into messages values (5, 'Aurelius', 'Octavian')
insert into messages values (6, 'Aurelius', 'Gaius')
insert into messages values (7, 'Aurelius', 'Gaius')
insert into messages values (8, 'Octavian', 'Gaius')
go

This is logging for every message, who sent it and who to (assuming for simplicity that the message body is stored in another table). So we can see that the top sender to Octavian is Gaius (3 messages of 5), and the top sender to Gaius is Aurelius (2 messages of 3). To query that using a CTE:

with q1 as (
  select recipient, sender, count(sender) as num_messages_from_sender,
  rank() over (partition by recipient order by count(sender) desc) as priority
  from messages group by recipient, sender)
select recipient, sender as top_sender, num_messages_from_sender
from q1 where priority=1
go

In practice you would have a job that ran every minute (or whatever interval is best) refreshing a lookup table mapping a user to their top sender (or top n senders using where priority <= n) (or in your case, you would be tracking the senders to which they reply with another column and filtering by that).

For the sake of simplicity I have left off indexes and partitioning - they would be the key to performance of this solution. You could certainly scale this to many billions of messages on any modern DB/hardware. GMail most likely has a custom solution tho', but with 20,000 engineers Google can do that!


What does below parallel processing execution plan mean? I have a table tbl_a with 200 k records. I wrote a query to bring sum of id (int) column. Now execution plans shows 2 workers launched and below that parallel sequential scan on tbl_a is shown 3 times; with processing 66667 records each time(200k/3=66667). Now my question is since there were two workers so should there not be only two times table seq scan and each time with record 100k (200k/3). I am attaching the image which lists the table detail, query and query execution plan for better understanding.

Can someone tell me why with 2 workers there are 3 loops , each processing 6667 records out of total 200k records?

The two worker processes are additional to the session backend process, which is the “parallel leader”, so there are three processes working on the scan.


SSIS 2012 Ftp parallel/asynchronous multiple file download Any help would be appreciated with either scenario.
if
Are there any settings available to download multiple files in parallel from an FTP server?
else
If not, I guess I'll just roll my own via C# script source component using: List Directory Contents with FTP, FtpWebRequest Class, and C# Multiple Download from FTP using parallel task - Duplicate Download issue to get going.
other details

SSIS 2012 saving to a local Windows Server 2012 directory
100 + ascii text files
6 sibling folders
Filename convention: AAAA_YYYYMMDD.txt
File size ranges from 5KB - 15MB
Currently only able to download one file at a time
Run once per day manually at this stage via SSDT (still prototyping/developing, so not on a production system, yet, but I plan to have Sql Agent running the packages in the future)
Downstream packages to load text file data into data warehouse staging tables (if script task is used then perhaps the response stream buffer can go into a multicast with 1 buffer stream writing to text files, while the other buffer stream writes to staging tables?)

Native, no.

Heck, I barely classify the out of the box FTP component as a real component. It meets some technical specification for FTP only for the most rudimentary operations. I've usually gone the route of calling ftp.exe from the Execute Process Task with a parameter file or just used the .NET libraries for doing so.

I had never thought about parallelizing FTP downloads but this question over on SO looks like a valid implementation of it https://stackoverflow.com/questions/18117536/c-sharp-multiple-download-from-ftp-using-parallel-task-duplicate-download-issu

Depending on how you design things, I've come to the conclusion that having packages download files and then perform operations on the file(s) it might have found is a painful pattern. Instead, I find I've had better results with separating those activities and making an assumption that the consuming package will only run if data already exists. This allowed me to make radical changes to how I acquired the data (we went from SFTP to FTP with no core package change) without having to validate/retest the processing of the data. Might not be an issue for me but simplified my compliance life.

The net result of the above was that my agent job went from "run Package" to "run ftp package", "test for existence", "run processing package" or "alert that no file found". Modularization allowed us more flexibility as well as letting more people work on the problem vs one person working on a monolithic package.


Composite constraint of numrange and boolean I have a current constraint like this:

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH &&);

This works fine, but I need to add one more thing to the constraint, it must only be enforced for rows where a boolean column is true (let's call it active). I tried this:

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH && active = TRUE);

This throws syntax error:

ERROR: syntax error at or near "active"
SQL state: 42601

Is it possible to have the constraint include the active column as well?

You can add a WHERE clause to make it a partial EXCLUSION constraint (creating a partial index to implement it):

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint
  EXCLUDE USING gist ((uuidcol::text) WITH =, numrange(start, end) WITH &&)
  WHERE (active);

The parentheses are not optional. The manual:

  The predicate allows you to specify an exclusion constraint on a
  subset of the table; internally this creates a partial index. Note
  that parentheses are required around the predicate.

BTW, in Postgres 10 or later you can install the additional module btree_gist to add (among others) the necessary operator class for type uuid in GiST indexes. So you can simplify:

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint
  EXCLUDE USING gist (uuidcol WITH =, numrange(start, end) WITH &&)
  WHERE (active);

Considerably more efficient than casting to text. Related:

What is the optimal data type for an MD5 field?


Is any way to restructure MongoDB? I have a MongoDB with compound index by 4 fields and I want to reorder physical data distribution at HDD by this index. I am looking for something like creating chunk in NOT sharded environment.
Is any way to do this?

UPDATE:
As workaround solution I can enumerate my collection by index and write it to another collection in this order. I didn't test it, but it should make sence.

The feature you are describing is a clustered index, which MongoDB (as at 3.2) does not support.

The most relevant feature request to upvote/watch is currently SERVER-3294.

  I am looking for something like creating chunk in NOT sharded environment.

Chunks in a MongoDB sharded cluster are metadata representing contiguous ranges of shard keys. All documents for a given chunk range will exist on the same shard, but the chunk does not currently determine the locality of data on disk.


How to remove leading zero from date I have a query in which i have used the following

date_add(DATE_FORMAT(ss.week_startdate,'%y-%m-%d'),INTERVAL (tt.day_id- 1 )DAY) as date

Datatype of the field is datetime and the result date is like this

2015-04-06

but the desired result is like this

2015-04-6

I don't need 0 in front of day. I tried this

date_add(DATE_FORMAT(ss.week_startdate,'%y-%m-%e'),INTERVAL (tt.day_id- 1 )DAY) as date

but didn't work. Can anyone help please!

You have the functions applied in wrong order. You need to do date arithmetic first (date_add, whatever) and apply the format last:

DATE_FORMAT( DATE_ADD(ss.week_startdate, INTERVAL (tt.day_id - 1) DAY),
             '%Y-%m-%e')

You also need %Y for a 4-digit year, not %y.

Also note that it's better to also change the first query, as it does a lot of redundant operations:

date_add(
    DATE_FORMAT(ss.week_startdate,'%y-%m-%d'),
                INTERVAL (tt.day_id- 1 )DAY
               )
as date

First, it calculates: DATE_FORMAT(ss.week_startdate,'%y-%m-%d')
which converts the datetime value to a string with format (example for current date, 2015 April 28): '15-04-28'.
Then, it does: date_add( previous_result,  INTERVAL (tt.day_id-1) DAY)
But the previous_result is a string so it has to be converted to date or datetime. It's only by accident that the 2-year format does not lead to a wrong date. After this conversion, the date_add() is calculated and because the first argument (after the conversion) is a date and you only add days, not hours or smaller unit, the result is also a date.
Then, the as day is considered but this does nothing, as the previous operation has resulted in a date, too.
Finally, the result is shown in the default format for dates, which (by one more accident!) is %Y-%m-%d, to get the wanted format.

In all, three conversions (datetime -> char -> date -> char), while you only need one.


What does Oracle use for handling a client connection (process or thread)? SQL Server 2016 and MySQL 5.7 use thread-based connection handling.

PostgreSQL 9.5 uses one operating system process for each individual connection.

According to Oracle 12c documentation it's not clear if a thread-based or a process-based model is used. So how does Oracle handle user connections?

Oracle up to 11g used a thread based model on Windows and a process based model on Linux.

Quote from the manual:

  A process is a mechanism in an operating system that can run a series of steps. The mechanism depends on the operating system. For example, on Linux an Oracle background process is a Linux process. On Windows, an Oracle background process is a thread of execution within a process

You can optionally enable so called "shared processes" where one process handles several client connections. Think of that as a kind of in-database connection pooling. On Windows this would mean a single thread handles multiple connections, on Linux this would mean a single process handles multiple connections.

Starting with 12c you can optionally enable the thread-based model for a Linux installation.

Quote from the manual:

  In releases earlier than Oracle Database 12c, Oracle processes did not run as threads on UNIX and Linux systems. Starting in Oracle Database 12c, the multithreaded Oracle Database model enables Oracle processes to execute as operating system threads in separate address spaces


Find rows in which the column doesn't contain a "space" I'm using Postgres 9.5.  I want to search for rows in which my name column does not contain a space.  I'm a little murky on how to define a space to you, though. I thought it would just be the space bar on my keyboard, so I ran:

.... where name not like '% %';

but then I got some results like this:

 | JASON FALKNER

That sure looks like a space to me, but there are probably some other things going on. Is there a better way I can scan for rows in which my name column doesn't contain a space?

Using a regexp, not (name ~ '\s') still returned columns that looked like they had a space.

Using:

select cast(name as bytea) ... where name not like like '% %';

returned:

\x4a41534f4ec2a0424c414b45

However, I'm still a little unclear how I use that data to figure out how to screen spaces from my results.

I tried where not (name ~ '[[:space:]]')' and it's returning "JASON BLAKE" with the same byte sequence above, \x4a41534f4ec2a0424c414b45.

I suggest you provide explicitly the characters you want to be considered as "white space" and excluded to a regex:

where name !~ '[ \t\v\b\r\n\u00a0]'

Characters:

\s         white space (space, \r, \n, \t, \v, \f)
' '        space
\t         (horizontal) tab
\v         vertical tab
\b         backspace
\r         carriage return
\n         newline
\f         form feed
\u00a0     non-breaking space
---

See the docs for pattern matching.

In your example, note that \xC2A0 is the UTF-8 representation of Non breaking space (00A0).


CREATE TABLE IF ONLY NOT EMPTY RESULT SET I'd like to create a table that consists of errors (incorrect format of data for my table, invalid geometry, etc). I have tried both SELECT INTO and CREATE TABLE AS queries, but both of them creates a table even if the resulting set is empty (0 records). Is there an option to perform this kind of query which won't create a table if empty set?
The reason for this request is I have to then collect all error tables and make a dump (to be more specific, a gbd with ogr2ogr) of them, so empty tables will be  disturbing.
P.s. Please do not recommend after creating tables to drop empty tables using information_schema data, I'm looking for more efficient way of dealing with this. Thank you.

You can create your tables in PL/pgSQL DO blocks (or functions if it makes sense). Check if a table is empty after creation and throw an exception if it is. Catch that exception (and do nothing). That rolls back all the changes the block (or function) made, i.e. the creation of the table, but none of the outer transaction.
For example, if you do
DO
$$
BEGIN
  CREATE TABLE elbat_1
  AS
  SELECT *
         FROM (VALUES ('a'),
                      ('b'),
                      ('c')) v (nmuloc_1)
         WHERE 1 <> 1;

  IF NOT EXISTS (SELECT *
                        FROM elbat_1) THEN
    RAISE EXCEPTION no_data_found;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
END;
$$
LANGUAGE plpgsql;

DO
$$
BEGIN
  CREATE TABLE elbat_2
  AS
  SELECT *
         FROM (VALUES ('a'),
                      ('b'),
                      ('c')) v (nmuloc_1)
         WHERE 1 = 1;

  IF NOT EXISTS (SELECT *
                        FROM elbat_2) THEN
    RAISE EXCEPTION no_data_found;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
END;
$$
LANGUAGE plpgsql;

elbat_1 will never be seen anywhere outside of the respective block. But elbat_2 will.
If you have another layer around that grants you transaction control, like you're writing some application, and can split each table creation into its own transaction, you can also simplify that and just create the table, check for it to be not empty and commit or otherwise rollback the transaction. Other transactions won't ever see the table if it's empty (unless it gets somehow emptied in another, committed transaction other than this one). You won't need the PL/pgSQL blocks then. Something along the lines of:
-- let your application start a transaction
BEGIN TRANSACTION; -- or do that by the means of transaction control functions of the adapter library
-- then create the (one) table
CREATE TABLE elbat
             AS ...;
-- return the result of
SELECT EXISTS (SELECT *
                      FROM elbat);
-- to your application and if it is true
COMMIT; -- or do that by the means of transaction control functions of the adapter library
-- otherwise
ROLLBACK; -- or do that by the means of transaction control functions of the adapter library


Why Jira can't connect database with pgBouncer? pgbouncer.ini

[databases]
jiradb = host=127.0.0.1 port=5432 dbname=jiradb

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

userlist.txt

"postgres" "postgres1"

It can be connected by psql:

psql -p 6432 -h 127.0.0.1 -U postgres jiradb
(Prompt password: postgres1)

But try connect from application:

Jira dbconfig.xml:

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>postgres72</database-type>
  <schema-name>public</schema-name>
  <jdbc-datasource>
    <url>jdbc:postgresql://127.0.0.1:6432/jiradb</url>
    <driver-class>org.postgresql.Driver</driver-class>
    <username>postgres</username>
    <password>postgres1</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <validation-query>select 1</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
  </jdbc-datasource>
</jira-database-config>

When restart Jira, the database can't be connected again:

Database: JIRA couldn't connect to your database

JIRA failed to establish a connection to your database.
This could be because:

    Your database isn't running
    The configuration of your dbconfig.xml file is incorrect (user, password, or database URL etc.)
    There is a network issue between JIRA and your database (e.g. firewall, database doesn't allow remote access etc.)

I am testing it in one server. Jira is installed at jira user. pgBouncer is running under postgres user.

This works:

<url>jdbc:postgresql://127.0.0.1:5432/jiradb</url>


Find the last visit by teacher and show the rest of columns in MySql I would like to group the teachers by the last visit and show the other values corresponding to the last visit.

CREATE TABLE IF NOT EXISTS `visits` (
`FIELD1` int(5) NOT NULL,
`teacher` varchar(9) DEFAULT NULL,
`visit_time` int(5) DEFAULT NULL,
`1A` varchar(1) DEFAULT NULL,
`2A` varchar(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I used this query

 SELECT i1.* FROM visits AS i1 LEFT JOIN visits AS i2
ON (i1.teacher = i2.teacher AND i1.visit_time < i2.visit_time) WHERE i2.visit_time IS NULL

The query in general gives correct results, but in the end there are more teachers than expected (for example when using group by teacher).

You need to join all the teacher's last visit_time value against the whole visits table

SELECT B.* FROM
(
    SELECT teacher,MAX(visit_time) visit_time
    FROM visits GROUP BY teacher
) A INNER JOIN visits B USING (teacher,visit_time);

Based on this query, you should also add the following index

ALTER TABLE visits ADD INDEX teacher_visit_time_ndx (teacher,visit_time);

Give it a Try !!!


Unable to start Oracle Database Server : Get an error as I try to start it I just installed Oracle Database Express Edition 11g Release 2 for windows.It created a short cut icon on the desktop :

but as I click this icon I see this dialog box :

What is it ? How do I start my Oracle Database Server ?

Here are the instructions on how to start the database:
Starting Oracle Database XE

Normally you first need to start the database and then you can access it with that link. I don't remember if the database is started after you do the installation.


wait on database engine handle recovery failed I am installing SQL 2012 and getting error wait on database engine handle recovery failed,  changed the credentials to "NT Authority\NETWORK SERVICE" but getting same error:

It sounds like you have TLS 1.2 enabled, but:

you're not running a build of SQL Server 2012 that supports TLS 1.2, and / or
you're not running a version of the .NET Framework that supports TLS 1.2

The following KB lists out the 2012 builds that support TLS 1.2:

TLS 1.2 support for Microsoft SQL Server
- SP2
  - 11.0.5644.2 (CU 10) or
  - 11.0.5352.0 (GDR update)
- SP3
  - 11.0.6518.0 (CU 3)
  - 11.0.6216.27 (GDR update)

The .NET Framework version you need depends on your Windows Server version, and is listed out in the following KB article:

FIX: Error when you install SQL Server 2012 or SQL Server 2014 on a server that has TLS 1.2 enabled

It's also possible that you don't have TLS enabled at all, which is required to run the product.  So the solution may be that you just need to enabled TLS 1.0 or SSL 3.0 (although 1.2 is recommended).


Find addresses shared by more than one customer_id where customer_name is is distinct I need to return the list of addresses that have more than one customer_ID associated with them, exclude the customer_IDs that have the same customer_name and return the number of time the address repeats

Here is what the data looks like:

CUSTOMER_ID   |   CUSTOMER_NAME   |   ADDRESS           | MEMBER_SINCE
----------------------------------------------------------------------
A1000         |      John Doe     |    123 West Lane    |  2/23/2005

A1001         |      John Doe     |    123 West Lane    |  9/30/2005

A1002         |      Tom White    |    456 East Street  |  5/7/2006

A1003         |      Frank Smith  |    123 West Lane    |  12/12/2006

A1004         |      Lisa Simpson |    456 East Street  |  7/19/2007

A1005         |      Clark Kent   |    700 North Ave.   |  2/23/2008

A1006         |      Darth Vader  |    123 West Lane    |  9/29/2008

A1007         |      John Doe     |    456 East Street  |  5/23/2007

A1008         |      Bart Star    |    456 East Street  |  4/19/2006

I want the result set to look like:

CUSTOMER_ID   |   CUSTOMER_NAME   |   ADDRESS           | MEMBER_SINCE| COUNT
--------------------------------------------------------------------------------
A1000         |      John Doe     |    123 West Lane    |  2/23/2005  |  3

A1002         |      Tom White    |    456 East Street  |  5/7/2006   |  4

A1003         |      Frank Smith  |    123 West Lane    |  12/12/2006 |  3

A1004         |      Lisa Simpson |    456 East Street  |  7/19/2007  |  4

A1006         |      Darth Vader  |    123 West Lane    |  9/29/2008  |  3

A1007         |      John Doe     |    456 East Street  |  5/23/2007  |  4

A1008         |      Bart Star    |    456 East Street  |  4/19/2006   |  4

The result shows the CUSTOMER_IDs, CUSTOMER_NAMES, ADDRESSES, MEMBER_SINCE and the COUNT of distinct CUSTOMER_IDs that share the address, while ignoring the rows that have same ADDRESS and CUSTOMER_NAME as another row even though the CUSTOMER_ID is different.  Any ADDRESSES that do not have more than one CUSTOMER_ID are also excluded.

Notice that CUSTOMER_ID A1001 is absent in the result since the CUSTOMER_NAME and ADDRESS are the same and must be a duplicate, and A1005 is absent since there are no other customers with that address.

This is the start:

SELECT
  CUSTOMER_ID as CUSTOMER_ID,
  "CUSTOMER_NAME" as CUSTOMER_NAME,
  "ADDRESS",
  "MEMBER_SINCE",
  count(CUSTOMER_ID) as COUNTER
FROM CUSTOMER_TABLE
  group by ADDRESS_ID, CUSTOMER_ID, CUSTOMER_NAME
  having
  count(CUSTOMER_ID)>1
  order by CUSTOMER_ID;

But I can't manage to take out the duplicate CUSTOMER_NAMES.

This is my first post here and it is very late for me, so I apologize in advance if I didn't post the question correctly.

AR

The query above groups by ADDRESS, CUSTOMER_ID and CUSTOMER_NAME. Since CUSTOMER_ID is unique, the output will have 1 row for each CUSTOMER_ID.

This query gives the correct result:

SELECT
    CUSTOMER_ID = MIN(ct.CUSTOMER_ID)
    , ct.CUSTOMER_NAME
    , ct. ADDRESS
    , MEMBER_SINCE = MIN(ct.MEMBER_SINCE)
    , cnt.c
FROM CUSTOMER_TABLE ct
INNER JOIN (
    SELECT ADDRESS, COUNT(DISTINCT CUSTOMER_NAME) as c
    FROM CUSTOMER_TABLE
    GROUP BY ADDRESS
    HAVING COUNT(ADDRESS) > 1
) as cnt ON cnt.ADDRESS = ct.ADDRESS
GROUP BY ct.ADDRESS, ct.CUSTOMER_NAME, cnt.c
ORDER BY MIN(ct.CUSTOMER_ID);

It should group by ADDRESS andCUSTOMER_NAMEonly and pick one of theCUSTOMER_IDandMEMBER_SINCE. I choose theMIN(value)` here.

Since you also need the count of distinct CUSTOMER_ID with similar ADDRESS, it has been added to a subquery.

Result:

CUSTOMER_ID | CUSTOMER_NAME | ADDRESS           | MEMBER_SINCE              | COUNT
A1000       | John Doe      | 123 West Lane     | 2005-02-23 00:00:00.000   | 3
A1002       | Tom White     | 456 East Street   | 2006-05-07 00:00:00.000   | 4
A1003       | Frank Smith   | 123 West Lane     | 2006-12-12 00:00:00.000   | 3
A1004       | Lisa Simpson  | 456 East Street   | 2007-07-19 00:00:00.000   | 4
A1006       | Darth Vader   | 123 West Lane     | 2008-09-29 00:00:00.000   | 3
A1007       | John Doe      | 456 East Street   | 2007-05-23 00:00:00.000   | 4
A1008       | Bart Star     | 456 East Street   | 2006-04-19 00:00:00.000   | 4

Note that I don't have access to SAP-Hana. However it seems to be pretty standard SQL and with query should work on most RDBMS.


Will a VM backup of a SQL Server work for restoring the SQL database I am wondering if a VM backup will restore a SQL database with out integrity issues....  Ie…    we backup SQL nightly and keep 3 copies, we backup with VM and keep 14 copies....  if we had to restore day 10 from VM would we have any integrity issues, I know we could not do log recovery, but that point in time would be better than nothing.  We have completed a test restore and it seemed to work, but I would like the experts answers also.   Thanks

I am wondering if a VM backup will restore a SQL database with out integrity issues

Maybe. If you have databases that span local volumes, then your VM backup must be "app consistent" by either using the Windows Volume Shadow Copy Service (VSS), or by ensuring write-ordering across the VM's volumes.

If you're not 100.000% sure of this, you can't rely on VM backups for database recovery.  You can always start with a VM backup and restore your SQL Server backups on it for a more robust recovery solution.


Database file ..._log has a max file size set to x MB. If it runs out of space, the database will stop working I am using BrentOzar's SQL Server First Responder Kit since I fall under the category "developer who manages Microsoft SQL Servers. It's your fault if they're down or slow."

One of the warning of running sp_blitz says the following:

  The [DB] database file DB_log has a
  max file size set to 10240MB. If it runs out of space, the database
  will stop working even though there may be drive space available.

Log files are on a separate drive from OS and I indeed limited their growth. All databases have simple recovery model (most of the data is automatically obtained through replication and ETL and recovering from last backup is enough for me).

The database has about 20GB of data.

Question: how can the database stop working if the recovery model does not force me to backup the log?

When someone does a BEGIN TRAN, inserts a row, locks their computer, and goes home for the weekend, your log file will grow and grow and grow.

That would be a Bad Thing™.

Rather than facing an outage, you're better off letting the log file grow, and using your monitoring tool to get alerts that there's a problem.


PostgreSQL custom operator UUID to varchar I have a rather complicated Postgres database in which many UUID fields were incorrect stored as VARCHAR. I'd like to migrate them over in piecemeal, but unfortunately, doing so breaks all my views as Postgres doesn't have a built in operator for varchar = uuid. Rather rewrite all my views or attempt a single massive migration, I wanted to temporarily create a uuid = varchar operator  until the migration is completed.

I've never created a custom operator before and my attempt to below is not working:

CREATE OR REPLACE FUNCTION uuid_equal_varchar (varchar, uuid)
RETURNS boolean AS 'SELECT $1::text = $2::text;' LANGUAGE sql IMMUTABLE;

CREATE OPERATOR = (
    leftarg = character varying,
    rightarg = uuid,
    procedure = uuid_equal_varchar,
    commutator = =
);

However this operator breaks everything. Including a simple varchar = varchar comparison (see below):

SELECT * FROM test WHERE pk_test = '123';
ERROR:  invalid input syntax for uuid: "123"

Can someone explain to me what I am doing wrong? Am I trying to attempt something that is not possible?

What you to do is CREATE CAST not an operator. This is the problem:

SELECT pg_typeof(uuid), uuid = uuid::varchar AS eq
FROM gen_random_uuid() AS t(uuid);
ERROR:  operator does not exist: uuid = character varying
LINE 1: SELECT pg_typeof(uuid), uuid = uuid::varchar FROM gen_random...
                                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

So we need to create a CAST. This promotes varchar to uuid when needed. Though you could go the other way if you really wanted. If you do this you need to create the cast (uuid AS text). The type system doesn't know varchar: we don't use that in PostgreSQL; it's essentially text with a type-inconsequential length constraint and thus slower.

CREATE CAST (varchar AS uuid)
  WITH INOUT
  AS IMPLICIT;

And now you can try again.

 pg_typeof | eq
-----------+----------
 uuid      | t
(1 row)

For reference,

IMPLICIT

  Indicates that the cast can be invoked implicitly in any context.

INOUT

  Indicates that the cast is an I/O conversion cast, performed by invoking the output function of the source data type, and passing the resulting string to the input function of the target data type.

That said, all of the views have to be recreated when an underlying type changes,

CREATE TABLE foo(uuid)
AS
  VALUES (gen_random_uuid()::varchar);

CREATE VIEW bar AS TABLE foo;

Now we try to change the type in foo

ALTER TABLE foo
  ALTER uuid
  SET DATA TYPE uuid;
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view bar depends on column "uuid"

That fails, so we drop bar change the type and recreate it,

BEGIN;
  DROP VIEW bar;
  ALTER TABLE foo ALTER uuid SET DATA TYPE uuid;
  CREATE VIEW bar AS TABLE foo;
COMMIT;

And we have joy.

 \d bar;
     View "public.bar"
 Column | Type | Modifiers
--------+------+-----------
 uuid   | uuid |


mysql database connections will be migrated I am not DBA, just use MySQL on local machine for testing my apllications.

I have MySQL server with Workbench and Notifier on Windows machine. MySQL Notifier periodically shows message "mysql database connections will be migrated" with possibility to click "No".
What does this message means? Why it wants to migrate connections?

This answer was given at the MySQL forum by Javier Treviño from the  MySQL on Windows Team:

  Some products are able to share connection information to MySQL Server
  instances with MySQL Workbench. The products that do this are MySQL
  for Excel, MySQL Notifier and MySQL for Visual Studio.

  By sharing connection information this means that if MySQL Workbench
  is installed in the same computer, all of these programs will attempt
  to use the same configuration file where MySQL Workbench stores the
  information about connections.

  So, if any of the 3 aforementioned products are installed but MySQL
  Workbench is not, they will fallback to use their own separate
  configuration file to store connection information, but as soon as
  they detect MySQL Workbench is present, they will attempt to migrate
  or move the connections stored in the separate configuration file to
  the file MySQL Workbench uses.

  The product presents a dialog with the message you have been talking
  about in this forum thread. You should notice there is a More Info
  button displayed in that dialog. If you click it an information
  message like this will be shown:

    {APP_NAME} now works with the connections file used by MySQL Workbench
    if it is installed.

    {APP_NAME} will attempt to merge all existing connections with MySQL
    Workbench and in order to do this MySQL Workbench must not be running.

    Please make sure MySQL Workbench is closed to continue with the
    merging operation.  If you choose not to migrate connections now, you
    will be prompted later to perform the migration.

  Also note that in the latest versions of the 3 aforementioned products
  the migration of connection information can be delayed to be done at a
  later time or delayed indefinitely, which means the connection
  information will remain in the product's configuration file until a
  manual migration of this information is performed.


Local Cosmos DB service unavailable I've installed the azure cosmos db in my local machine but is not loading the explorer pages as shown below:

Explicit error:

Failed to get authorization headers for offers: {"readyState":4,"responseText":"{\"code\":\"ServiceUnavailable\",\"message\":\"Service is currently unavailable.\r\nActivityId: d728958b-c0b4-4b32-a131-bba1dc7a8ba0, \r\nResponseTime: 2018-06-05T17:22:33.8740685Z, StoreReadResult:

This issue usually happens when we have Pulse Secure installed, I have uninstalled Pulse Secure as it was no longer needed and that fixed the problem.

Related Post:
https://stackoverflow.com/questions/43569917/documentdb-emulator-not-working-service-unavailable?rq=1


How to count number of rows inside Oracle dump file? I have a huge (38Gb after bzip2) Oracle EXP dump file. It contains one table.
Such big dumps usually end up with an error, but in fact contain the full table.

Earlier I was importing these tables back into DB to check that they are ok.
But it takes significant amount of time and resources.

Easy way to check that the file is fine - is to see how many rows in it. To compare with database (this table for example has 4 464 761 311 rows).
But because export ended with an error, EXP did not show how many rows it dumped.

So how is it possible to see how many rows inside dump file without importing it?

Export was done with such parameters: exp direct=y recordlength=65535 and there are no visible sql commands in it, just the data.

grep -a -A 5 METRICSTposttables table.dmp

outputs:

METRICSTposttables actions
METRICET 381
METRICSTPost-inst procedural actions
METRICET 381
METRICSTDeferred analyze commands
TABLE "my_table"

Where 381 matched the export count:

. . exporting table          MY_TABLE        381 rows exported


2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL Ref : https://stackoverflow.com/questions/32066097/group-by-with-having-distinct-php-mysql

I got answer (thanks coytech ) but i need one more column in its :

as per :

id | mid | pid | owgh | nwgh |
1    3      12    1.5    0.6
2    3      12    1.5    0.3
3    3      14    0.6    0.4
4    3      15    1.2    1.1
5    4      16    1.5    1.0
6    4      17    2.4    1.2
7    3      19    3.0    1.4

i got answer

  Select mid ,
        COUNT(distinct pid) as cpid  ,
        SUM(nwgh) as totalnwgh  from test  GROUP BY mid

sqlfiddle : http://sqlfiddle.com/#!9/45e68/2

mid  cpid       totalnwgh
3      4          3.8
4      2          2.2

But above i need one more column that's as below : totowgh

mid cpid        totalnwgh  totowgh
3      4          3.8        6.3 (DISTINCT value as per pid column)
4      2          2.2        3.9

where totowgh = 6.3 come by DISTINCT value of owgh column  as per distinct pid column

mid = 3 has count 5 but distinct pid  = 4 for mid=3 same way "distinct" owgh = 6.3 for mid=3 and distinct pid.

As pid=12 , column owgh value 1.5 is twice ...hence its is count 1 time hence,

1.5 + 0.6 + 1.2 + 3 = 6.3 ( please not this is as per DISTINCT value of pid )

Please note : i need distinct owgh value as per distinct pid or group by pid .. because if i replace value of owgh 0.6 with 1.5 then it will be 5.7 instead of 7.2 but value of owgh 0.6 belong to pid = 14 and not pid = 12 hence totalcount of owgh change ...but i need is 7.2

SEE WHAT I MEANS   : sqlfiddle.com/#!9/2a53c/6

I believe you are asking for the sum of "distinct" values of owgh for each value of mid and pid - a different "granularity" of data.

e.g. for mid = 3 then add 1.5 + 0.6 + 1.2 + 3.0 = 6.3; only count the 1.5 once instead of twice.
However if your record ids for the 1.5 were for 2 different pid values, then you would want to count the 1.5 twice.

One way you could do it is instead of querying the "test" table directly, calculate some of the aggregates beforehand:

Select mid,
COUNT(distinct pid) as cpid,
SUM(nwgh) as totalnwgh,
SUM(owgh) as totowgh    -- I added this one
from
    (select mid, pid, sum(distinct owgh) as owgh, sum(nwgh) as nwgh from test group by mid, pid) as A
GROUP BY mid


Automating an SQL task on a monthly basis At the start of each month I have to give a team a copy of the live database which is held in a backup from 1am each morning (D:\Backup) - We then copy this database elsewhere onto a dev server and restore it from the share.

When January is completed at the end of the month we repeat the process for the Feb - this time we take a copy of what has been worked on during the month of Jan by the team and back that up into an archive. The February 1st database then goes live onto the dev server.

Is it possible to automate this process? Our techie left us recently and didn't document this process so I've managed to do it manually thus far but imagine it can be automated.

What you are telling is possible.

A pseudo code would look like this :

[ Make sure to use database compression and have instant file initialization enabled.]

Check if it is first day of the month using select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

if it is first day of month, backup the previous month database to archive as db_name_datetime_Month_archive.bak.
backup the live PROD database and copy the file to DEV server and restore / overwrite the current database - use WITH REPLACE option when restoring.
Sync up users
Optionally since you are restoring database from PROD, you may have to sanitize it - remove PII or any sensitive information.
send out email to the concerned teams (DEV in your case) saying the restore or refresh job has been completed.

For backup and restore you can use PowerShell (make sure you use backup and restore method) or tsql.

You can completely automate the above pseudo code using sql agent job.


Performance difference between Text and Varchar in Mysql I was experimenting with text and varchars in mysql 5.7. I create a simple mysql table as below CREATE TABLE small_table (
  pk int(11) NOT NULL AUTO_INCREMENT,
  varc varchar(255) DEFAULT NULL,
  txt text,
  PRIMARY KEY (pk)
) ENGINE=InnoDB AUTO_INCREMENT=103925 DEFAULT CHARSET=utf8

The table has around 100k rows.

I have executed the below queries and profiled the both

Query 1 (Group by on varchar column)

select varc,count(*) from small_table group by varc;

Profile 1 result

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000064 |
| checking permissions | 0.000004 |
| Opening tables       | 0.004812 |
| init                 | 0.000026 |
| System lock          | 0.000005 |
| optimizing           | 0.000006 |
| statistics           | 0.000010 |
| preparing            | 0.000006 |
| Creating tmp table   | 0.000021 |
| Sorting result       | 0.000003 |
| executing            | 0.000001 |
| Sending data         | 0.052795 |
| Creating sort index  | 0.000049 |
| end                  | 0.000003 |
| query end            | 0.000006 |
| removing tmp table   | 0.000004 |
| query end            | 0.000002 |
| closing tables       | 0.000006 |
| freeing items        | 0.000021 |
| cleaning up          | 0.000018 |
+----------------------+----------+

Temp table Details 1

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+

Query 2 (Group by on text column)

select txt,count(*) from small_table group by txt;

Profile 2 result

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000134 |
| checking permissions |  0.000010 |
| Opening tables       |  0.006690 |
| init                 |  0.000034 |
| System lock          |  0.000007 |
| optimizing           |  0.000004 |
| statistics           |  0.000017 |
| preparing            |  0.000008 |
| Creating tmp table   |  0.000027 |
| Sorting result       |  0.000005 |
| executing            |  0.000002 |
| Sending data         | 26.359877 |
| Creating sort index  |  0.001042 |
| end                  |  0.000007 |
| query end            |  0.000007 |
| removing tmp table   |  0.000208 |
| query end            |  0.000003 |
| closing tables       |  0.000007 |
| freeing items        |  0.000028 |
| cleaning up          |  0.000020 |
+----------------------+-----------+

Temp table Details 2

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+

Both profiles took almost same time in each stage expect 'Sending data stage'. For first one it took 0.052795s and for second one it took 26.359877s. Why there is that much difference in that stage? (same queries without group by clause show no difference in 'Sending data stage')

The performance difference is mainly due to the fact, that text datatype columns are always copied to temporary tables on disk, because the memory engine doesn't support those types.

From the manual:

  Instances of BLOB or TEXT columns in the result of a query that is
  processed using a temporary table causes the server to use a table on
  disk rather than in memory because the MEMORY storage engine does not
  support those data types (see Section 8.4.4, “Internal Temporary Table
  Use in MySQL”). Use of disk incurs a performance penalty, so include
  BLOB or TEXT columns in the query result only if they are really
  needed.

When you don't use group by no temporary table is needed, obviously.


Can't enable binary logging ("index not found") I have a MySQL 5.5.41 server running on Ubuntu 14.04 that already contains several databases of data, and I would like to enable binary logging.  I want the binary logs to be placed in their own directory, /var/log/mysqld, which did not exist prior.  I created the directory, set its permissions to mysql:root 0755, and added a file to /etc/mysql/conf.d with the following contents:

[mysqld]
log_bin=/var/log/mysqld/mysql-bin.log
expire_logs_days=1
binlog_format=ROW

Unfortunately, running service mysql restart then failed to bring MySQL back up, and the MySQL error logs contain the message:

/usr/sbin/mysqld: File '/var/log/mysqld/mysql-bin.index' not found (Errcode: 2)

What do I have to do to get this to work?

It turns out the problem was caused by apparmor.  I had to add the following two lines to /etc/apparmor.d/usr.sbin.mysqld:

  /var/log/mysqld/ r,
  /var/log/mysqld/* rw,

and then run apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld before restarting MySQL.


how to decrease column values by 20 percent using mysql i have a procutd with this price: 60.33. When i increase the price to 20% i got like result 72.39 that is correct. but when i am tryng to decrease to 20% i dont have the same price that i had before. i am having 60.37 some one can help me to tell me what i have to do exactly?

This is the script sql i made to that:

round(listprice +(listprice *(20/100)),2)//Increase 20%
round(listprice -(listprice *(20/100)),2)//Decrease 20%

You need the reciprocal value of 20%, because you're new value isn't 120% it is the new 100% value.

round(listprice * 1.2,2) -- increase by 20%

The reciprocal for above +20% would be:

round(listprice / 1.2,2) -- decrease by 17.777777%

Or put basically: If you want to counteract an increase of 20% then you need to reduce by 17.7777777%.

You can verify this on Windows with the calculator. Beware of the round() function!

Mathematics Explanation

Multiplying a value with a number is another way of adding. If you want to add 100% to a given value, then you can multiply the value with 2:

listprice * 2

If we replace listprice with 100 then we have:

100 * 2 = 200

This is an increase of 100% compared to the starting value of 100.

If you want to increase a value by 20% then you want to add a fifth of the base value. This is the equivalent of multiplying by 1.2:

100 * 1.2 = 120

Now multiplying by 1.2 can counteracted by dividing the result by 1.2 again:

120 / 1.2 = 100

This is the equivalent of what you are looking for. You want to counteract the addition of listprice +(listprice *(20/100)).

Working Example

A working example can be found at db<>fiddle (MySQL 8.0)

Using the Original Addition

After poring over your equation last night and this morning I found a way of using your original "addition".

                20
x = y + ( y * ----- )
               100

This can be converted to:

                20
x = y * ( 1 + ----- )
               100

Which as you may note is a multiplication again. So if you were to want the original value of y, then all you need to do is to divide by original multiplicand:

                20
y = x / ( 1 + ----- )
               100

Or written as :

          x
     ------------
y =         20
     (1 + ----- )
           100

In your MySQL equation this would be:

listprice = round(listprice / (1 + (20/100)),2)

Working Example Using Addition

A working example can be found at db<>fiddle (MySQL 8.0)


Oracle listener does not restart because port 1521 is not released fast enough after stop I'm running Oracle 11g on a Windows 2012R2 server. For some reason we have some code that try to restart the listener and then perform a connection. This code has been working for years on many different servers but on one particular server, the listener cannot restart.
Here is the error found in the listener.log file when the listener tries to restart:
Started with pid=5132
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=thehostname)(PORT=1521)))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   64-bit Windows Error: 48: Unknown error
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))

I've managed to reproduce the issue using this command:
net stop OracleOraDb11g_home1TNSListener && net start OracleOraDb11g_home1TNSListener

If I modify the command line like this:
net stop OracleOraDb11g_home1TNSListener && sleep 30 && net start OracleOraDb11g_home1TNSListener

The listener restarts correctly.
I suppose that the port 1521 is not released fast enough after stop, but I don't know how to fix it.
For information here is the content of the listener.ora file:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\somepath\database\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = somevalue)
      (SID_NAME = somevalue)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = thehostname)(PORT = 1521))
      )
    )
  )

LOGGING_LISTENER=on
VALID_NODE_CHECKING_REGISTRATION_LISTENER=on

This is Working As Designed™.
TCP socket lifecycle is managed by the TCP stack outside the database process(es), asynchronously, and involves various delays when transitioning between states. It's entirely conceivable that on a fast machine the listener will terminate and start back up within few milliseconds, faster than the socket is fully released.
Since you have no control over the TCP stack logic, your only alternative solution is, in my view, to introduce a slight delay before starting the listener (as you already did). You may not need the full 30 seconds; try smaller values.


Best name for link table I was wondering what the best name for a linked table in this situation (or any) would be. I've searched for this, but could not find any concrete examples.

For example, I have the following tables (where 'table in the name refers to tables in a restaurant):

restaurant_table
restaurant_table_feature

A table can have all kind of features and also multiple. Think of rounded tables, tables near the window, ground-floor tables, tables near entrance. It really depends on the type of restaurant, so it needs to be customizable. Arrangements are not permanent, since tables can be moved around for various reasons. Usually, the smallest possible combination of a table has a table number, if tables are combined for a group, multiple table numbers are used.

I want to link multiple features to a table row, with a linked table. Should I call the linking table restaurant_table_feature_table or restaurant_table_to_feature.

I've seen both possibilities in multiple systems, but which one would be the preferred one?

I also could simply rename restaurant_table_feature to restaurant_feature, and create a restaurant_table_feature table as link table. However if I also have a restaurant_location table for which I would have features (which differ from the table features) I would run into problems.

To be clear, my question is what the best name would be (for the many-to-many tables) in any situation with multiple many-to-many tables.

This is generally developer's choice but in my opinion restaurant_tables implies one-to-many relationship. When a table resolves many-to-many relationship I prefer names like restaurants_X_dishes. Again, this is up to my taste.


Selecting closest, non expired value I'm trying to select multiple rows where the value is closest to the end date but not passed, and return only those rows. Here's a sample of some data:

School Code | School Year | Semester | Term | Term End
========================================================
EXAMPLE     | 20142015    |    1     |  1   | 2015-01-23
EXAMPLE     | 20142015    |    1     |  2   | 2015-06-20
DEMO        | 20142015    |    1     |  1   | 2015-01-23
DEMO        | 20142015    |    1     |  2   | 2015-06-20

What I've done in my test case was:

SELECT TOP 1 * FROM School_Terms
WHERE Term_End >= '2015-01-20'

The problem is this only returns one row and not the first valid row for each school.

My expected results for the term end date '2015-01-20' are:

School Code | School Year | Semester | Term | Term End
========================================================
EXAMPLE     | 20142015    |    1     |  1   | 2015-01-23
DEMO        | 20142015    |    1     |  1   | 2015-01-23

and if the Term End was '2015-01-30' I would expect:

School Code | School Year | Semester | Term | Term End
========================================================
EXAMPLE     | 20142015    |    1     |  2   | 2015-06-20
DEMO        | 20142015    |    1     |  2   | 2015-06-20

I think this is a situation where you just need a cross apply.  If you are not familiar with it, look at this link.

With your data I created a table and inserted the data with this scrip

CREATE TABLE school
(
    schoolcode VARCHAR(20)
    ,SchoolYear INT
    ,Semester INT
    ,Term INT
    ,TermEnd DATETIME2

);

INSERT INTO dbo.school
        (
          schoolcode,
          SchoolYear,
          Semester,
          Term,
          TermEnd
        )
VALUES  ('EXAMPLE', 20142015, 1, 1, '2015-01-23')
, ('EXAMPLE', 20142015, 1, 2, '2015-06-20')
, ('DEMO', 20142015, 1, 1, '2015-01-23')
, ('DEMO', 20142015, 1, 2, '2015-06-20');

And then I run the select to get your desired results in the following fashion

SELECT DISTINCT t.*
FROM School s
CROSS APPLY (SELECT TOP 1  *
                FROM school s1
                WHERE s1.schoolcode = s.schoolcode
                AND s1.SchoolYear = s.SchoolYear
                AND TermEnd >= '2015-01-20'
                ORDER BY TermEnd) t

If you change your date, you should continue to get those desired results.

The APPLY operator can be your friend.


How to design database for market place website I do not have much experience in table design. My goal is to create a multi-vendor market place website with laravel  that meet the requirement below:

Support many kinds of products (10 producs) such as (Car, Home, ..., Book) that each kind of product has a different or same set of field, like:

Car has title, color, model, ..., price.
Home has title, place, #bedroom, ..., price
...
Book has title, author, ..., price

I have two options for design our database as following:

Create one table for all product with same fields and one table for each product with different field.
Create table for each product with all fields.

The question is which of two options is better and some help for how to design standard database?

Thanks for sharing your knowledge.

My approach would be to think in terms of objects. Like John Eisbrener mentioned, think of the types of products that would share common attributes, like model number, weight, ISBN, and so on. But also think of the people that would be involved. A vendor table can list the people selling, and a customers table would list the people buying.

As far as a best practice that I try to live by is don't repeat your data. A table recording a sale should not include a customer's delivery address, that should be referenced through the customers table by using a foreign key constraint. But most of all open up a program like Visio and start with the basic information tables you pretty much know you'll need, and build it out. It's much easier to make changes there than altering tables later


Error Msg 10314, Level 16, State 11 with SAFE assembly after upgrade to SQL Server 2017 I have a pre-SQL Server 2017 database that I restored into / upgraded to SQL Server 2017. In this database I have a SQLCLR Assembly. The Assembly is marked as SAFE since it doesn't do anything requiring a higher level of permissions, and the database has TRUSTWORTHY disabled / OFF. The SQLCLR functions and stored procedures worked as expected before moving to SQL Server 2017, but now when I try to execute any of them I get the following error:

  Msg 10314, Level 16, State 11, Server XXXXXXXXXXX, Line YYYYYY
        An error occurred in the Microsoft .NET Framework while trying to load assembly id ZZZZZ. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
        System.IO.FileLoadException: Could not load file or assembly '{assembly_name}, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

I have confirmed that CLR Integration / SQLCLR has been enabled on the server / instance.

The error is a result of the new clr strict security server-level configuration option in SQL Server 2017. This new security setting disallows any Assembly, even those marked as SAFE, from being either created or loaded into memory for execution, unless:

the Database property of TRUSTWORTHY is ON (don't do this as it is an unnecessary security risk) and the Login that is the database owner (i.e. same SID used by the [dbo] User) needs to have the UNSAFE ASSEMBLY permission (which it might already have if the owner is sa or a member of the sysadmin fixed server role or maybe has the CONTROL SERVER permission).
the Server-level configuration option of clr strict security is disabled / 0 (don't do this as it is an unnecessary security risk)
the Assembly is signed and you have a corresponding signature-based Login that has been granted the UNSAFE ASSEMBLY permission (please do this)
the SHA-512 hash of the Assembly is registered as a "Trusted Assembly" (absolutely never do this as there are numerous problems with it, not to mention that it was never needed in the first place)

All you need to do in order to fix this situation are the following steps (which are not difficult and provide the highest level of security):

Create a Certificate in the Database where the Assembly resides
Back up the Certificate (both Public and Private Keys) to files (optional)
Sign the Assembly (or Assemblies) with the Certificate
Remove the Private Key (it can be restored later from the backup file) (optional)
For each additional Database that has unsigned, SAFE Assemblies:

Either skip this sub-step (i.e. skip to Step 6), complete the steps, and repeat the whole process (this will result in one Certificate per each Database needing one), OR
Do the following (this will result in one Certificate total, which will be used for all Databases):

Create the same Certificate from the backup files (including the Private Key!)
Sign the Assembly (or Assemblies) with the Certificate
Remove the Private Key (it can be restored later from the backup file)

Copy Certificate to [master] (Public Key only!)
Create Login from Certificate
Grant the Certificate-based Login the UNSAFE ASSEMBLY permission

A simple example of this (minus the optional backup and Private Key removal) would be:

USE [{database_containing_unsigned_safe_assembly}];

CREATE CERTIFICATE [{certificate_name}]
  ENCRYPTION BY PASSWORD = '{some password}'
  WITH SUBJECT = '{simple description}',
  EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
  TO Assembly::[{assembly_name}]
  BY CERTIFICATE [{certificate_name}]
  WITH PASSWORD = '{some password}';

DECLARE @PublicKey VARBINARY(MAX),
        @SQL NVARCHAR(MAX);

SET @PublicKey = CERTENCODED(CERT_ID(N'{certificate_name}'));

SET @SQL = N'
CREATE CERTIFICATE [{certificate_name}]
  FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @PublicKey, 1) + N';';
PRINT @SQL; -- DEBUG

EXEC [master].[sys].[sp_executesql] @SQL;

EXEC [master].[sys].[sp_executesql] N'
CREATE LOGIN [{login_name}]
  FROM CERTIFICATE [{certificate_name}];

GRANT UNSAFE ASSEMBLY TO [{login_name}]; -- REQUIRED!!!!
';

A fully working example of this (minus the backup and Private Key removal) can be found on PasteBin:
Avoiding "Trusted Assemblies" - Demo

A detailed explanation of why you should use Certificates, and why you should not use "Trusted Assemblies", to fix this problem is provided in my blog post:
SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment

Also, please consider supporting my request to have Microsoft remove the new "Trusted Assemblies" feature given the numerous problems surrounding it (including security concerns), and the overall lack of benefits for having it:

Trusted Assemblies are more problematic yet less functional than Certificates - Please Remove


Can't shrink Log file - "An invalid Floating point operation occurred. (Microsoft SQL Server, Error: 3623)" When selecting the Transaction Log file from the dropdown on the Shrink Files screen in SSMS, I get the following error:

  Failed to retrieve data for this request.  (Microsoft.SqlServer.Management.Sdk.Sfc)  Additional information:  An invalid Floating point operation occurred. (Microsoft SQL Server, Error: 3623)

It is happening on the same database on 2 different servers (restored from a SQL 2008 R2 Enterprise Ed. server to a SQL 2016 Standard Edition server nightly).  There are older copies spawned of the same database on QA and Dev from dates 2 months ago, and they do not experience the issue, so it is something that happened recently.

I have tried:

DBCC CHECKDB - no errors
Took DB offline, deleted transaction log, recreated t-log using T-SQL. - No effect on error message
Detached DB, Reattached - No effect on error message
Set DB to FULL recovery mode (was previously on SIMPLE), allowed users to generate a good amount of transactions successfully. - No effect on error message
Set DB back to SIMPLE recovery mode. - No effect on error message
Attempted DBCC SHRINKFILE (<database_name>_log, 1).  - No effect on error message.  However, running this did generate the following message:

  Msg 8985, Level 16, State 1, Line 24
  Could not locate file '<database_name>_log' for database '<database_name>' in sys.database_files. The file either does not exist, or was dropped.

... Which made me decide to go do a select over sys.database_files and see if anything looked out of place.
It looks normal to me... look and see if you see anything that looks abnormal:

file_id= 2
file_guid= 2BA3CDBB-3F3F-4F81-B067-25712493E963
type= 1
type_desc= LOG
data_space_id= 0
name= <database_name>_log
physical_name= L:\MSSQL\Data\<database_name>.ldf
state= 0
state_desc= ONLINE
size= 64000
max_size= 268435456
growth= 64000
is_media_read_only= 0
is_read_only= 0
is_sparse= 0
is_percent_growth= 0
is_name_reserved= 0
create_lsn= NULL
drop_lsn= NULL
read_only_lsn= NULL
read_write_lsn= NULL
differential_base_lsn= NULL
differential_base_guid= NULL
differential_base_time= NULL
redo_start_lsn= NULL
redo_start_fork_guid= NULL
redo_target_lsn= NULL
redo_target_fork_guid= NULL
backup_lsn= NULL

UPDATE  Since now I'm able to shrink the log using T-SQL  (DBCC SHRINKFILE (_log,...) ), I'm lowering the urgency of a fix.  However, I would still like to know if anyone has a fix for the original error, when shrinking using the GUI in SSMS.

You should update your question with the full @@version of your server.

You mentioned only that you use SQL Server 2008 R2, if you are only on SP2 this hotfix is for you:  Cumulative update package 8 for SQL Server 2008 R2 Service Pack 2

Among the issues it fixes, there is yours:

But I recommend you to patch your server to SP3.


Run two SQL Agent Jobs Simultaneously (not sequentially) as the next step At the completion of Step 1, the job currently goes to step 2 - an osql command to start a Sql Agent job on another server.  However at the completion of step 1, I would also like to start a concurrent SQL Agent Job on the same server as the step 1 SQL Agent job.   Right now, the jobs are cascaded but I would really like "job 2" and "Job 3" to run at the same time.  Any ideas on how to make steps non- sequential?

You can try using the maintenance plans under management folder. There are Execute SQL Server Agent Job task and other task that fit your need. you can design the plan in the way that after a step completion, it run multiple tasks like shown below.


Get id, distinct idLocal of the MIN(datetime) in MySQL I use MySQL 5.7 and I have a table with for example this data:

   id   idLocal       time
    1     1      2016-12-15 19:18:00
    2     1      2016-12-15 19:18:00
    3     2      2016-12-15 20:30:00
    4     3      2016-12-16 20:30:00
    5     2      2016-12-15 12:15:00
    6     4      2016-12-15 12:15:00

I need extract id, distinct idLocal of the MIN(time). The sentence must return this:

   id   idLocal       time
    1     1      2016-12-15 19:18:00
    4     3      2016-12-16 20:30:00
    5     2      2016-12-15 12:15:00
    6     4      2016-12-15 12:15:00

If idLocal have two rows with the same time, only need the first row.

I try with this sentence but if idLocal have two rows with the same time return me the two rows.

SELECT  P.id, fP.idLocal  ,P.time FROM Products P JOIN (SELECT idLocal,MIN(time) as time FROM Products GROUP BY idLocal) fP ON P.time = fP.time AND P.idLocal = fP.idLocal;

How do I solve it?

Make your query a subquery.

Take the result of the subquery and find minimum id grouped by idLocal,time ordered by id

PROPOSED QUERY

SELECT MIN(id) as id,idLocal,time
FROM (SELECT P.id,fP.idLocal,P.time
FROM Products P JOIN
(SELECT idLocal,MIN(time) as time FROM Products GROUP BY idLocal) fP
ON P.time = fP.time AND P.idLocal = fP.idLocal) A
GROUP BY idLocal,time
ORDER BY id;

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS carlos;
CREATE DATABASE carlos;
USE carlos
CREATE TABLE Products
(
    id      INT NOT NULL AUTO_INCREMENT,
    idLocal INT NOT NULL,
    time    DATETIME NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO Products (idLocal,time) VALUES
(1,'2016-12-15 19:18:00'),(1,'2016-12-15 19:18:00'),
(2,'2016-12-15 20:30:00'),(3,'2016-12-16 20:30:00'),
(2,'2016-12-15 12:15:00'),(4,'2016-12-15 12:15:00');
SELECT * FROM Products;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS carlos;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE carlos;
Query OK, 1 row affected (0.00 sec)

mysql> USE carlos
Database changed
mysql> CREATE TABLE Products
    -> (
    ->     id      INT NOT NULL AUTO_INCREMENT,
    ->     idLocal INT NOT NULL,
    ->     time    DATETIME NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO Products (idLocal,time) VALUES
    -> (1,'2016-12-15 19:18:00'),(1,'2016-12-15 19:18:00'),
    -> (2,'2016-12-15 20:30:00'),(3,'2016-12-16 20:30:00'),
    -> (2,'2016-12-15 12:15:00'),(4,'2016-12-15 12:15:00');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Products;
+----+---------+---------------------+
| id | idLocal | time                |
+----+---------+---------------------+
|  1 |       1 | 2016-12-15 19:18:00 |
|  2 |       1 | 2016-12-15 19:18:00 |
|  3 |       2 | 2016-12-15 20:30:00 |
|  4 |       3 | 2016-12-16 20:30:00 |
|  5 |       2 | 2016-12-15 12:15:00 |
|  6 |       4 | 2016-12-15 12:15:00 |
+----+---------+---------------------+
6 rows in set (0.01 sec)

PROPOSED QUERY EXECUTED

mysql> SELECT MIN(id) as id,idLocal,time
    -> FROM (SELECT P.id,fP.idLocal,P.time
    -> FROM Products P JOIN
    -> (SELECT idLocal,MIN(time) as time FROM Products GROUP BY idLocal) fP
    -> ON P.time = fP.time AND P.idLocal = fP.idLocal) A
    -> GROUP BY idLocal,time
    -> ORDER BY id;
+------+---------+---------------------+
| id   | idLocal | time                |
+------+---------+---------------------+
|    1 |       1 | 2016-12-15 19:18:00 |
|    4 |       3 | 2016-12-16 20:30:00 |
|    5 |       2 | 2016-12-15 12:15:00 |
|    6 |       4 | 2016-12-15 12:15:00 |
+------+---------+---------------------+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Replace function removing Ones / 1 when removing char(185) (superscript 1) Ok as the title suggests this is occuring.

When using the REPLACE function on char(185) for data in a temporary table, ones ("1") are being removed as well.

SQL SERVER 2008R2
UNICODE Data

DB collation - collate SQL_Latin1_General_CP1_CS_AS
Tempdb has default

Any other details I could provide which might be helpful, let me know.

CHAR(185), being VARCHAR data and using an 8-bit code page, can be a different character depending on the code page being used (which is determined by the collation of the current database when using the CHAR() function). In many code pages, including the common Windows-1252 (Latin1_General), the character is a superscript 1 (i.e. "1" ). This character, depending on the collation / code page being used, might have a "best fit" mapping to a regular "1".

Also, when using a Windows collation, case-sensitivity can affect the outcome of matches between subscript, superscript, and regular versions of the same characters (seriously! see example below). This also covers NVARCHAR / Unicode data (including UTF-8 in VARCHAR starting in SQL Server 2019).

When needing to match on specific characters you need to use a binary collation (i.e. one ending in _BIN2 unless on SQL Server 2005 or older, in which case only the _BIN collations are available).

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE SQL_Latin1_General_CP1_CI_AS
-- no rows (SQL Server collation, code page 1252, case insensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE SQL_Latin1_General_CP437_CI_AS
-- 1 row (SQL Server collation, code page 437, case insensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE SQL_Latin1_General_CP437_CS_AS
-- 1 row (SQL Server collation, code page 437, case sensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE Latin1_General_100_CI_AS
-- 1 row (Windows collation, code page 1252, case insensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE Latin1_General_100_CS_AS
-- no rows (Windows collation, code page 1252, case sensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE Latin1_General_100_BIN2;
-- no rows (Windows collation, code page 1252, _BIN2 collation; always works!)

As noted above, Unicode data (regardless of type of collation) works the same as VARCHAR data used with a Windows collation (because both scenarios use the same Unicode rules):

SELECT NCHAR(185)
WHERE  NCHAR(185) = N'1' COLLATE Latin1_General_100_CI_AS
-- 1 row (Windows collation, Unicode data, case insensitive)

SELECT NCHAR(185)
WHERE  NCHAR(185) = N'1' COLLATE Latin1_General_100_CS_AS
-- no rows (Windows collation, Unicode data, case sensitive)


How to Get the Last Day of the Previous Month? Is there a quicker and easier to read way in order to get the last day of the previous month in T-SQL?

I have found this piece of source code, which does the trick, but looks obfuscated unless properly previously commented:

-- Declar variabila pentru ultima zi din luna anterioară.
DECLARE @DSL [DATETIME]
SET @DSL = (SELECT DATEADD(  dd
                           ,  0
                           , DATEDIFF(  dd
                                      ,  0
                                      , DATEADD(   s
                                                , -1
                                                , DATEADD(  mm
                                                          , DATEDIFF(  m
                                                                     , 0
                                                                     , GETDATE())
                                                          , 0)))))
PRINT 'Declar variabila pentru ultima zi din luna anterioară.'
PRINT @DSL

The message window with the results show the following output:

Declar variabila pentru ultima zi din luna anterioară.
Mar 31 2015 12:00AM

Please forgive my ignorance, I feel like a new-born newbie when I see these tricks.

SELECT @@VERSION reports:

  Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
      Sep 16 2010 20:09:22
      Copyright (c) 1988-2008 Microsoft Corporation
      Express Edition with Advanced Services on Windows NT 5.1  (Build 2600: Service Pack 3)

This one works in SQL Server 2008 R2, will probably work on SQL Server 2008 too :

DECLARE @DSL [DATETIME]
SET @DSL = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1))
PRINT 'Declar variabila pentru ultima zi din luna anterioară.'
PRINT @DSL