texts: SQL Server - Any way to validate insert statements for mismatching column names?

texts:
I like to be verbose in my coding, so my typical insert statement looks like this:

INSERT INTO MyTable
(
     Column1
    ,Column2
    ,Column3
    ,Column4
    ,Column5
    ,Column6
)
SELECT
     Column1 = 'some value'
    ,Column2 = 'some value'
    ,Column4 = 'some value'
    ,Column3 = 'some value'
    ,Column5 = 'some value'
    ,Column6 = 'some value'
....

If you didn't notice the problem with this statement, then you have been fooled just as I have been numerous times.

And the really insidious part of this type of flaw is that it might work for ages before causing any issues, and when it does fail, if there even is an error, it will not indicate the true error.

For example, if columns 3 and 4 are numeric and string types, respectively, then only some values will cause runtime errors for column 4 (and none for column 3) with a type conversion failure (e.g. '0' converts to a number just fine but 'asdf' will not).

(In my case, this was buried inside a Service Broker activation procedure outside the guarded code section (it was a logging statement) so instead of even that error, I was getting the orphaned transaction error masquerading as a poison message disabling my queue.)

Is there any way to validate such a statement that doesn't require manual proofreading?

P.S. I realize that you can give whatever aliases you like, but in a completely optional context such as an INSERT statement, it would be extremely useful to have some sort of automated tool (i.e. IntelliSense) to point out probable accidental code.

Update: I have opened this feedback item to Microsoft, for this clearly-lacking functionality.


texts: SQL Server Connection String: Can the password be encrypted in line?

texts:
I have an app, a commercial app, that stores its connection string in clear in the registry.

When I used a trusted connection to SQL Server, this is fine (the user id and password are in the IIS app, and are stored encrypted within IIS).

I now must move to SQL auth, and away from a trusted connection (long story).

I dream of a conn string like this:

Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=@#$@#@%#$%#$<encrypted password>@#$@#$;encryptedpassword=yes;

And a way that I can encrypt the pw (using the machine key or such).

Is there a path in this direction?


texts: RDS Postgres - pg_stat_statements is missing queryid column

texts:
I upgraded my RDS postgres instances from 9.3 -> 9.4 -> 9.5 several months ago.  When installing the pganalyzer-collector tool, I got a funny error that the "queryid" column does not exist in the pg_stat_statements table. It looks like that column was added in version 9.4. I'm now running 9.5 and expect to see this column in that table.

How could this have happened?  And how can I make this column appear in the pg_stat_statements table?


texts: Adding a database to availability group is case insensitive and cause vss backup exclusions

texts:
I was using a vss solution to take backup of my SQL availability group databases where I encountered a scenario when I have a database name like "TestDB" and when i added database to an availability group with name "testdb", caused exclusion of database from vss snapshots. As SQL is case insensitive and automatically handles lower and upper case databases if collation set to CI(Case insensitive).
Can someone please help if there is any guidelines to follow when we add database to an AG with Case Insensitive and Case sensitive server settings?


texts: Mysql master-slave replication makes no sence

texts:
I have the following values in my.cnf settings in a simple Master -> Slave setup:

# MASTER my.cnf:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format=ROW
binlog_do_db = db2
max_binlog_cache_size = 2G
binlog_cache_size = 32K
max_binlog_stmt_cache_size = 2G
binlog_stmt_cache_size = 32K
max_binlog_size = 100M
expire_logs_days=3

# SLAVE my.cnf
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog_format=ROW
replicate-wild-do-table=db2.%
max_binlog_cache_size = 2G
binlog_cache_size = 32K
max_binlog_stmt_cache_size = 2G
binlog_stmt_cache_size = 32K
max_binlog_size = 100M
expire_logs_days=3

The MySQL documentation states that in row-based replication the USE database is ignored and only the changed database is taken under consideration. However I've noticed the following behavior:

USE db1;
CREATE TABLE db2.Fake (field1 int,field2 int);

This is not replicated to Slave.

When I change the USE to db2 the replication works.
Am I missing something here ?


texts: Can't dump mysql table with corrupt index

texts:
One of the tables in my database is having an issue with indexing, I was advised to

backup the table,
remove the index, and
create a new one of the same name using the same columns.

I can't even move to step 2 because I can't backup the table. When I try to export it I get the error below:

  mysqldump: Error 2013: Lost connection to MySQL server during query
  when dumping table data_1_2019_11 at row: 1622537

I've tried changing the connection, read and write timeouts but it doesn't seem to be working in my case.

Is there another way I can do this? To backup the table? I want to ask if its safe for me to just move on to step 2 and step 3? Would really be nice to know how to fix the issue as well though for future references.

Thank you


texts:
I have implemented what is effectively a reliable queue through the following:

CREATE TABLE Queue (MessageID UNIQUEIDENTIFIER, Message varchar(255))

--Enqueue Command
DECLARE @MessageID UNIQUEIDENTIFIER = NEWID()
INSERT INTO Queue (MessageID, Message) VALUES (@MessageID, 'message data')

--Process
BEGIN TRAN -- actually done via ADO
SELECT Message FROM Queue WITH (ROWLOCK, XLOCK, READPAST) WHERE MessageID = @MessageID
-- at this point, the client does some computation, which could fail
DELETE Message FROM Queue WHERE MessageID = @MessageID
COMMIT TRAN --again, from ADO

Question being: how can I take a lock on the original insert, but still keep the insert committed if the processing transaction fails?

The queue is inserted and read from by multiple clients and there is also a recovery client which attempts to find any orphaned messages by:

SELECT MessageID FROM Queue WITH (READPAST)

and it is on rare occurances catching the message after it is inserted but before it is locked for processing.

The database is running on SQL Server 2012, the client is a .Net 4 client using ADO.NET.


texts: To Foreign Key or not to Foreign Key..... (inet -> cidr)

texts:
Below is a very abridged version of a project I am working on.

CREATE TABLE cidr_block (
    id serial not null unique,
    block_def cidr primary key,
    dhcp_server_id int not null references computer_system (id),
    ...
);
CREATE TABLE ip_addr (
    computer_id int not null references computer_system (id),
    mac_addr macaddr not null unique,
    ip_addr inet not null unique,
);

This is a network configuration database.  Now, it is expected that most, but not all, ip_addr.ip_addr records will be in cidr_block.block_def blocks.  Others, which might be externally linked vpn interfaces to customer locations might not be.  There is no simple, declarative way to ensure a fkey for the applicable rows between the two tables.  So I am wondering:

Is it preferable to use triggers to create fkey links where appropriate?
Is it sufficient just to join where expected?  If a computer is given a bad IP address, it just won't be given one from the dhcp server, and we will know this pretty quickly.  (writes trigger notifications which trigger updates on relevant dhcp servers)
Is there another, better way?


texts: Maximum length or special character restrictions for PostgreSQL 13 user password?

texts:
I'm using a password generator to come up with a password that will be automatically pulled from Azure Key Vault and into the Postgres deployment. The passwords are manually added in Azure Key Vault with the UI and not CLI.
I've tried 256-character length passwords with and without special characters. Each time I then try to login using kubectl port-forward and pgAdmin, and I get a "password authentication failed."
I shorten it to like 16-characters and then it works fine.
So it seems like special characters aren't an issue, but length is. So just curious:

What special characters, if any, will cause an issue in the password?
What is the maximum length of a password?

Note: I've even dropped down to 128 characters and still the same issue.


texts: Linked tables versus stored procedure performance in MySQL

texts:
Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form).

Option 1 is to simply create a linked table and set that as the record source for the sub-form. This of course requires little work.

Option 2 is to create a set of stored procedures for inserting, deleting, updating and reading records from the MySQL table to/from a local temporary table. The sub-form's record source is then set to the local table and the appropriate form events are tied to the appropriate stored procedure calls. Obviously this requires a lot more work than option 1.

Now the obvious question is why ever go for option 2? Well, I've been experimenting with replacing linked tables with stored procedures and have found the speed increase to be considerable, particularly over a slow network. I've done a bit of detective work too and dug around in the MySQL log files and I've noticed that the linked table approach generates a shocking amount of SQL calls. This explains the performance difference I suppose.

Anyway I'm now considering replacing all linked tables with the stored procedures option (well maybe just the important tables). Has anyone else had this experience? Any thoughts or advice?


texts: Couldn't find the long running SQL under v$session_longops

texts:
I have following SQL running from a long time. I couldn't see the status When I tried to look the time remaining for the query to complete. Can someone explain me why I couldn't find the SQL status

insert into SCOTT.EMP select * from SCOTT.EMP@dblink;

Query used:
SELECT USERNAME, sid, SERIAL#, OPNAME, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1;

Thanks in advance!


texts: Do I need to back up the log file drive when using VSS snapshots in simple mode?

texts:
We're currently using AppAssure to back up the SQL servers (2012/2014). It uses VSS snapshots. All databases are in simple recovery mode. Do I need to bother backing up the drive that contains the ldf files or is it enough to just back up the mdf files and let the log files recreate during a full restore?

SQL Writer Service explains how VSS can be used to take application consistent snapshot backups of the database files directly. It says it does not support log backups, which is why I asked this question.

Snapshot Backups are a feature of SQL server that allow hot backups without doing traditional dumps.


texts: My pgstartup.log has got big. I have renamed it, and can set up logrotate to do it automatically if need be, but of course the server is still writing to the open file. I'd rather not restart the server, if I can avoid it. Is there a way to tell the server to reopen the file? I wondered if either pg_reload_conf or pg_rotate_logfile would do it, but the former only does the config files, and the latter only does the true log file (i.e. not pgstartup.log).


texts:
How to drop 'user'@'%' on the MySQL CLI using the mysql -e command?

mysql -uroot  -p -e "'myuser'@'%'";

This does not work.


texts:
I have three tables: task, taskmembers, members.  Each task may have up to five people associated to it.  Currently, when someone updates a task I delete all the current members and insert them as a new record.  Otherwise, I have to figure out if the person is still a member of the task and, if not, delete them.  Also, I have to delete the people no longer associated with the task and update the people still associated to the task.

My concern is that I'm currently using an int as my primary key within the members table.  I know MS SQL int values can go up to 2,147,483,647 before overflowing, but it's still a concern of mine.  Also, is deleting all the records a good practice?  Thank you in advance.


texts: Can InnoDB use a clustered index as a covering index?

texts:
The scenario I have in mind is a query like:

SELECT id FROM table WHERE id = ?

or equivalently,

   SELECT 1 FROM table WHERE id = ?

So here's some background:

In a B+ tree (which InnoDB uses as its clustered index (PK) data structure),
internal nodes contain records which are essentially (key, *pointer-to-page) tuples (obviously simplifying a bit).
Formally, the key values in the internal nodes don't need to be extant, i.e. there doesn't have to be a corresponding row in a leaf node with that key.
But, in practice, they most often are.

Now not all key values exist in internal nodes, and in that case, obviously a leaf node will have to be loaded and searched for the asked-for key.  But if there was a matching key found in an internal node, and it is known to be extant in an actual leaf record, then InnoDB could take advantage of those facts and save loading the leaf page (and therefore potentially save an expensive disk read).

So my questions are:

Do InnoDB internal nodes only contain extant keys? (would need to deal with deletes and even updates of a primary key)
If so, does InnoDB take advantage of this in resolving queries of the above kind; or does it always load a leaf page?
Is there anyway using the existing MySQL profiling stats (SHOW STATUS . . ., SHOW VARIABLE, INFORMATION_SCHEMA, etc) to determine whether a particular id (e.g. SELECT 1 FROM table WHERE id = 345) actually elided a leaf-page load?


texts: How to see whether db was hit or not when I run query in my app

texts:
Sorry if the question is stupid, but I have some strange behaviour in my app, and I want to see what is queried in mysql db in real time. Is that possible? Or not in real time, some logs with time with seconds will work as well.
Thanks a lot beforehand.


texts: Indexing issues while partitioning with dbms_redefinition

texts:
I get an error while doing the redefinition. Following are the steps which I have taken so far:

Step 1. Start redefinition

DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'T_FDC_TOOLCONTEXT',
 dbms_redefinition.CONS_USE_ROWID);

Successful

Step 2. Creating a interim table

CREATE TABLE "CDS_USER"."T_FDC_TOOLCONTEXT_ONLINE"
(   "SERIAL_ID" NUMBER(15,0),
"CONTEXTID" NUMBER(15,0) NOT NULL ENABLE,
"ENTITYNAME" VARCHAR2(35 BYTE) NOT NULL ENABLE,
"ENTITYCOMPONENT" VARCHAR2(10 BYTE),
"SOLUTIONTYPE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"JOBTIMESTAMP" DATE NOT NULL ENABLE,
"EAJOBNAME" VARCHAR2(35 BYTE),
"PRJOBNAME" VARCHAR2(35 BYTE),
"FAJOBNAME" VARCHAR2(50 BYTE),
"STEPNAME" VARCHAR2(80 BYTE),
"LOTNAME" VARCHAR2(20 BYTE),
"LOTTYPE" VARCHAR2(20 BYTE),
"CARRIERNAME" VARCHAR2(20 BYTE),
"PRODUCTNAME" VARCHAR2(80 BYTE),
"RECIPENAME" VARCHAR2(80 BYTE),
"WAFERID" VARCHAR2(23 BYTE),
"MATERIALNAME" VARCHAR2(20 BYTE),
"MATERIALCOUNT" NUMBER(4,0),
"CAPACITY" NUMBER(4,0),
"TECHNOLOGY" VARCHAR2(128 BYTE),
"RETICLE" VARCHAR2(60 BYTE),
"LAYER" VARCHAR2(20 BYTE),
"SWRLOT" VARCHAR2(5 BYTE),
"PORTNAME" NUMBER(1,0),
"SLOT" NUMBER(3,0),
"RUNNUMBER" NUMBER(2,0),
"MVINTIME" DATE,
"ROUTE" VARCHAR2(80 BYTE),
"EPA" VARCHAR2(64 BYTE),
"PROCTIME" DATE,
"IMAGEID" VARCHAR2(25 BYTE),
"PRODUCTGROUP" VARCHAR2(25 BYTE),
"BASICTYPE" VARCHAR2(25 BYTE),
"PRETOOL" VARCHAR2(25 BYTE),
"REWORK" VARCHAR2(100 BYTE),
"SENDAHEAD" VARCHAR2(100 BYTE),
"SEQUENCE" VARCHAR2(80 BYTE),
"RSN" VARCHAR2(25 BYTE),
"COMPLETED" NUMBER(1,0) DEFAULT 0,
"LAYER2" VARCHAR2(20 BYTE),
"RETICLE2" VARCHAR2(60 BYTE),
"TRUSTED_FLAG" VARCHAR2(2 BYTE),
"VALID_FLAG" VARCHAR2(5 BYTE),
"RECIPE" VARCHAR2(80 BYTE),
"POSITIONTYPE" VARCHAR2(10 BYTE),
"PROCESS_TYPE" VARCHAR2(3 BYTE),
"IN_WORK" NUMBER(1,0) DEFAULT 0,
"D_CARRIERNAME" VARCHAR2(25 BYTE),
"SUMOSTEP" VARCHAR2(10 BYTE),
"INSERTED_TIME" DATE DEFAULT SYSDATE,
 PRIMARY KEY ("SERIAL_ID"))
PARTITION BY RANGE ("INSERTED_TIME")
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "p1_1"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))

Successful

Step 3. Starting the redefinition

DBMS_REDEFINITION.START_REDEF_TABLE (uname => v_name,
  orig_table => 'T_FDC_TOOLCONTEXT',
  int_table => 'T_FDC_TOOLCONTEXT_ONLINE',
  col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

Successful

Step 4. Copying the dependents

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(v_name, 'T_FDC_TOOLCONTEXT',
  'T_FDC_TOOLCONTEXT_ONLINE',
  dbms_redefinition.cons_orig_params,
  TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));

Error:

ORA-01408: such column list already indexed.

I have following indexes on my existing column.

CREATE UNIQUE INDEX "CDS_USER"."SYS_C0012094" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("SERIAL_ID")
CREATE INDEX "CDS_USER"."I_FDC_CONTEXTID" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("CONTEXTID")
CREATE INDEX "CDS_USER"."I_FDC_ENTITY" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("ENTITYNAME")
CREATE INDEX "CDS_USER"."I_FDC_TIMESTAMP"ON"CDS_USER"."T_FDC_TOOLCONTEXT"("JOBTIMESTAMP")
CREATE INDEX "CDS_USER"."I_FDC_CTRL" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("EAJOBNAME")
CREATE INDEX "CDS_USER"."I_FDC_PRJ" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("PRJOBNAME")
CREATE INDEX "CDS_USER"."I_FDC_CARRIER" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("CARRIERNAME")
CREATE INDEX "CDS_USER"."I_FDC_MATERIAL" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("MATERIALNAME")
CREATE INDEX "CDS_USER"."I_FDC_PORT" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("PORTNAME")
CREATE INDEX "CDS_USER"."I_FDC_SLOT" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("SLOT")
CREATE INDEX "CDS_USER"."I_FDC_ENTITY_LOT" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("ENTITYNAME", "LOTNAME")

What can I do to avoid the error?


texts: Seeking feedback on table relationships, general schema design

texts:
I'm designing a database for a business that sells tickets to a variety of events worldwide. The app will be built with Rails using PostgreSQL.

Here's what I have so far, link to full size image

I'm mostly concerned with the relationship between EVENT_CATEGORIES / EVENT_SUB_CATEGORIES / TEAMS_ARTISTS / COMPETITIONS.

A typical path for a user to take on the app would be

Sports (event_category)  ---> Football (event_sub_category)  ---> Champions league (competitions)  ---> Arsenal (teams_artists), skipping competitions for events without competitions e.g, Concerts.

Will these relationships hold up in a production environment? Is there anything more optimal I'm overlooking? Any other tips in general would be welcome and appreciated.

Thanks!


texts: Is there an operator or an easy way to match an expression one or more times with the LIKE operator in SQL?

texts:
For example if I run the following query, the expression in the WHERE clause will only match on the first letter before a number but is there a way to match on one or more letters first (e.g. in the case where there's a variable number of letters before the number in the field I'm filtering on)?

SELECT FilteredField
FROM Table
WHERE FilteredField LIKE '[a-zA-Z][0-9]'

Example data:

ABC9
DEF2
GH7
Z1
XYH2

Essentially I'm looking for the SQL Server 2016 equivalent of the RegEx + when using the LIKE operator.


texts: To find SQL Server is actually used or not?

texts:
I have an SQL Server 2012 and a 2008R2 running in my environment. I need to decommission the server if the server is not actually being used.

I did go through this and this where connection count is one of the parameters to check the same, and I did run that and the result was 0 but is that concrete enough for to me do away with this server? Thing is I do not have any ownership details of this server to even proceed further, so I will have to see in the server itself.

There are chances the database could have been restarted sometime back, which I'm not sure. Auditing and logon triggers have been disabled at present. Should I enable them? If yes, which auditing specifically I will have to enable? And what logon trigger?

There aren't many databases in the instance, about 3-4 only.
What can be done to come to a concrete conclusion to stop this instance from running?


texts: SQL NULL Value to = A Sequential Number, Help!

texts:
I have upgraded a database at work from a poorly designed Microsoft Access database to a SQL database.

The previous system allowed NULL values and duplicates to be inserted into a field that should not allow NULL values or duplicates.  Since the existing data already has some entries that are NULL, I cannot set NOT NULL constraints.

My solution would be to use a sequential operator.  For instance, NULL data would be changed to a sequential number that system administrators would know was a bogus number starting at something like 999999900, incrementing up from there. There are only 250 records that would require updating.

To make things more interesting this field is not a integer type, its a VARCHAR type as it is a Hardware ID.  The existing data contains both alpha and numeric data.

Any advice on how to update these NULL values using an incremental ID?


texts: How to use default value of data type as column default?

texts:
How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago:

CREATE TABLE test
(
  col_1 CHAR(12) NOT NULL,
  col_2 INTEGER  NOT NULL WITH DEFAULT,
  col_3 CHAR(12) NOT NULL WITH DEFAULT
);

I would just like to define that the DB should use the default value for the column data type (like in my example above), without specifying which value exactly.


texts: Options for dumping Oracle queries to text files with no database server filesystem access

texts:
I've an application currently producing reports using UTL_FILE and DIRECTORY. We are migrating this application to an environment in which there will be no filesystem access on the database server (including mounts).

One option is to use SQL*Plus on the client to produce the file.

Any suggestions for a low-key reporting tool designed for producing text-file output?

Thanks for any help.


texts: Tuning a query to fetch all the records in a day with certain status

texts:
We are currently using Oracle Database:

Consider a table TRANSACTION with the fields TRAN_ID, STATUS, CREATED_TIME, ....

The below query gets executed multiple times in a day.

select tran_id
from TRANSACTION table
where created_time <= TO_DATE(:1,'YYYY-MM-DD HH24:MI:SS')
and created time > TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS')
AND STATUS = :3`

Possible values are I- InProgress, F- Failed, S - Successful. Currently only TRAN_ID is Indexed, so the table goes through a full table scan.

Have the following options:

Introduce an index on status, this will solve the problem for handling 'In Progress' transaction as they can be low, but failed and success would be higher (However query on 'failed' or 'success' would run once or twice in a day).
Introduce a composite index(status,created_at) but  as I understand the data would get skewed as time progresses and index table will also grow rapidly.
Move the data from the in progress table to another history table at regular intervals so that it can work coupled with option a. coupled with quite some application changes.

I am not sure if there are other options available.


texts: Function quote_nullable(timestamp without time zone) is not unique

texts:
When I run this query on "enterprisedb 9.2", it raises an error:

select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date;

Error:

LINE 1: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** Error **********

ERROR: function quote_nullable(timestamp without time zone) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add explicit type casts.
Character: 8

In Postgresql 9.1, this query above run ok.

Please tell me what is happening here. Why is the function quote_nullable(timestamp without time zone) not unique?


texts:
Hello my little snowflakes, how can I isolate the correct time part of a timestamp?

SELECT CURRENT_TIMESTAMP()                                                                                AS a,
       CURRENT_TIMESTAMP() :: timestamp_ntz                                                                                AS b,
       CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP)                                                                                AS c,
       TO_TIME(CURRENT_TIMESTAMP)                                                                                AS d,
       TIME_FROM_PARTS(DATE_PART(hour, CURRENT_TIMESTAMP()), DATE_PART(minute, CURRENT_TIMESTAMP()), DATE_PART(second, CURRENT_TIMESTAMP())) AS e

Produces

A                   B                   C                   D        E
------------------- ------------------- ------------------- -------- --------
2017-06-11 20:33:00 2017-06-11 18:33:00 2017-06-11 20:33:00 12:33:00 12:33:00

I just want 20:33:00


texts: Clarification on MySQL innodb_flush_method variable

texts:
Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in layman's terms on the difference in O_DSYNC and O_DIRECT, and how to know if it's a performance issue on a database server.

Some stats on my setup: Mac OSX 10.6 (32-bit kernel, since architecture is out of date) running MySQL 5.1.49-64bit (hoping it would allow me to use the memory). 8GB RAM, ~6GB of innodb data/indexes.


texts: MySQL performance slows down when accessed from different regions via the Internet

texts:
I have a MySQL database hosted in a server in Brazil.

When I connect through an ODBC connection (FE is access) with a Brazilian IP everything runs fine.

When I connect from the U.S. with the same connection performance slows down dramatically.

Any suggestions?


texts:
I have a table similar to this (simplified):

CREATE TABLE books (
    id INT AUTO_INCREMENT,
    category INT NOT NULL,
    PRIMARY KEY (id),
    KEY (category)
);

This table has over 10,000,000 rows, at around 12 categories. So each category has an average of 833,333 books.

When querying for count:

SELECT COUNT(*) FROM books WHERE category=1;

Even though it's using an index when querying, this takes quite a long time to complete (several seconds.) How would you optimize this?

Previously, I had increased a number every time I inserted to books (into a table that relates category -> book count.) But our code is complicated, and many places insert or delete books. I know it's possible to solve this with EVENTS, but I'm asking maybe there's a MySQL feature I missed.


texts: Slow performance when using a synonym that is configured to use a linked server

texts:
Situation:
I’m not sure if I should be embarrassed that we did this, or if we should be applauded. Anyways, we have a shell of a database that has synonyms pointing to another server via a linked server, and then views named identical to the tables that used to be in the database that utilize the synonyms. We did this because of server migrations and reorganization of dev teams. Anyways, the different teams had baked in code that references this particular database, and we couldn’t get them to change their code. My team was told that the effort was a huge undertaking, and they didn’t have time to do it. So, we came up with this plan for better or worse, and ended up placing this shell database on each of their SQL Servers. We can’t replicate the database because the vendor of the application doesn’t believe in primary keys (or at least that’s what we tell ourselves…. yeah ponder on that one for a second, and it will make your brain hurt even more when I tell you they are a large billion software dollar company).
Question
If we do a select against the view (select top 100 * from viewname) the data results come back in about 3-4 seconds. Remember, the view is using a synonym that uses a linked server reference.
If we do a select against the linked server (select top 100 * from ls.db.schema.object) the data results come back sub-second. The full path of the linked server reference is the same for the synonym above.
Why the difference In times? Is it because engine has to take an extra moment to build the query plan? The dev teams are starting to get restless because their apps are now running slow.

If I’ve completely confused you I’m sorry. I did the best I could to describe the environment.

Query plan using the synonym:
brentozar.com/pastetheplan/?id=Sy9b0yZJM

Query plan using the linked server:
brentozar.com/pastetheplan/?id=r17oAy-kG


texts: Does Oracle use rowid to recover from redo log and archives?

texts: I am learning about supplemental logging and this questios has came into my mind. What I have understood is, Oracle just save an image of the modified column in redo logs, that is why we need to enable supplemental logging to use log miner or golden gate for example, then we will have sufficient information to reconstruct the modified rows using sql apply. But when Oracle does not have any supplemental logging enabled, how oracle recover this images during a recover situation, I mean, for example recover database from RMAN, is it by rowid?


texts: difference between index REBUILD and REORGANIZE in relation to recompile in SQL Server 2008 R2

texts: Does the INDEX REORGANIZE index cause recompilation of the stored procs the same way as  INDEX REBUILD


texts: Why does a JSON element return text when it's not text?

texts:
When I try to access a JSON element using ->>'elementName' I get a texttype.

SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2)
FROM ( VALUES
  ('{"a":5}'::jsonb, '5'::jsonb)
) AS t(x1,x2);

 pg_typeof | jsonb_typeof
-----------+--------------
 text      | number
(1 row)

However, jsonb says it maps numbers to numeric types...

  When converting textual JSON input into jsonb, the primitive types described by RFC 7159 are effectively mapped onto native PostgreSQL types, as shown in Table 8-23.

Here is the table reproduced from the docs,

Table 8-23. JSON primitive types and corresponding PostgreSQL types

JSON primitive type     PostgreSQL type  Notes
string                  text             \u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8
number                  numeric          NaN and infinity values are disallowed
boolean                 boolean          Only lowercase true and false spellings are accepted
null                    (none)           SQL NULL is a different concept


texts: How to log multiple invoke-sqlcmd verbose outputs into single log file along with showing/not showing them through flag into prowershell prompt

texts:
Lets say we have multiple invoke-sqlcmd statements like below:
invoke-sqlcmd -Query "PRINT 'Hello World 1'" -ServerInstance Server\SQL2017 -Database abc -Verbose
invoke-sqlcmd -Query "PRINT 'Hello World 2'" -ServerInstance Server\SQL2017 -Database abc -Verbose
invoke-sqlcmd -Query "PRINT 'Hello World 3'" -ServerInstance Server\SQL2017 -Database abc -Verbose

I want to do the following things:

I want to log verbose output statements of multiple invoke-sqlcmd commands into a single log file
Along with logging into the log file, can we show the verbose output in powershell window too? Also, can we manage it through any flag to show the output in powershell window or not?

Please suggest if it's possible or not.


texts:
Table 1: linked native access table
Table 2: linked pgsql table

Both tables have same records but searching for a record using built-in Find and Replace dialog box in selected field is a lot lot slower on Table 2 (linked) with 30K+ records.

Q: Is there any settings or tricks that can be done to improve performance using native Find and Replace functionality? Thanks.


texts: Delayed or Asynchronous DML Trigger Execution

texts:
I have created a DML trigger (AFTER INSERT, UPDATE, DELETE) on a table
Trigger's logic takes about 30 seconds to execute
So if you change even 1 row, it takes ~ 30 seconds due to trigger execution
Developer asked me "Is there a chance that the trigger could be a fire & forget action?"
I said no, but is it really so ?
Question:
Can trigger be executed in "asynchronous" mode?
Application updates couple of rows in few ms, and thinks that transaction is completed, and then trigger is silently executed under the hood ?
I understand that this actually does not look good from consistency point of view, but still, is it possible ?


texts: ALTER TABLE ... SWITCHing from regular table to partitioned table fails

texts:
The code below does the following:

Creates a database play_partition in C:\TEMP
Creates two identical partitioned tables play_table and archive_play_table
Switches play_table partition 1 to archive_play_table partition 1
Creates a new unpartitioned table temp_table with the same structure as play_table on the same filegroup as play_table partition 2
Switches play_table_partition 2 to temp_table
Tries to switch temp_table back to play_table partition 2 and fails with

Msg 4982, Level 16, State 1, Line 64
ALTER TABLE SWITCH statement failed. Check constraints of source table
'play_partition.dbo.temp_table' allow values that are not allowed by
range defined by partition 2 on target table
'play_partition.dbo.play_table'.

Why does it fail?

I am using SQL Server 2014 (Enterprise Edition Trial).

Regards,

Colin Daley

http://www.colindaley.com/translator

/* Playing with partitioned tables */

USE master;
GO

DROP DATABASE play_partition;
GO

CREATE DATABASE play_partition
    ON PRIMARY(
        NAME = play_partition
        , FILENAME = 'C:\TEMP\play_partition.mdf')
    ,FILEGROUP play_fg1(
        NAME = play_fg1
        ,FILENAME = 'C:\TEMP\play_fg1f1.ndf')
    ,FILEGROUP play_fg2(
        NAME = play_fg2f1
        ,FILENAME = 'C:\TEMP\play_fg2f1.ndf');
GO

USE play_partition;

CREATE PARTITION FUNCTION play_range(INT)
    AS RANGE LEFT FOR VALUES(3);

-- Partition scheme
CREATE PARTITION SCHEME play_scheme
    AS PARTITION play_range TO (play_fg1, play_fg2);

-- Partitioned tables
CREATE TABLE dbo.play_table(
    c1 INT NOT NULL CONSTRAINT PK_play_table_c1 PRIMARY KEY CLUSTERED
)
    ON play_scheme(c1);

CREATE TABLE dbo.archive_play_table(
c1 INT NOT NULL CONSTRAINT PK_archive_play_table_c1 PRIMARY KEY CLUSTERED
)
    ON play_scheme(c1);

-- partition 1 = {1, 2, 3}, partiion 2 = {4, 5, 6}
INSERT INTO dbo.play_table(c1) VALUES (1), (2),  (3), (4), (5), (6);

-- move partition 1 from play_table to archive play_table
ALTER TABLE dbo.play_table
    SWITCH PARTITION 1 to dbo.archive_play_table PARTITION 1;

-- create empty table with same structure as dbo.play_table
SELECT * INTO dbo.temp_table FROM dbo.play_table WHERE 1 = 0;

-- move temp_table to filegroup play_fg2
ALTER TABLE dbo.temp_table
    ADD CONSTRAINT PK_temp_table_c1 PRIMARY KEY CLUSTERED(c1) ON play_fg2;

-- move contents of play_table to temp_table, which is not partitioned
-- but is in the same filegroup
ALTER TABLE dbo.play_table
    SWITCH PARTITION 2 TO temp_table;
PRINT 'Switched from partitioned table to non-partitioned table';

-- move data back to partitioned play_table from unpartitioned temp_table
-- FAIL
ALTER TABLE dbo.temp_table
    SWITCH TO play_table partition 2;
PRINT 'Switched from non-partitioned table to partitioned table';

SELECT 'archive_play_table' as table_name, t1.c1
    FROM dbo.archive_play_table AS t1
    UNION ALL
    SELECT 'temp_table' AS table_name, t1.c1
        FROM dbo.temp_table as t1
    ORDER BY 1, 2;


texts: Converting (a) an entity-relationship diagram about an airline booking scenario to (b) a relational schema

texts:
I am currently following notes on a certain "7 step strategy" to transform an ERD to a relational schema and was wondering some points about step 4, converting one-to-many (1:N) relationships.

The relevant ERD is the following one:

Questions

Would SEAT contain

Seat_no, Date (from LEG_INSTANCE)

or

Seat_no, Customer_name (from RESERVATION), Cphone (from RESERVATION), Date (from LEG_INSTANCE)?

Would FLIGHT_LEG contain Scheduled_dep_time & Scheduled_arr_time?


texts: Remote Scan when updating using functions

texts:
Why does using ISNULL() when updating through a linked server use Remote Scan to retrieve all rows and filter locally instead of filtering through Remote Query?
UPDATE LINKEDSERVER1.database1.dbo.table1 WITH(ROWLOCK)
SET number = ISNULL(number,0)
WHERE accounts = '123'

UPDATE LINKEDSERVER1.database1.dbo.table1 WITH(ROWLOCK)
SET number = number
WHERE accounts = '123'

The execution plan is as below:

The table does have a nonclustered unique index for the accounts column, but ISNULL() is not used on the accounts column and the index should still be usable. Am I missing anything here?


texts:
I need to write a trigger that will keep one table synced with 2 other tables. I have table_a, table_b and table_c on slave. The master has only table_a and whenever this table is updated on master, the two other tables on slave should also get updated using triggers or any other way.

In other words, I need to replicate only one table from this slave. Since there is no budget for one more server, I will use the same slave schema. Do I need to write after update, insert and delete triggers on table_a? What is the best approach?


texts: Postgresql trigger - get all changed rows after transaction

texts:
I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction.

CREATE CONSTRAINT TRIGGER invoice_check AFTER INSERT OR UPDATE OR DELETE ON invoice INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE invoice_total_price();

But this trigger is executed for each row. I believe it is waste of resources to check invoice for all changed row. I only need to get all the invoices which need to be checked.
I use django ORM where you cannot easily determine, which columns were changed. So column trigger is not the best thing I think.

EDIT:

Here is the code of invoice_total_price function:

CREATE OR REPLACE FUNCTION invoice_total_price() RETURNS TRIGGER AS $trigger_body$
DECLARE
    invoice RECORD;
    sum_prices DECIMAL;
BEGIN
    SELECT * INTO invoice FROM invoice WHERE invoice.id = NEW.invoice_id;
    SELECT SUM(invoiceitem.price_vat) INTO sum_prices FROM invoiceitem WHERE invoiceitem.invoice_id = invoice.invoice_id;
    IF invoice.total_price_vat != sum_prices THEN
        RAISE EXCEPTION 'Invoice total_price_vat is not equal to sum of price_vat of its items';
    END IF;
    RETURN NEW;
END;
$trigger_body$ LANGUAGE plpgsql;


texts: Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008

texts:
Are there better alternatives to setting the Row Locks and Page Locks in SQL Server 2008?

The case here is to set the row and page locks to false on the table. The lock is in effect during a series of insert, update and delete actions on the table which are run in a batch.


texts: Power BI on-prem data gateway for Azure devops pipeline?

texts:
I'm building a POC for DB devops using redgate SCA and an azure devops pipeline. Need to connect to our SQL Servers that don't have public IPs. Was hoping power BI on-prem gateway would facilitate. Is there a way to make that work, or do I need local build agents?

To clarify, I have :

Azure devops (cloud) on org.VisualStudio.Com
RedGate SQL Change Automation project in: TFSVC based repo (in azure devops services in org.VisualStudio.Com
Integration DB on SQL Server instance in company network - no public IP.

I now have a azure devops build pipeline triggered by source commit. The SCA build artifact is created and stored.  I want to deploy to our integration SQL Server DB.

At this point, I believe I need to install and configure a local windows build agent to enlist in the pipeline. It seems unfortunate, though, since we already have power BI on-premises data gateway. It would be nice to leverage that connectivity in our deployment pipeline instead of having to configure a local build agent.

It's also unfortunate because I'm finding the online docs for configuring a local build agent to be challenging and unclear.

Can you help me get over this hurdle?


texts: What is VPS\example.com_web identity in SQL server?

texts:
I was facing connection failed for requested login in SQL server 2016 used for example.com domain on a VPS. I added the following logins to the database NT AUTHORITY\IUSR and NT AUTHORITY\NETWORK SERVICE and IIS AppPool\SampleAppPool but the login failed untill I added the VPS\EXAMPLE.COM_WEB (found from the advanced search) and login was successful.
What is the identity of EXAMPLE.COM_WEB? It is not a windows user, nor application pool nor ISUR and SERVICES. I have to mention that there is SolidCP installed on the server. I don't know if these kind of identity is related to the solidCP and why it is needed to connect to database?


texts: Does `SERIAL PRIMARY KEY` create two indexes on the table?

texts:
Since SERIAL is shorthand for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, it looks like SERIAL PRIMARY KEY doubles up on the UNIQUE and PRIMARY KEY:

> CREATE TEMPORARY TABLE foo (id SERIAL PRIMARY KEY);

> SHOW CREATE TABLE foo \G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

There's definitely the wtf factor of seeing both PRIMARY KEY and UNIQUE KEY id in SHOW CREATE TABLE, but is that really two physical indexes I'm seeing?

Should one avoid using PRIMARY KEY with SERIAL and instead opt for writing bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, or using SERIAL with no primary keys on the table?


texts:
The following select query from my application is taking more than 200ms. The table has around 2 million rows. Is there any ways to improve the performance of this query ?

select count(id) from table1 where status='A' and empID=123

Table Indexes

PRIMARY KEY (`id`),

KEY `empID` (`empID`),
KEY `status` (`status`),
KEY `testID` (`testID`),

CONSTRAINT `fk_1` FOREIGN KEY (`empID`) REFERENCES `emp_info` (`id`)


texts: Changing the datatype of the primary key columns of partitioned tables

texts:
I am currently blessed with the following situation:

We have a production database that is partitioned and uses primary keys of type int
Multiple (~20) tables that each contain > 100.000.000 rows, 2 of these are near to 2.000.000.000 rows
These tables all have a column of type int as primary key which increments by one each row
These tables are partitioned, using a partition function of type int, using RIGHT(0), so effectively all data is in the one and only partition

Since some tables are nearing the int32 maximum value, we must alter these column types to bigint. Here's where the problems start:

Cannot modify the type of a primary key column (this I could dodge by dropping the primary key, modifying the type, and recreating the primary key again), however, I still cannot modify the column type after dropping the primary key constraint, getting the following exception:

  {"The object 'XXX_XXX' is dependent on column
  'ABCD'. ALTER TABLE ALTER COLUMN ABCD failed because one or
  more objects access this column."}

As far as I can see there is a schema bound dependency of the partition scheme (and function) to the table preventing the datatype modification. There are no foreign keys referencing these primary keys.

I'm not seeing a solution to updating the column type on this database, let alone a production database. We can afford some maintenance downtime, say +- 60 minutes, but no longer. What are my options?

Additional information

I'm starting to believe my only option is to create new tables with the correct structure and pump the existing table data into the new ones...
Tables are already partitioned based on their primary key value (currently just 1 partition (RIGHT(0), future partitions should be possible for new values (RIGHT(x)), the boundary x is yet to be determined)
These are data-logging tables, only inserts, no updates or deletes
There is always DML executed on these tables except for maintenance windows, once every 6-8 weeks for about 90 minutes tops.
The indexes based on the primary key are all clustered
There are no non-clustered indexes on these tables


texts: TempDB will not shrink. No Open Transactions

texts:
I have a TempDB on SQL 2008 that has gotten very large (>40gb) and I want to shrink it down.  I have used the dbcc shrinkdatabase, dbcc shrinkfile and the shrink command through Management Studio.

I get the following error:

  Page 1:4573184 could not be moved because it is a work table page.

I have been able to reclaim some space to get me out of danger by running DBCC FREEPROCCACHE and re running one of the shrink routines, but obviously this isn't ideal and will likely only buy me a bit time.

I have run DBCC OpenTran and there is nothing that is hanging out there.

Everywhere I have read on internet comes down to recycling SQL Server...surely there has to be a better way...anyone?

Thanks,

Tom


texts: MySQL - Get counts of `car models` by `car make`

texts:
I have a table with cars data, i.e. Makes, Models, & Year.

I want to get the count for each Car with how many models it has... something like the image below:

This is the SQL query I am using but it is giving me wrong counts. In fact, the number of rows against each car make is actually giving me my desired results.
SELECT `make`, COUNT(`id`) as `countmake`
FROM `cars_new`
WHERE 1
AND `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge')
GROUP BY `model`

Help me to fix this!


texts: MongoDB Replica Set member cannot start due "84 key/value already in index"

texts:
I have a Replica Set with 3 members (1 primary and 2 secondaries), and one of the secondaries was working just fine and thrown the following error

> 2018-03-12T11:03:54.868-0400 E REPL [repl writer worker 13] writer
> worker caught exception: :: caused by :: 84 key/value already in index
> on: { ts: Timestamp 1520867034000|8, h: 287037468373256260, v: 2, op:
> "u", ns: "Sitecore_analytics_PROD.Interactions", o2:
>
> { _id: BinData(3, EFC3DD1A15B75442986344FDE9CC71EB) } , o: { $set:
>
> { ContactVisitIndex: 2 } } } 2018-03-12T11:03:54.868-0400 I - [repl
> writer worker 13] Fatal Assertion 16360 2018-03-12T11:03:54.868-0400 I
> - [repl writer worker 13]
> ***aborting after fassert() failure

After that the MongoDB cannot start anymore, and keep throwing the same error!

How can I determine the root cause of it? Is there a way to prevent? How to solve it?


texts: In SQL Server, How Does Parallelism Change Memory Grants?

texts:
I've heard conflicting things about memory grants for parallel select queries:

Memory grants get multiplied by DOP
Memory grants get divided by DOP

Which is it?


texts: Can't get this PostgreSQL query to run faster

texts:
I'm running PostgreSQL 9.6 with PostGIS 2.3.3

I'm trying to make this rather critical query go faster (looking for users within 1000 meters of a location), but I'm having troubles setting up the right indexes.

Could someone point me in the right direction?

users are 200k rows, locations are 1200 rows, user_push_tokens are 155k rows

users table:

create table users
(
  id serial not null
    constraint users_pkey
      primary key,
  (an additional 20-ish columns),
  geo_point geometry(Point,4326)
);`

create unique index users_id
  on users (id);

create index users_geo_point_idx
  on users using gist(geo_point);

user_push_tokens table:

create table user_push_tokens
(
  user_id integer not null
    constraint push_tokens_user_id_fkey
      references users
        on delete cascade,
  push_token varchar(255) not null,
  push_provider varchar(64) not null,
  app varchar(64) default 'app'::character varying not null,
  id integer default nextval('user_push_tokens_id_seq'::regclass) not null
    constraint user_push_tokens_pkey
      primary key,
  active boolean default true not null,
);

create index trinity_unique_index
  on user_push_tokens (user_id, app, push_token);

create index user_push_tokens_token_fetch_idx
  on user_push_tokens (user_id, app, active);

create index user_id_index
  on user_push_tokens (user_id);

locations table:

create table locations
(
  id integer default nextval('locations_id_seq'::regclass) not null
    constraint locations_pkey
      primary key,
  (another 25 columns),
  geo_point geometry(Point,4326)
);

create unique index locations_id_key
  on locations (id);

create index locations_geo_point_idx
  on locations using gist(geo_point);

The query

EXPLAIN ANALYSE SELECT
    u.id AS user_id,
    upt.push_provider,
    upt.push_token
  FROM users u
    JOIN locations l ON l.id = 3896
    JOIN user_push_tokens upt
      ON upt.user_id = u.id AND upt.active = true AND upt.app = 'app'
  WHERE ST_DistanceSphere(u.geo_point, l.geo_point) <= 1000;

results in

Nested Loop  (cost=26087.06..63658.87 rows=30605 width=107) (actual time=353.304..887.371 rows=2498 loops=1)
  Join Filter: (_st_distance(geography(u.geo_point), geography(l.geo_point), '0'::double precision, false) <= '1000'::double precision)
  Rows Removed by Join Filter: 89539
  ->  Index Scan using locations_id_key on locations l  (cost=0.28..8.29 rows=1 width=32) (actual time=0.009..0.014 rows=1 loops=1)
        Index Cond: (id = 3896)
  ->  Hash Join  (cost=26086.78..39090.07 rows=91815 width=139) (actual time=352.437..657.228 rows=92037 loops=1)
        Hash Cond: (upt.user_id = u.id)
        ->  Seq Scan on user_push_tokens upt  (cost=0.00..7162.82 rows=91815 width=107) (actual time=0.032..103.512 rows=92037 loops=1)
              Filter: (active AND ((app)::text = 'app'::text))
              Rows Removed by Filter: 62437
        ->  Hash  (cost=22114.46..22114.46 rows=195546 width=36) (actual time=352.199..352.199 rows=195589 loops=1)
              Buckets: 65536  Batches: 4  Memory Usage: 3563kB
              ->  Seq Scan on users u  (cost=0.00..22114.46 rows=195546 width=36) (actual time=0.014..214.976 rows=195589 loops=1)

More readable explain output

Thank you for your time.


texts: Am I violating any NF rule on my database design?

texts:
I am a newbie on creating database... I need to create it for my recruitment web application.

My application needs to schedule screenings, exams and interviews of applicants and save result in the database..

My database schema is as follows:

My problem is I included the applicant_id on other tables... e.g. exam, interview, exam type.

Am I violating any normalization rule?  If I do, what do you recommend to improve my design?


texts: How does SQL Server determine key column order in missing index requests?

texts: How does SQL Server determine the order of key columns in its missing index recommendations for a query plan?


texts: How to determine if a timestamp in one table is between two timestamps in a subquery of another table?

texts:
I have two tables - one titled "index" which contains the 'datetime' timestamp with time zone, and one titled "planning" which contains the 'start_time' and 'end_time' timestamps.

Here are the schema of the two tables (edited for readability):

                 Table "public.index"
    Column     |           Type           | Modifiers
---------------+--------------------------+-----------
 datetime      | timestamp with time zone | not null
 pln           | boolean                  |

                     Table "public.planning"
      Column      |           Type           |         Modifiers
------------------+--------------------------+----------------------------
 name             | text                     |
 start_time       | timestamp with time zone |
 end_time         | timestamp with time zone |
 macro            | text                     |
 state            | text                     | default 'deselected'::text

I would like to assign TRUE to the items in the "index" table if their 'datetime' timestamp falls between a 'start_time' and 'end_time' in a subquery of the "planning" table.  Here is my attempt to do so:

UPDATE index
SET pln=TRUE
WHERE datetime BETWEEN (SELECT start_time
                        FROM planning
                        WHERE state='selected' AND macro NOT LIKE 'mae%')
                   AND (SELECT end_time
                        FROM planning
                        WHERE state='selected' AND macro NOT LIKE 'mae%');

which results in the error:

ERROR:  more than one row returned by a subquery used as an expression


texts: Data mismatch on Subscriber Database in SQL server transactional replication

texts:
We have Transactional replication set up between an OLTP Database server 'A' and reporting DB server 'B', where the Data from OLTP DB(Size 400 GB) "AR" is getting replicated on the server B as database "ARB".

There have been issues reported saying for the Data mismatch on the subscriber database ARB. As per them the latest data is for last two weeks and it should have been the latest instead.

We tried to figure if replication is broken or any error message at publisher or subscriber, but no luck.

In Replication monitor the status shows 'OK'

And status "Running"; Performance "Excellent" and latency varies between 2-3 seconds.

Please help me on how can i find for what could have been possibly going wrong where data does not match between both the DB.

I am new on Tran Replication, any inputs/helps would be appreciated, thanks!


texts:
When developing server-side stored procedures performance measurement is an important issue. EXPLAIN ANALYZE is very usefull, but when it comes to simple queries like SELECT name FROM clients WHERE ... execution time varies from time to time which makes it difficult to compare two different approaches to the same relatively simple task.

The method I use to solve this problem is as follows:

--1st approach
CREATE OR REPLACE FUNCTION perf_test_q1()
    RETURNS VOID AS
$BODY$
DECLARE
    i bigint;
BEGIN
    FOR i IN 1..1000000 LOOP
        SELECT name FROM clients WHERE id<10 AND id>1;
    END LOOP;
END;
$BODY$
    LANGUAGE plpgsql VOLATILE;
--2nd approach
CREATE OR REPLACE FUNCTION perf_test_q2()
    RETURNS VOID AS
$BODY$
DECLARE
    i bigint;
BEGIN
    FOR i IN 1..1000000 LOOP
        SELECT name FROM clients WHERE id IN (2,3,4,5,6,7,8,9);
    END LOOP;
END;
$BODY$
    LANGUAGE plpgsql VOLATILE;

And the explain analyze clause:

EXPLAIN ANALYZE select * from perf_test_q1(); --"Total runtime: 15188.418 ms"
EXPLAIN ANALYZE select * from perf_test_q2(); --"Total runtime: 17219.514 ms"

Is it valid (legit?) to measure performance of STABLE functions (which SELECT is in this case) by looping them? Or in other words, does PostgreSQL  planner automaticly decide if the select query is stable or volatile?


texts: Best Practice for database design (storing images to a database)

texts: I am currently developing a webpage for a real estate (using Java server faces) ,so it is essential to store images connected to its house. I am using mysql workbench and I really have a hard time deciding how to implement this feature. To begin with, I don't know what field of mysql workbench I can use in order to store images. Searching with google I found out about a blob field but I don't know how can I convert it to this blob type. Also I don't know if it is a good idea to store images on a database at all. Another approach I came up with is to have a VARCHAR field with a path to a folder of images. Which is better? thx in advance


texts: .Net Core connect to MS SQL Server Database using AD user creds

texts:
I'm trying to connect a .Net Core application running in a Linux container to a SQL Server database. Our DBA's require us to use AD users to connect to SQL Server. Another team is using NodeJS and seriate to connect using an AD user and password. Using those same creds in .Net Core is giving me a Login failed for user error. Am I missing something in the connection string?
DataSource=MyServer;Initial Catalog=MyDatabase;User Id=MyUsername;Password=MyPassword;Encrypt=true;


texts: Is it possible to define a function to prevent repeating lots of `case when` in transact sql?

texts:
A meta post gives this code

Select top 500
(case
    when lower(title) like '%open source%' then 0
    when lower(title) like '%recommend%'   then 1
    when lower(title) like '%free%'        then 2
    when lower(title) like '%good%'        then 2
    when lower(title) like '%best%'        then 2
    when lower(title) like '%better%'      then 2
    when lower(title) like '%any%'         then 2
    when lower(title) like '%what%should%' then 3
    when lower(title) like '%which%should%'then 3
    when lower(title) like '%is there%'    then 3
    when lower(title) like '%looking for%' then 4
    else 5 end
),
left(title,25) --as [Post Link]

,ROW_NUMBER() OVER (

  PARTITION BY(case
      when lower(title) like '%open source%' then 0
      when lower(title) like '%recommend%'   then 1
      when lower(title) like '%free%'        then 2
      when lower(title) like '%good%'        then 2
      when lower(title) like '%best%'        then 2
      when lower(title) like '%better%'      then 2
      when lower(title) like '%any%'         then 2
      when lower(title) like '%what%should%' then 3
      when lower(title) like '%which%should%'then 3
      when lower(title) like '%is there%'    then 3
      when lower(title) like '%looking for%' then 4
      else 5 end
  )
  ORDER BY (case
      when lower(title) like '%open source%' then 0
      when lower(title) like '%recommend%'   then 1
      when lower(title) like '%free%'        then 2
      when lower(title) like '%good%'        then 2
      when lower(title) like '%best%'        then 2
      when lower(title) like '%better%'      then 2
      when lower(title) like '%any%'         then 2
      when lower(title) like '%what%should%' then 3
      when lower(title) like '%which%should%'then 3
      when lower(title) like '%is there%'    then 3
      when lower(title) like '%looking for%' then 4
      else 5 end
)) AS Number

from posts

where (title like '%library%'
   or title like '%tool%'
   or title like '%book%'
   or title like '%tutorial%')
and closeddate is null
and deletiondate is null

In Python, Java, and lots of other language, I can define a function to prevent repeating lots of case when. Is there similar syntax in transact sql?


texts: creating a trigger based on time after one year in mysql

texts:
My database has two tables: plans and status. plans has an attibute issue_date.

I want to create a trigger such that when the issue_date crosses one year, the attibute status in the status table should become inactive.


texts: Is Neo4j 2 replication architecture a master-slave or multi-master?

texts:
In the new docs of Neo4j (23.1. Architecture) it says that:

  When running Neo4j in HA mode there is always a single master and zero
  or more slaves. Compared to other master-slave replication setups
  Neo4j HA can handle write requests on all machines so there is no need
  to redirect those to the master specifically.

And in wikipedia about Multi-master replication

  Multi-master replication is a method of database replication which
  allows data to be stored by a group of computers, and updated by any
  member of the group. All members are responsive to client data
  queries. The multi-master replication system is responsible for
  propagating the data modifications made by each member to the rest of
  the group, and resolving any conflicts that might arise between
  concurrent changes made by different members. Multi-master replication
  can be contrasted with master-slave replication, in which a single
  member of the group is designated as the "master" for a given piece of
  data and is the only node allowed to modify that data item. Other
  members wishing to modify the data item must first contact the master
  node. Allowing only a single master makes it easier to achieve
  consistency among the members of the group, but is less flexible than
  multi-master replication.

Is that means that is a multi-master architecture? What is the difference if not. Thanks


texts: Postgres multi tables vs single table performance

texts:
I am building a db using PostgreSQL for a survey application. The legacy system uses separate tables for each survey which has around 1 million rows each(maximum). Since the columns are exactly the same for all the tables, I was wondering if having separate table is more efficient than having a single table with an Foreign key column pointing to the survey id instead of having separate table for every survey id.

What is the trade-off and which approach is better scalable?


texts: Should I represent two distinct relationships via only one associative table?

texts:
I have a database to store the news of my website. The news entity type is associated with two different ones; images (which from the point of view of the app users are grouped in a gallery) and categories.

To represent this situation this, I have some tables like this:

image
id | src

category
id | name

news
id | title | text | author | ...

It is important to mention that each news may have multiple images and belong to multiple categories. So, to create the relationship between images, categories and news, I'll use another table, but my question is:

Should I have (a) two tables (one for image rows and other for category rows) or (b) just one with multiple columns? For example:

(a) Two distinct tables, one called news_relationship_category, with the column headings

id | id_news | id_category

and another one, named news_relationship_gallery set up as follows

 id | id_news | id_gallery

(b) One single table, entitled news_relationships with the headings

id_news | id_category | id_gallery

and INSERT INTO it rows with some columns that accept NULL marks, for instance:

10 | NULL | 3
---+------+------
10 | 27   | NULL
---+------+------

These are simple examples, but imagine if I have an e-commerce table, with products involved in multiple relationships. What is the best way to approach this case?


texts: Creatin Products Database with Multiple Color Variations

texts:
I found this Q&A already, that describes my problem and a more or less satisfying answer:
Create products with color variants
Basically, I want to set up a database for products with different color variations. Here the solution is given of setting up a product-table, a color-table, and a product-color-table as a combination of both.

Unfortunately, most of my products are available in ~180 different colors and we have about 8000 different articles. Additionally, every product has at least one standard color (up to ~5 standard colors) out of the 180 colors in which the product is cheaper than in other colors.

Do I really have to set up a database as described before, which would lead to over 1 Mil articles, or is there another way?
I thought of setting up a product-color-table in the sense, that there are 180 columns for each color and I just assign them values like "available, not-available, standard" or so. And then all the other information that don't really affect the price of the products in yet another table.

But since I'm new to setting up databases I don't really how I can realise it. Would that actually work? Can anyone give me any tips on how I can solve that and set up my tables?

Thanks in advance

PS: In case my description was confusing here is an example of an onlineshop I found that has the exact thing I want to do:
When you tap on "Ciew colour range" you can see all the different colors. But then there are also other articles that are available in less colors or different standard-colors.


texts: How to sync MySQL's time with system time without mysql restart?

texts:
On my machine I have a service to synchronise the SYSTEM time (including Time zone), and would like the DB will synchronize with the machine time once in a while.

When I change SYSTEM time, MySQL keep giving the old time when I use CURRENT_TIMESTAMP, It synchronize it only when I restart the MySQL service.

I would like to ask: is it possible, by update some system variable, or anything else, to set the DB time, to be equal to the SYSTEM time, or any other time, without a restart of the service?

I don't want to stop the DB at all. I think that maybe a sceduled task, which run once in a while, can be set, but what should it do?

To accept answer, it must pass the following test:

# change system time, date or timezone on the machine
select CURRENT_TIMESTAMP from dual; #--still show old time
call sync_time; #or some sync date + time + timezone commands...
select CURRENT_TIMESTAMP from dual; #--will show updated time

I just need the sync_time procedure, that will cause the last select to show the machine date and local time.

@rathishDBA answer didn't helped at all, it only changed the MySQL variables, not sync the DB time. I tried it as the 'root' user and both first and last select gave me the same unsynced result.

For example see this picture:

System time = 2:07
CURRENT_TIMESTAMP = 11:37


texts: Incorrect query results for execution plan, unsure of exact cause of problem

texts:
We had a query yesterday that was intended to page through results but on the first page of results the query only returned 4 records instead of the expect 25. Changing the query in any way resulted in 25 records which meant to me that it was an execution plan issue. I'm not familiar with a way to view execution plan in production and running the query in sql studio didn't result in same problem, probably due to minor differences.

I read suggestions that such a thing could be caused by a corrupted index. I ran a checkdb on the database and it found no errors. In the end I cleared the execution plans and all has been well.

If it wasn't a corruption issue of some kind and only a problem for the very specific execution plan then does that mean there was simply an error in the execution plan and we are hitting a bug in SQL Server? We are on SQL Server 2012 RTM with no updates so I looked through documentation on all fixes in cumulative updates and service packs but none of the issues appeared to related to our own.

Any other ideas or thoughts as to what could cause this?

(@P1 varchar(8000),@P2 bit,@P3 varchar(8000),@P4 bit,@P5 varchar(8000))

SELECT e.*
FROM (

    SELECT

        TOP 25
        ROW_NUMBER() OVER (
            ORDER BY AddedDate DESC
        ) AS Row,

        ID
        , Prefix
        , FirstName
        , LastName
        , Company
        , Address
        , City
        , State
        , Zip
        , Country
        , WorkPhone
        , HomePhone
        , MobilePhone
        , Email
        , MailingLists
        , AddedDate
        , AwaitingOptin
        , OptInDate
        , Processed
        , ProcessedDate
        , Deleted
        , Source
        , GRRecID
        , DatabaseID
        , (SELECT COUNT(*)
            FROM TableA
            LEFT OUTER JOIN TableB ON TableA.GRRecID = TableB.GRRecID
            WHERE TableA.AccountID = @P1
                AND TableA.Email = TableC.Email
                AND TableA.Deleted = @P2
                ) AS Matches
    FROM TableC
    WHERE AccountID = @P3
        AND Processed = @P4
        AND Deleted = 0
        AND Source = @P5
) AS e
WHERE Row >= 1 AND Row <= 25
ORDER BY Row


texts: Restoring a differential backup in SQL server 2005

texts:
I have created a full back up of my database  by executing the line below in my application:

Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'"

The *.bak file created successfully.

Then I restored it on another Database called test and it was ok.
Then I created a Differential backup by means of the following code:

Dim Query As String = "BACKUP DATABASE Ari TO DISK='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'" & "WITH DIFFERENTIAL"

and it created a *.bak file ,but when I try to restore it on the database I mentioned as test ,I receive the following error:

Please help me solve this problem.


texts: Is it safe to risk multiple Firebird nbackup -L?

texts:
I'm using Firebird 3.0 nbackup to lock + copy + unlock each night.

If some steps fail or are skipped e.g. due to system restart (updates), it may happen that the master is not unlocked (-N). Next night, it will already be in locked state, but the scheduled backup task will lock it again (-L).

Is this safe?

Will the second -L have any effect, or is it a no-op, so a single unlock (-N) will restore it to normal state? Or do I have to safeguard against a second lock (-L) if it's already in locked state?


texts: How can MySQL's data types with 4GB limits be fully used since max_allowed_packet is hard limited to 1GB?

texts:
The MySQL types LONGTEXT and LONGBLOB are documented with data limits of L + 4 bytes, where L < 2^32 (~4GB). But max_allowed_packet is only configurable up to 1GB.

  The largest possible packet that can be transmitted to or from a MySQL
  8.0 server or client is 1GB.

Wouldn't that mean the limit of LONGTEXT and LONGBLOG is actually ~1GB? The JSON type is limited by max_allowed_packet but the text/blob types do not mention the same limitation. Are there ways to work with those types, maybe in fragments, to work within the packet limit?

Note: I understand storing data larger than 1MB in a DB column is discouraged for performance and design reasons. I'm just wondering how I'd ever store or retrieve more than 1GB in a TEXT/BLOB if it's ever required.


texts:
I have the query below that returns all databases and full backup history with the last backup date and device name.  I am trying to modify the query to only return all the databases and just the last backup date and file location.

So now I get:

DatabaseA - 02/25/2014 - \\FileLocation
DatabaseB - 02/25/2014 - \\FileLocation
DatabaseC - 02/25/2014 - \\FileLocation
DatabaseA - 02/24/2014 - \\FileLocation
DatabaseB - 02/24/2014 - \\FileLocation
DatabaseC - 02/24/2014 - \\FileLocation
DatabaseA - 02/23/2014 - \\FileLocation
etc,etc

I would like to get:
            (Most Recent Full - Last File Location)
DatabaseA - 02/25/2014 - \\FileLocation
DatabaseB - 02/24/2014 - \\FileLocation
DatabaseC - 02/25/2014 - \\FileLocation
DatabaseD - 02/25/2014 - \\FileLocation

SELECT bs.database_name AS DatabaseName, bs.backup_finish_date, MAX(bms.physical_device_name) AS FullBackupName
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name
WHERE bs.type IN ('D')
AND s.name NOT IN ('master', 'msdb', 'model')
GROUP BY bs.database_name , bs.backup_finish_date
ORDER BY bs.backup_finish_date DESC


texts:
The Oracle 11gR2 Automatic Storage Managament Administrator's Guide says:

  When Oracle ASM allocates an extent for a mirrored file, Oracle ASM allocates a
  primary copy and a mirror copy. Oracle ASM chooses the disk on which to store the
  mirror copy in a different failure group than the primary copy.

It also says:

  If you omit the failure group specification, then Oracle ASM automatically
  places each disk into its own failure group, except for disk groups containing disks on
  Oracle Exadata cells.

My question is for the following scenario how ASM will create failure groups:

In my server when there is one disk controller and one physical hard disk having many logical volumes and created disk groups.
When server is having two controller card and two or more hard disks with logical volumes created and used for diskgroups.

My main question is, I want to understand how this ASM decides failgroups? Please some one can guide


texts: How to detect JSON data type support in MariaDB/MySQL

texts:
My first thought was to check server version but it is not the nicest solution.
Is there something like PHP's function_exists() in mysql?

I'd like to detect JSON_EXTRACT() or the JSON data type: CREATE TABLE t1 (jdoc JSON);

Thank you.


texts: Oracle 12C silent installation - response file

texts:
I am installing Oracle 12c using a response file. The installer exits after a while, with the following error message:

ERROR: Cannot add user to application access ACL. No mapping between account names and security IDs was done (534)

The installer exits but Oracle 12c partially installs. When I open SQL Plus, I get the error message:

ORA-12560: TNS:protocol adapter error

I don't have these problems with a manual installation, only using the response file.

What am I doing wrong?

Thanks


texts:
I am very new to this stuff. I just wanted to ask if somebody has a good my.cnf for my MariaDB database because every time i have more than  1000 connections to my website it starts to have hight load time up to 16 Seconds under this value it loads instand.

My mysql settings are very default i only changed the max_connections.

max_connections        = 10000

Server Specs:

CPU: Intel i7-4790K 4c / 8t 4GHz
RAM: 16 GB DDR3
Debian 9
PHP Version 7.0.33
Apache 2.4.25
MariaDb 10.1.41


texts: How to block SSMS from accessing the internet?

texts:
How do I block SQL Server Management Studio from accessing internet sites?
Let me set the stage.  I'm sitting behind a Bluecoat proxy server at work.  We have a management policy forbidding us to access the internet using a privileged account.  And I need to use a privileged account to run SSMS to perform database administration.
So to perform database administration, I "runas" SSMS with my privileged account.  I suspect SSMS is opening a connection to a microsoft.com site for unknown reasons.  The Bluecoat proxy server sees this unauthorized internet connection, gets mad at me for violating company policy, and blocks internet access to my IP address for the next hour.
I'm aware that SSMS checks for updates by default at startup.  I've turned that off.  Something else is going on.


texts: Importing schema to new or different tablespace

texts:
Is there a convenient way to import a schema into Oracle 11gR2 using a single new or different tablespace than where the data originated?

As an example, I have exported BLOG_DATA from OLDDB, where all user data is stored in the USERS tablespace.

On NEWDB, I would like to import the BLOG_DATA schema, but store the user objects in the BLOG_DATA tablespace, created specifically for this user.

I have created the BLOG_DATA user, created the BLOG_DATA tablespace and set it as the default tablespace for that user and added an appropriate unlimited quota.

CREATE TABLESPACE blog_data DATAFILE SIZE 1G;

CREATE USER blog_data IDENTIFIED BY secretpassword DEFAULT TABLESPACE blog_data QUOTA UNLIMITED ON blog_data;

GRANT connect,resource TO blog_data

The schema was exported from OLDDB with something like

exp blog_data/secretpassword@OLDDB file=blog_data.dmp

After reading Phil's excellent answer below, I found myself wondering:

Since data has no other place to go than the default tablespace - the only tablespace the user has a quota on - will this effectively force imp to put all user objects in that default tablespace?

imp blog_data/secretpassword@NEWDB file=blog_data.dmp

Would this then place the entire blog_data schema in the blog_data tablespace on NEWDB? Is there any reason why this wouldn't work or that I would run into problems with certain objects etc?

update:

I did a quick test and found this to be the case. Imp places objects in the default tablespace for that user, provided it can't place it in the original tablespace (eg. the tablespace does not exist). Full explanation: http://www.dolicapax.org/?p=57

Still, I suppose that using Data Pump like Phil suggests might be the preferred option.


texts: How to get the timezone from a PostgreSQL timestamp

texts:
This is probably a very simple question, but it's hard to Google because it matches too many different questions.

I want to get the timezone (or offset) of timestamps saved in my database.

For example, right now in my database I have

     expired_at
---------------------
 2018-04-28 00:00:00
 2018-03-28 08:00:00
 2018-02-28 05:00:00

I want to find all expired_at that has the PST offset.

So my pseudo-code would be something like this

SELECT expired_at FROM table WHERE expired_at IS IN TIMEZONE('PST')

Can I get some help. Thanks!

PS. Basically I am trying to fix a data integrity issue by finding all UTC timestamp and updating them to be PST. The database is screwed up right now with a mix of many different timezones.


texts: Cannot connect to integration services remotely on one machine

texts:
Something on workstation has broken the ability to get into any integrated services.  SMS and SQL (ssis) are same versions.

The error "The specified service does not exist as an installed service" comes up immediately.  This issue has been narrowed down to this workstation, same user works on other workstations.

What can we check/verify on this workstation?  TLS?  SSL?  Not firewall issue, this workstation worked at one point.

Could installing SQL 2017 DEV edition on the workstation cause this issue?


texts: Can we put an equal sign (=) after aggregate functions in Transact-SQL?

texts:
I have encountered a script like this:

set @sum = sum = (case when somecol1 is null then DATEDIFF(d,[somecol2],somecol3) else 0 end)

I can't understand the meaning of the equal sign (=) after the second keyword sum. When I run the query it is not showing any errors both with the equal sign and without.

I want to know the purpose of putting an equal sign after the keyword sum. Is that a mistake or not?

Thanks


texts: Rare error lasts only a second "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146)" 146=ECONNREFUSED

texts:
This is a rare error that occurs on Solaris 10, have seen it from Perl, where this line
DBI->connect(...) or die "Whoops! $DBI::errstr"

outputs Whoops! Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146) at something.pl line ...

I determined that on my system, 146=ECONNREFUSED

It is very rare for this error to occur, but it happens on an ongoing basis.
It always a temporary problem on this setup—lasting only a second or two.

The global variable max_connections has been ruled out as a problem.

This does not occur on TCP/IP based connections.

I am using the [mysqld_safe] log-error option in my.cnf.
The log outputs the following at startup:
mysqld started
InnoDB: Started; log sequence number...
[Note] .../libexec/mysqld: ready for connections.
Version: '5.0.27-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
There are no other log entries, so there's nothing here to indicate the problem.

Please let me know if you can suggest a setting for more detailed logging.

Can you enumerate the possible or likely causes of this error happening only occasionally and only for a second or two?


texts: Table structure/schema for spreadsheet-like web app (ex: Google Docs)?

texts:
I'm a front-end developer/designer who is trying to branch out and learn more about the back-end world. I've chosen Python, Django, and Postgres as my starting place.  My goal is to build a personal web app, and use that as my catalyst for learning. It's been really fun.

I'm at the stage where I'm designing the data model. I would normally tinker and figure things out, but in this case I'm curious what is possible.

A good analogy for my app's data model is a spreadsheet. The user can create a "spreadsheet", then determine "columns", and subsequently the data type per column.

So, as a pure example, a new sheet has the following columns:

Name (text)
Cost (number / currency)
Date (time stamp
Frequency (number)
Notes (text)

Then, "rows" can be added where each entry has values for name/cost/date/frequency/notes, as well as some meta data like who created the row, at what time, etc.

I'm unsure what is the best schema to accomplish this example.  Below is the best idea I came up with.  I'm wondering if anyone would be willing to comment on whether this is the best approach, or give feedback on a different direction.


texts: Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?

texts:
Is it possible to create an InnoDB table without a Primary Key?

Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that table still be a clustered index, but around a secondary key?

Thanks.


texts:
I have successfully set up Replication. The problem is Snapshot Agent won't start. When I try to start it manually I always end up with "The agent has never been run."

Publication runs under the service account - db_owner in source db for the Publication, msdb, distribution db and with read/write permissions to snapshot folder.

I'm asking for suggestions how to deal with this situation.
Thank you.


texts: "Could not find server error" when using INSERT SELECT statement in SQL Server

texts:
I'm trying to copy a record from a production database table into a staging database table.  They are both SQL Server databases on Amazon RDS.  My query looks something like this:

INSERT INTO [neonpig.staging1.us-east-1.rds.amazonaws.com].[neonpig_prod_db].[dbo].[AspNetUsers] SELECT * FROM [neonpig.prod1.us-east-1.rds.amazonaws.com].[neonpig_prod_db].[dbo].[AspNetUsers]

But this seems to produce the error below:

  Could not find server 'neonpig.prod1.us-east-1.rds.amazonaws.com' in
  sys.servers. Verify that the correct server name was specified. If
  necessary, execute the stored procedure sp_addlinkedserver to add the
  server to sys.servers.

I've ensured that the server address are correct.   Anyone know what I'm doing wrong?

Note that the database name is the same on both the prod server and the staging server.


texts: How to model (a many to many) to many relationship

texts:
Hello i am facing the following scenario. I have a student table with the following structure.

Student
id(PK), matriculation number, first_name, last_name, email

A student can have more than one Majors(BSc mechanical engineering, MSc mechanical engineering, etc...)

Major
id(PK), major

At the same time a major may be assigned to more than one students, therefore the relationship between  student and major is many to many. I implemented it using the following mapping table.

MajorStudent
id(PK), student_id(FK), major_id(FK)

Every student however can sit for many exams. But for every exam it is important to know his Major. That is he can sit for the same exam as BSc mechanical engineering and as MSc mechanical engineering, but this should count as two different exams. Meanwhile every exam is assigned a grade, which can have one of four values. My exam and grade tables looks like this.

Exam
id(PK), major_student_id(FK), grade_id(FK)

Grade
id(PK), grade(value1, value2, value3, value4)

What i am trying to get at the end is the following

View
student_id | first_name | last_name |... | major      | exam_id | grade
1            test1       testtest1        BSc M.engin   1         value1
1            test1       testtest1        MSc M.engin   2         value3

Do you think my desing is correct. And in general how would you go about modeling  a many to many to many relationship? (If it helps i am using MySQL and cakephp to implement it)


texts: Datatype for phone number: VARCHAR, INT or BIGINT?

texts:
So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition:

Take a look at the column from_number which is a VARCHAR(45) right now but it will hold a phone number. Since I don't know how many numbers a phone could have all over the world then I am trying to cover almost all of them. I want to keep database integrity as much as possible so I think VARCHAR is not a proper type for hold this kind of information - maybe I am wrong, you tell me - so I am thinking in change to INT or even BIGINT.

When I am defining a column in Workbench I should specify the number between parentheses () not in all the cases but in those I mention previous I had to. So if I do this: BIGINT() I got this error:

Which guide me to read a bit about this MySQL type here. Basically the info is this:

  A large integer. ... The unsigned range is 0 to 18446744073709551615.

Which make me ask: what value I should set for parentheses when I am defining a BIGINT() type. (I am using BIGINT because I don't know if INT can hold as many numbers as a phone could have - perhaps I am wrong too). Which is the right way to create|design a column in MariaDB/MySQL databases?

Anyway I would like to know your opinion, experience and of course I would like to get an answer

Note: I am using MySQL Workbench latest edition for create the ER diagram. I am using also MariaDB 10.0.x


texts: AWS Aurora running out of freeable memory and crashing

texts:
We have an issue with our AWS Aurora MySQL instance running out of freeable memory and crashing as a result. AWS's response was to upgrade to a bigger instance, but I don't feel that will necessarily solve the problem.

We are on a db.r5.large instance which is 15GB of RAM.

After a reboot it drops to 5GB of RAM which is pretty much expected, but then gradually declines over the course of a week to 0GB RAM and then reboots - and sometimes fails to do so and requires a manual reboot of the instance.

CPU usage generally hovers around 15% which spikes when we do heavy late-night processing.

From How large should be mysql innodb_buffer_pool_size?

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

gives 10GB as required to hold all data and indexes in memory.

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

gives 4.6GB in the InnoDB Buffer Pool

SHOW FULL PROCESSLIST

shows only a couple of processes and no hung threads.

My question is, is there a way of ensuring the freeable memory never goes past a certain point and is released back for usage e.g it should never fall below 2GB so if something is memory heavy, it won't exhaust memory.

I understand there might be a performance hit for this, but before I scale up to a bigger instance (at double the cost) and experience the same slow decline to 0, I'd like to see how it performs as it's better than a production machine crashing at random times.

Additonial information:

I've added the various MySQL outputs to https://pastebin.com/CkRxqL04

Not really sure how to run the Unix commands on the RDS instance as there's no access to the filesystem.

I checked other logs and it seems the CPU is steadily climbing too when it should burst and drop back to a normal level.

This is not a high traffic site, though it does do some heavy lifting behind the scenes.

While doing further digging, I noticed there's an attempt at synching to a slave still active from when the initial migration was done. Even as root I can't remove it, but could this be eating memory / CPU?

2019-08-29 00:30:04 7112 [Note] Error reading relay log event: slave SQL thread was killed
2019-08-29 00:30:04 7112 [Note] Slave I/O thread killed while connecting to master
2019-08-29 00:30:04 7112 [Note] Slave I/O thread exiting, read up to log 'mysql-bin-changelog.000016', position 904410367

UPDATE 19/09/09

The pattern still seems to be the same - CPU is going up and up - albeit gradually.

Slow log is empty (I know it's turned on as there was previously entries in it).

I also installed an APM tool and there is nothing suspicious coming up there - all script execute in the time they should as do the Db queries. We have some long-running CRON jobs (3 minutes), but they shouldn't affect the DB as they're on the webserver box.


texts: How to optimize a delete query with a subselect?

texts:
This query needs to delete over 17 million rows, from a table containing 20 million.

DELETE FROM statements
where agreement_id IN (
    select id FROM agreements
    WHERE created < DATE_SUB(CURDATE(), INTERVAL 6  MONTH)
);

DELETE FROM agreements
WHERE created < DATE_SUB(CURDATE(), INTERVAL 6  MONTH)

It takes hours to run, am I missing something that could speed things up a bit?

The subselect by itself takes a few seconds, I don't understand why the delete takes so long.


texts: Difference between MongoDB's find and findone calls

texts: I am working on a project and I am unsure if there is a difference between the way the find cursor works and the way the findOne cursor works. Is findOne just a wrapper for find().limit(1)? I was looking around for it and maybe someone knows if mongodb has a special method for it or not. I am working with the PHP API for mongodb if that makes a difference.


texts:
We are trying to archive our data for the first time in our database. The database is around 250 GB. Our approach is as follows. Create a copy of those tables without any index or constraints. And then dump the old data onto archive tables.And then remove the archive data in main tables.

For this either we can copy everything into archive tables,truncate main tables and then repopulate them. Or copy only necessary data to archive table and delete from main table. Considering the fact that main tables would have index and constraints on them which would make more sense? Also would it be better to disable  all indexes before this operation. If so, should I disable clustered index also?


texts: Best database choice for a highly scalable web ticketing system?

texts:
Essentially, we are developing using Ruby on Rails and want to make a highly scalable ticketing system. Essentially we will have a small database (containing event details and tickets sold) that will spike with large numbers of reads (users visiting the site, up to 1-2 million per day) and a low-ish level of writes (write when a user requests a ticket, and when they buy it).

The writes need to persisted to disk, but we are keen on keeping the whole database in memory (perhaps using memcached). Right now we are going to build this on Mysql (activerecord on RoR).

Question is, will this setup be scalable for our future needs where we may need to organize dozens of events with 10s of millions of hits.


texts: What does KEY SHARE OF in a PostgreSQL statement mean?

texts:
Given the following SQL, what does "ONLY", "OPERATOR(pg_catalog.=)" and "FOR KEY SHARE" do?
SELECT 1 FROM ONLY "public"."acmeinstanceinfo" x WHERE "widgetid" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

I have a fair amount of experience with Microsoft SQL server but zero with PostgreSQL. Any insights into what this query is doing would be great.


texts: Decrypt T-SQL log backup header and read LSN

texts:
For some reason I need to read the LSN from the T-SQL logs backups without restoring them or even their headers (I assume even restoring only their headers will change the LSN on the database side too, but I'm not sure).

So is the T-SQL log backup files encrypted or does they have special structure? Any information as to where should I start?

Could anyone confirm or denies that restoring the header only wouldn't affect the sys.fn_dblog or anything else?


texts:
I have a database of "records" that relate to photos - title, location, text, tags, etc. I am now adding more fields for other media types, videos for example will get an transcript, audio will get chapters.

I have been using Postgres until now and it works well but now my schema is moving towards a NoSQL model - each "record" conforms to two or three shapes.

Part of my wants to keep the data relational as there is the odd join - a record is a part of a collection, for example. However, I am struggling to see how full text search and unique ids across multiple tables would work - it seems as if the normalisation approach would have an images table, an audio table, a video table.

Any sage advice you can offer would be appreciated. I have the data in MongoDB and it works but I am not sure if I have missed a trick somewhere.


texts: Is it possible to make a SELECT wait until an ongoing UPDATE finishes?

texts:
Suppose that I am implementing a bank account logic using PostgreSQL. At some point, I will want to check if the user has sufficient balance to perform an operation. However, the user's balance might be in the middle of an UPDATE statement and, in my perception, any SELECT statements performed during this period will return the old balance. My questions are:

Is my perception correct, i.e. is it possible for the SELECT statement to get old data in this trivial scenario?
If yes, how do I implement locks to protect myself from this? I've read about FOR UPDATE in the docs, but I'm not sure it is meant for this specific situation.

Please assume that I cannot serialize the requests in the clients.


texts: Safe to use odbc link betw Filemaker 11 Server & SQL Server 2012?

texts: I’ve established a successful odbc connection between filemaker 11 Server and SQL Server 2012. Filemaker, inc has not certified this compatibility between the two but I have had no problems importing data from the sql database into filemaker. Does anyone know of risks utilizing the two databases? I need to understand better why this  connection works and why FileMaker’s specifications don’t include SQL 2012 Server!


texts: PostgreSQL equivalent of mysqld --bootstrap or mysqld --init-file

texts:
I'm trying to automate initializing a postgresql data directory, with a user and a default database pre-created. I know I can do this by running postgresql backgrounded and using createdb and createuser commands, but I really want to avoid that as this is for a docker container and I just want postgres to be running in the foreground once done.

In MySQL I use --init-file /path/to/bootstrap.sql, which runs the statements in the file before the server accepts connections. You can also use --bootstrap, which temporarily spins up mysqld and runs statements from STDIN.

I can't find an equally straightforward way to do this in Postgres. I can see a --boot option to postgres, but when I try and use it I just get "invalid command line arguments", so I must be doing something wrong.

echo "CREATE USER bob WITH PASSWORD 'secure';" | postgres --boot postgres -D data
postgres: invalid command-line arguments


texts:
How can I print the integer part of a number with commas separating every three digits?

For example,

 12345    -> 12,345
 1234.567 -> 1,234.567


texts: Why cannot I execute dbms_space_admin.drop_empty_segments despite granted?

texts:
Did i miss something in the documentation about granting execute on DBMS_% or SYS packages?

I have:

GRANT EXECUTE ON sys.dbms_space_admin TO DEVELOPER_ROLE;
GRANT DEVELOPER_ROLE TO my_user;
ALTER USER my_user DEFAULT ROLE ALL;

But when my_user tries this:

EXEC SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS()

I get:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

However, if only I grant directly - it works perfectly fine:

GRANT EXECUTE ON sys.dbms_space_admin TO my_user;
EXEC SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS();

What did I miss?


texts: SQL Server 2016 issue regarding migration of in-memory table and foreign keys

texts: Trying to use table memory optimization advisor on SQL Server 2016, seems I need to drop and re-create the foreign keys of some disk based tables, the size of the table is 300 GB and I'm wondering if there is any risk to drop and re-create the foreign keys before table memory migration?


texts: Unable to create a tablespace, unusual error

texts:
I'm having difficulty creating tablespaces with Oracle 11gR2 on a fiber attached disk array. Permissions are correct and there is ample space available. I can create tablespaces on other mounts that were formatted nearly the same (difference in stride size that reflect different stripe sizes), the only major difference I can determine is that one filesystem is local disk and the other is external. I can create files by hand as the oracle user without any difficulty whatsoever. There is nothing of interest getting dumped to /var/log/messages. Googling the error hasn't produced much useful information either. I am somewhat new to Oracle. Any help would be greatly appreciated. Thanks

Here is output creating a tablespace on the local disk:

SQL> CREATE TABLESPACE ts_internal_test DATAFILE '/dbidx/idx0/ts_internal_test.dbf' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;

Tablespace created.

Here is output creating a tablespace on the external disk:

SQL> CREATE TABLESPACE ts_external_test DATAFILE '/dbdata/vg00/lv0000/ts_external_test.dbf' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE ts_external_test DATAFILE '/dbdata/vg00/lv0000/ts_external_test.dbf' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL
*
ERROR at line 1:
ORA-01119: error in creating database file
'/dbdata/vg00/lv0000/ts_external_test.dbf'
ORA-27044: unable to write the header block of file
Linux-x86_64 Error: 22: Invalid argument
Additional information: 3

Here are permissions, creating a file as the 'oracle' user, free space and mount options:

$ su - oracle
$ whoami
oracle
$ cd /dbdata/vg00
$ ls -l
total 20
drwxr-xr-x 3 oracle oinstall 4096 Apr 26 12:05 lv0000
drwxr-xr-x 3 oracle oinstall 4096 Apr 26 10:38 lv0001
drwxr-xr-x 3 oracle oinstall 4096 Apr 22 18:14 lv0002
drwxr-xr-x 3 oracle oinstall 4096 Apr 22 18:14 lv0003
drwxr-xr-x 3 oracle oinstall 4096 Apr 26 10:18 lv0004
$ cd lv0000
$ ls
lost+found
$ echo "test file" > some_file
$ ls -l
total 20
drwx------ 2 root   root     16384 Apr 26 11:42 lost+found
-rw-r--r-- 1 oracle oinstall    10 Apr 26 12:08 some_file
$ cat some_file
test file
$ df -h | grep lv0000
/dev/sdc1             2.0T   71M  2.0T   1% /dbdata/vg00/lv0000
$ mount | grep lv0000
/dev/sdc1 on /dbdata/vg00/lv0000 type ext2 (rw,noatime)

NOTE: we are using ext2 due to limitations that we have to work around within our environment. The volume is currently mounted as (noatime,async) based off a recommendation I found in regards to Oracle and ext2/3. I've tried mounting with defaults but it didn't change anything.

-Daniel


texts:
impdp import is stuck on:

  Processing object type
  SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

How can you debug this?