mongorestore -d demo -c dcoll C:\data\dump\twitter\tweets.bson

As i have gone through your MongoDB syntax query it seems to be correct.
As per your script code demo is database and dcoll is collection and you want to restore the (.bson) file of tweets.bson in your dcoll collection.
I would like to say that here before restore the (.bson) file in MongoDB. You have to make sure that your database name and collection name should be in MongoDB.
If you have don't have Collection in database then first create that Collection in database.
For example first connect with mongo shell and  show the database and collection . Which you are going to restore in MOngoDB, which is exist or not.
To show the database in MongoDB
For example in my environment i have checked and these databases are exist in MongoDB.
> show dbs
admin             0.000GB
blog              0.011GB
citibike          0.338GB
city              0.002GB
enron             0.213GB
local             0.000GB
ships             0.001GB
test              0.164GB
video             0.237GB
week6             0.006GB

Suppose that i want to restore (.bson) file in test database.
> use test
switched to db test
>

Then i shall create the collection data in test database.
> db.createCollection("data")
{ "ok" : 1 }   // ok : 1 means collection successfully created

Also confirm from the query , the data collection is in test database or not.
> show collections
airline
amwaj
cars
collection
data
movies
stuff
>

Here data collection is present then finally restore the (.bson) into the data collection.
Note: Make sure that you are restoring the (.bson) file from the mongo shell where mongorestore.exe is present. Basically this file is available in BIN folder of the MongoDB Server.   In my place it is C:\Program Files\MongoDB\Server\3.6\bin.
so, the finally mongorestore command will be like that
C:\Program Files\MongoDB\Server\3.6\bin>mongorestore -d test -c data C:\data\dump\100YWeatherSmall\data.bson
    2017-12-25T09:55:50.531+0300    checking for collection data in C:\data\dump\100YWeatherSmall\data.bson
    2017-12-25T09:55:50.561+0300    reading metadata for test.data from C:\data\dump\100YWeatherSmall\data.metadata.json
    2017-12-25T09:55:50.565+0300    restoring test.data from C:\data\dump\100YWeatherSmall\data.bson
    2017-12-25T09:55:52.662+0300    [##......................]  test.data  43.9MB/403MB  (10.9%)
    2017-12-25T09:55:55.565+0300    [#####...................]  test.data  87.6MB/403MB  (21.7%)
    2017-12-25T09:55:58.501+0300    [#########...............]  test.data  152MB/403MB  (37.6%)
    2017-12-25T09:56:01.501+0300    [#############...........]  test.data  230MB/403MB  (57.1%)
    2017-12-25T09:56:04.501+0300    [################........]  test.data  282MB/403MB  (70.0%)
    2017-12-25T09:56:07.501+0300    [######################..]  test.data  377MB/403MB  (93.6%)
    2017-12-25T09:56:09.349+0300    [########################]  test.data  403MB/403MB  (100.0%)
    2017-12-25T09:56:09.349+0300    no indexes to restore
    2017-12-25T09:56:09.350+0300    finished restoring test.data (250000 documents)
    2017-12-25T09:56:09.350+0300    done

Here in above code i have used
mongorestore command for restoring the (.bson) file.
-d  // for database
-c // for collection
C:\data\dump\100YWeatherSmall\data.bson  // location of the (.bson)
file.

I hope this will help out to you.
For further your ref mongorestore


innodb_buffer_pool_size more data than pool size

If the buffer_pool is set at 26GB in a 64GB server, what is the rest of RAM being used for?  Normally you should simply set it to 45-50G and not worry.

On the what-if question:

Case 1:  There is high "locality of reference".  A buffer_pool that is much smaller than the total data (24.3G in your case) works fine.  The buffer_pool is a "cache", and this Case is an example where caching is important.

Case 2:  Lots of random access or lots of table scans.  In this Case performance will degrade, possibly seriously, if the buffer_pool is not big enough for the dataset.  The caching fails, and I/O ensues.

UUID primary keys or other indexes can lead to random accesses and performance problems in Case 2.

I see no clues in what you said that could explain "32gb .. caused stability problem".

In most servers, O_DIRECT is good.

innodb_buffer_pool_instances should probably be 16 in your range.


Use the window function lag() in a subquery:

UPDATE eventtransitions e
SET    count = COALESCE(count, 0) + t.count
FROM (
   SELECT prev, event, count(*) AS count
   FROM  (
      SELECT lag(pid)   OVER w AS prev_pid, pid
           , lag(event) OVER w AS prev    , event
      FROM   test4
      WINDOW w AS (ORDER BY time)
      ) t
   WHERE   prev_pid = pid  -- only where pid matches
   GROUP   BY prev, event
   ) t
WHERE  e.prev = t.prev
AND    e.event = t.event;

Seems like you only want to count rows cases where the previous row has the same pid. I eliminated all other.

Aggregate in the next step to get counts.  (That's what you were missing!) Then add the result to the existing count in eventtransitions.

COALESCE to catch possibly existing NULL values in eventtransitions.


Duplicate keys error after switching to MySQL Galera

User error.

Since when does an ip look like 41.205.7.198, 176.9.19.101?

444079-41.205.7.198, 1 implies that 41.205.7.198, 1 was what was used as the ip.  That's the first 15 characters, so I guess you have VARCHAR(15).  (Note: this is why the first comment was asking for SHOW CREATE TABLE.)

So, your funny IP got truncated, leading to a user-caused DUP KEY.


Replace part of strings in multiple columns and tables

First of all, your UPDATE without WHERE clause is needlessly (very!) expensive. It would write a new row version for all rows, even where nothing changes. Only update rows that actually need an update!
UPDATE table01
SET    field01 = replace(field01, 'http://localhost:5000', 'https://example.com')
WHERE  field01 ~ 'http://localhost:5000';

Related:

How do I (or can I) SELECT DISTINCT on multiple columns?

Next, careful what you replace. You'll want to avoid false positives. Test before you apply it to the whole database. Your current expression:
replace(field01, 'http://localhost:5000', 'https://example.com')

would also change strings that probably shouldn't be changed. Like 'http://localhost:50000/pic/img/1.jpg' (note: 50000 instead of 5000)
To avoid that particular case:
regexp_replace(field01, 'http://localhost:5000\M', 'https://example.com', 'g')

Using regular expressions instead. \M at the end of the string, per documentation,

matches only at the end of a word

You may want to do more, depending on your exact requirements. So:
UPDATE table01
SET    field01 =  regexp_replace(field01, 'http://localhost:5000\M'
                                        , 'https://example.com', 'g')
WHERE  field01 ~ 'http://localhost:5000\M';

Then apply to all string columns in a table. Ideally, only update each row once, to make this as cheap as possible. This plpgsql function executes the update for one given table:
CREATE OR REPLACE FUNCTION f_replace_everywhere(_pattern text, _new_string text, _tbl regclass, OUT updated_rows int) AS
$func$
DECLARE
   -- basic string types, possibly extend with citext, domains or custom types:
   _typ  CONSTANT regtype[] := '{text, bpchar, varchar}';
   _sql  text;
BEGIN
   SELECT INTO _sql     -- build command
          format('UPDATE %s SET %s WHERE %s'
               , _tbl
               , string_agg(format($$%1$s = regexp_replace(%1$s, $1, $2, 'g')$$, col), ', ')
               , string_agg(col || ' ~ $1', ' OR '))
   FROM  (
      SELECT quote_ident(attname) AS col
      FROM   pg_attribute
      WHERE  attrelid = _tbl            -- valid, visible, legal table name
      AND    attnum >= 1                -- exclude tableoid & friends
      AND    NOT attisdropped           -- exclude dropped columns
      AND    NOT attnotnull             -- exclude columns defined NOT NULL!
      AND    atttypid = ANY(_typ)       -- only character types
      ORDER  BY attnum
      ) sub;

   -- Test
   -- RAISE NOTICE '%', _sql;

   -- Execute
   IF _sql IS NULL THEN
      updated_rows := 0;                         -- nothing to update
   ELSE
      EXECUTE _sql
      USING _pattern, _new_string;

      GET DIAGNOSTICS updated_rows = ROW_COUNT;  -- Report number of affected rows
   END IF;
END
$func$  LANGUAGE plpgsql;

Call to apply to a single table:
SELECT f_replace_everywhere( 'http://localhost:5000\M'
                           , 'https://example.com'
                           , 'my_table');

Closely related with detailed explanation:

Replace empty strings with null values

Finally, apply to all relevant tables in our database. Don't touch system tables. You can loop through all tables based on pg_tables or information_schema.tables. See:

How to check if a table exists in a given schema

I'll leave that last step to you.


NLS_LANG AL32UTF8/UTF8 for Oracle database

In the original tutorial:

export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`

Notice the backticks. The value of NLS_LANG variable should be the output of the above command, not the file name with absolute path. Just set the value of NLS_LANG, as you already did:

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8


MySQL Rows to Columns to HBase (using Sqoop)

If you are interested in returning all the values as columns, you need to be try something very adventurous. First look at your query
SELECT
    CONCAT(id, '-', date),
    MAX(IF(`num` = 0, avg, NULL)) num0
FROM table
GROUP BY
    id,
    date;

If would be a big mess to get MySQL to execute it as a query by giving MySQL the query with each column formulated.
Perhaps you can get MySQL to concatenate the column values using GROUP_CONCAT. That function was designed for aggregation (or aggravation if you are the actual developer). You can take all the num values can display it as a column-separated listed of numbers like this:
SELECT
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0))) numlist
FROM table
GROUP BY
    id,
    date;

You can also change the list to be delimited by pipes instead of commas like this:
SELECT
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0)) SEPARATOR '|') numlist
FROM table
GROUP BY
    id,
    date;

The default maximum length of a GROUP_CONCAT is 1024.
You need to change that max length in the session using this:
SET group_concat_max_len = 10240;

before you issue your query.
Give it a Try !!!


mysql trigger / stored procedure - update another table row by row

I've made it in a TRIGGER calling a Stored Procedure inside resultsTBL table.

Table lotteryTBL and resultsTBL:

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
|  1 |    1 |    5 |   10 |   15 |   20 |   26 |           0 |
|  2 |    4 |    8 |   12 |   16 |   25 |   40 |           0 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test.resultsTBL;
Empty set (0.00 sec)

mysql>

Inserting on resultsTBL:

mysql> INSERT INTO test.resultsTBL(id, num1, num2, num3, num4, num5, num6)VALUES('1', '1', '5', '4', '8', '25', '40');
Query OK, 1 row affected (0.00 sec)

mysql>

RESULT:

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
|  1 |    1 |    5 |   10 |   15 |   20 |   26 |           2 |
|  2 |    4 |    8 |   12 |   16 |   25 |   40 |           4 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)

mysql>

If you look in the INSERT:

There are 2 values matching for the ID = 1: 1,5.
There are 4 values matching for the ID = 2: 4,8,25,40.

TRIGGER:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.resultsTBL_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
BEGIN
    CALL test.sp_lottery(NEW.id,NEW.num1,NEW.num2,NEW.num3,NEW.num4,NEW.num5,NEW.num6);
END$$
DELIMITER ;

STORED PROCEDURE:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lottery`(
IN eID INT(5),
IN eBLAH01 INT(5),
IN eBLAH02 INT(5),
IN eBLAH03 INT(5),
IN eBLAH04 INT(5),
IN eBLAH05 INT(5),
IN eBLAH06 INT(5))
BEGIN
    # [ lotteryTBL ]
    SET @ID=1;
    # [ ITERATE FOR EVERY ROW IN lotteryTBL ]
    WHILE @ID IS NOT NULL DO
        SET @NUM1=(SELECT num1 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM2=(SELECT num2 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM3=(SELECT num3 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM4=(SELECT num4 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM5=(SELECT num5 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM6=(SELECT num6 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @howmanyknew=0;
        # [ NUM1 ]
        IF (eBLAH01=@NUM1 || eBLAH02=@NUM1 || eBLAH03=@NUM1 || eBLAH04=@NUM1 || eBLAH05=@NUM1 || eBLAH06=@NUM1 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
         # [ NUM2 ]
        IF (eBLAH01=@NUM2 || eBLAH02=@NUM2 || eBLAH03=@NUM2 || eBLAH04=@NUM2 || eBLAH05=@NUM2 || eBLAH06=@NUM2 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM3 ]
        IF (eBLAH01=@NUM3 || eBLAH02=@NUM3 || eBLAH03=@NUM3 || eBLAH04=@NUM3 || eBLAH05=@NUM3 || eBLAH06=@NUM3 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM4 ]
        IF (eBLAH01=@NUM4 || eBLAH02=@NUM4 || eBLAH03=@NUM4 || eBLAH04=@NUM4 || eBLAH05=@NUM4 || eBLAH06=@NUM4 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM5 ]
        IF (eBLAH01=@NUM5 || eBLAH02=@NUM5 || eBLAH03=@NUM5 || eBLAH04=@NUM5 || eBLAH05=@NUM5 || eBLAH06=@NUM5 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM6 ]
        IF (eBLAH01=@NUM6 || eBLAH02=@NUM6 || eBLAH03=@NUM6 || eBLAH04=@NUM6 || eBLAH05=@NUM6 || eBLAH06=@NUM6 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ UPDATE ]
        IF @howmanyknew>0 THEN
            UPDATE test.lotteryTBL SET howmanyknew=@howmanyknew WHERE ID=@ID;
        END IF;
    SET @ID=(SELECT ID FROM test.lotteryTBL AS lt WHERE lt.ID>@ID ORDER BY ID ASC LIMIT 0,1);
    END WHILE;
END


DROP PROCEDURE IF EXISTS not included in mysqldump

I dumped my stored procedures with the following

C:\>mysqldump -u... -p... -n -d --routines --triggers --all-databases > Z:\stuff.sql

Here is one the procedures with the DROP PROCEDURE included:

--
-- Dumping routines for database 'lovesh'
--
/*!50003 DROP PROCEDURE IF EXISTS `LoadMyData` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = cp850 */ ;
/*!50003 SET character_set_results = cp850 */ ;
/*!50003 SET collation_connection  = cp850_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`lwdba`@`127.0.0.1`*/ /*!50003 PROCEDURE `LoadMyData`()
    DETERMINISTIC
BEGIN
    DECLARE NDX INT;
    SET NDX = 0;
    WHILE NDX < 100 DO
        INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
    SET NDX = NDX + 1;
    END WHILE;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

When you used --skip-opt, that is what caused it because --opt includes --create-options and DROP PROCEDURE is considered MySQL-specific.

  -a, --create-options
                      Include all MySQL specific create options.
                      (Defaults to on; use --skip-create-options to disable.)
  --skip-opt          Disable --opt. Disables --add-drop-table, --add-locks,
                      --create-options, --quick, --extended-insert,
                      --lock-tables, --set-charset, and --disable-keys.

--skip-opt would undo --create-options and thus remove DROP PROCEDURE.

Mystery solved !!!

Answer to Bonus Question

Those are not comments; those are MySQL directives. Whenever MySQL runs a Command, it looks for these directives encased in Comments

The number 50003 indicates that this command will execute if and only if the version of MySQL is 5.0.3 or greater.

Here is another example from a mysqldump:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `datas` (
  `ID` int(2) DEFAULT NULL,
  `CATEGORY` int(2) DEFAULT NULL,
  `ORD` int(1) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;

The number 40101 indicates that this command will execute if and only if the version of MySQL is 4.1.1 or greater.

These SQL directives are there for your protection if you load these mysqldumps into earlier versions. These directives allow full acceptance of certain commands. Please do not remove them.

However, if you only work with MySQL 5.0+ and plan to dump your stored procedures separately, you can strip them using Perl or awk. Personally I would leave them be.


no matching unique or primary key for this column-list

you have a combined Primanry key.
So use
Create table Branch(
Bid int primary key,
Loc varchar(20),
Sid int,
Sname varchar(20),
foreign key(Sid,Sname) references spare(Sid,Sname)
);

Or you define in your spare table two new indexes for the column, so you can have different foreign keys


Redesign basic Mysql storage architecture

In my opinion, you should use an SSD if you are facing performance problems. Here is a 120 GB drive for approx. 64 Euro (< 100$ US). This will keep your application ticking over for a couple of years without any need for a redesign and performance should substantially improve for minimum expense.

Down the road, if you are ultimately deciding to move to a DW scenario, may I recommend PostgreSQL as your DW server over MySQL? MySQL is good for read-heavy web-facing apps, but not for DW type work. PostgreSQL has CHECK CONSTRAINTs, SET operators (INTERSECT, EXCEPT), CTEs (Common Table Expressions) and Windowing functions (all of which MySQL (incredibly) lacks).

Traditionally PostgreSQL was not strong on the replication, clustering and connection pooling front, but that has changed.


In sql-server according to syntax, who has to go first: DECLARE or SET NOCOUNT ON?

The declare statement will not generate a rowcount message so there is no observable difference between the two.


Standard data types for email address, telephone number , first name, rixh text editor, etc

E-mail address: I go with NVARCHAR(320) - 64 characters for local part + @ + 255 for domain name. You may also consider normalizing the domain away, since it's pretty wasteful to store hotmail.com 500,000 times when a much smaller INT or even SMALLINT is probably capable (I don't know how many domains you plan to support). I used to be against NVARCHAR but the standard has expanded to support Unicode characters, so even though most mail servers in the world will reject them, better safe than sorry. Next will be to enforce validation - you can read all you want to know about valid formats, and then some, on Wikipedia.
Telephone Number: Probably some varchar in the 25-30 range (here you don't need Unicode support). Unless you know for sure you will only support phone numbers from certain geographical areas, then your choice is much simpler. What is "best" is hard for us to gauge - do you want perfect storage for the formats you support now, or more flexible storage in case you support more later or in case people are sloppy at entering form data and you still want to capture whatever they entered.
First name: Completely subjective. Probably nvarchar(50) but I agree with @kevinsky - ask the business people what they think the longest first name is (or, if you have source data in another system, verify), then double it. Because I promise as soon as someone puts some artificial limit on the length, someone will come along and break the rule.
Role: Again, this is pretty subjective. Is this supposed to be a paragraph of text, or an autobiography? You will need to decide if you want to allow some finite number of characters (say, nvarchar(1000)) or much larger than standard formats (e.g. nvarchar(max), which supports up to 2GB of data per row). You should also take total row size into consideration - it may be easy to pick nvarchar(4000) but you will quickly exceed the row size and force some of that data to be stored inefficiently if people really use it. If a role is tied to a title and not a specific person, this should probably be stored elsewhere anyway.

Make sure your form fields are set to these max lengths, make sure any input is validated and sterilized, and make sure you use parameterized inputs to avoid SQL injection attacks.


Oracle SQL, how to access latest queries and their data

To count the rows deleted just after the delete statement use SQL%ROWCOUNT.  Here is a demonstration.

--Setup.
drop table t1;
create table t1 as (select level x from dual connect by level <=5);

set serveroutput on size 1000000 format wrapped
DECLARE
   vCount Number(3);
BEGIN
   DELETE FROM T1 WHERE rownum<=2;
   vCount := SQL%ROWCOUNT;  --<<---<<---<<---<<---<<---<<---<<---<<---
   DBMS_Output.Put_Line('RowCount:' || vCount);
END;
/

For your broader question you can look into tracing, auditing, flashback database, flashback query, logminer, materialized views, streams, and change data capture (not necessarily in that order).  We would need more details about what data you need and why before directing you any more specifically.


Order by column with specific adjacent value

Basically Json makes the things, a bit more complicated, but not that much, but as you noticed, i don't think that it is necessary.
My approach as you need the data, is to use a pivot table.
This is actually flexible as it uses all, independent which are needed.
At the end is the query without the flexibility of automation. But then you have manually have to select the item you want.
Another approach is to transfor the values data into a json, bu then you need to extract all vital parts again. It wouldn't look that much prettier

CREATE TABLE items (
  `id` INTEGER,
  `name` VARCHAR(11)
);

INSERT INTO items
  (`id`, `name`)
VALUES
  ('1', 'first-item'),
  ('2', 'second-item'),
  ('3', 'third-item');

CREATE TABLE fields (
  `id` INTEGER,
  `name` VARCHAR(11)
);

INSERT INTO fields
  (`id`, `name`)
VALUES
  ('1', 'title'),
  ('2', 'description'),
  ('3', 'available');

CREATE TABLE values1 (
  `id` INTEGER,
  `item_id` INTEGER,
  `field_id` INTEGER,
  `value` JSON
);

INSERT INTO values1
  (`id`, `item_id`, `field_id`, `value`)
VALUES
  ('1', '1', '1', '"First Item"')
  ,
  ('2', '1', '2', '"This is the first item"'),
  ('3', '1', '3', '"1"'),
  ('4', '2', '1', '"Second Item"'),
  ('5', '2', '2', '"This is the second item"'),
  ('6', '2', '3', '"1"'),
  ('7', '3', '1', '"Third Item"'),
  ('8', '3', '2', '"This is the third item"'),
  ('9', '3', '3', '"0"');

    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(CASE v.`field_id` WHEN "',
         f.`id`,
          '" THEN v.`value` ELSE NULL END) AS `',
          f.`name`), '`'

        ORDER BY f.`id`
      )
      INTO @sql
    FROM `items` i
         left join
         `values1` v
         on i.`id` = v.`item_id`
         left join `fields` f on v.`field_id` = f.`id`;

SET @sql = CONCAT('select MAX(i.`name`), ',@sql,' from `items` i
         left join
         `values1` v
         on i.`id` = v.`item_id`
         left join `fields` f on v.`field_id` = f.`id`
group by i.`id`
HAVING `available` + 0 = 1
ORDER BY title DESC');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MAX(i.`name`) | title         | description               | available
:------------ | :------------ | :------------------------ | :--------
second-item   | "Second Item" | "This is the second item" | "1"
first-item    | "First Item"  | "This is the first item"  | "1"

select
MAX(i.`name`)
, MAX(CASE v.`field_id` WHEN "1" THEN v.`value` ELSE NULL END) AS `title`
,MAX(CASE v.`field_id` WHEN "2" THEN v.`value` ELSE NULL END) AS `description`
,MAX(CASE v.`field_id` WHEN "3" THEN v.`value` ELSE NULL END) AS `available`
from `items` i
         LEFT  join
         `values1` v
         on i.`id` = v.`item_id`
         LEFT join `fields` f on v.`field_id` = f.`id`
group by i.`id`
HAVING `available` + 0 = 1
ORDER BY `title` DESC

MAX(i.`name`) | title         | description               | available
:------------ | :------------ | :------------------------ | :--------
second-item   | "Second Item" | "This is the second item" | "1"
first-item    | "First Item"  | "This is the first item"  | "1"

db<>fiddle here


SQL query for finding a set of columns that create a unique key for a table

First, it seems like the wrong way of approaching the problem. I would start by investigation what is supposed to be unique according to the business rules.

Nevertheless, you will have to investigate the power set of column combinations. The cardinality of the power set on n is 2^n, but the empty set can be ignored. So if you have 10 columns this means 2^10-1=1023 combinations to investigate. It's probably easiest to use a host language to generate the sql, but you can use group by cube to get the combinations. Assuming columns c1, c2, c3:

  with t(a,b,c) as ( values ('c1','c2','c3') )
  select a,b,c
  from t group by cube (a,b,c)

A  B  C
-- -- --
-  c2 c3
-  -  c3
-  c2 -
-  -  -
c1 c2 -
c1 -  -
c1 -  c3
c1 c2 c3

Now you can generate the sql by looping over this result set:

select c2,c3 from t group by c2,c3 having count(1) > 1

if no rows exists the column combination is a candidate for a key. Just verify that no sub set of this combination is also a candidate.


Professor told us to store serialized Java objects as blobs instead of defining relational tables

It is not, in itself, a bad thing - at all. Arguing about "which is better" without a proper context (=exact requirements) is an exercise in futility.
The part in bold is wrong. You can easily extend objects already serialized to add new fields and achieve full binary compatibility with the older objects. You can also simply create new classes instead of changing the original ones.

Your discussion with the professor should focus on pros and cons of "relational" versus "key-value store" in different scenarios, not on abstract "betterness". Or you could as well have a discussion on whether Christmas is superior to Thanksgiving.

-- an edit, after reading other answers.

One of the other answers goes as far as to state that that "it's hard to imagine a case where pros outweigh the cons".

Because the whole discussion must be about concrete problems (otherwise we can't even define "better" and "worse"), let me give you one concrete example. It's completely made up, but I tried to flesh out as many details as possible.

Imagine you have an online gaming site, with a database that stores statistics of players in different online games (played in-browser, written in GWT and cross-compiled to javascript). Some of the games are strategic, some are action games, some are platformers. The database is relational and stores players and history of plays and the score.

One day you get an additional requirement: let the players save the game state to the cloud, during the game, so they can restart the game later, at the same point. Needless to say, the only reason to store this temporary state is to return to the game, the state itself will never be introspected.

Now you have two basic choices:

since the games are written in Java, you can quite easily take the model, send it to the server, serialize it in one line of code and store as a blob. The table will be called "saved_games" and it will have foreign keys to the player and so on. From the point of view of the database a "save game" is an opaque, indivisible blob.
you can create a separate relational model for each of your 100 games
(this will be tens of tables per game). For pacman alone, for example, you will have to have a table storing positions of all the uneaten pellets, bonuses, positions and current state of ghosts. If someone, someday, modifies the game, even slightly, you will have to update the relational model. Also, for each type of game, you will have to implement a logic to write the Java model to the database, and to read it back.

The answer by Justin Cave says, that you should go with the second option. I think this  would be a huge mistake.

Also, I have a hunch that Justin Cave's perception is that what I presented above is an "edge" or "rare" case. I believe that unless he can present some sort of hard data (based on a representative sampling of all the IT projects in the world, not just, say, enterprise applications in the US), I will consider such opinion a classic case of a projection bias.

Actually, the problem of serialized Java objects in a relational database is far deeper than it seems. It touches the very core of the 1NF, namely  what is the domain of an attribute?.  If you are really interested in the topic, there's a great article by C. J. Date, in his Date on Database: Writings 2000-2006.


Need to Check if a Table Exists Before Deleting a Record

You should probably use a stored procedure to to do this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`DeleteByID` $$
CREATE PROCEDURE `test`.`DeleteByID` (db VARCHAR(64),tb VARCHAR(64),id_to_delete INT)
BEGIN
    DECLARE FoundCount INT;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.tables
    WHERE table_schema = db
    AND table_name = tb;

    IF FoundCount = 1 THEN
        SET @sql = CONCAT('DELETE FROM ',db,'.',tb,' WHERE id=',id_to_delete);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

END $$

DELIMITER ;

All you do in your code is call the stored procedure. For example, to delete ID 128 from drupaldb.comments:

CALL test.DeleteByID('drupaldb','comments',128);

Give it a Try !!!

Another variation on this would be to sculpt the query directly:

set @given_db = 'drupaldb';
set @given_tb = 'comments';
set @given_id = 128;
set @good_sql = CONCAT('DELETE FROM ',@given_db,'.',@given_tb,' WHERE id=',@given_id);
set @evil_sql = 'SELECT 1';
SELECT IF(table_exists=1,@good_sql,@evil_sql) INTO @DeleteSQL
FROM
(
    SELECT COUNT(1) table_exists
    FROM information_schema.tables
    WHERE table_schema=@given_db
    AND table_name=@given_tb
) A;
PREPARE stmt FROM @DeleteSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;

NOTE: If the table does not exist, the query executed is SELECT 1.

Here is a sample that shows if the table does not exist:

mysql>     set @given_db = 'drupaldb';
Query OK, 0 rows affected (0.00 sec)

mysql>     set @given_tb = 'comments';
Query OK, 0 rows affected (0.00 sec)

mysql>     set @given_id = 128;
Query OK, 0 rows affected (0.00 sec)

mysql>     set @good_sql = CONCAT('DELETE FROM ',@given_db,'.',@given_tb,' WHERE id=',@given_id);
Query OK, 0 rows affected (0.00 sec)

mysql>     set @evil_sql = 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)

mysql>     SELECT IF(table_exists=1,@good_sql,@evil_sql) INTO @DeleteSQL
    ->     FROM
    ->     (
    ->         SELECT COUNT(1) table_exists
    ->         FROM information_schema.tables
    ->         WHERE table_schema=@given_db
    ->         AND table_name=@given_tb
    ->     ) A;
Query OK, 1 row affected (0.00 sec)

mysql>     PREPARE stmt FROM @DeleteSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Now, here is a sample where the table exists:

mysql> select count(1) from mysql.user where user='rolando';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> grant all on *.* to 'rolando'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(1) from mysql.user where user='rolando';
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> set @given_db = 'mysql';
Query OK, 0 rows affected (0.00 sec)

mysql> set @given_tb = 'user';
Query OK, 0 rows affected (0.00 sec)

mysql> set @given_id = 'rolando';
Query OK, 0 rows affected (0.00 sec)

mysql> set @good_sql = CONCAT('DELETE FROM ',@given_db,'.',@given_tb,' WHERE user=''',@given_id,'''');
Query OK, 0 rows affected (0.00 sec)

mysql> set @evil_sql = 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(table_exists=1,@good_sql,@evil_sql) INTO @DeleteSQL
    -> FROM
    -> (
    ->     SELECT COUNT(1) table_exists
    ->     FROM information_schema.tables
    ->     WHERE table_schema=@given_db
    ->     AND table_name=@given_tb
    -> ) A;
Query OK, 1 row affected (0.00 sec)

mysql> PREPARE stmt FROM @DeleteSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.02 sec)
Statement prepared

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select count(1) from mysql.user where user='rolando';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>


How do I solve the following error on attaching ldf-less database in SQL Server?

EDIT; Version 8, really? That is SQL 2000? I fear that is far too out of support.

I did get a call 10 years ago about a corrupt ldf file in SQL 2000 and fixed it with an undocumented & unsupported command.

I don't remember what command I used, but take a look here
https://blog.sqlauthority.com/2007/05/15/sql-server-dbcc-commands-list-documented-and-undocumented/

Possibly one of these will do it, you have to see if anything is left on the google.

  DBCC dbrecover (dbname [, IgnoreErrors])
  DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])

For later versions:

    CREATE DATABASE TestDb ON
    (FILENAME = N'C:\Program Files\....\MSSQL\DATA\TestDb.mdf')
    FOR ATTACH_REBUILD_LOG

or

    EXEC sp_attach_single_file_db @dbname='TestDb',
    @physname=N'C:\....\MSSQL\DATA\TestDb.mdf'

and a third approach

    CREATE DATABASE TestDb ON
    ( FILENAME = N'C:\Program Files\..R\MSSQL\DATA\TestDb.mdf')
    FOR ATTACH

and for future reference, if the is NOT cleanly shutdown
https://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/


I don't use Access, but it is likely that it is unable to query extended properties except those under the dbo schema - it must make the assumption that everything is under dbo. I don't really have a solution for you other than not using schemas with your ADP if extended properties are critical.


Oracle 11: Change global index to local on huge production table

I found solution making creating index quite faster... (on Oracle 11g works fine)

drop index I_EMPLOYEE_SALARY_T;
create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT)
tablespace IDX_TABLESPACE local unusable;

Then I need to rebuild index online choosing partition:

ALTER INDEX I_EMPLOYEE_SALARY_T REBUILD PARTITION partition_name online;

Altering index is much faster because it using different, faster mechanism and data can be accessed much sooner. For example when data for employees (in this example) is partitioned by timestamp. So newer data are more quickly available.


When was the syslogin's password last updated?

SQL Server does not track this information by default, so you will not be able to look back and see when these happened in the past. The trace event is 107 and it is not in the default trace (and I don't see anything in the system_health extended event session either).

For the future, you can set up an audit specification (no idea if this is an option for you because you didn't specify version or edition). You may also be able to capture these events using a server-side trace or an event notification on ALTER LOGIN.

Server-Side Trace

Here is a script that will produce a server-side trace that captures just the Audit Login Change Password Event (and this works for both ALTER LOGIN and sp_password) - please be sure to update the path in the first EXEC line:

DECLARE @TraceID INT, @mfs BIGINT;
SET @mfs = 5;

EXEC sp_trace_create @TraceID OUTPUT, 0, N'C:\path\tracefile', @mfs, NULL;

EXEC sp_trace_setevent @TraceID,107,1, 1;
EXEC sp_trace_setevent @TraceID,107,11,1;
EXEC sp_trace_setevent @TraceID,107,8, 1;
EXEC sp_trace_setevent @TraceID,107,12,1;
EXEC sp_trace_setevent @TraceID,107,14,1;
EXEC sp_trace_setevent @TraceID,107,40,1;
EXEC sp_trace_setevent @TraceID,107,42,1;

EXEC sp_trace_setstatus @TraceID, 1;

Later when you want to review the events the trace captured, you can see the last update time for each login with the following query:

DECLARE @path NVARCHAR(260);

SELECT @path = [path]
FROM    sys.traces
WHERE  id = <traceID from above>;

SELECT TargetLoginName, LastTimeStamp = MAX(StartTime)
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 107 -- in case you've added other events
  GROUP BY TargetLoginName;

You'll probably want to schedule this query periodically and snapshot the results somewhere, since the data won't hang around in the default trace forever.

Event Notifications

If you'd rather have an EVENT NOTIFICATION instead of a server-side trace running, you can do it this way. First, create a table in MSDB to track the login name and the time of the change (you may want to capture other information from the event as well, like I did with the trace above, but this is a bit more involved and I'm trying to maintain some brevity).

USE [msdb];
GO

CREATE TABLE dbo.PasswordChanges
(
    LoginName     SYSNAME,
    EventDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Now, we need a queue and a service for our notification:

CREATE QUEUE PasswordChangeQueue;
GO

CREATE SERVICE PasswordChangeService
    ON QUEUE PasswordChangeQueue
   ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

CREATE EVENT NOTIFICATION PasswordChangeNotification
    ON SERVER WITH FAN_IN
    FOR AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
    TO SERVICE 'PasswordChangeService', 'current database';
GO

And a procedure to actually do something when the event happens:

CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @message_body XML;

   WHILE (1 = 1)
   BEGIN
       WAITFOR ( RECEIVE TOP(1) @message_body = message_body
          FROM dbo.PasswordChangeQueue), TIMEOUT 1000;

       IF (@@ROWCOUNT = 1)
       BEGIN
        INSERT dbo.PasswordChanges(LoginName)
          SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
       END
   END
END
GO

And finally set the notification to use this procedure.

ALTER QUEUE PasswordChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogPasswordChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Now change the password for a few logins, and you should see results from the following query:

SELECT LoginName, LastChange = MAX(EventDateTime)
  FROM dbo.PasswordChanges
  GROUP BY LoginName;


What happens when the mirroring server goes offline (for a short while)?

I'd do a set partner suspend, then take the mirror down.

Some additional links:

Mirror backup & restore

Mirror backup script


SQL Data Warehouse - Is it possible to switch a whole table to a partition?

Azure SQL Data Warehouse does not support check constraints so partition switching is a slightly different ballgame.  The partition scheme as described in the table DDL (rather than a partition schema/function pair) must match exactly in both tables.

Code example duplicated from here:

-- Assume we have a file with the values 1 to 100 in it.

-- Create an external table over it; will have all records in
IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'ext' )
EXEC ( 'CREATE SCHEMA ext' )
GO

-- DROP EXTERNAL TABLE ext.numbers
IF NOT EXISTS ( SELECT * FROM sys.external_tables WHERE object_id = OBJECT_ID('ext.numbers') )
CREATE EXTERNAL TABLE ext.numbers (
    number          INT             NOT NULL
    )
WITH (
    LOCATION = 'numbers.csv',
    DATA_SOURCE = eds_yourDataSource,
    FILE_FORMAT = ff_csv
);
GO

-- Create a partitioned, internal table with the records 1 to 50
IF OBJECT_ID('dbo.numbers') IS NOT NULL DROP TABLE dbo.numbers

CREATE TABLE dbo.numbers
WITH (
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED INDEX ( number ),
    PARTITION ( number RANGE LEFT FOR VALUES ( 50, 100, 150, 200 ) )
    )
AS
SELECT *
FROM ext.numbers
WHERE number Between 1 And 50;
GO

-- DBCC PDW_SHOWPARTITIONSTATS ('dbo.numbers')

-- CTAS the second half of the external table, records 51-100 into an internal one.
-- As check contraints are not available in SQL Data Warehouse, ensure the switch table
-- uses the same scheme as the original table.
IF OBJECT_ID('dbo.numbers_part2') IS NOT NULL DROP TABLE dbo.numbers_part2

CREATE TABLE dbo.numbers_part2
WITH (
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED INDEX ( number ),
    PARTITION ( number RANGE LEFT FOR VALUES ( 50, 100, 150, 200 ) )
    )
AS
SELECT *
FROM ext.numbers
WHERE number > 50
GO

-- Partition switch it into the original table
ALTER TABLE dbo.numbers_part2 SWITCH PARTITION 2 TO dbo.numbers PARTITION 2;

SELECT *
FROM dbo.numbers
ORDER BY 1;

HTH


Authentication failed on secondary mongodb

I found the problem. This is the mongo client issue. I added mongodb repository and install mongo shell

echo "deb http://repo.mongodb.org/apt/ubuntu xenial/mongodb-org/3.4 multiverse" > /etc/apt/sources.list.d/mongodb.list

# apt-get update; apt-get install mongodb-org-shell


Determining why a transaction log file is so large

the transaction log for the second database is staying around 300GB. Each transaction log backup is around 80GB.

That sounds reasonable.  Your log file should have enough room to accommodate the log records between any two backups without growing.  And it's better if you can afford to skip a log backup, or accommodate an unusually busy period without growing.  Growing the log file requires it to be zeroed, which takes time, and there may not be space available.

So if you normally need 80GB, having 300GB reserved sounds reasonable.

If you shrink the log file after every log backup for a few days, you can probably shrink it, but I wouldn't go below 200GB probably.  Internally the effectiveness of a log shrink operation depends on which VLFs inside the log file have active records.  See Shrinking the Transaction Log and How to Shrink the SQL Server Log for an explanation of why it can take several tries to shrink the log file down to something close to the average log backup size.


Difference between storing a DBUnit file in version control and using Liquibase for Database Version control?

From their own FAQ:
"Can I use DbUnit in order to check and execute DDL statements?
    No. DbUnit is not intended to track or test structural DB changes. For this purpose check out liquibase."

So it appears that DbUnit is useful for storing data differences between different versions of a database, while LiquiBase is for schema changes.


Row-row based logging in a blackhole replication filter setup?

https://dev.mysql.com/doc/refman/8.0/en/replication-features-blackhole.html -->

The BLACKHOLE storage engine accepts data but discards it and does not store it. When performing binary logging, all inserts to such tables are always logged, regardless of the logging format in use. Updates and deletes are handled differently depending on whether statement based or row based logging is in use. With the statement based logging format, all statements affecting BLACKHOLE tables are logged, but their effects ignored. When using row-based logging, updates and deletes to such tables are simply skipped—they are not written to the binary log. A warning is logged whenever this occurs.
For this reason we recommend when you replicate to tables using the BLACKHOLE storage engine that you have the binlog_format server variable set to STATEMENT, and not to either ROW or MIXED.


It's unclear what they want to measure.  To see the network lag, all they need is ping in the OS.  That should be much less than a second, even to the other side of the world.  (Unless they have a sloppy network.)

Seconds_behind_master is problematical.  It is a handy tool for the DBA, but it can be "wrong" for many reasons.

When running a long query, it shows how long ago the query started on the Master.  So, Seconds_behind_master will rise as long as that query is running on the Slave.
If you flood the Master with lots of writes from lots of connections, the Slave can get "behind".  This is congestion because of single-threaded Slave, not because of network lag.

One thing it can tell (I think) -- If the timezones are inconsistent, or they don't have a good clock sync (NTP).

A SELECT will not propagate through replication, only writes.  So, I see no use for your suggested SELECT.

If you are running at least 5.6.4:  CREATE a DATABASE and a login limited to that database (GRANT ALL ON db.* ...).  Give them the user name an password.  Let them INSERT into the Master and see what pops up in the Slave.  I think this is what is needed (but I have not tried it):

-- Create on Master and propagate to Slave:
CREATE TABLE Timer (
    id INT NOT NULL,
    raw_time TIMESTAMP(6) NOT NULL,
    sys_time TIMESTAMP(6) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;
-- Run on Master:
REPLACE INTO Timer (id, raw_time, sys_time) VALUES (1, NOW(6), SYSDATE(6));
-- Run on Slave:
SELECT * FROM Timer;

The difference of the two columns will say how much delay where was -- Including

A tiny delay between NOW and SYSDATE on the Master
Network delay
Slave backlog (if something was running on the Slave when the REPLACE was being replicated; this can be minimized by using multi-threaded replication)

Note:  The (6) everywhere gives you microsecond precision.  This is more than sufficient to measure the milliseconds for replicating cross-country.

Note:  That technique won't work if you run with --sysdate-is-now.

Point the Company at this Answer; challenge them to top it.


A lot of data in the table is only accessed for 4 days or so, how to handle it afterwards?

Yes, there is a cost to having lots of cold data in actively-queried tables.  One obvious case is that bigger tables have deeper indexes (i.e. more pages between the root and the leaves) so require more IO to read and write.

I'd suggest you use partitioning.  Keep the active partitions in the current table and swap the others out.  Ideally you'd then use a maintenance task to move the historical data to a separate DB for archive.  Make it clear to your external users what the time cut-off is and allow them to query either accordingly.


SSRS - : How to make a conditional formatting for matrix by group?

If you are formatting cells based on group totals and not a row value, you need to use an aggregate and indicate the correct row group scope.  In this sample, "UserName" is the name of the row group scope on the Row Groups section of the Grouping pane
=IIf(Sum(Fields!CountFailures.Value, "UserName") >=1, "Pink", "White")


Use mysqltuner for some quick suggestions. At a glance it's easy to tell that you will be alerted for  high memory allocation!! Your innodb-buffer-pool is much higher WRT available memory.

About high CPUs, what does your processlist or slow query log tells you? Sort or long running queries, using tmp tables? Queries not using indexes? Make sure you have well indexed tables for respective queries.

Use explain to review and optimize your queries.


How to avoid delimiter character in BCP export?

I managed to make this work by combining all columns into one single string from SQL and return it to BCP tool.


Create tables:

postgres=# create table "user" (
    id int primary key,
    name text unique);
postgres=# create table "role" (
    id int primary key,
    name text unique);
postgres=# create table "userroles" (
    "user" int references "user",
    "role" int references "role",
    primary key("user", "role")
);

Populate test data

postgres=# insert into "user" values (1, 'privateuser'), (2, 'publicuser');
postgres=# insert into "role" values (1, 'private_account'), (2, 'other_role');
postgres=# insert into userroles values (1,1), (1,2), (2,2);

Find all users and their roles:

postgres=# SELECT u.id, u.name as "user", r.id, r.name as "role"
    FROM "user" u
    LEFT JOIN "userroles" ur ON ur.user=u.id
    LEFT JOIN "role" r ON r.id=ur.role;

 id |    user     | id |      role
----+-------------+----+-----------------
  1 | privateuser |  1 | private_account
  1 | privateuser |  2 | other_role
  2 | publicuser  |  2 | other_role
(3 rows)

Find all users who do NOT have the specific role:

postgres=# select u.id, u.name as "user", r.id, r.name as "role"
    FROM "user" u
    LEFT JOIN userroles ur ON ur.user=u.id
    LEFT JOIN "role" r ON r.id=ur.role
    WHERE u.id NOT IN (
        SELECT "user" FROM userroles ur2, "role" r2
        WHERE r2.id=ur2.role AND r2.name='private_account');

 id |    user    | id |    role
----+------------+----+-------------
  2 | publicuser |  2 | other_role
(1 row)

This last query is your answer.


Can I ignore queries about extendedproperty?

You can modify this behaviour by changing the scripting options:

Tools -> Options -> SQL Server explorer -> Scripting -> Script extended properties

  Is there any important data or settings in these queries or I can completely ignore them...

It depends.

DBA's and developers can use extended properties to save information about database objects. If you're scripting other dba's objects you could loose information about them.

Have a look at this articles on MSSQLTips:

Working with SQL Server Extended Properties

Quoted from doc

  Extended Properties can be created for the below database objects, but
  in this tip, we are going to focus on column level Extended
  Properties.

  Database
  Stored Procedures
  User-defined Functions
  Table
  Table Column
  Table Index
  Views
  Rules
  Triggers
  Constraints

  Extended Properties can be used to:

  Specify a caption for a table, view, or column.
  Specify a display mask for a column.
  Display a format of a column, define edit mask for a date column, define number of decimals, etc.
  Specify formatting rules for displaying the data in a column.
  Describe a specific database objects for all users.


Hexadeicimal data and corresponding MySQL field type

SQL is textual language. So you must convert your binary data to its textual representation (solid hexadecimal codes, for example, 'MySQL' -> '4D7953514C') and use it while building query text or attaching text parameter, and use UNHEX() function over this value in query text for to reconstruct binary value on the server side.

Avoid exceeding the max_packet_size value for your query text, increase it if needed in MySQL configuration file. Remember - textual representation is 2 times longer than binary one.

If the file which you want to insert into the table is local for your MySQL server you can use LOAD_FILE() function.


How to deal with a large database containing zipped files.

Your file system is already a database, one made specifically for storing files in a hierarchical structure just like the one you have here. So without knowing more about your use case, the proper way of handling them is exactly what you have already.

If the problem is that extracting all the zip files uses too much disk space, the obvious solution is to not extract them until you actually need them.


Based on the information you provided in the SQL script, the NT account which the SQL Server service is running under doesn't have access to the folder which the file is sitting in.  You'll need to see which account the SQL Service is running under, then grant that account rights to the folder.


How can I backup my large SQL Server table?

You could partition the table. Put each partition in a separate filegroup via the partition scheme. Each filegroup can be backed up individually. From SQL Server 2019 there is a DMV that makes available information on which pages, and hence files, are dirty.

There will still be 1.4TB to backup so a complete backup cycle will still take at least 40 hours. However, each individual filegroup will take much less time avoiding downtime, network congestion and all the other worries.

If writes typically touch only a few partitions you could establish some form of change tracking - either built-in or application code - and only backup the dirty filegroups.

Depending on when rows are written you may be able to make some filegroups read-only. These can then be omitted from the on-going backup cycles once a final backup is taken, reducing elapsed.

Potentially backups and transfers to network shares could happen in parallel reducing the amount of local workspace required (backup partition 1, transfer that over the network while partition 2's backup is prepared, then transfer 2 while 3 is backed up and so on; each local backup can be deleted as soon as it is on the network drive).

If you can arrange things appropriately you may be able to implement piecemeal restores which will certainly improve your RTO from 40hr.

Of course the scripting and restores will be that bit more complicated.


OK.. You have managed to get your self in middle of mess. As it says:

  UnsupportedFormat: Unable to find metadata for
  table:index-3--243586308771972589 Index: {name: id, ns:
  admin.system.version} - version too new for this mongod. See
  http://dochub.mongodb.org/core/3.4-index-downgrade for detailed
  instructions on how to handle this error

Index meta information "says" that it's version is beyond >3.4, what of course it cannot be. Problem index is index of _id at admin.system.version -collection. We can "loose" this collection without problems because we know exactly what we have there. It would be easy, if you had enabled 'directoryPerDB' and especially 'directoryPerIndexes'.. BUT there is tool to fix things in wiredTiger, what I have never used but here is story how to do it. Find out files of admin.system.version -collection, rename those files and try to start mongod again.


The query

SELECT Place,
       AVG(Money)
FROM Table6
GROUP BY Place

gives us a place and average money for it.

The query

SELECT AVG(Money)
FROM Table6

gives us an average money over whole table.

This 2 queries allows us to compare AVG for place and AVG for all (because there are more than 1 table we must add table alias to each field)

SELECT t6.Place,
       AVG(t6.Money) avg_place,
       CASE WHEN AVG(t6.Money) > subquery.avg_all THEN 'High'
            WHEN AVG(t6.Money) < subquery.avg_all THEN 'Low'
            ELSE                                       'Equal'
       END avg_rank
FROM Table6 t6, ( SELECT AVG(t6_1.Money) avg_all
                  FROM Table6 t6_1 ) subquery
GROUP BY t6.Place

And now we can to store the result in new table

CREATE TABLE Table7
AS
SELECT t6.Place,
       AVG(t6.Money) avg_place,
       CASE WHEN AVG(t6.Money) > subquery.avg_all THEN 'High'
            WHEN AVG(t6.Money) < subquery.avg_all THEN 'Low'
            ELSE                                       'Equal'
       END avg_rank
FROM Table6 t6, ( SELECT AVG(t6_1.Money) avg_all
                  FROM Table6 t6_1 ) subquery
GROUP BY t6.Place


MySQL - maximum of sum over different months

You need to GROUP BY nature_of_goods, if the counts should be separated by their values. To only show results of a particular month you have to put that in the WHERE, otherwise all months will be used.

Try something like this:

select max(mycount)
from   (select   count(nature_of_goods) AS mycount
        from     commodities
        where    month(created_at) = <insert desired month here>
        group by nature_of_goods) x;

.


TFS Installation Fails over to mirroring secondary despite not having a Fail-over partner specified

Specifying Failover Partner in the connection string is only for an initial connection.  After connecting the client is notified of the failover partner, and will use that for reconnecting in case of a failover.

  [After connecting to the principal] the data access provider downloads
  the server instance name of the current mirror server. This name is
  stored in the cache as the failover partner name, overwriting the
  client-supplied failover partner name, if any.

Connect Clients to a Database Mirroring Session

So even applications not specifically configured for database mirroring will discover the failover partner at startup.

  our TFS installation has no failover partner specified

So on reboot or cold start TFS will fail to connect if server specified in its connection string is not available and hosting the principal database.  But if a failover occurs after TFS has succesfully connected to the principal it will know to reconnect to the failover partner.


SQL Server - Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

I believe the issue is the client machine's domain isn't trusted by the remote server when logging in via Windows Authentication in SSMS. You should still be able to login with your AD credentials from your local SSMS client by switching the Authentication mode from Windows Authentication to Azure Active Directory - Password.
If you run into the subsequent error "The certificate chain was issued by an authority that is not trusted" then you can tell SSMS to trust it manually by clicking the "Options" button on the Login window. Then under the "Connection Properties" tab, check off the "Trust server certificate" checkbox. Then click the "Connect" button.
Screenshot Example


How does ORDER BY FIELD() in MySQL work internally

For the record

SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4);

should work as well because you do not have to order the list in the WHERE clause

As for how it works,

FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists.

IF id = 1, then FIELD(id,3,2,1,4) returns 3 (position where 1 is in the list)
IF id = 2, then FIELD(id,3,2,1,4) returns 2 (position where 2 is in the list)
IF id = 3, then FIELD(id,3,2,1,4) returns 1 (position where 3 is in the list)
IF id = 4, then FIELD(id,3,2,1,4) returns 4 (position where 4 is in the list)
IF id = anything else, then FIELD(id,3,2,1,4) returns 0 (not in the list)

The ORDER BY values are evaluated by what FIELD() returns

You can create all sorts of fancy orders

For example, using the IF() function

SELECT * FROM mytable
WHERE id IN (1,2,3,4)
ORDER BY IF(FIELD(id,3,2,1,4)=0,1,0),FIELD(id,3,2,1,4);

This will cause the first 4 ids to appear at the top of the list, Otherwise, it appears at the bottom. Why?

In the ORDER BY, you either get 0 or 1.

If the first column is 0, make any of the first 4 ids appear
If the first column is 1, make it appear afterwards

Let's flip it with DESC in the first column

SELECT * FROM mytable
WHERE id IN (1,2,3,4)
ORDER BY IF(FIELD(id,3,2,1,4)=0,1,0) DESC,FIELD(id,3,2,1,4);

In the ORDER BY, you still either get 0 or 1.

If the first column is 1, make anything but the first 4 ids appear.
If the first column is 0, make the first 4 ids appear in the original order

YOUR ACTUAL QUESTION

If you seriously want internals on this, goto pages 189 and 192 of the Book

for a real deep dive.

In essence, there is a C++ class called ORDER *order (The ORDER BY expression tree). In JOIN::prepare, *order is used in a function called setup_order(). Why in the middle of the JOIN class? Every query, even a query against a single table is always processed as a JOIN (See my post Is there an execution difference between a JOIN condition and a WHERE condition?)

The source code for all this is sql/sql_select.cc

Evidently, the ORDER BY tree is going to hold the evaluation of FIELD(id,3,2,1,4). Thus, the numbers 0,1,2,3,4 are the values being sorted while carrying a reference to the row involved.


Want to know the No. of rows,space used,space reserved in each table against all the databases on server level

I believe one I use provides everything you need?

--Table space
SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages)/128 AS TotalSpaceMB,
    SUM(a.used_pages)/128 AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages))/128 AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    AND p.[rows] > 500000
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.Name

Note the conditions in the WHERE statement which may be different for you. This is one I obtained from another website I believe, so it isn't my work! I can't find the reference to which website though...

EDIT

OK, this one is better, includes non clustered index space, identifies the schema for the tables and includes decimal points:

WITH idxs AS (
    SELECT
        t.object_id as TableID,
        t.name AS TableName,
        SUM(a.used_pages)/128.0 AS 'Indexsize(MB)'
        --, i.*
    FROM
        sys.tables t
        INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
        INNER JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
        INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
    WHERE i.[type]=2
    GROUP BY t.object_id, t.name
)
SELECT
    s.name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages)/128.0 AS TotalSpaceMB,
    SUM(a.used_pages)/128.0 AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages))/128.0 AS UnusedSpaceMB,
    idxs.[Indexsize(MB)]
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
LEFT OUTER JOIN
    idxs ON t.object_id = idxs.TableID
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    --AND p.[rows] > 500000
GROUP BY
    s.name, t.Name, p.Rows, idxs.[Indexsize(MB)]
ORDER BY
    t.Name


How to get last values in SQL Server 2008

You can use a CTE (or subquery) to determine which row is the latest per customer, then filter for those in the outer query. Consider using more legible formatting (tabs and carriage returns are your friends), table aliases, and always use the schema prefix.

;WITH x AS
(
  SELECT
    c.CusID,
    rn = ROW_NUMBER() OVER (PARTITION BY c.CusID ORDER BY ct.MonthBill DESC)
    c.CusName,
      ... all those other columns ...
    ct.MonthtBill
  FROM
    dbo.Calculte_Table AS ct
  INNER JOIN
    dbo.CusAdd AS c
    ON ct.CusID = c.CusID
)
SELECT CusID, CusName, ... all those other columns ...
FROM x
WHERE rn = 1
ORDER BY MonthtBill DESC;


importing database backup to phpmyadmin return #1064

Most likely your backup file is a zip file (judging by magic bytes PK at the beginning) that contains a binary backup of MyISAM tables (a set of files):

f2blog_attachments.frm
f2blog_attachments.MYD
f2blog_attachments.MYI
etc...

What you need to do to restore your backup

Unzip the archive file
Stop MySQL
Copy unpacked table files to a DB directory (or create a DB first) in the MySQL data directory
Start MySQL


Stored Procedure Asking for Input While Processing

Short answer is that a stored procedure cannot prompt to accept an input to utilize in the transaction after it has been executed.

What you could do is use a stored procedure to obtain a count or the value you are looking to validate. Based on this, you can use a logic function in your code to then call the second stored procedure which performs the action.

Likewise this IF ELSE logic could be incorporated into the procedure. Let's say you want to delete all records from a table where the value = 1, you expect 50 deletes. You could call the stored procedure and input the value 50 and it would run:

SELECT COUNT(*) FROM TABLE WHERE Value = 1

You can do validation to say that count was greater, less, or equal to 50, and then it can either call another procedure and pass another parameter to it which could be a delete procedure, or it could just end the action.

I'm typically against daisy chaining stored procedures and I like to keep the business logic in the application though, so I would suggest handling it in VB.


Could Stack Overflow run on SQL Server Web Edition?

Considering that SO is one of the most highly visited sites in the world, it may not be the best site to compare against for a new venture.

That being said, SO, to my knowledge, has rolled their own solution in terms of scaling various aspects of the system, including caching just about everything that they can to remove as much load off of the database as possible. Most systems of the SO scale end up in this scenario because no off-the-shelf product or built-in scaleability feature fully meets the needs of these extremely-high-traffic sites.

So, while I wouldn't be very surprised if SQL Web couldn't handle high traffic loads, I would bet that the built-in scaleability pieces you get from other tiers of SQL Server will not, on their own, be enough to handle these sorts of extremely high loads. (hence why SO doesn't rely on them as their primary scale vector.)

Lastly, the business plan for any new venture that is concerned with a high potential to scale needs to also include a revenue stream that scales along with the business volume (whatever that may be).

So, if you scale in the volume of data your system has to hold and manage, does revenue scale with it? Likewise for number of users, or any other metric that will grow. If you find that you are not scaling your revenue as fast as the expense of scaling the combined metrics, you have issues to worry about.


Error during table creation : is not valid at this position expecting an identifier

You are using a reserved word "Order" as a table name.

This is possible, though still not a good idea, if you (always) embrace the name in back-ticks, i.e.: `Order`. It would probably be better to come up with a different table name, though.

By the way, your Order table has a few other issues as well. You need to add the CustomerID and EmployeeID columns before you can create foreign keys with them:

Create Table `Order` (
  OrderNo Int Primary Key,
  CustomerID Int,
  EmployeeID TinyInt,
  Foreign Key(CustomerID) REFERENCES Customer(CustomerID),
  Foreign Key(EmployeeID) REFERENCES Employee(EmployeeID)
) ENGINE=INNODB;

Also, are you sure tinyint is the data type you want for EmployeeID? It's got a very limited range, only [-128, 127].

Another good idea is to specify your ints (in all the tables) as unsigned, i.e.: CustomerID Int unsigned, since presumably you won't be using negative IDs.


Create user to monitor database activity on Postgres

At this time, only superusers can see others' activity details in pg_stat_activity.

PostgreSQL could use a finer-grained rights model, where you can GRANT the MONITOR_QUERIES right to a user, for example. But right now it doesn't have one, and quite a few things are superuser-only. This is one of them.


understanding mysqldump auto-backup performance issues and inner functionality

From here, the complete mysqldump command for a hot backup of a MySQL database using only InnoDB tables is: (from here).

mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

In addition to the Enterprise solution mentioned by @TrentLloyd, there is a F/LOSS solution available from Percona (who are big hitters in the MySQL world). I would recommend without hesitation any tool by Percona and I would put this first on my list for testing on any MySQL system which I work with.

What they offer is Percona XtraBackup which has an incremental backup functionality. The documentation is here.

You might also be interested in LVM snapshots - it's not a technology with which I am currently familiar but it's on my to-do list (like a lot of stuff :-) ). If you're familiar with all that stuff about mounting logical volumes &c., this may be of interest to you.

You could also look at zmanda - (mentioned in "High Performance MySQL").

I would concur with @TrentLloyd that regularly taking a full backup (at such small time intervals) doesn't make sense - your system will not be able to do any real work with a constant resource drain like that.

For completeness, you might want to look at the options here:
(Credit where credit is due, I "stole" this image from here :-) )

On a final note, there is an endless number of shell (mostly bash) scripts out there. A Google of "mysql incremental backup bash script" (or similar) will get you a shedload of these. One of the best (IMHO) of which (and one of the most popular) is automysqlbackup, but you may like to try out any/all/some of the others.

With bash now being available natively on Windows (traditionally poor in the MySQL backup area), these might be worth investigating for users of that OS.

An interesting variant on automysqlbackup (disclaimer - I haven't used it) is a script which will perform incremental backups which is to be found here.

Another way to backup - which is "hot" is to use replication - this process is explained in detail here. The MySQL documentation is available here.


Mysql Server optimization tips needed! (InnoDB)

A few suggestions:

You might want to increase the size of your log files.  They are currently set to 250MB a piece.  A larger value there (perhaps a gig or two) should help reduce IO a bit.
Turn off the slow query logging
Decrease the buffer pool size a bit (or get more memory).  You might be starving threads for memory by using everything for the buffer pool.


Combining data from two databases with same structure into one database

There's some lack of detail in the question; for example, it would be helpful to know:

What is the reason to combine the data together into a single database? It makes no sense to me if there isn't a further goal behind doing so. Perhaps there is a better way to make that end goal happen with fewer intermediate step(s).
Is there some kind of boundary between the sources where the backups are taken, and the destination where the backups are restored that prevents using a more direct approach?
What kind of overlap in data is there between the source sites? Obviously the user data will be different, but things like default/pre-defined rows may be common (are they user-editable?).
How well-structured is the database schema? Are there sufficient surrogate and business keys defined to be able to uniquely identify rows, or merge rows by business meaning? This is critical if you choose a programmatic/dynamic approach to merge the data.
What is the data access strategy? Is it stored-procedure-based, or is it direct table access, or by views, etc.?

In any event, here are a few different architectures you could pursue based on the answers to the questions above.

The most obvious, and probably least disruptive, is to add a piece of code to the existing environment that will merge the data together from the restored databases.

Depending on the number of tables in the database, you could simply script it out (for a small number of tables), or see if there's an off-the-shelf piece of software to do it for you (for a large number of tables). Since you're asking this question, I'm assuming the number of tables isn't trivial.

I've actually written a piece of software that essentially does this (we didn't use off-the-shelf because we have custom requirements) for our database, which is about 750 tables, with the requirement to merge common data. I will tell you now that this was not an easy task. If there are custom requirements, I would strongly consider manually scripting the transfer process, even for a relatively large number of tables. That may sound like a lot of work, and it is, but it's simpler to create and maintain -- the complexity is in size rather than code wizardry, which is much more difficult to debug and test.
Merge replication. This could be accomplished directly from the source databases, assuming there isn't any barrier between the sources and target (see above).

This introduces extra requirements, potential performance issues, and support complexity around the source databases. I would only recommend this if the schema is really clean and there is very little overlap in data between the sources.
Shell database. Create a new (empty) database at the target location that contains views that mimic the source tables or views by using 3-part names to UNION or UNION ALL the data from the individual target databases.

If the eventual plan is to do something like create a data warehouse and you're just going to end up scanning the target databases anyway, this strategy could work out really well, as it's completely transparent if you end up adding more databases down the line. It also requires very little additional storage space.

So those are a few of the architectures I could think of off the top of my head. There are undoubtedly others. What you ultimately end up doing will depend greatly on your specific environment and requirements.


copy only tables with data from one database to another database

Here are several options you can try:

Export/import wizard: If you only want to copy tables and don’t want to bother with relationships and such this is probably the quickest way to do this.

Generate scripts wizard: Generate scripts for all tables you want to migrate. Right click on the database -> Tasks -> Generate scripts -> select only tables you want to migrate.
Also make sure that you manually review execution order of scripts because SSMS doesn’t order these correctly be default (doesn’t take dependencies into account)

Third party tools: There are many database comparison tools out there you can use for this such as Red-Gate’s SQL Compare, ApexSQL Diff and many others…. All of these are premium tools but you can get the job done in trial mode.


how to script server side permissions - specifically EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER?

Unlike sys.server_principals and sys.database_permissions holds information about server level access/permissions, information similar to that sys.database_principals' andsys.database_permission` holds information about database level access/permissions.

Joining these two objects with UNION of sys.objects and sys.sysobjects will give you desired information about user defined and system objects, which can be converted into script.

Note: To generate script for object level permission we need to execute the script in respective database. Like sys.xp_prop_oledb_provide is an object of master database, so we'll execute the SELECT statement in master database.

Example:-

CREATE LOGIN Radhe WITH PASSWORD='HareKrishna001!', DEFAULT_DATABASE=master;
GO

CREATE USER Radhe FOR LOGIN Radhe;
GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [Radhe];
GO

USE master;
GO
select
        case when DBPerm.state_desc='GRANT_WITH_GRANT_OPTION' then 'GRANT' else DBPerm.state_desc end COLLATE SQL_Latin1_General_CP1_CI_AS+
        ' '+DBPerm.permission_name  COLLATE SQL_Latin1_General_CP1_CI_AS+' ON '+o.objectschema+'.'+o.name+' TO ['+DBPrin.name+']'+
         case when DBPerm.state_desc='GRANT_WITH_GRANT_OPTION' then 'WITH GRANT OPTION;' else ';' end COLLATE SQL_Latin1_General_CP1_CI_AS AS [Script]
from
            sys.database_permissions DBPerm
inner join  (
            select name,object_id,schema_name(schema_id) objectschema from sys.objects
            union all
            select name,id,'sys' as objectschema from sys.sysobjects
            ) o
on          DBPerm.major_id=o.object_id
inner join  sys.database_principals DBPrin
on          DBPerm.grantee_principal_id=DBPrin.principal_id
where
            DBPrin.name NOT LIKE '##%##'
        AND DBPrin.name NOT LIKE 'NT AUTHORITY%'
        AND DBPrin.name NOT LIKE 'NT SERVICE%'
        AND DBPrin.name not in ('sa','public');

Results to:

Script
-------------------------------------------------------------
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO [Radhe]

Hare Krishna!


Your .LDF log is growing, as it cannot be truncated because the mirror is unavailable. Eventually you'll run out of space unless you either break the mirroring or bring the mirror back online.


Mariadb times out on startup after system reboot

These messages:

[ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired
[ERROR] mysql.db: 1 client is using or hasn't closed the table properly

are a symptom of a crash and some MyISAM table(s) was open at the time of the crash.

The standard cure is to run

myisamchk --quick

More details: https://dev.mysql.com/doc/refman/5.7/en/myisam-table-close.html and https://dev.mysql.com/doc/refman/5.7/en/myisam-crash-recovery.html  This is reasonably fast and avoids a reinstall.

A better cure is to switch to InnoDB, since it takes care of repairing itself.


Will frequent creation and deletion of TABLE (not indexes) cause fragmentation in database?

There are quite a lot of factors as to whether the staging table will be fragmented, and if so, how much.  The most obvious that come to mind are:

How much data is being added to the table
How much other tables are adding or deleting rows at the same time
Where exactly in the database file the table was created and if there are a lot of contiguous free extents

So basically, the answer is that it is quite likely that the table will be fragmented.  They won't necessarily cause your other permanent tables to be fragmented much, but again, if they are growing at the same time and need new extents, then yes.

All that said, the more important questions are:

Does the fragmentation cause a performance problem?  Not likely.  Fragmentation is not the problem it used to be when you had 3 hard drives in a RAID 5 array.  On Azure your data is using 100s of disks, so the combined read and write power reduces the performance impact of fragmentation to almost nil.  See  Stop Worrying About SQL Server Fragmentation"
Is there anything I can do about it?  Nothing reasonable to stop it unless you want to create a different filegroup on another volume dedicated to the staging tables.  You could also rebuild your other tables and indexes with a lower fill factor and that may stop them from becoming fragmented as much if data is being inserted into someplace other than then end of the table (which might indicate poor table design), but again, see point above.  Perhaps the most reasonable option is to simply run your reindexing job after the ETL job is done.


How to include a datetime parameter within a stored procedure along with string query?

The fundamental issue is that TSQL cannot implicitly convert datetime (or integer or floating point) into character data types. It actually goes backwards, it attempts to implicitly convert the character data to the datetime (int/floating point value) based on the data type precedence rules. This is what your error message is telling you by the way, it couldn't convert the strings to datetime values. You have to explicitly ask it to mash values into character strings.

This example demonstrates the principal with integers as they are easier to understand expected values.

DECLARE
    @stringInt nvarchar(3)
,   @intint int

SELECT
    @stringInt = N'3'
,   @intint = 5

SELECT
    @stringInt + @intint AS implicit_conversion
,   @stringInt + CAST(@intint AS nvarchar(5)) AS explicit_conversion

The value of the implicit conversion shows the @stringint is converted to integer first and then the + is treated as numeric addition and results in 8. The explicit conversion of @intint to a character data type results in the + sign treated as concatenation with the returned string of 35

implicit_conversion explicit_conversion
------------------- -------------------
8                   35

To solve your supplied problem, you must explicitly cast your datetime values to a character type so the query string can be concatenated as expected.

set @query='SELECT [col1]
FROM [Table1]
WHERE ([col2] BETWEEN ''' + CONVERT(nvarchar(24), @FromDate, 121) +''' AND'''+ CONVERT(nvarchar(24), @ToDate, 121) +''')'

But as indicated above, you really don't want to do that for a number of reasons, SQL Injection being one of them. It also makes your maintenance much harder when you're slicing and dicing a query string in TSQL.

A better approach is to parameterize your query and use the power of sp_executesql. A nice thing about the sp_executesql parameter is that you don't have to use all the parameters supplied. Depending on what you are truly trying to do, this may be beneficial.

Sample table and data

CREATE TABLE
dbo.table1
(col1 int, col2 datetime)

INSERT INTO
    dbo.table1
SELECT
3, '2009-04-06'
UNION ALL SELECT
1, '2001-09-11'

Demo of using parameters

DECLARE
    @FromDate datetime,
    @ToDate datetime

SELECT
    @FromDate = '2005-03-17'
,   @ToDate = current_timestamp

DECLARE
    @query nvarchar(max)
SET @query = N'
SELECT [col1]
FROM [Table1]
WHERE ([col2] BETWEEN @start AND @end)'

-- gratuitous use of parameter assignment here
-- could just as easily used @FromDate and @ToDate
-- in the @query and the parameter list
EXECUTE sp_executesql
    @query
,   N'@start datetime, @end datetime'
,   @start = @FromDate
,   @end = @ToDate

Results

col1
3


what are memory fractions and how to get rid of them (or the sort operator) in the example below?

Having a look at the properties of the sort operator:
it has MemoryFractions Input="1" Output="1"

getting rid of this sort operation

having a look at the indexes and definition of table dbo.activity
there are no nonclustered indexes only the primary key

CREATE TABLE [dbo].[activity] (
[activityId]      INT              IDENTITY(1,1)                 NOT NULL,
[title]           VARCHAR(100)                     NOT NULL,
[description]     VARCHAR(max)                     NOT NULL,
[inclusions]      VARCHAR(max)                         NULL,
[imageUriMain]    VARCHAR(255)                         NULL,
[imageUriThumb]   VARCHAR(255)                         NULL,
[imageUriTeaser]  VARCHAR(255)                         NULL,
[categoryId]      INT                              NOT NULL  CONSTRAINT [DF__activity__catego__0F975522] DEFAULT ((1)),
[locationId]      INT                                  NULL,
[documentId]      INT                                  NULL,
CONSTRAINT   [PK_activity]  PRIMARY KEY CLUSTERED    ([activityId] asc),
CONSTRAINT   [FK_activity_category]                         FOREIGN KEY ([categoryId]) REFERENCES [ref_activityCategory]([categoryId]),
CONSTRAINT   [FK_activity_location]                         FOREIGN KEY ([locationId]) REFERENCES [location]([locationId]))

GO

same with table documents

CREATE TABLE [dbo].[document] (
[documentId]      INT              IDENTITY(1,1)                 NOT NULL,
[uploadToBucket]  VARCHAR(200)                         NULL,
[path]            VARCHAR(200)                         NULL,
[activityId]      INT                                  NULL,
CONSTRAINT   [PK__document__EFAAAD856EBBBDCD]  PRIMARY KEY CLUSTERED    ([documentId] asc))

I create the following indexes (paying attention to the order by and all the needed columns for this query):

create index I_title_01 on [dbo].[activity]
(title asc,documentId  asc,activityid asc)
INCLUDE(
        [description],
        [inclusions],
        [imageUriMain],
        [locationId]
)
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  ,
SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, --drop_existing=on,
DATA_COMPRESSION=page, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON NONCLUSTERED_INDEXES

create index I_doc_01 on [dbo].[document]
(documentId  asc,activityid asc)
INCLUDE(
                            [path],
                            [uploadToBucket]
)
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  ,
SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF,
 --drop_existing=on,
DATA_COMPRESSION=page, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON NONCLUSTERED_INDEXES

And now the new execution plan looks like this (without sort operation):

Regarding what are memory fractions, I get my answer from this site below, which has explanations and even examples:

Batch mode memory fractions

  There’s very little information out there about memory fractions. I
  would define them as information in the query plan that can give you
  clues about each operator’s share of the total query memory grant.
  This is naturally more complicated for query plans that insert into
  tables with columnstore indexes but that won’t be covered here. Most
  references will tell you not to worry about memory fractions or that
  they aren’t useful most of the time. Out of thousands of queries that
  I’ve tuned I can only think of a few for which memory fractions were
  relevant. Sometimes queries spill to tempdb even though SQL Server
  reports that a lot of query memory was unused. In these situations I
  generally hope for a poor cardinality estimate which leads to a memory
  fraction which is too low for the spilling operator. If fixing the
  cardinality estimate doesn’t prevent the spill then things can get a
  lot more complicated, assuming that you don’t just give up.


Backup/restore of a database that has a filestream

I believe the mystery is cleared up.  The documentation only says that the SQL Server account needs full access to the filestream folder, but when I checked who had access to the main DATA folder, I saw that the SYSTEM account also had access.  I gave full rights to the FS folder to the SYSTEM user, and now I don't get this error anymore.


There could be various reason for this error.
 You are using wrong ORACLE_SID and ORACLE_HOME.
 The instance that you are trying to connect isn't up and running.

When you have wrong ORACLE SID and HOME.

I could reproduce the error by changing my SID to something else. My SID is oracdb.

[oracle@myserver ~]$ ps -ef | grep pmon
oracle   29336     1  0 Apr27 ?        00:00:22 ora_pmon_oracdb

But I changed it to orcl.

[oracle@myserver Desktop]$ export ORACLE_SID=orcl

Then I tried to connect to the database using SQL*PLUS.

[oracle@myserver Desktop]$ sqlplus sys

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 00:23:50 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -923528497
Process ID: 0
Session ID: 0 Serial number: 0

Then I changed it to original SID.

[oracle@myserver ~]$ export ORACLE_SID=oracdb
[oracle@myserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 00:28:40 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

When your instance is down and connected as a non-sys user

Use the following linux command to check whether some of the important background processes are running or not such PMON or SMON.

[oracle@myserver ~]$ ps -ef| grep pmon
oracle   57587 57044  2 00:52 pts/3    00:00:00 grep pmon

My oracle instance is down.

Now, as you have tried, I attempt to connect to the database using non-sys user, sysdba is a privilege not a database user.

[oracle@myserver ~]$ sqlplus sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 00:53:04 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -688980181
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

I got the same error as you have got.

Now, connect as sysdba/sysoper and start the database instance.

[oracle@myserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 00:53:36 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup


specify the correct element to pick in JOIN mysql

Your query is using grouping but you do not have any aggregation happening on any of your columns. By default, I believe MySQL will allow you to do this, but it is not supported or generally accepted as safe because MySQL will pick arbitrary values for your non-aggregated columns. (SEE: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by)

So assuming you want to group properly, I'm guessing you probably want a sum of sales for each product and returned with that aggregate, you want the "first" image.

What version of MySQL are you using? If you are using < v8.0 then there's no "simple" way to effectively do this unless a few assumptions can be made. MySQL < v8.0 lacks windowed functions / groupwise sorting (google "row_number over partition by") and I have not worked with that functionality in 8.0 yet so I cannot speak to that.

If you are able to assume a few things though, you can probably get a working query.

Assuming your images table ID is auto_increment and you will always want the image with the lowest ID to be returned in this query, then the following should work:

mysql> select p.id, p.product, i.image, sum(sale) from products p left join (select p_id, min(id) as minImageId from images group by p_id) as minI on p.id = minI.p_id left join images i on minI.minImageId = i.id left join sales s on p.id = s.p_id group by p.id;
+----+--------------+-------------+-----------+
| id | product      | image       | sum(sale) |
+----+--------------+-------------+-----------+
|  1 | super prod 1 | myimg01.jpg |        45 |
|  2 | super prod 2 | myimg04.jpg |        29 |
|  3 | super prod 3 | myimg06.jpg |        12 |
|  4 | super prod 4 | myimg08.jpg |        55 |
|  5 | super prod 5 | myimg10.jpg |        16 |
+----+--------------+-------------+-----------+
5 rows in set (0.00 sec)

Here is how I built the query up from the products table all the way to the finished query:

SELECT *
FROM products;
+----+--------------+
| id | product      |
+----+--------------+
|  1 | super prod 1 |
|  2 | super prod 2 |
|  3 | super prod 3 |
|  4 | super prod 4 |
|  5 | super prod 5 |
+----+--------------+

-- for each product, get the minimum image ID
SELECT *
FROM products
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON products.id = minI.p_id;
+----+--------------+------+------------+
| id | product      | p_id | minImageId |
+----+--------------+------+------------+
|  1 | super prod 1 |    1 |          1 |
|  2 | super prod 2 |    2 |          4 |
|  3 | super prod 3 |    3 |          6 |
|  4 | super prod 4 |    4 |          8 |
|  5 | super prod 5 |    5 |         10 |
+----+--------------+------+------------+

-- clean up query by removing a column we do not care to see
SELECT p.id,
       p.product,
       minI.minImageId
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id;
+----+--------------+------------+
| id | product      | minImageId |
+----+--------------+------------+
|  1 | super prod 1 |          1 |
|  2 | super prod 2 |          4 |
|  3 | super prod 3 |          6 |
|  4 | super prod 4 |          8 |
|  5 | super prod 5 |         10 |
+----+--------------+------------+

-- join in image table using the min image ID we found ealier
SELECT p.id,
       p.product,
       minI.minImageId,
       i.image
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id;
+----+--------------+------------+-------------+
| id | product      | minImageId | image       |
+----+--------------+------------+-------------+
|  1 | super prod 1 |          1 | myimg01.jpg |
|  2 | super prod 2 |          4 | myimg04.jpg |
|  3 | super prod 3 |          6 | myimg06.jpg |
|  4 | super prod 4 |          8 | myimg08.jpg |
|  5 | super prod 5 |         10 | myimg10.jpg |
+----+--------------+------------+-------------+

-- bring in and aggregate our sales data. if you do not put an aggregate function on the sale value, mysql will pick a value for you arbitrarily
SELECT p.id,
       p.product,
       minI.minImageId,
       i.image,
       sum(sale)
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id
LEFT JOIN sales s ON p.id = s.p_id
GROUP BY p.id;
+----+--------------+------------+-------------+-----------+
| id | product      | minImageId | image       | sum(sale) |
+----+--------------+------------+-------------+-----------+
|  1 | super prod 1 |          1 | myimg01.jpg |        45 |
|  2 | super prod 2 |          4 | myimg04.jpg |        29 |
|  3 | super prod 3 |          6 | myimg06.jpg |        12 |
|  4 | super prod 4 |          8 | myimg08.jpg |        55 |
|  5 | super prod 5 |         10 | myimg10.jpg |        16 |
+----+--------------+------------+-------------+-----------+

-- clean up query by removing a column we do not care about
SELECT p.id,
       p.product,
       i.image,
       sum(sale)
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id
LEFT JOIN sales s ON p.id = s.p_id
GROUP BY p.id;
+----+--------------+-------------+-----------+
| id | product      | image       | sum(sale) |
+----+--------------+-------------+-----------+
|  1 | super prod 1 | myimg01.jpg |        45 |
|  2 | super prod 2 | myimg04.jpg |        29 |
|  3 | super prod 3 | myimg06.jpg |        12 |
|  4 | super prod 4 | myimg08.jpg |        55 |
|  5 | super prod 5 | myimg10.jpg |        16 |
+----+--------------+-------------+-----------+


Return both one value and one column from pl/pgsql function

but RETURN a, b; is not possible like this, right? How to do?

Right, not like this. But this is possible:

CREATE OR REPLACE FUNCTION public.retrieve_vector(
   _vname bigint
 , OUT _info text       -- "data interpretation information"
 , OUT _arr  text[]     -- "vector"
   )
  RETURNS record AS     -- that is the return type you need
$func$
BEGIN
   -- Retrieve data information
   SELECT info
   FROM   public."InfoTable"
   WHERE  name = _vname
   INTO   _info;        -- actually a more complicated process

   -- Retrieve "vector"
   SELECT vname         -- you may need explicit casting to text[] ...
   FROM   public."Vectors"
   INTO   _arr;         -- also actually more complicated

   -- RETURN;           -- optional
END
$func$  LANGUAGE plpgsql;

Don't quote the language name plpgsql.

Assuming you want to return a single row. For returning 0-n rows (a set) you would use RETURNS TABLE(_info text,_arr text[]) in combination with RETURN NEXT or RETURN QUERY or similar. See:

Difference between return next and return record

The term "vector" in your question does not help to clarify which actual data types we are dealing with. You also mention "array". Any Postgres array can be cast to text[], so I use that type as common denominator. PL/pgSQL SELECT INTO coerces compatible types into the target data typesd automatically. Else you may need to add an explicit cast like source_array_column::text[]. Not enough info in your question to narrow this down.

You asserted that:

  type is unknown at call time

Else you might be able to work with a polymorphic array type as well. See:

Refactor a PL/pgSQL function to return the output of various SELECT queries


difference between create constraint non clustered and create index non clustered

With this:

CREATE TABLE [Ref].[Country](
    [CountryId] [uniqueidentifier]  NOT NULL,
    [CountryName] [varchar](50) NOT NULL,
    [CreatedDate] [datetime2](2) NOT NULL,
    [ModifiedDate] [datetime2](2) NULL,
    CONSTRAINT [UCI_CountryName] UNIQUE CLUSTERED ([CountryName] ASC),
    CONSTRAINT [PK_Country] PRIMARY KEY ([CountryId])
)

  i want to create NCI on Primary key column with INCLUDE.

You already have it.  A non-clustered index always contains the "row locator", which for a table with a clustered index is the clustered index key.  The leaf level of the non-clustered PK with have (CountryId,CountryName) rows, but sorted by CountryId, whereas in the clustered index the rows are sorted by CountryName.

For a unique index the clustered index columns are stored as "included columns" only on the leaf level.  For a non-unique index the clustered index columns are added as trailing index keys.

But you cannot include additional columns in a Primary Key constraint.  You can implement the identical physical design by adding a unique, non-clustered index with included columns.

Also there's nothing wrong with using a UNIQUEIDENTIFIER for the clustered index key, so long as your table isn't large, or you generate the value with NEWSEQUENTIALID().


how to verify that next sequence value can be used as a new primary key

The values of the PK comes from different sources: horrible. Using the sequence would be the preferred way. You can not tell what exactly is the next value for the sequence by lookin in the dictionary. There is caching in effect, obviously for performance reasons. If you look in the dictionary you will find the value that is valid after the cache ran out of numbers.
What you don't know, is how far the cache is used.

    drop sequence z;
    create sequence z;  -- default cache size 20
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 1
    select z.nextval from dual; --> shows 1
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 21

Can you ignore the pre entered value for the PK, so effectively only using the sequence? The way it works now is almost as bad as querying the max(pk)+1 from the table.