Network issues with sql server There are two servers on co-location. First is Windows Server Standard 2008 R2 SP1. Second is Windows Server 2012 + Sql Server Standard 2012 SP1.

First server has a task with a report against tables in Server B. Report runs via sqlcmd11. Normally, it takes 3-4 seconds. But sometimes it hangs in the middle of data receiving (even, If I redirect output to console) and around at 20 seconds after start connection is killed with these errors:

HResult 0x2746, Level 0, State 10
TCP Provider: An existing connection was forcibly closed by the remote host.

HResult 0x2746, Level 0, State 0
Communication link failure

Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total     21107  avg   21107.00 (0.05 xacts per sec.)

Sqlcmd runs script from file. Command line is:

"%ProgramFiles%\Microsoft SQL Server\110\Tools\binn\SQLCMD.EXE" -S "ip,port"
    -U username -P password -u -i script.sql -o output.html -m-1 -p

Query has header:

declare @today datetime = cast(cast(getdate() as date) as datetime)
declare @yesterday datetime = dateadd(day, -1, @today)
declare @days15 datetime = dateadd(day, -15, @today)
declare @days10 datetime = dateadd(day, -10, @today)

Then is contains 5 pairs:

print N'Report header'
select <report body>

Queries doesn't use table variables or any temp table. I read about tcp chimney and other tcp options. Disabling them on both sides didn't help.

Sql Server logs don't have messages about killing my process.

[Can't add comments yet so "commenting" here and a moderator can change it to a comment.]

I would recommend checking the network adapter properties on all systems involved and disabling auto-detect network speed/duplex.  If it's set to auto-detect and you don't want to change it without knowing it's causing a problem, copy some large files and see if you're getting the expected throughput.  If not, auto-detect has probably not negotiated the correct speed an duplex and this will cause poor throughput and flaky connections.  I used to do tech support for a data replication co. and we fixed so many connection stability and throughput issues with this it's not even funny.


Fixed by converting distance to numeric and rounding:

round(ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756))::numeric, 2)

I've found it here http://postgis.net/docs/manual-1.4/ST_Distance_Sphere.html, under "Examples".


Backup from Azure SQL Managed Instance and restore on an On-Prem version As per my understanding Azure SQL MI runs SQL Server under the hood. So a backup from MI can be restored on latest version of SQL Server (2019). I have a task to find a rollback strategy if the move to MI is called off after the migration.

Took backup from MI
Tried restore to SQL Server 2019 (15.0.2070.41)

I expected it a 50/50 chance of success but it failed. The error however is a bit misleading :

  Msg 3169, Level 16, State 1, Line 2 The database was backed up on a
  server running version 15.00.2000. That version is incompatible with
  this server, which is running version 15.00.2070. Either restore the
  database on a server that supports the backup, or use a backup that is
  compatible with this server. Msg 3013, Level 16, State 1, Line 2
  RESTORE DATABASE is terminating abnormally

If we can't go back smoothly to server we got the backup from then how do we plan the rollback. The bacpac/dacpac method is a it of pain when db size is 30GB and you get loads of errors.

Why is that error coming up? Need to know whats running in MI
Shall we reconsider moving to MI, with the pain of not having an easy/smooth rollback.

PS: AWS RDS provides versions which makes it much better as you have the option to just rollback if something goes wrong in the cutover.

.

Currently the only way to restore a database from Azure to OnPrem is the bacpac method.

Also FYI:
I am currently looking at Azure MI migration but due to the fact you can't point a DNS CNAME at it I am unable to Migrate.


Disk space efficient foreign key index for insert intensive scientific database? I'm working tuning a scientific database whose associated simulation is very insert intensive (i.e., run for a long time inserting data, execute summary query at the end). One of the tables is starting to cause some problems since the table size is 235 GB with index sizes of 261 GB, and the server only has 800 GB so we would like to free up a bit of space.
Currently there is one foreign key reference (integer data) that is stored as a clustered b-true. This has been good for the summary queries, but likely isn't helping the disk space issues.
Is there a more disk efficient way of storing this foreign key index? Would it make sense to switch over to a hash index instead of the b-tree index?

The referencing columns don't need to be indexed at all.  If not, then some operations on the referenced table might be extremely slow (e.g. verifying that a row to be deleted on the referenced table has no referencing rows, or if it does than cascading actions to them) but if you never do those operations it wouldn't matter.


How to recursively update a same string? I have a table with a column containing names like this:

id, employee
1, Mr. John Cole Thornton
2, Mr. Paul George Mckenzie
3, Mr. George Mick McDoughal
4, Ms. Emily Suzan Flemming
5, Mr. Alan Bourdillion Traherne

I have a second table with a list of first names, like this

id, first_name
1, Emily
2, John
3, George
4, Suzan
5, Paul
6, Alan
7, Mary
8, Mick
9, Bourdillion
10, Jim
11, Cole

And I want to remove the first names in the first table, in order to obtain this:

id, employee
1, Mr. Thornton
2, Mr. Mckenzie
3, Mr. McDoughal
4, Ms. Flemming
5, Mr. Traherne

No matter how many first names, I would like to remove them all without calling several times my first_names table, and I wonder if this would be possible without using a loop in a function.

I have tried a query like this :

WITH RECURSIVE name AS (   SELECT REPLACE(t1.employee, t2.first_name, '') sec_name
                        FROM  t1, t2
                        WHERE position(t2.first_name in t1.employee) > 0 )
SELECT sec_name FROM name ;

But I get as many output as there are first names in the input, like :

Id, sec_name
1, John Thornton
1, Cole Thornton
2, Paul Mckenzie
2, George Mckenzie
...

My Postgres version is 9.6 .

Any help will be much appreciated !

WITH RECURSIVE cte AS (
    SELECT employee, 1 id
    FROM t1
    UNION ALL
    SELECT REPLACE(employee, first_name, ''), id+1
    FROM cte
    JOIN t2 USING (id)
)
SELECT REGEXP_REPLACE(employee, ' +', ' ') employee
FROM cte
WHERE id > ( SELECT MAX(id)
             FROM t2 )

fiddle

If t2.id values do not start with 1 or have omissions, you must re-enumerate t2 records in CTE using ROW_NUMBER().

UPDATE.

Possible problem - some first_name is a substring of some employee (John and Johnson, for example). If so use spaces as additional wrappers:

WITH RECURSIVE cte AS (
SELECT employee || ' ' employee, 1 id
FROM t1
UNION ALL
SELECT REPLACE(employee, ' ' || first_name || ' ', ' '), id+1
FROM cte
JOIN t2 USING (id)
)
SELECT REGEXP_REPLACE(employee, ' +', ' ') employee
FROM cte
WHERE id > ( SELECT MAX(id)
             FROM t2 )

fiddle

PS. Trim excess trailing space if needed.


MariaDB: Connecting from LAN on Centos I have a working MariaDB 10.2 running on my CentOS 6 server. I can connect to the DB from another machine on the LAN.

Out of curiosity, I wanted to revisit how this was made possible. Everything I read mentions using bind-address in the my.cnf file, but when I examine this file, there is no such directive. Neither is there anything in the files /etc/my.cnf.d/*.cnf.

Is network access a default in this case, or is there some other mysterious directive?

https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/ says :

  MariaDB only supports one bind-address, but binds to 0.0.0.0, or ::
  (every IP) if the bind-address directive is left out).

I understand that in this case mariadb listens on every IP address of the machine. However even in this case you can filter on hostname at user account level with the user@host syntax.


administrator permissions on a SSAS database I had a third party company coming over and building ssas databases to produce reports.

They did, but now the DBA wants to have a look but I haven't got permissions, to even see those databases, as you can see on the picture below.

Can I grant myself administrator permissions?

I am system administrator. there is a AD group called SQLDBA and

When right-cliking on the server-properties-security-Add-
then adding my AD group SQLDBA it says I don't have the permissions to alter the server object as you can see on the picture below.

I had previously added that sqldba AD group as an administrator to that box.

TITLE: Microsoft SQL Server Management Studio

Either the 'mycompany\MMiorelli' user does not have permission to alter the 'myserver\instance' object, or the object does not exist.
 (Microsoft.AnalysisServices.AppLocal.Core)

BUTTONS:

OK

The SSAS permissions are completely independent from SQL Server permissions.

If you don't have permissions, there is one hope:

  By default, members of the local Administrators group are also granted
  administrative rights in Analysis Server. Although the local group is
  not explicitly granted membership in the Analysis Services server
  administrator role, local administrators can create databases, add
  users and permissions, and perform any other task allowed to system
  administrators. The implicit granting of administrator permissions is
  configurable. It is determined by the BuiltinAdminsAreServerAdmins
  server property, which is set to true by default.

Source: https://docs.microsoft.com/en-us/sql/analysis-services/instances/grant-server-admin-rights-to-an-analysis-services-instance?view=sql-analysis-services-2017

I suggest you to read the link above and try to run your Management Studio using an account that is member of Local Administrators group of your SSAS Server


Find all tables without foreign keys in a database In SQL Server, how do we find all tables which do not have either of:

foreign key constraints
references by other tables' foreign keys

SwapnilBhate's answer will return tables that are referenced by a foreign key. Adding a further LEFT JOIN will exclude these:

SELECT tbl.name
FROM sys.tables AS tbl
    LEFT JOIN sys.foreign_key_columns AS fKey
        ON tbl.object_id = fKey.parent_object_id
    LEFT JOIN sys.foreign_key_columns AS rKey
        ON tbl.object_id = rKey.referenced_object_id
WHERE fKey.parent_object_id IS NULL
    AND rKey.referenced_object_id IS NULL;


Why I don't need to COMMIT in database trigger? We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database trigger:

CREATE OR REPLACE TRIGGER user_login_as
  AFTER LOGON
    ON SCHEMA
BEGIN
  INSERT INTO user_login_log(username, log_date, action) VALUES (user, sysdate, 'User has logged in');
END user_login_as;

The trigger does not contain autonomous transaction procedure with commit inside that, so who is commiting the insert? This triggger works like a charm and inserts new record into log table after user logon. It smells like hidden Oracle functionality and I can't find any reference in Oracle docs about that. I'm using Oracle11g.

You get an autonomous transaction context for these triggers automatically.

From the CREATE TRIGGER docs:

  One or more particular states of the database that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. For each of these triggering events, the database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

What happens if the trigger fails depends on the exact trigger/event. See Exception Handling in Triggers. In particular, a logon trigger that fails with an exception might very well lock non-dba users out - the exception makes the login fail unless the user has specific privileges.


Database stuck in synchronized mode and another one in 'Initializing / In Recovery' mode I have a database which is stuck in Synchronized mode and another database in Initializing / In Recovery mode. Both of them have been removed from their availability groups and I cannot even delete them. Both of them are in secondary server and I cant even do a failover. Any help is much appreciated.
UPDATE
When I try to delete the database in this state: Initializing / In Recovery, it says Lock request time out period exceeded.
When i try to delete the database in this state: Synchronized, it says certain operations cannot be performed while database is in a mirroring session., even though I have removed it from its availability group.

Use the query SHUTDOWN WITH NOWAIT on the server, then try restarting the services for the server.
Refer link for more information:
https://blog.sqlauthority.com/2018/02/09/sql-server-change-pending-state-sql-server-configuration-manager/#:~:text=WORKAROUND%2FSOLUTION,and%20able%20to%20run%20queries.


Recursive calls in stored procedure and temporary tables I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to which subsequent recursive calls generate error.

How should this be fixed so as to avoid error?
Also, how can I improve performance of this procedure?

Definition of Stored Procedure:

-- Function: ramerdouglaspeuckerfunction2(text, double precision, integer, integer)

-- DROP FUNCTION ramerdouglaspeuckerfunction2(text, double precision, integer, integer);

CREATE OR REPLACE FUNCTION ramerdouglaspeuckerfunction2(IN table_name text, IN epsilon double precision, IN startindex integer, IN endindex integer)
  RETURNS TABLE(up_link double precision, time_stamp timestamp without time zone) AS
$BODY$
declare dmax double precision default 0;
declare idx integer default 0;
declare a integer;
declare b double precision;
declare c double precision;
declare d double precision;
declare e double precision;
declare norm double precision;
declare distance double precision;
declare i integer default 2;
declare j double precision;
declare result result_load_status;
begin
create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop;
a := endIndex - startIndex;
execute 'select up_link from ' || table_name || ' where bar2 = '|| startIndex    into d;
execute 'select up_link from ' || table_name || ' where bar2 = '|| endIndex      into e;
b := e-d;
c := -(b * startIndex - a * d);
norm := sqrt(power(a, 2) + power(b, 2));

loop
execute 'select up_link from ' || table_name || 'where bar2=' || i into j;
distance := abs(b * i - a * j + c) / norm;
if distance > dmax
then
idx := i;
dmax := distance;
end if;
i := i+1;
exit
when (i > endIndex);

end loop;

if (dmax >= epsilon)
then
insert into test
select * from ramerDouglasPeuckerFunction2(table_name,epsilon, startIndex,idx);

insert into test
select * from ramerDouglasPeuckerFunction2(table_name,epsilon, idx, endIndex);

else
execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || startIndex into result ;
execute 'insert into test values(' || result.load_status || ','|| result.time_stamp || ')';

execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || endIndex into result ;
execute 'insert into test values(' || result.load_status || ','|| result.time_stamp || ')';

end if;

return query select * from test;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION ramerdouglaspeuckerfunction2(text, double precision, integer, integer)
  OWNER TO postgres;

Error Message:

ERROR:  relation "test" already exists
CONTEXT:  SQL statement "create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop"
PL/pgSQL function ramerdouglaspeuckerfunction2(text,double precision,integer,integer) line 15 at SQL statement

Drop the table if it exists before the next run.

...
begin
drop table if exists test;
create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop;
a := endIndex - startIndex;
...

Or, as @dezso suggests, you could create the table only if it doesn't exist.

...
begin
create temp table IF NOT EXISTS test(up_link double precision, time_stamp timestamp without time zone) on commit drop;
TRUNCATE test;
a := endIndex - startIndex;
...

As far as efficiency goes, this function works about as well as it can (O=n).  That is, each statement is executed a single time and the flow is pretty linear.  It would take some knowledge of the underlying tables to make any significant improvements.  Those improvements are likely to be measured in small percentages, not geometric or exponential.


how do I change this query from SQL Server 2016 to MySQL? This is a sample of a query that I currently use regularly in SQL Server 2016 (Standard edition).  I need assistance converting this script to MySQL 5.6 syntax that will provide the same result.

Create the table:

CREATE TABLE zip_unique
(     store_nbr integer
     ,zipcode varchar(30)
     ,distance float);

Populate the table:

INSERT INTO zip_unique (store_nbr, zipcode, distance)
VALUES (123, '76135', 2.356)
     , (456, '76135', 3.002)
     , (789, '76135', 9.521)

SQL Server Query to Rank Zipcode based on the distance value:

SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
  FROM zip_unique

Output:

+-----------+---------+----------+----------+
| store_nbr | zipcode | distance | ROW_RANK |
+-----------+---------+----------+----------+
|       123 |   76135 |    2.356 |        1 |
|       456 |   76135 |    3.002 |        2 |
|       789 |   76135 |    9.521 |        3 |
+-----------+---------+----------+----------+

You can do the following:

(See also the PostgreSQL fiddle and MySQL one with your data)

CREATE TABLE zip_unique
(
  store_nbr integer
  ,zipcode varchar(30)
  ,distance float
);

Then populate:

INSERT INTO zip_unique (store_nbr, zipcode, distance)
VALUES (123, '76135', 2.356)
     , (456, '76135', 3.002)
     , (789, '76135', 9.521);

And then perform the query:

SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
  FROM zip_unique;

Result:

store_nbr   zipcode     distance    row_rank
      123     76135        2.356           1
      456     76135        3.002           2
      789     76135        9.521           3

At @Rick_James ' suggestion, I also ran this:

SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
      ,DENSE_RANK() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS D_RANK
      ,RANK()       OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS R_RANK
  FROM zip_unique;

Result

store_nbr   zipcode     distance    row_rank    d_rank  r_rank
123     76135   2.356   1   1   1
456     76135   3.002   2   2   2
789     76135   9.521   3   3   3

See the PostgreSQL fiddle here and the MySQL one here.

The ranks will vary according to the data - in this case they're the same. This was just to show that PostgreSQL will perform standard Window function code, as does MySQL (PostgreSQL is still the superior db though!).

One of the things I didn't mention previously was PostgreSQL's almost fanatical pursuit of the standards laid down by the ISO/ANSII committee on SQL.

The following is the answer to the original question - see edits to trace what's gone on prior to the above answer.

You can do the following, (see the MySQL fiddle here and the PostgreSQL one here):

CREATE TABLE fred (mary int, paul varchar(20));

Populate it:

INSERT INTO fred VALUES (1, 'sdf'), (2, 'xxx'), (3, 'yyy');

MySQL syntax seems to require this - CTAS, i.e. `CREATE TABLE AS which more or less does what you want:

CREATE TABLE bill AS SELECT * FROM fred WHERE mary <= 2;

Then

SELECT * FROM bill;

Result:

mary    paul
   1     sdf
   2     xxx

PostgreSQL does it this way (only differences shown):

SELECT * INTO TABLE bill FROM fred WHERE mary <= 2

The result is the same.

So, it appears, at least as far as I have been able to determine, that PostgreSQL does exactly what you want. MySQL will do the same thing, but it requires a slight (but hardly crippling) change to the syntax of the query. You will see from the fiddles that both syntaxes are accepted in PostgreSQL


Running a TPC-C Benchmark Without sleep() i.e. key-in + think time We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost 0 from 300 that we got at times) TPS graph even in supposedly steady state. During these tests we had not specified the sleeptimes for transactions (there are different sleep times recommended for different transaction types). So, by default all agents (for example, 100) continuously ran a mix of five transaction types without any sleeps in between. Later, we found out that we could in fact specify the sleep times; and when we did we could see a smoother graph without much variations.

So, the question is, if not specifying the sleep could be the real reason behind the bad results in first few tests?

Apologies for the late response to this, but there is a key reason as to why you are not observing an expected smooth behavior:  You have a client-server model violation when you eliminate think time.

The client server model is built upon a foundation that not all user are using system resources at the same time.  This is different than the mainframe model with terminals where all users are being serviced at the same time by the core host.   With client-server distributed processing exists and there is a delay between requests during which the resources are free'd to service the needs of other inbound users.  Normally when you place a host under load with think time these free internals tend to become smaller and smaller and as load increases you eventually reach a tipping point of no free resources to service requests.

By eliminating the think time you eliminate the free resource window to service additional requests and your tests become invalid as a predictor in real world performance.   If you ever hear a performance tester tell you that they have eliminated think time and every one user is equal to n real world users, fire them on the spot.


How do I list the constraints of a DOMAIN? Consider the following scenario:

CREATE DOMAIN dom_zipcode AS text;
ALTER DOMAIN dom_zipcode
  ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);

Now, if I want to drop that constraint with ALTER DOMAIN, The manual says:

ALTER DOMAIN name
    DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

But how can we find constraint_name? \dD only shows the constraint's definition (CHECK statement).

\dD dom_zipcode ;
                             List of domains
 Schema |    Name     | Type | Modifier |             Check
--------+-------------+------+----------+--------------------------------
 public | dom_zipcode | text |          | CHECK (char_length(VALUE) = 5)
(1 row)

I can dump the schema using pg_dump, but I believe there must exist a more elegant way to establish this using the psql terminal.

PostgreSQL's System Catalog

SELECT conname
FROM pg_constraint
WHERE contypid = 'dom_zipcode'::regtype;


DIFFs promoted to FULLs We use Ola Hallengren's DatabaseBackup stored procedure to backup a load of SharePoint databases on a SQL Server 2012 instance into Azure blob storage. We have been doing this for quite a while without any issues. However, for the last 6 weeks our DIFFs are randomly being promoted to FULL and we cannot find out why.
This is the output from the agent step

BACKUP DATABASE [Database] TO URL = N'https://strorgage.blob.core.windows.net/server/instance/Database/2020/11/diff/Database_FULL_20201105_200000.bak'
WITH NO_CHECKSUM, COMPRESSION, CREDENTIAL = N'*storeageaccountname*'

If you have a look at the generted URL you will note that the procedures is storing in the DIFF directory, but creating a FULL backup file.
https://strorgage.blob.core.windows.net/server/instance/Database/2020/11/diff/Database_FULL_20201105_200000.bak
                                                                        --^           --^

DatabaseBackup (Ola proc) is from 2019-06-14 so it needs an upgrade to be fair, but it has worked well for over 18 months.
We do not call Ola code directly as we have a small wrapper procedure which builds up the virtual path name for Azure, but essentially this is how we call Ola's code.
This is an issue were for some unknown reason DIFF backups are being promoted to FULL, this causes petabytes of Azure blob backups instead of gigabytes - each day.
EXECUTE dbo.DatabaseBackup
    @Database = @DatabaseName,
    @URL = @BackupPath,
    @Credential = @StorageAccount,
    @BackupType = @backupType,
    @Compress = @Compression,
    @LogToTable = 'Y',
    @ChangeBackupType = 'Y',
    @Updateability = @DatabaseReadOnlyState,
    @DirectoryStructure = NULL,
    @AvailabilityGroupDirectoryStructure = NULL

Do you have any thoughts on that?

You say that this promotion of DIFF to FULL backups is "random" but I'd wager you can find a connection between this activity and data churn (or index maintenance) in the database itself.
Because you're using ChangeBackupType='Y', the backup job is looking at sys.dm_db_file_space_usage to see how much of the database has been changed, and performs a FULL backup if it exceeds a threshold (I'm having difficulty discerning the default threshold from the source code). You can change that threshold by adjusting the ModificationLevel parameter, which is a percentage. From the documentation

ModificationLevel
Specify a percentage when a differential backup will be changed to a full backup. This option can only be used together with @ChangeBackupType = 'Y'.
DatabaseBackup checks allocated_extent_page_count and modified_extent_page_count in sys.dm_db_file_space_usage to calculate how much of a database that has been modified.


Issue with users creating objects in a user owned schema I gave a group (AD group) in SQL 2014, access to create, alter, delete objects in a schema [Test] in a database that they have datareader. The user testing (UserA) is able to create objects in a user schema [UserA].[Table_test]. How do I prevent this? I had to grant Create table, view, procedure to the database, but they do not have access except in schema [Test].

See Books Online for CREATE SCHEMA
The article above contains the following:
Implicit Schema and User Creation
In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:
- A login has CONTROL SERVER privileges.
- A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

This behavior is necessary to allow users that are based on Windows groups to create and own objects. However it can result in the unintentional creation of schemas and users. To avoid implicitly creating users and schemas, whenever possible explicitly create database principals and assign a default schema. Or explicitly state an existing schema when creating objects in a database, using two or three-part object names.


How do I copy a table with SELECT INTO but ignore the IDENTITY property? I have a table with identity column say:

create table with_id (
 id int identity(1,1),
 val varchar(30)
);

It's well known, that this

select * into copy_from_with_id_1 from with_id;

results in copy_from_with_id_1 with identity on id too.

The following stack overflow question mentions listing all columns explicitly.

Let's try

select id, val into copy_from_with_id_2 from with_id;

Oops, even in this case id is an identity column.

What I want is a table like

create table without_id (
 id int,
 val varchar(30)
);

From Books Online

  The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list. The IDENTITY property of a column is transferred except under the conditions defined in "Working with Identity Columns" in the Remarks section.

Down the page:

  When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

  The SELECT statement contains a join, GROUP BY clause, or aggregate function.
  Multiple SELECT statements are joined by using UNION.
  The identity column is listed more than one time in the select list.
  The identity column is part of an expression.
  The identity column is from a remote data source.

  If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. See "Creating an identity column using the IDENTITY function" in the Examples section below.

So... you could theoretically get away with:

select id, val
into copy_from_with_id_2
from with_id

union all

select 0, 'test_row'
where 1 = 0;

It would be important to comment this code to explain it, lest it be removed the next time someone looks at it.


Can I grant a user ALTER TRACE permission to a single database? I have a development user who requires the ability to use SQL Profiler on a single database. Is there a way to enable this without granting a server-wide ALTER TRACE permission? (The user should not have any access to the other databases at all.) This is Microsoft SQL Server 2008 R2.

i doubt that

the ALTER TRACE permission is a server level permission, and access is at the server level; if a user can start a trace, he or she can retrieve event data no matter what database the event was generated in.

for more info

http://technet.microsoft.com/en-us/library/cc293611.aspx

http://msdn.microsoft.com/en-us/library/ms187611.aspx


Redis docker container not coming up with custom config file I'm using the below command to run the Redis docker container

docker run -tid -v /data1/REDIS_DOCKER_IMAGE/6379/redis.conf:/usr/local/etc/redis/6379/redis.conf
-p 6379:6379 --name node_6379  redis:5.0.8 redis-server /usr/local/etc/redis/redis.conf

After I run this command, I check "docker ps" but it comes up empty. There are no logs shown by docker logs so I don't know what's going wrong.

It turns out that in my custom config file I had the daemonize option enabled and after disabling that my container is working fine.


insert multiple authors only if it exists in MySQL I have a problem . I am doing a PHP -My SQL project . I got to store author/s of a conference paper in a database . But sometimes its just 1 and sometimes it can be 2 or 3 or 10 . So how do i store this without creating rows like author1 ,author 2, ... author n . As for each conference papaer the number of authors will be different .How do i design the table , please help

conference paper table

authors table

As this is a m:n relationship you'd need an intermediate table that links authors to papers, e.g.:

CREATE TABLE paper_author (
  paper_id INT,
  author_id INT,
  PRIMARY KEY(paper_id, author_id)
);

Then you can check for all authors of a paper with:

SELECT paper.name, author_name
  FROM paper
  JOIN paper_author ON paper.id = paper_author.author_id
  JOIN author       ON paper_authors.author_id = author.id
 WHERE paper.name = ...;

Same for all papers by a specific author with

 WHERE author.name = ...;

Or if you you want a list of authors for each paper on a single row you can use GROUP BY and the GROUP_CONCAT() aggregate function:

SELECT paper.name, GROUP_CONCAT(author_name)
  FROM paper
  JOIN paper_author ON paper.id = paper_author.author_id
  JOIN author       ON paper_authors.author_id = author.id
 GROUP BY paper.name;


Can CPU cores be separated for MySQL? Is it possible to separate CPU cores in Mysql?

I understand it is possible for Memory as we can tune it in innodb_buffer_pool_size or innodb_additional_mem_pool_size. How about CPU cores in Hardware? Or something in OS Level? It is going to be on RHEL 6.5.

EDITED:

I have a standalone Server with

2*8 Processor
32 GB Ram

I would like to run two Mysql instances one to use only 2 processors and another instance should use remaining all? Is it possible in defining them in server variables of Mysql?

If you mean getting MySQL to engage multiple cores, you have come to the right place.
In order to get MySQL use multiple core for InnoDB, you must use the new settings that came with the InnoDB Plugin starting with MySQL 5.1.38.
Rather than reinventing the wheel, here are my earlier posts on this subject for InnoDB

Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

Give it a Try !!!


Make primary key and unique id as foriegn key I have two tables as follows:

UserProfile

CREATE TABLE IF NOT EXISTS USER_PROFILE (
  id                BIGINT       NOT NULL AUTO_INCREMENT,
  user_id           BIGINT       NOT NULL,
  name              VARCHAR(100) NOT NULL,
  username          VARCHAR(100) NOT NULL,
  mobile            BIGINT       NOT NULL,
  description       VARCHAR(100),
  created_date      DATETIME,
  modified_date     DATETIME,
  PRIMARY KEY (id),
  UNIQUE (user_id)
);

UserDescriptionHistory

CREATE TABLE IF NOT EXISTS USER_DESCRIPTION_HISTORY (
  id           BIGINT        NOT NULL   AUTO_INCREMENT,
  user_id      BIGINT        NOT NULL,
  description  VARCHAR(100),
  dt_datetime  DATETIME      NOT NULL   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (id)
);

SHOW CREATE TABLE UserProfile

CREATE TABLE `CYCLOS_USER_PROFILE` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` bigint(20) DEFAULT NULL,
  `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `mobile` (`mobile`)
   ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

I read that the best practice is to reference pk from the parent table. But I also need the unique constraint from the parent table which is the user_id. So can I reference both pk and unique constraints on foreign key? If yes, how can I do that? Thanks in advance!

The first table, user_profile has two unique constraints, the PRIMARY KEY on (id) and the UNIQUE on the non-nullable (user_id).

That makes it possible to reference either of them in FOREIGN KEY constraints from other tables. You can use what you have, to reference the PK:

CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (id)

or, to reference the UNIQUE column:

CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (user_id)

There is no need to reference both of them.


SQL Server 2008 - How did I solve the need to check the column definitions quickly? Another tool I developed for myself, with regards to making the creation of various DML operations easier in a large database of hundreds of tables, is a nifty user defined function to easily show almost all relevant information about a column. Running the function will display the type, length, potential key constraints (FK or PK), potential indexes as well as potential triggers tied to the column.

Code posted in the answer below. There may be situations where the function doesn't work, depending on some database settings or features that don't exist in our project. But it should be relatively dynamic.

Again, if you have any similar useful tips or advice, don't hesitate to share!

Run the following script to create the function:

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'getColDef')
    DROP FUNCTION dbo.getColDef;
GO

CREATE FUNCTION getColDef (@TAB_AND_COL VARCHAR(256))
RETURNS @RESULT TABLE
   ([Table] varchar(256),
    [Column] varchar(256),
    [Type] varchar(64),
    [Length] int,
    [IsNullable] varchar(5),
    [FK_Name] varchar(256),
    [Index_Name] varchar(256),
    [Trigger_Name] varchar(256))
AS
BEGIN
   DECLARE @TNAME VARCHAR(256), @COL VARCHAR(256), @CHARIX INT

    SET @CHARIX = CHARINDEX('.', @TAB_AND_COL)
    SET @TNAME = LEFT(@TAB_AND_COL, CHARINDEX('.',@TAB_AND_COL)-1)
    SET @COL = SUBSTRING(@TAB_AND_COL, @CHARIX + 1, LEN(@TAB_AND_COL) - LEN(@TNAME))

    INSERT INTO @RESULT
        SELECT DISTINCT OBJECT_NAME(c.OBJECT_ID) [Table],c.name [Column],t.name AS [Type],c.max_length AS [Length]
            , CASE C.is_nullable WHEN 1 THEN 'Yes' ELSE 'No' END AS [IsNullable]
            , FK.FK_NAME as FK_Name
            , IDX.IdxName as Index_Name
            , trig.name as Trigger_Name
        FROM sys.columns c
        JOIN sys.types t ON c.user_type_id=t.user_type_id
        LEFT JOIN
            (SELECT f.name AS FK_NAME
                , OBJECT_NAME(f.parent_object_id) AS FK_TABNAME
            FROM sys.foreign_keys AS f
            INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
            WHERE OBJECT_NAME(f.parent_object_id) = @TNAME
            AND COL_NAME(fc.parent_object_id,fc.parent_column_id) = @COL) FK
                ON FK.FK_TABNAME = OBJECT_NAME(c.OBJECT_ID)
        LEFT JOIN
            (SELECT i.Name AS IdxName, OBJECT_NAME(i.object_ID) AS TName, c.Name AS CName
            FROM  sys.indexes i
            JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
            JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
            WHERE c.name = @COL
            AND OBJECT_NAME(c.object_id) = @TNAME) IDX
                ON IDX.TName = OBJECT_NAME(c.object_id)
        LEFT JOIN sys.triggers trig ON trig.parent_id = c.object_id
        WHERE C.object_id = object_id(N''+ @TNAME +'') AND c.name = @COL

    RETURN
END

To execute the function, simply run the following script:

SELECT * FROM getColDef('TABLENAME.COLUMN_NAME')

Similarly, you can get the column definitions for an entire table by executing the following script:

DECLARE @TABLENAME VARCHAR(256)
SET @TABLENAME = 'TABLENAME' -- Put the name of the table here

DECLARE @RESULTS TABLE([Table] varchar(256),[Column] varchar(256),[Type] varchar(64),[Length] int,[IsNullable] varchar(5)
    ,[FK_Name] varchar(256),[Index_Name] varchar(256),[Trigger_Name] varchar(256))
DECLARE @ROW_NOW INT, @ROW_MAX INT, @COLNAME VARCHAR(256)

SELECT @ROW_NOW = MIN(ROWNUM), @ROW_MAX = MAX(ROWNUM)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) = @TABLENAME) SRC

WHILE @ROW_NOW <= @ROW_MAX
BEGIN
    SELECT @COLNAME = name FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) = @TABLENAME) SRC
    WHERE ROWNUM = @ROW_NOW

    INSERT INTO @RESULTS
    SELECT * FROM getColDef(@TABLENAME+'.'+@COLNAME)

    SET @ROW_NOW = @ROW_NOW + 1
END

SELECT * FROM @RESULTS ORDER BY [Column]


Row lock contention issue with large transactions I have a situation where we are acquiring lock on an object from database using SELECT FOR UPDATE. This is necessary for us to insert and delete records from multiple tables in an orderly fashion. The functionality works something like this.

Login -> Acquire lock on unique lock object and insert records to multiple tables and release lock -> Logout -> Acquire lock on same unique lock object and delete records from multiple tables and release lock.

We have synchronization enabled to track users have logged in before logging him out. It is taken care in Java code. However we obtain another lock at database level to make sure the database transactions are synchronized when large number of users are logging in.

Problem: The whole system works perfectly in multi-clustered servers and singleton servers. However, when the number of concurrent users reaches 4000+, we are facing row lock contention (Mode 6) in the database. And few users are not able to login.

Objective: To fix the locking mechanism to enable users to login and logout successfully.

Things tried so far: Added NOWAIT and SKIP LOCKED to SELECT FOR UPDATE query. This doesn't solve my problem because the first one simply throws an error and the second one basically skips the lock which would affect synchronization.

Need suggestions and opinions from Database experts to resolve this issue. TIA.

This doesn't solve my problem because the first one simply throws an
  error

That is part of the solution. You can catch the error/exception then display the appropriate message to the client ("you are already logged in", "connection limit exceeded", etc.).

This is not something we can solve in the database.

I have seen a similar extreme case, where transactions were blocked with the event enq: TX - allocate ITL entry, that is something that can be managed in the database.

But I have yet to see a case where enq: TX - row lock contention is a database problem.


Convert SQL Server UPDATE Statement for Use in Oracle I can not get this UPDATE statement to work in an Oracle environment. It was written for SQL server.

I am looking for some guidance on how to convert it.

UPDATE
    SOB
SET
    COA =   CASE
                WHEN
                    (
                        SELECT COUNT(*)
                        FROM SOB
                        WHERE
                            ORD = T.ORD AND
                            SHP_KEY <= T.SHP_KEY AND
                            SHP_DTTM <= T.SHP_DTTM AND
                            SOB_KEY <= T.SOB_KEY
                    ) > 1 THEN 0
                ELSE 1
            END
FROM
    SOB T
WHERE
    COA IS NULL

Just remove the FROM clause and replace the reference to the alias with the table name:

UPDATE
    SOB
SET
    COA =   CASE
                WHEN
                    (
                        SELECT COUNT(*)
                        FROM SOB s2
                        WHERE
                            s2.ORD = SOB.ORD AND
                            s2.SHP_KEY <= SOB.SHP_KEY AND
                            s2.SHP_DTTM <= SOB.SHP_DTTM AND
                            s2.SOB_KEY <= SOB.SOB_KEY
                    ) > 1 THEN 0
                ELSE 1
            END
WHERE
    COA IS NULL


How to arrange rows selected in MYSQL query? I'm fetching data from a table "TABLE_A", it's having Primary Key as "PK_ONE" (which is unsigned INT) .

I'm fetching data from "TABLE_A" with where clause on PK_ONE ;
Ex: SELECT PK_ONE from TABLE_A where PK_ONE in (2,88,3999,4,282,33399,1,394);

When I'm using dataset mentioned above in PHP cursor; I want to ensure that data fetched comes in the sequence of values of "PK_ONE" in WHERE clause.

Can you please give me some direction to achieve same? [Currently rows are coming in asending order]

You are looking for ORDER BY FIELD(), and it looks like this:

SELECT
  PK_ONE
from
  TABLE_A
where
  PK_ONE in (2,88,3999,4,282,33399,1,394)
ORDER BY
  FIELD (PK_ONE, 2,88,3999,4,282,33399,1,394)

FIELD() is a function which returns the position of an element (first argument) in a list of values (2nd, 3rd, ... arguments).

So you need to generate/template the list of values twice for this query.


Using pt-table-checksum to compare a slave's DB to another slave I understand that pt-table-checksum can do checksum compares between a master's DB and a slave's DB easily.

However, let's say your master has two slaves replicating from it for the same DB, i.e. Slave 1 and Slave 2.

Is there a way to use pt-table-checksum to run a direct checksum comparison between Slave 1 and Slave 2's databases? In this scenario, I am not as concerned with comparing any of the slaves with the master itself.

Thanks
IS

If I'm not mistaken the pt-table-checksum uses report_host value to find slaves.

May I suggest:

stop slave2. Note its current position.
Temporarily set up slave2 to replicate slave1 (binglog needs to be enabled on slave1)
Start only the io thread to get replication chain (i.e. start slave io_thread;)
Run pt-table-checksum from slave1
After check is done, stop slave and restore original replication on slave2 with saved position

This way you don't run the risk of messing up any slave.


What are the pros and cons of using Postgres over SQLite for tiny and small projects? I'm a fullstack junior programmer with a few side projects, every with a few tables and a few hundred or thousand rows at max.
I like to use a dockerized Postgres even for tiny projects, since it's more fully-featured (from small things such as the built-in regexp function, which SQLite does not have, to bigger, such as Row Based Access Control - all of which I can do without, but they are convenient) and my knowledge will carry over to bigger projects. Its memory footprint seems to be pretty manageable even for 1GB VMs as well, as the Postgres container seems to consume around 100/200 MBs at max, therefore, at least as far as my reason goes, it's definitely not a problem resource-wise.
SQLite, on the other hand, obviously uses even less resources and it seems to be heavily implied it should be used for such tiny projects for other reasons I'm not fully aware of.
What would be the potential reasons to use SQLite over Postgres for small projects?

The main differences will be the features you need, for the applications you'll be developing and their use cases. It sounds like if your PostgreSQL instances are only consuming up to 200 MBs of memory at a time, you already have tiny databases, and it doesn't really matter too much if you choose one way or the other due to data size, rather it'll be more feature specific which as you realize SQLite is a much simpler database lacking a lot of addition features a full RDBMS like PostgreSQL has.
Personally, I'd say unless the environment you're working in / hosting your data on is constrained to use SQLite like mobile development (because of its portability for offline storage) I'd opt for a full RDBMS system by default.


Error While restoring DB2 db from AIX to AIX server? I am required to migrate 1 database  One AIX server to another AIX server.
To achieve, I took backup from 1 machine and followed below steps

1st Command I tried is as  following.

db2 restore db DBNAME FROM /home/LOCATION taken at 20130912184605 into DBNAME1 REDIRECT generate script DBNAME1.sql

Which generated 1 sql file. Then I tired to run below command.

db2 -tvf DBNAME1.sql

I am getting following error

UPDATE COMMAND OPTIONS USING S ON Z ON DBNAME_NODE0000.out V ON
DB21005E  An error occurred while accessing the file "DBNAME_NODE0000.out".

Z ON DBNAME_NODE0000.out instructs the DB2 command line processor to redirect all output to a file named DBNAME_NODE0000.out in the current directory. Apparently, you don't have permissions to create files in that directory.


How to speed up a loading data into MySQL from a zipped archive I dumped a large database to an archive with:

mysqldump --compress --max_allowed_packet=500M -h myhost mydb | gzip > /tmp/mydb.sql.gz

This took about 10 minutes to run, and generated a file about 400 MB in size.

I then tried to load it into a database server, configured identically to the original, but running on my localhost with:

gunzip < /tmp/mydb.sql.gz | mysql --quick --reconnect -u myuser -pmypass -h localhost -D mydb

This ran for over 12 hours before I finally killed it. What am I doing wrong that would cause it to dump so quickly but load so slowly?

My localhost is running MySQL 5.1 running on Ubuntu 11.10.

The database is designed as a data warehouse, using both InnoDB and MyISAM tables, so most columns are indexed, causing fast reads but slow writes. Is MySQL enabling these indexes before all data is loaded, and attempting to update the index after inserting each row?

Please keep in mind the behavior a mysqldump

It dumps all databases in alphabetical order
It dumps all tables in each database in alphabetical order
It loads each tables like this

Drops the table
Creates the Table
Table is Loaded

Disables Keys
Locks the Table for Writes
Multiple Exteneded INSERTs
Unlock the Table
Enable Keys

When the keys of table are disabled, only PRIMARY KEYs and UNIQUE KEYs are reloaded. When keys are enabled, then all non-unique keys are built linearly.

I would recommend you add this to /etc/my.cnf setting bulk_insert_buffer_size as follows

[mysqld]
bulk_insert_buffer_size=256M
key_buffer_size=1G

because the default is 8M. You do not need to restart mysql. Just run this SQL command

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

Give it a Try !!!


Is it better to delete then insert, or to update then insert in SQL server? I'm using SQL server 2005.  I have a table that is basically a list of ids with timestamps that is recording the last time an event happened for that id.

I have a list of ids in a table variable, and I want to update the table with the current time for all the ids that are already in the table, and insert new records for the ids that are not yet in the table.  SQL 2005 doesn't have a MERGE statement or a ON DUPLICATE KEY UPDATE.

Which of these forms is preferred:

UPDATE IdTimes SET LastEventTime = GetDate() WHERE Id in (SELECT NewId FROM @NewIds)

INSERT INTO IdTimes(Id, LastEventTime)
   SELECT NewId, GetDate() FROM @NewIds
   WHERE NOT EXISTS (SELECT Id FROM IdTimes WHERE NewId = Id)

Or:

DELETE FROM IdTimes WHERE Id IN (SELECT NewId FROM @NewIds)
INSERT INTO IdTimes(Id, LastEventTime) SELECT NewId, GetDate() FROM @NewIds

Or is there a better solution to achieve this?

UPDATE then INSERT usually. Simply, it's less work.

In this case, you have an ID columns (IDENTITY): I'll assume this is the clustered index

You delete rows, you leave gaps in pages = fragmentation. You add rows, you probably need more pages allocated. Other processes are doing this too.

An UPDATE will update in-situ and you'll have a less expensive INSERT because there is less rows.

Saying that...

If your new:update is 100:1 then it doesn't really matter of course. And the EXISTS is required too.

However, from a raw "shifting data" perspective UPDATE..INSERT would be my choice


What are changes made to a database schema that is already in 4NF that would break applications? For this question I consider applications select and use columns by name (so I am excluding use of select * as a breaking change).

Asked in a different way, is it the case that once a Database schema is in 4NF then any change that needs to be applied to it should only be an "additive" change and therefore not break any existing applications that use it?

For me I am discounting some obvious changes:

Drop column (This change can be held back and only applied once all uses of the column are no longer active)
Drop table (Same as drop column)

For me I am considering "additive" changes as non-breaking:

Add column
Add table
Denormalisation (Add column + whatever triggers etc are required)

For me the "Refactoring" change that would break applications is to alter a columns datatype (e.g. Change from INTEGER to VARCHAR type)

What other changes can be made to a schema in 4NF that will break applications using that schema?

UPDATE: Currently we have:

Changing the data type in a breaking way (INTEGER -> VARCHAR)
Adding a constraint (or tightening an existing constraint)

Q: Are there any schema design changes that can take a design from 4NF into a different 4NF design?

Most changes are "breaking" if you aren't careful.  Any change to code for example (you mentioned triggers).  Any new or modified constraints, calculated columns etc.

You could even add a new index and effectively "break" your application because a query that used to run sub second now runs in seconds, minutes, even hours because the query plan has changed.

Assuming your code is written to always specifically name columns will probably make adding new columns reasonably safe and adding a table almost always is but you still probably want to test how each change to your database affects your application and it's performance.


Can't add another node from primary node with MongoDB replication set On CentOS 7.

MongoDB version: 3.2.15

There are two nodes:

node1
node2

node1

/etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 node1
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.0.100              node1
192.168.0.101              node2

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /mongo-metadata
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1 192.168.0.100  # Listen to local interface only, comment to listen on all interfaces.

security:
  keyFile: /root/keyfile

#operationProfiling:

replication:
  replSetName: rs0

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:

node2

/etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 node2
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.0.100              node1
192.168.0.101              node2

/etc/mongod.conf

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /mongo-metadata
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1 192.168.0.101  # Listen to local interface only, comment to listen on all interfaces.

security:
  keyFile: /root/keyfile

#operationProfiling:

replication:
  replSetName: rs0

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:

Run mongo command on node1

mongo
rs.initiate()
use admin
db.createUser(...)
db.auth('admin', '...')
rs.add('node2')

Caused error:

{
    "ok" : 0,
    "errmsg" : "Quorum check failed because not enough voting nodes responded; required 2 but only the following 1 voting nodes responded: node1:27017; the following nodes did not respond affirmatively: node2:27017 failed with No route to host",
    "code" : 74
}

Is it can't connect to node2 from node1? Is there a official guide about how to do mongodb replication set?

Answer is simple. You have not open your firewall for port 27017, is must be done on both nodes.

And asked documentation can be found here!


Can I replace these left joins with a union or some other optimization? I'm trying to optimize a query that returns the membership of objects in two groups.  Group "a" is a one-to-one link table (ie. object_id is a unique foreign key in group_a) and group "b" is one-to-many (object_id can have multiple entries in group_b).  Here is a simplified version of the query:

-- show objects and their membership w/ LEFT JOIN
SELECT DISTINCT o.name,
    a.object_id IS NOT NULL as in_group_a,
    b.object_id IS NOT NULL as in_group_b
FROM object o
LEFT JOIN group_a a ON (a.object_id = o.object_id)
LEFT JOIN group_b b ON (b.object_id = o.object_id)
WHERE a.object_id IS NOT NULL
OR b.object_id IS NOT NULL;

It returns something like this:

+--------+------------+------------+
| name   | in_group_a | in_group_b |
+--------+------------+------------+
| Fred   |          1 |          0 |
| Barney |          1 |          1 |
| Betty  |          0 |          1 |
+--------+------------+------------+

Note that Barney is in both groups but only one row is returned; this is critical.  I tried to rewrite this query with a union:

-- show objects and their membership w/ UNION
SELECT o.name, tmp.in_group_a, tmp.in_group_b
FROM ((
    SELECT a.object_id, 1 as in_group_a, 0 as in_group_b
    FROM group_a a
) UNION (
    SELECT b.object_id, 0 as in_group_a, 1 as in_group_b
    FROM group_b b
)) tmp
INNER JOIN object o ON (tmp.object_id = o.object_id);

This query is 10 times faster but I get multiple rows for objects in both groups:

+--------+------------+------------+
| name   | in_group_a | in_group_b |
+--------+------------+------------+
| Fred   |          1 |          0 |
| Barney |          1 |          0 |
| Barney |          0 |          1 |
| Betty  |          0 |          1 |
+--------+------------+------------+

I'm guessing the original query is slow because it starts with the object table, which is large and most entries are not in either group.

Is it possible to make the union query return the results I want, and if not is there another way to optimize this query?

FYI, here are my simplified example tables and data:

CREATE TABLE object (
    object_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    name VARCHAR(64) NOT NULL,
    PRIMARY KEY (object_id)
);

CREATE TABLE group_a (
    object_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE KEY (object_id),
    FOREIGN KEY (object_id) REFERENCES object (object_id)
);

CREATE TABLE group_b (
    group_b_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    object_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    description VARCHAR(64) NOT NULL,
    UNIQUE KEY (group_b_id),
    KEY (object_id),
    FOREIGN KEY (object_id) REFERENCES object (object_id)
);

INSERT INTO object VALUES (1, "Fred"), (2, "Barney"), (3, "Wilma"), (4, "Betty");
INSERT INTO group_a VALUES (1), (2);
INSERT INTO group_b VALUES (1, 2, "a"), (2, 4, "a"), (3, 4, "b"), (4, 4, "c");

Thanks!

@Jorge Campos is correct

SELECT o.name, sum(tmp.in_group_a), sum(tmp.in_group_b)
FROM ((
   SELECT a.object_id, 1 as in_group_a, 0 as in_group_b
   FROM group_a a
   ) UNION (
   SELECT b.object_id, 0 as in_group_a, 1 as in_group_b
   FROM group_b b
   )) tmp
INNER JOIN object o ON (tmp.object_id = o.object_id)
GROUP BY o.name;


Does MySQL run WHERE clause before ORDER BY? I have this query:

SELECT my_code
FROM ranges
WHERE 123456789 BETWEEN first_number AND last_number
ORDER BY type_of_code ASC LIMIT 1 ;

My question is, will it run the ORDER BY after it has selected rows from the WHERE clause?

I have KEY on first_number and on last_number. My table is around 5 million rows. I do not wish to ORDER BY first.

The logical order in which a query is evaluated is:

FROM / JOIN clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

so from a logical point of view, yes the WHERE should be evaluated before the order by.

But the DBMS:s (in this case MySQL) is allowed to evaluate the query in any order as long as the result of the query is preserved. So in a future version it might evaluate a query in another evaluation order which may cause unexpected performance effects.

Now, since ORDER BY basically transforms the query into a cursor I think it is pretty safe to assume that the WHERE clause will be evaluated before the ORDER BY (at least as long as the table is row-based, for a column-based table the situation may be different).


Use limit with where clause in in mysql I know where clause search and display the records as per criteria comes true, and limit is use to fetch the number of records from a table.

But please help me when I am trying to use them both together.

I have a table with following records

Table: records

+---------+--------+------------+
|  id     |  type  |   Credits  |
+---------+--------+------------+
|    1    | my     |   100      |
|    2    | my     |   200      |
|    3    | other  |   50       |
|    4    | my     |   500      |
+---------+--------+------------+

there are many different such records in the table after on an other.

I want to fetch any 3 records by type columns then I designed a following query!

select * from `records`
where `type` = 'my'
limit 3

I got 2 results with type 'my' however I want to skip the other type data and then top 3 records with type 'my'

  How can I do this with mysql database.

thanks in anticipation!

You need to use order by credits desc (descending) in order to get the 3 highest credits so the sql would be:

select * from `records`
where `type` = 'my'
order by credits desc
limit 3;


Temporal database design, with a twist (live vs draft rows) Not sure if/how I should be cross-posting this... But in case some follow DBA but not SO...

Is there a generally accepted means of managing live vs non-live rows in a row-version controlled environment? And if not, what have you tried and been reasonably successful with?

(Full details on the SO thread).

I'm looking into implementing object-versioning with the added twist of needing to have both live and draft objects, and could use the insights from someone experience in this, as I'm beginning to wonder if it's even possible without potentially horrific hacks.

I'll break it down to posts with tags for the sake of the example, but my use-case is a bit more general (involving slowly changing dimensions - http://en.wikipedia.org/wiki/Slowly_changing_dimension).

Suppose you've a posts table, a tags table, and a post2tag table:

posts (
 id
)

tags (
 id
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id)
)

I'm in need of a couple of things:

Being able to show exactly how a post looked like at an arbitrary datetime, including for deleted rows.
Keep track of who is editing what, for a complete audit trail.
Needs a set of materialized views ("live" tables) for the sake of keeping referential integrity (i.e. logging should be transparent to the developers).
Needs to be appropriately fast for live and the latest draft rows.
Being able to have a draft post coexist with a live post.

I've been investigating various options. So far, the best I've come up with (without points #4/#5) looks a bit like the SCD type6-hybrid setup, but instead of having a current boolean there's a materialized view for the current row. For all intents and purposes, it looks like this:

posts (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)

post_revs (
 id,
 rev pkey,
 public,
 created_at,
 created_by,
 deleted_at
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)

tag_revs (
 id,
 public,
 rev pkey,
 created_at,
 created_by,
 deleted_at
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by
)

post2tag_revs (
 post_id,
 tag_id,
 post_rev fkey post_revs(rev), -- the rev when the relation started
 tag_rev fkey tag_revs(rev), -- the rev when the relation started
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_rev, tag_rev)
)

I'm using pg_temporal to maintain indexes on period(created_at, deleted_at). And I keep the various tables in sync using triggers. Yada yada yada... I created the triggers that allow to cancel an edit to posts/tags in such a way that the draft gets stored into the revs without being published. It works great.

Except when I need to worry about draft-row related relations on post2tag. In that case, all hell breaks loose, and this hints to me that I've some kind of design problem in there. But I'm running out of ideas...

I've considered introducing data duplication (i.e. n post2tag rows introduced for each draft revision). This kind of works, but tends to be a lot slower than I'd like it to be.

I've considered introducing drafts tables for the "last draft", but this quickly tends to become very very ugly.

I've considered all sorts of flags...

So question: is there a generally accepted means of managing live vs non-live rows in a row-version controlled environment? And if not, what have you tried and been reasonably successful with?

Cross-posting the answer as well:

I think I nailed it. Basically, you add a (unique) draft field to the relevant tables, and you work on the drafts as if they were a new post/tag/etc.:

posts (
 id pkey,
 public,
 created_at stamptz,
 updated_at stamptz,
 updated_by int,
 draft int fkey posts (id) unique
)

post_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by,
 draft fkey tags (id) unique
)

tag_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by,
 pkey (post_id, tag_id)
)

post2tag_revs (
 post_id,
 tag_id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_id, tag_id, created_at)
)


Preparing CREATE statement I try to have a view created on the fly.
In my case it's a pivot view on a EAV-style table.

Assuming a table which contains two attributes I use a statement like this, executed inside of a trigger function (written in PL/Python):

CREATE OR REPLACE VIEW pivot_eav AS
  SELECT ct.id,
         ct.attr1,
         ct.attr2
  FROM crosstab(
    '
      SELECT entity, attribute, value
      FROM eav_table
      ORDER BY 1
    '::text,
    '
      VALUES (''attr1''), (''attr2'')
    '::text
    )
  AS ct(id    text,
        attr1 text,
        attr2 text
    );

But now given I don't know the names of the two attributes at creation time. I will have to insert them at runtime.
I can build the statement on the fly and execute it. Actually that's what I do at the moment:

"""
CREATE OR REPLACE VIEW pivot_eav AS
  SELECT ct.id,
         ct.attr1,
         ct.attr2
  FROM crosstab(
    '
      SELECT entity, attribute, value
      FROM eav_table
      ORDER BY 1
    '::text,
    '
      VALUES (''{0}''), (''{1}'')
    '::text
    )
  AS ct(id text,
        {0} text,
        {1} text
    );
""".format("attr1", "attr2")

But I'm aware that this is highly insecure, because input is executed.

I tried to use a prepared statement like this:

CREATE OR REPLACE VIEW pivot_eav AS
  SELECT ct.id,
         ct.attr1,
         ct.attr2
  FROM crosstab(
    '
      SELECT entity, attribute, value
      FROM eav_table
      ORDER BY 1
    '::text,
    '
      VALUES (''$1''), (''$2'')
    '::text
    )
  AS ct(id text,
        $1 text,
        $2 text
    );

which doesn't seem to be legal.

I was able to circumvent the first to placeholder by using dollar sign escaping, but still the second occurrences seem not to e legal in those positions!?

I also wasn't able to use dollar escaping in that place, because the names are use as literal there.

My question:
Is it correct, that placeholder in prepared statements only have special uses? (And can be used in arbitrary positions like string interpolation in programming languages.)
If so, is there another, secure, way to put that statement together?

Robin

DDL queries, including CREATE statements, cannot be prepared.

Moreover, even in DML queries that can be prepared, parameters cannot be used for identifiers. They're allowed at places in the query where a literal would be allowed.

As an alternative, you can safely inject column names into a query with the dedicated function that PL/Python provides for dynamic SQL: plpy.quote_ident(string):

https://www.postgresql.org/docs/current/static/plpython-util.html


How to optimize MariaDB database when it is serving queries We would like to run an optimization on our MariaDB database while still allowing it to serve queries.

I see that for MySQL this is possible since 5.7.4 (from this link)

  For InnoDB tables prior to 5.7.4 and other table types, MySQL locks
  the table during the time OPTIMIZE TABLE is running. As of MySQL
  5.7.4, OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables.

Does anyone know how we can do it for MariaDB as well? I see no mention at the MariaDB OPTIMIZE page.

Do you want to optimize MariaDB tables or do you want to run OPTIMIZE TABLE on MariaDB? Because those are 2 things that have nothing to do with each other.

Optimizing, in general, is not a process as easy as running a command- you need to analyze your server, profile your queries, change your schema, check your configuration; select, dimension and configure your hardware and os correctly, and use it efficiently from your application.

OPTIMIZE TABLE is a command that -to summarize-, for MyISAM and InnoDB MySQL/MariaDB engines, it defragments your table- in most cases with no real impact on performance, although it can save space under certain circunstances. Please do not execute optimize table regularly, it serves no purpose in most cases, while creating a lot of work for the servers (it basically recreates the table).

Online alter table is only possible on MySQL >=5.6+, and equivalent versions of MariaDB. OPTIMIZE TABLE basically runs ALTER TABLE <table> ENGINE=InnoDB for InnoDB tables, which is known to be able to be online since 5.6.17 for MySQL. I cannot say for sure for MariaDB 10+, as it will depend on the exact version, but that is as easy to check as forcing it to be online with the LOCK=NONE parameter, which would fail immediately otherwise.

If for any reason you cannot run a table defragmentation online, you can always use pt-online-schema change and perform a null operation, which will copy the new table without (almost) any locking.


Using a Parameter Query to generate Charts in MS Access Reports I am using Microsoft Access 2007 and I have a query which looks something like this;

PARAMETERS Identifier TEXT(255);

SELECT
  DateDiff('h', (SELECT Min(SampleDateTime) FROM Specimen WHERE UserID=[Identifier]), SampleDateTime) AS ElapsedTime, Specimen.ValueA
FROM Specimen
WHERE Specimen.UserID=[Identifier]
ORDER BY Specimen.SampleDateTime

Now, this query works fine if I open the query and then enter the appropriate 'Identifier' value in the form that pops up. However, I would like to then use this query to generate a chart in a report that lists all of the studies. ie. The report will be made of 1 page per record, and each page will contain 1 chart, which shows the above data plotted as a line chart.

However, if I pop a chart in and use the wizard to create the chart, when I get to the stage where I would like to assign a fiend from the current record in the report to a value in the chart, I am unable to assign a report variable to the parameter I described above 'Identifier'. Now, normally, I would build the query to pull down all of the data, and then assign a 'filter' on that data I guess using the Chart Wizard. However, as I have a sub-query, this isn't going to work in this case.

What I believe I'm looking for, is a way of assigning the parameter 'Identifier' from the current record parameters in a multi-record report.

You have have to try something a little loopy, such as the following:

Reports have a property called Tag
Open the Report in Design View
Open the Property Sheet and Select the 'Other' Tab
The Tag property is on the bottom of the list

You can access that Tag as follows:

Reports("Report Name").Tag

Simply replace the [Identifier] with either Reports("Report Name").Tag or Me.Tag

That way, if you open the query, you will still be prompted to input the UserID

When you open the Report, set Me.Tag to whatever number you want. How to you set it in the first place ?

On the property sheet, Click Event Tab. In the OnLoad Event, click the three dots (ellipses). Menu pop ups. Click Code Builder. VBA opens on with :

Private Sub Report_Load()

End Sub

Now put that setting in (say the UserID is from a form):

Private Sub Report_Load()

   Me.Tag = Forms("Form Name")("Field Name")

End Sub

Give it a Try !!!


Search a Redis Database Imagine having a tun of hashes in a Redis Database, each representing a user object. How would you get the user with for example the lowest registration date? The user where ts_registered is the lowest. I searched a bit on the Commands Page on redis.io but didn't seem to find a command for it there.

I don't have much experience with Redis and I'm basically just checking out how it works.

Redis doesn't come out of the box with the ability to search/index inside hashes, but it gives you all the tools you need to accomplish your task. Your challenge could be solved, for example, by keeping a key in the database called last_user_registered or similar, and SETting it every time that a user registers to that user's ID. When you want to fetch the last user that had registered at any given moment - just GET it, get it?

But you (and Redis!) can do much better: for example, you could use a Redis list and LPUSH/RPOP members to keep scores with the users who last joined. Or, you could use a sorted set where each member is a user id and the score is the timestamp... ah, the possibilities are endless and you're only limited by your imagination (and time, resources, etc...) ;)

I think this is exactly why Redis 'aficionados' lovingly refer to it as a database building tool rather than as just a database - you can actually make it do whatever you want and if what you want isn't in Redis already, there's always Lua that you can use to extend with or even developing your own fork and putting in a pull request to the github.


Best way to re-import large amount of data with minimal downtime I need to import about 500,000 records containing IP lookup (read-only reference) data about once a week (only three int/bigint cols).

I don't really want to worry about merging the data with the existing table, I'd prefer to clear down the old and reimport.

Ideally queries running over the data would continue to run (we don't get a lot of these and it is acceptable for them to run a little bit slower whilst the import occurs, but need to be up 24/7, so running this "out of hours" is not an option).

Things Tried So far

SSIS: I have created an SSIS package that truncates the table and imports - it takes about 30 seconds to run (too long really).

Temp Table: Importing into a temp table, truncating and copying across also takes about 30 seconds.

BCP: Bulk Import also is rather too slow (for some reason it's slower than SSIS (even with no indices to maintain) - I'm guessing it's something to do with the char->int/bigint transactions :/

Mirror table? So, at the moment, I'm wondering about reading the table through a view, importing the data into a mirror table, and altering the view to point to this table... this seems like it will be quick, but it seems tiny bit hacky to me.

This seems like it should be a common problem, but I can't find recommended practises - any ideas would be most appreciated!

Thanks

A solution I've used in the past (and have recommended here and on StackOverflow before) is to create two additional schemas:

CREATE SCHEMA shadow AUTHORIZATION dbo;
CREATE SCHEMA cache  AUTHORIZATION dbo;

Now create a mimic of your table in the cache schema:

CREATE TABLE cache.IPLookup(...columns...);

Now when you are doing your switch operation:

TRUNCATE TABLE cache.IPLookup;
BULK INSERT cache.IPLookup FROM ...;

-- the nice thing about the above is that it doesn't really
-- matter if it takes one minute or ten - you're not messing
-- with a table that anyone is using, so you aren't going to
-- interfere with active users.

-- this is a metadata operation so extremely fast - it will wait
-- for existing locks to be released, but won't block new locks
-- for very long at all:

BEGIN TRANSACTION;
  ALTER SCHEMA shadow TRANSFER    dbo.IPLookup;
  ALTER SCHEMA dbo    TRANSFER  cache.IPLookup;
COMMIT TRANSACTION;

-- now let's move the shadow table back over to
-- the cache schema so it's ready for next load:

ALTER SCHEMA cache TRANSFER shadow.IPLookup;
TRUNCATE TABLE cache.IPLookup;

-- truncate is optional - I usually keep the data
-- around for debugging, but that's probably not
-- necessary in this case.

This will be more cumbersome if you have foreign keys and other dependencies (since you may have to drop those and re-create them), and of course it completely invalidates statistics etc. and this, in turn, can affect plans, but if the most important thing is getting accurate data in front of your users with minimal interruption, this can be an approach to consider.


How to dump PostgreSQL database whose owner has no password? First, some background on my setup:

The server has root access disabled. So, I log-in as (say) john who also belongs to the sudo group and is therefore able to run superuser commands.

I created a new password-less user#1 santa (using the command sudo adduser --shell /bin/bash --gecos 'Santa Claus' --disabled-password santa).

Then I changed the login session's owner to santa using the command: sudo su - santa and created a new PostgreSQL database: createdb myapp_db (the database got created without asking me for password).

Now, given the condition, how do I make an SQL dump of the database myapp_db (whose owner is santa) using the command pg_dump?

This is supposed to work, but it isn't:

john@host:~$ pg_dump myapp_db -U santa -h localhost --no-owner -W > myapp_db_backup.sql
Password:

When I run the above command, it asks me for "password" as you can see. What password am I supposed to enter here? I didn't enter any password for the database when creating it, nor does the database owner santa have any.

So, I tried entering the password of john, the sudo user, and got this error:

"FATAL:  password authentication failed for user "santa""

I also tried (which didn't work either):

john@host:~$ su - santa
santa@host:~$ pg_dump myapp_db -U santa -h localhost --no-owner -W > myapp_db_backup.sql
Password:

This time, I created a password for the user santa and entered it. Still get the error:

"FATAL:  password authentication failed for user "santa""

What am I missing here?

(Please let me know if I am missing any necessary details.)

More Information

(As requested in the comments.)

john@host:~$ sudo su postgres
postgres@host:~$ psql -l

                                         List of databases
          Name           |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------------------+----------+----------+-------------+-------------+-----------------------
 app_db                  | santa    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres                | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0               | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |          |          |             |             | postgres=CTc/postgres
 template1               | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |          |          |             |             | postgres=CTc/postgres
(4 rows)

Output of \du santa:

john@host:~$ sudo -u postgres psql
postgres=# \du santa

                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 santa     | Superuser, Create role, Create DB, Replication | {}

See this part of the pg_dump manpage:

   -W, --password
       Force pg_dump to prompt for a password before connecting to a database.
       This option is never essential, since pg_dump will automatically prompt
       for a password if the server demands password authentication.

Don't use -W at all. In your case, it's just confusing.

Also, you need to know that the fact that the server asks for a password or not is not driven by the existence  of this password.

It's driven by the server-side pg_hba.conf file that you need to study and possibly modify according to your needs (don't forget to reload the server after modifying it).

EDIT: reviewing your pg_hba.conf. The relevant lines are:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

The 1st line concerns the postgres user. It's irrelevant for your pg_dump command since you're using the santa user with -U santa

The 2nd line concerns any other connection through Unix domain sockets (TYPE column is local). From the client, it means when you do not use -h localhost. It says that if the OS user is the same name than the db user, he doesn't need a password.

The 3rd line says that if -h localhost is used (IPv4 TCP connection), a password will always be asked to the client.  The 4th line says the same with IPv6.

Based on this, this command run by the santa OS user should not ask or need  a password:

 pg_dump --no-owner myapp_db > myapp_db_backup.sql

-U santa is optional because the db username is taken as the OS user by default.


creating table syntax error 1064 I'm using workbench to create a table. This script is giving me a problem. It is saying that there is a syntax error new ').

DROP TABLE IF EXISTS `waitronmain`.`managers` ;

CREATE  TABLE IF NOT EXISTS `waitronmain`.`managers` (
  `manager_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `restaurant` VARCHAR(45) NOT NULL ,
  `location` VARCHAR(45) NULL ,
  PRIMARY KEY (`manager_id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `waitronmain`.`waiters`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `waitronmain`.`waiters` ;

CREATE  TABLE IF NOT EXISTS `waitronmain`.`waiters` (
  `waiter_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`waiter_id`) ,
  INDEX `manager_id` () ,
  CONSTRAINT `manager_id`
    FOREIGN KEY ()
    REFERENCES `waitronmain`.`managers` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

The line causing the problem is this:

INDEX `manager_id` () ,

Any ideas? I'm at a loss, the code was generated from an ERD model I made within Workbench so I'm suprised it has a syntax error at all. I got this error code when executing:

  Error Code: 1064 You have an error in your SQL syntax

I see a few issues with your existing code to create the waiters table.

First, the index creation code is incorrect.  The column to be indexed should be inside the parentheses -- INDEX  (manager_id).

Second, you are trying to add an index on a column that is not listed in your waiters table. You need to include the manager_id as a column.

Third, the syntax for the FOREIGN KEY constraint is incorrect. The syntax is from the MySQL docs is:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

So your code should be:

CREATE  TABLE `waiters` (
  `waiter_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `manager_id` INT NOT NULL,
  PRIMARY KEY (`waiter_id`) ,
  INDEX  (`manager_id`) ,
  CONSTRAINT FK_Manager
    FOREIGN KEY (`manager_id`)
    REFERENCES `managers` (manager_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

See SQL Fiddle with Demo


Create user for alternate port I am running a private MySQL instance on WebFaction

WebFaction is a shared host who provides both for their public MySQL instance and for your own private MySQL instance. The private instance is a 1-click install using their system which then provides a root user and port to you. The public instance is always just localhost, and the private instance is localhost:port.

What is the proper way to set up a user for that private instance?

CREATE USER 'user'@'localhost:port' IDENTIFIED BY 'password';
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'user'@'%' IDENTIFIED BY 'password';

Does it matter?

I'm running into an issue trying to set up a Simplified Machines forum instance. The SMF forum will connect to localhost just fine, and it seems to connect to localhost:port okay (at the point in code where it makes the connection to the DB, it is successful), but then will only read and write to localhost and not localhost:port.

I have successfully created a db user which can connect to, read, and write via localhost:port, but refuses to do so via SMF.

Any thoughts?

Here is what I did:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

In the php.ini:
mysql.default_port = <private instance port>

In the forum settings, set database host to:
127.0.0.1


PUBLIC privilege potential security threat - Oracle database Recently, audit findings at my site found that PUBLIC being by default assigned to all the schemas that are created is a big risk since when a new user is created and even if we were to assign only CONNECT and RESOURCE roles, the schema can still insert/update/delete the tables that of even system schemas' objects.

Even though while querying dba_sys_privs, there are no system privileges assigned to that grantee 'PUBLIC' whereas dba_tab_privs shows otherwise, which in itself is a risk. But, Oracle says in a metalink that, revoking PUBLIC has its own ramifications.

I did try to insert a row into one of the SYSTEM tables using SCOTT schema, and it did go through.

How much of a threat does revoking PUBLIC from normal schemas affect the functioning? How big is the risk if this is not taken care of? or should I carefully revoke them at granular level for application schemas other than default schemas?

What is the very use of PUBLIC being assigned by default if this is a security flaw?

You cannot revoke the public pseudo-role from users.  Every user always has public.

You can revoke privileges from the public role.  The first question would be whether these privileges were granted by default by Oracle, in which case revoking them might prove difficult.  It might, for example, break upgrades and patches and/or be re-granted by future upgrades and patches.  There are ways to reduce the default set of grants made to public but that tends to be quite a bit of work for very little benefit:

In a default Oracle install, a user that has just create session cannot create objects, cannot read objects in another user's schema, and certainly cannot modify data in another user's schema.  Only if someone in your organization were crazy enough to do something like granting update any table to public would that happen.  If someone has granted a raft of powerful privileges to public beyond what Oracle ships with, that would be a concern absent a compelling justification for each grant.

On the other hand, if a user that has no privileges other than create session can modify data in a system table, though, that implies that you (or someone in your organization) has granted a ton of object privileges to the public role.  Absent a rather compelling justification for each of those grants, that would definitely be a concern.  That really should have been done by creating appropriate roles for your application and granting those roles to whatever users needed them.  If you created objects in the system schema, that would be another source of concern-- only Oracle should be creating objects in sys or system.

Real applications should not be using the connect or resource roles.  If you look at the actual privileges those roles grant, they are likely to be both far more and far less than you would expect.  Plus they change across versions-- Oracle has been locking down the roles in later versions because people were granting them freely without understanding the implications of those grants.

The public role exists because certain privileges rightly should be given to every user by default.  It seems likely, for example, that you want a new user to be able to query user_tables to see the tables they own or all_tables to see what tables they have privileges on.  It would be terribly annoying if every application had to ship with the full list of every data dictionary table it needed to query (some of which would vary based on how different JDBC/ODBC/OLE DB/ etc. providers implemented certain API functions).  It is useful to have a small baseline of functionality that you get by default.


Why add indexes to these columns in the Database? I am trying to get my head around this code. It's from the Rails Tutorial Book and is part of the process of making a twitter like application.

   class CreateRelationships < ActiveRecord::Migration
  def change
    create_table :relationships do |t|
      t.integer :follower_id
      t.integer :followed_id

      t.timestamps
    end

    add_index :relationships, :follower_id
    add_index :relationships, :followed_id
    add_index :relationships, [:follower_id, :followed_id], unique: true
  end
end

Since their are only 2 columns (follower_id and followed_id) why would their be a need for an index?
Does the index sort them is some way? It just seems a bit strange to me to add an index to a table with 2 columns?
What does the index do to the rows?

Why would you add an index to the back of a book? So you can quickly look up a word and see which page numbers it's on.

Why is it fast to look up a word in a book index? Because the words in the index are sorted.

Similarly, in a database index, you can quickly look up a value and get the rows it is on.

The usage of indexes, and the number of columns, is not particularly related. You can have zero, one, or many indexes on (almost) any column in a table.
The index itself is a sorted structure, to allow for fast look-ups, but does not sort the physical rows of its table†.
An index doesn't do anything to the rows. It is a separate structure that points to rows. For example, if you're looking for follower_id 3, you could look at the index and it says, follower_id 3 is on rows 4, 9 and 99. This is faster than looking at every row for follower_id 3.

The last index in your example is a unique index, which prevents one from following the same person twice. An exception will be raised if you try to enter {follower_id=1, followed_id=2} and then {follower_id=1, followed_id=2}.

A unique index is similar to a primary key (though a primary key may not contain nulls), and is identical to a unique constraint in many systems††.

A final question to ask is, is it easy to be the guy that maintains the index of a book if the author is making a lot of revisions to the book?

† unless it's a clustered index

†† In Postgres a unique index may use expressions whereas a constraint may not


Best practices for committing a transaction in SQL Server where TRY CATCH is used In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?.

For example, is option A or option B the correct approach or are they subjective choices?

Option A

CREATE PROCEDURE DummyProc
BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

Option B

CREATE PROCEDURE DummyProc
BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)

   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
   IF @@trancount > 0  COMMIT TRANSACTION

In Option B, is there a possibility of some error happening when its doing a commit outside the TRY-CATCH block ?

Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the COMMIT will be caught and you can gracefully handle this error and rollback.

See this answer on SE. In your example code, if we assume other users are running this procedure concurrently it might be possible for the TRY block to complete successfully and then fail on the COMMIT due to changes made by other sessions.


Securely Send Database Mail via an Insert Trigger I have spent about 30 hours so far trying to get sp_send_dbmail to work. I finally got SOMETHING to work, but I have serious doubts as to its security. I would like know a method which is better yet quick and easy to setup (I'm severely limited on time at this point), and the 'ideal' (best practice) solution.

Current SQL Server 2005 Configuration

- `After Insert` Trigger on table executes `sp_send_dbmail`
- Database Mail SMTP Account setup (with successful mail test)
- Database Mail Profile setup (with "Public Profile" ticked)
- "guest" account added to "DatabaseMailUserRole"

I found this post suggesting the "correct" solution and I read up on this article where I discovered that it's a pretty involved solution with too many things that can go wrong it seems, not to mention difficult to maintain.

My thoughts for an alternative solution are these.

1. Setup an Application Role in msdb (this would be my first time using one
    and not even sure if it IS a solution :P), and use it to ensure that email
    can ONLY be generated from the app and not by connecting directly to the server.
2. Abandon the trigger and instead run a job on the table to send email. Definitely
    NOT ideal as I would prefer the email to go out instantly, but at least this
    way I would be able to control the user context. Additionally this is similar
    to the current mechanism, except it would use a Stored Procedure instead of a
    DTS package.

The issues I've been battling these 30 hours and the reason for my inquiry are related to the security context under which the trigger was being executed. When I did my testing, everything worked great because I am a DBA, but when my users used the app responsible for generating the email, it would break because their credentials were not sufficiently high to for the trigger to execute the sp_send_dbmail proc, and therefore it broke the app. (Unfortunately my error handling as the SQL Server level is less than desirable at the moment).

You are far better off simply inserting into a mail queue table in the trigger and having a separate email process handling the mail - either in SQL Server Agent or an external program.

This gives you the benefit of using the trigger as you desire, relatively low latency as the other job can see the mail once it's committed, scalability of managing the mail load independently etc.

You can wrap the mail queueing into the transaction properly.  Then the queueing app is reliable if the mail server is temporarily unavailable or if you have email address issues.  If mail needs to be resent, it's simply a matter of resetting the sent flag.  You can make expiration on mail, so that mail which couldn't be sent to the SMTP won't be retried after a certain time.  You can quickly route all mail to a test mailbox.  You can do quite interesting things with attachments and other things based on data in the queue without tying up the trigger for extended periods - things like generating reports etc.

So the app offloads the mail responsibility to another component and is free to complete the database transaction and return success to the users, while the mail subsystem gets on with its work ensuring all you want to do with the mail gets done.


MariaDB/InnoDB: Temp file write failure When attempting to alter a table to enable compression by modifying the KEY_BLOCK_SIZE I received the error:

  ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB

The command run was:

MariaDB [events_prod]> alter table positions KEY_BLOCK_SIZE=8;

The alter table ran for about an hour before generating this error.  The table in question is 280 gigabytes and the filesystem on which it resides has 3.4 terabytes free.  I am already using per-table files and barracuda for storage.

I can't seem to find any documentation that explains what the cause of this error might be or where to even start trying to resolve it.  Any suggestions are appreciated!

Look carefully at the error message

  ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB

What is error 64 ? perror says the following:

$ perror 64
OS error code  64:  Machine is not on the network

The ALTER TABLE command is create a temp file with the new compression. However, it is trying to create a temp file but it cannot write to the directory tmpdir is mapped to.

If tmpdir is mapped to a SAN that disconnected from the network, that error would make sense.
If tmpdir ran out of space, the error message makes no sense but the end result would still stop ALTER TABLE from completing its operation.

If this problem is repeatable, you may want to try doing this change manually.

create table positions_comp like positions;
alter table positions_comp KEY_BLOCK_SIZE=8;
insert into positions_comp select * from positions;
alter table positions rename positions_old;
alter table positions_comp rename positions;