how to optimize mysql query/ structure/ approach for large datasets?

I believe that I have the (better) solution to your problem.

(tl; dr) I've created a query which CROSS JOINs newly users with newly inserted content (few records presumably), thereby avoiding an expensive CROSS JOIN between the full user and content table (many records, i.e. expensive!).

I created tables user, content and user_content in the dbfiddle here. See DML and DDL at the end of this answer. I changed some of the names of the tables &c. to reflect my own preferences

singular table names (tables are collections anyway)
all lower case table names with underscores)
a composite PRIMARY KEY for user_content (see here for my thoughts on composite PRIMARY KEYs).

I then ran this query:

SELECT u.user_id AS "User ID", x.content_id AS "Content ID"
FROM user u
CROSS JOIN
(
  SELECT c.content_id FROM content c WHERE c.content_id NOT IN (SELECT content_id FROM user_content)
) AS x
UNION
SELECT y.user_id, c.content_id
FROM content c
CROSS JOIN
(
  SELECT u.user_id FROM user u WHERE u.user_id NOT IN (SELECT user_id FROM user_content)
) AS y
ORDER BY "User ID", "Content ID";

Which gives the same results as your one!

What my query is doing is CROSS JOINing the results of a search for new content (in the first subquery) with a search for new users (second subquery) providing, AIUI, this is the desired result, but without a CROSS JOIN over the full tables. There are differences in the result of EXPLAIN EXTENDED.

my query:

mysql> explain extended SELECT u.user_id AS "User ID", x.content_id AS "Content ID"
    -> FROM user u
    -> CROSS JOIN
    -> (
    ->   SELECT c.content_id FROM content c WHERE c.content_id NOT IN (SELECT content_id FROM user_content)
    -> ) AS x
    -> UNION
    -> SELECT y.user_id, c.content_id
    -> FROM content c
    -> CROSS JOIN
    -> (
    ->   SELECT u.user_id FROM user u WHERE u.user_id NOT IN (SELECT user_id FROM user_content)
    -> ) AS y
    -> ORDER BY "User ID", "Content ID";
+----+--------------------+--------------+----------------+--------------------+--------------------+---------+------+------+----------+--------------------------+
| id | select_type        | table        | type           | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+--------------+----------------+--------------------+--------------------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY            | <derived2>   | system         | NULL               | NULL               | NULL    | NULL |    1 |   100.00 |                          |
|  1 | PRIMARY            | u            | index          | NULL               | PRIMARY            | 4       | NULL |    4 |   100.00 | Using index              |
|  2 | DERIVED            | c            | index          | NULL               | PRIMARY            | 4       | NULL |    4 |   100.00 | Using where; Using index |
|  3 | DEPENDENT SUBQUERY | user_content | index_subquery | uc_content_user_uq | uc_content_user_uq | 4       | func |    4 |   100.00 | Using index              |
|  4 | UNION              | <derived5>   | system         | NULL               | NULL               | NULL    | NULL |    1 |   100.00 |                          |
|  4 | UNION              | c            | index          | NULL               | PRIMARY            | 4       | NULL |    4 |   100.00 | Using index              |
|  5 | DERIVED            | u            | index          | NULL               | PRIMARY            | 4       | NULL |    4 |   100.00 | Using where; Using index |
|  6 | DEPENDENT SUBQUERY | user_content | index_subquery | PRIMARY            | PRIMARY            | 4       | func |    4 |   100.00 | Using index              |
| NULL | UNION RESULT       | <union1,4>   | ALL            | NULL               | NULL               | NULL    | NULL | NULL |     NULL | Using filesort           |
+----+--------------------+--------------+----------------+--------------------+--------------------+---------+------+------+----------+--------------------------+
9 rows in set, 1 warning (0.01 sec)

then show the 1 warning:

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `ptest`.`u`.`user_id` AS `User ID`,'4' AS `Content ID` from `ptest`.`user` `u` union select '4' AS `user_id`,`ptest`.`c`.`content_id` AS `content_id` from `ptest`.`content` `c` order by 'User ID','Content ID' |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Running the query in the warning

select
  `ptest`.`u`.`user_id` AS `User ID`,
  '4' AS `Content ID`
from `ptest`.`user` `u`
union
select
  '4' AS `user_id`,
  `ptest`.`c`.`content_id` AS `content_id`
from `ptest`.`content` `c`
order by 'User ID','Content ID'

gives the correct result. I think that this query is MySQL's optimisation of my query - obviously, the '4' is taken from values in the tables and not hard-coded by me.

Running your orignal query gives 5 warnings:

mysql> explain extended SELECT
    ->   u.user_id as "User ID",
    ->   c.content_id AS "Content ID"
    -> FROM
    ->   user u, content c
    -> WHERE
    ->   u.user_id NOT IN
    ->                (
    ->                  SELECT DISTINCT uc.user_id
    ->                  FROM user_content uc
    ->                  WHERE uc.user_id = u.user_id AND uc.content_id = c.content_id
    ->                )
    ->
    -> AND
    ->   c.content_id NOT IN
    ->               (
    ->                 SELECT DISTINCT uc.content_id
    ->                 FROM user_content uc
    ->                 WHERE uc.user_id = u.user_id
    ->                 AND uc.content_id = c.content_id
    -> )
    -> ORDER BY user_id, content_id;
+----+--------------------+-------+--------+----------------------------+---------+---------+------------------------------------+------+----------+----------------------------------------------+
| id | select_type        | table | type   | possible_keys              | key     | key_len | ref                                | rows | filtered | Extra                                        |
+----+--------------------+-------+--------+----------------------------+---------+---------+------------------------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY            | u     | index  | NULL                       | PRIMARY | 4       | NULL                               |    4 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY            | c     | index  | NULL                       | PRIMARY | 4       | NULL                               |    4 |   100.00 | Using where; Using index; Using join buffer  |
|  3 | DEPENDENT SUBQUERY | uc    | eq_ref | PRIMARY,uc_content_user_uq | PRIMARY | 8       | ptest.u.user_id,ptest.c.content_id |    1 |   100.00 | Using where; Using index; Using temporary    |
|  2 | DEPENDENT SUBQUERY | uc    | eq_ref | PRIMARY,uc_content_user_uq | PRIMARY | 8       | ptest.u.user_id,ptest.c.content_id |    1 |   100.00 | Using where; Using index; Using temporary    |
+----+--------------------+-------+--------+----------------------------+---------+---------+------------------------------------+------+----------+----------------------------------------------+
4 rows in set, 5 warnings (0.00 sec)

mysql>

4 of the warnings are about resolving fields or references, but the final one is a select (which is quite complex!)

select
  `ptest`.`u`.`user_id` AS `User ID`,
  `ptest`.`c`.`content_id` AS `Content ID`
from `ptest`.`user` `u`
join
  `ptest`.`content` `c`
   where
   (
     (
       not(<in_optimizer>
             (`ptest`.`u`.`user_id`,
              <exists>(select distinct 1 from `ptest`.`user_content` `uc`
                 where ((`ptest`.`uc`.`user_id` = `ptest`.`u`.`user_id`)
                 and (`ptest`.`uc`.`content_id` = `ptest`.`c`.`content_id`)
                 and (<cache>(`ptest`.`u`.`user_id`) = `ptest`.`uc`.`user_id`))))))
  and (not(<in_optimizer>(`ptest`.`c`.`content_id`,
              <exists>(select distinct 1 from `ptest`.`user_content` `uc`
                 where ((`ptest`.`uc`.`user_id` = `ptest`.`u`.`user_id`)
                 and (`ptest`.`uc`.`content_id` = `ptest`.`c`.`content_id`)
                 and (<cache>(`ptest`.`c`.`content_id`) = `ptest`.`uc`.`content_id`)))))))
order by `ptest`.`u`.`user_id`,`ptest`.`c`.`content_id`

As you can see, your EXPLAIN EXTENDED query is a good deal more complex than mine. My query provides a CROSS JOIN of new users and content (presumably smaller than the existing table), and I believe will be more performant on large datasets - which I don't have. You'll need to test!

------------------- DDL and DML --------------------------

CREATE TABLE user
(
  user_id INTEGER(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR (50) NOT NULL
);

CREATE TABLE content
(
  content_id INTEGER(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  content_text VARCHAR (1000) NOT NULL  -- can be a BLOB, whatever
);

INSERT INTO user (user_name) VALUES
('Bob'),
('Alice'),
('Eve');

INSERT INTO content (content_text)  VALUES
('Content1'),
('Content2'),
('Content3');

CREATE TABLE user_content
(
  user_id INTEGER NOT NULL,
  content_id INTEGER NOT NULL,
  CONSTRAINT user_content_pk PRIMARY KEY (user_id, content_id),
  CONSTRAINT uc_user_fk FOREIGN KEY (user_id) REFERENCES user (user_id),
  CONSTRAINT uc_content_fk FOREIGN KEY (content_id) REFERENCES content (content_id),
  CONSTRAINT uc_content_user_uq UNIQUE INDEX (content_id, user_id)

-- will be unique anyway because of PK, but you can never give the optimiser
-- too much information. This index will speed up content by user searches.
 );

INSERT INTO user_content
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(2, 2),
(2, 3),
(3, 1),
(3, 2),
(3, 3);

-- now insert a user with no content and content with no user

INSERT INTO user (user_name) VALUES ('Mary');
INSERT INTO content (content_text) VALUES ('Content4');


Agent Job: Execute T-SQL on different server?

You can trigger a SSIS job from another server, no need to move your jobs.

So you can leave your SQL Agent jobs on the database server, deploy the SSIS packages to the new ETL server and then modify your job steps to start the SSIS packages running on the ETL server from your database server. You might need to modify the 'Run As' part depending on how you set up your permissions in between your 2 servers.


No, but here's a few options:

Run a server-side trace. You don't get the Profiler GUI, but the good news is that this kind of tracing is faster. (Heck, I recommend this to folks even when they have Windows on the desktop.) SQL Server Central has a good Stairway to Server-side Tracing. Server-side traces can output to file or to table - I'd caution against writing the trace data into a table on the same server that you're monitoring because that'll have a performance impact.

Run Profiler in a VM. After all, if you're managing SQL Server, you probably need SQL Server Management Studio anyway, and that's still Windows-only. (That's how I manage SQL Server personally - I'm a Microsoft Certified Master of SQL Server, and I've been using Macs since the mid-2000s.)

Run sp_BlitzCache. The open source sp_BlitzCache analyzes the most resource-intensive queries in your plan cache - without starting a heavy-overhead trace or XE session. SQL Server is already gathering this data for you on every supported version/edition. Yes, right now, even as you're reading advice on a web site. You can run it from anything app runs T-SQL, like Microsoft's new cross-platform SQL Operations Studio. (Disclaimer: I'm one of the sp_BlitzCache authors.)

Use Extended Events instead. (This isn't really a solution, but I know someone's going to suggest it, so might as well get it out of the way. The Extended Events people are like vegans, crossfitters, and atheists: they can't wait to tell you about their religion.) Extended Events is the replacement for Profiler, but the thing is, if you're just getting started, you're going to want to use SSMS's excellent wizard for setting up a new XE session - meaning, you still need SSMS. There's also a "Profiler" extension for Azure Data Studio, but...that's actually Extended Events.

Get a third party monitoring tool. If you need to know what's happening on your SQL Server on a regular basis, this is usually more lightweight than running Profiler. Plus, they have way more smarts built in to tell you what's been happening.


Worse query performance even after improving the query by making it to run in parallel mode

One thing stands out clearly for me - this warning in the XML query plan:

<Warnings>
    <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="N'Public'=CONVERT_IMPLICIT(nvarchar(20),[r].[SecurityLevel],0)"/>
</Warnings>

A little known speed killer in SQL Server is passing an nvarchar() parameter to use in an indexed seek against a varchar() indexed value.  What SQL Server does in this case is to convert each value in the indexed field to an equivalent nvarchar() value, and compare them against the parameter.

Why doesn't it convert the nvarchar field to a varchar and use the index? Because all of varchar is a subset of nvarchar, but not all of nvarchar is a subset of varchar. Thus, to do the correct comparison, the varchar values must be converted to nvarchar values.

The effect of this is that the index is not used as an index (and may not be used at all, depending on the fields needed.)  Instead, the index is scanned from beginning to end to identify the matches, with each index entry having the value converted, one by one.  This is very slow.

There are two ways to handle this:

Change the field in the table and index to nvarchar
(Recommended) Change the parameter value to varchar before using it in the lookup.

The change can be handled in a number of ways. Either change the parameter type in the stored procedure being called, change the parameter type being passed by the application (There are articles on how to do this for Entity Framework calls to SQL), or add a CAST/CONVERT call in the code (which may be more trouble than it is worth.)


Redis remove slave from replication along with sentinel

I should read sentinel doc carefully. It's mentioned in sections Adding or removing Sentinels and Removing the old master or unreachable slaves.

These are steps:

Shut down the redis slave to be removed.
Remove slaveof statement from conf

# Generated by CONFIG REWRITE
slaveof 10.128.130.139 6379

Send a SENTINEL RESET mastername command to all  Sentinel instances. One after the other, waiting at least 30 seconds between instances.

Now the redis slave is standalone, and sentinels forget this slave.


Update with distinct on violates unique key in postgres?

As Akina hinted at, without a relational predicate between your subquery and the outer parent query, this is going to try to UPDATE all rows in your schema.current_status table to the same value from your schema.daily table, violating the unique key constraint. This would only work if there was only a single record in your schema.current_status table. What is the relationship between schema.current_status and schema.daily?...as that's what you're currently missing in a predicate clause (such as a WHERE clause).


Implementing a one to zero or one relationship

Scenario 1 could have multiple products linked to the same product_price. The second scenario, where the tables have the same PK (and one is a FK), is forced to being 1 to {0,1}.

Edit: Well now that you've changed Scenario1, my answer doesn't fit any more. Now, your two scenarios are identical, but your first table has two PKs. Scenario2 is fine and achieves what you want. If most products have prices, then you could also have them in a single table, which would look like the results of a Left Join, such that the products without prices just have NULL in those columns.


Microsoft SQL Server Metadata Corruption

Before you try any fixes, make sure you have backups.

  unfortunately the warnings were ignored and they worked a week on the broken SQL database.

Use old backups to locate a version that does not have the corruption. You will need the most current corruption free backup as you work through the issue.

Optimally you will take a fresh full backup, restore it to a test server, and try to work through the problem on the test server.

When you make it worse than it was to begin with, restore the backup to your test server again and try again.

Hopefully you have been taking regular backups (daily at least) if so make sure that old backups do not age out of the inventory, before you get the problem solved.

If the data is highly valuable you might want to consider contacting a corruption consultant.


Best method to synchronize two tables across SQL servers

I suspect you grab the list of rows to move, then an insert occurs, then you delete the rows.  Instead...

Ping-pong a pair of tables.  You are always inserting into one table, always copying from the other.  Then do an atomic RENAME TABLE to switch.

See my high speed ingestion blog for something very similar.  It explains the flip-flop technique.


Multiple match() against() in a MySQL query

First, I think we need to assume that str1 and str2 occur in less than half the rows.

I think you will need to CREATE TEMPORARY TABLEs, one with list of id from one MATCH, one from the other.  Create an index on id.
 Then JOIN the two tables.

It's messy, but FT wants to go first, yet you have two FT clauses in a single query.  Hence my attempt to turn it into two queries.

Since you are using 5.7, and it has the smarts to create an index on a derived table:

SELECT t3.*
    FROM ( SELECT id FROM tbl1 WHERE MATCH ... +str1 ... ) AS t1
    JOIN ( SELECT id FROM tbl1 WHERE MATCH ... -str2 ... ) AS t2  USING(id)
    JOIN tbl1 AS t3  ON  USING(id);

Give it a try. -- I am guessing.


Query to get current column based on date range

Assuming that tVehicle.BranchId contains Branch where the vehicle is currently at and branchId of branch B is 2 this should do the trick
DECLARE @branchId int= 2;
DECLARE @availableDate date= '2020-12-09';

SELECT tv.*
FROM tVehicle tv
WHERE tv.BranchId=@branch
AND NOT EXISTS(SELECT 1 FROM tBooking tb WHERE tb.VehicleId=tv.VehicleId AND @availableDate BETWEEN tb.StartDate AND tb.EndDate);


revoke and apply privileges on current active sessions

Assume that you revoke a privilege when the user is executing some long-time query which needs in this privilege for successfull execution. This will cause unpredictable break and may result into data unconsistence. So privilege altering cannot be applied to existing session. You can reset the user's connection to force him to reconnect, new privileges will be applied in that case. Of course the best way is to do that only when user's connection is idle. – Akina

If you have suitable permissions, you can kill his connection. Local vs remote is not relevant. Do SHOW GRANTS; for yourself.  From the commandline mysql tool: SHOW PROCESSLIST; to see what the id is, then kill <<that id>>; – Rick James


All your numbers are integers, so the whole expression is done using integer division. You need to either provide numeric constant, e.g. using 3600.0 or cast at least one number to numeric
select 33/(17400/3600::numeric);

returns 6.8276, when applying to_char() it's correctly rounded to 7:
select to_char(33/(17400/3600::numeric),'FM99,999,999')

Online example


Modifying table structure within a transaction?

As DDL implicitly commits, the only way to "rollback" your changes is to construct the reverse operation and apply it to revert the change, as a_horse_with_no_name states.

Constructing such a rollback won't always be straightforward however. If data could be written to the table between type modifications (varchar2(10) -> varchar2(50), number -> varchar2) and rolling this back then you'll also have to check the new data will be valid when reverting to the original type (or perform some conversion). Be aware that dropping columns on large tables could take some time and generate large amounts of redo.

You'll also have to be wary of invalidating any stored procedures on your database and other application dependencies as a result of these changes.

The flashback option won't help you in this instance. Once you've made DDL changes to a table, you can't restore it to it's previous state using flashback. Trying to do so will give you the error:

ORA-01466: unable to read data - table definition has changed

Flashing-back your full database would be overkill and also not possible from the Java app - you need to shut down and then mount the database to complete this operation.

Which all raises the question of what your tool is for. If you just need a GUI for people to edit tables, then something like Oracle SQL Data Modeler can do this and generate DDL scripts for you. These can then be validated, tested, an appropriate rollback constructed and applied to the database. Modifying the structure of a (production) database should be done with care and tested to ensure that all changes are valid!


"Display width out of range for column" error when importing mysql 5.1 dump into 5.5

This is likely to be a bug in the version of MySQL 5.1 that you are running, causing the server to provide incorrect metadata to mysqldump when it uses DESCRIBE to learn about the composition of views.

http://bugs.mysql.com/bug.php?id=65379

Background: any time you create a view that references a table, that table must already exist before you can create a view that references it.  For this reason, mysqldump creates all the tables, and then all the views.  That solves the "views depend on tables" issue... but when views reference other views, the referenced view also has to exist before a view that references it can be created.  To work around these semi-circular dependencies without having to actually resolve them, mysqldump generates a dummy placeholder table for each view, with the same columns the view has, first ... and then later drops each dummy table and replaces it with the actual view.

The bug causes mysqldump to generate invalid CREATE TABLE statements for these dummy placeholder tables.

You can edit the existing dumpfile manually by fixing the dummy table definition, or upgrade the old server and rerun the dump, or -- possibly -- use the version of mysqldump that ships with the latest GA version of MySQL, which (I'm working from memory, here) may also contain a workaround that avoids trying to generate bad statements when the server returns nonsense metadata.  The version of mysqldump for 5.6 is backwards-compatible with 5.1 servers, barring bugs or regressions.

If you don't have any views depending on these views, you should be able to simply remove the dummy table definitions from the dump files, which should then load without a problem.

Here's an example from a dumpfile, with some noise removed.

With the other tables, the dummy table is created, after dropping any table or view that already exists on the destination:

--
-- Temporary table structure for view `staff_list`
--

DROP TABLE IF EXISTS `staff_list`;
/*!50001 DROP VIEW IF EXISTS `staff_list`*/;

...

/*!50001 CREATE TABLE `staff_list` (
 `ID` tinyint NOT NULL,

...

) ENGINE=MyISAM */;

Then, much further down in the dump file, the placeholder table is dropped and the real view is created:

--
-- Final view structure for view `staff_list`
--

/*!50001 DROP TABLE IF EXISTS `staff_list`*/;
/*!50001 DROP VIEW IF EXISTS `staff_list`*/;

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`mezzell`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `staff_list` AS select `s`.`staff_id` AS `ID` ...


What happens when you modify (reduce) a column's length?

The Oracle Administrators Guide says the following:

Use the ALTER TABLE...MODIFY statement to modify an existing column
definition. You can modify column data type, default value, column
constraint, column expression (for virtual columns) and column
encryption.
You can increase the length of an existing column, or decrease it, if
all existing data satisfies the new length. You can change a column
from byte semantics to CHAR semantics or vice versa. You must set the
initialization parameter BLANK_TRIMMING=TRUE to decrease the length of
a non-empty CHAR column.
If you are modifying a table to increase the length of a column of
data type CHAR, realize that this can be a time consuming operation
and can require substantial additional storage, especially if the
table contains many rows. This is because the CHAR value in each row
must be blank-padded to satisfy the new column length.

The Oracle SQL Language Reference has much more detail including the following:

You can change the data type of any column if all rows of the column contain nulls. However, if you change the data type of a column in a materialized view container table, then Oracle Database invalidates the corresponding materialized view.
You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the rows contain nulls. You can reduce the size of a data type of a column as long as the change does not require data to be modified.The database scans existing data and returns an error if data exists that exceeds the new length limit.
You can modify a DATE column to TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE. You can modify any TIMESTAMP WITH LOCAL TIME ZONE to a DATE column.
If the table is empty, then you can increase or decrease the leading field or the fractional second value of a datetime or interval column. If the table is not empty, then you can only increase the leading field or fractional second of a datetime or interval column.
For CHAR and VARCHAR2 columns, you can change the length semantics by specifying CHAR (to indicate character semantics for a column that was originally specified in bytes) or BYTE (to indicate byte semantics for a column that was originally specified in characters). To learn the length semantics of existing columns, query the CHAR_USED column of the ALL_, USER_, or DBA_TAB_COLUMNS data dictionary view.

There is additional information and restrictions in the above documentation.  Here is a demonstration of attempting to reduce the precision of a Number column and reduce the length of a Varchar2.  You can try other changes so you will know what will happen.
--Setup.
DROP TABLE FOO;
CREATE TABLE FOO (BAR Number, BAR2 VARCHAR2(300));
INSERT INTO FOO (SELECT Level, RPAD(to_char(Level),10*Level,to_char(Level))
   FROM DUAL CONNECT BY Level <=20);
COMMIT;
SELECT * FROM FOO;

--Reduce Number to Number(10).
ALTER TABLE FOO MODIFY (BAR NUMBER (10));

--Reduce Varchar2(300) to Varchar2(100) (data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100));

--Reduce Varchar2(300) to Varchar2(200) (no data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(200));

The alter statements have the following output:
ALTER TABLE FOO MODIFY (BAR NUMBER (10))
Error report:
SQL Error: ORA-01440: column to be modified must be empty to decrease precision or scale
01440. 00000 -  "column to be modified must be empty to decrease precision or scale"

ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100))
Error report:
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"

table FOO altered.

Reduce precision by creating a new column.
ALTER TABLE FOO ADD (BAR3 NUMBER(10));
UPDATE FOO SET Bar3 = Bar;
ALTER TABLE FOO DROP COLUMN BAR;
ALTER TABLE FOO RENAME COLUMN BAR3 TO BAR;


How to add/update a column with an incremented value and reset said value based on another column in SQL

You can maintain this column easily:

;WITH x AS
(
  SELECT case_id, modify_time, version,
    rn = ROW_NUMBER() OVER (PARTITION BY case_id ORDER BY modify_date)
  FROM dbo.source_table
)
UPDATE x SET version = rn WHERE version <> rn;

But since you could also generate that version column on the fly every time you need to query (maybe it could be in a view), I don't see any benefit of storing the output or even having a version column in the table, since you are potentially going to be updating it more often than you read it (you will have to update entire ranges or even the entire table every time any rows are changed or added).

If you are moving data from one system to another, or one table to another, you still don't need this column to exist in the source table, and you don't need a temporary table to store this column either:

INSERT dbo.target_table(case_id, modify_time, version)
SELECT case_id, modify_time, version = ROW_NUMBER() OVER
  (PARTITION BY case_id ORDER BY modify_date)
FROM dbo.source_table;


I have some code that did this; here's the relevant line:

EXECUTE msdb.dbo.sp_update_schedule @schedule_id         = @target_schedule_id
                                   ,@enabled             = 1
                                   ,@active_start_date   = @new_rundate
                                   ,@active_start_time   = @new_active_start_time

NOTE: In may case, I was updating job schedules a few days in advance, to allow a different job to run the last business day before a holiday. I seem to recall that the job scheduler may cache some data, so it's possible that doing this ten minutes before the job is supposed to run will not have the desired effect.

That said: I always double-checked that the scheduled looked right in the Job Activity Monitor after I ran my script, and they always did.


I see in your script, there is a

BEGIN TRANSACTION

and followed by delete/insert/update. I guess if you have a big volume change, this transaction may take longer depending on your destination db's status, such as whether there is conflicting sessions blocking your current session, or your destination db's log file growth is set to 1024KB. So I'd suggest that you can start Profiler to check which sql statement is started, and also check whether there is any blocking issue.


How to query plan cache for all query's using a table variable?

As far as I can tell, a real table can still be called [@sometable] the same as a table variable, but this is still a useful filter. The definitive XQuery filter seems to be that there is no @Database attribute on a node named Object.
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select qp.query_plan
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_query_plan(p.plan_handle) qp
where qp.query_plan.exist('//Object[empty(@Database) and substring(@Table[1],1,2)="[@"]')


How to "properly" back up a MySQL/MariaDB database?

You should use mysqldump, for example if you want to backup all databases:

# mysqldump --all-databases > dump.sql

You might need to add options for username, password, etc. Read the online documentation on mysqldump (MySQL Reference Manual) for other options.

If you just copy the database files while the server is running, it is very likely that you will be unable to restore databases using those files.


How to disable HTML output in mysql console?

You told it to generate html.

$ mysql -H my_base

The -H option is an alias for the --html option.

I assume you were thinking of -h ... which is the shortcut for --host.

http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_html


How to represent the array of String in Entity Realtionship Diagram?

In a logical datamodel the array should be a separate table. It will have a foreign key referencing the table it is currently in.

In a physical model, from which DDL will be generated, showing it as an array is the right thing to do as this it the desired type for the column.

Neither is the "correct" representation. Each has a different purpose and is right for its own use.

Some modelling tools allow physical and logical representations to coexists in a single model and hold the mappings between them, some do not. If yours doesn't decide at what level you want to model and be consistent.


Unable to execute the Teradata stored procedure due to syntax errors

Teradata doesn't support CTE in stored procedures (as of 15.00), see SQL Data Manipulation Language > The SELECT Statement > WITH and WITH RECURSIVE Statement Modifiers. Surprisingly, in some cases WITH works, but in more complex cases you get a syntax error like that.


Schema based replication in Oracle using Data Guard

If you want to do replication from one database to another, but skip certain schemas, then a Data Guard logical standby database may be a good fit for you.

However, if you want to perform replication from within the same database at the schema level, then Oracle GoldenGate "may" be able to do that... however, I've never used GoldenGate, so I can't confirm if that would be a good fit.

This answer assumes you want to do replication from one database to another, but skip certain schemas.

A logical standby database uses logminer and SQL Apply to replay the SQL statements on the standby database.  You can have a very fine grained control over what is replicated and what isn't; however, administering a logical standby is far more complicated than administering a physical standby, therefore, if you aren't familiar with Data Guard, I would become very familiar with administering a physical standby before attempting to administer a logical standby database.  In addition, a logical standby is open in read/write, and you can control what objects can and can't be modified directly by turning off SQL Apply for those objects (or entire schemas), and setting GUARD to standby.

1) Create a Data Guard physical standby

2)  Convert a physical standby to a logical standby

--STANDBY
alter database recover managed standby database cancel;

--PRIMARY
execute dbms_logstdby.build;

--STANDBY
alter database recover to logical standby keep identity;
alter database open;
alter database start logical standby apply immediate;

3)  Skip replication (DDL and DML) for an entire schema (assuming HR is the schema you wish to skip)

--STANDBY
alter database stop logical standby apply;

EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'SCHEMA DDL', schema_name => 'HR', object_name => '%');
EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DML', schema_name => 'HR', object_name => '%');

alter database start logical standby apply immediate;

4)  Allow changes to objects not being maintained through SQL Apply

alter database guard standby;

The 12c docs on Data Guard logical standby databases can be found here:

Creating a logical standby database

Managing a logical standby database


Postgesql non-deterministic order of result with LIMIT and ORDER BY

If there are multiple rows with the lowest value for index, then no, there is no guarantee you will always get the same row. If you only specify ordering for a column that contains duplicates, then the database does not have to provide anything more than any row that meets the criteria.

Same would be true if you were to say ORDER BY index DESC - which row with 5 would you expect? Why?

In order to force determinism / predictability, add a tie-breaker on a column that is unique (or at least unique within the same values of index). With your sample data, you could say:

ORDER BY index, name LIMIT 1;

But you haven’t shared enough about your table structure and constraints to get a more specific suggestion than that.


Find the identity of the client firing a query in SQL Server without using triggers?

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information.

This is given in Books Online here. I changed the query to add columns which would give you the IP address:

SELECT
    c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme,
    s.host_name, s.program_name, s.client_interface_name,
    c.local_net_address, c.client_net_address, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time, s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = SPID;  --session ID you want to track


SQL Server 2012 hangs at SqlRsConfigAction_install_ConfigNonRC_Cpu64

Solved this problem after uninstalling Microsoft Visual C++ 2017 Redistributable (x86) and (x64) and reinstall successfully sql express 2016.

But the best solution for avoiding this issue is install sql server management studio and sql server express before installing visual studio


Simple Linux MySQL GUI Client That Reads UTF-8?

This was asked (and answered) on StackExchange, but I do think it's a very valid question for our forum.

The correct answer is that you have to issue the following command:

SET NAMES 'charset_name' COLLATE 'collation_name'

which for your case (from here) is

SET NAMES 'hebrew' COLLATE 'hebrew_general_ci'

If for whatever reason this doesn't work for you, you could install a Hebrew version of Linux on some machine you have lying around?


Promote a slave to master; fall back to former master if new master has issues?

The reason you have to rebuild the old master from scratch is because as soon as you make one single change to the database on the new master (former slave), then the database on the old master is (by definition) stale, out of date, inconsistent, and obsolete.

Promoting a slave to master, as you likely realize already, is not technically a MySQL concept, since every server has the potential to be both, simultaneously.  Promotion of a slave to master is really nothing more than "choosing which server the application will be permitted to write to."

If your intention is to make the old master into a new slave and preserve data integrity, you would configure the two machines to be each other's slaves before you promote the current slave to master... this is master/master a.k.a. circular replication.  If no data is being directly manipulated on the original slave, then any binlog events the slave may send back to the master would be those that originated on the original master, which would be stamped with the master's server_id, so the master will skip over and disregard them.

When you switch things around with your application, the old master/new slave will immediately start acting on replication events it sees in the binlog that were generated by writes to the old slave/new master.

You have likely read negative things about master/master in MySQL, but there's nothing inherently problematic about it as long as you understand that there is no provision for handling conflicting writes made simultaneously to both systems.  If your application is only ever connecting to 1 of the machines at a time, then conflicting writes cannot occur.


Moving a Postgres database from standalone local DB to Amazon RDS

@joni correctly points out that pg_dump is what you want. However, I recommend not doing a simple SQL dump. Instead, use a PostgreSQL "custom" format dump that can be fed into pg_restore. This allows parallel dump/restore (faster) if you use directory format, but it's mostly useful because it offers control over the details of the restore without taking a new dump.

Custom-format dump/restore

For example, assuming you've already done a CREATE DATABASE on the destination so there's an empty DB named mydb there:

# dump the local db
pg_dump -Fc -o mydb.dump mydb

# restore to Amazon RDS
pg_restore -h my.amazon.host -U rds_superuser -d mydb --no-owner --no-privileges --no-tablespaces

Roles, tablespaces, foreign servers, GRANTs, etc

Note that roles (users and groups) are not included in the dump. Neither are foreign data wrapper servers or some other global objects. Thus, things that depend on them will fail to restore. In the above I omit restoration of the owner and of privileges for that reason - meaning you'll need to redo your GRANTs and REVOKEs if you have non-default privileges in your database.

Similarly, all objects are restored to the default tablespace, because any tablespaces you may have locally won't be on RDS.

No remote access to rds_superuser?

If you don't allow your rds_superuser to log in remotely, but have done a GRANT rds_superuser TO myusername for some other role, then instead of -U rds_superuser above you can use:

-U myusername --role=rds_superuser

to get pg_restore to authenticate as myusername then SET ROLE rds_superuser;.

Replication

Most production PostgreSQL database migrations are performed using replication and failover. This isn't currently possible with Amazon RDS. RDS does WAL archiving and streaming replication internally, but offers no way to get at the WAL archives or to create a replication entry in pg_hba.conf for replication for clients outside RDS.

For that reason, at time of writing you have to dump and reload to move data into or out of RDS. There's no low-downtime option.


How to do SUM() but with some rules to follow?

I think you can differentiate this SUM's using a SUM(CASE) statement:

select sum(case when vit_codigotiposexames = 21
                then vit_codigotiposexames
                else 0 end) Cadaverico,
       sum(case when vit_codigotiposexames <> 21
                then vit_codigotiposexames
                else 0 end) CorpoDelito
from   tb_vitima;

I've set up an example:

create table tb_vitima (id int identity, vit_codigotiposexames int);
insert into tb_vitima values (2),(5),(10),(11),(21),(21),(10),(23);

select sum(case when vit_codigotiposexames = 21 then vit_codigotiposexames else 0 end) Cadaverico,
       sum(case when vit_codigotiposexames <> 21 then vit_codigotiposexames else 0 end) CorpoDelito
from   tb_vitima;
GO

Cadaverico | CorpoDelito
---------: | ----------:
        42 |          61

dbfiddle here


Informix JDBC login fails, local login succeeds

Ultimately an Informix restart solved the issue.


How to set a mongodb node to return as the primary of a replication set?

Typically, you don't. Thinking of your nodes in terms of primary or secondary is the wrong way to approach it. Since standard data bearing nodes should have the same dimensions, it is better to think of them as replica set members, one of which gets elected to primary. Since the drivers are replica set aware and know which the current primary is and you are supposed to give more than one server in your connection string like

mongodb://jpb01275:37001,jpb01275:37002/?replicaSet=Krishna

there is no advantage in setting a fixed replica set primary.

If you really have to do it, you need to do an

rs.stepDown(90,30)

on the current primary and repeat that until your chosen node becomes primary, within 90 seconds (the first numeric value set). For details, see the documentation of rs.stepDown().


MySQL replication with 5.7 master and 5.5 slave

From the source files, sql/log_event.h, event 34:

ANONYMOUS_GTID_LOG_EVENT= 34,

GTID wasn't introduced until 5.6, so there's no possible way a 5.5 server can understand this event (nor can the the version of mysqlbinlog that shipped with 5.5 understand it -- hence the error; you will probably be able to read this relay log if you use the version of mysqlbinlog that ships with 5.7).

It might seem like you could disable gtid_mode on the master, but there are two problems with this:

Even if that seems to work, it's completely unsupported, and only a matter of time until something else breaks.  The 5.7 master can emit events the pre-5.7 slave can't handle.

  when upgrading to MySQL 5.7, the slaves must be MySQL 5.7 before you can upgrade the master to 5.7. — https://dev.mysql.com/doc/refman/5.7/en/replication-upgrade.html

Any events written to the master's binlog since the upgrade are lost, from the perspective of a pre-5.7 slave, since such a server can't replicate them, so changing gtid_mode now is the equivalent of closing the barn door after the horses have escaped.  This slave's data is no longer useful, unless it is upgrade to understand the master's events and can continue replicating where it stopped.

You may still be able to salvage the slave if you upgrade it to 5.7 but I see no other meaningful alternatives.


It means the number of columns you select in the top query must be the same as the number of columns in the second. If you don't have the same number, you can work around it. Below I can add a NULL to the second query because it's missing a third column.

SELECT col1,col2,col3
FROM t1
UNION ALL
SELECT col1,col2, NULL
FROM t2


As Remus succinctly puts it, there is no secret feature to rollback a transaction without logging. It is also not possible to rollback a committed transaction.

Your code is broken, not SQL Server.

From the scant details the only scenario I can envisage is the now missing data having been inserted as one long running distributed transaction, someone sighting the data via an uncommitted read and the transaction subsequently being rolled back.

Most likely is an error in the logic or analysis of the failure. Human error and cover-up perhaps even more so.


mongodb restore multiple collections with single command

Try these parameter to Include or exclude collections

--nsInclude
--nsExclude

During restoration, you can ignore the unwanted collections.

EG:

Dump all the collections

mongodump --db bhuvi --out /tmp

drwxr-xr-x 2 root root 4096 Dec 20 18:09 ./
drwxrwxrwt 9 root root 4096 Dec 20 18:25 ../
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll1.bson
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll1.metadata.json
-rw-r--r-- 1 root root   83 Dec 20 18:09 coll2.bson
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll2.metadata.json
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll3.bson
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll3.metadata.json

My dump folder has 3 collections.

coll1
coll2
coll3

Restoring coll2 and coll3 collections

I want to restore coll2 and coll3 in the test db.

mongorestore --db test --nsExclude 'test.*1' /tmp/bhuvi/

2017-12-20T18:25:50.299+0000    the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead
2017-12-20T18:25:50.299+0000    building a list of collections to restore from /tmp/bhuvi dir
2017-12-20T18:25:50.299+0000    reading metadata for test.coll3 from /tmp/bhuvi/coll3.metadata.json
2017-12-20T18:25:50.312+0000    restoring test.coll3 from /tmp/bhuvi/coll3.bson
2017-12-20T18:25:50.313+0000    reading metadata for test.coll2 from /tmp/bhuvi/coll2.metadata.json
2017-12-20T18:25:50.326+0000    restoring test.coll2 from /tmp/bhuvi/coll2.bson
2017-12-20T18:25:50.327+0000    no indexes to restore
2017-12-20T18:25:50.327+0000    finished restoring test.coll3 (3 documents)
2017-12-20T18:25:50.327+0000    no indexes to restore
2017-12-20T18:25:50.328+0000    finished restoring test.coll2 (2 documents)
2017-12-20T18:25:50.328+0000    done

Check the collections

> use test
switched to db test
> show collections
coll2
coll3

For restore collections with similar names

mongorestore --db test --nsInclude 'test.coll*' /tmp/bhuvi/

Restore collections with different pattern

There is no straight forward method for this, but we can create a loop to restore this.

create a file with list of collections that we need to restore

vi collections
#add the collections names and save.
coll1
coll2

shell script for restore in loop

#!/bin/bash
input="collcetions"
backup_path="/tmp/mydb"
while IFS= read -r col_name
do
  mongorestore --db sqladmin --collection $col_name $backup_path/$col_name.bson
done < "$input"

root@lin_sql1:/home/ubuntu# ./restore

2017-12-21T04:56:36.596+0000    checking for collection data in /tmp/mydb/coll1.bson
2017-12-21T04:56:36.596+0000    reading metadata for sqladmin.coll1 from /tmp/mydb/coll1.metadata.json
2017-12-21T04:56:36.616+0000    restoring sqladmin.coll1 from /tmp/mydb/coll1.bson
2017-12-21T04:56:36.677+0000    no indexes to restore
2017-12-21T04:56:36.677+0000    finished restoring sqladmin.coll1 (3 documents)
2017-12-21T04:56:36.677+0000    done
2017-12-21T04:56:36.686+0000    checking for collection data in /tmp/mydb/coll2.bson
2017-12-21T04:56:36.686+0000    reading metadata for sqladmin.coll2 from /tmp/mydb/coll2.metadata.json
2017-12-21T04:56:36.699+0000    restoring sqladmin.coll2 from /tmp/mydb/coll2.bson
2017-12-21T04:56:36.760+0000    no indexes to restore
2017-12-21T04:56:36.760+0000    finished restoring sqladmin.coll2 (2 documents)
2017-12-21T04:56:36.760+0000    done

Check the collections

> use sqladmin
switched to db sqladmin
> show collections
coll1
coll2


Is select * still a big no-no on SQL Server 2012?

If you SELECT COUNT(*) FROM TABLE that only returns one row (the count), is relatively light, and is the way to get that datum.

And SELECT * is not a physical no-no, in that it is legal and allowed.

However, the problem with SELECT * is that you can cause a lot more data movement.  You operate on every column in the table.  If your SELECT only includes a few columns, you might be able to get your answer from an index or indexes, which reduces the I/O and also the impact on the server cache.

So, Yes it is recommended against as a general practice because it is wasteful of your resources.

The only real benefit of SELECT * is not typing all the column names.  But from SSMS you can use drag and drop to get the column names in your query and delete those that you do not need.

An analogy: If someone uses SELECT * when they do not need every column, would they also use SELECT without a WHERE (or some other limiting clause) when they do not need every row?


Based on the recent changes to your question, I've completely rewritten my answer.  There may be more efficient ways of doing this, but here's my solution.  Let me know if I have misunderstood your request.

--set up demo data
declare @Table1 table (no int, ticket varchar(5))
insert into @Table1 (no, ticket) values
(1,'T1'),(2,'T2'),(3,'T3'),(4,'T4'),(5,'T5'),(6,'T6'),(7,'T7'),(8,'T8'),(9,'T9'),(10,'T10'),(11,'T11')

declare @Table2 table (ID int, CustomerId varchar(5))
insert into @Table2 (id, CustomerId) values(1,'C1'),(2,'C2'),(3,'C3'),(4,'C4')

--The actual solution
;
--Table2 row count
WITH Table2RowCount
AS (
    SELECT count(*) AS T2RowCount
    FROM @Table2
    )
--Add row number (sequence) to Table1 rows
    ,Table1AndRowNumber
AS (
    SELECT *
        ,row_number() OVER (
            ORDER BY no
            ) AS sequence
    FROM @Table1
    )
--Add row number (sequence) to Table2 rows
    ,Table2AndRowNumber
AS (
    SELECT *
        ,row_number() OVER (
            ORDER BY id
            ) AS sequence
    FROM @Table2
    )
--Add a grouping column for Table1 using the modulus operator
--This grp column will be used to join up against the sequence column of Table2
    ,Table1Grouped
AS (
    SELECT *
        ,CASE
            WHEN t1arn.sequence % (t2rc.T2RowCount) = 0
                THEN (t2rc.T2RowCount)
            ELSE t1arn.sequence % (t2rc.T2RowCount)
            END AS grp
    FROM Table1AndRowNumber t1arn
    CROSS APPLY Table2RowCount t2rc
    )
SELECT t1g.ticket
    ,t2arn.CustomerId
FROM Table1Grouped t1g
JOIN Table2AndRowNumber t2arn ON t2arn.sequence = t1g.grp

| ticket | CustomerId |
|--------|------------|
| T1     | C1         |
| T2     | C2         |
| T3     | C3         |
| T4     | C4         |
| T5     | C1         |
| T6     | C2         |
| T7     | C3         |
| T8     | C4         |
| T9     | C1         |
| T10    | C2         |
| T11    | C3         |


PostgreSQL function not executed when called from inside CTE

That's kind of expected behaviour. CTEs are materialized but there is an exception.

If a CTE is not referenced in the parent query then it is not materialized at all. You can try this for example and it will run fine:

WITH not_executed AS (SELECT 1/0),
     executed AS (SELECT 1)
SELECT * FROM executed ;

Code copied from a comment in Craig Ringer's blog post:
PostgreSQL’s CTEs are optimization fences.

Before trying out this and several similar queries, I thought that the exception was: "when a CTE is not referenced in the parent query or in another CTE and doesn't reference itself another CTE". So, if you wanted the CTE to be executed but the results not shown in the query result, I thought this would be a workaround it (referencing it in another CTE).

But alas, it doesn't work as I expected:

WITH test AS
    (SELECT * FROM __post_users_id_coin(10, 1)),
  execute_test AS
    (TABLE test)
SELECT 1 ;     -- no, it doesn't do the update

and therefore, my "exception rule" is not correct. When a CTE is referenced by another CTE and none of them is referenced by the parent query, the situation is more complicated and I'm not sure exactly what happens and when the CTEs are materialized. I can't find any reference for such cases in the documentation either.

I don't see any better solution than using what you already suggested:

SELECT * FROM __post_users_id_coin(10, 1) ;

or:

WITH test AS
    (SELECT * FROM __post_users_id_coin(10, 1))
SELECT *
FROM test ;

If the function updates multiple rows and you get many rows (with 1) in the result, you could aggregate to get a single row:

SELECT MAX(1) AS result FROM __post_users_id_coin(10, 1) ;

but I'd prefer to have the results of the function that does an update returned, with SELECT * as your example, so whatever calls this query knows if there were updates and what the changes in the table were.


Conditionally add an index to a temp table

This is somewhat subjective but I'm not at all a fan of SELECT ... INTO anyway and normally replace it with an explicit CREATE TABLE and INSERT ... SELECT as the datatypes, column names, and nullability can then be seen much more explicitly (and both can be minimally logged).

In this case if you were to create the temp table with an unnamed primary key constraint on the column up front

CREATE TABLE #temptable
(
PKColumn INT PRIMARY KEY
)

rather than creating the table and adding an index afterwards you would be able to benefit (at least potentially as that does have some issues) from temp table object caching.

I wouldn't bother making it conditional on row count unless you've proved that the presence of the index with few rows somehow makes a significant deterioration in performance (unlikely).


The output is only showing the current query in a transaction.  What else is in the BEGIN...COMMIT?  Or are you running autocommit=0?  Or =1?

SELECT grabs shared locks on the rows it is looking at, for the duration of the transaction.

INSERT grabs exclusive lock(s).  It sounds like the SELECT already has a lock that the INSERT desires.

Perhaps the SELECT involves a table scan?

Note also that it says table metadata lock.  This is something 'bigger' than a "row lock".

If you have an 11-table JOIN that it taking 12 days, I suspect you are missing an index.  Or perhaps missing an ON clause, thereby turning a INNER JOIN into a very costly CROSS JOIN.  Please provide the EXPLAIN, the SELECT and SHOW CREATE TABLE if you would like us to dig deeper into this possibility.


Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

OK, based on feedback from Erwin Brandstetter above, I came up with a VERY nice solution detailed below:

CREATE TABLE device_data (
  collected TIMESTAMPTZ,
  id VARCHAR(100),
  data JSON,
  PRIMARY KEY(id,collected)
);

COPY device_data (collected, id, data) FROM stdin;
2016-01-02 00:02:12+00  switch1.mycompany.com   {}
2016-01-02 00:02:12+00  switch2.mycompany.com   {}
2016-01-02 00:02:12+00  switch3.mycompany.com   {}
2016-01-02 00:02:12+00  switch4.mycompany.com   {}
2016-01-02 00:02:12+00  switch5.mycompany.com   {}
2016-01-02 00:02:12+00  switch6.mycompany.com   {}
2016-01-03 00:02:12+00  switch1.mycompany.com   {}
2016-01-03 00:02:12+00  switch3.mycompany.com   {}
2016-01-03 00:02:12+00  switch4.mycompany.com   {}
2016-01-03 00:02:12+00  switch5.mycompany.com   {}
2016-01-03 00:02:12+00  switch6.mycompany.com   {}
\.

CREATE OR REPLACE FUNCTION get_most_recent()
RETURNS TABLE(id TEXT, collected TIMESTAMPTZ) AS $$
SELECT
    id,
    MAX(collected) AS collected
FROM device_data GROUP BY id
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION get_data()
RETURNS TABLE(id TEXT, data JSON) AS $$
SELECT
  d.id,
  d.data
FROM device_data d
INNER JOIN get_most_recent() r ON r.id=d.id AND r.collected=d.collected
$$ LANGUAGE SQL;

SELECT * FROM get_data();


How to hint Remote Scan operator to estimate more than 10000 rows?

I assume ManyManyColumns is really multiple columns and not one column?...I see your comment states it's 110 actually.
10,000 rows is the default cardinality estimation for a Remote Scan operation in your version of SQL Server, so I don't think you can do much to change that, unfortunately.
How slow is slow currently? Keep in mind even with perfectly accurate cardinality estimates, 3 million rows is always going to be a lot of data to pipe across the network / linked server, especially if you have many columns.
The only general ideas I have at the moment is to either pre-stage the DISTINCT data on your remote server, or use a data synchronization feature like replication to copy it over to your local server instead of using a linked server. If I think of anything else, I'll update my answer accordingly.


Server Local Connection Provider has stopped listening on local pipe due to a failure

Had this for the first time in x years. Happened about the same time as the log backups.

However, I also got the Severity 17 alert email which had an additional bit of information:

  Comment: Error 17 - Insufficient Resources.

Am investigating but it currently looks like this is connected to Shared Memory and IIS logging. The suggestion I found was that IIS on the server (VM) had run out of memory.

Might be worth sending yourself the alert by email (Severity 17) to see if you do get more information - may help you determine the cause. Meanwhile, I'll keep digging into why I got mine...


Unable to create restore plan due to break in LSN chain

Recently I have faced with the same issue. I have googled for a while and found that this is a problem in the Microsoft products. I wrote the article according to this error message, so you can find more information there.

Disclaimer: I am the Marketing Manager for Pranas.NET, maker of the Sql Backup and FTP tool promoted in that article.

So, to solve this issue and restore your database to point-in-time use T-SQL commands:

Restore your last full backup

RESTORE DATABASE your_database FROM DISK = 'd:/full' WITH NORECOVERY, REPLACE

Restore your last differential backup

RESTORE DATABASE your_database FROM DISK = 'd:/diff' WITH NORECOVERY

And restore your transaction log backups, when you will restore the last transaction log backup point the time you need to restore your database

RESTORE LOG your_database FROM DISK = 'd:/log1' WITH NORECOVERY

RESTORE LOG your_database FROM DISK = 'd:/log2' WITH STOPAT = '2016-01-05 13:29:59.000', RECOVERY


How about only SQL statements! no triggers, and no SPs; within a transaction for integrity:

START TRANSACTION;
INSERT INTO notification_log SELECT field1, field2, ... FROM notification_table WHERE user_id=123;
DELETE FROM notification_table WHERE user_id=123;
COMMIT;

Make sure you have an index on user_id.

HTH


Your question is great, but it skips one step. You are assuming that the query planner (or optimiser, depending which terminology you prefer) can keep into account all CHECKs, merging them.
So you have:
check (title in ('BOSS','WORK'))

And:
CHECK ( (title <> 'BOSS' OR salary > 100) )
CHECK ( (title <> 'WORK' OR salary <= 100) )

And you assume that the planner should be able to know that:
CHECK ((title = 'BOSS' and salary > 100) or title = 'WORK' AND salary <= 100)

This could be the case or not.
In general, the question to your answer depends on the technology you are using. MySQL and MariaDB don't take into account CHECKs. Some other technologies probably do. You'll have to test the technologies you are interested in.


You need the USAGE privilege (at least) for the schema as well:

GRANT USAGE ON SCHEMA something TO GROUP data_viewers;

Related Postgres example:

Permission for sequence in another schema

Remember you only granted permissions to already existing tables. Does not apply to tables created later. To cover those, too:

ALTER DEFAULT PRIVILEGES FOR USER role_that_creates_tables
IN SCHEMA public
GRANT SELECT ON TABLES TO GROUP data_viewers;

Amazon Redshift implemented DEFAULT PRIVILEGES as well.

Here is a complete cookbook for Postgres:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

Be aware of some differences between mainline Postgres and Redshift! Redshift sticks to separate users and groups, while Postgres replaced that with the universal concept of roles:

Why did PostgreSQL merge users and groups into roles?

And I am not sure how Redshift handles sequences ...


How can I ensure that only one copy of a procedure is running in Oracle?

You can do this with DBMS_LOCK and an exclusive lock.

See the following procedure:

CREATE OR REPLACE PROCEDURE myproc
IS
  lockhandle VARCHAR2(128);
  retcode NUMBER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('myproclock',lockhandle);

  retcode:=DBMS_LOCK.REQUEST(lockhandle,timeout=>0, lockmode=>DBMS_LOCK.x_mode);

  IF retcode<>0
  THEN
    raise_application_error(-20000,'myproc is already running');
  END IF;

  /* sleep so that we can test with a 2nd execution */
  DBMS_LOCK.sleep(1000);

  retcode:=DBMS_LOCK.RELEASE(lockhandle);

END myproc;
/

Test (session 1):

SQL> BEGIN
  2  myproc();
  3  END;
  4  /

(Obviously returns when DBMS_LOCK.sleep() returns).

Test (session 2):

SQL> BEGIN
  2  myproc();
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-20000: myproc is already running
ORA-06512: at "PHIL.MYPROC", line 12
ORA-06512: at line 2

SQL>

Obviously you need to GRANT EXECUTE ON DBMS_LOCK TO YOURUSER;.


SQL Server process only using half of the memory on host

After a reboot of the server we noticed that the SQL service is only using half of the memory.

SQL server will use memory as an when needed. Once you reboot your instance, it will take sql server some time to ramp up (bringing pages in the buffer pool), since a reboot will trash all the query plans and reset DMV data.

Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory usage has increased beyond the Min Server Memory setting, SQL Server won’t release any memory below that amount.

Let your workload run and sql server will eventually reach your MAX MEMORY setting.

Check : How to determine ideal memory for instance?

  On the instance in Properties > General > Memory it shows 131072 MB

This is the total memory available to the instance i.e. It lists the amount of RAM installed on the server.

  On the instance in Properties > Memory > Max Server Memory it shows 2147483647 MB

The is the MAX memory and you should change it away from the default (use up all memory)

Also, from sql server 2012, Memory management has gone some major changes.

  Setting max server memory becomes more straightforward with SQL Server 2012. The redesigned Memory Manager takes a central role in providing page allocations for the other components, and the max server memory setting governs all memory manager allocations.


How to get a parallel nested loop in postgres 12?

I think that lateral join (other than for function calls) just doesn't support parallel execution.  Not everything that conceivably could support it has actually been implemented yet (and probably never will be).
Also, the DISTINCT feature also doesn't support parallel execution currently.  But changing your example to get rid of that still does not show parallel execution.


Should I disable MongoDB balancer to prevent moveChunk to block database due to "W" lock

To see your "real" chunk distribution count between shards, use command sh.status() at mongos.
As you know, result should show that every shard have more or less same amount of chunks.
Yes, you can disable the balancer and if you really use hashed sharding key, end result should be same as if you keep balancer running. Check that sh.status() carefully, I think that you have there something else sharded too, something what don't use hashed sharding key.
On case when movechunks happen even you have hashed shardingkey is of course situtation where you have added new shard to the cluster. That new shard is empty and system starts to fill it.


How to use password with special chars in postgressql .pgpass file

There's only one quoting rule for .pgpass according to the documentation:

 If an entry needs to contain : or \, escape this character with \.


Optimal time interval for scheduling of Transaction Log Backup

The main question to ask yourself is how much data can you afford to lose. That will determine how often you need to backup the log. But there is nothing wrong with running transaction log backups very often, even every minute. This accomplishes 2 things: 1. Provides you with the security that you won't lose data; 2. Improves performance - the less data you backup at any one time the lower your I/O overhead.
I recently read a blog post stating that, from a performance point of view, log backups taken every minute greatly reduced I/O pressure. The downside of such frequently run backups is restoring them. As Bogdan rightly mentions in the comments, you should write a script to perform the restores. Depending on how far back in time you need to go you may need to restore a large number of transaction log backups. Doing that manually is very time consuming and prone to errors. There are probably third party tools that help with this as well.


What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?

I don't know the PostgreSQL engine, however in the real world, doing

WHERE X > y

will cause the engine to seek to the point in the B-tree index where X = y, then scan the rest of the data to return the relevant rows.  With enough data, it will take significant time to return.

Clearly, WHERE X = y will return more quickly since it only has to return a single row, necessitating only the initial seek operation.

This is, of course, assuming X is indexed.


Microsoft recommends setting it to SIMPLE to limit space utilisation. See here. From an IO perspective, SIMPLE mode will generally require the same IO because it has to retain enough transaction data to facilitate rollback of an active transaction. There is some good information on TLOG utilisation here.


Call python machine learning model from UDF or SP

You have basically three options:

Have a program written in Python, that uses PsycoPG, PyGreSQL or some other driver with equivalent functionality to get the data out of PostgreSQL and process it in Python.  Or you could use some toolkit like SQLAlchemy, which works as an Object Relational Mapper.
Write a User Defined Function using PL/Python, which means you actually write UDFs within PostgreSQL using the Python language. If your model works with a certain function model_predict(a, b, c), you can call it in a fashion similar to:

SELECT a, b, c, model_predict(a, b, c) AS prediction
   FROM table
  WHERE <condition>
Export the data from PostgreSQL (with COPY FROM ..., or using any tool such as pgAdmin), into a suitable format (such as CSV), and then import it and process in Python (you could use Pandas read csv, e.g.).


Entity-Relationship Diagram problem: trying to resolve a tentative many to many relationship

I'm not sure I entirely understand your question as it could be better structured, is this the structure you are looking for?

A CarType can have many cars BUT a car can only have one CarType. 1-to-many

A Mechanic can specialise in many CarTypes AND a CarType can have many Mechanics specialise in it.
many-to-many

A mechanic can then work on multiple cars BUT a car can only be worked on by 1 mechanic.
1-to-many

So when implementing the structure will look something like this:

Hope this is of help, apologies if i haven't answered what you are looking for.


You can SELECT the DISTINCT member ids before joining

CREATE TABLE members (
  `id` INTEGER,
  `full_name` VARCHAR(15),
  `sex` INTEGER
);

INSERT INTO members
  (`id`, `full_name`, `sex`)
VALUES
  ('1', 'John Smith', '1'),
  ('2', 'Carol Danvers', '2'),
  ('3', 'Clark Kent', '1'),
  ('4', 'Lois Lane', '2'),
  ('5', 'Diana Prince', '2');

CREATE TABLE member_email (
  `id` INTEGER,
  `member` INTEGER,
  `email` VARCHAR(21)
);

INSERT INTO member_email
  (`id`, `member`, `email`)
VALUES
  ('1', '1', 'jsmith@mail.com'),
  ('2', '1', 'johnsmith@jsa.org'),
  ('3', '2', 'danvers@marvels.com'),
  ('4', '4', 'llane@dailyplanet.com');

SELECT
COUNT(*) AS total,
SUM(`sex`= 1) AS male
,SUM(`sex`= 2) AS  female
FROM
(SELECT DISTINCT `member` FROM member_email) m_e
INNER JOIN  members m ON m_e.`member` = m.`id`

total | male | female
----: | ---: | -----:
    3 |    1 |      2

db<>fiddle here


ORA-12546: TNS:permission denied, on Windows through Task Scheduler

The problem lies somewhere deep in windows permissions. Those are messing somehow with Oracle TNS system. I still do not know the right way and what exactly those permissions are, but the problem is solved for now, by means of bypassing TNS, as self-suggested previously:

set ORACLE_SID=dbname
exp.exe myuser/pass owner=myuser log=mylog.txt

This works, no error


about giving same column name in every table of database

You can skip table name prefix in the status column, You can have all the tables with same column name and same datatype, that will make your naming uniform through out the tables in the database

I have seen Database with 2000 of tables, and most of them have column status with  same datatype. TINYINT in our case.

Let say a query.

SELECT T1.columnName...., T2.Column,,...
FROM Table1 T1
JOIN Table2 T2 ON .....
WHERE T1.Status = 1 and T2.Status =1

If the status column is only for row data active and inactive, you can choose datatype as bit or TINYINT incase you are looking to add more option in future


Oracle Active Dataguard and User passwords

You can use the same "ugly" mechanism Oracle uses to generate AWR reports for standby hosts. Create a database link that actually points back to primary and execute PL/SQL procedure on primary, through DB link.


Having been a DBA in a past life (and now in charge of hiring them), I have some thoughts:

How do you know you want to be a DBA without any experience admin-ing a database management system? That's like saying "How do I be an auto mechanic but the closest I've come to working on them is driving to and from work each day"... I'd take a long look at why you like database systems and see if it's on the production (SQL) or the support (DBA) end...

You should not be able to get a job as a production DBA without production, on-the-job experience as a DBA, so don't start applying for any DBA positions.

You may be able to be a junior DBA on a team if and only if you show some experience administrating DBMS's combined with lots of knowledge and/or certifications.

Your best bet is to start at a company as a SQL developer -- chances are there is no dedicated DBA and as you work more with it you can grow into a dedicated DBA.

Or, run a production multi-user application that uses a DBMS as the backend. Run your own website that uses MySQL and keep the DBMS running 24/7 and you'll get enough war stories to prove that you know what you're talking about. Hang out on user forms (such as DBForums) and provide support to other users for production DBMS issues.

Focus on backup/recovery, performance tuning, etc. and that'll be enough to at least get you in the door for a junior DBA position.

Good luck!


How to stop logging messages in error log

How do i stop logging unwanted messages in SQL error logs […] likewise is there any other trace flags […]

No, there are no trace flags to turn this logging off.

If you'd like to give some feedback on making a trace flag or have this disabled by default, put in a suggestion on User Voice with this specific example :)


Isn't Kettle (Pentaho Data Integration Community Edition) still available without licensing fees?

Kettle 4.1 was released to the community as a stable release in fourth quarter of 2010. The community is currently working on a minor patch release to 4.1. Kettle continues to be a community supported product. Kettle may be a more appropriate tool in your sandbox or development environments.

However, in a production environment you may find that PDI Enterprise Edition with technical support from Pentaho more appropriate for your mission critical systems.

After discussion in comments below:
 Pentaho Data Integration (PDI - ak "Kettle") is still maintained (4.4 was release 2012-09) and Open Source. It can be downloaded from here: http://sourceforge.net/projects/pentaho/files/Data%20Integration


ORA-12518: TNS:listener could not hand off client connection ORACLE XE 11.2 x64

To understand the problem and try to fix it you need to check related logs:
 listener.log, alert log, trace file, etc

possible causes and solutions:

listener problem:

Make sure there is no error message and listener is up and running by execute the following command:

lsnrclt status

if listener is down check the following it may help you troubleshoot this issue:

could be network error: check the even log for any network error and fix them.
service stopped or it has failure when trying to start:  start--->run--->services.msc the search for oracle listener service  should be like OracleInstancename  try to start it, if it failed to start it will throw an exception inside listener error and windows error number. please provide that error

listener workaround:

add the following parameter to your listener:

DIRECT_HANDOFF_TTC_LISTENER=OFF

database problem:

1.database server memory were enough for new connection

database connection parameters such as: dedicated server, please checked processes parameter or query from v$resource_limit where resource_name=’processes’; checked max_utilization must less than Limit_value or shared server, verify large_pool_size or check dispatcher were enough for all connection.


Disclaimer:
As @JustinCave wrote, you should contact Oracle support.
I give the following information without any warranty.
Source of Information:
I found the following metalink notes that analyze this problem:

ORA-08102: Rebuilding Index does not correct ORA-8102 (Doc ID 96118.1)
ORA-8102 CORRUPT INDEX, ORA-1499 EVEN AFTER DROPPING AND RECREATING INDEX (Doc ID 1081343.6)

Why does Rebuilding an Index not help?
Rebuilding the index may not help. 'REBUILD' uses the data of the existing index. If the data of the index is corrupt you will not get rid of this corruption. So a 'DROP INDEX ...' followed by a 'CREATE INDEX ...' is an option. But the create index may scan another index that contains the fields of the index to create. An if the index that is scanned is corrupt you still may create a corrupt index. Finally you may drop all indexes that my be used for creating your index and recreate them all. But it may also happen that your table is corrupt and then index recreation will generate again corrupt indexes. If this is the case you have to repair the corrupt table before you rebuild the indexes.
What is Corrupt?
To check what problems your table/indexes have you can issue the statement
ANALYZE TABLE <owner>.<table_name> VALIDATE STRUCTURE CASCADE;

It will generate a trace file with the appropriate errors, but maybe only an Oracle support engineer can read this trace output. The analyze statement may lock your table.
How to Repair a Table?
If rebuild or the recreation of one or more indexes does not help, because the table is corrupt then the table mus be rebuilt. This can be done by exporting the table with data pump (expdp) , dropping the table and importing the table with data pump (impdp) (or exp/imp for older Oracle versions). You may loosed data (of the corrupt rows) when doing this export import.


Migration from SQL Server 2008 R2 to SQL Server 2012 SP2

As you noted, you'll be running the DBCC CHECKDB command (note that DBCC just means "Database Console Command", whereas CHECKDB is the command it is executing).

This will be sufficient to ensure that the databases have survived the restore process.

For complete peace of mind, you can issue this command on each database:

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS;

EXTENDED_LOGICAL_CHECKS will do extended checks on "indexed views, XML indexes, and spatial indexes, where present" (per the documentation);
NO_INFOMSGS returns no results if no errors were detected.

Incidentally, updating the compatibility isn't a required step. The databases can stay at the 100 compatibility level until you're ready to take advantage of the newer features of 2012 that require a newer compatibility level.

If you perform your backups with the CHECKSUM option, you can do an additional verification check during the database restore for added peace of mind.


SQL Management Studio Intellisense - Hide global variables

Intellisense, however intelligent it may strive (or claim itself) to be, cannot possibly know that you only want columns in that context. Without that piece of information, therefore, the list will include all items that are valid in the given context.

If you want to make it clear that you are only interested in selecting the table columns, you could qualify the column names with the table name and the syntax will restrict the number of available tokens for you.

Consider this:

Same as in your example.

Now look at this:

You can see that the list only has the column names. To make this easier, I would further suggest you use short aliases for tables, like this:


Copy database removed source - Cannot acces mdf file

I have never tried to use SSMS to copy a live database as you have done.  But I have copied many, many databases without an issue.

Use the following approach in the future and you will not have the same issue again.

Take a full backup (use COPY_ONLY)

Then restore the backup with a new name.

Your live database will never be detached, so you won't have to try and figure out how reattach it


Just as SELECT ... FOR UPDATE is sometimes needed, I think you must implement your action in a transaction:

BEGIN;
SELECT COUNT(*) FROM the_table WHERE user_id = 40987 FOR UPDATE;
if the count >= 5, ROLLBACK and exit.
INSERT INTO the_table (product_id, user_id) VALUES (121, 40987);
COMMIT;

Or...

BEGIN;
INSERT INTO the_table (product_id, user_id) VALUES (121, 40987);
SELECT COUNT(*) FROM the_table WHERE user_id = 40987;
if the count > 5, ROLLBACK and exit.
COMMIT;

(Three diffs:  order of statements; FOR UPDATE; >= vs >.)

In your original code, see if you can add FOR UPDATE to the subquery in the WHERE clause.


Column Data Type for Bitwise Comparison?

I only use flags/bitmask fields in cases where a.) the flatness of the schema matters (data warehouse) and b.) I'm absolutely guaranteed to never pass 64 bits. If this is an OLTP system I'd refactor to use both a many to many mapping table to track the bits and a lookup table with flag names. The m2m table would have 2 fields: FK to the ID of the table currently housing the flags and an FK to a lookup table. The lookup table would be FlagID, FlagName ("Flag" should be replaced with whatever this data actually is "error" maybe). This has the added bonus of greatly simplifying queries and constraints on this data.


pg_basebackup failing from another server

What are the permissions set on the file pg_hba.conf?

Does the postgres user have the ability to read it?

Here is my configuration:

-rw-------. 1 postgres postgres  4334 Apr  4 22:14 ./pg_hba.conf


How to get sum of the count of rows till date from table in MySQL

It can be, for example,
SELECT SUM(dates.a_date >= test.on_date AND (dates.a_date < test.off_date OR test.off_date IS NULL)) `count`,
       dates.a_date `date`
FROM ( SELECT on_date a_date FROM test
       UNION
       SELECT off_date FROM test WHERE off_date IS NOT NULL ) dates
CROSS JOIN test
GROUP BY 2
ORDER BY 2;

fiddle
dates subquery generats dates list when the amount of ON ids might have changed.
SUM tests each row against the conditions for particular date and counts the amount of matched rows (testing result is 1 while matching and 0 otherwise).
If some date is absent then look the row with closest lower date.


Error 8/40 PLS-00364: loop index variable 'I' use is invalid

Answers originally left as comments:

Gerard H. Pille: A stored procedure can't use permissions granted via a role. You need:
GRANT EXECUTE ON RDSADMIN.RDS_FILE_UTIL TO PQP

Albert Godfrind: As an aside, granting DBA to a regular user or application is a very bad idea. It should only ever be granted to an actual DBA account.


Degrading PostgreSQL 8.4 write performance

Assuming it's not a VACUUM problem, it sounds like you've got an unindexed FOREIGN KEY. During the bulk load, do you see an increase in CPU or disk IO?

For giggles, can you please post the output from a VACUUM VERBOSE ANALYZE table_name? An EXPLAIN ANALYZE INSERT ... may also be useful along with as much of the relevant schema.


Q2: If I understand your question correctly, I don't think you want to do that nor should you use Cassandra if that's what you're trying to do. Cassandra is meant to spread data across nodes so that if one node fails then you don't lose your data or crash the entire cluster. If you are putting only specific data one one single hard drive, than you're compromising how Cassandra works.

What's your use case for Cassandra? Why don't you transfer the SSD drive data to a commodity disk then just unplug the SSD and use it for something else? Why do you need Cassandra specifically? Do you have an insane number of writes? What size data are you working with? If you're read heavy you might be better off with a different database.


Mysql Innodb cluster does not show mysql server instances in Mysql shell

The unofficial docker innodb cluster manual does not use MySQL Shell to set up and configure the MySQL InnoDB cluster.

If you want/need to use the MySQL Shell to manage your InnoDB cluster then you must "adopt" the running Group Replication group. To do so, use the adoptFromGr option of dba.createCluster():

dba.createCluster("testcluster", {adoptFromGr: true})

For more information, please consult the official userguide:

https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-from-group-replication.html

Cheers,

Miguel


SQL Server 2016 - High page writes, but normal/low everything else

So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis?

Great question.  Page writes are caused by changing your database.  Full stop.  Changes are written to the log file before a commit, but database pages are changed in memory, but not written on commit.

So first of all, page writes are performed in the background when no user is waiting.  If you concurrently have page reads, this can be an issue, but you seem to have most of the relevant pages cached.  So page writes are not necessarily causing any problems.

That being said, how do you determine what's causing the writes?  It's not super-easy.

There's a DMV that will allow you to query over all the cached pages, and see which ones are dirty.  This DMV can be expensive to run on systems with lots of memory, however.  Something like:

select schema_name(o.schema_id) schema_name, object_name(p.object_id) table_name,  i.type_desc index_type, i.name index_name, bd.page_type, count(*) page_count
from  sys.dm_os_buffer_descriptors bd
join sys.allocation_units au
  on bd.allocation_unit_id = au.allocation_unit_id
join sys.partitions p
  on (p.hobt_id = au.container_id and au.type in (1,3))
   or(p.partition_id = au.container_id and au.type = 2)
join sys.indexes i
  on p.index_id = i.index_id
  and p.object_id = i.object_id
join sys.objects o
  on o.object_id = i.object_id
where database_id = db_id()
and is_modified = 1
group by o.schema_id, p.object_id, i.name, i.type_desc, bd.page_type
order by page_count desc


Not really a solution but a workaround that worked is to restore through a csv file.

COPY potential_users FROM '/potential_users.csv' WITH (FORMAT csv);


Creating a Materialized view with fast refresh and on Demand option

Restrictions on Fast Refresh on Materialized Views with Joins Only

  Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:

  They cannot have GROUP BY clauses or aggregates.

  Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

  Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

Restrictions on Fast Refresh on Materialized Views with Joins Only

Example:

SQL> CREATE MATERIALIZED VIEW LOG ON XXARG_DUMMY with rowid;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON XXARG_DRAGON with rowid;

Materialized view log created.

SQL> create materialized view XXARG_GOKU
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
select
XDU.rowid as XDU_rowid, XDR.rowid as XDR_rowid,
XDR.GLOBAL_ID
XDR.PARTY_ID,
XDR.EMAIL_ADDRESS,
XDU.CITY_CODE,
XDU.STD_CODE,
XDU.PIN_CODE_SERIAL_NO
from XXARG_DUMMY XDU, XXARG_DRAGON XDR
where
XDR.PIN_CODE_SERIAL_NO=XDU.PIN_CODE_SERIAL_NO;


Teaching Slony replication to select slave nodes

I don't see a way to do this directly.  As I understand it, Slony sets replication destinations on the table, not row, level.

How I would solve this specific problem would be to materialize views based on replication destination group, and then use triggers to maintain those.  Slony should then be able to replicate these tables to the slaves.  I don't know if you can have triggers on the slaves that populate the main tables, but if not, there are ways around that. This requires decoupling the initial tables from the replication, and adds some complexity, of course.


SQL Server: grant all 'public' users the permission to create tables

Ensure you are in the correct database and then issue the following command:

GRANT CREATE TABLE TO public
GO

If you already have objects that require public to ALTER the table then you can add the following for each existing table:

GRANT ALTER ON OBJECT::dbo.your_table_name TO public
GO

Reference: GRANT (Transact-SQL)


import text file getting command is not allowed with this MySQL version

From the MySQL Documentation on Security Issues with LOAD DATA LOCAL

If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL version

How ? The server option local_infile must be disabled. Check your my.cnf for load_infile. If you are using a web hosting service, they may or may not allow it. For example, you cannot do that with Amazon RDS for MySQL. You might be able to get away with using MySQL in Amazon EC2. Check with your administrators in that respect.


One "Faster" server slower than the other

You are running in a virtualized environment so just because a server has more virtual RAM or more vCPU's doesn't mean it's "faster" (as you call it).

Several factors are abstracted by the virtualization layer and you would need to know and even benchmark them all.

For example, there is no guarantee the storage underneath the vmdk's is anywhere near comparable, so you would need to benchmark the storage and monitor it on a physical level.

The underlying physical CPU's could make a huge difference too. How fast are the physical processors? Are the CPU's overcommitted in the virtualization layer (i.e. are more vCPU's assigned per physical core) etc.
You could look at the CSTP or RDY values in VMWare among other things.

It really isn't possible to call one server faster than another because it has more virtual resources if you don't know the underlying hardware and virtualization configuration.

Some things you could check are

Storage performance
vCPU/pCPU ratio (overcommitting resources)
The vm's running on the same box (you might not know what your neighbour is doing unless you get access to the VMWare/Hyper-V counters such as esxtop CSTP and RDY values).
NUMA setup on the VM layer
power settings and hyperthreading configuration in the bios of the underlying machines to make sure they perform well when not fully stressed (like they are when running benchmarks)
...

I hope some of these pointers can help you troubleshoot further, but without any hard evidence about the underlying hardware there really isn't any point in comparing the virtual resource configuration to determine which VM should be "faster"

That being said, you may get some mileage looking at perfmon counters to determine disk latency or cpu load, or even use some VMWare counters such as effective speed in MHz or stolen time and you could get some information from wait stats but I still think getting access to some information about the VM configuration and underlying hardware is essential.

If you can, benchmark the underlying hardware.


The user name is an ID/QUOTED ID that should be enclosed in double quotes or square brackets while the password is a regular string where single quotes need to be doubled. The correct syntax would in other words be;

CREATE LOGIN "Ng'ang'a" WITH PASSWORD='Ng''0ng''0'

or

CREATE LOGIN [Ng'ang'a] WITH PASSWORD='Ng''0ng''0'


How can I speed up this query on table with 65 million records

I think your biggest issue is the schema.

you should try to see if you can reduce the row footprint. For example, is nvarchar really needed? Using Varchar will cut the size in half. This may cut your processing time in half too. Is BigInt really needed for HashID? you have only 65M rows, a regular INT is more than enough and again half the size. Each byte you save will gain you 65Mb of data.
you should see if you could use separate fields instead of using text manipulations, that would simplify the query, improves integrity and most probably speed.
primary key on a nvarchar(100) is not a great idea, that should be on your identity column instead. And then make it clustered. I'm sure that will remove the need for the index hint.
instead of making (hash_value, cg_id) a primary key, set it as an unique index
make sure you have a good maintenance plan to reduce index fragmentation


Frequent MySQL table corruptions and instability

High CPU usually means inefficient queries.  Let's see the slow queries, together with SHOW CREATE TABLE and EXPLAIN.

The solution may be as simple as adding an index.  Or perhaps reformulating the query.  A common inefficiency is using a subquery instead of a JOIN.

You can't tune your way out of CPU troubles.  Throwing hardware at it is, at best, a temporary solution.


How to maintain referential integrity of an object graph when a node is deleted

Suggestions:

Disallow deletion of a product that has been ordered.
Move the order detail row to an archive table that has no foreign key to products (perhaps denormalized to store product name etc that would otherwise be lost) then delete the product.
Move the product to an archive table and the order detail to another archive table with a foreign key reference to the product archive table.


Error: ERROR 1047 (08S01) at line 6955: WSREP has not yet prepared node for application use

I had the same issue with gcloud percona instance running on 3 servers.

After running mysql logs from the first instances i found out it was not able to reach to instance-2. I also ssh to instance-2 but i could not reach it. On tailing mysql logs in instance-3 i found out its also not able to reach instance-2.

I restarted instance-2 and everything went back to normal.
With percona mysql clustering when one cluster is down and dirty_write turned off all the write operations are blocked untill all the clusters are restored.


Reclaim space from dropped column in SQLServer2008R2

I did more work on the subject and came up with an interesting insight.
Working with the Express edition database engine, I just dropped the column, re-indexed the primary key (cluster index) to re-arrange the data, which inflated the database file size to more than 10Gb and then simply performed a shrink step (NOTRUNCATE) to compact the database file and reduce it's size within nominal limits.
The database engine didn't complain for the excess size during this process and the end result was a database file that had the space needed reclaimed.

So I guess the answer was simpler to solving the problem at hand.

However I still remain puzzled about CLEANTABLE not doing what the documentation describes it does.


Include UDFs in replication publication on SQL Server 2012

This issue was posted on Connect:

  We can't snapshot any User Defined functions to SQL 2016 version from
  existing SQL 2014 servers. They work fine within 2016 versions and
  from SQL 2016 to SQL 2014\2012.

MS's answer is:

  This issue is fixed in the following cumulative update for SQL Server:

  Cumulative Update 4 for SQL Server 2016.

  It contains one fix: FIX: Snapshot Agent fails when you publish UDF’s
  to SQL Server 2016 Distributor in Transactional Replication
  https://support.microsoft.com/en-us/help/3197883/fix-snapshot-agent-fails-when-you-publish-udf-s-to-sql-server-2016-distributor-in-transactional-replication


You need to setup a AG aware SSB route between x and y. See Service Broker with AlwaysOn Availability Groups. This route will deliver the messages over the network even when x and y are collocated on the same host. When failover occurs and x and y are on different hosts, the delivery continues uninterrupted.

  My personal guess is that SSB Always uses the local services and I have no way out of this.

You should read Service Broker Routing (although is a bit dated, as it misses the AG aware endpoints). SSB uses the local services (the "auto" routes) as a last resort, if no explicit route is declared. You always have a way "out" by explicitly creating a route.,


A message is clean - it cannot create a temp file in "base/pgsql_tmp" directory. From some reason, this directory is missing.


Multiple IPs registered for listener when using AlwaysOn AGs w/ multi-subnet clustering

This document from Microsoft lists a known issue with .net 3.5 erroring out at times.   While not specifically identifying your issue in DNS --It may be the cause and is probably worth reading:

https://msdn.microsoft.com/en-us/library/hh213080.aspx#MultiSubnetFailover


Strange UPDATE results in presence of merge replication

So, it turns out (after hours upon hours of trying to diagnose this) that I did have an issue caused by merge replication being enabled, just not the one I thought I did. For future reference, here's what was happening...

I had a DAO query written in VBA that looked like this:

Set rs = CurrentDb.OpenRecordset("tblExample", dbOpenDynaset, dbSeeChanges)
rs.AddNew

rs.FirstName = "John"
rs.LastName = "Citizen"

rs.Update

rs.Move 0, rs.LastModified
txtPrimaryKey.Value = rs!PrimaryKeyID

rs.Close

The issue was the rs.Move 0, rs.LastModified line, which moves to the most recently updated row (the one just inserted) and then the next line retrieves the new primary key. This had multiple issues:

If two users were inserting records at exactly the same time, this would retrieve the same primary key for both users.
And of course, the triggers that run after an insert to do merge replication also change things (in other tables) and so this again returns a primary key for what the replication was doing, not what we were doing.

Basically, this code works for single-user databases (which is where it originated) but doesn't work for multi-user scenarios.

The solution (for now) is something along the lines of this, manually retrieving the correct row using all the values just inserted:

txtPrimaryKey.Value = DLookup("PrimaryKeyID", "tblExample", "FirstName=""John"" AND LastName=""Citizen""")

This has other potential issues (if you have two rows with exactly the same data, you'll probably get back the wrong primary key ID) but in this case, for this table, that isn't an issue.


Is it possible for a client to kill a long-running Postgres query?

Possible workaround: we created a stored procedure for users that would accept a PID to be killed (eg: sp_kill 4221) and would write the requesting user name and PID into a table.  That table was checked every 5 minutes by a script.  If the PID requested belonged to the user who requested it and it was still active (ie, select pid, usename from pg_stat_activity;) then the superuser would do the kill on the user's behalf.

Note that we only implemented this in development.


How to tell if Postgres was built with the option to include LLVM technology for JIT?

Is LLVM built-in?

You can run pg_config --configure to see with which flags and options your version was compiled.

To quote the documentation:

  Print the options that were given to the configure script when PostgreSQL was configured for building. This can be used to reproduce the identical configuration, or to find out with what options a binary package was built.

Is JIT enabled?

To check if JIT is enabled, examine the configuration variable named jit.

show jit ;

Is JIT available?

Call the system information function pg_jit_available to see if JIT can be used in this session. Returns false if jit is set to false.

SELECT pg_jit_available() ;


Use this function:

CREATE OR REPLACE FUNCTION get_prefix (
   string text,
   max_bytes bigint
) RETURNS text
   LANGUAGE sql STRICT AS
$$SELECT p
FROM (SELECT p.p, octet_length(p.p) AS len
      FROM generate_series(0, length($1)) AS len
         CROSS JOIN LATERAL substr($1, 1, len.len) AS p) AS q
WHERE len <= $2
ORDER BY len DESC
LIMIT 1$$;

It works like this:

SELECT get_prefix('abc€', 4);
 get_prefix
------------
 abc
(1 row)