texts: mysql ssl replication - fails while attempting to connect to master

texts:
I'm trying to troubleshoot why my slave cannot connect to the master.
This is what I see in the output from the command "show slave status \G"

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.1.1.1
                  Master_User: test_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/ssl/ca-self-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/ssl/server-self-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/ssl/server-self-key.pem
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1129
                Last_IO_Error: error connecting to master 'test_user@10.1.1.1:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)

What I've Checked:

Made sure port 3306 is open on master:

test-dev:/etc/ssl# netstat -lnp | grep 3306
tcp        0      0 10.1.1.1:3306       0.0.0.0:*               LISTEN      -
test-dev:/etc/ssl#

Made sure firewall is open from slave machine:

test_dev_2:/etc/ssl# nc -vzw 3 10.1.1.1 3306
10.1.1.1 (10.1.1.1:3306) open
test_dev_2:/etc/ssl#

Checked permissions on the certs on both master and slave:

 master:

-rw-r--r--    1 root     root       10.4K Nov 11  2014 server-cert.pem
-rw-r--r--    1 root     root        1.9K Nov 11  2014 server-key.pem
-rw-r--r--    1 root     root        1.3K Dec  2 15:45 server-self-cert.pem
-rw-r--r--    1 root     root        1.7K Dec  2 15:45 server-self-key.pem
-rw-r--r--    1 root     root         948 Nov 25 13:14 server-self-req.pem
slave:

-rw-r--r--    1 root     root       10.4K Nov 11  2014 server-cert.pem
-rw-r--r--    1 root     root        1.9K Nov 11  2014 server-key.pem
-rw-r--r--    1 root     root        1.3K Nov 20 12:06 server-self-cert.pem
-rw-r--r--    1 root     root        1.7K Nov 20 12:05 server-self-key.pem
-rw-r--r--    1 root     root         964 Nov 20 12:05 server-self-req.pem

Made sure ssl is enabled on the server.

 mysql> show variables like "%ssl%";
 +---------------+-------------------------------+
 | Variable_name | Value                         |
 +---------------+-------------------------------+
 | have_openssl  | YES                           |
 | have_ssl      | YES                           |
 | ssl_ca        | /etc/ssl/ca-self-cert.pem     |
 | ssl_capath    |                               |
 | ssl_cert      | /etc/ssl/server-self-cert.pem |
 | ssl_cipher    |                               |
 | ssl_key       | /etc/ssl/server-self-key.pem  |
 +---------------+-------------------------------+
 7 rows in set (0.00 sec)

 mysql>

Finally, I ran the command

 "openssl x509 -in server-self-cert.pem -text -noout"

on both the master and the slave and it looked like the certs were okay.
But I'm no expert and am just learning about certs.

I'm not too sure what else I should check / try. I'm a programmer ... not dba or even sys admin so .. I've been running out of ideas.


texts: Unify specific tables in one product table

texts:
Today my database has the tables in this first image. The problem is that now I need to do stock control and other products will be added too.

To support many types of products, first I designed the tables in the next image (design 1). This will be a solution to generic products that don't have any kind of serial number to differentiate they.

After doing some research I found the solution in the next image (design 2). This supports generic and the specific products (like SIM cards and devices). I think that the complexity will increase greatly this way. Also there is a problem that I have FKs in simcards and devices. One great thing about this solution is that will support future products with specific data.

What is the best approach to solve this problem?


texts: Inconsistency in Tables - Mariadb Galera Cluster

texts:
MariaDB cluster Version : mysql Ver 15.1 Distrib 10.0.24-MariaDB,

I have a two node + arbitrator cluster which is live and replicating data across nodes.

Unfortunately we found some inconsistency in some of the tables in Databases.

For instance:

Node1:

MariaDB [(none)]> select count(*) from example_db.reports;
+----------+
| count(*) |
+----------+
|      299 |
+----------+
1 row in set (0.00 sec)

Node2:

MariaDB [(none)]> select count(*) from example_db.reports;
+----------+
| count(*) |
+----------+
|      285 |
+----------+
1 row in set (0.00 sec)

Note: Not find any noticeable errors in mysql error log

What could be the reasons for these kind of inconsistency ?

Is that a bug or a known issues ?

Thank you.


texts: problem with service broker in always-on

texts:
one of my clients has a database that works with service broker, last week we tried to move the database to an always on environment without knowing the application uses the service broker.
after there were complaints that it didn't work(scheduled meetings were not added to the calendar and did not close when old ones were done (its a clinic application)) we found Microsoft reference to service broker with always on and tried to use it:
 https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/service-broker-with-always-on-availability-groups-sql-server?view=sql-server-2017

but it still didnt work.
at first we tried

alter database [databasename] set enable_broker with rollback immediate

alter database [databasename] set new_broker with rollback immediate

and we received an error

  Msg 1468, Level 16, State 1, Line 2 The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. Msg 5069, Level 16, State 1

we also tried these commands:

 CREATE ENDPOINT [SSBEndpoint]

    STATE = STARTED

    AS TCP  (LISTENER_PORT = 4022, LISTENER_IP = ALL )

    FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)

     GRANT CONNECT ON ENDPOINT::[SSBEndpoint] TO [PUBLIC]

ALTER ROUTE AutoCreatedLocal

   WITH ADDRESS = 'TCP://[server]:4022'  ;

at the end we didn't have a choice and we took it out of the always on group and after executing this command it worked:

   ALTER ROUTE AutoCreatedLocal
   WITH ADDRESS = 'LOCAL'  ;

does anyone have advise of what we are missing here on the configuration?
thanks.


texts: SQL Server Database Tuning Advisor - Temp index being dropped

texts:
Recently I ran a trace on our database so I could then plug it into Database Engine Tuning Advisor and see what queries needed to be improved and where there might be a real need for indexes.

While running the DTA, it had created an index before the end (I assume as a test) and then it deleted the index.  However, because this was all on Live, a query had wanted to use that index and when it wasn't there anymore, it locked up the database.

My general questions...

Is my assumption correct?  That DTA created the index?
Was it created and dropped (before I clicked "apply recomendations") as a method of testing performance and increase and such?
I thought that DTA only used "Hypothetical Indexes"....is there a field/flag for that?
Am I using DTA correctly in this manner?  What are the best practices for using this tool?
What really could have cause this lock-up?  It seems so odd.


texts: What kind of deadlock the deadlock_timeout config parameter detects in Postgresql?

texts:
My overall goal is to get a 40P01/ERRCODE_T_R_DEADLOCK_DETECTED from postgres when an deadlock is detected. (To be more precise, I would like to get a LockAcquisitionException in Hibernate, which is the exception the 40P01/ERRCODE_T_R_DEADLOCK_DETECTED error code is translated in PostgreSQL81Dialect.java) I use Postgresql 9.6.

For this I thought I should set the deadlock_timeout and log_lock_waits config variables
as suggested by 19.12. Lock Management

  deadlock_timeout (integer) This is the amount of time, in
  milliseconds, to wait on a lock before checking to see if there is a
  deadlock condition.
  [...]
  When log_lock_waits is set, this parameter also determines the length
  of time to wait before a log message is issued about the lock wait. If
  you are trying to investigate locking delays you might want to set a
  shorter than normal deadlock_timeout.

I've set the following values in postgresql.conf

log_lock_waits = on         # log lock waits >= deadlock_timeout
deadlock_timeout = 5s

Now, when I create a deadlock situtation (from Java using Hibernate) I find the following in the postgresql.log

LOG:  process 17493 still waiting for ShareLock on transaction 322815 after 5000.464 ms
DETAIL:  Process holding the lock: 17495. Wait queue: 17493.
CONTEXT:  while updating tuple (132,35) in relation "publication"

However no 40P01/ERRCODE_T_R_DEADLOCK_DETECTED error is generated (and sent to the JDBC driver).

I digged a little but into the postgres source code and found that the dead lock detection done by setting deadlock_timeout/log_lock_waits is a different mechanism than the one generating 40P01/ERRCODE_T_R_DEADLOCK_DETECTED. The deadlock_timeout case is handled in  backend/storage/lmgr/proc.c
, while the 40P01/ERRCODE_T_R_DEADLOCK_DETECTED case in backend/storage/lmgr/deadlock.c

So, my questions are:

Are these actually two different types of deadlocks that are detected?
Is there a way to get an error when deadlock_timeout based deadlock detection happens?
How can actually a ERRCODE_T_R_DEADLOCK_DETECTED error be forced to happen?

UPDATE: the code I use to get into the deadlock situation goes like this (Spring/Java):

// This is in a Transaction managed by spring
Publication p = em.find(Publication.class, id);
p.setComment("Outer "+new Date());
em.flush();

// This utility method runs the lambda expression in a new Transaction
// using Spring's TransactionTemplate and tries to update
// the same Publication that is about to be updated by the
// "outer" transaction
Utils.runInSeparateTransaction(status -> {
    Publication p2 = em.find(p.getClass(), p.getMtid());
    p2.setComment("Inner "+new Date());
    return p2;
    // Would flush/commit after the return, but will "hang" instead.
    // Here I would expect the deadlock error but only get the
    // deadlock log.
};

// Outer transaction would commit at this point but will


texts: MariaDB 10.1.16 on Ubuntu fails to start: WSREP Failed to recover position

texts:
I'm using 10.1.16-MariaDB-1~xenial from the official MariaDB apt repository for 10.1 [stable], via the University of Texas mirror.

I had a perfectly functioning MariaDB Galera cluster setup on 3 Ubuntu 16.04 servers.

Then I upgraded them. Now I have nothing.

The upgrade to 10.1.16 failed, and quickly brought down the whole cluster. I don't have the output, but dpkg failed on setting up mariadb-server and mariadb-server-10.1.

I have backups, so I purged all traces of MariaDB/MySQL/Galera from my servers (including removing /var/lib/mysql/, /etc/mysql/, and /var/log/mysql/) and started over. However, now, with a clean install on each server, none of the standard system startup scripts work. I suspect this is why the upgrade process through apt failed, too.

I've tried each of the following on my first node:

galera_new_cluster
service mysql bootstrap
service mysql bootstrap --wsrep-new-cluster
service mysql bootstrap --wsrep-cluster-address="gcomm://"
service mysql start
service mysql start --wsrep-new-cluster
service mysql start --wsrep-cluster-address="gcomm://"
systemctl start mariadb
systemctl start mariadb --wsrep-new-cluster
systemctl start mariadb --wsrep-cluster-address="gcomm://"

Every single one gives me the same output:

Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

systemctl status mariadb.service:

● mariadb.service - MariaDB database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: failed (Result: exit-code) since Fri 2016-07-22 13:29:45 CDT; 42s ago
  Process: 10799 ExecStartPre=/bin/sh -c VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] &&   systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=1/FAILURE)
  Process: 10794 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 16865 (code=exited, status=0/SUCCESS)

Jul 22 13:29:41 sql2 systemd[1]: Starting MariaDB database server...
Jul 22 13:29:45 sql2 mysqld[10799]: WSREP: Failed to recover position: '2016-07-22 13:29:41 140110745778432 [Note] /usr/sbin/mysqld (mysqld 10.1.16-MariaDB-1~xenial) starting as process 11080 ...'
Jul 22 13:29:45 sql2 systemd[1]: mariadb.service: Control process exited, code=exited status=1
Jul 22 13:29:45 sql2 systemd[1]: Failed to start MariaDB database server.
Jul 22 13:29:45 sql2 systemd[1]: mariadb.service: Unit entered failed state.
Jul 22 13:29:45 sql2 systemd[1]: mariadb.service: Failed with result 'exit-code'.

The only way I can start my servers now is by manually executing:

sudo -u mysql mysqld --wsrep-cluster-address='gcomm://'

On the first node, and then:

sudo -u mysql mysqld --wsrep-cluster-address='gcomm://ip1,ip2,ip3'

On the other two nodes. That works, and I have a working cluster again. But now, systemd/systemctl have no idea the service is running. It seems like the systemd startup scripts can't use the wsrep-cluster-address setting in my configuration files at all. Specifying it to service or systemctl command line does not work either.

How am I supposed to start mariadb?


texts: Does MySQL allows incoming queries while altering a table?

texts:
I'm adding a new column to an existing table say ABC with the following query,

  ALTER TABLE ABC add column STATUS int(10) NOT NULL

This is done in master, slave and secondary slaves. Meanwhile, while altering the existing table (which does not contain any data) - other databases (other schemas and tables) were queried - both in master and slave databases.

After altering column in the table ABC has been completed I could see slowness in many app servers and few got killed stating "You have reached your max limit to DB Connections" and you could make no more DB connections and the app server got killed.

I also run a jsp in one of my servers - the thread gets hanged (stopped after making connection to one of secondary slave) in the mean time. I'm a no DB expert. I'm a novice MySQL learner and do not have any prior experience I do not know how to debug further.

I suspect whether my MySQL servers (either master or slave) does not accept any incoming connections while altering a table and once the table alteration gets completed it accepts the queued up requests and process them one by one.

Since the connections got queued up the app servers - which are waiting for previous query response - couldn't make any more requests. Can someone shed some insights to debug

on db connection getting exhausted and
the thread which invoked a jsp never ends (stops where it makes connection to mysql) - this happened during the timestamp when alter table happened.


texts:
I'm trying to set up a test environment that simulates (very roughly) having three servers. I'm doing this by creating 3 databases on my local instance of SQL which I'll call [A], [B], and [C]. Why I can't simply set up three test instances, is a story for another time. I've got a bunch of code files I want to execute using SQLCMD, which is no problem. I've got a parent SQLCMD file that looks like

:Setvar db gt
:Setvar filepath "C:\Documents\test\"
:r "C:\Documents\test\master.sql"

then another file (master.sql) with a few statements like create database $(db) and so on.

The problem now however is in the actual code files, the database referenced is [Alerts] on the actual server, but in my text harness the database has to be [A], [B] or [C] (since I'm using the database names to simulate the server).

Imagine I also have a file MyTable.sql which looks roughly like this:

create table [alerts].[dbo].[MyTable]
(
    id int identity(1,1)
)

In pseudocode, I'd like to do something like this:

(:r $(filepath)MyTable.sql).Replace('[Alerts]', '$(db)')

Does anyone know if this is possible?


texts: Surrogate vs composite key in hierarchical data structure

texts:
I'm currently evaluating a schema for a hierarchical data structure. My main problem is how I should design the schema to prevent inconsistent data (reference of foreign key in another hierarchy). The two variants I discovered are either to use a composite or a surrogate key.

Requirements

Strict consistency checking per hierarchy, e.g. a Part in Hierarchy A cannot have a foreign key on a Type in Hierarchy B (see example schemas below).
The constraints have to be checked in the database and not with application code, as the database is used from different applications.
Scalable for performance and extendable with more hierarchy levels.

Variant 1: Composite Key

In this example, the foreign keys are part of the composite primary key. With this, it is automatically checked that the Type of the Part are assigned with the same Item (same hierarchy), because they are part of the composite primary key.

This approach seems troublesome to me, as it is not very easy to extend the model. For example, if I decide to create a PartMetadata table, which holds metadata for a single Part, I have to include the whole composite key, even though the metadata has no connection to the hierarchy.

Item
  ItemId (PK)
  Name

Type
  TypeId (PK)
  ItemId (PK, FK)
  Name

SubItem
  SubItemId (PK)
  ItemId (PK, FK)
  Name

Part
  PartId (PK)
  ItemId (PK, FK)
  SubItemId (PK, FK)
  TypeId (PK, FK)
  Name

Variant 2: Surrogate Key

In this case I would have to define additional constraints or triggers to ensure that it is not possible to insert a Part with a Type that belongs to a different Item.

Item
  ItemId (PK)
  Name

Type
  TypeId (PK)
  ItemId (FK)
  Name

SubItem
  SubItemId (PK)
  ItemId (FK)
  Name

Part
  PartId (PK)
  SubItemId (FK)
  TypeId (FK)
  Name

Questions

Should the surrogate solution always be favored? In what cases is it useful to use the composite key solution?
Which solution is better maintainable in the long term, especially if the hierarchy gets bigger?
Which solution offers better performance (reporting, CRUD)? Does the surrogate solution have a huge impact because of check constraints or triggers?
Are there other options (in my opinion, both my variants seem like non-ideal solutions)?
Has someone experience with both variants and can share his wisdom?


texts: When to make changes to the cost threshold for parallelism

texts:
While examining a performance issue , I have seen an influx on CXPACKETS suggesting I might need to look at the cost threshold for parallelism and perhaps the MAXDOP.

Before making any drastic changes to the MAXDOP I have following the advice of many others including that of @mrdenny in the answer to CXPACKET Waits performance tune for SQL Server 2008 and @aron-Bertrand 's answer from Dealing with CXPACKET waits - setting cost threshold for parallelism. I have added to the maintenance to update the statistics fully on a nightly basis. This feels like a sensible move.

However, making modifications to the cost threshold is still something which niggles me.

At what point should the cost threshold for parallelism be altered? Does any one have an example of where (after examining the cost of their queries and workload) they made change to this cost?

Apologizes if this is something which which has been answered in a previous question.

Thanks!


texts: Azure SQL Databases hit cpu limits too quickly

texts:
Background in a nutshell: -
We have a SAAS solution with the following main components.
1. We have a web-portal back-end where administrators can edit data.
2. We have a web API that is called by mobile devices. The mobile devices track or report on students reading progress

Up to now the solution was hosted on virtual servers.
Now we are migrating the solution to the Azure framework so that we can take advantage of the scalability of elastic database pools.
We are using event topics to handle large volumes of posts from the mobile devices when the posts can be processed asynchronously,
but there are some posts that need to be processed synchronously, and we are finding Azure's fabric really slow when it comes to multiple concurrent connections.

An example of the issue: -
So when Azure runs a query like the following: -

SELECT q.Category, COUNT(*)
FROM Question q
JOIN Answer a
ON a.QuestionId = q.QuestionId
GROUP BY q.Category
ORDER BY q.Category

The SQL CPU peaks above 97% in all the following scenarios: -
1. The DTU's are 50 and there is more than one concurrent call.
2. The DTU's are 1500 and there are 5 or more concurrent calls.
3. The DTU's are 4000 and there are 20 or more concurrent calls.

So we opened a support call with Microsoft.
We spent more than a week of investigating things from sql statistics and indexes up to web api pricing tiers.
After all that we still came up with the evidence that the CPU was peaking in the SQL database with the scenarios as outlined above.

This leads to the inevitable "re-write large chunks of your system" kind of argument.

So the underlying issue is that elastic database pools can't seem to perform anywhere near the ability of standard SQL databases.
Also, the stand-alone database's performance doesn't seem to compete with the virtual server's performance.

This is so frustrating because Elastic databases pools were recommended for us for reasons of maintaining performance and adding scalability.
We currently run 700+ customers on one virtual server; and were expecting to create one shard database per customer.
The idea being that we could then scale up from hundreds of customers to tens of thousands of customers.
In reality we are fighting to get the Azure fabric to perform anywhere near the kind of performance we have on virtual servers.
So this question is to ask if there is anyone with significant experience in making Azure perform non-trivial tasks at a reasonable pace?
(preferably without having to re-write large chunks of the system)


texts: PostgreSQL JSONB query on comparison operation with Index

texts:
Considering the below query,
select
    *
from
    sub_test df,
    jsonb_array_elements(json_data) jba
where
    (jba->>'name')::text = 'SubYear'
    and (jba->>'value')::numeric > '2050';

There are about 500+ keys such as "name" as mentioned in the above query in the json_data JSONB type column. I would need to make a comparison against a specific year. Should individual index be created for all the available keys or how to handle such queries to make use of index scan?
Kindly help to clarify.


texts: Oracle Database authentication through Active Directory

texts:
We have Oracle 11g database running on a RHEL 6 server.
and we have a Active Directory domain running on windows 2008 server in same network.
I want to authenticate Oracle database users using this AD.
I searched but found only documents related to kerberos authentication where the database clients directly authenticate with AD and then contact Oracle database.
I cant use this setup as we don't have any network connection between client softwares like Toad and Active directory. The client softwares run in a different network and not connected to this windows domain.  These clients can only connect to Oracle database.
I want the following setup

User will enter domain user name and password in the client software
The Oracle database server has to pass these details to AD and try to authenticate
Based on AD response, oracle has to allow or block the user

Please let me know if this is possible with my environment.

Note: the RHEL server on which Oracle is installed is joined to this Widows Domain using SSSD and this OS authentication n works without issues


texts: Should I create a nonclustered index on all searchable columns or just each column separately?

texts:
I am not sure as to properly use non-clustered indexes. The SSMS plan estimator said to add another index when the following index was on the table already.

    CREATE NONCLUSTERED INDEX [ix_zone_fetch_shipping] ON [dbo].[tbl_shipping_rates_zones]
    (
        [iso] ASC, [mzone] ASC, [postal] ASC
    )
    INCLUDE
    (
        [region], [zone_dom], [zone_emi], [zone_pmi], [zone_fci],
        [zone_ups], [zone_fed]
    )

My query was

SELECT * FROM tbl_shipping_rates_zones WHERE postal = '10001'

My question is... should I be creating an index for all possible look up keys? I search by mzone, iso, and postal in different queries.

Thanks


texts: Hosting postgres wal logs on a separate dedicated server with failover setup

texts:
a postgres novice here, so sorry if my questions don't sound meaningful. My postgres server setup goes as follows:

Master(on server A), Upstream(on B, receiving wal from A), Downstream(on C, receiving wal from B). A is streaming wal logs on a few physical_replication_slots. All the other servers are created from pg_basebackup of A.

Now, I've another server X, dedicated to WAL storage. Whatever be the primary at the moment, its wal logs will be dumped on X inside directory(say /root/wal_logs). There's a pg_receivewal(on server X) process receiving this wal from Master and saving it in the same directory.

Under failover scenarios, slaves are promoted to master, and others act like slaves. So, I wanted to know,

How the logs on server X will be affected if masters keep on switching ? Can a new master server create a wal conflicting with the old server's wal ?
What if I create a new standby( D ) from pg_basebackup of A, and try recovering it from these logs ? There was some Timeline kinda concept(which I couldn't grasp much) in postgres wals. Can it have effect on recovery of D ?
And is it possible, for a Master demoted to standby, recover from these logs ? Or will I need pg_rewind for this?
Otherwise, what's the recommended way to do it ?


texts: Best practices for connecting DBs that are in different geographical regions

texts:
We are about to set up SQL Servers in different countries. We need to link them, but we don't have to have a direct link (as in linked server). In other words, they can be loosely coupled.

Is it better to connect them via a VPN and have them as linked servers, or use loose coupling via web services?

By "better", I am referring to stability.


texts: Trying to find a way fit an extremely large index in to memory

texts:
Description:

I am having trouble with the on-growing data size in my MySQL database.

I am using Ejabberd and MAM function which will make use of an archive table to store messages sent between users and the table keeps growing. It now takes over 10 seconds to query something like

SELECT COUNT(*) FROM archive
    WHERE username = '<some_id>'
      and bare_peer = '<some_string>'
      and timestamp >= '<some_timestamp_in_microseconds'

or

SELECT timestamp, XML, peer, kind, nick FROM archive
    WHERE username = '<some_id>'
      and bare_peer = '<some_string>'
      and timestamp >= '<some_timestamp_in_microseconds>'
      and timestamp <= '<some_timestamp_in_microseconds>'

These are very common SQL that would execute thousands of times each day, and since the SQL are executed from within Ejabberd, I cannot change the syntax.

Current Situation:

Instance Specification: 8 core CPU, 64 GB RAM
innodb_buffer_pool_size: 49392123904 bytes (roughly around 49GB)
With references to this post, I got the result of 1005383M (roughly 1TB) estimated requirement of memory size.
The archive table size: 700GB of data, and ~200GB of index, around 0.9b of rows (yes, a lot of rows)
Here is the table creation SQL:

CREATE TABLE `archive` (
  `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `timestamp` bigint(20) unsigned NOT NULL,
  `peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bare_peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `xml` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `txt` text COLLATE utf8mb4_unicode_ci,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `kind` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nick` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`id`),
  KEY `i_username` (`username`) USING BTREE,
  KEY `i_timestamp` (`timestamp`) USING BTREE,
  KEY `i_peer` (`peer`) USING BTREE,
  KEY `i_bare_peer` (`bare_peer`) USING BTREE,
  FULLTEXT KEY `i_text` (`txt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Discussion:

With the below information, one thing I could think of is to:

Partition the archive table with Primary Key (RANGE / Per 5m of rows), but from my understanding, since MySQL doesn't support fulltext index in Partitions, I would be required to drop the fulltext index in the txt column, which I think is ok.
Unfortunately, since MySQL could only partition on Primary Keys, and I cannot change the SQL. I therefore cannot utilize the partition directly on the SQL. What I could do is to drop the entire partition regularly and keep the remaining index size to fit into memory as much as possible.

I am posting to seek for a second opinion on whether:

Is this the best way I could do with the above limitations?
If so, How can I partition such a big table without downtime, by using possibility pt-online-schema-change ?

Thank you all for your time.


texts: MySQL next entry after certain condition


texts: Force SQL Server to run query conditions as written?

texts:
I'm using SQL Server 2008 R2 and I have this pseudo query (SP) :

select ...
from ...
WHERE    @LinkMode IS NULL
     AND (myColumn IN (...very long-running query...))
     ...
     ...

The problem is that the query takes a very long time to execute -- even if I execute the SP with @LinkMode=2.

As you noticed, the long-running query should be executed only if @LinkMode  is null, which is not the case here. In my case @LinkMode = 2 !

However, if I change it to  :

 select ...
    from ...
    WHERE    1=2
         AND (myColumn IN (...very long time exeted query...))
     ...
     ...

the SP does run fast.

I've heard before that sometimes the optimizer can optimize the order of criteria.

So I ask :

Even if the optimizer chooses a different route, what can be faster than checking if =null? I mean, I think that checking if a==null is much faster than running the other long query...
How can I force SQL Server to run the query as I've written it (the same order)?


texts:
How come MySQL Server software is bigger (in size) than SQLite? What makes it so big? Isn't it just about some network protocols and code that comes to save the tables and its entries?

SQLite, with the same language as MySQL, comes with only a 300kb library, but the MySQL Server is around 600mb in size.


texts: Error when creating a table in MySQL with a Foreign Key Constraint

texts:
CREATE TABLE OFFICES
 (OFFICE INTEGER NOT NULL,
 CITY VARCHAR(15) NOT NULL,
 REGION VARCHAR(10) NOT NULL,
 MGR INTEGER,
 TARGET DECIMAL(9,2),
 SALES DECIMAL(9,2) NOT NULL,
 PRIMARY KEY (OFFICE),
 FOREIGN KEY HASMGR (MGR)
 REFERENCES SALESREPS
 ON DELETE SET NULL);

Prouduces the following error:

#1239 - Incorrect foreign key definition for 'foreign key without name':
Key reference and table reference don't match


texts: Combine different index types in postgres

texts:
we have query for fulltext where we have also another condition. Can you tell me what is correct usage of multiple indexes of different type?

for instance:

select * from foo_tbl foo
where foo.bar_tsv @@ to_tsquery('simple', 'xxx & yyy')
and foo.reg_date is null
and foo.is_active = true;

I have created index like this:

CREATE INDEX foo_fts_idx ON foo_tbl
USING GIN(bar_tsv);

and

CREATE INDEX foo_active_idx ON foo_tbl(is_active);
CREATE INDEX foo_reg_date_idx ON foo_tbl(reg_date);

when I execute query plan with EXPLAIN command I find out there is hit only full text index, can you tell me how to fix it? Thank you.

EDIT:

Output of EXPLAIN (ANALYZE, BUFFERS):

"Bitmap Heap Scan on foo_tbl foo (cost=44.55..323.29 rows=71 width=2963) (actual time=0.475..0.497 rows=3 loops=1)"
"  Recheck Cond: (foo_tsv @@ '''xxx '':* & ''yyy'''::tsquery)"
"  Filter: ((reg_date IS NULL) AND is_active)"
"  Heap Blocks: exact=3"
"  Buffers: shared hit=24 read=4"
"  I/O Timings: read=0.089"
"  ->  Bitmap Index Scan on foo_fts_idx   (cost=0.00..44.53 rows=71 width=0) (actual time=0.412..0.412 rows=3 loops=1)"
"        Index Cond: (foo_tsv @@ '''xxx'':* & ''yyy'''::tsquery)"
"        Buffers: shared hit=24 read=1"
"        I/O Timings: read=0.014"
"Planning time: 4.977 ms"
"Execution time: 0.578 ms"

There is not used index for is_active nor reg_date.


texts: One Non-Clustered Index that covers every possible combination of predicates?

texts:
Say we have a table against which a number of different queries can be executed and each query has its own combination of predicates.

So, for example, if we have a table with columns A, B, C, etc we may have a query that filters on columns A and B but not C, a second query that filters on A and C but not B, and so on.

Does it make sense to have a non-clustered index that has as its key columns every column in the table (or tables) that can be a predicate?

To my mind, I'd have thought it is better to have a non-clustered index for each query with the predicates for that query as the key columns.

Any advice on this would be great!


texts: Running a complex query for every date in a range

texts:
I've got a table of orders

   Column   |            Type             |                      Modifiers
------------+-----------------------------+-----------------------------------------------------
 id         | integer                     | not null default nextval('orders_id_seq'::regclass)
 client_id  | integer                     | not null
 start_date | date                        | not null
 end_date   | date                        |
 order_type | character varying           | not null

The data has non overlapping standing orders for a client_id and occasionally a temporary order that overrides the standing order on it's start_date when they have a matching client_id. There is application level constraints keeping orders of the same type from overlapping.

 id | client_id | start_date |  end_date  | order_type
----+-----------+------------+------------+------------
 17 |        11 | 2014-02-05 |            | standing
 18 |        15 | 2014-07-16 | 2015-07-19 | standing
 19 |        16 | 2015-04-01 |            | standing
 20 |        16 | 2015-07-18 | 2015-07-18 | temporary

For example, on 2015-07-18 client 16 has order #20 as it's active order because it overrides the standing order #19. With some fuss I found an efficient way of querying for active order id's on a date.

    SELECT id from (
      SELECT
        id,
        first_value(id) OVER (PARTITION BY client_id ORDER BY order_type DESC) active_order_id
      FROM orders
      WHERE start_date <= ? and (end_date is null OR end_date >= ?)
    ) active_orders
    WHERE id = active_order_id

If you query this with 2015-07-18 as the placeholders, you would get

 id
----
 17
 18
 20

The query plan on this query compared to some of my other ideas (like sub queries counting the number of temporary orders for a client on a date) is quite small and I'm pretty happy with it. (the design of the table, I'm not thrilled about)

Now, I need a to find all the active orders for a date range joined with the dates they are active on. For example, with the date range of 2015-07-18 to 2015-07-19 I would like the following result.

active_date | id
------------+----
 2015-07-18 | 17
 2015-07-18 | 18
 2015-07-18 | 20
 2015-07-19 | 17
 2015-07-19 | 18
 2015-07-19 | 19

Order 20 overrides order 19 on 2015-07-18 but not on 2015-07-19.

I found with generate_series() I can generate a range of dates, but I haven't a clue how to join that with this to get a table of dates and order id's. My hunch is a cross join but I can't figure out how to make that work in this circumstance.

Thanks

UPDATE
Added an sql fiddle.


texts: Check if the value from one table exists in another tables in SQL Server

texts:
I have one table (tbl1) with column ID, the values can be duplicated. I have others tables (tbl2, tbl3...) with column ID , values are unique.
I want to write a trigger on insert row in tbl1 and check if ID in new row has not exists in tbl2,tbl3.... There is part of my code.

    CREATE TRIGGER dbo.tbl1_ID
       ON dbo.tbl1
       AFTER INSERT
    AS
    BEGIN

       SET NOCOUNT ON;
       DECLARE @CHECK int

       SELECT OBJECTID,ID, ROW_NUMBER() over(Order by OBJECTID) as aID into #T1
       from inserted where (ID is not null)

       SELECT @CHECK = COUNT(p.ID from #T1 as p where not exists (select e.ID
                                                                  from dbo.tbl2 as e
                                                                  where p.ID=e.ID))

       IF @CHECK>0
           BEGIN
                RAISERROR("ID ALREADY EXISTS",16,1);
                ROLLBACK TRANSACTION;
           END
    END

I don't know how to check in a range of tables, they are stored in table JoinTables(f.ex.). I suppose I need function here.

Table JoinTables similar to

obID|firsttable|secondtable

 1  |   tbl1   |   tbl2
 2  |   tbl1   |   tbl3
 3  |   tbl1   |   tbl4
 4  |   tblM   |   tbl5
 5  |   tblM   |   tbl6


texts: How do I read Query Cost, and is it always a percentage?

texts:
I'm currently studying for SQL 70-433 (the Microsoft Certification exam), and I'm getting very confused about the "query cost" performance metric.

According to any documentation I could find via Google, the query cost is a percentage figure, and represents the percentage of the whole batch taken up by any one part of it.
This already seemed a little odd to me, as I am interested in the absolute merit of a particular query, rather than its merit relative to other queries which happen to appear alongside it.

But then I thought, well, maybe what you are expected to do is place two alternative queries side by side, run them as "a batch", and then whichever one has a cost less than 50% is the winner.

But the discussion of query cost in chapter 6, lesson one of Microsoft's SQL 70-433 Training Kit doesn't seem to bear any relation to this.

Here is an example: They show a query containing two correlated sub-queries, and then improve on it by replacing the sub-queries with an OUTER APPLY. The result: "This query has a cost of roughly 76, while the first query's cost was double that, about 151."
They then improve the query even further, and reduce the cost from 76 to 3.6. They do not imply these figures are percentages, whereas they do imply that they are absolute figures which relate to the query as a standalone object, without reference to any other queries. And anyway, how could the first query have a cost of 151%?

Later on in the chapter, they show a screenshot of an execution plan which has three parts. The first says "Cost: 0%", the second says "Cost: 1%" and the last says "Cost: 99%" but the text (of the book itself) below the screenshot "The cost of this query is 0.56".
I'm guessing that they mean some other kind of cost, but I can't find reference to it elsewhere.

Can someone help? I'm thoroughly confused.


texts: How to filter on multiple values for a given set of keys using JSONB, and an index

texts:
Suppose we have a table (named rel_1) with an info field of type JSONB. The content of this field is client specific and may be changed every once in a while, but let's assume it to be of the form {"ki":"vi"} / i=0->N.

Now suppose we want to search for specific rows that have k0="foo" AND k1="bar". From the documentation, One can simply use the containment @> operator like so:

SELECT * FROM rel_1 WHERE info @> '{"k0":"foo", "k1":"bar"}';

While being elegant and working perfectly fine, it can also make use of a GIN index. It's also agnostic to what actual data for info might be. As long as the clients use the same keys for writes and reads, the DB side can completely ignore it and still do a fine job at optimizing requests using the index.

The only problem is that this approach fails if we change the requirement a little bit so that multiple values are allowed for a given key: (k0="foo" OR k0="bar") AND k1="baz".

Is there any way to achieve this while retaining the elegance and efficiency of the first solution?


texts:
I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table.

I've read around and found ApexSQL Log but when I connect I try to connect I see "SQL server Azure is not supported"

I understand that this tool examines logs to reproduce the data, is there a manual technique to do is, or an alternative compatible tool?

Really grateful for you support.

Thanks.


texts: Oracle: update table with object type udt

texts:
I have this Oracle code:

Create type address_ty as object( street varchar2(50),city varchar2(50),prov varchar2(50),cap number);

Create type person_ty as object( name varchar2(50), address address_ty);

Create table client (client_id number,person person_ty);

insert into client
values(1,person_ty('pinco pallo',
         address_ty('Boulevard street' ,'torino','to',10100)));
Commit;

select c.client_id,
     c.person.name,
     c.person.address.street,
     c.person.address.city,
     c.person.address.prov,
     c.person.address.cap
from client c;

Everything works good.

Now i want to update c.person.address.prov

I tried with:

update client
set client.person.address.prov='MI'
where client.client_id=1

But i get an error.

Ora-00904: "client.person.address.prov" invalid identifier.

How can i update the field prov??

Thank you for your answer.


texts:
select DATE(added), SUM(amount) ,currency
from transactions
where  DATE(added)  > subdate(CURDATE(),10)
AND status = 'APPROVED' GROUP BY  DATE(added);

This will return one sum per day which is wrong since i  have many currencies.
i want to get daily sum's by currency.
example:

24/10/2019 EUR 15000
24/10/2019 USD 18000

23/10/2019 EUR 55000
23/10/2019 USD 68000


texts: MySQL multi-source replication with identical DB names

texts:
How can I set up a multi-source master/slave relation with masters having the same database names?

I am using the MySQL labs 5.7.2 release with multi-source replication.  One of the use cases for multi-source replication is centralized backup of many mysql servers (https://dev.mysql.com/worklog/task/?id=1697).  I want to set up an environment for this purpose.  However, we have multiple database masters that all have the same database names.  There doesn't appear to be any way of rewriting the database name on the slave to avoid naming collisions (like prefixing it with host or channel name).

I found the replicate-rewrite-db option (http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-rewrite-db), which sounded promising but doesn't seem to apply to this case.

Is there any way to accomplish this on the slave side, or is the only option to prefix the names on the master side?


texts: moving all databases from SQL 2005 EXPRESS SP2 to SQL2005 Enterprise SP4

texts:
Thank you for such a great forums on web.
I'm not a DBA specialist and my knowledge about SQL server is low so this simple issue is getting challenging for me.

We have an old SQL Server express 2005 SP2 on WINDOWS 2003 platform.

We want to copy all of the databases to our new server which is 2008R2 and it is suggested to have SQL Server SP4 to have compatibility on 2008R2

My question is this:

How to move/copy all of the credentials and databases and the whole data from OLD server to new server?

We can have 4-5 hours of downtime and i have only 100 databases.

I want to use the easiest way and the must simple procedure to do this job.

** A friend of mine told me SQL Server management studio 2012 has a built in COPY DATABASE wizard which easily copy data from source to destination server. But it needs SQL Agent be installed on the source server. Since the source server is EXPRESS edition so we don't have any AGENT there.

Do you have any easy to apply solution? I will be thankful if you guide me in details since i'm not a DBA specialist and my main work area is on other web services expect MSSQL.

I have also installed SP2 on the new 2008R2 server and i didn't find any issue with that.

Thank you for your time on my case.


texts: Query performance and join hints, plan cost, and duration

texts:
Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases.

Background

I have a reporting database using traditional star schema (dimension/fact tables). SQL is generated by ORM in the BI tool. I have some flexibility with the generated SQL in terms of adding optimizer hints, but that's it (no query refactoring, etc.).

Problem

When the below query is executed with no optimizer hints, average duration is around 90 seconds and estimated subtree cost is around 2.9. When executed with the OPTION (MERGE JOIN) or OPTION (HASH JOIN) hints, average duration is around 3 seconds, but estimated subtree cost is around 4.9.

I have verified that statistics on affected tables are up to date using UPDATE STATISTICS <schema>.<table> WITH FULLSCAN;. Indexes have also been added based on optimizer recommendations.

Here's the query (yes, it's ugly, see ORM comment above):

SELECT  a11.trans_00_key  TRANS_00_KEY,
    a11.region_id  REGION_ID,
    Max(a15.region_cd)  REGION_CD,
    a11.state_id  STATE_ID,
    Max(a13.district_cd)  DISTRICT_CD,
    a12.cntrct_nbr  CNTRCT_NBR,
    a11.proj_nbr  PROJ_NBR,
    Max(a11.proj_nbr)  PROJ_NBR0,
    CONVERT(DATETIME, CONVERT(VARCHAR(10), (a12.sys_date_yr + '-' + a12.sys_date_mon + '-01'), 101))  CustCol_5,
    a12.proj_ctgry_nbr  PROJ_CTGRY_NBR,
    a11.type_of_work  TYPE_OF_WORK,
    a11.funct_rng  FUNCT_RNG,
    Isnull(a11.fis_id, -1)  FIS_ID,
    Max(Isnull(a14.fis_dscr, 'Blank'))  FIS_DSCR,
    CASE WHEN a12.bid_amount > 1 THEN a12.bid_amount ELSE a12.eng_est_amt END  CustCol_7,
    Sum(a11.est_amt)  WJXBFS1,
    (Sum(a11.ltd_amt) - (Sum(a11.ltd_ind_bill_cst) + Sum(a11.ltd_ind_non_bill_cst)))  WJXBFS2,
    Sum(a11.ltd_cost_cntrct)  WJXBFS3,
    ((Sum(a11.ltd_amt) - (Sum(a11.ltd_ind_bill_cst) + Sum(a11.ltd_ind_non_bill_cst))) - Sum(a11.ltd_cost_cntrct))  WJXBFS4,
    (Sum(a11.est_amt) - (Sum(a11.ltd_amt) - (Sum(a11.ltd_ind_bill_cst) + Sum(a11.ltd_ind_non_bill_cst))))  WJXBFS5
FROM    sys_trans_detail_fact   a11
    JOIN    sys_trans_hdr_fact  a12
      ON    (a11.proj_nbr = a12.proj_nbr AND
    a11.trans_00_key = a12.trans_00_key AND
    a11.state_id = a12.state_id)
    JOIN    district_lkp    a13
      ON    (a11.state_id = a13.state_id)
    JOIN    fis_lkp a14
      ON    (Isnull(a11.fis_id, -1) = Isnull(a14.fis_id, -1))
    JOIN    region_lkp  a15
      ON    (a11.region_id = a15.region_id)
WHERE   (((a11.trans_00_key)
 IN (SELECT r12.trans_00_key
    FROM    sys_trans_detail_fact   r12
    WHERE   r12.fund_src_name_id IN (3, 7, 5)))
 AND a11.fund_src_name_id IN (6, 8, 2, 3, 7, 5, 4)
 AND a11.state_id IN (8, 4, 19, 14, 20, 23, 17, 25, 16, 18, 24, 2, 12, 22, 5, 11, 6, 1, 21, 7, 15, 10, 9, 3, 13, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36)
 AND a11.status_id = 1
 AND a11.extract_dttm IN (CONVERT(DATETIME, '2012-09-01 00:00:00', 120))
 AND a11.cost_cat_id IN (10, 4))
GROUP BY    a11.trans_00_key,
    a11.region_id,
    a11.state_id,
    a12.cntrct_nbr,
    a11.proj_nbr,
    CONVERT(DATETIME, CONVERT(VARCHAR(10), (a12.sys_date_yr + '-' + a12.sys_date_mon + '-01'), 101)),
    a12.proj_ctgry_nbr,
    a11.type_of_work,
    a11.funct_rng,
    Isnull(a11.fis_id, -1),
    CASE WHEN a12.bid_amount > 1 THEN a12.bid_amount ELSE a12.eng_est_amt END

Actual execution plan with no hints

I'm confused as to why the plan shows 572 million actual rows for the index seek outlined in red.

Actual execution plan using OPTION (HASH JOIN) hint

I've read that optimizer hints are a last resort after verifying appropriate indexes have been applied and statistics are verified up-to-date. In this case, SQL Server seems to be picking the best plan based on cost, but there is a significant (about 87 second) penalty in terms of query duration. Does this sound like a case where the optimizer hint should be used? If not, what other items should I check to ensure the optimizer picks the best plan for cost and duration?


texts: Why is table opened every time a query is executed against it?

texts:
I execute this SQL command twice and profile

select * from sbtest.sbtest2 limit 100;

The first time profile result is

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000124 |
| checking permissions | 0.000015 |
*| Opening tables       | 0.163660 |*
| System lock          | 0.000029 |
| init                 | 0.000056 |
| optimizing           | 0.000017 |
| statistics           | 0.000073 |
| preparing            | 0.000228 |
| executing            | 0.000007 |
| Sending data         | 0.001327 |
| end                  | 0.000019 |
| query end            | 0.000008 |
| closing tables       | 0.000013 |
| freeing items        | 0.004998 |
| logging slow query   | 0.000015 |
| cleaning up          | 0.000009 |
+----------------------+----------+
16 rows in set (0.00 sec)

This is the first time6 ,so it needs to "open tables" from hard disk, and above is the second time profile.

mysql> show profile for query 5;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000072 |
| checking permissions | 0.000012 |
*| Opening tables       | 0.000026 |*
| System lock          | 0.000013 |
| init                 | 0.000025 |
| optimizing           | 0.000006 |
| statistics           | 0.000016 |
| preparing            | 0.000013 |
| executing            | 0.000003 |
| Sending data         | 0.000296 |
| end                  | 0.000008 |
| query end            | 0.000006 |
| closing tables       | 0.000010 |
| freeing items        | 0.000018 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000004 |
+----------------------+----------+
16 rows in set (0.00 sec)

The second time "open tables time" is 0.00026, which is much faster than the first time "open tables time" 0.163660.

What I don't understand is when I use innodb buffer pool and my open_table_cache is using, why I still need to open tables from hard disk ?


texts: Best structure for user-level versioning of data in mysql

texts:
I have an application I'm building that allows users to upload data files and I read the contents of the data file into a database. All the data files have the same fields, but there may be slight differences between the data elements depending on the original source and\or user (differences in the values, not in the data types).

To respect this, I need every user to have a personal copy of the data in the database, so no user can read the data that another user uploaded. The files are translated into a number of tables with foreign keys relationships, etc..., so its not trivial.

I have thought of the following solutions:

A single table structure with a user_id column in every table to  keep the data specific to the user - The current solution, but I'm  scared it may be a nightmare to maintain.
A different copy of the table structure per user (so datatable_1 and datatable_2 for users 1 and 2) - simpler to maintain and segment, but could be a lot of   tables and I'll need to create seperate views for each table   strucure... just alot of objects. Also, table-level locking would    prevent access issues when one user is uploading and another is   reading.
A different database for each user (db_1.datatable,   db_2.datatable) - Even better structuring and segmentation, and fewer   tables per database, but most pricing for web-supported applications   is per database, so this may be a non-starter...

Any thoughts or other suggestions would be much appreciated...


texts: MySQL: AVG of defined variable per month

texts:
I need help with the following code:

SELECT DISTINCT
    userid
    , count( userid ) as login_count
FROM
    (
        SELECT DISTINCT
            userid
            , date(FROM_UNIXTIME(date_time)) AS DAY
        FROM xcart_login_history
        WHERE status="success"
            and (action ="login" or action = "autologin")
        ORDER BY userid, DAY
    ) as login_days
WHERE login_days.DAY <
    (
        SELECT DISTINCT min(date(FROM_UNIXTIME(xcart_orders.date)))
        FROM xcart_orders
        WHERE xcart_orders.userid = login_days.userid
        GROUP BY userid
    )
GROUP BY userid;

This shows me the COUNT of logins for individual users before purchasing. It is on purpose not returning more than one per day counted.

I now need the average login number / login count before purchase on a monthly basis.

For instance, the calculation should analyze the buys in January and calculate the average logins (max 1 per day) that were necessary for this purchase.

Could please somebody help me?


texts: Renaming multiple databases on a MySQL slave

texts:
I have 4 mysql databases that I'm replicating from a master to a slave.  In the past, I have renamed one of them because the slave is also a dev server where it has a dev version of that schema with the same name as prod, to make things easier for devs for various reasons.  Replication currently goes like this:

db1 => db1_prod
db2 => db2
db3 => db3
db4 => db4

(mind you, these aren't the actual names).

This has worked for us in the past because db1 is actually a very unique name & we took care of the renaming by piping the dump through sed, such as:

mysqldump --master-data --databases db1 db2 db3 db4 | sed 's/db1/db1_prod/g;' | pv | pbzip2 -p2 > mysqldump.sql.bz2

Then on the slave we have the rename directives in my.cnf:

replicate-rewrite-db='db1->db1_prod'

This has always worked due to db1's actual unique name.  However, my testing has shown that it will not work for the other DB names, because some of the strings actually exist in tables or rows.  What we want to do is rename them all to _prod:

db1 => db1_prod
db2 => db2_prod
db3 => db3_prod
db4 => db4_prod

I've also tried:

s/`db1`/`db1_prod`/g

But when I grep'd the resulting file, it still got more hits than I expected, though I can't say exactly what because the output is a bunch of very long rows, but I know it's more than just the DB name.

Best thing I can think of right now is to dump one at a time without the --databases flag in mysqldump, then specify manually which DB schema to restore it to, but I'm not sure how to do that while keeping replication running if I'm having to reset the binlog position on the slave for each DB restore.

I did search around a bit & saw this post:  Restore mysql database with different name, but my use case is a little different since I have multiple DBs to consider.

Is there a better/easier way to do what I'm trying to do?

Edit:

I guess I could open the dump file up & edit the 3 lines for each DB each time I need to restore:

-- Current Database: `db1`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `db1`;

and edit those 3 lines for each DB.


texts: MySQL - Large Table best practices/indexes

texts:
I have a table which currently contains around 33 millions rows of data, and is constantly recording more rows second by second.  The query to pull data out of this table is gradually getting slower and slower, to the point where it is practically unusable.

My table schema is like so:

and the table has the following indexes:

The usual query from this table consists of selecting rows where the shift is equal to X, and the timestamp is between X and Y.

Can anyone provide/suggest a better table set up, or how I can improve upon the indexes to make this table more efficient?

Right now I'm trying to go through and re-index it, as I believe the indexes have become fragmented, and MySQL keeps loosing connection just trying to reindex the table.  So I'm not sure how I can even accomplish this.  My thought is to re-create the table structure and then slowly migrate over the data using a more efficient index set.


texts: How do I create a point with an SRID in MySQL?

texts:
MySQL supports using ST_PointFromText to construct a point with an SRID,

  ST_PointFromText(wkt[, srid [, options]])

  Constructs a Point value using its WKT representation and SRID.

This assumes the input is Well-Known Text (WKT). How do I construct a point with latitude and longitude as doubles (or floating point types).


texts: How to get the spatial index in CREATE TO script?

texts:
Is it possible to get the spatial index using the create to script using SQL Server 2012?

When I write out a table using SCRIPT TABLE AS > CREATE TO in Microsoft SQL Server Management Studio 2012, I get the primary key index, but the spatial index does not carry over.

Thank you!


texts:
I have a table for article that save the category for each article.
I want to update the sort order, but categorywise using a single query.
I am using this for data migration using bulk inserts and updated, so it looping the data is not a feasible solution.

id      category_id         sort_order
10          5                   1
11          5                   2
12          5                   3
13          6                   1
14          6                   2
15          6                   3
16          7                   1
17          8                   1
18          8                   2
19          7                   2
20          8                   3

Is it possible to do this in a single query?

I have written the query for update sequentially on all rows. But unable to do it categorywise. The query i have written is below

SET @so = 0;
UPDATE article A
INNER JOIN  category M ON A.category_id = M.id
SET A.sort_id = (@i := @i + 1)
WHERE A.user_id = 1;

SQLFIDDLE


texts: which one is the fastest way of moving database form local to live server in sql server

texts:
I am using SQL Server 2008, I have a big database of almost 500 MB. Now I wanted to upload or deploy this DB to the Live server. Currently I am generating scripts and then uploading them to live and by command line I am deploying the database.

This took almost 2 hours so that's very cumbersome to do and one more problem is also that I am not able to use import export database option cause it loses constraints like primary key and all and also gets an error while inserting record from the same process.

Can you tell me how can I do all this and with proper data transfer.


texts: How to merge column in mysql depending on match

texts:
I have one table which looks like this in mysql

column1  column2    name    column3 data
1          1        foo     1       1.5
1          1        foo     2       2.5
1          1        foo     3       3.5
1          1        foo     4       4.5
1          1        foo2    1       1.1
1          1        foo2    2       3.5
1          1        foo3    3       5.6
1          1        foo3    4       6.7

I want to do query like something like this, I really don't know how to write as I am new to mysql

select name from mytable where name = 'foo' or name = 'foo2'or name = 'foo3'

I expect output to be like this

column1 column2 column3    foo    foo2  foo3
1       1         1        1.5     1.1  NaN
1       1         2        2.5     3.5  NaN
1       1         3        3.5     NaN  5.6
1       1         4        4.5     NaN  6.7

Is it possible in mysql ?


texts: We are having problems with log file sync waits periodically.  We have a 2 node RAC cluster using ASM. I'd like to monitor the physical device(s) containing the REDO log files using iostat, but I'm not sure which physical device contains the redo log groups.  We have something like 150 physical devices so I'd like to narrow this down as much as possible.  How would I map the ASM files containing the redo logs to the physical linux devices?


texts: How to display additional column in select with true/false value [SOLVED]

texts:
Solved. Look in comment.

I am using PostgreSQL 9.1. I have 3 tables:

simcards:
id serial NOT NULL,
iccid character varying(30) NOT NULL,
customer_id integer
customer:
id serial NOT NULL,
name character varying(50) NOT NULL
topup:
id serial NOT NULL,
amount integer NOT NULL,
iccid_id integer NOT NULL

And now when I select some simcards I would like to get extra column that shows value yes/no, true/false, 1/0 if this simcard has also a record in topup table (if had been toped up).

I think I have to use LEFT JOIN but I don't know how to display only value true/false. Here is my current SQL that shows everything form topup table:

select
   b.iccid,
   b.phone_number,
   d.name as customer,
   t.*
from
   simcard_simcard as b
   join simcard_customer as d
       on b.customer_id = d.id
   left join simcard_topup as t
    on b.id = t.iccid_id
where d.name = 'THREE'

Can you give me any suggestions?
Thanks


texts:
I have a table which contains the following columns Table6
ID Place Name Money,

From this I am trying to create a table which contains the following column
Place ,Avg_Money_Place ,High_or_Low , the aim for high/low column is to show equal when this is avg of Place is equal to overall avg, low when it is lower than overall_avg and high elsewhere.

I have a vague idea of how to calculate this with Where structures but am struggling to do with select structures. Here is my feeble attempt
I am struggling to this till now I have tried this

Create table Table7 AS
   Select Distinct(Place) , Avg(Money)
     from Table6
    order by ID

This gives me the places and the corresponding averages but I am unable to get the hig/low column

I tried using the following function

INSERT INTO TABLE7 (
  Select High_Low
    CASE
      WHEN AVG(money) < (select AVG(Money) from Table6 ) THEN 'LOW'
      WHEN AVG(money) > (select AVG(Money) from Table6 ) THEN 'HIGH'
      WHEN AVG(money) = (select AVG(Money) from Table6 ) THEN 'EQUAL'
)

This did not work !
Please help


texts: Is there any difference at all between NUMERIC and DECIMAL?

texts:
I know that the NUMERIC and DECIMAL data types in SQL Server work the same: the syntax for creating them is the same, the ranges of values you can store in them is the same, etc.

However, the MSDN documentation describes the relationship between the two as this:

  numeric is functionally equivalent to decimal.

Normally, when I see the the qualifier "functionally equivalent", it means that the two things aren't exactly the same , but that they are two different types that are indistinguishable from the outside.

Is this implication true? Are there differences between NUMERIC and DECIMAL that just happen to behave the same to an outside observer? Or are they actually equivalent, e.g. is NUMERIC just a legacy synonym for DECIMAL?


texts: "ERROR: there is no parameter $1" in "EXECUTE .. USING ..;" statement in plpgsql

texts:
I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this:

CREATE TABLE parent_table (
  value integer,
  end_time timestamp without time zone
);

CREATE OR REPLACE FUNCTION mk_child(_year INTEGER, _month INTEGER)
RETURNS text AS $$
DECLARE
  tname varchar;
  start_date date;
  end_date date;
  next_month varchar := (_month + 1)::text;
  next_year varchar := (_year + 1)::text;
BEGIN
  tname := 'child_y' || substring(_year::text from 3 for 2)
           || 'm' || lpad(_month::text, 2, '0');
  start_date := DATE (_year::text || '-' || _month::text || '-01');
  IF ( _month = 12 ) THEN
       end_date := DATE (next_year || '-01-01');
  ELSE
       end_date := DATE (_year::text || '-' || next_month || '-01');
  END IF;

  RAISE NOTICE 'Creating child table %', tname;
  EXECUTE format('CREATE TABLE %I ( CHECK ( end_time >= %L AND end_time < %L ) )
                  INHERITS (parent_table)',
                 tname, start_date, end_date);
  -- EXECUTE format('CREATE TABLE %I ( CHECK ( end_time >= $1 AND end_time < $2 ) )
  --                 INHERITS (parent_table)',
  --                tname)
  --     USING start_date, end_date;
  RETURN tname;
END
$$ LANGUAGE plpgsql;

When I call it, it is successful:

# select mk_child(2015,1);
NOTICE:  Creating child table child_y15m01
   mk_child
--------------
 child_y15m01
(1 row)

However, if I use the EXECUTE ... USING ...; form (commented out in the snippet above), I get an error:

# select mk_child(2015,2);
NOTICE:  Creating child table child_y15m02
ERROR:  there is no parameter $1
CONTEXT:  SQL statement "CREATE TABLE child_y15m02 ( CHECK ( end_time >= $1 AND end_time < $2 ) ) INHERITS (parent_table)"
PL/pgSQL function mk_child(integer,integer) line 21 at EXECUTE statement

The PostgreSQL docs say explicitly that this format works and is more efficient.

So why does it not work as expected?


texts: Select same records that only have different values for a particular column

texts:
Below is an example of my customer table. There some records having multiple values in BIRTHDAY DATE (by mistake or so). I only want to select those records that have same values for LASTNAME, MIDDLENAME, FIRSTNAME, SSN but different BIRTHDAY:

Member table

LASTNAME   MIDDLENAME   FIRSTNAME    SSN      BIRTHDAY
Jones      M            Carol        1234     17-DEC-45
Jones      M            Carol        1234     17-DEC-45
Jones      M            Carol        4425     20-APR-70
Black      S            Ted          5555     15-MAY-57
Roberts    T            Cole         1412     14-MAY-57
Roberts    T            Cole         1412     20-OCT-57
Roberts    S            Cole         1412     15-MAY-57

I would like the result to be:

LASTNAME   MIDDLEANME  FIRSTNAME    SSN      BIRTHDAY
Roberts    T           Cole         1412     14-MAY-57
Roberts    T           Cole         1412     20-OCT-57

Notice that there were few accounts with same SSN or full name in the table, they are not selected since they don't have everything same. Also Jones M. Carol with 1234 as SSN is not selected either since she does not have different Birthday for two different account.

This is my SQL query that I have so far and this is not working well necessarily.

SELECT x.FIRST_NM, x.MDL_NM, x.LAST_NM, x.SSN, x.BRTH_DT
FROM Member_table x
WHERE EXISTS
(
    SELECT FIRST_NM, MDL_NM, LAST_NM, SSN, COUNT(*)
    from Member_table
    WHERE CURRENT_RECORD_IN = 'Y'
    group by FIRST_NM, MDL_NM, LAST_NM, SSN
    having count(distinct BRTH_DT) > 1
)
ORDER BY FIRST_NM ASC, LAST_NM ASC, MDL_NM ASC, SSN ASC;

Any advice for this query?


texts: Out of memory error from Java stored procedure

texts:
I have a Java stored procedure inside Oracle 11g database. The procedure uses lots of memory, so I'm setting the max memory size in same session:

create or replace function setMaxMemorySize(num number) return number
is language java name
'oracle.aurora.vm.OracleRuntime.setMaxMemorySize(long) returns long';
/

select setMaxMemorySize(4*1024*1024*1024) from dual;

I can confirm that max memory has been set:

create or replace function getMaxMemorySize return number
is language java name 'oracle.aurora.vm.OracleRuntime.getMaxMemorySize()
returns long';
/

select getMaxMemorySize() from dual;
4294967295

However, when I run my Java procedure, I get out of memory error from PostgreSQL JDBC driver that the program is using

Exception in thread "Root Thread" org.postgresql.util.PSQLException:
Ran out of memory retrieving query results.
...
Caused by: java.lang.OutOfMemoryError

But the same program (with same arguments) runs fine outside database by setting the memory limit as JVM argument.

Why is setMaxMemorySize not working? Is there some other parameter I need to set? Java Pool size is already set to 5 GB.


texts: How to configure SQL Server Port on multiple instances?

texts:
I have a server called SQLDWDEV01, with 2 instances: default and UAT.

To the default instance that uses port 1433, everything works fine.

However, the UAT instace, which I configured according to the pictures below, I cannot connect from my local machine.

I have access, and remote connections are enabled.

What is it that I miss doing so that I could connect to this instance?

If this question is somehow not complete, please ask and I will reply.

there are no firewall(s).

The error message that I am currently having is:

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


texts: Types of ways to achieve Many-to-Many relationship between tables in DB?

texts:
I have two tables namely 'City' and 'Travels'. City Table will have list of cities in each row with city_id as the Primary Key and Travels Table will have list of travels in each row with travels_id as the Primary key.

Scenario:

  One City has many travels for service and One Travels has many city destinations as their service. (i.e) Many-To-Many Relationship in the Table.

         city table                     travels table
+------------+------------+      +-------------+----------------+
|    city_id |  city_name |      | travels_id  | travels_name   |
+------------+------------+      +-------------+----------------+
| 1          | coimbatore |      | 1001        | KPN travels    |
| 2          | mumbai     |      | 1002        | Garude travels |
+------------+------------+      | 1003        | SRT travels    |
                                 +-------------+----------------+
  city_travels join table
+-------------+----------+
| travels_id  | city_id  |
+-------------+----------+
|  1001       |   1      |
|  1001       |   2      |
|  1002       |   1      |
|  1003       |   1      |
|  1003       |   2      |
|  1003       |   3      |
+-------------+----------+

This is how the Many-to-Many relationships between tables are made.
But my doubt question is that if either of city table or travels table row increases the city_travels join table row will increase enormously.

Is there any other way to achieve Many-to-Many relationship between tables. I searched a lot but i couldn't find any other solutions.

Please let me know, if there any other options or trick to achieve this. Moreover please comment if there is any wrong in this question.

Thanks a lot in advance


texts:
I found that My MariaDB's ibdata file keep increasing.
So, I've searched for this, and found that innodb_file_per_table should be set as 1.
but, my DBMS's configuration has already set as 1;
Why ibdata file size keep increasing and what else should I do for this.
These below are my dbms info.
DBMS: MariaDB
engine: InnoDB Engine
version: 10.3

** my.cnf **
innodb_file_per_table=ON
transaction-isolation=READ-COMMITTED


texts:
I have the following table structure with example values:

Number      Reference                 Length                                  Quantity
----------- ------------------------- --------------------------------------- ----------
16002983    ASH 14                    383.000000                              4
16002983    ASH 14                    1669.000000                             4
16002983    GLAS-NP                   403.000000                              1
16003002    SZR 16                    1402.000000                             1
16003002    Energy Surcharge F        56556.680000                            1
16003256    SZR 14                    726.000000                              1
16003256    Energy Surcharge F        40706.820000                            1
16003256    7283-ASB                  726.000000                              1
16003308    ASH 14                    740.000000                              4

The table has, as of now, 742 entries and expands every day.

I need to find a Reference that is shared among all Numbers, if there is any.

How? I don't even know if I need JOINs, GROUP BY, something else? I seem to fail at logic here.

Any help appreciated.


texts: I’m having an interesting conversation with my friend who’s a developer. I’m a database admin. I say file is a basic form of database. I believe whatever that stores data can be called a database. He says only engine like sql server is a database. I think he’s confused between a database and database management system? Please share your thoughts. Am I right or wrong ? Btw he agrees an excel is a database since it has rows and columns. I say a simple word document is also a simple form of database without coming into the argument of whether it’s efficient or what are it’s capabilities!


texts: query output shows ##,###,### instead of right output

texts:
I am working on one project in which, query output gives ##,###,### instead of right values.
according to requirement we calculate the time period and display it in separate table.
this is query I am using as calculation
select (to_char((coalesce(11808000,0) - (coalesce(3562.66,0) - (coalesce(1800,0) + (coalesce(1800,0))))),'FM99,999,999'))::character varying as mytest

so if I run this one, it will give output properly, but the seperate table what we use to display output is giving wrong values. can any one help me with this ??


texts: Will "Stack Signature for the dump..." appear in the SQL Server error log every time there is a memory dump?

texts:
I am currently monitoring sql server memory dumps as they appear in the error log. All I really need to know is if this occurred, I can find out the details another way.

Right now my logic is that if the string "Stack Signature for the dump" appears in the log, this indicates that a memory dump event has occurred. I am trying to confirm that this happens every time a memory dump occurs, regardless of what comes after.

I know there is the dmv that has this information, but our logging platform can only look at the sql server error log at the moment.

I am going with this string from the error log instead of other ones because I have noticed that sometimes it doesn't log that a dump file was created, but I see one happen. For example:

This appears in the error log with the address to a dump file

Stack Signature for the Dump is 0x0000000000000002342
*Short Stack Dump
****************
**
*BEGIN STACK DUMP:
**
****************
*Stack Dump being sent to C\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\SQLDump002.txt
**Dump thread spid 0, EC = 0X000000000000000\

This doesn't appears in the error log, with no address to the dump file:

Stack Signature for the Dump is 0x000000000000000654
*Short Stack Dump
****************
**
Non-yielding scheduker
*
*BEGIN STACK DUMP:
**
****************
*Unable to get thread context for spid 0

In both cases, the memory dump shows up in sys.dm_server_memory_dumps. But the fact that the second scenario create a dump file without indicating it in the error log makes me question if "Stack Signature" is 100% reliable as well.
So, is there eve a case where a memory dump would occur in SQL server and it would not appear in the error log as a "stack signature"?
Thanks.


texts: How to atomically replace a subset of table data

texts:
In PostgreSQL 9.6 I have a table T like this
category | id | data
---------+----+------
A        | 1  | foo
A        | 2  | bar
A        | 3  | baz
B        | 4  | eh
B        | 5  | whatcomesafterfoobarbaz

There is a view V giving me the data for T, so it has columns category, id, data. T is essentially the materialized view for V, except that I need to refresh it with more granularity than "refresh everything".
So I will select from V for example
SELECT * FROM V WHERE category = 'A';

Or
SELECT * FROM V WHERE category = 'A' AND id = 2;

And replace the relevant rows in T with whatever data V gives me. Unfortunately I cannot do a simple UPDATE: asking V eg. for WHERE category = 'A' might give me a totally different set of rows than before. Therefore I need to do this sequence:
DELETE FROM T WHERE <condition>;
INSERT INTO T (SELECT FROM V WHERE <condition>);

<condition> is either WHERE category = ? or WHERE category = ? AND id = ?.
How do I do this so that the following conditions hold?

Reads from rows not satisfying <condition> should be unaffected.
The change should be atomic, meaning reads from rows satisfying <condition> should either see the the old row set or the new row set, not a mix.

Note: unlike this question, I don't want to replace the whole table at once - only the rows affected.
Added details

There are more reads than writes, on the order of 10-100 times more. After each write there will be a read to the adjacent categories. The application is looking at a set of categories, ids and data and updates the data for one or more categories at a time. Right after it will re-fetch those categories and display them, and it must see the fresh data. All the ids are always fetched with "their" category.

Each category will have something like 1-10 ids, there will be tens of thousands of categories.

More details after first answer

Transactions can run concurrently. There can definitely be a case when two transactions start with DELETE FROM T WHERE category = 'A';.

There is a table categories where it's possible to lock rows FOR UPDATE. There also is a table where ids can be locked FOR UPDATE.

RETURNING makes not much sense here as I need to fetch more than just the rows changed. Thus all is simpler with a separate SELECT.


texts: SELECT with large WHERE IN clause taking long time

texts:
I'm pretty new at all this so be gentle :).

I have large medical DB where I need to extract data for patients based on selected doctor(s).

Right now I build a patient list by

SELECT patID
FROM appointments
WHERE docID IN ('docid1', 'docid2', ..... , 'docidn')

and once i get my patient list I use it when extracting data from a whole bunch of patient related tables.

Depending on my initial doctors list I can end up with patient lists that are 20,000+. The total # of patients in the system is close to 1,000,000.

I then take my patient list and start getting data from other tables, more or less like this

Select *
FROM xrays
WHERE patID IN ('patid1', 'patid2', 'paid3',....., 'patidn' )

where I build the list of patids based on my the list I have from the first part.

As you can imagine, with a patient list of 20,000, the above query is taking a long time.

Any suggestions on a better way to do this?


texts: SQL Server Virtualization best practices - NUMA, HT, Cores vs Speed

texts:
We run on Cisco UCS + VMware ESX + HP 3PAR.

Host blade config :
UCSB-B200-M4 , Xeon E5-2667 v3 3.1GHz , 2 sockets , 8 cores each, Hyper-Threading Active
So total 16 physical cores, or 32 logical cores.

We have Software Assurance with M$, so all are Enterprise editions, and lot of additional SQL core licenses paid for, so money is not an issue.

Our primary single OLTP SQL VM is 'dedicated' to one of the hosts, i.e. no other VMs are allowed to run on it, cos it requires all 16 cores of power. Even with that, CPU regularly runs ~60-80% , so we're planning to upgrade hardware. Questions below :

http://imgur.com/a/1t8oQ

SQL Server Processor NUMA config - all 16 processors are on 1 NUMA node - But I thought since the host has 2 sockets, there would be 2 NUMA nodes ?
vCenter Socket/Core config - 16 sockets with 1 core per socket - Is it better to change this to 2 sockets with 8 cores per socket since that matched underlying hardware ?
I recently heard at PASS conference, that if an entire VM is being dedicated to a single host, then leave Hyper-Threading disabled. Anybody agree/disagree ?
As a basic pen-paper calculation, we need to double our 'compute capacity' from 50 (16 cores x 3.2Ghz) to ~100 GHz.
Is it better to go for higher core-count across 2 sockets:
E5-4669 : 2 socket x 22 cores x 2.2 Ghz = 96.8 Ghz
or higher clock speed across 4 sockets:
E5-4627 : 4 socket x 10 cores x 2.6 Ghz = 104 Ghz


texts: Oracle equivalent of sp_rename in SQL Server

texts:
I am trying to rename a column of multiple tables in Oracle using a cursor over a table that has all the information of other table.

In SQL Server I was able to do it correctly using sp_rename. I am not able to find an equivalent in Oracle.

I tried with alter table but my table names are in variable so I am not able to do it. I am using:

execute immediate alter table var_table_name rename var_col to var_new_col

Can anyone provide a method that is similar to sp_rename in Oracle or any other method?


texts: Storing the correct answer to check against

texts:
I'm designing a database for a polling app. Users get to answer a bunch of questions and we store them.

The relevant ERD part

I want to store the correct answer for each question so we can check whether they answered the question right or not.

The questions can have different "types":

string
number
single_response
multiple_response

How should I store the correct answer?

Adding correct_answer to both questions and options

adding just a correct_answer field to questions wouldn't work in case the question has type multiple_reponse. I've thought about adding correct_answer to both questions and options where questions would have a string or number with the correct answer and options would simply get a true or false. We'd check if all options with correct_answer set to true are selected or not and if an option with correct_answer set to false is selected

Creating a new table called correct_answer

Add 2 columns:

correct_answer
options_id

And then check if either is set. We'd have to create a new correct_answer for each option.

Adding correct_answer to questions

We could use type to determine how to read correct_answer and then either check correct_answer directly or explode() a comma delimited option_id value to check the answer

I'd like to know which option is the best practice or if anyone has a better idea.

My entire sql: fiddle


texts: Automatically purge dropped tables in Oracle

texts:
I am using an Oracle 18c database in a build system.

One of my builds is for a JPA/Hibernate project, which is configured as "create-drop". This means it will create all tables during startup and drop all of them again at the end.

The problem is: I can't configure Hibernate to create "drop ... purge" statements.

It seems that as a result the recycle bin has grown with each build, exceeding now the maximum allowed database size of 12 GB - and I am not even able to open the database anymore:

alter database open
*
ERROR at line 1:
ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

I have now droped the PDB and created a new one - but this will last for at most a month, before I run into the same problem again.

My question: Is there any option to tell Oracle to immediately purge dropped tables?


texts: Oracle 12c export stuck forever if any table has default sequence

texts:
Recently I came across an issue while exporting database dump from one of the Oracle 12c database schema. The export got stuck forever in a table which has a default sequence like below:

create sequence TEST_SEQ_1 start with 1;

create table TEST_TABLE_1(
    SEQ_NO NUMBER(10) DEFAULT TEST_SEQ_1.NEXTVAL PRIMARY KEY,
    NAME VARCHAR2(10)
);

I could only proceed with the export after dropping the table containing default sequence. Do I need to use any additional parameter in the export or it is an issue with the export utility? IMHO, if the table creation is allowed in the above fashion, export utility should also be able to export the table.

PS: Due to restrictions, I could not use expdp and I have not tried the behavior in expdp.

Thanks in advance.

EDIT

export command:

exp abc/xyz@test file=testdmp.dmp log=testdmp_exp.log

Export: Release 12.1.0.2.0 - Production on Fri Aug 11 19:32:48 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Applicat
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                   TEST_TABLE_1          0 rows exported
                                          ^^
                                          ||
                                          ||
                                      Stuck here


texts: How best to store multi-dimensional timeseries data in a (R)DBMS environment?

texts:
Any suggestions on how multi-dimensional time-series data should be stored and organized in an RDBMS environment? For example, we have multiple experiments from multiple customers. This translates well to say a NetCDF file type structure, where one NetCDF can be for one customer project, and it contains several data variables overtime. This image explains the concept.

From my preliminary research, this seems like an unsolved problem or one with a complicated solution. Are there any recent (not necessarily optimal) attempts at this?


texts: How can I add a column to multiple tables at once in MySQL Workbench?

texts: I have a MySQL Workbench model (diagram) that belongs to a Ruby on Rails application. I now need to add the created_at and updated_at timestamp columns to each of the tables. What is a quick and easy way to do this (instead of doing it manually)? My client does not want to use Rails migrations and uses the Workbench as the authorative database schema so I'm stuck with doing it in Workbench.


texts: Error creating database on new storage in SQL Server 2008 R2 Failover Cluster

texts:
Via the Failover Cluster Manager, I recently added a new disk to an existing SQL Server 2008 R2 Cluster, running on Windows Server 2012 R2.

When attempting to create a database on this new storage, I get the following error:

Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'J:\DATA\mydatabase.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I can manually add a file to J:\data
The new storage can be moved between nodes without any errors
The SQL Server Service Account has full control over the entire J:\ drive
The SQL Server Service Account has full control over the J:\DATA
Here is a layout of the drive, you will notice that DATA is a seperate volume mounted inside J:\

Can someone please help me figure out why I am getting this error?

Update

From James, if I add a subdirectory to data, then the create database command works

CREATE DATABASE [MCO_DB] ON  PRIMARY
( NAME = N'MYDATABASE_DB', FILENAME = N'J:\DATA\MYDATABASE\MYDATABASE_DB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'MYDATABASE_DB_log', FILENAME = N'J:\TRN\MYDATABASE_DB_1.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO


texts: Impact of using UUIDs with an UNIQUE index vs. PK?

texts:
It's well known how the performance of random UUIDs as PKs in an InnoDB table degrades terribly as it increases in size. Would an UNIQUE index for a non-PK UUID column have the same impact?

UUIDs are Version 4, random, stored as binary(16).


texts: Multicolumn GIN index not fully facilitated by query planner

texts:
I am experiencing unexpected behaviour with ProstgreSQL 11.10 query planner with multicolumn GIN index usage.
After defining an index on two columns (a,b) of a large table and executing a query involving conditions for both columns a = 4 and b @> '{"key": "value"}'::jsonb, the planner applies the multicolumn index only for one of the two conditions. It seems that another scan is executed in parallel for the other condition, using another index. Why can this happen?
A few more details:
The index is:
CREATE INDEX coupons_campaignid_attrs_index ON coupons USING gin (campaignid,attributes);

The query is:
explain (ANALYZE, COSTS)
SELECT COUNT(*)
FROM coupons
WHERE campaignid = 97 AND attributes @> '{"CountryId": 3}';

The plan is:
Aggregate  (cost=34532.02..34532.03 rows=1 width=8) (actual time=56613.161..56613.164 rows=1 loops=1)
  ->  Bitmap Heap Scan on coupons  (cost=32208.05..34526.31 rows=2284 width=0) (actual time=23541.180..56384.603 rows=1090012 loops=1)
"        Recheck Cond: ((attributes @> '{""CountryId"": 3}'::jsonb) AND (campaignid = 97))"
        Rows Removed by Index Recheck: 10532325
        Heap Blocks: exact=138539 lossy=583394
        ->  BitmapAnd  (cost=32208.05..32208.05 rows=2284 width=0) (actual time=23478.234..23478.236 rows=0 loops=1)
              ->  Bitmap Index Scan on coupons_campaignid_attrs_index  (cost=0.00..1543.35 rows=179379 width=0) (actual time=14247.668..14247.668 rows=81803572 loops=1)
"                    Index Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
              ->  Bitmap Index Scan on campaignid_deleted_idx  (cost=0.00..30663.31 rows=2284099 width=0) (actual time=8658.669..8658.670 rows=3097095 loops=1)
                    Index Cond: (campaignid = 97)
Planning Time: 1.711 ms
Execution Time: 56619.880 ms

EDIT: I removed the second index campaignid_attrs_idx to see if then the multicolumn index will be used for both conditions. Strangely I still see that the only one of the conditions used in the index scan. Here's the plan:
Aggregate  (cost=181951.27..181951.28 rows=1 width=8) (actual time=209633.017..209633.018 rows=1 loops=1)
  ->  Bitmap Heap Scan on coupons  (cost=1424.30..181945.81 rows=2183 width=0) (actual time=8938.605..209401.433 rows=1091580 loops=1)
"        Recheck Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
        Rows Removed by Index Recheck: 31487517
        Filter: (campaignid = 97)
        Rows Removed by Filter: 80674951
        Heap Blocks: exact=121875 lossy=5572599
        ->  Bitmap Index Scan on coupons_campaignid_attributes_idx  (cost=0.00..1423.75 rows=179434 width=0) (actual time=8908.682..8908.682 rows=81802589 loops=1)
"              Index Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
Planning Time: 6.885 ms
Execution Time: 209638.234 ms


texts: Row locking or avoiding duplicate insertion on primarykey/otherfield combo

texts:
I have a table that stores additional data pertaining to files/images saved in files elsewhere. Each entry can be associated with another type in the system.

For users there may be one or more entries depending on how many files have been uploaded.

For depots, deliveries the same applies, a stripped down version of the table looks like this:

CREATE TABLE IF NOT EXISTS `files` (
  `file_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '...',
  `file_company_id` int(11) NOT NULL DEFAULT '0' COMMENT '...',
  `file_relation` varchar(100) NOT NULL COMMENT '...',
  `file_relation_id` int(11) NOT NULL COMMENT '...',
  `file_name` varchar(200) NOT NULL COMMENT '...',
  `file_mime_type` varchar(150) NOT NULL COMMENT '...',
  PRIMARY KEY (`file_id`),
  KEY `file_relation` (`file_relation`),
  KEY `file_relation_id (`file_relation_id`),
  KEY `file_name` (`file_name`),
  KEY `file_mime_type` (`file_mime_type`),
  KEY `file_company_id` (`file_company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

To get an individual entry is simple enough to query the file_id, to get a list of all entries for another entry I do something like the following:

User 123
SELECT .... FROM files WHERE file_relation='user' AND file_relation_id=123

When users upload images they are displayed in the UI either in their original form, or a variety of sizes which are registered in the back end:

<img src="path/to/image/1/200" style="width:200px;" />

The above snippet requests image 1 with a width of 200px ... if the size does not exist yet it is created on the fly (if it is a registered size).

The problem I now have is ensuring that only one entry for the new size is created when first inserting.

#when creating new sizes they reference the original file
file_id:99, file_relation:'files',file_relation_id:1,file_dimension:200

#for another file - this is valid
file_id:100, file_relation:'files',file_relation_id:5,file_dimension:200

#this entry should not have been created because a previous one with the dimension exists
file_id:101, file_relation:'files',file_relation_id:1,file_dimension:200

There can be duplicate file_dimension entries for different parent relations, but if another thread is inserting at the same time it should be able to pick up that an entry already exists and not create a new one.

I am not sure how row locking would work for this scenario (when INSERT is used) or if I even need to worry about it, here is what happens currently:

SELECT * FROM `files` WHERE `file_relation`='files AND `file_relation_id`=1 AND `file_dimension`=200
#if no entry is found
INSERT INTO FILES (...,`file_dimension`) VALUES (...,200)

I am quite certain that there are other places in my application where this type of thing also applies, any advice appreciated.


texts: MS Access query-by-form opening query, but not running it with new form data

texts:
I have created a query-by-form in MS Access. The form has a text field for each field in my table. I'm only using one starting out to test. The query criteria for that field is

Like "*" & [Forms]![MasterSearchForm]![KeyWords] & "*" Or [Forms]![MasterSearchForm]![KeyWords] is Null

The form has a button with the onclick event to open the query.

If the query is closed when I click the button, it opens the query with the appropriate criteria. However, if I change the value in the form and then click the button again, only the old results appear. If I execute the query manually, then it does suck the data from the form. Why do I need to close the query each time? How do I make the button run the query, rather than just focusing the query tab?

Private Sub Run_Click()
On Error GoTo Run_Click_Err

    DoCmd.OpenQuery "QueryFromMasterSearch", acViewNormal, acReadOnly

    Run_Click_Exit:
    Exit Sub

    Run_Click_Err:
    MsgBox Error$
    Resume Run_Click_Exit

End Sub


texts:
I am a big fan of Postgres both for its price but also for its features.
I am going to have to need to upload into it both Oracle dump and SQL Server files.
I will try to ask and beg for plain .csv for schema DDL but I suspect that I will be given dmp files.

Is there a tool, most preferably open source one, that would allow me to read, profile and possibly load Oracle/SQL Server files into Postgres?


texts: Unique index on multiple columns vs single column index

texts:
I have a table in the following structure. Now from a data integrity standpoint I want to add a joint uniqueness index on product_id and item_id. Now there can be around 100K product_id and 50k item_id. Generally my queries would like Select item_id from product_items where product_id = 'some-id' and item_type=2. Will the uniqueness index be sufficient in this case or should i add a separate index on product_id as well. Also does uniqueness order matter when creating unique index

     product_items
      :id,
      :product_id
      :item_id
      :item_type (int)
      :created_at
      :updated_at


texts: How to find view dependencies in Netezza?

texts: I want to find the dependent table of views in Netezza. Is there a table that provides the details?


texts: special setup to create views in Oracle in default schema

texts:
When creating views in Oracle, do you need special setup for a given user if the view and table in which the view is reading from are in two different schemas?  The user creating the view has dba privileges.

The following command throws the error below.

create or replace VIEW view_patient as select * from DATA.PATIENT_DIMENSION where PATIENT_NUM in (select PATIENT_NUM from patient_list);

Error:

Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.


texts:
Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table.

Something like:

SELECT * -the_geom FROM segments;

I once heard that this functionality was deliberately excluded from the SQL standard because changing adding columns to the table will alter the query results.  Is this true? Is the argument valid?
Is there a workaround, especially in PostgreSQL?


texts: How can I get Sql Server 2016 on a 32-bit server?

texts:
I didn't build it, but we've got a 32-bit Windows Server 2008, with SQL server 2008 and 4GB of RAM.

I need to upgrade it to SQL Server 2016 in order to log ship to a new server, so we can put this thing out of its misery.

The problem - it looks like SQL Server 2016 requires, at minimum, Windows Server 2012, which is only 64-bit.

Question: how can I migrate the data to a SQL 2016 server with <1 day of downtime?

Is there a way to get around it with compatibility levels?