Can an index speed up a query with GROUP BY/aggregate over the whole table (no selectivity)? Let's say i have a table with 3 columns a,b and c.

Could i possible speed up a query that looks like this by using index(es) ??

SELECT a,b,SUM(c)  # or AVG(c)
FROM table
GROUP BY a,b
ORDER BY a,b
;

If the above question is positive, what type of index do you recommend and how would this work?

Not likely. GROUP BY and ORDER BY typically entail a sort. However, in this case a HashAggregate is used (likely because we're working with the whole table).

CREATE TABLE foo AS
SELECT x % 5 AS a, x % 10 AS b, x AS c
FROM generate_series(1,1e6) AS x;

With the HashAggregate plan,

# EXPLAIN ANALYZE SELECT a,b,sum(c) FROM foo GROUP BY a,b ORDER BY a,b;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=23668.04..23668.16 rows=50 width=14) (actual time=611.607..611.608 rows=10 loops=1)
   Sort Key: a, b
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=23666.00..23666.62 rows=50 width=14) (actual time=611.589..611.593 rows=10 loops=1)
         Group Key: a, b
         ->  Seq Scan on foo  (cost=0.00..16166.00 rows=1000000 width=14) (actual time=0.012..71.157 rows=1000000 loops=1)
 Planning time: 0.168 ms
 Execution time: 611.665 ms

so we add an index...

CREATE INDEX idx ON foo (a,b);
VACUUM FULL ANALYZE foo;

... still shows the same query plan. So we disable HashAggregate

SET enable_hashagg = false;

And, try again..

# EXPLAIN ANALYZE SELECT a,b,sum(c) FROM foo GROUP BY a,b ORDER BY a,b;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..61292.04 rows=50 width=14) (actual time=108.149..655.536 rows=10 loops=1)
   Group Key: a, b
   ->  Index Scan using idx on foo  (cost=0.42..53791.41 rows=1000000 width=14) (actual time=0.066..272.299 rows=1000000 loops=1)
 Planning time: 0.121 ms
 Execution time: 655.594 ms
(5 rows)

And, it takes more time 655ms compared to the previous 611ms.

Need faster?

If that's not fast enough (and 611ms to group and sum a million rows isn't bad). Then you can use a MATERIALIZED VIEW if your workload permits it (if the query is hot and/or infrequently updated),

CREATE MATERIALIZED VIEW foo2 AS
SELECT a,b,sum(c)
FROM foo
GROUP BY a,b
ORDER BY a,b;

Now you're down to sub-MS time when TABLE foo2. Then just do REFRESH MATERIALIZED VIEW foo2; to refresh the view. Or, you can create a trigger update another table and update it with triggers.

On the actually column being aggregated.

There are some exceptions, but sum() isn't one of them. Most aggregates do not use an index because they usually do not have a need for one. The exceptions are the aggregates that are order-specific (like min() and max()). So for instance, if after we created the index on (a,b) you run a sum(a),

# EXPLAIN ANALYZE SELECT sum(a) FROM foo;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=18666.00..18666.01 rows=1 width=4) (actual time=287.063..287.063 rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..16166.00 rows=1000000 width=4) (actual time=0.015..85.435 rows=1000000 loops=1)
 Planning time: 0.098 ms
 Execution time: 287.104 ms
(4 rows)

you can see that it still uses a seq scan.. You'll see the same plan for sum(c) which doesn't have an index at all. Now here is the kicker,

# EXPLAIN ANALYZE SELECT min(a) FROM foo;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.48..0.49 rows=1 width=0) (actual time=0.041..0.041 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.42..0.48 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=1)
           ->  Index Only Scan using idx on foo  (cost=0.42..56291.41 rows=1000000 width=4) (actual time=0.035..0.035 rows=1 loops=1)
                 Index Cond: (a IS NOT NULL)
                 Heap Fetches: 1
 Planning time: 0.171 ms
 Execution time: 0.080 ms
(8 rows)

min(a) unlike sum(a) can make use of an ordering, and so the query planner realizes that the index scan, which isn't free, has a benefit.

Proof using index on (a,b,c)

For whatever reason if you want to see proof that a further index on c doesn't matter for the purposes of summing (ask a question if after reading the above you still do not understand why),

-- turn this back on we turned it off earlier
SET enable_hashagg = true;
DROP INDEX idx;
CREATE INDEX idx ON foo (a,b,c);
VACUUM FULL ANALYZE foo;
EXPLAIN ANALYZE SELECT a,b,sum(c) FROM foo GROUP BY a,b ORDER BY a,b;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=23668.04..23668.16 rows=50 width=14) (actual time=608.888..608.889 rows=10 loops=1)
   Sort Key: a, b
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=23666.00..23666.62 rows=50 width=14) (actual time=608.869..608.871 rows=10 loops=1)
         Group Key: a, b
         ->  Seq Scan on foo  (cost=0.00..16166.00 rows=1000000 width=14) (actual time=0.015..72.613 rows=1000000 loops=1)
 Planning time: 0.130 ms
 Execution time: 608.947 ms
(8 rows)

No improvement whatsoever. Disabling hashagg still shows no improvement whatsoever.

TLDR;

In this specific and simple use case indexes don't matter. The planner chooses the best method.


How to refer to individual columns of a record returned by a function? I have a function that returns setof record type.

CREATE TYPE CIR_TYPE AS
(
    ID integer,
    path text,
    cycle boolean
);

CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS $body$

DECLARE
    r CIR_TYPE;

BEGIN
    For r in WITH RECURSIVE graph(ID, path, cycle) AS (
                SELECT id AS id
                     , ARRAY[parentid, id] AS path
                     , (parentid = id) AS cycle
                FROM  mytable

                UNION ALL

                SELECT d.id, d.parentid ||path, d.parentid = ANY(path)
                FROM   graph g
                JOIN   mytable d ON d.id = g.path[1]
                WHERE  NOT g.cycle
             )
             SELECT DISTINCT *
             FROM   graph
    Loop

        If r.cycle= TRUE then
        Return NEXT r;
        End if;

    End loop;

END;
$body$ LANGUAGE plpgsql;

When I execute the above function I get an output like below:

circular_ref
----------------------------
(0,"{1,0,1}",t)
(1,"{0,1,0}",t)

Here 0 and 1 are ID, {0,1,0} and {0,1,0} are path and t indicates cycle.

I want to print the output like

ID     |    Path
----       ------
0      |   1,0,1
1      |   0,1,0

Please suggest how to get the expected result.

The problem, I guess, hits everyone starting with PostgreSQL.  Fortunately, the solution is rather easy: simply call

SELECT * FROM circular_ref();

instead of

SELECT circular_ref();

This way it won't be a single record you get but a proper row.  This way, you can even do

SELECT id, path FROM circular_ref();

to get the columns you want.


How Datapump is faster than export/import backup? Why is datapump export faster than exp?

Datapump is much more efficient than exp:

Datapump runs within the Oracle server processes, and can read directly from the database files and write directly to a file on the server. As I understand it, data access is direct, not via SQL.

exp uses the ordinary Oracle Call Interface (OCI) -- the library that Oracle provides to connect to the database. It can only run ordinary SQL statements like any other program.


Trying to join 2 tables to get a count of orders and grouped by email I have two tables in DB Orders with columns Orders Id, Orders NO, Customer ID; Customer with columns Customer ID, Customer email.  I need a count of the orders for each customer and group by email with output something like

customeremail@email.com 23

SELECT Customer.Customer Email,
Count Orders.Order ID,
From Orders,
Left Join Orders on Customer.Customer ID
Group by Customer.Customer email, Orders.Order ID

You're kind of close by using a LEFT JOIN but you should start with your Customer table like this:
SELECT Customer.[Customer Email], COUNT(Orders.[Order ID]) AS OrderCount,
FROM Customer,
LEFT JOIN Orders
    ON Customer.[Customer ID] = Orders.[Customer ID]
GROUP BY Customer.[Customer Email]

Note this assumes the Customer Email field is unique in the Customer table, otherwise if two Customers can share the same email, then you should group on Customer ID instead. Also you should avoid spaces in your columns names, it's best practice to not use special characters.

Additional note, an edit suggestion was made to use COUNT(DISTINCT Orders.[Order ID]) instead, on the query I suggested, in case Order ID was not unique in the Orders table. While it's a good thought, it's also an assumption (and likely not the case in this context). Using extra operators like DISTINCT (when not needed) could lead to alternative execution plans that are potentially less performant (e.g. DISTINCT will likely result in an additional sort operation to be performed). Again I think it's a good thought, but I don't like to make answers on assumptions when there are other potential consequences, so I think the query I proposed is sufficient unless OP provides any additional information that states otherwise.


Can I copy installed mysql from ubuntu to centos7 directly without installation? I'm trying to copy an installed MySQL from Ubuntu to Centos7 because I do not want an installation on Centos7 any more. This looks like it works but I'm not sure if there any potential issues which I do not know.
Has anyone tried this operation before?
The purpose to try this copying way is that I'm writing an one click installation sh script which including serveral softwares, such as JDK, MySQL, nginx, elasticsearch  and some Java, python code. Coping existing MySQL is obviously the easiest way to finish MySQL installation script, since I need to install MySQL and create accounts and DB's in traditional way .
The steps for my copying MySQL directly from Ubuntu to Centos7 are:

create user/group MySQL on Centos7
copying MySQL installed directory from ubuntu to the same directory on centos7, such as /usr/local/mysql
copying my.cnf file from Ubuntu to /etc on Centos7
copying mysql.server to /etc/init.d/mysql
grant permission to mysql.server and chown the owner of /usr/local/mysql  to mysql:mysql
start/stop MySQL service manually, it's looks fine

BTW, my Centos7 machine is new installed, which means I did not installed any other software on it, and my Centos7 can not reach internet so I could not install MySQL through apt, yum ways. I need to download installation package from website firstly, then I can install MySQL through rpm, or tar in the traditional way.
What I want to say is that no matter the apt, yum, rpm, or tar way,  it's complicated to write these steps in my one click installation script, so I choose copying directly, but I  have some worries about it since I do not know if there are some potential issues.

The simple answer is NO. A more realistic answer would be not easily. If you wanted to do this "the wrong way", you would do something like this:

Install the exact same version of MySQL on Ubuntu as you are running on CentOS
Copy the entire contents of your /var/lib/mysql directory from CentOS to Ubuntu
Modify all of the configuration files to ensure they are using Ubuntu's directory paths rather than those used by CentOS
Cross your fingers
Start MySQL and hope everything magically works

This is a recipe for disaster as it almost always results in more work than doing it "a better way". Here is how I migrate MySQL data from one server to another:

Note: All of the following commands work on Ubuntu and MySQL.

Install MySQL on the target server:
sudo apt update
sudo apt install mysql-server

Configure MySQL:
sudo mysql_secure_installation

You will be walked through the preliminary setup for MySQL, including the option to set a password for the root account. Do note that even though you've set a password for the root MySQL user, the account is not configured to authenticate with a password when connecting to the MySQL shell. More on this later.

Create your MySQL administration account:
mysql> CREATE USER 'huangxy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'superSecretPassword!123';

If you will be connecting to MySQL remotely, you will need to replace localhost with %.
Important: Do not use root for all of your administration activities. It is better if you have an account specific to you, even if it has full access to the whole system. root should be used only when absolutely necessary, such as when configuring replication or fixing corrupted databases.

Give your account full access:
mysql> GRANT ALL ON *.* TO 'huangxy'@'localhost' WITH GRANT OPTION;

Ensure the privileges are written:
mysql> FLUSH PRIVILEGES;

Exit MySQL:
mysql> exit;

With this done, now you can create a backup of the MySQL database(s) on your CentOS server. You will need two connections to do this: one to MySQL, and the other to the shell where you can use mysqldump.

Lock MySQL, preventing any updates:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

This is done to ensure that nothing interferes with the database export.
Dump the databases to disk:
mysqldump -u root -p --routines --triggers --add-drop-database --all-databases --all-databases > full_backup.sql

If the version of MySQL on CentOS is 8.0.2 or above, you can also migrate your Users along with permissions:
mysqldump -u root -p mysql user --no-create-info > user_backup.sql

Note: If you are using any version of MySQL lower than 8.0.2, you cannot migrate your users with this command. Instead they will need to be created manually on the new server. This is because the mysql.user table has seen a number of schema changes over the years.
If people still need access to the previous system, you can now unlock the databases:
mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

Be sure to advise people that any changes to data in the old system will not be reflected in the new system.
Copy the database dumps to the new server
Import the databases:
sudo mysql < full_backup.sql

Note: You do not need to specify -u or enter a password here because it is implied that a person with sudo access on an Ubuntu system has full permissions, therefore another password for MySQL is unnecessary.
If applicable, import the user accounts:
sudo mysql mysql < user_backup.sql

Note: Because the --no-create-info option was used when writing the user data to disk, there is no risk of the table being unnecessarily dropped. If there are any schema differences, an error will be reported and you will need to manually resolve the issues.
Connect to MySQL and check that your databases are intact.
Restart MySQL just to clear the buffers:
sudo service mysql restart

So long as there are no issues with the import process, a successful restart will let you know that you're pretty much good to go.

Next Steps ...
Nothing has been said about the various configuration files that MySQL uses for its settings, and that's because it is impossible for me to offer a properly comprehensive outline of what to do. I do not know anything about your server hardware, the databases, or how the systems are used. What I can recommend is this:

Tweak the basic InnoDB settings for your hardware
Monitor the system and make incremental changes as you see fit

I would not recommend copy/pasting the .cnf files from CentOS to Ubuntu, as this would be suboptimal.
While this answer is rather long and may look complicated, it's generally the most reliable way in my experience to move from one server and/or OS to another.


In SQL Server, for numeric data type, can the CHARACTER_MAXIMUM_LENGTH be different from NULL? I have a SQL Server with numeric types for few columns.

When getting a table structure, I have the following results:

Query:

    SELECT *
FROM DB.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE'
  AND DATA_TYPE = 'numeric'

The result is the following (truncated):

CHARACTER_MAXIMUM_LENGTH  NUMERIC_PRECISION   NUMERIC_SCALE
NULL                     2                   0

Will the value CHARACTER_MAXIMUM_LENGTH always be NULL for a numeric ? Don't scale and precision impact the character length of the value ?

From this wonderful website:

INFORMATION_SCHEMA.COLUMNS

CHARACTER_MAXIMUM_LENGTH

  Maximum length, in characters, for binary data, character data, or
  text and image data.
  -1 for xml and large-value type data. Otherwise, NULL is returned. For more information, see

Data Types (Transact-SQL).

Column name      Data type      Description
TABLE_CATALOG   nvarchar(128)   Table qualifier.
TABLE_SCHEMA    nvarchar(128)   Name of schema that contains the table.
TABLE_NAME      nvarchar(128)   Table name.
COLUMN_NAME     nvarchar(128)   Column name.
ORDINAL_POSITION    int Column identification number.
Note: In SQL Server 2005, these column IDs are consecutive numbers.

COLUMN_DEFAULT              nvarchar(4000)  Default value of the column.
IS_NULLABLE                 varchar(3)  Nullability of the column.
                            If this column allows for NULL, this column returns YES. Otherwise, NO is returned.
DATA_TYPE                   nvarchar(128)   System-supplied data type.
CHARACTER_MAXIMUM_LENGTH    int Maximum length, in characters, for binary data, character data, or text and image data.
-1 for xml and large-value type data. Otherwise, NULL is returned. For more information, see Data Types (Transact-SQL).

CHARACTER_OCTET_LENGTH      int Maximum length, in bytes, for binary data, character data, or text and image data.
-1 for xml and large-value type data. Otherwise, NULL is returned.

NUMERIC_PRECISION           tinyint Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_PRECISION_RADIX     smallint    Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_SCALE               int Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
DATETIME_PRECISION          smallint    Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.
CHARACTER_SET_CATALOG       nvarchar(128)   Returns master. This indicates the database in which the character set is located, if the column is character data or text data type. Otherwise, NULL is returned.
CHARACTER_SET_SCHEMA        nvarchar(128)   Always returns NULL.
CHARACTER_SET_NAME         nvarchar(128)    Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.
COLLATION_CATALOG         nvarchar(128) Always returns NULL.
COLLATION_SCHEMA          nvarchar(128) Always returns NULL.
COLLATION_NAME            nvarchar(128) Returns the unique name for the collation if the column is character data or text data type. Otherwise, NULL is returned.
DOMAIN_CATALOG            nvarchar(128) If the column is an alias data type, this column is the database name in which the user-defined data type was created. Otherwise, NULL is returned.
DOMAIN_SCHEMA             nvarchar(128) If the column is a user-defined data type, this column returns the name of the schema of the user-defined data type. Otherwise, NULL is returned.
DOMAIN_NAME               nvarchar(128) If the column is a user-defined data type, this column is the name of the user-defined data type. Otherwise, NULL is returned.


MySQL query to fetch data by lang_id (if not fetch by default lang_id = 0) I have a following table

CREATE TABLE `lang_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang_id` int(11) NOT NULL,
  `page_id` int(11) NOT NULL,
  `page_title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

insert into lang_pages set lang_id = 0, page_id = 1, page_title = 'About Us (English)';
insert into lang_pages set lang_id = 1, page_id = 1, page_title = 'About Us (German)';
insert into lang_pages set lang_id = 2, page_id = 1, page_title = 'About Us (French)';
insert into lang_pages set lang_id = 0, page_id = 2, page_title = 'Contact Us (English)';

I need a MySQL query which fetches the page_title by page_id & lang_id, if not present it should return the one with lang_id = 0 using single query.

I have tried it as below which works fine

select * from lang_pages where page_id = <page_id> and lang_id in (0, <lang_id>) order by lang_id desc limit 1;

For example:

select * from lang_pages where page_id = 1 and lang_id in (0, 1) order by lang_id desc limit 1;

This works just fine in case you are adding page_id in the condition.
(You are welcome if you have another approach for this)

PROBLEM
How would you find all the pages with certain lang_id, say 1? The result will be like If row exists get page from that lang_id else from lang_id = 0

What you have is fine.

Another way to write this (assuming that for every page_id, there is always a row with the default lang_id = 0), is to use a LEFT JOIN. There should be minimal if any difference in performance but this is easily extendable for multiple requests:

select d.page_id,
       coalesce(p.lang_id, d.lang_id)       as lang_id,     -- ours or the default
       coalesce(p.page_title, d.page_title) as page_title   -- ours or the default
from lang_pages as d               -- the default
  left join lang_pages as p        -- our language
    on  p.lang_id = <lang_id>
    and p.page_id = d.page_id
where d.lang_id = 0
  and d.page_id = <page_id>             -- one page or
--  and d.page_id in (1, 3, 7, ...)     -- possible other criteria
 ;


MySQL FULLTEXT search (match against) not working CREATE TABLE `test` (
  `id` int(11) NOT NULL COMMENT 'unique id',
  `text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `text`) VALUES
(1288, 'i just don\'t know what to say.'),
(1340, 'so what?'),
(1344, 'that was probably what influenced their decision.'),
(1345, 'i\'ve always wondered what it\'d be like to have siblings.');

ALTER TABLE `test` ADD FULLTEXT KEY `text` (`text`);

select *,
    match(text) against ('what' IN BOOLEAN MODE) as hasWhat,
    match(text) against ('just' IN BOOLEAN MODE) as hasJust
from test;

The match against "what" always returns 0 even though I'm using "IN BOOLEAN MODE." Why?
With MySQL 5.7.26 but it's also reproducible on http://sqlfiddle.com/#!9/c5c7f90/1 and https://sqltest.net/#1303145.
thanks

It's not working because "what" is a "stopword" ...
from https://dev.mysql.com/doc/refman/5.7/en/fulltext-stopwords.html
I followed the instructions and created a new stopword list.
create table test.custom_stopwords as
select * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD
where length(value) = 1;

SET GLOBAL innodb_ft_server_stopword_table = 'test/custom_stopwords';

Then I rebuilt the fulltext index and it worked with the word "what."


Why doesn't this round up? SELECT ROUND(4.524965325714286, 2);

I'd have thought that should be 4.53, because

6 rounds 8 to 9
9 rounds 2 to 3
3 leaves 4 as 4
..
so. it ends up lastly doing 4.525 rounds to 4.53

It looks like it's doing

I want 4.524965325714286 as 2 decimal places
Q: What's the third decimal? A: 4
4 doesn't round up
Return 4.52

Is that a correct representation of what happens?

I'm using MySQL version 5.7.20

When it is looking at rounding it only looks at the next decimal place to see which direction to round. So in your case it is seeing 4.524 which does not round up to 4.53. I have never heard of a system that rounds up all decimal values until it gets to your limit.

Think of it this way if 2 decimal points is what is important (think a lot of currencies) would you really want the 4th or later decimal value to impact what is calculated for a payment amount?


SQL Server - backup / restore schema only We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - maybe from where someone has backed up data into another column, updated the original column and not deleted the backup column, for example.

There are also varying column sizes (i.e. column a on table b on one server may be nvarchar(7500) but same column on same table on another server may be nvarchar(max)).

Furthermore some sprocs were originally created with dependencies on views (for example) but the views have been deleted.

We've tried the following approaches:

Backup / restore entire database, delete unwanted data

UAT server doesn't have enough room for some databases, enlarging is not an option

Backup / restore small database, truncate all tables and insert into from other server

Not suitable as schema varies from one server to the next

Loop through tables, select * into <tablename> from [ip].[db].[schema].[tablename] where ...

Creates table structures fine including data types, but primary keys, triggers, sprocs etc. not created - we've tried scripting them from various sys tables but due to some views being removed some sprocs fail (same with triggers if they use removed views)

We basically just need to copy across the data for one client only onto a UAT server, run some tests against that and then move onto the next client. Forget things outside the database, these are not part of my task; my task is simply to replicate the structure of a DB from one server to another including sprocs, triggers and primary keys. We will potentially have up to 36 DBs on each of 2 UAT servers, hence the space restriction (6 per developer allowing up to 6 clients on the server per developer). We may be able to increase the number of servers and make it, say, 12 DBs per server, but to encourage those who can get these servers we will have to have a working proof of concept first, if you get what I mean.

Method 3 above gets closest but the fact that sprocs may not even exist is not satisfactory - if any program detects the cause of the error, it may act differently depending on whether the sproc is missing or failed during execution. There might be some code which executes before failure, in which case this would be executed each time the sproc is run on live but not at all on UAT.

Lastly I'm running SSMS 2005 and I believe servers vary between SQL Server 2005 and 2008.

Does anyone have any ideas on how to achieve this? Is it even possible, bearing in mind we physically cannot do a full backup / restore and cannot script all sprocs due to non-existence of views?

I haven't used it before but there is an object in SSIS called Transfer SQL Server Objects.  It has an option to copy data or not and you can select copy all objects or just specific types of objects, permissions etc.

You could also look into doing replication but blocking the data portion of it.  In fact if you set up a snapshot replication that just pushed schema for each of your servers you could potentially have an automated process to do the push.

I think the SSIS option is probably your best bet though.


Is there a more efficient way of using a match score in MySQL full text search? SELECT i.*, MATCH(i.description, i.keywords) AGAINST ("searchterm") AS score
        FROM items AS i
        WHERE i.instock=1
        AND MATCH(i.description, i.keywords) AGAINST("searchterm")
    ORDER BY score DESC  LIMIT 0, 80

I'm wondering if there is a way to write the query so that the match operation is not preformed twice?

Actually the operation is not performed twice. See the manual:

  The following example is more complex. The query returns the relevance
  values and it also sorts the rows in order of decreasing relevance. To
  achieve this result, specify MATCH() twice: once in the SELECT list
  and once in the WHERE clause. This causes no additional overhead,
  because the MySQL optimizer notices that the two MATCH() calls are
  identical and invokes the full-text search code only once.

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

Update

Notice that you do not need to sort explicitly though:

  When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first.

If you do it anyway, the optimizer will not notice that it is unnecessary and will sort again using filesort, which would add overhead.


SSIS Job "Argument "ORARA********.xxxxxxx.COM" for option "connection" is not valid. The command line parameters are invalid. The step failed. " error My SSIS package is running fine in BIDS but when I tried to schedule the job it's showing:

  Argument "ORARACOCCUAT1.xxxxxxx.COM" for option "connection" is not valid.
  The command line parameters are invalid.
  The step failed.

I'm trying to move data from SQL Server to Oracle. I'm executing the package as well as scheduling the job in same server.

My command line is:

/FILE "C:\Users\..(snip)..\VERIFIERTERMCODE.dtsx"
/CONNECTION "10.226.211.222.KK.*";"Data Source=10.226.211.222;
User ID=*;Initial Catalog=KK;Provider=SQLNCLI10.1;
Application Name=SSIS-VERIFIERTERMCODE-{6B1F6F56-1097-4F40-B681-217B3EB954C1}
10.226.211.222.KK.*;Auto Translate=False;\""
/CONNECTION "ORARACOCCUAT1.xxxxxxx.COM/LSSUATDB.*****";
"\"Data Source=ORARACOCCUAT1.xxxxxxx.COM/LSSUATDB;
User ID=*****;Password=****;Provider=OraOLEDB.Oracle.1;\"" /X86
/CHECKPOINTING OFF /REPORTING E

Steps I tried to fix this issue:

As stated on this site:

  If your SQL Server is 64 bit
  In SQL Server JOB-->Properties-->Steps--->
  In the Execution Option Tab -- Check "Use 32 bit runtime"
  In the Data Sources Tab -- Uncheck the checkboxes of the connection managers if checked.
  Schedule the Job and run it.

...but that is not working for me and showing the same error.

As mentioned on this site, removed hyphen and double quotes before Data source and after Provider but getting same error.

Can anyone help me to fix this?

I fixed this issue by changing the connection name(without space, dot,comma) of both source and destination in Package itself and I tried to run the job manually by clicking Start Job at Step and it worked fine :)


Full Text Search With PostgreSQL i have a table with this rows:

Stickers
------------------------------------------------------
ID | Title                 |Keywords (ts_vector)
------------------------------------------------------
01 | Sticker Case 580H     |'580h':3 'cas':2 'stick':1
02 | Sticker Case 580L     |'580l':3 'cas':2 'stick':1
03 | Sticker Case 580      |'580':3 'cas':2 'stick':1
04 | Sticker Case Plus 3000|'3000':4 'cas':2 'plus':3 'stick':1

Well, when i do search using this script, just row 03 return, how i do return the row 01 and 02?

SELECT
*
FROM
stickers
WHERE
keywords @@@ to_tsquery('case & 580');

In addition to what @swasheck already explained, you'll probably get better performance with LIKE (~~) and ILIKE (~~*) in combination with a trigram GiST or GIN index. You'll have to install the additional module pg_trgm for that. Find details under these related questions:

How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Trigram indexes can be used for basic regular expression matches (~) as well, but LIKE is generally faster.

Create an index like:

CREATE INDEX stickers_title_gin_trgm_idx ON stickers
USING gin (title gin_trgm_ops);

Then this should be very fast:

SELECT *
FROM   stickers
WHERE  title ~~* '%case 580%';

Or (not clear from your question):

SELECT *
FROM   stickers
WHERE  title ~~* '%case%'
AND    title ~~  '%580%';


Join with sqlite on table in android Given a table like:

object    subject    ID      parentID
------    -------   -----    --------
  A         NULL      1          0
  B         NULL      2          0
  C         NULL      3          0
  D         NULL      4          0
  E         NULL      5          0
 Null        CC       6          1
 Null        DD       7          2
 Null        EE       8          2

I need a query with good performance that returns the following result:

result
------
  A
  CC
  B
  DD
  EE

You can use coalesce, it's a function that returns its leftmost non-null argument:

select coalesce(object, subject)
from t;

Coalesce is a shorter form of:

select case when object is not null then object else subject end
from t;


Is better to create a table for each room or make a single table that handles all the rooms in my case? I need to make a server-side for iOS and android application that does something similar to chat(Is not really a chat), and I'm a PHP and MYSQL newbie.
The process is that 2 users join a room and do some stuff like chatting (calculated an avergate of 50-80 messages per room) and after all users disconnect from room, remove it.

I have done it this way:

When user creates a room, I'm creating a table with roomName and users start doing things and when all disconnect, remove the table.

After a chat with a friend, he told me that is ugly to have a database for each room and it will become very slow when many users are connected in the same time and that I should use a single table and indexes to make it faster and salable and that I also must keep a history of conversations to be able to offer to let's say police if there are asked or stuff like that.

The idea seems ok but don't know if it worth modifying code and what I've done until now. Each room user also must make a request each 2-3-4 seconds and ask for new messages(based on a timestamp sent by the user) and I don't know what to do.

My question is:
Is it better to have a single table to manage all the rooms or it's okay to make a table for each room and after disconnecting to move in a single history table?

Thanks

Here's one approach:

create table users
( username ... not null primary key
, <additional attributes>
) engine = innodb;

create table rooms
( room_name ... not null primary key
, creator ... not null
, <additional attributes>
, constraint ... foreign key (creator) references users (username)
) engine = innodb;

create table conversations
( room_name ... not null
, username ... not null
, <additional attributes>
, constraint ... primary key (room_name, username)
, constraint ... foreign key (room_name) references rooms (room_name)
, constraint ... foreign key (username) references users (username)
) engine = innodb;

Assuming users are already created, a user can create a room. A conversation is a room with one or more users. To find out who is having conversation in a room:

select username from conversations where room_name = ?

Whether this is a good approach or not depends on your requirements. Personally I like to write down facts about my business and extract a logical model from that.

I would definitely stay away from changing the model as a consequence of a user action (example create a table for a new room).


AOAG Database went into NOT SYNC\SUSPECT MODE I have SQL Server AOAG 2014 with 3 replicas. 2 Syncronous Commit with Automatic Failover and 1 (BCP) Asynchronos with Manual Failover.

It is a DEV environment and I scheduled log backups every 15 mins.

BCP Replica AOAG Database went into "NOT SNCHRONIZING\SUSPECT" mode.

When I checked the logs it says LOG DRIVE is 100 GB and it is completely occupied with this specific Database Log.

When I checked SYS.DATABASES --> Log_reuse_wait_desc -- I have seen "AVAILABILITY REPLICA".

I tried to backup log of this particular database but still I cannot shrink this particular database.

Finally , I have to re-initialize the database by removing it from AOAG and then by re-adding. then backup log for this particular DB and shrink it . This fixed the issue.

What else can I do for this particular situation.

This situation is caused by the transaction logs not being properly hardened on one of your secondary replicas. This could happen is one of them was offline for a period of time, or was unable to connect correctly.

To help identify the replica having the problem you can query sys.dm_hadr_database_replica_states, in particular pay attention to the log_send_queue_size column.

There are some odd things that happen with AGs sometimes, particulary after a machine has been restarted, whereby one of the databases in the AG will just stop syncing (and there will be no logged errors or issues). Rather than remove it from the AG entirely, and then add it back in you can attempt to restart the hadr_endpoint on the secondary that is being the problem, and then watch to see if traffic starts flowing again.


Permissions to Create DB's and be able to perform any function only on those DB's I am looking for a way to allow users to create databases which only they and SA accounts will have access to (Perform any function; creating/deleteing tables, deleting the DB, etc).

For example, User1 creates DB1 and User2 creates DB2. User1 must have full access to DB1 but no access to DB2 and User2 will have full access to DB2 but no access to DB1.

Thanks

I think you need to clear up the distinction between logins (security entities at the server level) and users (security entities at the database level, which may be associated with a server-level login).

What you want to do here is grant CREATE ANY DATABASE to the server-level login. For example:

CREATE LOGIN foo WITH PASSWORD = N'bar', CHECK_POLICY = OFF;
GO
GRANT CREATE ANY DATABASE TO foo;
GO

Now, log in as foo in another window, and note that they can do this:

CREATE DATABASE foo_1;
GO
USE foo_1;
GO
CREATE TABLE dbo.bar_1(foo_what INT);
GO

But as soon as they try to use a database they did not create:

USE AdventureWorks2014;
GO

They are blocked:

  Msg 916, Level 14, State 1
  The server principal "foo" is not able to access the database "AdventureWorks2014" under the current security context.


Java and SQL error: Unknown column 'newID' in 'where clause' I have a table called colleagues and it has 6 columns: userid, firstname, lastname, title, squad, and email. The code I have typed for updating the first name is

String ID = textID.getText();
String first = textFirstName.getText();
String last = textLastName.getText();
String title = textTitle.getText();
String squad = textSquad.getText();
String email = textEmail.getText();

int newID = Integer.parseInt(ID);
int newSquad = Integer.parseInt(squad);

try {
    if(first != null) {
        String sql = "Update colleagues " + "set firstname = first " + "where userid = newID";
        myStmt.executeUpdate(sql);
    }
    DoConnect();
    JOptionPane.showMessageDialog(NewJFrame.this, "Updated");
} catch (SQLException err) {
    System.out.println(err.getMessage());
}

The DoConnect(); after the if statement is just to update a jtable when I run the program.

You surely want to change this:

String sql = "Update colleagues " + "set firstname = first " + "where userid = newID";

You're writing first and newID as if they were the names of columns... when you want to actually add the actual values of your variables (with adequate escaping)

Check SQL Injection Prevention Cheat Sheet: MySQL Escaping

You're probably be better of with a prepared statement (you'll save yourself of the trouble of escaping, plus you'll have some extra documentation about the types of the parameters, plus you'll avoid risks of SQL injection):

if (first != null) {
    String sql = "UPDATE colleagues SET firstname = ? WHERE userid = ?";
    // Assuming there is a global variable for the connection, named con
    PreparedStatement upd = con.prepareStatement(sql);
    upd.setString(1, first); // replace first ? with value for first name
    upd.setInt(2, newID);    // replace second ? with value for userid
    upd.executeUpdate();
}


Create dump file using Oracle SQL Developer I think I'm missing a simple point, but I couldn't find a way to use Oracle export (exp/expdp) and Oracle import (ipm/impdp) with Oracle SQL Developer (version 1.5.5)? Is there a way for it?

If possible, how can I make it point different Oracle export or import utilities (which are located different places and have different versions i.e. 10g , 11g)?

As far as I know, Import/Export and Data Pump are command-line-only types of tools.  But if you really wanted to use that functionality from within SQL Developer (or SQL Plus), Data Pump uses the built-in DBMS_DATAPUMP and DBMS_METADATA packages.  You should be able to call those packages directly, so I would start there.  There's documentation available in the
Oracle® Database PL/SQL Packages and Types Reference on both of them (DBMS_METADATA and DBMS_DATAPUMP).


Stopping SQL Server Log Reader Agent In 2012 We implement Transactional Replication with 2 publishing servers, 4 publications and 1 subscribing server. I need to shut down our distribution server for several hours. When restarted, will the Log Reader be able to pick back up where it stopped reading the logs before the shutdown?

My thought is that, because transaction log backups will continue on the publishers and clear the logs, the log reader will not have anything to grab.

I believe that my only reliable option is going to be reinitializing each publication and starting over.

Is my understanding of this correct or am I over complicating things?

Thanks!

SQL won't clear transactions from the log that the log reader hasn't marked as done (Committed to Distribution).  So your log may grow while the Distribution server is down, but once it comes back, the log reader should be able to catch up over time.
From BOL:

The Log Reader Agent runs at the Distributor; it typically runs continuously, but can also run according to a schedule you establish. When executing, the Log Reader Agent first reads the publication transaction log (the same database log used for transaction tracking and recovery during regular SQL Server Database Engine operations) and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data in transactions that have been marked for replication. Next, the agent copies those transactions in batches to the distribution database at the Distributor. The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database.
After the entire batch of transactions has been written successfully to the distribution database, it is committed. Following the commit of each batch of commands to the Distributor, the Log Reader Agent calls sp_repldone to mark where replication was last completed. Finally, the agent marks the rows in the transaction log that are ready to be purged. Rows still waiting to be replicated are not purged.


Why do some rows returned by sys.dm_exec_query_profiles have "???" for the physical operator name? SQL Server 2014 introduced the sys.dm_exec_query_profiles DMV which provides a way to monitor real time progress of executing queries. Recently I was looking at a somewhat complex query in SQL Server 2016 SP1 and noticed that sys.dm_exec_query_profiles contained extra nodes that weren't present in the query plan. These nodes had ??? for the physical_operator_name:

For the parallel version of the query there was just one hidden node. The node had an object_id associated with the inner table of a nested loop join. According to the node_id here is where the node should have appeared:

Running the query with MAXDOP 1 resulted in a few more hidden nodes. The ??? node showed up in the same place as before:

There was also a new one not present in the parallel plan:

These only seem to show up around some nested loop joins. I cannot recall seeing this behavior on SQL Server 2014. Unfortunately, the query is complex and I cannot upload an anonymized plan.

What's going on here? Why does sys.dm_exec_query_profiles report extra query plan nodes that don't show up in the graphical plan?

Batch mode adapters (places in a query plan in which row processing switches to batch processing or the other way around) show up as ??? in the DMV with a thread_id of 0. However, the example query doesn't use batch processing so that isn't the cause here.
Nested loops prefetching can also be responsible for extra rows showing up in sys.dm_exec_query_profiles. There is a documented trace flag for disabling nested loop prefetching:

Trace flag 8744 disables pre-fetching for the Nested Loops operator.
Incorrect use of this trace flag may cause additional physical reads when SQL Server executes plans that contain the Nested Loops operator. For more information about the Nested Loops operator, see the "Logical and physical operators reference" topic in SQL Server 2005 Books Online.

If I add a query hint of QUERYTRACEON 8744 to the query then the ??? nodes no longer appear.
For a reproducible example of nested loop prefetching I'm going to borrow Paul White's example against Adventure Works from his Nested Loops Prefetching article:
SELECT TOP (1000)
    P.Name,
    TH.TransactionID
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
WHERE
    P.Name LIKE N'[K-P]%'
ORDER BY
    P.Name,
    TH.TransactionID;

If I run that query against SQL Server 2016 SP1 and quickly capture the output of sys.dm_exec_query_profiles I get the following results:
╔════════════════════╦════════════════════════╦═════════╦═══════════╗
║    OBJECT_NAME     ║ physical_operator_name ║ node_id ║ thread_id ║
╠════════════════════╬════════════════════════╬═════════╬═══════════╣
║ NULL               ║ Top                    ║       0 ║         0 ║
║ NULL               ║ Nested Loops           ║       1 ║         0 ║
║ TransactionHistory ║ ???                    ║       2 ║         0 ║
║ Product            ║ Index Seek             ║       3 ║         0 ║
║ TransactionHistory ║ Index Seek             ║       4 ║         0 ║
╚════════════════════╩════════════════════════╩═════════╩═══════════╝

If I run the same query in SQL Server 2014 I get these results:
╔════════════════════╦════════════════════════╦═════════╦═══════════╗
║    OBJECT_NAME     ║ physical_operator_name ║ node_id ║ thread_id ║
╠════════════════════╬════════════════════════╬═════════╬═══════════╣
║ NULL               ║ Top                    ║       0 ║         0 ║
║ NULL               ║ Nested Loops           ║       1 ║         0 ║
║ Product            ║ Index Seek             ║       3 ║         0 ║
║ TransactionHistory ║ Index Seek             ║       4 ║         0 ║
╚════════════════════╩════════════════════════╩═════════╩═══════════╝

In both cases the nested loop prefetch optimization happens. It appears that only SQL Server 2016 reports it though which could explain why I've never seen this in SQL Server 2014.


Critical query plans removed from sys.dm_exec_query_stats while trivial items are kept Running SQL Server Express 2019.
Have a complicated stored procedure containing 11 sql statements (when selected from sys.dm_exec_query_stats by plan_handle).
9 of those statements are simple variable setting based on passed-in parameters, the last 2 statements (IF/ELSE) do all the work and return data (e.g. query_hash 0x111... and 0x222...).
There's quite a variability in data volume based on input parameters, so I "prime" the stored procedure with optimal values to ensure quick execution.
I've noticed that every few days (sometimes more often), my last 2 statements (query_hash 0x111... and 0x222...) get dumped from sys.dm_exec_query_stats and then re-generated based on whatever parameters come from the users on the next stored procedure call.
I know this by looking at creation_time column, and it's reflecting the time of "priming" for first 9 queries, and a later time for query_hash 0x111... and 0x222... .
From what I understand, cached plans get cleared under memory pressure, and less used plans get cleared first. My queries are being used a lot, and I have a lot of other cached plans with 1 execution that stick around for a while.
Question: why are my most used queries get dumped form cache and how do I stop it from happening?

There are a bunch of different reasons why a statement might be recompiled, including both performance and correctness-related reasons.
For instance, an index used by the query may have been dropped, in which case the plan must be recompiled because it genuinely can't be run in that state.
Or statistics may have been updated enough that an automatic recompilation occurs.
Depending on the load on your server, and how frequently this statement really gets recompiled, you might be able to capture the actual reason using this extended events session:
CREATE EVENT SESSION [recompiles] ON SERVER
ADD EVENT sqlserver.sql_statement_recompile
(
    WHERE ([sqlserver].[query_hash]=(111))
)
ADD TARGET package0.event_file (SET filename=N'recompiles')
WITH (STARTUP_STATE=OFF)

This will fire for every recompile on the system, but will only store the ones with a query hash that matches what you put in the filter.
To be clear: don't start this thing up and then leave for the weekend.  Try it on a test system, try it briefly on production and make sure it's not too disruptive, etc.  Don't take down your server just to track this down
In case it's helpful, the XE defines all of these statement recompile reasons on SQL Server 2019:
SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = N'statement_recompile_cause';

map_value

Schema changed

Statistics changed

Deferred compile

Set option change

Temp table changed

Remote rowset changed

For browse permissions changed

Query notification environment changed

PartitionView changed

Cursor options changed

Option (recompile) requested

Parameterized plan flushed

Test plan linearization

Plan affecting database version changed

Query Store plan forcing policy changed

Query Store plan forcing failed

Query Store missing the plan

Interleaved execution required recompilation

Not a recompile

Multi-plan statement required compilation of alternative query plan


Large table and poor performance - what to do next? We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical data. The table is currently 101 million rows of 35 columns, and growing at the rate of around 250,000 rows a day. Splitting the table into smaller tables unfortunately isn't really an option due to a mass of legacy code.

The database itself (around 100Gb) is held as a single file on a single SSD drive. The server has another two 10K SAS disks used for the boot OS, paging etc, and the server has 22Gb of RAM.

Although everything's running fine, we have a few dozen users who need to query the data in this table. We have limited control over what these queries do: sometimes it's pulling a few hundred rows from yesterday, at other times it's tens of thousands of rows from 6 months ago. 99.9% of the activity is reading rows; there is very little writing apart from the live data being INSERTed throughout the day. At peak times, simple queries that return a lot of data can take half an hour or more to complete.

We have indexes in place that are helping, but the ultimate bottleneck appears to be disk I/O. The SSD in place isn't the fastest, and as a result we're looking at retrofitting a RAID1+0 array of high-end SSD drives to increase performance (we've checked the array card can handle the throughput).

Assuming we have this array in place, what is the best plan to increase read throughput to this database? If we have a super-fast SSD array, is that enough? Or would partitioning the database into separate files on separate logical drives be a better idea, even though they're essentially destined for the same disks? Similarly, would splitting database and log files across logical drives in the same array make any difference?

If you were using Enterprise Edition, I would recommend partitioning. Separate files on separate logical drives is unlikely to provide much help, since the data is all going through the same controller and writing to the same underlying disk (and without partitioning, you don't have much control over proportional fill, meaning many queries will likely still have to hit several or all files anyway).

Since you're not using Enterprise Edition, one alternative would be to split the data into multiple tables, and then have views that combine them. Assuming the older data no longer gets updated, you could even put it on read-only filegroups, which will alleviate some resource contention. You can make the query logic as complicated and picky as you want; for example, the simplest solution would be to just issue all queries against the view, but your data access layer or stored procedure logic could - based on the date range parameters - decide which table(s) to access at runtime. You can even use filtered indexes so that certain views will pull a very limited subset of data even from the subset that exists in its own table. This isn't necessarily an easy button but I can elaborate on some of these details if you think you may be interested in putting in some elbow grease to implement something.

The cheapest fix, of course, would be to throw hardware at the problem: specifically, such that more of your database will fit into memory. RAM is cheap and assuming your box supports it I would say that an upgrade from 22 GB (what an odd number?) to 128 64 GB will go a long way. (And if the OS is Standard also, even 32 GB might help, but not as much.) While I'd love to see an actual execution plan and statistics I/O metrics for a query that takes half an hour to return from an SSD, if a larger portion of the data is in memory it will be that much faster.


Temporarily removing replication from Publisher Is it possible to temporarily remove Transactional Replication from a publisher database in order to allow a release to be executed against the database?

If I stopped the log reader agent would I be able to perform DDL/DML changes against the publisher database without error?

The product vendor has a bespoke tool which issues the T-SQL commands against the database for the release, which will include dropping and recreating constraints, keys, indexes etc. I need to make sure releases can happen without error caused by replication - the database needs to allow the changes.

Publisher and Distribution db live on the same server. Both publisher and subscriber are SQL 2014 Enterprise. I am replicating the entire database.

Help is appreciated.
Thanks
Peter

Update: Yes I am replicating schema changes

Update: Is this the answer? https://stackoverflow.com/questions/15673829/resume-replication-after-restoring-publication-database

Update: I've tested our vendor's release on a published database and found that dropping stored procedures is the first issue we see.

This seems to be the best method: Script the Publication -> Drop Subscriptions -> Drop Publication -> Re-create them

I was hoping there would be a 'trick' with replication where I could leave subscribers in place and just script out the publisher and recreate it. I don't see a clear way to restart replication without doing a full rebuild/reinitialise.

What I have noted is that this method does work pretty well once you have established replication for a database, worked out all the issues, scripted it all out; running those scripts does simplify the process.

Hope this helps someone


How to make column data in upper case by default in SQL Server 2008? Normally in SQL, select and insert statements use UPPER() and LOWER() functions to format the column cases. But in this application, whenever new data is inserted it must be stored as UPPER CASE by default.

Example:

insert --> INSERT
Apollo --> APOLLO
TyUiOP --> TYUIOP

You could use a trigger to accomplish this but why not deal with this in the front-end of the application? Or, if you're displaying the data somewhere else (such as a report), set the case there?

Why do you want the data stored in a particular case?


How to specify a display name in emails from SSRS subscriptions? How can I specify a display name (e.g. "Reporting Server" <noreply@domain.com>) in the emails sent from SSRS subscriptions?

This will need to be set up in Exchange Properties for the email address which you specify in the Reporting Services Configuration Manager


Error on SQL Server 2000 backup (.bak file) restored on SQL Server 2012 When I try to restore a database in my new SQL Server 2012 instance I get this error :

  Restore of database 'MyDb' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  System.Data.SqlClient.SqlError: The database was backed up on a server running version 8.00.2055. That version is incompatible with this server, which is running version 11.00.2218. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)

Can you help me?

The database is a SQL Server 2000 database (version 8.00.2055) - and you cannot directly update a SQL Server 2000 database to SQL Server 2012.

You'll need to take an interim step first (restore it in SQL Server 2008, back it up from that server again).

See the relevant MSDN documentation:

  In SQL Server 2012, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version.

but SQL Server 2000 is NOT supported.


How can I fix "Login failed for user sa" error in SQL Server? I can access my databases through SQL Server Management Studio (SSMS) by using my Windows account no problem.
I have two issues though:

I want to enable sa and then try to log in using sa and the password. I get this error:

I enabled the SQL Server authentication:

I tried to create a new user, but that user cannot connect either:

How can I fix this?

In SSMS, right-click on the server and go to Properties and under the security page, check what Server authentication looks like. Odds are it's set to Windows Authentication only and needs to change if you want to use SQL Server users:


Is it possible to use TSQL differential backups on a system that uses VSS? Our colocation provider uses VSS to backup our dedicated server.  The backups show up in the SQL logs as SQL backups even though they are not and that breaks the Full-Diff chain.  The issue is well documented on StackExchange Database Administrators, but they all stop there.

Is it possible to do TSQL differential or incremental backups on a system that also uses VSS?

If not, is there a better approach for off-site backups assuming that we don't want to lose the onsite full-system VSS backups?

SQL Server 2012 SP3 if that matters.

It seems that there are two primary solutions to allow differential SQL backups on a server that does VSS backups.

1) On the full server backup, use COPY_ONLY if that is available.

2) Disable the "SQL Server VSS Writer" service.

Some full system backups using VSS record a backup to get SQL Server to pause long enough for a backup. This pause is recorded as if it were a backup but the file is never created.

In our case, disabling the SQL Server VSS Writer service seems to be the best approach.  Since the database is not quiesced during the system backup, we assume that the database will not be in a valid state after a full system restore and we will have to restore our regular SQL Server backups at that time.

More information can be found at:

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?
How can I fix "Cannot perform a differential backup ... a current database backup doesn't exist"?

“Cannot perform a differential backup for database ”myDb“, because a current database backup does not exist.”
https://serverfault.com/questions/147459/

Quiesce operations and SQL backups https://nixx.ca/wiki/doku.php?id=vmware:quiesce_sql

Cannot perform a differential backup because current backup does not exist
https://sqlbackupandftp.com/blog/cannot-perform-a-differential-backup-database-current-database-backup-exist/

SQL differential backups failing after windows server backup https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35080750-ad5d-4841-b2ba-e17ee5a799d5

SQL Server records a backup operation in the backupset history table when you use VSS to back up files on a volume https://support.microsoft.com/en-us/kb/951288

Error message when you try to restore a database by using SQL Server Management Studio in SQL Server 2005 after you use the Backup tool: "Restore failed for Server '' (Microsoft.SqlServer.Smo)" https://support.microsoft.com/en-us/kb/937683


Inserting data from one table, then taking the primary key of that insert and put it into another table I have a quick question. I have a table users

 -----------------------------
| ID | first_name | last_name |
 -----------------------------

and another table like users.login

 ------------------
| users_ID | EMAIL |
 ------------------

My id's in the first table are auto incrementing from a certain point, say 160,000. How do I take the id from the first table, save it to a value, and then put that value into the id of the 2nd table? The id in users.login is foreign key to users.id.

So far i have

INSERT INTO public.users (first_name, last_name)
                VALUEs ($1,$2) RETURNING ID;
INSERT INTO public.users_login(email, ID)
                Values($1, id)

does this work? I'm new to postgres.

Consider using the RETURNING clause on an INSERT or UPDATE statement to insert the foreign key.  From the PostgreSQL docs:
WITH upd AS (
  UPDATE employees
  SET sales_count = sales_count + 1
  WHERE id =
    (
       SELECT sales_person
       FROM accounts
       WHERE name = 'Acme Corporation'
    )
    RETURNING *
)
INSERT INTO employees_log
SELECT *, current_timestamp FROM upd;

So, your statement would need to do the initial insert in common-table-expression, which returns the id to allow it to be inserted into the users_login table:
CREATE TABLE users (
      user_id serial
    , first_name varchar(30)
    , last_name varchar(30)
);

CREATE TABLE users_login (
      user_login_id serial
    , email varchar(30)
    , user_id int
);

WITH ins AS (
    INSERT INTO users (first_name, last_name)
                VALUES ('hannah', 'vernon')

    RETURNING user_id
)
INSERT INTO users_login(email, user_id)
SELECT 'us@them.com', user_id
FROM ins

Querying the two tables:
SELECT *
FROM users u
    INNER JOIN users_login ul ON u.user_id = ul.user_id;

╔═════════╦════════════╦═══════════╦═══════════════╦═════════════╦═════════╗
║ user_id ║ first_name ║ last_name ║ user_login_id ║    email    ║ user_id ║
╠═════════╬════════════╬═══════════╬═══════════════╬═════════════╬═════════╣
║       1 ║ hannah     ║ vernon    ║             1 ║ us@them.com ║       1 ║
╚═════════╩════════════╩═══════════╩═══════════════╩═════════════╩═════════╝


Cannot run psql in postgresql 9.5 I am using Postgresql 9.5 on Ubuntu 16.04 LTS.
I receive the below error when I type psql:
a

psql: could not connect to server: No such file or directory
  Is the server running locally and accepting
  connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

On checking the logs in /var/log/postgresql/postgresql-9.5-main.log, I see the error as:

  2018-11-26 13:17:41 IST [3508-1] FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied

Below are the permissions of etc/ssl/private and ssl-cert-snakeoil.key files:

vivek@vivek-ThinkPad-E480:~$ ls -l /etc/ssl
total 36
drwxr-xr-x 2 root root     20480 Nov 22 13:06 certs
-rwxr-xr-x 1 root root     10835 Dec  8  2017 openssl.cnf
drwxr--r-- 2 root ssl-cert  4096 Nov 22 13:06 private

vivek@vivek-ThinkPad-E480:~$ sudo ls -l /etc/ssl/private
total 4
-rw-r----- 1 root ssl-cert 1704 Nov 22 13:06 ssl-cert-snakeoil.key

Can anyone guide me in the right direction in setting permissions?

UPDATE #1: postgres user is also added to the group ssl-cert.

vivek@vivek-ThinkPad-E480:~$ getent group ssl-cert
ssl-cert:x:112:postgres

UPDATE #2: I found that there is no server.key present in /var/lib/postgresql/9.5/main.

The permissions on "/etc/ssl/private" should be "drwx--x---" (aka "0710"), which should have been the permissions it came with.  I don't know it got changed.  You could restore it with:

sudo chmod 0710 /etc/ssl/private/

Although maybe there is also a way to do it with your package manager.

You don't need a server.key file in "/var/lib/postgresql/9.5/main", because postgresql is configured to look for it in "/etc/ssl/private/ssl-cert-snakeoil.key" instead.  You can make or get your own cert and put in "/var/lib/postgresql/9.5/main", but then you will have to change the configuration to look for it there.


log_min_duration_statement setting is ignored I am running Postgresql 9.1 on Ubuntu. Exact Postgresql version is 9.1+129ubuntu1 as my package manager shows.

I have 2 databases that are actively in use and they are used from a remote server.

I wish to log queries that have a long execution time. So I set following parameters in /etc/postgresql/9.1/main/postgresql.conf file

log_min_duration_statement = 10000
log_statement = 'mod'

so Postgresql will log queries that takes longer than 10 seconds.

But when I reload the postgres configuration, Postgresql begins to log every query that fits log_statement value. That I set  duration to 100 seconds to be sure

log_min_duration_statement = 100000

But Postgresql keep logging every query that fits log_statement value, regardless of log_min_duration_statement value.

Setting log_statement to none seemed to stop logging.

Is there something that I missed about the configuration?

You were close. Your last idea is actually the way to go:

log_statement = none
log_min_duration_statement = 10000

Then no statement will be logged, except those running longer than 10 seconds - including the query string itself. Logging may have seemed to stop because 10 seconds is a high threshold. I am using 2 seconds normally, but YMMV.

This related answer on SO has more:
Cannot get log_min_duration_statement to work


SQL Server 2012 AlwaysOn Availability Group active/passive I have one Primary and one secondary replica on WSFC and AAG configuration. Both nodes are physical machines and sync with automatic fail over.

The secondary replica is readable and we run maintenance job on it but now we want to keep the "Readable Secondary" setting to "NO" instead of "Readable" and move the maintenance jobs to Primary replica so the secondary replica becomes pure passive node instead of active which saves licensing cost.

My question is - Does changing the readable secondary replica setting to "NO" will make it passive node please?

You have to do two things

ALTER AVAILABILITY GROUP group_name SECONDARY_ROLE ALLOW_CONNECTIONS =  NO

The above will not allow connections to secondary. So your secondary will be TRUE standby and will be used when you do a failover.

ALTER AVAILABILITY GROUP group_name AUTOMATED_BACKUP_PREFERENCE = 'PRIMARY'

The above will indicate that your maintenance especially, backups will always happen on PRIMARY replica.

Since we are talking about maintenance, its worth mentioning the Recommendations for Index Maintenance with AlwaysOn Availability Groups


Does enabling LPIM work even if the memory pressure is coming from SQL Server itself I have a scenario where I have 128 GB Physical memory for the SQL Server 2016 machine.
It is a SQL Server dedicated machine.
I have 110 GB allocated to SQL Server itself.

I have performance issues when the CPU usage is high or the no of connections go high. It is normal for the CPU usage to go high as there are more request coming in with more connections.
I looked at the RING BUFFERS,plan cache etc. I see that plan cache gets cleared often when i have more usage. Also RING BUFFER indicated memory pressure. I see that for most of the cases IndicatorProcess=2 which means SQL server internally detected memory pressure. On few occasions it is IndicatorSystem = 2.

I have been struggling with this for sometime now. I have tuned the frequently executed queries and most of them runs fast when there are plans available in plan cache.
My Question here is :

Does enabling LPIM help in this scenario since i see
IndicatorProcess=2 in most of the cases.
I already have 128GB RAM.Is that the maximum i can have for SQL Server 2016 Standard.?

Additional Info

Here is what the monitoring tool has to say for the last one hour.

Does enabling LPIM help in this scenario since i see IndicatorProcess=2 in most of the cases.

No.

  I already have 128GB RAM.Is that the maximum i can have for SQL Server 2016 Standard.?

Yes.

And

  I see that plan cache gets cleared often when i have more usage
  and
  most of [the frequent queries] runs fast when there are plans available in plan cache

Suggests you should focus on your plan cache.  Are there a large number of single-use plans?  If so consider setting the optimize for ad hoc workloads configuration option.


Which privileges for MySQL extended status? If I want to execute "SHOW STATUS" within MySQL or use "mysqladmin extended-status" on command line with a non-root user, which privileges would this user then need?

Taken from the MySql documentation:

  This statement does not require any privilege. It requires only the
  ability to connect to the server.


Get few ip postgresql slave from pg_stat_replication using non-superuser I try Get few ip postgresql slave from pg_stat_replication using non-superuser

CREATE OR replace FUNCTION client_addr_pg_stat_replication3
  () returns setof RECORD
AS
  $x$
  DECLARE
    rec RECORD;
  BEGIN
    SELECT client_addr
    FROM   pg_stat_replication
    INTO   rec;

  END;
  $x$ LANGUAGE plpgsql security definer;

GRANT EXECUTE ON FUNCTION client_addr_pg_stat_replication3(SETOF record) TO non-superuser;

select * from client_addr_pg_stat_replication3() as x(a int, b int);
ERROR:  function client_addr_pg_stat_replication3() does not exist
LINE 1: select * from client_addr_pg_stat_replication3() as x(a int,...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Second function

CREATE OR replace FUNCTION client_addr_pg_stat_replication4
  () returns TABLE (client_addr cidr)
AS
  $func$
BEGIN
  RETURN query
  SELECT client_addr
  FROM   pg_stat_replication;

END $func$ LANGUAGE plpgsql security definer;

GRANT EXECUTE ON FUNCTION client_addr_pg_stat_replication4() TO non-superuser;

select * from client_addr_pg_stat_replication4();
ERROR:  column reference "client_addr" is ambiguous
LINE 1: SELECT client_addr FROM pg_stat_replication
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT client_addr FROM pg_stat_replication
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication4() line 1 at RETURN QUERY

You need a view not just function, e.g.

CREATE OR replace FUNCTION public.pg_stat_repl() returns setof
  pg_catalog.pg_stat_replication
AS
  $$begin
    RETURN query
      (
       SELECT *
       FROM pg_catalog.pg_stat_replication);
  end$$ LANGUAGE plpgsql security definer;

CREATE VIEW public.pg_stat_repl AS SELECT * FROM public.pg_stat_repl();

GRANT SELECT ON public.pg_stat_repl TO non-superuser;

Then you can as non-superuser

SELECT * FROM public.pg_stat_repl;


Best approach for querying multiple tables In SQL Server, I have a table, let's call it Source. The structure is

ID | SourceValue
-----------------
1  |  'value1'
2  |  'value1'
3  |  'value2'

and so on.

ID is a primary key and SourceValue is not unique.

I also have several other tables which have SourceValues in their records. As it's not unique, there is no Foreign Key constraint. This column also isn't UNIQUE in other tables. Furthermore, these columns can hold values both of SourceValue format and other formats. Because of this, I need to include a DISTINCTed SourceValue data in the query.

I need to return COUNT of every occurence of SourceValue in all tables other than Source.

Our approach was to create a view which holds DISTINCT SourceValues from the Source table, and then do the following:

SELECT q.SourceValue, SUM(q.Count) as Count
FROM (
  SELECT dvw.SourceValue, COUNT(dvw.SourceValue) AS Count
  FROM distinctValuesView AS dvw
  JOIN DependantTable1 dt1
  ON dvw.SourceValue = dt1.SourceValueColumn
  GROUP BY dvw.SourceValue

  UNION

  SELECT dvw.SourceValue, COUNT(dvw.SourceValue) AS Count
  FROM distinctValuesView AS dvw
  JOIN DependantTable2 dt2
  ON dvw.SourceValue = dt2.SomeOtherColumn
  GROUP BY dvw.SourceValue
) AS q
GROUP BY q.SourceValue

distinctValuesView is defined as

SELECT DISTINCT SourceValue FROM Source

The problem is there are more than 20 DependantTables (transaction tables) and all these tables have tens of thousands of rows, with the Source table having more than 20.000 rows.

Naturally, this query is unacceptably slow (2 seconds execution time). Is there any other approach, or a way to speed it up?

I'm not sure why you join your dependent tables with the distinctValuesView. Is it to rule out sourceValues that does not exist in Source? In that case you may try to use EXISTS instead:

SELECT q.SourceValue, SUM(q.Count) as Count
FROM (
    SELECT dt1.SourceValue, COUNT(*) AS Count
    FROM DependantTable1 dt1
    WHERE EXISTS (
        SELECT 1 FROM Source s
        WHERE s.SourceValue = dt1.SourceValueColumn
    )
    GROUP BY dt1.SourceValue

    UNION ALL

    ...
) q
GROUP BY q.SourceValue

Note that I changed UNION to UNION ALL. I assume that if you have 4 occurrences of 'value1' in DependantTable1 and 4 occurrences in DependantTable2 the sum should be 8. If you use UNION the result will be 4

If most of your SourceValue exists in Source, you may try to apply the union first:

SELECT q.SourceValue, COUNT(*)
FROM (
    SELECT dt1.SourceValue
    FROM DependantTable1 dt1
    UNION ALL
    SELECT dt2.SourceValue
    FROM DependantTable1 dt2
) q
WHERE EXISTS (
    SELECT 1 FROM Source s
    WHERE s.SourceValue = q.SourceValueColumn
)
GROUP BY q.SourceValue


Connect to SQL Server with Windows Authentication in a different domain I am trying to connect to a remote SQL Server on a VPN in a different domain. When I enter the Server name on the SQL Server and choose Additional Connection Parameters to add some extra stuff needed by my school:

Integrated Security=SSPI; User ID=DOMAIN\username; Password=Password

I get the following error:

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

There is another way, which I now use in preference to the runas /netonly method.
You can add the credentials to your profile in Windows using the Credential Manager found in the Windows control panel.

Open Credential Manager

Click "Add A Windows Credential"

Populate the "internet or network address" field with the name and port number of the SQL instance you wish to store credentials for.
Example: UniServer:1433 (1433 is the default port, you may need a different port, especially if you are connecting to a named instance)

Populate the "User Name" (don't forget to include the domain e.g. MYDOMAIN\MYUSER)

Populate the "Password"

Click OK

If you have the server name, port and login details correct, you should now be able to use Windows Authentication from most client tools, SSMS, Excel, whatever. They will all use the stored credentials.
Tip: Sometimes you need to use the FQN for the server when adding the credentials. e.g. UniServer.UniDomain.org:1433, it all depends on your network specifics.
Here is a quick demo of the method : http://youtu.be/WiVBPsqB9b4
It is a screen grab of me attempting (and failing) to connect to a SQL Server running in a VM from my desktop, then adding the required credentials and trying again - successfully.
Tip: use the "cmdkey /add" command to script creating and updating stored credentials.


Why does replication with SSL fail on my MySQL database in AWS RDS? I'm trying to replicate from AWS RDS to my own server. It works without SSL. Whenever I include the SSL property to the slave, it breaks with this error:

  error connecting to master
  'user@xxxxx.us-west-2.rds.amazonaws.com:3306' - retry-time: 60
  retries: 86400

I can log in with SSL to RDS using mysql client without problems:

mysql -h xx.rds.amazon -u user -p --ssl-ca=rds-ca-2015-root.pem --ssl-verify-server-cert

This is the STATUS:

     Slave_IO_State: Connecting to master
                  Master_Host: xxxxx.us-west-2.rds.amazonaws.com
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.001453
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin-changelog.001453
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: DB
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ssl/rds-ca-2015-root.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2026
                Last_IO_Error: error connecting to master 'user@xxxx.us-west-2.rds.amazonaws.com:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:

Again, I want to highlight that REPLICATION works well without using SSL, and SSL works well without using REPLICATION.

You may want to check apparmor/selinux configuration.

Another option is to move your certs to a location already allowed (e.g. /var/lib/mysql).


Getting the next bigserial value in PostgreSQL Say I have a table with an id BIGSERIAL column. I just started a new session. I want to know what the id of the next row I insert will be. How can I tell this?

I can't simply SELECT MAX(id) FROM my_table (and return 1 if none found) because if I previously deleted things, the next id will not be 1 but will be higher.

I've already looked at other answers that use currval() and nextval(). They don't work unless I inserted within the current session... And I don't want to insert a row and see what ID I got, haha.

I'm about to lock the table and insert a lot of rows from a Python script on a client that is also keeping track of row IDs in a dictionary. The row IDs must match up, so I need to know what the first one will be. I'm surprised nobody else is dealing with this scenario.

Edit: I was wrong about the functions not working unless I inserted within the current session, but see the comments below.

I want to know what the id of the next row I insert will be. How can I tell this, without inserting a row?

This is exactly what nextval() does. But I can't think of any useful thing to do with this value, if you are not inserting into the table. It will be the id value of a row that may never be inserted:

  Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

As stated above, the value returned by nextval() is hardly ever useful, unless you insert into the table. But if you are inserting some rows and you simply want the id values of the serial, you can simply use the RETURNING clause in the INSERT:

insert into t (col1, col2, ...)
values ( (value_a_col1, value_a_col2, ...),
         (value_b_col1, value_b_col2, ...),
         ...
       )
returning t_id ;

See an example at SQLfiddle.

Some more complicated cases - like when the ids are needed for inserting into more tables, related through foreign keys, or when an UPSERT is need - can be written in one statement, using RETURNING and data-modifying CTEs.
Note that UPSERT has been implemented in 9.5, with the INSERT ... ON CONFLICT DO NOTHING/UPDATE construct.
Examples below:

How to Choose Between VALUES and SELECT for INSERT?
Idiomatic way to implement UPSERT in PostgreSQL
Inserting into related tables
PostgreSQL multi INSERT…RETURNING with multiple columns
Bulk insert in multiple tables

If you want the id values to be used in different columns, see this:

Reference value of serial column in another column during same INSERT


INSERT INTO SELECT causing data to double I have strange issue. I am working in a Teradata database.
In a procedure I am populating a main table by processing data via multiple intermediate tables.
But in the end only one table is populating the main table.
The syntax is something like this

INSERT INTO sch_1.main_table
(
col1,
col2...
coln
)
SELECT
col1,
col2...
coln
FROM sch_1.work_table;

Now the count(*) from sch_1.work_table results in 112rows whereas its going straight to double in sch_1.main_table i.e. 224 rows.

I am unable to share the logic of the procedure as its for a client.
But this thing has me stumped.
There are no joins involved in this statement, just a plain and simple insert from a work table to a main table.

Has anyone faced this?

Might be caused by a trigger on the target table


restore mongodb sharded cluster to another server MongoDB version is 2.4.12
Production Server has 3 shards and each shard have another 2 replica sets.
Config server & router also runs on these servers.

Backup procedure :
1. Shutdown one of replica set on each shard.
2. We use mongodump to backup config server.
3. Cold backup od /data & /log folders.

Restore Procedure :
Restore will happen on only 3 servers so there will not be any replica sets.
Server name where it is getting restored, is different than where backup happend so server name is going to change.

Can someone put high level steps how do I restore ?
** Restore Config server from dump ( Is this 1st step)
** Copy all /data and logs to these three servers.
** Is there anything I need to change in config server or any other settings since Production server did have replica set configuration but this new server will not have replica set.

Thanks

Restore Config Server using mongorestore.
Restore data backup for each shards.
Change Config settings. use config and see db.shards.find(), you will see setting as related to production server, which you need to update it based on new server settings.


Backing up and restoring all databases in MySQL on linux I plan to dump all databases on one computer and restore them on another using the following 2 commands:-

mysqldump -u username -p --all-databases > dump.sql

mysql -u username -p < dump.sql

My query is : will the second command overwrite an existing table with the same name as on the first computer ? Also, will the second command delete all tables which are NOT there on the first computer?

Can someone please clarify?

The exact syntax used varies depending on what version of MySQL you are using, but the basic version of mysqldump (without any other options) you are using will run:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /;

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (. . .

You can check this if you are concerned, before running the second statement, simply look in the file dump.sql (it is just a text file) and you can see clearly what queries it will run.

So to answer your questions:

  will the second command overwrite an existing table with the same name as on the first computer ?

Yes it will

  will the second command delete all tables which are NOT there on the first computer?

No it won't

You can get a full list of the options available from: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-option-summary


Is using dbms_utility.format_error_stack to filter errors in the exception block a bad idea? I'm trying to handle two different exceptions could trigger in an external table that reads a CSV.

The code works great, but I'm concerned I'm doing it wrong.

SET SERVEROUTPUT ON;
DECLARE
  records INT := 0;
  external_table EXCEPTION;
  PRAGMA EXCEPTION_INIT(external_table, -29913);
BEGIN
  SELECT COUNT(*) INTO records FROM ext_testing;

  EXCEPTION
    WHEN external_table THEN
      IF dbms_utility.format_error_stack LIKE '%KUP-04040%' THEN
        RAISE_APPLICATION_ERROR(-20000, 'Missing CSV file!');
      ELSIF dbms_utility.format_error_stack LIKE '%ORA-30653%' THEN
          raise_application_error(-20001, 'Invalid UTI report, check for missing fields!');
      END IF;
    WHEN others THEN
        dbms_output.put_line('An exception occurred');
        dbms_output.put_line(SQLCODE || '=>' || SQLERRM);
END;

I would suggest two small modifications:

re-raise the exception in the WHEN OTHERS exception handler. Not raising the exception will make the calling application think that the procedure did succeed. Or better yet don't use WHEN OTHERS: there's no point in catching an exception if you do nothing but re-raise it. Let the natural exception bubble up.
add an ELSE clause in the first exception handler just to be sure that you don't silently ignore a new type of error that doesn't get catched by the two IF conditions.

Aside from that, I don't see anything wrong with using dbms_utility.format_error_stack in an exception clause (in fact you can't use it anywhere else).

EXCEPTION
   WHEN external_table THEN
      IF dbms_utility.format_error_stack LIKE '%KUP-04040%' THEN
         RAISE_APPLICATION_ERROR(-20000, 'Missing CSV file!');
      ELSIF dbms_utility.format_error_stack LIKE '%ORA-30653%' THEN
         raise_application_error(-20001, 'Invalid UTI report, check for missing fields!');
      ELSE
         raise_application_error(-20002,
                                 'An exception occurred: ' || SQLERRM,
                                 TRUE);
      END IF;
   -- WHEN OTHERS THEN -- No point really
   --    raise_application_error(-20003,
   --                            'An unknown exception occurred: ' || SQLERRM,
   --                            TRUE);
END;


jsonb_array_elements() in the select seems to turn a left join into an inner join PostgreSQL 9.6.

create table jon.vins (vin citext primary key);
insert into jon.vins values
('3GNAXUEV1KL221776'),
('3GNAXHEV2KS548975');

CREATE TABLE jon.describe_vehicle (
  vin citext primary key,
  response jsonb);

jon.describe_vehicle contains data for only 1 vin, 3GNAXHEV2KS548975;

This query:

    select a.vin,
      b.response->'attributes'->>'bestMakeName' as make
    from jon.vins a
    left join jon.describe_vehicle b on a.vin = b.vin;

Returns what I expect, one row for each vin in jon.vins:

        vin        |   make
-------------------+-----------
 3GNAXUEV1KL221776 |
 3GNAXHEV2KS548975 | Chevrolet
(2 rows)

But this query:

    select a.vin,
      jsonb_array_elements(b.response->'style')->'attributes'->>'name' as style
    from jon.vins a
    left join jon.describe_vehicle b on a.vin = b.vin;

Returns:

        vin        |      style
-------------------+------------------
 3GNAXHEV2KS548975 | FWD 4dr LS w/1LS
(1 row)

It is as if the jsonb_array_elements in the select turns the left join into an inner join.

I expected to see a row for vin 3GNAXUEV1KL221776 with a null value for style.

What am I doing wrong?

Set returning functions should be put into the from clause. Putting them into the SELECT clause is allowed, but can result in strange result (as you have noticed).

The clean approach would be:

select a.vin, r.data ->'attributes'->>'name' as style
from vins a
  left join describe_vehicle b on a.vin = b.vin
  left join jsonb_array_elements(b.response->'style') as r(data) on true

Online example: https://rextester.com/RGY32895


How to list all grants per user/role on PostgreSQL I've run these statements on Postgres CLI (I'm using PostgreSQL v13.1):
CREATE ROLE blog_user;
GRANT blog_user TO current_user;

And I created a function
CREATE FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT)
RETURNS jwt_token AS
$$
DECLARE
  token_information jwt_token;
BEGIN
....
END;
$$ LANGUAGE PLPGSQL VOLATILE SECURITY DEFINER;

Finally I granted a permission:
GRANT EXECUTE ON FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT) TO anonymous;

I wish to list all grants per user/role in my schema/database. \du and \du+ show basic information, which does not contain info about the grant (execute on function) made recently.

While the following is not a complete solution (column privs aren't included, it doesn't have the function signatures) you should hopefully be able to get most of what you're asking for using:
SELECT rug.grantor,
        rug.grantee,
        rug.object_catalog,
        rug.object_schema,
        rug.object_name,
        rug.object_type,
        rug.privilege_type,
        rug.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_usage_grants rug
    WHERE rug.object_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rtg.grantor,
        rtg.grantee,
        rtg.table_catalog,
        rtg.table_schema,
        rtg.table_name,
        tab.table_type,
        rtg.privilege_type,
        rtg.is_grantable,
        rtg.with_hierarchy
    FROM information_schema.role_table_grants rtg
    LEFT JOIN information_schema.tables tab
        ON ( tab.table_catalog = rtg.table_catalog
            AND tab.table_schema = rtg.table_schema
            AND tab.table_name = rtg.table_name )
    WHERE rtg.table_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rrg.grantor,
        rrg.grantee,
        rrg.routine_catalog,
        rrg.routine_schema,
        rrg.routine_name,
        fcn.routine_type,
        rrg.privilege_type,
        rrg.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_routine_grants rrg
    LEFT JOIN information_schema.routines fcn
        ON ( fcn.routine_catalog = rrg.routine_catalog
            AND fcn.routine_schema = rrg.routine_schema
            AND fcn.routine_name = rrg.routine_name )
    WHERE rrg.specific_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rug.grantor,
        rug.grantee,
        rug.udt_catalog,
        rug.udt_schema,
        rug.udt_name,
        ''::text AS udt_type,
        rug.privilege_type,
        rug.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_udt_grants rug
    WHERE rug.udt_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND substr ( rug.udt_schema, 1, 3 ) <> 'pg_'
        AND grantor <> grantee ;


How can I cache queries in sqlalchemy just like I do it in mysql by using SQL_CACHE keyword? I want to cache sqlalchemy,i have discovered that using keyword SQL_CACHE in mysql can be helpful in caching queries on demand.
But how do i do it in sqlalchemy ?
Is it possible ?

You need use a prefix_with.
For example, this:

query = session.query(User.name).prefix_with('SQL_CACHE')

would render:

SELECT SQL_CACHE users.name AS users_name FROM users


Table with two customer IDs that I want to relate to the entity Let's say I have a transaction where Party1 and Party2 are associated, eg by a transaction - let's say Party1 sells something to Party2.

I have a list of transaction IDs, eg transaction1 is Party1 sells goods to Party2, transaction2 is Party3 sells goods to Party4 and so on.

I have another table, Entities, that tells me that Party1 is 'Smith Enterprises' and Party2 is 'Jones Corp' etc.

I want to create a query where Access puts the name of the party in for both parties, eg the transaction now says 'Smith Enterprises' sells goods to 'Jones Corp'.

I can't figure out how to get a query to look up Party1 and Party2 in Entities and insert the entity name for both. I can do them one at a time and create a list of identified sellers or a list of identified buyers, then join them back together, but I feel sure there must be a more efficient way of doing this.

Apologies if this is either a trivial request or unclear; I'm a 'semi-competent amateur' trying to help myself in the absence of organisational coding resources. But if you tell me which command(s) I need to use, I can probably figure it out from tutorials.

You want to get names matched to two separate fields.

Fields are indepentent.

So the tables which are used to find relative names must be independent too.

So you need 2 separate copies of names table:

SELECT tr.*,
       n1.name AS buyer_name,
       n2.name AS seller_name
FROM transactions AS tr
JOIN names AS n1 ON tr.buyer = n1.id
JOIN names AS n2 ON tr.seller = n2.id


Data lost in MySQL InnoDB after crash I found a 6-days rollback in a MySQL 5.6 community edition server installation.
It seems that some logs were lost, and I cannot understand why.

2013-12-22 20:38:54 380 [Note] Plugin 'FEDERATED' is disabled.
2013-12-22 20:38:54 380 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
2013-12-22 20:38:54 388 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-12-22 20:38:54 380 [Note] InnoDB: The InnoDB memory heap is disabled
2013-12-22 20:38:54 380 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-12-22 20:38:54 380 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-12-22 20:38:54 380 [Note] InnoDB: Not using CPU crc32 instructions
2013-12-22 20:38:54 380 [Note] InnoDB: Initializing buffer pool, size = 86.0M
2013-12-22 20:38:54 380 [Note] InnoDB: Completed initialization of buffer pool
2013-12-22 20:38:54 380 [Note] InnoDB: Highest supported file format is Barracuda.
2013-12-22 20:38:54 380 [Note] InnoDB: The log sequence numbers 5988825 and 5988825 in ibdata files do not match the log sequence number 6057379 in the ib_logfiles!
2013-12-22 20:38:54 380 [Note] InnoDB: Database was not shutdown normally!
2013-12-22 20:38:54 380 [Note] InnoDB: Starting crash recovery.
2013-12-22 20:38:54 380 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-12-22 20:38:57 380 [Note] InnoDB: Restoring possible half-written data pages
2013-12-22 20:38:57 380 [Note] InnoDB: from the doublewrite buffer...
2013-12-22 20:39:07 380 [Note] InnoDB: 128 rollback segment(s) are active.
2013-12-22 20:39:07 380 [Note] InnoDB: Waiting for purge to start
2013-12-22 20:39:07 380 [Note] InnoDB: 5.6.13 started; log sequence number 6057379
2013-12-22 20:39:07 380 [Note] Server hostname (bind-address): '*'; port: 3306
2013-12-22 20:39:07 380 [Note] IPv6 is available.
2013-12-22 20:39:07 380 [Note]   - '::' resolves to '::';
2013-12-22 20:39:07 380 [Note] Server socket created on IP: '::'.
2013-12-22 20:39:12 380 [Note] Event Scheduler: Loaded 0 events
2013-12-22 20:39:12 380 [Note] C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld: ready for connections.

Can you tell me why this occurs and how to restore that data? Is is possible?
Do you think MyISAM tables are safer and a switch could solve this kind of errors?

EDIT

More informations about this puzzle.

This machine has a RAID-1 volume.
The problem has occourred after a poweroff on 22 december. After this power off I got the previous log and my database had a many-days data rollback. I found the database exactly with data of 14 december.
From 14 to 21 december there were many graceful server shutdowns (every evening) and I found a full backup for every day from 14 to 21 december. The 22 december backup has the 8days data gap.

I think this is very strange. I can understand data loss after a poweroff, but I can not understand this so big rollback (8days) because during this 8 days there were many server shutdowns and mysqldump backup confirm that all data was correctly stored.
I supposed there was a innodb log flush problem, but why so big?

Thanks

This log just indicates that you didn't shut down correctly, so upon startup, InnoDB had to do a "crash recovery". It's not the crash recovery that would have lost data -- it's your operating system or hardware. Crash recovery is designed actually to avoid losing data due to crashes (that's its entire purpose).

The log message indicates a severe problem:

The log sequence numbers 5988825 and 5988825 in ibdata files do not match the log sequence number 6057379 in the ib_logfiles!

This likely means that data is not getting to disk effectively either because of a misconfiguration in MySQL, or a misconfiguration of your operating system or hardware.

If you are running with innodb_flush_log_at_trx_commit set to anything other than 1, this is your fault as this is a dangerous setting. If you are running it with 1, this would imply that InnoDB is requesting data to be written to disk and confirmed, but the system is lying to InnoDB about writing it, causing corruption later.

There is no way to get the data back. If you need data that was lost, you must restore it from backup.

MyISAM is definitely not safer, and in fact MyISAM would more than likely have suffered extreme corruption under the same circumstances.