texts: RDS Postgres chicken-and-egg problem altering roles

texts:
I just created an RDS Postgres instance, the user postgres (and it's master password), and connected via psql as user postgres.
Then I ran the script generated from the current system using pg_dumpall --schema-only, and it immediately failed on the ALTER ROLE commands:
ERROR:  must be superuser to alter superusers

Ok, but how do I grant superuser to postgres?  All of the documentation I've found "conveniently" skips that part...
EDIT: Here are the exact commands and error message:
postgres=> CREATE ROLE "READONLY";
CREATE ROLE
postgres=> ALTER ROLE "READONLY" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'mdXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
ERROR:  must be superuser to alter superusers


texts:
I'm not sure if this is a feature, or a bug, so I figured I'd ask here. I'm using MariaDB 10.1.14 on Amazon RDS, and I found that a user with an assigned role that has SELECT privileges on database(s) can't actually see those databases when they run SHOW DATABASES on the command line, or when they use a GUI client.

I tested this on a MariaDB instance on RDS, and with Docker locally.

The Docker command:

docker run \
--name mariadb-10114-test \
--publish 3306:3306 \
--env MYSQL_ROOT_PASSWORD=root \
--detach \
mariadb:10.1.14

Creating the database, table, role, user, and assigning the default role to the user as root:

mysql> DROP DATABASE IF EXISTS `testdb`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP ROLE IF EXISTS `developer`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP USER IF EXISTS 'john'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE `testdb`;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `testdb`.`t0`(`c0` INT);
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE OR REPLACE ROLE `developer`;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON `testdb`.* TO `developer`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'john'@'%' IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT 'developer' TO 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SET DEFAULT ROLE 'developer' FOR 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

And then logging in as the newly created john user in another window:

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| developer      |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> USE `testdb`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| t0               |
+------------------+
1 row in set (0.00 sec)

I can use the database, but that's only because I have explicit knowledge that I can use it. Is this normal for MariaDB roles? In MySQL / MariaDB with non-role based permissions, the user can see which databases they have access to. The fix for this is obviously to do GRANT SHOW DATABASES ON *.* TO 'developer' but this wouldn't be preferred.


texts:
I have to do the insert operation in the db as follows,

I create an item
I create 5-10 properties for that item, all these data i get in the form of a collection of objects.

I donot want to repeatedly hit the db with the following sequence,
insert statement for item,
insert statement for property1,
insert statement for property2,
...,
insert statement for property10.

Kindly note that i can use the C# code transaction for item and properties, i can even insert the item and all properties inside this transaction.

Is there any other way than using the above technique. I also cannot run Stored Procedures or create files with SQL statements as the number of properties is not known before hand.

Can i use the following kind of one,

Begin;
INSERT into item values(...........);
INSERT INTO item values (............);
GO;

Will this execute all the insert statements in a single transaction.

Suggest the best practice to use with C# code.


texts: Update Table contents from another table

texts:
I want to update the products table from my store db from a temp table.. the production table 'Products' needs to update all columns from the temp table 'tmpprod' based on StockCode and not change any other columns in the Products table

UPDATE Products
JOIN tmpprod
ON Products.StockCode = tmpprod.StockCode
    SET Products.CategoryCode = tmpprod.CategoryCode,
        Products.CategoryName = tmpprod.CategoryName,
        Products.SubcategoryID = tmpprod.SubcategoryID,
        Products.SubcategoryName = tmpprod.SubcategoryName,
        Products.BarCode = tmpprod.BarCode,
        Products.Weight = tmpprod.Weight,
        Products.ManufacturerSKU = tmpprod.ManufacturerSKU,
        Products.ProductName = tmpprod.ProductName,
        Products.WarrantyLength = tmpprod.WarrantyLength,
        Products.Manufacturer = tmpprod.Manufacturer,
        Products.DBP = tmpprod.DBP,
        Products.DBP5 = tmpprod.DBP5,
        Products.RRP = tmpprod.RRP,
        Products.StandardRRP = tmpprod.StandardRRP,
        Products.AvailabilityTotal = tmpprod.AvailabilityTotal,
        Products.AvailabilityAdelaide = tmpprod.AvailabilityAdelaide,
        Products.AvailabilityBrisbane = tmpprod.AvailabilityBrisbane,
        Products.AvailabilitySydney = tmpprod.AvailabilitySydney,
        Products.AvailabilityMelbourne = tmpprod.AvailabilityMelbourne,
        Products.AvailabilityPerth = tmpprod.AvailabilityPerth,
        Products.ImageUrl = tmpprod.ImageUrl,
        Products.ImageFilename = tmpprod.ImageFilename,
        Products.DescriptionLink = tmpprod.DescriptionLink

and...

CREATE TABLE `Products` (
    `StockCode` text,
    `CategoryCode` text,
    `CategoryName` text,
    `SubcategoryID` int(11) DEFAULT NULL,
    `SubcategoryName` text,
    `BarCode` text,
    `Weight` double DEFAULT NULL,
    `ManufacturerSKU` text,
    `ProductName` text,
    `WarrantyLength` int(11) DEFAULT NULL,
    `Manufacturer` text,
    `DBP` int(11) DEFAULT NULL,
    `DBP5` int(11) DEFAULT NULL,
    `RRP` double DEFAULT NULL,
    `StandardRRP` double DEFAULT NULL,
    `AvailabilityTotal` text,
    `AvailabilityAdelaide` text,
    `AvailabilityBrisbane` text,
    `AvailabilitySydney` text,
    `AvailabilityMelbourne` text,
    `AvailabilityPerth` text,
    `ImageUrl` text,
    `WebsiteUrl` text,
    `ImageFilename` text,
    `DescriptionLink` text,
    `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
    `ForSale` int(11) NOT NULL DEFAULT \'0\'
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

and...

'CREATE TABLE `tmpprod` (
    `StockCode` text,
    `CategoryCode` text,
    `CategoryName` text,
    `SubcategoryID` int(11) DEFAULT NULL,
    `SubcategoryName` text,
    `BarCode` text,
    `Weight` double DEFAULT NULL,
    `ManufacturerSKU` text,
    `ProductName` text,
    `WarrantyLength` int(11) DEFAULT NULL,
    `Manufacturer` text,
    `DBP` int(11) DEFAULT NULL,
    `DBP5` int(11) DEFAULT NULL,
    `RRP` double DEFAULT NULL,
    `StandardRRP` double DEFAULT NULL,
    `AvailabilityTotal` text,
    `AvailabilityAdelaide` text,
    `AvailabilityBrisbane` text,
    `AvailabilitySydney` text,
    `AvailabilityMelbourne` text,
    `AvailabilityPerth` text,
    `ImageUrl` text,
    `WebsiteUrl` text,
    `ImageFilename` text,
    `DescriptionLink` text,
    `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1'


texts: MySQL database configuration for fastening the select and insert queries

texts: There are several DBEngines are available for MySQL DB. Currently I'm using InnoDB. My actual problem is, I have a table that consist of millions of data. Therefore the select queries that given to this table is extremely slow. I need to know which configuration should I use to make the select query faster, that doesn't affects the speed of insertion.


texts: How to package a PostgreSQL extension as a binary .deb file in Ubuntu?

texts:
What is the minimal-effort way to package a PostgreSQL extension into a .deb file for Ubuntu (as of 18.04)?

I did some search but find many different suggestions (some outdated).

Suppose I want to package a simple postgres extension such as the first and last aggregate functions.

This StackOverflow answer suggests that one should fist make a directory structure manually as follows:

ProgramName-Version/
ProgramName-Version/DEBIAN
ProgramName-Version/DEBIAN/control
ProgramName-Version/usr/
ProgramName-Version/usr/bin/
ProgramName-Version/usr/bin/your_script

and then dpkg -b /path/to/the/ProgramName-Version after setting the right file permissions.

Others suggest using dh-make, and the Ubuntu Packaging Guid suggests a work flow that uses some toolchain based on bzr.

But I think PostgreSQL has its own build infrastructure for extensions called PGXS (as of PostgreSQL 12.x). I found PGXS quite easy to use, because I don't have to worry the folder structure myself and make install will copy files to the right system directories regardless of the PostgreSQL version.

Is there a way to combine the manual approach above and the PGXS approach to automatically create a PostgreSQL extension as a .deb file?


texts: Optimise MySQL SELECT with LEFT JOIN subquery

texts:
I have a query which combines a LEFT JOIN and subquery. The dataset is quite big and the time to execute the statement is over 70 seconds.

SELECT
    s.siblings,
    l.id
FROM
    `list` l
        INNER JOIN
    child c ON c.id = l.child_id
        INNER JOIN
    parent p ON p.id = c.parent_id
    LEFT JOIN (
      SELECT COUNT(c.id) AS siblings, c.id, c.parent_id
      FROM child c
      GROUP BY c.id
    ) AS s ON s.parent_id = c.parent_id AND s.id != c.id
WHERE
    l.country = 1
GROUP BY l.id, s.siblings
ORDER BY l.dateadded

This query should return all lists for a country. Each list is specific to a unique baby. For each list I would like to return a count of the number of children that have the same parent.

If I remove the LEFT JOIN subquery the fetch time is 0.1 seconds. Is there a way to make the query more efficient?


texts: How do we handle timestamp formatting errors on large data imports?

texts:
We have a large database with millions of rows of data from ship AIS pings.

We were recently formatting and importing the data from our holder, ping_raw relation into a ping relation.

During our last go at this our query was stopped: ERROR: invalid value "2.01" for "YYYY", SQL state: 22007. We are trying to import a text string (ping_raw.datetime_pos) into a timestamp with timezone field in ping.dt_pos using the to_datetime() function (in PostgreSQL 9.3).

Last thing - we searched for any values SELECT * FROM ping_raw WHERE datetime_pos LIKE '%2.0%', which returned 0 rows. It seems like all the text values in ping_raw are formatted YYYY-MM-DD HH24:MI:SS.

Why is this error coming up? How can we successfully convert text value ping_raw.datetime_pos into a timestamp with timezone datatype in ping.dt_pos?


texts: SQL Management Studio - Add-on / Extension to view Column Data Type

texts:
Is there any way (built in) or free/paid add-on/Extension for SQL Management Studio that allows us to know exactly a data type of a column when we are writing a query?

In this specific project I have had situations where by looking a the query result I am not exactly sure of the datatype, if it is a number written in a nvarchar(max) column.... or if it is a tinyint, or int, or bigint, etc....

And I am spending so much time just to drill on the "tree list" to reach a speficic table and expand the Columns node to find out what am I dealing with....

A situation when you have bunch of JOINS (and allias) and sp_help <tablename> would require me always to go to some other query window and type more sql (table names)....  then it would be easier and faster just to have like a tooltip message of the column with extra info.


texts: Leveraging an index on another column with the same ordering guarantee

texts:
I have a massive table that is only indexed by the auto-increment column, which is also populated with the current timestamp (which is not indexed).

CREATE TABLE MyTable
(
    ID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY
    ,AuditTimestampUtc DATETIME NOT NULL DEFAULT(GETUTCDATE())
    ,...
)

If I need to query by the creation timestamp of the row, how can I do it efficiently? Adding an index is not feasible because of how gargantuan the table is (hundreds of millions to billions of rows), as we cannot afford the downtime, and I am performing a rare debugging task in a readonly environment, which is essentially

SELECT [...] FROM MyTable WHERE AuditTimestampUtc BETWEEN @Start AND @End

I am trying to debug a novel issue, and have not had to do this task before, so I would have difficulty making the argument for creating a new index. And unfortunately there is quite a process for processing a request to create and sanitize a full database dump (especially given its size), or cloning it to another environment. I have an outdated dump to experiment with, but running the final query will be supervised through a read-only account on production.

Writing a custom binary search seems like overkill, especially in a RDBMS, but alas computers are not mind-readers, even though it is apparent to a person that the identity column can be used as a surrogate ordering* to efficiently search the table by creation time.

*Assuming nobody enables IDENTITY_INSERT to violate this temporal ordering guarantee.

P.S. I do not believe the database platform is very relevant to this question for the first time ever (ignoring the specific syntactical differences of declaring the index/default constraint/etc) but I am using SQL Server.


texts: Match exact word/string in begining, in between & end in string

texts:
Here I need to match exact word but I am getting all results I used '%' + columnname + '%' in wildcard.

I am looking for exact word match doesn't matter it is beginning, in between and end of string.If i try to match 'America', I should be able to get 1st row as output.


texts: Perfmon Resource Utilisation for PROD SQL Server

texts:
I want to create a new Data collector set to find out my Memory utilization in my PROD SQL Server for at least 2-3 weeks.

Can anyone let me know if this takes too  much resource utilization which in turn reduces Performance.

Below are the performance counters I am planning to create as adviced :

Memory – Available M Bytes

SQL Server: Buffer Manager – Page Life Expectancy (Higher is good)

SQL Server: Memory Manager – Memory Grants Pending (Lower is good)

SQL Server: Buffer Manager, Buffer Cache Hit Ratio (higher is better)

SQL Server: Memory Manager – Target Server Memory

SQL Server: Memory Manager – Total Server Memory

SQL Server: SQL Statistics – Batch Requests/sec

SQL Server: SQL Statistics – Compilations/sec


texts: Postgres 9.4 wal_level=logical increases disk space

texts:
I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance.

Database Migration Service requires the wal_level to be set as logical in the source Postgres DB. But when done, Postgres started consuming a lot of disk space to the point where the instance became unusable.

How long are the WAL logs retained? Are there other settings that can be enabled so disk space use is limited?


texts:
I have a database currently sitting on ~6B live tuples, ~4B dead tuples.

I've ran the VACUUM command for 3 days now, and then suddenly my internet died. Meaning i lost all progress. How can i speed up the VACUUM speed? It's currently running on AWS RDS.

Is there a way to run command in background, e.g. disconnect/turn off PC?

Btw running on 9.3


texts: SELECT INTO OUTFILE vs INSERT INTO ... SELECT

texts:
During my cursory research I have not been able to find a definitive answer on the benefits that SELECT INTO OUTFILE provides over INSERT INTO ... SELECT. Upon reading the docs related to INSERT INTO ... SELECT concerning locks on InnoDB tables it states:

  sets an exclusive index record without a gap lock on each row inserted into T. If the transaction isolation level is READ COMMITTED or innodb_locks_unsafe_for_binlog is enabled, and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S.

To avoid the lock with INSERT INTO ... SELECT it seems that I must ensure the isolation level is READ COMMITTED to avoid locks on the source table during the query.

However, I have not been able to find any authoritative answer regarding locks and using SELECT INTO OUTFILE, not even the MySQL docs reference lock info.

My goal is to avoid locking the source table while the query runs to avoid connection stacking.


texts:
First I want to know how to estimate the database size regarding the biggest table it will contains. I've the following :

+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| users_id | int(32) unsigned | NO   | MUL | NULL    |       |
| s        | binary(16)       | NO   | PRI | NULL    |       |
| t        | binary(16)       | NO   | PRI | NULL    |       |
| x        | binary(16)       | NO   | PRI | NULL    |       |
+----------+------------------+------+-----+---------+-------+

This is the table that will be significant for the size (the other table is only to maintain user data (id, user, pass, email), I don't expect them to have more than 100 entries).

I expect around 7.61263 * 10^9 entries into the table above. I made this simple calculations : 7.61263 * 10^9 + (4 Bytes + 16 Bytes + 16 Bytes + 16 Bytes) ~= 395 GB. But I don't know how to take into account the overhead coming from the dbms (indexes, database structure ...) ?

How to estimate the database size ?

What about the speed and the stability of MySQL running with this large table. Do I have to split the data over two or more databases in order to reduce the size ?


texts: Grouping data checking against top values


texts: Duplicate fails with no copy of datafile nnn found to restore

texts:
I've duplicated our production to our UAT environment several times in the past but due to various reason, this time has been a serious PITA.
Oracle 11.2.0.3.
We have a recovery catalog setup.  This is the script thats run on the UAT box:

connect catalog rman/cat@rc
connect auxiliary /
run {
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;

duplicate database RACDB01 to GVPUAT skip tablespace
TS_ADMIN2,TS_ERROR_ERRORLOG,...
<big list of tablespaces I don't care about>...,RADSS_WG_INDEX_LARGE
NOFILENAMECHECK until time "to_date('2016-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS')";

The problem is it errors with RMAN-06023: no backup or copy of datafile 352 found to restore.  This is because on 03/01/2016 I added a datafile to production.  A backup was started on production at 12pm, I added a datafile at 6 pm, and the backup finished at roughly 11pm.  How can I use that backup?


texts:
I have been a DBA for about 2 years now, and there are some subtleties that I still do not understand about AlwaysOn availability groups.  First off, they have been nothing but trouble as far as I can tell, mainly because of a situation that occurs frequently in our environment.

We do Windows patches once per month, with the expected server reboots.  Roughly once per month, we encounter a situation where clusters with multiple availability groups divide themselves up between the nodes of a cluster.

If I am scheduling backup jobs, I usually administrate the jobs via multi-server administration from a master server.  I do this at the resolution of the listener, since it connects to the primary node of an availability group.  The problem is, if I specify "all databases," the entire node attempts to backup, no matter the primary/secondary status of a given availability group.

As a result, a lot of noise is generated in our monitoring solutions, as any cluster with diffuse availability groups returns a failed status for the backup, as the failure occurs on the attempt to take a normal backup (not copy-only) on a secondary AG.

Do I have to script jobs to run by availability group in these cases?

Is there a combination of settings that only backup an availability group which is primary for that node?

I have made the argument for consolidating the groups so that we can administrate by SERVER instead of by AG.  My bosses argue that we should be using AG's for load balancing across clusters so that we are not paying a SQL license for a node we only use for HADR.  I have put forth everything that can go wrong with these diffuse AG's, but maybe there is something I am not aware of.

For clarity, I get that we can point listeners to the different AG's and that they will connect to the primary of its respective node.  I just cannot seem to be able to manage any sort of generic backup plan that doesn't produce monitoring and write capability conflicts against these split AG situations.  Any clarity that anyone can offer would be greatly appreciated.


texts: If I actively BEGIN a transaction, but fail to ROLLBACK at error, will PG become confused?

texts:
Suppose I have this application pseudo code (it's actually PHP and communicating with PG with the pg_* functions):
function handle_update_of_foo_table()
{
    BEGIN;
    TRUNCATE TABLE foo;

    loop (thousands of times)
    {
        INSERT INTO foo blablabla...;

        if (INSERT FAILED)
        {
            ROLLBACK;
            return false;
        }
    }

    COMMIT;
    return true;
}

handle_update_of_foo_table();
UPDATE unrelated_table SET blablabla;

Do I understand things right if I say that, had I not actively remembered to do the "ROLLBACK" before returning, when an INSERT failed for whatever reason, the transaction would have "continued on" to perform the unrelated UPDATE query inside the transaction, and thus, when the script ends right afterwards, it would have auto-rolled back because there had been a BEGIN but no COMMIT, so as far as PG is aware, all of my queries, including the unrelated UPDATE in the end, are part of the same transaction? And thus, the unrelated_table will not be updated in the end, because the entire transaction was auto-rolled back due to not COMMIT?
With me forgetting the ROLLBACK (which I did not forget in the example code, but might very easily do in reality somewhere), it has no way to know what my intentions were since it just sees a series of queries inside an explicitly started "transaction"?
If I have understood this, I think I finally "get" transactions. It only took me twenty years...


texts:
I have been shown a great way to bulkinsert with this example:

WITH p AS (
    INSERT INTO parent_table (column_1)
    SELECT $1
    RETURNING id)
INSERT INTO child_table (parent_table_id, column_a)
SELECT p.id, a
FROM   p, unnest($2::text[]) AS a

However, I need to insert multiple rows from multiple arrays, so I tried this syntax:

WITH p AS (
    INSERT INTO parent_table (column_1)
    SELECT $1
    RETURNING id)
INSERT INTO child_table (parent_table_id, column_a, column_b)
SELECT p.id, a, b
FROM   p, unnest($2::text[]) AS a, unnest($3::bigint[]) AS b

I have a primary key on parent_table_id and column_a, and when I try to execute this query, Postgres complains of a duplicate key violation.

How should the arrays be unwrapped so that they form individual rows?

In other words, if $2 and $3 both have two entries, how can the first entry of $2 only be inserted with the first entry of $3 and the same for the respective second entries?

If this is not possible, can I construct a multidimensional array?  If so, how should it be passed with multiple array types, and what is the multidimensional array syntax?


texts:
We're trying to define a backup policy.
We were going to apply following:

Weekly full backup, retained for 30 days.
Nightly differential backups, retained for 7 days

But we're also exploring other option - Transaction Log backups, and Azure backup(which is offering a default of 2 hours)

Am I right in saying that that the differential backup would get us to the last differential backup, and then we can use log backups to get closer to where we want to recover from?

So, backups will be as below:

Sunday 1am- Full backup.
Monday 1am - differential
Tuesday 1am - differential
Wednesday 1am - differential

Every 2 hours since Wednesday Inc - Log Backups.

Lets say Disaster hits at Wednesday mid day!

Recover the differential , which gets us to Wednesday at 1am...
Then we use Log backups to get us to before the disaster struck.

Is that the correct distinction between the types?


texts: MariaDB: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

texts:
This question has already been asked before, but none of the answers suggested were applicable to my situation.

Issue:

I am prevented from inserting data into a column because of a foreign key violation. However, the foreign key is a valid reference.

Failing query:

INSERT INTO `insert-table`(`not-important`, `foobar-id`) VALUES ('whatever', 'This exists in both tables');

Error:

  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (mydb.insert-table, CONSTRAINT FK_foobar FOREIGN KEY (foobar-id) REFERENCES foobar-table (foobar-id))

SELECT COUNT(1) FROM `foobar-table` WHERE `foobar-id` = 'This exists in both tables';
1

Schema:

mysql> SHOW CREATE TABLE `insert-table`;
CREATE TABLE `insert-table` (
  `foobar-id` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  `not-important` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`foobar-id`,`not-important`),
  CONSTRAINT `FK_foobar` FOREIGN KEY (`foobar-id`) REFERENCES `foobar-table` (`foobar-id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

mysql> SHOW CREATE TABLE `foobar-table`;
CREATE TABLE `foobar-table` (
  `foobar-id` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`foobar-id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Troubleshooting:

the data is there (no typo, see the error section)
both columns have the same type declaration (see above)
the engine used is already InnoDB (some websites recommend to switch to it)
I tried to remove the column names from the statement in case a bug was making MySQL ignore that (since they're both of the same type)
SELECT VERSION(); 5.6.10
SHOW VARIABLES LIKE 'innodb_version'; 1.2.10
Run (with the PROCESS privilege):

SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-01-10 15:24:39 2b1f59283700 Transaction:
TRANSACTION 17469656069, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s)
MySQL thread id 147376, OS thread handle 0x2b1f59283700, query id 152047035 123.45.67.89 theDbUser update
INSERT INTO `insert-table` VALUES (‘This exists in the other table’, ‘whatever’)
Foreign key constraint fails for table `mydb`.`insert-table`:
,
 CONSTRAINT `FK_foobar` FOREIGN KEY (`foobar-id`) REFERENCES `foobar-table` (`foobar-id`)
Trying to add to index `PRIMARY` tuple:
DATA TUPLE: 4 fields;
0: len=22; bufptr=0x2b2ea459a812; hex= 546869732065786973747320696e20626f7468207461626c6573; asc This exists in both tables;;
1: len=25; bufptr=0x2b2ea459ab0c; hex= 7768617465766572; asc whatever;;
2: len=6; bufptr=0x2b3a97464610; hex= 00041145cc05; asc    E  ;;
3: len=7; bufptr=0x2b3a97464618; hex= 00000000000000; asc        ;;

But the parent table `mydb`.`foobar-table`
or its .ibd file does not currently exist!

Related:

StackOverflow: MySQL 5.5 foreign key constraint fails when foreign key exists : links to bug below
https://bugs.mysql.com/bug.php?id=60309 fixed in 5.5 (2011), should not apply to MySQL 5.6; only for OSX
Foreign Key Constraint fails : suggests to temporarily disable foreign key checks. Not sustainable in my case.
"Cannot add or update a child row" when deleting a record? : no accepted answer
SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails : same


texts: Does having many databases affect MySQL performance?

texts:
I have huge unused databases in MySQL. Do they affect my MySQL engine performance even if they are unused? Do they consume memory on their own?

Thanks


texts: Is my Publisher database my actual source database or is it a new database?

texts:
I am setting up transactional replication for the first time and am working through my backup policies I need to set.

Is my Publisher database my actual source database?  Or is it a new database that gets created when I create the new publication?

I know our production database is currently backed up regularly, Im not sure if I also need to back up the 'Publication` as well once it is created?


texts: Conversion of simple ERD to Table: is the solution below any good?

texts:
Consider the following ERD:

Suppose that all entities only have two attributes: a PK plus a name/number.

I want to convert that ERD to tables.

Is it needed 4 tables (1 for each relationship plus the phone table)? Is there a better solution?

Here is my solution (4 tables, one for each relationship plus the phone table):

Relationship 1 (R1)
-----------------------------------------
ID_PhoneNumber | ID_Company | CompanyName
-----------------------------------------

Relationship 2 (R2)
-------------------------------------------
ID_PhoneNumber | ID_Employee | EmployeeName
-------------------------------------------

Relationship 3 (R3)
-------------------------------------------
ID_PhoneNumber | ID_Supplier | SupplierName
-------------------------------------------

Telephone
----------------------------
ID_PhoneNumber | PhoneNumber
----------------------------

I assumed that is not needed to add three more tables, one for each entity (Company, Employee, Supplier) since those entities are already represented by the tables of each relationship (R1, R2, R3).

I'm not sure if that decision is correct or if I'm making good use of the fundamental concepts (maybe I'm lacking some).


texts: MongoDB numerous slow query logs with zero keysExamined

texts:
using a MongoDB version 3.6 Replica Set, I am wondering why a query that resulted in no key examination and document examination would result in being slow. For example, I have numerous slow query logs similar to the following (on the primary):
I COMMAND  [conn187790] command dbname.colname command: find { find: "colname", filter: { personId: 1234, createdAt: { $gt: new Date(1610444300000) } }, projection: {}, returnKey: false, showRecordId: false, lsid: { id: UUID("049d5c65-9483-479e-9c84-57e778059560") }, $clusterTime: { clusterTime: Timestamp(1610455000, 78), signature: { hash: BinData(0, 2358064BDB560A37AA2860A3ED9E5B267677A386), keyId: 6880748521151004683 } }, $db: "dbname" } planSummary: IXSCAN { personId: "hashed" } keysExamined:0 docsExamined:0 cursorExhausted:1 numYields:0 nreturned:0 reslen:242 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_msg 325ms

As you can see, my query involves an index that returns nothing, as can be seen in keysExamined:0 docsExamined:0 part. However, it took 325 milliseconds to run.
What factors can result in such behavior? The output of iostat -xm sda is like below:
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          15.74    0.00    6.87    1.25    0.00   76.14

Device            r/s     w/s     rMB/s     wMB/s   rrqm/s   wrqm/s  %rrqm  %wrqm r_await w_await aqu-sz rareq-sz wareq-sz  svctm  %util
sda              2.00 1685.00      0.02     20.30     0.00   162.00   0.00   8.77    2.00    2.81   4.78     8.00    12.34   0.18  29.60

It seems that my machine is not under disk pressure. Also the page fault rate is near to zero and I think is negligible.


texts: Creative workaround for granting edit rights to individual jobs for a group?

texts:
We have two developers that say they can not function without full rights to a few jobs on our database server. They both want rights to the same jobs.

Given that it seems that MS doesn't seem to be budging on granting rights to windows groups, I was wondering if anyone had a creative workaround? I can't let them have the system wide agent roles. http://connect.microsoft.com/SQLServer/feedback/details/508137/allow-windows-groups-to-own-sql-agent-jobs

I'm about to create a user and tell these two to log in to a shared account if they want to run/edit their jobs - but then there is no accountability.

Anyone have a creative solution I have missed?

Thanks :)


texts: Zapier & MS SQL - Use azure DB to increase security?

texts:
I'm interested in using Zapier to automate some processes that should be fired upon certain records appearing as the result of a custom query. Zapier seems to be a good tool as it can do things based upon said query. In this case i need to fire off some data to a webhook, and then update the original record.

I could build this out myself, but why re-invent the wheel?

What am i concerned about is their security. Their documents require you to open up port 1433, and whitelist their IP address, then supply a username and password to them that they use to connect.

As long as our security is configured properly:

Firewall: NAT policy only transforms if the IP matches
Firewall: rules only allow requests from zaps IP to communicate
Using a secure password
Restricting the account to only the tables/data needed

I'm worried about this security, my solution was setting up a simple Azure DB, and using Azure DB Sync to replicate the tables from the onpremise database to the azure cloud DB, and then have zapier connect to that. Azure Sync seems to be a bit more secure in the way it transports data.

Am i just complicating my life without actually increasing security? Is there a better way to do this?


texts: Why does pt-table-checksum gives exit status 0 if slave is unreachable?

texts:
I'm using pt-table-checksum version 2.1.4 for checking my master-slave set up. In the case when I forget to set up the right privileges on the slave, it writes an error to stderr like this:

# pt-table-checksum -d employees
Cannot connect to h=node03.mydomain.local                                 <== error
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
10-04T15:33:33      0      0        9       1       0   0.014 employees.departments
[...]

However, the exit status of it is 0.

# echo $?
0

The number differences it will find is always zero, as it has nothing to compare against. And yes, documentation says to exit with status 0 if no differences or errors have been reported. But this does not make sense in most cases.

Is there a way to make pt-table-checksum not failing silently and reporting this as a general failure? Should I file a bug or is this configurable?

It proves to be confusing to more users, for example in this question: "pt-table-checksum help required".


texts: Performing SELECT or INSERT if does not exist in a single SQL statement without using WITH

texts:
I am wondering if there is a way to perform SELECT or INSERT if the record does not exist in a single SQL statement without using WITH clauses (i.e. CTEs)?

It would be cleaner to write such SQL in a single statement then use it in a long and complex query.

Thank you!


texts: Remove characters from recordset permanently

texts:
I have some data in this format:

52-681-Functional-EHT
53-682-Functional-EGT
52-683-Functional-EZT
51-684-Functional-EYT
59-685-Functional-E5T

And I want to remove the interior '-Functional' permanently with a sql statement so that my records would now look like...

52-681-EHT
53-682-EGT
52-683-EZT
51-684-EYT
59-685-E5T

All my research leads me to Replace but that seems to just be on a select and can't quite figure out how to form it into an appropriate update statement. Again, I want to remove the string FOREVER.

Creation script:

create table part (
id int identity(1,1) PRIMARY KEY,
Partname nvarchar(50))

insert into Part (Partname)
values ('52-681-Functional-EHT'),
('53-682-Functional-EGT'),
('52-683-Functional-EZT'),
('51-684-Functional-EYT'),
('59-685-Functional-E5T')


texts: is there any reason to explicitly grant the server permission CONNECT SQL?

texts:
I have been using this script from Kenneth Fisher to get the server permissions.

-- Server Permissions
SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName,
   Grantor.name AS GrantorName, Permission.class_desc, Permission.permission_name,
   Permission.state_desc,
   'REVOKE ' +
       CASE WHEN Permission.class_desc = 'ENDPOINT' THEN NULL
       WHEN Permission.[state]  = 'W' THEN 'GRANT OPTION FOR ' ELSE '' END +
       ' ' + Permission.permission_name COLLATE Latin1_General_CI_AS +
       ' FROM ' + QUOTENAME(Grantee.name COLLATE Latin1_General_CI_AS)  + '; ' AS RevokeScript,
   CASE WHEN Permission.class_desc = 'ENDPOINT' THEN NULL
       WHEN Permission.[state]  = 'W' THEN 'GRANT' ELSE Permission.state_desc COLLATE Latin1_General_CI_AS END +
       ' ' + Permission.permission_name COLLATE Latin1_General_CI_AS +
       ' TO ' + QUOTENAME(Grantee.name COLLATE Latin1_General_CI_AS)  + ' ' +
       CASE WHEN Permission.[state]  = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END +
       ' AS '+ QUOTENAME(Grantor.name COLLATE Latin1_General_CI_AS) + ';' AS GrantScript
FROM sys.server_permissions Permission
JOIN sys.server_principals Grantee
   ON Permission.grantee_principal_id = Grantee.principal_id
JOIN sys.server_principals Grantor
   ON Permission.grantor_principal_id = Grantor.principal_id
WHERE 1=1
  --AND Grantee.name LIKE '%TAllard%'

In my live systems I get for this user in particular the following permissions (server):

the VIEW ANY DEFINITION permission I explicitly granted but the other permission CONNECT SQL was not explicitly granted. I believe it is automatically generated when you create a login to the server, and a user for that login on any database that live there.
that implies a connection.

considering this, is there any specific reason why I should grant the CONNECT SQL at servel level?


texts: Is it possible to create an alias or synonym for a database?

texts:
Is it possible to create a database alias or synonym?  What I am hoping to be able to do is have NewDatabaseName aliased as OldDatabaseName to prevent having to rewrite a ton of code.

Backstory:
I was approached by a client with a ridiculous self-induced problem.  They have a third-party application that has a SQL server back-end.  The users have been writing custom reports against this database using Visual Basic, with all database connection information hard-coded in their programs.

Recently the third party software renamed their database.  Now all the report EXE's are failing.

Oh, they dont' have a lot of the source code either.

I held back my laughter and said I would see what I could do.


texts:
Here is my code:
create table failed_login_attempts(
login_id number(5) primary key,
date_created timestamp default systimestamp,
email varchar2(110),
ip varchar2(24),
mac varchar2(18),
attempt number(1),
isLocked number(1)
);

I want to count attempt column and if 5 then isLocked become 1 from 0;
How can I make query, view or procedure?


texts: Architecture to scale Postgres read capacity for a limited time each day?

texts:
We run a postgreSQL (9.4) server to store data for a web application. Our next feature will require a very data intensive processing on the server requiring a lot of db queries and computation over these data.

As we update our database daily, the web requests are valid across all users and throughout the day. So we obviously want to cache results. That works well for us (results stored in Redis) except that everyday, we have a cold start problem, and each possible request can take multiple seconds to complete the first time it is requested by a user.

The possible web requests are actually predictable and today we can list circa 30000 ones, but as the feature evolves, we could easily reach 100000 or 500000.

So we think about warming the cache up, processing every request and caching the result, so our users wouldn't experience delays. The processing code runs in nodeJS in Docker. We would be able to run multiple of these containers (on another machine) to parallelize the computation and expect it to divide the total caching time by the number of containers that would be able to run at full speed.

A quick test shows that the postgres server becomes the bottleneck in the process: half (and sometimes more) of nodejs execution time is spent retrieving data, and during this time the postgres process runs at ~ 100% cpu. To be able to divide by the number of containers, we need postgres to answer each one of them simultaneously at full speed. I read that postgres dedicate a single core to each session, so my guess is: if we provide postgres we the same number of core than the number of container that it has to answer to, then we would be back in line with our original expectations (linear reduction of computing time).

So first question: Is this reasoning OK in theory? (Any alternative suggestion ?)

Let's assume it is.

Our production server currently runs on 4 vcores. Our provider can start instances up to 32 vCore on-demand.
It is not reasonable to think that our production setup will run 24/7 a 32-core machine, to handle a job that might not exceed 1 or 2 hours a days, but won't be using more than 1 or 2 cores the rest of the day.

A solution that we think of is to spin a 32-vcore machine at a given time of the day, with a postgres server on it, wich would synchronize with our main 4-vcore postgres. Up on sync completion, we could run 16 containers that would work against a 16-vcore postgres instance. When all caching is done, we would suspend our 32-vcore machine and get away with an advantageous hourly billing.
That also seems to scale linearly, as if we start multiple 32-vcore instances, each can sync their own postgres server and spin up their own containers.

Second question: Is this 2nd reasoning OK in theory? (Any alternative suggestion ?)

Let's assume it is.

How would we accomplish sync between 32-vcore postgres and main 4-vcore postgres ?
A naive way would be to do a single pg_dump on the 4-vcore postgres, and download that file on each 32-vcore machine to run them against the local postgres. Seems simple, maybe time consuming.
Another way would be to setup 32-vcore postgres servers as hot stanby servers, and to expect them to catch up only with missing data when we restart their host. But we are not yet familiar with that technology and its capabilities, so any advices would be kind.

Any help/idea appreciated.


texts: Master keyword table connecting to multiple table

texts: We have one keyword table and different tables connecting to the keyword table as the interface table (e.g question_keyword, answer_keyword). We would like to create one master keyword table so when the user search for a word it can find the word is in question table or answer table and retrieve information from that table. We thought of having one master_table(id, question_id, answer_id, keyword_id). Is this design correct? and if we have more tables (means more columns in master_table), how would be the performance (since it might make the master_table as sparse matrix)?


texts: High amount of disk writes during upsert with no changes on table

texts:
We are running some nightly data sync jobs which move data from an external database into postgres using an upsert statement. The data is updated in batches and using parallel workers (10 currently).
The problem we are having is that even when no data is changed (e.g. when running two consecutive syncs with no changes in between), there is a high amount of disk io (100% disk usage, 200 MB/s is writes). Our monitoring reports that there are no updated rows and only reads are performed on the table itself.
The expected behavior would be a high amount of reads but close to zero write operations on the disk. We can reproduce this consistently, even when running two syncs on a previously empty table.
What can be the reason for this high amount of disk write operations?

Some more details on our setup:

Postgres 12.5 running on Kubernetes with 25 GB of memory
1TB disk with 3000 IOPS gp3 volumes (AWS)
Table has around 570 millions rows (120 GB)
Primary key index is 43 GB

Upsert-Statements used:
INSERT INTO ... (...)
VALUES (...)
ON CONFLICT (primayKeys...)
DO UPDATE ...
WHERE (tableColumns...) IS DISTINCT FROM (excludedColumns...)


texts: How to add index to increase speed of mysql statement

texts:
How do I increase the speed of this mysql statement? taking 1.10s but was wondering if it could be made faster.

SELECT
        res_rooms.room_id AS room,
        res_rooms.type AS room_type,
        res_rooms.status AS room_status,
        IFNULL(utilizations.user_id, - 1) AS user_id,
        CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
        CONCAT_WS(0, system_users.name, camps.s_first) AS name,
        IFNULL(utilizations.start_date, NULL) AS start_date,
        IFNULL(utilizations.end_date, NULL) AS end_date,
        utilizations.keys_issued AS keys_issued,
        utilizations.resource_type AS resource_type,
        utilizations.staff_id AS staff_id,
        CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
        CONCAT_WS(0,
                students.date_of_birth,
                camps.date_of_birth) AS dob,
        CONCAT_WS(0, students.gender, camps.sex) AS gender,
        CONCAT_WS(0, students.status, camps.status) AS status,
        countries.common_name AS citizenship,
        CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
        IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
        IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
    FROM
        res_rooms
            LEFT JOIN
        utilizations ON utilizations.resource_id = res_rooms.room_id
            LEFT JOIN
        system_users ON system_users.user_id = utilizations.user_id
            LEFT JOIN
        camps ON camps.camp_id = utilizations.user_id
            LEFT JOIN
        students ON students.user_id = utilizations.user_id
            LEFT JOIN
        countries ON (students.citizenship = countries.code
            || camps.citizenship = countries.code)
            LEFT JOIN
        fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
    WHERE
        (students.status = 'B'
            OR students.status = 'VC')
            AND utilizations.start_date <= CURDATE()
    ORDER BY room_id , start_date DESC

When using Explain:

Stats of the query:


texts: How to skip table data for replication in MySQL?

texts:
I would like to create a table on the master, whose data are not replicated to the slave.

I'm temporarily inserting many millions of rows in this table a part of a batch job; the data in this table can be re-created on demand, hence do not need to be replicated on the (slower) slave.

I don't want to use TEMPORARY tables, as I ideally want to keep the table structure in the database (on the slave as well, so that I can reliably use it for backup recovery), and above all I want to be able to recover from the current state if the job exits for any reason, so the table data need to persist on the master if the job dies.

Ideally, I would like to be able to control the replicability of the table from the CREATE statement, to be able to control this from my normal SQL workflow.

Is this possible? If not, close alternatives are welcome.


texts:
Can you recommend a good self service solution for creating SQL Databases? Our dev and test teams need to frequently create new databases. I would like to keep these databases as close to production configuration as possible but I would like them to be able to create this on their own without me being in the picture? We are a VMWare shop, so I prefer solutions that work with VMWare.

[Edit - Clarifying the question. Need to install a SQL server from scratch. Create a new VM, Install SQL and create a new DB]

Thanks,
Joe


texts: How to terminate all DB usage of a PostgreSQL user

texts:
How to terminate all DB activities of a particular PostgreSQL user?

I know how to select them: SELECT * FROM pg_stat_activity WHERE usename='foo_user'?

And I found this here: https://stackoverflow.com/a/35319598/633961

  What I did is first check what are the running processes by

SELECT * FROM pg_stat_activity WHERE state = 'active';

  Find the process you want to kill, then type:

SELECT pg_cancel_backend(<pid of the process>)

  If the process cannot be killed, try:

SELECT pg_terminate_backend(<pid of the process>)

But how to apply this to several processes?

Use Case

Sometimes a test in CI takes too long and the system should be destroyed.


texts:
I have 4 tables users, posts, categories, categories_map

posts has id, text, category_id
categories_map contains user_id and category_id

My goal is to make an queue that user can preview. Also, user will be able to skip some posts or edit text in them. If user skipped post it will never appear in queue. However, user is not able to change sequence because cron will be executing script.

First approach I think is to create a table that will contain
user_id, post_id, text_modified, is_skipped, last_posted. So when cron job will be executed it will leave a timestamp and next time this post won't be grabbed and user easily can change text for this post.

Second appoach to create separate table where queue will be generated for user user_id, post_id, category_id, text_modified. And cron job can easily job follow this table and remove row after it was done. But with this approach if I will have 30 users with average 3 categories that contains 5000 posts each my table will have 450000 rows already. Yes if it will be indexed properly it should be all good. But will it be scalable after I will have 100-200 users?

Which approach should I go or is there any other solution?


texts: Why does the SQL Server build number sequence not correlate with the release date sequence?

texts:
I'm looking at the Microsoft SQL Server 2016 Builds (see that section heading). The list of builds is given in build number order. That is, at the top of the list is the "highest" build number, and each subsequent row has lower build number than the one before.
However the release dates don't correlate with this. So here are 5 rows, for example:

Build 13.0.5081.1 - released 14/08/2018
Build 13.0.5101.9 - released 9/07/2019
Build 13.0.5102.14 - released 11/02/2020
Build 13.0.5149.0 - released 30/05/2018
Build 13.0.5153.0 - released 17/07/2018

The general sequence for builds around this time comes from 2018 - and then we have those 2 odd rows, from 2019 and 2020 and then back to the general sequence from 2018.
If a cumulative update is supposed to contain all prior cumulative updates, then how could that last build (in the 5 above) have contained the updates from the two builds from 2019 and 2020? (Oops - only just noticed the first line is from Aug, which is after the 4th and 5th lines too!)
If I had installed the 4th and 5th builds at the time of their releases (in May 2018 then July 2018), and then that 1st build gets released in Aug 2018 - would I automatically have had those changes incorporated already? If so, what was the point of issuing an older build number?
Another example
Here is another example that just makes no sense to me.

First, the RTM is dated June 2016. Then the first cumulative update - CU1 - is dated July 2017, which is 10 months after CU2 was released in Sep 2016. Huh?
And read the text at the bottom - "After certain Cumulative Updates, Microsoft released a Service Pack for SQL Server, which contains the entire previous fixes, Cumulative Updates, and hotfixes". Huh again? Note SP1 was released November 2016 and a full 7 of the 9 cumulative updates - correction, "previous ... Cumulative Updates", according to the caption - were released in 2017 after the Service Pack.
Table is from MSSQLTips.


texts: Oracle expdp et impdp : order of schemas?

texts:
When I try to export and then import many schemas from one Oracle DB to another one, I get errors because grants and synonyms cannot be recreated in the target DB; the reason is probably that the schemas are not imported in the right order.

So :

With impdp, is it possible to define the order in which the schemas are to be imported ?
If not (and I think that impdp simply uses the order in which the schemas were found in the dump file) : is it possible during the expdp to define the order in which the schemas are to be exported ?


texts:
Do we have sp_sequence_get_range equivalent in Oracle.

I need to advance my sequence by some numbers always how can I achieve this.


texts: What are my options for GUI based Administration & Management of PostgreSQL

texts:
I have been given the task of working with PostgreSQL 9.1. I have previously worked with Microsoft SQL, and had used the SQL Management Studio. I had used Oracle's SQL developer as well as TOAD when I had to deal with Oracle.

When Compared to such tools, the pgAdmin seems rather limited. I have checked the list at http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools#Lightning_Admin_for_PostgreSQL but many of links I checked were dead.

I would like something which gives me the following features:

Import and Export of data into various formats like .xls/.xlsx, .csv
etc
Ability to see the data in the tables without having to click so much. (for example, in Oracle's SQL developer, once I sign in, I can see the data in my tables in 3 clicks; In pgAdmin, I have to navigate through the hierarchy, to reach my table, and then rightclick to get the 'View Top 100 Rows option')
Append data from an external source (.xls/.csv) to an existing Table.
Intelisence/ Code Completion while writing SQL commands.


texts: How to execute stored procedure with output parameter in Oracle PL/SQL?

texts:
This is the table I have created:

CREATE TABLE Toy
(Toy_No NUMBER PRIMARY KEY,
 Toy_Name VARCHAR(30) NOT NULL
 );

This is the sequence I have created:

CREATE SEQUENCE toy_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

Then I tried creating a simple stored procedure with output parameter:

CREATE OR REPLACE PROCEDURE insert_toys(toy_id OUT NUMBER,toy_name VARCHAR(30))
AS
BEGIN
toy_id := seq_toy.NEXTVAL;
INSERT INTO Toy (Toy_No,Toy_Name)
VALUES (toy_id,toy_name);
END;

But I'm getting a compilation error.
Where can be the mistake possibly be ?


texts: Any issue encountered after implementing SQL Server 2017 CU22?

texts: We all are aware of SQL Server 2019 CU7 issue (Microsoft recalled the patch). Now I am cautiously optimistic to apply SQL Server 2017 CU22. I wanted to reach out to the SQL Server Community to know if anyone implemented a new update (CU22) and encounter any issue. I tried to research online but end up having nothing. Thank you all for sharing.


texts: I thought that this was fun to share. I was wondering when should I give up all hope that my question would ever be answered and begin to start crying. So, I wrote a query for the StackOverflow DB that would give me that answer (in general). It calculates how many and what percentage of questions have been anwsered within an hour, day, week, month, 3 months, year and over a year. So if you like you can find that out yourself. Btw, the difference between the 2010 and full version shows some improvement, so that is very positive. :-P


texts: Query data from multiple linked remote databases

texts:
Using SQL Server 2005 databases and SQL Server Management Studio 2008, I need to query a single value from each of n linked databases at a regular interval. The servers are physically remote and I can basically count on a timeout, attempts to create a query that completes have failed. Every attempt fails when a server times out, I've specifically gotten the 'Login timeout Expired' error.

Ideally I need a stored procedure that generates a row or table with a column or row for each server and a value indicating what my value is in the remote database, or null if I get a timeout (the value on the remote database cannot be null).

My first thought to gather the data was

select
(SELECT TOP 1 [value]
FROM
[RemoteServer1].[DB].[dbo].[Table]) as
RemoteServer1,
(SELECT TOP 1 [value]
FROM
[RemoteServer2].[DB].[dbo].[Table]) as
RemoteServer2,
...

Which generates a single row with all the values I need and indexes I can use to report the needed values. But a single time out in one select statement kills the whole query (it does execute correctly if all servers are reached). I can safely assume that at some point in the query at least one server will time out.

I tried an exception, but it doesn't seem to work (I've never trued to use exceptions in SQL Server before). I tried this in place of each subquery:

begin try
(SELECT TOP 1 [Value]
FROM
[RemoteServer1].[DB].[dbo].[Table])  as RemoteServer1
end try
begin catch
select null as RemoteServer1
end catch,

But the timeout still causes the whole query to fail, I think it's because try only works if the connection doesn't fail?

Is there some way this can work? The only other solution I can think of would be a stored procedure on each remote database to send this record so it's all on our local database (which we can depend on reaching) but that would introduce much more complexity than this query is worth.


texts:
It seems this is a common problem over the internet so let me give you details on my environment and the steps I did:

Client - Windows 7 32-bit with SQL Management Studio
Server - Windows Server 2008 R2 Standard with SQL Server 2008 R2 [Default not named instance] and SQL Management Studio
For the server I added Application server role. I think I also added Web server role. The server and client are on the same LAN.

After installation and testing windows authentication to the SQL server, this is -in short words- what I did to enable SQL server remote login:

Enabled and started all SQL Server services.
Enabled TCP/IP for SQL Server. [I restarted the server to be sure after this step]
Enabled TCP port access on the firewall.
Allowed sqlservr.exe and sqlbrowser.exe on the firewall.
Created another user rather than sa and give it sysadmin role

On the client machine, when I open SQL management studio, I tried to login with server IP address, server name, server name or IP with port [192.168.1.25,1443]. Username and password are correct.

After the previous explanation, I can't access the server's SQL Server remotely, I get the famous error "A network-related or instance specific". Notice that the same error message appear, but with different error codes after performing some steps from the above. I can't provide you with the error codes since I'm not with the machine right now. Any help is really appreciated! Thanks!


texts: IN Clause causes Execution plan to change from Nested Loops to Hash Match

texts:
I'm tuning a query and have discovered some behaviour I'm not clear about.

If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes.

There only 7 rows returned in the result since there are only 7 items in the IN clause.

Thinking this was a bit odd, I started trying to get both parts of the query behaving individually, however no matter what I've tried the IN clause causes the long execution time.

Looking at the execution plan I can see that it's taking some joins and converting them from Nested Loops to Hash Match.

I've diced up the SQL so the single SELECT is now split between a View, CTE and SELECT.  I would have expected the IN clause to not impact what is happening inside of the View, but it is.

The execution plans are: with the IN clause and without the IN clause.  Please be gentle regarding the quality of the query; it's a work in progress on an inherited project and I'm by no means a proper DBA.

Selecting all 1.8 million rows from the View takes ~1 minute; executing the CTE by itself takes ~3 seconds; executing the full query takes ~3 minutes.

How can I convince SQL Server to maintain the Nested Loops join instead of switching to Hash Match when I add in the IN clause?  Or is there something else that I should be trying?


texts: ON DUPLICATE KEY UPDATE in INSERT INTO SELECT with GROUP BY

texts:
I have the query of
INSERT IGNORE INTO t2 (ID,P1,P2,Items)
    SELECT ID,P1,P2,SUM(Items)
    FROM t1 GROUP BY ID,P1,P2;

Since I need to run this query frequently to inspect changes in t1, I need to add
ON DUPLICATE KEY UPDATE t2.Items=SUM(t1.Items)

but it gives ERROR 1111 (HY000): Invalid use of group function error.


texts:
An Oracle 12c production database includes tables with a BLOB column. Usually the growth rate is 2GB per week. This is the table create statement:

CREATE TABLE XXX.FILEENTRY
(
  FILENAME     VARCHAR2(300 CHAR),
  FILESIZE     NUMBER(20),
  FILECONTENT  BLOB
)
LOB (FILECONTENT) STORE AS SECUREFILE (
  TABLESPACE  DATA
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  CACHE
  LOGGING
      STORAGE    (
                  INITIAL          104K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
TABLESPACE DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;

Since some days the LOB increases up to 100GB a day. The LOB which increases that fast is: SYS_LOB0000119621C00011$$:

Owner,Segment name,Segment size (GB)
xxx,FILEENTRY,1,8
xxx,SYS_LOB0000119621C00011$$, 1156,1

The application has a transfer table which only shows the usual input of about 2GB a week. What could be the reason for this growth if this is not coming from the users? Is there a way to check the content of SYS_LOB0000119621C00011$$?


texts: Backup errors after TDE certificate expired and new cert was created with AlwaysOn

texts:
I have 2 databases in AlwaysOn with TDE enabled.
My TDE cert expired and i created a new one but Ola's Backup script is still failing:
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DB1, DB2',
@Directory = 'D:\SQL-Backup',
@CopyOnly='Y',
@CleanupTime = 120,
@Compress='Y',
@BackupType = 'FULL',
@Verify = 'N',
@CheckSum = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'DB_DEK',
@LogToTable = 'Y'

With this error (which is an old Cert):

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFAF8C09F62ED7D523AE634133331690FC2FD8367'.

Using native sql backup works:
BACKUP Database [DB1]
TO  DISK = N'D:\SQL-Backup\DB1.bak'
WITH FORMAT, INIT,  NAME = N'DB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [DB_DEK]),  STATS = 10
GO

For this to work I had to take the DBs from AlwaysOn.
Has anyone else experienced this type of scenario? And, if so, how did you resolve it?


texts:
I have three Servers, two of whom are running postgres 9.5.3 and the third runs pgbouncer 1.7.2.

i can access one of the two postgresql instance through pgbouncer and it all works perfect. the other postgresql Servers, lets call it P1 is not under my control and i have to connect using ssl so it got and therefor the following line was added to this Servers pga_hba.conf

hostssl    all       all             ###.###.###.###/32               md5

I can connect to P1 from pgbouncer server with psql with no Problem.
But when i connect via pgbouncer to P1 i get this error message

psql: ERROR:  no pg_hba.conf entry for host "XXX.XXX.XXX.XXX", user "myuser", database "mydb", SSL off

here is my pgbouncer.ini

[databases]
my_database = host=xxx.xxx.xxx.xxxx port=5432 dbname=mydb user=myuser password=mypassword client_encoding='UTF8'

[pgbouncer]
listen_addr = *
listen_port = 5432
auth_type = md5

auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

admin_users = rms_admin

pool_mode = session

server_reset_query = DISCARD ALL
server_check_query = select 1

default_pool_size = 40

stats_period=300

log_pooler_errors = 1

i've tried add sslmode=require on the connection string to my_database in combination with ignore_startup_parameters but that led to the following error in the pgbouncer log .

ERROR skipping database my_database because of unknown parameter in connstring: sslmode

how can i make pgbouncer build up an ssl Connection?


texts: Cannot set READ_COMMITTED_SNAPSHOT to OFF on Azure SQL

texts:
Me and my team are trying to use Azure SQL for our development environment and ran into some critical and unwanted differences in locking behaviour  to our production environment. The main problem is duplication in increment values because with READ_COMMITTED_SNAPSHOT set to ON the xlock, holdlock, rowlock does not prevent selects to other transactions, so we get the typical:

[tran1] select value (1),
[tran2] select value (1),
[tran1] update value (++1),
[tran2] update value (++1)

Instead of the second transaction setting the value to 3.

We tracked it down to having READ_COMMITTED_SNAPSHOT set to ON in Azure SQL, which comes as a default, unlike if you do your own SQL Server setup (that's weird but lets not dwell).

Great, so now we just needed to set READ_COMMITTED_SNAPSHOT to OFF but this is where we are stuck. In Azure SQL we can't seem to do that, even if no other processes are connected to the server we always get a deadlock:

ODBC error: State: 40001: Error: 1205 Message:'[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 120) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.'.

Even if we run the alter with rollback immediate:
ALTER DATABASE [xxx] SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE

We spent some time trying to go around this problem with no success so far, help!


texts: SQL Server 2008: NULL SID for WINDOWS_USER in sys.database_principals

texts:
Has anyone ever seen anything like this before? I have a WINDOWS_LOGIN AD user that has a record in sys.server_principals with a SID and can login fine. But in a few databases, they have a record in sys.database_principals with a NULL SID.

I have scoured Google trying to find out what's causing this as it's causing problems with my SQL Server auditing tools but have had no luck.


texts: Suddenly unable to connect to PostgreSQL

texts:
I have a simple backend running on a server back at my house while I'm away at college. I had it just sitting there, doing nothing, with daily endpoint tests for about a month, when suddenly yesterday the endpoint tests failed.

I SSHd to the server, and saw that the error given was

FATAL: Ident authentication failed for user "username"

at the line where pg tries to connect to the postgres database, line 24 here.

The connectionString hasn't been changed, neither has pg_hba.conf. What could cause the error?

In the log, I get

{ [error: Ident authentication failed for user "jkearl"]
 name: 'error',
 length: 92,
 severity: 'FATAL',
 code: '28000',
 detail: undefined,
 hint: undefined,
 position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
 where: undefined,
 schema: undefined,
 table: undefined,
 column: undefined,
 dataType: undefined,
 constraint: undefined,
 file: 'auth.c',
 line: '304',
 routine: 'auth_failed' }

/home/jkearl/LilyPad/LilyPadBackend/routes/authentication.js:25
   var query = client.query(cleanser('SELECT * FROM lilypad.people '+
                   ^
TypeError: Cannot call method 'query' of null

And in postgres's log:

2015-10-19 22:04:15 PDT LOG:  could not connect to Ident server at address  "127.0.0.1", port 113: Connection refused
2015-10-19 22:04:15 PDT FATAL:  Ident authentication failed for user "jkearl"
2015-10-19 22:04:15 PDT DETAIL:  Connection matched pg_hba.conf line 92: "host    all             all             127.0.0.1/32            ident"


texts:
I have an SSIS package deployed to both my DEV and QA environment, both QA and DEV are under the same server and SQL Instance. The package has only three objects in the dataflow: an MDX query, a derived column transformation, and the destination table.
When I run that package it does so successfully on both DEV and QA.

I then created another package with just a simple logic that validates whether the original package is to be run or not depending on the month. I ran it and the validation works fine. For this new package I added a File connection to the original package and then added an Execute Package Task which I then mapped to the File Connection created previously.

I manually copied the DTSX file created and placed it in the Server shared folder hosting the SQL Server.
When I run this new package from my DEV folder it works with no issues
When I run the new package from the QA folder it fails with the following error

  ERROR MESSAGE: Code: 0xC0202009     Source: Data Flow Task
  Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has
  occurred. Error code: 0x80040E21.  End Error Code: 0xC004701A
  Source: Data Flow Task SSIS.Pipeline     Description: component failed
  the pre-execute phase and returned error code 0xC0202009.  End Error
  DTExec: The package execution returned DTSER_FAILURE (1).    The
  package execution failed.  The step failed.

The error indicates the issue is on the Data Flow task but if I run the package that contains that data flow by itself it does work on both environments.
Why would that package fail only when called by another package and only for the QA schema?
I'm on SQL Server 2008 R2 by the way.


texts: How Datapump is faster than export/import backup?


texts: Change Table format from within MySQL 8?

texts:
Is there a mysql command to change the output format of a table?
Specifically, I'd like to get rid of the formatting '-'s and '+'s:

+------+------------+
| ID   | Name       |
+------+------------+
| 1523 | Wien       |
| 1524 | Graz       |
+------+------------+

I tried:

Appending

--result-format=tabbed

to my docker exec command:

docker exec -it [DB-container-name] mysql -uroot -p --result-format=tabbed

as I gathered from this MySQL page, but this gives me "unknown variable".


texts: AWS Aurora queries much slower in "sending data" phase than RDS instance

texts:
I've been using an RDS instance during the development phase of a project. This was a db.t2.micro instance, so not very powerful at all.

Today I have created a new Aurora (5.6) [serverless] database and migrated all our data across by doing an export / import of SQL data. I've assigned Aurora between 2 and 8 capacity units.

My RDS instance was using mySQL 8.0, so a rather newer version that that provided by Aurora. But other than that it's all the same tables / structure and data.

I've done a few mildly complex queries to check it all out and noticed a huge speed difference. That that difference is almost all accounted for in the "sending data" phase.

With one query the old RDS instance took 5.6 seconds, the new Aurora one took over 30 seconds... The more data I pull back, the bigger the difference in performance.

Doing the same query with 2 and with 8 capacity units yields pretty much the same overall query time. So it does not appear to be related to capacity. Given that the old RDS was just a micro instance, I'd really expect Aurora to be significantly faster, not significantly slower.

I saw some threads talking about innodb_adaptive_hash_index. I tried enabling that, but that made no difference.

Is Aurora's I/O simply poor compared with normal RDS instances? Or is there some reason for this that can be remedied?


texts: No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

texts:
I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats.

Please suggest what could be various reason for this as I can see my stored procedure has been executed multiple times.


texts: Which FLUSH command before taking EBS volume snapshot?

texts:
The FLUSH TABLES WITH... command has a few flavors. The manual meagerly describes the differences between:

FLUSH TABLES WITH READ LOCK
FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT
FLUSH TABLES ... FOR EXPORT

Am I interpreting the manual correctly that if one needs to perform a disk volume snapshot, such as the EBS Snapshot, the preferred way now is to use the FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT right before initiating the snapshot?


texts: How do I quickly tell the structural differences between two databases?

texts:
I am upgrading a web application to a newer version and need to migrate the data manually. The newer version uses slightly different database structure. It has added a few tables, but all of the old tables still exist with the same name. How can I quickly see if any of the new tables are differently structured than the old ones?

Both databases (the old structure and the new structure) currently exist on the same server. The new one is empty, but the old one still has all the original data. I only have access to the server through ssh and the MySQL cli (no phpMyAdmin or GUI desktop applications like this one).

Version information: mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64)


texts: How to find a data warehouse's design diagram?

texts: I have a database with lots of tables. I need to quickly find out what data warehouse schema was used. Specifically, looking for the dimensions and fact tables. There does not seem to be a standard naming convention. The database diagram is not showing anything.


texts:
I have an application that deals with businesses and their physical locations. So most tables have a location_id field that logically partitions the data.

One table deals with marketing referrals and each location has a set of sources that they can define in the application. However, ALL locations have an "Unknown" source that is basically a catch-all when the source isn't known. The marketing referral id is the primary auto-incrementing key on the table, and the location_id is a foreign key to the locations table.

As its set up right now, there is an "Unknown" record for each location in the table, each with its corresponding location_id. This means there are a bunch of duplicative records (aside from the location_id). Is this the best possible design for this scenario? I don't really want to remove the foreign key constraint, but in a perfect world I'd see there being a record with an ID of 0 that would serve as the "unknown" record for all the various locations.

Am I overthinking it?


texts:
I'm working on a query that essentially aggregates a slew of tables together.

The first method I tried was to inner join all of the tables into one nice big query that pretty much looked like this:

select
  a.col1
, a.col2
, a.col3
...
, b.col1
...
...
, l.col1
, l.col2
from myprimarytable a
join secondtable b on a.key = b.key
join third table c on a.key = c.key
...
join thelasttable l on a.key = b.key

This took forever to run. However, a second solution that looked like this:

create table #output (
  primkey char(40)
, mycol1 char(1) null
, mycol2 decimal(20,1) null
...
, mylastcol varchar(max) null
)

insert into #output
select
  a.col1
, a.col2
...
from myprimarytable a

update #output set
 mycol3 = b.col1
, mycol4 = b.col2
...
from #output a
join secondtable b on a.primkey = b.key
...

My question:

Why does the temp table with updates take so much less time ( about 30x faster in my particular case), than one with all of the joins in one go?


texts: Post Migration error in sql server error log

texts:
I am seeing below error messages when we have moved/migrated databases to new server/instances from SQL2K12 stanadlone to cluster

last target outstanding: 20800, avgWriteLatency 30
average throughput:  69.12 MB/sec, I/O saturation: 243550, context switches 398948
FlushCache: cleaned up 579723 bufs with 388331 writes in 65526 ms (avoided 125170 new dirty bufs) for db 19:0

I have no clue if this points any issue with stoage.

Now the concern is, before we did migration i had similar error messages on the this instance but they were like coming after half hour 2 hours or may 15mins. no fixed pattern.

But after migration above error message keeps coming every 2 mins from the day we migrated. That is what sounds alarming.

On the old server from where we migrated, yes we had similar messages but again there was no fixed patterm, sometimes 2 mins, an hour may be after 4 hours, but not continuous like on new server.

I got some info here https://blogs.msdn.microsoft.com/psssql/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log/

But still i am confused how to deal with this and what exactly these messages point dba's to look for?

Please suggest


texts: Postgresql: Modify one value inside of a nested json column

texts:
I have a json column called "Parameters" in a "Assets" table. The jsons are nested and have the following structure:
{
"params1": {"k": 1.1, "efficiency": 0.7},
"params2": {"k": 1.9, "C_max": 100.0, "C_min": 10.0}
}

I would like to update only one value "k" in "params2" and keep the rest of the values unchanged. I tried with the following code:
update "Assets"
set "Parameters" = "Parameters"::jsonb || '{"params2":{"k":2.2}}'::jsonb
where "Id" = 185;

This results in
{
"params1": {"k": 1.1, "efficiency": 0.7},
"params2": {"k": 2.2}
}

So the rest of the parameters in "params2" are dropped which is not desired. What would be a way to modify "k" and leave the rest as it is?


texts: SQL Query not throwing error even if the entered scale value is higher than specified

texts:
I need to capture numeric values without truncation. But the output value is getting truncated even though I specified the Scale as '2'.

Column 'A' datatype - numeric(5,2)

Case 1:

Entered value: 123.55689335218777665555777778899995544

Output : 123.56

Case 2:

Entered value: 1234.55689335218777665555777778899995544

Output: Arithmetic overflow error converting varchar to data type numeric.

My question is why I am not receiving any error when the scale is higher than '2' but I am getting error when the precision is higher than 3.

I am facing this issue in SSIS Data Conversion task. I need to capture the rows which are truncated in another table. Simplified this question in this way for obvious reasons.


texts: i am working on porject to encrypt the connection between ssrs reports and sql server. we have already installed ssl certificate on sql server. can i know how to encrypt the connection string in SSRS datasource


texts: Default and temporary tablespaces for installing sample Oracle database schemas?

texts:
What should I specify as default and temporary tablespaces (users and temp parameters of mksample.sql script) for installing sample Oracle database schemas?

In my environment I call the script like this:

$ORACLE_HOME/bin/sqlplus SYSTEM/systempw@\"localhost:1521/ORCLPDB1\"
SQL>@mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /home/oracle/log localhost:1521/ORCLPDB1

Do they exist in a clean Oracle 18 instance? Or should I create them?


texts: Grouping records time interval (start-end time) into groups of specific time interval (eg. 30 min)

texts:
I would like a query that would return the count of records that have their start-end time in specific time interval (eg. 30 mins).

For example records:

id|start|end
1|10:00|12:00
2|11:00|12:20
3|10:15|10:45

would result in

10:00-10:30|2x (records 1 and 3)
10:30-11:00|3x (all)
11:00-11:30|2x (records 1 and 2)
11:30-12:00|2x (records 1 and 2)
12:00-12:30|1x (record 2)

Usage: If I have for example 3 private instructors; I can't have more than 3 clients, meaning (in the example above) I can't appoint anybody from 10:30-11:00 as all 3 instructors are already occupied.


texts: MySQL - What exactly does smallint(2) permit?

texts:
Sorry for the really simple question, but I can't figure it out.

When I set up my schema, I set up my primary key to use a field of type smallint(2). The thought behind it was that the field should only ever consist of 2-digit numbers, 1-99.

We've broken the upper limit of that and now I'm seeing 3-digit numbers being added to this field. I'm now speculating that smallint(2) and varchar(2) do not function the same, but since I don't understand what's going on with this table I'm getting a little concerned.

What values does a field such as smallint(2) allow?

Thanks!


texts: SQL Agent PowerShell job runs in ISE but not in SQL Servere

texts:
My PowerShell script uses WinSCP to download files.  It writes a message to a sql server table containing the number of files downloaded.  When run within ISE it's fine.  When run as a sql agent job it fails with error msg 'You cannot call a method on a null-valued expression.'  This error is generated by the variable that the file count is assigned to.  The variable is $fileCount.

The code is below.  See variable $fileCount.  I suspect that the beginning $ in the variable name is causing sql to misinterpret the variable in some way but I'm not sure.  Any help is appreciated.

# Step 1 - Powershell script to download xml files to local folder.

param (
    $remotepath = "./",
    $mask = "*.xml",
    $destination = "D:\Files\Downloaded"
)

try
{
    # Load WinSCP .NET Assembly
    Add-Type -Path "c:\Program Files (x86)\WinSCP\WinSCPnet.dll"

    # Setup session options
    $sessionOptions = New-Object WinSCP.SessionOptions -Property @{
        Protocol = [WinSCP.Protocol]::Sftp
        HostName = "hostname"
        UserName = "user"
        Password = 'password'
        SshHostKeyFingerprint = "fingerprint"
    }

    $session = New-Object WinSCP.Session

    $fileCount = 0
    $elements = 0
    $errOut = $null

    try
    {
        $session.Open($sessionOptions)
        $directory = $session.EnumerateRemoteFiles($remotepath, $mask, [WinSCP.EnumerationOptions]::None)
        $elements = $directory.count
        **$fileCount = $elements.count**

        $transferOptions = New-Object WinSCP.TransferOptions
        $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary
        $transferResult = $session.GetFiles($remotepath, $destination, $False, $transferOptions)
        $transferResult.Check()
     } # end try

    finally
    {
        $session.Dispose()
    }

} # end try

catch
{
        $errOut = ($_.Exception.Message)
}

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "ConnectionString;"
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
$SqlCommand.CommandText = "EXEC Stored_Proc @chvMessage, @chvFileIndex, @chvRecordCount"
$SqlCommand.Connection = $SqlConnection
$SqlCommand.Parameters.Add("@chvMessage", [system.data.SqlDbType]::nvarchar) | Out-Null
$SqlCommand.Parameters['@chvMessage'].Direction = [system.data.ParameterDirection]::Input
$SqlCommand.Parameters.Add("@chvFileIndex", [system.data.SqlDbType]::nvarchar) | Out-Null
$SqlCommand.Parameters['@chvFileIndex'].Direction = [system.data.ParameterDirection]::Input
$SqlCommand.Parameters.Add("@chvRecordCount", [system.data.SqlDbType]::nvarchar) | Out-Null
$SqlCommand.Parameters['@chvRecordCount'].Direction = [system.data.ParameterDirection]::Input

$SqlConnection.Open()
if ($errOut -ne $null)
{
    $SqlCommand.Parameters['@chvMessage'].Value = $errOut
}

if ($errOut -eq $null)
{
    $SqlCommand.Parameters['@chvMessage'].Value =  "**** " + $fileCount.toString() + " FILES DOWNLOADED"
}

$SqlCommand.Parameters['@chvFileIndex'].Value = ''

$SqlCommand.Parameters['@chvRecordCount'].Value = ""
$SqlCommand.ExecuteNonQuery() | Out-Null
$SqlConnection.Close()


texts: Stolen Server Memory on SQLServer that hosts Sharepoint databases

texts:
On one of our servers we have problems with high stolen server memory (around 70% as reported by SCOM).

There are Sharepoint databases on the server and there are 2 extended events sesions called SharePoint_Diagnostics with ring buffers configured as a target.

CREATE EVENT SESSION [SharePoint_Diagnostics_3067bd7806cd459ebdbf3eb257c43267_0] ON SERVER
ADD EVENT sqlserver.module_end(
    ACTION(sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff')))
ADD TARGET package0.ring_buffer(SET max_memory=(49152))
WITH (MAX_MEMORY=98304 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

They seem to have max_memory set, but MEMORYCLERK_XE is taking around 9GB of memory (the server itself has 16GB).

Version of SQL Server is:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
    Oct 19 2012 13:38:57
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Is there any way to free memory without restarting SQL?

Could it be the issue with memory leak as described in:
Memory leak occurs...
?


texts: Cannot find Database Mail feature in SQL Server Express 2012 SP1

texts:
I need to use SQL Server Database Mail feature in my project. However Database Mail seems not available on my development instance.

There should be a 'Database Mail' node in the Object Explorer. In this screenshot I can't seem to find any. Also shown is my SQL Server version.

Is Database Mail not supported in this SQL Server version? Which SQL Server version (or edition) required to use Database Mail?

All documentation I can find online always assume the feature is already installed and just go on explaining how to configure it. If my SQL Server version supports Database Mail, how to install/enable the feature?


texts: Remote connection failing to run any command

texts:
Our partner has created a new database to us so we can use it as a test database. He gave us all addresses and connection information so we can use it to connect to the database.

I've asked here how to add a catalog to the database so we could connect to it and it work as it should.

After connecting to it we could not run any command always getting same error:
We try to run:

create table test (id integer)

  SQL0551N  "MY_USER" does not have the required authorization or privilege to
  perform operation "EXECUTE" on object "NULLID.SQLC2H23".  SQLSTATE=42501

And then we called to our partner telling him that and he send a screenshot executing same command and it work as it should.

I think that it worth to mention that he is at the terminal on the server and we are from our machine on the db2 client console.

So, is there any permission or authorization or grant that he should add to our MY_USER user so we can run anything on it?

The strange thing is that when we connect with the same user on the TOAD client all commands works as it should. So my wild guess is something to do with the catalog we created.


texts: Alwayson Database Recovery Failure during TDE encryption

texts:
Last week I enabled TDE encryption on my AlwaysOn Availability Group databases of SQL Server 2012. During the encryption of one of the larger databases, bigDB  (~200gb) a failover event occurred. Of the 4 databases in the AG, 3 recovered without issue but the last stayed in a recovery pending state. Checking the error logs now I see that there is no line that states "Recovery is writing a checkpoint in database big DB..." as there is for the other databases in the AG. I could not fail-back, with or without data loss. I first restarted the failed server in the AG and verified that it was working as expected. I could then move some AGs back to the original server I just restarted but still could not recover bigDB.  The dashboard at this time showed that that bigDB was critical and 'Not Synchronizing'. The option  to 'resume data movement' was grayed out and the t-sql statement to do the same resulted in an error. As a last resort I failed all the other AGs back to the original server and forced a restart of the SQL Server Service.  At this time the error logs indicate that the SQL Server began to recover and completed after 2293 seconds. Normally this recovery takes less than a minute and I have never seen it take any longer than 2 min.

Could this have been a symptom of the incomplete TDE encryption? I can not find any documentation that indicates that a failover during encryption is not supported and I find it hard to believe that it is not.  I have engaged Microsoft support on this issue and they are claiming that the Database would have 'eventually recovered' if I had just left it alone long enough but my error logs do not back that statement up. I am at a bit of a loss of where to keep looking to find the cause of the recovery failure but am VERY interested in determining why this happened as it was 5 hour outage for us.  Has anyone experienced a recovery failure similar to this?


texts: mongo cluster collection count showing different every time

texts:
I had a replica set earlier which I have converted to cluster.

Current configuration is:

3 shard
4 router
4 config server

When I am trying to get count of one of collection sometime it is showing null and some time some value , why this is happening?

Please help.


texts:
What is the best approach (or what options am I not aware of) to creating a static table or view where the data rarely, if ever, changes?  An example use-case is a list of US states, where my structure would look something like:

state(
  id NUMBER(15) PRIMARY KEY,
  name VARCHAR2(35),
  abbreviation VARCHAR2(35)
)

The two approaches I am currently evaluating are:

Create a table and insert the desired data using a one time data import script

CREATE TABLE state
  (
    id           NUMBER(15) PRIMARY KEY,
    name         VARCHAR2(35),
    abbreviation VARCHAR2(2)
  );
INSERT INTO state (id, name, abbreviation) VALUES (1, 'California', 'CA');
...

Concern: data can be manipulated easily and modifying these records would break other parts of the software

Create a view using unions of hard-coded values

CREATE OR REPLACE VIEW state
AS
  SELECT 1 AS id, 'California' AS name, 'CA' AS abbreviation FROM dual
  UNION
  SELECT 2 AS id, 'New York' AS name, 'NY' AS abbreviation FROM dual
  UNION
  ...

Concern: maintenance, potential for errors when inputting (as opposed to using a sequence and trigger to populate id, for example), and it just doesn't feel right


texts: MySQL, foreign key, can't create table error 150

texts:
UPDATE: Sorry, it was because i forgot to add UNSIGNED attribute to the lang_id column.

Original:
I'm trying to create a table with foreign key in MySQL.

I get this error: #1005 - Can't create table 'xy.trans' (errno: 150)

Any ideas whats wrong with the following create statement?

CREATE  TABLE IF NOT EXISTS `lang` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` CHAR(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `trans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `something` TEXT NOT NULL,
  `lang_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `index_trans_lang` (`lang_id` ASC),
  CONSTRAINT `fk_trans_lang`
    FOREIGN KEY (`lang_id` )
    REFERENCES `lang` (`id` )
    ON DELETE SET NULL
    ON UPDATE CASCADE
) ENGINE = InnoDB;


texts: Why is it recommended to store BLOBs in separate SQL Server tables?

texts:
This highly-upvoted SO answer recommends to put images in separate tables, even if there is only a 1:1 relationship with another table:

  If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures - do not store the employee photo in the employee table - keep them in a separate table. That way, the Employee table can stay lean and mean and very efficient, assuming you don't always need to select the employee photo, too, as part of your queries.

Why? I was under the impression that SQL Server only stores a pointer to some dedicated BLOB data structure in the table, so why bother to manually create another layer of indirection? Does it really improve performance significantly? If yes, why?


texts: Join two SELECT results with the same post_id value

texts:
I need to do a query to the MySQL database of my site built in WordPress / WooCommerce.

The 'wp_posts' table keeps products, articles and more. Every row in 'wp_posts' has a 'post_id' to connect 'wp_posts' with 'wp_postmeta' table. In the 'wp_postmeta' table, there are rows with additional data and values for every 'wp_post'. These rows have 'meta_key', 'meta_value' and 'post_id' values.

I need to SELECT post_ids where values downloable and virtual are 'no' at the same time. To have an example, there are two rows in 'wp_postmeta', one where 'meta_key' is '_downloable', 'meta_value' is 'no' and 'post_id' is 100, and another one where 'meta_key' is '_virtual', 'meta_value' is 'no' and 'post_id' is 100.

I just get to do it separately, as follows:

SELECT wp_postmeta.post_id
FROM wp_postmeta
WHERE wp_postmeta.meta_key='_downloadable'
AND wp_postmeta.meta_value='no';

SELECT wp_postmeta.post_id
FROM wp_postmeta
WHERE wp_postmeta.meta_key='_virtual'
AND wp_postmeta.meta_value='no';

Can you help me?


texts:
I have one ".ibd" file with me. I have configured my local Database server with FILE_PER_TABLE option. i have two question here.

When i was trying to restore/import the IBD file, the engine expect me to have the structure information. Is it not possible to load the data and structure using Single IBD file?
Also can we do hot copy and restore the innodb files from one server to other as MyISAM engine?

Please need your suggestion on the same.


texts:
When i connect to SSMS on my server through remote desktop and query my database using:

SELECT * FROM [sys].[database_principals] WHERE [principal_id] = USER_ID()

I am getting the correct results (my name is DOMAIN\Glen and the type is WINDOWS_USER)

However when i connect to SSMS as a Administrator (right clicking and selecting run as administrator) i get the following results (name is dbo and type is SQL_USER)

This is a problem for me as in the users section under DOMAIN\Glen i have the default schema set. So when i run as administrator it is not picking up the correct tables.

Plus i am a little confused on why i am connecting as a SQL_USER when my authentication is set to Windows Authentication only.

The main reason i am trying to solve this is because when i run SSMS from my local machine it is connnecting in the same way as if i run as an administrator on the server and therefore i am unable to access my tables within the defined schema. I can not add this onto my procedures as they are all hard coded and there are over 1000.

Any help would be great.


texts: Mongodb: Determining shard key strategy on compound index

texts:
I have a collection with 170 millions+ documents and it is only going
 to increase. The size of the collection is not that huge, currently
 around 70 GB.

The collection has two fields indexed on: {AgentId:1, PropertyId:1}.
 Generally one imports a huge file(millions of documents) belonging to
 a particular AgentId but the PropertyId(non numeric nullable) is
 mostly random unique value.

Currently I have two shards with shard key based on {_id: hashed}. But
 I am planning to change the shard key to  compound Index {AgentId:1,
PropertyId:1} because I think it will improve query performance( most
of the queries are based on AgentId filter). Not sure whether one can
have a nullable field in the shard key. If this is the case then app
 will make sure that the PropertyId is random no.

So looking to get a picture as to

How the data will be distributed to shards during insertion
 and how the range of a chunks are calculated during insertion?
Since the PropertyId is random value. Does the compound key fits the
  definition of monotonically increasing value?

I am a newbie to MongoDB. And wanted to know if I am on the right path?