texts: How can I allow part of a key to be null and enforce uniqueness of records with nulls?

texts:
I have an Access table called Engagement with four fields:

Emp_id, Year, Week, Act_id

It records when an employee is/was engaged in an activity.
Each field is Number and each makes up part of the composite primary key.
The semantics of the application was that each entry (each Emp_id-Year-Week-Act_id combination) must be unique.
That is, while an employee will generally have different year-weeks and different activities, an employee can sometimes engage in the same activity on different year-weeks or even engage in different activities in the same year-week. Nulls were not allowed. Everything worked.

Now I need to expand/modify the semantics to allow unknown – or more aptly, not disclosed – Year-Week values associated with any given employee-activity.
Of course, attempting to enter a row with empty Year-Week results in
"Index or primary key cannot contain a Null value".
So I need a change to the table design.

One thing I tried was to convert the primary key index into a non-primary index by turning Primary off (and leaving Unique on) in the Indexes window.
This correctly prevents duplicate records where Year-Week values are non-empty -- but it allows duplicate records where Year-Week are empty.

For example, using the above non-primary, unique index, the following data are allowed:

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Same activity,
     7  2015    22      31  } different dates.
     7  2015    33      32
     7  2015    40      33  } Same dates,
     7  2015    40      34  } different activities.
     7  2016     2      36
     7                  38  } Different activities,
     7                  39  } undisclosed dates.

And none of the following additions are subsequently allowed:

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Both records are
     7  2015    33      32  } duplicates of above.

So far so good (behavior matches requirements). However, both the following additions are subsequently allowed, but should not be:

Emp_id  Year  Week  Act_id
     7                  38  } Both records are
     7                  39  } duplicates of above.

Why is that?

What is a good way around this problem of, on the one hand, allowing something equivalent to null for Year and Week and, on the other hand, constraining each Emp_id-Year-Week-Act_id combination to be unique?

I can think of two other (untried) solutions:

Choose an equivalent-to-null numeric value, such as zero or -1 and somehow explain this to users.
Convert the Year-Week field types from Number to Text and simply use "" (empty string) for null.

In your experience, what is a good solution in this kind of situation?

I'm aware of What's wrong with nullable columns in composite primary keys? and NULL value in multi-column primary key, which explain certain things but don't provide a solution.


texts:
When I try to create a statistics on a filestream column, I get the following error message:

  Column 'MyColumn' in table 'MyTable' is of a type
  that is invalid for use as a key column in an index or statistics.

This is documented in the BOL page for CREATE STATISTICS and it's not an issue.

However, when I run a query with a predicate on the column (IS NOT NULL, for the record), a statistics is auto-created. Why is this disallowed for manual creation but still possible for auto creation?

The reason why I want to create a statistics manually is that I want to mark it NORECOMPUTE in order to avoid having to scan several GBs of data. I know I can let SQL Server auto create it and then mark it NORECOMPUTE later, but auto creation is triggered with the default sample, which I could override to 0 ROWS in manual creation. I would also like to know if there is a reason for this.


texts: Cardinality Estimation for >= and > for intra step statistics value

texts:
I am trying to understand how SQL Server try to estimate for 'greater than' and 'greater than equal to' where clauses in SQL Server 2014.

I think I do understand the cardinality estimation when it hits the step for example if I do

    select * from charge where charge_dt >= '1999-10-13 10:47:38.550'

The cardinality estimation is 6672 which can be easily calculated as
32(EQ_ROWS) + 6624(RANGE_ROWS) + 16 (EQ_ROWS) = 6672 (histogram in below screenshot)

But when I do

    select * from charge where charge_dt >= '1999-10-13 10:48:38.550'

(increased the time to 10:48 so its not a step)

the estimate is 4844.13.

How is that calculated?


texts:
I'm in need of some help with an Oracle 11g instance that won't start.  First of all, this is development instance so if it won't start it's not the end of the world, but it does have some useful data in it if I can bring it back to life.  This is running on a VM that was restored and since that time Oracle won't start.  Here is what I tried:

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/12/2014 13:01:08
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'

RMAN> recover database;

Starting recover at 12-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/12/2014 13:01:23
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

It seems like the ORA-01113 and ORA-16433 create kind of vicious circle.  I can't open the database until I perform media recovery, but I can't recover because I can't open the database?  Full disclosure - I work at a small shop that doesn't have DBAs and I'm a dev.

Thanks in advance for any help / suggestions.


texts: Is there a way to get the stack trace of the SQL query that is given in awr, I mean a way to check the flow of the query from which package or procedure that it has been triggered? tkprof and awr both show only the SQL and the elapsed time, parse and fetches.


texts: Using activity over last few days to label user as "active"

texts:
Here's an example dataset.
with activity_cte (day, user_id, act1, act2) as (
    values
        ('2020-01-01'::date, 1, 0, 1),
        ('2020-01-01'::date, 3, 1, 0),
        ('2020-01-02'::date, 1, 3, 2),
        ('2020-01-02'::date, 2, 0, 2),
        ('2020-01-02'::date, 5, 0, 1),
        ('2020-01-03'::date, 1, 1, 2),
        ('2020-01-03'::date, 5, 1, 1),
        ('2020-01-04'::date, 2, 1, 1),
        ('2020-01-04'::date, 5, 4, 0)
)
select * from activity_cte;

In this I'm tracking user activity counts.  There are two activities I'm tracking here: "act1" and "act2", and I'm just summing the number of times the user engages in that activity over time.  For now, if a user doesn't engage in either activity on a given day they're just not showing up for that day (though this could be changed if needed).  So for example, user 2 engages in activity 2 twice on Jan 2 and in both activities once each on Jan 4.
What I'd like to do is calculate the number of "active" users each day which I'll define as users who have engaged in at least one of the activities since the day before (really it'll be something like a week before, but I don't want to have to write a ton of rows in this example set here).  So here's what I'd like returned.
2020-01-01 2
2020-01-02 4
2020-01-03 3
2020-01-04 3
This seems like something I'd need to use a window function for.  Maybe I'm overthinking it, but I'm having trouble coming up with the actual approach to generate these numbers.


texts: Thousands of After Insert and Update Triggers Causing Performance Issues

texts:
We have a After Insert and Update trigger on one of our tables.  The trigger basically makes a json payload and enqueues it in a RabbitMQ system.
Today a large insert script was run on the table (over 50,000 inserts).  This scenario had not been tested or accounted for and now we are having performance problems on that database.
We notice that the RabbitMQ is having the records trickle in slowly over a long period of time.  Even though the data has been in place for a while (because it is an After Trigger).
It seems as if the After triggers have been queued up somehow and are very slowly working through the system.
How are the After Trigger events kept track of for execution?  Are they queued somewhere?  Is there a way I can clear them out?


texts: Not all MySQL processes are killed when restarting

texts:
We have a MySQL 5.6 server running on Ubuntu 12.04 using the MySQL 5.6 PPA.

The service comes up fine when rebooting the box, but whenever we restart the MySQL process via /etc/init.d/mysql restart, some of the child processes are not killed, resulting in file lock errors when the daemon tries to start again:

2015-04-16 08:40:23 2713 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2015-04-16 08:40:23 2713 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2015-04-16 08:40:24 2713 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2015-04-16 08:40:24 2713 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2015-04-16 08:40:25 2713 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2015-04-16 08:40:25 2713 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

Full config file:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
skip-external-locking
basedir                         = /usr
tmpdir                          = /tmp
pid-file                        = /var/run/mysqld/mysqld.pid
socket                          = /var/run/mysqld/mysqld.sock
datadir                         = /var/lib/mysql
lc-messages-dir                 = /usr/share/mysql
log-error                       = /var/log/mysql/mysql.err
user                            = mysql
port                            = 3306
bind-address                    = 192.168.50.253
explicit_defaults_for_timestamp = 1

max_allowed_packet              = 1024M
thread_stack                    = 192K
thread_cache_size               = 32
query_cache_limit               = 128M
query_cache_size                = 32M
max_heap_table_size             = 512M
tmp_table_size                  = 2G
key_buffer_size                 = 1024M
sort_buffer_size                = 1024M
read_buffer_size                = 1024M
bulk_insert_buffer_size         = 1024M

myisam_sort_buffer_size         = 1024M
myisam_max_sort_file_size       = 20G
myisam-recover                  = BACKUP

innodb_thread_concurrency       = 8
innodb_flush_method             = O_DIRECT
innodb_buffer_pool_size         = 5120M
innodb_buffer_pool_instances    = 4
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table           = 1
innodb_log_file_size            = 1G
innodb_log_buffer_size          = 8M
innodb_strict_mode              = 0
innodb_autoinc_lock_mode        = 0

[mysqldump]
quick
quote-names
max_allowed_packet  = 1024M

[mysql]

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

Is there anything anyone can see that could cause this behaviour? The debian-sys-maint user exists and has the appropriate grants.


texts:
I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating this rating schema for e.g pictures I moved them to separate rating table and only store the rating_id in restaurant.

I know that 1 rating will only be used by 1 other row in the whole database.
How can I cascade a delete to rating when deleting a row in restaurant or picture?

I've been looking around but all I can find is set a REFERENCES, but for that I need to know which table data will be deleted.

I know triggers would do the job, I was simply hoping for something more elegant.


texts: Unable to shrink log file due to snapshot replication

texts:
Our database log (SQL Server 2008 R2) is growing too big, and truncating it via DBCC SHRINKFILE doesn't work (the database is set to simple recovery mode).

A bit of background. I released some changes to our live servers a few days ago, I needed to drop some articles from the snapshot replication which I did, made the changes, and re-added the removed articles.
However, when I tried to reinitialise the subscribers I got a strange error (which I didn't save unfortunately and am not allowed to recreate until the next release window). I set the replication processes running again but it seems to be holding a lock on the log file.

 select log_reuse_wait_desc from sys.databases where name = '<dbname>'

gives a result of 'REPLICATION'.

DBCC OPENTRAN ('<dbname>')

gives

  Transaction information for database '< dbname >'.

  Replicated Transaction Information:
          Oldest distributed LSN     : (0:0:0)
          Oldest non-distributed LSN : (3891297:327:1) DBCC execution completed. If DBCC printed error messages, contact your system
  administrator.

I've done some reading around and I suspect that the way to fix it is to kill off a blocking spid or use sp_repldone somehow, but...

exec distribution.dbo.sp_browsereplcmds @xact_seqno_start = '0x003B6061000001470001', @xact_seqno_end = '0x003B6061000001470001'

returns nothing. I'm not sure that I've converted the lsn correctly.

I'm not the DBA, he is unavoidably out of the country at the moment and getting him working on this would be quite bad. I'd prefer to fix the log growth problem and have him look into why reinitialisation doesn't work when he gets back, but this is a time sensitive problem since we will run out of space sometime on Sunday at the current rate of growth.

I can't do anything too drastic since we have other publications (transactional replication) running on the same server. Any ideas on what I should do would be very appreciated right now.


texts:
I've got this tree-like key (think object OIDs--it's very similar) to store and index in a table, for which queries want to select subtrees thereof. The most straightforward way to do this on a btree is to use the tree path as the single key and descend the key with a starts-with operation to find the row blocks.
So the obvious representation is VARBINARY(n)*. I cannot think of a way to express starts with in a way that SQL Server can figure out to use the index thereof.
The next-most obvious is to pack the data into some VARCHAR with a binary collation and use a like operation; however I've discovered that like @n + '%' will never use an index, but only like 'literal%. Adding the WITH INDEX hint still scans the index in its entirety because SQL server just doesn't understand. I really don't want to think about how to escape arguments in SQL so I can build an EXEC. That's just madness and a security disaster waiting to happen.
Expressing this in ordinary recursive tables and querying with recursive SQL is out of the question. The recursive query will power-sink the database server.
I actually have the data in a long string of long variables right now and can select my serialization form. As I said, the natural form is VARBINARY and it really would look like 0x000000100004A010000000D0000A000. The most common query is of the form "give me everything starting with 0x000000100004A01" or in natural model, the first n values from the string of long values. I could write them in the form a.b.c.d.e.f... but the individual numbers are long and come from clicking on things on the GUI.
Roughly speaking I'm looking at a plausible form of
CREATE TABLE Record (
   RecordId BIGINT NOT NULL IDENTITY(1,1),
   RecordedDate DATETIME NOT NULL,
   RecordClass CHAR(1) NOT NULL,
   UserId INT NOT NULL,
   ObjectId VARBINARY(384) NOT NULL,
   RecordValue NVARCHAR(100) NULL,
   OwnerId BIGINT NULL, -- Joins to another table
   SubOwnerId BIGINT NULL, -- and yet another table
   PRIMARY KEY (RecordId)
)
CREATE INDEX name ON Record(RecordedDate);
CREATE INDEX name ON Record(OwnerId);
CREATE INDEX name ON Record(SubOwnerId);
CREATE INDEX name ON Record(ObjectId);

What is the best way to do a starts-with index?
*I've computed the largest possible n and it's less than 400


texts: How to select non unique values from 1 column if a value doesn't exist in column 2

texts:
I have a table containing (among other things) 2 integer values, neither of which are unique. See below for example data:

---------------
| a    | b    |
---------------
| 1    | 1    |
---------------
| 1    | 2    |
---------------
| 2    | 1    |
---------------
| 2    | 2    |
---------------
| 2    | 3    |
---------------

How can I query column a, where certain values don't exist in the corresponding column b. E.g. for value 3, I'd want it to return the value 1 for column a
I've come up with:

SELECT a FROM table WHERE a NOT IN (SELECT a FROM table where b = 3)

But this doesn't feel like the best/most efficient way.


texts: DB2 GLOBAL VARIABLES Refresh for datapool managed sessionis

texts:
I've recently discovered the feature of defining global variables for DB2. I wanted to utilize it. My use case is to create a variable containing the value for Previous Banking day.

 CREATE VARIABLE SCHEMA.GV_PREV_PROC_DATE DATE
 DEFAULT ((SELECT SCHEMA.DATE_UTIL.PREVIOUS_BANKING_DAY(PROC_DATE)
           FROM SCHEMA.TBL_PARAMETERS));

This particular date is usually referenced throughout various programs. Instead of querying everytime, I thought of placing it in the variable. Based on the DB2 specification, the value is session scoped. Since we utilize datsource pooling, the value is not updated once the date is incremented by the end of day batch program, unless i reset my connection pool.

Is there a way to propagate the updates to existing sessions, or is my use case totally not appropriate for this global variables? By the way I'm using DB2 10.1 LUW


texts: Does surrogate key assignment for a fact table require that the source data has natural keys?

texts:
Let's say I have a simple OLTP database with orders, products, and customers:

And from it, I am building a data mart with an orders fact table, product dimension, customer dimension, and date dimension:

When loading the orders table into fact_orders (let's say I was using an SSIS Lookup Transformation to assign the surrogate keys), does that mean that the source of data for the orders would also need to have the natural "foreign key" values that were associated with the order in the OLTP system?

In other words, would the data that's being loaded come from a query like this?

SELECT
  order_date,                   -- needed to get date surrogate key
  customer_name,                -- needed to get customer surrogate key
  product_name,                 -- needed to get product surrogate key
  order_number,                 -- denegenerate dimension,
  qty_ordered AS order_qty,     -- measure
  total_amount AS order_amount  -- measure
FROM orders o
  INNER JOIN customers c
    ON o.customer_id = c.customer_id
  INNER JOIN products p
    ON o.product_id = p.product_id


texts: Installing SQL Server 2000 32-bit on Windows Server 2008 x64?

texts:
Am I correct in assuming there won't be any issues installing SQL Server 2000 32-bit on a Windows Server 2008 x64 system?

I understand the memory limitations, etc, but that doesn't matter at this point.


texts: Adding IF Exist to truncate statement while looping through DBs

texts:
Please, I am trying to add if exists statement to the code below such that it only print truncate statements on all DBs where the table= dbo.log only exist. (SQL Server 2016)

IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = 'dbo'
                 AND  TABLE_NAME = 'log'))

BEGIN
    PRINT 'Table Exists'
END

--Truncate log from db like'OG_%'
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = CONVERT(NVARCHAR(MAX), (
    SELECT CONVERT(NVARCHAR(MAX), 'TRUNCATE TABLE ') + QUOTENAME(name) + '.dbo.log; '
    FROM sys.databases
        WHERE NAME LIKE  'OG_%%'
         FOR XML PATH('')
))
PRINT(@sql)
--EXEC(@sql)

Thanks


texts:
For the first time in my life, I have Ubuntu, so I do not know much how to manage and what should I be looking for; I'm using version 18.04.
I installed MySQL 5.7, it worked great for a couple of days and suddenly it can't even restart, doesn't start again (with service mysql start or anything)...

Using systemctl status mysql.service I get:

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor
preset: enabled)
Active: failed (Result: exit-code) since Thu 2018-07-26 14:24:49 CEST;
10min ago
Process: 3758 ExecStart=/usr/sbin/mysqld --daemonize --pid-
file=/run/mysqld/mysqld.pid (code=exited
Process: 3749 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
(code=exited, status=0/SUCCESS)

јул 26 14:24:49 maax systemd[1]: mysql.service: Service hold-off
time over, scheduling restart.
јул 26 14:24:49 maax systemd[1]: mysql.service: Scheduled
restart
job, restart counter is at 5.
јул 26 14:24:49 maax systemd[1]: Stopped MySQL Community Server.
јул 26 14:24:49 maax systemd[1]: mysql.service: Start request
repeated too quickly.
јул 26 14:24:49 maax systemd[1]: mysql.service: Failed with
result 'exit-code'.
јул 26 14:24:49 maax systemd[1]: Failed to start MySQL Community
Server.

and using journalctl -xe I get:

јул 26 14:24:49 maax systemd[1]: mysql.service: Scheduled
restart job, restart counter is at 5.
-- Subject: Automatic restarting of a unit has been scheduled
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Automatic restarting of the unit mysql.service has been scheduled,
as the result for
-- the configured Restart= setting for the unit.
јул 26 14:24:49 maax systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has finished shutting down.
јул 26 14:24:49 maax systemd[1]: mysql.service: Start request
repeated too quickly.
јул 26 14:24:49 maax systemd[1]: mysql.service: Failed with
result 'exit-code'.
јул 26 14:24:49 maax systemd[1]: Failed to start MySQL Community
Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has failed.
--
-- The result is RESULT.

What should I do to fix this and continue to use Mysql every day normally?
Mysql works okay when I reinstall it, but after some time, it crushes again...


texts: CSV (UTF-8) to Postgres database (UTF-8), doesn't show äöå characters

texts:
I have csv-file that is UTF-8 encoded. I'm using python (psycopg2) to copy it into a Postgres database. My Postgres database is using UTF-8. However I have problems to show Ä, Ö and Å characters.

If I'm using query SET client_encoding = 'ISO-8859-1'; It would fix the issue.

My database is looking like this (psql -l):

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
 data      | postgres | UTF8     | fi_FI.UTF-8 | fi_FI.UTF-8 |

My server locale is fi_FI.utf8

My CSV file is UTF-8. Its made with python command:

with open('users.csv', "w+", encoding="UTF-8", newline='') as f:
     writer = csv.writer(f)
     writer.writerows(list_table)

I'm writing it to Postgres table with this code:

cur = conn.cursor()

cur.execute("CREATE SCHEMA IF NOT EXISTS data;")

cur.execute("DROP TABLE IF EXISTS data.users"`)

cur.execute("CREATE TABLE data.users(name VARCHAR(40), lastname VARCHAR(40), phone VARCHAR(13), email VARCHAR(100), iban VARCHAR(18), id VARCHAR(11))")
copy_ = """
           COPY data.users FROM stdin WITH CSV HEADER`
           DELIMITER as ',' ENCODING 'UTF-8'
           """
with open('users.csv', 'r') as f:
    cur.copy_expert(sql=copy_sql, file=f)
    conn.commit()
    cur.close()

First I used this code

with open('users.csv', 'r') as f:
    next(f)
    cur.copy_from(f, 'data.users', sep=',')
    cur.close()

I switched to Copy_expert to get that ENCODING 'UTF-8' into the string. However it didn't do anything.

Now when I use pgadmin4 or console (Linux ubuntu 18.04LTS terminal) I can't get ÄÖÅ characters to show up. Mökkisuo is "MÃ¶kkisuo" and Lammasjärvi is "LammasjÃ¤rvi". I'm using basic query like select * from data.users to check results.

However if I change my client encoding to ISO-8859-1 everything shows like it should? (SET client_encoding = 'ISO-8859-1';).

Start of EDIT

Here is my locales.

~$ locale
LANG=fi_FI.utf8
LANGUAGE=
LC_CTYPE="fi_FI.utf8"
LC_NUMERIC="fi_FI.utf8"
LC_TIME="fi_FI.utf8"
LC_COLLATE="fi_FI.utf8"
LC_MONETARY="fi_FI.utf8"
LC_MESSAGES="fi_FI.utf8"
LC_PAPER="fi_FI.utf8"
LC_NAME="fi_FI.utf8"
LC_ADDRESS="fi_FI.utf8"
LC_TELEPHONE="fi_FI.utf8"
LC_MEASUREMENT="fi_FI.utf8"
LC_IDENTIFICATION="fi_FI.utf8"
LC_ALL=

~$ locale charmap
UTF-8

And here is how I get bad result!

:~$ sudo su - postgres
postgres@Server:~$ psql
postgres=# \c data
data=# select * from data.users;

result is like this:

 Piritta      | MÃ¶kkisuo        | 0    | piritta.mokkisuo@notreal.com

Oh and data is just generated. It's not real people data.

What I have to change to get this show correctly in UTF-8? I don't understand where that conversion to ISO-8859-1 is coming from?

I don't understand why file that is UTF-8, Database that is UTF-8. Locale that is UTF-8 etc. Has to be changed to Latin-1 (client encoding) to show up correctly? What is causing this? Everything should already be UTF-8? So why ISO-8859-1? Why UTF-8 doesn't show these characters?


texts:
I'm learning mysql.  Under all cases, is Join the same as Cross Join?  For example, this query

SELECT * FROM table t1
JOIN table t2

Will always be the same as

SELECT * FROM table t1
CROSS JOIN table t2

Even if I add WHERE, GROUP BY, HAVING, LIMIT, ORDER etc.... conditions afterwards, JOIN and CROSS JOIN will always yield the same results?

Thanks


texts: Launching a python script from an insert trigger

texts:
We have a nice piece of python that sends some emails and interacts with a cloud system. Works fine. But we have to fire it every few minutes to poll the db. We really need, for business purposes, to have the python script fire in real time, so there is no polling delay. (This serves sales people who are on the phone with customers.)

We really do not want a 1 minute polling loop. Or 30 seconds. We want the record to show in the db and for things to happen right away.

The fast way to make this fly is to have it fire when a specific record type is inserted into a table.

Can we fire a python script from a trigger?

Per Aaron's note below, we know that this is a Very Bad Thing™, but this table gets very very little use (0-12 inserts a day). Polling the table fails to meet our business need (we need the .py to run immediately -- it does much more than send an email).

We believe a way to meet our business need is to set up the .net version of python on the SQL Server, and then have T-SQL call the python script the way it calls C# stuff... but we have no idea how to actually do this! (ergo this question).

Docs/details?

I asked a follow-up question on Stack Overflow: How do I create a Python CLR procedure in SQL Server?

The question under the question: You have a piece of python. You want it to fire from a SQL trigger, but you know that is a Very Bad Thing. So what do you do to actually accomplish the same effect without having python code in the middle of a SQL operation?

What is the non-trigger, non-polling approach to solving this need?

(The same effect = "insert/update/delete happens in a table and a python script is triggered within 2 seconds of the db event, without polling the table")


texts: Can I create a replica server for my Elasticsearch cluster?

texts: I have a running Elasticsearch cluster on a single server. Is it possible like in MySQL you have a master server then you can create a replica server which replicates all transactions from master onto replica server. Is that possible on Elasticsearch?


texts: How do I configure AWS Aurora to separate write/read operations

texts:
I want to migrate my database instance from AWS RDS MySQL to Aurora, but I have a doubt about replication and how Aurora management the write/read operations.

I have my application and I want to separate the write operations from the reads. I want to create a Master instance only for write operations and other instance  (Read Replica) only for read operations.

The problem is here, I read on AWS documentation that I need to do this separation on my Application and I think or I hope to find a way to do that and be transparent for my Application. I draw a simple schema do I have to do to get with Aurora (from AWS):

What AWS says do I have to do:

      -----------------
      |  Application  |
      -----------------
       |             |
       | writes      |reads
       |             |
------------      ------------------
|  Master  |      |  Read Replica  |
------------      ------------------
       ^            ^
       |replication |
       |____________|

What do I need:

I need the Master always keep with the write operations and it redirects the read to the Read Replica instance.

      -----------------
      |  Application  |
      -----------------
       |
       |write/read
       |
------------   Reads    ------------------
|  Master  | <------->  |  Read Replica  |
------------            ------------------
       ^                     ^
       |      replication    |
       |_____________________|

The replication is always running. But I want to separate the write and read process. So  my summary:

The master instance detecte the different between writes/reads operations and all read operation will be manage by Read Replica. I need this solution because aurora offer a good features to improve my RDS, but the only problem is that I need to create a balance between write  and read operations: The write operation be processed in the master and it send the read operation to the Read Replica. I don't want to define this process and make selection between them in my application code as Amazon propose.


texts: barcode inventory design - transaction or master?

texts:
I am building a inventory software for mobile phone retailers. There are several things confuse me.

Ok, we have:

Product
Product Attributes
Barcode
Stock In Batch (Purchases/Return, etc)
Stock Out Batch (Samples/Sales, etc)
Stock Transfer Batch (Transfer stock from A to B warehouse)

I plan to do a transaction table for stock in/out/transfer.
So my product & attributes & barcode table should not have a qty field, right?

Below is my business rules

    1 product, 0 or many attributes;
    1 attributes, 1 to many product

    1 product, 0 to many barcode;
    1 barcode, 1 product

    1 attributes, 0 to many barcode;
    1 barcode, 1 attributes

    1 barcode, 0 to many quantity

How many table should I created for this?

My table design is like this:

    - warehouse (warehouse id[pk])
    - product (product id[pk])
    - attributes (attribute id[pk])
    - product attributes (product id[pfk], attribute id[pfk])
    - barcode (barcode id[pk])

    - stock in batch (sib_id[pk], warehouse id[fk])
    - stock in batch item (item_id[pk], sib_id[fk], product id[fk], qty, unit cost)
    - stock in batch item attributes (item_id[pfk], attribute id[pfk])
    - stock in batch item barcode (item_id[pfk], barcode id[pfk])

    - stock out batch (sob_id[pk], warehouse id[fk])
    - stock out batch item (item_id[pk], sob_id[fk], product id[fk], qty, unit cost)
    - stock out batch item attributes (item_id[pfk], attribute id[pfk])
    - stock out batch item barcode (item_id[pfk], barcode id[pfk])

    - stock transfer batch (stb_id[pk], from warehouse id[fk], to warehouse id[fk])
    - stock transfer batch item (item_id[pk], stb_id[fk], product id[fk], qty, unit cost)
    - stock transfer batch item attributes (item_id[pfk], attribute id[pfk])
    - stock transfer batch item barcode (item_id[pfk], barcode id[pfk])

Should I have qty field?
Where should I put qty?

I am thinking of using sum() in the stock in and and minus() stock out transaction to get product quantity, but after thinking I am going too complicated and stuck at here.

I also thinking to use qty field, for every transaction it will auto adjust the qty field, but afraid that may be the final qty doesn't tally with the transaction calculation.

Please advise.


texts: Getting error on a particular query on a table in Postgres

texts:
I am getting the following error on one query in Postgres:

select * from sampletable where sample_id = 1234;

Error is :

ERROR: could not access status of transaction <some-id>
Detail: CLOG segment 42 does not exist: No such file or directory.

But when I am trying to run other queries on the same table, it works fine.
Eg.

select * from sampletable where sample_id = 4321;

So, its happening for a particular value of a sample_id only.

What could be the cause of it and how can I fix it? Any suggestions.


texts: Best way to create a materialized view in MySQL

texts:
I am using MySQL 5.6. I am not being able to a create materialized view like I can in Oracle. I have seen one or two solutions like Flexview.

Can anybody tell me best way to create a materialized view in MySQL (auto refresh like in Oracle ) with the minimum of complexity?


texts: Structuring SQLite database with 2 relationships?

texts:
I'm current working on a SQLite-database in a android project which will store 3 types of objects; feed, users and posts.

There will be several feeds containing several varying users(a user can be part of serveral feeds) and of my understanding i should implement this by having a Many-to-Many relationship:
feedTable - bridge_feeduserTable - userTable

But there will also be posts made by users and a post should be linked to a the user which created the post. How should i link this up?

Should i have a feedTable - bridge_feeduserTable - userTable - bridge_userpostTable - postTable?  or should the posts be stored in a separate database with all the posts? What is the best practice regarding this issue?

Some basic questions that will asked to the database is:
What users are part of feed x?
What posts have been made by the users in feed x?


texts: difference between pg_dump command's --column-insert and --attribute-insert

texts:
Background:

I'm trying to create a dump file that only contains the data in my database.  I would like it to create the data with the INSERT command.
In reading the manual, (http://www.postgresql.org/docs/9.2/static/app-pgdump.html#PG-DUMP-EXAMPLES) i see that I can use either

--column-insert
--attribute-insert

It wasn't clear from the manual what the difference is.  So I tried both and did a diff on the files.
There are differences and I'm wondering why.
Can someone tell me what the difference is between these two commands?

Here's the full command I'm running:

pg_dump -a --column-inserts -U postgres testdb > /tmp/test_data_as_inserts2.sql

and :

 pg_dump -a --attribute-inserts -U postgres testdb > /tmp/test_data_as_inserts.sql

Any tips would be appreciated.  In the meantime, I'm going to dig in and see what's different between these two files... why certain records are included and others not.

Thanks.


texts: Count() versus keeping a column with a total count

texts:
Possible Duplicate:
  Storing vs calculating aggregate values

Say I have a table full of photos that each have an albumId that is a foreign key to an album table. To get the amount of photos in each album, I could simply do a Count() on the photo table where the albumId = the id from the album table. However, to accomplish this same task, I could keep a photoCount column in the album table. Is it worth keeping that extra column in the album table instead of using the Count() function?


texts: In SQL server I can use the USE statement to switch between working databases. Is there a similar command for DB2?


texts: sp_Blitz - undocumented DBCC events related to the replication marked as a finding

texts:
Recently I found in the sp_Blitz output new finding 'Overall Events' in 'DBCC Events' findings group:

15278 DBCC events have taken place between Aug  8 2017 12:15AM and Aug  8 2017  8:17AM. This does not include CHECKDB and other usually benign DBCC events.

When I digged into this and checked the default trace, I found that every minute it records entries like this:

dbcc incrementinstance ("SQL Replication Distribution", "Dist:Delivered Cmds/sec", @agent_name, @new_delivered_commands)
dbcc setinstance ("SQL Replication Distribution", "Dist:Delivery Latency", @agent_name, @delivery_latency)
dbcc addinstance ("SQL Replication Logreader", @agent_name)

It's plainly visible this is replication related, but since these commands are undocumented I am not sure whether they should come up in the trace. However, basing purely on the output it just measures the amount of commands per seconds plus the latency. I think this is nothing harmful.

So if you could possibly check your servers where you run the replication and confirm if you have these entries and the frequency is more-less the same?

The separate question is if it's normal to see such commands (and I guess it is) in the default trace, perhaps it shouldn't be considered as a finding by sp_Blitz and it should be grouped with other harmless DBCC commands already excluded in sp_Blitz.

We use Sentry One to monitor SQL Server, but I don't think it's monitoring replication. I think it might be something internal. There are some default alerts defined when you deploy replication (SQL Agent -> Alerts, for example: Replication Warning: Transactional replication latency (Threshold: latency)) It is basing on SQL Server error log, but the entry in the error log has to be initially triggered by something.

I have opened an issue on Github to cover this.


texts: Is there a way to throttle index creation?

texts:
When creating an index concurrently, I'm reaching IO limitations and it's impacting the production system. Is there a way to throttle the index creation?

I'm on 9.6, will hopefully be on 11 or 12 in the near future.

Previous question: Postgres: CREATE INDEX, but very slowly, to reduce performance hit on rest of database?


texts: Turn off auto qualification of table names when creating a view

texts:
Problem Definition
I am trying to modified a shared development database, for a dev team to use for application development/testing.
Most of the collective work (table names, views, etc) are stored in the public schema, but I have set up schemas for each user to use as scratch space. However, the real goal would be for a user to use the objects in their schema if they exist and then fall back on the other objects, like how a search_path is used today.
Example
This would probably be better described with an example.  Let's say there's a database for an automotive repair application that a team is working on that has some tables and a view:
public.automobiles
public.parts
public.inventory
public.mechanics
public.schedule
public.v_repairs  -- view that joins fields from all tables above

This is working great, but let's say a developer (e.g., Sally) wants to test a new feature out, with her own dataset to check for visual feedback, or threshold testing.  She creates a table in her own schema, sally.schedule.  Because the default search_path is something like "$user",public, any simple queries she creates, would first check her schema before public.  For example:
SELECT * FROM schedule LEFT JOIN mechanics USING(mechanic_id);

This would use public.mechanics and sally.schedule, when sally is connected to the database.  That's exactly the intended use, but when saving a view, it fully qualifies the table name by inserting the schema.  So if the same query above was created as a view in the public schema it would look like:
SELECT * FROM public.schedule LEFT JOIN public.mechanics USING(mechanic_id);

The magic of search_path is negated.  When Sally connects to the database to  call the view (SELECT * FROM v_mechanics_schedule), it ignores the sally.schedule table she created, and only uses the public one.
Is there a way to not have Postgres store the schema name for table/view objects when saving a view?

Note: this is something new I was looking into, but something I've never really had the need for, since developers can typically clone applications, replicate databases, and work within their own sandbox environment. No need for some clever collaborative schema setup


texts: Best MySQL storage engine for read heavy table with two indexes

texts:
I have a pretty big MySQL table (70G) with about 15 columns and 1.5M rows.
There is one index on the autoincrement id and one on a foreign key (integer). Apart from that there are several VARCHAR and TEXT fields.

The use case is as follows: The data is inserted once and then read several times (SELECT count(*) FROM table WHERE foreign_key = 1). Every day rows that are older than 3 months are deleted.

I have thought of using ARCHIVE as storage engine but have read that it has many downsides and basically only one upside - saving disk space.

What would be the preferred storage engine for this use case?

Thanks!


texts:
We recently upgraded the compatibility level (CL) of our production databases from 100 to 120.

Within a couple of days, users started complaining of increased wait times and general slowness. It was decided to revert the CL back down to 100. While things did get better, query times did not go back to historical averages.

We then cleared the plan cache to remove any 120 query plans still hanging around, and restarted the SQL Server service in configuration tools. After all this, our average times are still noticeably higher, though significantly better than when we initially switched to CL 120.

Is there anything else I can do, or is there a step I missed, to revert back to the old Cardinality Estimator?

I don't think this had any impact, but part of the same changes that we made was to enable CLR with the CL upgrade, and to disable it again when trying to revert back.

We are on SQL Server 2014 Service Pack 1, no CUs yet. We have a limited update schedule. CU 6 is scheduled for next month. We update stats and check for indexes to be rebuilt nightly.


texts: Hierarchical references abstraction with strict model

texts:
I want to abstract a real world hierarchy in a strict DB model (as strict as possible).

My problem is to to create a structure with a this-or-this-but-not-both rules set.

I have two entity types,

Device, should be connectable to one other "device" as child and as parent.
Combiner, it can be connected with multiple "devices" as parents and one as child.

BUT(!) a device can only be connected to one of them at the same time. So like "device" as only one child and one parent.

I try to visualize it somehow:

All my ideas are not strict but I searching if there is a pattern to solve this without rules on application level. On a class model I would solve this with inheritance. Some ideas?


texts:
Problem
I have a Windows 7 Ultimate x64 machine running SQL Server 2008 SP3. After creating a local database, when trying to start the service it keeps stopping (crashing).
Research
I read a lot of material online, disabled Windows and Java updates that might cause conflicts, and shut down the SQL Server service on the local machine in order to find out if it was the problem - but it didn't help.
Went over to the Event Viewer and received the following logs:

Cannot create file 'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\temp_MS_AgentSigningCertificate_database.mdf' because it already exists. Change the file path or the file name, and retry the operation.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Question
I don't really understand what is the temp_MS_AgentSigningCertificate_database.mdf file and is SQL Server trying to upgrade/update itself?
How do I resolve this issue?
Additional information
This is the first installation on a fresh operating system, so it couldn't be from any previous installation attempt. I should use the latest SQL Server version, which is much much better (I'm already using it for other purposes), but this one is used as a part of my learning of penetration testing. I need to launch this specific version. I don't think a reinstall would solve my problem in that case.
I really don't have anything on this database yet; I just finished installing it. It is the first install on a fresh operating system (Windows 7 Enterprise x64 on a VMware virtual machine, but that shouldn't really matter).
The book I am reading is https://www.nostarch.com/pentesting. It was published in 2014, so it is considered to be up-to-date, yet still explicitly requires to install SQL Server 2008 as it has known vulnerabilities that we can exploit as a part of the learning process.


texts: Setting the column width of a column in a table created by logparser

texts:
I'm trying to use LogParser for ETL. I am trying to import this file with the following command:

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT Field1 AS CountryCode, Field2 AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -createtable -cleartable

It creates a table like so:

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [varchar](255) NULL,
    [Zip] [int] NULL
);

If I change the command to

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT Field1 AS CountryCode, TO_STRING(Field2) AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -createtable -cleartable

It creates the following table:
    CREATE TABLE [dbo].[ZipCodes](
        [CountryCode] varchar NULL,
        [Zip] varchar NULL
    );

So TO_STRING(Field2) AS Zip INTO ZipCodes creates [Zip] [varchar](255) NULL.

My goal however, is for the resulting table to be:

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [char](2) NOT NULL,
    [Zip] [char](5) NOT NULL
);

Both columns remain VARCHAR(255) NULL with a SUBSTR like so:

  SUBSTR(Field1, 0, 2) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip

How do I get LogParser to create the table with fixed length NOT NULL char columns?


texts: How to group all linked objects in a undirected cyclic graph

texts:
I have a table that has customers and its joint customers. e.g Customer 1 has a joint customer 2. Customer 1 is also a joint customer to Customer 3.

I am trying to group all customers that are linked and assign same GroupCustNo to them. In below table 1-2 are linked, 3-1 are linked. So 2-3 are also linked. All customers from 1 to 8 in below table are thus linked to each other and will have same GroupCustNo.

 tbl_GroupCustomers:

CustNo   JtCustNo  GroupCustNo
---      -------     ------
1           2          null
2           null       null
3           1          null
4           1          null
4           5          null
5           6          null
5           7          null
6           null       null
7           null       null
8           5          null

The recursive stored procedure I wrote is below. I am calling this in a while loop for each CustNo:

exec usp_UpdateGroupCustomerNo 1, 1
The stored procedure ran successfully for most of the customers but threw up

Recursion Limit of 32 reached
error for some customers. These are customers that have many joint customers and are also Joint Customers to other customers.

It seems recursion wont work here and I am at a loss on how to proceed. Please let me know if there are any alternate methods to address this problem.

CREATE PROCEDURE [dbo].[usp_UpdateGroupCustomerNo]
     @MainCustNo int, @GrpNo int
AS
    declare @JtCustNo int; declare @MainCustNo2 int;

if exists(select 1 from tbl_GroupCustomer
          where CustNo = @MainCustNo and groupcustomernumber is null)
begin
    update tbl_GroupCustomer
    set groupcustomernumber = @grpno
    where CustNo = @MainCustNo
      and groupcustomernumber is null

    DECLARE db_cursor CURSOR LOCAL FOR
         select JtCustNo
         from tbl_GroupCustomer
         where CustNo = @MainCustNo and JtCustNo is not null

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @JtCustNo

    WHILE @@FETCH_STATUS = 0
    BEGIN
        select @JtCustNo as JtCustNo

        exec usp_UpdateGroupCustomerNo @JtCustNo, @GrpNo

        DECLARE db_cursor2 CURSOR LOCAL FOR
            select CustNo
            from tbl_GroupCustomer
            where JtCustNo = @JtCustNo
              and groupcustomernumber is null

        OPEN db_cursor2

        FETCH NEXT FROM db_cursor2 INTO @MainCustNo2

        WHILE @@FETCH_STATUS = 0
        BEGIN
            if exists(select 1 from tbl_GroupCustomer
                      where CustNo = @MainCustNo2
                        and groupcustomernumber is null)
            begin
                exec usp_UpdateGroupCustomerNo @MainCustNo2, @GrpNo
            end

            FETCH NEXT FROM db_cursor INTO @MainCustNo2
        END

        CLOSE db_cursor2
        DEALLOCATE db_cursor2

        FETCH NEXT FROM db_cursor INTO @JtCustNo
END

CLOSE db_cursor
DEALLOCATE db_cursor

END


texts:
Basically, I have table a, which has a type and type_id and want to join on either two different tables where there is a hit.

So basically, if table b has a match, return the record or if table c has a match, return that record (essentially two inner joins with an OR); just one of them has to match. The below query seems to work... however I feel like this is a very naive solution that will actually result in me running into some issues.

SELECT
    a.*
FROM
    a
    LEFT JOIN b ON a.type_id = b.id
    AND b.user_id = 123
    AND a.type = 'dog'
    LEFT JOIN c ON a.type_id = c.type_id
    AND c.user_id = 123
    AND a.type = 'cat'
WHERE
    b.id IS NOT NULL
    OR c.type_id IS NOT NULL;

Basically, I want all the records where the user exists in either table.


texts: Is there a way to view WAL updates on a Hot Standby server?

texts:
We are trying to debug some of the problems we are having on our production database server. One thing we would like to enable is pg_stat_statements, but we are holding off on restarting the service. In the meantime, we had the idea that we could enable pg_stat_statements and restart the hot standby. The thinking here was that we could at least see all of the writes that were issued in production, just on a time delay. However, this doesn't seem to be the case.

Is there a way to view the updates that are issued by the WAL logs on the hot standby?

EDIT: or by streaming replication?


texts: Import asp.net / SQL Server 2012 website database to local SQL Server 2014 express

texts:
I have website developed in asp.net and used SQL Server 2012 for database, my task to make website responsive, I have decided make changes locally first, I have whole source through ftp, and downloaded database backup from control panel.

My problem is that when I try to restore downloaded db backup file, it keeps showing me error message "No backupset selected to be restored", please check attached screen. I am new to ASP.NET / SQL Server 2012, can any one help me to solve this issue ?


texts: How to specify trigger execution order under PostgreSQL?

texts:
I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table.

CREATE TABLE twitter_interactions(...);
CREATE OR REPLACE FUNCTION insert_twitter_interactions ...;
CREATE TRIGGER insert_twitter_interactions_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

CREATE OR REPLACE FUNCTION maintain_data_pointers ...;
CREATE TRIGGER maintain_data_pointers_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

I haven't fully verified, but I suspect the partitioning logic runs before the maintain trigger, and since the row doesn't end up in the parent table, then the 2nd trigger never fires.

What happens if I want to run an AFTER INSERT OR UPDATE as well? Since the row doesn't make it into the original table, then I'm at a loss to implement the after logic.


texts: How to restore MySQL database from Files

texts: Primary hard disk where MySQL (version 5.1) was installed is crashed and I had to re install entire OS. I have all the files including ibdata1 file. How do I go about recreating and attaching database to restore it to stage where it was crashed. Do i need log files or I can delete them ?. I am OK if some of the data I cannot restore. Important is I have all tables and most of the data possible.


texts: connect to psql and run SQL command from command line

texts:
I'm trying to write a quick line in bash that will use psql to connect to a database and delete all entries in two tables:

\connect some_db
DELETE from some_table
DELETE from another_table

I'd like to do this in one line of bash scripting like so:

psql -U <username> -c "\c some_db; DELETE from some_table; DELETE from another_table"

but it seems like I can't do that because the \c command creates a new context that the subsequent commands don't apply towards.

What's the right way to do this in a line or two from the CLI?


texts:
I could not establish a local DAC connection using SSMS while I was logged into the Sql-Server's Operating System's Remote Desktop. First reason was SQL-Browser Service turned off.
Lateron, second reason I was pointed to was this "DAC is not activated" error:

  "No DAC connection could be established on the default port. Make sure
  DAC is enabled".

I looked out for a solution. The suggested solution I found was to enable the "RemoteDacEnabled" Facet property or run

sp_configure 'remote admin connections', 1

This makes me wonder, why I should enable a REMOTEDacEnabled property in case I wanted to connect from LOCAL SYSTEM? Do I get REMOTE wrong here?

MSDN explains:

"The connection is only allowed from a client running on the server. No network connections are permitted."

If I HAVE TO logon from local server and NOT over the network, what sense does a REMOTE DAC setting ever make?

EDIT/part of the answer:
The main reason for my question was, that I did NOT understand why we have to allow /enable the REMOTEDAC settings just in case we want to establish a LOCAL DAC connection from the server itself (that was what I tried with no luck in the first place).

NOW after giving it more tries I figured out that using...

ServerName.SubDomain.Domain.de

caused this error connecting to DAC while...

ServerName

DOES WORK (without activating the RemoteDac setting).

(Weird about this is that I can connect fine using a "normal" connection with both options)

Root cause was that the FQDN was interpreted to be a remote server and this caused the error above.

However, after I have enabled REMOTEDAC, both options from above work for a  DAC, no matter if I try it local on the server or from remote clients.


texts: How to get Row Value & Sum/ Aggregate in Same row? (SQL Server)

texts:
Just wondering if I have data like

 name  score
    A    2
    B    5
    C    7

I would like to have a 3rd row

     name  score  total
        A    2     14
        B    5     14
        C    7     14

I mean ultimately -- I want to find 'z-scores' or percentiles of various row entries across various totals. I'm just not sure if there's a simple way to compare row values to aggregate values, except by a subquery such as

select A.name, A.score, B.total
from myTable A
left join
(select name, sum(score) from myTable group by name) B
on A.name = B.name

That could work but it seems inefficient querying the same table twice for what is a calculation. Is there any simpler way of doing it that I'm missing?


texts:
lets say I have 2 tables :

group
{
     id,
     export boolean not null default false
}

and

channel
{
     id,
     group_id null,
     export boolean null
}

Is it possible, with 1 query, to request the export boolean value for a specified channel id such as :

channel.export is not null return it as is
if channel.export is null and channel.group_id is null return false
if channel.export is null and channel.group_d is not null return group.export

I am using postgresql 10.

This seems a little bit hard.


texts:
I have stumbled upon an interesting MySQL Error message that I do not really know how to interpret.

The setup:

There are two tables A and B.
When data is written or updated in the table A, then a trigger is writing data to the table B.
Operations happen on a Master database
Data is replicated to a slave server

Now, whenever I decide to update data in table A, then it is updated and the corresponding log message is written to table B. MySQL, however, spawns the following error message:

  Note: #1592 Unsafe statement written to the binary log using statement
  format since BINLOG_FORMAT = STATEMENT. The statement is unsafe
  because it uses a LIMIT clause. This is unsafe because the set of rows
  included cannot be predicted.

  Note: #1592 Unsafe statement written to the binary log using statement
  format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it
  invokes a trigger or a stored function that inserts into an
  AUTO_INCREMENT column. Inserted values cannot be logged correctly.

  Note: #1592 Unsafe statement written to the binary log using statement
  format since BINLOG_FORMAT = STATEMENT. Statements writing to a table
  with an auto-increment column after selecting from another table are
  unsafe because the order in which rows are retrieved determines what
  (if any) rows will be written. This order cannot be predicted and may
  differ on master and the slave.

Nothing happy to the eye. How can I avoid these?


texts: Can I use the same filepath for recurring backups?

texts:
I need to set a back up plan to cover a dev/pre-production server for a bit until a real DBA comes along. My plan is to run 3 T-SQL Jobs that basically do run the following statements

Nightly:

BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDB.bak'
WITH CHECKSUM;

Hourly:

BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDBDiff.bak'
WITH DIFFERENTIAL;

Every 5 minutes

BACKUP LOG [MyDB]
   TO  DISK = N'L:\MSSQLSERVER\Logs\MyDB.trn';

I am concerned that overwriting the last differential back up / log back up file is bad practice because I haven't been able to confirm that the new log/diff back up will cover the entire period from the last full db backup.

Can anyone confirm how this works in terms of what time range is covered by what back up file?

My hope is that in case of a 9:54 AM failure

MyDB.bak brings me up to midnight
MyDBDiff.bak Brings me up to 9
MyDB.TRN can bring me up to any time between 9 AM and 9:50 AM

Even though I have only one file from each job.

Am I crazy or on the right track?


texts: Can vmware (virtualisation) configuration impact the data integrity of the data file

texts:
I'm wondering how much can a VMWare configuration impacts the integrity of the data file.

I mean, if the VMWare configuration is not optimal and the database writes a lot of complex operations (creations / modifications / deletions).

Can poor performances and bad configuration of the virtual machine, that host the database, lead to a potentially corrupted datafile when the the application is solicited a lot in read/write operation?


texts: MRG_MyISAM with myisampack tables blocking inserts

texts:
I'm using INSERT_METHOD=FIRST and compressing all tables that are members of the union but when I try to insert into the MRG_MyISAM table it tries to insert into the second one (instead of the first).  Why?  When I uncompress the 2nd table and insert again, it tries to insert into the 3rd.  It gives an error that the table is read-only which I'm fully aware.  This behavior is unexpected.

mysql  Ver 15.1 Distrib 5.5.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

My MRG_MyISAM table looks like:

CREATE TABLE `ah_sales` (
 `server` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `itemId` smallint(5) unsigned NOT NULL DEFAULT '0',
 `countof` tinyint(1) NOT NULL DEFAULT '0',
 `seller` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `buyer` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `price` int(10) unsigned NOT NULL DEFAULT '0',
 `saleon` int(10) unsigned NOT NULL DEFAULT '0',
 KEY `buyer` (`buyer`),
 KEY `itemserver` (`countof`,`server`,`itemId`),
 KEY `seller` (`seller`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`ah_sales_4_2014`,`ah_sales_4_2013`,`ah_sales_8_2012`,`ah_sales_4_2012`,`ah_sales_12_2011`,`ah_sales_6_2011`,`ah_sales_3_2011`,`ah_sales_12_2010`,`ah_sales_6_2010`,`ah_sales_3_2010`,`ah_sales_11_2009`,`ah_sales_8_2009`,`ah_sales_5_2009`,`ah_sales_2_2009`,`ah_sales_11_2008`,`ah_sales_8_2008`,`ah_sales_5_2008`,`ah_sales_2_2008`,`ah_sales_11_2007`,`ah_sales_9_2007`,`ah_sales_8_2007`,`ah_sales_6_2007`,`ah_sales_5_2007`,`ah_sales_3_2007`,`ah_sales_2_2007`,`ah_sales_1_2007`,`ah_sales_12_2006`,`ah_sales_11_2006`,`ah_sales_10_2006`,`ah_sales_9_2006`,`ah_sales_8_2006`)

ah_sales_4_2014 is uncompressed.


texts: Outer join on a view with calculated columns performs calculation for missing ("outer") records

texts:
I have a view in MySQL, which has some calculated fields — COALESCE and the like. If I do a left outer join on that view (so records from that view may be missing), then the calculated fields are still calculated for the missing records, instead of showing up as NULL.

Example

I've tested the following example on MySQL versions 5.5.40-36.1-log and 5.5.53-38.5 (because that's what I've got on hand) as well as on SQL Fiddle‡ versions 5.5 and 5.6.

Setup

-- A simple view
CREATE OR REPLACE VIEW foo
AS
      SELECT 1 AS x
UNION SELECT 2
UNION SELECT 3;

-- Another simple view
CREATE OR REPLACE VIEW bar
AS
      SELECT 1 AS y
UNION SELECT 2
UNION SELECT 3;

-- A (contrived) view with a calculated column
CREATE OR REPLACE VIEW baz
AS
SELECT
   f.x,
   b.y,
   SIGN(COALESCE(b.y, 0)) AS z
FROM foo f
LEFT JOIN bar b ON f.x = 2 * b.y;

Query

-- A query that does a left outer join
-- on the view with the calculated column
SELECT *
FROM foo f
LEFT JOIN baz b ON f.x = b.y;

Expected result

I would expect the following result, where we see one row with records from baz and two rows without.

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Actual result

But instead I get the following, with indeed one row with records from baz, but two rows where there are no records from baz except for the calculated column, which is calculated for all three rows!

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | 0 |
| 3 | - | - | 0 |

So z is calculated, even where baz returns no records (as evidenced by the NULLs for x and y).

My actual case involves a join between the view and a table the view is based on. I've replicated this in the example, using views only. I've also tested it without the "self-join"; this makes no difference.

Workaround

What does work for me, is hiding baz in a sub-select, like so.

SELECT *
FROM foo f
LEFT JOIN (
   SELECT *
   FROM baz
) b ON f.x = b.y;

This yields the expected result:

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Is this expected behaviour? Why?

‡: worked for me yesterday, can't get it to work today.


texts: Cannot run JSON_VALUE in SQLite database (.db file) in SQL Server Management Studio

texts:
I want to update some JSON data in SQLite database (.db file) and I met a problem.

I work in Microsoft SQL Server Management Studio where I added SQLite/SQL Server Compact Toolbox through Visual Studio Extensions. I'm succesfully able to connect to this file and I'm also able to run a query on this database (create table, insert data etc.). The only problem is that a table I want to update has a JSON column and I need to use the JSON_VALUE function to get some data from that column. And when I run  SELECT JSON_VALUE([data]) FROM myTable, I get an error message:

ErrorCode : 1
Message   : SQL logic error
no such function: json_value
Result    : Error

for a query SELECT sqlite_version I got a result 3.24.0.

What am I doing wrong? What should I add to system to make this thing working? In standard SQL everything is working fine. SSMS is version 14.0.
I also tried programs "DB Browser for SQLite" and "SQLiteStudio" and also did the same example in Visual Studio and got exactly the same error.
Any hints (please step by step, I'm totally new to SQLite) would be appreciated.


texts:
I have a table in SQL 2012 which we can call 'Table' for instance. It has items like this in it,

Column

_one
_two
three_blah
four_blah

I would like to be able to do this,

select * from Table order by Column

And the result be this,

four_blah
one
three_blah
two

So it orders the items based on removing the leading underscore characters. It can't just blindly remove the underscore characters though because the underscores might be in the middle of the items.

For instance this does not work,

select Replace(Column, '_', '') from Table order by Replace(Column, '_', '')


texts: Msg 5145: Autogrow of database log file took 1010871 milliseconds

texts:
In the past, I had an update query that ran for 7 days. I couldn't find a solution, so I created a new database with indexes. The update query has now been running for 26 hours.
I am seeing messages like the following:

Autogrow of file database1_log file in database 'database1' took 1010871 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
Autogrow of file 'database1.log' in database 'database1' took 79342 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

The data file is 40GB
The log file is 50 GB, with autogrowth set to 10%

I really don't understand what to do.


texts: mysql 8 is failure to start after upgrade from 5.7

texts:
I've upgraded one of the slave server from mysql 5.6 to 5.7 and now 5.7 to 8.
But after up-gradation steps done when i use systemctl start mysqld.service or systemctl restart mysqld facing error and mysql is not starting.
In error log i am getting following errors :

  2019-01-16T15:32:05.808246Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 22860

  2019-01-16T15:32:14.328460Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint 'columnsname_ibfk' in the referenced table 'table_name'

  2019-01-16T15:32:15.692313Z 2 [Warning] [MY-010772] [Server] db.opt file not found for test database. Using default Character set.

  2019-01-16T15:32:15.975759Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.

  2019-01-16T15:32:15.975874Z 0 [ERROR] [MY-010119] [Server] Aborting

  2019-01-16T15:32:18.083808Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13)  MySQL Community Server - GPL.

If i can start mysql then i can run upgrade command to upgrade mysql :

mysql_upgrade -uroot -p****  --upgrade-system-tables


texts: PostgreSQL for high volume transactions and for Data warehousing

texts:
Am quite new to PostgreSQL, I have never done a large deployment using it before. But, I have good experience in enterprise solutions and I want to try and apply some of what I learned using PostgreSQL.

I have site which is sized to handle large number of data and traffic. The infrastructure will be built using on amazon (AWS) using EC2 instances and EBS volumes.

The design should have two databases, a main transactional database and a data warehouse to handle analysis and reporting.

Main transactional database

will be used for live website, the site is built on multiple nodes to scale up concurrent users. Mainly we require the database for this case to be extremely fast in reading operations, we expect >100GB data with 30% annual growth. At this point, we are planning to use two EC2 servers (and add more later as we need).

my question, what is the recommended setup for the above requirements? Plus, is there a way to manage the table and volume partitioning? is there recommendations for using AWS setup?

Data warehouse database

Will be used mainly for capturing all the data from main transactional database in time dimension. so, even deleted records from main database will be captured in the DWH. Therefore, the data will be very large and growth will be even bigger. We will also use couple EC2 instances or more if required.

What is the recommended setup in this case? this will require fast writing operation because of the constant writing (ETL). Can we build OLAP cubes in PostgreSQL? if yes, has anyone out there gave it a try?

Connecting to database

The web servers will be connecting to the main database to query and write. We are currently developing an application using django which uses native library for connecting. Is it recommended to use the same basic method? or should we configure pgpool?

Data warehouse (ETL)

What is recommended way for building ETL processes to read from main and load to data warehouse? Any tools? methodology to follow? does PostgreSQL offer any helpful functions/tools in building ETL processes?


texts: Postgres record locking issue - could asynchronous UPDATES be a solution?

texts:
Is it possible (and sensible) to issue asynchronous UPDATEs to a postgres table?

Here’s the issue. I have two process, both of which access the same table.

Process “A” loads vast quantities of external records into a postgres table, this involves a trigger that updates a summary table (the summary table is the one involved in the conflict)

Process “A” is quite critical, by tweaking the number of records in each INSERT statement, and the number of INSERTS before issuing a COMMIT, I can insert about 4000 records in a single transaction that takes between 5 and 8 seconds.

Process “B” reads data from the database, does a lot of number crunching on the data and then UPDATEs the summary table. The problem is that although the reading and processing of the data can be achieved in milliseconds, the UPDATEing of the summary table typically takes  4 to 5 seconds to wait for the process “A” transaction to release it’s locks on the summary table. This is very in-efficient and dramatically slows down the overall process.

The following may (or may not) also be relevant.

Process “A” updates only fields “A”, “B” and “C” in the summary
table.
Process “B” updates only fields “D”, “E” and “F” in the summary
table.
Both process “A” and “B” only update the summary table by
explicitly identifying single rows via the unique primary key.
No other processes read or update the summary table.
Only the latest version (for any one record) of the values in “D”, “E” and “F” are relevant.

Bearing in mind the above, is it possible for process “B” to issues it’s UPDATES to the summary table asynchronously (by this I mean issue the UPDATE, and then carrying on with the next iteration of the process without waiting for the response to the UPDATE). If it is possible, how? And perhaps more importantly, is it sensible?

Thanks


texts:
We have two identical servers running SQL Server 2017.

Let's call them A and B. A is hosting 12 databases and B is hosting 6 databases.

Each server is synchronously mirroring its DBs on the other one, so that A has 12 running DBs and 6 restoring DBs; B has 6 running DBs and 12 restoring DBs.

Would there be a benefit to running all the DBs on the same server and have all the restoring DBs on the other one? We have many queries that join tables from different DBs/servers.

Previously, the DBs were not mirrored and we were running two servers to spread the load. But now that we're synchronously mirroring all the DBs, each write must occur on both servers. It makes me think that the performance gain of having the DBs on two servers is nil.

We're a registered charity and we get our Windows/SQL licenses from TechSoup for dirt cheap, so licensing price is not a factor.


texts: Is there's another function to optimize this query in MySQL?

texts:
I want to use a function that returns for me the same result in one line is that's possible??

MIN(
    CASE
       WHEN (data.pro LIKE '1 %'   )  THEN 1
       WHEN (data.pro LIKE '2 %'   )  THEN 2
       WHEN (data.pro LIKE '3 %'   )  THEN 3
       WHEN (data.pro LIKE '4 %'   )  THEN 4
       WHEN (data.pro LIKE '5 %'   )  THEN 5
       WHEN (data.pro LIKE '6 %'   )  THEN 6
    END) as products

Many thanks for any help.


texts: Is it dangerous to have duplicated entries in a table?

texts:
My table have many duplicated entries, is it dangerous if I don't fix this? My table is structured like this:

Music_id | Music_title      | Music_author | Music_link
1          Look at me now     Chris Brown
2          Look at me now     Jay-z

This is just an example, but that is the table I have. Look at the Music title. There's duplicate entries already. Sometimes the music_title will reach 10 duplicates

So I plan to make a look up Music table

Music
id | music_title
1    Look at me now

My table
id | music | music_author | Music_link
1     1      chris brown
2     1      jay-z

But my concern is, the 1 in the music field is also duplicated. Which is better anyway? I'm confused.


texts: Is it correct that functional dependencies are not symmetrical?

texts:
Say we have a relation R(A, B, C). We have one known functional dependency: A → B. I'm wondering if it's true that there would be no way to prove that A → B implies B → A. If it was true, I can't think of a proof with Armstrong's axioms. Is there a name for this? I believe asymmetry and antisymmetry state something stronger.

A → B says that any tuple sharing the same values of the attribute A must have the same values of the attribute B.

Thus this relation is valid:

A B C
1 1 1
1 1 2
2 2 1

But then so is this one:

A B C
1 1 1
1 1 2
2 1 1

Because the tuple (2, 1, 1) does not violate A → B.

Is this correct?


texts: How to make Database to support Multiple Languages

texts:
How to make the database to support national language characters if there are any special characters in languages other than English it is showing as ?.

For example :AQUAFRESH Мой первый зубик is showing as AQUAFRESH ??? ?????? ????? in Database.

Database is not supporting Language other than English.

The data is showing correct in the application (front end) but incorrect in database.  Can you please suggest what database settings needs to be changed to get the correct characters in the database.


texts: Does SQL Server TLS1.1 support only come bundled with TLS 1.2 support in 2008 R2?

texts: I strongly suspect that Microsoft introduced TLS 1.1 support in 2008 R2 in the same updates that they introduced TLS 1.2 support but I can't find any documented evidence of this. I understand that TLS 1.2 is strongly encouraged and I expect to get responses to this effect but in some cases this is not my decision to make. What I need in asking this question is positive or negative confirmation that is stronger than my own hunch.


texts:
The slave is not working due to primary key error as shown below:

      Last_SQL_Errno: 1062
       Last_SQL_Error: Error 'Duplicate entry '550696860' for key 'PRIMARY'' on query.
 Default database: 'vserv'.
 Query:
  'INSERT INTO ox_data_intermediate_ad
        (date_time, ad_id, zone_id, clicks, impressions,
         requests, operation_interval, operation_interval_id,
         interval_start, interval_end, creative_id, updated)
    SELECT
        date_time, ad_id, zone_id, SUM(clicks) AS clicks,
        SUM(impressions) AS impressions, SUM(requests) AS requests,
        5 AS operation_interval, 1871 AS operation_interval_id,
        '2012-05-26 11:55:00' AS interval_start,
        '2012-05-26 11:59:59' AS interval_end,
        0 AS creative_id, '2012-05-26 12:04:01' AS updated
    FROM
        (
        SELECT
            interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id,
            count AS clicks, 0 AS impressions, 0 AS requests
        FROM
            ox_data_bkt_c
        WHERE
            interval_start >= '2012-05-26 11:55:00'
            AN

The query mentioned does not try to insert the auto-incremented column  data_intermediate_ad_id. It expects mysql to automatically add the next ID. When I checked the slave, I found a record for primary key 550696860.

Master is 5.0 and slave is 5.5.24

The second slave with version 5.5.21 is working fine. Is this a bug related to 5.5.24 version?

The query that has an issue is as follows:

INSERT INTO
                ox_data_intermediate_ad
                (date_time, ad_id, zone_id, clicks, impressions, requests, operation_interval, operation_interval_id, interval_start, interval_end, creative_id, updated)
            SELECT
                date_time, ad_id, zone_id, SUM(clicks) AS clicks, SUM(impressions) AS impressions, SUM(requests) AS requests, 5 AS operation_interval, 1591 AS operation_interval_id, '2012-05-25 12:35:00' AS interval_start, '2012-05-25 12:39:59' AS interval_end, 0 AS creative_id, '2012-05-25 12:43:01' AS updated
            FROM
                (
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, count AS clicks, 0 AS impressions, 0 AS requests
                FROM
                    ox_data_bkt_c
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59' UNION ALL
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, count AS impressions, 0 AS requests
                FROM
                    ox_data_bkt_m
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59' UNION ALL
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, 0 AS impressions, count AS requests
                FROM
                    ox_data_bkt_r
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59'
                ) AS virtual_table
            GROUP BY
                date_time, ad_id, zone_id

I will like to know why does the slave has an issue with the insert into ... select statement.


texts:
When executing multiple SQL commands, such as in the pgAdmin SQL Editor, how can I print some strings to the console (output pane in pgAdmin). I want to monitor execution.

UPDATE x
SET a = b
WHERE a IS NULL
;

Print to console --> Finished x.

UPDATE y
SET a = b
WHERE a IS NULL
;

Print to console --> Finished y.

My question is the same as this StackOverflow Question. Or, as Craig Ringer interpreted that Question:

  Do you wish to observe SQL queries that are run by PgAdmin-III by printing them to standard error, viewed by launching PgAdmin-III on a cmd.exe interactive console?

I have seen remarks about \echo 'some_text'. But that gives me the error:

ERROR: syntax error at or near "\"
SQL state: 42601


texts: Upgrade from SQL Server 2008 R2 Enterprise to SQL Server 2016 Standard Edition?

texts:
We have SQL Server 2008 R2, which is in production & cannot be shutdown at this stage. I have installed SQL Server 2016 Standard Edition. Can I copy the databases, users and logins, with .ldfs, .mdfs & .ndfs, or use backups to restore to the server running 2016 Standard Edition?

Note that my SQL Server 2008R2 Enterprise databases do not utilize any of the Enterprise features.


texts: compare to total, compare to percent of a main category, compare over periods e.g. month over month to get growth %

texts:
This is kind of a big question. I am looking for maybe a general direction, possible solutions to my issue, if a specific solution is impossible to give over the forum.
I am working in BigQuery, however the syntax is almost identical to SQL with some functions being the exception.
I am relatively new to advanced SQL and I've been tasked with creating the following report:

from the following tables:

The report needs to be presented via Google Data Studio, where data modelling is not possible, so the data must be prepared in SQL in advance to loading in Data Studio which would be the visualization/presentation tool.
My idea was to write a query per each period required, and then UNION ALL these queries, so for example the query for "last week" as a period would be:
SELECT distinct orderid,
       net_revenue,
       country,
       main_category, sub_cat, sub_sub_cat --there's several nested categories
       'last week' as period,
FROM table_orders
join table_order_values
  on table_orders.orderid = table_order_values.orderid
full outer join categories as cats
  on cats.product_id = table_orders.product_id
join countries
  on table_order_values.country = countries.country
and table_order_values.date >
    DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK(sunday)), INTERVAL 1 WEEK)
    AND table_order_values.date <=
        DATE_TRUNC(CURRENT_DATE(), WEEK(sunday))

...and the end-table after UNION ALL of a bunch of tables for different periods would look like this:

However, with this end-table I have no clue how to get a growth % per period (so a comparison month over month, or week over week, etc.), or how to properly calculate a sub_category's revenue % of total revenue (and not with a bunch of declared variables per period), and even more difficult - to get a revenue % of any sub_category from the main category, which then also needs to be filterable by country...
Is this even possible the way it was requested? Could I restructure the data somehow from the very beginning to make these % from total, from main category, and growth over periods possible?


texts: Temporary Tables With Nonclustered Indexes Including All Columns

texts:
Background: Once per day several temporary tables that calculate a variety of different aggregated values are created. They all contain the same unique identifier (the field I would make a PRIMARY KEY on). Each table has around 100k rows with only 2–8 columns. Several JOINs and UNION ALLs are then done on the temporary tables. Every field in all of the temporary tables are used. Furthermore, most of the time every row is also used—worst case scenario, half of the rows is used somewhere and the other half is used somewhere else. Once the query is finished, the results are saved on the disk so that individuals can access the data for the rest of the day.
Question: Which of the following approaches should be the best fastest:
• Have no indexes on the temporary tables.
• Have a clustered index on all of the temporary tables (via the PRIMARY KEY declaration on the unique identifier).
• Have a nonclustered index on the unique identifier while including the remaining columns on all of the temporary tables.
• The last two bullet points together.
Thoughts: While running all four options simultaneously, each one had a query cost of 25% (relative to the batch); however, when the first bullet point is run, the execution plan (on the SELECT query) states that I should create a nonclustered index on the unique identifier while including the remaining columns for each of the temporary tables.
I'm a little perplexed by this. If I effectively use every field and row in all of the temporary tables, why would it suggest this? Wouldn't a heap or a clustered index be better?
To me, a heap would minimize the overhead in creating an index and sorting the data; and since I essentially need every row, there is no harm in doing a table scan.
A clustered index, on the other hand, should improve the temporary table that is created via a JOIN on two of the other temporary tables as well as improve the final SELECT query which relies on JOINs and UNION ALLs.
Related Note: On a large table with 400 columns and 70M rows—don't ask me why we have an absurd amount of columns—a query that only grabbed the PRIMARY KEY was ≈50 times faster when utilizing a non-relevant nonclustered index over the clustered index.
If anyone has any insight, I'd be grateful.


texts:
We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type.  I know this can't be added to the index and I also know it would be good to convert the column to nvarchar(max) however as this is a 3rd party app (its Microsoft Dynamics!) we are at present unable to do this.

The issues looks to be all the lob logical reads that are being performed as part of a RID lookup.  Is there any way of speeding this up?


texts: how to counting time in partition (MYSQL)

texts:
I looking for how to counting time by partition in condition if value=1 occurs consecutively 3 or more times count 1 activity.

Showing result on this picture


texts: What's faster than Mirroring tables, compare or write?

texts:
A co-worker is trying to sync  table B with the contents of table A, both tables have the same structure, we wrote 3 statements, one for update, one for delete and one for inserts.

I have a question when it comes to updating rows:

tables structure:

        [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [value1] [int] NULL,
        [value2] [int] NULL,

We could have this:

update tb_B
set tb_B.value1 = tb_A.value1,tb_B.value2 = tb_A.value2
from tb_B inner join  b_A on tb_B.id = tb_A.id

but each time we would be writing all the values again and again regardless if the values changed, so I thought about something like this:

update tb_B
set
    tb_B.value1 = tb_A.value1,
    tb_B.value2 = tb_A.value2
from tb_B
inner join tb_A on tb_B.id = tb_A.id
where
    tb_B.value1 <> tb_A.value1 or
    tb_B.value2 <> tb_A.value2

The last update checks if the value of any column has changed, if true, then it proceeds to update all of the values for that row.

Which do you think it's faster ? writing all the values again and again VS comparing the values and writing ONLY if at least one value is different ?

I think that the farther away the updated column is, the more SQL will take to compare because of the way the OR operator works, this is how I think it works:

think of the numbers as columns:
if ( 1 <> 1 OR 2 <> 2 OR 3 <> 3 OR 4 <> 5)
    then UPDATE

the program will make 4 comparisons in order to realize the row needs to be updated, whereas in this case:

if ( 1 <> 2 OR 2 <> 2 OR 3 <> 3 OR 4 <> 5)
    then UPDATE

the program will make 1 comparison and proceed with the update. The comparison in my opinion will vary tremendously because the number of comparisons vary for each row !

I hope I am on the right track here,

I appreciate your comments,
thanks


texts: get column from too many tables in mysql

texts:
.I have too many tables X1,X2,...Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company.
every table has structure something like this

desc X1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ReqF    | int(11)     | Yes  |     | NULL    |       |
| EmpName | varchar(32) | Yes  | MUL | NULL    |       |

now I am supposed to return all ReqF from departments that John works in.
I know i can do so by left join like this

select X1.ReqF,X2.ReqF,...,Xn.ReqF FROM X1 left join X2 on (EmpName) left join .... left join Xn on X1.EmpName=Xn.EmpName where X1.EmpName='John'

is there any better way to do so and what if we need to retrieve different fields from tables (ex X1.ReqF1, X2.ReqF2 or random number of fields from tables like [SELECT X1.F1, X1.F2, X2.F3,...])??


texts: Why is it a bad practice to allow everyone to use the sa login?

texts:
Even Microsoft discourages the use of SQL Server authentication mode, but our applications require it.

I've read that it's a best practice to not let users use the sa login directly, instead using Windows Authentication and allowing those accounts (or account groups) sysadmin privileges.

Isn't that essentially the same thing? What are the advantages/disadvantages?
How does the best practice increase the security of my SQL Server instances?
Does this only apply to production instances, or to our internal development instances, too?


texts: Upload multiple csv file to multiple tables sqlServer

texts:
I have over than 200 csv files that each of them is diffrent table.

I create now a new DB and want to upload them all so I will have all the table.

I not accessable to old DB to backup in diffrent way.

I looking for a simple way to do it fast  (script/gui/ 3pary tool)

I found I can do it with sqlServer gui but I cant upload there multiple files, only 1 by 1, so it will takes hours.

I saw this post How to upload multiple csv files to sql server without constrains but it not help me, not found there any solution.


texts: Primary key should be chosen so as attributes never change?

texts:
I was reading about keys in this DatabaseSystems book -

The primary key should be chosen such that its attributes are never, or very rarely, changed. For instance, the address field of a person should not be part of the primary key, since it is likely to change.

Can someone provide an explanation to this ?

Is it because a change might be a danger to its inherent requirement of being unique ?

I cant really make out the proper logic for this.


texts:
Is Mysql not an ACID compliant according to Postgresql? In some blogs I see Mysql is not ACID compliant. How true is that?

Let't not consider the replication here, lets consider a standalone and how efficient is Mysql ACID?

In my understanding for Mysql-ACID.

  A - Atomicity (Set of transactions should all be committed if one
  fails it has to rollback. Yes means all are committed , no means even
  one failed it has to Rollback).

  I.E. Features that supports in Mysql are.

  start Transaction; ..... commit ;
  auto_commit=1;

  C - Consistency.

  ( PK,FK,UK,NOT-NULL). It adheres to Relations and constraints for
  Databases. Instance a parent key can be deleted only when its child
  key is removed.

  I - Isolation.  Isolation between users and their state of commit.

  Read Repeatable Read Uncommitted Read Committed Serialized

  D - Durability. At the event of DB crash innodb recovers the DB by
  applying committed transaction from iblog file and discards
  not-committed transaction.

Click here for the source of this question.   - Is it because the blog is created @2001?

UPDATE Jun-30-2017: As per "Evan Carroll" response and I have personally tested the blog experiment on 5.7.18-enterprise. The results obtained from the experiment seems to be Mysql is Not an ACID Compliant.


texts: More Best Practices To Updating Large Tables?

texts:
I know there are a number of topics on this question, but I'm always seeking more insights.

I have a large table with a billion+ records. The amount of records could be reduced and archived, but the size will still be large. My task is to change a existing data type of a single column where the old value of data is safe to convert into the type.

Here are some of my approaches:

1 - Drop the constraints on the table that impact the targeted column, drop the indexes that also impact the targeted column, add a new column with NULL's at the end of the table, update the new column with the old column values in chunks of 10K, 50K or 100K increments, drop the old column when data has been copied and reapply indexes for that column only.

2 - Copy all data into a new table with the data type change in chunks as before, verify data is completed, drop the old table, rename the new table to the old and apply indexes.

3 - Import all data from another data source like a flat file to a new table with the data type change using BULK INSERT and MERGE SP's, basically like option 2 with having 2 duplicate tables, verify data, drop old to replace with new table and apply indexes.

What would be the fastest and safest option? Are there other options I'm not considering? I've updated 100 million records for other tables really well with option 1. The bigger the table, the harder option 1 becomes due to the time duration of updating.


texts: Where can I find a list of the available extensions for PostgreSQL?

texts: Just wondering if there is an official list for extensions that are available for PostgreSQL?


texts: PostgreSQL functions for creating and dropping declarative partioning

texts:
I created function for creating declararative partitions.
CREATE OR REPLACE FUNCTION createpartition(table_name varchar(25), forecast_id integer) RETURNS varchar(30) AS $$
DECLARE
    partition_name varchar(30);
    sql_query text;
BEGIN
    partition_name = table_name || '_' ||  forecast_id::varchar(10);
    sql_query = format('CREATE TABLE %I PARTITION OF %I FOR VALUES IN (%L)', partition_name, table_name, forecast_id);
    EXECUTE sql_query;
    RETURN partition_name;
EXCEPTION
    WHEN duplicate_table THEN
        RAISE NOTICE 'caught duplicate_table';
        RETURN 'duplicate_table';
    WHEN OTHERS THEN
        RAISE NOTICE 'caught others';
        RETURN 'others';
END;
$$ LANGUAGE plpgsql;

I want to create similar function for dropping partitions.
But I see in one of answers on Postgresql function to create table

yes, this is possible. however, you have to be a little careful. DDLs
in a stored procedure USUALLY work. in some nasty corner cases you
might end up with "cache lookup" errors. The reason is that a
procedure is basically a part of a statement and modifying those
system objects on the fly can in rare corner cases cause mistakes (has
to be). This cannot happen with CREATE TABLE, however. So, you should
be safe.

Can a "cache lookup" error happen with CREATE TABLE ... PARTITION OF .. or DROP TABLE, if they using in procedure?


texts: Create a view and populate 1 column with the extracted day-month from a epoch date from the main table

texts:
I have this main table (year 2010):

  Serial   | Day_start | Data
-----------|-----------|--------
  1        |1262304000 | data1
  2        |  epoch    | data2
  3        |  epoch    | data3
  4        |  [...]    | .
  5        |  epoch    | .
  6        |  epoch    | .
  7        |  epoch    | .
  [...]    |  epoch    | .
  365      |1293753600 | .
  366      |  epoch    |

Where Serial corresponds to the day of the year.

Serial: 1 -> 1/jan/2010 -> 1262304000

I want to create a view, where the first column of this view extracts the day-month from the epoch of this table.

CREATE OR REPLACE VIEW year_2010 AS
 SELECT row_number() OVER (ORDER BY "MainTable".Day_start) AS index,
    "MainTable".Day_start,
    "MainTable".Data
   FROM "MainTable"
  WHERE "MainTable".Day_start >= 1262304000 AND "MainTable".Day_start <= 1293753600;

With this SQL I create a view using the column Day_start as Index. Each, for each year. This table contains 8 Years of data (that's why I do not use the "Serial".

How do I extract the day-month from the epoch and rebuild the view?
I can't find the function I would need from here:
https://www.postgresql.org/docs/9.1/static/functions-datetime.html

The generated view should be:

Index(date)| Data(double precision)
-----------|--------
  01-01    | data1
  02-01    | data2
  03-01    | data3
  04-01    | .
  [...]    | .
  06-11    | .
  07-11    | .
  [...]    | .
  31-12    | .
  01-01    |


texts:
My PostgreSQL server unexpectedly restarted with such messages:

2017-08-16 03:44:34 GMT LOG:  received fast shutdown request
2017-08-16 03:44:34 GMT LOG:  aborting any active transactions
2017-08-16 03:44:34 GMT FATAL:  terminating connection due to administrator command
2017-08-16 03:44:34 GMT FATAL:  terminating connection due to administrator command
2017-08-16 03:44:34 GMT LOG:  autovacuum launcher shutting down
2017-08-16 03:44:34 GMT LOG:  shutting down
2017-08-16 03:44:34 GMT LOG:  database system is shut down
2017-08-16 03:46:04 GMT LOG:  incomplete startup packet
2017-08-16 03:46:04 GMT LOG:  database system was shut down at 2017-08-16 03:44:34 GMT
2017-08-16 03:46:04 GMT LOG:  MultiXact member wraparound protections are now enabled
2017-08-16 03:46:04 GMT LOG:  database system is ready to accept connections
2017-08-16 03:46:04 GMT LOG:  autovacuum launcher started

what could be the reason?

Configuration details:

OS: Ubuntu 16.04.1 LTS (Windows Azure VM)
PostgreSQL version: 9.5.8
listen address: * (my bad, but Azure have to block another ports from external access.
                   I didn't enable PG 5432 port for external access)
HBA config:
local   all             postgres                                ident
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             10.0.0.0/24             md5
host    replication     app_replicator 10.0.0.5/32              md5
local   all             all                                     peer