Search within same ID across several rows My table has the following structure:

+----+---------+------------+-------------+
| id | word_id | meaning_id | sentence_id |
+----+---------+------------+-------------+
| 1  | 1       | 15333      | 1           |
| 2  | 1       | 12355      | 1           |
| 3  | 1       | 768        | 1           |
| 4  | 2       | 12455      | 1           |
| 5  | 2       | 9581       | 1           |
| 6  | 3       | 41         | 1           |
| 7  | 4       | 125        | 1           |
| 8  | 1       | 17854      | 2           |
| 9  | 2       | 35214      | 2           |
| 10 | 3       | 12455      | 2           |
| 11 | 3       | 988        | 2           |
+----+---------+------------+-------------+

I have a list of meaning_ids and I want to get all the sentence_ids that contain all of the meaning_ids. I do not care what the sentence it is, as long as the sentence contains all meaning_ids (= sentence_id is the same).

Lets say I have the following sentence_ids: 12455 and 15333. The query should return sentence_id 1 because both only occur there.

How can I achieve this with MySQL?

This requirement is known as relational division. One popular approach is

SELECT sentence_id
FROM   YourTable
WHERE  meaning_id IN ( 12455, 15333 )
GROUP  BY sentence_id
HAVING COUNT(DISTINCT meaning_id) = 2

SQL Fiddle

If your table has a unique constraint on sentence_id,meaning_id you can drop the DISTINCT in the query above.


Is there a way to get the cardinality between tables, from a MySQL database? I am working on a project where I have to analyze a MySQL database. I can find every relationship between tables in information_schema.tables, but I need to get the cardinality from each relationship.

  In the relational model, tables can be related as any of "one-to-many"
  or "many-to-many". This is said to be the cardinality of a given table
  in relation to another.

Is there a way to get that information?

What I understood from your question is that you need to list all the parent and child relationship in your database, for that you can execute below query

SELECT
    ke.referenced_table_name 'parent table',
    ke.referenced_column_name 'parent column',
    ke.table_name 'child table',
    ke.column_name 'child column',
    ke.constraint_name
FROM
    information_schema.KEY_COLUMN_USAGE ke
WHERE
    ke.referenced_table_name IS NOT NULL
        AND table_schema = 'your_db_name'
ORDER BY ke.referenced_table_name;

Here your_db_name is my database.


What's the difference between "expression <=1." and "expression <= 1"? Reviewing code and a case statement has expression <= 1. as opposed to <= 1

I'm not sure what the purpose of the 1. is.  Any thoughts?

1. is a NUMERIC (or DECIMAL) constant, while 1 is an INTEGER constant. In some cases it is useful to specify the data type of a constant explicitly to avoid an unnecessary (or undesirable) implicit type conversion.

Consider, for example

create table t(f1 int);
insert into t values (2);

Then select 1/f1 from t returns 0 (INTEGER), while select 1./f1 from t returns 0.5 (DECIMAL).

One might assume that on the left side of the comparison in your example there is a DECIMAL column, and explicitly specifying a DECIMAL constant could marginally improve performance by avoiding an implicit type cast.


Entering Exact Values into a Table Using SQL I am needing to insert particular values into a table I am working on with SQL (see function below):
INSERT INTO *Table Name* (...) VALUES(...);

The values that are going to be stored inside this table are values that involve another programming language that have a lot of weird symbols involving a single backslash, *, dollar signs, etc.
How can I insert values precisely as they are inside this table?

For instance, when I type '\2' it needs to return \2 inside the table instead of just 2. I am unable to manually enter the two backslashes to get \2. This is because I am receiving input from another user via HTML and sending it to my table with PHP. The issue I am having in that there are these special characters with SQL which are being entered is not the way I intend them to as values.

Is there a way to type these values in exactly with a certain string function that is called beforehand? Other ways of doing this would be greatly appreciated!
Additional Info for Database:

Server: Localhost via UNIX socket
Server type: MySQL
Server connection: SSL is not being used Documentation
Server version: 5.6.49-cll-lve - MySQL Community Server (GPL)

Potential Answer:
Is $mysqli defined to be as follows? (I call it $db.)
$mysqli=mysqli_connect(...);
So, that literals are added as follows:
$query = "INSERT INTO Table (C1, ... Cn) VALUES('$C1', ... '$Cn')";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $C1, ..., $Cn);
$stmt->execute();
mysqli_query($db, $query);

Just do not use string interpolation or concatenation to get values into SQL queries in PHP. That's error prone and might make your program vulnerable to SQL injection attacks.
Use parameterized queries. See "How to include a PHP variable inside a MySQL statement" and "How can I prevent SQL injection in PHP?".
Using parameterized queries will also ensure that the data is automatically escaped in the necessary way. So this also solves your problem. No manual escaping is necessary.


Simple SELECT-query on big table gets stuck in "Sending data" state I have a table with about ~200 million rows looking like this:

id, name, timestamp, class

I have an index on each column and I am trying to run a query like this:

SELECT name
FROM t
WHERE timestamp >= date_x AND
      timestamp < date_y AND
      class='class_a'

This query only uses the index on class and when date_x and date_y is about a month a part EXPLAIN says that it looks at about 12.5 M rows which is less than for the index on timestamp.

When the date range is small this query obviously returns fast but as the range increases this query soon gets stuck and never even starts sending any data (i.e stuck on Sending data), compared to a simple SELECT * FROM t which in SHOW PROCESS list alternates between Sending data and Writing to net.

My current workaround is to fetch rows within a daily range and then concatenate the results. For a month it should return about 150 K rows which I think should be doable for a straight-forward query such as this. That is why I think that there should be a way to do this without the workaround.

I have tried looking at the settings but have found no apparent mistakes there either. I can update this question with the relevant MySQL settings.

Do I need a multicolumn index on both class and timestamp?
Why cannot MySQL use both indexes to limit the search?
Have I missed something?

EXPLAIN output:

1   SIMPLE  table   ref index_class,index_timestamp index_class 302 const   11898928    100.00  Using where

I believe that for a small interval the index chosen by the query optimizer is the one that uses the timestamp, so it is faster.

I think a composite index class, timestamp would solve the problem.

Ps: Can you place the query plan when the range is small?


Optimising query: not sure why LOWER() index isn't used I've got comments that have 1..N comment_notifications.
Each notification basically contains a username of the user notified.
Each notification can be resolved_at when the user is done with it.
This query is slower than I'd like – seeing times around 100–150 ms in production (hobby DB plan on Heroku) but would like to get it down to at least 50 ms or so.
EXPLAIN ANALYZE
  SELECT c0."id", c0."resolved_at", c0."username", c0."comment_id", c0."inserted_at", c0."updated_at",
  c1."id", c1."github_id", c1."commit_sha", c1."body", c1."commented_at", c1."commenter_username", c1."path", c1."position", c1."payload", c1."inserted_at", c1."updated_at"
  FROM "comment_notifications" AS c0
  INNER JOIN "comments" AS c1 ON c1."id" = c0."comment_id"
  WHERE
    (NOT (c0."resolved_at" IS NULL)) AND
    (LOWER(c1."commenter_username") = 'henrik')
  ORDER BY c0."resolved_at" DESC
  LIMIT 300

These are some of my indexes:
CREATE INDEX comment_notifications_resolved_at_index ON public.comment_notifications USING btree (resolved_at);

CREATE INDEX comment_notifications_lower_username_index ON public.comment_notifications USING btree (lower((username)::text));

This is the result of the EXPLAIN ANALYZE:
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=57.86..57.86 rows=5 width=298) (actual time=1.931..1.969 rows=300 loops=1)
   ->  Sort  (cost=57.86..57.86 rows=5 width=298) (actual time=1.930..1.949 rows=300 loops=1)
         Sort Key: c0.resolved_at DESC
         Sort Method: quicksort  Memory: 238kB
         ->  Hash Join  (cost=43.64..57.85 rows=5 width=298) (actual time=1.101..1.520 rows=459 loops=1)
               Hash Cond: (c0.comment_id = c1.id)
               ->  Seq Scan on comment_notifications c0  (cost=0.00..13.75 rows=866 width=48) (actual time=0.014..0.177 rows=871 loops=1)
                     Filter: (resolved_at IS NOT NULL)
                     Rows Removed by Filter: 46
               ->  Hash  (cost=43.62..43.62 rows=6 width=250) (actual time=1.051..1.051 rows=473 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 140kB
                     ->  Seq Scan on comments c1  (cost=0.00..43.62 rows=6 width=250) (actual time=0.058..0.805 rows=473 loops=1)
                           Filter: (lower((commenter_username)::text) = 'henrik'::text)
                           Rows Removed by Filter: 387
 Planning Time: 1.221 ms
 Execution Time: 2.056 ms
(16 rows)

My reading is that the username filter is the most expensive part by far. But I have an index on LOWER(username) as seen above. Any thoughts on why it's not being used?
Any suggestions of what I could improve, or further experiments I could make?

Two different indexes for your query:
As you found out yourself, you had the column comment_notifications.username indexed, which is irrelevant for the case. You need this one instead:
CREATE INDEX c_lower_commenter_username_idx
ON public.comments (lower(commenter_username));

More importantly, the plan also shows a sequential scan on comment_notifications - the far bigger table. Another index on (comment_id) seems to be missing.
Or, tailored to the query, this partial multicolumn index:
CREATE INDEX cn_comment_id_resolved_at_idx
ON public.comment_notifications(comment_id DESC, resolved_at DESC)
WHERE resolved_at IS NOT NULL;

Run ANALYZE on the tables after creating these (and possibly deleting the ones you had).
Adding WHERE resolved_at IS NOT NULL only makes sense if a sizable proportion of rows with NULL are excluded.
If you can change ORDER BY, the second index can be used much more efficiently, yet:
SELECT cn.id, cn.resolved_at, cn.username, cn.comment_id, cn.inserted_at, cn.updated_at
     , c.id, c.github_id, c.commit_sha, c.body, c.commented_at, c.commenter_username, c.path, c.position, c.payload, c.inserted_at, c.updated_at
FROM   comments              c
JOIN   comment_notifications cn ON cn.comment_id = c.id
WHERE  lower(c.commenter_username) = 'henrik'
AND    cn.resolved_at IS NOT NULL
ORDER  BY cn.comment_id DESC, cn.resolved_at DESC
LIMIT  300  -- or 150 or whatever
If you only added AND cn.resolved_at IS NOT NULL to get rid of the (few) rows with NULL being listed first, there is a more elegant solution with NULLS LAST.
Query:
SELECT ...
...
WHERE  lower(c.commenter_username) = 'henrik'
ORDER  BY cn.comment_id DESC, cn.resolved_at DESC NULLS LAST
LIMIT  300  -- or 150 or whatever
Matching index:
CREATE INDEX cn_comment_id_resolved_at_idx
ON public.comment_notifications(comment_id DESC, resolved_at DESC NULLS LAST);

See:

Sort by column ASC, but NULL values first?

Aside 1: Why the cast to text? What's the actual data type of username and commenter_username? (Should be text to begin with.) And why the need for lower()? User names should not be saved with varying upper/lower case spellings to begin with.
Aside 2: The explain plan shows low cardinalities. Indexes pay for big tables. Not so much for small tables, where queries are fast either way. 2 ms doesn't seem too bad. 150 ms seems to be a misunderstanding, including network latency or display times.


Database keeps using utfmb4 instead of utf8 Using MariaDB 10.1. The default encoding is utf8mb4, but I have an existing database that uses utf8.

So in order to re-use the same DB, I would have to keep using utf8 for now. So I set the server variables to the following in my /etc/mysql/my.cnf:

[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

To ensure it's working, I checked the following:

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_client     | utf8            |
| character_set_connection | utf8            |
| character_set_database   | utf8            |
| character_set_filesystem | binary          |
| character_set_results    | utf8            |
| character_set_server     | utf8            |
| character_set_system     | utf8            |
| collation_connection     | utf8_general_ci |
| collation_database       | utf8_unicode_ci |
| collation_server         | utf8_unicode_ci |
+--------------------------+-----------------+

However, everytime I run migration with new tables and columns, they are created with encoding: utf8mb4, collation: utfmb4_general_ci, which further throws the Specified key was too long; max key length is 767 bytes error because I have column with varchar(255) which needs to be indexed.

Have been working on this for hours and couldn't find anything else to try. Please advise. Thank you.

So it looks like when the server variables are set, they don't change the database that had already been created before. So what I did was to:

Set the server variables as shown in the question.
Verify that they are now as expected as shown in the question.
Recreate the database again, and check that the database encoding and collation is correct.


Is it "bad design" to have 2 related tables, each one having a column (named differently) whose value will always be the same in both? Imagine that I have the following database structure

TableUser (
   userId integer,
   weeksToLive integer
)

TableCalendar (
   calendarId integer,
   numberOfCells integer,
   userId integer REFERENCES TableUser
)

The point is this will always be the case:

TableCalendar.numberOfCells = TableCalendar.weeksToLive

So I'm wondering if it is harmful or something to have two columns which will always contain the same value, so that the same value can be obtained using different select statements.

In general, keeping redundant data in a relational database is considered a bad design. There are several problems associated with redundant data:

There is the danger that application errors or manual interventions introduce inconsistencies into the database by modifying only one copy of the redundant information. This is the main problem.
Performance is sapped by having to write more data than necessary, or by constraints or other measure that are to ensure consistency of the copies.
You waste storage space. That is usually a minor concern.

These things are reflected on a theoretical level in the concept of database normalization, which establishes rules that aim at avoiding data redundancy (among other things).

So from a performance point of view, data redundancy makes data modifications slower, but it can speed up queries, because the data can be fetched from several locations, which may make some queries simpler and faster.

Sometimes that is a good enough reason to introduce data redundancy (“denormalization”), but you should only do that if it is necessary for performance reasons, and the advantages outweigh the disadvantages listed above.


How to add additional tables/fields into a query with multiple inner joins? Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result?

SELECT desired_categories.*
FROM
    ( SELECT nid, title, COUNT(1) AS all_categories
        FROM node
        GROUP BY nid
    ) AS A

INNER JOIN
    ( SELECT nid, COUNT(1) AS good_categories
        FROM node
    INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        WHERE category.dir_phys_category_tid
        IN (498,499,500)
        GROUP BY nid
    ) AS B USING (nid)

INNER JOIN
    ( SELECT DISTINCT nid, title
        FROM node
    ) AS desired_categories USING (nid)

WHERE all_categories = good_categories;

I have about 5 fields from 5 other tables I need access to.  All tables use the node id nid to indicate their relationship.  I suspect it's possible to simply add a join in for each table but I haven't had any luck yet.

After further tinkering, I worked it out.  If I'm correct, then it's stupidly simple.

Additional tables can be added to my scenario if:

they are added as LEFT JOINS
they are added after the INNER JOINS

I'm still trying to wrap my head around exactly how JOIN functions, but it makes sense to me that the first three joins are narrowing down the selection.  In order to expand the selection (to add my additional tables/values) I use the LEFT JOIN, which essentially says, "use everything to the left (my inner joins) and use this table too."

SELECT desired_categories.*, t1.valueX, t2.valueX, t3.valueX
FROM
    ( SELECT nid, title, additional_col_1, additional_col_2, COUNT(1) AS all_categories
        FROM node
        GROUP BY nid
    ) AS A

INNER JOIN
    ( SELECT nid, COUNT(1) AS good_categories
        FROM node
    INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        WHERE category.dir_phys_category_tid
        IN (498,499,500)
        GROUP BY nid
    ) AS B USING (nid)

INNER JOIN
    ( SELECT DISTINCT nid, title
        FROM node
    ) AS desired_categories USING (nid)

LEFT JOIN table_1 AS t1 ON desired_categories.nid = t1.entity_id
LEFT JOIN table_2 AS t2 ON desired_categories.nid = t2.entity_id
LEFT JOIN table_3 AS t3 ON desired_categories.nid = t3.entity_id
LEFT JOIN table_4 AS t4 ON desired_categories.nid = t4.entity_id
LEFT JOIN table_5 AS t5 ON desired_categories.nid = t5.entity_id

WHERE all_categories = good_categories
  AND A.additional_col_1 > t1.valueX
  AND A.additional_col_2 = 'active';


Database design: sharing tables between databases? PREAMBLE: I'm taking a bit of a risk asking a slightly abstract question in a stack exchange (cough) such as this, but I'm hoping that someone may have come across this type of situation before. This is a question which is also likely to be useful to be others in the future. I can give detailed technological aspects in relation to this question, and the answer need not be "opinion" based. Furthermore, aspects in relation to this question are unlikely to change temporarily. So if you wish to downvote me because of my inexperience, or because you think you are smarter than me go ahead, but don't pretend it is because it is an inherently bad question. /PREAMBLE

I'm working with MySQL, and have developed a database to support a room allocation program. Aspects such as time, rooms, buildings are fixed and never change. However, the actual allocations entirely change every half year. As such the tables related to the allocation of users to rooms is entirely overhauled every 6 months. In order to preserve these records I duplicate the entire database, archive the old one and start off from scratch with the allocation information every time an overhaul is required.

I dislike the redundancy that is inherent to that process. The tables that never change need not be duplicated (and in my opinion, should not be duplicated). Instead it would make more sense for them to be shared between database children, which inherit this information.

Is this even possible with MySQL? No course that I have attended concerning rdmss have ever mentioned such a process. Looking on-line I have seen people state that the sharing of tables between databases would be a design flaw, but I do not see a more elegant solution. Are there better solutions to this design problem?

Normally when I have something like this, I have a table in one database and a VIEW pointing to that table in the other database.   Can you just use a view on the other database which references the first one?  If not then maybe a "materialized view/mirror table" of sorts which be a "slave" table that would get refreshed once an hour or once a day/week/month.


Using MySQL triggers or transactions? I want to ask your opinion in using MySQL triggers or transactions in a website.

Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So each payment operation is inserted in this table.

However, it will be time consuming calculating each time the total credit amount of the user every time he do action. So I thought maybe is a good idea to keep the total credit amount for each user in a user profile table.

Here is the problem. How can I be sure that the total credit amount form the profile table will stay synchronized with the operations from payment history table ?

I thought using 2 methods:

MySQL triggers or
transactions coded in the source code

Which is more reliable? What if I have large database (over 100.000 users) ?

Do you have any suggestions to do this?

The BD MySQL engine is InnoDB.

Without a doubt, I would rule out triggers and strictly stay with transactions.

Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive row locks. Such would be the case if triggers were manipulating tables with INSERTs and UPDATEs being stagnated to perform heavy duty MVCC inside each call to a trigger.

Combine this with the fact that proper data validation protocols are not implemented in MySQL's Stored Procedure Language. Business Intelligence is OK to have contained in a database provided the Stored Procedure Language can handle a transactional environment. As a MySQL DBA, I have to honestly say that such is not the case with MySQL. Oracle (PL/SQL), PostgreSQL (PL/pgSQL), and SQL Server (T-SQL) have this edge over MySQL.

Concerning transactions, MySQL has InnoDB as its main ACID-compliant storage engine (Deafult storage engine in MySQL 5.5). It has excellent crash recovery and obeys the ACID compliance protocols.

I would choose transacitons over triggers every single time.


Alias of COUNT is not being "recognized" by SQL Server I have restored the Northwind database(https://northwinddatabase.codeplex.com/) from a backup file using SQL Server.

Trying to execute the following query results in error "Invalid column name 'products'.", product is an alias.

SELECT OrderID, COUNT(ProductID) products
FROM [NORTHWND].[dbo].[OrderDetails]
GROUP BY OrderID
HAVING products > 5;

What is the problem?

The SELECT clause is logically processed after the HAVING clause. Therefore the aliases used in SELECT don't exist (yet) when the HAVING clause is processed.

On MSDN you can look at SELECT (Transact-SQL):

  Logical Processing Order of the SELECT statement

  The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  FROM
  ON
  JOIN
  WHERE
  GROUP BY
  WITH CUBE or WITH ROLLUP
  HAVING
  SELECT
  DISTINCT
  ORDER BY
  TOP

This query works because it repeats what will later be defined (the COUNT) in the SELECT clause:

SELECT OrderID, COUNT(ProductID) products
FROM [NORTHWND].[dbo].[OrderDetails]
GROUP BY OrderID
HAVING COUNT(ProductID) > 5;


Why do I need to cast NULL to column type? I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this:

(Both the active and core fields are of type boolean)

UPDATE fields as t set "active" = new_values."active","core" = new_values."core"
FROM (values
(true,NULL,3419),
(false,NULL,3420)
) as new_values("active","core","id") WHERE new_values.id = t.id;

However it fails with:

  ERROR:  column "core" is of type boolean but expression is of type text

I can get it to work by adding ::boolean to the nulls, but that just seems odd, why is NULL considered of type TEXT?

Also it's a bit tricky to cast because it would require quite a bit of a rework of the code for it to know what type it should cast NULLs to
(the list of columns and values is currently being autogenerated from a simple array of JSON objects).

Why is this necessary and is there a more elegant solution that doesn't require the generating code to know the type of NULLs?

If it's relevant, I'm using sequelize over Node.JS to do this, but am also getting the same result in the Postgres command line client.

This is an interesting finding.  Normally, a NULL has no assumed data type, as you can see here:

SELECT pg_typeof(NULL);

 pg_typeof
───────────
 unknown

This changes when a VALUES table comes into the picture:

SELECT pg_typeof(core) FROM (
    VALUES (NULL)
) new_values (core);

 pg_typeof
───────────
 text

This behaviour is described in the source code at https://doxygen.postgresql.org/parse__coerce_8c.html#l01373:

 /*
  * If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
  * then resolve as type TEXT.  This situation comes up with constructs
  * like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
  * UNION SELECT 'bar'; It might seem desirable to leave the construct's
  * output type as UNKNOWN, but that really doesn't work, because we'd
  * probably end up needing a runtime coercion from UNKNOWN to something
  * else, and we usually won't have it.  We need to coerce the unknown
  * literals while they are still literals, so a decision has to be made
  * now.
  */

(Yes, PostgreSQL source code is relatively easy to understand and most places, thanks to excellent comments.)

The way out, however, might be the following.  Let's say you are always generating VALUES that match all columns of a given table (see the second note below for other cases).  From your example, a small trick could possibly help:

SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);

 active │ core │  id
────────┼──────┼──────
 t      │      │ 1234

Here you use row expressions casted to the table's type, and then extracting them back to a table.

Based on the above, your UPDATE could look like

UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
           ((true, NULL, 3419)::fields),
           ((false, NULL, 3420)::fields)
     ) AS new_values(x) WHERE (x).id = t.id;

Notes:

I removed the double quotes for better human readability, but you can keep them as they help when generating (column) names.
if you need only a subset of the columns, you can create custom types for this purpose.  Use them the same way as you would above (where I use the type automatically created with the table, holding the row structure of the latter).

Look at the whole thing working on dbfiddle.


Individual SQL account per user? I am designing a web application that will have probably around ~1000 users.  The website is facing the internet but it can only be used by partners (i.e. the general public cannot just "sign up an account").  Account maintenance will be handled by our own IT support.

I am considering having each individual user to have their own SQL login associated.  This way I do not need to store their salted/hashed password - SQL server will handle the authentication.  This also allow us to use SQL Server based audit and logging tool.

Is there any disadvantage doing that?

Yes, plenty :)

You will have to setup encryption from the application server to the database server as the password is passed in clear text.
All login requests from the internet will result in an expensive authentication request to the database server.
You are not able to use connection pooling which will result in having a single connection for each user accessing the database.


Is network traffic encrypted when writing remote backups using SQL Server TDE? They do say there is no such thing as a “stupid question”, so here goes:

I understand that SQL Server Transparent Data Encryption (TDE) encrypts data at rest, so that your database files (.mdf) and your backup files (.bak) are encrypted should someone break into your storage and steal those files. I also understand that the data is decrypted when read from disk so that it is unencrypted in memory (in motion). Therefore data requested by a user running a remote query (select * from SensitiveData) will be unencrypted when travelling over the network and thus vulnerable to intercept.

So, assuming all the above is correct, here is my stupid question: If my SQL Server instance is on computer A and my TDE-database backups are written off to storage on remote computer B, is the backup operation data encrypted as it travels from computer A to be written to disk at computer B? I assume it must be (because I suppose the encryption operation occurs on computer A first), but I can’t find confirmation of this in any of the Microsoft documentation or on the blogs. And likewise, during a restore operation – were anyone to intercept the data being transferred from disk at computer B to restore the database at computer A – would they find that data in motion encrypted?

Yes, backups are encrypted while moving over the network because TDE data is encrypted on disk, and the backup operation never decrypts it.
Paul Randal's Backup Myths:

Myth 30-09) backups read data through the buffer pool
No. The backup subsystem opens its own channels to the database files to avoid the performance hit of having to read everything into SQL Server’s memory and back out to the backup device (and also effectively flushing the buffer pool in the process). If you ask the for page-checksum checking, it uses it’s own small portion of memory.

If the pages were loaded into the buffer pool (the "normal" memory space SQL uses to cache database table and index data), they would have to be decrypted. But backups don't do that, they just dump raw encrypted "extents" (contiguous 8-page chunks) to your backup destination.
I was able to get confirmation from Paul Randal that his above comment is still relevant for TDE:

It works exactly the same way. The buffer pool does encryption then adds a page checksum before writing a page to disk. Backups never read through the buffer pool. So yes, a backup of TDE database has the encryption still in it. Page checksums are validated, but by backup code, not buffer pool code.

In other words, if you've enabled CHECKSUMs on a database, those are added (during normal SQL write operations) after encryption occurs. This means that the backup process can read the raw (encrypted) extent, validate the checksum, and write the backup, all without decrypting the data.
This is almost certainly the reason that (prior to SQL 2016), enabling backup compression on database with TDE didn't do anything, since encrypted data is not very compressible:

This is because when backups of a TDE encrypted database are taken the database pages are not decrypted when backed up. They are backed up in the same encrypted state that they are normally in, then compressed. By it’s nature encrypted data is very unique so data compression doesn’t do much good against encrypted data.

For a restore operation, the same principle applies. The encrypted backup stays encrypted across the network, and is written to the restoring server's disk in their still-encrypted state. They are only decrypted when the database is loaded in memory after the restore is complete.


High amount of Read Misses and Pages To Be Flushed I am running a mysql database backend for a Moodle installation, and after a few months performance really starts to suffer (Up to 30 seconds for some pages to load). Under investigation in the InnoDB buffer pool, I found that the buffer pool size seemed to be correct (innodb_buffer_pool_wait_free = 0). However, I also found that I have an exceedingly high percentage of Read Misses (52%) and what seems like a rather large amount of Pages To Be flushed (31 million). I'm currently running the slow query log, but the lag on a page loading seems like too much to be from simply an unoptimized query.

I haven't been able to find any explanation of why those could both be so high. Does anybody have an explanation for why Read Misses and Pages To Be Flushed would have those results?

Update:
I am restarting the servers on a weekly basis during a scheduled down-period. I still cannot imagine why this is getting so large. Is there no auto-flush mechanism built-in?

What really jumps at me is that 52% miss. It's possible your innodb_buffer_pool_size is just too small.
Keep in mind that the InnoDB Buffer Pool caches data pages and index pages.
How can you get a good size for it? Run this query:
SELECT
    CONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
    CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
    CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
FROM (SELECT SUM(data_length+index_length) ibps
FROM information_schema.tables WHERE engine='InnoDB') A;

This will give you something like this
mysql> SELECT
    ->     CONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
    ->     CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
    ->     CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
    -> FROM (SELECT SUM(data_length+index_length) ibps
    -> FROM information_schema.tables WHERE engine='InnoDB') A;
+-----------+---------+---------+
| IBPS_KB   | IBPS_MB | IBPS_GB |
+-----------+---------+---------+
| 30333520K | 29623M  | 29G     |
+-----------+---------+---------+
1 row in set (11.43 sec)

mysql>

If the output gives you settings beyond 80% of the DB Server's Installed RAM, then use 80% of whatever the installed RAM is as the innodb_buffer_pool_size.
Give it a Try !!!
UPDATE 2013-04-15 12:43EDT
Let's look at the definition of Innodb_buffer_pool_wait_free

Normally, writes to the InnoDB buffer pool happen in the background. However, if it is necessary to read or create a page and no clean pages are available, it is also necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size has been set properly, this value should be small.

As stated, if the buffer pool size has been set properly, this value should be small. You may simply have lots of dirty pages in the Buffer Pool that need flushing to disk. You should be monitoring Innodb_buffer_pool_pages_dirty.
There are two things you could do to improve the situation:
IMPROVEMENT #1 : Upgrade to the latest MySQL
I trust MySQL 5.5. I have a client going to MySQL 5.6.10 soon. I trust it as well. These versions of MySQL have the InnoDB Plugin standard. They flush dirty pages much more efficiently.
You can also tune InnoDB. Under MySQL 5.1, there are 4 read IO threads and 4 write IO threads. MySQL 5.5+ allows you to increase these for better read and write InnoDB performance. InnoDB For MySQL 5.5.+ can access multiple CPUs/Core. MySQL 5.1 can do this if using MySQL 5.1.38+ and you install the InnoDB Plugin (IMHO too messy, go with MySQL 5.5/5.6). MySQL 5.1.27 cannot do this.
IMPROVEMENT #2 : Get Dirty Pages to Flush More Frequently
You can do this immediately with
SET GLOBAL innodb_max_dirty_pages_pct = 0;

The default value for innodb_max_dirty_pages_pct in MySQL 5.1 is 90. Drop this to zero(0). Then, start watching Innodb_buffer_pool_pages_dirty. On a busy write server, this should drop to 1% of Innodb_buffer_pool_pages_total.


How do I remove backslashes from records in MySQL Long story short, I have backslashes in my data due to PHP magic quotes. I have straightened out what was wrong there, but have some records now which have unnecessary backslashes.

1 | This is an example of \' single, \" double, and \\ backslash (in the messed up records)
2 | This example is how it gets stored now \ backslash ' single " double

When I want to output my data, I need to use strip slashes to fix the issue and get the following output:

1 | This is an example of ' single, " double, and \ backslash

But then this one is wrong:

2 | This example is how it gets stored now backslash ' single " double

Thus stripslashes in my php is removing a slash which isn't technically used to escape anything.

I want to not have to use strip slashes, since my data should be clean like this:

1 | This is an example of ' single, " double, and \ backslash (how data should be)

How can I query to replace \' and \" and \\ with ' " and \?

DROP TABLE IF EXISTS tmpTable;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable ( BadText VARCHAR(100));

INSERT INTO tmpTable
SELECT 'This is an example of \\\' single, \\\"double, and \\\\ backslash';

select BadText from tmpTable;

update tmpTable
set BadText = replace(replace(replace(BadText,'\\\'','\''),'\\\"','"'),'\\\\','\\');

select BadText as GoodText from tmpTable;


Is there any point with a combined index on all columns in a table? I have stumbled upon several cases of indices set on all columns of a table. In my mind this will be as efficient as the full table scan that would occur without the index. Is this correct or is there any benefit of such an index? I have seen this in Active Record on top of Postgres so this setup is my primary concern. I also wonder if and how this may differ between the different databases.

The index is a sorted structure - if you need only a sufficiently small portion of the data in the table, it could be fetched from the index more efficiently.

This needs a few prerequisites, though:

PostgreSQL version 9.2 or newer, as index-only scans appeared in this version
the index supports the query (the order of the columns of the index decides this)

Also worth noting that even index-only scans must visit the underlying table (to be precise, its visibility map, and in cases the heap itself) to figure out if the rows to be returned are still visible to the current transaction.  This has to be also considered when determining what 'sufficiently small' means above.


Restore script in SQL server doesn't work in W7 I need a little help with a restore process, I can't find specific help in other similar questions, sorry if my question does not correspond.

I have this script in my system:

RESTORE DATABASE [Basesistema] FROM DISK = N’C:\Sistema\backup.bak’ WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

It work perfect in Windows XP, but not in Windows 7…

The message is:

  Error al buscar el archivo ‘C:\Archivos de programa\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Basesistema.mdf’ en los directorios, error del sistema operativo: 3(failed to retrieve text for this error. Reason: 15105)

English translation:

  Failed to find file 'C: \ Program Files \ Microsoft SQL Server \
  MSSQL10.SQLEXPRESS \ MSSQL \ DATA \ Basesistema.mdf' directories,
  operating system error: 3 (failed to retrieve text For This error.
  Reason: 15105)

What can I do?
Thanks for your help!!

Followup:

Well, actually I need restore from a script embedded in my system, not from SSMS; but SSMS (even as Admin) did not have access to the backup file.
If I move the file to C: or D: is the same...

I have SQL running under Windows Autentication account (integrated security), my only windows account have admin rights... I don't understand what happens.

The curious is the path "MSSQL10.SQLEXPRESS" doesn't exists, SQL is in "..\MSSQL10_50.SQLEXPRESS\MSSQL..". Does this have something to do?

You don't need rights to access the backup file, the SQL Service does. Running SSMS as administrator will not help.

Check that the account you have SQL running under is able to read from that directory.


Is it a good idea to shrink prior to a SQL Server upgrade to a new instance We currently have a 100GB database which is in simple recovery mode, and it is running on 2008 R2. We have a new server with SQL 2016 Enterprise installed, ready to go - and we're building a migration plan, for how to do the upgrade.

We've ran a test run using SQL 2016's Data Migration Wizard (https://www.microsoft.com/en-us/download/details.aspx?id=53595), which we used to point to our current, live, production DB instance and to restore the DB to '16. This took 15 minutes, which is very quick considering the other approach (backup currently-running DB, copy over to new server) -- mainly because we can't back up / restore over a UNC path.

We've realised that we have a lot of old/unneeded data in our DB, and have kicked off some scheduled tasks using the SQL Agent to perform some deletes, so by the time we're near to our "go live" date then we'll have less data residing in the DB, with the idea being that we should be able to restore the live instance over to '16 quicker with less data.

However, obviously the actual size of the database FILE on disk will not shrink until we perform a DB shrink.

We're thinking of shrinking the DB for these reasons:

faster backup times
less files to restore when we take a copy of production,  scrub it and restore to a test environment, for a like-for-like copy of production to perform tests against
there will be a lot of residual space after we've completed these deletes, so makes sense to reclaim it (I'm thinking ~30-35GB, so a third of the DB itself)

I'm aware that by shrinking the DB then the DB size will just grow again in some time, but I think we've previously overlooked some aspect of data growth, to which we've put some processes in place to reduce excessive DB writes. We currently have a 75MB autogrow which happens around 2-3 times a day.

Will this be a good idea? We'll be restoring our DB over, upgrading the SQL compatibility level and then running an index rebuild (using Ola Hallengren's index script) & updating stats. I'm curious if the shrink will massively spike these index operation times as it'll leave the DB pretty fragmented from what I've understood. I'm not sure what the best approach is here - perhaps don't shrink and only rebuild the index on '16 after the data migration?

Thanks

I hope following information will help you to make your final decision.
faster backup times
You will not have a faster backup time.  See this answer by Aaron
Bertrand.

The backup process does not back up empty pages, only pages
with data, so typically, the backup file will be a lot smaller.
One important thing to note: when using compression, the initial size
needed on the destination drive will be the uncompressed size; the
backup will only be fully compressed at the end of the process.

less files to restore when we take a copy of production
You are not getting rid of any files so I am not sure what you are gaining here. Yes you can save your allocated space by shrinking the database.
there will be a lot of residual space after we've completed these deletes, so makes sense to reclaim it (I'm thinking ~30-35GB, so a third of the DB itself)
It all depends, can your company afford to spare 30~40 GB of space for few weeks/Months which you will need anyway.  You mentioned your auto-growth is set to 75MB and it happens 2-3 times a day. Based on this information you will use 30GB space in  in next 4.5 months.
If you are rebuilding index please do not update all statistics after that. Read this.
I will highly recommend you to increase the autogrowth size and avoid growth 2-3 times a day. Read this.


Replace a specific part of a string I have many strings in the database like:

Original

www.youtube.com/embed/-qTiYA1WiY8&index=8&list=PLPNcLSfYNC3Dw535smncyHRCBRmdf1Bti

Desired

www.youtube.com/embed/-qTiYA1WiY8

So final string can be converted to:

I can do this using PHP, but I want to do it with a MySQL query. I tried to use:

UPDATE MyTable
    SET StringColumn = REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis')

It needs to be a general solution that can replace string from & and before " if exist in the string. I am not able to get the proper result.

If the strings are all the same, and the column is called c1 in a table called t1 then SUBSTRING_INDEX() might get you there.  Don't update your table until you've verified it with SELECT.

Whitespace added for clarity.

SELECT CONCAT(
  SUBSTRING_INDEX(c1,'&',1),
  '"',
  SUBSTRING_INDEX(c1,'"',-3)
) FROM t1;

SELECT the substring left of the first '&' counting from the left, a literal " and then the substring right of the third " counting from the right.

The only caveat is that the link format you've shown seems wrong.  The first "&" seems like it should have been "?" instead, and you should be clipping it at the "?" which marks the start of the query string in a URL.  Perhaps youtube was doing something nonstandard with URLs to work around plugin or browser issues.


Mandatory and non mandatory attributes for products I am trying to come up with a simple way of implementing the following design, but I always end up with something overly complicated.

I am trying to design a database where companies can store products. Each product belongs to exactly one category, and depending on the category, they share some common attributes that I want to be mandatory. They can also have different attributes that are up to the company to define.

As of now, and based on this question I asked last year, my idea is to have the following:

Company table to store everything related to the company (one company can have several products)
Product table to store everything related to the product (one product can be in one category, and can have several features)
Category table just to save the name of the category
Attribute table attributes that can be defined by the company for that product
Product Feature table joint table

With these tables, companies will be able to add their products to a category and add as many attributes as they want.

Now, dependant on the category, I want to make mandatory to define a set of attributes. For example, if we are talking about groceries, and a category would be 'Fruits', I might want the company to always provide the following attributes: color, country of origin. If instead of fruits, the category is 'Cereals' I might want to be always provided with the following: allergens, expire date...

I have thought of two different solutions, which are:

Create a new table for each category that needs these mandatory attributes. This, of course, will be a problem as if a new mandatory attribute is added to an existing category, all previous ones will have no value. Also, every time I add a new category I should define a new table for the attributes.
The other option I have thought of is to simply manage this in the frontend of the application. Once a company adds a product belonging to one of those categories, prompt them to input the mandatory attributes and simply save them as a 'normal' attribute.

I am more inclined towards using the second solution. Using the first one I could end up with a ddbb with dozens of tables, but I would like some input to see if anyone can come up with a better solution.

I think further information is still required before a design can be decided; namely, whether it is important to validate the attribute data in any way. This may affect the data type you choose for storing attribute data.
For example, you will probably want to store your "expire date" attribute using a date-based data type. There are many reasons for this, but consider one: if you accept just strings in which people type information representing dates, you may get a variety of formats for those dates, eg) "1/2/20" for first of February, 2020, in Australia, "1/2/20" for second of January, 2020, in USA, "Feb 1st 2020", "1 Feb 20" and "1 February 20" - all representing the same date. Yuck.
You could mitigate that issue by introducing rules in your application logic - for example, use a date picker which always outputs dates in a standard format ... but now the robustness of your data model depends on application developers doing what you ask them to.
However, even without a decision on data types, I can propose some guidelines that have their caveats (relating to the questions of validation).
I would have a table called product. If there is data that every product has, regardless of category, then I would store it here - think name, code, activeFlag, dateCreated, createdBy, dateLastModified, lastModifiedBy, etc.
I would have a table called category. This will probably have a name field. (If you are building a category tree then you will need fields to allow you to put categories into a hierarchy. For now I will assume the category list is a straight list of categories - not a hierarchy).
I would have a table called attribute. This will have a field named code. The code is a unique, non-null value that, once set, should never be modified. This means if you need to introduce business rule logic into your application that relates to a given attribute, your application can reference the attribute by its code value - I am talking about hard-coding the data value in application code - and not have to worry about the logic breaking in future (because that data value will never change). A concrete example: Say "colour" is an attribute - let's put a field on the table called name and put the word "Colour" in that field. Now, in the code field we might put a value like "COL" (short for "colour"). In your application you might have a business rule saying that managers can update the colour value for products, but other staff cannot, so your application code will have a line of code something like if userIsManager===1 and fieldCode==='COL' then renderFieldAsEditable().
All good so far. Now I would create a table categoryAttribute which contains the PK columns from category and attribute (probably autoincrement integer values - I didn't bother mentioning them before). This table essentially says "this category has this attribute". Now I would put further fields on this table, such as mandatory - perhaps as a bit data type where 1 means this attribute is mandatory in this category, and 0 means it is optional. I might include sortOrder of some kind - so you can dynamically define the order in which these fields should be rendered by the application code - which, if you haven't guessed, should just be looping through all attributes for a given category when it generates the data entry form in the application. If you want to get really clever, maybe have a field for validationMask which will contain a regular expression - in the language of your choice, to be applied by the application at data entry time: user data fails to match the regular expression? Too bad - you can't update. Oh, then why not have validationMessage too, to show them why we can't save that bad data (like "Dates must be in the format DD/MM/YYYY").
Coming back to that question of data validation - all well and good if you can validate with a singular regular expression (and the mandatory flag), but if you need to validate on the database server prior to updating/modifying data, then consider the following additions. You might like to put another column on your attribute table called something like type or attributeType. It is a pseudo-representation of data type. It might contain string values from a fixed set that you define, like "date", "int", "string". You might then write a few database functions along the lines of validateIntAttribute, validateStringAttribute and validateDateAttribute. They might make use of native functions like SQL Server's try_cast to check that the data value received from the application (ie. received as string input) can be cast to the data type we want.
Lastly, we come to saving user-contributed data. For this we have a table for data. It relates to product and attribute. This means for a given product-attribute combination, here we are saving the data value - probably in a field like value or data. This means literally every single data value becomes a separate row on this table - regardless of which product relates to or how many attributes are saved for a given product. If you wanted to, you could actually have a few tables like this - for the different data types (I mentioned date, int and string before) - and then you could actually cast your data to the relevant data type, but splitting across tables means that when you retrieve data you will have lots of "if the attribute is of type 'int' then join to the intData table" - and that's really bad for performance and legibility - I don't recommend it.
So... the above works fine, if you are ok with the caveat of all your data being saved in a string-based type (and therefore building whatever you need to, to ensure consistency in data representation).
You might also want to think about another type of attribute: the dropdown list. I'll leave that modeling to you.
I have built all the above, and it works.
It takes some people time to wrap their heads around the notion of fields being records (rows) on a table (here, attribute) and the data values for a single "record" (like your products) being split across multiple rows (in your data table). It also takes time to think about how you're going to query your data and edit or delete it, but it can be done. The result is a very dynamic and incredibly extensible approach to storing attribute data which has a good measure of future-proofing. In fact, during development of the system I've designed, the business encountered an excellent opportunity - but needed to be able to store two new kinds of information against their records. Two new fields were defined and added to the system in less than five minutes and the application rendered them - in correct sort order, with validation rules implemented - automatically; no coding/deploy/release required.
Oh! One last comment. You mentioned you are looking for a simple solution and always come up with something overly complicated. I wouldn't call the above design simple, but what I will say is that any time you need new fields for storing data in future, you simply define them dynamically (in data) in minutes without the need for any application deployment. Secondly, the system I built was geared around contact information - I found at least seven different areas in the application where contact information had been modeled in different ways: that's seven iterations of design, code, test, deploy - each with a different data model, each with its own issues, none of them extensible. This one design will, in due course, replace all seven of those - making that information stored in a consistent way across the entire application. So that's your second trade-off - building something extensible is more work now for trivial work in future. Building something "simple" now means less work now and ongoing work - and reduced business responsiveness - in future.


Full text search using words stored in another table Is there a way to do a full-text search using words stored in another table?  This is my scenario:

Table_A contains phrases entered by users in field "UserInput" (and other stuff).  I have a full-text index on this table looking at UserInput.
Table_B with one field "Word" containing a bunch (more than a 1k) words that I want to make sure my users are not using.

I tried this query:

Select *
From Table_A
  Cross Join Table_B
Where Contains(UserInput, Word)

But that does not work. The error says "Incorrect syntax near 'Word'". SSMS also complains saying "Expecting STRING, TEXT_LEX or VARIABLE".

I have also tried CHARINDEX and LIKE in the WHERE statement but those functions return false positives as they do not look for whole words but strings within strings.

If full-text will not work, is there any other approach that can help identify whole words from Table_B.Word in Table_A.UserInput?

UPDATE:

Also tried the following:

Declare @AllWords nvarchar(4000) = '';
Select @AllWords = @AllWords + Case When @AllWords = '' Then Word Else ' OR ' + Word End
From Table_B;
Set @AllWords = '''' + @AllWords + '''';
Select *
From Table_A
Where Contains(UserInput, @AllWords);

This one seems to bring up the right results but I don't have a way to know what words were found in Table_A.UserInput.

To reiterate, you currently have a table with a list of words you want to make sure your users are not searching Table_A with.
You have successfully been able to query Table_A with Table_B which contains your list of bad words.
Your end goal going from the first point, to the second point, is that you want to know if they searched for those bad words, what would be returned. What bad word correlates to a return of a record in Table_A.

What you need to do to resolve point 3 is query sys.dm_fts_parser. There is an excellent Example by Jefferson Elias on SQL Shack. I'll post the relevant bit in case it is removed or broken by the internet later:

  How to check the results of a Full-Text parsing

  There are two ways to check how Full-Text feature parses a given text
  depending on the source of the text.

  Source of the text is a String

  If you want to check fast what keywords you would get for a particular
  string, you might want to use sys.dm_fts_parser built-in function.

  Here is an example of call to that function.

  The first parameter is the string that has to be parsed. The second
  parameter is the language identifier. Here, it’s set to 0, which means
  it’s neutral. The hhird parameter is the identifier of the stoplist.
  Here no stoplist is used. The last parameter tells this function
  whether to be sensitive or not to accents. Here, we asked for
  insensitivity. In other words, this function will take the information
  you would provide when creating a Full-Text Index.

 select * from sys.dm_fts_parser(
    '" dsolkjfdskljfsd dfsd-MMM-236.127 dojfdslfkjds"',
    0,
    NULL,
    0
) ;

  If a table is already created with a Full-Text index, we would use
  another dynamic management function (DMF) called
  sys.dm_fts_index_keywords which takes as a parameter:

  The database identifier in which it should look at The object
  identifier in that database It returns a dataset with a hexadecimal
  representation of the keyword, its corresponding form in the plain
  text, the identifier of the column in which the keyword has been found
  and finally the number of documents where this keyword can be found.

  You will find below a T-SQL query to get back keywords found by
  Full-Text feature in our dbo.DM_OBJECT_FILE table so as its results
  set.

select *
From sys.dm_fts_index_keywords(DB_ID(),OBJECT_ID('dbo.DM_OBJECT_FILE'))

This method should show you what would be returned based on your criteria. To help with point 1, you may want to look into Stopwords, Stoplists, and the Thesaurus features to help change the words from Table_B into usable words. Alternatively, you may need to implement triggers to keep the terms from being searched.

I imagine this code would work:

Declare @AllWords nvarchar(4000) = '';
Select @AllWords = @AllWords + Case When @AllWords = '' Then Word Else ' OR ' + Word End
From Table_B;
Set @AllWords = '''' + @AllWords + '''';
select * from sys.dm_fts_parser(
    @AllWords,
    0,
    NULL,
    0
) ;


Comma-separated join (cross join) I don't understand how this query works:

1    SELECT
2        EXTRACTVALUE(definition_xml.COLUMN_VALUE, 'SubtypeFieldInfo/DomainName') AS domain_name
3    FROM
4        SDE.GDB_ITEMS_VW i
5        JOIN SDE.GDB_ITEMTYPES it ON
6            i.Type = it.UUID
7            ,TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo'))) definition_xml

What does the comma at the start of line #7 do?

My guess is that whoever wrote the query used some sort of SQL short-hand. Perhaps if someone could help re-format the query in 'beginner SQL' it might help.

The comma is the implicit join operator, ANSI SQL-89 syntax. No different than the comma in:

select ...
from a , b             -- <-- this comma
where a.aid = b.aid;

I think you can replace it with CROSS JOIN*, assuming a recent enough Oracle version.

The i, it and definition_xml are the aliases of the 3 joined tables (the 2 tables are base tables or views and the 3rd is something like a derived table, Oracle calls them "table collection expressions") but they are no different regarding how they can be joined or referenced.

The parentheses around the joins and the last table are completely optional. I added them just to show how the query is parsed:

SELECT
    EXTRACTVALUE(definition_xml.COLUMN_VALUE, ...) AS domain_name
FROM
      (   SDE.GDB_ITEMS_VW i
        JOIN
          SDE.GDB_ITEMTYPES it
        ON
          i.Type = it.UUID
      )
    ,                           -- or: CROSS JOIN
      (  TABLE(...) definition_xml
      )
;

* CROSS JOIN and the comma join operator have different precedence but this doesn't matter for the specific query. As a good practice though, it's best not to mix the comma join with explicit JOIN.


query two point values from a table then insert a new record after creating a line from the two points I am running postgres: 9.5.19

I have already point values as separate records in a table. I want to create a new record which represents a line geom derived from the two points above. I have lots of point records but I want to be able to select by id each point to link together.

I am trying this syntax:

insert into locations (geom,type,label,user)
select st_makeline(a.geom,b.geom) as line from (select * from locations where id = 144) as a, (select * from locations where id = 145) as b,
'Link','test','ADMIN';

and get this error:

  ERROR:  syntax error at or near "'Link'"
  LINE 1: ...a, (select * from locations where id = 145) as b, 'Link','te...

Can someone point me to a working syntax?

You have to put the literal strings in the right spot.  You have them as part of the FROM clause, which doesn't make sense.  Put them in the select-list.

insert into locations (geom,type,label,user) select
    st_makeline(a.geom,b.geom) as line, 'Link','test','ADMIN'
from (select * from locations where id = 144) as a,
     (select * from locations where id = 145) as b;


Query very slow on innodb I'm using mysql 5.7 on ubuntu server 14.04, and engine innodb.
I have a table that are very huge (about 66 Go), I insert and delete from it without problem, but some query of select are taking lot of time till they are abondoned from java.

When I do select count(*) from TABLE, it take about 10 minutes.

I export all my database and data to another server ubuntu server 16.04 mysql 5.7, and do the same query and it take about 2 minutes.

Why this difference happends on the same table and same data?

Here is my config files :

First server (10 minutes)

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket  = /home/mysql_data/mysqld.sock
datadir = /home/mysql_data
log-error   = /var/log/mysql/error.log
bind-address    = 127.0.0.1
symbolic-links=0
open_files_limit = 10000
query_cache_type=0
query_cache_size=0
innodb_buffer_pool_size = 36G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8
innodb_thread_concurrency = 8
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000
innodb_stats_on_metadata = 0
innodb_buffer_pool_dump_at_shutdown = 1 # MySQL 5.6+
innodb_buffer_pool_load_at_startup = 1 # MySQL 5.6+
innodb_buffer_pool_dump_pct = 75 # MySQL 5.7 only
innodb_checksum_algorithm = crc32 # MySQL 5.6 or newer
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_file_per_table

Second server (2 minutes)

[mysqld_safe]
socket  = /var/run/mysqld/mysqld.sock
nice    = 0
[mysqld]
user    = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket  = /home/mysql/mysqld.sock
port    = 3306
basedir = /usr
datadir = /home/mysql
tmpdir  = /tmp
lc-messages-dir = /usr/share/mysql
bind-address    = 127.0.0.1
skip-external-locking
key_buffer_size = 16M
max_allowed_packet  = 16M
thread_stack    = 192K
thread_cache_size = 8
query_cache_limit   = 1M
query_cache_size = 16M
myisam-recover-options = BACKUP
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size = 100M

Let check the the table size after you migration to new db(in new db server).
a reason is

First server (10 minutes)

your table might not up to day of statistic use ANALYZE TABLE for Innodb.
you mention you table is 66GB but it might not the real table size it may include the dead-tuple(caused by your update,delete operation).

when you migration it to other place this table or whole database will be ANALYZE to the real time statistic.


SQL command mode - unable to run command I just answered this question but never tried to run it. So I tried it.. https://stackoverflow.com/questions/53521410/sql-command-mode-sql-server-unable-to-print/53521635#53521635

and I thought if Print works then the command will run for sure but to my surprise it didn't

USE [DBName]
        :setvar ScriptPath 'C:\Work\'
        :setvar SQLFile 'Test.sql'

         ----- commented  PRINT $(ScriptPath) + $(SQLFile)    ---- Works
        GO
         :r $(ScriptPath) + $(SQLFile)   ---  Doesn't work
-- throws:A fatal scripting error occurred. Incorrect syntax was encountered while parsing :r

             :r $(ScriptPath)+$(SQLFile)   ---  Doesn't work
    -- throws:A fatal scripting error occurred. Unable to process :r command
             GO

I searched but couldn't find correct answer.

What is needed to be fixed here ?

I used double quotes in your setvar commands and removed the (+) signs from your attempted concatenation.  This worked for me

:setvar ScriptPath "C:\Work\"
:setvar SQLFile "Test.sql"

print '$(ScriptPath)$(SQLFile)'    ---- Works
GO
:r $(ScriptPath)$(SQLFile)         ---- Works
GO


postgresql - paginate correctly ordering by non-distinct values? How can I properly page by ordering on a column that could possibly have repeated values? I have a table called posts, which has a column that holds the number of likes of a certain post, called num_likes, and I want to order by num_likes DESC. But, the image below shows a problem that I run into - the new row inserted between the two pages causes repeated data to be fetched.

This link here explains the problem, and gives the solution of keyset pagination, but from what I've seen, that only works if the column that the rows are being sorted on are distinct / unique. How would I do this if that is not the case?

I believe the simplest solution is to sort on multiple columns (the one(s) you need for your basic sort, plus another column (or set of columns) that would make the effective "key" of your query unique).

This is more difficult (but may not be impossible) if your table(s) don't have a primary or unique key; you'd have to establish that there is a set of columns that could uniquely identify the rows you're presenting.


PostgreSQL - how to rename CamelCase column? When I tried to rename a CamelCase column like this:

ALTER TABLE mytable RENAME COLUMN camelCaseColumn TO camel_case_column;

I get the following error:

ERROR: column "camelcasecolumn" does not exist

Surrounding the CamelCase column name with single quotes like this:

ALTER TABLE mytable RENAME COLUMN 'camelCaseColumn' TO camel_case_column;

gives me a syntax error.

Is there any way I can rename the column? Or do I have to create a new table and transfer the data?

The database is running on Ubuntu, and I am using Mac OS X to ssh into the server where the database is running on to issue the ALTER TABLE commands. I know that Mac OS X is case insensitive, so could this be part of the problem?

Thanks!

Turns out that using double quotes works:

ALTER TABLE mytable RENAME COLUMN "camelCaseColumn" TO camel_case_column;


Need help rectifying IN/NOT IN clause Hi there everyone!

I have built a link/content sharing site. The way it works is that each link can have multiple categories assigned to it. A video of a motorcycle race might have motorcycle, racing, extreme and trending attached to it. The way it gets stored into the database is by a linkcats table in which it stores the link_id and the cat_id. The example above would result in 4 records for that link_id, since it had four categories assigned to it.

The site learns what people like as they browse. As they view, vote on and post content, the site figures out their most and least favorite categories. My intention is to display the results of what the site thinks is their most favorite while cutting out what it thinks is their least favorite. To do that, I'm trying to display links from their 5 most favorite categories while nixing them if any unfave cats are assigned to them.

Here's what I tried:

SELECT shortenified_urls.*
FROM shortenified_urls
LEFT JOIN linkcats ON linkcats.link_id = shortenified_urls.id
WHERE linkcats.cat_id IN (17,1,35,33,50)
AND linkcats.cat_id NOT IN (28)
AND shortenified_urls.is_social = '1'
AND shortenified_urls.active = '1'
GROUP BY shortenified_urls

This didn't work, I think because of the multiple categories. Since the links are being matched more than once due to multiple category matches, even if links matching 28 get passed by, if that same link gets matched on another of it's categories, it will still get included in the results. At least, I think that's what's happening.

Does anyone have an idea on how I might alter the query to achieve the desired result?

Thanks for your time!

Your LEFT JOIN is converted to a INNER join because the WHERE contains conditions about the columns of the right (linkcats) table. Then, since the conditions are contradicting, the query will always return 0 rows:

WHERE linkcats.cat_id IN (17,1,35,33,50)
AND linkcats.cat_id NOT IN (28)

You need a semijoin and an anti-semijoin, which can be written with EXISTS and NOT EXISTS subqueries, like this:

SELECT u.*
FROM shortenified_urls AS u                        -- all links
WHERE u.is_social = '1'
  AND u.active = '1'
  AND EXISTS
      ( SELECT 1
        FROM linkcats AS lc                        -- that are related
        WHERE lc.link_id = u.id                    -- to at least one of
          AND lc.cat_id IN (17,1,35,33,50)         -- these categories
      )
  AND NOT EXISTS                                   -- but not to
      ( SELECT 1
        FROM linkcats AS lc
        WHERE lc.link_id = u.id
          AND lc.cat_id IN (28)                    -- any of these
      )  ;


Changing SQL Server (MSSQLSERVER) Service Account I am sorry if this has already been answered in the past.
We have SQL Server 2016 which is our Dynamics NAV Database. Currently all backups are stored on local drive of the server. I have been given a task to change the destination of backups to a NAS box network drive. On the maintenance plan I changed the destination path to the network drive \10.##.#.##\databasebackup. I received following error:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\10.##.#.##\databasebackup\navdatabase.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

After having a look at SQL Server configuration manager, I found that the Log On As account for SQL Server (MSSQLSERVER) is NT Service\MSSQLSERVER but Log On As account for SQL Server Agent (MSSQLSERVER) is domain service account. The service account used for SQL Server Agent (MSSQLSERVER) has read/write access to the network drive \10.##.#.##\databasebackup. In this case I believe I need to change the Log On As account for SQL Server (MSSQLSERVER) to a domain account, am I correct? If I change the NT Service\MSSQLSERVER to a domain account which has read/write access to the network share, does it break anything?
Thank you in advance for your help.

Correct, it is the service account for the database engine that matters here. Agent is irrelevant (it only tell SQL Server to produce the backup). It shouldn't break anything, but every change should be done with care. And make sure you do the change with the right tool (SQL Server Configuration Manager).


Executing SSIS Package from a stored procedure with different user privileges I'm having issues with allowing my users to execute SSIS Packages in a reasonable manner due to varying levels of privilege required.
The scenario: we've created a data warehouse, with two different SSIS packages responsible for loading it with data, one is to be run automatically (via a SQL Agent job, and is working fine), and another that must be run on-demand by users once upstream data is finalised and cleansed etc.
This package performs very privileged operations including backing up the database at the start of the run (to be sure, to be sure), dropping and recreating calculated tables etc.
I've written a stored procedure to execute this job via the [SSISDB].[catalog].[create_execution] and [SSISDB].[catalog].[start_execution] stored procedures.... this works fine when run under my account (I'm a sysadmin).
The stored procedure failed when run by a normal user due the higher level of permissions required in SSISDB and MSDB to enqueue the execution, and the package itself failed because it is running under their (lowly) security context.
What I've tried:
I attempted to resolve the issue using 'Execute As' in the stored procedure, however this failed due to the cross-database chaining issues, Trustworthy flag etc.
I've also attempted to solve the issue by having Agent jobs to run the package, and just running the agent job from the stored procedure, however I rapidly entered a world of pain involving:

The inability to set execution permissions on a per-job basis
The hope to configure this access via a central Server Role to cater for changing staff over time, and jobs can only have a single user as owner
The dark world of Proxy accounts, Credentials in combination with sql-auth logins etc

Plans C and D
The only options I can think of remaining to me are to create a dedicated SQL Server Login with elevated permissions, and trust the users not to pass the credentials around/lose auditability of who scheduled the import (how this problem is solved in other areas of the organisation), or custom build a web front end purely to allow the users to authenticate as their 'Server Role' account, and then let the web app run the stored procedure under a second (privileged) connection.
So....
Is there any advice out there on how to:

have an SSIS package perform privileged operations
executed by a lowly privileged user (using an AD windows account)
preferably where the access to run the job is managed via a central Server Role (I don't have an easy ability to create a new windows group for them)
and where any new, intermediate/proxy accounts are SQL Server Auth accounts (again, very limited ability to make changes to the AD)

I understand there's a lot of moving parts here (and some feel like spinning blades) so let me know if there's any other info you feel I've missed.
Cheers,
Tim
Edit....
So today I created a dedicated SQL Server login with ssis_admin permissions, created three SQL Server Agent jobs owned by that user, and updated the stored procedure my end users call to execute as that user.  This failed due to the inability to call create execution as a SQL Server login, it requires a windows account.
I updated the users stored procedure to execute as the Windows account SQL Server is running as (an AD service account), granted it ssis_admin and it fails with the error

The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

This isn't going anywhere fast :(

For posterity I got this working via the following:

Altering the stored procedure called by the users (Admin.RunImport) to 'Execute as' the account used by the SQL Service
The SQL Server service account (an AD Managed Service account) is modified to have permissions to execute the Admin sproc allowing the use of execute as above
The SQL Server service account is modified to have ssisdb.ssis_admin and msdb.SQlAgentOperator roles.
This Admin.RunImport stored procedure queues up one of 3 Agent Jobs using sp_start_job dependent on a passed parameter

This redirection via SQL Agent is required to bypass the ssis security context error above
The agent jobs are owned by 'sa', and simply execute an underlying stored procedure (Raw.hp_Execute_Import_Impl) passing in a different parameter per job.
This means that the Agent job runs as sa due to the ssis_admin privelege above, the same as the scheduled jobs

The Raw.hp_Execute_Import_Impl stored procedure queues the SSIS Package as sa the same as normal.

In lieu of being able to create dedicated windows accounts for this purpose I think this as good as I'm going to get at the moment.

Thanks for the help guys!


How can I search for a hex string in oracle? There's a database record that is incorrect. The name field displays like this in a browser ( this is incorrect ):
Thcˇodore

And when I look at the record via SQL results, I see:
Thc\xCB\x87odore

I'm trying to search for other records that might be affected, so I would like to query the name field for any value "LIKE" \xCB\x87
I've tried using HEXTORAW and many other combinations including:
REGEXP_LIKE(NAME, \'\xCC\x88\' )

REGEXP_LIKE(NAME, \'[\xCC\x88]\' )

NAME like '%' || HEXTORAW(\'CC88\') || '%'

NAME LIKE ‘%’ || X’CC’ || X’88’ ||’%’

NAME like \'%\' || \'\x88\' || \'%\'

Can't seem to get any of them to work, and I can't find any other examples or documentation online. Maybe I've been googling for the wrong things but this is what I've tried to google:

oracle escape hexadecimal
oracle hex to ascii
oracle like hexadecimal query
oracle pcre Hexadecimeal select
oracle query binary character
oracle REGEXP_LIKE hexadecimal
oracle select \x0A
oracle select escape character hex
oracle select hex codes
oracle select like \x0a
oracle select REGEXP_LIKE \x0A
oracle select X 0A

How can I search for \xCB\x87 with Oracle?


Finding the nearest geo points across two tables? I have two tables, both have lat/lon columns within same country (UK). Tables sizes are roughly 80M and 50M.

Along with the lat/lon columns I have created a geo-indexes for both tables in this way:

SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2);
UPDATE my_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

To find the nearest points from the table with 80M respect to 50M within 0.1 miles I run something like:

   SELECT A.latitude, A.longitude, B.latitude, B.longitude,
   FROM my_table_50 AS A, my_table_80 AS B
   where ST_Distance(A.geom, B.geom) < 0.1609 -- 1 mile / 10
   ORDER BY ST_Distance(A.geom, B.geom) ASC LIMIT 1;

The query is very slow to run (pretty much is cartesian 50M X 80M).

Is there a way to speed it up?

Also, is it really useful to use "postgis geo indexing" for a problem like this?
Using "pythagorean theorem" can be enough (like in the chosen answer here https://stackoverflow.com/questions/1664799/calculating-distance-between-two-points-using-pythagorean-theorem), as I am expecting distances way far shortest than earth radius or it may lead to some error?

Don't use ST_Distance. It will never use an index. Instead use KNN distance with <->, and use ST_DWithin when possible.

  To find the nearest points from the table with 80M respect to 50M within 0.1 miles I run something like:

To find the nearest point, you can do..

SELECT A.latitude, A.longitude, B.latitude, B.longitude,
FROM my_table_50 AS A, my_table_80 AS B
ORDER BY A.geom <-> B.geom
LIMIT 1

To find the nearest point within one mile,

SELECT A.latitude, A.longitude, B.latitude, B.longitude,
FROM my_table_50 AS A, my_table_80 AS B
WHERE ST_DWithin(A.geom, B.geom, 1609.34)
ORDER BY A.geom <-> B.geom
LIMIT 1

Also, unless your postgis is old don't ever do

SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2);

From the docs,

  Changed: 2.0.0 This function no longer updates geometry_columns since geometry_columns is a view that reads from system catalogs. It by default also does not create constraints, but instead uses the built in type modifier behavior of PostgreSQL. So for example building a wgs84 POINT column with this function is now equivalent to: ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326);

Make sure in the above you have a spatial/gist index on A.geom and B.geom. And, consider clustering both of them on those indexes.


Deleting all duplicates I am trying to delete all the duplicates but keeping single record only (shorter id).
Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones.
DELETE FROM emailTable WHERE id IN (
 SELECT * FROM (
    SELECT id FROM emailTable GROUP BY email HAVING ( COUNT(email) > 1 )
 ) AS q
)

Its MySQL.
DDL
CREATE TABLE `emailTable` (
 `id` mediumint(9) NOT NULL auto_increment,
 `email` varchar(200) NOT NULL default '',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=298872 DEFAULT CHARSET=latin1

Try this:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable
    GROUP BY email HAVING COUNT(*) > 0
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250,000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE email_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.


MySQL join two table if second table is not empty I have 2 tables like below

table 1: users

id   name
1    John
2    Jane
3    Mery
4    Alpha

table 2 : notes

id   note         owner created
1    alpha alpha    1   2013-02-03 10:30
2    yeah yeah..    2   2013-02-03 10:35
3    crap crap..    2   2013-02-03 10:50
2    yeah yeah..    3   2013-02-03 10:50
3    blah blah..    3   2013-02-03 10:55

Now i want to show in a list all the users with their latest notes
like below

id   name     note
1    John     alpha alpha
2    Jane     crap crap..
3    Mery     blah blah..
4    Alpha

How can I get this in one query?
(PS: I am using PHP for scripting)

SQLFiddle demo

select users.*, notes.note from users
LEFT JOIN
(
select owner,max(created) as mc from notes group by owner
) t on users.id=t.owner
LEFT JOIN notes on t.owner=notes.owner and t.mc=notes.created


A Difficult Case of "A transport-level error has occurred when receiving results from the server" SQL Server We are building a new BizTalk cluster, with two BizTalk Application Servers and two SQL Servers. From SSMS on BizTalk AppServer #1 (and similarly from #2), we have two scenarios:

Current - our infrastructure team just rebuilt 2012/R2 Windows. It cannot connect to one specific SQL Server, but it can connect to others. The perplexing part is that any other machine seems to be able to connect to the same SQL Server just fine, and also run queries against it.
Yesterday - (We rebuilt the box since yesterday, so can't go back... just wanted to describe the issues.) It seemed to be connecting sporadically. When it was connected, I tried a few queries, created a test database and table, and tried the following:

Code:

declare @MaxLoops int = 100
declare @LoopCounter int = 0

while (@LoopCounter < @MaxLoops)
begin
     set @LoopCounter = @LoopCounter + 1

     --select SYSDATETIME(), * from NealTest.dbo.NealTest
     waitfor delay '00:00:01'
     print @LoopCounter
     print SYSDATETIME()
end

My original intent of the script was to see if we lost connections after it got "rolling", in other words I could set it up and increase the @MaxLoops and let it run for an hour or something. (Could also add try/catch to help it keep going to see if their were sporadic connectivity problems.)

The script (with the Select statement commented out) gave a

  Transport-level error

So we started highlight one or two lines at a time. The declare worked fine, and I could print the values after the declare. When we lowered the @MaxLoops to 5 it actually ran.  We increased to 15 it failed with Transport-level error. The same script runs fine (even up to @MaxLoops = 1000) from any other client in SSMS 2008 or 2012 client running against the same SQL Server.

This lead me to think that the packet size involved might have been the issue. I put about 20 rows of data with 40 bytes per row, and was able to select the entire table, which seemed to nullify the idea that the packet size was the issue.

And further, from the BizTalk App Server #1, we can use SSMS to connect to any other SQL Server in our shop, and it works fine. So the issue seems to be specifically between these two pairs of servers.

We are stuck, trying to decide if we should call Microsoft, rebuild the SQL Server, or what.

The SQL Server @@Version is

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
  Dec 28 2012 20:23:12
  Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on
  Windows NT 6.2  (Build 9200: )

The original issue was that the BizTalk configure utility was having the same "Transport-level errors".  In the current scenario, BizTalk is not even installed.  If SSMS cannot connect, we don't expect BizTalk to connect.

There is no firewall between these internal servers. Could some GroupPolicy have any impact?

I'm not sure what authentication method you are using, but I've seen issues when using Windows Authentication when the DC (Domain Controller) is being overwhelmed with requests you can see issues.  Usually it is a timeout issue though.

If you are using Windows Auth, try switching to SQL Auth to rule that out.  Sorry I tried to add a comment but it wouldnt let me b/c i'm new.


How to delete old entries based on MAX(id) after an INSERT query? How can I chain multiple sql queries and reuse a query result as a variable?

Specific: I want to store MAX(id), persist new data, and delete the old one based on the maxid fetched before.

SELECT MAX(id) as maxid FROM table; //TODO how to save this?
INSERT INTO table SELECT * from temp_table...
DELELTE FROM table where id < maxid; //TODO how to reuse?

CREATE TABLE temp LIKE real;
load up `temp`
copy 'last' from real into temp  (why??)
RENAME TABLE real TO old,
             temp TO real;
DROP TABLE old;

Depending on the size of the table, this could be a lot faster than using DELETE.  Also, the table is never not there.

No 'transaction' is needed.


Postgres default privileges do not seem to work I am trying to set up RBAC in Postgres where I have a database and a schema. I have a few roles provisioner, service, mydb_ro, and mydb_rw. I ran the following to change default privileges
ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT SELECT                           ON TABLES TO mydb_ro;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO mydb_rw;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT REFERENCES, TRIGGER              ON TABLES TO mydb_rw;  -- + TRIGGER

ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT USAGE, SELECT, UPDATE            ON SEQUENCES TO mydb_rw;  -- SELECT, UPDATE are optional

ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
    GRANT EXECUTE                          ON FUNCTIONS TO mydb_rw;

this is what I see in default privileges
postgres@mydb=> \ddp
                           Default access privileges
┌─────────────┬──────────────────┬──────────┬─────────────────────────────────┐
│    Owner    │      Schema      │   Type   │        Access privileges        │
├─────────────┼──────────────────┼──────────┼─────────────────────────────────┤
│ provisioner │ mydb_schema      │ function │ mydb_rw=X/provisioner           │
│ provisioner │ mydb_schema      │ sequence │ mydb_rw=rwU/provisioner         │
│ provisioner │ mydb_schema      │ table    │ mydb_ro=r/provisioner          ↵│
│             │                  │          │ mydb_rw=awdDxt/provisioner      │
└─────────────┴──────────────────┴──────────┴─────────────────────────────────┘

But when I am logged in as a user that has the provisioner role and I create a table, a user that has service role cannot see it. What am I doing wrong?
I now ran
GRANT mydb_ro TO mydb_rw;
GRANT mydb_rw TO service;

But that did not help with the permission issue.

Having the 'provisioner' role is not the same thing as being 'provisioner'.
For your shown default permission to apply, you would have to create the object as 'provisioner' (for example, SET ROLE provisioner; before you create the objects)


MySQL: DELETE didn't shrink DB size, nor table size In mydatabase, there is a table table1 with almost 8M row, after deleting around 7M rows from the table, the size of the DB and the table itself remains the same.
Table size:
+---------------------------------------------+-----------+
| Table                                       | Size (MB) |
+---------------------------------------------+-----------+
| Table1                                      |     96000 |

Delete query:
DELETE FROM table1 WHERE foo < 'boo';
Query OK, 7809000 rows affected (43.25 sec)

Size after deleting the data:
+---------------------------------------------+-----------+
| Table                                       | Size (MB) |
+---------------------------------------------+-----------+
| Table1                                      |     96000 |

Any idea why deleting the data from this table didn't decrease the database size?
Server version: 5.7.19-17-log Percona Server (GPL)

There are several ways to delete 'most' of a table.  Perhaps the best is
SET @@innodb_file_per_table = ON; -- if this is not already on  (optional)

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        WHERE ... -- the rows you want to _keep_;
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

More techiques:  http://mysql.rjweb.org/doc.php/deletebig
If this will be a recurring task, see the partitioning suggestion in that link.