texts: How can I tell if an index is being used to sort in MySQL?

texts:
I have a query with an ORDER BY clause which uses a column which is the last column on an index which is being used in the WHERE clause, essentially of the form:

SELECT
  cols
FROM
  tables
WHERE
  col_1 = x
  AND col_2 = y
  AND col_3 = z
ORDER BY col_4

and the index is created on columns (col_1, col_2, col_3, col_4) in that order.

When I profile the query over 99% of the time is spent in the "Sorting result" state. col_4 is a timestamp column if that makes any difference. I understand that ORDER BY can only use an index under certain circumstances, but I'm still a bit mystified as to precisely when the optimiser would do so.


texts: How to migrate SQL Server 2014 to SQL Server 2019 on Linux using AG

texts:
I would like to migrate SQL Server 2014 on Windows to SQL Server 2019 on Linux using while running AG.

Is there an option to Add the SQL Server 2019 to the same AG and once the time comes gracefully fail over to the SQL Server 2019 version?

I currently have 3 nodes on the AG using Asynchronous mode (primary and two standby nodes).

In addition what is the suggested clustering method that I should be using in Linux?
Is there something like WSFC or can I use other Linux clustering method?

And finally I am using EC2 at AWS.


texts: Unable to convert a table to hypertable due to unique index error

texts:
I am creating a table using Flask SQL Alchemy as follows:

class Price(db.Model):
    __tablename__ = "prices"
    id = db.Column(db.Integer, primary_key=True)
    country_code = db.Column(db.String(2), nullable=False)
    value = db.Column(db.Float(precision=2), nullable=False)
    start = db.Column(db.DateTime(timezone=True), nullable=False)
    end = db.Column(db.DateTime(timezone=True), nullable=False)
    price_type = db.Column(db.String(32), nullable=False)

and then I want to create the table and convert it into hypertable by doing:

def register_timescale():
    result = db.session.execute(
        "select exists(select * from timescaledb_information.hypertable " +
        " where table_name='prices')")
    is_hypertable = [row[0] for row in result]
    if is_hypertable[0] is False:
        db.session.execute(
            "SELECT create_hypertable('prices', 'start', chunk_time_interval=>INTERVAL '1 day')")
        db.session.commit()

def create_app(app_config):
    app = Flask(__name__)

    app.config.from_object(app_config)

    register_extensions(app)
    register_blueprints(app)
    with app.app_context():
        db.create_all()
        register_timescale()
    return app

I want to create a hypertable of the table prices with the start column being time_column_name as described at create_hypertable and the chunk_time_interval should be 1 day. Meaning, one chunk must have data of 1 day

Example:

One chunk must contain data where start value is 2020-04-19 00:00:00+02 and end value is 2020-04-20 00:00:00+02 (from row 1 to 24 in the below image) and then the second chunk must contain value from 2020-04-20 00:00:00+02 till 2020-04-21 00:00:00+02 (from row 25 to 48 in the below image) and so on.

When I do this, I am getting the following error:

  Exception has occurred: DatabaseError (psycopg2.DatabaseError) cannot
  create a unique index without the column "start" (used in
  partitioning)

  [SQL: SELECT create_hypertable('prices', 'start',
  chunk_time_interval=>INTERVAL '1 day')] (Background on this error at:
  http://sqlalche.me/e/4xp6)

Then, I tried changing the register_timescale() function as follows:

def register_timescale():
    result = db.session.execute(
        "select exists(select * from timescaledb_information.hypertable " +
        " where table_name='prices')")
    is_hypertable = [row[0] for row in result]
    if is_hypertable[0] is False:
        db.session.execute(
            "ALTER DATABASE mydb SET timezone TO 'Europe/Berlin'")
        db.session.execute("SELECT pg_reload_conf()")
        db.session.execute("ALTER TABLE prices ADD PRIMARY KEY (id, start)")
        db.session.execute(
            "SELECT create_hypertable('prices', (id, start), chunk_time_interval=>INTERVAL '1 day')")
        db.session.commit()

And this gives me the following error:

  Exception has occurred: ProgrammingError
  (psycopg2.errors.InvalidTableDefinition) multiple primary keys for
  table "prices" are not allowed

  [SQL: ALTER TABLE prices ADD PRIMARY KEY (id, start)] (Background on
  this error at: http://sqlalche.me/e/f405)

My data looks as follows

Can someone please point out the mistake I am doing?

Thanks


texts:
I am very new to SSIS. I have inherited a bunch of databases and SSIS packages.  I notice many jobs utilizing many different packages.  When I look at an existing job and try to see where a package it is using is located I just see that it is in the "SSIS Packages" folder.

I know you can either deploy a package to the file system or as a database object(?).  How can I tell which type of deployment a package has taken?

Also, in an existing job, an associated SSIS solution where multiple projects contain multiple packages utilized by this job, there is one package I needed to modify. It isn't currently scheduled as part of a step of the job.  When I go to add the new step, I don't see as an option that I can choose this package. I assume it must not be deployed.  So how do I deploy it and how do I make sure I deploy it the same way the other packages in this job were deployed? (Really - how do I not mess things up!?)


texts: Oracle create user with privileges to grant references

texts:
I want to create an Oracle user who can grant references to other users.
Do I need to make him sysdba for that, or is there any other way?


texts: Is it possible for a SELECT-only stored procedure to deadlock on itself?

texts:
Environment is Microsoft SQL Server 2012. Is it possible for a stored procedure consisting exclusively of SELECT statements to deadlock on itself?

That is the report I am getting from a vendor for some recent slowness, and I am being asked to optimize the offending custom stored procedure. But I'm not entirely buying their analysis. How can such a deadlock occur?


texts: Is there any benefit to defragmenting SQL indexes in a SAN environment?

texts:
Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records.

We have been running Ola Hallengren's index maintenance scripts weekly, and it runs for several hours each time. Based on the fragmentation threshold, the script will either reorganize or reindex an index. We have observed that during reindexing, the log files get huge which leads to an excessive consumption of bandwidth during the log shipping.

Then comes an article from Brent Ozar in which he says to stop worrying about SQL indexes:

  Your hard drives are shared with other servers that are also making drive requests at the same time, so the drives will always be jumping all over the place to get data. Defragging your indexes is just meaningless busy work.

Googling this question leads to varying opinions, most supported with arguments that seem too brief or weak. Our tentative plan is to adjust the fragmentation threshold in our maintenance script so that it reorganizes much more often than it reindexes.

What is the final verdict? Is it worthwhile to defrag SQL indexes on a SAN considering the burdens associated with running weekly maintenance jobs?


texts: SQL Server query performance affected by rebuilding an un-fragmented index

texts:
I have a query within a stored procedure that the same input will periodically time out ( > 30 seconds)  when it normally runs in milliseconds.
The table has 2077748 rows, 59 columns, and 13 indexes.
We have found that rebuilding one of the indexes takes the performance back to milliseconds.
Here is the fun part.  The percentage fragmentation on this index every time it is rebuilt is < 1%, the last time it was .01%.  I've been playing with the fill factors because I thought it may be an expansion issue, it was 100, then 80, now 60.  This has not effected the frequency of this problem.
When I run the following
select b.name,*
from sys.dm_db_index_physical_stats(5,1075691080,NULL,NULL,NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id

I get

index_id  partition_number    index_type_desc alloc_unit_type_desc    index_depth index_level avg_fragmentation_in_percent    fragment_count  avg_fragment_size_in_pages  page_count
66    1   NONCLUSTERED INDEX  IN_ROW_DATA 3   0   0.01    1116    18.9229390681004    21118

Any suggestions on other places to look or sources of this problem?


texts:
This has come up a couple of times, e.g., in the postgresql newsgroup and the wiki. In general, the relationship between different intervals may not be well defined - a month can be different numbers of days depending upon which month (and year) is under consideration. But sometimes it is necessary to calculate how many intervals occur between two points in time, e.g. (simplified example):

CREATE TABLE recordings(tstart timestamp, tend timestamp, interval ticklength);

SELECT (tend - tstart) / ticklength AS numticks
FROM recordings;

This isn't allowed in PostgreSQL because it is division between two intervals, which wouldn't have well defined general behaviour for the above reasons. A workaround exists for when an interval can be converted to seconds, but what is the best way when this is not the case, e.g, when the interval is of the order of milliseconds?


texts: High-resolution CPU usage information for batches and statements

texts:
I'm working on a tool that I use to evaluate the performance of SQL Server queries in a development environment.  I use, and highly recommend, tools like SentryOne's Plan Explorer, but my tool reports on queries submitted by application code.  So I use Extended Events.

One of the things I've found is that the resolution of CPU times reported for the sql_batch_completed and sql_statement_completed events is poor.  Even though the cpu_time units is microseconds, the numbers reported are multiples of 15 milliseconds.  That's much larger than the values I'm trying to measure, for a certain class of queries I work with.  (Duration values are fine.)

Does anyone know a way to get higher-resolution CPU times?  Maybe a server setting or a different field or event?


texts: Run a Stored Procedure in DB2 (which has dynamic DDL) under the Definer rights

texts:
For our Datawarehouse applications, we want to perform ALTER TABLE ALTER CONSTRAINT <constname> NOT ENFORCED, before the load. Perform the load and then ALTER TABLE ALTER CONSTRAINT <constname> ENFORCED.

Tables are owned by DBA (SYSADM) and the application id doesn't have rights to perform ALTER.

So, the DBA asked me to create a Stored Procedure which will be owned by DBA and will be invoked by the application id.

For this, we explored DYNAMICRULES option of DB2.

So before the procedure is created by the DBA, the below call is made by the DBA

CALL SET_ROUTINE_OPTS('DYNAMICRULES RUN)
Procedure is created under DBA owner
Execute grant privilege to the application id

When I called the procedure using the application id, the error that I get is

  SQL0551N  "App id" does not have the required authorization or privilege to
  perform operation "ALTER TABLE" on object "DBA.Object_name".

The documentation that I referred is

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0007210.htm

Any help on this would be very much appreciated, as I feel that Dynamic DDLs can't be allowed to execute using Definer rights by a invoking auth id.


texts: SQL Error [1242] [21000]: Subquery returns more than 1 row (AMATEUR)

texts:
Basically, everything works in the brackets by itself. The statement in the brackets bring back 4 different values all on different rows.
How can I get around the fact that I need to apply the sql statement outside the brackets to the 4 values returned from the statements in the brackets when subqueries can only return 1 row?
SELECT
    to2.Name, to2.PhoneNum
FROM
    tbl_operatordesc to2
WHERE
    to2.operatorID = (SELECT
            to3.operatorID
        FROM
            tbl_operatorrouterelation to3
        WHERE
            to3.routeID = (SELECT
                    tr.routeID
                FROM
                    tbl_route tr
                WHERE
                    tr.`Start` = (SELECT
                            tb.busstopID
                        FROM
                            tbl_busstop tb
                        WHERE
                            tb.Description = 'Durham Estate')
                        OR tr.Destination = (SELECT
                            tb.busstopID
                        FROM
                            tbl_busstop tb
                        WHERE
                            tb.Description = 'Durham Estate')))

Thanks!


texts: How to get performance benefits from a view vs subquery?

texts:
I'd like to know how/if anyone has gained significant performance benefits from using views instead of subqueries.

I would prefer not to define a specific case, since I'm trying to establish good practice and rule of thumb in addition to case-by-case.

An example would be finding the last claim date in an insurance policy claim list where you started the search by sorted/filtered customer set and all claims are in their own table.
I'm currently using a view and thinking about a subquery instead.

Things that might affect performance across cases:

Can views be used somehow to avoid a full scan where a subquery would need to?
Are there any limitations/caveats on ensuring that the best indexes are used when joining to a view?


texts: How to speed up a loading data into MySQL from a zipped archive

texts:
I dumped a large database to an archive with:

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

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

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

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

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

My localhost is running MySQL 5.1 running on Ubuntu 11.10.

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


texts: Problems loading pgAudit library on Postgres 12.2

texts:
I'm trying to add the pgAudit extension to register the events of my database. I downloaded pgAudit and installed via git.
git clone https://github.com/pgaudit/pgaudit.git
cd pgaudit
git checkout REL_11_STABLE
gmake install USE_PGXS=1

And added 'pgaudit' to the shared_preload_libraries. However, when I try to start the server, it throws the following error:
FATAL: could not load library "/usr/local/lib/postgresql/pgaudit.so": /usr/local/lib/postgresql/pgaudit.so: Undefined symbol "IsSystemNamespace".

I've googled for a while but cannot find what's wrong. I've checked that the library is really there and uninstalled/reinstalled it. I'm currently working with FreeBSD-12.0 RELEASE and PostgreSQL 12.12.
Thanks in advance for your time.


texts: Adding more tempdb data files gives more write latency

texts:
I am facing problems with tempdb please help and provide your suggestions.

Initially, I have found tempdb contention problem in my server (Sql server) by using Simple talk Script. By using that script results i have found tempdb contention is existed. So i have started adding more tempdb files. I have added 8 files since i have 24 logical cores.

I have used below query to find cpu count.

SELECT cpu_count,hyperthread_ratio FROM [sys].[dm_os_sys_info]

It returns

Cpu_Count-24
hyperthread_ratio -6

After i have added more tempdb data files it resolves tempdb contention problem. I used to monitor Write/Read latency of each Database regularly.

Now i am seeing more write latency in tempdb it is in multiplies of 1000 for all files.

Before adding files i have never seen tempdb latency like above mentioned in my sql server

I have used Paul Randal script to monitor latency.

So please help me to solve this problem. Why latency is more?. Is anything wrongly i did?

Note:- Data files(All databases) are located in one separate drive.


texts:
I am restore a MariaDB 10.4.17 Database to Amazon aurora MySQL 5.6
while creating a table it is showing me this error
ERROR 1064 (42000) at line 25 in file: 'name.sql': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sha2(uuid(),256),
  `InsertTs` timestamp NOT NULL DEFAULT current_timestamp(),
 ' at line 17
Query OK, 0 rows affected (0.001 sec)

error is in the line 'sha2(uuid(),256)'
CREATE TABLE `User` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Version` bigint(20) DEFAULT 0,
  `FirstName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `LastName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Password` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordSalt` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordExpires` datetime DEFAULT NULL,
  `ForcePasswordChange` bit(1) DEFAULT b'0',
  `Email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Phone` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `NickName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TextNumber` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `IsValid` bit(1) DEFAULT b'0',
  `IsMigrated` bit(1) DEFAULT b'1',
  `UUID` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT sha2(uuid(),256),
  `InsertTs` timestamp NOT NULL DEFAULT current_timestamp(),
  `UpdateTs` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `PIN` bigint(20) DEFAULT NULL,
  `LatestInviteStatus` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `LatestInviteDate` date DEFAULT NULL,
  `LatestInviteUUID` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `uk_User_UserName_UNIQUE` (`UserName`),
  UNIQUE KEY `uk_User_UUID_UNIQUE` (`UUID`),
  KEY `idxPIN` (`PIN`)
) ENGINE=InnoDB AUTO_INCREMENT=90184 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I cannot figure out what it is on workbench it says
sha2 is not valid at this position


texts:
I need to query our (Azure) AD to get a user username based on on an email address I am being passed and then write it to a table ideally via an SP.

From my brief bit of reading, Azure AD supports Graph as does SQL Server 2017 and in theory I could use that to integrate the two, unfortunately, I am using SQL Server 2016 and the amount of testing required to upgrade to a more modern SQL version would probably be more than I could justify for this (small) project.

Does anyone have any suggestions?  Am I going to have to resort to calling an external power shell script?


texts:
I get an odd problem in my SQL R2.
Although I can attach a new db to my db instance, but all buttons on the tool menu panel are disabled.
So I cannot execute any sql query statements.
It is really annoying !!!
I have been stuck on it for ages.

Could someone help me figure it out?

Thank you so much.


texts: select two tables and print the columns side by side with matching key in hive

texts:
I have the below tables:

table_1

name_1  id_1    value_1
avi        1    21
adi        2    X

table_2

name_2  id_2    value_2
avi        1    21
adi        2    22

metadata_info: can be read from a file or hard coded

table_1_col  table_2_col
name_1       name_2
id_1         id_2
value_1      value_2

I want the below result:

table_1_col  table_2_col  table_1.name_1  table_2.name_2  table_1.id_1  table_2.id_2  table_1.value_1  table_2.value_2  flag
name_1       name_2       avi             adi             1             1             21               21               0
name_1       name_2       avi             adi             2             2             X                21               1

I am trying to do the entire process in a shell script, and I want to run multiple Hive queries.


texts:
I am just starting to play around with all the cool replication tools to Programmatically Monitor Replication
One of the early finds is sp_replmonitorhelpsubscription
When I run it as
 sp_replmonitorhelpsubscription

I get

Msg 20587, Level 16, State 1, Procedure sp_replmonitorhelpsubscription, Line 77 [Batch Start Line 16]
Invalid '@publication_type' value for stored procedure 'sp_replmonitorhelpsubscription'.

According to MS documents it should be valid with a null default

NULL (default)

If I run it with the Parameter it works fine.
sp_replmonitorhelpsubscription  @publication_type = '0'

I using it with transactional replication, on a single server (Reporting Copy) I have tried and I get the same results on SQL 2017 & 2016.  I am running it against the distribution database
Not sure If I am doing something stupid, if the MS docs are wrong, or what.
Why does sp_replmonitorhelpsubscription not work without parameters?


texts: MySQL Stored Procedure not throwing error if it contains a query that succeeds

texts:
I am writing a simple MySQL Stored Procedure, and calling it from PHP using PDO.

This is just simple enough to illustrate the problem I'm having.

This procedure test_procedure_2, does not throw any error.

CREATE PROCEDURE `test_procedure_1`()
BEGIN
    select 'gary';

    CALL raise_error;
END

It returns the result set:

[0] => Array
    (
        [0] => Array
            (
                [gary] => gary
            )

    )

However...

CREATE PROCEDURE `test_procedure_2`()
BEGIN
    CALL raise_error;

    select 'gary';
END

...this procedure test_procedure_2, when called, will actually throw an PDOException with the message "SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE vjs_admin_dev.raise_error does not exist". And the select 'gary' query never gets executed. I like that.

Why is it that the first procedure did NOT throw an error when it clearly contained an error? I would like to come up with a stored procedure that, whenever there is an error anywhere in it, it will throw an error. Thanks for your help!

I'm using

Ubuntu: 16.04, and
PHP: 7.0.15
mysql: Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper

EDIT:
I have tried this with mysqli, and with PDO, and I get the same results. Here are some simple code examples that show my issue:

Mysqli Version: https://pastebin.com/rATMST4H
PDO version: https://pastebin.com/Wiq3yFmQ

The output of the Mysqli version is:

Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN  select 'gary';  CALL raise_error;END
RESULTS:Array ( [gary] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN  CALL raise_error;   select 'gary';END
CALL failed: (1305) PROCEDURE vjs_admin_dev.raise_error does not exist
----------

The output of the PDO version is:

Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN  select 'gary';  CALL raise_error;END
RESULTS:Array ( [gary] => gary [0] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN  CALL raise_error;   select 'gary';END
CALL failed: (42000) Array ( [0] => 42000 [1] => 1305 [2] => PROCEDURE vjs_admin_dev.raise_error does not exist )
----------


texts: How Can I change pgbouncer pooling Point? Please help

texts:
Good morning,
I have more important questions that PGbouncer in postgreSQL module.

I saw lot of many threads, but I can't solved it.
so, I'm crave answer my question.

The questions are: I am using pgbouncer module on postgreSQL server, Pooling of pgbouncer Pooling starts when the server is running out of resources.

Can I arbitrarily adjust the point of pooling?

I hope that the pooling of pgbouncer will start when the lightweight resources are consumed.

thanks.

( I using transfer{google} ) sorry.


texts: Howto use Windows Authentication with SQL Server 2008 Express on a workgroup network?

texts:
I have two computers running SQL Server 2008 Express: c01 and c02, I setup both for remote connection using windows authentication. Worked fine for c02 but not for c01.

This is the error message I'm getting:

  Cannot connect to ACAMP001\SQLEXPRESS.

  ADDITIONAL INFORMATION:

  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

  For help, click: http://go.microsoft.com/fwlink?>ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

I don't know if I'm missing something, here is what I did:

Enabled TCP/IP protocol for client from Sql Server Configuration Manager.
Modified Windows firewall exceptions for respective ports.
Started the Sql Browser service as a local service
Added Windows user to this group: "SQLServerMSSQLUser$c01$SQLEXPRESS"
From Management Studio, I added "SQLServerMSSQLUser$c01$SQLEXPRESS" to SQLEXPRESS instance's logins under security folder, and I granted sysadmin permissions to it.
Restarted c01\SQLEXPRESS
Restarted Sql Browser service.
There is no domain here. It's only a workgroup.

Please any help is appreciated, Thank you.


texts:
I am not sure why the below join query is not returning any results.

SELECT * FROM table1 JOIN table2  ON table1.fieldid = table2.idfield

The below 2 queries return results so I am not sure why the join wont pick up any rows when i can clearly see that both tables have join field values in common.

SELECT *  FROM table1 WHERE fieldid =  900399530  (returns rows)
SELECT *  FROM table2 WHERE idfield =  900399530  (returns rows)

Any ideas?

Below are the data types for the 2 columns:

  fieldid [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

  idfield [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL


texts:
Recently moved SQL Server from a desktop to a virtual machine and now facing the below error message while connecting to SQL Server using SQL Authentication.

  -2147205987:[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot execute as the database principal because the principal “guest” does not exist, this type of principal cannot be impersonated, or you do not have permission.

The user ID SQLADMIN is used to connect to SQL Server.

May I know the permissions that SQLADMIN should have to do a BULK_COPY operation along with normal functions?

Currently SQLADMIN is set with the PUBLIC and BULKADMIN permissions. For Database1 and Database2 (used in the project), SQLADMIN is given PUBLIC and db_OWNER permissions.

After these changes, I am still facing the same issue.


texts: Going out of disk space in the course of querying a postgres database

texts:
Do SELECT queries (w/ table JOINs) on a database affect disk space whilst they're running?

Background: I have a Django app with a Postgresql backend (9.3.10). My DB resides in a VM that was running critically low on disk space (around ~400MB left).

I queried a few tables to assess which data to deprecate in order to release disk space (these included joins across tables). These analytical-type queries were bundled behind a single url, and run in unison. When I hit the url, the VM containing my DB went out of space after around half a minute.

I'm an accidental DBA and still learning the ropes. Can anyone explain why I went out of space in this scenario? Are some kind of temporary files created in such operations? I'll share my config details in case they're needed.


texts:
I'm a PHP developer so don't be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB so what i tried:

Alter table in screen with table lock. Took ~30hours and nothing. So I just stopped it. First I made a mistake because I didn't end all transactions but the 2nd time was no multilock. Status was copy to tmp table.
Since I also need to apply partitioning for this table we decide to make dump, rename and make table with same name and new structure. But dump is making strict copy(at least I didn't found something else). So i added to dump a new column with sed and query it. But some strange errors began. I believe it was caused by charset. Table in utf-8 and file became us-ascii after sed. So I got errors(unknown command '\'') on 30% of data. So this is also a bad way.

What are other options to accomplish this and speed performance(I can do it with php script, but it will took ages). What will be performance of INSERT SELECT in this case.

Thanks for any advance.


texts: Set uncommitted read as default in Oracle SQL Developer with DB2 database

texts: I am using SQL Developer with a IBM DB2 database. I am only querying the data for analytical purposes - never making any updates, additions or deletions. I was wondering if there is a way to ensure that every query is an uncommitted read by default so that I never lock the tables. Is there a way that I can do this without typing FOR FETCH ONLY WITH UR at the end of every query? Would enabling the settings for Autocommit in Preferences -> Database -> Advanced achieve the same goal (not lock tables)?


texts:
Is MongoDB feasibly suited to handle 90,000,000 documents per day? Version 3.2.

I did some rough estimates on my data size. There will be about 90,000,000 recorded events per day, each a maximum size of 16KB.

Despite the size of this data which our RDBMS is currently handling but not very well, can Mongo feasibly handle that and more if this grows?


texts: How to condition on one column with different values and print-out paired values in next column in multiple columns?

texts:
I have a sample date in 'myTable' table, including one column which represents IDs and second column represents their paired values (something like below sample)

myTable

IDs    values
0      A+
1      A
1      D
2
0      A
2      C
1
3      C

I need to get result as below,

0IDs    1IDs    2IDs    3IDs
A+      -       -       -
-       A       -       -
-       D       -       -
-       -       *       -
A       -       -       -
-       -       C       -
-       *       -       -
-       -       -       C


texts: Why is my recursive CTE so much slower on Azure SQL?

texts:
I have this simple recursive CTE for a hierarchy of folders and their paths:
WITH paths AS (
    SELECT Id, ParentId, Name AS [Path] FROM Folders
    WHERE ParentId IS NULL

    UNION ALL

    SELECT f.Id, f.ParentId, [Path] + '/' + f.Name FROM Folders f
    JOIN paths on f.ParentId = paths.Id
)
SELECT Id, [Path] from paths
WHERE Id = @FolderId

On my local SQL Server express, it runs in 35 ms no problem. On my Azure SQL database, it occasionally takes around 400 ms. The Azure SQL table only has around 2000 rows, and other simple queries only take around 80ms. I have indexes on Id and ParentId. My app can make this query very frequently and it's annoying to have to wait half a second or more every time. Here are screenshots of the two separate plans, no idea why they are different:
https://www.brentozar.com/pastetheplan/?id=Hky4zASAI

https://www.brentozar.com/pastetheplan/?id=H17LfRHAU

Is Azure SQL just slower?
EDIT: this has nothing to do with Azure SQL, the table there was a lot bigger than my test.


texts: MySQL tuning (my.cnf) for very large tables and ad hoc queries

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

We have tried to configure my.cnf but heavy queries can get stuck for many many hours without any result and same queries take one or two hours to complete on sqlserver (8GB Ram).

It's a dedicated MySQL server.

OS = ubuntu
Installed Ram = 512GB
Table type = Myisam

We have very large tables, (few tables having more then 70 million rows), almost every time new tables to match so indexes wont help that much, although there exist necessary indexes.

We do not have any application to connect our database, just 2 users who writes ad hoc queries directly.

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

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

Below is the mysqltuner recommendation

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

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

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

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

Any help would be greatly appreciated.


texts: SQL Server Extended Events to Capture Objects access by a User

texts:
I am in for a requirement where I need to use EE to capture the Objects Accessed by a particular SQL User.
I want to avoid the action sqlserver.sql_text, if I use this, I will need to manually scan though the data which may be prone to missing some data or incorrect results.
Is there an alternative to using sqlserver.sql_text


texts: How to map a drive in a SQL Server script

texts:
I am trying to execute below script to Network a Drive

use master
go
exec sp_configure 'show advanced options', 1;
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1;
go
reconfigure
go

EXEC XP_CMDSHELL 'net use Z: \\RemoteServerName\SharedName'
EXEC XP_CMDSHELL 'Dir Z:'

Is there any script that  I can use to connect the network drive in long term


texts:
I have a .mdf file that is 10gb. This is the max file size. How can I reduce the file size? I do not need the data, I just need to be able to write new data to the database.

I have deleted a ton of rows, but the file size has not changed at all.

Any help would be appreciated!

Also, my log file grew by about 50gb when I deleted the rows from the .mdf. How do I make that smaller as well?


texts:
I read in a stackoverflow post that you can restore scripts from the past 24 hours by using this query:

Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

But I want to see only queries which a specific user ran, how can I perform this?


texts:
I have this grant permission below

use eNtsaRegistrationDB
go
grant select on UserReg to Users;

Cannot find the object 'UserReg', because it does not exist or you do not have permission.

Both of table were created and granted permission(UserReg) under properties on my sql server Login folder as well mapped with my Damain, although using Window authentication mode. Still getting that issue, please help mates.


texts: Automatic vacuum fails with `ERROR: XX000: cache lookup failed for type 0`

texts:
I've been noticing something like this popping up every 15 seconds in my server logs:

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

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

The table in question is defined as follows:

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

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


texts: How do I configure PostgreSQL 8.3 to log bind parameters?

texts:
I'm running into some unique constraint problems with my database. I can't reproduce them, but see error messages from our production system:

exception 'UniqueViolation' with message
    'SQLSTATE[23505]: Unique violation: 7 ERROR:
    duplicate key value violates unique constraint...'

When I check the log files, I see the statement causing the exception.

Unfortunately, I don't see what the bind parameters for the SQL statement are.

The documentation for 8.3 seems to indicate that I could log the bind parameters, but I cannot determine how to turn on logging.

Can anybody help me figure out what to change in postgresql.conf?


texts: How do SQL Server users deploy for different licenses of the same release of SQL Server?

texts:
Let's assume I want to deploy something like this to SQL Server,

ALTER TABLE foo
    ALTER COLUMN a varchar(11) NOT NULL
    WITH (ONLINE = ON);

This will work fine if your version of SQL Server is "Enterprise" but if you don't pay that much, you'll get this warning (as taken from "Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64)")

  Msg 1712 Level 16 State 1 Line 3

  Online index operations can only be performed in Enterprise edition of SQL Server.

What I would like to happen is that WITH (ONLINE = ON); is provided for users of Enterprise, but that if you're not on Enterprise, because the migration still must happen that it falls back to ONLINE = OFF?

There is no ONLINE = IF_PAID_ENOUGH,

| ONLINE = {
      ON [ ( <low_priority_lock_wait> ) ]
    | OFF }

Is there a supported mechanism to deploy to both crippled and non-crippled versions of SQL Server?


texts:
While doing Audits found the Cross database chaining enabled for the system databases. When I run the alter database query to disable, it says that the value cannot be set.

A few questions :

Why the system databases do not allow us to disable this control?
What purpose does this serve?


texts: Export a SQL Server 2000 database to Server 2012

texts:
I have a production environment running Windows Server 2000 and a development environment using SQL Server 2012.  I need to make updates to the DB and implement some new features using .ASP.  Is there a way to export the 2000 database and import it into 2012?  All that I could find is this MS support doc about transferring the DB to 2008.

http://support.microsoft.com/kb/962355


texts: Column that must be unique only for rows with matching foreign key

texts:
I'm trying to enforce a uniqueness constraint on a table and I'm not sure the best way to go about it. I have two tables: Parent and Child. Child.parent_id is a foreign key referencing Parent.id. I would like for Child.name values to be unique, but only for members of Child where the Child.parent_id value is the same. That is,

child_one.parent_id == child_two.parent_id && child_one.name == child_two.name should be impossible
child_one.parent_id != child_two.parent_id && child_one.name == child_two.name should be possible.

If it's a great deal of trouble I may be willing to relax the second constraint and simply say that Child.name must simply be globally unique, but I'm not very experienced with databases so I thought I would ask for advice.

I'm writing the application using Laravel, so the exact database technology is more of an implementation detail, but we happen to be using MySQL.

Thank you for any help.


texts: Open files in MySQL increasing over time, eventually causing it to stop accepting connections

texts:
Every few seconds MySQL is opening a file and not closing it, which we can see in real time by querying

SHOW GLOBAL STATUS WHERE variable_name = 'Open_files';

Looking at the open file descriptors using

sudo ls -la /proc/<pid>/fd | less

shows that these open files are mostly deleted temporary tables:

lrwx------ 1 mysql mysql 64 Apr 17 08:56 990 -> /tmp/mysql_temptable.xTHQV4 (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 991 -> /tmp/mysql_temptable.gr1swq (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 992 -> /tmp/mysql_temptable.sXackV (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 993 -> /tmp/mysql_temptable.Tom8Pa (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 994 -> /tmp/mysql_temptable.OqNhMl (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 995 -> /tmp/mysql_temptable.VOlk8X (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 996 -> /tmp/mysql_temptable.ti1nry (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 997 -> /tmp/mysql_temptable.EeXTiS (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 998 -> /tmp/mysql_temptable.r2GHks (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 999 -> /tmp/mysql_temptable.NDCeta (deleted)

This is similar to the issue reported at MySQL crashing at about 70k open files, although we get a different error when MySQL runs out of file descriptors. There it's mentioned that this is fixed in 8.0.16, but who knows when that version will release. Meanwhile, we're having serious issues...

Is there any way we can find out what queries are causing these temporary tables to be created? Or is there a way to mitigate this problem in some other way?


texts: Negative values for Wait Time per Core per Sec in sp_BlitzFirst

texts:
I'm a developer in a firm that doesn't want to spend money on a DBA so i'm on my own with keeping the SQL servers healty. I checked some of Brent's videos and decided to check how hard the SQL servers are working.
I used sp_blitzFirst (sp_BlitzFirst 2017-07-01 00:00:00.0000000 +00:00) within a SQL agent job, that is executed every 3 minutes whole day. No special parameters, just table names to let the procedure know where to store the data.

SQL Server is 2016 with Service Pack 1 (13.0.4001.0)

So far i really like the output because it tells me when the server is idling...and when it's working. I am confused though because i get some negative numbers in the "wait time per core per sec". How should i interpret that?


texts: Best practice for a Table with private columns

texts:
I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ideas.

Extra field for each field

My first idea was to just add a boolean to each fiend to mark if the previos column was public.

---------------------------------------------------------------------------------------
| user_id | user_data_1 | user_data_public_1 | user_data_2 | user_data_public_2 | ... |
|---------|-------------|--------------------|-------------|--------------------|-----|
| int     | string      | boolean            | int         | boolean            | ... |
---------------------------------------------------------------------------------------

In this case I have to use IF(user_data_public_1, user_data_1, NULL) on the SELECT query.

Related table to mark public fields

Another idea was to have the table with the fields and a related table with to mark the public fields:

---------------------------------------
| user_id | user_data_1 | user_data_2 |
|---------|-------------|-------------|
| int     | string      | string      |
---------------------------------------

---------------------------------
| user_id | filed_name | public |
|---------|------------|--------|
| int     | string     | string |
---------------------------------

In this case I'd need to do the JOIN and check if the field is public on the related table.

I don't like so much any of the solutions, so is there some elegant and hopefully efficient solution to solve this type of design.

Thanks in advance.


texts: Performance issue with xpath in SQL Server 2008

texts:
I have a table with lots of large xml-documents.

When I run xpath expressions to select data from those documents I run into a peculiar performance issue.

My query is

SELECT
p.n.value('.', 'int') AS PurchaseOrderID
,x.ProductID
FROM XmlLoadData x
CROSS APPLY x.PayLoad.nodes('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest";
/NS:ProductAndRelated[1]/NS:Product[1]/NS:PurchaseOrderDetails[1]/NS:PurchaseOrderDetail/NS:PurchaseOrderID[1]') p(n)

The query takes 2 minutes and 8 seconds.

When I remove the [1] parts of the single occurance nodes like this:

SELECT
p.n.value('.', 'int') AS PurchaseOrderID
,x.ProductID
FROM XmlLoadData x
CROSS APPLY x.PayLoad.nodes('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest";
/NS:ProductAndRelated/NS:Product/NS:PurchaseOrderDetails/NS:PurchaseOrderDetail/NS:PurchaseOrderID') p(n)

The execution time drops to just 18 seconds.
Since the [1]-nodes occurs just once in each parent node in the documents the results are the same except for ordering.

Actual execution plan for the first (slow) query is

and the second (faster) query is

Query 1 full screen Query 2 full screen.

As far as I can see the query with [1] does the same execution as the query without, but with the addition of some extra calculation steps to find the first item.

My question is why the second query is faster.
I would have expected the execution of the query with [1] to break early when a match was found and thus reduce the execution time instead of the opposite.
Are there any reasons why the execution does not break early with [1] and thus reduce the execution time.

This is my table

CREATE TABLE [dbo].[XmlLoadData](
    [ProductID] [int] NOT NULL,
    [PayLoad] [xml] NOT NULL,
    [Size]  AS (len(CONVERT([nvarchar](max),[PayLoad],0))),
 CONSTRAINT [PK_XmlLoadData] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
       ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Edit:
Performance numbers from SQL Profiler:

Query 1:

CPU     Reads   Writes  Duration
126251  1224892 0       129797

Query 2:

CPU     Reads   Writes  Duration
50124   612499  0       16307


texts: Is using dbms_utility.format_error_stack to filter errors in the exception block a bad idea?

texts:
I'm trying to handle two different exceptions could trigger in an external table that reads a CSV.

The code works great, but I'm concerned I'm doing it wrong.

SET SERVEROUTPUT ON;
DECLARE
  records INT := 0;
  external_table EXCEPTION;
  PRAGMA EXCEPTION_INIT(external_table, -29913);
BEGIN
  SELECT COUNT(*) INTO records FROM ext_testing;

  EXCEPTION
    WHEN external_table THEN
      IF dbms_utility.format_error_stack LIKE '%KUP-04040%' THEN
        RAISE_APPLICATION_ERROR(-20000, 'Missing CSV file!');
      ELSIF dbms_utility.format_error_stack LIKE '%ORA-30653%' THEN
          raise_application_error(-20001, 'Invalid UTI report, check for missing fields!');
      END IF;
    WHEN others THEN
        dbms_output.put_line('An exception occurred');
        dbms_output.put_line(SQLCODE || '=>' || SQLERRM);
END;


texts: Adding new columns to Table with Clustured Columnstore Index

texts:
I have a daily facts table 'FactsTable1' with 240 columns on which I am exploring 'Clustured Columnstore Index'(CCI).

I want to know,

What happens when I add another ~50 columns to the table?
Will they automatically added to CCI? Or I have to rebuild CCI once
again?
What should be the best way to handle such conditions?
If I have to rebuild index to make new columns to be part of CCI
then what will happen if I dont rebuild? How will it affect my query
performance?

Additional information :

FactsTable1 contains 400000+ rows & 240 columns
Its partitioned on date column
It is heavily queried for analytic data.
@@version : Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)


texts: Professor told us to store serialized Java objects as blobs instead of defining relational tables

texts:
Instead of actually defining a tables with the correct attributes, my professor told us we could map objects to ids like this:

id (int)  |   Serialized Object (blob)
   1               10010110110

I can see so many problems with this; data redundancy, having to track ids separately, having to pull the whole table in to memory to search for anything, and **if I want to change my model in the Java code I will no longer be able to deserialize the blob stored in the database into that model.

Either I am forever stuck with that model or I have to do some other really ugly stuff to change my model.** This whole things seems like bad form to me. Am I justified in disagreeing with my professor? Is there some benefit to doing this that I have not thought of? If I'm correct should I say something to my professor about this? He was preaching this to my whole class and even said that he has built projects that way. A second opinion would be great.

The course is named Software Design.

My professor did not say that this was the best way, but he did say that it was a legitimate alternative to defining relational tables.

The model is not dynamic in any way.


texts:
select  Month(IH.InvoiceDate) as Month,
    COUNT(ID.InvoiceNo) as Count,
    COUNT(ID.InvoiceNo) as DirectMobile,
    COUNT(ID.InvoiceNo) as Preselling,
    COUNT(ID.PerformaInvoiceNo) as DirectWeb
    from InvoiceDetail ID
INNER JOIN InvoiceHeader IH ON ID.InvoiceNo = IH.InvoiceNo
WHERE IH.IsBackend = 1
GROUP BY Month(IH.InvoiceDate)

I want to ask how to retrieve data from InvoiceNo into two columns and how to give the where clause to complete these conditions.

Count is full count of every type of invoices and i want to get the count of DirectMobile , DirectWeb and Preselling . For get the count of those 3 these are the coditions.

DirectMobile - IsBackend=0

DirectWeb - IsBackend=1 and PerformaInvoiceNo= '' or 0

Preselling - IsBackend = 1 and PerformaInvoiceNo = a varchar value.


texts: Error Msg 10314, Level 16, State 11 with SAFE assembly after upgrade to SQL Server 2017

texts:
I have a pre-SQL Server 2017 database that I restored into / upgraded to SQL Server 2017. In this database I have a SQLCLR Assembly. The Assembly is marked as SAFE since it doesn't do anything requiring a higher level of permissions, and the database has TRUSTWORTHY disabled / OFF. The SQLCLR functions and stored procedures worked as expected before moving to SQL Server 2017, but now when I try to execute any of them I get the following error:

  Msg 10314, Level 16, State 11, Server XXXXXXXXXXX, Line YYYYYY
        An error occurred in the Microsoft .NET Framework while trying to load assembly id ZZZZZ. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
        System.IO.FileLoadException: Could not load file or assembly '{assembly_name}, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

I have confirmed that CLR Integration / SQLCLR has been enabled on the server / instance.


texts: SQL Server - failed to update records when RAM is fully occupied

texts:
We are using SQL server 2008 in Windows Server 2008 R2 and Ram is 32 GB.
When we check in the Windows Task manager 25GB is always occupied even though no one is accessing the server.

Around 10 clients connected to the server. When we run a report from application,  (2 or more clients runs it simultaneously) the memory occupied fully ie 32 Gb and saving records from other application got failed.

I checked in RAMmap tool AWE occupies 25 GB.

How to handle this situation?  ie., how  to limit the usage of memory for SQL server  or adding records when  the report query takes long time to run?
Thanks


texts: Script for listing tables having no primary key oracle 9i

texts: I'm looking for a script to find tables within my database that have no primary key.


texts:
I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc).
However, in my use-case, every single day, the table gets 2160000 new rows.
It's also crucial that new inserts to the db takes less than 3ms.

I'm in the design phase, so the database isnt in use yet.
Question is, should I put everything into one table?
Or should I design my system from the start to use multiple tables, divided by for instance days? (Will this improve performance, since I'm having smaller tables?)


texts:
I need to upgrade a WordPress site - meaning the WordPress code itself, but maybe more importantly, the plugins and themes in use.
I've created a git repo with a branch for each of the plugins I'm updating, and I've managed to capture the files that change upon upgrading each plugin. I still need to do the equivalent for any db changes that are occurring.
So the most obvious solution is something like:

Store the original database (a simple mysqldump wordpress > wordpress.sql for example) in the master branch.
In each plugin branch, create a mysqldump wordpress > wordpress_myPlugin.sql.

There are multiple problems with this, primarily that I can't see how multiple sql dumps can be overlapped/merged to create one coherent master sql to restore from, that contains all of the changes across all of the plugin updates.
It also seems like separating the data from the schema will be important here: perhaps a full dump in master, schema-only changes for each plugin, and then finding a way to merge those schema changes into a new branch might work, and merge all of that on top of the master. Point-in-time recovery may be a way to accomplish that.
Another option I guess would be to use MySQL's built in binary snapshots, but the whole point in doing these upgrades using git was to do it non-linearly so that any given upgrade can be easily cherry-picked and rolled back. The binary log file system seems linear in nature (unless I misunderstand it), so it's difficult to selectively roll back one upgrade and not the others, if the one being rolled back is chronologically earlier than ones that should be kept.
This is likely not an ongoing problem but a one-time upgrade. Any help would be appreciated.
I have read Is backing up a MySQL database in git a good idea and Backup your Database in Git.


texts: What is the real difference between vacuum and vacuum analyze on Postgresql?

texts:
What is the difference between:

vacuum;

and

vacuum analyze;

I read the postgresql manual, but this is still not clear 100% for me.

Do I need to run both, or one of them is sufficient?


texts: Would generate a new table help reducing the overall size

texts:
I am relatively new to the database design or just database in general so I apologize first if any term or format I use is not super precise.

Scenario: Say I have a People table that contains PersonName, with other attributes say profession, as primary key and other non-primary attributes. And for whatever the reason I am only going to have two distinct people, Databaseguy1 and Databaseguy2, in the table. Normally I would save the PersonName as a char type with max size up to say 12.

So I am wondering that maybe in the People table I can have a PersonID as my primary key and represent it with bit (the smallest data type in SQL server, correct me if I am wrong) and create another table with PersonID(pk) and PersonName.

I believe that by doing so, I only need to record the person as 0 or 1 in the first table. It wont affect the overall number of rows but will save space in each row since now one of the fields is represented with bit data type rather than a variable char data type. And in the new table I am just going to have two rows, 0 to Databaseguy1 and 1 to Databaseguy2. Is this a valid argument or is there a better way to accomplish it?  Will this affect the performance such as retrieving the data since now it has to go to a separate table to fetch the actual name of a person?

Other questions: will the relationship between the second table the People table be one to many relationship?


texts:
I had my innodb file format changed from antelope to barracuda recently.

Is it safe to have both antelope and barracuda at the same time? I have my setup running properly now.

What issues I would face? Or should I change it back to antelope.


texts: Would we see a performance increase by moving one table to another physical drive?

texts:
We have a database of about 600MiB. One table is particularly large and occupies about 70% of that space.

We quite often have to query this table - sometimes as far back as a few years, so we can't archive much of it - and because it takes a long time, this often creates a bottleneck for other queries which I believe is caused by the hard disk's physical speed.

The machine it's on it is quite old and slow and we're looking at moving over to a new one, but this could take a while so we're looking for an interim solution.

The machine has multiple physical drives. If we turned on innodb-file-per-table and moved the large table to a different physical drive, could we expect a performance increase when querying this table?


texts: How do I change the text in the export format dropdown on SQL Reporting server

texts:
I am looking for a way to change the text in the export format dropdown of a report on the SQL Reporting server (2005). For example, currently, one of the dropdown items is named: Web archive. I would like to change that to say: Web archive (.mhtml).

I've done some searching on google, but couldn't find any satisfactory answer. I have also searched through both config files in the Report Services folder of the instance in question, but found nothing.

Any assistance is appreciated.


texts:
I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running

ALTER TABLE foo DROP FOREIGN KEY fk_foo_1;

Today while it was running I checked in on it periodically; everything looked normal, the query was running, in state "copying to tmp table", I could see the temp table on disk getting larger and larger (still plenty of free space). I could see other processes blocked waiting for this table to be unlocked. Finally after about 4.5 hours, they got the "lock wait timeout exceeded; try restarting transaction" error. This is actually the 2nd time they've tried, and it seems to fail right about when I would expect it to complete.

innodb_lock_wait_timeout is set to the default 50, I can't imagine it would run for so long to be affected by this. No errors logged, no deadlocks or other weirdness seen in 'show engine innodb status'. Can anyone help me with other ideas? I'm fairly stumped on this one.

thanks


texts: how to avoid deadlock while updating 2 rows in mysql

texts:
This is a interview problem.

There is a perl program that updates the database, and it could run in different processes.
One process may execute a transaction like: update row A -> update row B -> commit.
The other process may execute a transaction like: update row B -> update row A -> commit.
The rows need to be updated is selected before or in the transaction.

I was asked how to avoid deadlocks without changing the transaction logic (I cannot commit after updating A and commit again after updating B).

They want me to propose at least 3 different methods.
What I know is to use "select .. for update" when selecting row A and B. Can anyone help to suggest some other methods?


texts: Always On adding a database outside of group

texts:
I have a 2 node AG with a synchronous replica. I want to know whether I can safely add another database to the synchronous replica node, without adding it to the Availability Group.

Does doing this carry any caveats?


texts: Function pg_indexes_size(oid) does not exist

texts:
I'm attempting an operation on a pg_class c, but getting this:

ERROR:  function pg_indexes_size(oid) does not exist
LINE 10:               , pg_indexes_size(c.oid) AS index_bytes
                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Version of Postgres

$ psql --version
psql (PostgreSQL) 8.4.20
contains support for command-line editing

I tried typecasting to a few different types, but with no success.


texts: mongodb equivlant query to this sql group by

texts:
I have a collection where most documents have an ISODate column.

I'm wanting to get a basic count by date for that column.  In MySQL if I had a datetime column on a table, to get what I'd want I'd do

select date(date_col), count(*) from mytbl group by 1;

It looks like mongos group method might be what I want but I don't seem to be able to get the syntax correct.

Note I'm running version 2.3, so anything specific to 2.4 won't be applicable for me.

Can someone help me with the equivalent mongo translation?


texts:
Is there a way, to optimize this query, where several IsNull in relation with dates are used? Unfortunately the query is a way slow and actually I have no glue, how to make it way faster.

case
when IsNull(A.TD, '1900-01-01') > IsNull(B.TD, '1900-01-01') then IsNull(A.TD, '1900-01-01')
when IsNull(A.TD, '1900-01-01') < IsNull(B.TD, '1900-01-01') then IsNull(B.TD, '1900-01-01')
when IsNull(A.TD, '1900-01-01') = IsNull(B.TD, '1900-01-01') then S.TE end as DZ

Thank you.


texts: Restoring corrupt database - "mdf is not a primary database file"

texts:
Unfortunately the hard disk of my system is damaged. I am trying to recover my database, but it cannot be attached again to SQL management studio 2005. The error is

  myfile.mdf is not a primary database file. (SQL server , error 5171)


texts: Most effective way to manage a growing list of time series data in an MS Access database

texts:
I am building a database to store time series data along with some other summary statistics. Since this is a local application I do not have access to more robust database solutions and am confined to the MS Access 2013 offering. I was trying to manage the work as a flat table in excel but this becomes unworkable. Each run consists of 6 trials each having a handful of metrics (say 7) important pieces of information.

My issue arises from the fact that I have time series information associated in the run that I would like to store and query for plotting. The size of the scatter data ranges from 10,000-50,000 points. I was thinking to have access query excel, but I would like the response to be somewhat snappy. The intent is to be able to compare different runs and simply pulling data in as needed.

* UPDATE *
I am using this database to store runs from lab equipment. The dataset comes in x,y pairs and have array lengths up to 50,000 points. I will have more and more sets to add to the database.

Where I am stuck is I am not sure how to get the data into access in a smart way. I was thinking about adding field for each dataset but that seemms stupid and obviously I cannot do a muli-valued field for this.

snip of data in excel


texts: How to query two values from the same table from different records combined with fields from other tables?

texts:
I have a query similar to the one below which is getting data from a couple of tables and works as expected. The table eventsink.RedEvent has another column called Finished. I want to add the Finished column to the result of my query, however the value I want for Finished will be from another record than the record that Created is queried from. I need the MIN() of Finished that also matches DeviceId and also has an Id = '1'

Thanks for any help you can provide. I am pretty new to such complex SQL queries.

SELECT eventsink.RedEvent.DeviceId,
       MAX(eventsink.RedEvent.Created),
       BranchNumber,
       CountryCode
FROM eventsink.RedEvent
JOIN md.Elevator ON md.Elevator.DeviceId = eventsink.RedEvent.DeviceId
JOIN eventsink.RedMasterDataEvent ON eventsink.RedMasterDataEvent.DeviceId = eventsink.RedEvent.DeviceId
WHERE (Id = '9'
       AND SoftwareVersion LIKE '[1][.]%')
GROUP BY eventsink.RedEvent.DeviceId,
         BranchNumber,
         CountryCode


texts: SQL Server - SQL Agent Alerts - Alert 824 warning about old problems

texts:
I have alerts enabled on a SQL Server 2017 where corruption was encountered and repaired over a year ago. I have also confirmed that there is no new corruption on any database on this server.

When restarting the SQL Server it sends out an error 824 alert message that states the following:

  DATE/TIME:  1/17/2019 7:54:22 PM

  DESCRIPTION: SQL Server detected a logical consistency-based I/O
  error: incorrect checksum (expected: 0xe660dd36; actual: 0xcd481907).
  It occurred during a read of page (1:386939) in database ID 7 at
  offset 0x000000bcef6000 in file 'C:\DATA\database.mdf'.  Additional
  messages in the SQL Server error log or operating system error log may
  provide more detail. This is a severe error condition that threatens
  database integrity and must be corrected immediately. Complete a full
  database consistency check (DBCC CHECKDB). This error can be caused by
  many factors; for more information, see SQL Server Books Online.

The issue here is that the date/time is from 11 months ago. What I am looking for is details on what the alert for error 824 is checking or what needs to be purged from 11 months ago to prevent this false positive alert from being sent out.

I have checked msdb.dbo.suspect_pages - there are no entries in that table.
All SQL server logs have rotate out in that time period so it is not something in the log.
I have ran checkDB a couple of times to confirm no new corruption.

Any suggestions?


texts: Postgresql failback without resync the data directory

texts:
Im using Master slave streaming replication in PostgreSQL. Repmgr is configured to handle automatic failover.

Once the failover happened I need to make the old master as a slave without doing any basebackup, also the new primary requires some changes to act as a master like need to enable wal logs and other things.

How can I avoid these changes in Secondary during replicate to Old master and sync Old master without basebackup?


texts: Oracle Data Guard:Can archived redo log files be sent from primary database to physical standby database manually?

texts: As a result of the configuration of firewall,the primary database can`t not send the archived redo log to physical standby database in real time,the network connection between primary and physical standby would be available at midnight.In that case,i want to copy the archive log to physical standby manually by the shell scripts.In the general case,the sending of archived redo log is handled by the process call ARCH,so if i copy the archived the redo log to the physical standby ,can the data guard keep working correctly?


texts:
In my production server I have mistakenly deleted datafiles. When I am trying to open the database I am getting an error. The files are not important but the database is not opening up.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL>  alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/mars/oradata/MARS/system01.dbf'

I have tried plenty of ways to recover but it is not opening.
LIKE

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/u01/mars/oradata/MARS/example01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/mars/oradata/MARS/example01.dbf'


texts: Implementing "ANY" aggregate operator in T-SQL

texts:
I have a database that contains department and employee tables, and I need to return the number of employees for each department. I write the following query:

SELECT d.ID, d.Name, COUNT(*) EmployeeCount
  FROM dbo.Departments d
       INNER JOIN dbo.Personnel p
       ON p.DepartmentID = d.ID
GROUP BY d.ID, d.Name;

The problem is, I'm worried that the GROUP BY operator is going to be slow when working on a VARCHAR column. I'd like to be able to write

SELECT d.ID, ANY(d.Name), COUNT(*)  EmployeeCount
  FROM dbo.Departments d
       INNER JOIN dbo.Personnel p
       ON p.DepartmentID = d.ID
GROUP BY d.ID;

I could probably use the MIN/MAX aggregate functions, but they would likely make the query even slower. I know that, in MySQL, you can select a column that doesn't appear in the GROUP BY clause, and the DB engine will return any row value at random for that column. Is something like that doable in T-SQL?


texts:
I want to save the year in a PostgreSQL database - just the year. How should I create this field? There is a lot of information out there how I can extract the year from a date field, but not on how I should set up a table for using a year only. I tried this:

CREATE TABLE information (
    id serial PRIMARY KEY,
    year date NOT NULL
);

INSERT INTO "information" ("year") VALUES (2010);

But this fails:

ERROR: invalid input syntax for type date: "2010"


texts:
For example:

CREATE USER c##mybear IDENTIFIED BY pretty
DEFAULT TABLESPACE USERS
QUOTA 500M ON SYSTEM;

So, Why shouldn't I create a new user on Oracle SYSTEM Tabespace?


texts: Making intermediate CTE results available for output/query without reprocessing earlier CTEs

texts:
Say I have the following query:

WITH
    d AS
    ( SELECT x, y FROM test WHERE x > 0 AND y > 0
    )
  , sc_x AS
    ( SELECT AVG(x) av_x, AVG(y) av_y FROM d WHERE x >= 5
    )
  , sc_y AS
    ( SELECT AVG(x) av_x, AVG(y) av_y FROM d WHERE y >= 5
    )
SELECT
    SUM(sc_x.av_x)+SUM(sc_x.av_y)+SUM(sc_y.av_x)+SUM(sc_y.av_y)
FROM
    sc_x
  , sc_y

Let's pretend those calculations are super complex and make sense to have in separate CTEs (I know these aren't). I need to query each of the CTE results and the final results for output for a program. I am thinking the best way to do this is to remove the CTEs and do SELECT ?? INTO like this for each CTE:

DROP TABLE d IF EXISTS;
SELECT x, y into d FROM test WHERE x > 0 AND y > 0;
DROP TABLE sc_x IF EXISTS;
SELECT AVG(x) av_x, AVG(y) av_y into sc_x FROM d WHERE x >= 5;
DROP TABLE sc_y IF EXISTS;
SELECT AVG(x) av_x, AVG(y) av_y into sc_y FROM d WHERE y >= 5;
SELECT SUM(sc_x.av_x)+SUM(sc_x.av_y)+SUM(sc_y.av_x)+SUM(sc_y.av_y)
FROM
    sc_x
  , sc_y;

Then query these tables separately. Is that the best strategy? I don't see another way to make the CTE results available without recalculating them. Speed is an issue, so I don't want to do all of the processing again that occurs before the CTE I want for output. Hopefully that makes sense.

PostgreSQL CTE page:
https://www.postgresql.org/docs/current/static/queries-with.html

I have Postgres 9.6.


texts: How to structure IF condition in MySQL trigger?

texts:
I am trying to write a MySQL trigger. I have two tables like this:

Table A------------------------------Table B

order_id--------sku---------------order_id----order_#----sku_copy

    568---------AAA---------------568---------2345
    567---------BBB---------------567---------6789-------empty column
    566---------CCC---------------566---------1234

When a customer makes a purchase a new record is added to each table. I have added column ‘sku_copy’ to Table B, so it does not get populated when a new record is created.

When a new record is created, I want my trigger to copy the ‘sku’  field in Table A to the ‘sku_copy’ field in Table B. However, the problem I am having is how to structure the following condition in the trigger.

IF: ‘order_id’ in Table A matches ‘order_id’ in Table B. THEN: copy ‘sku’ from that Table A record to the record in Table B with the matching ‘order_id’. The data should be added to Table B ‘sku_copy'.

I am using the following SQL trigger but it gives this error when it's run:

"#1363 - There is no OLD row in on INSERT trigger"

Here is the trigger:

DELIMITER $$
CREATE TRIGGER trigger_name
    AFTER INSERT ON tableA
    FOR EACH ROW BEGIN

    INSERT INTO tableB
    SET sku_copy = OLD.sku,
         order_id = OLD.order_id,
        order = OLD.order;
END $$
DELIMITER ;

Can some one show me how to correct the error in this code or suggest a better one?

Thank you for any help you can give.

Here is an update:

I tried this trigger (this is the live data instead of simplified as in the above examples) but get an error code:

  "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE virtuemart_order_id=new.virtuemart_order_id; END IF; END' at line 7"

Here is that trigger:

DELIMITER $$
CREATE TRIGGER `sku_after_update` AFTER UPDATE ON `uau3h_virtuemart_order_items`
  FOR EACH ROW
  BEGIN
    IF (old.order_item_sku_copy != new.order_item_sku)
    THEN
    UPDATE uau3h_virtuemart_orders
        SET order_item_sku_copy=new.order_item_sku,
    WHERE virtuemart_order_id=new.virtuemart_order_id;
    END IF;
  END$$
DELIMITER ;

Does anyone have any suggestions on how to make this trigger work?


texts: Repro for missed rows during Allocation Order Scan + Page Splits

texts:
In Read Uncommitted, an index scan can execute as an allocation order scan. If a page split occurs during the scan, it is possible that a row will be read twice if it moves to a later position in the allocation order. I have been able to reproduce this.

This example, from Brent Ozar, will consistently demonstrate rows being read more than once:

https://www.brentozar.com/archive/2018/10/using-nolock-heres-how-youll-get-the-wrong-query-results/

It is also reported that a row can be missed when a page split occurs. The inference is that the relocated row is moved to an earlier place in the allocation order. I have not been able to reproduce this.

Does anyone have a repro for this phenomenon ? Ideally something that shows it consistently.


texts: In Oracle, why would an OR / LIKE(A,B) take far longer than running the same query for each condition separately and unioning

texts:
I have a complicated subquery that looks something like this

SELECT ...
FROM ...
WHERE
   my_column IN (1,3)

It takes a long time to run.

I tried splitting it out into:

SELECT ...
FROM ...
WHERE
   (my_column = 1 OR my_column = 3)

and that had no impact on the performance.

However, when I run:

SELECT ...
FROM ...
WHERE
   my_column = 1

and

SELECT ...
FROM ...
WHERE
   my_column = 3

Each runs quickly separately.

And when I union the results:

SELECT ...
FROM ...
WHERE
   my_column = 1
UNION ALL -- or UNION
SELECT ...
FROM ...
WHERE
   my_column = 3

It also runs quickly.

My question is, why would this EVER be the case?


texts:
I didn't do an upgrade or anything but suddenly MySQL (MariaDB) stopped working after a restart. This is the log:

2018-02-11 14:10:02 140193631740480 [Note] Using unique option prefix 'innodb_buffer_pool_instance' is error-prone and can break in the future. Please use the full name 'innodb-buffer-pool-instances' instead.
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: The InnoDB memory heap is disabled
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Compressed tables use zlib 1.2.8
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Using Linux native AIO
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Using SSE crc32 instructions
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Completed initialization of buffer pool
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Highest supported file format is Barracuda.
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: The log sequence number 1445306045 in ibdata file do not match the log sequence number 1445306065 in the ib_logfiles!
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: 128 rollback segment(s) are active.
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Waiting for purge to start
2018-02-11 14:10:03 140193631740480 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.36-82.1 started; log sequence number 1445306065
2018-02-11 14:10:03 140190647551744 [Note] InnoDB: Dumping buffer pool(s) not yet started
2018-02-11 14:10:03 140193631740480 [Note] Plugin 'FEEDBACK' is disabled.
2018-02-11 14:10:03 140193631740480 [Note] Recovering after a crash using tc.log
2018-02-11 14:10:03 140193631740480 [Note] Starting crash recovery...
2018-02-11 14:10:03 140193631740480 [Note] Crash recovery finished.
2018-02-11 14:10:03 140193631740480 [Note] Server socket created on IP: '127.0.0.1'.
2018-02-11 14:10:03 140193631740480 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

I tried looking around but can't quite see how to fix it.


texts: How to launch SQL Server Agent job on completion of another one?

texts:
An SQL Server Agent job (SSA) have to be executed after a few other SSA jobs.

Is there any way to start SSA jobs on completion (or success, or failure) of another one?

Currently, I do this on schedule adding the delay for the duration of previous jobs. This is uncomfortable for many reasons:

the real execution time of preceding job can exceed the usual time and and the follow-up job will start without required completion;
if to include the follow-up processing as job steps then I need to duplicate them over a few jobs

SQL Server 2012 R2 on Windows Server 2008 R2


texts:
I've encountered this comment on reddit:

  I love Postgres but the one thing I wish is had is pessimistic
  locking. It's just such a huge help when you need multi-statement
  queries when multiple sources of writes against the same data are
  coming in.

https://www.reddit.com/r/programming/comments/2yl65b/goodbye_mongodb_hello_postgresql/

But then there's this:

  Row-level Lock Modes

  FOR UPDATE causes the rows retrieved by the SELECT
  statement to be locked as though for update. This prevents them from
  being locked, modified or deleted by other transactions until the
  current transaction ends.

https://www.postgresql.org/docs/9.4/static/explicit-locking.html

Is that or is it not the same thing as pessimistic locking?


texts: SQL Server : cannot add subscriber to publication

texts:
I am trying to configure replication as per the official tutorial from Microsoft. In the section Create a subscription to the transactional publication, see step 5.

The name of the subscriber instance is not displayed, so I click Add Subscriber, and then click Add SQL Server Subscriber from the drop-down list. Then the Connect to Server dialog box appears. I enter the subscriber instance name, switch to SQL Server Authentication, fill out the sa username and password, and then click Connect.

After a few seconds, an error dialog appears that reads:

  Cannot connect to DBSUB\SQLEXPRESS.

  Additional information:

  -> Failed to connect to server DBSUB\SQLEXPRESS. (Microsoft.SqlServer.ConnectionInfo)

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)

The thing is, the server IS very much available. I am able to ping DBSUB as well as RDP into it.

The SQL Server Agent is running.

The SQL Server instance is configured to allow remote connections. (RDP to DBSUB, log into DBSUB\SQLEXPRESS with SQL Server Management Studio on DBSUB (as opposed to SQL Server Management Studio on DBPUB), Server Properties, Connections page, Allow remote connections to this server is checked)

Strangely, intentionally entering the wrong name (DBSUB, DBSUB\BLAH, etc) produces a different error.

Also note that this is not an Express instance. It is an old name from an upgrade.

I would very much appreciate help solving this issue.

Thank you in advance,

Update:
I have configured Aliases as per this article (sorry it's behind a microsoft login). The instructions were incorrect; however, after learning a bit about Aliases, I was able to correctly set it up. Unfortunately, I get a new error that states that Aliases are not a solution to this issue.

  Cannot connect to Subscriber.

  Additional information:

    SQL Server replication requires the actual server name to make a connection to the server.
    Connections through a server alias, IP address, or any other alternate name are not supported.
    Specify the actual server name, 'DBSUB\SQLEXPRESS'. (Replication.Utilities)

I noticed the SQL Browser service was not running. I enabled it on both Publisher and Subscriber and connected to it through DBSUB\SQLEXPRESS and now it works.


texts: How to set user-level security in 2007+ MS Access?

texts:
I've read this MS info which should cover my problem. There are quite clear steps:

  Open the .mdb or .mde file that you want to help protect.
  On the Database Tools tab, in the Administer group, click the arrow below Users and Permissions, and then click User-Level Security Wizard.

The problem: I don't see "Users and Permissions" option in the "Administer" group. So I can't proceed. (I use Access 2013, which I don't think that's a problem here.)

In the site, it also says that "you must also use the /WRKGP command-line switch to point to your workgroup information file when you start Access".

And I did exactly that (which should be correct according to this):

"msaccess.exe" "C:\Temp\testDB.mdb" /wrkgrp "C:\Users\ZygD\AppData\Roaming\Microsoft\Access\System1.mdw"

The file loads, I get no error. Any thoughts? Anything I'm missing?

So, the problem is that I cannot proceed in creating user-level-security. This is a step towards creating separate user accounts for the Access DB file. Initially there is only one Admin account, and it's quite a challenge to create other accounts in new versions of Access...


texts: Is there any benefit in limiting the number precision on a surrogate PK in Oracle?

texts:
I'm reviewing some DDL that creates surrogate primary keys of type NUMBER(38).  When I first looked at this I thought it was a problem because 10^38 is a HUGE number, much much bigger than the number of stars in the universe or all the grains of sand on earth.  But I've come to learn that this doesn't matter in terms of how the numbers are stored.  And there doesn't seem to be any other options than say using a float type which I'm not really ready to seriously contemplate.  Here are some references about those facts:

Is there any point in specifying the precision and scale of a number for a foreign key?
Oracle integer to number conversion
Oracle Datatypes Reference

Given that, it would seem that it's pointless to bother with declaring a limit on the size of the PK i.e. just use NUMBER (which apparently can hold 40 digits.)  The only answer that I've seen is that you should do it for consistency.  But for a surrogate PK, the only thing it needs to be consistent with is itself.  What I mean is, if we set a limit, it's going to be something larger than what we would possibly reach so it seems completely pointless.

One piece of this I'm not sure about is whether the declaration could matter for indexing.  I think it probably doesn't for the most common approaches but are there any indexing strategies that might take this limit into consideration or use more storage because the column is not limited?


texts: Calculate Count Distinct without grouped columns in Oracle

texts:
I am trying to extract a few records and I ran into the "not a single group" error. This error can be resolved by removing the (count(distinct dc.DUCT_FID)) as NO_OF_WAYS column expression. However, I really need this column in my extraction.

SELECT TELEM.EXCH_CODE,
       TELEM.CENTRAL_OFFICE,
       TELEM.IPID as SEGMENT_IPID,
       TELEM.FEATURE_STATE AS SEGMENT_STATUS,
       DCON.G3E_FID AS DUCT_FID,
       DELEM.FEATURE_STATE AS DUCT_STATUS,
       COND.FR_MH,
       COND.TO_MH,
       TI.TRENCH_LENGTH,
       (count(distinct dc.DUCT_FID)) as NO_OF_WAYS,
       TELEM.YEAR_PLACED AS YEAR_BUILD,
       ADDR.STREET_NAME AS LOCATION,
       ADDR.POSTAL_CODE,
       TELEM.OWNERSHIP
FROM SGTEL10.B$GC_NETELEM_0404 TELEM
JOIN SGTEL10.B$GC_COND_0404 COND ON COND.G3E_FID = TELEM.G3E_FID
JOIN SGTEL10.TRENCH_INFO TI ON TI.PIPE_FID = COND.G3E_FID
LEFT JOIN SGTEL10.B$GC_CONTAIN_0404 FCON ON FCON.G3E_OWNERFID = TELEM.G3E_FID
LEFT JOIN SGTEL10.B$GC_CONTAIN_0404 DCON ON DCON.G3E_OWNERFID = FCON.G3E_OWNERFID
LEFT JOIN SGTEL10.B$GC_DUCT_0404 DUCT ON DUCT.G3E_FID = DCON.G3E_FID
LEFT JOIN SGTEL10.DUCT_COUNT DC ON DC.DUCT_FID = DUCT.G3E_FID
LEFT JOIN SGTEL10.B$GC_NETELEM_0404 DELEM ON DELEM.G3E_FID = DUCT.G3E_FID
LEFT JOIN SGTEL10.B$GC_ADDRESS_0404 ADDR ON ADDR.G3E_FID = COND.G3E_FID
WHERE TELEM.IPID IN (4001702,
                     4005753,
                     4005757,
                     4005785
                    );

What should I do?


texts: Does it make sense to use OPTION(RECOMPILE) in dynamic SQL?

texts:
I would like to find out if using OPTION(RECOMPILE) is beneficial when executing dynamic sql queries?
There are a few points to keep in mind:

There will be hundreds (maybe even thousands) of different dynamic queries created.
About 1/4 of them will be used repeatedly with "higher-frequency". Let's say several hundred times a day (that's several hundred executions of 1 specific query per day).
The rest will be used sporadically, might be a few times per day, might be a few times over a week.
Even if a query should be "composed" basically the same way (ie. joining the same tables etc.) as the one before it. There is also the question of input variables that come into play.
At minimum 2 objects (tables, views, joins etc.) will come into play in every query. At most it will be dozens of objects, in average it should be around 10 (this should loosely corelate with the 1/4 "high-frequency" queries)
Purpose is to optimize for speed.

Thank you beforehand for any tips and recommendations.
DB used: MS SQL 2019


texts: Use GROUP_CONCAT() to generate polygons in mysql

texts:
I have a table that has columns zone(name of the zone which is a string), longitude (decimal), latitude (decimal), logical order (integer).

logical_order indicates the order in which the coordinates can be combined to create the polygon. For example, each zone will have multiple rows in this table with different longitude, latitude. For each zone row, the logical_order will start at 1 (for starting point) and increase by one per row. For example, if a zone has 3 points, logical_order will run from 1 through 3. Coordinates at 3 are not the same as at 1. In other words, the coordinates do not close the polygon.

I'm trying to create POLYGON by grouping coordinates of each zone and using spatial package as below:

I tried the below query:

SELECT t0.zone, ST_GeometryFromText(CONCAT("POLYGON(", GROUP_CONCAT(t0.coordinate ORDER BY t0.logical_order SEPARATOR ','), ")")) FROM
(
    SELECT zone, CONCAT(longitude, ' ', latitude) AS coordinate, logical_order FROM zones
    UNION
    SELECT zone, CONCAT(longitude, ' ', latitude) AS coordinate, COUNT(zone) + 1 AS logical_order FROM zones GROUP BY zone
) t0
GROUP BY t0.zone

However, this gives me the error:

Error Code: 3037. Invalid GIS data provided to function st_geometryfromtext.

How can I fix this error? Am I approaching the right way?

EDIT:

If I try without the ST_GeometryFromText() in the second query, I get a string like:

POLYGON(77.5068350000 -11.4907909800,179.7363280000 -11.4907909800,179.7363280000 -60.0000000000,77.5068350000 -60.0000000000,77.5068350000 -11.4907909800)

After adding two parenthesis like below still give the same error:

POLYGON((77.5068350000 -11.4907909800,179.7363280000 -11.4907909800,179.7363280000 -60.0000000000,77.5068350000 -60.0000000000,77.5068350000 -11.4907909800))

EDIT:

SQL fiddle with sample data: http://www.sqlfiddle.com/#!9/5094e5/5


texts: The same timestamp in different MySQL versions

texts:
I read in mysql 5.6 SQL changes following text:

  TIMESTAMP columns not explicitly declared with the NULL attribute are
  assigned the NOT NULL attribute. (Columns of other data types, if not
  explicitly declared as NOT NULL, permit NULL values.) Setting such a
  column to NULL sets it to the current timestamp.

(extracted from https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html)

I have tried to create table with following DDL command in both MySQL 5.1 and MySQL 5.6:

CREATE table test(
id INT NOT NULL,
my_timestamp TIMESTAMP
);

However, I have found that in both databases, my_timestamp column is NOT NULL. According to the aforementioned sentence, I would expect my_timestamp to be NULL in older versions (< 5.6). Could somebody explain me this (for me) strange behavior?

Thanks in advance.


texts:
When querying from certain tables, I'll often receive the error:

character with byte sequence 0xef 0xbf 0xbd in encoding "UTF8" has no equivalent in encoding "WIN1252"

Varying bytes, but you get the idea. I can  solve this by doing:

set client_encoding to 'UTF8';

But the (very) annoying thing is, I have to do this client_encoding set every. Single. Time. I open postgres. How do I set this to default?

Also, I am not asking how to change the encoding of a table's data, or of databases I create from now on--that's a different issue.


texts: Why can't I use a cname/alias in the permissions table?

texts:
At work we have a usual setup with web server(apache) and database server(mysql). From time to time there is the need to migrate the web stuff to a new server. Lets say their a records are dull and shiny with cname www for dull, which we want to reassign to shiny, when shiny is set and done.

We - trying to be good admins - want to restrict database access as much as possible and only gave access to 'dbuser'@'dull'. But what we really want is to give access to 'dbuser'@'www', so that after reassigning the cname we don't need to alter the permissions.

I read that mysql does a reverse looup of the IP and then a lookup of the a record and will fail if anything in this process is fishy.

Why is there such a strict policy on this? Is there a reason why I can't use a cname in the permissions?

Bonus Question: What can I do to accomplish easy migration?

Edit: Split of bonus question


texts: How do I run a long-running stored procedure on SQL Server, even if I get disconnected?

texts:
I have a stored procedure in an Azure SQL Database that does not return a result and can take several hours to run. It takes one parameter, and I am running it through the query editor window in Management Studio on my local machine:

EXEC @return_value = dbo.ComputeBatchData @BatchId = 2

My problem: If I get disconnected, the procedure is cancelled.

The procedure does not need to run on a schedule or anything. I just want to run the procedure and not have to worry about disconnects.


texts: Multiple database servers for performance vs failover

texts:
If I have two database servers, and I am looking for maximum performance vs high-availability, what configuration would be best?

Assuming the architecture is two load-balanced web/app servers in front of two db servers, will I be able to have both db servers active with synced data, with web1 to db1, web2 to db2 setup? Is this active/active?

I'm also aware that the two db servers can have their own schema to manually 'split' the db needs of the app. In this case daily backups would be fine. We don't have 'mission critical data.'

If it matters, we have traffic around 3,000-7,000 simultaneous users.


texts: Optimising a query with two aggregate fields

texts:
I have a table in a MySQL 5.5 database with around 7 million records, with fields typeId, regionId, scanTime, scanId and a query that returns around 239,000 results.

For every combination of typeId and regionId, I want to find the most recent scanId/scanTime. (There are a small number of regionIds and a large number of typeIds).

I initially approached this with the following naive query:

SELECT max(scanTime), max(scanId), typeId, regionId
FROM scanTimes
GROUP BY typeId, regionId;

At this time there was the primary key index on scanId and an index on typeId, regionId, and the query took a few minutes as it had to read the entire database to calculate the aggregates.

As the scanId is auto-incrementing, and time shouldn't run backwards under normal conditions, carrying out an aggregate on both scanId and scanTime seemed a bit wasteful - it should surely be possible to just select the record containing the highest Id, then retrieve the matching Time.

So I adjusted my query to use a subquery as follows:

SELECT sq.maxScanId, q.scanTime, q.regionId, q.typeId
FROM
  (SELECT max(scanId) as maxScanId, regionId, typeId
  FROM scanTimes
  GROUP BY regionId, typeId
  ) sq
INNER JOIN scanTimes as q
ON q.scanId = sq.maxScanId AND q.regionId = sq.regionId AND q.typeId=sq.typeId

I also added an index on typeId, regionId, scanId, and another on typeId, regionId, scanTime (though it turns out I don't need the latter).

This got the query down to a few seconds which is a big improvement. Is it possible to do any better than this?

This is the result of EXPLAIN:

+----+-------------+--------------------+--------+-------------------------------------------------------------+--------------------+---------+--------------+--------+--------------------------+
| id | select_type | table              | type   | possible_keys                                               | key                | key_len | ref          | rows   | Extra                    |
+----+-------------+--------------------+--------+-------------------------------------------------------------+--------------------+---------+--------------+--------+--------------------------+
|  1 | PRIMARY     | <derived2>         | ALL    | NULL                                                        | NULL               | NULL    | NULL         | 239307 |                          |
|  1 | PRIMARY     | q                  | eq_ref | PRIMARY,REGION_TYPE,SCANTIME_REGION_TYPE,region_type_scanid | PRIMARY            | 4       | sq.maxScanId |      1 | Using where              |
|  2 | DERIVED     | scanTimes          | range  | NULL                                                        | region_type_scanid | 8       | NULL         |    201 | Using index for group-by |
+----+-------------+--------------------+--------+-------------------------------------------------------------+--------------------+---------+--------------+--------+--------------------------+

Is it possible to optimise this query further?


texts: Postgres jsonb vs composite type performance differences

texts:
What considerations are involved in choosing between a jsonb column and a composite type column of the same structure?

For example, consider a column like that used in the Postgres documentation:

CREATE TYPE inventory_item AS (
name            text,
supplier_id     integer,
price           numeric
);

What are the tradeoffs involved between this approach vs a jsonb column mirroring this structure?

For example, I suspect that the composite type won't require storing the key names for every record, whereas the jsonb type would require this.


texts: Is it possible to modify postgresql.conf using pg_ctl?

texts: I am programmatically starting PostgreSQL instances using pg_ctl init and pg_ctl start. I need to modify the configuration in postgresql.conf (specifically listen_addresses, and maybe some other values) and am trying to find a way to do this using pg_ctl, if possible. The documentation for pg_ctl and initdb make no mention of this. The section on Setting Parameters states that I can pass parameters to the postgres command, but in my case I am using pg_ctl to start the server process. Is this possible with pg_ctl or other command line tool included with PostgreSQL 9.5?