Use users / logins from another database Is it possible to use logins/users from one database(F) in another(S) one? I know I can copy it but, when I add user to (F) database I have to copy again them all (or add the same user to (S) database) - I want to avoid that...

All I want is to know if is it possible to set Security/Users to Users from another database.

This means that database users have no matching server logins.

That is, each database sys.database_principals has no match in sys.server_principals

For Windows logins this is easy. This generates your missing CREATE LOGINS

USE MyDB
SELECT
    'CREATE LOGIN ' + QUOTENAME(SUSER_SNAME(sid)) + ' FROM WINDOWS'
FROM
    sys.database_principals DP
WHERE
    DP.type IN ('G', 'U')
    AND
    NOT EXISTS (SELECT * FROM sys.server_principals SP WHERE SP.sid = DP.sid);

For SQL Logins, you need to recreate it with the correct SID.

SELECT
    'CREATE LOGIN ' + QUOTENAME(DP.name) +
                ' WITH PASSWORD = ''ChangeMe'', SID = ' +
                CONVERT(varchar(128), sid, 1)
FROM
    sys.database_principals DP
WHERE
    DP.type = 'S' AND DP.principal_id > 4
    AND
    NOT EXISTS (SELECT * FROM sys.server_principals SP WHERE SP.sid = DP.sid);

Note that sp_change_users_login is deprecated.

Now, if your SQL Logins already exist, then the names match but the sid values are different. For this you use ALTER USER use the LOGIN option.

Finally, the SQL Login passwords can be recreated if you have a backup of the "old" master database. If you restore this as, say, FixLogins then you can use this

SELECT
    'CREATE LOGIN ' + QUOTENAME(DP.name) +
                ' WITH PASSWORD = ' + CONVERT(varchar(256), SL.password_hash, 1) + ' HASHED, SID = ' +
                CONVERT(varchar(128), DP.sid, 1)

FROM
    sys.database_principals DP
    JOIN
    FixLogins.sys.sql_logins SL ON DP.sid = SL.sid
WHERE
    DP.type = 'S' AND DP.principal_id > 4
    AND
    NOT EXISTS (SELECT * FROM sys.server_principals SP WHERE SP.sid = DP.sid);


Optimizing a query by adding an index Here are my tables:

accounts_company:
 - id
 ...

accounts_companyview:
 - account_id: FK to company
 - viewer_id: FK to company

shipments_address:
 - companyview_id: FK to companyview
 - is_shipper: boolean (not null)

accounts_similarcompanyview
 - original_id: FK to companyview
 - similar_id: FK to companyview

This is my query (generated by Django)

SELECT COUNT(*) AS "__count"
  FROM "accounts_similarcompanyview"
 INNER JOIN "accounts_companyview"
    ON ("accounts_similarcompanyview"."original_id" = "accounts_companyview"."id")
 INNER JOIN "shipments_address"
    ON ("accounts_companyview"."id" = "shipments_address"."company_view_id")
 INNER JOIN "accounts_companyview" T5
    ON ("accounts_similarcompanyview"."similar_id" = T5."id")
 INNER JOIN "shipments_address" T6
    ON (T5."id" = T6."company_view_id")
 WHERE ("accounts_similarcompanyview"."are_similar" = true AND "shipments_address"."is_shipper" = true AND "accounts_companyview"."viewer_id" = 51729 AND T6."is_shipper" = true AND T5."viewer_id" = 51729);

Output of EXPLAIN ANALYZE: https://explain.depesz.com/s/slJK

I can see that there is an Index Scan taking most of the time, so I tried to add a few indexes but no change whatsoever, I still get a full index scan.

CREATE INDEX "shipments_a_is_ship_d78ee4_idx" ON "shipments_address" ("is_shipper");

CREATE INDEX "shipments_a_is_ship_d78ee4_idx" ON "shipments_address" ("is_shipper", "company_view_id");

What index could I add to make this query faster?

EDIT: forgot to add the size of the tables

select count(*) from shipments_address;
 2998765

select count(*) from accounts_company;
 168224

select count(*) from accounts_companyview;
 371560

select count(*) from accounts_similarcompanyview;
 83434

The main problem are the bad estimates of the join cardinalities that lead PostgreSQL to use a nested loop join when a hash join would perform better.

There is one simple thing you can do to reduce the impact of the outermost nested loop join:

CREATE INDEX ON shipments_address (company_view_id) WHERE is_shipper;

That should cut the execution time roughly in half.

Other than that, I can think of the ugly method of temporarily disabling nested loop joins:

BEGIN;

SET LOCAL enable_nestloop = off;

/* your query */

COMMIT;


How to find out unused port on Linux server We are trying to use port 5444 for our new PostgreSQL instance and we are not sure whether 5444 is used or unused.

Could you please help us to find the answers to the below questions.

We want to find out all the used and unused ports report on PROD Linux servers (RHEL 7.2).
Also find out the application port even the application was shutdown.

Is there any file on Linux servers which holds all the ports (or sockets) info for helping with the above query?

netstat -an | grep LISTEN | grep 5444 will show if there's a process listening on that port.

netstat -an | grep LISTEN will show you all ports with a process listening on them. You can then use lsof to see which processes are actually using a given port.

There's no such concept of "find out the application port even the application was shutdown", as anything can listen on any port (with certain restrictions if not running as root). The closest thing to this is the /etc/services file, which documents ports that are "well known"/assigned by IANA. Also see http://www.iana.org/assignments/port-numbers. But, as I said, there's nothing to stop anything from running on one of these ports.

To be honest, you should know what's running on a production box that you have root access to?


what is so special about MongoDB indexing? I was reading the MongoDB documentation, where it lists Indexing as an special feature. But after reading the doc, I still can't see what it is that makes MongoDB index special. Anyone can explain?

there is absolutely nothing special about it. absolutely zero; nothing.
if you want REALLY advanced indexing -> use PostgreSQL.


PostgreSQL invalid frontend message type 74 Error

invalid frontend message type 74

I'm getting this error and I have no idea where it's coming from or what it means. I can't find any documentation on frontend message types in the PostgreSQL documentation.

I tried enabling the log of every statement, but this error isn't occurring after any specific statement in the log. The statements are different each time, and when run via pgsql, the statements run without error.

Is there anyway I can print out what statement or message is causing this error? I really don't know how to proceed without knowing the source.

PostgreSQL Version

PostgreSQL 9.4.5 on amd64-portbld-freebsd10.2, compiled by FreeBSD clang version 3.4.1 (tags/RELEASE_34/dot1-final 208032) 20140512, 64-bit

I'm getting this error and I have no idea where it's coming from or
  what it means. I can't find any documentation on frontend message
  types in the PostgreSQL documentation.

They're listed here:

https://www.postgresql.org/docs/current/static/protocol-message-formats.html

and a more general description of the protocol is at:

https://www.postgresql.org/docs/current/static/protocol-overview.html#PROTOCOL-MESSAGE-CONCEPTS

The type of the message is given by the first byte. For example, if a client issues a "Bind", the first byte is the ASCII code for 'B', or 66.

The value 74 from the error message would be 'J', but according to the doc this message type is not used for anything in any version of the protocol, so the client or middleware sending it must be misbehaving.

If the traffic is not encrypted, a packet inspection tool like Wireshark might help to look into these packets. Wireshark knows about the PGSQL protocol. On the postgres server side, I don't think you can get more information beyond the mentioned error, because the backend gives up on the message at the first byte. As far as it's concerned, it's not correlated to any query.


Automatic vacuum fails with `ERROR: XX000: cache lookup failed for type 0` I've been noticing something like this popping up every 15 seconds in my server logs:

2020-01-30 21:10:30 UTC::@:[24969]:ERROR: XX000: cache lookup failed for type 0
2020-01-30 21:10:30 UTC::@:[24969]:CONTEXT: automatic vacuum of table "myschema.mytable"
2020-01-30 21:10:30 UTC::@:[24969]:LOCATION: get_typlenbyval, lsyscache.c:2036

When I manually run a VACUUM ANALYZE myschema.mytable; it runs without any error, and the error in my logs goes away. However, inevitably, it returns again at some point.

The table in question is defined as follows:

tfs_dev=> \d myschema.mytable;
                                                   Table "myschema.mytable"
   Column    |   Type    | Collation | Nullable |                                  Default
-------------+-----------+-----------+----------+---------------------------------------------------------------------------
 level       | text      |           | not null |
 id          | integer   |           | not null | nextval('myschema.mytable_id_seq'::regclass)
 text_id     | text      |           |          |
 name        | text      |           |          |
 geom        | geometry  |           |          |
 valid_dates | tstzrange |           |          | tstzrange(NULL::timestamp with time zone, NULL::timestamp with time zone)
 adjacent    | integer[] |           |          |
 valence     | integer   |           |          |
 color       | smallint  |           |          | 0
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (level, id)
    "mytable_text_id_key" UNIQUE CONSTRAINT, btree (text_id)
    "mytable_gix" gist (level, geom, id, text_id, name)
    "mytable_spgix" spgist (geom)
    "mytable_text_id_ix" btree (text_id, level, id, name)
Referenced by:
    TABLE "otherschema.othertable" CONSTRAINT "othertable_mytable_fk" FOREIGN KEY (level, level_id) REFERENCES myschema.mytable(level, id)
    TABLE "otherschema.othertable2" CONSTRAINT "othertable2_mytable_fk" FOREIGN KEY (level, level_id) REFERENCES myschema.mytable(level, id)

Many other posts with a similar error seem to be about a foreign data wrapper, which is present in this database, but not with this table/schema.

As your investigation uncovered, you are suffering from this PostGIS bug (bug number #4440).

Your options:

Update PostGIS to the latest minor release. The bug has been fixed in June 2019.
Workaround: Use GiST rather than SP-GiST indexes.


Strange behaviours with SET options in SSMS I am investigating the effect of various SET options on the creation of indexed views on SQL Server 2014 CU1. The following code:

ensures that the SET options are as they should be
creates a table
creates a view based on the table
creates an index on the view
inserts a record in the view (which will fail if the SET options are incorrect)

It works as expected. However, if I uncomment the section where I deliberately set each SET option to the wrong value, the CREATE INDEX statement (which occurs earlier in the script) fails saying that QUOTED_IDENTIFIER has the wrong value.

I have tested with SSMS 2012 and 2014. How can this be?

USE tempdb;

-- ensure correct SET options for indexed view creation
-- http://msdn.microsoft.com/en-us/library/ms191432.aspx
SET ANSI_NULLS  ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS
            WHERE TABLE_NAME = 'v1'
            AND TABLE_SCHEMA = 'dbo')
    DROP VIEW dbo.v1;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = 't1'
            AND TABLE_SCHEMA = 'dbo')
    DROP TABLE t1;
CREATE TABLE dbo.t1(c1 int);
GO
CREATE VIEW dbo.v1 WITH SCHEMABINDING AS SELECT c1 FROM dbo.t1;
GO
CREATE UNIQUE CLUSTERED INDEX i1 ON dbo.v1(c1);

---- make SET options the opposite of what is required
--SET ANSI_NULLS    OFF;
--SET ANSI_PADDING  OFF;
--SET ANSI_WARNINGS OFF;
--SET ARITHABORT    OFF;
--SET CONCAT_NULL_YIELDS_NULL   OFF;
--SET NUMERIC_ROUNDABORT    ON;
--SET QUOTED_IDENTIFIER OFF;

INSERT INTO dbo.v1(c1) VALUES(1);

DROP VIEW dbo.v1;
DROP TABLE dbo.t1;

I can work around it with dynamic SQL and get the expected error message:

  Msg 1934, Level 16, State 1, Line 32
  INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...

-- make SET options the opposite of what is required
EXECUTE ('SET ANSI_NULLS    OFF;
SET ANSI_PADDING    OFF;
SET ANSI_WARNINGS   OFF;
SET ARITHABORT  OFF;
SET CONCAT_NULL_YIELDS_NULL OFF;
SET NUMERIC_ROUNDABORT  ON;
SET QUOTED_IDENTIFIER   OFF;
INSERT INTO dbo.v1(c1) VALUES(1);');

but why should I have to?

Among the SET statements in your script, SET QUOTED_IDENTIFIER is special in that it is processed at parse time rather than at execute time.

From SET Statements (Transact-SQL) (emphasis mine):

  Considerations When You Use the SET Statements

  All SET statements are implemented at execute or run time, except for SET FIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SET QUOTED_IDENTIFIER. These statements are implemented at parse time.

Confirmed in SET QUOTED_IDENTIFIER (Transact-SQL):

  Remarks

  […]

  SET QUOTED_IDENTIFIER is set at parse time. […]

So, in your case QUOTED_IDENTIFIER gets reset at parse time and is, therefore, OFF by the time CREATE INDEX is executed. The engine complains about it as expected.

To resolve this, you don't have to resort to dynamic SQL, it is enough to put a GO line just after the CREATE INDEX statement to make it a different batch from the subsequent SETs, which would cause it to be parsed separately from them:

…

CREATE UNIQUE CLUSTERED INDEX i1 ON dbo.v1(c1);

GO

-- make SET options the opposite of what is required
SET ANSI_NULLS    OFF;
SET ANSI_PADDING  OFF;
SET ANSI_WARNINGS OFF;
SET ARITHABORT    OFF;
SET CONCAT_NULL_YIELDS_NULL   OFF;
SET NUMERIC_ROUNDABORT    ON;
SET QUOTED_IDENTIFIER OFF;

…

Note, however, that SET Statements (Transact-SQL) also includes the following (emphasis mine):

  Considerations When You Use the SET Statements

  […]

  When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

  If any one of these options is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist.

Which means that the uncommented SET statements would still break your script, only not at CREATE INDEX but at INSERT.


Increment value for unique key column I have a table:

CREATE TABLE tbl (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    rank BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY rank (rank)
)

With some values:

(2,    11)
(1,    12)
(9,    42)
(4711, 43)
(9000, 44)

I.e the ranks may have gaps. The rank is always >= 1.

I want to increment every rank by one, starting by the 3rd least value:

UPDATE tbl table_1
JOIN (
    SELECT table_2.id
    FROM tbl table_2
    ORDER BY table_2.rank ASC
    LIMIT 2, 18446744073709551615
) sub_1 ON table_1.id = sub_1.id
SET table_1.rank = table_1.rank + 1

Problem:

  #1062 - Duplicate entry '43' for key 'rank'

What I have tried:

Adding ORDER BY:

UPDATE tbl table_1
JOIN (
    SELECT table_2.id
    FROM tbl table_2
    ORDER BY table_2.rank ASC
    LIMIT 2, 18446744073709551615
) sub_1 ON table_1.id = sub_1.id
SET table_1.rank = table_1.rank + 1
ORDER BY table_1.rank DESC

  #1221 - Incorrect usage of UPDATE and ORDER BY

Eliminating the join:

UPDATE tbl
SET rank = rank + 1
WHERE rank >= (
    SELECT table_2.rank
    FROM tbl table_2
    ORDER BY table_2.rank ASC
    LIMIT 2, 1
)
ORDER BY rank DESC

  #1093 - Table 'tbl' is specified twice, both as a target for 'UPDATE' and as a separate source for data

The only options I can think of are either omitting UNIQUE KEY, or splitting up the statement into two: get the 3rd least rank, then run UPDATE  … ORDER BY ….

Is there any proper solution for my problem?

Server: MariaDB 10.2.15
InnoDB: 5.7.22

I think You can perform update You need using 2 statements.

1) Update all records in decreasing order. Single-table UPDATE allows ordering.

UPDATE tbl
SET rank=rank+1
ORDER BY rank DESC;

2) Restore values for first 2 records.

UPDATE tbl
SET rank=rank-1
ORDER BY rank ASC LIMIT 2;


User Account Mismatch My disk crashed so I reinstalled SQL Server to a new drive and restored my databases from my backup drive.  However, when I attempt to install Diagram support, I get this message:

  Cannot execute as the database principal because the principal "dbo" does not exist....

This issue is admirably answered here but the reply doesn't seem to apply in my case since running the first query in the "Solution" section returns no records so I have nothing to fix, yet I get that error message.

Here are my results from the queries suggested in that reply:

Please inform me how to resolve this matter.

The owner of your database on the old server was some Windows account that does not exists as login on the new server (if it existed, its login sid would match user sid as it's Windows account, so it's Windows sid).

To fix it you can make sa the owner of your database:

alter authorization on database::YourDBName to sa

Or any other login (I just don't see other logins on your picture that are not certificate logins)


Set-ClusterParameter : Parameter 'OverrideAddressMatch' does not exist on the cluster object while configuring failover cluster resources I configured an availability group in Azure using this guide. I have 2 failover cluster nodes with SQL Server 2016. User 'Install' has administrative permissions on both machines and sql server instances. I used this guide to create an avilability group listener but I failed on the final step. According to the guide I had to log in to primary replica machine as 'Install' user and create IP resource for availability group and then execute configuration script. Configuration script is

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$CloudServiceIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}

But this script always fails with error:

Set-ClusterParameter : Parameter 'OverrideAddressMatch' does not exist on the cluster object 'Resource1'. If you are trying to update an existing parameter, please make sure the parameter name is specified correctly. You can check for the current parameters by passing the .NET object received from the appropriate Get-Cluster* cmdlet to “| Get-ClusterParameter”. If you are trying to update a common property on the cluster object, you should set the property directly on the .NET object received by the appropriate Get-Cluster* cmdlet. You can check for the current common properties by passing the .NET object received from the appropriate Get-Cluster cmdlet to "| fl ". If you are trying to create a new unknown parameter, please use -Create with this Set-ClusterParameter cmdlet.

What should I do to get rid of this error? It seems like permission issue.

I have to reiterate that you're not doing this on a valid IP Address resource. I have no problem getting these values from any of my clustered IP resources. Tried on Windows Server 2012R2 and Windows Server 2016, both show values just fine.

Double check your input is a valid IP Address resource type and double check it holds a resource that exists in the current context.

Windows Server 2012R2

Windows Server 2016


Why scalar valued functions need execute permission rather than select? I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select?

meanwhile a table valued functions works just fine with only select permission or db_datareader membership.

to be more clear here is my example:
I need a user that has read only permission to the database.
so I created a user called testUser and give it db_datareader membership.
then I created a table valued function called fn_InlineTable. And all is great. testUser runs this SQL all day long

select * from dbo.fn_InlineTable

then I need a scalar function , so I created a scalar function called fn_ScalarTest.
testUser cannot run this SQL

Select dbo.fn_ScalarTest(1)

Well understandably: it is because I have not given "testUser" permission to execute fn_ScalarTest.

My question is: based on this link https://stackoverflow.com/questions/6150888/insert-update-delete-with-function-in-sql-server,
that says a FUNCTION cannot be used to perform actions that modify the database state. So why not let a scalar function to be used with the same "SELECT" permission rather than execute permission??

I hope my question makes sense. Thank you.

Most likely the primary reason is that Table-Valued Functions return a Result Set, just like Tables and Views. This means that they can be used in the FROM clause (including JOINs and APPLYs, etc) of SELECT, UPDATE, and DELETE queries. You cannot, however, use a Scalar UDF in any of those contexts.

Secondarily, you can also EXECUTE a Scalar UDF. This syntax is quite handy when you have default values specified for input parameters. Take the following UDF, for example:

CREATE FUNCTION dbo.OptionalParameterTest (@Param1 INT = 1, @Param2 INT = 2)
RETURNS INT
AS
BEGIN
    RETURN @Param1 + @Param2;
END;

If you want to treat any of the input parameters as "optional", you still need to pass in the DEFAULT keyword when calling it like a function since the signature is fixed:

DECLARE @Bob1 INT;

SET @Bob1 = dbo.OptionalParameterTest(100, DEFAULT);

SELECT @Bob1;
-- Returns: 102

On the other hand, if you EXECUTE the function, then you can treat any parameters with a default value as truly optional, just like you can with Stored Procedures. You can pass in the first n parameters without specifying parameter names:

DECLARE @Bob2 INT;

EXEC @Bob2 = dbo.OptionalParameterTest 50;

SELECT @Bob2;
-- Returns: 52

You can even skip the first parameter by specifying parameter names, again, just like with Stored Procedures:

DECLARE @Bob3 INT;

EXEC @Bob3 = dbo.OptionalParameterTest @Param2 = 50;

SELECT @Bob3;
-- Returns: 51

UPDATE

Why might you want to use the EXEC syntax to call a scalar UDF just like a Stored Procedure? Occasionally there are UDFs that are great to have as UDFs since they can be added to a query and operate over the set of rows returned, whereas if the code were in a Stored Procedure then it would need to be placed into a cursor in order to iterate over a set of rows. But then there are times that you want to call that function on a single value, possibly from within another UDF. Calling a UDF for a single value can be done as either:

SELECT dbo.UDF('some value');

in which case you get a return value in a result set (a result set won't work). Or it could be done as follows:

DECLARE @Dummy INT;

SET @Dummy = dbo.UDF('some value');

in which case you need to declare the @Dummy variable;

HOWEVER, with the EXEC syntax, you can avoid both of those annoyances:

EXEC dbo.UDF 'some value';

ALSO, scalar UDFs have their executions plans cached. This means that it is possible to run into parameter sniffing issues if there are queries in the UDF that have execution plans. For scenarios where it is feasible to use the EXEC syntax, then it is possible to also use the WITH RECOMPILE option to ignore the plans compiled value for that execution. For example:

SETUP:

GO
CREATE FUNCTION dbo.TestUDF (@Something INT)
RETURNS INT
AS
BEGIN
   DECLARE @Ret INT;
   SELECT @Ret = COUNT(*)
   FROM   sys.indexes si
   WHERE  si.[index_id] = @Something;

   RETURN @Ret;
END;
GO

TEST:

DECLARE @Val INT;

SET @Val = dbo.TestUDF(1);
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 -- uses compiled value of (1)
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 WITH RECOMPILE; -- uses compiled value of (0)
SELECT @Val;

EXEC @Val = dbo.TestUDF 3 -- uses compiled value of (1)
SELECT @Val;


Does SQL Table Column is assigned with column number In Table, we can see there is number assigned automatically (line number) is the same thing is available for the columns (column number) and how can we get that number?
I need a SQL query to find the column number not the number of columns.

The row number you get in SSMS (I'm presuming you were refering to this number) is not generated by SQL but by SSMS (so it doesn't really exist in your database).
SQL does have a "number" associated with each columns (their IDs) and you could get those with a query like this one:
  select c.column_id from sys.columns c
  join sys.objects o on o.object_id =c.object_id
  where o.name = 'YourTableName'

I'm not sure it will do exactly what you expect it to do, so if you could specify why you want those columns number, we will be in a better position to give you a clearer answer.


import 100 csv files into postgresql I have a 100 csv files named sequentially 1,2,3....100.csv

I copy the csv files with this script:

COPY location FROM 'C:\Program Files\PostgreSQL\9.5\data\´1.csv' DELIMITER ',';

I would to execute the COPY with a single script that covers all the files (1,2,3 .... 100.csv).

I use pgadminIII on MS Windows

You can use pgScript:

DECLARE @i, @filename;
SET @i = 1;
WHILE @i <= 100
BEGIN
    SET @filename = 'C:\\Program Files\\PostgreSQL\\9.5\\data\\' + CAST(@i AS STRING) + '.csv' ;
    COPY location FROM '@filename' DELIMITER ',' ;
    SET @i = @i + 1;
END

NOTE: You shouldn't have your CSV files under the PostgreSQL data directory. This directory is for the database engine to use, not users. Mistakenly overwriting or deleting any of PostgreSQL files could have your database rendered useless.

You should keep the files somewhere under C:\Users\user\Documents\CSV\... or a similar location in "userland" and give proper permissions to that directory for the PostgreSQL process to read. For a standard install of this version, you normally need to give read permission to the LocalSystem account. Some installations can use the Postgres account).

NOTE 2: pgScript hasn't had much traction. It's not been ported to pgAdmin 4.


Updating a row through Linked Server to MySQL DB I'm trying to update a single row using linked server from MS SQL 2012 to MySQL DB.

I run this query:

UPDATE b2b...product set [index]=`test_value` where id=12345;

Error Message:

  The OLE DB provider "MSDASQL" for linked server "b2b" could not UPDATE table "[b2b]...[product]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

I have googled this error and everyone recommends to check option "Return     matched rows instead of affected rows" in MySQL ODBC Data Source Configuration, so I tried to do that, but it was already checked. So I tried to uncheck this, it didn't help, so I re-checked this setting.

If checking this option doesn't help, what else could I do?

By the way I tried the same update using MySQL WorkBench and it works fine.

Edit: this is very weird.
When I update a particular row using MySQL WorkBench then I'm able to update it using SQL Server Management Studio. I tried it on several rows.
BTW, I had to set safe mode in MySQL WorkBench in order to be able to update rows.

You can use

EXEC('UPDATE product set [index]=''test_value'' where id=12345;') AT b2b

as an alternative syntax, which executes the query in pass-through mode.

The reason why you're getting the optimistic locking error is probably because the query can't be run in pass-through, so SQL Server downloads the whole table, locates the row to update and then pushes the row back to the linked server. Between the moment the row is read and the moment the row is written, the value has changed in the linked server, so you have an optimistic locking violation.


How to use values from table column as field names in SELECT query? Scenario:

Field names of Table1 named as testtable

id,name,size,width,height

Field names of Table2 named as errortable

id,desc,field1,field2,operator

Values of errortable

+----+-------------------------------------+--------+--------+----------+
| id |                desc                 | field1 | field2 | operator |
+----+-------------------------------------+--------+--------+----------+
|  1 | size should not greater than width  | size   | width  | >        |
|  2 | size should not greater than height | size   | height | >        |
|  3 | with should be equal to height      | width  | height | <>       |
+----+-------------------------------------+--------+--------+----------+

Now I want to check from testtable:

count all those records where size > width.
count all those records where size > height.
count all those records where width <> height.

Required Output

+-------------------------------------+-------+
|              errorname              | count |
+-------------------------------------+-------+
| size should not greater than width  |     6 |
| size should not greater than height |     2 |
| with should be equal to height      |     3 |
+-------------------------------------+-------+

Is it possible to do like this?

Current query:

select desc,
  (select count(*) as "Total Errors"
   from testtable
   where errortable.field1 errortable.operator errortable.field2 )
from errortable group by id;

Assumptions and Clarification

Postgres 9.4
You want various counts from the same given table.

desc is a reserved word in SQL, don't use it as identifier. Using descr instead.

1. Basic query

The fastest and most elegant way to get multiple partial counts from the same table is a single SELECT statement with multiple aggregate functions using the FILTER clause:

Return counts for multiple ranges in a single SELECT statement

A statement like:

SELECT count(*) FILTER (WHERE size > width)    AS ct1
     , count(*) FILTER (WHERE size > height)   AS ct2
     , count(*) FILTER (WHERE width <> height) AS ct3
FROM   testtable;

Or, for Postgres 9.3:

SELECT count(size > width OR NULL)    AS ct1
     , ...
FROM   testtable;

2. Advanced query

To get the result in the form you want it, I counter-pivot with a VALUES expression in a LATERAL join, adding the ID and description id, descr in the process:

SELECT x.*
FROM  (
   SELECT count(*) FILTER (WHERE size > width)    AS ct1
        , count(*) FILTER (WHERE size > height)   AS ct2
        , count(*) FILTER (WHERE width <> height) AS ct3
   FROM   testtable
   ) t
, LATERAL (
   VALUES (1, 'size should not greater than width' , ct1)
        , (2, 'size should not greater than height', ct2)
        , (3, 'with should be equal to height'     , ct3)
   ) x(id, descr, ct);

Bold parts come from the errortable when building the statement dynamically:

3. Dynamic query

To concatenate the above statement dynamically from values provided in errortable:

CREATE OR REPLACE FUNCTION
SELECT 'SELECT x.* FROM (SELECT '
    || string_agg(
         format('count(*) FILTER (WHERE %I %s %I) AS c%s'
              , field1, operator, field2, id)
       , ', ')
    || ' FROM testtable) t, LATERAL (VALUES ('
    || string_agg(format('%s, %L, c%s', id, descr, id), '), (')
    || ')) x(id, dscr, ct)'
FROM   errortable;

format() with %I quotes column names where necessary and makes the statement safe against SQL injection - except for operator, which is concatenated as is. You might be able to make that safe, too, with the OPERATOR() construct ...

If untrusted users don't have write access to errortable, you can control its content and need not worry.

4. Full automation

Since the return type is uniform and well known, we can encapsulate it in a function:

CREATE OR REPLACE FUNCTION f_error_count()
  RETURNS TABLE (id int, descr text, ct bigint) AS
$func$
BEGIN

RETURN QUERY EXECUTE (
   SELECT format('SELECT x.* FROM (SELECT %s FROM testtable) t
                        , LATERAL (VALUES (%s)) x(id, dscr, ct)'
               , string_agg(format('count(*) FILTER (WHERE %I %s %I) AS c%s'
                                 , e.field1, e.operator, e.field2, e.id), ', ')
               , string_agg(format('%s, %L, c%s', e.id, e.descr, e.id), '), ('))
   FROM   errortable e
   );

END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_error_count();

Simplifying the concatenation by one more step with an outer format(). Also note how all columns are table-qualified to avoid conflicts.
Again, since dynamic SQL is involved, make sure it cannot be abused for SQL injection.

SQL injection in Postgres functions vs prepared queries
Permission for sequence in another schema

SQL Fiddle for Postgres 9.3 since 9.4 is not available.


How can I view special characters in SQL Server 2012 Management Studio grid view? In a SQL Server 2012 database, I have a log table where I log SQL queries executed by a VB.NET application.

The field is defined as nvarchar(MAX)

In Microsoft SQL Server Management Studio, when I go to the table and do "Edit All Rows",
on one line I see spaces in the end of the text

But when I enter my cursor in the line to edit it (not touching anything, before I even start editing), the spaces disappear (along with the trailing quote)

And when I go to New Query and execute the same query

SELECT LOGID, LogDate, SqlLog
FROM ezber_SQL_LOG
WHERE (LOGID = 1604))

I get the result without the spaces and the trailing quote:

So my guess is that there are special characters in the data (line breaks, tabs or something like that), but how can I find out for sure and know what they are?

I found viewing the data as binary was the way to go for me:

SELECT @String as MyString, CAST(@String as varbinary(max)) MyString_In_Hexidecimal;

I found an ASCII table then helped decipher what was in the string.


SQL Server "TOP 101" much slower than "TOP 100" SQL Server shows huge decrease in performance when query is written like:

select top 101 name
from Dogs
order by name

compared to select top 100.

As far as I know, this happens for the reason that SQL Server in a top 100+ query simply orders all dataset and picks top records (while top 100 and less queries use more sophisticated algorithm).

Are there any workarounds for this?

"As far as I know, this happens for the reason that SQL Server in a top
100+ query simply orders all dataset and picks top records (while top
100 and less queries use more sophisticated algorithm)."

Your two queries likely have different execution plans. There's some hard-coding in the engine that says the top 100 will perform differently, but you might have stumbled into a problem like a spill into TempDB.
To find out for sure and get custom advice based on the queries you're facing, you can post the execution plans for download. You can use SQL Sentry Plan Explorer to anonymize them if necessary. Post the actual plans, not the estimated plans.


Transaction isolation level/design for write-only ledger This is a simplified example of a problem I've been working on. Say I have the following database schema:

Table: Deposits
+--------+------------+--------+---------+
|   ID   |    Date    | Amount | User ID |
+--------+------------+--------+---------+
| c1f... | 1589993488 |   40.0 | 6c7...  |
| bfe... | 1589994420 |   30.0 | 744...  |
+--------+------------+--------+---------+

Table: Withdrawals
+--------+------------+--------+---------+
|   ID   |    Date    | Amount | User ID |
+--------+------------+--------+---------+
| ad4... | 1589995414 |   20.0 | 6c7...  |
| e9b... | 1589996417 |   20.0 | 6c7...  |
+--------+------------+--------+---------+

And I'm writing a function that performs a withdrawal for a User. It:

Sums the deposits (SELECT amount FROM deposits WHERE user_id = ...)
Sums the withdrawals (SELECT amount FROM withdrawals WHERE user_id = ...)
If the difference is greater than the requested amount, INSERTs a new Withdrawal

We're using MySQL 8 and the default isolation level of REPEATABLE READ.

This function may be running as a lambda, so memory locks are not an option, and we don't have distributed locking (ie. a Redid-based lock) available.

A Caveat

There are existing admin operations run at the REPEATABLE READ level to create/delete these entities on-demand, by ID.

My Questions:

Am I correct in understanding that I need to use SERIALIZABLE as the isolation level for this transaction?
Will the SERIALIZABLE range lock prevent the REPEATABLE READ transaction from inserting new rows into the Withdrawals, or removing rows from the Deposits?

Be sure to have FOR UPDATE on the end of the SELECTs.  Else another connection could sneak in and change the results of the SELECTs.

Also, steps 1,2,3 need to be inside a single TRANSACTION.

Once you do all that, you can probably leave the isolation level at the default.


Assigning a logical name to a sql instance How can we assign a logical name to a sql instance please? We would need to as to property identify messages coming through from the server.

Update

Thanks much for your replies. Yes it is for the instance name. Is it possible to assign one once the installation is already done? We now have two instances in this box, the other is server_name\inst2008. And it is the first instance that we want to name properly - which is now "local". I've considered Alias but I want to know another option - if there is. This instance is a SQL 2000 by the way.

Sorry just to clarify this topic...
We have a server that for some reasons doesn't have a logical name - it doesn't have an entry in the sys.servers table for the local server (server_id = 0).

With some research and testing I was able to do this using sp_dropserver and sp_addserver commands.

What I can think of is a server alias.

In SQL Server Configuration Manager, expand SQL Native Client Configuration, right-click Aliases, and then click New Alias.


Is there a way in sqlite3 to set .width of all columns to be equal to the width of the column header? I am using:

SQLite version 3.21.0

I look for away to prettify my SQLite SELECT shell output (to make it more human readable, in essence). This answer helped me a lot.

Though, now I am wondering if there are ways to:

Set a column width to be equal exactly to the width of its header? I went through the corresponding section of the documentation — the closest thing it mentions is .width 0, which effect is explained as follows:

  If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data.

if it was only 'the maximum of two numbers: 10 and the width of the header' — it would suffice my needs, though '...and the width of the first row of data.' spoils it for me.
Is there a way to  set width of all columns to be the same, without knowing in advance how many columns are there? E.g. instead of doing .width 5 5 5 for 3-column SELECT, I would like to do something like .width all 5.

The sqlite3 shell is designed to be simple, so there are limited customization mechanisms.

There is no built-in way to modify the widths in this way.

You'd have to either modify the shell's source code, or use some alternative shell written in a scripting language.


how can I ensure database connectivity on a spotty network? (and allow for entirely offline connectivity) My place of work has a pretty spotty network... we have quite regular (though usually brief) outtages.

Not too long ago, my team was logging application data to a SQL Server owned and managed by IT (I am a programmer outside of IT, so I don't control what they do)... some time ago, due to the spottiness of the network and this SQL Server in particular, the applications got changed to point to an instance of MySQL.  The data getting stored in MySQL then gets sent to the SQL Server periodically when we can ensure connectivity.

This has improved reliability greatly, however, there are still some annoying blips due to the spotty network issues...

Due to the poor network conditions, and the desire to potentially operate software in an entirely offline state, I have been tasked with looking into local database options...

We are using MySQL currently... I am entirely unaware of a local MySQL option that's simple to configure.  I also don't want to be in the business of managing potentially hundreds of MySQL instances... But the data we are recording locally will eventually be migrated to either a MySQL database or SQL Server.

What options are out there for a very lightweight/easy to configure database?  What about options for easily migrating this data without a lot of overhead?

Honestly the only thing that comes to mind is to have a bunch of local databases (potentially something like SQLite), and run scripts that talk to the local databases on some given frequency to push the data out to a database server on the network, but this seems like a lot of overhead that we'd need to manage...

I'm afraid this whole setup is feeling quite hacky and I personally feel the responsibility of the data storage should fall on the application itself (the application should know the network is down and wait until it can try to send the data again)... I also feel that more pressue should be put on IT to improve the reliability of the network and the SQL Server... But I am trying to see what options if any are available that might be simple to impement on my end.

You have come across the first fallacy of distributed computing: the network is reliable.  I think no matter how much time and money your company invests in improving the network, there will always be outages.

I recommend designing the application/system such that it does not rely on the network to be up to function correctly.  What I think you are asking for is a way for the logging to eventually make it to the centralized SQL Server database.  Your proposed "store and forward" mechanism is a great way to accomplish this.

I would look at using a message queuing system such as MSMQ that supports local storage (no network dependency on a central bus/broker).  Frameworks such as NServiceBus can assist with this and abstract much of the low layer implementation details.  This will help avoid writing the replication, error handling, etc. logic yourself.

From the client app's perspective, instead of writing the log data directly to the remote or local database, it would simply send a WriteLogToDatabaseCommand message.  The messaging system running on the local system would persist this in its own storage and then try to forward it to the centralized logging server.  It keeps the local copy until it successfully forwards it to the receiver.  There would be a receiver process running on the logging server that handles the message by writing the log data to the centralized SQL database.

The same approach can be used for more than just "fire and forget" type of logging information.  However, for application data, you would have to design the whole system to work in an asynchronous fashion.  In that case, you may also want to research command/query responsibility segregation (CQRS).  Since the central state is not known by the remote clients, they must be able to handle that.  The updates (Commands) must always succeed, even if that means being more flexible than traditional thinking (e.g. an order is placed but the item is out of stock - still accept the order and say it's on backorder vs. stopping the order from being placed in the first place).

For more information on this approach, I recommend reading:

https://particular.net/blog/the-network-is-reliable
https://www.enterpriseintegrationpatterns.com/patterns/messaging/Introduction.html


Getting "Cannot open user default database. Login failed." for connections that don't specify a default database I have two virtual machines. Server A is running SQL Server 2000. Server B is running SQL Server 2008R2. I am using the same domain user to log in to both boxes.

But I cannot log in to SQL Server locally on server A. I get Cannot open user default database. Login failed. when attempting with Query Analyzer or Enterprise Manager. I can connect from server B using SSMS if I specify a database to connect to. Once thus in, I have tried changing the default database for my user to 'master' via the GUI and via exec sp_defaultdb 'DOMAIN\username', 'master' but it does not change things. I have tried setting other databases as the default and verified that those databases are queryable by me. No luck. Unless I open a connection with SSMS and specify a database, the login fails.

This login problem is also preventing me from running linked queries against server A. How can I get this fixed?

That error either means that the default database does not exist or that it is not accessible to the user. Try to set tempdb as the default and see if that helps.


Backup and Restore Oracle database in Azure Has anyone recreated an on-premise Oracle Database in the cloud and specifically an Azure Database(Oracle) Instance?

The total size of the database I want to recreate as backup in the cloud is around 260GBs and the version is Oracle 10G.

The two options I can see it's either RMAN(via tape) or a database dump(exp or expdp). What is the most efficient and trouble-free way out of these two? My opinion is that a database dump could be a better option, however I am worried in terms of performance and running all those insert statements from scratch. Any thoughts?

Any help gladly appreciated! Thanks!

As far as I know, Oracle databases in Azure are not provided as DBaaS, you get OS access, so it does not really affect the choice.

RMAN is efficient when working with large databases, while Data Pump is trouble-free and is easier to manage for someone not familiar with DBA tasks. A 260 GB database is not big, I would just export-import the database.

To restore an RMAN backup, you need the same platform + the same or higher software version. If your software version is higher, you need to immediately upgrade the database after restoring it.

With Data Pump you can just import the data directly to a higher version database, even on a different platform. Loading data with Data Pump is usually fast. The slow parts are creating indexes and validating constraints. This is a one-time operation, it should not affect performance in a negative way. In fact, it reorganizes tables and indexes, it may even improve the performance depending on their current state.


9000 rows affected message, but no rows actually changed I'm having a problem with MySQL. When I run the code below, via phpmyadmin:

UPDATE wp_posts
    SET post_content = REPLACE
        (
        post_content,
        '31.media.tumblr.com/',
        '41.media.tumblr.com/'
        )
    WHERE post_content LIKE '%31.media.tumblr.com/%'
    AND post_content NOT LIKE '%.gif%';

It returns:

  9000 rows affected

If I make a search, it shows that it replaced all the needed things successfully. But if I go and hit the 'EDIT' option in phpmyadmin on one of the rows inside the post_content column, no changes are done inside it; everything stays the same.

What might be the reason?

For me there's something wrong with the code above. Maybe it returns NULL or something and stops the update. If I remove this part:

AND post_content NOT LIKE '%.gif%';

...it does work properly.

The database is a default WordPress one:

Example of data inside post_content:

some text

other text

some other text
...


Why does MySQL consecutive JOIN give an unexpected result? I had 3 tables which were Category, Product and Vendor. Then I tried to make a consecutive join 3 tables in 1 query to count the product for each category.

If a product or it's vendor is deleted, it will be excluded from the list. Moreover, if there was no product available, I still need to keep the category with the 0 count result. Because all tables are using the soft-delete strategy, I need to filter deleted items from the Query to get a proper count result. Then, the problem start from here.

A quick sample is below

Category
--|----------|----------|
id|name      |deleted_at|
--|----------|----------|
 1|Food      |      NULL|
 2|Stationery|      NULL|

Product
--|-------|---------|------|----------|
id|cate_id|vendor_id|name  |deleted_at|
--|-------|---------|------|----------|
 1|      1|        1|Bread |      NULL|
 2|      1|        2|Milk  |      NULL|
 5|      2|        2|Pencil|      NULL|

Vendor
--|-------|----------|
id|name   |deleted_at|
--|-------|----------|
 1|Woolies|      NULL|
 2|Cole   |2020-01-18|

Query1 gave an unexpected result

SELECT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product
FROM
    category c
LEFT JOIN product p
    ON p.cate_id = c.id AND p.deleted_at IS NULL
INNER JOIN vendor v
    ON v.id = p.vendor_id AND v.deleted_at IS NULL
GROUP BY c.id;

cate_id|cate_name|total_product|
-------|---------|-------------|
      1|Food     |            1|

Query2 gave a correct response.

SELECT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product
FROM
    category c
LEFT JOIN (SELECT p1.* FROM product p1 INNER JOIN vendor v ON v.id = p1.vendor_id AND v.deleted_at IS NULL)
    AS p ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id;

cate_id|cate_name |total_product|
-------|----------|-------------|
      1|Food      |            1|
      2|Stationery|            0|

I'm not likely sure but I guess the Query1 did the LEFT JOIN between Category & Product tables first, then did the INNER JOIN on the JOINED Record & Vendor, finally leading to an unexpected result. Am I correct?

Is there any way to achieve this purpose without using a sub-query?

Use

SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Category c
LEFT JOIN ( Product p
            INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL
            ) ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;

or

SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Product p
INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL
RIGHT JOIN Category c ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;

fiddle

  I guess the Query1 did the LEFT JOIN between Category & Product first, then did the INNER JOIN on the JOINED Record & Vendor, finally leading to an unexpected result. Am I correct?

Yes, if at least one outer joining is present then tables scanning is performed in query text ordering (like STRAIGHT_JOIN is used).

  It would be really thankful if you can suggest a name or a link so that I can do some research about it.

MySQL 8.0 Reference Manual  /  ...  /  JOIN Clause


How to grant access for multiple tables to a user in MySQL DB? I have mysql db named "greats"  with four tables as below

+---------------------+
| Tables_in_greatstat |
+---------------------+
| log                 |
| sitedet1            |
| siteindex           |
| user                |
| whois1              |
+---------------------+

Here i have decided to create an mysql user "test" and i want to give the access only for specific table of the db "greatstat".
So, May i know how to grant specific table perm for an user in MYSQL ?

First create the user

GRANT USAGE ON *.* TO test@'localhost' IDENTIFIED BY 'whateverpassword';

To grant only SELECT privilege to the siteindex table in the greatstat database

GRANT SELECT ON greatstat.siteindex TO test@'localhost';

To grant only SELECT privilege to the whois1 table in the greatstat database

GRANT SELECT ON greatstat.whois1 TO test@'localhost';

To grant all privileges to every table in the greatstat database

GRANT ALL PRIVILEGES ON greatstat.* TO test@'localhost';

Please read the MySQL Documentation on the GRANT command.


How can I get distinct counts with different where clauses on the same field? How can I get DISTINCT row COUNTs with different conditions in a single SELECT statement, and if count is zero get zero as a result?

I tried the following query:

SELECT DISTINCT
    A.DeviceFilter,
    (SELECT count(*) FROM DeviceLicense AS B WHERE A.DeviceFilter = B.DeviceFilter AND b.OrganizationId = 1001)
FROM
    DeviceLicense AS A
GROUP BY
    A.DeviceFilter

It seems like you really want a PIVOT.

First, we'll create your DeviceLicense table, and populate some sample data:

CREATE TABLE dbo.DeviceLicense
(
    SINo int NOT NULL
    , DeviceFilter nvarchar(50) NULL
    , OrganizationID int NOT NULL
);

INSERT INTO dbo.DeviceLicense (SINo, DeviceFilter, OrganizationID)
VALUES (1, NULL, 1001)
    , (2, NULL, 1001)
    , (3, 'iPad', 1000)
    , (4, 'iPad', 1001)
    , (5, 'MACommon', 1000)
    , (6, 'MACommon', 1001);

This is the PIVOT statement.  As you can see below, the statement adds a PIVOT clause to a typical SELECT statement to allow rows to be transformed via an aggregate into columns.

SELECT *
FROM (
    SELECT *
    FROM dbo.DeviceLicense
    ) src
PIVOT (
    COUNT(SINo)
    FOR OrganizationID IN (
        [1000]
        , [1001]
    )
) pvt;

The results of the select statement above:

+--------------+------+------+
| DeviceFilter | 1000 | 1001 |
+--------------+------+------+
| NULL         |    0 |    2 |
| iPad         |    1 |    1 |
| MACommon     |    1 |    1 |
+--------------+------+------+

I haven't added all your sample data, so the output is limited, but you can see there are columns for each OrganizationID, and rows for each DeviceFilter.  In future, know that it helps us give you a better answer if you provide the scripted DDL for the objects involved (the tables, etc), along with a table of data, not a screenshot.

Here, I'll attempt to show the PIVOT clause syntax in an understandable way:

PIVOT (
    COUNT(SINo)
    FOR OrganizationID IN (
        [1000]
        , [1001]
    )
) pvt;

The first obvious bit is the presence of the PIVOT keyword.  This tells SQL Server we're going to provide the following items:

a column from the main "src" SELECT query, wrapped in an aggregate, such as MAX(), SUM(), or in our case, COUNT().
The column headings we want, in the form of a list of the values present in a column from the "src" SELECT query.  In this case, the OrganizationID column contains the values 1000 and 1001, which we'd like displayed as headings.  The OrganizationID column might contain other values, such as 1002, but the way the PIVOT clause is written now, values from those rows will not be displayed.  In order to have 1002 values shown as another column, we'd need to define the pivot like this:

PIVOT (
    COUNT(SINo)
    FOR OrganizationID IN (
        [1000]
        , [1001]
        , [1002]  --note this creates an additional column in the output!
    )
) pvt;


How is it possible for mysql storage engine to be NULL? I'm just reading a book and in one of the examples I get notices:

That the engine in the information_schema.table is null, how is that possible?
Can I create tables without any engine??, is there any pro in that?

I actually wrote a post back in July 2011 ( Modify DEFINER on Many Views ) about how to access views for modification.

It is a fact that when the storage engine is NULL, it is always a View.

The actual definition of the View is

MySQL 5.1/5.5

mysql> desc information_schema.views;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG        | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA         | varchar(64)  | NO   |     |         |       |
| TABLE_NAME           | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION      | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION         | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE         | varchar(3)   | NO   |     |         |       |
| DEFINER              | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE        | varchar(7)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT | varchar(32)  | NO   |     |         |       |
| COLLATION_CONNECTION | varchar(32)  | NO   |     |         |       |
+----------------------+--------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

MySQL 5.0

mysql> desc information_schema.views;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION    | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE    | varchar(3)   | NO   |     |         |       |
| DEFINER         | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE   | varchar(7)   | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

To see what the view definition is for sakila.actor_info you can either run

SELECT * FROM information_schema.views
WHERE table_schema='sakila' AND table_name='actor_info'\G

or

SHOW CREATE VIEW sakila.actor_info\G


Effect on execution plans when a table variable has a primary key Having read a great deal about the differences between temporary tables and table variables in SQL Server, I am experimenting with switching from mostly using temporary tables to mostly using table variables.  (They seem to be a better fit for the types of queries I usually work with.)

In these queries, the tables hold unique identifiers that drive the lookup process.  It's been my habit, when working with temporary tables, to include a PRIMARY KEY constraint so that the query optimizer is aware that it won't see any duplicates.  However, given that the optimizer (in most circumstances, and for my queries) assumes that a table variable only holds a single row*, which is unique by definition, is the query optimizer going to make choices any differently if there's a PRIMARY KEY constraint?

* Technically, it assumes there are no rows, but replaces the zero with a one.  (Because the zero interacts very poorly with the rest of the estimation process.)  But it also depends on whether the table variable is populated or not when the query is compiled.  There is some background information here: What's the difference between a temp table and table variable in SQL Server?.

I'm currently using SQL Server 2014, but I would be curious if the behavior changes in newer versions.

As has been pointed out, a PRIMARY KEY constraint comes with a  clustered index that gives the query optimizer more choices on how to get data out of the table variable.  I was aware of this and thinking about the rest of the query plan.  But after attempting to clarify my question, I've decided that the question I was attempting to ask was too broad and probably particular to my extreme situation.  (Nothing but navigational-type queries into half-a-trillion-row tables with an expectation of sub-second performance.)  So I am going to leave my question as-is.

...is the query optimizer going to make choices any differently if there's a PRIMARY KEY constraint?

Yes it might do. Estimating one row (with the understanding that estimates can be incorrect) is different from knowing that the table contains only unique values. Certain plan space explorations require a key, for example.

A good general rule of thumb is to provide as much information about the data and query task as you can to the optimizer. If there is a key, say so explicitly. It's not as if declaring the key will add much cost (beyond a little keyboard work) in most cases.

I personally rarely use table variables. The lack of statistics (including distribution and density) and cardinality information (all separate considerations) provides less information to the optimizer than an equivalent temporary table. My experience has very much been that table variable plans do not adapt as well to changing circumstances over time.

I only use a table variable when there are special reasons to be sure that it will always be adequate from a query optimization point of view. Only you have enough information about your databases and queries to say whether that is true in your case or not.

The question is rather broad (without a specific example), and so is this answer.


Safest place to download SQL Server 2005 Service Pack 2 Installer I need to obtain Microsoft SQL Server 2005 Service Pack 2 as part of a rollback plan for an upgrade to SP3. All links on the Microsoft website seem to be broken. This will be installed on a very safety sensitive system and I would prefer to obtain the installer from the official Microsoft website. I am able to obtain a download link on MSDN, but it says "No product key is required" so I'm assuming it wont even give me an option to enter a license key when I install it.

Where is the safest place to obtain a Microsoft SQL Server 2005 SP2 installer (iso or exe)?

That message is simply saying you won't need to enter a product key at installation.

The version from the MSDN downloads is the best download to use.

The SQL Server installers downloadable via MSDN subscriptions don't require at license key - the installer asks you what edition you want to install.  I'd recommend downloading the full installer for SQL Server 2005 from MSDN, installing it on a virtual machine for test purposes to see how the installer works.  The last thing you want to do is try to figure out how to install it as part of your rollback effort.


SQL Server Job logging best practice To be able to design a robust Job in SQL Server, we need to have log for each step of the job. I know there are different options available which help us for troubleshooting a job, but what if we want to record the number of affected records during the step, as well as being able to see the error if anything goes wrong. We have tables below in msdb database which we have query to see the job history:

sysjobstepslogs
sysjobhistory
sysjobactivity

Also the option available in each job step (where we have specify a text file to record the logs):

Output file

But What is the best practice in this regard? How can we log the number of records affected during each step?

Thanks in advance.

Each job step has a setting that allows it's output to be sent to a text file. This can be cumbersome because it's a text file located in the file system and not in a table and might be hard to get to. There is also an option to send the output of a job step to a table. You can query the table, but it's still clunky. (It is hard to write a query to pick the "X rows affected" string out of a blob of text, then pull the number out so that you can aggregate it, etc.)

I would recommend that you build a simple table in your user database (not in msdb or master) to keep that information. IMO, this is the best way to keep close track of the number of rows affected by each command, particularly if you want to be able to write simple queries to do historical reporting. This way, you can decide to log at the job step level, or if the step calls a stored procedure with multiple commands, you can log a audit record after each command. As always, @@ROWCOUNT is your friend.


How to drop all scheduler events in MySQL 5.6? In MySQL 5.6 is there a way to drop all scheduler events with an administrative command or do I have to go through them one by one with DROP EVENT [IF EXISTS] event_name?

you can disable them via the following command:

set GLOBAL event_scheduler=0;

or dropping them one by one using the command:

DROP EVENT [IF EXISTS] event_name;


Domain Certificate Authority Generated Certificate and SQL Server - Keyset does not exist I do not have much experience with certificates or certificate terminology, so I may be incorrect in how I title things.
I am trying to use a domain issued certificate as the certificate used in the creation of a Column Master Key (CMK) to setup Always Encrypted (AE) columns.
When I try to add the certificate I receive the error "Keyset does not exist".
Here is the program location:

I did find information about the certificate needs to be, referenced here, and it says:
To be a valid column master key, a certificate must:

To be an X.509 certificate.
be stored in one of the two certificate store locations: local machine or current user. (To create a certificate in the local machine certificate store location, you must be an administrator on the target machine.)
contain a private key (the recommended length of the keys in the certificate is 2048 bits or greater).
be created for key exchange.

Now, I don't quite know how to verify that information, but I believe we have set the certificate up correctly.
I have gotten AE to work with a self-signed certificate in SQL Server, but we wanted to use the Domain Certificate Authority. Any suggestions?

For my particular issue I needed to give the user access to read the Cert.

So, with the administrator of SQL Server you have to:

Open CertLM and find the certificate that you are going to use for Always Encrypted
Right click the Certificate
Press All Tasks > Manage Private Keys
Press the Add... button if the username is not there
Find your User or Group in AD
Give that User or Group read access.


Will subquery be executed first here with full scan? I have below query

select *
    from employee employee,
    (select address1.id
        from address1 add1, address2 add2
        where add1.address2_id = add2.id ) address_data
    where emp.empId = 100
    and emp.address_id = address_data.id
// index exists  on both columns i.e. address_id and id

I was expecting first subquery(address_data) will be executed internally and there will be full scan on address1 and address2 table. Then outer
query will be executed.

Revised understanding after looking at explain plain

But when I looked at explain plain looks like it is not happening. I found there was not any full scan on address1 and address2 table. Looks like
first outer query executed(which returned only record) and then only relevant records were fetched from address1 and address2 table(just fyi address tabl also returned 1 row).

This strategy looks logical to me as there is no need to do full access of address tables in this case.

My question - Is my revised understanding correct ? But I was under impression that sub query will always be executed first. Look like  I was wrong here ?

PLAN

Your understanding of the Plan seems correct.

Common Misunderstanding

People often forget that SQL is a declarative language, not a procedural language.

You should never assume sub-queries run in a certain order.


Advice on Database Design context :

Teachers create problems to their Students
A Problem have Numerals(Questions)
A Numeral(Question) have Steps to guide the student to the answer
Choice represent the type of mathematical abstraction that fits
better

Solve: (unknown variable, starting expression)
Simplify: (reference, target)
Plot: (slope,b)

Base Models and Relations:

Problems: one to Many Numerals(Questions)
Numerals: zero to Many Steps
Steps:

Requirements:

Numerals and Steps now have a choice which where described above, currently just 3 choices but it's expected to grow. only one and at least one choice
Depending on the choice the numeral or step will add new attributes
for now choices do not have any relations between them
choice in Numeral could be different to the choice in Step and vice versa (see Example)

Example: did my best to make a close abstraction to show the idea, I've made a JSON Structure, almost identical to the structure used in my front end. Maybe the example is a little bit dumb but it gives a taste.

My Attempts:
For simplicity , Models are shown with the minimum attributes

1st:

-My concerning with this approach is that the amount of FK in the Numeral and Step model. Since only one choice is possible, i would have choices-1 amount of useless columns per record

2nd:

-I'm programmatically checking for the choice field in the Numeral or the Step, and then using the proper table to fill the data, this second attempt started as a solution to fix the empty columns in the 1st approach
-My concerning with this approach is that the amount of tables will grown 2*n, since for each choice i need to create the corresponding table for step or numeral

I'm open to suggestions or ideas about this attempts.
Edit:
Thanks to @J.D for suggesting this idea. i've modified a bit because i need to keep track of the choices and their fields somewhere somewhere. I think this solution is easier to mantain and removes the need to add tables in case of new choices

3rd:

As Colin touched on, it's hard to follow what schema problem you're trying to solve with just generalities. Unfortunately the devil's in the details with this kind of thing, since one detail could drastically change the best approach.
Looking at your database diagrams at a high level the tables A, B, and C in the first diagram look like the same entity in regards to their purpose, but varies in value of their columns, most likely?...same when comparing tables ANumeral, BNumeral, CNumeral to each other, or when comparing AStep, BStep, and CStep to each other, in your second diagram. The question is why couldn't each of these sets of tables be just one table each (or possibly even one table altogether)?...e.g. you likely could have just a single StepAttributes table that Step joins to.
I did notice in your example the only difference between two tables of the same set are the attribute data types. If that's your reasoning for making multiple tables, instead just structure your single attribute table (e.g. StepAttribute) to account for that which can be done two ways.
The first way by having three columns, one being the AttributeName, the next being the AttributeValue (which will be a string type field, like VARCHAR, to accommodate any value), and the third being the AttributeDataType which stores the native data type of the attribute so you know what to appropriately cast it to when you consume it.
The second way is you can have a denormalized attribute table that has a nullable column of each data type with generic column names, such as DateAttributeValue, IntAttributeValue, VarCharAtrributeValue etc, and a column to specify which data type the value is / column it's stored in. Some systems even design such a table like DateAttributeValue1, DateAttributeValue2, DateAttributeValue3, IntAttributeValue1, IntAttributeValue2, etc so they can support storing multiple attributes within the same row.

Thanks for updating your question with more details. So at the least it looks like each sub-table to Numeral vs Step are a bit redundant in your second design, and I understand why you normalized them that way based on your first design. You could actually combine the best of both worlds though and just have a single Plot, Simplify, and Solve table with the foreign key reference field in those tables going back to your Numeral and Step tables by storing both IDs in that column and calling it something like NuneralOrStepId. Then you'd need an additional field on those tables called like IsForNumeral or ParentType (come up with a better name than mine lol) which would distinguish which base table it joins back to. Then you have no waste in fields and reduce your redundancy as well.
It depends on how many of these sub-tables you anticipate having to generate and manage (right now 3 is completely manageable) but if you think that's going to grow into over 10 tables or so, I'd still consider my first suggestion with one generic table of AttributeName / AttributeValue pairs. You can add any additional columns to this structure that make sense as well, such as the ParentType column to distinguish which parent table the Attribute belongs to.


MySQL restore only Incremental backups with innobackupex I am migrating a product to AWS and we have 1.5TB of MySQL database. I want to migrate it to AWS with minimum downtime possible. I am planning to achieve following.

Take one full backup with Percona XtraBackup innobackupex.
Restore it on AWS node.
Then on the day of final migration take an incremental backup and restore it on AWS node.

So my question is, is it possible this way ?

According to my research it is not possible to restore only "incremental" backup. The way Percona described that we need to take full backup then incremental backups and then prepare them and get a one full backup that cane be restored on destination.

First lets understand that to take an incremental backup you need to keep full backup on the server where you are taking incremental.

Though you take a full backup on SERVER(A) you may push to AWSSERVER(B). It doesn't matter, but inorder to take an incremental you need to keep the full backup copy as source backup.

UPDATE

Below is the approach for incremental, you will be able to get your answers whether the idea you have is a feasible one or not.

After Xtrabackup Full backup is done.

  mkdir -p  /backup/

  innobackupex --defaults-file=/etc/my.cnf --user=mysql
  --password=‘****’  /backup/

From here /backup/2017-06-01_18-01-52  full backup set is obtained.

Take incremental backup with full backup.

  mkdir -p /backup/INCR

  innobackupex --defaults-file=/etc/my.cnf --user=mysql
  --password=‘*****’ --incremental /backup/INCR --incremental-basedir=/backup/2017-06-01_18-01-52 --slave-info

From here /backup/INCR/2017-06-01_18-10-41/ incremental delta data is obtained.

Merge the backups

  innobackupex --defaults-file=/etc/my.cnf --user=mysql
  --password=‘*****’ --apply-log-only /backup/2017-06-01_18-01-52/ --incremental-dir=/backup/INCR/2017-06-01_18-10-41/

From here  /backup/2017-06-01_18-01-52/2017-06-01_18-22-29 Final backup set is merged to parent(FULL BACKUP) and created a sub directory out of it.

Apply logs to Final Backup Set.

  innobackupex --defaults-file=/etc/my.cnf --user=mysql
  --password=‘*****’  --apply-log /backup/2017-06-01_18-01-52/2017-06-01_18-22-29

Now this is your final data set : /backup/2017-06-01_18-01-52/2017-06-01_18-22-29/* , replace data dir to this data set -> Ensure every 1 to 4 steps had no errors and completed OK is found on all outputs.  Test the result incremental backup data on a Test DB and Restore it to see if the server starts without any error messages

If your my.cnf is pointed to /mysql/data/

  service mysqld stop
  mv /mysql/data/* /tmp/MOVED/
  cp -r /backup/2017-06-01_18-01-52/2017-06-01_18-22-29/* /mysql/data/
  chown -R mysql:mysql /mysql/data/
  chmod -R 775 /mysql/data/
  service mysqld start
  Login to mysql

Check error log and do few select queries .


Alter failed for schema 'dbo' when trying to change schema owner I have some MS Access code that uses ODBCDirect to execute some sql statements in a SQL 2008 R2 DB.

The sql statements do not specify the schema name so subsequently are executed using the windows user as the schema name and not the result im looking for. The schema should be dbo.

All users belong to a Windows group which is a SQL login.

I read that if I change the owner of the schema dbo to the windows group that sql statemenets that do not specify a schema will default dbo.

So I tried to change owner of the schema to the windows group but get message

  Alter failed for schema 'dbo'

I do seem to change alter permission on schemas in the DB as well.

Does sql allow you to change the owner if so how???

Malcolm

You can't change the owner of "dbo" because it is a system schema. From ALTER AUTHORIZATION, "Special Cases and Conditions":

  SCHEMA
  ... Cannot change the owner of sys, dbo, or information_schema

And you can't set a default schema for windows logons (yet).

This matters in 2 ways:

you are issuing DDL (CREATE TABLE) etc when you want objects to be in dbo
you won't have reusable query plans for DML without schema so different users will have different query plans

Basically, you have two options:

fix the queries
stop using ODBCDirect from MS Access to SQL Server.

The third option, ugly and dangerous, is to make the Windows logon "sysadmin" which means they will always map to dbo. I don't recommend this

Personally, I don't use the dbo schema any more: see dba.se (and the further links there)


Saved state for SQL Server Always On on Hyper-V For our SQL DEV/QA environment our system-engineers did setup an Hyper-V enviroment
- 3 hosts
- 2 VM on each host (1 for DEV an 1 for QA)

Patching for the hosts is scheduled once a month but I noticed the VM's on these hosts are put in a saved mode and not shut down gracefully during the patching.

We are running multiple SQL Server Always On AG's accross these VM.
Is it the best practice to shut the VM's down gracefully before rebooting the hosts or can SQL handle the saved state without problems?

Thanks for your help!

Since this not production, it's not so critical to do this the "right" way.  Having said that, running QA the same way as production is beneficial for ensuring everything goes well in production.

"Best practices" would be to gracefully shutdown each VM prior to rebooting the host.  That allows the AG to realize the databases need to fail over to the remaining node(s).  You'd also need to do a single host reboot at a time, so the remaining hosts are able to take the load and retain quorum.

Of course, you could just save-state the VMs and let the AG sort itself out - that is after all what high availability is designed to do.


How to prevent daily index fragmentation of 99% I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. Is there a way to prevent this by tweaking the settings?

CREATE TABLE HighScore(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user] [int] NULL,
    [player] [int] NULL,
    [round] [tinyint] NULL,
    [group] [int] NULL,
    [rank] [int] NULL,
    [delta] [int] NULL,
    [roundpoints] [int] NULL,
    [totalpoints] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [HighScore_RoundGroup_Nidx] ON .[HighScore]
(
    [round] ASC,
    [group] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

I think you should try higher FILLFACTOR settings on HighScore_RoundGroup_Nidx (e.g. 50 or 40). You can set FILLFACTOR to 0 or 100 for the PRIMARY KEY because it shouldn't fragment If it still does, FILLFACTOR does not help because the reason is that newly allocated pages interleave with other newly allocated pages. This is a well-known SQL Server issue. You could move this index into its own filegroup which would stop this problem.


Is there a name for this pattern, representing one-to-many relationships by adding columns with an index to a single table? So a domain expert wants the following relation:

CarOwners(owner_id, name, address, car_1_registration, car_2_registration, car_3_registration, car_4_registration)

The reason given that most people are unlikely to have more cars than that, and that it's easier to reason with than the general case of an arbitrary number of cars. This seems like a common pattern for Excel jockeys, whereas for a DBA it would be much easier to simply have a separate relation:

Car(registration, owner_id)

But this idea is hard to sell to anyone who doesn't know what a JOIN is.

I looked in database literature and couldn't find any references to this (arguably anti-)pattern. Perhaps there's nothing theoretically wrong with it (it's just impractical), or it's simply too goofy to even be considered by academics. But it would be interesting to hear if it's actually covered.

The Domain Expert has given you a requirement disguised as an implementation detail. I would implement this by:

Implementing a relation table as you want
Implementing a view which will give up to 4 associated registrations per owner

When an owner registers a fifth registration the system should keep working with no problem (which is probably what the DE wants) but the view he uses for reporting will still only show 4 registrations. (Maybe the view could include the total number of registrations per owner as well?)

This way you may have to change the view (used only for reporting) every now and then, rather than have to change a major table and all the relations and code that will be affected.


How to optimize table size by deleting columns or just emptying them Should I set a column to NULL Or DELETE it to reduce DB Size.
For example in the below image you can see "SmallImageURL" (number 13) should I set it to NULL or delete the column.

( If I delete the column, Application will start throwing errors, Is it worth to delete the column and fix the errors instead of emptying it ?, My concern is only about Reducing DB size and optimizing performance)

Short answer:  I doubt if it is worth doing either.

Medium answer:  It depends on a lot of things.  You could calculate SUM(LENGTH(col1)) + SUM(LENGTH(COL2)) + ... to get a crude estimate.  But (as the long answer explains) that savings may be an over- or under-estimate in the case of NULLing.  In the case of DROPping, it is likely to be an underestimate of the shrinkage.

Long answer:

(It would help if you had provided SHOW CREATE TABLE.

In MySQL, VARCHAR(512) will take a little bit of space even if empty or NULL.  At most it will take 2050 bytes since it is utf8mb4, but it is unlikely to really be that big.

Since you have a number of large VARCHAR columns, they may be placed in an off-row storage that is handled in a different way.  This depends on how bulky all the columns are and what the ROW_FORMAT is.  I am assuming you are using InnoDB; MyISAM works quite differently.

Because of the way setting a column to NULL works, you may not save as much space as you would calculate.

DROP COLUMN is a drastic approach.  It will take a long time because it will copy the table over and rebuild all the indexes.  But, in doing so, it will defragment both the data and the indexes, squeezing out more space than simply NULLing would do.  The DROP approach might reduce the table size more than you would compute.

But... If you have innodb_file_per_table = 0, no space will be returned to the OS by either method!  It might even increase the space used!  This is because a copy of the table is needed during the DROP.

With innodb_file_per_table = 1, you will recoup the freed space, but only after needing space for a copy of the table.

Why do you want to shrink the table?  My above answers assume you are getting low on disk space.

Note DROP does the same as OPTIMIZE TABLE, with the additional feature of removing the column.

If you are hoping to speed things up by shrinking the table, forget it.


Is the key_buffer_size applicable to myisam tmp tables? I have a database about 750GB in size. It's all innodb.

Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The tmp tables fit into memory.

My cache hit ratio (Key_reads / Key_read_requests) is 0.

When MySQL creates these tmp tables, I'm guessing it doesn't it create pseudo indexes to be used by key_buffer_size.

No, not at all.

Every time, I see a temp table materialize on disk, I see something like this:

-rw-rw---- 1 mysql mysql    0 May  8 17:44 #sql_22d5_2.MYD
-rw-rw---- 1 mysql mysql 1024 May  8 17:44 #sql_22d5_2.MYI

.MYI is always the default size.

On disk, they must be using the MEMORY storage engine. Again, there are no indexes created in conjunction with the temp table.

If you are concerned about many temp tables, use a RAM disk (See my post  Is it bad to create many mysql temporary tables simultaneously?)

Your goal should be avoid making temp tables, regardless of mapping tmp tables to disk or RAM.

See my other posts on this

Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
May 10, 2012 : Why is MySQL is creating so many temporary tables on disk?

Going back to your question: Since all your data is InnoDB, you can make key_buffer_size 8M


Return count from not direct related tables To clarify what I'm asking:
My main goal was how I can retrieve all Manufacturers with a count of entries next to them.

Working with 3 tables {Manufacturers (wp_manufacturers), Dealers(wp_users), Entries(wp_posts)} [plus a fourth, wp_usermeta, tied to wp_users].

Manufacturers hold brands like Suzuki Honda etc
Dealers as the users publishing an Entry that showcase a Car of a manufacturer.
Entries is the table that holds an entry with the content and the user who published.

The table Dealers has a field that connects every user with the Manufacturer (actually, currently set up through the fourth table).

What I trying to do is to have for every Manufacturer a count of entries that exist in the Entries table.

A dealer can be only with one manufacturer. Also, a manufacturer can have multiple dealers.

This is original for a WordPress site that is heavily customized and we would like a direct approach to the database.

Given this data, with 2 Manufacturers and 3 dealers:

  Manufacturers           wp_posts
+-----------------+    +-----------------------------------------------+
|id      |name    |    |ID     |post_author    |post_type |post_status |
+-----------------+    +-----------------------------------------------+
|   1    |suzuki  |    | 455   |    13         |  car     | publish    |
|   2    |honda   |    | 689   |    13         |  car     | publish    |
|        |        |    | 1689  |    2          |  car     | publish    |
|        |        |    | 1889  |    13         |  car     | draft      |
|        |        |    | 2090  |    44         |  car     | publish    |
+---^----+--------+    +------------^----------+----------+------------+
    |                           ____|
    |           wp_usermeta    |
    |          +---------+-----v-------------+------------------+
    |          |umeta_id | user_id |meta_key | meta_value       |
    |          +------------------------------------------------+
    |          | 498     |   13     | _manuf  |     1           |
    |          | 709     |   2      | _manuf  |     2           |
    |          | 1029    |   44     | _manuf  |     2           |
    |          |         |          |         |                 |
    |          +------------------------------------------------+
   ++----------------------------------------------^

The results I'd like to get are:

+-------+----------+--------------+
|  id   |   name   |   count      |
+---------------------------------+
|  1    | suzuki   |     3        |
+---------------------------------+
|  3    | honda    |     2        |
+-------+----------+--------------+

I also want to check the post_status to only count entries that have been published.

Today I read again my question and I found indeed not clear. The reason I didn't like to write from the start the table original names was not to be confused with the WordPress solution as I would like only the MySQL approach.

Example on the wp site where I use the tables of wp this returns the data but not with 0 when not an entry found in the wp_post(post_type=car) and also not sure this is the optimal way to do this

 SELECT DISTINCT id, IFNULL(COUNT(id),0),name FROM (
SELECT wp.post_author,wp.post_status,wp.post_type,wu1.meta_value FROM wp_posts wp
  LEFT JOIN (
  wp_users wu
  LEFT JOIN wp_usermeta wu1 ON wu1.user_id=wu.ID
  ) ON wp.post_author=wu.ID

  WHERE wu1.meta_key='_manuf' AND wp.post_status='publish'  AND wp.post_type='car'
 ) x
 left jOIN manufacturers ma  ON x.meta_value=ma.id

  GROUP BY id
  ;

If your dealers are related to the manufacturers and entries are related to the dealers so you have to JOIN three tables, then GROUP resulting table by manufacturers and COUNT() all entries grouped together:

SELECT w.ID AS manufacturer
     , count( q.ID ) AS counter
  FROM manufacturers AS w
  JOIN dealers       AS z ON z.manuf_ID  = w.ID
  JOIN entries       AS q ON q.dealer_ID = z.ID
 GROUP BY w.ID
 ORDER BY w.ID ASC
;


Checkpoint process RAM usage Environment:

 Centos 6.5
 PostgreSQL 9.3
 shared_buffers = 3000 MB
 checkpoint_segments = 128

Top command: top -M (shift F -> N)

The checkpointer process uses 1.3 GB RAM

Question:
Why does checkpointer process use a lot of RAM ?

Referenced:
this article, postgresql doc, interdb.jb 9.7, this post

The "VIRT" size (3.2 GB in this case) is the total amount of memory the process can see. The reason all of the postgres processes have a similar VIRT count is because they all map the 3 GB of shared memory into themselves, and in addition to that allocate whatever more they need to operate.

The "RES" size is the amount of memory touched by the individual processes. So, the checkpointer process has touched (directly read or written) about 1.5 GB of memory, most of which is almost certainly from the shared buffers pool. So, this 1.5 GB of RES is a mostly a subset of the 3 GB allocated for the shared buffers, plus a smaller amount for active per-process work.

To see which is which, use a utility such as pmap.


How do I get HeidiSQL to connect to Postgres databases for the first time? With two different Linux servers I have installed Postgres on them using no standard configurations.  They are in different networks off-site.

The Postgres service is running (as I checked by running ps -ef | grep postgres).  I have installed namp on both Linux servers.

These commands show port 5432 is not blocked:

nmap -p 5432 x.x.x.x

I can SSH to either Linux server from my Windows machine.  I have Heidi SQL on my Windows desktop.  When I try to connect to either of these servers with HeidiSQL (using port 5432), I get "could not connect to server: Connection refused...is the server running on host "x.x.x.x" and accepting TCP/IP connections on port 5432."  This has never worked before.

What should I do to get HeidiSQL to connect to a Postgres database on a Linux server?

First of all, make sure you can connect to your PostgreSQL database from your Linux machine itself (if you have two, you'll have everything on both machines).

You do that using psql:

 psql -h localhost -p 5432 postgres

This assumes psql is in your path; if not... finding where it resides depends on your specific Linux version, PostgreSQL version and installation methods; please, specify them.

If you cannot connect to the database from the local machine... make sure it is running, and that it is the process actually using the 5432 port.

Next, you need to check your PostgreSQL configuration file settings. The file to check is called postgresql.conf. Its location, again, depends on how you installed it. Check Where are my postgres *.conf files?.

In the configuration file, you need to check for the parameteres listen_addresses and port, which indicates PostgreSQL which TCP-IP addresses and ports it should connect to. You might have port 5432 blocked, and PostgreSQL listening to it, but only for your 127.0.0.1 (localhost) IP address, but not listening to the "outside world". The default setting (AFAIK) is to listen only to 'localhost'.

If your Linux box is connected to the outside world through port 192.168.1.2, and you want to use the standard 5432 port, you should have:

listen_addresses = 'localhost, 192.168.1.2'
port = 5432

If you change any of these parameters, you should stop and restart the PostgreSQL service. [There are alternatives that don't need to stop, but at this point, I think this is the easiest way.]

Once you have PostgreSQL listening to the outside world, you still need to allow connections from users. That is done through the pg_hba.conf file. You need to have some user(s) allowed to connect to your database(s) from some IP address (or some range, or all of them, ...) using a specific authentication method. The most usual settings would look like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# local net
host    all             all             192.168.1.0/24          md5

This means: all users can connect to all databases using a TCP/IP host on an address 192.168.1.0 .. 192.168.1.255, authenticating using md5 (meaning a password).

If your Linux box uses SELinux/iptables, make sure they allow the connections. Check, for instance Linux: Iptables Allow PostgreSQL server incoming request, or Connect to Postgres remotely, open port 5432 for Postgres in iptables.

If you need to setup SSL for PostgreSQL connections... check Secure TCP/IP Connections with SSL. I would suggest that you use SSL only after you have managed to have insecure (non-SSL) connections first.

Check the firewall in your Windows Machine, and/or any external Firewall between your Windows and your Linux machines. If any of them exists and are active, make sure they allow for communications to port 5432. Your specifics depend a lot on your actual setup. Resource: How to open firewall ports in Windows 10

Make sure you can ping your Linux box from within Windows. If you have telnet on your windows machine, try telnet (ip-of-your-linux-box) 5432 and check also that you can connect (as soon as you type something, you're very likely to get disconnected, that's ok).

At this point, HeidiSQL should be able to connect to your remote Linux machine. (Now, repeat with the 2nd one).