texts: SQL Server online maintenance - False publicity?

texts:
I'm being challenged since we have customers that are seeing different types of errors while running what is supposed to be online maintenance.  errors like timeouts, scan stopped due to data movement and more.
Some of our customers purchased SQL server enterprise edition to be able to get the rebuild index online feature.
To replicate the issues, I've been testing on a big database the dbcc checkdb, reorganize and rebuild of indexes and the update of statistics while I bombard the server with transactions on a database that is almost 1TB.
My first test was with the checkdb with maxdop=1 while the sqlServer was processing 124000 small transactions... I received a timeout from my application which has a the timeout set to 5 minutes.
Research on the way the check db works , is that is creates a snapshot, uses tempdb intensively and nolocks are created to create the snapshot...
So how can one of my small transactions be blocked if it doesn't lock tables?
My second test was to reorganize all indexes (which is also supposed to be online) while processing 124000 transactions, This time I got a deadlock...
My third test was to update all statistics with maxdop=1 while running 124000 transactions.  The error that was received in this case is: Could not continue scan with no lock due to data movement
my forth test to rebuild all indexes online while running 124000 transactions will be running soon and I will update my post with the results.
The maxdop=1 that I used where it can be used was to eliminate contention of resources.
I've read multiple articles inclusing Paul Randal's explanation of online vs offline of the rebuild of indexes and understand the difference... but as I said, I'm being challenged to explain why it isn't really online although Microsoft claims it's online.
Any input would be much appreciated.
What am I missing


texts: Practices to keep SQL Server databases easy to port to MySQL?

texts:
We've developing an ASP.NET Web application that is to be designed for MS SQL Server 2008. I'd like to minimize my future dB admin's headaches should there be a need to migrating over to MySQL.

With that goal, are there any specific design patterns or features we should avoid on MS SQL? Issues that trip over the MS SQL -> MySQL migration tools?


texts: MySQL SELECT query hangs - Opening Tables INNODB

texts:
On my server I have 6 MySQL databases. They all power Wordpress websites. For some reason, 2 of these databases are not accessible. To my knowledge they use INNODB, but I can't confirm it as even doing a:

mysql> use INFORMATION_SCHEMA;
mysql> SELECT * FROM TABLES;

hangs on me. The working databases seem fine (at least the websites are online). When I tried to access the bad database websites, it will just keep spinning.

If I do a:

mysql> use rbb;
mysql> SELECT * FROM wp_posts;

It will just hang.

My server is running CentOS 6.2 and up to now i've had no issues.

Any pointers would be much appreciated. I have no idea what to do next.

Even opening a Sequel Pro to the server hangs the application.

If I try a query and then check the process list in another connection, it shows:

+----+------+-----------+-----+---------+------+----------------+------------------------+
| Id | User | Host      | db  | Command | Time | State          | Info                   |
+----+------+-----------+-----+---------+------+----------------+------------------------+
| 10 | root | localhost | rbb | Query   | 45   | Opening tables | SELECT * FROM wp_users |

MySQL Version is 5.5.28-log:

mysql> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.5.28-log |
+------------+
1 row in set (0.00 sec)

I was experimenting about a year ago with ClouSE, but haven't used this since. My error log does report this, however.

131207 20:49:38 [ERROR] ClouSE: S3 listObjects for 'hondurasproject' failed. SSL certificate problem, verify that the CA cert is OK. Details:
error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed.

I have tried a few times to restart the server, but it hasn't helped.

Server disk space is looking good. 66G is available. Output below:

[root@server log]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda1             91G   21G   66G  24% /
tmpfs                 1.9G     0  1.9G   0% /dev/shm


texts:
I am confused after microsoft changed the documentation style.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups

Earlier we used to be able to change the version of sql server at the top of the page.  Now I just see this.

How do I change the version of document to SQL Server 2014?


texts:
I'm trying to run mongodb server :
sudo systemctl start mongod

this gives me no error, but when I try to check the status, I get error saying :
mua@mua  ~  sudo systemctl status mongod               ✔  2916  14:07:16

● mongod.service - MongoDB Database Server
   Loaded: loaded (/lib/systemd/system/mongod.service; enabled; vendor preset: e
   Active: failed (Result: exit-code) since Tue 2021-04-27 14:07:16 +06; 5s ago
     Docs: https://docs.mongodb.org/manual
  Process: 13625 ExecStart=/usr/bin/mongod --config /etc/mongod.conf (code=exite
 Main PID: 13625 (code=exited, status=1/FAILURE)

এপ্রিল 27 14:07:16 mua systemd[1]: Started MongoDB Database Server.
এপ্রিল 27 14:07:16 mua mongod[13625]: {"t":{"$date":"2021-04-27T08:07:16.650Z"},"
এপ্রিল 27 14:07:16 mua systemd[1]: mongod.service: Main process exited, code=exit
এপ্রিল 27 14:07:16 mua systemd[1]: mongod.service: Failed with result 'exit-code'
lines 1-11/11 (END)...skipping...
● mongod.service - MongoDB Database Server
   Loaded: loaded (/lib/systemd/system/mongod.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Tue 2021-04-27 14:07:16 +06; 5s ago
     Docs: https://docs.mongodb.org/manual
  Process: 13625 ExecStart=/usr/bin/mongod --config /etc/mongod.conf (code=exited, status=1/FAILURE)
 Main PID: 13625 (code=exited, status=1/FAILURE)

এপ্রিল 27 14:07:16 mua systemd[1]: Started MongoDB Database Server.
এপ্রিল 27 14:07:16 mua mongod[13625]: {"t":{"$date":"2021-04-27T08:07:16.650Z"},"s":"F",  "c":"CONTROL",  "id":20574,   "ctx":"main","msg":"Err
এপ্রিল 27 14:07:16 mua systemd[1]: mongod.service: Main process exited, code=exited, status=1/FAILURE
এপ্রিল 27 14:07:16 mua systemd[1]: mongod.service: Failed with result 'exit-code'.
~
~

Here is some details :
MongoDB shell version v4.4.5
Build Info: {
    "version": "4.4.5",
    "gitVersion": "ff5cb77101b052fa02da43b8538093486cf9b3f7",
    "openSSLVersion": "OpenSSL 1.1.1  11 Sep 2018",
    "modules": [],
    "allocator": "tcmalloc",
    "environment": {
        "distmod": "ubuntu1804",
        "distarch": "x86_64",
        "target_arch": "x86_64"
    }
}

Can anyone help me solving this problem ?
Here is the service
 mua@mua  /lib/systemd/system  cat mongod.service   127 ↵  3002  13:28:11
[Unit]
Description=MongoDB Database Server
Documentation=https://docs.mongodb.org/manual
After=network-online.target
Wants=network-online.target

[Service]
User=mongodb
Group=mongodb
EnvironmentFile=-/etc/default/mongod
ExecStart=/usr/bin/mongod --config /etc/mongod.conf
PIDFile=/var/run/mongodb/mongod.pid
# file size
LimitFSIZE=infinity
# cpu time
LimitCPU=infinity
# virtual memory size
LimitAS=infinity
# open files
LimitNOFILE=64000
# processes/threads
LimitNPROC=64000
# locked memory
LimitMEMLOCK=infinity
# total threads (user+kernel)
TasksMax=infinity
TasksAccounting=false

# Recommended limits for mongod as specified in
# https://docs.mongodb.com/manual/reference/ulimit/#recommended-ulimit-settings

[Install]
WantedBy=multi-user.target


texts: Is it possible to block a specific query, or return a static result?

texts:
Before you yell at me for doing it wrong, I know I'm doing it wrong. I'm using WHMCS (source code is not available), and we have a quarter of a million invoices. One particular query, which runs on every page, is absolutely murdering the database.

SELECT
    SUM(amountin-amountout)
FROM tblaccounts
WHERE invoiceid IN (
                    SELECT
                      id
                    FROM tblinvoices
                    WHERE
                      userid=*userid* AND status='Unpaid'
                   )

This generates the logged-in user's total unpaid invoice sum. Problem is, due to the nested queries, each pageload is running over 450k queries (the amount of rows in tblaccounts), each of which is filtering over 750k rows (the amount of rows in tblinvoices).

Can this be blocked at the database server level? Or ideally, set to return a specific value?

Server version: 5.5.41-0+wheezy1-log - (Debian)
Protocol version: 10

The tables are using the MyISAM engine. Here are the CREATE TABLE statements:

tblaccounts

CREATE TABLE `tblaccounts` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `currency` int(10) NOT NULL,
  `gateway` text NOT NULL,
  `date` datetime DEFAULT NULL,
  `description` text NOT NULL,
  `amountin` decimal(10,2) NOT NULL DEFAULT '0.00',
  `fees` decimal(10,2) NOT NULL DEFAULT '0.00',
  `amountout` decimal(10,2) NOT NULL DEFAULT '0.00',
  `rate` decimal(10,5) NOT NULL DEFAULT '1.00000',
  `transid` text NOT NULL,
  `invoiceid` int(10) NOT NULL DEFAULT '0',
  `refundid` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `invoiceid` (`invoiceid`),
  KEY `userid` (`userid`),
  KEY `date` (`date`),
  KEY `transid` (`transid`(32))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

tblinvoices

CREATE TABLE `tblinvoices` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `invoicenum` text NOT NULL,
  `date` date DEFAULT NULL,
  `duedate` date DEFAULT NULL,
  `datepaid` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `subtotal` decimal(10,2) NOT NULL,
  `credit` decimal(10,2) NOT NULL,
  `tax` decimal(10,2) NOT NULL,
  `tax2` decimal(10,2) NOT NULL,
  `total` decimal(10,2) NOT NULL DEFAULT '0.00',
  `taxrate` decimal(10,2) NOT NULL,
  `taxrate2` decimal(10,2) NOT NULL,
  `status` text NOT NULL,
  `paymentmethod` text NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  KEY `status` (`status`(3)),
  KEY `userid-status` (`userid`,`status`(3))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


texts: Secure method beside phpMyAdmin for remote mysql

texts: Recently, one of my centos linux servers was hacked and the cause of due to phpMyAdmin. It was said due to this problem PhpMyAdmin vulnerability CVE-2011-2505 (http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2011-2505). What is the best way to login securely into my mysql db? Any idea please.


texts: No priviligies to create table in custom database

texts:
I'm using locally installed express edition of SQL server. CURRENT_USER = dbo, and I used win authentication. The database was successfully created this way:

CREATE DATABASE [online_banking]
 ON  PRIMARY
( NAME = N'online_banking', FILENAME = N'C:\...\online_banking.mdf' ,
  SIZE = 512000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 30%)
 LOG ON
( NAME = N'online_banking_log', FILENAME = N'C:\...\online_banking_log.ldf' ,
  SIZE = 1024KB , MAXSIZE = 20GB , FILEGROWTH = 10%)
GO

However creating table cause the error, that I don't have permission.

CREATE TABLE [online_banking].Customers
     (
      customer_id INTEGER NOT NULL,
      customer_name VARCHAR(100) NOT NULL,
      CONSTRAINT PK_cust_id
               PRIMARY KEY CLUSTERED (customer_id)
               WITH (IGNORE_DUP_KEY = OFF)
      )
;

What is wrong here?

P.S. Table can be created this way:  CREATE TABLE Customers ...


texts: SQL Query with Aggregate Count, where at least 1 of the counts has a value in a field

texts:
SELECT PERSON_ID,COUNT(APPL_ID) COUNTS
FROM APPLICATIONS
GROUP BY PERSON_ID
ORDER BY COUNTS DESC

The above query returns all persons who have applied for all time (essentially a count of the whole table).
I want a clause to limit this to persons who have applied this year, yet still show the count of ALL their applications...
IE, a person may have applied in 2018 once, but they've applied 20 times total thus the results would be similar. Skipping over id's 1,2, 4-11.

ID | COUNT
---+-------
003|     20
---+-------
012|      4


texts: Create archive solution on seperate server

texts: We have a custom application that uses a database trigger that updates a separate "Audit" database to track any changes in data.  I'd like to come up with a way to keep another database on separate server (Archive server) that would keep older archive data. Current thought is Prod Server keeps an archive database with changes made that day/week.  Archive database pushes any changes older than a day/week to the historical archive server, then truncates all the data in the archive database (on production server) to keep inserts fast and limits the amount of data.  I'd keep the data on the archive server for a set period.  Is it possible to setup replication to keep inserting on the archive server, but not delete the data when I truncate the tables on the source database?


texts:
In a oracle table column, there is a varchar2 column which contains value '2017-02-14T18:04:34.988+00:00' I need to convert it to timestamp. I am using TO_TIMESTAMP() to convert but I am not able to find out what date mask I need to provide. I have tried with

sql> select to_timestamp('2017-02-14T18:04:34.988+00:00','YYYY-MM-DD HH24:MI:SS.FF') from dual ;

But it does not work.

Kindly suggest what datetime mask I have to specify as 2nd argument.


texts:
I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file.

My question is, how exactly do statistics aid in performance?


texts: PostgreSQL 9.2 (PostGIS) performance problem

texts:
A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow.
Well, for example, i have table:

CREATE TABLE sometable
(
  gid serial NOT NULL,
  geom geometry(LineString) NOT NULL,
  CONSTRAINT sometable_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
CREATE INDEX sometable_geom_gist
  ON sometable
  USING gist
  (geom);

The table contains about 150 000 rows and when i try to get all rows by SELECT * FROM sometable it takes 10 seconds! I doubt that its normal for PG.

When i'm trying to use spatial indexes by querying something like

SELECT ST_AsBinary(geom) FROM sometable
    WHERE   ST_Intersects(geom,
            ST_MakeEnvelope(-10000, -20000, 10000, 15000))

It takes 9 sec. (100 000 rows).
And

SELECT ST_AsBinary(geom) from sometable

takes 13 sec.

EXPLAIN ANALYZE
SELECT * FROM sometable

Seq Scan on sometable (cost=0.00..3406.47 rows=151547 width=69)
(actual time=0.021..102.959 rows=151547 loops=1)
Total runtime: 165.174 ms

EXPLAIN ANALYZE
SELECT ST_AsBinary(geom) FROM sometable

Seq Scan on sometable (cost=0.00..3785.34 rows=151547
width=65) (actual time=0.030..234.241 rows=151547 loops=1)
Total runtime: 296.704 ms

EXPLAIN ANALYZE
SELECT ST_AsBinary(geom) FROM sometable
        WHERE   ST_Intersects(geom,
                ST_MakeEnvelope(-10000, -20000, 10000, 15000))

Bitmap Heap Scan on sometable (cost=3142.47..30805.90 rows=32623 width=65) (actual time=38.644..1066.704 rows=98469 loops=1)
  Recheck Cond: (geom && '01030000000100000005000000000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2'::geometry)
  Filter: _st_intersects(geom, '01030000000100000005000000000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2'::geometry)
  ->  Bitmap Index Scan on sometable_geom_gist  (cost=0.00..3134.31 rows=97870 width=0) (actual time=38.109..38.109 rows=98469 loops=1)
        Index Cond: (geom && '01030000000100000005000000000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2'::geometry)
Total runtime: 1125.249 ms

Well, would you be kind enough as give me some tips how to solve the problem or explain the situation? Thank you.


texts:
Is it possible to reduce the initial log-file size of a DB.
See the screen-shoot below, the log-file shouldn't be more than 500 MB with an autogrowth of 100 MB.
I dont want to shrink the file, i want to reduce the allocated space to 500 MB.
How do i do it?


texts: Relational database primary key requirements

texts:
I am trying to put together an ER Diagram to design a fairly simple asset tag tracking/inventory management system for our servers/switches/UPS/etc.  The top-level entity corresponds to the business location, followed by entities for Room, Rack, RackU, and finally device.  Now, I know that primary keys are supposed to be unique, but is that unique over the entire database, or unique for the individual branch of the system?

By that I mean, there are one to many rooms in a location, and one to many racks in each room.  do the primary keys for the racks in one room in a given location need to be unique from those of either another room in the same location, or even from a room in a different location?

I probably didn't describe this very well, so feel free to ask for clarifications on or at any point.


texts: What are the main principles of SQL Server AGs depending on WSFC?

texts:
We have WSFC with 4 nodes + a file share witness.

Only nodes 1-3 have SQL server on it with Availability Groups (AGs), and node 4 is shut down gracefully.

Node 1 has the primary AAG server.

Now, we recently had an uncontrollable outage for which the only thing we know for certain is that nodes 1 and 2 were together with power supply and network, but nodes 3-4, and the witness, were cut off - but probably still up.

Since the power supply renewed, WSFC is showing results that I don't understand:

Current host server is Node 3;
In Cluster Core Resources, IP addresses that belong to nodes 1 and 2, are shown as down;
In Nodes section, nodes 1-3 are shown as up, and node 4 down (as expected);

BUT: The primary AG server is still on node 1 as it was before, and it accepts read/write connections as usually.

This make me doubt if I understand correctly how WSFC and AGs work together.

Should not the current WSFC host also be the primary AG server?
How does the listener work? In our case, we have multi-subnet networking, and the listener's DNS name always resolves to 3 IP addresses among which one belongs to the current primary AG server, and works for connections. Does WSFC determine the listener's addresses?
Is there any other principle (except quorum) that AG depends on WSFC?

Thanks.


texts: Time addition based on previous column partition by different object

texts:
I asked a question on
Time addition based on the previous row.

Now I have another one related to that question. Appreciate if someone could give me some help. I use the following code to create the data my need.

create temporary table test(object varchar(10),
                            id integer,
                            realtime datetime,
                            realminute float(15));

insert into test(object, id, realtime, realminute) values
("A","1","2018-12-12 03:03:03","96"),
("B","1","2018-12-12 05:24:01","15");

insert into test(object, id, realminute) values
("A","2","15"),
("A","3","10"),
("A","4","30"),
("A","5","30"),
("A","6","15"),
("B","2","15"),
("B","3","15"),
("B","4","15"),
("B","5","15"),
("B","6","15");

Then I have this.

object|id|          realtime                      | realminute
     A|1|           2018-12-12 03:03:03           |  96
     A|2|                                         |  15
     A|3|                                         |  10
     A|4|                                         |  30
     A|5|                                         |  30
     A|6|                                         |  15
     B|1|           2018-12-12 05:24:01           |  15
     B|2|                                         |  15
     B|3|                                         |  15
     B|4|                                         |  15
     B|5|                                         |  15
     B|6|                                         |  15

For object A, 2018-12-12 03:03:03 plus 96 minutes would be 2018-12-12 04:39:03. This is the realtime for object A in the second column. For object B, 2018-12-12 05:24:01 plus 15 minutes would be 2018-12-12 05:39:01. This is the realtime for object B in the second column. The following table is what I need.

object|id|          realtime  | realminute
     A|1| 2018-12-12 03:03:03 |  96
     A|2| 2018-12-12 04:39:03 |  15
     A|3| 2018-12-12 04:54:03 |  10
     A|4| 2018-12-12 05:04:03 |  30
     A|5| 2018-12-12 05:34:03 |  30
     A|6| 2018-12-12 06:04:03 |  15
     B|1| 2018-12-12 05:24:01 |  15
     B|2| 2018-12-12 05:39:01 |  15
     B|3| 2018-12-12 05:54:01 |  15
     B|4| 2018-12-12 06:09:01 |  15
     B|5| 2018-12-12 06:24:01 |  15
     B|6| 2018-12-12 06:39:01 |  15

How could I get this result?


texts:
I'm having an issue where my MySQL server (part of a LAMP stack running WordPress) hangs every few hours (giving WordPress a "database connection error").

The error log shows nothing other than launching after a restart, and shutting down ("normal shutdown") when I need to restart it. The only thing of note is that it says it is "purging the queue" during a restart, and then has ~300 lines like this:

151026 19:04:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 31577  user: 'root'

The only other weird behaviour I've noticed is that running mysqladmin status shows "Questions" increasing rapidly, presumably until it hangs. I've looked online and can't find much discussion about what normal range or behaviour "Questions" should exhibit. Eg, this is after 10 minutes uptime:

Uptime: 521  Threads: 3  Questions: 16667  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 100  Queries per second avg: 31.990

I have query_cache_size set to 50M, otherwise my.cnf is default on Ubuntu 14.04. This is the performance analysis from the mysqltuner perl script:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 52s (29K q [31.475 qps], 1K conn, TX: 73M, RX: 4M)
[--] Reads / Writes: 88% / 12%
[--] Binary logging is disabled
[--] Total buffers: 226.0M global + 2.7M per thread (151 max threads)
[OK] Maximum reached memory usage: 290.5M (1.81% of installed RAM)
[OK] Maximum possible memory usage: 631.8M (3.94% of installed RAM)
[OK] Slow queries: 0% (0/29K)
[OK] Highest usage of available connections: 15% (24/151)
[OK] Aborted connections: 0.00%  (0/1515)
[OK] Query cache efficiency: 71.8% (17K cached / 24K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (10 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 59% (1K on disk / 1K total)
[OK] Thread cache hit rate: 97% (39 created / 1K connections)
[OK] Table cache hit rate: 93% (103 open / 110 opened)
[OK] Open file limit used: 4% (51/1K)
[OK] Table locks acquired immediately: 100% (8K immediate / 8K locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.0M
[OK] Read Key buffer hit rate: 100.0% (6 cached / 0 reads)
[OK] Write Key buffer hit rate: 100.0% (8 cached / 0 writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/292.9M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 75.48% (6183 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 99.70% (1960793 hits/ 1966769 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 894 writes)

The hanging has started happening more frequently since an increase in logged in users (people accessing the database) and changes to my Apache config, which solved Apache crashes - my Apache prefork MPM is set as follows:

StartServers              20
MinSpareServers           10
MaxSpareServers           20
ServerLimit               1000
MaxRequestWorkers         500
MaxConnectionsPerChild    10000

The server is a DigitalOcean droplet with 8 CPU cores and 16GB RAM.


texts: Master Slave Error user specified as a definer ('dbadmin'@'%') does not exist

texts:
I setup mysql (version 5.5) master slave replication and when test it with root user from phpmyadmin data succesfully transferd. When start application slave have an error:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.1.1.31
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 6963420
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 529310
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1449
                   Last_Error: Error 'The user specified as a definer ('dbadmin'@'%') does not exist' on query. Default database: 'mydb'. Query: 'update mytbl
                                        set ....limit 1'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 6300951
              Relay_Log_Space: 1191935
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1449
               Last_SQL_Error: Error 'The user specified as a definer ('dbadmin'@'%') does not exist' on query. Default database: 'mydb'. Query: 'update mytbl
                                        set ....limit 1'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

How to solve it ? maybe just add user 'dbadmin'@'%'?


texts: Backup headerinformation hold wrong database name

texts:
We're in the proces of preparing our migration to our new servers aswell as SQL 2012 and everything is looking fine and dandy and i'm testing some of the scripts we will be using.

One of these scripts grabs all the full backups in a specfic folder and restores the databases. It looks at the Database name specified in the HEADER information of the specific backup files.

Now we've got atleast one database which some genius deceided to somehow copy from an old database, and then rename or something. (Don't ask me exactly what he/she did) When the script get's to this database it attempts to restore it with the old name. As the old database is still in use and already restored the script fails here and goes to the next database which is fine.

How ever before our migration i'd like to actually change the name of this database so it's reflected in the HEADER information so that this script works on it's own. (Yes, I know I can make the code skip this database and simply restore this one manually later).

Is there any way to check the headerinformation of a live database and change it?


texts: Handling attributes that are time-variant in a Datamart

texts:
I have looked through the entire list of sites, and this is I think the best match. This is not really about database administration, more like database design. Please excuse me and point me to the correct site.

I am designing a database for a rudimentary BI system. At this moment I have hit a wall, which is this (explaining using dummy data):

Suppose my fact table contains this information:

John Doe flew from LAX to ATL on 1 Nov in flight AB-123

The dimensions and their attributes are:

Flyer - name, club
Airport - city, code
Date - year, month, date
Flight - code, std, delay, price

Now, from this I can easily generate a report like this:

Airport --> LAX  DFW  ORD  ATL Total
Gold         50   40   10   25   125
Silver      240  300   95  140   775
Bronze     1000 1500  800 1800  5100
Total      1290 1840  905 1965  6000

Using a query like:

select fd.club, ad.code, count(f1.id) from flyer fd, airport ad, fact1 f1
where fd.id = f1.fid and ad.id = f1.aid and month(f1.date) = 10
group by f1.club, ad.code;

But my problem comes from the fact that the "club" status of a flyer is a moving target. A flyer who is in Gold today could have been in Silver in October, so I am counting him in the incorrect group here. Thus, I imagine I need a separate fact table like this:

 John Doe entered Bronze club on 8/15
 John Doe entered Silver club on 10/20
 ...

"Club" drops out as an attribute of the original flyer dimension. Instead, a new club dimension emerges.

And then to generate the report I need, I join these two fact tables.

Am I on the right track? Or is there an alternative, simpler solution to this? One alternative I could think of is to include the club in the original fact table, handling it during the ETL process. So the fact becomes:

John Doe of Silver Club flew from LAX to ATL on 1 Nov in flight AB-123

Please let me know which approach is better, or if there is a third one.


texts: Can a junction table have more than two foreign Key?

texts:
I have to design a schema that involves entities.
Schools, Teachers, Students, Classes, Sections, Subjects, Departments, Timetables
Below is the schema that I have designed.
SchoolsClassesSections // a class can have many sections, 9th can have sections A, B ...
---------------------------
id
school_id
class_id
section_id

TeachersClasses // A teacher can be in many classes and sections

----------------
id
teacher_id
school_class_section_id

ClassesSubjects
-------------------
id
subject_id
school_class_section_id

Subjects
-------------------
id
department_id

Timetables
--------------------
id
school_class_section_id
teacher_id
subject_id

I still not sure if it's a good one. The problem is there will be many schools which have many classes. Not all schools may have all the classes, some may have 1 only 10.
Same for classes, not all classes have all the sections, some classes may have only section A or no section at all.
I am never confident when it comes to schema design, however, I know normalization. Please also suggest some books or tutorials. Thank.


texts:
mysqld can't start without any info, that is the only information I get is 'Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!'.

I edited the my.cnf file to enable logging on /var/log/mysql/mysql.log but it apparently doesn't want to log anything.

So I don't know anything. I know there are many similar questions, but in most cases people are able to tell something more from the log file(s).

BTW, it is happening after abnormal system shutdown.

Update 1: in my.cnf file I can see a line like this:
Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.

When I open the file I can see this:
[mysqld_safe]
syslog

What does it mean? How can I see error log?

Update 2: when I do
mysqlcheck -u root -p --all-databases

I get "Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect"

Update 3: OK, now I know what syslog is:) It says: Can't start server : Bind on unix socket: No space left on device
So now I have a final question - on which device?


texts: SQL Query: Join Tables using two partial string matches

texts:
Using MS Access, I have to combine two tables where two criteria must be met: Container = Container AND Booking = Booking.

I have two tables where fields are missing a value:

Table1 and Table2 have in common two colunmns:

Container
Booking

Table 2 has additional column "Date". I need to add "Date" to Table1.

Problem is

Container is often missing a "check digit" at the end, so it may be 10 or 11 characters, so i need to match first 10 of container

Booking is often missing a leading zero, so it may be 9 or 10 numbers, so i need to match last 9 of booking.

The following does not work when one or the other Table are missing a leading zero and/or container check digit. How do i adjust SQL?

RIGHT JOIN Table1 ON Table2.[Booking] = Table1.[Booking] AND Table2.[Container] = Table1.[Container];

Containers can be reused on new bookings so booking number must also be match.


texts: How do I configure SQL Server Management Studio Intelisense to ignore case

texts: The way we have our db configured, case does not matter, but in the query editor intelisense  is using case much to my frustration. I like that autocomplete feature of intelisense but I have had to disable it until I can find a way to get it to ignore case.


texts:
I have an interesting situation here.  We're basically trying to migrate some SQL Server infrastructure to new hardware, and one of the boxes (single-instance, 2-node cluster) is running 2008R2.  The others are 2012 & 2014, but those don't exhibit this problem.

There's an app that connects to a server named, say, "OLD-SQL"; and let's say that IP is 11.22.33.44.  This is the name of the legacy SQL box running the default instance, SQL 2008R2, and Windows Server 2008R2.  The app's connection setting/config/string/whatever cannot be changed at this time.

The new SQL box that is set to replace that one, is named, say, "NEW-SQL"; and let's say it's IP is 11.22.33.55.  Also running SQL 2008R2 (same SQL build).  OS is Windows Server 2012 R2 (newer OS). Both boxes are actually Clustered Instances w/ 2 nodes each (old-fashioned failover clustering, nothing fancy).

So to aid the migration, for now, for testing/QA purposes, we've done the following:
1. Set up Hosts file on the client QA'ing machine to redirect the name "OLD-SQL" to 11.22.33.55 (new server).
2. Created a SQL Server Alias on the NEW-SQL server (using SQL Config Mgr.), named "OLD-SQL", pointing at itself, port 1433, protocol TCP/IP.

To test it out, I try connect via SSMS; I enter "OLD-SQL" as the server-name to connect to.  It fails with the infamous "SSPI context" error (https://support.microsoft.com/en-us/kb/811889).  The same thing happens with the application that's being tested.  Ping from cmd-line resolves fine -- it knows "OLD-SQL" resolves to new IP 11.22.33.55 based on the Hosts file.

Now, to really throw a wrench into things.  I go back onto the server NEW-SQL, and add another Alias, same parameters but named "OLD-SQL2".  This name is unique within the domain network.  I go back to my box, change my Hosts file to point from that name to the IP (11.22.33.55), and go to SSMS and try to connect again.  THIS WORKS!

I verify that I'm on the "right server" by doing a SELECT @@SERVERNAME, and lo-and-behold, it says "NEW-SQL".  But of course this isn't the alias I really want; I want to be able to give it the alias "OLD-SQL" and have my app redirected to "NEW-SQL" via the Hosts entry and the SQL Alias.

So what am I doing wrong with the first alias?  Is it just that I can't use the same name as an existing server on the network, with 2008R2?

Similar post on SO: https://stackoverflow.com/questions/6406811/alias-not-working-on-sql-server-2008-r2
(Did not solve the issue)

PS: I say "with 2008R2" specifically, because when I try the same setup (Hosts, SQL Alias) with our 2012-2014 boxes, they work the first way just fine!  (I.e. the Alias on the "NEW-SQL2012" box can be "OLD-SQL2012", which is the same as an existing server, and no problems connecting or anything.)

PPS: I did read about these aliases being more of a client-side thing, but that's why I'm using the Hosts file trick.  When we get ready for the "real" migration, we'll use DNS and other tricks outside of my knowledge (that's the SysEng's domain) to do the redirection of clients (apps/computers) to the new servers, but they said for now, for testing, the Hosts file thing is a good substitute.

Update: The main difference between the "working" setups and the "non-working" one, besides the SQL Server versions, is the fact that the old 2008R2 instance "SQL-OLD", when I connect using the "pristine" setup (no aliases or hosts-file-redir.), uses Kerberos authentication.  When I connect via a unique alias or redirect, such as "OLD-SQL2", it registers as NTLM.  And in ALL OTHER working connection methods, it's also NTLM.  What the hell is Kerberos doing to me??


texts: Is it bad to have M:M relationships defined where 1:M is being used?

texts:
In general, would it be a bad idea to design a database with M:M relationships where ever things have a relationship, instead of using, say, 1:M instead?

My reason for doing this is because later on if I decide that I no longer want a 1:M relationship but want a M:M instead, I don't have to modify the database at all; all I would have to do is change my business logic to handle "many" instead of just "one".

For instance, say I have these tables:

user
image

A user should only have one profile image, however I handle the relationship with a table like this:

user_profile_image

That table would have user_id and image_id foreign keys. The alternative would be to embed a image_id foreign key in the user table.

In my business logic, I explicitly only allow one profile image to be set for a user. But as I stated before, my theory is that later on if I want to allow multiple profile images for a user, the database doesn't need a single change and I can just update my business logic to handle many instead of one.

Is this a good idea, or is it frowned upon? Please explain? I have been doing this all throughout my app, so I don't have a single 1:M relationship defined in the schema layer, though my business logic has basically 80% 1:M relationships.


texts:
I'm trying to go from a table like this:

ID | NAME | METADATA
-------------------------------
1  | test | '{"a":"x","b":"y"}'

where the metadata column is a JSON string. What I'd like to end up with is this:

ID | NAME | A | B
-----------------
1  | test | x | y

where the JSON "keys" are merged in-line as column names and the corresponding JSON "values" as the fields. So far, I have managed to parse the JSON and create a "pivot" table like this:

A | B
-----
x | y

but I can't work out how to append the other columns from the original table. My code so far:

SELECT * FROM (
    SELECT
        SUBSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)), 1, INSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)),':') - 1) COL_NAME,
        SUBSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)),    INSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)),':') + 1) COL_VAL
    FROM   (SELECT REPLACE (REPLACE (REPLACE (:json, '}'), '{'),  '"') AS str FROM DUAL)
    CONNECT BY LEVEL < LENGTH (str) - LENGTH (REPLACE (str, ',', NULL)) + 2
)
PIVOT
(
  MAX(COL_VAL)
  FOR COL_NAME IN ('a' AS A, 'b' AS B)
);

This is where my SQL knowledge fails.

Note: I know the JSON parsing is ugly, but I can't use a 3rd-party library and I'm running on 11g so don't have any built-in JSON support.

Thanks in advance for any help!


texts: Initials separated by periods (e.g. J.E.F. Smith)

texts:
In a MySQL database I have a column that holds the initials of a person. However not all input is nice and clean. To give an example the initials for the name John Edward Fredrick Smith should be J.E.F.

Unfortunately in our database you would also find JEF or J.EF or JE.F. or J E F.

I thought I'd use the replace function to first remove all spaces and periods and then add periods after each letter. That last part (the adding a period after each letter) is the hard part. I just can't figure out how to do that in MySQL.

Any help would be much appreciated.

Vincent


texts:
We have a table which stores about 20 million records:

                     Table "public.tbl"
         Column          |            Type             | Modifiers
-------------------------+-----------------------------+-----------
 id                      | uuid                        | not null
 a                       | timestamp without time zone | not null
 b                       | timestamp without time zone | not null
 c                       | timestamp without time zone | not null
 d                       | timestamp without time zone |
 e                       | integer                     |
 f                       | integer                     |
 g                       | uuid                        |
 h                       | timestamp without time zone | not null
 j                       | uuid                        | not null
 k                       | uuid                        | not null
 l                       | character varying(32)       |
 m                       | boolean                     | not null
 n                       | boolean                     |
 o                       | boolean                     |
Indexes:
    "pk_table" PRIMARY KEY, btree (id)
    "idx_table_k" btree k)
    "idx_table_j" btree (j)
Foreign-key constraints:
    "fk_table_k" FOREIGN KEY (k) REFERENCES z(id)
    "fk_table_j" FOREIGN KEY (j) REFERENCES y(id)
Referenced by:
<....>

 select count(*) from tbl;
  count
----------
 20594896
(1 row)

  oid  | table_schema | table_name | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes | total |  index  | toast |  table
-------+--------------+------------+--------------+-------------+-------------+-------------+-------------+-------+---------+-------+---------
 16532 | public       | tbl  |  2.09275e+07 | 13775568896 |  5316452352 |             |  8459116544 | 13 GB | 5070 MB |       | 8067 MB

And we wanted to run a single query:

update tbl set o=false where o is null;

On a slow testing Google Cloud Sql Machine (1vcpu, 3.75mb ram, 100gb storage) this query took ~3 hours.

The best we could get to was ~23 minutes by increasing disk throughput.
Logs were also full of message like these:

[3091] HINT:  Consider increasing the configuration parameter "max_wal_size".
[3091] LOG:  checkpoints are occurring too frequently (5 seconds apart)
[3091] HINT:  Consider increasing the configuration parameter "max_wal_size".
[3091] LOG:  checkpoints are occurring too frequently (5 seconds apart)
[3091] HINT:  Consider increasing the configuration parameter "max_wal_size".
[3091] LOG:  checkpoints are occurring too frequently (5 seconds apart)
[3091] HINT:  Consider increasing the configuration parameter "max_wal_size".

Since we cannot tune those params on CloudSQL, we've launched a simple Debian based machine (4 vCPUs, 15 GB, 500GB storage) with PG9.6 By setting max_wal_size to 20GB and checkpoint_timeout to 50 min we got down to ~19 minutes

explain analyze update tbl set j=false where j is null;
QUERY PLAN

 Update on tbl  (cost=0.00..1720212.95 rows=59869756 width=133) (actual time=1193435.444..1193435.444 rows=0
loops=1)
   ->  Seq Scan on tbl  (cost=0.00..1720212.95 rows=59869756 width=133) (actual time=0.039..69547.781 rows=20
142544 loops=1)
         Filter: (j IS NULL)
         Rows Removed by Filter: 452352
 Planning time: 0.725 ms
 Execution time: 1193435.523 ms
(6 rows)

Looking at top cpu is at ~20% iowait so I guess we are hitting iops or disk throughput limit.

Anyway to improve this, or how to troubleshoot that? Or is this as good as it gets?


texts: First field from my return table is being eaten up

texts:
Another problem with some PostgreSQL stored procedure code...

Same from this question:

I have this function

drop function uptimes(inicio date, fim date);
create or replace function uptimes(inicio date, fim date)
    returns table (id integer, entrada timestamp with time zone,
    saida timestamp with time zone, erro boolean, uptime interval) as
$$
begin
    create temporary table localdata (id_local integer,
      entrada_local timestamp with time zone,
      saida_local timestamp with time zone, erro_local boolean) on commit drop ;
    insert into localdata select * from uptime u
    WHERE u.saida>=inicio and u.entrada<=fim order by u.entrada ;

    alter table localdata add uptime_local interval;
    update localdata set saida_local=fim where saida_local>fim;
    update localdata set entrada_local=inicio where entrada_local<inicio;
    update localdata set uptime_local=saida_local-entrada_local;
    return query select * from localdata;
end;
$$
language plpgsql;

The idea is to fetch some data from a table and treat it by replacing the entrada field for any field that is lower than the inicio parameter by the inicio.

But with it, my query returns

  id  |        entrada         |         saida          | erro |     uptime
------+------------------------+------------------------+------+----------------
 1538 | 2015-10-14 05:02:15-03 | 2015-10-14 05:04:57-03 | f    | 00:02:42
...

and commenting the

    update localdata set entrada=inicio where entrada<inicio;

they returns

  id  |        entrada         |         saida          | erro |     uptime
------+------------------------+------------------------+------+----------------
 1537 | 2015-10-12 07:04:15-03 | 2015-10-14 05:01:56-03 | f    | 1 day 21:57:41
 1538 | 2015-10-14 05:02:15-03 | 2015-10-14 05:04:57-03 | f    | 00:02:42
...

What I want is to replace 2015-10-12 07:04:15-03 to 2015-10-14 00:00:00, that is passed as parameter in inicio

Any suggestions? Maybe I passed some logic error. :(


texts: SSRS and PowerBI Report Server on same server

texts:
I've just installed the August 2018 release of PowerBI Report Server onto the same Win 2012 server as SSRS 2016. I can get it running but it clashes with SSRS with URL reservations.

I've configured them to use different Virtual directories and ports but SSRS keeps breaking.

SSRS: http://ServerName:80/Reports
PBIRS:  http://ServerName:8080/PBIReports

What have I missed?

Edit: I have SSRS 2017 and PBIRS working at the same time on my laptop. Both on Port 80 but with different virtual directories.


texts: Join tables with same keys, second table has multiple values for key

texts:
I have two tables with shared keys and I'm trying to join them and output the results to one table. The problem I'm having is the second table has multiple values for the shared key.

hosts

host_object_id | hostname  |
1              | America1  |
2              | Another1  |

vars

object_id      | varname   |  varvalue |
1              | type      |  Country  |
1              | location  |  N.A.     |
1              | capital   |  D.C.     |
2              | type      |  Another  |
2              | location  |  Location |
2              | capital   |  Example  |

What I'm trying to get is

Hostname       | Type      |  Location |
America        | Country   |  N.A.     |
Another1       | Another   |  Location |


texts:
The syntax for rules in SQL Server 2005 is given:

CREATE RULE [ schema_name . ] rule_name
AS condition_expression

Whenever using rule, for example:

CREATE RULE list_rule
AS
@list IN ('1389', '0736', '0877');

Why are we using a variable like @list in it? It doesn't correspond to the table name or anything.
Is that used for storing the values given in the IN statement or anything else?

I just need an explanation for this. I know that rules are now deprecated but I am using SQL Server 2005.


texts: What is the benefit of marking function as IMMUTABLE?

texts:
I was looking through some of the plpgsql functions in our codebase and realized that most of them cannot be set IMMUTABLE only because they contain reference to now(), e.g.
CREATE OR REPLACE FUNCTION find_nearest_time (
  target_times time without time zone[],
  target_dows int[],
  timezone_name text
)
RETURNS timestamp with time zone
AS $$
DECLARE
  target_time time without time zone;
  nearest_time timestamp with time zone;
  next_time timestamp with time zone;
BEGIN
  IF (
    array_length(target_times, 1) = 0 OR
    array_length(target_dows, 1) = 0
  ) THEN
    RETURN null;
  END IF;

  FOR add_day IN 0..6
  LOOP
    FOREACH target_time IN ARRAY find_nearest_time.target_times
    LOOP
      next_time := (date_trunc('day', (now() at time zone find_nearest_time.timezone_name) - target_time::interval) + interval '1 day' + target_time::interval) at time zone find_nearest_time.timezone_name;
      next_time := next_time + interval '1 day' * add_day;

      IF (
        extract(isodow from next_time) = ANY(target_dows) AND
        (
          nearest_time IS NULL OR
          nearest_time > next_time
        )
      ) THEN
        nearest_time := next_time;
      END IF;
    END LOOP;

    IF (nearest_time IS NOT NULL) THEN
      RETURN nearest_time;
    END IF;
  END LOOP;

  RETURN next_time;
END
$$
LANGUAGE plpgsql;

However, we can easily rewrite the above function to where now() is passed as a parameter, e.g.
CREATE OR REPLACE FUNCTION find_nearest_time (
  seed_time timestamp with time zone,
  target_times time without time zone[],
  target_dows int[],
  timezone_name text
)
RETURNS timestamp with time zone
AS $$
DECLARE
  target_time time without time zone;
  nearest_time timestamp with time zone;
  next_time timestamp with time zone;
BEGIN
  IF (
    array_length(target_times, 1) = 0 OR
    array_length(target_dows, 1) = 0
  ) THEN
    RETURN null;
  END IF;

  FOR add_day IN 0..6
  LOOP
    FOREACH target_time IN ARRAY find_nearest_time.target_times
    LOOP
      next_time := (date_trunc('day', (find_nearest_time.seed_time at time zone find_nearest_time.timezone_name) - target_time::interval) + interval '1 day' + target_time::interval) at time zone find_nearest_time.timezone_name;
      next_time := next_time + interval '1 day' * add_day;

      IF (
        extract(isodow from next_time) = ANY(target_dows) AND
        (
          nearest_time IS NULL OR
          nearest_time > next_time
        )
      ) THEN
        nearest_time := next_time;
      END IF;
    END LOOP;

    IF (nearest_time IS NOT NULL) THEN
      RETURN nearest_time;
    END IF;
  END LOOP;

  RETURN next_time;
END
$$
LANGUAGE plpgsql
IMMUTABLE;

This way the function can be marked as IMMUTABLE.
What is the benefit of marking function as IMMUTABLE in this case?


texts:
I recently installed SQL Server 2014 in my machine. I configured my user and provided high level access for the same. While connecting through SSMS 2014; if I choose 'Windows Authentication', I can able to connect to database engine successfully. However if I choose to connect through 'SQL Server Authentication' and provide my username/password; I'm getting Error: 18456.

The purpose of me trying this option is - I wanted to try out subscriptions for my SSRS report. Since I opted for 'Windows Authentication' in my report settings I couldn't able to do subscriptions for my reports.

Any suggestions will be very useful. Thanks.


texts: Why is below 2 query generating different execution plan in MYSQL?

texts:
Only difference in the below query is the order by column. The resultset of both query is same.
However, the 2nd query execution plan shows 'Using temporary; Using filesort' for ordering where as for 1st query it does not require filesort.
Apart from id I have index on c.table_b_id and c.some_indexed_id
SELECT * FROM TableA a
inner join TableB b on a.id = b.id
inner join TableC c on c.table_b_id = b.id
where c.some_indexed_id = 1
order by c.table_b_id asc;

SELECT * FROM TableA a
inner join TableB b on a.id = b.id
inner join TableC c on c.table_b_id = b.id
where c.indexed_id = 1
order by b.id asc;


texts: SQL Server: INSERT in transaction locks the table until transaction finished

texts:
The sample VB.Net code below gets stuck and times out at the first read operation on CMD2.
That seems to contradict the top ranked answer to question 6374.

If that answer was correct, what can help me avoid the lock here?

SQL Server 2008 was used for testing this.

    Using cn1 As New SqlConnection("Server=(local);database=tempdb;Integrated security=SSPI"),
          cn2 As New SqlConnection("Server=(local);database=tempdb;Integrated security=SSPI"),
          cmd1 As SqlCommand = cn1.CreateCommand,
          cmd2 As SqlCommand = cn2.CreateCommand

        cn1.Open()
        cn2.Open()

        Try
            cmd1.CommandText = "CREATE TABLE ttt1 (x int, y int)"
            cmd1.ExecuteNonQuery()

            cmd1.Transaction = cn1.BeginTransaction(IsolationLevel.ReadCommitted)

            cmd1.CommandText = "INSERT INTO [ttt1] VALUES (1,2)"
            cmd1.ExecuteNonQuery()

            ' The next query holds up execution,
            ' waiting for the table to become available
            ' until a timeout occurs.
            ' Tried "select top 1 x" as well: also times out.
            cmd2.CommandText = "SELECT count(*) FROM ttt1"
            Dim i1 As Integer = CInt(cmd2.ExecuteScalar)

            cmd1.Transaction.Commit()

            Dim i2 As Integer = CInt(cmd2.ExecuteScalar)
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        Finally
            cmd1.CommandText = "DROP TABLE ttt1"
            cmd1.ExecuteNonQuery()
        End Try

        cn2.Close()
        cn1.Close()
    End Using

I would post this as a comment to the highest quoted answer to question 6374, but (1) it's too long to post as a comment, (2) I don't have the required points to comment yet, (3) posting a question as an answer to another question seemed even more wrong than launching a new question about the same thing.


texts: Backup and restore db2 between instances and diferent owners

texts:
I have two instances of db2 express-c. On one of those instances I have a database onto which I created a schema with tables and stored procedures and other stuff.

I've created a backup of that DB and restored it onto the other instance that I had.

That other instance has db2inst2 as the owner, and I've transferred ownership of the schema to him, granted control on everything, granted privileges over the schema etc...

I've given execute to this user over the stored procedures, and I've given control over every package on the DB.

Right now I can create new procedures on this schema, but I can't replace a previously existing stored procedure.

Right now I tried to transfer ownership of the package that my SP uses and I got caught with a weird error:

the command I used was:

transfer ownership of package schema.P1001724249 to user db2inst2 preserve privileges;

And I got this error:

  Transferring ownership of "schema.P1001724249" failed because of a dependency involving "schema.SQL150203162243554". Reason code: "5".. SQLCODE=-20344

I'm getting out of ideas, has anyone ever did something close to this?


texts:
I have a linked server setup on my source production server. I also have a secondary server which is the linked server. What I was looking to do is to execute a stroed proc and log connections to the linked server. However I seem to be having issues.

  The object name 'MetricDB.Baseline.dbo.WhoIsActive' contains more than
  the maximum number of prefixes. The maximum is 2.

Metric DB is the destination server which is configured on the source server as a linked server.

I been using Adam's sp which was described by Brent here:
https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

Due to space issues on my production DB I wanted to log it all to another server. Has anyone done this previously? or know if I'm missing something?

Thanks


texts: Update same field from multiple transaction

texts:
I have a service which makes some manipulations in DB, everything is working normally until I tried to make two requests in parallel.

Below see the sql debug log:

Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): START TRANSACTION;
Executing (f49986af-9741-4f20-94ba-0644fbafea05): START TRANSACTION;
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
Executing (f49986af-9741-4f20-94ba-0644fbafea05): INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1
Executing (f49986af-9741-4f20-94ba-0644fbafea05): UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): COMMIT;
Executing (f49986af-9741-4f20-94ba-0644fbafea05): ROLLBACK;

Deadlock found when trying to get lock; try restarting transaction

I don't understand why a transaction does not wait for another to end, but a Deadlock exception is thrown?

Thanks.


texts:
I have a table like this,

CREATE TABLE foo (
    id integer,
  col1 integer,
  col2 integer,
  col3 integer,
  col4 integer,
  col5 integer
);
INSERT INTO foo (id, col1, col2, col3, col4, col5)
VALUES
    (322, NULL, 3, 1, 2, 2),
    (323, 1, 3, 1, 2, 2),
    (324, 1, 1, 1, 2, 2),
    (325, 3, 3, 3, 3, NULL);

I need to write a query such that I need to get

ID, concatinated string of unique value from the columns without null

My desired output should be

ID, UniqVal
322, "3,1,2"
323, "1,3,2"
324, "1,2"
325, "3"

Note: The version is MySQL 5.5


texts:
my DBA obviously thinks that it is best practice to use shrinkdb / shrinkfile all the time some bigger table is truncated / deleted just a little bit.

I tried to talk sense to him and explain him how it works but he won't listen.
So I end up with a database / database files with messed up pages.

Is there any way that i can fix that for certain tables. Does an Index and Table Rebuild also reorders / reallocate the data or does it just logically rebuild it?

In the end i want new fresh data pages because the old ones might be copied to the front of the data file due to shrink usage.


texts: HAProxy + Patroni - Configure reads from a master when no slaves available

texts:
I have a HAProxy + Patroni setup with split reads and writes.
The problem is, when both my replicas go down, only my writes are supported, the reads stop because both replicas are down.
How is this problem generally handled in this setup? Is there a way to have HAProxy direct the read traffic to the master in case no read servers are available?


texts: Consolidate / sort / combine / merge data for dummies

texts:
I am looking for a non-scripting solution to combine data from 3 different tables (preferably using  spreadsheet software like Microsoft Excel but a database would also work e.g. Microsoft Access). I have tried searching for an answer but have not managed to find anything useful.
So, how do I take these 3 tables...
Table 1: 4 most popular past 1 day
|Label    |Frequency|Rank|
|---------|---------|----|
|Apples   |2        |4   |
|Pears    |5        |2   |
|Oranges  |10       |1   |
|Kiwis    |3        |3   |

Table 2: 4 most popular past 7 days
|Label    |Frequency|Rank|
|---------|---------|----|
|Apples   |20       |3   |
|Pears    |50       |2   |
|Plums    |100      |1   |
|Oranges  |10       |4   |

Table 3: 4 most popular past 30 days
|Label    |Frequency|Rank|
|---------|---------|----|
|Apples   |550      |4   |
|Pears    |650      |3   |
|Plums    |1000     |1   |
|Mangoes  |990      |2   |

...and automatically merge them into...
Table 4: combined table
|Label    |30 days  |    |7 days   |    |1 days   |    |
|---------|---------|----|---------|----|---------|----|
|         |Frequency|Rank|Frequency|Rank|Frequency|Rank|
|Apples   |550      |  4 | 20      |3   |2        |4   |
|Pears    |650      |  3 | 50      |2   |5        |2   |
|Plums    |1000     |   1| 100     |1   |1        |    |
|Kiwis    |         |    |         |    |3        |3   |
|Mangoe   |990      |2   |         |    |         |    |
|Oranges  |10       |    | 10      |4   |10       |1   |

As, you can see, Table 4 comprises the columns from Tables 1, 2, and 3. No mathematical operations are performed.
Where there is an entry that does not have data, the cell is left blank, for example, I just got some kiwis in the shop to sell yesterday and they sold very well and got a rank but because I only just got the kiwis yesterday there is no data for them selling over the past 7 days and past 30 days. Similarly, there have been no mangoes in the shop to sell in the past 1 day or 7 days but actually they have sold very well over the past 30 days.
Please not, as far as I can tell the "Consolidation" routine in Microsoft Excel does not do what I am looking for.
Thank you!


texts: How to insert a record with generated identifiers using UUID()?

texts:
The Problem

Overview

So I'm trying to create records that have:

id (primary key, auto-increment)
4 columns that are each a different UUID generated using the MySQL
functions: REPLACE(UUID(), '-', ''). These 'obfuscated keys' are
used for external lookup in other tables. REPLACE() is used to
strip the dashes (-) from the UUID generated by MySQL, which is
required to later transform the UUID into a binary format.  Please
don't worry about that!!!

The Table structure is as follows:
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| info            | varchar(32) | YES  | UNI | NULL    |       |
| note            | varchar(32) | YES  | UNI | NULL    |       |
| auth            | varchar(32) | YES  | UNI | NULL    |       |
| test            | varchar(32) | YES  | UNI | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

Environment

OS         : CentOS 7
Database   : MariaDB/MySQL
DB Version : 5.5

Partial Solutions Found

I have found a few articles that seem to point "a direction", however I have been unable to solve this.

Trigger before insert : Partially works, but only on the first column.  The MySQL error is: Error Code: 1235. This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
  Also requires the columns to allow null defaults.  Seems like too much of a band-aid hack.

CREATE TRIGGER before_insert_infoid
  BEFORE INSERT ON user
  FOR EACH ROW
      SET new.info = replace(uuid(),'-','');
CREATE TRIGGER before_insert_noteid
  BEFORE INSERT ON user
  FOR EACH ROW
      SET new.note = replace(uuid(),'-','');
CREATE TRIGGER before_insert_authid
  BEFORE INSERT ON user
  FOR EACH ROW
      SET new.auth = replace(uuid(),'-','');
CREATE TRIGGER before_insert_testid
  BEFORE INSERT ON user
  FOR EACH ROW
      SET new.test = replace(uuid(),'-','');

Direct using insert : Works, but means that the logic is not contained in the database.  Also, I get a MySQL warning warning(s): 1364 Field 'id' doesn't have a default value.

INSERT INTO `meanlearn`.`students`
(`info`,
`note`,
`auth`,
`test`)
VALUES
(REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''),
REPLACE(UUID(),'-',''));

Preliminary Conclusion / Request for Solutions

I'm sure I'm missing something.  Any and all suggestions are greatly appreciated.


texts:
I have a table with a few rows in it. It has a primary. I insert a large number of rows, including all fields including the primary key. Now when I try to an insert without specifying the primary key it fails because it's trying to use a number that was already used as a primary key in the previously inserted rows. How can I tell the table to create new primary keys at numbers higher than the existing keys?

For example, say I have rows with primary keys 1-8 in the table and I do a mass insert of rows with primary keys 9 through 64. Then I try to do a single insert without a primary key and it fails because it's trying to use 9. The next try fails because it's trying to use 10. And so on. I want to tell the table to start counting new primary keys at 65. How can I do that?


texts: How to get the schema name of a table of type regclass in PostgreSQL?

texts:
In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??).

CREATE OR REPLACE FUNCTION column_exists(_tbl regclass, col_name text)
  RETURNS bool AS
$func$
  SELECT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema=get_schema($1)
      AND table_name=get_table($1)
      AND column_name=$2
    );
$func$  LANGUAGE sql;

So if the table name is 'staging.my_table'::regclass, I'd like to get staging from an imaginary function get_schema.

Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)?

In addition, is it guaranteed that the table name _tbl, when converted to text, will always have a schema name? (i.e. not omitting things such as public.)

I'm not very familiar with the regclass type. I searched but couldn't find how to extract the schema name, and just wanted to ask first before re-inventing wheels.


texts: SQL Server Update Statement joining on index column

texts:
Using SQL Server 2012, I have a stored procedure that updates data by doing a delete and insert similar to this:

DELETE  FROM MyTable
WHERE   EXISTS (   SELECT   1
                   FROM     StagingMyTable s
                   WHERE    MyTable.ID = s.ID
                            AND MyTable.ID2 = s.ID2)

INSERT INTO MyTable
SELECT  *
FROM    StagingMyTable

which was changed to a Update and Insert process like this:

UPDATE  mt
SET     mt.ID = s.ID
      , mt.ID2 = s.ID2
      , mt.col1 = s.col1
      , mt.col2 = s.col2  /* Rest of the columns here*/
FROM    MyTable        mt
JOIN    StagingMyTable s ON mt.ID = s.ID
        and mt.ID2 = s.ID2

INSERT INTO MyTable
SELECT  *
FROM    StagingMyTable s
WHERE   NOT EXISTS (   SELECT   1
                       FROM     MyTable
                       WHERE    MyTable.ID = s.ID
                                AND MyTable.ID2 = s.ID2)

There is a clustered index on MyTable on the ID and ID2 columns which is why we decided to update the stored procedure to update and insert.  What I want to know is whether or not I should leave out the two key columns on the update statement even though those values aren't being changed i.e.

UPDATE  mt
SET     mt.col1 = s.col1
      , mt.col2 = s.col2  /* Rest of the columns here*/
FROM    MyTable        mt
JOIN    StagingMyTable s ON mt.ID = s.ID
                         and mt.ID2 = s.ID2

If I include the key columns in the update statement, does SQL Server thinks these will be new values and delete and re-insert the rows anyway?


texts: How to combine "SELECT" and "REPLACE INTO" when combining two MySQL tables into another?

texts:
I've got data with measurements split between two different tables, but the data doesn't need to be present in both:

mysql> SELECT * FROM t1;          mysql> SELECT * FROM t2;
+----------------+-------+        +----------------+-------+
| measurement_id | value |        | measurement_id | value |
+----------------+-------+        +----------------+-------+
| 1              | data1 |        | 1              | NULL  |
| 2              | data1 |        | 2              | data2 |
| 3              | NULL  |        | 4              | data2 |
| 5              | data1 |        | 8              | data2 |
| 8              | data1 |        | 9              | data2 |
| 9              | NULL  |        +----------------+-------+
+----------------+-------+

My users need to be able to combine the two tables into a third table, depending on their choice of whether data1 should take precedence over data2, whether NULL data should be copied, etc.

An example of one such possible user-requirement combination is "fill t3 with data from t1 unless there is non-NULL data from t2 to replace NULL or non-existing data from t1."  The resulting table would be

mysql> SELECT * FROM t3;
+----------------+-------+
| measurement_id | value |
+----------------+-------+
| 1              | data1 |
| 2              | data1 |
| 3              | NULL  |
| 4              | data2 |
| 5              | data1 |
| 8              | data1 |
| 9              | data2 |
+----------------+-------+

The way I implement that condition is the following SQL commands:

INSERT INTO t3 SELECT * FROM t1;
REPLACE INTO t3 SELECT * FROM t2 WHERE t2.measurement_id NOT IN (SELECT measurement_id FROM t1) OR t2.measurement_id IN (SELECT measurement_id FROM t1 WHERE value IS NULL);

There are other example, more simple or more complicated, I can give, but they all rely on first copying all data from one table into another, then doing a REPLACE INTO to overwrite data on conditions.

My question is: How can I combine the above INSERT and REPLACE INTO commands into a single command so I'm not copying data from t1 that will just be replaced in the second command?


texts: Check which statistics were used in query optimization

texts: Is it possible to tell when statistics were last accessed by a query using T-SQL? I'm trying to ascertain what statistics are involved in a query, I know I can check the visual query plan but I wondered whether there was a table (or tables) with this information.


texts: Design database with versioning and without redundant information

texts:
I have the following tables:

  Orders -> Procedures -> Steps -> Tasks -> NeededMaterials -> Materials

When I receive an order I want to generate procedures for its execution. The procedures are different depending on the order and its parameters.
Each procedure has some steps and each step contains several tasks. For each task are assigned materials.
In each procedure, the steps, tasks, and materials may be different, depending on the order's parameters, so I want to generate and store them.

All of the Procedures, Steps, Tasks and NeededMaterials are predefined, but at any time they may be updated(changed) and I need to keep track of all of the executed(generated) procedures with their steps, tasks, and materials. I need the possibility to trace back an order for example that was generated a year ago, and since then the procedure has been changed several times.

The problem is that I don't know how to do it while avoiding to store redundant information. Is it actually possible? Is there a design pattern? I can easily create another set of tables with the generated content, but then on every order I will store all of its data(i.e. all material names), because everything may be changed at any time.

If there is no way of avoiding redundancy, is NoSQL database a better approach?


texts:
I have a Mongo replica set with a few secondaries. A box, which hosts a secondary instance, crashed and lost the database.

I started the secondary Mongo instance again and now it's stuck in STARTUP2 for more than 12 hours. Does it make sense ? The docs say Mongo should be in STARTUP2 for a short period of time before entering the RECOVERING state

What does STARTUP2 exactly mean ? Is it copying the database from the primary ? How can I verify it (assuming the Mongo is running in Linux) ?


texts: Connect to Microsoft Access database via Microsoft SQL Server / MySQL

texts:
A client has an Microsoft Access database where they save their products.
To access the database now you have to login via VPN and open Microsoft Office Access.
I need to access this database from a PHP script running on another server.

I've done some research and found that it's not possible to connect to an Access database like a MySQL database.
So what I wonder is if it may be possible to set up an Microsoft SQL Server on the same server that uses the Microsoft Access database. So I can connect to the Microsoft SQL Server with the PHP script. Or if it's possible MySQL.

They are running Microsoft Windows Server 2008 and Microsoft Office Access 2003.


texts: Peer-to-peer replication - replication stored procs not created after new peer initialization

texts:
I have a vexing situation where I'm trying to add a new node to peer-to-peer replication.  Replication stored procedures don't get created in the new node causing the dreaded 'stored procedure doesn't exist" error message.

The publisher is a busy OLTP database so stopping the agent is not an option.

I did the following steps:

Set up database A in Server A (SQL Server 2014 SP2) to be a peer-to-peer replication publisher
Backed up database A
Restored backup to database A in Server B (SQL Server 2014 SP2)
Performed the configure peer-to-peer steps to add Server B as the new peer,
selecting Option 2 in New Peer Initialization to initialize the new peer from the backup
Database A on Server B is now a subscriber for Server A publisher
Launched Replication Monitor and immediately noticed that the publisher on Server A can't execute replication stored procedure sp_XXXX because it doesn't exist in the subscriber on Server B.
I checked Database A on Server B and noticed it's missing all the replication stored procs it was supposed to get from the publisher.

Am I missing something or is there a bug in the peer-to-peer replication config process?  No matter how many times I repeated the process even after carefully researching to make sure I checked off every box.

Any advice is much appreciated!


texts: can't backup 11.2.0.2 database after catalog upgrade to 11.2.0.4

texts:
(main question is 4th)

1.
We today have almost all databases running with Oracle 11.2.0.2.
Actually we have an RMAN database created (in 11.2.0.2) and a Catalog with user (called RMAN112)

2.
We are now installing new databases with version 11.2.0.4.

3.
What should we do in order to backup these new database ?

should we create a new user in the current RMAN databases ? (rman114 ?) even if those RMAN db's are running 11.2.0.2 ?
should we create a new RMAN database running 11.2.0.4, and new catalogs, dedicated for these 11.2.0.4 databases ?
should we upgrade the current catalog and how ?

I've just tested to register a new 11.2.0.4 database in the current catalog (11.2.0.2), it works, but then i had to do an "upgrade catalog" twice.
only problem : the backup of all the 11.2.0.2 databases now fail with errors like :

RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 11/21/2014 16:15:34
RMAN-06004: ORACLE error from recovery catalog database: ORA-04068: existing state of  packages has been discarded
ORA-04061: existing state of package "RMAN112.DBMS_RCVMAN" has been invalidated
ORA-04065: not executed, altered or dropped package "RMAN112.DBMS_RCVMAN"
ORA-06508: PL/SQL: could not find program unit being called: "RMAN112.DBMS_RCVMAN"
How do i fix that ? and what is the best practice ?
after running a few "resync catalog;" it looks like i can backup a few 11.2.0.2 instances, but i don't really understand how it works..

thanks a lot

regards,


texts:
I'm a bit confused with checkpoint_segments definition in PostgreSQL documentation. What happens when checkpoint_segments reached?

The dirty pages in shared_buffers written to WAL. Then what is the
usage of background writer?
WAL is merged to actual data files.


texts:
I am learning SQL, and I am trying to get a job. I have the following table in MS SQL Server 2014:

The table is called Game with the following fields: Name, Stage #, Score.

My goal is to write the winner of each stage with his/her name (winner of the stage is the person who earned the maximum score).

Here is the original table:

Name    Stage #         Score
George     A              10
Joe        A              10
Pete       A               9
Jane       B               7
Sally      B               6

Here is what the output should look like:

Winner Name    Score         Stage
George           10             A
Joe              10             A
Jane              7             B

How can I accomplish this task? A colleague referred me to http://sqlfiddle.com/ to help me figure this out, but this website is apparently not working well for MS SQL Server 2014 or MS SQL Server 2008. Therefore, can I receive some assistance here?

I understand some of the basic functions, such as SELECT, WHERE, GROUP BY, JOIN, and HAVING, but I am having trouble putting it all together to get the correct three-line output that I desire above.


texts: Guidance on simplifying the following diagram

texts:
I have a case scenario where a customer can book accommodation or flight only or both.

I have modeled the accommodation part, however i am making things complex by over-thinking how stuff works in real life.

What i have thought is that a customer cannot book a flight because it is referring to a whole aircraft, very unlikely. Therefore i thought that a customer books a flight seat.

The following is what i have done so far, but i have made it too complex.

Can someone guide me to make this simpler? Could i merge flight_seat with flight and aircraft? please help


texts: Transfer row from 'parent' to 'child' without creating duplicate and without knowing all 'parent' columns

texts:
I want to set up a database with different schemas that correspond to different data-entry systems. There needs to be a “master” (administration) schema with a “parties” table, and multiple other schemas with their own “parties” tables that inherit fields from the master parties table

Here’s a simplified example of the structure:

'PARENT'

CREATE TABLE master.parties
(
  party_key serial NOT NULL,
  name text,
  date_of_birth date,
  phone text,
  CONSTRAINT "PK_master_parties" PRIMARY KEY (party_key)
)

'CHILD'

CREATE TABLE corporate.parties
(
  corp_key integer,
  corp_role text
) INHERITS (master.parties)

I would like to be able to bring down a row that was added to the master.parties table and insert it into any of the parties tables in the other schemas (ie. corporate), without creating a duplicate row in the master table.

This solution I found on another post (https://stackoverflow.com/questions/29509162/postgres-table-inheritance-move-from-parent-to-child-and-vice-versa) was very close to what I am looking for:

WITH deleted AS (
  DELETE FROM ONLY master.parties
  WHERE party_key = 1
  returning *
)
INSERT INTO corporate.parties (name, date_of_birth, phone, corp_key, corp_role)
SELECT name, date_of_birth, phone, 2, 'President'
from deleted;

However, it seems to me that there should be a better way of adding a row from the 'parent' table to the 'child' table without having to delete the existing row in the 'parent' table.

This solution also poses another, more serious problem: if a change is made to the parties table in the master schema (eg. new column added), I don’t want to have to update all the data-entry systems corresponding to the sub-schemas to include that new column in every INSERT statement. If I were to miss an INSERT statement in code, or if it wasn't possible to immediately update one of the data-entry systems, I’d risk losing the values in that new column of the master.parties table when I try to bring down a row into another schema’s party table (as it is not included in the existing INSERT statement).

Basically what I’m after is to have the ability to add a row to a 'child' table from the 'parent' table, without necessarily knowing all of the columns in the 'parent' table and without creating a duplicate row in the 'parent' table. Is this possible? Should I be reconsidering my DB design?

Any help or suggestions are much appreciated!


texts:
We have 2 tables:

 CREATE TABLE `allcallers` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `company_id` varchar(50) NOT NULL,
  `clid` varchar(30) NOT NULL,
  `clid_raw` varchar(50) NOT NULL,
  `uid` varchar(32) NOT NULL,
  `channel_id` varchar(100) NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `type` varchar(30) NOT NULL COMMENT 'Call,SMS,Fax',
  `event` varchar(30) NOT NULL COMMENT 'Incoming/Outgoing',
  `status` varchar(20) NOT NULL COMMENT 'failed/success/bridege/missed',
  `via` varchar(50) NOT NULL COMMENT 'department',
  `department_id` varchar(50) NOT NULL,
  `filename` varchar(50) NOT NULL,
  `file_url` varchar(200) NOT NULL,
  `state` int(5) DEFAULT NULL,
  `country` int(5) NOT NULL,
  `rdnis` varchar(50) NOT NULL,
  `is_billable` tinyint(4) NOT NULL DEFAULT '0',
  `is_synced` int(2) NOT NULL,
  `star_type` int(2) DEFAULT '0',
  `is_archived` int(2) DEFAULT '0' COMMENT '0=not archived ,So show in logs\n1=archived ,Don''t show ',
  `server` varchar(20) NOT NULL,
  `notification_status` tinyint(4) NOT NULL DEFAULT '0',
  `subscription_status` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `start_time` (`start_time`) USING BTREE,
  KEY `uid` (`uid`) USING BTREE,
  KEY `Index 4` (`company_id`,`clid`,`via`,`status`) USING BTREE,
  KEY `is_synced` (`is_synced`),
  KEY `remarketing1` (`subscription_status`,`start_time`) USING BTREE,
  KEY `remarketing2` (`subscription_status`,`department_id`) USING BTREE,
  KEY `remarketing3` (`company_id`,`type`,`status`,`event`) USING BTREE,
  KEY `allcaller_allcaller_logs` (`start_time`,`type`,`event`,`is_billable`,`end_time`,`company_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and second table

CREATE TABLE `allcaller_logs` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `allcaller_id` varchar(50) NOT NULL,
  `property_id` varchar(50) NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `uid` varchar(32) NOT NULL,
  `via` varchar(200) NOT NULL,
  `user_id` varchar(50) NOT NULL,
  `transfer_to` varchar(50) DEFAULT NULL,
  `file` varchar(50) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `action` varchar(50) NOT NULL,
  `country_code` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `allcaller_id` (`allcaller_id`,`user_id`,`start_time`) USING BTREE,
  CONSTRAINT `FK_allcaller_logs_allcallers` FOREIGN KEY (`allcaller_id`) REFERENCES `allcallers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

We are using this query:

SELECT
  SUM(CEIL(TIME_TO_SEC(TIMEDIFF(C.`end_time`,C.`start_time`))/60)) AS `call_duration`
FROM `allcallers` A
INNER JOIN allcaller_logs C ON C.allcaller_id = A.id
WHERE A.start_time BETWEEN '2015-05-22 23:59:59' AND '2015-06-10 18:49:29'
  AND A.type = 'call'
  AND A.event = 'incoming'
  AND A.is_billable = '1'
  AND (C.`action` = 'received' OR C.`action` = 'transferred')
  AND A.end_time != '0000-00-00 00:00:00'
  AND  A.company_id = '54bcf339a0eeb309';

It should use allcaller_allcaller_logs index but it is using index 4 (which is not even matching the order of conditions in where clause ). can somebody tell me why it is happening.

Explain result for this query:


texts:
Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the parent_id column this is a good and normalized technique i agree but there is a disadvantage also, it’s slow and inefficient. This is mainly caused by the recursion like for each parent we have to run the query again and again to make a tree

SELECT id FROM `table` WHERE parent_id=something

I have looked at the solutions some might try to do it with any programming language by running query again and again which makes a loads on server , Some have provided the stored procedure but that also involves the recursion.

So my question is can we do it with one database query for the tree(joins or subqueries) if we know the depth or if we don't know if it is possible so how can we get the top most parent(i.e parent_id=0) of any child if it is not possible then why this technique is so famous while it has the flaws or we have another solution for this? . i have added the sql fiddle but it only has the schema

FIDDLE


texts: Getting Information on active Restore in TSQL / SQL Server Management Studio

texts:
On my server a database is currently restoring according to the SQL Server Management Studio

In the history

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'myDB'
ORDER BY restore_date DESC

I can't see any entry of that restore currently running.

Is there any other system-table where I can find out who started the restore and when?


texts: Can I prove my database does not have memory issues?

texts:
(This is a continuation of Do indexes consume memory?)

A fellow database developer believes we are having memory issues.  He has seen increased run times in some standard queries - going from under 10 seconds to about two and a half minutes.  He looked at Task Manager on the server and found high memory usage and now wants to take some of the memory currently allocated to the OS and free it up for SQL Server.

We are on SQL Server 2008, a 64-bit machine, AWE is not enabled, minimum 4096 MB, max 10240 MB.

I found Brent Ozar's A Sysadmin’s Guide to Microsoft SQL Server Memory which indicates Task Manager is not reliable.  I have also found that our Page Life Expectancy is not indicating memory pressure. (Checked via Pinal Dave's query.)

Where else should I look?  What else should I check?  I'd like to report back to the database developer to either confirm his suspicions or prove them incorrect.

EDIT: modified my actual question.  I appreciate, and agree, that these queries are overwhelming more likely slower for reasons other than memory problems.  I am in a situation, however, that I need to prove memory is not the culprit across the board.  That is, proving that a handful of queries are slower for other reasons won't accomplish my task.  I'd like to understand where I can get such information and what metrics I should check.


texts: Postgresql as docker container not starting with initial data from mapped volume

texts:
On my macbook I have postgres running in a docker container and I use a mapped volume to persist the data. This works perfectly locally.

However, when I try to do the same on an Ubuntu server the 'initial' data from the mapped volume is not working. Postgres starts up in an 'empty' initial state.
I can see that the volume mapping is working and I've checked and confirmed the data folder for postgres within the container.
Interestingly the mapping does seem to be working, since an added table and data in that table in the default postgres database IS persistent. Furthermore it is interesting to note that I'm getting an error when I try to create a table in a new database. The new database is persistent as well, but the table cant be saved as there is an error thrown:

  could not open file "base/16384/2611": No such file or directory

This is expected as the folder base/16384 doesn't exist.

To me this seems this is a user/rights issue perhaps or some postgres setting? I tried running the container as root, which didn't help.

Any suggestions?

I'm starting the container with either docker-compose or from the command line using;

docker run --rm --name pg -e POSTGRES_PASSWORD=[password] -d -p 5432:5432 -v /root/docker/volumes/postgres:/var/lib/postgresql/data postgres -c listen_addresses='*'

EDIT:
Did try a bunch of things;

The 'bazooka': I chmod 777 the mapped volume, to no effect
run docker container as user postgres:user (this seems to be the user:group of the mapped files), to no effect
run chown of PGDATA folder on docker container start to set owner to postgres:postgres as this seems to be the user:group that is creating a folder when I create a new database, to no effect. even still can't create a table eventhough the database folder was succesfully created


texts: Simple count id in MySql table is taking to long

texts:
I have to tables with 65.5 Million rows:
1)

CREATE TABLE RawData1 (
  cdasite varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  id int(20) NOT NULL DEFAULT '0',
  timedate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  type int(11) NOT NULL DEFAULT '0',
  status int(11) NOT NULL DEFAULT '0',
  branch_id int(20) DEFAULT NULL,
  branch_idString varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id,cdasite,timedate),
  KEY idx_timedate (timedate,cdasite)
) ENGINE=InnoDB;

2) Same table with partition (call it RawData2)

PARTITION BY RANGE ( TO_DAYS(timedate))
(PARTITION p20140101 VALUES LESS THAN (735599) ENGINE = InnoDB,
 PARTITION p20140401 VALUES LESS THAN (735689) ENGINE = InnoDB,
 .
 .
 PARTITION p20201001 VALUES LESS THAN (738064) ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

I'm using the same query:

SELECT count(id) FROM RawData1
where timedate BETWEEN DATE_FORMAT(date_sub(now(),INTERVAL 2 YEAR),'%Y-%m-01') AND now();

2 problems: 1. why the partitioned table runs longer then the regular table? 2. the regular table returns 36380217 in 17.094 Sec. is it normal, all R&D leaders think it is not fast enough, it need to return in ~2 Sec.

What do I need to check / do / change ? Is it realistic to scan 35732495 rows and retrieve 36380217 in less then 3-4 sec?


texts:
I had a few questions around deployment best practices and how folks are using mongodb today. Feel free to add any other comments you can think off.

No of databases per mongod instance. Is the recommendation to create multiple databases in a single mongod instance or to run many mongod's on the same box for the different DBs. Also how are folks running doing this for dev and prod envs?
Ideal number of shards per cluster. How does one decide the ideal number?
Recommended replicaset. 3 or more and how does one decide?
Best way to backup and restore. Is it to use mongodump or is something else? Also do you usually do it on the cluster/instance level vs database level?
Cloning of databases or clusters. Does anyone actively clone and do you clone a db or the whole cluster/instance?


texts: Pervasive linked server provider missing

texts:
I am having the same problem as this question.

I installed Pervasive v11 (which does have 64bit ODBC drivers) and I still do not see Pervasive listed as a provider when trying to create a linked server in SQL Server Express 2008 R2. Does anyone have any ideas what to try?


texts: Can I create a DOMAIN on a range of years

texts:
I have to create a custom domain that is an interval between two years, but i actually need both years.
For example:

'2013/2014'
'2015/2016'

Is there a way to pack both years inside a single domain, or have i to separate them in two attributes of the relation?


texts: Is there a difference between RANGE and LIST method for partitioning by consecutive integer values?

texts:
I have a table with a year column that has consecutive values -- 2000, 2001, and so on. It's a read-heavy MyISAM table, with 25+ million rows for every value of year and a comparable set inserted in bulk approximately annually.

Most of our queries include a WHERE year = N condition. In a few cases we do a self-join ON T1.year = T2.year - 1 or similar. So I'd like to explore partitioning by the year to see if we get any performance benefit. But since I don't store a full date, it's not exactly a range, and since I want on year per partition, it's not exactly a list.

So, assuming that I have a table definition that starts like this:

CREATE TABLE foo (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, year SMALLINT UNSIGNED NOT NULL
, ...
) ENGINE=MyISAM

(Note: I'm leaving the bad PRIMARY KEY in this example so the answer below that addresses it will still make sense - but it's tangential to the real question, and not a problem for my actual schema. For the purposes of answering, please assume PRIMARY KEY (id, year) is defined instead.)

Assuming every value of year that might end up in the table is accounted for in one explicit partition (no years < 2001 and no LESS THAN MAXVALUE partition for the RANGE case), would the following definitions be equivalent? If not, what's the difference?

-- Definition 1

PARTITION BY RANGE (year) (
  PARTITION p0 VALUES LESS THAN (2001)
, PARTITION p1 VALUES LESS THAN (2002)
, ...
);

-- Definition 2

PARTITION BY LIST (year) (
  PARTITION p0 VALUES IN (2000)
, PARTITION p1 VALUES IN (2001)
, ...
);


texts: Running out of Transaction Log space during Alter Table

texts:
I'm running an alter table, alter column on a table with nearly 30 million rows and SQL Azure fails after approximately 18 minutes saying that The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

I'm guessing that it's not possible to break this down into modifying fewer rows at a time, so I'm wondering what my options are for making this change to the database. SQL Azure will not let me change the size of the transaction log (limited to 1gb).

I'm guessing that my best bet is going to be creating a new table with the new layout, migrating the data across into that one, deleting the original table and then renaming the new table to match the name of the old table. If that's the case, how is it best to structure those commands?

Scheduled downtime for our system is not an issue currently so this operation can take as long as it needs to.


texts: Oracle 11g PGA advice view is empty when it shouldn't

texts:
I have the following situation in an Oracle 11g server (it's part of a RAC). PGA_AGGREGATE_TARGET is set, STATISTICS_LEVEL is TYPICAL, and yet V$PGA_TARGET_ADVICE shows up empty. Why does this happen?

Also, the 'maximum PGA allocated' parameter, being more than PGA_AGGREGATE_TARGET, seems to indicate there's room for tuning. Correct?

SQL> show parameter STATISTICS_LEVEL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> select * from V$PGA_TARGET_ADVICE;

no rows selected

SQL> show parameter PGA_AGGREGATE_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 23G

SQL> select * from V$PGASTAT where name='maximum PGA allocated';

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
maximum PGA allocated                                            3.6542E+10 bytes

SQL>


texts: What happens if there are too many inserts in MongoDB? How to ensure all data is stored?

texts:
I use MongoDB to store periodically measured values. Every ~100 ms a bunch of values is inserted as document. It works fine, but I'm worried about performance issues. (I use safe inserts, it seems as if in PyMongo this is the default.)

What happens if there are more inserts per second than mongod is able to save to the hard disk? Will there be any warning or will it simply fail silently?

Is there any method to monitor write load? I've found only db.serverStatus().writeBacksQueued which is always set to false when I call it. How could I test how much data I have to insert to fill up the write queue?

mongostat displays locks. Is this something I should be worried about?

insert  query update delete getmore command flushes mapped  vsize    res faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn repl       time
  *117     *0     *0     *0       0     2|0       0  17.4g  35.3g  3.76g      0     .:6.5%          0       0|0     0|0   124b     6k     2  SLV   09:58:10
  *111     *0     *0     *0       0     2|0       0  17.4g  35.3g  3.76g      0     .:0.8%          0       0|0     0|0   124b     6k     2  SLV   09:58:11
  *111     *0     *0     *0       0     2|0       0  17.4g  35.3g  3.76g      0     .:4.2%          0       0|0     0|0   124b     6k     2  SLV   09:58:1

Do I have to worry about write locks? What happens to an insert during a write locked time period? Is it queued and stored later on?

I am thinking about a simple replication setup using one master and one slave. Does the initial sync or a resync process lock the databases?

(I'm using version 2.4.3.)

Update:
I think have partly answered my own question. I managed to get up to 12.000 inserts per second using a simple while loop inserting a small test document. But qr|qw still shows that there are the read- and write queue is still empty:

insert  query update delete getmore command flushes mapped  vsize    res faults       locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn repl       time
 11234     *0      2     *0    1563     1|0       1  21.9g  44.3g  1.22g      0    testdb:58.9%          0       1|0     1|1   797k   980k     6  PRI   10:26:32
 12768     *0      2     *0    1284     1|0       0  21.9g  44.3g  1.22g      0    testdb:58.0%          0       0|0     0|1   881k     1m     6  PRI   10:26:33
 12839     *0      2     *0    1231     1|0       0  21.9g  44.3g  1.22g      0    testdb:60.3%          0       0|0     0|1   883k     1m     6  PRI   10:26:34
 12701     *0      2     *0     910     1|0       0  21.9g  44.3g  1.22g      0    testdb:61.8%          0       0|0     0|1   858k     1m     6  PRI   10:26:35
 12241     *0      2     *0    1206     1|0       0  21.9g  44.3g  1.22g      0    testdb:56.7%          0       0|0     0|0   843k     1m     6  PRI   10:26:36
 11581     *0      2     *0    1406     1|0       0  21.9g  44.3g  1.22g      0    testdb:61.8%          0       0|0     0|1   811k     1m     6  PRI   10:26:37
  8719     *0      2     *0    1210     1|0       0  21.9g  44.3g  1.22g      0    testdb:43.8%          0       0|0     0|1   618k   762k     6  PRI   10:26:38
 11429     *0      2     *0    1469     1|0       0  21.9g  44.3g  1.22g      0    testdb:60.6%          0       0|0     0|1   804k   993k     6  PRI   10:26:39
 12779     *0      2     *0    1092     1|0       0  21.9g  44.3g  1.22g      0    testdb:60.2%          0       1|0     0|1   872k     1m     6  PRI   10:26:40
 12757     *0      2     *0     436     1|0       0  21.9g  44.3g  1.22g      0    testdb:59.7%          0       0|0     0|1   838k   432k     6  PRI   10:26:41

I suppose this means that inserts alone won't cause a lot of troubles:
"Queues will tend to spike if you’re doing a lot of write operations alongside other write heavy ops, such as large ranged removes." (found here]

My open question: What happens to my data if the write queue increases on long term?