texts: MongoDB Replication Set Configuration Issue on CentOS 7

texts:
My mongodb instance will not run as standalone and I haven't been able to discover why.  Its probably something simple.  Please point me in the right direction.

Linux myserver 3.10.0-514.el7.x86_64 #1 SMP Tue Nov 22 16:42:41 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

 mongo -version
MongoDB shell version v4.0.6

running the command:

    mongo --host 10.0.0.7/admin --port 27017 -u user -p mypassword

returns:

MongoDB shell version v3.4.13
connecting to: mongodb://admin:27017/?replicaSet=10.0.0.7
2019-03-06T00:54:32.412-0500 I NETWORK  [thread1] Starting new replica set monitor for 10.0.0.7/admin:27017
2019-03-06T00:54:32.414-0500 I NETWORK  [thread1] getaddrinfo("admin") failed: Name or service not known
2019-03-06T00:54:32.414-0500 W NETWORK  [thread1] No primary detected for set 10.0.0.7
2019-03-06T00:54:32.414-0500 I NETWORK  [thread1] All nodes for set 10.0.0.7 are down. This has happened for 1 checks in a row.
2019-03-06T00:54:32.914-0500 I NETWORK  [thread1] getaddrinfo("admin") failed: Name or service not known

So I logged into mongo shell on the host and ran:

use admin
 show users

{
        "_id" : "admin.myname",
        "user" : "myname",
        "db" : "admin",
        "roles" : [
                {
                        "role" : "readWriteAnyDatabase",
                        "db" : "admin"
                },
                {
                        "role" : "root",
                        "db" : "admin"
                },
                {
                        "role" : "userAdminAnyDatabase",
                        "db" : "admin"
                }
        ],
        "mechanisms" : [
                "SCRAM-SHA-1",
                "SCRAM-SHA-256"
        ] }

and

  rs.status()

{
        "ok" : 0,
        "errmsg" : "not running with --replSet",
        "code" : 76,
        "codeName" : "NoReplicationEnabled"
}

Mongd.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: /var/lib/mongo
  dbPath: /data/mongo
  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
  timeZoneInfo: /usr/share/zoneinfo

# network interfaces
net:
  port: 27017
#  bindIp: 127.0.0.1  # Enter 0.0.0.0,:: to bind to all IPv4 and IPv6 addresses or, alternatively, use the net.bindIpAll setting.
  bindIpAll: true

security:
  authorization: "enabled"

#operationProfiling:

#replication:

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:


texts:
Is there a way to create an immutable field/cell in a Postgres record? AKA, only allow the field to be written once, perhaps just on insert?
Use case: I have a "uuid" field for a record, and it should never ever be changed (same with the PK as well).


texts: MySQL import file with no column delimiter

texts:
Is there a way to use the mysqlimport command on a file that has no column delimiters?

I would like to import the ctl file used with the sqlloader for Oracle


texts: Dropping then adding a table to a subscription with Identity option Not for Replication causes errors

texts:
We have a Transactional Replication environment that was initialised from a backup that replicates the data for several hundred tables.

When a table is altered, say a new column added, I have created a script that does the following:

Apply table ALTER at the subscriber
Apply table ALTER at the publisher
Drops the subscription
Drops the article
Adds the article again (so it will now include the new column - sp_refreshsubscriptions is called automatically by the sp_addarticle to add it back to the subscription)

In testing I thought this was working but it appears in our production environment that this causes the following replication error:

  Cannot update identity column 'XXX'

for the identity column of the table being altered. Investigation has shown that this is not what it seems; the command that is failing is not an update of data on the row but the addition of the article to the existing subscription.

If I turn Is Identity off for the identity column on the subscriber then the above script works as expected and data replication continues. I can even then turn 'Is Identity' back on after the article is added to the subscription and replication continues. The solution is to turn 'Is Identity' off for all the tables (or before we try to alter them) in our subscriber database but these were all on after replication was set up and are working fine with the 'Not For Replication' setting turned on.

Why would 'Is Identity' being on for an table in the subscription database prevent me from adding the table again after I have dropped it? Can I circumvent my having to turn it off before altering the table using my script?

Thanks


texts: Concern regarding auto_increment in MySQL InnoDB

texts:
I'm developing and implementing CFR Part 11 compliant system. In each table I create a primary key that is used for audit trail. I implement triggers fired on create, update and delete operations.

I use MySQL 5.7 with InnoDB engine on JawsDB DaaS.

A few days ago I came across the following article: https://desmart.com/blog/be-careful-with-mysql-s-auto_increment-how-we-ended-up-losing-data

The author shared his concern regarding auto_increment mechanism in MySQL. Here is the issue in a few words:

You create 10 employee records with auto_increment PK (let's call it "recordID"). The system assigns recordID numbers from 1 to 10.
Employee "9" does something in the system and his actions are recorded in audit trail table with his recordID.
Employee 9 and 10 are deleted . You delete 2 last records (9 and 10)
You restart MySQL server
You insert new record and the DB system assigns the newly inserted record's recordID value "9".

The problem is that we already have audit trail on the recordID "9" in the audit trail table.

Here are my questions:

Have you ever came across the situation?
Is MySQL 5.7 affected by the bug? Shall I upgrade to 8.0 since it is stated here that the bug was fixed in 8.0?
What would you suggest to mitigate the risk? Shall I pick another DB engine? MyISAM?
Is there any way to ensure that auto_increment does not increment sequentially if last record was deleted?


texts: Composite index performance with varchar columns

texts:
I have large fact tables with composite non-clustered indexes, something like this:

NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey)

The columns ending with "Key" are varchar(50) columns, but they store strings of 20-30 characters.

Do I need to worry in long term, that the varchar columns in the index are actually varies in length?
Will I get more page splits, slower performance when millions of rows merge into these tables?
https://stackoverflow.com/questions/59667/what-are-the-use-cases-for-selecting-char-over-varchar-in-sql

Does the Sql Server engine have harder times to figure out the plan using that index since the columns are varies in length?
I read that CHAR is better since its fixed size, and its easier for the db engine to calculate with, hence it performs better.
Is it that significant?

Does it worth to hash these "Key" columns individually with lets say SHA2_256 function, so they become fixed size BINARY(32)?

NONCLSUTERED INDEX (OrderDate,OrderType,HASHEDClientKey,HASHEDItemKey,HASHEDCustomerKey)

I did my test to answer at least the performance question, and doesn't see any performance benefit with hashkeys over varchar.
In fact its a big overhead to calculate the hashes and store the extra 32 bytes of each next to the original "Key" column.

What I cannot test is how these varchar indexes will behave when i have millions of rows merging into the tables each day?

Which one would you choose for both performance and maintenance point of view?


texts: Why "SET LOCAL statement_timeout" does not work as expected with PostgreSQL functions?

texts:
My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works within a transaction:

BEGIN;
  SET LOCAL statement_timeout = 100;
  SELECT pg_sleep(10);
COMMIT;

where the results are (as expected):

BEGIN
SET
ERROR:  canceling statement due to statement timeout
ROLLBACK

However, if I put the same commands within a function body:

CREATE OR REPLACE FUNCTION test() RETURNS void AS '
    SET LOCAL statement_timeout = 100;
    SELECT pg_sleep(10);
' LANGUAGE sql;

SELECT test();

the timeout does not occur, and the function test() takes 10 seconds to execute.

Please advise on why the two cases differ, and how I can correct it to set statement timeouts within a function.


texts:
We have encountered some performance issue on our production environment.

we found that when active sessions go up above 25, the usage of CPU reach to 100%
and it takes long time to go down.

The environment we have:

Product Microsoft SQL Server Enterprise Edition 9.3(sp2)

CPUs    2(Xeon 2.13)

Memory  7G

snapshot of session detail1

Active Sessions  25

Active Transactions  496

Idle Sessions    289

Blocked Transactions 29

snapshot of session detail2

Active Sessions  59

Active Transactions  885

Idle Sessions    267

Blocked Transactions 49

I'd like to know:

whether 2CPUs can handler 25 active sessions (500 active transactions ) well.PS:
we have test that without concurrency request,one transaction ,which read/write 5 tables, takes about 1sec in application level.
whether the blocked transactions takes more CPU usage.PS:the blocked transactions is mainly because of locks on 2 tables.

what's the solution:
Add CPUs ?or tuning application(java/hibernate) to shorten this transaction and to decrease blocks on the table?


texts:
In our production servers for SQL Server, we usually split our drives by Software, Logs, Data, and Backups. I've been assigned to help with a MySQL installation (On Windows Server) and I'm not as experienced in setting up the environment with it. We are working with 500 GB.

Are there best practices with the drive assignments? Or does MySQL best manage everything on a single drive? Most of what I find on the web has to do with configuring InnoDB and Partitioning.


texts: some tips on setting up sql server database server

texts:
I am currently setting up a new SQL Server database server with databases. It is supplied by a third party company.

I need to request access for them around ports etc.

I would like to be able connect to the SQL Server instance on the above database from SQL Server Management Studio on my PC. What do I need to get setup on the server so I can get such access?

The other question I have is that I have a number of databases on other servers, I will like to "copy" these databases to the new server. I need to copy both structure and data. What is the best way to do such?


texts:
I have a MySQL table that holds a few thousand records which go out of date very quickly.  A few times a day we run a slow process which needs to pull data from multiple sources and re-populate the table.

The catch is the table needs to be available all the time.  My initial idea was to do the slow population process on a temp table, then drop the live table and rename the temp table to replace the live table.

Is it possible to lock the live table before the drop/rename process so that queries will be delayed and not lost?

Or is this the wrong approach?


texts:
I have a message table in my application and one of the most commonly executed SQL queries on this table is the following one:

select * from message message0_
 where message0_.sender_id=? or message0_.recipient_id=?
 order by message0_.send_date asc;

I am basically querying for the messages the current user has either received or sent.

The value of the parameter for sender_id & recipient_id is the current user ID.

I have created the following index:

CREATE INDEX ON message(recipient_id, sender_id);

I would like to know if the order of the fields matters for my use case bearing in mind the OR clause in my SQL query.

Can someone please help?


texts:
I have 3 different types of content for a website, 'Article', 'Video', 'Review'.  Each of those three types of content contain common fields and type specific fields.  For this, I have created the following 5 tables:

content - being the primary table containing the common fields across all content types
content_type - name, description, and type specific table name of available content types
article - the table that contains all the type specific fields for content of type 'Article'
video - the table that contains all the type specific fields for content of type 'Video'
review - the table that contains all the type specific fields for content of type 'Review'

I am a bit confused as to properly create the relationships between the tables. Realistically one 'content' row relates to one 'article' (type specific table) row.  To me this sounds like a one to one - non-identifying relationship.  Where I am think I am getting most confused, is the type can be one of many. So should they be One to Many or Many to Many, identifying / non-identifying?

I need to be able to search the content fields and then retrieve the corresponding type specific row based on the search.  I also need to be able to search the type specific fields, and retrieve the corresponding content row for search matches.

Also to note, I am using MySQL with Doctrine/ORM, however I am not looking for the SQL statements here I am just looking to better understand what the relationships should actually look like.


texts:
I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too?

Currently, I use two separate queries to do so as

SELECT COUNT(*) FROM col WHERE CLAUSE
SELECT * FROM col WHERE CLAUSE LIMIT X

Is there a way to do this in one query?

EDIT: The output should be the col cells and the number of rows. In fact, after selecting the col cells, it should walk over the table to count only.

I know that it is not possible to merge these two queries, as the first return 1 row, but the second X rows. I am curious if there is a function in mysql to return the number of rows in the presence of LIMIT.


texts: How to find corrupt page in SQL Server 2008

texts:
I know I can execute a DBCC CHECKDB and get a status of my database.

Questions

How can I find if there is some corrupt data page into my database?
If an error is throwndue to page corruption, where can I find which pages are corrupt?
How can can I find out the page number of each corrupt page.

Can anybody can tell me where to find those page ids ?


texts: How to increase the amount of memory sql server uses on the server?

texts:
On the db server, we have 4 GB of memory. Currently, the memory utilization of Sql Server 2005 on that server is 1,725,000 K and the total memory usage is stable at 2.04 GB. The Cpu usage on the server though is at 99% almost constantly.

How can I increase the memory that Sql Server uses so that it performs more operations using memory rather than reading from disk etc.?


texts: Junction table Trigger after update in another table

texts:
I'm just trying to create a trigger that performs an update in a junction table.
In principle, I have three tables with Many-to-Many relationship.
Table a:
a_id, a_is_active
Table b:
b_id, b_is_active
Table ab:
ab_id, a_id, b_id, ab_is_active

What I want is that the ab_is_active needs to be actualized when a_is_active or b_is_active is updated, so ab_is_active should be like this (pseudo code):
ab_is_active = a_is_acvite && b_is_acvite where a_id = a.a_id and b_id = b.b_id
and that must be carried out after each is_active update in Table a or in Table b:
I have tried the following, but it does not work (nothing is updated in ab):
CREATE OR REPLACE FUNCTION is_active_changes()
    returns trigger
    language plpgsql AS
$BODY$
DECLARE
    b_is_active boolean;
    r            b%ROWTYPE;
BEGIN
    for r in select *
             into b_is_active
             from ab
                      join b _b on ab.b_id= _b.b_id
                      join a _a on ab.a_id= _a.a_id
        LOOP
                update ab set ab_is_active = r.a_is_active and r.b_is_active where b_id = r.b_id and a_id = r.a_id;
        END LOOP;
END;
$BODY$;

create trigger is_active_trigger
    after update
    on a
    for each row
execute procedure is_active_changes();

Can someone tell me what I do wrong or suggest a better solution?


texts: Using EXCEPTION to ignore duplicates during bulk inserts

texts:
In pq 9.3.5, I'm importing records from an external source where duplicates are VERY rare, but they do happen. Given a readings table with a unique compound key on (real_time_device_id, recorded_at), the following will fail once in a blue moon:

INSERT INTO readings (real_time_device_id, recorded_at, duration) VALUES
  ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:33 -0700', 10.0),
  ... many more records ...
  ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:43 -0700', 10.0);

(FWIW, the above fails 'properly' with a duplicate key violation.)

I know that handling exceptions is expensive, but as I said, duplicate entries are very rare.  So to keep the code simple, I followed an example given in Optimal way to ignore duplicate inserts?:

BEGIN
    INSERT INTO readings (real_time_device_id, recorded_at, duration) VALUES
      ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:33 -0700', 10.0),
      ... many more records ...
      ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:43 -0700', 10.0);
EXCEPTION WHEN unique_violation THEN
  -- silently ignore inserts
END;

The above gets two errors:

psql:sketches/t15.sql:11: ERROR:  syntax error at or near "INSERT"
LINE 2:         INSERT INTO readings (real_time_device_id, recorded_...
                ^
psql:sketches/t15.sql:14: ERROR:  syntax error at or near "EXCEPTION"
LINE 1: EXCEPTION WHEN unique_violation THEN
        ^

Can anyone set me straight on the correct syntax?  Or is my error deeper than mere syntax?  (For example, will all of the INSERTs be ignored if there is a single duplicate?)

updated question

As @dezso points out, there are a few problems with the code above. So (at the risk of having the question flagged as too general), what's a good way to do bulk inserts where very few (< .1%) are duplicates?


texts:
I am using merge replication on SQL 2012. I have a very large database with a large number of tables. The subscribers are using netbooks.

I have a filtering scheme that controls which tables end up being empty at the subscriber. We are saying this for instance 'the user bob is not interested in the street light table so he will receive that table, but with no data in it'. That filtering is working fine.

What I am interested in is whether you can go further and not even sent the streetlight table to that user. The reason I ask is running the scripts to create the tables and add the replication triggers is quite slow on the netbook when you are talking about a large number of tables.

Here is the breakdown of my sync to the netbook,

Download Snapshot Files     0:03:33.580
Applying Scripts            0:05:02.290
Bulk Copying                0:07:59.723
Applying Scripts            0:14:31.777
Finishing Sync              0:03:59.720

So it turns out that applying the scripts is a large portion of the sync.


texts:
While trying to Process a cube i go this error

"Error  133 The following system error occurred:  No mapping between account names and security IDs was done.       0   0"

i know its some user which is available in the roles have been removed from active directory.

the annoying part in this is the error doesn't indicate which user has been removed from active directory, our SQL is setup as windows authentication.

and i have to go and check one by one.

is there a way to identify the user, so i remove it and continue with the processing


texts:
We are on MYSQL 5.7 and when we create  a column like so in our DDL

my_update_timestamp timestamp(6) NOT NULL

MySQL complains when we try to create the table with

Error 1067: Invalid default value for my_update_timestamp.

Is it possible to have a not nullable timestamp with no default value?


texts: What makes for a good index in this scenario?

texts:
I have a pretty big temp table (~5 million rows) that I need to update a few fields on.

Here's an example update statement:

UPDATE T
SET CarModel =
    CASE
        WHEN CarModel = '350z' AND ModelYear = 2008 AND CarMake NOT LIKE '%DATSUN%' THEN '370z Nismo'
        ELSE CarModel
    END,
IsMidTierCar =
    CASE
        WHEN NumberOfCarsSold >= 500000 AND <= 1000000 THEN IsMidTierCar
        ELSE NULL
    END,
IsRareCarMakeModel =
    CASE
        WHEN CarModel = '350z' AND SpecialEditioName != '' AND ModelYear = 2008 THEN 1
        ELSE 0
    END
FROM #CarsTempTable AS T
WHERE IsRareCarMakeModel IS NULL

The catch is the IsRareCarMakeModel field is NULL for every single row in the temp table before this update runs. Right now the fastest time I see it run in is when I don't create any index on my temp table. Any other index combination I've tried runs in at least 5x as long (and usually I just end up killing it instead of waiting for it to finish.)

Is no index going to be the best I'll ever get because of the cardinality of the temp table's IsRareCarMakeModel field is extremely low therefor a table scan is always going to be the best course of action for the optimizer?


texts: What causes 'Recovery completed for database ...' log messages?

texts:
I am running an MS SQL Server Express (64-bit v11.0.2100.60) database and now and then I get problems that my connections times out when I want to connect. In the server log I can see that at that time there are log messages 'Recovery completed for database ...'.

I did not run any datababase recovery nor was anything happened at that time (Windows event  log and MS SQL server logs are both 'clean').

What could cause this recovery? Is there a way to prevent it?


texts: Shortcut to select all SQL text in Access

texts:
When working in the SQL view in MS Access, is there a shortcut to select all the text in a SQL query.

For example, most text editors in Windows follow the {Ctrl+ a} combination, but this doesn't seem to have any affect within Access. Is there some other combination?

It is useful when copying text from an outside editor into the Access SQL query creator.

Thanks!


texts: How can I execute a stored procedure whose name is in a variable?

texts:
Given a variable that contains a stored procedure name:

DECLARE @stored_procedure_name varchar(512);
SET @stored_procedure_name = 'some_stored_procedure_name';

How can I execute the stored procedure (without passing in any arguments)?


texts: How do I generate a Postgres dump with the column names in my INSERT statement?

texts:
I’m trying to export data from my Postgres 9.5 instance on my Mac to my Postgres 9.5 db on an Ubuntu Linux box.  I’m using this command to export

pg_dump -U username --format plain --section data --inserts --file "t1.backup" --table “table1” --table “table2” --table "addresses" “mydb”

But when I import the data using pg_restore, I get errors

  pg_restore -d my_production --data-only t1.backup
     …                                                         ^
     Command was: INSERT INTO table2 VALUES (NULL, NULL, 1762000, '2016-12-10 05:07:41.857082', '2016-12-10 05:07:41.857207', 'Brendon Sch...
  pg_restore: [archiver (db)] could not execute query: ERROR:  invalid input syntax for integer: "a6a40213-dcd8-4312-9ff1-ae6c23b31ade"
  LINE 1: ..., '8a22784f-91aa-4b1e-a3d4-ec44f5de19ff', 55, 59, 'a6a40213-...

I think this is because although table2 has the same columns on both my Mac and Linux machines, they are in different orders.  How do I force INSERT statement to include the column names or barring that, how do I export/import my data successfully?  Note, I don’t want to delete the data on the remote machine, just add to it.


texts: CASE ORDER BY with multiple columns, and different sort options

texts:
I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT.

@Orderby = 1 Then Date column should be ASC
@Orderby = 2 Then Date column should be DESC

My question is: How can I get the date column to sort desc when I am passed a 2 for that parameter, and have the a string column sort asc in the same CASE ORDER BY statement?

This is what I have now for the CASE ORDER BY

    ORDER BY
    CASE WHEN @OrderBy = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) + tp.LastName + tp.FirstName END ,
    CASE WHEN @OrderBy = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) + tp.LastName + tp.FirstName END DESC

This codes parses and returns a result set without error, but the 2nd CASE ORDER BY is all in DESC sort order, when I would prefer to have ccd.CertEndDate DESC , tp.LastName ASC , tp.FirstName ASC

Thanks in advance.


texts: Any correct procedure for couchbase backup and restore?

texts: I have just started using couchbase server and trying to configure backup and restore method. We require to take backup for each couchbase node or cluster backup also available ?


texts: How can we foresee (and configure) B-Tree and R-Tree indexes width and depth in PostgreSQL?

texts:
The B-Tree and the R-Tree are two similar index structures that PostgreSQL offers in its implementation.

Both structures can have at most m children per node and as other tree structures, children can have other children. However, in my test data, my structures are always in the form of a root node with many children i.e. just 2 levels.

How can I understand better at which point will the structure's depth increase? What determines how many elements can be on the same level of a tree in terms of PostgreSQL? Is it possible to configure these or the m variable?


texts: Does SQL92's REPEATABLE-READ preclude Write Skew (A5B)?

texts:
REPEATABLE READ

In SQL92's definition, REPEATABLE READ is defined by serveral conditions.

P1 ("Dirty read"):

  1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.

P2 ("Non-repeatable read"):

  2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

Atomicity and no updates will be lost:

  The four isolation levels guarantee that each SQL-transaction will be executed completely or not at all, and that no updates will be lost.

Table 9, which defined REPEATABLE READ must preclude P1 & P2:

  Table 9, "SQL-transaction isolation levels and the three phenomena" specifies the phenomena that are possible and not possible for a given isolation level.

So in SQL92's definition of REPEATABLE READ, it must preclude P1, P2, and support for atomicity, no update lost.

A5B (Write Skew)

On the other side, A5B (Write Skew) is defined in A Critique of ANSI SQL Isolation Levels:

  Suppose T1 reads x and y, which are consistent with C(), and then a T2 reads x and y, writes x,and commits. Then T1 writes y. If there were a constraint between x and y, it might be violated. In terms of histories:

  r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur)

  (Write Skew)

Is REPEATABLE READ preclude A5B (Write Skew) ?

In the later paper, it claims REPEATABLE READ will preclude A5B (Write Skew) in Table 4. Isolation Types Characterized by Possible Anomalies Allowed., and I am not conviced.

Any thought?


texts:
I have a SQL Server 2008 R2 server instance where I'm unable to use full text catalogs. It was upgraded from SQL Server 2008. When I go to the database properties of an existing database or try to create a new database the "Use full-text indexing" checkbox is disabled.

Some things that I have checked are:

Full text search service is installed and started
Named pipes protocol is enabled
Full text service is running under local system account
EXEC sp_fulltext_database 'enable' does run successfully

What else could go wrong, because I'm stuck at it?


texts:
This might be a duplicate questions asked before. But I need to understand a possible approach for my ENV running innodb engine.
Is it possible to upgrade a Master MySQL Server to 5.5 to 5.6 and  replication Slave which is on Version 5.7

If this is possible then how do we perform this activity, because we am getting this below error of Server_UUID when replicating the Slave (Slave version 5.7.11 and Master Version 5.6.29)

Errno: 1593
Fatal error: The slave I/O thread stops because master and slave
have equal MySQL server UUIDs; these UUIDs must be different for
replication to work.

Purpose is to UPGRADE both the server on the same version 5.7

Currently Version

Server1 and Server2 are on 5.6.29

Beginner in MySQL unable to understand the concept of replication with the new version, open for better suggestion.


texts: SQL Server 2008 log files have minimum sizes, what gives and how do I make them smaller?

texts:
Ok, to start with, I screwed up when I created the databases, using a create script roughly like so: (artificial linebreaks and names/paths for wrapping purposes)

CREATE DATABASE [EXAMPLE] ON  PRIMARY
( NAME = N'EXAMPLE_Data', FILENAME = N'J:\SQLServer2008\MSSQL.INSTANCE\EXAMPLE.mdf',
    SIZE = 446046KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'EXAMPLE_Log', FILENAME = N'J:\SQLServer2008\MSSQL.INSTANCE\EXAMPLE.ldf',
    SIZE = 664505KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

because I scripted it out from an existing development database, and I just wanted to get something going.  I screwed up when I didn't change my sizes to something reasonable (like 4096KB) and so now I can't shrink the logfile below roughly 600MB.

I know where I went wrong, but how do I fix it easily?


texts:
I need to create a vehicle tracking database.  There are several vehicles that send data, consisting of around 20 parameters, to my database.

The parameters of a vehicle include the constants vehicle number and hardware id, along with a variety of variable items.

I will have a table in this database containing the login credentials of the users and I would like to create a link between the two tables with the vehicle number as primary key in the login table and with the vehicle number in table containing parameters.

Since the vehicle number column is redundant I cannot have it as the primary key.

I would like to know:

What way can I achieve the link between tables
Is there a good way so that I dont store this vehicle number and hardware id so many times.
Should I be storing each vehicle's data in separate table rather than all in one table?

This is the first time I am designing a database.  I have only been a programmer to date.


texts:
I am replacing a SQL Server 2012 cluster with a new one (keeping the same version of SQL, changing OS from 2008R2 to 2012R2).
I have 1 node installed so far, but the agent won't start.  The error message is:
unable to connect to server '(local)'; sqlserveragent cannot start

The steps I have taken:
Stop SQL services on existing cluster
Move system databases
Unmap drives from old cluster
Setup new cluster using same drives
Install SQL server.
Stop SQL services
Replace system databases with original ones.
Star SQL services

The above all worked fine. But the agent won't start.  Am I being reall special?

Hope someone can help.

Thanks

Alex


texts: Creating relationships between tables that where not specified in the given UML diagram

texts:
Good evening!

I'm working on designing my very first actual database from the following UML class diagram (in French unfortunately):

I'm at the point of creating the relational sketch of it which I created this way:

Yet, it creates some issues when trying to ask to the database which are the clients that never ordered product number one?

Indeed, I don't know how to do it in SQL as far as it seems that there is no relations between Client, Commande and Produit. It gived the following sql code:

CREATE TABLE Client (
IDClient INT NOT NULL,
AdresseClient VARCHAR(255)NOT NULL ,
NomContact VARCHAR(255)NOT NULL,
NumeroSIRET VARCHAR(14) NOT NULL,
CONSTRAINT cclient PRIMARY KEY (IDClient)
);

CREATE TABLE Produit (
IDProduit INT NOT NULL  ,
PrixVente INT NOT NULL ,
QuantiteEnStock INT NOT NULL ,
CONSTRAiNT cproduit PRIMARY KEY (IDProduit)
) ;

CREATE TABLE Commande (
IDNumeroCde INT NOT NULL  ,
Date DATE NOT NULL ,
CONSTRAINT ccommande PRIMARY KEY (IDNumeroCde)
);

In relational algebra and calculus it gives:

The fact that I can't explain it in relational calculus let me think that I've done a mistake.

Edit with the new tables

I read all the links you provided and therfore add some comments and changes on my tables, can you tell me if I'm right on my assumptions?

But the last ones have the same primary key then. is it a problem?


texts: why in explain in mysql row count is very high


texts: Understanding where/how to use the "selectivity of an equality predicate between two columns"

texts:
This lecture on "Result size estimation" defines a formula of equi-joins based on Selinger paper.

result cardinality = max number of tuples possible * product of all selectivities

1 - max number of tuples possible. This is the product of the cardinalities of both relations.

2 - product of all selectivities. Which means that we have to compute the selectivity of each term and multiply them. This is defined in an equi-join by 1/MAX(#keys_left, #keys_right).

ex:

100 bunnies
with 2 distinct left ear colors {C1, C2}
with 10 distinct right ear colors {C1...C10}.
What is the probability of matching ears?

P(L = R) =
E_i P(C_i, C_i) =
P(C_1, C_1) + P(C_2, C_2) + P(C_3, C_3) + ... =
(1/2 * 1/10) + (1/2 * 1/10) + (0 * 1/10) + ... =
1/10 =
1/MAX(2,10)

Ok. Now I suppose I am able to estimate the size of the result or how much storage I will use to compute this equi-join.

scenario 1 - Relation L I have 10 tuples and the key cardinality is 10.
Relation R I have 2 tuples and the key cardinality is 2.
The result estimation cardinality is:
10*2*1/MAX(2,10) = 20*(1/10) = 2 tuples

2 - Relation L I have 100 tuples and the key cardinality is 10.
Relation R I have 20 tuples and the key cardinality is 2.
The result estimation cardinality is:
100*20*1/MAX(2,10) = 2000*(1/10) = 200 tuples

3 - Relation L I have 1000 tuples and the key cardinality is 10.
Relation R I have 200 tuples and the key cardinality is 2.
The result estimation cardinality is:
1000*200*1/MAX(2,10) = 200000*(1/10) = 20000 tuples

Is that right? If the professor is teaching about estimation and my answer is 20,000 tuples on the scenario 3, I suppose this is the estimation of the tuples. But I guess this is actually the exact number of tuples that my equi-join will produce. Is this an estimation or exactly answers?
Or maybe the estimation comes if my cardinality is estimation the result will be also an estimation?

I am not sure if I have to open another question to the following subject. And what if I am working with data stream? I mean with unbounded data coming to execute a join?

Thanks


texts: Why do select Queries create additional queries to sys tables

texts:
I have an external application querying our database with an SELECT statement via Linked Server.
When I check Queries with SQL Server Profiler - I see that these queries are accompanied by an enormous load of querie to sql server internal tables/sprocs and I have no idea where these are coming from. But in the Report of top quries by total CPU time, they occupy the top 10 with millions of calls.
These are the calls:

RPC:Completed  exec [xxx].[sys].sp_tables_info_90_rowset_64
RPC:Completed   exec [xxx].[sys].sp_columns_100_rowset
RPC:Completed   exec [xxx].[sys].sp_indexes_100_rowset
RPC:Completed   exec [xxx].[sys].sp_check_constbytable_rowset
RPC:Completed   exec [xxx].[sys].sp_table_statistics2_rowset

Is there maybe some option or flag set which enables some kind of metadata/statistcs/debug collection or check?!


texts: Linkserver update not working in Transaction

texts:
I am trying to do operation on my linked server. I have created one linked server and everything is ok. I just observed one thing which I am not able to understand.

if I run:

update SERVER.testdb.[AAA].MyTable
set MyName = 'abc@pqr.com'
where ID = 999

This works fine. But when I execute the same code in following way:

 BEGIN TRY
    BEGIN TRANSACTION TRF

    update SERVER.testdb.[AAA].MyTable
    set MyName = 'abc@pqr.com'
    where ID = 999

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION TRF
END CATCH

It says that the parameter is incorrect.

Can anyone tell me why this is happening and what is solution ?


texts: how do I prevent my postgres connection from timing out? (python, psycopg2)

texts:
very quick one from me: I have a python script that runs continuously, keeping an eye on a postgres database. I've set up a listening channel and everytime a relevant change is made, the script gets a notification and does something accordingly.
the problem I'm having is that sometimes there are hours between notifications and my connection simply "drops" after a while. that means even though a change is taking place, the connection is not active anymore, hence python won't get a notification and everything technically stops.
is there a parameters that can be used to prevent that from happening? when I was using php/mysql, there was a persistent connection option. does postgres have something similar? this is what I currently use to connect:
try:
    conn = psycopg2.connect('postgresql://username:password@localhost:5432')
    conn.set_session(autocommit = True)
    cursor = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
    print('Database connection OK\n')
except:
    print('Database connection error\n')
    sys.exit()


texts: Should I convert empty varchar values to NULL?

texts:
I have a stored procedure which accepts various varchar parameters. The middle tier code which calls the procedure has not been consistent in terms of the values it submits. For example, sometimes a parameter like "Transport Description" will be submitted as NULL and sometimes it will be an empty string.

In terms of the client, an empty string simply means a value was not entered.

Should I convert all the empty varchar parameters to NULL before inserting the data?

I can't decide if this falls into the realm of a best-practice to preserve the integrity of the data or a bad idea because I'm not representing what was actually submitted to the database.

I should note that all inserts and updates are tracked in an audit log.


texts:
I have hotel booking system, Where there are Partitions rooms too and they can be merge into one.

Scenario : 3 rooms (A,B and C) can be merge to Make 1 Big (ABC) room.
Validation : When any one of A, b or c is booked, we cannot booked ABC. Similarly, when ABC is booked, we cannot Booked A, B and C.
So, What I have done, is create a new column parent in the table, add new room named ABC and set ABC identity id in column parent of room A, B and C.

What kind of relation would be useful, One to One self reference, or something else?


texts: Default privileges granted only if table created by one superuser

texts:
I have a role group called normal_users, my username is rbourgeon (I am superuser) and there is another superuser called admin.

I previously ran the following query:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON TABLES TO normal_users;

Now, if I am logged in as rbourgeon and create a new table in the schema, I can see the privilege normal_users=arwdDxt, which is the expected behaviour.

But now, the strange thing is, if I log in as admin and create another table in the same schema, the role normal_users will not be granted these privileges on this table.

The official PostgreSQL documentation, on this issue, reads:

  ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be
  applied to objects created in the future. (It does not affect
  privileges assigned to already-existing objects.) Currently, only the
  privileges for tables (including views), sequences, and functions can
  be altered.

  You can change default privileges only for objects that will be
  created by yourself or by roles that you are a member of. The
  privileges can be set globally (i.e., for all objects created in the
  current database), or just for objects created in specified schemas.
  Default privileges that are specified per-schema are added to whatever
  the global default privileges are for the particular object type.

Is there a workaround? Because many users may create tables in this database.


texts: MySql remove counted value from another count

texts:
I'm trying to remove a counted value from another counted value but I can't get it to work.

I'm using MySql

This is my SQL:

SELECT `GameID`,
count(case `Liked` when 1 then 1 else null end) as likes,
count(case `Liked` when 0 then 1 else null end) as dislikes
FROM `rating`
SET likes = likes + dislikes
GROUP BY `GameID`
ORDER BY likes DESC

For each separate game I'm counting the amount of likes and dislikes this works fine. But I'm wondering if its possible to remove the amount of dislikes from the amount of likes like this:

SET likes = likes-dislikes

This doesn't seem to work.

Does anyone have any ideas how to do this?

Thanks for reading/helping!


texts: Cannot connect to Aurora RDS using workbench over ssh

texts:
I set up an environment on AWS that consists of a VPC with a private and public subnet following the instructions for Magento Quick Start: http://docs.aws.amazon.com/quickstart/latest/magento/architecture.html
The private subnet includes:

an EC2 instance (web server)
an Aurora RDS that connects to the web server

The public subnet includes:

a bastion instance for ssh connections to the EC2 instance in the private subnet

Generally, I can connect to Aurora RDS via ssh by connecting to the bastion host, then connecting to the private EC2 instance, and then to the RDS, however this makes it hard to perform simple tasks on the RDS.
Is it possible to connect to the RDS using mySQL workbench over ssh through the bastion?
I've tried everything but nothing seems to work.
Thanks.


texts: How to include in the result of a `SELECT ... GROUP BY ...` all the other columns that are functionally dependent on the grouping ones?

texts:
I'll base this question on a toy example.

Let this be table A:

            A
-------------------------
 U | V | W | X | Y |  Z
-------------------------
 a | b | c | 1 | 6 | 8.3
 a | b | c | 1 | 4 | 3.7
 a | b | f | 3 | 4 | 2.6
 a | b | f | 3 | 2 | 6.0
 a | e | c | 1 | 0 | 3.5
 a | e | c | 1 | 5 | 8.8
 d | b | f | 1 | 0 | 3.5
 d | b | f | 1 | 3 | 2.3
 d | e | c | 2 | 6 | 2.2
 d | e | c | 2 | 4 | 3.3
 d | e | f | 0 | 7 | 5.0
 d | e | f | 0 | 6 | 3.6

I can produce a second table B by grouping the rows of A by columns U, V, and W, and computing the average of column Z for each group.

         B
-------------------
 U | V | W | Z_avg
-------------------
 a | b | c |  6.0
 a | b | f |  4.3
 a | e | c |  6.2
 d | b | f |  2.9
 d | e | c |  2.7
 d | e | f |  4.3

The SQL for this would be something like

SELECT U, V, W, AVG(Z) AS Z_avg FROM A GROUP BY U, V, W;

But I want the new table to include all the columns of the original table that have a functional dependence on the grouping columns U, V, and W.  In this example there is one such column, namely column X.

In other words, I want to generate the table C shown below:

           C
-----------------------
 U | V | W | X | Z_avg
-----------------------
 a | b | c | 1 |  6.0
 a | b | f | 3 |  4.3
 a | e | c | 1 |  6.2
 d | b | f | 1 |  2.9
 d | e | c | 2 |  2.7
 d | e | f | 0 |  4.3

So this problem has two parts, at least conceptually.

How to determine which columns are functionally dependent on
 columns U, V, and W?
What is the SQL to generate table C?

I know how to implement a (say, Python) script that can answer (1), but it is tedious and slow.  (Basically, for each of the candidate columns, in this case X and Y, the script would collect all of its values for each distinct combination of values in columns U, V, and Z, and then, if each of these sets of values has exactly one element, then the column is functi\onally related to U, V, and Z.)

Likewise, once I have identfied the functionally dependent columns, I can muddle may way through (using temporary tables and what not) to eventually end up with something like table C above (thus, effectively solving (2)).

I figure, however, that this task is sufficiently common that there may be standard tools/techniques to carry it out.


texts:
I'm evaluating PostgreSQL 9.1 and have few questions related to failover and replication details.

I have few test scenarios. First one with a Master server and few Slaves. In case Master crashes I want one of the Slaves to become a Master. After Master get back to normal state it should sync with other servers in cluster (apply all changes done while it was down) and claim back Master role or become a Slave.

The problems I see with PostgreSQL and current scenario are the following.

1) I don't see built-in tools for detecting Master server outage. I read that pgpool can handle it and create trigger file, I also read that people use Linux heartbeat or similiar tools for this. Okay, I can detect failover and assign a new Master in cluster. Will the other Slaves understand that there is a new Master and they should backup it now?

2) I don't understand the failback procedure. Master and Slave host configurations are different. So will I have two Masters after crashed Master failback? How will servers get back in sync? I saw only manual solutions like "transfer data folder to server and restart it". So what is the solution or best practice or at least key principal here?

3) How should I handle server outage on client side? When I create connection I explicitly specify server IP. Should I develop some kind of ConnectionManager which will know my Master-Slave structure, send requests to Master only and in case of connection loss will switch to backup servers and so on? I read that pgpool can be an entry point for applications and manage connections in correct way. Is pgpool the only solution here? Does it handle failover and failback well?

4) Are there any solutions (commercial also) so I could avoid manually copying the data, reconfiguring PostgreSQL instances and other stuff which should be done by hands? So kind of cluster configuration when everyone in sync, it's clear who is Master and everything switches automatically without operator attention?

According to these threads and articles

Streaming replication and failover on PostgreSQL

Automating failover in PostgreSQL 9.1

http://denishjpatel.blogspot.com/2010/11/possibility-of-graceful-switchover.html

there is no single fully automatic solution to solve these questions. Am I right?

Thanks!


texts:
Hello I have The tablespace with 8192 and According with this Link From Oracle BigFile
I can have 32,768 GB In my tablespace but one of my table have over 1 billion records that make datafile over the 120GB and I getting error with ORA-01652: unable to extend temp segment by XXX in tablespace so what happening here why with 120GB I'm reaching my limit in datafile?
CREATE BIGFILE TABLESPACE TABLE_SPACE
    DATAFILE
        '~\TABLE_SPACE.DBF' SIZE 127301910528 AUTOEXTEND ON NEXT 8192 MAXSIZE 35184372064256
    BLOCKSIZE 8192
    DEFAULT NOCOMPRESS NO INMEMORY
    ONLINE
    SEGMENT SPACE MANAGEMENT AUTO
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;


texts: Enable TLS 1.2 for SQL Server 2016 database mail

texts:
I have been puzzled by this issue for almost 1 week. Hopefully someone in our community has experienced the same issue and already found a solution.

So here is my problem:

As per our company policy, we want database mail to be able to send emails over port 25 with TLS 1.2 enabled and with TLS 1.0 & TLS 1.1 disabled.

Our mail server is Exchange Server 2010, our SQL Server 2016 (Developer and Enterprise editions) boxes have OS of Windows Server 2016 Standard editions.

Our SQL Server version is:

select @@version
----------------------------------------
Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)
    Dec 22 2017 11:25:00
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

We have the DB mail configuration as shown here.

The issue is whenever we turn on SSL

use msdb
exec dbo.sysmail_update_account_sp @account_id=2, @enable_ssl = 1;

We CANNOT send db mail (no matter whether our SMTP authentication is Windows Authentication, Basic authentication or Anonymous Authentication). The error message in db mail log is as follows:

  Message

  The mail could not be sent to the recipients because of the
  mail server failure. (Sending Mail using Account 2
  (2018-07-30T10:52:41). Exception Message: Cannot send mails to mail
  server. (Failure sending mail.). )

But if we turn off this SSL, there is no problem for db mail sent out.

So how can we enable SSL and uses TLS 1.2 for db mail?

I have enabled TLS 1.2 by adding registry as shown below

The details is from this link (see the FAQ section)


texts:
In SQL Server 2005, what happens when the plan cache fills up?  I have done some research, and from bits and pieces gathered it seems that cached plans are given an "age" that consists of it's weight, or cost to compile, multiplied by the number of times it has been run since being cached.  Over time this age is decremented, until it reaches 0, when it is considered "aged out" and is a candidate for flushing when needed.  When the plan cache fills, aged out plans are flushed from the cache.

Is there anything else that is happening behind the scenes to complicate this scenario?

Thanks,

Matt


texts: How to verify that a PostgreSQL base + WAL backup has been restored correctly

texts:
Coworkers were trying to extract a PostgreSQL database copy from a backup made at a hot standby in version 9.1, but it wasn't reliable - we would run it daily, but usually it would end up with various errors while running queries against the copy.

Sadly I hadn't been able to find a definitive answer as to why on the web, and it took a kind soul in the PostgreSQL IRC channel to set me straight - taking a backup like that from a standby isn't supported out-of-the-box in that version.

So for the benefit of others who may run into the same problem and try to google it, I'm going to write up our notes in an answer below.


texts: What signals do the DBWn processes send to LGWR?

texts:
I'm preparing to take the OCA exam.

I would really like to know what signals are in the following quote from the Process Architecture docs, and how does DBWn know LGWR has completed?

  Before DBWn can write a modified buffer, all redo records that are associated with the changes to the buffer must be written to disk (the write-ahead protocol). If DBWn finds that some redo records have not been written, it signals LGWR to write the redo records to disk and waits for LGWR to complete writing the redo log buffer before it can write out the data buffers.


texts: How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats

texts:
Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings:

FOR COLUMNS SIZE 5 some_column

and

FOR COLUMNS some_column SIZE 5

both will create a histogram with 5 buckets for the column 'some_column' right?

The official Oracle reference states that METHOD_OPT should have this form:

  FOR COLUMNS [size clause] column|attribute [size_clause]
  [,column|attribute [size_clause]...]

but this doesn't really help my case.

I've seen sources which use both versions so not sure if there is any difference.

EDIT

I was told that both of these are the exact same, but then what about these 2:

FOR COLUMNS SIZE 5 some_column SIZE 6 some_column2

and

FOR COLUMNS some_column SIZE 5 some_column2 SIZE 6


texts:
I am using reporting services to generate a list of families in my database. After the first family member in the list, I want to put the family's address:

family, First
123 la la lane
Somewhere, Ca 90210
famliy, Second
family, Third

two, first
345 zippy st
Nowhere, UT 85763
two, second
two, third

At first, I thought my expression would be:

=IIF(First(Fields!person_id.Value) = Fields!person_id.Value,
Fields!last_name.Value & " " & Fields!person_suffix.Value & ", " & Fields!person_title.Value & " " & Fields!nick_name.Value & " (#" & Fields!person_id.Value & ")" & vbcrlf & Fields!address.Value & vbcrlf & Fields!city.Value & ", " & Fields!state.Value & " " & Fields!postal_code.Value,
Fields!last_name.Value & " " & Fields!person_suffix.Value & ", " & Fields!person_title.Value & " " & Fields!nick_name.Value & " (#" & Fields!person_id.Value & ")"
)

This is generating a "Overload resolution failed because no accessible 'IIf' accepts this number of arguments." I am guessing my First() does not evaluate properly.

How would I craft this expression to achieve the above output?


texts: Why does the "Date Created" for the master database show such an old date (8 April 2003) in SQL Server 2008 R2?

texts:
Why does the "Date Created" for the master database show such an old date?

I created the new instance today, not 2003.


texts: Compare two users permissions on SQL Server database?

texts:
Is it possible to compare permissions of two users on one SQL Server database?
Is there any simple query to do it?

I am asking this because I want to find out if user B has different or same level of permissions as user A on a database.


texts: Perform update and raise notice in Postgres Function

texts:
I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened.
I don't know is "NOTICE" is the proper RAISE (Why not "INFO"?).
The central question is: how I can add the raise notice argument after the update?


texts: mysqlimport locks all tables system wide

texts:
I recently developed a script that spawns multiple processes to import tables in parallel using mysqlimport and a --tab type mysqldump export. On the development server it works very well and compared to a standard mysql db_name < backup.sql type of import it cuts the time from around 15 minutes to 4 or 5 minutes.

The problem is on our production server this script seems to be locking tables system wide. That is to say, I'm importing a backup to a completely different database but our live application tables still end up locked. A SHOW PROCESSLIST confirms that tables on our live db are indeed locked but no INSERT or UPDATE queries are running on any tables in that database.

Why is this happening? Is there a configuration variable / setting that I can adjust to prevent this lock from happening?


texts:
Today, my SQL Server 2008 is attacked by hacker from IP address in China [4300 Login failure in last 7 hours]. I contacted GoDaddy support and below is what they told me.

EXEC sp_readerrorlog 0, 1, 'Login failed' [and result 4300+ failed login and still increasing]

  As for the SQL Server brute force attack against your server you would need to determine the best method for resolving that on the server. On some systems the best solution is to simply update the firewall on the server to block access to all connections to the port for SQL Server, 1433, so that only a specific IP can access that port, however the security of your server would be up to you and as such we would not be able to guide you on changing the scope of a windows firewall rule. You may wish to review Microsoft's knowledge base for information on using and modifying the windows firewall on your server.

How can I  update the firewall on the server to block access to all connections to the port for SQL Server, 1433, so that only my own IP address can access the SQL Server?

Here is what i have done so far (Next day):
I disabled the SA account, since all the attack was on SA username.

I tried to add a inbound rule in firewall for Port number 1433 to block anonymous IP address.
I tried to add a inbound rule in firewall for Program(sqlserver.exe) to block anonymous IP address.

but no WIN yet. login failed attempts is continue to increasing. Can someone guide me how to block anonymous IP on the firewall to access the SQL Server.


texts: Install SQL Server Express without administration rights

texts:
Is there a way to install SQL Server Express without administration right?

Is there a portable version?

(I need to test SQL Server on my computer before using the server but I don't have administration right. so I cannot install it. I search a way to bypassed my problem)


texts:
We are using Merge Replication with SQL 2005. It seems I need to change the Autogrowth settings on the Publisher but I am wondering what the impact is to Replication.

Will it force an Initialization? Anything else that I need to be aware of?


texts: Performance issue when querying items inside circles

texts:
I need to query 300 items among 100,000+ and all of these 300 must be contained inside a set of many circles (hundreds). I am using a MongoDB database and here is a part of my logs:

"command": "{
  count: \"items\",
  query: {
    location.coordinates: {
      $geoWithin: {
        $box: [
          [ 2.172203063964844, 48.78040136289702 ],
          [ 2.506599426269531, 48.98765230234855 ]
        ]
      }
    },
  search: \"rent\", isPublished: true,
  $or: [{
    location.coordinates: {
      $geoWithin: {
        $center: [
          [ 2.512155566998092, 48.96309599735446 ], 0.002204591143019205 ]
        }
      }
    },{
      location.coordinates: {
        $geoWithin: {
          $center: [
            [ 2.316596761508817, 48.88132429394468 ], 0.02168492811759336
          ]
        }
      }
    }, { location.coord...",

I tried to add a geospatial index but the performances are still very bad (around 10 seconds).

I'm building my query with mongoose:

    query.where({
      $or: arrayOfNodes
    });

where a node looks like:

{
    'location.coordinates':
    {
        $geoWithin:
        {
            $center: [ [parseFloat(node.lng), parseFloat(node.lat)],
            reachableRadiusInDegrees]
        }
    }
}

Do you have and idea how I could improve this query?


texts: How can I see the plan for ONE statement in a stored procedure?

texts:
I've got some SQL which I've run several times a day in development, generally getting execution time of 3-8 minutes.

Now I've wrapped it in a stored procedure (with no parameters), the performance is impossible.  THe first "heavy" statement, which involves only c.27k rows, never finishes.

Actual execution plan is not an option, because I can't even get this statement to finish.  So I have to look at estimated execution plan.

Trouble is I get a separate "sub-pane" in the Execution Plan pane for every statement.  I can't comment out preceding statements as they're necessary for the problem statement to run (though I've commented out all following statements).

How can I view just the plan for the problem statement on-screen?  It's obviously a huge plan.  All the other statements' plans (which I'm not interested in) take up an enormous amount of screen space, so it's impossible to scroll through the important one and find the chokepoints.  But there seems to be now way to "Show just the plan for this statement, and take up the full screen".

Am I missing something?


texts:
We are an email marketing company and we recently switched our setup to MySQL. We need to configure MySQL (my.cnf) for extreme performance.

We have tried to configure my.cnf but heavy queries got stuck for many hours without any result. Some queries take one or two hours to complete on our SQL server (8GB RAM).

It's a dedicated MySQL server.

OS = Ubuntu
Installed RAM = 512GB
Table type = MyISAM

We have very large tables (a few tables having more then 70 million rows). Almost every time new tables to match so indexes won't help that much, although the necessary indexes exist.

We do not have any application to connect our database, just 2 users who write adhoc    queries directly.

Below are some variables we have changed from the default values.

key_buffer_size         = 250G
read_buffer_size        = 2G
read_rnd_buffer_size    = 50M
join_buffer_size        = 25M
tmp_table_size          = 5G
sort_buffer_size        = 2G
innodb_buffer_pool_size = 2G
query_cache_limit       = 10M
query_cache_size        = 100M
max_connections         = 20

Below is the MySQLTuner recommendation:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-0ubuntu0.12.04.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1909G (Tables: 940)
[--] Data in InnoDB tables: 2G (Tables: 3)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6m 59s (9 q [0.021 qps], 6 conn, TX: 10K, RX: 625)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 252.1G global + 4.1G per thread (20 max threads)
[OK] Maximum possible memory usage: 333.6G (66% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 5% (1/20)
[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 83% (1 created / 6 connections)
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 1% (18/1K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[!!] Connections aborted: 16%
[!!] InnoDB data size / buffer pool: 2.2G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 10M, or use smaller result sets)
    innodb_buffer_pool_size (>= 2G)

Any help would be greatly appreciated.


texts: Inserting documents via standalone connection to MongoDB replica set

texts:
I have a MongoDB standalone server, which I am converting to a replica set by adding other servers. However, some clients are still connecting to the original server without specifying any other servers or the ?replicaSet= parameter in the URI, and some of these clients insert new documents.

What happens to the documents that are inserted? Will they be propagated correctly to the rest of the replica set? What happens if the original mongo switches to Secondary status and the clients keep trying to insert documents?


texts: How should I deal with sub attributes of xml data when loading onto mysql?

texts:
I have some xml files (example below). As you can see in the xml, id 4 has 3 entries for tax. When I load it right now, only the last entry gets inserted into the table.

How can I include all 3 of them when I load it into mysql?
What's the best approach to deal with sub attributes of data in xmls?

Table:
mysql> create table if not exists trial (
    -> id NUMERIC PRIMARY KEY,
    -> tax TEXT,
    -> shipping VARCHAR(4),
    -> cost NUMERIC
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD XML INFILE '/var/lib/mysql-files/trial.xml' INTO TABLE trial ROWS IDENTIFIED BY '<entry>';
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from trial;
+----+------+----------+------+
| id | tax  | shipping | cost |
+----+------+----------+------+
|  1 | NULL | YES      |   10 |
|  2 | NULL | NO       |   20 |
|  3 | NULL | YES      |   30 |
|  4 | NULL | YES      |   90 |
+----+------+----------+------+
4 rows in set (0.00 sec)

XML data:
<?xml version="1.0" encoding="utf-8" ?>
<feed>
    <entry>
        <id>1</id>
        <tax>
            <shipping>YES</shipping>
            <cost>10.0</cost>
        </tax>
    </entry>
    <entry>
        <id>2</id>
        <tax>
            <shipping>NO</shipping>
            <cost>20.0</cost>
        </tax>
    </entry>
    <entry>
        <id>3</id>
        <tax>
            <shipping>YES</shipping>
            <cost>30.0</cost>
        </tax>
    </entry>
    <entry>
        <id>4</id>
        <tax>
            <shipping>YES</shipping>
            <cost>70.0</cost>
        </tax>
        <tax>
            <shipping>YES</shipping>
            <cost>80.0</cost>
        </tax>
        <tax>
            <shipping>YES</shipping>
            <cost>90.0</cost>
        </tax>
    </entry>
</feed>


texts: Overriding a row FORMAT_STRING with a column FORMAT_STRING in MDX

texts:
Does anyone have an idea how you could override the format string on a row with a format string on a column?

I have a query like the following:

WITH
    MEMBER [Measures].[ROH Total Sold Gross]                AS [Measures].[ROH Total Sold Dollars] - [Measures].[ROH Total Sold Cost], FORMAT_STRING='Currency'

    MEMBER [ClosedDate].[Date].[MTD]                        AS AGGREGATE([ClosedDate].[Date].[2017-07-01]:[ClosedDate].[Date].[2017-07-31])
    MEMBER [ClosedDate].[Date].[PrevMTD]                    AS AGGREGATE([ClosedDate].[Date].[2017-06-01]:[ClosedDate].[Date].[2017-06-30])

    MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD]          AS [ClosedDate].[Date].[MTD] - [ClosedDate].[Date].[PrevMTD]
    MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD %]        AS [ClosedDate].[Date].[MTD vs. Prev. MTD] / [ClosedDate].[Date].[MTD], FORMAT_STRING='Percent'
SELECT
    {
        [ClosedDate].[Date].[MTD],
        [ClosedDate].[Date].[PrevMTD],
        [ClosedDate].[Date].[MTD vs. Prev. MTD],
        [ClosedDate].[Date].[MTD vs. Prev. MTD %]
    } ON COLUMNS,
    NON EMPTY
    {
        [Measures].[ROH Count],
        [Measures].[ROH Total Sold Gross]
    } ON ROWS
FROM
    [ServiceDept]

The ROH Count row is properly formatted as an integer, with the exception of the MTD vs. Prev. MTD % column, which is properly formatted as a percentage.

The problem is the ROH Total Sold Gross row, where all columns are formatted as currency.

Has anyone done anything like this before?


texts: Change column type from varchar to text in all tables at once

texts:
I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text.

If anyone knows that I would be very glad for your help.


texts:
Not trying to start a war here, but I would like some thoughts.

I work for a small nonprofit that is eligible for generous charity licensing from Microsoft. I am the database/web coder and my background is with open source stacks. I am most comfortable using PHP and MySQL.

That said, we are undertaking some large projects and am wondering whether it'd be worth investing in MS Server 2012 as a data staging and reporting system. It would not be used for any production purposes. I am most interested in the server studio management's advanced capabilities for importing and exporting data and for little productivity boosts like Intellisense.

What do you think? Is it worth the 80% discounted costs, or should we remain open source?


texts: Oracle ASM : Alter asm_diskstring when ASM Instance is Down,

texts:
I am beginner for Oracle ASM
I know we can alter asm_diskstring with following ways

SqlPlus
ASMCA

but these requires +ASM to be UP and running. Is there way to set asm_diskstring when +ASM instance is down ?

Thanks,


texts: SQL Services Wont Start After Removing TLS

texts:
I need to remove TLS 1.0 from one of my SQL servers. When I do so and then reboot the server the SQL services dont start automatically. When I attempt to start them I get an error that it cant be done.
Once I renable TLS 1.0 the SQL services start again after a reboot.

Im running SQL 2012 Developer edition. Version is 11.0.2100.60

Im using IIS crypto to remove TLS.
I keep TLS1.1 and 1.2 on there

Has anyone got a suggestion of what I can do to have the SQL services start when I remove TLS 1.0

Thanks


texts: Why does my AWS-RDS storage, under billing, say its approaching the limit (20GB) when my DB size is only 10MB?

texts:
I only have one instance running on my AWS RDS as Psql DB. The size of my DB, which has twenty tables but all the data amounts to 10MB. As for the screenshot I added, it says the month-end estimate of my RDS storage will be close to reaching the allocated limit. I'm not understanding how 10MB DB can balloon up to 11.80GB.

I went through the AWS RDS forums and one possible problem would be automatically created error logs. But I went through my logs and it's about 100 logs of 3.0KB each. So I doubt that's the culprit.

I'm not even remotely close to understanding what the possible issue could be.

Update 1, responding to @PhilTM and @Dave Rix:

To determine the size of my DB I ran the query below:

SELECT
  *, pg_size_pretty(total_bytes) AS total
  , pg_size_pretty(index_bytes) AS INDEX
  , pg_size_pretty(toast_bytes) AS toast
  , pg_size_pretty(table_bytes) AS TABLE
FROM (
  SELECT
    *, total_bytes - index_bytes - COALESCE(toast_bytes, 0) AS table_bytes
  FROM (
    SELECT
      c.oid, nspname AS table_schema, relname AS TABLE_NAME
      , c.reltuples AS row_estimate
      , pg_total_relation_size(c.oid) AS total_bytes
      , pg_indexes_size(c.oid) AS index_bytes
      , pg_total_relation_size(reltoastrelid) AS toast_bytes
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE relkind = 'r'
  ) a
) a;

As for the size of the backup I haven't configured it to include extra backups. The automatic backup had always been turned on (since that's default). But the customer doesn't get charged extra for letting the default automatic backup to work per :

https://aws.amazon.com/rds/pricing/
"Backup Storage
Backup storage is the storage associated with your automated database backups and any user-initiated DB Snapshots you have taken. Increasing your backup retention period or taking additional database snapshots increases the backup storage consumed by your database.
There is no additional charge for backup storage up to 100% of your provisioned database storage for an active DB Instance.* After the DB Instance is terminated, backup storage is billed at $0.095 per GB-month.
Additional backup storage: $0.095 per GB-month
* For example, if you have an active DB Instance with 10GB-month of provisioned database storage, we will provide up to 10GB-month of backup storage at no additional charge. Based upon our experience as database administrators, the vast majority of databases require less raw storage for a backup than for the primary data set, meaning that most customers will never pay for backup storage. "

Just to be sure I turned off the automated backups but the usage increments daily despite the fact that the size of my DB hasn't changed much.

As for getting the size of the WAL file, AWS does not allow the user to get one, assuming the link below is correct:
Get WAL files from AWS RDS PostgreSQL instance

To answer @Dave Rix, I've had this RDS instance for close to six months now. Also, it's not the estimate I'm worried about. It's the "Month-to-date actual usage". I have not used 11.8GB of storage.

Update 3,
The issue has been answered per the link below:
https://forums.aws.amazon.com/thread.jspa?threadID=196444

It seems that with the "free-tier w/ 20GB storage/m", when I have a single instance running, aws increments 20GB / 31 (or 30) daily making it out to close to 20GB by the end of the month. So no problem here. Thank you both.


texts: Simple way to Drop Test databases and restore from Prod databases on 2008r2

texts:
I have a production database instance on a production server and a test database instance on a test server. I need to restore the production databases to the test instance daily.

What I need:

LOOP through Prod DB
If DB name exists in Test Drop the test database
Restore DB from Prod
Next in LOOP

SQLServers Copy Databsase Wizard seemed to do what I wanted but it errors on custom database roles, apparently an issue with 2008r2.

Apex Backup is also getting an error.

Backups are made of the production databases daily so I could also just drop the test databases and restore from those BAK files.

Is there an easy way to do what I need to do?

PS: I'm not a DBA, I'm a dev who has to do DBA stuff, so forgive me my ignorance.


texts:
We have an application database on Server A that is replicated to Server B using push transactional replication. Reporting is done on the replicated copy on Server B.

We are having issues where the replication process, which obtains a shared with intent exclusive (SIX) lock, deadlocks with SELECT statements, which obtain an Intent shared (IS) lock. The SELECT statement is chosen as the deadlock victim, the query is terminated, and the report developer isn't happy.

Where should I fix this? Should we add NOLOCK hints to the SELECT statement? Change some replication setting? Is there a bigger issue, like poor database design, at work here?


texts: Two FOREIGN KEY constraints with SET NULL targeting the same PRIMARY table

texts:
I feel like I'm missing something obvious here...

I have a situation with a primary table (let's call it People) and a secondary table (let's call it Groups) with two different roles that people might fill (Secretary & Treasurer).  Both those columns are int and FKs back to People.ID.

CREATE TABLE People(
ID int NOT NULL,
FullName varchar(50) NOT NULL,
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED (ID ASC))

CREATE TABLE Groups(
ID int NOT NULL,
Treasurer int NULL,
Secretary int NULL,
CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (ID ASC))

So far so good.  But the roles are optional (nullable) so I want to be able to delete a Person and have the FK values in Groups set to NULL.

ALTER TABLE dbo.Groups ADD CONSTRAINT FK_Groups_Treasurer FOREIGN KEY (Treasurer) REFERENCES dbo.People (ID) ON DELETE SET NULL

ALTER TABLE dbo.Groups ADD CONSTRAINT FK_Groups_Secretary FOREIGN KEY (Secretary) REFERENCES dbo.People (ID) ON DELETE SET NULL

Now we have a problem:

  Introducing FOREIGN KEY constraint 'FK_Groups_Secretary' on table 'Groups' may cause cycles or multiple cascade paths.

What is the problem here, and why does SQL Server care?  What is the possible cascade conflict?  If I delete a Person, set NULL the FK values in any related tables.  This would be no problem against two different tables, each with a FK.  But having two FK values in the same table seems to be prohibited.

Am I missing something?  Is this somehow bad design?  What's the correct way to deal with this situation?  It seems like a common use-case.


texts: How to insert records using select statement?

texts:
Hi I am working on one project, here what I am trying is, I want to insert records what I get using select statements into temp table. but it is giving error to me, can any one help me to solve this ?
ERROR:  relation "temp_mytbl" does not exist
SQL state: 42P01
following is my query
truncate table temp_mytbl;
create temporary table temp_mytbl as
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum  limit 0;

 insert into temp_mytbl
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
group by iex_id,dte,agent_name,schd_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.schd_total) AS s_schdhours
   FROM temp_mytbl i
     JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
     JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no AND idm.src_type_id = 618 AND soar_date(a.calendar_date_key) >= idm.start_dte AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
  GROUP BY i.dte, idm.empl_id limit 100;


texts: Partitioning / indexing an extremely large table

texts:
I'm working on indexing and partitioning a single data warehouse table that weighs in around 500 GB. The table is a heap, has over a hundred TEXT columns, and the TEXT_IN_ROW option is enabled. I didn't design this table, and I don't have any capacity to change it in the immediate future.

I've been tasked with partitioning it. We're tackling this using a copy of the database on a test server. It can push about 2 GB per second to the SSD RAID arrays, so I/O isn't a significant bottleneck, and it's got 16 cores (2 NUMA nodes), and 64 GB of RAM.

My approach is to disable all the nonclustered indexes, create a partition function and partition scheme (about 12 partitions, all on the PRIMARY filegroup - they're using this to enable rolling maintenance and provide more localized inserts for nightly ETL, and not to distribute I/O), then build a clustered index for the table using this partition scheme.

I'm creating the clustered index and partitioning the table as follows:

CREATE CLUSTERED INDEX CX_DailyTable ON DailyTable (LoadDate, SeqNumber)
  WITH (SORT_IN_TEMPDB = ON) ON monthly_on_primary (LoadDate)

Obviously, it's taking a long time (3 hours so far as of this post), and I certainly don't expect it to be quick. What worries me slightly is that tempdb is now pushing nearly 1 TB and steadily climbing, despite the current table being around half that size. The MS docs I've read suggest the tempdb space usage should be about the size of the final table/clustered index.

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

  If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure. The sort runs contain the leaf rows of the index.

Are their estimates incorrect? Is tempdb being used for substantially more than just the sort runs? Or is creating this clustered index somehow doubling the size of the table? (Seems pretty unlikely; it's a rather wide table, and I estimate we're getting an extra 4-8 bytes per row, plus non-leaf pages by adding a clustered index.)


texts: how can be changed hot_standby parameter on postgres 9.6 aws/rds?

texts:
I have a postgres solution on aws/rds with a master and a read replica.

                         master    slave
hot_standby               on        on
transaction_read_only     on        on
pg_is_in_recovery()       true      true

Having the master set in this way the vacuum and analyze process don't work.
Any idea how to change this setting?
thank you!


texts: Using a data warehouse staging database as a source of operational data

texts:
Should you use the staging database within a data warehouse as a source of operational data?

ie, is it good practice for other operational (non BI) systems to source their data from this database? Or is the data warehouse there for information reporting/analysis and definitely not for information processing/feeding to other systems?

In general, is it good practice to source operational data from any part of a data warehouse system? Or should a data warehouse be a consumer of data only?


texts: Understanding joined set-returning functions in the FROM list

texts:
I am using a construct like this very often:

SELECT *
FROM
    my_table,
    my_srf(my_column)

for example here:

CREATE TABLE my_table (
    my_col text[]
);

INSERT INTO my_table VALUES
('{a,b}'),
('{c,d,e}');

SELECT
    *
FROM
    my_table,
    unnest(my_col);

The result is as expected:

my_col    unnest
{a,b}     a
{a,b}     b
{c,d,e}   c
{c,d,e}   d
{c,d,e}   e

demo:db<>fiddle

Now I thought about this construct a little bit and got confused. The comma notation is the shortcut for as CROSS JOIN. In fact, this works as well:

SELECT
    *
FROM
    my_table
CROSS JOIN
    unnest(my_col)

But because this is a cross join, this result would look more logical to me:

my_col    unnest
{a,b}     a
{a,b}     b
{a,b}     c
{a,b}     d
{a,b}     e
{c,d,e}   a
{c,d,e}   b
{c,d,e}   c
{c,d,e}   d
{c,d,e}   e

A cross join joins all records from one part with all records of the other.

Although I am using this SRF joins as described above, I am now not sure anymore why it works in detail. So, I think that I obviously did not understand the complete functionality of SRFs so far.

Because the documentation made it not clearer to me, I would like to ask you to explain the behaviour.


texts:
The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit last night)
The backup started 12 hours ago and for the first 2 hrs it was showing an estimated total runtime of 8 hrs. For the last two hours it is showing an estimated remaining time of 90 minutes. It seems to be doing 1% per hor right now leaving another 8 hours to finish.

Perfmon is showing 6MB/s going out on the network and 6MB/s going in on an otherwise idle machine (share 1). Share 2 is not idle, so there is noise on the network but no significant trafic. USB drive is at 0 bytes a second for a while now.

Any Ideas of how to revive the two "forgotten" backup targets to get this backup finished before tonight?

P.S:ETA and percent completed coming from sys.dm_exec_requests

Backup command is:

BACKUP DATABASE [XXXXXXXXXX] TO
DISK = N'D:\backup\XXXXXXXXXX_20121019_T3_A',
DISK = N'\\server3\XXXXXXXXXXBak\XXXXXXXXXX_20121019_T3_B',
DISK = N'\\server2\XXXXXXXXXXBak\XXXXXXXXXX_20121019_T3_C'
WITH NOFORMAT, NOINIT,  NAME = N'XXXXXXXXXX-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

The drive that it is currently stil writing to is the one that we usually backup to. That usually takes 60 hours writing to a single file.


texts: Table check contraint to verify unique on specific value

texts:
Oracle 10g: Suppose the following table (T_REGISTER):

ID_PROCESS   PERIOD    CUSTOMER    STATUS
===========================================
0001234      201801    12300344    INVALID
0001236      201801    12300344    INVALID
0001246      201801    12300344    UNPAID
0001249      201801    12300344    UNPAID
0001278      201801    12300344    COMPLETED

ID_PROCESS is the PK.
But I also need that (PERIOD + CUSTOMER + STATUS) to be unique just when the STATUS is COMPLETED.

In other words, for one UNIQUE (PERIOD + CUSTOMER), I don't care having several INVALID or UNPAID rows, but I only can admit one unique (PERIOD;CUSTOMER; STATUS="COMPLETED")

Any elegant solution using CHECK CONSTRAINT before going for the trigger solution?
Thanks in advance...


texts: MySQL multiple update queries vs. one insert … on duplicated key update query

texts:
I need to update hundreds to thousands rows. I wonder which of the following options is better:

sending multiple (hundreds to thousands) "UPDATE" queries
sending one (big) "INSERT INTO ... ON DUPLICATE KEY UPDATE" query

More info:

The table is InnoDB.
The rows are all existing (duplicated). There won't be new rows to be inserted in this operation.
About 4 - 10 columns will be involved.
Only this one table is involved.
I am using PHP to execute the queries.

Insert should be faster because I can batch multiple rows into one single query, but this is a read-heavy table and I am worried about locking. Will the insert query result in more locking problems than the multiple update queries?

Thanks


texts: How SQL server generates a Query plan with Auto Create Statistics set to OFF

texts:
I am wondering how SQL server can generate a query plan with "Auto create statistics" set to "off".

I stumbled on the below context at the below link while searching for an answer(Sorry, I searched, but couldn't find anything better).

https://www.simple-talk.com/sql/performance/execution-plan-basics/

  Table variables do not ever have statistics generated on them, so they
  are always assumed by the optimizer to have a single row, regardless
  of their actual size

Even though the above is applicable only for Table variables, I thought SQL Server will use the same principle to derive a query plan for a table and decided to do a small test.

I cleared the PROC cache and Ran the below query on a table with 1 million records

My test proved me wrong and SQL server reported missing statistics(as expected), But came up with the estimated number of rows as 31622(I expected it to be 1) when there are no statistics. How did the query optimizer come up with this value without statistics?


texts: Does SQL Server repair rollback the buildnumber?

texts: Let's say that my current SQL Server version is 2014 sp1 cu1. I repaired the existing installation using base rtm file. Will that rollback my version to 12.00.2000?


texts: Questions and advice for complying with Federal tax information (FTI) Security Guidelines for Federal, State and Local Agencies per Publication 1075

texts:
I've been able to find writings on compliance topics (e.g. HIPAA, SARBANES-OXLEY, PCI DSS) but I haven't found much on FTI (Federal tax information) and Publication 1075 outside of the IRS website itself.

Since I haven't found hardly anything out there this will be quite lengthy in the hopes of getting answers that will benefit others than myself as well.

Just like the other external audits, it recommends (requires) adherence by doing such things as:

Set the 'Ad Hoc Distributed Queries' Server Configuration Option to 0
Set the 'CLR Enabled' Server Configuration Option to 0
Set the 'CLR Assembly Permission Set' to SAFE_ACCESS for All CLR Assemblies -
Set the 'Remote Access' Server Configuration Option to 0
Set the 'Remote Admin Connections' Server Configuration Option to 0
(what if I need to use DAC)
Set the 'Trustworthy' Database Property to Off
Disable the 'sa' Login Account
Rename the 'sa' Login Account Set the 'xp_cmdshell' Server
Configuration Option to 0
Ensure the SQL Server’s SQLAgent Service Account is Not an
Administrator
Set the 'MUST_CHANGE' Option to ON for All SQL Authenticated Logins

The full list of Computer Security Evaluation Matrix (SCSEM) downloads and the one for Database – SQL Server SCSEM (XLS) are available for use in preparing an IT environment that will receive, process, or store FTI. Maybe the free downloads there could help someone else with Audit findings ideas as well.

All that to setup my questions to hopefully get answers that decrease the burden that all of this brings.

Is there anything on the internet or print outside of the IRS website (videos, podcasts, books, whitepapers, blogs, etc.) which discusses IRS Publication 1075 (which outlines the requirements and guidelines for agencies in receipt of FTI to establish procedures to ensure the adequate protection of the FTI data they receive).
If anyone has been through one of these audits...what is your advice and do you have any recommendations?
Are there any resources (e.g. tools, scripts, spreadsheets) that anyone knows of for other external audits (e.g. HIPAA, SARBANES-OXLEY, PCI DSS) that could be helpful for this or other types of audits?
Are there specific items in the safeguards-scsem-sql-server.xls that cause the most trouble when changed and what are some of the things to look for and prepare in advance of changing?
Lastly, how would you object to certain settings that are recommended (required) but aren't necessary an industry best practice? Looking at the SCEM spreadsheet are there any that just make your eyes roll and you can provide valid reasons for not adhering to it?

Any help you can provide me would be greatly appreciated because I'm not finding much info that's helpful.


texts: "If not exists" using OBJECT_ID() doesn't work for views and triggers. why?

texts:
For tables I can implement "if not exists" and "if exists" as folows:

--if table exists - drop
If OBJECT_ID('A','U') is not null
Drop Table [A]
--if table not exists - Create
If OBJECT_ID('A','U') is null
Create Table A([key] varchar(20), [value] varchar(max))

but it is not quite working the same on views and triggers

I can do:

-- if exists - drop
If OBJECT_ID('VA','V') is not null
Drop view [VA]

but when I'm trying the oposite:

-- if not exists - create
If OBJECT_ID('VA','V') is null
Create view [VA] as Select * from [A]

I'm Getting the following error:

  Incorrect syntax near the keyword 'view'

And the same is with triggers. when I do:

-- if not exists - create
If OBJECT_ID('Trigger_A_ins','TR') is null
Create trigger [Trigger_A_ins] On [A] instead of insert As
   insert into A select * from inserted

I'm getting error:

  Incorrect syntax near the keyword 'trigger'

But:

-- if exists - drop
If OBJECT_ID('Trigger_A_ins','TR') is not null
Drop Trigger Trigger_A_ins

is working.

Did I missed anything?

Can anyone explain this difference between tables to triggers and views?

Note: I'm using sql server 2012


texts:
According to the Percona article MySQL checkpoint age may freeze it:
https://www.percona.com/blog/2011/04/04/innodb-flushing-theory-and-solutions/
How can I calculate it?
How can I ensure that innodb_log_file_size of 134 mb is enough for me or I need to increase it?
This is the section LOG from SHOW ENGINE INNODB STATUS, but what is the correct formula?
( and how do I get megabytes to compare with innodb_log_file_size )
---
LOG
---
Log sequence number          26361002986468
Log buffer assigned up to    26361002986468
Log buffer completed up to   26361002986148
Log written up to            26361002986148
Log flushed up to            26361002902700
Added dirty pages up to      26361002986148
Pages flushed up to          26360959010673
Last checkpoint at           26360958543063
7934296064 log i/o's done, 669.00 log i/o's/second

Thanks.
P.S  We are thinking should we increase innodb_capacity_io from default 200 to, lets say, 500 or 1000. ( we have enough iops available )


texts: Is there any way to trace the optimizer's work in Oracle?

texts: Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in Oracle through which I can see what are the alternative plans the optimizer has ever considered (and pruned because they are more expensive than the plan that the optimizer chooses to execute)?


texts: Delete millions of rows disabling Indexes

texts:
I have a table with millions of rows. I'm deleting where year(data) in (2013,2014,2015). It's taking more than 4hrs. I'm trying now to delete only year 2013 but it's still very very slow (every year has like more than 600.000 rows and it has a varbinary column to store .PDF files).

I know that indexes can decrease performance on deletes. It will be a good Idea to Disable both indexes in that table, and then re-enable them?

Can it be faster if I create a view, and delete from the view?

Edit:

I made this query to delete it by top 1:

delete top (1) from MyTable where year(data) in ( 2013)--,2014,2015)
while @@rowcount > 0
    begin
    delete top (1) from MyTable where year(data) in ( 2013)--,2014,2015)
    end


texts: Can copy command in postgresql append data to a file?

texts:
I want to add some data from a postgresql table to a text file. But already some data is present in the file. I do not want to delete that data. Is it possible?
The questions asked earlier were the one in which the table name was dynamic. But in mine, it is not. All I am asking for is to append data. Also, the answer given is for Unix, not windows.


texts: Find first-time purchases with certain category

texts:
I'm trying to find the first purchase of a customer who buys a computer category table only within all time purchase to segment them using SQL. My trial is. The JOIN part worked by someone else, so I just copy them and add the WHERE condition.

select order_table.guest_email as "Email",MIN(order_table.date_purchased as "First Purchase", main_category_name as "Category"
FROM (SELECT *,
                date_trunc('day', date_purchase) AS day_date
          FROM order_order

        ) AS order_table

    JOIN order_line
      ON order_table.id = order_line.order_id
    JOIN catalogue_productcategory
      ON order_line.product_id = catalogue_productcategory.product_id
    JOIN (SELECT catalogue_category.id, catalogue_category.name, main_category.name AS main_category_name FROM catalogue_category
          JOIN catalogue_category as main_category
            ON main_category.path=substring(catalogue_category.path, 1, 4))
          AS category_table
      ON category_table.id = catalogue_productcategory.category_id
    WHERE main_category_name = 'Computer'

The Min used to find the first purchase, and Where at the bottom used to filter 'Computer' category only. However, my work is wrong, the query returns the first 'Computer' which the customer purchase. The query should skip customers who did not purchase "Computer" on their first purchase. Are there any suggestion to this?


texts: Replicate data from multiple tables with same structure to a single destination table with additional columns?

texts:
I have a table with say the following structure. This table is in multiple databases.
Name varchar(50),
Age int

I want to replicate the data from this table from multiple databases to a single table. The target table has an extra column TabCode. I want to populate this TabCode with a value depending on from where the data is coming.
I do not have the option of adding any new column to the source tables.
Name varchar(50),
Age int,
TabCode int

Can this be done in Microsoft Sql Server?


texts: Find duplicate records across one-to-many relationship tables

texts:
Let's say I have two tables that have a one-to-many relationship: People and Attributes. I'm trying to find duplicates based on the first_name, last_name, and all attributes must match exactly.

CREATE TABLE People (Id int,
                 first_name varchar(100),
                 last_name varchar(100));

CREATE TABLE Attributes (Id int,
                     person_id int,
                     field varchar(100),
                     field_value varchar(100));

INSERT INTO People VALUES (1, 'John', 'Smith');
INSERT INTO People VALUES (2, 'John', 'Smith');
INSERT INTO People VALUES (3, 'John', 'Smith');

INSERT INTO Attributes VALUES (1, 1, 'HairColor', 'Brown');
INSERT INTO Attributes VALUES (2, 1, 'EyeColor', 'Blue');
INSERT INTO Attributes VALUES (3, 2, 'HairColor', 'Brown');
INSERT INTO Attributes VALUES (4, 2, 'EyeColor', 'Blue');
INSERT INTO Attributes VALUES (5, 3, 'HairColor', 'Blonde');

Which gives us:

 id | first_name | last_name
----+------------+-----------
  1 | John       | Smith
  2 | John       | Smith
  3 | John       | Smith

 id | person_id |   field   | field_value
----+-----------+-----------+-------------
  1 |         1 | HairColor | Brown
  2 |         1 | EyeColor  | Blue
  3 |         2 | HairColor | Brown
  4 |         2 | EyeColor  | Blue
  5 |         3 | HairColor | Blonde

I'd like a query that will return IDs 1 and 2 from the People table. I can find duplicates within a single table:

select first_name,last_name,count(*) from People
    group by first_name,last_name having ( count(*) > 1 );

But I'm having trouble joining the one-to-many table and detecting duplicates across both tables. How can I detect duplicates across tables that have a one-to-many relationship?


texts: What can cause MySQL to accidentally execute the same operation twice in the same second?

texts:
For the past couple months, I've been seeing a lot of these errors: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

The proximate cause is that I have a unique composite index on columns note and created_at.

But what could the root cause be? Is there ever something in MySQL that causes a hiccup where an operation runs twice?  Or is this something that is definitely happening at the app code level?

(I'm not an expert at MySQL tuning and all the config settings available, but I do remember that certain settings have tradeoffs between performance and consistency/reliability, so I'm wondering whether there are certain settings or environmental conditions that might lead my DB to lose track of whether it has written something, and then it occasionally tries to write twice.)

I have been unable to reproduce the issue; I've never figured out how to get a write operation to happen twice within the same second intentionally.

My Ubuntu Docker server has 1GB RAM and not much traffic.

P.S. I also see a lot of these errors in the log: PDO::prepare(): MySQL server has gone away and E_WARNING: Error while sending STMT_PREPARE packet. PID=10 but not at the same time as this integrity constraint violation error.