SQL Server 2016 - Disparity Between View Definition and View Design I've run into an interesting issue with some databases I've inherited, and could use some help unpacking it all.

The Issue

After adding a column to a table, views selecting * from that table are now breaking in strange ways. The best way for me to illustrate is to repro it, so here goes.

Repro

Here's my table definitions:

CREATE TABLE TestTable1 (
    col1 INT,
    col2 varchar(12)
)

CREATE TABLE TestTable2 (
    col3 INT,
    col4 varchar(12)
)

And some test data:

INSERT INTO TestTable1 VALUES
  (1,'abc')
, (2,'def')
, (3,'ghi')
, (4,'jkl')

INSERT INTO TestTable2 VALUES
  (1,'ABC')
, (2,'DEF')
, (3,'GHI')
, (4,'JKL')

Now the View definition (and yes I know views should have an explicit column set. Whoever wrote this one did not):

CREATE VIEW TestView AS

SELECT
    tt1.*
    , tt2.col4
FROM TestTable1 AS tt1
INNER JOIN TestTable2 AS tt2
ON tt1.col1 = tt2.col3
GO

As you'd expect, this is the result when I SELECT * FROM TestView:

col1        col2         col4
----------- ------------ ------------
1           abc          ABC
2           def          DEF
3           ghi          GHI
4           jkl          JKL

Super sensible. Here's where it runs off the rails - I add a column to TestTable1:

ALTER TABLE TestTable1
ADD col5 varchar(8)

And having done so, here's what the view now returns:

col1        col2         col4
----------- ------------ --------
1           abc          NULL
2           def          NULL
3           ghi          NULL
4           jkl          NULL

Because of course it does. However, if I run the same query used to define the view, I get:

col1        col2         col5     col4
----------- ------------ -------- ------------
1           abc          NULL     ABC
2           def          NULL     DEF
3           ghi          NULL     GHI
4           jkl          NULL     JKL

Which is exactly what I'd expect. So what's happened? I've tried every method I can find to query the view definition to see what's changed:

SELECT VIEW_DEFINITION FROM information_schema.views WHERE TABLE_NAME = 'TestView'

EXEC sp_helptext 'TestView'

SELECT definition
FROM sys.objects AS o
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE o.object_id = object_id( 'dbo.TestView')
  and o.type      = 'V'

And each one returns the definition exactly as it was originally defined. It's not until I use the SQL Server Management Studio Design tool that I see something different:

SELECT        tt1.col1, tt1.col2, tt1.col5 AS col4, tt2.col4
FROM            dbo.TestTable1 AS tt1 INNER JOIN
                         dbo.TestTable2 AS tt2 ON tt1.col1 = tt2.col3

It's the work of a lunatic. It's easily fixed by updating the view to use explicit columns rather than the wildcard *. However, what I'm seeing is that this database and table are referenced in myriad views across multiple databases.

The Question

So my question is thus - where is the metadata/definition for this nonsensical version of the view, and why does it trump what is supposedly the unambiguous definition of the view?

The view will run according to it's metadata, which is no longer correct.

If you run sp_refreshview, it should work correctly.

sp_refreshview  @viewname =  'TestView' ;

select * from TestView ;

+------+------+------+------+
| col1 | col2 | col5 | col4 |
+------+------+------+------+
|    1 | abc  | NULL | ABC  |
|    2 | def  | NULL | DEF  |
|    3 | ghi  | NULL | GHI  |
|    4 | jkl  | NULL | JKL  |
+------+------+------+------+


sysmessages table issue I'm experiencing an annoying problem with a table named SysMessages in one of our databases.
When I run a select statement I don't get the contents of this table, but of the sys.sysmessages view in the master database.
A use [DBNAME] doesn't help, nor select * from [DBNAME].dbo.SysMessages.

I'm in the middle of a migration to a newer SQL Server version.
In SQL Server 2008 R2 I didn't encounter this problem with this database, but in SQL Server 2016 I'm not able to query my own SysMessages table.

Any help would be greatly appreciated.

This is the expected behavior for system compatibility views from SQL Server 2012+:

  When referenced in a user database, system tables which were announced
  as deprecated in SQL Server 2000 (such as syslanguages or
  syscacheobjects), are now bound to the back-compatibility view in the
  sys schema. Since the SQL Server 2000 system tables have been
  deprecated for multiple versions, this change is not considered a
  breaking change.

  Example: If a user creates a user-table called syslanguages in a
  user-database, in SQL Server 2008, the statement SELECT * from
  dbo.syslanguages; in that database would return the values from the
  user table. Beginning in SQL Server 2012, this practice will return
  data from the system view sys.syslanguages.

You're probably going to have to fix your database to not use the same names as  deprecated system tables (now system views in the sys schema).


Oracle idle connections lost I have a problem with an Oracle 11g instance.
Every connection that has been idle for about 20 minutes are lost.
I get the following error

ORA-03135: connection lost contact

I did the following actions to get rid of it:

Changed the value os the IDLE_TIME in DEFAULT profile to UNLIMITED
Checked the TIMEOUT parametes in sqlnet.ora. There are no one setted
Checked the TIMEOUT parametes in listener.ora. There are no one setted

This problem only happens remotely.

What more I can do?

Thanks.

Add the option ENABLE=BROKEN into tnsnames.ora. This will enforce sending TCP Keepalive packets over TCP connection. These keepalives are not sent by Oracle client but by OS Kernel directly.

Then you must configure your TCP stack. Set TCP sending interval to value lower than 20 minutes.

Most probably you have a FW on the path and this FW drops tracking information for every in-active TCP connection.


How to resolve the error for "Alter Subscription disable"? I have the following subscription for logical replication:

pctest1=> SELECT subscription_name, status FROM pglogical.show_subscription_status();
          subscription_name          |   status
-------------------------------------+-------------
         sandbox_rds_pg10_subscriber | replicating
(1 row)

When I try to disable this, it returns the below error:

pctest1=>  alter SUBSCRIPTION sandbox_rds_pg10_subscriber disable;
ERROR:  subscription "sandbox_rds_pg10_subscriber" does not exist

I figured out that the below functions will work:

     pctest1=> select pglogical.alter_subscription_disable('sandbox_rds_pg10_subscriber');
-[ RECORD 1 ]--------------+--
alter_subscription_disable | t

pctest1=> select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+------------------------------------------------------------------------------------------
subscription_name | sandbox_rds_pg10_subscriber
status            | disabled
provider_node     | sandbox_provider
provider_dsn      | host=10.xx.xx.xx port=xxxx dbname=sandbox_uat user=admin password=xxxxxxxxxx
slot_name         | pgl_pctest1_sand2c004cb_sand98307b9
replication_sets  | {default}
forward_origins   | {all}

pctest1=> select pglogical.alter_subscription_enable('sandbox_rds_pg10_subscriber');
-[ RECORD 1 ]-------------+--
alter_subscription_enable | t

pctest1=> select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+------------------------------------------------------------------------------------------
subscription_name | sandbox_rds_pg10_subscriber
status            | replicating
provider_node     | sandbox_provider
provider_dsn      | host=10.x.x.x port=xxxx dbname=sandbox_uat user=admin password=xxxxxxxx
slot_name         | pgl_pctest1_sand2c004cb_sand98307b9
replication_sets  | {default}
forward_origins   | {all}


mongoDB design - to embed or not to embed Let's say I have a mongoDB database with 3 collections:

widgets
thingomajiggers
locations

The way the application is going to be used that I will always force the user to specify a location first.  and then from there, they can drill down into the type object they are looking to read / create / update - namely either a widget or a thingomajigger.

So in this case, would it be better to have a separate location collection and refer to this collection from the documents in widgets and thingomajiggers, or should I embed location information right inside my widget / thingomajigger documents?

Currently my prototype is small so I can't imagine me really noticing performance differences.  But eventually I will have hundreds of thousands of widgets and other object types.

Thanks.

I think I found my answer from the mongoDB manual:

In general, use embedded data models when:

you have “contains” relationships between entities. See Model One-to-One Relationships with Embedded Documents.
you have one-to-many relationships between entities. In these relationships the “many” or child documents always appear with or are viewed in the context of the “one” or parent documents. See Model One-to-Many Relationships with Embedded Documents.

http://docs.mongodb.org/manual/core/data-model-design/

The later scenario would apply to me.


How do I list available casts in SQL Server? In PostgreSQL, there is a command, \dC+ I can use in psql \dC+,

\dC[+]  [PATTERN]      list casts

To lists the possible CASTS. This is useful, because I can easily find out what I can use to cast to timestamp,

\dC+ timestamp
                                             List of casts
         Source type         |         Target type         |  Function   |   Implicit?   | Description
-----------------------------+-----------------------------+-------------+---------------+-------------
 abstime                     | timestamp without time zone | timestamp   | yes           |
 date                        | timestamp without time zone | timestamp   | yes           |
 timestamp without time zone | abstime                     | abstime     | in assignment |
 timestamp without time zone | date                        | date        | in assignment |
 timestamp without time zone | timestamp without time zone | timestamp   | yes           |
 timestamp without time zone | timestamp with time zone    | timestamptz | yes           |
 timestamp without time zone | time without time zone      | time        | in assignment |
 timestamp with time zone    | timestamp without time zone | timestamp   | in assignment |

Is there any command like that in SQL Server? How can I find what I can CAST to DateTime2? Is this documented anywhere?

The Microsoft Docs page for CAST and CONVERT provides a great matrix of conversions in SQL Server.

The image included in the above page:

As far as I know, there is no built-in mechanism for displaying a list of valid casts.


MySQL slow sending data I have a large table that frequently sends 3,000 rows of data to a client.

The longest wait state is Sending data.  I would like to minimize this if possible!

Is there a foolproof way of doing so?

Send fewer rows to the client.

Send less data per row to the client.

Compress the data from the server to the client.

Note that there seems to be a raging argument on the web as to the meaning of "Sending data".  Some day that it might actually mean the prior step.


How Sql server 2012 join cardinality is calculated? We have data warehouse based on SQL Server 2012. Often we get poor cardinality estimates for joins impacting downstream operators and slow execution, memory spills, etc.

Is there a good article or whitepaper or blog or video or something that explains how cardinality for joins are calculated? Update stats with full scan only helps for sometime/some cases.

Also any guidance links on how to remediate join cardinality errors would be very helpful.

To answer your first question, Microsoft released a white paper for the new cardinality estimator in SQL Server 2014. That white paper describes how join cardinality was calculated in the 2012 CE. I also know of a blog post that dives into the internals of join cardinality quite a bit.

To answer your second question, I can go over a few techniques.

Trace flag 2301 changes some aspects of cardinality estimation along with other things. It may improve performance of your queries. Test carefully before using.
Trace flag 2371 causes statistics to be updated more frequently on large tables. This is on by default in SQL Server 2016. This will not help you if your statistics have already been updated with a FULLSCAN.
Trace flag 4139 can provide better estimates when your queries suffer from the ascending key problem. That is, you are filtering on new data on an ascending column and your filter range is outside of all histogram steps.
Trace flag 4199 contains a rollup of query optimizer fixes that are on by default in SQL Server 2016. I don't know if any of them directly affect join cardinality estimates but they might improve the performance of your queries in other ways.
If you upgrade to SQL Server 2014 you can use the new cardinality estimator which drastically changes the algorithms for cardinality estimation. It could offer a significant improvement for your workload.
You can create filtered statistics to get a full histogram with up to 200 steps on a table filtered down to the data that you're interested in. This can be useful for certain types of data skew when you know that queries will be commonly filtering on a specific value.
You can create multi-column statistics when you need a more accurate value for the density of both columns. The histogram for the multi-column statistics is only on the first column. SQL server will not automatically create multi-column statistics for you.
For some queries I've only had luck with putting an intermediate result set into a temp table, gathering stats on the temp table, and referencing that temp table in the query. Sometimes even putting just a few thousand rows into a temp table can make a huge improvement in overall cardinality estimates.


Finding time ranges in multiple rows I have a table with (suppose) just 2 columns:

- fecha (a date)
- ignicion (0/1 representing boolean state)

The goal is to retrieve which periods of time was ignicion held at '1', for such task I figured out an iterative approach like the one in the following queries:

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 00:00:00-03' and fecha < '2016-05-12 00:00:00-03';

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=0  and fecha > '2016-05-11 03:01:38+00' and fecha < '2016-05-12 00:00:00-03';

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 03:42:19+00' and fecha < '2016-05-12 00:00:00-03';

Programming that is no problem, but executing it might be an issue, as the report may involve a month's wide of data, and not from just a single table being "scanned", but around 500 with the same data structure (just different table name).

Any ideas on how this could be achieved in a cleaner/better/faster way?

I've been advised on "window" functions, but I don't really know if any of those actually fit this task.

This can be surprisingly simple with a smart application of a window function.
Based on this table definition:

CREATE TEMP TABLE tbl (
  fecha timestamp PRIMARY KEY
, ignicion bool NOT NULL  -- use an actual boolean!!
);

Query:

SELECT period, min(fecha), max(fecha), count(*) AS row_ct
FROM  (
   SELECT *, count(*) FILTER (WHERE NOT ignicion) OVER (ORDER BY fecha) AS period
   FROM   tbl
   ) sub
WHERE  ignicion
GROUP  BY period
ORDER  BY period;

... combining the new aggregate FILTER clause (pg 9.4+) with a window function in a subquery.

Return counts for multiple ranges in a single SELECT statement

The trick is to compute a running count rows with ignicion = FALSE in the subquery, and then group rows with ignicion = TRUE by that count.

If fecha is not unique or one of the columns can be NULL, you need to do more.


How can I query names using regular expressions? How could I search for a specific pattern in a column?

For example, I like to get all user names that start with either letter A or B.

(btw: this is tagged oracle, but it might be interesting in other RDBMS as well).

Your question doesn't require regular expressions:

WHERE name LIKE 'A%' OR name LIKE 'B%'

For Oracle, there's REGEXP_LIKE; Postresql, there's SIMILAR TO; mysql has REGEXP.  One thing to remember though is not all regular expression engines are the same, so just because they support regular expressions doesn't mean they necessarily support word boundry assertions, non-greedy quantifiers, or negative lookbehind assertions, etc.


SQL Server Buffer Pool Extension in FullText Index Population I have a server that has 32GB of RAM and SQL Server 2014 Enterprise installed over it. I want to try the impact of Buffer Pool Extension (BPE) on Full Text Population.

I created a table and populated it with dummy information till it reached 100GB of size.

First I tried populating the full text index before BPE.  It took 3 hours.
Then I tried populating the full text index with BPE configured on a 140GB SSD with 120GB allocated memory. The weird thing is the population of the full text index didn't get any faster.

I used the following query to create the BPE on SSD then restarted the instance:

alter server configuration
set buffer pool extension
on ( filename = 'Z:\Cache.bpe' , size = 120gb )

The questions are :

How to check the buffer pool usage of SQL Server ( I want to know if the BPE is used )?
Does BPE affect FullText index population?
Did I miss any Server or Buffer Pool configuration?

How to check the buffer pool usage of SQL Server ( I want to know if the BPE is used )?

You can query sys.dm_os_buffer_descriptors

An example would be something like:

SELECT   CONVERT(NUMERIC(38, 2), COUNT(*) * 8 / 1024.0) AS cached_data_mb,
         CONVERT(NUMERIC(38, 2), SUM(CONVERT(BIGINT, free_space_in_bytes)) / 1024. / 1024.) AS free_mb,
         CASE database_id
              WHEN 32767 THEN 'ResourceDb'
              ELSE DB_NAME(database_id)
         END AS database_name
FROM     sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id), database_id
ORDER BY cached_data_mb DESC;

  Did I miss any Server or Buffer Pool configuration?

It doesn't appear that way from the documentation. There aren't a lot of options presented.

  Does BPE affect FullText index population?

You're at the distinct disadvantage of using two features together that not a lot of people use separately. You may have to do your own experiments here.

One thing to be aware of is that BPE activity is single threaded, and each I/O is limited to a single 8K page. This might make them ineffective with Full Text.

I'd much rather stick an appropriate amount of RAM in the server.


Ola Hallengren Index Maintenance with compressed indexes If the Ola Hallengren index maintenance solution is installed onto a server which already has compression applied to several of the indexes, will the script respect the current compression level when rebuilding the corresponding index?

Yes - I don't see how Ola's script could possibly alter the existing compression level of an index.

If you look at the source for Ola's Index Maintenance, you will not see the word COMPRESSION anywhere.  In order to alter the compression level, his script would actually have to have the word COMPRESSION in it.


Show all stored procedure definitions (content) for editing Apologies if this has been asked before, the suggested 'related' questions weren't relevant and my own search turned up very little.

I need to list the definitions (code) of all my stored procedures, so that I can start working through all 450 of them and adding in semi-colons to work toward v2014 standards.

I found the following code here and adapted it slightly:

SELECT
    obj.Name AS SPName,
    REPLACE(modu.definition, 'CREATE PROC', 'ALTER PROC') + 'GO' AS SPDefinition
FROM
    sys.sql_modules modu INNER JOIN
    sys.objects obj ON modu.object_id = obj.object_id
WHERE
    (obj.type = 'P')
ORDER BY
    obj.name;

The code above is run using the SA account.

I assumed that this provided exactly what I needed, but, I've just noticed that somebody on MSDN is claiming it doesn't show the full text as expected.

When pasting the results into the SSMS editor, I have 46,000 lines of code to work through, so its not apparent to me if anything is missing.  I'd like to be sure I've started this process correctly before wasting a day in the editor.

Therefore, I am reaching out to you pro's to ask if this is a genuine shortcoming of this approach, or is there an alternative method rather than one-by-one?

The problem mentioned on MSDN has nothing to do with sys.sql_modules or OBJECT_DEFINITION(); they've misinterpreted the problem. What actually happened is they were thwarted by an output limitation in Management Studio, which by default will only show 255 characters and at most 8192 in any output tuple in Results to Text.

So first, make sure you change this to 8192:

  Tools > Options > Query Results > SQL Server > Results to Text >
  Maximum number of characters displayed in each column

Now you can make one script for all the procedures that are less than 4K:

SELECT REPLACE(m.definition, N'CREATE PROC', N'ALTER PROC')
  + CHAR(13) + CHAR(10) + N'GO'
FROM sys.sql_modules AS m
INNER JOIN sys.procedures AS p
ON m.[object_id] = p.[object_id]
WHERE LEN(m.definition) <= 4000
ORDER BY p.name;

(Of course, your replacement is naïve - what if they have create    procedure (more than one space) or you are on a case sensitive collation? You may have to fix some of those manually.)

Now, you only have to worry about manually deriving the script for the larger procedures. First, see if you have any:

SELECT name
  FROM sys.procedures
  WHERE LEN(OBJECT_DEFINITION([object_id])) > 4000;

I talk a little about some workarounds here (in the context of dynamic SQL, but the same would be true if you assigned OBJECT_DEFINITION() to a variable).

Mr. Magoo is right though, you will get the full definition without having to worry about truncation if you use the scripting functionality within Management Studio:

right-click a procedure, modify or script as > alter to >
in Object Explorer Details, do the same as above but for multiple
right-click a database, tasks > generate scripts > (but this does CREATE, not ALTER, so you'll need to perform replacements still)

As an aside, while you are adding semi-colons everywhere, I also suggest ensuring that all of your procedures are created with schema prefixes (e.g. dbo.procedurename, not just procedurename), and also for all object references inside the procedure. Those are likely to cause problems long before a lack of semi-colons ever will. Also a few general things to keep in mind while performing your changes.


How to add a PostgreSQL 10 identity column to an existing table with rows? I have a table with some rows and I try adding an identity column using the command:

ALTER TABLE sourceTable
    ADD COLUMN ogc_fid int
        GENERATED BY DEFAULT AS IDENTITY;

The command above is suggested as answer to this question:

How to add a PostgreSQL 10 identity column to an existing table?

But I get:

  ERROR:  column "ogc_fid" contains null values
  SQL state: 23502

It looks like the existing rows of the table do not get the identity auto generated but result in this error (the command works fine if the table has no rows). Any ideas? Do I have to first generate values for the column I'm adding maybe using DEFAULT nextval('some_sequence')? Or is there a better approach in PostgreSQL 10?

I cannot reproduce this with current Postgres 10.3. (This error should never occur.) Simple test:

dbfiddle here

I suspect you are running an outdated point release. Postgres 10.2 fixed some bugs that fit your case exactly. The manual:

  Fix assorted failures to apply the correct default value when
  inserting into an identity column (Michael Paquier, Peter Eisentraut)

  In several contexts, notably COPY and ALTER TABLE ADD COLUMN, the
  expected default value was not applied and instead a null value was inserted.

If so, an upgrade should fix your problem.
Consider the project policy:

  We always recommend that all users run the latest available minor release for whatever major version is in use.


GRANT DROP ON TABLE springbootdb.* TO AdminUser I've created an MsSQL database to serve a springboot application.

I want to grant something like the following permission to an admin user:

GRANT DROP ON TABLE springbootdb.* TO AdminUser

How can I assign this using Server Management Studio?

There isn't a specific "drop table" permission. But you can give them control on the schema using:

GRANT CONTROL ON SCHEMA::springbootdb TO AdminUser;

This allows them to drop any table in that schema, but also to do all kinds of other things you might not intend. You can take some of those things away with more granular DENY permissions, or with reactionary tools like DDL triggers (which can roll back operations based on the object and user).


Postgres taking 3h to DELETE CASCADE I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 parts "P3", ...).

The items table contains ~500k rows, and each row references between 10 and 10000 rows on each part table, meaning each part table can contain several million rows.

Each table has a _id_ primary key, and each "child" table has a foreign key pointing to item._id_ (with an index) and UPDATE/DELETE CASCADE so that all parts are deleted when an item is deleted. Some parts reference each other as well.

SELECT on item and parts tables is quite fast (SELECT * FROM p1 WHERE item_id=? <1000 ms) but DELETE is awfully slow: it took a full 24h just to DELETE FROM item WHERE _id_=?.

I tried to delete sequentially from each parts with DELETE FROM p1 WHERE item_id=?: the first deletion took 3h but the next ones took a few ms only... Can it be some inefficient cache-fetching? In case statistics were a problem, I ran a VACUUM ANALYZE on the whole database (which completed in a couple hours) but deletion still is painfully slow. Items are added at a pace of 10/h so I will eventually run out of disk space in the near future (~100 GB left now).

I ran EXPLAIN ANALYZE DELETE FROM item WHERE _id_=?:

Delete on "item"  (cost=0.42..8.44 rows=1 width=6) (actual time=10375838.942..10375838.942 rows=0 loops=1)
  ->  Index Scan using "item_pkey" on "item"  (cost=0.42..8.44 rows=1 width=6) (actual time=0.043..0.054 rows=1 loops=1)
        Index Cond: ("_id_" = 29878)
Total runtime: 10375838.996 ms"

I'm don't know much about those costs and their meaning; I just see it is using the index, but I can't explain why it takes so long. The deleted items are the old ones, barely accessed (if at all), so I could understand cache miss.

Is there any parameter to tune to speed it up? I can also wait for maintenance time to do some things like dropping indexes, deleting rows, rebuilding indexes, as we can lock down the database (but then, what to do?), but I would prefer to be able to do it live, if possible. If I have to choose, the database should be optimized for fast INSERT and SELECT over fast DELETE.

Here is an excerpt from the table definitions:

TABLE item(
    _id_ serial,
    ...
    CONSTRAINT item_pkey PRIMAY KEY (_id_)
)

TABLE p1(
    _id_ serial,
    item_id integer,
    ...
    CONSTRAINT p1_pkey PRIMARY KEY (_id_)
    CONSTRAINT fk_item FOREIGN KEY (item_id)
        REFERENCES item(_id_)
        ON UPDATE CASCADE
        ON DELETE CASCADE
)
CREATE INDEX idx_p1_item ON p1 USING btree(item_id)

TABLE p2(
    _id_ serial,
    item_id integer,
    p1_id integer,
    ...
    CONSTRAINT p2_pkey PRIMARY KEY (_id_)
    CONSTRAINT fk_item FOREIGN KEY (item_id)
        REFERENCES item(_id_)
        ON UPDATE CASCADE
        ON DELETE CASCADE
    CONSTRAINT fk_p1 FOREIGN KEY (p1_id)
        REFERENCES p1(_id_)
        ON UPDATE CASCADE
        ON DELETE CASCADE
)
CREATE INDEX idx_p2_item ON p2 USING btree(item_id)
CREATE INDEX idx_p2_p1 ON p2 USING btree(p1_id)

Danke schön to @a_horse_with_no_name for pointing EXPLAIN ANALYZE VERBOSE that shed light on a foreign key loop in my definitions.

I need to explain more about the tables:

An item is composed of parts p1, p2, ... (12 or 13 types) and of "meta parts" meta_p1 that are groups of several parts p1
A device is like an item (it is also composed of parts and meta part 1) but with different columns.

Which means we have a graph like this (mind the ASCII-art ;))

 DEVICE       ITEM
(1)(1)       (1)(1)
 |   \       /   |
  \  (n)   (n)  /
   \  META_P1  /
    \   (1)   /
     \   |   /
     (n)(n)(n)
         P1

(I might have reversed (1) and (n), I mean "(1) item has (n) meta_p1").

Whenever an item is deleted, I want all parts p1, p2, ... and meta_p1 to be deleted as well. Same when deleting a meta_p1: all p1 "contained" in the meta part were supposed to be deleted (which is a logic flaw, as an instance of p1 can also exist outside of meta_p1). It meant that there would be a kind of n^2 search because of that loop (delete item => delete p1 and meta_p1 but delete meta_p1 also means delete p1), as shown on the EXPLAIN ANALYZE VERBOSE results:

Delete on public.item  (cost=0.42..8.44 rows=1 width=6) (actual time=0.100..0.100 rows=0 loops=1)
  ->  Index Scan using "item_pkey" on public.item  (cost=0.42..8.44 rows=1 width=6) (actual time=0.040..0.041 rows=1 loops=1)
        Output: ctid
        Index Cond: (item._id_ = 29919)
Trigger RI_ConstraintTrigger_a_24686 for constraint fk_item on item: time=0.116 calls=1
Trigger RI_ConstraintTrigger_a_24719 for constraint fk_item on item: time=0.232 calls=1
Trigger RI_ConstraintTrigger_a_24747 for constraint fk_item on item: time=33.225 calls=1
Trigger RI_ConstraintTrigger_a_24784 for constraint fk_item on item: time=50.434 calls=1
Trigger RI_ConstraintTrigger_a_24817 for constraint fk_item on item: time=0.351 calls=1
Trigger RI_ConstraintTrigger_a_24840 for constraint fk_item on item: time=0.333 calls=1
Trigger RI_ConstraintTrigger_a_24863 for constraint fk_item on item: time=0.278 calls=1
Trigger RI_ConstraintTrigger_a_24886 for constraint fk_item on item: time=0.269 calls=1
Trigger RI_ConstraintTrigger_a_24909 for constraint fk_item on item: time=0.318 calls=1
Trigger RI_ConstraintTrigger_a_24932 for constraint fk_item on item: time=0.142 calls=1
Trigger RI_ConstraintTrigger_a_24950 for constraint fk_item on item: time=16.547 calls=1
Trigger RI_ConstraintTrigger_a_24977 for constraint fk_item on item: time=48.029 calls=1
Trigger RI_ConstraintTrigger_a_25003 for constraint fk_item on item: time=0.182 calls=1
Trigger RI_ConstraintTrigger_a_24769 for constraint fk_p1 on meta_p1: time=1285.033 calls=53
Trigger RI_ConstraintTrigger_a_24789 for constraint fk_dev on meta_p1: time=102571647.818 calls=53
Total runtime: 102573083.569 ms

The constraint fk_item on item are one for each p* table, and the last two lines show the loop (though I don't understand what fk_dev is doing here as I'm not considering "devices" but "parts").

(Table definitions were not included for brevity).


mysql_connect() system error :111 I have 2 VPS.

I would like to connect from the frist to the second's mysql db like this :

$db = mysql_connect("94.xxx.xxx.xxx:222", "user", "pass");

I got this  error:

  Warning: mysql_connect(): Lost connection to MySQL server at 'reading
  initial communication packet', system error: 111 in
  /var/www/pcsart/test.php on line 4

My server is works on first server is works on port 222.
In the first server I don't have a mysql.
On the second is running on port 3306

First and second server's firewall got these rules :

iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:222
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:222
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:222

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:222
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql

What i'm doing wrong?

By default mysql server accept connections only from localhost, to change this behaviour you should change in mysql config the bind-address variable in /etc/mysql/my.cnf:

 bind-address=ipAddressOfYourDatabaseServer

than restart mysql server.

 sudo service mysql restart

The explain: http://www.howtogeek.com/howto/mysql/switch-mysql-to-listen-on-tcp/

After this i recommend you, that change your iptables, that your 222 port should be reachable only from your other VPS.

 iptables -A INPUT -s ! yourOtherVPSipAddress -p tcp -dport 222 -j DROP

The other thing: the mysql_ funcrions are deprecated, as the other commenter wrote. If you do not want to make a lot of work with your existing code, you should use mysqli. See the php manual, only a minimal work with sintax if you use mysqli as function library.


GCP MySQL query logging I have a managed MySQL instance on GCP. Where would I find a query log? Is this something I need to enable first somewhere? I have read their documentation ( https://cloud.google.com/sql/docs/mysql/how-to ) and didn't find it helpful on this topic.

It's all documented here and here.

Relevant part:

  To make your general or slow query logs available, enable the
  corresponding flag and set the log_output flag to FILE. This makes the
  log output available using the Logs Viewer in the Google Cloud
  Platform Console. Note that Stackdriver logging charges apply.

  If log_output is set to NONE, you will not be able to access the logs.
  If you set log_output to TABLE, the log output is placed in a table in
  your database. If this table becomes large, it can affect instance
  restart time or cause the instance to lose its SLA coverage; for this
  reason, the TABLE option is not recommended. If needed, you can
  truncate your log tables by using the API. For more information, see
  the instances.truncateLog reference page.


Static Cursor and Where current of Static Cursor doesn't allow to modify the data because it's read only, and when executed with "Where current of", it returns error as expected.
So far so good.
But i was surprised to find that static cursor allow to modify data with variable like this.
DECLARE @nome varchar(100), @salario int,@idemp int
DECLARE contact_cursor  CURSOR STATIC FOR
SELECT empno,ename, sal FROM emp

OPEN contact_cursor;

FETCH NEXT from contact_cursor into @idemp,@nome, @salario
WHILE @@FETCH_STATUS=0
BEGIN

If @salario < 5000
    Update Emp
    Set Sal = Sal * 1.1
    where empno=@idemp --No error and do the update
    --Where current of contact_cursor; --gives error

print @nome+' '+cast(@salario as varchar(100));

FETCH NEXT from contact_cursor into @idemp,@nome, @salario
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
The question is:
Which is the difference between using "where current" and the variable extracted with the cursor in this update?

The main difference seems to be how each approach finds the row to be updated. The STATIC Cursor copies the full result set to a hidden temporary table first (hence why it is read-only), so it would seem to be less efficient to then have to re-query the main table for each UPDATE. However, the Positioned Update seems to have quite a bit more in Logical Reads and operations. One advantage of the Positioned Update, however, is noted in the MSDN page for UPDATE:

  CURRENT OF

  Specifies that the update is performed at the current position of the specified cursor.

  A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. This can be more accurate than a searched update that uses a WHERE  clause to qualify the rows to be updated. A searched update modifies multiple rows when the search condition does not uniquely identify a single row.

Test Setup

SET NOCOUNT ON;
-- DROP TABLE ##CursorTest;
CREATE TABLE ##CursorTest ([ID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
                           [Val] INT NOT NULL);
INSERT INTO ##CursorTest ([Val]) VALUES (1), (1), (1), (1);

Updateable CURSOR and WHERE CURRENT OF

UPDATE ##CursorTest SET [Val] = 1;
SELECT * FROM ##CursorTest;

SET STATISTICS IO ON;
DECLARE curTest CURSOR TYPE_WARNING
  LOCAL
  FORWARD_ONLY
  KEYSET -- removing only reduces logical reads by 4
  SCROLL_LOCKS
  --OPTIMISTIC
FOR
  SELECT [ID] FROM ##CursorTest WHERE [Val] < 5
  FOR UPDATE OF [Val];

DECLARE @ID INT;
OPEN curTest;

FETCH NEXT
FROM  curTest
INTO  @ID;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  UPDATE tmp
  SET    tmp.[Val] = tmp.[Val] + 2
  FROM   ##CursorTest tmp
  WHERE CURRENT OF curTest;

  FETCH NEXT
  FROM  curTest
  INTO  @ID;
END;

CLOSE curTest;
DEALLOCATE curTest;
SET STATISTICS IO OFF;

SELECT * FROM ##CursorTest;

Results:

Table 'Worktable'. Scan count 0, logical reads 8
Table '##CursorTest'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 0
Table 'Worktable'. Scan count 1, logical reads 2

Removing the KEYSET option did reduce the logical reads by 4 (I believe), but that might not be a savings on a more complicated query, possibly with JOINs.

Also, switching SCROLL_LOCKS to be OPTIMISTIC increased the Logical Reads.

STATIC Cursor and standard UPDATE

UPDATE ##CursorTest SET [Val] = 1;
SELECT * FROM ##CursorTest;

SET STATISTICS IO ON;
DECLARE curTest CURSOR TYPE_WARNING
  LOCAL
  FORWARD_ONLY
  STATIC
  OPTIMISTIC
FOR
  SELECT [ID] FROM ##CursorTest WHERE [Val] < 5;

DECLARE @ID INT;

OPEN curTest;

FETCH NEXT
FROM  curTest
INTO  @ID;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  UPDATE tmp
  SET    tmp.[Val] = tmp.[Val] + 2
  FROM   ##CursorTest tmp
  WHERE  tmp.[ID] = @ID;

  FETCH NEXT
  FROM  curTest
  INTO  @ID;
END;

CLOSE curTest;
DEALLOCATE curTest;
SET STATISTICS IO OFF;

SELECT * FROM ##CursorTest;

Results:

Table 'Worktable'. Scan count 0, logical reads 8
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2

These simple tests seem to show the STATIC Cursor and regular UPDATE being the better option, and a more complicated query for the Cursor might be an even bigger difference (assuming you are able to update based on the Clustered Key of the target table).

But, if you have a situation where you can't narrow down to a individual row / have no Key value to use, then the Positioned Update would be quite handy.


Query To Return Column Name And Date I have a SQL Table that houses the date that a student was assigned and completes one of three exams.  I am in need of a way to query the table and return the column name of the exam the student needs to take and the date it was assigned.

For example, these are my desired query results:

Joe - exam1date - 01-30-2018
James - exam2date - 03-02-2018
Javier - exam3date - 04-01-2018

And this is DDL for my table:

Create Table Information
(
   fname varchar(100)
  ,exam1date date
  ,exam1complete date
  ,exam2date date
  ,exam2complete date
  ,exam3date date
  ,exam3complete date
)

Insert Into Information (fname, exam1date)
Values ('joe', '2018-01-30')
INSERT INTO Information (fname, exam1date, exam1complete, exam2date)
Values ('james', '2018-02-14', '2018-02-21', '2018-03-02')
INSERT INTO Information (fname, exam1date, exam1complete, exam2date, exam2complete, exam3date)
VALUES ('javier', '2018-01-01', '2018-01-14', '2018-03-01', '2018-03-12', '2018-04-01')

What would a query be that can returned my desired result set from the above table schema?

I guessing that you need the examdate of the last exam that does not have a complete date?

Query

 SELECT fname,
        CASE
        WHEN exam1complete IS NULL THEN 'exam1date'
        WHEN exam2complete IS NULL THEN 'exam2date '
        ELSE 'exam3date' END AS columnname,
        CASE
        WHEN exam1complete IS NULL then exam1date
        WHEN exam2complete IS NULL then exam2date
        ELSE exam3date END AS examdate
FROM Information;

Result

fname   columnname  examdate
joe exam1date   2018-01-30
james   exam2date   2018-03-02
javier  exam3date   2018-04-01


Restore MySQL DB from its folder Some time ago, I've built an webapp on top of WAMP server. At that time, I didn't do any export/dump of my MySQL DB but I've did a copy of the WAMP folder. Does anyone have any ideas about how I can restore my DB from the 'mysql' folder of WAMP?

I have the *.frm files and lots of mysql-bin.* files

Do you have any advice for me?

Assuming default data directory setup (no partitioning etc), you follow these steps:

1) Stop the mysql server
2) Make a backup of the current mysql/data directory
3) Copy all the files and folders from the mysql/data backup directory into the active data directory
4) Restart mysql

This should let you restore the database to the point-in-time of your backup copy. The mysql-bin files aren't necessary for a recovery, but ibdata* files are (assuming innodb tables). If myisam, you should have .MYI and MYD files for each tablespace as well as the .frm files.

Hope this helps


Compilation error on stored procedure using PL/SQL in Oracle? create or replace procedure updateStudentResult(id int,
m1 in int,
m2 in int,
m3 in int,
tot out int,
avg out float,
grd out char)
as

begin

select marks1,marks2,marks3 into m1,m2,m3 from students where sid=id;

tot:=m1+m2+m3;
avg:=(m1+m2+m3)/3;

if avg >= 85 then
grd:='A';

elsif (avg >= 65) then
grd:='B';

elsif (avg >= 50) then
grd:='C';

else
grd:='D';

end if;
UPDATE students SET
total=tot,
average=avg,
grade=grd
where sid=id;

end;
/

  PLS-00103: encountered the symbol '=' when expecting one of the following: (

  these are the line where the error occurs

  if avg >= 85 then grd:='A';

  elsif (avg >= 65) then grd:='B';

  elsif (avg >= 50) then grd:='C';

@kevinsky is correct, you cannot use avg as a variable name in PL/SQL, however that is not the only issue. You also cannot assign values to IN variables like m1, m2, and m3. My best guess is that you meant those to be local variables seeing as the first thing you do with then is SELECT INTO them.

test table:

create table students(
  sid integer
, marks1 integer
, marks2 integer
, marks3 integer
, total integer
, average number
, grade char(1)
);

your procedure errors:

select * from user_errors;

NAME                | TYPE      | SEQUENCE | LINE | POSITION | TEXT                                                                                | ATTRIBUTE | MESSAGE_NUMBER
:------------------ | :-------- | -------: | ---: | -------: | :----------------------------------------------------------------------------------------- | :-------- | -------------:
UPDATESTUDENTRESULT | PROCEDURE |        1 |   17 |        8 | PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
 | ERROR     |            103
UPDATESTUDENTRESULT | PROCEDURE |        2 |   20 |       12 | PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
 | ERROR     |            103
UPDATESTUDENTRESULT | PROCEDURE |        3 |   23 |       12 | PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
 | ERROR     |            103
UPDATESTUDENTRESULT | PROCEDURE |        4 |   32 |       12 | PLS-00103: Encountered the symbol "," when expecting one of the following:

   (
 | ERROR     |            103

your procedure errors after fixing use of avg keyword:

select * from user_errors;

NAME                | TYPE      | SEQUENCE | LINE | POSITION | TEXT                                                                                | ATTRIBUTE | MESSAGE_NUMBER
:------------------ | :-------- | -------: | ---: | -------: | :-------------------------------------------------------------------------------------- | :-------- | -------------:
UPDATESTUDENTRESULT | PROCEDURE |        1 |   12 |       34 | PLS-00403: expression 'M1' cannot be used as an INTO-target of a SELECT/FETCH statement | ERROR     |            403
UPDATESTUDENTRESULT | PROCEDURE |        2 |   12 |       37 | PLS-00403: expression 'M2' cannot be used as an INTO-target of a SELECT/FETCH statement | ERROR     |            403
UPDATESTUDENTRESULT | PROCEDURE |        3 |   12 |       40 | PLS-00403: expression 'M3' cannot be used as an INTO-target of a SELECT/FETCH statement | ERROR     |            403
UPDATESTUDENTRESULT | PROCEDURE |        4 |   12 |       43 | PL/SQL: ORA-00904: : invalid identifier                                                 | ERROR     |              0
UPDATESTUDENTRESULT | PROCEDURE |        5 |   12 |        1 | PL/SQL: SQL Statement ignored                                                           | ERROR     |              0

fixed procedure:

create or replace procedure updateStudentResult(id int,
tot out int,
v_avg out float,
grd out char)
as
  m1 int;
  m2 int;
  m3 int;
begin

select marks1,marks2,marks3 into m1,m2,m3 from students where sid=id;

tot:=m1+m2+m3;
v_avg:=(m1+m2+m3)/3;

if v_avg >= 85 then
grd:='A';

elsif (v_avg >= 65) then
grd:='B';

elsif (v_avg >= 50) then
grd:='C';

else
grd:='D';

end if;
UPDATE students SET
total=tot,
average=v_avg,
grade=grd
where sid=id;

end;
/

with no errors!:

select count(*) from user_errors;

| COUNT(*) |
| -------: |
|        0 |

dbfiddle here

If the grading rules are set in stone, you may wish to use virtual columns instead of updating them at all:

create table students(
  sid integer
, marks1 integer
, marks2 integer
, marks3 integer
, total integer as (marks1+marks2+marks3)
, average number as ((marks1+marks2+marks3)/3)
, grade char(1) as (case when (marks1+marks2+marks3)/3>=85 then 'A'
                         when (marks1+marks2+marks3)/3>=65 then 'B'
                         when (marks1+marks2+marks3)/3>=50 then 'C'
                         else 'D' end)
);

insert into students (sid,marks1,marks2,marks3) values (1,55,66,77);

select * from students;

SID | MARKS1 | MARKS2 | MARKS3 | TOTAL | AVERAGE | GRADE
--: | -----: | -----: | -----: | ----: | ------: | :----
  1 |     55 |     66 |     77 |   198 |      66 | B

dbfiddle here


Should I add SET NOCOUNT ON to all my triggers? It is fairly common knowledge that you should have

SET NOCOUNT ON

by default when creating new stored procedures.

Microsoft has changed the default template to include this in 2012. I thought this should be the same for triggers, yet it is not included in the template.

Is this intentional or just an oversight?

Personally, I would recommend it - I can't think of a reason not to, unless you have a trigger where you specifically want to call out the fact that it's doing additional work behind the scenes.

I wrote a short article about things to watch for when writing triggers, and this is one of them:
http://dave.brittens.org/2012/03/29/writing-well-behaved-triggers/

tl;dr version:

Cut the chatter. Use NOCOUNT.
Make sure your trigger can handle multiple rows.
Prevent unbounded trigger cascading and recursion.
Avoid horrible performance of the INSERTED and DELETED virtual tables.


Access the database by different users simultaneously My two databases(.mdb) are placed at a network shared folder.

Now at the same time few users are connected to database from the network place and doing their job.

In the middle get the error like :

error 1 : "The database has been placed in a state by a user "Admin" on machine "machine no." that prevents it from being opened or locked."

error 2: “Cannot Open Database”. It may not be a database that your application recognizes, or that file may be corrupt.

can anyone please suggest me how i can open this database in programatically, so that different users can run their own application simultaneously without any error.

Since you mention that several users are working fine for a while, you have the database configured for multi-user Shared access.

However, apparently someone on "machine no." is able to acquire Exclusive control of the database. The errors you are getting are the fallout from the Open Exclusive and likely does not indicate any corruption.

This suggests that you also need to configure the security for the database to deny your users the Open Exclusive permission.

There once was help on this at this at a now defunct link. For what it is worth, you can examine:  http://traycare.com/Help/NtwrkOps.htm  and see if it helps you.

EDIT: Note that changes to the database structure (Forms, Views, Tables, etc) require Open Exclusive and should be scheduled for a maintenance window so as to avoid unnecessarily interfering with users of the database.


Does freeing up disk space improve the performance of MySQL? We have a MySQL master server here which was not set up with any kind of management for the binary logs. Over time, the disk space (1 TB) was slowly filling up and it was noticed that the performance of the MySQL server was becoming worse. At the time, I didn't make any connection between disk space issues and performance. I took it upon myself to clean this up, backing up old binary logs and freeing up space on the server.

Now that the transition is (mostly) complete and the server has much more free disk space, I've noticed that the performance has increased. I don't have any numbers to back this up, but one thing that I have noticed is that certain transactions in some of our apps which used to time out on a semi-regular basis are now proceeding smoothly.

I can imagine a couple of explanations for the improved performance:

The disk controller is not having to search as hard for free space
MySQL is better able to optimize tables, indices, etc.

but I really don't know why. So I really have two questions:

Can a lack of free disk space impact MySQL performance?
If so, why?

Can a lack of free disk space impact MySQL performance?

Certainly a lack of disk space at all can prevent writes from happening. Especially when storage is beginning to become full (>90%, as a minimal ballpark) and the disks are fragmented, it can take some time to search where writes can even occur when MySQL is trying to write to disk.

This would be true of any database platform or any application that writes to disk. This isn't so much a MySQL behavior as it is a storage behavior.

My suspicion is that you have different qualities of storage disks available to you filesystem(s) that contain the MySQL data. As your server became more full, you would be more frequently subject to the performance of your worst disks in terms of IO.

After purging your server of old binlogs you've likely been able to increase your storage-tier's performance with IO because it takes a shorter amount of time to find places to write. Shorter time to find places to write means faster transaction performance. Faster transaction performance means less timeouts and noticeable delays in application functionality.


ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user' I am trying to grant privileges for a user to a new database

mysql> grant all on db_test.* to 'user_test'@'localhost' identified by 'usersexistingpassword';

I get the following error

ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

I am using MySQL 5.6

mysql> select @@version;
+-------------+
| @@version   |
+-------------+
| 5.6.24-72.2 |
+-------------+
1 row in set (0.00 sec)

I did find an article on MySQL on updating Native Passwords on 5.6.x and later

  6.3.9.3 Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin
  https://dev.mysql.com/doc/refman/5.7/en/account-upgrades.html

I ran the command they recommend as root

mysql> UPDATE mysql.user SET plugin = 'mysql_native_password'
    -> WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41);
ERROR 1054 (42S22): Unknown column 'plugin' in 'where clause'

Your problem has to do with mysql.user and the way you upgraded to MySQL 5.6

If you look my answer to Cannot GRANT privileges as root, I show you the description of mysql.user from MySQL 4.1 to MySQL 5.6.

The column plugin is column #41 in mysql.user in MySQL 5.5/5.6

mysql> SELECT column_name,ordinal_position FROM information_schema.columns
    -> WHERE table_schema='mysql' and table_name='user' and column_name='plugin';
+-------------+------------------+
| column_name | ordinal_position |
+-------------+------------------+
| plugin      |               41 |
+-------------+------------------+
1 row in set (0.04 sec)

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.6.24    |
+-----------+
1 row in set (0.02 sec)

mysql>

That column does not appear in MySQL 5.1, 5.0 or 4.x. What this tells me is that somehow MySQL was upgraded to 5.6 but still has the mysql.user from 5.1 or older.

If you run SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; and you get 39, I have just the fix for you.

See my post MySQL service stops after trying to grant privileges to a user on how manually fix mysql.user from 5.1 straight to to 5.6.


How to minimize\plan ahead the effect of running sp_delete_backuphistory? Measure the gains too! while running the following query:

-- DATEADD (datepart , number , date )

DECLARE @dt DATETIME
SELECT @dt = DATEADD(month,-6,getdate())
select @dt
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @dt

I get lots of locks and blocks, possibly because this command has not been run for a while, if ever.

Is there a way to find out how much is there to delete in each of the involved tables, before I actually run or plan to run this command?

I use dateadd to calculate 6 months.

Within sp_delete_backuphistory the following tables are trimmed:

  sp_delete_backuphistory must be run from the msdb database and affects
  the following tables:

  backupfile

  backupfilegroup

  backupmediafamily

  backupmediaset

  backupset

  restorefile

  restorefilegroup

  restorehistory

My gripes with this proc go back a long way:

The Annals of Hilariously Bad Code, Part 1: Critique the Code
The Annals of Hilariously Bad Code, Part 2

The problem you run into when deleting large amounts of data is the crappy estimate you get from the table variables.

I've had pretty good luck creating a new version of the proc using temp tables. You could also try just adding recompile hints, but hey, this way we get useful indexes.

As a side note: if you still run into this blocking because this is running long, you can try either removing the transaction code, or changing it to encapsulate each individual delete (though at that point the benefits are negligible).

CREATE   PROCEDURE [dbo].[sp_delete_backuphistory_pro]
   @oldest_date datetime
 AS
 BEGIN
   SET NOCOUNT ON

   CREATE TABLE #backup_set_id      (backup_set_id INT PRIMARY KEY CLUSTERED)
   CREATE TABLE #media_set_id       (media_set_id INT PRIMARY KEY CLUSTERED)
   CREATE TABLE #restore_history_id (restore_history_id INT PRIMARY KEY CLUSTERED)

   INSERT INTO #backup_set_id WITH (TABLOCKX) (backup_set_id)
   SELECT DISTINCT backup_set_id
   FROM msdb.dbo.backupset
   WHERE backup_finish_date < @oldest_date

   INSERT INTO #media_set_id WITH (TABLOCKX) (media_set_id)
   SELECT DISTINCT media_set_id
   FROM msdb.dbo.backupset
   WHERE backup_finish_date < @oldest_date

   INSERT INTO #restore_history_id WITH (TABLOCKX) (restore_history_id)
   SELECT DISTINCT restore_history_id
   FROM msdb.dbo.restorehistory
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)

   BEGIN TRANSACTION

   DELETE FROM msdb.dbo.backupfile
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.backupfilegroup
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.restorefile
   WHERE restore_history_id IN (SELECT restore_history_id
                                FROM   #restore_history_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.restorefilegroup
   WHERE restore_history_id IN (SELECT restore_history_id
                                FROM   #restore_history_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.restorehistory
   WHERE restore_history_id IN (SELECT restore_history_id
                                FROM   #restore_history_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.backupset
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE msdb.dbo.backupmediafamily
   FROM msdb.dbo.backupmediafamily bmf
   WHERE bmf.media_set_id IN (SELECT media_set_id
                              FROM   #media_set_id)
     AND ((SELECT COUNT(*)
           FROM msdb.dbo.backupset
           WHERE media_set_id = bmf.media_set_id) = 0)
   IF (@@error > 0)
     GOTO Quit

   DELETE msdb.dbo.backupmediaset
   FROM msdb.dbo.backupmediaset bms
   WHERE bms.media_set_id IN (SELECT media_set_id
                              FROM   #media_set_id)
     AND ((SELECT COUNT(*)
           FROM msdb.dbo.backupset
           WHERE media_set_id = bms.media_set_id) = 0)
   IF (@@error > 0)
     GOTO Quit

   COMMIT TRANSACTION
   RETURN

 Quit:
   ROLLBACK TRANSACTION

 END


How do I distribute data from the *source database* to the *destination database* in `SQL`? I have a different source and destination database server with the same name.

On a periodic interval, we push "new" data configuration to the source database and manually update the data in the destination database.

Instead of handling it manually, we are trying to do one time "new" data distribution from source db to the listed linked server destination db's.

I presume that if we use "Linked Server"s it can help to achieve the data distribution by validating missing (or) unavailable records in the destination database servers.

Scenario : New price data distribution from source database to the client databases. We might have new price for new products (or) new price for existing products. Based on the "new" data changes or "new" data in the source db, we will have to distribute the client db.

How do I distribute data from the source database to the destination database in SQL?

Replication is the right solution here, despite the comment above.  Simply copy the published table into a separate staging table at each subscriber.  Then, on a schedule of your choosing, copy the data from the staging table to wherever it needs to go in the subscriber database.

You can use SQL Server Transactional Replication for this, or just copy the tables on a schedule with SSIS or over Linked Server connections.

If you use SSIS, your SSIS package you can use a Foreach loop to load multiple databases, or you can configure a separate SQL Agent job or job step for each target database, which would enable you to schedule them differently, and run just one.  A SQL Agent job step can specify the target connection string.


select string that doesn't contain a substring/field TableA.content is varchar2(4000), which was populated from a CLOB value that contains markup and text for a website, paragraphs actually.
TableB.keyword contains a string that I want to find in TableA.content.

I want to do something like this, but it is not quite right.  What am I missing?

select tableA.content from TableA
where (select distinct(TableB.keyword) from TableB) not in TableA.content;

So I want all the rows in TableA that don't have any of the values in TableB.keyword.

I was checking out MATCH or CONTAINS but I can't get it right using those.

I believe the problem could be summarised like this:

  Select a row from TableA if no row exists in TableB such that TableB.keyword is a substring of TableA.content.

It would probably be fair to say that the previous answer implements roughly the same logic. At the same time, it does so in a less straightforward way, in my opinion. The query below matches the proposed logic more directly and is arguably easier to follow:

SELECT
  content
FROM
  TablA
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      TableB
    WHERE
      TableA.content LIKE '%' || TableB.keyword || '%'
  )
;


How to grant user rights to create stored procedures in a custom schema I'm trying to grant SQL Server login rights to create stored procedures and bind them to a custom schema. In this case I've created a schema called IC. A service account will then be added to the schema with execute rights for the stored procedures.

My question is: how do I grant this SQL Server login the rights to create new stored procedures binded to IC?

I've tried GRANT CREATE ON SCHEMA::IC TO [username]; but only get

  Msg 102, Level 15, State 1, Line 1
   Incorrect syntax near 'CREATE'.

Any suggestions?

You can't change the ability to create procedures to only one schema by only granting permissions on the schema. (Assuming the user has no other rights.)

Why?

The user still needs the right  to create objects in the database, which are in this case procedures.

What you can do, is grant the user CREATE PROCEDURE rights, and then either change the owner of the schema to that user (more secure, see below for more information) or grant that user permissions on the 'IC' SCHEMA.

Only granting CREATE PROCEDURE does not allow the user to create procedures on  schema's such as dbo.

Create the login, the corresponding user and the IC schema

USE [master]
GO
CREATE LOGIN [TestIC] WITH PASSWORD=N'Test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
USE [test]
GO
CREATE USER [TestIC] FOR LOGIN [TestIC];
GO
CREATE SCHEMA IC;

Grant Create procedure to the user

GRANT CREATE PROCEDURE TO [TestIC];

Either Change the owner of the schema (Safer Option)

ALTER AUTHORIZATION ON SCHEMA::IC TO [TestIC];

Or grant the user access to the schema

GRANT
     ALTER

ON SCHEMA::IC

      TO [TestIC] ;

Risk involved with granting rights on the schema

  Granting a user the ability to alter another user's schema gives that
  user the ability to SELECT, INSERT, UPDATE and DELETE rows in any
  table owned by the owner of that schema. This is called "Ownership
  Chaining" and it's what makes Views and Stored Procedures really
  simple ways to control security, as a user who has permissions on a
  View or Stored Procedure does not need to be granted permissions on
  the underlying Table, so long as the View/Proc is has the same owner
  as the Table.

Source

Take a look at the answer by @DavidBrowne-Microsoft for the origin of the quote, and to get more information on "ownership chaining" security risks when granting alter schema rights.

Test

EXECUTE AS LOGIN  = 'TestIC';

CREATE PROCEDURE IC.test
as
select * from sys.databases;

Result:

  Commands completed successfully.

This fails

CREATE PROCEDURE dbo.test
as
select * from sys.databases;

  Msg 2760, Level 16, State 1, Procedure test, Line 1 [Batch Start Line
  35] The specified schema name "dbo" either does not exist or you do
  not have permission to use it.

Undo impersonation

REVERT;


Flat file manager TextQualifier not working I've a CSV text file which contains

col1,col2,col3

"abc,cdef",a,b

When I use the Flat File Source and preview the
a

bc,cdef appears on first column.

But when i run and view the result in data viewer

the first column contains "abc and second column cdef"...

I've a textQualifier set to "

can anyone please help. I'm using SSIS 2008 R2

It seems a bug, I have found: SSIS flat file parser does not read Column delimiters embedded in text data. Work around: using some exotic separator.


Insert sometimes takes more than 2 seconds in table with +6 million records I have a table in my database that acts like a log to record accesses to some selected endpoints of my application.

That table contains:

id (primary key)
The http method (GET, POST, PUT, etc)
The endpoint, some useful data that might be passed
If the request was made by a logged in user, the user_id. Otherwise user_id is NULL (meaning that the request was made by a guest).

This table is starting to get big (already more than 6 million records) and sometimes the insert statements take more than 2 seconds. We don't perform "intensive" operations in this table: just INSERT and SELECT * FROM table WHERE user_id = xxxx.

Is it normal that sometimes this insert is done quickly and sometimes it takes more than 2 seconds? What can be done to reduce this time?

Thanks in advance!

EDIT

Table definition:

CREATE TABLE `user_actions` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `http_method` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
      `endpoint` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `request_data` text COLLATE utf8mb4_unicode_ci NOT NULL,
      `user_id` int(10) unsigned DEFAULT NULL,
      `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `user_actions_user_id_foreign` (`user_id`),
      CONSTRAINT `user_actions_user_id_foreign`
      FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=6312242 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This is an example of one INSERT statement that took 5.3 seconds:

insert into `user_actions`
   (`http_method`, `route_name`, `query_string`, `ip_address`, `user_agent`, `request_data`, `user_id`, `updated_at`, `created_at`)
values
   ('GET', '19-chars-string', '64-chars-string', '123.123.123.123', '139-chars-string', '[]', 96411, '2018-11-29 18:49:08', '2018-11-29 18:49:08');

Server RAM = 16GB
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
innodb_page_cleaners = 12

Well, it takes time (possibly more than 2 seconds) to shovel tens of thousands of rows. Disks, networks, etc, are not fast enough to go faster.

Meanwhile, such a SELECT is interfering, at some level, with anything else that is going on -- such as an INSERT that might be hitting the same user_id.

Also, are you using autocommit = 1, which commits each statement as you go.  Or are you using =0 or BEGIN, in which case groups of statements are bunched together into a "transaction"?  This can impact on how one connection is interfering with another.

When you say that a 1-row INSERT is taking 2 seconds, what tool are you using for the timing?  To an end-user something can 'feel' like 2 seconds, but the real cause may be something else.

(Analysis of VARIABLES and STATUS:)

Observations:

Version: 5.7.24-0ubuntu0.18.04.1-log
16 GB of RAM
Uptime = 4d 00:07:33
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Wow!  This is the first VARIABLES+STATUS I have review for which I saw nothing 'important' to fix.

Details and other observations:

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 451,008 / 786432 = 57.3% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Select_scan / Com_select ) = 226,117 / 2089280 = 10.8% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( back_log / max_connections ) = 80 / 151 = 53.0%

Abnormally large:

innodb_page_cleaners = 12
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210


Splitting large database into smaller ones Our system currently stores all customer (merchant) accounts in one "flat" MySQL (5.6) DB namespace. We would like to scale out better - we are considering breaking our data up based on the merchant account ID. So instead of having:

use database single;
table sales (
  `account_id`
  ...
)

Break up merchants into separate namespaces:

use database <account_id>;
table sales (
  ... // stores data  for a single account
)

Above has several benefits:

Accounts can be placed on different servers and scale out much better. We can decide how many accounts to co-locate, and migrate as needed.
Individual account backup/restore times would be greatly reduced.
Ability to upgrade one account's database at a time (more granularity).
One bad user won't affect all accounts.
etc.

Concern:
Today, all of our DB access is via stored procedures - both reads and mutations. We would like to keep it that way as it isolates the DB schema from the app code. If we were to break up the accounts into separate db-namespaces the dot-notation used inside of the stored procedures cannot be parameterized, as far as we can tell. We would have to duplicate the stored procedures (~150 count) for each of the account-namespaces. Consequently if we need to make a change to one SP, we need to apply it to all of the namespaces' stored procedures. In fact, any DB schema change needs to be applied (via automation) to all the DB namespaces. The administrative overhead is slightly scary, even after automating the deployment of changes.

Any alternative/better solutions for scaling out?

If we were to break up the accounts into separate db-namespaces the dot-notation used inside of the stored procedures cannot be parameterized
We would have to duplicate the stored procedures (~150 count) for each of the account-namespaces.

That should not be an issue: when calling a procedure in database1 it will have that context so you won't have to change the procedures. You don't even need to change the procedures and tables at all: keep the account_id column in and have it always a fixed value for each account/database. That way you can switch back to multiple tenants per DB.
If you always develop and test against databases with multiple tenants this should be safe (if you dev & test against single-tenant DBs you make it easy for cross-tenant data revealing bugs to be missed).
If you decide later that you will never need/want multi-tenant databases, you can at that stage decide whether it is worth the effort and regression testing required to remove account_id from everything.

In fact, any DB schema change needs to be applied (via automation) to all the DB namespaces. The administrative overhead is slightly scary, even after automating the deployment of changes.

There are many tools to help out here, which is best (or if rolling your own is better) depends very much on the application, scale, and deployment frequency, amongst other factors.

Do you agree that it's not possible to have one common namespace (database) hosting all the stored procedures, then be able to invoke it as shared.sp_get_info() in the context of another (per-merchant-account) database?

If mySQL allows cross-DB references like that (I've not used it much recently, I'm an MS SQL Server guy mainly, which would allow such things (though not in Azure SQL)) then that woul be possible and would work, but I wouldn't recommend it.
Your common DB becomes a single point of failure for all the supporting DBs. It tightly couples them, making it more work to scale by simply shifting some databases onto another server/service as you can't just simply move the desired client DBs and update connections settings in the apps (you have to create a new common DB and make sure each has the right families for procedures). You still have to maintain all the different copies of the same procedures. You still have the problem of keeping things appropriately up-to-date but it opens up a new class of possible errors: if due to a build process problem or human error the procedures for a given client DB get updated before the schema in that DB or don't get updated when the DB does, you have problems some of which might be hard to diagnose. Also if you have to restore a client DB from an old backup, you have extra work to make sure that the procedures are at the right version.
It might feel like a simplification, but you are really adding complication. Keeping the same code in procedures in each DB is cleaner. You'll have potential versioning issues but the common DB won't really remove them, in fact it will add more such concerns.


( RDS ) MySQL to MySQL migration within aws RDS MySQL to MySQL migration

I've 2 MySQL(5.7) RDS instance in my AWS account namely prod & dev.
My task here to migrate all objects from Prod instance to Dev in order to make sure all prod objects exist in dev.

Both MySQL instance sits in same aws account.

What would be right approach so that I can further re-research about it.

If your instances are accessible over the net than you can simply dump the prod instance on the dev instance via the host having required utilities and access to both instances:

mysqldump --host 10.20.30.40 \
          -u myaccount1 \
          -pmypassword1 \
          --all-databases \
          --events \
          --routines  \
| mysql --host 20.30.40.50 \
        -u myaccount2 \
        -pmypassword2


Joining two database tables into one I have a database with two tables. Each table has different structure but they do share few common fields.
Simplified example:
Table1 field structure:
 Field1, Field2, Field3, Field4

Table2 field structure:
 Field1, Field4, Field3, Field5, Field6

Of course my database has better named fields and much more of them. As you can see both tables have Field1 (master key), Field3 and Field4.
Now I'm wondering if there is any possibility to somehow merge the data from these two tables into one, preferably without actually copying and duplicating the data (use of database relations or queries).
The third table should contain all common fields and some others (blank cells when there is no data available or perhaps some default data).
I know that in the long run it would be best to modify the database structure so that all common fields are stored in one table and all other fields in other relational tables but at this time I'm not allowed to make any changes to existing tables as my boss is afraid that this could lead to existing systems to crash.
There is a plan to update and finally merge those systems into one (that includes database restructuring) but I do need to do some statistical analysis before then so I'm looking for quickly implementable solution.
Performance isn't of critical importance for this as this would be only a temporary solution but is still desired because I don't want to wait for too long for retrieving the data nor do I want to cause any significant slowdowns to the whole database system.
So any suggestions are welcome.

I would use a view. Just prepare a select in the form of:

SELECT Field1, Field2, Field3, Field4, null as Field5, null as Field6
  FROM Table1

UNION

SELECT Field1, null, Field3, Field4, Field5, Field6
  FROM Table2;

You can refine the select to filter or extend, as you like. When you're satisfied, just define a view as:

CREATE VIEW MergedTable AS
SELECT Field1, Field2, Field3, Field4, null as Field5, null as Field6
  FROM Table1

UNION

SELECT Field1, null, Field3, Field4, Field5, Field6
  FROM Table2;

Then you can always run your selects against the view, nearly as it was a table.

SELECT * FROM MergedTable;


MySQL Installation: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' Tried to install MySQL on Ubuntu, with these 3 commands:
sudo apt update
sudo apt install mysql-server
mysql_secure_installation

When running the last command, it asks for the password for the root account. After I enter it, I got the following error:
Securing the MySQL server deployment.

Enter password for user root:
Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I have checked and apparently there is no mysqld.sock file anywhere.
Is there anything else I should run that I haven't yet?

I know there are a lot of descriptions about this error on the internet, but there seems to be many causes for that, and all of them seems linked to installation.
In my case, I found out my "sudo" access had some issues. After I've corrected them, I've uninstalled everything and installed again, and everything seems to work fine now.


SQL Server Cluster + Availability Groups? I'm actually finding a way to provide a good High Availability and Failover solution while I have a way to manage some sort of horizontal scalability and Load-Balancing...

I actually have a SQLServer Failover Cluster with 2 nodes (Active-Passive) but I wonder if could be possible to add more nodes to this cluster and configure some Database from the actual FailOver Cluster in AlwaysON Availability groups as read-only secondary copies.

And in the top of everything some sort of IP Load Balancer... I don't know if something like this could be possible.

You can have AGs within an FCI but I don't see the point (I mean when you do this you usually do away with the FCIs unless there was some specific requirement).

In an AG the listener acts similar to a load balancer - kind of. If applications support using application intent read only in the connection string it can offload reads to the "passive" server. It's not load balancing though as it only goes to one server. In 2016 those reads are round robin and so it works better.

If your apps don't support application intent and splitting reads and writes (very few do) though then you're largely out of luck. And you're not going to be able to load balance writes.

In your comment to me you mentioned you'd want to keep the FCI in order to balance load. That isn't really going to work that way with an AG and it would be wasteful of resources - which are one of the best parts of having an AG.

Let's say you have two nodes in an AG and ten databases. You can (if queries don't cross the databases, or only groups of them), create ten groups with a different name each. Then to do balancing you can failover just groups within the node to other nodes until you're happy with it. (Note though that you'll need to do scripting to do this; AGs don't have a notion of preferred nodes to go to automatically unlike FCIs).

As the nodes are "as" available as an FCI there's not many reasons to leave the FCI with its shared storage in place. Those resources are kind of wasted.


Deadlock free MERGE-alike in MySQL? I am trying to implement some parts of MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions.

So, whatever we try, deadlocks occur. What we do, we start a transaction, then SELECT ... FOR UPDATE, try an INSERT and if it causes an 23xxx integrity error try an UPDATE instead. Deadlocks. We removed the FOR UPDATE cos we decided that for our use, it's OK. Still deadlocks.

I can't just switch isolation levels because READ COMMITTED needs row logging per SET TRANSACTION:

  As of MySQL 5.1, if you use READ COMMITTED [...] you must use row-based binary logging.

And per READ UNCOMMITTED also needs row logging. And here comes Binary Log Setting:

  To change the global binlog_format value, you must have the SUPER privilege. This is also true for the session value as of MySQL 5.1.29.

I can't require every Drupal setup to have SUPER nor we can say that Drupal is incompatible with statement based binlogs when that's the default and the most widespread.

INSERT ... ON DUPLICATE KEY is neither versatile enough nor is it deadlock free.

So what now?

If possible, write a Stored Procedure to encapsulate the entire task.  And deal with exceptions.  Then have Drupal call the SP.


How to extract values from a JSON field with Mongodb? When I run mongdob query I got this json results in a column.
Column name is "user"

{"email": "mail1@mail.com"}
{"email": "mail6@mail6.com", "name": "name1"}
{"email": "mail2@mail.com"}
{"email": "mail7@mail7.com", "name": "mail2"}
{"email": "mail3@mail.com"}
{"email": "mail5@mail8.com", "name": "name3"}
{"email": "mail4@mail.com"}
{"email": "mail5@mail9.com", "name": "name4"}

However I need to extract email and name from this JSON into different columns like
"email", "name"

How can I do this with Mongodb?

Output

Have you tried with aggregate yet?

Following I used $addField and $project

var c = db.getCollection("json_column")
if (c == null) {
    db.createCollection('json_column');
}
db.getCollection("json_column").remove({});

db.getCollection('json_column').insert({user:{"email": "mail6@mail6.com", "name": "name1"}});
db.getCollection('json_column').insert({user:{"email": "mail2@mail.com"}});
db.getCollection('json_column').insert({user:{"email": "mail7@mail7.com", "name": "mail2"}});
db.getCollection('json_column').insert({user:{"email": "mail3@mail.com"}});
db.getCollection('json_column').insert({user:{"email": "mail5@mail8.com", "name": "name3"}});
db.getCollection('json_column').insert({user:{"email": "mail5@mail9.com", "name": "name4"}});

db.getCollection('json_column').aggregate([
{
    $addFields: {
        "email": "$user.email",
        "name": "$user.name"
    }
},
{
    $project: {
        "user":0
    }
}
]);


SELECT  n.*
    FROM  `reservation_notes` AS n
    INNER JOIN  `reservations` AS r  ON r.`id_hash` = n.`reservation_id_hash`
    INNER JOIN  `contacts` AS c  ON c.`id_hash` = r.`contact_id_hash`
    WHERE  c.`user_id` = 1 */

A guess:  user_id is VARCHAR??  It should be INT (or some other numeric datatype).  And c (Contacts) needs INDEX(user_id).  (Please don't use those awfully long names for indexes; it clutters things unnecessarily.)

That would let the Optimizer start with c and do the filtering (user_id=1) first.

Also, provide INDEX(user_id, id_hash), in this order.  That will make the index "covering".  This means that the processing can be done entirely in the index's BTree, without touching the Data BTree.  This is an extra optimizaiton.

Then r (Reservations) needs INDEX(contact_id_hash, id_hash).  The first column is for reaching into the table.  The extra column is to make it "covering".

Then n (Notes) needs INDEX(reservation_id_hash), which you apparently have.

With these changes, EXPLAIN will list the table in a more optimal order:  c, r, n.  (I prefer to write JOINs in the order that the Optimizer will perform the query.  However, the Optimizer will rearrange the tables as it sees fit.)

When you get millions of rows, you may find that "hashes" slow things down.  But that is another topic.


How to ensure that two or more simultaneous requests to a MySQL table don't interfere Suppose I have about a 1000 simultaneous requests to a table in my DB which consists of these statements (in order):
SELECT

INSERT or UPDATE (could be multiple)

DELETE

and I want to make sure that none of the requests interfere with one another and the table is in an updated state for the next incoming request. For example:
Request #1 comes in.

Request #2 comes in.

Request #1 starts to get processed.

Request #2 should start only when Request #1 is processed so that the data view is consistent and updated for the next request.

I searched for this situation around the web and found out two things that could be of help:

Transactions

Table Locking

But when I read about Transactions, I found out that while a row is being processed by a transaction, other transactions can still come along and modify that row which defeats my purpose.
Also in case of locking, a column can only be locked in one mode i.e. either in READ or WRITE mode, which again fails to keep the data consistent as while reading a row it may get updated and the data which was just read would become an older version of what the table actually is right now.
Is there a solid way to achieve this which can make sure that no request interferes with another and the table is in an updated state for the next incoming request?
I am using MySQL with PHP if this helps.

Transactions are the way to go.

... I found out that while a row is being processed by a transaction, other transactions can still come along and modify that row ...

Cite your source because, frankly, it's just plain wrong.
The whole point of Transactions is that they give you exactly the kind of "atomicity" of change that you're looking for.
Table locking is far too broad a brush to use here.
Locking many rows just update one is not efficient, creates more contention, more waiting and just slows everything down.  To draw a parallel, it would be like trying to deliver a letter to one house by blocking up every letterbox on every other house in the city.


SQL Server 2008 R2 Dormant Users I would like to know if Too many Dormant Users/sessions on a SQL Server instance can force SQL server to not to create new connections or give connection denied error to the application which is trying to connect to server. Or what is the harm of having too many (70-100) dormant sessions/users?

Thanks in advance.

The maximum number of user connections by default is 32,767.  So it doesn't sound like you're reaching the cap.  It is possible to alter that limit though.

But it is worth noting that it is best practice not to leave connections open, not just because the idle state, but it can get messy and mixed up. It is best to open a connection when you require access to the database, and then immediately close it once you are done. Connection pooling takes place, so there would be little to no gain of keeping a connection open and hope other code properly closes it.


How to order by Levenshtein distance? I want to order a table according to a char field using Levenshtein distance or similar because some entries may contain underscores or grammar errors.

It is a table with all the books I plan to read and it contains repeated entries I need list all the entries ordered by the field name, It should take the previous row and find the closest text.

This is a failed attempt I made:

SELECT name,
levenshtein(substring(name from 1 for 200), lag(name) OVER (PARTITION BY name))
FROM books WHERE name <> '';

Thanks

You didn't tell us what the "failed attempt" means. But something like this should work:

with ldist as (
   select name,
          levenshtein(substring(name,1,200), lag(name) OVER (order by name))  as distance
   FROM books
   WHERE name <> ''
)
select *
from ldist
order by distance;

you don't want partition by name because that essentially puts every name into a single group and thus there is no "previous" row to.

As you you want the "previous" row based on the ordering of the name column, you need an order by name in the window definition.


No DAC connection could be established on the default port. Make sure DAC is enabled I could not establish a local DAC connection using SSMS while I was logged into the Sql-Server's Operating System's Remote Desktop. First reason was SQL-Browser Service turned off.
Lateron, second reason I was pointed to was this "DAC is not activated" error:

  "No DAC connection could be established on the default port. Make sure
  DAC is enabled".

I looked out for a solution. The suggested solution I found was to enable the "RemoteDacEnabled" Facet property or run

sp_configure 'remote admin connections', 1

This makes me wonder, why I should enable a REMOTEDacEnabled property in case I wanted to connect from LOCAL SYSTEM? Do I get REMOTE wrong here?

MSDN explains:

"The connection is only allowed from a client running on the server. No network connections are permitted."

If I HAVE TO logon from local server and NOT over the network, what sense does a REMOTE DAC setting ever make?

EDIT/part of the answer:
The main reason for my question was, that I did NOT understand why we have to allow /enable the REMOTEDAC settings just in case we want to establish a LOCAL DAC connection from the server itself (that was what I tried with no luck in the first place).

NOW after giving it more tries I figured out that using...

ServerName.SubDomain.Domain.de

caused this error connecting to DAC while...

ServerName

DOES WORK (without activating the RemoteDac setting).

(Weird about this is that I can connect fine using a "normal" connection with both options)

Root cause was that the FQDN was interpreted to be a remote server and this caused the error above.

However, after I have enabled REMOTEDAC, both options from above work for a  DAC, no matter if I try it local on the server or from remote clients.

From Using a Dedicated Administrator Connection:

  By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.

From that latter page:

  SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. By default, the DAC is only available from a client on the server. To enable client applications on remote computers to use the DAC, use the remote admin connections option of sp_configure.

I think the page you read is just a little light on the details, that's all.

As for why a local DAC connection might not work, it's possible that it's because you're running SQL Server Express, which requires TF 7806 to enable. Another possibility is the network protocols that are enabled.


Script file execution through Power shell We are trying to automate few things, one of them is to deploy patch in one go on selected DBs of SQL Server, as per my knowledge Powershell is an option. But I have zero taste buds of Power shell.

Question: is following activity possible through power shell ?

Get the list of selected databases.
Run a script file which may contain DDL and DML scripts on that list.
And results should be saved in text file.

I found this link but this is for all DBs of server but we need execute script on filtered DBs set.

Assuming your or a user for which you have credentials has applicable permissions, it is very easy to run scripts into SQL Server.  The simplest method is to use Invoke-Sqlcmd.  You can set the -Query parameter to a hard coded value like

Invoke-Sqlcmd -Query "SELECT name FROM sys.databases" -ServerInstance "YourSQLInstance"

Or you can set a variable equal to the value of a .txt or .sql script, using the Get-Content cmdlet.

$sqlQuery= Get-Content -Path "c:\Scripts\MySQLScript.sql"

Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourSQLInstance"

Using this method would allow you to create more complicated scripts.  If you tokenized your USE statement, you could do a simple replacement of the value, after you've captured it.

$sqlQuery.replace("xxxDBNamexxx", "YourDBName")

Putting all this together, you could query your instance and capture the required databases, loop through the databases - for each DB read in the required sql script and replace the DB token with the current DB, execute the script.

Logging is another matter, but not too difficult, depending on the method you use. The easiest, might be to just capture the results of the Invoke-Sqlcmd to another variable and write the variable.

$sqlResults=Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourSQLInstance"

Add-Content -Path "c:\Logs\sqlLog.txt" -Value $sqlResults

These are overly simplistic examples intended to point you in the right direction.  Someone with more experience might use more advanced methods.  It might be worth your while to spend a few hours with the Microsoft Virtual Academy or some other online resource to pick up some basic Power Shell.  It's all I've had time for, but even the basics can get you a long way.


OLE DB Sybase Connection Failing I have a connection to a Sybase database which we stage in a SQL Server 2008R2 database. I can connect the the database and the connection tests out fine. I am using an OLE DB source with a Sybase Driver.

I can even preview data. Note I have shrunk this data set so the data can not be seen but it still demonstrates that the source does return rows.

However when I execute the package it results in failure.

[source 1] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "MyDataSource" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Any ideas as to why this is happening?

UPDATE
Adding other error message in spite of them being the opposite of useful.
[SSIS.Pipeline] Error: component "source" (1) failed validation and returned error code 0xC020801C.
[SSIS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.

UPDATE
After a few attempts the account sybase account gets locked. Must have something to do with the password.

Under Solution Explorer On Righ Side In BIDS
---->RightClick
     ---->Properties
          ---->Configuration Properties
               ---->Debugging
                    ---->Debug Options
                         ---->Run64BitRuntime
                              ---->False


Unable to create partial indexes using bloom I'm new to bloom indexes and currently my table has 3 partial indexes for the same column in the WHERE clause and with different column combinations for the same table. I was trying to replace them all with 1 bloom index.
However, when I create a bloom index with WHERE clause using below syntax, I get below error:
CREATE EXTENSION bloom;

CREATE INDEX idx_bloom_bar ON message USING bloom
(s, p, c, m, e, id)
WHERE (islatest = true)
WITH (length=56, col1=4, col2=4, col3=4, col4=4, col5=4, col6=4);

SQL Error [42601]: ERROR: syntax error at or near "WITH"
Position: 165

However, without WHERE clause, I'm able to create this index successfully.
Please let me know if we can use them as a replacement of multiple partial indexes or not to reduce number of indexes on my table.

The WITH clause comes before the WHERE clause in the CREATE INDEX syntax.  You are not free to rearrange the order as you have attempted here.
This ordering is a little unintuitive to me, but that is how it works.


Is it possible to migrate data from a shard MongoDB to a non-shard MongoDB with dump Question:

I have a shard mongo cluster and I want to migrate data from this cluster to a one node mongo instance. So I want to know is it possible to use dump/restore?

  I use pymongo and Django and python3 and mongo3.4

If the answer is No, So what is the best way to migrate data from a mongo cluster to a single node mongo instance?

I tried to write a script to migrate the data:

# fetch data from old cluster
data = MongoCollection._get_collection().find(query)

# insert data to new single mongo instance
NewMongoCollection.insert(data)

What I did had a lot of issues such as:

Fetch whole data and store data in RAM
If something goes wrong, I have to start it again
...

It's definitely possible to mongodump from a sharded cluster and mongorestore to another deployment (standalone, replica set, or sharded cluster).

With this approach there are some general considerations to be aware of:

You should always mongodump data via mongos for a sharded cluster.
mongodump needs to read all requested data through the memory of the mongod process(es), so this can have a significant resource and performance impact for the deployment being backed up (particularly if the uncompressed data set is much larger than available RAM).
mongorestore will rebuild all indexes for collections being restored, which can have a significant performance impact on the target cluster. If you are using a version of MongoDB server older than 4.2, the default foreground index builds will block other reads & writes to the destination database. MongoDB 4.2 has less impactful index build process (see: Index Builds on Populated Collections).
mongodump does not take a point-in-time backup, so if the source cluster is being actively updated your backup may not be consistent.
If your deployment is using MongoDB 4.2 or newer and distributed transactions, mongodump/mongorestore should not be used (the lack of point-in-time consistency is particularly problematic in this case).

Your original approach of writing a script to migrate the data would also be viable if you iterate rather than trying to store the whole result set in RAM. You could make this approach resumable by scanning a collection in a predictable order (such as sorting by _id, assuming this is monotonically increasing if new documents continue to be inserted).

Unless you have very custom requirements for data export or throttling, I would be inclined to use mongodump and mongorestore  with appropriate options for compression and concurrency (for example parallel collections to dump and number of insertion workers per collection).