texts: Impact of table compression on HA Synchronous-commit mode scenarios

texts:
I am working with a HA SQL 2014 instance with Synchronous-commit mode.

I did some table compression analysis with sp_estimate_data_compression_savings and I learned that table compression can be very beneficial for the overall performance. My servers have very powerful CPU setup.

However, It is unknown to me how table compression impacts a HA Synchronous-commit instance. My guess is that since data is compressed, it makes it easier for Synchronous-commit mirroring.

What is the impact of table compression on HA Synchronous-commit mode scenarios?


texts:
I need to replicate an oracle database without updating each other just for backup purposes.
I have a Main DB(MDB) that have jobs to send to be processed data to interface DB(IDB).  IDB will be updated by an external application. I want the backup of IDB without updating changes to replicated IDB.
I can not control Main DB job to send IDB data for another IDB for back up so I need a software solution.
I there any software solution that can do this scenario?


texts:
I am doing  select which is:

SELECT replace(field_1, E'

publicidade

', '') FROM test

An that returns what I want, but when I try to update doing the same thing, It doesn't work as expected:

UPDATE test SET field_1 = replace(field_1, E'

publicidade

', '')

It doesn't update the column without the

publicidade


texts: Managing SQL Server MDF and LDF file size

texts:
I am new to SQL Server and don't fully understand its architecture. I've looked around, Googled, asked around but  found no simple (understandable) answer to the problem. I'm not even sure if it  is problem in technical terms or just the default behavior, but in terms of my business operation, it is a big problem.

I have acquired SQL Server 2008 R2 [from the developer who doesn't work here anymore], with a database of physical storage size of 2 TB. I've created a database, say TEST with

TEST.mdf default at 10 MB, 10% growth and max size: unlimited. There are 40 files comprising the TEST.mdf file say TEST_part01 through TEST_part40 with same configuration as TEST.mdf.
The log file - TEST_log.ldf file also has same configuration as TEST.mdf
The database handles millions of rows in daily basis, with lots and lots of insert, delete, update, drop etc. operations.
I am running out of space. While trying to shrink the database, I saw that the TEST.mdf could be shrunk by 41% and TEST_log.mdf by 99%.

Questions

Is 41% and 99% of recoverable space actually taken by garbage, un-used data? If so can   I selectively remove/purge (if not all than most of ) the 41% and 99% of data?
Is there any temporary or otherwise structure/objects created by the SQL Server itself,  which I can safely delete/purge?


texts: Installing SQL server 2008R2/2012 on windows server 2012 box

texts:
we are trying to install SQL2k8R2 and SQL server 2012(Standard edition) on the windows server 2012 box (Standard 64 Bit).

For SQL 2k8r2, i used slipstream method so as to bring set up to atleast SP1 for support as compatibility on windows 2k12.

But the problem comes when we are running the set up, i.e. SQL2k8R2 and even SQL2012 , we are getting the below error for missing .Net 3.5

If we try to enable 3.5 manually from roles and features, we get the below erro

I've read on many articles, that we need to have the set up for windows to find the required source files for 3.5 for this to proceed. But before going to the team who actually set up the box, is there something we may be missing here and any action or alternative that can be given a try?

Thanks.


texts: Cannot import database exported by phpPgAdmin

texts:
I have exported database with phpPgAdmin tool - format sql, structure and data.

When I try to import this sql using:

psql command \i filename.sql

After some time (file is 2GB) I get following error:

ERROR:  unterminated quoted string at or near ".. , '\x25504... "

with "arrow" pointing to \x.

Looks like \ is making a problem here...


texts:
I am evaluating log shipping as a solution to keep a permanent failover copy of our live database. It looks ideal.

We are using Filestream which is part of the reason to use it over mirroring.

I have a secondary database in the (Standby / Read-Only) state. In my tests which are on a small database I have been able to take it offline, copy the mdf and ldf files, create a new database and replace the mdf and ldf files.

This then gives me a copy of the database to test that the log shipping is working as expected, and the idea is that I want to test that the database will be in a state that come go back live straight away.

The problem comes when I go back to our live database and use log shipping. To test that it can failover I cannot copy away the mdf and ldf files because it is using Filestream, and those files won't include the Filestream data.

Suggestions for the testing? I would ideally like to keep the secondary database connected to log shipping because it is big and rebuilding it again wouldn't be ideal.


texts: Innobackupex (Xtrabackup) 2.4.8 incremental backups take the same time as full backup

texts:
We have our operational as well as decision making databases both running on Percona MySQL 5.7.10. The OS is Ubuntu 16.04. On these various database instances we have been using Percona Innobackupex (Xtrabackup) for daily and incremental backups.
   Both of these backup types take the same amount of time to finish. For our Data Warehouse, running on an AWS ec2 m4.2xlarge instance type, which is around 3 TB in size both the full and incremental take around 5-6 hours to finish. Shouldn't the incremental finish faster?

Based on the full backup taken a day before, I've tried testing the incremental backup numerous times and each time I found it to be taking the same time as the full backup.

Any help or pointers to this would of be of great help.


texts:
I am debugging an application that is working correctly on many PCs but not on one particular one.

The part of the application that is having an issue is an automated restore using dpimp from another machines backup which used expdp.

I am seeing two issues, the most concerning is in the function where it drops objects and synonyms in preparation for the schema restore.

The function queries all synonyms for synonyms owned by a particular user then loops through the results dropping each synonym.

The function logs each sql statement that it is about to execute to a log.

Here is a snippet:

CURSOR SYNONYM_LIST (v_owner   VARCHAR2) IS
SELECT synonym_name
FROM all_synonyms
WHERE owner = upper('pos')
AND table_name != 'SP_IMPORT_DATABASE'
AND table_name != 'SP_WRITE_DBMS_OUTPUT_TO_FILE'
AND table_owner = v_owner;

...then later on:

    IF upper(argProd) = 'PRODUCTION' THEN
    BEGIN
    OPEN SYNONYM_LIST (argTargetOwner);

LOOP
    FETCH SYNONYM_LIST INTO ls_synonym_name;

    EXIT WHEN SYNONYM_LIST%NOTFOUND;

    sqlstmt := 'DROP SYNONYM pos.'|| ls_synonym_name;
    dbms_output.put_line (sqlstmt);

    IF sqlStmt IS NOT NULL THEN
        EXECUTE IMMEDIATE sqlStmt;
    END IF;

END LOOP;

CLOSE SYNONYM_LIST;
sp_write_dbms_output_to_file('SP_IMPORT_DATABASE');
EXCEPTION
    WHEN OTHERS THEN
      v_code := SQLCODE;
      v_errm := SUBSTR(SQLERRM, 1, 64);
      DBMS_OUTPUT.PUT_LINE (v_code || ' ' || v_errm);
      select user into  cur_user from dual;
      DBMS_OUTPUT.PUT_LINE('Current user: ' || cur_user);
      sp_write_dbms_output_to_file('SP_IMPORT_DATABASE');
      CLOSE SYNONYM_LIST;
    END;
END IF;

Here is output of the script to the log:

2017-02-18 22:28:48,160 DBAUSER is starting SP_IMPORT_DATABASE.
2017-02-18 22:28:48,253 DROP SYNONYM pos.CAT_AWARD_ACCT_COUPON
2017-02-18 22:28:48,253 -1434 ORA-01434: private synonym to be dropped does not exist
2017-02-18 22:28:48,253 Current user: DBAUSER

The issue is this works on many other machines and this machine in particular is causing problems.

If I take the output SQL in the log, "drop synonym..." and run it in either sqlplus or sqldeveloper verbatim, as the same user (DBAUSER), I can drop the synonym no problem.

I should note, this function is called through a Java program on a JDBC connection.

I have also thought that maybe there was a case-sensitive issue, and tried quoting the "pos" as "POS" and "pos" as well as quoting the synonym name, but this results in no difference in the function.
When I take that quoted sql and run in sqldeveloper, it works the same as sit always does in sql developer.

We also have a Windows WIM image that the PCs are built from.
We decided to re-image this PC as we could not figure out the error.
We re-imaged the PC to new hardware and the exact same issue came up.
-To be noted is that there is a process after applying the WIM which changes the hostnames/data in tables/etc to correspond with the location this PC is at, so all PCs are not "identical" in the end, but should have identical setups.

Note these systems are using Oracle 12c 12.1.0.2 Enterprise.

Thanks


texts:
The problem of this issue has been identified and can be treated as resolved. (see the comments bellow)

I try to install a web-application on a server (debian) and on a local machine (ubuntu).

When I log-in the same error reproduces on both machines - odd timestamp interpretation. When a user logs-in certain data is stored in the database and one of those details is date and time.

The error shows a strange thing - looks like the date is getting written in YDM format, which doesn't correspond to the default postgresql datestyle. That's the postgresql error log :

2018-04-18 15:57:34.838 CEST [11031] user@db STATEMENT:  insert into log(login, nom_mod, log_date, comment, ip) values ($1, $2, $3, $4, $5) RETURNING log_id
2018-04-18 15:57:36.995 CEST [11033] user@db ERROR:  date/time field value out of range: "2018-18-04 15:57:36"
2018-04-18 15:57:36.995 CEST [11033] user@db HINT:  Perhaps you need a different "datestyle" setting.

I suspect that the system locale affects the database initialization. I recreated the cluster several times by following those steps like that :

sudo pg_createcluster --locale fr_FR.UTF-8 9.6 main

That created an unexpected 'positive' glitch - I found myself connected after refreshing the page. That's it, case solved ! I disconnected and tried to reconnect. Unfortunately the error reproduced.

This is the date command output on my local system :

  vendredi 20 avril 2018, 15:05:53 (UTC+0200)

And the system locale :

LANG=en_US.UTF-8
LANGUAGE=en_US
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC=fr_FR.UTF-8
LC_TIME=fr_FR.UTF-8
LC_COLLATE="en_US.UTF-8"
LC_MONETARY=fr_FR.UTF-8
LC_MESSAGES="en_US.UTF-8"
LC_PAPER=fr_FR.UTF-8
LC_NAME=fr_FR.UTF-8
LC_ADDRESS=fr_FR.UTF-8
LC_TELEPHONE=fr_FR.UTF-8
LC_MEASUREMENT=fr_FR.UTF-8
LC_IDENTIFICATION=fr_FR.UTF-8
LC_ALL=

That's the pertinent section from postgresql.conf :

# - Locale and Formatting -

datestyle = 'iso, dmy'
#intervalstyle = 'postgres'
timezone = 'localtime'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                                        # abbreviations.  Currently, there are
                                        #   Default
                                        #   Australia (historical usage)
                                        #   India
                                        # You can create your own file in
                                        # share/timezonesets/.
#extra_float_digits = 0                 # min -15, max 3
#client_encoding = sql_ascii            # actually, defaults to database
                                        # encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'                     # locale for system error message
                                        # strings
lc_monetary = 'fr_FR.UTF-8'                     # locale for monetary formatting
lc_numeric = 'fr_FR.UTF-8'                      # locale for number formatting
lc_time = 'fr_FR.UTF-8'                         # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.english'

Remark : I can't alter the database as suggested in this solution :

ALTER DATABASE database_name SET datestyle TO "ISO, YDM";

because that format YDM isn't conform with the postgresql datestyle.

According to the postgresql documentation LC_COLLATE and LC_TYPE can not be modified once a database is created, but that's not problematic since it's possible to recreate the database once those two variables are properly set.

https://www.postgresql.org/docs/current/static/locale.html

However, what's the right configuration ? Which locale variables should be configured and how should they be set ? Or the issue is elsewhere ?

Thanks for pointing out any suggestions about how to sort this out.


texts: Is there any way to speed up a query with 3x UNION on the same large table?

texts:
Assuming that the following query is legitimate and executes successfully, is there any way to speed it up?

SELECT foo from T1 --T1 is a huge (>5e6 rows) table with no indexes
UNION
SELECT bar from T1
UNION
SELECT baz from T1
UNION
SELECT qux from T1

The four columns shown above are all of type nvarchar(128), non-sparse and nullable. I am using SQL Server 2008. Thanks!

EDIT: Provided more info. I cannot add indices because it is a Stage table. Also, removing the DISTINCTs; I totally forgot about that.

Phil: How do I consolidate this into a single query?


texts: Join relation on itself and pull distinct rows

texts:
I have relation A (reviewer_id, book_id, trivalFields)
Suppose at only 1 or 2 reviews exist for the book_id.
I would like a result set that
review_id (first), review_id (second), book_id.
Example:

  johnson, smith, 'horton hears a who'
  johnson, smith, 'green eggs and ham'.

But not

  johnson, smith, 'horton hears a who'
  smith, johnson,  'horton hears a who'
  johnson, smith, 'green eggs and ham'.
  smith, johnson,  'green eggs and ham'.

I know how do something like this with brute strength code - but how to do it in pure SQL??

PostgreSQL - by the way.


texts: Inner procedure is rolled back but outer procedure is still committed

texts:
I have an outer procedure which calls 5 different inner procedures something like

Parent procedure(Tran/try/catch)
Inner sp1 (Tran/try/catch)
'
'
Inner sp5 (Tran/try/catch)

But in this case, if one of my inner procedures is rolled back , the procedure is still committed because there are no errors in the outer procedures. How can I overcome this problem ? Thanks !!

Sorry my bad. The code looks something like:
--INNER PROCEDURE

CREATE PROC spInner
AS
BEGIN

DECLARE @transactioncount INT
SET @transactioncount = @@TRANCOUNT

IF (@transactioncount > 0)
BEGIN

SAVE TRANSACTION savepoint1
END
ELSE
BEGIN
    BEGIN TRANSACTION

BEGIN TRY
    DROP TABLE IF EXISTS #temp
    CREATE TABLE #temp (id INT NOT NULL)
    INSERT INTO #temp (id)
    SELECT NULL
    INSERT INTO #temp (id)
    SELECT 1
    INSERT INTO #temp (id)
    SELECT 'A'

IF (@transactioncount = 0)
BEGIN
    COMMIT TRANSACTION
END

END TRY
BEGIN CATCH

IF (@transactioncount = 0)
BEGIN
    ROLLBACK TRANSACTION
END
ELSE IF (XACT_STATE() <> -1)
BEGIN
    ROLLBACK TRANSACTION savepoint1
END

    --logs errors into Error Log table
PRINT ERROR_MESSAGE()
END CATCH
END

--Outer procedure

CREATE PROC spOuter
AS
BEGIN

DECLARE @transactioncount INT
SET @transactioncount = @@TRANCOUNT

IF (@transactioncount > 0)
BEGIN
    SAVE TRANSACTION savepoint2
END
ELSE
BEGIN
    BEGIN TRANSACTION

BEGIN TRY

    Exec dbo.spInner

        IF (@transactioncount = 0)
    BEGIN
        COMMIT TRANSACTION
    END

END TRY
BEGIN CATCH

    IF (@transactioncount = 0)
    BEGIN
        ROLLBACK TRANSACTION
    END
    ELSE IF (XACT_STATE() <> -1)
    BEGIN
        ROLLBACK TRANSACTION savepoint2
    END

        --logs errors into Error Log table
    PRINT ERROR_MESSAGE()
END CATCH

END

In this case the errors caught in the inner procedure is not caught in the outer procedure and outer procedure is committed though there are errors in inner procedures


texts: How do I unload data from a database to a flat file?

texts:
I am aware of the concept of bulk insert wherein I load data from a flat file to a
database.

Now I want to unload data from a table to a flat file, i.e. I want to export data in a table to a flat file. Can anyone tell me how to do this?

I am using SQL Server 2008 R2.


texts:
Our database writing needs exceed the capacity of a single Postgres cluster (since each cluster can only support 1 writer server).

To combat this, we've sharded our data across 8 separate Postgres clusters, which we call shards. These shards are completely independent Postgres clusters, that just happen to have the same schemas. Our data is such that there is no need for any cross-shard joins, foreign keys, etc. (For example, all records are tied to a customer, and all of a customer's records reside within a single shard, so there's no need to do cross-shard foreign key look ups.)

This approach has been working successfully in production, but there's one hiccup: aAny time that we need to make a change to the schema, we need to manually execute the DDL on every shard. To be clear: we don't need to synchronize the actual records, only schema changes, such as adding a column to a table.

Obviously, such a manual process is error-prone, and it's only a matter of time until our shard's schema start de-synchronizing via an accumulation of small human errors.

Is there an established way to automate this process?


texts: postgresql cannot drop a table when autovacuum holds lock on it

texts: Autovacuuming process holds RowExclusiveLock and ShareUpdateExclusiveLock on the table, which I want to drop. I read that autovacuum should be canceled automatically, when submitting DROP TABLE command, but it doesn't happen. Maybe I have wrong configuration of postgresql? Furthermore I don't have any delete or update operations of those tables — only inserts and drops. Can I disable autovacuum on those particular tables?


texts: Replace column by LEFT OUTER join to save space

texts:
I have a table t1 with 100M row. For column A, 95% of the rows have value NULL0, the other records have values from 1 to 9.

To shrink my table, I'm thinking about moving column A to table t2. t2 would contain only rows where A is not NULL0. The "original" table would then be created by doing a LEFT OUTER JOIN between t1 and t2.

Questions:

Is this a common approach?
Is there a name for this pattern?
Are there any downsides except that I have to handle two tables now in my application code?

(I use MySQL, but I guess it doesn't matter so much for this question.)

Update: As it turns out, NULL was not a good choice for my example (since depending on db engine, it might not need any storage at all). I rewrote the first sentence of my question to make it more clear what I'm asking for.


texts:
I am pretty new to SQL and I have this maintenance job that keeps failing and I am not sure how to troubleshoot this. I have troubleshot other job failures with google help to success but this one is eluding me. Any help is appreciated. Here is the log...BTW..is there an easier way to read these logs, or does it just take time and experience?

Date        2/1/2015 4:00:00 AM
Log     Job History (SERVER MAINTENANCE.REBUILD)

Step ID     1
Server      SERVER
Job Name        SERVER MAINTENANCE.REBUILD
Step Name       REBUILD
Duration        04:01:38
Sql Severity        0
Sql Message ID      0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted       0

  Progress: 2015-02-01 06:00:01.80     Source:
  Rebuild Index Task      Executing query "ALTER INDEX
  [DTA_reports_indexcolumn_index2] ON [d...".: 40% complete  End Pro...
  The package execution fa...  The step failed.


texts: Is there a way to grant SELECT for every database (including new ones) at a server level?

texts:
I have a unique (well, I think it is unique) situation where databases (yes, entire databases) can be created more or less dynamically.

Ignoring the obvious issue with that, I need to be able to allow an AD account to read any of the data in any of the databases.

Acknowledging the security issue surrounding all of this, is there a way to grant this just once at the server level, rather than having to grant constantly when a new database pops up?
I would prefer to just keep it at a datareader level.

Specifically:
1. databases are created dynamically without advance knowledge of the DBA 2. Reporting jobs iterate through these databases and fail if they encounter a new one.

I am using SQL Server 2008R2 and SQL Server 2014.


texts: Show all results for past N "insert batches"


texts:
I am building a products site with php and mysql(no shopping cart).

I have 400k products, the main site with 2 methods for query.

query for products types, all the query by menu, cloud tags (no custom form search)
query for products details, custom form search, search with products name, details.

Each products have these fields: name, details, add_date, catalog, brand. The main problem is each products would have multi catalogs. The total catalogs have more than 600. And each product would have 3-10. For example:

(name) SAUCONY LADY PROGRID RIDE 3 RUNNING SHOES
(details) This ProGrid Ride 3 is a neutral running shoe designed for the keen female runner who is competing at a decent level.
(add_date) 1333128354
(catalog) sport women USA pink leather
(brand) nike

So in my require, how to design this db?

I am not good at mysql, I have 2 ideas.

Use Innodb, make 2 dbs

db1

id | name      | details      | add_date   | catalog             | brand
1  | some name | some details | 1333128354 | 101 201 301 401 501 | nike
2  | some name | some details | 1333128355 | 101 202             | nike
3  | some name | some details | 1333128357 | 102 202 334 401     | puma

db2

cid | catalog
101 | sport
201 | man
202 | woman
301 | USA
302 | Franch

Use select * from db1 where catalog like '%101%' to get all the sport shoes, use Rlike to do the custom form search.

Use MyISAM, make 3 dbs

db1

id | name      | details      | add_date   | brand
1  | some name | some details | 1333128354 | nike
2  | some name | some details | 1333128355 | nike
3  | some name | some details | 1333128357 | puma

db2

pid | catalog
1   | 101
1   | 201
1   | 301
1   | 401
1   | 501
2   | 101
2   | 202
3   | 101
3   | 202
3   | 334
3   | 401

db3

cid | catalog
101 | sport
201 | man
202 | woman
301 | USA
302 | Franch

Use SELECT * FROM db1 INNER JOIN db2 ON db1.id=db2.pid WHERE db2.pid='101' to get all the sport shoes, Add fulltext index to db1.name and db1.details, use fulltext search to do the custom form search.

Or other better ideas? I need a fast way and occupy lower CPU and RAM. many thanks.


texts:
We are on SQL Azure v12 Standard/S2 Service Tier and are not satisfied with the performance so I am trying to measure where the bottleneck is. I realize that we could be hitting the limits of the S2 service tier. S2 has 50 DTU and 2,570 transactions per minute limitation.

Memory Pressure:
I looked at the sys.dm_exec_query_memory_grants and there are no pending memory grants and the requested memory is being granted.
Page Life expectancy is about 600.

DTU
The average DTU doesn't seem to be more than 50% unitized. avg_cpu_percent and avg_data_io_percent are rarely over 50%. avg_memory_usage_percent stays at 99%.
I got this information from the sys.dm_db_resource_stats

Transactions Per Sec
How do I measure Transactions/Sec in SQL Azure? The DMV sys.dm_os_performance_counters for Transactions/Sec doesn't have any values for the User databases. it has values for system databases.


texts:
I have a query like this

SELECT m.title
FROM Movie AS M
WHERE
(SELECT MAX(r.stars)
 FROM rating AS r
WHERE r.mID = M.mID);

I want to show the MAX(r.stars) in my result set, what am I doing wrong?


texts: I was taking some courses to understand better the way Cassandra works (at academy.datastax.com) and by what I gathered it came to me that it is a Key Value Database because of the way it stores data based on a hash of the key and distributes to nodes accordingly. But everywhere I look people say Cassandra is a Column-Based Database and I can't see why.


texts:
I need query of looping in stored procedure for table like this:

Table Matrix Report

| Nama  | Motor   |
|-------|---------|
| Abdul | Vario   |
| Abdul | Beat    |
| Mei   | Supra   |
| Leli  | Supra   |
| Budi  | Megapro |
| Ali   | CB150   |

Output :

| Nama   | Vario | Beat | Supra | Megapro | CB150 |
|--------|-------|------|-------|---------|-------|
| Abdul  | 1     | 1    | 0     | 0       | 0     |
| Mei    | 0     | 0    | 1     | 0       | 0     |
| Leli   | 0     | 0    | 1     | 0       | 0     |
| Budi   | 0     | 0    | 0     | 1       | 0     |
| Ali    | 0     | 0    | 0     | 0       | 1     |

I need dynamic query because if I added new data, they automatically sum up.


texts:
I have a very typical table of user information for my application, with an average set of columns (ints, varchars, etc).

In this table are two varchar(100) fields:  emailAddress and emailAddress2.

I'm extremely perplexed at why one of my queries is so slow (5+ seconds) and in testing some alternates they are so fast (<= 0.02 seconds).

This query is fast (matching on emailAddress field):

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress = 'user@domain.com'

This query is also fast (matching on emailAddress2 field):

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress2 = 'user@domain.com'

This query is NOT fast (matching on either emailAddress or emailAddress2 field):

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND (u.emailAddress = 'user@domain.com' OR u.emailAddress2 = 'user@domain.com')

I have 2 indexes, each on the emailAddress and emailAddress2 field.

(EDIT) Here is the EXPLAIN output:

id                   1
select_type          SIMPLE
table                u
type                 ref
possible_keys        active,emailAddress,emailAddress2
key                  active
key_len              1
ref                  const
rows                 166808
Extra                Using where

Any idea why the 3rd query above is so slow? Suggestions for doing it another way? I need to match on a handful of fields and then get all users EXCEPT ones where their email address is in emailAddress or emailAddress2 (the queries above are simplified for my post).

This is driving me crazy! (and negatively affecting my app's performance).

Thanks


texts: How to determine a schema version at runtime

texts:
Is there a portable way or best practice to access a version number for a schema at runtime?

Rationale: you have multiple systems (development, QA, etc.) where you push schema changes from one system to the next. Say you're not sure if you already have the latest schema on the QA system, how can you find out the version of the current schema (preferably automated with a script)?

I could imagine having

a convention on using a table comment (COMMENT ON TABLE with Oracle, is that a standard?) to version every table
a special table in the schema storing version information

How do you do this? Is there a best practice?


texts: What are some practical use cases for memory tables?

texts:
Trying to learn mysql and having tons of other material to learn, I'm considering skipping memory tables, since they have many special characteristics to consider especially that they vanish, after server restart.
In production environments, what are they used for?


texts: Update column with data from another table

texts:
I am working on complicated problem, but I will simplify it to this problem.

I have two tables

A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

and I want to update the third:

C [ID, column1, column2,column3]

I am updating another third table using this query.

UPDATE C
  set column1=t.firstTab, column2=t.secondTab, column3=t.thirdTab
  from (select A.column1 as firstTab, B.column2 as secTab,
              (A.column1 + B.column2) thirdTab
        from A, B limit 1; ) as t ;

I got:

UPDATE 0

When I run this query:

select A.column1 as firstTab, B.column2 as secTab, (A.column1 + B.column2) thirdTab
            from A, B limit 1;

I got results. Am I missing something ?

Sample data: http://sqlfiddle.com/#!15/e4d08/5


texts:
I am using SQL Server 2014 SP2 with AG, running this command:

sqlcmd -S ABC-ISELL -E -d ABP_SFA_BMB -K ReadOnly

Where ABC-ISELL is the listener name, I am getting this error:

Sqlcmd: 'KReadOnly': Unknown Option. Enter '-?' for help.

sqlcmd -? shows there is no -K option, it`s just this:

[-k[1|2] remove[replace] control characters]

What could be wrong here?


texts:
Is it possible to force the order in which a result set is grouped? I have a table with multiple languages in it, and I'm doing a GROUP_CONCAT to get a comma separated list of the product name in each language, for each product_id. However it doesn't seem possible to get MySQL to return that concatenated string in any particular order of language_id's. What I'd like is to be able to order the grouping by language id, so that they'll always come out in a pre-determined order.

Is this possible? If so, how? If not possible within the SELECT statement, is there a modification I can make to the table to adjust how the GROUP BY would order the result?


texts: Which timeout(s) are causing my linked server query to time out?

texts:
We have two databases on linked SQL Servers that are partially synchronized. Occasionally, one side goes down for an upgrade, then the other goes down for an upgrade, and then we re-synchronize.

Upgrades can take upwards of two days. Re-synchronizing two days worth of data is a large operation, so we broke it into batches. During testing, we completed twenty batches successfully before this message appeared in the query window in SSMS:

  OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer" returned message "Query timeout expired".

We restored, tested it again, and the operation was successful. We tested on a slower pair of machines, and it timed out on the first batch. We concluded that environmental noise (other processes on the server) and random variance is causing our query to occasionally take too long.

Here is the query that is timing out:

MERGE INTO dbo.MyTable AS target
USING (
    SELECT localData.ID, remoteData.Name AS Name, remoteData.Value AS Value
    FROM OPENQUERY (
        MyLinkedServer,'
        SELECT TOP 500000 Name, Value
        FROM MyDatabase.dbo.MyTable
        WHERE Synced = 0'
    ) AS remoteData
    JOIN dbo.MyTable AS localData ON localData.Name = remoteData.Name
    WHERE localData.Synced = 0
) AS source
ON source.ID = target.ID
-- We need to pull this record
WHEN NOT MATCHED BY target THEN
    INSERT (
        Name, Value, Synced
    ) VALUES (
        source.Name, source.Value, 1
    )
-- We already have this record
WHEN MATCHED THEN
    UPDATE SET target.Synced = 1
-- There is an OUTPUT clause here that collects the remote IDs of all of
-- the rows that were synced and stores them in a table. We update the
-- remote table before we do the next batch, so that we don't sync the same
-- 500000 rows over and over again.
;

The query above represents one batch. It replaces batchablequery below. We separated it out like this because we sync more than one table.

DECLARE @count INT = 500000
WHILE @count > 0
BEGIN
    batchablequery
    SET @count = @@ROWCOUNT

    INSERT INTO MyLogTable
    VALUES(GETDATE(), 'We finished a batch of size ' + CONVERT(varchar(20), @count))

    CHECKPOINT
END

We could fix the query timeout in one of three ways:

Improve query performance

It spends most of its time waiting with a PAGEIOLATCH_SH

Decrease or throttle batch size
Increase timeout

Before we choose a solution, we want to fully understand which timeouts are affecting us. We are confused by which ones are applicable to our query. Here are our current settings, and some notes we took from MSDN:

Server Remote Query Timeout

Ours is set to 600

Linked Server Connection Timeout

Ours is set 0, so sp_configure Remote Query Timeout Option is used instead.

Linked Server Query Timeout

Ours is set to 0, so sp_configure Query Wait option is used instead.

Query Wait

Ours is set to -1, so 25 times the estimated query cost is used.
The estimated query cost doesn't have a unit of measure on it...

Which setting or settings are the cause of our timeout?


texts:
I have a table with a column for ID, Date and Frequency of occurrence for that particular date and ID.

ID    date          Frequency
1     2014-05-18      5
1     2014-05-19      4
1     2014-05-20      25
2     2014-05-20      7
3     2014-05-18      4
3     2014-05-20      1
4     2014-05-18      6

I'm looking to insert the dates for each ID that aren't currently there (assuming that the date ranges from 2014-05-18 to 2014-05-20) and add a value of 0 in Frequency for those.

For example, you can see that

ID 1 has an observation for all three dates;

ID 2 is missing 2014-05-18 and 2014-05-19;

ID 3 is missing 2014-05-19;

ID 4 is missing 2014-05-19 and 2014-05-20.

Therefore, the output should look like this

ID    date          Frequency
1     2014-05-18      5
1     2014-05-19      4
1     2014-05-20      25
2     2014-05-18      0
2     2014-05-19      0
2     2014-05-20      7
3     2014-05-18      4
3     2014-05-19      0
3     2014-05-20      1
4     2014-05-18      6
4     2014-05-19      0
4     2014-05-20      0

I've attempted the following from this question. However, that will only add dates with 0 Frequency that aren't present throughout the entire table instead of on an ID level.

DECLARE @s DATE = '20140518', @e DATE = '20140520';

SELECT c.date, Frequency = COALESCE(a.Frequency,0)
FROM dbo.Calendar AS c
LEFT OUTER JOIN dbo.table AS a
ON c.date = s.[date]
WHERE c.d >= @s
AND c.d < DATEADD(DAY, 1, @e);


texts: We are running SQL Server 2016 and cannot view the list of reports in the browser when we go to http://servername/reportserver. When we go to that link in the browser, it asks for credentials. When I've used my own, it simply gives me the credentials pop-up again with no error. This repeats until the browser gives an HTTP 400 error. When I try the sa credentials, I only have to do it once but it still gives the HTTP 400 error. I can connect to the server in SSMS and can even query the Catalog table from the database. I can also save reports onto the server using Report Builder.


texts:
I am having a bit of trouble getting accurate results from a query that looks to me as if it should work. RDBMS is MySQL.

I am trying to get the max last login date from a union select on three databases for Atlassian products JIRA, Confluence and Stash. There should only be a single entry in the results for each user (the max(Last_Login) is supposed to get the maximum last login date and show a result if that max(Last_Login) is older than today - 90 days.

I summarize the output of the union query then select from that aggregate so that I can get the max login date that is 90 days ago or prior.

The problem: The query seems to work for the most part, but there are a handful of cases where a user shows activity in Confluence, Stash or JIRA interfaces that is more recent than what I have here.

Here is the query I am attempting to use:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(ca.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM confluence.cwd_user u
  LEFT JOIN confluence.cwd_user_attribute ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
  JOIN confluence.cwd_directory d ON u.directory_id = d.id
where u.active = 'T'
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc


texts:
I been using SSMS for diagramming my databases and I got used to create the key constraints with this tool. Now I started thinking, while going through related chapter of a book, "...maybe this is not such a good practice"

Is there any downside towards this practice? That is defining key constraints using the Diagram Designer on SSMS?


texts:
I am designing a database for an web application, in this system there are multiple users. Consider,

Public (Buyer).
Merchant.
Tele Marketing Executive(TME).
Marketing Executive(ME).
Lead Generator...etc.

I have decompose the User table into UserType and UserRoles, according to me, this is known method.

The issue is when I trying to design for sales dashboard, I need to get the values of Tele Marketing, Marketing and Merchant.

The Sales board will be consisting of following fields.

Sale No.
TME Name.
ME Name.
Merchant Name.
Total Amount.
Payment Mode...etc.

For sales_dashboard table I need to use the User_id as a Foreign key, but my doubt is can I get the TME Name, ME and Merchant details from User table by using that foreign key.

FYI:

Also I need to find the Users who belongs to Merchant but same doubt both Users, Merchants and staffs in a same User table, how can I identify the particular staff is belongs to particular Merchant?

Any method to do this, I hope this is my mistake sorry for my lack of knowledge, I am new to Database.

Thanks in advance.


texts:
I just want to know how they came to 8kb when striking a balance between:
smaller page = more page splits
bigger page = longer to look through to find value

I expect there are many more pros and cons to having a bigger page but if we could start again would we pick 8kb again?

I'm not saying it's the wrong decision and I know it would be a massive change to the product to alter the size.

Also I know 8kb was chosen a fair few years ago now and I wonder does it still make sense when rows are likely to be longer now due to new data types?


texts: How to read "CPU (ms/sec)" in the "Recent Expensive Queries" of Activity Monitor?

texts:
Is CPU (ms/sec) the cpu time in ms in previous second? If it reaches 1000, does it means that it uses 100% CPU?

However, on my 8 cores server, the % Processor Time is usually very low like several percent when the most expensive query in the "Recent Expensive Queries" reaches 1000.


texts:
I have a table that stores time-series data and has about 8 million rows. The table structure is as follows:
Timestamp | ComponenentID | Parameter1 | Parameter2 | Parameter3
I have a clustered index on the Timestamp and ComponentID column. (This is also my primary key)
The query I am trying to run is:-
SELECT * FROM table
WHERE Timestamp BETWEEN '2020-01-01'
AND '2020-01-02'
AND ComponentId = 5

When I run this query an RID lookup seems to be taking place. I have read that this only happens when the index does not cover everything but since I have a clustered index I thought it should itself cover everything and prevent the lookup. How do I prevent this lookup from happening?
Query 1 (Timestamp+ComponentId):-

Query 2 (Timestamp):-


texts: Storing large image files in database vs system files

texts:
I need to store many large photos that will be uploaded daily in my application and retrieve those with id. There is no need to any special queries.
What is the best approach for storing this files consider to time that number of the images pass a million and side of images exceed the server disk size. What is the most manageable solution?

Using SQL FileStream or FileTable and storing files in separate database.
Using file system and saving images as files.
Using a FTP server.


texts: Get all field names from all tables in Sybase DB

texts: I've been tasked to get all the fields in all tables in a Sybase DB. Anyone know how to do this? Thanks in advance.


texts:
I'd like to setup the following infrastructure:

  PSQL 9.6 in USA (master)

  PSQL 9.6 in Europe (slave)

  PgPool-II in Europe

  App Server in Europe (it reads the DB in 90% of time).

My question is: Can I set write-only and read-only databases in pgpool? I'd like to send all my reads to slave and every write (but ONLY write) to master. Is this possible? Or in master/slave mode the read queries are sended to both master and slave?


texts:
I am trying to import a xml (actually a docx file) to a sql server 2008 database. I am almost a novice in XML programming. I googled a lot but almost all the examples there are with simple xml file. Here the xml file is little complex(please see below). Can you please give me some idea how I should create the table for this XML and what query should I run in sql server. I need values for all the tags e.g. w:rsidP,w:rsidRDefault,w:rsidR of w:p,w:pStyle,w:bookmarkStart,w:t tags etc.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<w:document xmlns:ve="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml">
<w:body>
<w:p w:rsidR="00EF42E0" w:rsidRDefault="00EF42E0" w:rsidP="00EF42E0">
<w:pPr><w:pStyle w:val="Heading1"/>
</w:pPr><w:bookmarkStart w:id="0" w:name="_Toc212523610"/>
<w:r>
<w:t>Summary</w:t>
</w:r>
<w:bookmarkEnd w:id="0"/>
</w:p>
<w:p w:rsidR="00EF42E0" w:rsidRDefault="00EF42E0" w:rsidP="00EF42E0"><w:pPr><w:pStyle w:val="mainbodytext"/><w:ind w:right="-694"/><w:rPr><w:b/><w:bCs/></w:rPr></w:pPr><w:r><w:rPr><w:b/><w:bCs/></w:rPr><w:t>What is the Group Defined Practice for Integrity Management?</w:t></w:r></w:p>
<w:p w:rsidR="00EF42E0" w:rsidRDefault="00EF42E0" w:rsidP="00EF42E0"><w:pPr><w:pStyle w:val="mainbodytext"/></w:pPr><w:r><w:t xml:space="preserve">This Practice is derived from the GP Group Standard, GRP 01 January 2006, </w:t></w:r><w:proofErr w:type="gramStart"/><w:r><w:t>Integrity</w:t></w:r><w:proofErr w:type="gramEnd"/><w:r><w:t xml:space="preserve"> Management.  In developing QMS it has been possible to embed much of the content of the IM Standard directly into the Group Essentials statements.  For elements 2, 7, 8 and 9 of the Standard it was possible to do that in their entirety and therefore content of those elements are not repeated within this Practice.</w:t></w:r></w:p></w:body></w:document>


texts:
I am working on an application which records data from different forms. New forms can (and will be) constructed dynamically by an admin. I am wondering what would be the correct way to save this data in a database? My first guess would be to use EVA, but considering all the bad publicity I am a bit hesitant.

Let's say I have two forms:

#form 1
description = TextField()
score = IntegerField()

#form 2
feedback = TextField()
NOR_score = IntegerField()

There is no way of predicting the number of possible attributes. I am playing around with two ideas, but maybe you'll be able to suggest a much better approach.

Idea I:

unique_id | entity | attribute   | value
1         | form 1 | description | test123
2         | form 1 | score       | 90
3         | form 2 | feedback    | blabla
4         | form 2 | NOR_score   | 5

Idea II:

unique_id | entity | value
1         | form 1 | {"description": "test123", "score":"90"}
2         | form 2 | {"feedback": "blabla", "NOR_score":"5"}

Thanks in advance for any suggestions.


texts:
I am trying to run the following DB2 command through the Python pyodbc module:

DB2 export to C:\file.ixf of ixf select * from emp_hc

I am successfully connected to the DSN using the pyodbc module in Python and it works fine for select statements, but when I try to execute the following command from the Python IDLE 3.3.2:

cursor.execute(" export to ? of ixf select * from emp_hc",r"C:\file.ixf")

I get an error:

  pyodbc.ProgrammingError: ('42601', '[42601] [IBM][CLI
  Driver][DB2/LINUXX8664] SQL0104N An unexpected token "db2 export to ?
  of" was found following "BEGIN-OF-STATEMENT". Expected tokens may
  include: "". SQLSTATE=42601\r\n (-104) (SQLExecDirectW)')

When I use

cursor.execute(" export to C:\file.ixf of ixf select * from emp_hc")

I get another error:

  Traceback (most recent call last): File "", line 1, in cursor.execute("export to C:\myfile.ixf of ixf select * from emp_hc") pyodbc.ProgrammingError: ('42601', '[42601] [IBM][CLI Driver][DB2/LINUXX8664] SQL0007N The character "\" following "export to C:" is not valid. SQLSTATE=42601\r\n (-7) (SQLExecDirectW)')

Am I doing something wrong? Any help will be greatly appreciated.

From what I came to know DB2 export is a command run in shell, not through SQL via ODBC.

can you please give me some more information on how to run the command in the shell i am confused and what does that mean ? any guide or small quick tutorial will be great


texts: Do same PostgreSQL Limits apply for PostgreSQL 11 version?

texts:
I'm writing a research paper in which I've been using implementation of PostgreSQL, version 11.8 and what bothers me it's Docs about DB Limitations (at https://www.postgresql.org/docs/12/limits.html) which is given ONLY for versions 12 and newly released 13. Do these same limits apply to version 11.8 ORR only to those two mentioned versions?
Any guide/help is more than welcome.


texts: How do dirty reads at both the transaction and table level provide increased performance?

texts:
I ran the following code past our architect as I thought there may have been a tautology between the isolation level and table hint:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT  p.Forename ,
        g.Descriptor AS PersonGender
FROM    dbo.Person p WITH ( NOLOCK ) ,
        dbo.Gender g WITH ( NOLOCK )
WHERE   p.GenderId = g.Id;

I am assured by the architect (and I have no reason to doubt him) that this is for high volume environments and helps the optimiser better decide about how to improve the performance of the query.

So, some questions:

How does it do this?
Which versions of SQL Server support this?
Is this a documented or undocumented feature?


texts: Finding matching suggestions with multiple records from different "users"

texts:
I'm not even entirely sure how to phrase my question because it is rather complicated. I am trying to implement a feature that will help crowd source information for one of our systems, and the system should validate these "suggestions" automatically. There are certain criteria that determine a "valid" suggestion:

3 suggestions
3 different "users"
Same combination of "adaptors"

For example, Tom works for Acme Co. and suggests using Adaptor A + Adaptor B for this particular scenario. Likewise, Jon works for Acme Inc. and suggests the same Adaptor A + Adaptor B for this particular scenario. Finally, Bob works for Acme Enterprises, and he too suggests the same Adaptor A + Adaptor B for this particular scenario.

Upon Bob submitting his suggestion, the system would be able to determine that there are 2 matching suggestions from 2 different companies, constituting a valid suggestion, and would then perform some action (this action is outside of the scope of this issue).

What I have done so far is create a table to store suggestions:

id
user_id
vehicle_id
service_id

Each suggestion may have one or more "adaptors" so I am using a pivot table:

suggestion_id
adaptor_id

Finally, because we are really concerned about the "company" the user works for (remember suggestions are only considered valid if they come from 3 different companies), so I have composed a view to join this information:

suggestion_id
user_id
company_id
vehicle_id
service_id
adaptor_id

Here is an example of what some of this data would look like:

The highlighted rows a potentially valid suggestion, with the exception that the suggestions from company 58 should only be counted as 1 suggestion because they come from the same company. Furthermore, suggestion 87 only contains 1 of the adaptors found in the other 2 matching suggestions. If suggestion 87 included both adaptor id 669 and 936, these suggestions would be considered valid.

My question is how to query this information? I currently am trying something like this:

select distinct
`company_id`,
`vehicle_id`,
`service_id`,
`adaptor_id`
from `service_guide_suggestions`
where `user_id` != 2
and `company_id` != 1
and `vehicle_id` = 30149
and `service_id` = 3
and `adaptor_id` in (669,936)

I need to be able to answer some question as "Do we have at least 3 suggestions for the same vehicle and service, from 3 different companies, with matching adaptors?"

My apologies if my question is not clear or is lacking enough information. I'm happy to provide as many details as possible for any feedback. Many thanks!

UPDATE
Trying danblack's suggestion yields these results:


texts: Getting intervals between time from all succeeding rows? (Time is in text data type)

texts:
NAME    DATE         TIME      INTERVAL
a      10/19/2015    5:00 PM
a      10/20/2015    5:00 PM    86400
a      10/20/2015    5:30 PM    1800

This is easy to do in Excel. But considering we have a data of 2 million+ rows, we can only do it to the only  DB resource we have which is Access.

How do we do it on Access? We need the interval of seconds between all the time each row per NAME.. So for all "A", we need the second interval between its first time, and second time, the second time, to third time, and so on.

So for "B", its all the same too.

What makes it extra hard is i stored all the data types as TEXT. So I cant just use a datediff or timediff function. :(


texts: How Count Row Value in SQL Server database?

texts:
I have a table called TimeSheet :

Card_No    Name    1    2    3    4
123        GGG    A    P    P    P
123        GGG    A    P    A    P
123        GGG    A    P    P    P
123        GGG    A    A    A    P

I want to count total A in one row. Result should be like this

Card_No    Name    1    2    3    4    TotalA
123         GGG    A    P    P    P    1
123         GGG    A    P    A    P    2
123         GGG    A    P    P    P    1
123         GGG    A    A    A    P    3

How can I do it ? Please help me.


texts: Setting up Master - Slave replication: error connecting to master (Error_code: 2003)

texts:
I'm configuring this across two different servers on two different hosting companies.

Here is the tutorial I've followed for setting this up: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

The master my.cnf settings are as follows:

bind-address = 00.000.000.00
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydatabase

On the slave:

server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = mydatabase

Everything looks good until I try and check mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 000.000.000.00
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'slave_user@000.000.000.00' - retry-time: 60  retries: 3
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 180104 23:22:36
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

I've checked the error log as well and can see this:

2018-01-04T23:05:10.948738Z 2 [ERROR] Slave I/O for channel '': error connecting to master 'slave_user@000.000.000.00:3306' - retry-time: 60  retries: 6, Error_code: 2003

I've checked the firewall and the port, which looks correct. I'm not sure what else I can check?


texts:
Goal:

Extract address fields from a users table into a separate addresses table
linked by foreign key.

I have created the structure below, and have millions of rows to deal with. I
need to do this once, but relatively quickly (ie, I can't loop over every row
in my Rails code)

The query below does work to extract the address fields into new rows in the
addresses table. But I can't figure out how to link the new rows back to the
users table that the data came out of.

Structure:

Before:

User
  login
  address_line_one
  address_line_two
  address_city
  address_state
  address_zip

After:

User
  login
  address_id

Address
  id
  line_one
  line_two
  city
  state
  postal_code
  country

What I have so far:

INSERT INTO addresses (line_one, line_two, city, state, postal_code, country)
SELECT address_line_one,
  address_line_two,
  address_city,
  address_state,
  address_zip AS zip,
  'US'
FROM users
WHERE
  address_line_one IS NOT NULL ||
  address_line_two IS NOT NULL ||
  address_city     IS NOT NULL ||
  address_state    IS NOT NULL ||
  address_zip      IS NOT NULL

PROBLEM

I don't know how to get the address_id back into the users table


texts: SQL Job PowerShell task fails same command succeeds as user

texts:
Simple requirement: delete files on remote path older than 20 days.

PS C:\Users\Administrator> Get-ChildItem `
    -Path \\FileServer\SQLBackups\SQLServer\ `
    -File -Recurse -Force |
  Where-Object {
    ($_.LastWriteTime -lt (Get-Date).AddDays(-20))
} | Remove-Item

The above command succeeds (vanity backticks & linebreaks added for readability).

The relevant SQL 2016 Job (backticks and linebreaks not added, but the command text is the same)

@subsystem=N'PowerShell',
@command=N'Get-ChildItem -Path \\FileServer\SQLBackups\SQLServer\ -File -Recurse -Force | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-20))}|Remove-Item',
@proxy_name=N'XPProxy'

The error information returned by PowerShell is:

  'A parameter cannot be found that matches parameter name 'File'.

No permissions issues (refer proxy_name);
SQL Powershell version:

Major  Minor  Build  Revision

-----  -----  -----  --------
4      0      -1     -1

SQL @@VERSION:

  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Below is the actual parameter list of the Get-ChildItem function. Why can't SQL use the -File parameter?

Get-ChildItem
   [[-Path] <String[]>]
   [[-Filter] <String>]
   [-Attributes {ReadOnly | Hidden | System | Directory | Archive | Device | Normal | Temporary | SparseFile | ReparsePoint | Compressed | Offline | NotContentIndexed | Encrypted | IntegrityStream | NoScrubData}]
   [-Depth <UInt32>]
   [-Directory]
   [-Exclude <String[]>]
   [-File]
   [-Force]
   [-Hidden]
   [-Include <String[]>]
   [-Name]
   [-ReadOnly]
   [-Recurse]
   [-System]
   [-UseTransaction]
   [<CommonParameters>]


texts: Dates, Times and Timezones - How are they incorporated in Interval?

texts:
Does interval '1 month' incorporate month differences and leap years when calculating date/datetime boundaries?  If so, how does it work?

I was asked this question, and I'm not sure how to answer it.  I am using Postgres as a reference example, I assume it has its own time offset and calculation integration.  But how does this take into account timezones, leap years, and other oddities of the calendaring?

I want to know if there's anything in a the SQL spec that defines how the behavior will occur.


texts: How to combine three table to find Avg,High,Low value in mysql?

texts:
I have three different tables such as

  prac_test
UID | net_wpm
5        58
5        67

 multi_test
UID | net_wpm
5        88
5        97

 shadow_test
UID | net_wpm
5        8
5        77

How to combine these three tables and get avg,High,Low net wpm for UID  5

Thank in advance


texts:
I run queries in Hive...I wanted to create a temporary tables to save intermediate results of a complex query. Here is my query:
CREATE TEMPORARY TABLE test AS
SELECT * FROM my_table
LIMIT 100

The query ran through without reporting an error. But when I did a select statement from the table I got this error "Table test is not found".
Is temporary table supported in Hive? I read docs and posts here and there. Most say yes so I do not know what is wrong.


texts: What is the best way to store X509 certificate in PostgreSQL database?

texts:
I'm working on web authenticate system where users will digitally sign random tokens and this will be checked with X509 certificates stored on the server.

Therefore I have to store several X509 certificates (PEM or DER format) in PostgreSQL database. Sounds easy, but I want to have possibility to search certificates with subject, issuer, notBefore, notAfter and similar criteria.

My idea is to have following columns in database: X509data, notAfter, notBefore, subject, issuer etc. Than I will create object (in SQL alchemy) representing X509 certificate with methods like add_new_X509(), find_X509(search criteria)  etc. So whenever I will add new certificate with method add_new_X509() it will automatically reads from certificate all data and fill up rest of the columns and put raw certificate into X509data column.

Unfortunately this solution have two disadvantages:

I will store the same information twice (in X509 certificate itself and in separate columns for easy searching)
Whenever I want to read X509 certificate, my application will have to crosscheck notAfter, notBefore, subject, issuer with information stored in original certificate (this is for security reasons, in case someone would try to modify this fields).

So.. anybody have better idea, or suggestion? Maybe somebody see any other security issue that can arise with this solution?


texts: How to rename MySQL users without logging into MySQL?

texts:
I wanted to rename the default Linux username on a Lubuntu 16.04 VM (from osgeo live) from user to newuser. I followed answers in this question to rename the Linux user. But after renaming, I cannot log into mysql, and mysql generates the following error:

mysql ERROR 1045 (28000): Access denied for user 'newuser'@'localhost' (using password: NO)

My guess is that there is a default MySQL user created by the VM creators which is the same as the Linux user (i.e. user). My question is:

How can I change the MySQL username to newuser) to be consistent (now that I cannot log in)?

-- Update --

I executed sudo service mysql restart --skip-grant-tables --skip-networking as suggested (after deleting /var/log/mysql/error.log) and the log has the following diagnosis:

2017-01-12T02:25:23.664857Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-01-12T02:25:23.664951Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-01-12T02:25:23.804276Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-01-12T02:25:23.805216Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.16-0ubuntu0.16.04.1) starting as process 2574 ...
2017-01-12T02:25:23.808154Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-01-12T02:25:23.808182Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-01-12T02:25:23.808186Z 0 [Note] InnoDB: Uses event mutexes
2017-01-12T02:25:23.808189Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-01-12T02:25:23.808192Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-01-12T02:25:23.808194Z 0 [Note] InnoDB: Using Linux native AIO
2017-01-12T02:25:23.808365Z 0 [Note] InnoDB: Number of pools: 1
2017-01-12T02:25:23.808440Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-01-12T02:25:23.809445Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-01-12T02:25:23.815490Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-01-12T02:25:23.816764Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-01-12T02:25:23.828144Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-01-12T02:25:23.860141Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-01-12T02:25:23.860201Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-01-12T02:25:23.903728Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-01-12T02:25:23.904430Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-01-12T02:25:23.904442Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-01-12T02:25:23.904815Z 0 [Note] InnoDB: Waiting for purge to start
2017-01-12T02:25:23.955251Z 0 [Note] InnoDB: 5.7.16 started; log sequence number 2632377
2017-01-12T02:25:23.956218Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-01-12T02:25:23.963845Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-01-12T02:25:23.968294Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170112  3:25:23
2017-01-12T02:25:23.972653Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-01-12T02:25:23.972715Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2017-01-12T02:25:23.972741Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2017-01-12T02:25:23.972799Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2017-01-12T02:25:24.006451Z 0 [Note] Event Scheduler: Loaded 0 events
2017-01-12T02:25:24.007120Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.16-0ubuntu0.16.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2017-01-12T02:25:24.686565Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)


texts: I'm dealing with s db.r3.4xlarge RDS instance with 2000GB all purpose SSD (theoretically capable of up to 6000 IOPS), I'm restoring a bunch of table backup files of over 200GB. There are 26 simultaneous threads running, but Write IOPS is stuck at about 800. Queue depth is at 2 (give or take) and CPU is at 4-5%. Does this make sense? Why isn't the server using more IOPS available to it?


texts: Is mongoexport more optimized for querying than running the same query with a driver like "mongodb" for nodejs?

texts:
I'm in charge of building a nodejs module that dumps varied amount of data from a mongodb into a file and then put it into S3 (sometimes a single document, sometimes thousands, like a whole collection).
The project where this module will be already uses "mongodb" driver for different use cases.
I was told that for larger dumps, using mongoexport could be better in performance, than running the query within nodejs, but this solution would imply spawning a new process and depending on a different tool (mongoexport), it certainly adds complexity to the solution.
My concern isn't the performance in terms of speed, but in terms of overhead to the DB.
Is it true that mongoexport will dump a whole collection faster (and with less "effort") than simply querying and writing the result with a fs stream?
are there guidelines or best practices to do things like this?


texts: Persistent problems importing CSV files via pgAdmin 4 to PostgreSQL 12 on Mac OS - Failing with Exit Code -6

texts:
I've been trying for several days to import CSV files into a PostgreSQL 12 database hosted on Mac OS 10.15.5, using the Import/Export tool in pgAdmin 4. I am consistently getting an error message reading: "Failed (exit code: -6)" and despite extensive internet searching have not been able to determine what that exit code means or why the process is failing.
Here is a screenshot of the error:

Troubleshooting steps I've tried so far:

Moving the CSV files into directories that should be accessible to the postgres server to rule out permissions problems.
Adjusting the data types of the columns in my empty table to rule out a data-type mismatch.
Creating new CSV files with very simple test entries and a new table with matching columns.
Restarting the machine and relaunching PostgreSQL 12 and pgAdmin4.
Lots of other suggestions and steps recommended on this site and other related forums.

Anyone run into this problem before or have some insights into what might be happening? Any help you can offer would be greatly appreciated. Happy to add other details and provide more information as needed.
Thanks in advance for your time and assistance.


texts: How to change DB file location with Replication

texts: I need to move few DB files to another location on same server due to low disk space. Replication is present with that DB and this scenario is on subscriber server. Is there any way to do this without doing reconfiguration?


texts: Error: Unable to write inside TEMP environment variable path

texts:
I am installing PostgreSQL 9.2.4-1-windows.exe on Windows 7 64 with Service Pack 1.

I get the following, rather famous it seems, error:

  There has been an error.

  Unable to write inside TEMP environment variable path.

This occurs immediately upon launching.  I see the postgresql splash, then this error occurs.

Everything I have found on the interwebs so far, i have tried.  It includes:

Disabled all anti-virus software.  I have also stopped everything in the startup.
Checked file associations for .VBS scripts.  Nothing irregular (Windows Script Host registered)
Windows Script Host is Enabled.
I've tried installing as Administrator and other users that have admin rights.
I have created the key HKLM\SOFTWARE\PostgreSQL\Installations manually and give it full rights.
I have given Everyone full access to C:\Users\[username]\AppData\Local\Temp and c:\temp

Basically everything in the top 10 google hits.

I am working with the 32 bit version, but the 64 bit version install fails with the same error.  I'm able to install successfully on other machines with similar config.

What else can I try?

Install log file:

  Log started 09/20/2013 at 11:55:16
  Preferred installation mode : qt
  Trying to init installer in mode qt
  Mode qt successfully initialized
  Executing C:\Users\chad\AppData\Local\Temp/postgresql_installer_67b274715d/temp_check_comspec.bat
  Script exit code: 0

  Script output:
  "test ok"

  Script stderr:

  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Data Directory. Setting variable iDataDirectory to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Base Directory. Setting variable iBaseDirectory to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Service ID. Setting variable iServiceName to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Service Account. Setting variable iServiceAccount to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Super User. Setting variable iSuperuser to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Branding. Setting variable iBranding to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Version. Setting variable brandingVer to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Shortcuts. Setting variable iShortcut to empty value
  Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 DisableStackBuilder. Setting variable iDisableStackBuilder to empty value
  [11:55:21] Existing base directory:
  [11:55:21] Existing data directory:
  [11:55:21] Using branding: PostgreSQL 9.2 (x86)
  [11:55:21] Using Super User: postgres and Service Account: NT AUTHORITY\NetworkService
  [11:55:21] Using Service Name: postgresql-9.2
  Executing cscript //NoLogo "C:\Users\chad\AppData\Local\Temp\postgresql_installer_67b274715d\prerun_checks.vbs"
  Script exit code: 1

  Script output:
  Input Error: There is no script engine for file extension ".vbs".

  Script stderr:
  Program ended with an error exit code

  Error running cscript //NoLogo "C:\Users\chad\AppData\Local\Temp\postgresql_installer_67b274715d\prerun_checks.vbs" : Program ended with an error exit code


texts: SQL Server allow viewing of jobs by job category

texts:
I know how to assign or create "categories" for jobs.  But is there a way to actually view jobs by category by default.  I do not want to see all the jobs just listed within SQL Server Agent.  I'd like to be able to have a view where I expand "SQL Server Agent" and I first see all the categories and I can expand each category to see the SQL job.  Is this possible?

I already know I can right click the jobs folders and do "Manage Categories" and view jobs by category there, but that is not what I am looking for. I am looking for a default view to show jobs by category.


texts: Which is more efficient b+ tree or search (binary or linear)?

texts:
SELECT A
FROM T
WHERE M > 1000 AND M < 5000;

I am unable to judge which of the below is best suited for the above query:

Linear search
Binary search on M
B+-tree with index on M
B+-tree with index on A

Which is the best answer and why?

I faced this question recently in an exam and I couldn't find the solution. I chose option 3 (B+ tree with index on M) because the query is not sorted on M and B+ tree indexing on A will make it difficult compared to M (I guess).

I want some clarification of the concepts, because I am a little confused about when each of the above can be effectively used.

To be more clear: when a query optimizer chooses optimization plans which of the given options will it pick? That is what the intention of the question was.


texts: Does using inequality () or NOT in where clause lead to SQL ignoring the index?

texts:
I found this article that talks about avoiding using <> operator in where clause because optimizer ignores the index.
https://www.mssqltips.com/sqlservertutorial/3203/avoid-using-not-equal-in-where-clause/
Is this absolutely true?
What is the best way to handle this? How to avoid using <> ?


texts: improve database design for monitoring application

texts:
I need assistance with my DB design, I have a monitoring script that produce the following data every minute , and produce stats about virtual machine performance.

Array
(
    [timestamp] => 2015-05-05T18:11:00Z
    [vm_name] => i-2-20-VM
    [memory_internal_free] => 3516900
    [vbd_xvdd_read] => 0
    [vbd_xvdd_write] => 0
    [vif_0_rx] => 0
    [vif_0_tx] => 0
    [memory] => 4294967296
    [vbd_xvda_read] => 0
    [vbd_xvda_write] => 0
    [cpu0] => 0.000100
    [cpu1] => 0.000100
)
Array
(
    [timestamp] => 2015-05-05T18:11:00Z
    [vm_name] => i-2-24-VM
    [memory_target] => 4294967296
    [vbd_xvdb_read] => 0
    [vbd_xvdb_write] => 0
    [vbd_xvda_read] => 0
    [vbd_xvda_write] => 0
    [vif_0_rx] => 0
    [vif_0_tx] => 0
    [memory] => 4294967296
    [cpu0] => 0.000100
    [cpu1] => 0.000100
)

I'm trying to store the following info into my database, I have create single table

I have created table as follows

CREATE TABLE `md_metrics_status` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `vm_name` varchar(50) DEFAULT NULL,
  `memory_target` varchar(255) DEFAULT NULL,
  `vif_2_rx` varchar(255) DEFAULT NULL,
  `vif_2_tx` varchar(255) DEFAULT NULL,
  `memory` varchar(255) DEFAULT NULL,
  `vbd_xvda_read` varchar(255) DEFAULT NULL,
  `vbd_xvda_write` varchar(255) DEFAULT NULL,
  `vbd_xvdd_read` varchar(255) DEFAULT NULL,
  `vbd_xvdd_write` varchar(255) DEFAULT NULL,
  `vif_1_rx` varchar(255) DEFAULT NULL,
  `vif_1_tx` varchar(255) DEFAULT NULL,
  `vif_0_rx` varchar(255) DEFAULT NULL,
  `vif_0_tx` varchar(255) DEFAULT NULL,
  `cpu0` varchar(255) DEFAULT NULL,
  `cpu1` varchar(255) DEFAULT NULL,
  `vif_3_rx` varchar(255) DEFAULT NULL,
  `vif_3_tx` varchar(255) DEFAULT NULL,
  `memory_internal_free` varchar(255) DEFAULT NULL,
  `vbd_xvdb_read` varchar(255) DEFAULT NULL,
  `vbd_xvdb_write` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=162339 DEFAULT CHARSET=utf8

Now this table has grown so large, and searching for records between specific timestamps is very slow.

I'm unable to add an index on timestamp field, because I get many duplicate records, as you can see in the example.

What is wrong with this table design? how can I improve this setup?

I have full control over the database, and over the script that produces the data.

Your input is highly appreciated.

Thank you


texts: Adding secondary replica from different subnet for AlwaysON

texts:
If I understood the configuration of setting up a multi subnet AG setup where 2 nodes are in one datacenter and the 3rd node in another, I was under the assumption that we require 2 listner ip's - 1 listner ip for each subnet. Is this not the case? I was going through this article today, where the author no where mentions about creating another listner for a multi subnet AG setup. Can someone please clarify if we require a different listner ip for the second subnet or not?

Refering to https://www.mssqltips.com/sqlservertip/4598/add-secondary-replica-to-existing-sql-server-alwayson-availability-group/#comments

Thanks!


texts: SQL VSS returns complete block size of the ldf files

texts:
I am using SQL VSS Writer to take backups of SQL Databases. What I actually do is take backups of files in form of mdf and ldf files by creating snapshots and taking backup of that snapshot.
The issue which I came across is while taking Differential backup of ldf and mdf files, where for ldf file I was unable to get the changed blocks using "partial_files" method in vss. So suppose I have ldf file of size 10GB and only 100 changed rows which constitutes to 1 GB of data, VSS still returns complete block size of the ldf files. This leads us to scan complete file to check actual changed blocks which in turn increases backup time. Can you please let me know if you have faced this issue and how you solved?
Also, can you please suggest if I can switch to VDI type of backups or is VDI deprecated?


texts: How does Apache Cassandra handle dynamic column set conflicts?

texts:
I know that each column in Apache Cassandra has a timestamp attached and that read conflicts for a single column is resolved deterministically by looking at timestamp or by comparing the value.

Let's say I add a column to a dynamic column set. I write this column to a single node. I then later add another column to the same column store, but this time to another node. How does Apache Cassandra merge these two? Will both columns exist after the merge?


texts: UPDATE on big table in PostgreSQL randomly takes too long

texts:
I'm trying to figure out why an UPDATE statement takes too long (>30 sec).
This is random, i.e. in most cases it finishes in under 100msec. However, sometimes (randomly) it takes > 30sec to complete.
Some specifics:

I'm using PostgreSQL 12 (actually, AWS Aurora)
I'm trying this in a database with no traffic, so it's not being affected by any other queries running in the same time. I'm also monitoring the logs to see if anything else is running, and I don't see anything.
I've tried REINDEX ing, VACUUMing (and VACUUM ANALYZE), with no improvement
I've checked for locks, (log_lock_waits) and I don't see anything.
The query is performed in a loop (from a Python app). It performs ~5000 queries, and at some point some of them, they don't seem to follow a pattern, take a huge time to complete.
I've tried running them in batches, but again, some batches randomly take too long.
The size of the table is kinda big, ~10000000 rows and ~25 indexes.

The query:
UPDATE "my_table" SET "match_request_id" = 'c607789f-4816-4a38-844b-173fa7bf64ed'::uuid WHERE "my_table"."id" = 129624354;

The output of EXPLAIN (ANALYZE VERBOSE BUFFERS COSTS)
 Update on public.my_table  (cost=0.56..8.58 rows=1 width=832) (actual time=34106.965..34106.966 rows=0 loops=1)
   Buffers: shared hit=431280 read=27724
   I/O Timings: read=32469.021
   ->  Index Scan using my_table_pkey on public.my_table  (cost=0.56..8.58 rows=1 width=832) (actual time=0.100..0.105 rows=1 loops=1)
         Output: (...)
         Index Cond: (my_table.id = 130561719)
         Buffers: shared hit=7
 Planning Time: 23.872 ms
 Execution Time: 34107.047 ms

Note that this is EXPLAIN ANALYZE. I'm baffled since while the cost is really low, the actual running time is huge!
I'm trying to understand if this is expected and if I can improve the situation somehow. Any ideas would be welcome, I'm kinda running out!
EDIT: Adding some more info requested by comments:
A query plan for a "normal" update
 Update on public.my_table  (cost=0.43..8.45 rows=1 width=837) (actual time=2.037..2.037 rows=0 loops=1)
   Buffers: shared hit=152 read=1
   I/O Timings: read=1.225
   ->  Index Scan using my_table_pkey on public.my_table  (cost=0.43..8.45 rows=1 width=837) (actual time=0.024..0.026 rows=1 loops=1)
         Output: (...)
         Index Cond: (my_table.id = 129624354)
         Buffers: shared hit=4
 Planning Time: 1.170 ms
 Execution Time: 2.133 ms
(9 rows)

The table has 23 indexes, 6 foreign-key constraints. 3 BRIN, 1 GIN. The rest B-Tree. I'm not sure how to check for fastupdate on the GIN index, the output of \d+ index_name is
 Column | Type | Key? | Definition | Storage  | Stats target
--------+------+------+------------+----------+--------------
 search | text | yes  | search     | extended |
gin, for table "public.my_table"


texts: Using SMO from PowerShell, is there a way to state that a jobstep output file should be appended and not overwritten?

texts:
I deploy multiple SQL Server jobs to a fleet of servers using a PowerShell script. I can create the job steps, success fail action, and even the output file. This all works on multiple servers. What I cannot determine is how to specify that the output file is to be appended to instead of overwritten.

The following is the section from my main script. $Job is populated in a previous step.

$JobStep = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($Job, 'MyJobStepName')
$JobStep.ID = 1
$JobStep.SubSystem = 'PowerShell'
$Command = 'dir d:\'
$JobStep.Command   = $Command
$JobStep.OnSuccessAction = 'GotoNextStep'
$JobStep.OnFailAction    = 'GotoNextStep'
$JobStep.OutputFileName  = 'D:\MyJobFile.txt'
$JobStep.Create()

I have been using this MSDN page as a reference.

I modified one of the steps using SQL Server Management Studio and traced the result. This looks promising. I can issue a SQL command after I create the jobsteps.

EXEC msdb.dbo.sp_update_jobstep @job_id=N'12156dcb-c8cd-4267-901a-55175dd44960', @step_id=1 ,
        @flags=2


texts: Switching between queries with "date  now" slows down the performance a lot

texts:
In our MySQL database we have a table with about 12 million posts. We are using a datetime that is indexed to get posts from it.

There is never any UPDATE, INSERT or DELETE made to the table.
What we do is running three different queries in a row:

SELECT CONCAT('datetime', 'Z') as datetime FROM table WHERE
datetime >= FROM_UNIXTIME(1479423600)
AND
datetime <= FROM_UNIXTIME(1479510000)
ORDER BY datetime

SELECT CONCAT('datetime', 'Z') as datetime FROM table WHERE
datetime > FROM_UNIXTIME(1479423600)
LIMIT 1

SELECT CONCAT('datetime', 'Z') as datetime FROM table WHERE
datetime <= FROM_UNIXTIME(1479510000)
LIMIT 1

If we only runt the first two, it works super fast, or if we run one repeatedly after itself, it takes 0.0001 second. But as soon as we search in the opposite direction it gets super slow, because I guess MySQL needs to rebuild the INDEX. Even if we limit the result to 1 post it takes 5 seconds, and we are running this on a dedicated server.

We will get a new server with SSD discs and 64 GB RAM but my colleague says that will not improve the speed enough. What he has suggested is that we create another table and revert the posts there. I would like to find another solution, there must be a way MySQL can store two differently structured indexes for the same table.

Why is the request so slow in some cases?

Answer: The concat slows down everything crazy much!

I simply removed the concat and all queries is super fast now!


texts: many 'unauthenticated user', 'connecting host', in show full processlist (Mysql 5.1.5)

texts:
I have a mysql slave on AWS/EC2 that receives peak CPU randomally.

I run show full processlits and I get many (few 100s) of processes like this:

'1141944', 'unauthenticated user', 'connecting host', NULL, 'Connect', NULL, 'login', NULL

What is it ?

Any idea on how I can detect what causes the server to suddnely climb to 200 load average?

Thanks


texts:
I've got a view which accidentally (don't know how) lost it's name.

Now I can not drop it any longer because pgadmin III throws an error
Error: syntax-error at the end of input
Line 1:DROP VIEW public

Trying to rename it causes the error
Error: syntax-error at "to"
Line 1:ALTER TABLE RENAME TO NewName

The view definition is

CREATE OR REPLACE VIEW  AS
 SELECT ...
   FROM table a;

ALTER TABLE OWNER TO postgres;
GRANT ALL ON TABLE  TO postgres;


texts: For BCNF decomposition, is it okay to have a relation with none of the original functional dependencies?

texts:
Given the relation R[c, f, g, h, e, j, a, b, d, i] with the below functional dependencies I have come up with the below solution. However, I am unsure about it, considering R8 has none of the listed functional dependencies even though it has the LHS of some. I followed the instructions for decomposition into BCNF but I am unsure it this is the correct solution?

Functional Dependencies

a → b
{ c, a } → d
c → e
f → { g, h, i, j }

Solution

In BCNF:
R1[f, g, h, i, j]
R3[a, b]
R5[c, a, d]
R7[c, e]
R8[c, f, a]


texts:
I am trying to SUBSTR the first 3 character in use this query -> CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1
But it return me this ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s". May i know what wrong in my query? I tried to use To_NUMBER or To CHAR, it also return me same error. Appreciate if someone could help on this issue.
Below is my query:
SELECT RMSTMP_PNG.ota_activity.EQP_ID,
SUM(CASE WHEN (RMSTMP_PNG.ota_activity.MESSAGE='Load lot success.' AND RMSTMP_PNG.ota_activity_lotinfo.KEY = 'Quantity') THEN 1 ELSE 0 END) AS LOT_ID,
SUM(CASE WHEN (RMSTMP_PNG.ota_activity.MESSAGE='Load lot success.' AND RMSTMP_PNG.ota_activity_lotinfo.KEY = 'Quantity') THEN TO_NUMBER(RMSTMP_PNG.ota_activity_lotinfo.VALUE) ELSE 0 END) AS QUANTITY,
CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1
FROM RMSTMP_PNG.ota_activity
FULL OUTER JOIN RMSTMP_PNG.ota_activity_lotinfo ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID
WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE >= to_date('27-Sep-2020')
AND RMSTMP_PNG.ota_activity.MODIFIED_DATE < to_date('27-Sep-2020') + 1
group by RMSTMP_PNG.ota_activity.EQP_ID,RMSTMP_PNG.ota_activity_lotinfo.VALUE, RMSTMP_PNG.ota_activity_lotinfo.KEY
order by RMSTMP_PNG.ota_activity.EQP_ID


texts: I manage to connect to Azure Analysis Services from SSMS, but not from SSIS

texts:
I'm new to the Microsoft Server Suite.
I've downloaded SSMS and connected to Azure Analysis Services from it. I'm able to query my data using mdx without any problems.
However, I actually intend to build an ETL pipeline with the AAS cube as one of the sources. So I installed SSIS and have been trying to connect it to the AAS cube.
I first add "Analysis Services Processing Task" to the package. The result looks ok (when I click on "Test connection" the result is positive). But when I click on "Add", it doesn't detect any cubes (there are two on the AAS server specified):

I assumed it worked anyway, but I can't query the cube no matter how I try to do that. I added "Execute SQL task", but when I run it, it gives me an error:

The error message is:
An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  Hresult: 0x80004005  Description: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  Hresult: 0x80004005  Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  Hresult: 0x80004005  Description: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ".
Error: 0xC00291EC at Execute SQL Task, Execute SQL Task: Failed to acquire connection "asazure://northeurope.asazure.windows.net/xxxx". Connection may not be configured correctly or you may not have the right permissions on this connection.
Task failed: Execute SQL Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\Users\49176\source\repos\Integration Services Project1\Integration Services Project1\Package.dtsx" finished: Failure.
The program '[18664] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
Any ideas?


texts:
I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with administrator rights.
It's a third-party product, and they are slow to answer questions. I know the data is inside that database, so I want to do a little bit of reverse-engineering.
Given a table name, is it possible to get a list of the names of the columns in that table?
For example, in SQL Server, it's possible to dump a table into a reusable CREATE statement, which textually lists all the columns the table is composed of.


texts: PostgreSQL: relation would be inherited from more than once

texts:
Assume I have a schema which initially have several yearly sliced data like:

schema: land_price
  - land_price_1990
  - land_price_1991
  ...
  - land_price_2010

Now I would like to create a parent table so that I can better manage them. I firstly try

ALTER TABLE land_price.land_price_2010 INHERIT land_price.land_price_parents;

It works, however, when I try to alter the second table, it fails and returns

ERROR: relation "land_price_parents" would be inherited from more than once

So in this case, how can I solve this problem?

p.s. Here is the version information:

select version()
PostgreSQL 9.3.23 on x86_64-unknown-linux-gnu (Ubuntu 9.3.23-2.pgdg14.04+1), compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit


texts:
I have a question about joining two tables.

Schema

CREATE TABLE [dbo].[DCString] (
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCDistributionBoxID] [bigint] NOT NULL,
    [CurrentMPP] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey3] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

ALTER TABLE [dbo].[DCString]
    ADD CONSTRAINT [FK_DCString_DCDistributionBox] FOREIGN KEY([DCDistributionBoxID])
    REFERENCES [dbo].[DCDistributionBox] ([ID])

CREATE TABLE [dbo].[StringData](
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey4] PRIMARY KEY CLUSTERED ( [TimeStamp] DESC, [DCStringID] ASC)
)

The [StringData] table has the following storage stats:

Data space: 26,901.86 MB
Row count: 131,827,749
Partitioned: true
Partition count: 62

Usage

I now want to JOIN the data in the [StringData] table with the data from the [DCString] table.

Something like:

declare @begin datetime = '22.02.2016';
declare @end datetime = '23.02.2016';
declare @dcStringID bigint = 6658;

SELECT [DCString].[ID], [StringData].[TimeStamp]
FROM [StringData]
RIGHT OUTER JOIN [StringData] ON [StringData].[DCStringID] = [DCString].[ID]
WHERE [StringData].[ID] = @dcStringID
AND [StringData].[TimeStamp] >= @begin
AND [StringData].[TimeStamp] < @end;

What I expect on searched date range where matching data in the [StringData] table exist is this:

ID   | TimeStamp
6658 | 22.02.2016 10:00:00
6658 | 22.02.2016 11:00:00
6658 | 22.02.2016 12:00:00

...and on a searched date range where no matching data in the [StringData] table exist is this:

ID   | TimeStamp
6658 | NULL

Question

What I get on a searched date range where no matching data in the [StringData] table exist, is a 0 rows result. Why?

I simply want always to get all [DCString].[ID]s. Whats wrong with my JOIN or did I mess up completely?

Update 1 (related to the answer of @Aaron Bertrand):

I tried your way already, but I have to cancel testing the query because after 10min it is still running. It looked like this:

declare @begin datetime = '22.02.2016';
declare @end datetime = '23.02.2016';
declare @dcStringID bigint = 6658;

SELECT [DCString].[ID], [StringData].[TimeStamp]
FROM [StringData]
LEFT JOIN [DCString]
    ON [StringData].[DCStringID] = [DCString].[ID]
    AND [StringData].[TimeStamp] >= @begin
    AND [StringData].[TimeStamp] < @end
WHERE [StringData].[ID] = @dcStringID;


texts: Are 'per second' Performance Counters measured over a certain period of time?

texts:
This may be a very silly question, but when I'm looking at the current value of a 'per second' counter in PerfMon, for example SQL Statistics\Batch Requests/sec - how is that value actually measured?

Let's say I use PowerShell's Get-Counter method to return a result for a specific server, as follows:

(Get-Counter -ComputerName $server -counter $countername -SampleInterval 1 -MaxSamples 1).CounterSamples.CookedValue

And this returns: 32

How can that value actually be contextualized? Is it an "average of 32 batch requests per second in the last x minutes"? Or is it "in the last second there were 32 batch requests"


texts: Need help to build SQL query for a complex scenario

texts:
I need help to build an SQL query for a slightly complex scenario. I have an interface with the following dropdowns.

Cuisine: Any, Chinese, Indian, Italian, French

Delivery type: Any, Delivery, Pickup, Dine-In

Meal type: Any, Breakfast, Lunch, Dinner

Cuisine Table

CuisineId  | Type
-------------------
     1     | Chinese
     2     | Indian
     3     | Italian
     4     | French

Delivery type Table

DeliveryTypeId | Type
--------------------------
         1     | Delivery
         2     | Pickup
         3     | Dine In

Meal type Table

    MealTypeId | Type
--------------------------
         1     | Breakfast
         2     | Lunch
         3     | Dinner

Food Table

    Food            CuisineId   MealTypeId      DeliveryTypeId
---------           -------     ---------       -------------
Cakes               NULL         NULL            NULL
Pizza               3 (Italian)  NULL            NULL
Noodles             1 (Chinese)  2 (Lunch)       NULL
Butter Chicken      2 (Indian)   3 (Dinner)      1 (Delivery)
Ice cream           NULL         2 (Lunch)       2 (Pickup)
Soup                NULL        NULL             2 (Pickup)
Drinks              NULL        NULL             1 (Delivery)

Please note: In the above table, NULL represents the selection of Any from the dropdown.

The logic here is as follows:

Cuisine: if the option is selected as Any or if the selected option doesn't have a matching value in the corresponding column in Food table, then show items that have NULL in Cuisine column.

Delivery Type: default should be Delivery / NULL unless Pickup or Dine In is selected

Meal type: Show only NULL (Any) items unless any specific option is selected.

For example, please find the sample output based on the selections.

Cuisine     Meal type       Delivery Type       Output
-------     ----------      -------------       ------
Any         Any             Any                 Cakes,Drinks
Any         Any             Delivery            Cakes,Drinks
Any         Lunch           Any                 NO RECORDS
Any         Any             Pickup              Soup
French      Any             Any                 Cakes,Drinks
French      Any             Delivery            Cakes,Drinks
French      Lunch           Any                 NO RECORDS
French      Any             Pickup              Soup
Chinese     Any             Any                 NO RECORDS
Chinese     Lunch           Any                 Noodles
Indian      Any             Any                 NO RECORDS
Indian      Dinner          Any                 Butter Chicken

How can I build an SQL query for the above scenario. Any help is highly appreicated. Thanks in advance.

The query will be used in a stored proc and the parameters passed will be @cusineType (like Chinese or French), @deliveryType (like Delivery or Pickup) and @mealType (like Breakfast or Lunch) or NULL for any of these.

Script for the above tables with data here: script.sql


texts:
I have the following schema in my database

Neighbors: ( central_id, neighbor_id)
Data: (central_id, value)

This schema is for a convolution algorithm which for each central_id I have 6 neighbors(it is hexagonal grid)
for example here is a part of my Neighbors table

Central_id|Neighbor_id
0|1
0|2
0|3
0|4
0|5
0|6

and here is my data

central_id|value
0|20
1|1
2|6
3|1
4|6
5|1
6|0

I want to join these tables in a way that I can sum value for each central_id
my final result is sth like this

central_id|sum
0|(1+6+1+6+1+0=15)

Here is some of my tries

this query gives me the central_id|neighbor_id|value

select nb.c ,ac.c,ac.value
from
 neighbors as nb
inner join data as ac on nb.neighbour_id=ac.central_id

but it's result is wrong, since value is 20 which is value of central_id.
this is my second try

select c as nb,value from data where c in(
      select nb.c
from
 neighbors  as nb
inner join data  as ac on  nb.neighbour_id=ac.central_id
where  and ac.c=0
)

this query result is correct. It returns neighbour_id and it's value, but I can not sum them based on central_id
If I could end up with such result my problems would be solved

central_id|neighbour_id|value(of each neighbour_id not central_id)
0|1|1
0|2|6
0|3|1
0|4|6
0|5|1
0|6|0

Can some one give me some hints about fixing this problem?thanks


texts: Using HASHBYTES() yields different results for nvarchar and a variable

texts:
I'm using server-side hashing to transmit passwords, then running PBKDF2 in the database to store the hashed password + salt combination.

Hashing nvarchar(max) and a @variable holding the same value yielded different results with the HASHBYTES() function.

DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'

SELECT HASHBYTES('SHA1', 'password5baa61e4c9b93f3f0682250b6') AS NVARCHAR_INPUT,
       HASHBYTES('SHA1', @hash) AS VARIABLE_INPUT

Yields the following:

NVARCHAR_INPUT                             | VARIABLE_INPUT
0xA03BEF0E3EC96CC7C413C6646D3FEC6174DA530F | 0x74B55C42E1E0AB5C5CDF10B28567590B240355C3

This is SQL Server 2012. This particular database is running SQL Server Express, so I'm also curious if this question is version agnostic.


texts: Removing a FAILED node from repmgr cluster

texts:
How do I remove failed node from a PostgreSQL cluster?

postgres@db-prod-ms47ox-10-3-104-92:~$ repmgr -f /etc/repmgr/repmgr.conf cluster show
Role      | Connection String
* master  | host=db-prod-ms47ox-10-3-105-192.vandelay.io
  standby | host=db-prod-ms47ox-10-3-104-92.vandelay.io
  FAILED  | host=db-prod-lu8spc-10-3-104-145.vandelay.io

Running repmgr cluster cleanup does not remove this node and it appears to stop me from adding a new node into the cluster.

This is with repmgr 2.0 and postgres 9.1