10000 tenants and Muti-Tenant Data Architecture performance? We are trying to decide on a database design for a new web application. We expect to have close to 10,000 tenants and would like to keep their data in separate databases if it makes sense to do so. Each tenant will have a DB that is around 20MB they are tracking mostly the personal data of 50 to 100 youth and 100 to 300 adults plus events, attendance, awards etc. I do not know how many total users would log in at once but each tenant could have several.

My options as I understand them is to have:

A shared-nothing approach giving each tenant a separate database.
A schema approach giving each tenant separate tables.
shared-everything approach where each tenant has their own tenantID

I would ideally like the shared-nothing approach but I am unclear of how many databases you can have in SQL Server before you run into performance issues. Managing multiple databases is not a major concern for us but if SQL Server becomes unresponsive due to the number of DBs this is the kind of information I am seeking. Keep in mind the databases are small 20MB on average. I also need to easily restore backups for individual tenants. I have also read that you can have roughly 32,000 databases in SQL Server but other post suggest you shouldn't try to get anywhere close to this number. Even 1000 to 2000 databases can cause performance issues but it was unclear of the database sizes that caused the issues. If anyone has used thousands of small databases in a SQL Server environment please let me know.

Performance wise which approach would be the best route for us to take? Please also take into consideration of having multiple SQL Server Licenses would not be ideal cost wise. But if it is necessary please let me know.

I have also read several post here and also Multi-Tenant Data Architecture but I haven't seen a real clear winner with a large number of tenants but really small databases.

If anyone has experience with something similar please let me know how you handled this and if it is working out well. Any real world experience/advise would be greatly appreciated.

Thank You.

Design a shared-everything system.  This can be deployed in a shared-nothing way i.e. put each tenant in their own database, should that prove desirable.  The extra development effort to type the additional predicate is very small and easy to do up front.  The reverse - where you re-factor a shared-nothing system to become multi-tenant - will be a significant project and difficult to get right retrospectively.  You will need some scripts or ETL packages to load / unload one tenant from a database.  This will help with size & load balancing, privacy concerns and such like.

The schema-based approach has all the disadvantages of single-database and none of the advantages of multi-tenant.  You will end up with a lot of dynamic code, stored procedures will be difficult to get right and statement recompiles will be ubiquitous, increasing response times.

Invariably some of your tenants will be much larger than others.  My experience is that you will end up with a handful of tenants representing 30%-70% of the total data volume.  These will each have their own DB and, perhaps, infrastructure.  They will consume 60% of your support and maintenance time.  The others will all fit into one or two databases.

I've run development instances with roughly 1,000 databases online sharing about 8GB of memory.  This worked well enough for dev.  I'd never try this in production as the risk of problems snowballing would be too high.

If you're feeling brave you may like to experiment with auto-close on one or more database.  This will save some resources on the instance.  This is definitely not recommended.  I mention it merely as an observation.


Sql Server 2008r2 Replication Snapshot Agent Hang I am setting up a new merge publication, but I can not get the snapshot agent to run successfully.  In fact, I can not run any snapshot agents for any of the other publications on this server now.  This is effecting transactional and merge publications

When running a snapshot, I get the "Starting agent." status returned and then the occasional "agent has not logged a message in 10 minutes" messages.

I've also tried generating a snapshot from the command line:

I never get any errors nor is the c:\repllog.txt log file ever created.  It feels like something mysterious is blocking the snapshot executable pretty much on start up.  Any ideas?

I've not set up Merge Replication before but I do have a fair amount of experience working with Transactional and Snapshot Replication.

This gets me every time I set up a new Subscription or Publication.  Make sure your passwords are correct.  I will typically get this same type error every single time, then I have to go and test to make sure they are correct, and guess what, even though I thought I entered them correctly, I didn't.

A good test is to try to connect to the SQL server using the account/password you are using in the replication environment.

Once you've made the change, if any, attempt to restart the desired replication agent.  I've occasionally had to restart the SQL Server Agent as well, if it was royally screwed up.

Hopefully this is a good starting place.  Best of luck in getting this resolved.

Regards,
Tom


How to get the row number where its ID is 'n' and rows are ordered by some column? I'm trying to get the row number, knowing the row ID, smomething like this:

Get the row number (position) from Table1 order by Col1, where this row ID is 263

 ID |     Col0 |  v Col1 |    Col2
----|----------|---------|---------
258 |        R |       a |       7
262 |        A |       a |       8
277 |        C |       a |      29
263 |        R |       a |      30
202 |        A |       b |      13
219 |        D |       b |      14
  4 |        A |       c |      15
220 |        G |       e |      16
278 |        B |       e |       7
  9 |        P |       f |      18

Is there any way to achieve this

I imagine you'll probably need a secondary ordering column, as Col1 is not a unique value, however this should get you started.

WITH sampledata AS
(
  SELECT
    ROW_NUMBER() OVER(ORDER BY Col1) as row_num,
    ID,
    Col0,
    Col1,
    Col2
  FROM Table1
)

SELECT
  row_num
FROM sampledata
WHERE ID = 263;


Alter trigger Oracle Does alter trigger hang if a table is locked by some DML operation
?
What kind of exception does it throw? Resource busy 0054 or some other exception...?  I have tried to reproduce this scenario but in vain.

Test case.

Open 2 sqlplus sessions, run the following in the first to set the test up:

create table stall_table
(
  a number
);

create or replace trigger stall_session
before insert on stall_table
begin
  DBMS_LOCK.sleep(10000);
end;

Then, execute in session 1:

SQL> insert into stall_table values ( 1 ) ;

Execute in session 2:

SQL> create or replace trigger stall_session
before insert on stall_table
begin
  DBMS_LOCK.sleep(10000);
end;
/

You'll get, after some time:

create or replace trigger stall_session
                          *
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

SQL>

... which answers your question.


Bi-directional replication for the same MySQL table AppA stores/retrieves data from dbA.tableA
AppB stores/retrieves data from dbB.tableA

tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA (assuming both had 5 rows).

row6 was created by AppA (say primary key 6)
row7 was created by AppB (say primary key 7)

I would like row7 to be copied to dbA.tableA and row6 to dbB.tableA

Is this even possible to setup bi-directional replication, so that the AppA, AppB view the same data at any point in time.
If the primary key is an auto-increment, would it be possible to maintain integrity of data or is there a possibility that there would be collisions on the primary key.

I'm assuming these are different instances of MySQL (most likely on different servers).

You can set up Master-master replication, where dbA is a master of dbB and vice versa.

Then, to avoid auto-increment collisions, set the following in my.cnf on dbA:

auto_increment_increment=2
auto_increment_offset=1

And the following on dbB:

auto_increment_increment=2
auto_increment_offset=2


Postgres tablespace size management I have a Postgres 10 database which is the model for an MVC app.
On it many (200,000) LOBS get saved, eventually processed, and stored or deleted depending on some conditions.

I have two problems.

The first is about disk space management, i.e. I want that my tablespace occupies only a portion of disk that I'll allow to it, and when the maximum allowed storage is reached, the application has to stop inserting LOBS.

AFAIK there are no ways to set quota on tablespaces, so if the application continues inserting it fills up the whole disk space, not only the one I've chosen to occupy.

So in order to avoid problems I've set up a disk quota at the OS level, but this is a rough solution.

Is there any better way to manage this problem?
The second problem is about unused space release. AFAIK, if I delete some LOBS, the pg_largeobject size is exactly the same, so I have to do a vacuum full to release the unused space of it.
But for doing this how much additional space do I need?

Reading somewhere it seems that I need at list the table size free disk space, so if it's 100 GB, I need to have another 100 GB in order to perform vacuum.

Is it true?

I've got a baffling experience on that. I had deleted 100,000 records, tried to vacuum full, and I got a "no disk space" error. I deleted another 120,000 records chunk (but the table is not empty), retried the vacuum full and this time worked.

Can you help me shedding light on this matter?

The best way to maintain a size limit for a table (or set of tables or a database) is to create a separate file system with the desired size maximum, define a tablespace on that file system and put the tables into that tablespace.

It is OK to limit size for user tables in that way; exceeding the limit will cause an error, but processing will continue. But you should make sure that PostgreSQL does not run out of space for system tables or (most important) WAL. That's why a separate tablespace is the right solution.

If you are using large objects, you have to take care that the large objects are removed when you delete a table row. That does not happen automatically.

VACUUM (FULL) rewrites a table, so you must have room for both the old and the new table until the operation is finished.

Your “baffling experience” is easily explained like this:

In your first experiment, there was not enough space to hold a new copy of the table with the rows that were not deleted.
In your second experiment, the new table was smaller, so the space was sufficient.


How to prepare executon plan for given sql query on a server with 4 processors (Oracle database) 4 processors means the query runs in parallel.

Select /*+ parallel(4) */ T1.A
FROM T1,T2,T3
WHERE T1.A = T2.A
AND T1.B=T3.B
AND T1.C=T3.C

Tables T1,T2 and T3 have 4,8, and 16 partitions respectively.

Using nested loops,sort merge and hash join as available methods of join. How to create execution plan for above query? I am not able to understand when to apply sort merge in execution plan.

Hints for join operations are used the same way as other hints, for instance

SELECT /*+ parallel(4) USE_MERGE(T1 T2)*/ T1.A
FROM ....

Optimizer chooses join algorithm based on different criteria. There are many things to be considered, but in general merge join is used when optimizer mode is ALL_ROWS (it prefers nested loops for FIRST_ROWS) , both tables involved in the operation have index on join column[s] ( it may still use merge join even if you don't have indexes, but hash_join_enabled set to false or using hash join estimated as more expensive).

The query with hint parallel(4) may potentially allocate 8 parallel servers (4 consumers, 4 producers).


MongoDB does not allow to create unique partial index in field that contains null I'm using MongoDB 3.6.9 (with Ubuntu) to save a set of documents that contain one field named rss_id and for some documents, this field has the value null. I want to create a unique index in that field and tried this solution. From the documentation it seems a good solution but for some reason, I can't make it work in my MongoDB.

When I excute the folowing command (I'm using the shell of Robo 3T):

db.getCollection('noticias').createIndex(
   { rss_id: 1},
   { unique:true, partialFilterExpression: {rss_id: {$exists:true }}}
)

I get the error:

{
    "ok" : 0.0,
    "errmsg" : "E11000 duplicate key error collection: newsWall.noticias index: rss_id_1 dup key: { : null }",
    "code" : 11000,
    "codeName" : "DuplicateKey" }

Please use the below query to create the unique partial index. The reason why your query work is, there may many fields with the value like {rss_id: null}

db.getCollection('noticias').createIndex(
   { rss_id: 1},
   { unique:true, partialFilterExpression: {rss_id: {$ne:null}}}
)

Or you can use sparse index option along with unique index as shown below

db.getCollection('noticias').createIndex( { rss_id: 1}, { sparse: true, unique: true })


Percona Xtrabackup (Partial + incremental) we have been trying restore Percona (Partial + Incremental) mysql backup, but failing. The process followed is as below:

For Backup

# Step I: Take Partial-Full Backup

    innobackupex --user=root --password=**** --databases="db1" /path/to/basebackupdir --no-timestamp

# Step II: Take Partial-Incremental Backup

    innobackupex --incremental /path/to/incbackupdir --databases="db1" --incremental-basedir=/path/to/basebackupdir/ --user=root --password=**** --no-timestamp

For Restoration

Step I: apply log to full backup

 innobackupex --apply-log --redo-only fullbackup

Step II: apply-log incremental backup

innobackupex --apply-log fullbackup --incremental-dir=/tmp/tmp_inc

Step III: Export Backup

innobackupex --apply-log --export fullbackup

Step IV: CREATE Required database in MySQL

mysql -uroot -p**** -e "DROP DATABASE IF EXISTS db1"
mysql -uroot -p**** -e "CREATE DATABASE db1"

Step V: Import DATABASE Structure

mysql -uroot -p**** db1 < db1.sql

Step VI: Discard tablespace

Step VII: Import table

    cd /tmp/fullbackup/db1
    cp -R *.ibd /var/lib/mysql/db1
    cp -R *.cfg /var/lib/mysql/db1

Step VIII: Import tablespace

Step IX: restart mysql

    service mysql restart

Restoration process works fine, Still we are failing, that One of the tables could not create the structure for FULLTEXT index(it gets corrupted), when we try to create table manually error is table already exists.

Please Help

Why do you import the tablespaces? Strickly speaking you could use the data from step II, move it in the datadir, adjust the permissions and start MySQL. The procedure you using would make sense only if you want to restore db1 out of many other databases.  What is the error message?  Anything specific regarding the table that is failing?


Why does query parameter type affects mysql index choice? I have a query like this:

select a, b, c from sometable where cateId = ? /**1**/

We can also do this:

select a, b, c from sometable where cateId = "1"

as MySQL will cast it. Interestingly I observed different performance characteristics on these two queries. Why this is the case?

I omitted quite some details regarding table structure and index details, but in general is it possible for such scenario to really occur, and why?

adding the details I mentioned. if it helps.

    select
        memberId,
        prodId,
        prodName,
        sum(count) as total
    from
        SOMETABLE
    where
        statDate >= ?
        and statDate<= ?
        and catId = ?
        and name <> 'OTHERS'
    group by
        memberId, prodName
    order by
        total desc
    limit 5

    --IDX catId, prodName
    --IDX catId, statDate
    --IDX memberId, statDate
    --IDX catId, statDate, prodName, prodId, count

Query processing takes the given SQL, applies a set of rules and heuristics and comes up with an execution plan. This states which indexes are used and how. The outcome is dependent on the input and how the rules have been written.

Given a slightly different start point a different end point may result. It's somewhat like dropping a ball in a landscape of river vallies. Drop it once and it will roll into a river. Drop it in a slightly different place and chances are it will roll down into the same river. Occasionally, if you're near a ridge, you drop the ball in a slightly different place and it rolls into the next river instead.


preserve generate_series rows through joins I have a histogram query that filters the entities counted by conditions that can only be checked if joins are used.

The query looks something like this:

SELECT gs.t, COUNT(packets.id) FROM packets
RIGHT OUTER JOIN
  (SELECT generate_series(NOW() - '72 hours'::interval, NOW(), '1 hour') AS t) gs
ON packets.transceived_at > gs.t AND packets.transceived_at <= gs.t + '1 hour'
JOIN devices ON devices.id = packets.device_id
WHERE devices.mandate_id = 3
GROUP BY gs.t;

Sadly this will omit rows where there's no packet that was received between gs.t and gs.t + '1 hour'.

This does not happen when I don't join devices, but I need that table (and others in derivative queries) to filter some devices.

Is there a way to still get all rows of generate_series even when using joins?

If you want the COUNT of each element of the series you can use an scalar subquery.

WITH gs AS
(
    SELECT generate_series(NOW() - '72 hours'::interval, NOW(), '1 hour') AS t
)
SELECT gs.t, (SELECT COUNT(packets.id)
              FROM   packets
              JOIN   devices
              ON     devices.id = packets.device_id
              WHERE  devices.mandate_id = 3
              AND    packets.transceived_at > gs.t
              AND    packets.transceived_at <= gs.t + '1 hour')
FROM   gs;


Cannot add Network Service as User/Login I want to setup ProGet on a 2016 server with SQL Express and IIS.

Both the service and IIS web runs under the Network Service user. None seem to be able to access the Database on a local SQL Express instance.

The IIS website tells me that the login WORKGROUP\PACKAGE$ (where PACKAGE is the servers name) cannot access the database:

I understand that I need to add the Network Service (which IIS uses to actually authenticate with SQL Express) as a login/user to the database.

When I tried adding the NT-Authority\Network Service as a login/user to the database, I get an error that tells me that Windows cannot find any authentication for the Network Service.

How can this be? Why can't SQL Express find the Network Service?

It can be a bit misleading, when working with the German version of SQL Server and/or a Windows Client/Server OS.

When you select the <HOSTNAME>\Network Service from the Active Directory pop-up in the SQL Server Login dialog, then SQL Server automatically displays the login (when closing all the dialogues with OK) as:

 <HOSTNAME>\Network Service

This is actually wrong.

German Solution

If you enter the following German string then it will resolve correctly:

NT-Autorität\Netzwerkdienst

English Solution

If you were to use an English Windows / SQL Serer installation then you would have to enter:

NT-Authority\NetworkService

If you click on OK, then the SQL Server Login will be created.


Merge Overlapping intervals in Bigquery I have a bigquery table with following columns:
user_id, unique_id, start_timestamp(UTC), end_timestamp

unique_id is always unique, no repeating values. currently data is grouped by user_id. and then sorted by start_timestamp.

Example Input:

Desired Output:

Since BigQuery support MIN/MAX aggregation function on time fields You can use this SQL:

SELECT user_id, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM `data`
GROUP BY user_id
ORDER BY user_id ASC

And this the sql with some test data you can play with

WITH `data` AS (
SELECT 1 AS user_id, 'a' as unique_id, timestamp('2019-01-20 5:10:05') as start_time, timestamp('2019-01-20 5:10:20') as end_time UNION ALL
SELECT 1 AS user_id, 'b' as unique_id, timestamp('2019-01-20 5:10:11') as start_time, timestamp('2019-01-20 5:15:31') as end_time
)

SELECT user_id, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM `data`
GROUP BY user_id
ORDER BY user_id ASC

Which produce the following desired result:


Oracle data Guard status (active/passive)- version 11g I have 2 servers: Primary (ABCUAT) and Standby (ABCUAT1) configured as Active-Standby DataGuard by previous DBA.

When I took over the system, I think  DataGuard was not in sync, but I could not confirm that because the Archive Log List gives me the same log sequence on both server.

Questions:

If DataGuard is not in sync, why does the Archive log list show the same sequence  number in both server? I thought if the sequence number were the same, it meant the log have been applied to the standby database.
How to resume DataGuard if not in sync? The gap seem to be huge according to max(sequence#).
I tried to delete some old archive log files with RMAN> delete noprompt expired archivelog all; but got in response:

  specification does not match any archived log in the repository,

but physically in the directory there are 50GB of files dated year 2018.

Findings:

SYS@ABCUAT>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     190144
Next log sequence to archive   190146
Current log sequence           190146

Standby server :

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     190144
Next log sequence to archive   0
Current log sequence           190146

Primary:

SYS@ABCuat>select max(sequence#) from v$log_history

MAX(SEQUENCE)   190251

Standby server :

SQL> select max(sequence) from v$log_history

MAX(SEQUENCE#)   7421

DataGuard configuration:

DGMGRL> show configuration;
Configuration - dg_config
  Protection Mode: MaxAvailability

  Databases:
    ABCUAT  - Primary database
      Warning: ORA-16629: database reports a different protection level from the protection mode
    ABCUAT1 - Physical standby database (disabled)
Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> show database ABCUAT1

Database - ABCUAT1

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
O    ABCUAT1

No. The redo (archivelogs) can be transported without applying it on the standby.
sequence# alone is not enough. You need to consider resetlogs_change# too.
An expired archivelog entry is an archivelog entry that exists in the controlfile, but it does not exist on the disk or the database is unable to access it. Such a command does not delete archivelogs from the filesystem.

The standby database was disabled in the broker configuration. Enable it in dgmgrl:

enable database ABCUAT1;

If you are in luck and the standby has all the necessary logs, it will start processing them and it will eventually catch up to the primary. You should see a decreasing transport/apply lag value instead of unknown. If it is unable to do so, newer errors will be shown.

At this point you may have everything on the standby site which just needs processing. Or you could also have an archive gap when the standby is unable to continue due to missing logs, which you can resolve by restoring the missing logs or rolling forward the standby with an incremental backup or rebuilding the standby. Or the standby could be in a totally different state, like a snapshot standby, without the broker being aware of it.


How would I model a product to have multiple image URLs and have only one as the default? Background: I'm using Postgres with Golang GORM library by jinzhu

Here is a basic representation of what I'm looking for, however I dont think it would be right to have a circular reference like this.  Is there a better way to achive what I'm looking for?  One idea of mine is to omit the primary_pictures_id and add a boolean field to the images table to flag a picture as the default (just not sure yet on how to enforce only one to be true)

products
    id
    name
    price
    primary_picture_id FK:images(id)

images
    id
    url
    products_id FK:products(id)

Yes, your idea can work. Add an is_primary boolean column to images:

ALTER TABLE images
ADD is_primary boolean NOT NULL;

and enforce the "No more than one primary picture per product" rule with a partial index:

CREATE UNIQUE INDEX images_primary_image_constraint
ON images (product_id)
WHERE is_primary;

The above index will ensure uniqueness of product_id among rows where is_primary is set. If you try to specify a second primary image for some product, the uniqueness will immediately be violated and the operation will fail.


MySQL create user incorrectly updates user table I recently upgraded from mysql-community 5.7.4 to 5.7.8. All packages show the newer version is correctly installed. I reinitialized my database using mysqld --initialize and corresponding steps. I then restored from a full dump using the standard method (ie, zcat dumpfile.gz | mysql). The server started up fine; I was able to start slave and catch up to the master, still running on 5.7.4. Now the weirdness begins. The user tables don't match. Even if I stop slave and restart my mysqld process, the following (and similar) commands do something strange:

create user 'test'@'%' identified by 'test334455' ;

It puts the hash into the "plugin" field of the user table:

mysql> select user,host,password,plugin,authentication_string from user where user='test';
+------+------+----------+-------------------------------------------+-----------------------+
| user | host | password | plugin                                    | authentication_string |
+------+------+----------+-------------------------------------------+-----------------------+
| test | %    |          | *443C443E4789A416587C0A5892C7D345B73B9B3B | NULL                  |
+------+------+----------+-------------------------------------------+-----------------------+

To make sure this isn't some weird package-specific bug, I initialized a new database and with a separate mysqld process, verifies the command works as expected. Somehow restoring from a dump or connecting as a slave to an older version causes the problem.

However, existing user entries (ones restored from the dump) are correct and work just fine.

UPDATE: It appears the password field was removed between 5.7.5 and 5.7.6, but the restore-from-dump (not replication!) recreated the column; but the current version's create user commands still assume a particular order to the columns (** facepalm **).

So the Question now is: How do I safely update the master database on 5.7.4 to 5.7.8, preferably without dump/restore?

UPDATE: The Mysql documentation does indeed cover this particular quirk, more or less, on a page well-worth bookmarking (Because really, MySQL, you have no shame in making incompatible changes to sub-minor versions.) https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

mysql_upgrade is your friend

  mysql_upgrade examines all tables in all databases for
  incompatibilities with the current version of MySQL Server.
  mysql_upgrade also upgrades the system tables so that you can take
  advantage of new privileges or capabilities that might have been
  added.


How can I cap the total space that mysql / mariadb binary logs take up? I recently had my disk space completely filled with mysql binary log files (mysql-bin.000123, etc).

I was able to resolve short term by with PURGE BINARY LOGS TO (via this answer).

To try and keep it from happening going forward I set lower limits on expire_logs_days and max_binlog_size. Yet, I'm still worried that a huge number of log files could be generated in a short period without a limit on the total space used by logs or the total number of logs allowed.

Is there something I'm missing? Or do I just need to keep setting expire_logs_days lower or turn off binary logging all together if it keeps being a problem?

EDIT:

I understand it's important to diagnose what's causing the logs to fill up quickly and solve that issue. This question is not about how to diagnose what's making them fill quickly, but rather - how do I safeguard against the disk filling to 100% and crashing the server in the case that something is setup wrong or an unexpected circumstance starts to fill these logs.

max_binlog_size has little effect on keeping disk space down; expire_logs_days is the one to worry about.

How fast are the logs filling up?

One way binlogs can fill up extremely fast is when you accidentally have the same server_id on multiple servers in a Replication setup.

Which version?  Which binlog_format?  Lots of multi-row UPDATEs/DELETEs?

More

expire_logs_days is the best available builtin tool.  There may be 3rd party monitoring tools (or you could write one) that watch for disk space.

In any system, you should have some kind of monitoring on disk space -- it is not just the binlog that can go berserk.  The "general log" and the slowlog can, also.  And your application can go berserk.  And so forth.


Huge database logging of event type rows and ways to optimize it We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the last 1-2 months.

Each event corresponds to a resource_id (estimated 5000).

Since just keeping all these in one table gets all maintenance and selects quite slow and resource consuming I was thinking on improving it in some ways:

a) separate every resource_id to each table. 5000+ tables. Selects from multiple resource_ids (maximum 100) will need some rewrite and unions or something.
b) separate every day to a different table. unions or something would be required when selects will require data from multiple days.
c) both the above (too extreme I think)
d) partitioning in someway on timestamp index and maybe even resource_id index. Is it worth it? Or "manual" partitioning of above suggestions is better?

The database is PostgreSQL.

We have something simular, ~5000 events per seconds. Tables are partitioned by month and we store 5 years of data. That gives us a 60 partitions, works fine. It runs on PostgreSQL version 9.1, works better than any older version when using partitions.

EXPLAIN and EXPLAIN ANALYZE are your best friends to get your queries right.


Select images where ALL images are greater than pixel width I will try to explain this clearly.

I have a table of 200,000 records that stores pixel sizes for our product images. A variety of image sizes have been saved over 20 years, and products do not necessarily have images in all folders.

I am trying to find out how many products have all of their images over 800 pixels wide to inform design decisions for a new website.

The fields are:

ID           (unique reference of product)
ImgNo         (id of each image under product: 1 to 36)
CDN_W         (width of image on CDN storage)
LG_W          (width of image in LARGE folder)
LGR_W         (width of image in LARGER folder)

Data looks like this:

ID 123
ImgNo 1
CDN_W 800
LG_W 400
LGR_W 1280

ID 123
ImgNo 2
CDN_W 600
LG_W 600
LGR_W 1280

ID ABC
ImgNo 1
CDN_W null
LG_W 800
LGR_W null

etc...

THE TASK

I need to query the data to find all products where ALL of the images in a particular folder are over 800 pixels wide. There are 3 folders, any of which could provide the best set of images over 800 pixels. I only want products where all images are over 800 - if there are 5 images and 1 is below 800 then that product should not be valid.

Ideally I'd like to identify the TOTAL number of products that are valid, and separately the actual list of products IDs.

Is there a way to do this in MS SQL?

SELECT id
FROM table
GROUP BY id
HAVING MIN(CDN_W) > 800
   AND MIN(LG_W) > 800
   AND MIN(LGR_W) > 800

?


NVL v/s Check IS NULL We have queries in procedures in oracle like,

    SELECT  ......
    WHERE   .....
    AND (SomeColumn IS NULL OR SomeColumn = SomeThing)

But our DBA is saying this is not good, and saying use NVL instead. We are using this for long time and haven't found any issue. Should we change IS NULL with NVL? from NVL I mean NVL(SomeColumn, '') = SomeThing

Many databases including Oracle have a COALESCE function that behaves like NVL, but takes two or more values.  This is useful to return a value when there are multiple columns that could have the desired value and/or to provide a default value.

If all columns in an index are NULL, Oracle does not index the row, so it will require a table scan to retrieve rows when you have a NULL value in the match condition.  In other cases, you have values that are not NULL, to match on and only one column that could be NULL.  This is common in OUTER JOIN when you want to select only one value in the outer table.

In any case, unless you have in index where the NULL value is indexed, a search of all candidate records will be required.  If you have an index that can be used to reduce the number of possible matches to less than 4% of the the records, then the candidate set may consist of those rows.  Oracle will generally use a table scan for small tables, or if there is no index that can limit the candidate rows sufficiently.  Above 4% (possibly lower) the preference will be for a table scan.

You have three options for the conditional:

NVL(column, value) = value
COALESCE(column, value) = value
column = value OR column IS NULL

In my experience, all three are equivalent.  You can verify for yourself, by running an explain plan on the query.

It is possible to influence the plan with hints.  However, this can easily result in sub-optimal plans.


Weird planning time data dependency in Postgres We got a fairly large table holding session data in Postgres. Recently, we found that the planning time for the most common query on this particular table increased over time, at one point to ~130ms which starved the whole instance. The planning time went back to normal after vacuuming and analyzing but is slowly creeping up again. What's particularly strange though is that the planning time seems to depend on the input data:

explain analyze select * from django_session where expire_date > now() limit 1;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.05 rows=1 width=207) (actual time=0.008..0.009 rows=1 loops=1)
   ->  Seq Scan on django_session  (cost=0.00..6357552.08 rows=129398117 width=207) (actual time=0.008..0.008 rows=1 loops=1)
         Filter: (expire_date > now())
 Planning Time: 7.201 ms
 Execution Time: 0.022 ms

In contrast to:

explain analyze select * from django_session where expire_date > now() + interval '2 days' limit 1;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.05 rows=1 width=207) (actual time=0.008..0.009 rows=1 loops=1)
   ->  Seq Scan on django_session  (cost=0.00..6681337.26 rows=128206390 width=207) (actual time=0.008..0.008 rows=1 loops=1)
         Filter: (expire_date > (now() + '2 days'::interval))
 Planning Time: 0.053 ms
 Execution Time: 0.020 ms

This cut-off of about 2 days between increased and normal planning time exists regardless of what type of comparison is made (i.e. <, >, = all have the same effect).

The table is cleaned up every day by deleting old sessions that are expired.

Given the range of expiry_date of up to a year in the future could it be that Postgres' heuristic assumes that an expiry_date in the near future is an unlikely input? Why does this only happen when querying for this particular column and none of the others in the same table?

The reason is an optimization in PostgreSQL, weird as it sounds.

When estimating result counts ow WHERE conditions like the one in your query, PostgreSQL uses a histogram, which is an approximation of the distribution of the values in the column.

Now these histograms are only re-calculated when 10% or more of the table has changed. With data like time-series, that can lead to a considerable skew, because the maximum (or minimum) of the values keeps increasing (or decreasing). So even if only a small percentage of the data change, the minimum or maximum can become inaccurate rather quickly.

To improve estimates, PostgreSQL commit 40608e7f949 has added a feature: when the cut-off value is in or beyond the last histogram bucket (as it is in your case), and if there is an index on the column, the optimizer will scan the index for the actual minimum or maximum and use that instead. This improves the quality of the estimate considerably.

However, there is a drawback. If the extreme values of the index all belong to deleted (“dead”) table rows, the index scan has to scan way more entries than it bargained for the get the actual minimum or maximum. This has been improved with commit 3ca930fc39c in version 11, which allows such an index scan to “kill” those index tuples, so that the next index scan can quickly ignore them.

I conclude that you must use a version older than v11.

There are two remedies:

Upgrade to v11 or better.
Set autovacuum_vacuum_scale_factor low (0.01 or lower) for that table, so that it is processed by autovacuum more often. VACUUM gets rid of those dead index entries, which will make query planning fast again.


mysql stored procedure create date plus one day but time set to 00:00:00 In a mysql stored procedure, how can i put into a date variable:-

curdate()+interval 1 day but at midnight - e.g. today is 2014-09-14 13:15:23 so the variable will hold:-
2014-09-15 00:00:00

thanks


Substr() with Group by clause I've been working on solving the problem shown here, and a question arose

Here's the schema I created, my statement of the problem, and my test data:

Schema: Students

CREATE TABLE Students (group_id text, sql_quotient float);
INSERT INTO Students(group_id, sql_quotient)
VALUES
    ( 'A', 25 ),
    ( 'B', 30 ),
    ( 'C', 40 ),
    ( 'A', 35 ),
    ( 'B', 20 );

Task: Display max average sql_quotient among all the groups.

group_id is guaranteed to be a Single character of range from A-Z.
Here for group A, avg is 30; for B, avg is 25; and for C, avg is 40;
hence, 40 should be displayed.

I tried the following 2 queries; both give me the right answer.

Query 1

select max(round(b.avg_quotient,2)) as answer
         from
          (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY group_id) as b;

Runtime = 0.002378 sec

Query 2

select max(round(b.avg_quotient,2)) as answer
     from
      (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY substr(group_id,1,1) )as b;

Runtime = 0.000459 sec

The difference - the first query groups the data by group_id; the second by `substr(group_id,1,1).

As the second query applies an additional function, I would expect it to take longer. However, as you can see above, runtime of query no.2 is remarkably less than query no. 1.

My question: why does query 2 have a lower runtime than query 1, even though query 2 has one extra function (substr()).

Notes:

The schema is already defined. I don't know why the datatype of Id is text instead of char(1), and for the purposes of my question it's irrelevant.
I'm looking for the reason for the difference of runtime between these 2 queries, not the another query for same problem.
The Students table is created anew for each run, so this isn't a case of the data having to be read from disk on the first run, and being in memory on the second. To prove this, I ran the queries four more times, running Query 2, then Query 1, then 2 again, then 1 again. The run times were:

Q2, 1st: 0.000493
Q1, 1st: 0.002779
Q2, 2nd: 0.000499
Q1, 2nd: 0.002787


Would the error log for MySQL for the IBM i be in mysql.general_log? I have an IBM i running Zend DBI (Zend Supported MySQL 5.1) that I'm trying to find log information for. I do SELECT * FROM mysql.general_log and get 0 rows.

my.cnf looks like this:

[mysqld]
sql-mode=NO_AUTO_VALUE_ON_ZERO
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=100M
innodb_log_file_size=20M
innodb_log_buffer_size=8M
key_buffer_size=64M
table_cache=256
sort_buffer_size=4M
read_buffer_size=1M
bulk_insert_buffer_size=8M

Process startup looks like this:

  /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/bin/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysqldata --port=3306 --user=mysql --socket=/tmp/mysql.sock --pid-file=/usr/local/mysqldata/zmysql.pid --tmpdir=/tmp

Am I looking in the wrong place for error messages

According to SHOW VARIABLES general_log is OFF and log_error is blank. So the default configuration is to not log anything.


Remove duplicate comparisons in postgres? I have a polygon layer named scale_polygons_v4. I want to get ids of overlapping polygons. I have tried the code below:

select distinct s1.id,s2.id from scale_polygons_v4 s1
inner join scale_polygons_v4 s2
on s1.id!=s2.id and s1.kind=s2.kind and st_overlaps(s1.geom,s2.geom)

The results are correct, but with duplicate comparisons it returns for example:

10029161,10011031
10011031,10029161

There are multiple such cases I only want to get one comparison.

You can use least() and greatest() for this:

select distinct least(s1.id, s2.id), greatest(s1.id, s2.id)
from scale_polygons_v4 s1
  join scale_polygons_v4 s2
    on s1.id <> s2.id
   and s1.kind = s2.kind
   and st_overlaps(s1.geom,s2.geom);

Another option is to do this in the join condition:

select distinct s1.id, s2.id
from scale_polygons_v4 s1
  join scale_polygons_v4 s2
    on s1.id > s2.id
   and s1.kind = s2.kind
   and st_overlaps(s1.geom,s2.geom);


How do I figure TOTAL DURATION for specific group I have a table with CARD # and REDEEMED DATE. I am trying to figure out the total duration (in minutes) for each CARD #. I am using Microsoft SQL Server 2014

You just need to take the difference, in minutes, between the lowest date and the highest date, per card:

SELECT [CARD #], duration = DATEDIFF(MINUTE,
  MIN([REDEEMED DATE]), MAX([REDEEMED DATE]))
FROM dbo.table_name
GROUP BY [CARD #];


How to change a column type from SET to an ENUM I have a table with a SET column type that need to be changed to ENUM type.
Can I do it with a simple ALTER or will this mess up my data?
NB: there are no multiple values (a,b,c) in the table.

Technicaly YES, I just did the test :

mysql> CREATE TABLE my_t (my_c SET("one","two"));
Query OK, 0 rows affected (0.44 sec)

mysql> INSERT INTO my_t VALUES ("one");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO my_t VALUES ("one");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO my_t VALUES ("one");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO my_t VALUES ("two");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM my_t;
+------+
| my_c |
+------+
| one  |
| one  |
| one  |
| two  |
+------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE my_t MODIFY my_c ENUM("one","two");
Query OK, 4 rows affected (1.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_t;
+------+
| my_c |
+------+
| one  |
| one  |
| one  |
| two  |
+------+
4 rows in set (0.00 sec)

You've mentioned it but it will mess if you have multiple values (replace by an empty set in ENUM):

mysql> CREATE TABLE my_t (my_c SET("one","two"));
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO my_t VALUES ("two,one");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM my_t;
+---------+
| my_c    |
+---------+
| one,two |
+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE my_t MODIFY my_c ENUM("one","two");
Query OK, 1 row affected, 1 warning (0.87 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM my_t;
+------+
| my_c |
+------+
|      |
+------+
1 row in set (0.00 sec)

Max.


How can I convert a key in a SQL Server deadlock report to the value? I have a deadlock report that tells me that there was a conflict involving
waitresource="KEY: 9:72057632651542528 (543066506c7c)"

and I can see this:
<keylock hobtid="72057632651542528" dbid="9" objectname="MyDatabase.MySchema.MyTable" indexname="MyPrimaryKeyIndex" id="locka8c6f4100" mode="X" associatedObjectId="72057632651542528">

within the <resource-list> element.
I want to be able to find the actual value for the key (id = 12345, for example). What SQL statement would I need to use to obtain that information?

You have the hobt_id so the following query will identify the table:-

SELECT o.name
FROM sys.partitions p
INNER JOIN sys.objects o ON p.object_id = o.object_id
WHERE p.hobt_id = 72057632651542528

From that you can then run the following statement to identify the row in the table (if it still exists):-

SELECT %%LOCKRES%%,  *
FROM [TABLE NAME] WITH(INDEX(MyPrimaryKeyIndex))
WHERE %%LOCKRES%% = '(543066506c7c)'

Be careful with the above statement however, it will scan the target table so run in READ UNCOMMITTED and monitor your server.

Here's an article by Grant Fritchey about %%LOCKRES%% - http://www.scarydba.com/2010/03/18/undocumented-virtual-column-lockres/

And here's an article from my own blog about using %%LOCKRES%% to identify rows from an extended event:-  https://dbafromthecold.wordpress.com/2015/02/24/identifying-blocking-via-extended-events/


Group replication ports issues What should be the ports for group replication if we are implementing group replication on three different MySQL servers? The my.cnf for the first server is

port=3306
loose-group_replication_local_address= "x.x.x.x:3307"
loose-group_replication_group_seeds= "x.x.x.x:3307,x.x.x.x:3307,x.x.x.x:3307"

For the second server the settings are same and I have changed the IP's in loose-group_replication_local_address accordingly, but I am facing this error when running start GROUP_REPLICATION:

  [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3307'

Does any one know the solution?

In the manuel they advise to use port 33061.
https://dev.mysql.com/doc/refman/5.7/en/group-replication-configuring-instances.html :

  "The recommended port for group_replication_local_address is 33061"


Not a able to kill the DB session in Postgres RDS We are trying to killing  one of DB session but not able to do it. Tried with following command getting "TRUE" after issuing command. Still we can see the session is active.

select pg_terminate_backend(933);

pg_terminate_backend
t (1 row)

session:

933 | -1 days -01:51:38.165893 | test | SELECT col1,col2,col3 FROM test_table "PC0" WHERE ( col1 LIKE $1 ) ORDER BY 3 ASC;

can anyone please help me to resolve this.
We are using Postgres-9.3 RDS

I found the answer to this on stack overflow, basically you don't have rdsadmin privileges so you can only terminate sessions for your own username. https://stackoverflow.com/questions/22713375/permissions-for-pg-terminate-backend-on-amazon-rds


Safely promoting master to slave Previously I've asked the similar question MySql Switching Masters During Failover with log-slave-updates.

Let's say this is our current replication

In addition, I have few application servers. Every app server connects to Master 1 directly in order to INSERT and UPDATE and to Slave[1-3] in order to SELECT.

My question is how exactly can I promote Master 2 to be a real and the only master. Importance notice that Master 2 and all slaves below it are mysql 5.5 and Master 1 is mysql 5.2

When I changed IP of Master 1 to IP of Master 2 in application settings and made deployment, there was a moment when I had UPDATE and INSERTS both on Master 1 and Master 2, which caused "Duplicate entry ..." on Master 2. After that I rebuild the tree and run STOP SLAVE on Master 2 few seconds before the deployment. Of course, I lost some data, that was inserted to Master 1 and not replicated to Master 2.

So, what is the right way to do so without losting data.

In addition, I still don't understand what to run on Master 2 after STOP SLAVE in order to reset "slave settings", it looks like I don't really have to do this, just STOP SLAVE and everything should be working, but just to keep the order I want to reset "slave settings".

When you shifted the IP within your application, any DB Connections that were open at the moment were totally unaware of the move. A quick netstat | grep -i mysql would reveal that on Master1.

You would be better off doing the cutover of the IP as follows:

On Master1

FLUSH HOSTS;
service mysql stop

Change the IP in the App
On all web servers, service httpd restart

In order to safeguard the app from having to edit it for the sake of assigning a new IP, try using a DB VIP instead.

For example:

Master1 is 10.1.2.30
Master2 is 10.1.2.40
Use 10.1.2.70 as the DBVIP

Here is what you can setup

On Master1, run ip addr add 10.1.2.70/24 dev eth1
Use 10.1.2.70 in your app

Then, when it is time to cutover, do the following

On Master1

FLUSH HOSTS;
service mysql stop
ip addr del 10.1.2.70/24 dev eth1

On Master2

ip addr add 10.1.2.70/24 dev eth1
SHOW SLAVE STATUS\G and make sure all final SQL statements from Master1 executed

On all web servers, service httpd restart

That way, a cutover would not involve editing any part of the app. Please notice that I did not mention running STOP SLAVE anywhere because this would allow any final SQL statements to flow over from Master1 to Master2 once mysql is stopped on Master1.


Postgres: querying jsonb array with an array My products table has a jsonb column called 'specs'.

This specs hash will often have an array field, let's call it 'spec_options'.

When querying this table, I need to find any product that has certain spec options.

Let's say we have a product with these spec_options:

['b', 'c', 'd']

And the user wants to find products with these spec_options:

['a', 'b', 'c']

The query I need should return find above product.

This where clause worked just fine when the field wasn't an array:

'["a", "b", "c"]'::jsonb ? (specs->>'spec_options')

But now that it's an array, I think I need to use the |? operator, and I can't work out how to get the right side of this operation into the correct format. (I think it needs to be text[] but I'm not sure.)

You can use the @> operator.

From the docs:

  @>    Does the left JSON value contain within it the right value?

So in your case it would look something like this:

select * from products where specs @> '{"spec_options": ["a", "b", "c"]}';


What is the difference between start and enable in the Oracle RAC srvctl? In the Oracle 12c documentation it was not clear what is the difference between the srvctl start and srvctl enable commands for services associated with a database.
In the view dba_service a database that has a service already started, has the column ENABLED with NO, what I noticed is that when a RAC service has already started and you try to enable it, it informs you that it is already enabled.
srvctl enable service -d mydb -s myservice
PRCC-1010: myservice was already enabled
PRCR-1002: Resource ora.mydb.myservice.svc is already enabled

Enable means the service can be used and it will autostart when the cluster is restarted.
Also, you can have a service enabled, but with the current status stoped.
If you change the service to disable, you can't start the service.


Create Table If Not Exists Hanging I am still new to using databases, but currently I am using a mysql database and attempting to make my bash script unbreakable because it might be used in several different environments (dev, qa, etc). I know it will run correctly without this one line.

CREATE TABLE IF NOT EXISTS backupfiles (fileName VARCHAR(20), archiveId VARCHAR(500), checkSum VARCHAR(100), glacierVault VARCHAR(100), timeStamp date);

I also know that if I put that exact line into my database when that table does not exist then it runs perfectly and if it does exist already it gives me a warning, but does not break.

+-------+------+------------------------------------+
| Level | Code | Message                            |
+-------+------+------------------------------------+
| Note  | 1050 | Table 'backupfiles' already exists |
+-------+------+------------------------------------+

When I run the line above and there is no table named backupfiles it works perfectly, but when there is it hangs(when I say hangs it sits there and does nothing) 95% of the time and 5% of the time works. Has anyone run into a problem like this? I am using AWS RDS(Amazon Web serices Relational Database Service) and the mysql server is  5.5.27

Here is all of my code that I relates to the mysql database

mysql -h portal-rds -u $user --password=$mysqlpw <<QUERY_INPUT
CREATE DATABASE IF NOT EXISTS $DATABASE;
use $DATABASE;
CREATE TABLE IF NOT EXISTS backupfiles (fileName VARCHAR(20), archiveId VARCHAR(500), checkSum VARCHAR(100), glacierVault VARCHAR(100), timeStamp date);
INSERT INTO backupfiles VALUES ('$archive_file_name', '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());
COMMIT;
QUERY_INPUT

I find this troubling for two reasons
REASON #1
This could be a bug that never got squashed out of MySQL 5.5.17

Nov 08, 2011 : http://bugs.mysql.com/bug.php?id=63144 (Status is Needs Triage on MySQL 5.5.17)
Mar 21, 2011 : http://bugs.mysql.com/bug.php?id=60563 (MySQL 5.5.10)
Jun 23, 2006 : http://bugs.mysql.com/bug.php?id=20662

REASON #2
You are trying to acquire a metadata lock on an InnoDB table in an RDS environment? Ouch !!!
Possible Workaround
Instead of trusting the metadata locking in RDS, ask the information_schema if the table exists already. Suppose the table is mydb.backupfiles. Run this query:
SELECT COUNT(1) FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='backupfiles';

If the answer comes back 0, run CREATE TABLE backupfiles .... Otherwise, don't run it.
Possible Solution
Upgrade to the Latest MySQL (5.6.12) or the Latest MySQL5.5 (5.5.31). Hopefully, this issue was solved in a more recent version
Give it a Try !!!
UPDATE 2013-07-11 15:16 EDT
Here is a rewrite of your bash code to check if the table already exists
    echo "CREATE DATABASE IF NOT EXISTS $DATABASE;" > /tmp/runthis.sql
    echo "use $DATABASE;" >> /tmp/runthis.sql
    SQLSTMT="SELECT COUNT(1) FROM information_schema.tables"
    SQLSTMT="${SQLSTMT} WHERE table_schema='${DATABASE}' AND table_name='backupfiles';"
    TABLE_EXISTS=`mysql -h portal-rds -u $user --password=$mysqlpw -ANe"${SQLSTMT}"`
    if [[ ${TABLE_EXISTS} -eq 0 ]]; then
      SQLSTMT="CREATE TABLE backupfiles (fileName VARCHAR(20),"
      SQLSTMT="${SQLSTMT} archiveId VARCHAR(500), checkSum VARCHAR(100),"
      SQLSTMT="${SQLSTMT} glacierVault VARCHAR(100), timeStamp date);"
      echo "${SQLSTMT}" >> /tmp/runthis.sql
    fi
    SQLSTMT="INSERT INTO backupfiles VALUES ('$archive_file_name',"
    SQLSTMT="${SQLSTMT} '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());"
    echo "${SQLSTMT}" >> /tmp/runthis.sql
    echo "COMMIT;" >> /tmp/runthis.sql
    mysql -h portal-rds -u $user --password=$mysqlpw < /tmp/runthis.sql


MySQL Import Super Slow I'm running MySQL on a HyperV virtual machine. The host has 384Gb of RAM and is nowhere near being fully committed. I've tried giving the VM 128G of RAM and 8 CPU and I've tried 2 CPU's and 32GB of RAM. Same symptoms on both configs. The server simply acts like it's not doing anything. It uses only 1-2% of CPU and only 3GB of RAM. Is there some config I should change?

My command to import:

MySQL.exe -u username -p databasename < filename.dump

I've even tried importing several dumps at the same time and still the server uses little to no resources.

If there is less than a few GB of data in all the tables and indexes, then mysqld may use only 3GB of RAM.  Look on disk in the datadir; see what the total size if that dir tree is.  That will be an approximation of how much RAM might be used.
A well tuned mysql will run with under 5% CPU.  That includes tuning of the VARIABLES, adding necessary INDEXes, and formulating queries optimally.  (Plus other things.)

I once made a 1-line change to a SELECT and it changed from 50% CPU all the time (there were 2 cores) to under 2%.

You say the import is slow?  How big is the import file?  How long would it take to read that file?  The import will be only a small factor slower than the time to read the file.

Check the import file -- does it have INSERT statements that each have lots of rows?  That is critical to the speed of import.

Do you have lots of FOREIGN KEYs?  Lots of secondary indexes?  These may impact the speed.

The import is single-threaded.  Multi-threading it (which is not easily done) is mostly useless, since you are probably nearly I/O-bound.

If it has been running for a long time without filling up much disk, then connect and do SHOW PROCESSLIST; to see what it is doing.  That might be a useful clue.


Generating Backups of Databases I would like to create a backup of four databases on one SQL Server 2000 instance at the exact same moment in time.

How can I achieve it?

Creating different jobs with the same schedule date and time does apparently produces a difference in the moment when the backup is taken.

You will need to have the Full Recovery Model enabled on all four databases.

Now you will issue a full backup on all four databases, let's say ten minutes before the point in time you require.

Now you will do a transaction log backup of each database, at a point in time immediately after the exact moment you need.

On the other end, you will restore each full backup WITH NORECOVERY, and then transaction log backup, with the RESTORE LOG WITH STOPAT switch to specify the exact moment in time you need, as a DATETIME.

Remember to RESTORE WITH RECOVERY at the end to bring them into active use.


Flatten recursive hierarchy I have the following recursive query:

SELECT SYS_CONNECT_BY_PATH(object_name, '\') AS path
FROM user_group_nodes
START WITH parent_id = '2892107544AA2278166C1AA8D123E761'
CONNECT BY parent_id = PRIOR object_id
ORDER SIBLINGS BY object_name;

Which returns the following sample dataset:

\UserGroupA\UserGroupAA\UserGroupAAA\JaneDoe
\UserGroupA\UserGroupAA\UserGroupAAA\UserGroupAAAA\UserGroupAAAAA\JohnDoe

Based on the above sample dataset, I need to insert the following records into a table:

Group            User
---------------  ---------------
UserGroupA       JaneDoe
UserGroupAA      JaneDoe
UserGroupAAA     JaneDoe
UserGroupA       JohnDoe
UserGroupAA      JohnDoe
UserGroupAAA     JohnDoe
UserGroupAAAA    JohnDoe
UserGroupAAAAA   JohnDoe

Can someone provide direction on how to accomplish this? I'm leaning towards using a cursor, but I'm hoping there is another option that performs better since I need to do this across 10,000 users and 1,500 groups.

I've knocked up a MODEL clause to do this.

Sample data:

create table sampledata
(
  dat varchar(255)
);

insert into sampledata values ('\UserGroupA\UserGroupAA\UserGroupAAA\JaneDoe');

insert into sampledata values ('\UserGroupA\UserGroupAA\UserGroupAAA\UserGroupAAAA\UserGroupAAAAA\JohnDoe');

Query:

WITH PIVOTEDDATA AS(
SELECT   * -- RN, POSITION, DAT
  FROM   SAMPLEDATA
   MODEL
    RETURN UPDATED ROWS
    PARTITION BY(ROWNUM RN)
    DIMENSION BY (0 POSITION)
    MEASURES     (DAT ,NVL(LENGTH(REGEXP_REPLACE(DAT,'[^\\]+','')),0) DATA, REGEXP_SUBSTR(DAT,'[^\\]+',1,NVL(LENGTH(REGEXP_REPLACE(DAT,'[^\\]+','')),0)) USERNAME )
    RULES
    (DAT[FOR POSITION FROM  1 TO DATA[0]-1 INCREMENT 1] =
      REGEXP_SUBSTR(DAT[0],'[^\\]+',1,CV(POSITION)),
    USERNAME[FOR POSITION FROM  1 TO DATA[0]-1 INCREMENT 1] = REGEXP_SUBSTR(DAT[0],'[^\\]+',1,NVL(LENGTH(REGEXP_REPLACE(DAT[0],'[^\\]+','')),0)))
)
SELECT *
FROM PIVOTEDDATA;

There will be an easier way to do this, but I needed some MODEL practice and my brain hurt thinking about how to do the unpivoting and keeping the last record.

SQL Fiddle.

If you've never used MODEL before, my apologies.


Need to replace only first occurrence of string I hope I worded the question right. Here are the specifics:

This is regarding a MySQL database. I've inherited several hundred posts with custom fields and a unique excerpt. At this point I think I can solve the problem by inserting the <!--More--> tag into each post at a specific point. I need to do a search and replace with the following parameters:

Search each post_content field for the first occurrence of </em>
Insert the <!--More--> tag right after the </em>

Unfortunately each post has multiple <em> tag pairs, and if a post has more than one <!--More--> tag in it, WordPress just ignores all of them. So I need to so some sort of search and replace, but I have had no success after two days of trying every possible code snippet, SQL query and plugin I could find.

This may come across as silly, but if the CMS ignores subsequent  tags, you shouldn't really care if it adds one after every  closing tag. Sure it may make the posts slightly larger than they should be, but since they're just going to be ignored, it seems silly to spend two days trying to not do that when at the end of the day it doesn't really matter whether you do or not.

That said, I am not a MySQL guy, but looks like this is as decent an approach as any, from this StackOverflow answer:

UPDATE wp_posts
 SET post_content = CONCAT(REPLACE(LEFT(post_content,
   INSTR(post_content, '')+4), '', ''),
   SUBSTRING(post_content, INSTR(post_content, '') + 5))
 WHERE INSTR(post_content, '') > 0;

or the (slightly simpler):

UPDATE wp_posts
 SET post_content = CONCAT(LEFT(post_content, INSTR(post_content, '')-1),
                           '',
                           SUBSTRING(post_content, INSTR(post_content, '')+ 5))
 WHERE INSTR(post_content, '') > 0;

Before you do that you may want to check to see how many rows this will affect:

SELECT COUNT(*)
  FROM wp_posts
  WHERE INSTR(post_content, '') > 0;

You'll probably want to add a WHERE clause to only identify those posts that actually contain an  tag (and you may need to define the requirements for what to do in those cases).


Implementation of JDBC DataSource in MySQL Connector/J 8 In the MySQL Connector/J 8.0 Developer Guide (JDBC driver for MySQL), I have not found any mention of an implementation for the DataSource interface in JDBC.
To quote the DataSource Javadoc:

An alternative to the DriverManager facility, a DataSource object is the preferred means of getting a connection.

So I would expect an implementation to be provided with Connector/J. For example, for this Postgres JDBC driver, I can find the implementation of DataSource in the class PGSimpleDataSource, as discussed on Stack Overflow.
➥ Is there no implementation of DataSource for Connector/J? Or did I miss it?
There is this page, 6.1 Driver/Datasource Class Name. But, despite the title, mentions only the driver class name, not the class name for a DataSource implementation.

The driver has com.mysql.cj.jdbc.MysqlDataSource (there is also a ConnectionPoolDataSource and an XADataSource implementation). The actual implementation of MysqlDataSource uses the JDBC driver directly, similar to what DriverManager would do.
The MySQL Connector/J hardly mentions data sources. In fact, the only reference I could find to MysqlDataSource was in Changes in the Connector/J API. I'm not sure if it is a conscious choice by the MySQL documentation team, but it usually makes little sense to use MysqlDataSource directly in your code (it doesn't provide connection pooling), and MysqlConnectionPoolDataSource and MysqlXADataSource are not for direct usage nor are they a connection pool (instead they serve as a factory for connections for a Java EE data source that provides connection pooling and/or distributed transactions).
A lot ways of using JDBC involves using third-party data sources providing a connection pool (eg HikariCP, DBCP, c3p0), and those usually use DriverManager directly (though some also allow you to provide a data source implementation), or through a data source of a JavaEE/JakartaEE application server. It is usually better to use those than to rely on the data source implementation of a driver.


MySQL: Delete a record from all tables in one shot I have a MySQL database, with one table that has a primary key, f_id that is referenced in multiple table as a foreign key.

I want to delete all records where f_id is a specific value from all referenced tables in a single batch.

I am attempting to delete a specific f_id record in the primary table, however I cannot since there are records present in the referenced tables containing that f_id value.

How can I delete all related records at once without having to hand-write a query for each and every related table?

The automated way of doing so is by defining foreign keys on your tables, with a DELETE CASCADE in your case. This way, a DELETE on the master table will propagate and delete matching rows from all derived tables.

You will have to use InnoDB tables for that.

I assume your tables do not have foreign keys at the moment, otherwise you wouldn't ask. In which case you may try using triggers, see this post for an example. Triggers are bad for performance, mind you.

Those who do not define foreign keys tend to work out all DELETEs in application code.


Name already been used by an existing constraint after renaming the table and creating a new one I would like to know why, if I alter a table name to different name and then create a table with the same name, I am getting the error "name already been used by existing constraint".

This is the create table code

   create table abc
  (
       num number (4),
       name varchar2 (50),
      constraint abc primary key (num, name)
 );

This is the alter table code

 alter table abc rename to cba;

This is  the code to recreate the table

   create table abc
  (
       num number (4),
       name varchar2 (50),
      constraint abc primary key (num, name)
 );

After renaming the table, rename the constraint and the index as well:

alter table cba rename constraint abc to cba;
alter index abc rename to cba;


How to print text in select query instead of NULL value in DATE field in postgresql? I have a table test as follows :

create table test (id int, enroll date)

I have inserted some rows as follows :

insert into test values (1,'2012-01-02');
insert into test(id) values (2);

Now I want to select all rows and want to print 'Error' where the enroll field that has date datatype has NULL values.
I wrote the following query which gives me error :

select case when enroll is null then 'Error' else enroll end as Enroll from test;

My desired output is :

2012-01-02
Error

Thanks in advance for help.

You need to cast it to string first. Check the below query;

select coalesce(to_char(enroll, 'YYYY-MM-DD'),'Error') as formatted from test;


Cascading Inserts in MySql I have a users table that has a one to one relationship to a user_preferences table (primary foreign key user_id). When a new row is added to the users table (new id added), is there a way to setup the relationship between the users_preferences table so that a row with the new user id is also added to it?

Calling this a way to "setup the relationship" isn't quite technically accurate, but you can do this with an AFTER INSERT trigger on the user table.  If all of the other columns in user_preferences have appropriate default values defined, and creating a "matching" row is all you need, then:

mysql> CREATE TRIGGER user_ai AFTER INSERT ON `user` FOR EACH ROW INSERT INTO user_preferences (user_id) VALUES (NEW.user_id);

http://dev.mysql.com/doc/refman/5.5/en/triggers.html

Triggers that execute more than one statement require you to temporarily change the DELIMITER from the normal semicolon to something else, and use an outer BEGIN and END block.  This also works with a single-statement trigger, but isn't strictly necessary, so the format above will work in this case.


Inserting into Microsoft SQL Server Table I have been using SQL for a bit now but have only recently tried out Microsoft SQL server Express. I have created a database called test and a table called idk. then I have tried to run the following query

USE test;
INSERT INTO idk(Id, Name) VALUES ("1","Name")

But that just says

  Msg 207, Level 16, State 1, Line 2
  Invalid column name '1'.
  Msg 207, Level 16, State 1, Line 2
  Invalid column name 'Name'.

Any help for using SQL with Microsoft SQL server will be appreciated.

Use single quotes (') instead of double quotes (") for your values, such as:

USE test;
INSERT INTO idk(Id, Name) VALUES ('1','Name');

Using double quotes is one way to let SQL Server know you are specifying a column name instead of a value. Be careful about the data type for the ID. If it is an integer, SQL Server will convert the value in the query from a string to integer for you but it will be better to not have it as a string unless the data type is a string. For the ID, if it's an integer data type (int, bigint, smallint, or tinyint), then change the query as follows to remove the quotes from the integer value:

USE test;
INSERT INTO idk(Id, Name) VALUES (1,'Name');


MySQL - maximum of sum over different months with ties over multiple years This question was inspired by this one [closed] and is virtually identical to this one but using different RDBMS's (PostgreSQL vs. MySQL).

Suppose I have a list of tumours (this data is simulated from real data):

CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);

INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');

You want to find out which particular tumour was most common in a given month - so far so good!

Now, you will notice that for month 1 of 2017, there is a tie - so it makes no sense whatsoever to randomly pick one and give that as the answer - so ties have to be included - this makes the problem much more challenging.

The correct answer is:

  Year    Month  Tumour count      Type
  2017        1             1    Cervix  -- note tie
  2017        1             1      Lung  --   "   "
  2017        2             3      Lung
  2018        1             5    Cervix
  2018        2             3      Lung

A further bonus would be to have the month name appear as text rather than an integer.

I have a solution but it's quite complex - I'd like to know if my solution is optimal or not. The MySQL fiddle is here!

My attempt to solve this is as follows. I would appreciate any advice on how this query could be improved:

SELECT
  t3.c_year AS "Year",
  t3.c_month AS "Month",
  t3.il_mc AS  "Tumour count",
  t4.ill_nat AS "Type" FROM
(
  SELECT c_year, c_month, il_mc FROM
  (
    SELECT
    c_year,
    c_month,
    MAX(month_count) AS il_mc
  FROM
    (
      SELECT nature_of_illness as illness,
        EXTRACT(YEAR  FROM created_at) AS c_year,
        EXTRACT(MONTH FROM created_at) AS c_month,
        COUNT(EXTRACT(MONTH FROM created_at)) AS month_count
      FROM illness
      GROUP BY illness, c_year, c_month
      ORDER BY c_year, c_month
    ) AS t1
  GROUP BY c_year, c_month
  ) AS t2
) AS t3
JOIN
(
SELECT
  EXTRACT(YEAR FROM created_at) AS t_year,
  EXTRACT(MONTH FROM created_at) AS t_month,
  nature_of_illness AS ill_nat,
  COUNT(nature_of_illness) AS ill_cnt
FROM illness
GROUP BY t_year, t_month, nature_of_illness
ORDER BY t_year, t_month, nature_of_illness
) AS t4
ON t3.c_year = t4.t_year
AND t3.c_month = t4.t_month
AND t3.il_mc = t4.ill_cnt

And it does give the correct result, as can be seen in the fiddle here!


Restore a clustered SQL database from a specific time I have a 2-node clustered Lab SQL Server and I need to restore the database to the previous day state. This is my first time to try a restore in a cluster. I have a full recovery model and since this environment is rarely used, I have a once a week full backup complemented by 2 differential backups and daily log backups; i.e. Full=Mondays at 1am, Diff=Wed and Fri at 1am, and Log=daily at 9pm.

Now my questions is - if I wish to restore the database on the Monday state, can I simply restore the full backup on Monday (1am), then restore log backup on same day (9pm), then recover the database?

Do I have to worry about restoring any of the master, model or msdb system databases?

Thanks in advance.

Regards,
Trev

There isn't a difference between a restore on a standalone instance and one on a Failover Cluster Instance. There IS a difference if it is in an Availability Group.

Yes you can restore the FULL from Monday and the LOG from 9pm.

Whether or not you have to restore the system databases is dependent upon what you are needing to recover. If it's just data in the user database, then simply restoring the user database is sufficient.


get DDL from Mongo database I haven't done much with MongoDB. But in trying to understand the data that we do have in MongoDB, it makes sense to start with looking at the DDL in the database. What is the simplest way to pull the DDL out of the MongoDB database. I can log directly onto the Linux server and run commands, plus I have the Robo 3T client.
Thanks

MongoDB doesn't have Data Definition Language. In place of "rows" and "columns" it has "objects" and "attributes". An object is typically not defined or constrained by the storage engine as it would be in a relational database.
The data is not defined by the thing that keeps it but rather by the thing that reads and writes it. For this reason, you can store data about an "Employee" object in the same collection where you also store data about a "Transaction" object. The storage engine won't stop you unless you tell it to.
For MongoDB the Data is Defined by the application, not the database. So you need to go to the application to understand what the equivalent of "DDL" is for the application. Anything you infer from the database is anecdotal and not rigorously constrained.


Replication between two different SQL Server versions We have mirroring configured (transactional replication) in our primary and secondary SQL Servers, which are running on Windows 2008 R2 SP2 OS with 2008 R2 SP3 as the SQL Server version.

If we first upgrade the secondary server, we have to figure out how the synchronization between the two servers has to be configured, as for some time they will be running with two different versions of SQL Server.

We want to upgrade them to SQL Server 2014.

It sort of depends whether you're using Database Mirroring, or Transaction Replication (although I suppose you could be using both)

Assuming Transaction Replication:

This is supported, so long as you stick to certain rules, as described in the MSDN article Upgrade Replicated Databases

As long as your Distributor is the same version or greater than your Publisher, and a Subscriber is within two versions of the Publisher, which fits in your case so you're OK. That MSDN article provides instructions.

This is similar to the question/answer asked a couple of years back, but the MSDN article has been updated since.

If you mean Database Mirroring:

I'm assuming you don't mean this, as this question has been asked before in a number of ways, but I'll add the answer in for completeness.

This can be done, but it is supported strictly for minimising downtime during upgrades (which it seems is what you are doing, so that's OK).

Have a look at MSDN - Minimize Downtime for Mirrored Databases When Upgrading Server Instances for information about this (and a walk-through).


Updating values in table based on ranges defined in another table I have two tables definitions and history with the following schema:

create table definitions (
    id UNIQUEIDENTIFIER,
    revision INT,
    majorVersion INT
  );

create table history (
   id UNIQUEIDENTIFIER,
   revision INT
   );

I want to update table history with another column majorVersion where the value ranges are defined in table definitions. For example, if records in definitions look like:

+--------------------------------------+-----------+---------------+
|                  id                  |  revision |  majorVersion |
+--------------------------------------+-----------+---------------+
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         2 |             1 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         4 |             2 |
+--------------------------------------+-----------+---------------+

Then the updated table history should look something like this:

+--------------------------------------+-----------+---------------+
|                  id                  |  revision |  majorVersion |
+--------------------------------------+-----------+---------------+
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         1 |             1 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         2 |             1 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         3 |             2 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         4 |             2 |
+--------------------------------------+-----------+---------------+

Here is a SQL Fiddle with a more number of rows.
The table definitions can contain potentially thousands of different id and multiple majorVersion. Subsequently, table history can contain can contains close to a million rows.

I want to make the query as fast and optimized as possible. One possible solution is to use something like this:

update history
set majorVersion = (
  select top 1 majorVersion
  from definitions
  where definitions.id = history.id
  and definitions.revision >= history.revision
  order by definitions.majorVersion
  )
where history.majorVersion = 0;

But the problem with this is that we are querying rows for every row in table history (which can be very large compared to definitions). Any suggestions on how to improve upon this?

One of two solutions

solution 1

If you are able to split the work in two update statements, first update all the matching revisions

UPDATE h
set h.majorVersion = d.majorVersion
FROM dbo.history h
INNER JOIN
dbo.definitions d
ON d.id = h.id
and d.revision = h.revision;

Then use a CTE and windowing functions to update the majorVersion's that are still 0 on the updated table

;WITH CTE
AS
(
SELECT ID,revision, MAX(majorVersion) OVER (PARTITION BY Value2) as majorVersionUpdated,majorVersion
FROM
(
    SELECT ID, majorVersion,revision
        ,COUNT(case when majorVersion = 0 then NULL else majorVersion END) OVER (ORDER BY ID DESC,revision desc) AS Value2
    FROM dbo.history
) a
)

UPDATE CTE
SET majorVersion = majorVersionUpdated
WHERE majorVersion = 0;

SELECT * FROM dbo.history;

An index like this could help

CREATE INDEX IX_id_revision
on dbo.history(id,revision)
include(majorVersion)

Result

SELECT * FROM dbo.history;

id                                 revision majorVersion
9F717823-B9CA-4C7B-97F9-7770AAAFB468    1   1
9F717823-B9CA-4C7B-97F9-7770AAAFB468    2   1
9F717823-B9CA-4C7B-97F9-7770AAAFB468    3   1
9F717823-B9CA-4C7B-97F9-7770AAAFB468    4   2
9F717823-B9CA-4C7B-97F9-7770AAAFB468    5   2
9F717823-B9CA-4C7B-97F9-7770AAAFB468    6   3
9F717823-B9CA-4C7B-97F9-7770AAAFB468    7   3
546EF185-54AC-4AF8-82C6-61EFA3202353    1   1
546EF185-54AC-4AF8-82C6-61EFA3202353    2   2
546EF185-54AC-4AF8-82C6-61EFA3202353    3   2

SQL Fiddle

solution 2,  still two updates but with a CROSS APPLY and a self join. The self join is not ideal.

First update all the matching revisions

UPDATE h
set h.majorVersion = d.majorVersion
FROM dbo.history h
INNER JOIN
dbo.definitions d
ON d.id = h.id
and d.revision = h.revision;

Then do a self join to update the other majorVersions to the next one that is not 0

UPDATE H
SET h.majorVersion = h2.majorVersion
FROM dbo.history h
CROSS APPLY(
SELECT MIN(CASE WHEN h2.majorVersion = 0
           THEN NULL
           ELSE h2.majorVersion END) as majorVersion
FROM
dbo.history h2
WHERE h2.id = h.id
AND h2.revision >= h.revision
) h2
WHERE h.majorVersion = 0;

SELECT * from dbo.history;

SQLFiddle

You would have to add BEGIN TRANSACTION ... COMMIT TRANSACTION to the updates if they have to be executed in one batch. The fastest way will depend on additional factors, such as your indexes & data, more than an example can show. YMMV


Can shrinking temporary TEMP tablespace in Oracle cause any harm? I have a massive TEMP tablespace that is 1TB large. It consists of 30datafiles, each one is like 31GB in size.

Entire TEMP tablespace has a size of 1TB, while used size is only ~600MB.

I want to shrink tablespace and I am dilema whether to use option

a) to shrink each datafile individually:

    ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp01.dbf' KEEP 10MB
    ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf' KEEP 10MB
    ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp03.dbf' KEEP 10MB

...

or

b) to shrink entire tablespace:

    ALTER TABLESPACE TEMP SHRINK SPACE KEEP 300MB;

c) shrink without KEEP (so it gets shrunk to minimum)

   ALTER TABLESPACE TEMP SHRINK SPACE;

I am on Oracle Enterprise 12.2.

I am wondering:

Which option is the 'best' ?
Why wouldn't one just always use option C ?
Why would one want to KEEP anything in TEMP temporary tablespace at
all at shrinking (except that bare system required minimum)?
Can any harm be done with shrinking TEMP tablespace? I will run it
    on Productional database (out of working hours tho when noone is connected to the DB) and I have to be sure.

I am running on Oracle Enterprise 12.2.

all the three options would work, but would prefer to use first or the second one in production environment to avoid queries failing post activity due to not enough temp tablespace. Would recommend identifying the usage history pattern using queries similar to the one defined in this blog and then use that for your keep size.
https://zukus.blogspot.com/2016/06/temp-tablespace-usage-history.html


Creating oracle Database instace We need to create a oracle database instance similar to customer environment. What are all the parameters needs to be considered other than similar version of software.

Like Character set, Multibyte Or Single Byte and other Database, Instance, Session level parameters which are to be considered.

What happen if a Dump Successfully imported into a Database instance with different NLS parameters, Will it be having any Side effects?
Is there a standard way of creating a replica of customer instance in our local?

Oracle 'takes care' of the conversion between different NLS settings. As long as both NLS settings can store the same characters the there should be no problem. It is the same as the difference between the database and a client.

If for example the database of the customer is in UTF-8 and you create a database in ISO8859-P1 then you will loose (some) characters with accents and from non-western alphabets. The other way around gives no problem.


How to determine if a column is the first part of an index? Is there anyway to tell with SQL if an indexed column is the first part of an index.

The closest thing looks to be information_schema.columns.  But that seems to only tell me if it's a PRI or MUL key (not if it's the only member of that index or what position its in if it were a composite key).

Use INFORMATION_SCHEMA.KEY_COLUMN_USAGE to retrieve the position of columns in your key. The position of column in a key is the ORIDINAL_POSITION column of KEY_COLUMN_USAGE.

See more information from MySQL::The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table


Is the speed of a PostgreSQL SELECT adversely affected by too many indexes on the table? I have read that when having a lot of indexes on a database It can seriously hurt the performance but in the PostgreSQL doc I can't find anything about it.

I have a very big table with something like 100 columns and a billion rows and often I have to do a lot of searches in a lot of different fields.

Does the performance of the PostgreSQL table will drop if I add a lot of indexes (maybe 10 unique column indexes and 5 to 7 three column indexes)?

EDIT: With performance drop I mean SELECT performance; the database will only be updated once per month so UPDATE and INSERT speed is not an issue.

No, the query performance will not be affected, or not very much.  Indexes are updated on DML statements (and TRUNCATE) while they may or may not be used when executing a query.

The decision whether they will be used are made by the planner.  With very many indexes present I can imagine that the planner spends more time on choosing the usable ones, but I would expect the difference to be small.  (Normally, planning is faster that retrieving rows.)

Note that usually a few indexes will speed up a lot of different queries.  This may not be the case if you filter on many of your columns.

Also note that in a lot of cases (and without knowing your table definition one cannot decide whether your case falls into 'most' or not) having so many columns reflects having a less-that-optimal schema design.


Deadlock on partitioned table we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing.

Our setup is like this: A big data warehouse table gets data added to it every night. This is done through an SSIS Package, and in parallel. The data can be separated by column "country", and we run the same package for several countries at the same time to speed up things. One package alone simply does not use all resources of the machine.

With a non-partitioned table, we saw deadlocks once every few weeks. Not too bad, but too often to ignore.

We decided to partion the table by country, hoping to get rid of the deadlocks. I followed what I think is best practice, with an empty partition to the left and right, and one partition for each country. The country is encoded in an NVARCHAR(3) datatype. We recreated the clustered index using the partitioned column, and finally set lock escalation for the table to auto.

I can verify that all partitions are loaded with data belonging to it only, so all seems to be working fine.

However, we still keep getting deadlocks. I would have expected that my "write operation" (quotes will be explained next) would each focus on just one partition, and the parallel running SSIS packages could not interfere.

First question: Am I right in this expectation? Should I not be getting deadlocks now?

I think I have an answer to why they still appear: the "write operation" mentioned in quotes is somewhat complicated, it is an own SSIS component written in c# before my time with my employer. Each package creates and writes ALL DATA into it's own temp table, and then executes a

MERGE WHEN MATCHED UPDATE WHEN NOT MATCHED INSERT

from it's temp to the final table.
When building this MERGE-Statement, the component uses a construction to compare NULLs, i.e.

WHERE @given IS NULL and col is NULL OR @given is not null and col = @given

and it does this also for the country-column, which is part of the clustered index and partition key.

I suspect, that by having a query relating NULL to the partition key, I trigger a "search and lock every partition" operation. Is this the case, does something like this exist? Or does the description point to another reason why we still get deadlocks?

Thanks a lot for reading and your suggestions!

There are a number of "features" of merge statements that can lead to issues like this. For a relatively comprehensive list of issues take a look at the following link:

Use Caution with SQL Server's MERGE Statement

I've seen similar issues occurring myself, the solution was either to re-write the code to avoid MERGE altogether or in once case where it was unavoidable, dropping indexes on the target table before the MERGE and then restoring them afterwards resolved the issue.


Return List of Database Users and Server Logins How can I return a complete listing of all server logins and all database users across my enterprise? Need to include the remote server name, database name, AD account and SQL users.

For such purpose i use this login audit script which gives me detailed information of logins and users attached to them along with database and server level permissions. Overall can be helpful to you :

if exists (select * from tempdb.sys.all_objects where name like '%#Login_Audit%')
drop table #Login_Audit
create table #Login_Audit
(A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))
go

insert into #Login_Audit  (A,B,C,D)
SELECT
[Security Report] = '-----SQL SERVER SECURITY AUDIT Report-----','-----','-----','-----'
go
insert into #Login_Audit  (A,B,C,D)
SELECT
[Login count] = 'Total Count of Login','Windows User','SQL server User','Windows Group'
go

insert into #Login_Audit
select a,b,c,d from
(select count(name)a from sys.syslogins where name not like '%#%') a, -- total count
(select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, -      -for login is windows user
(select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login
(select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;
go

insert into #Login_Audit (A,B,C,D)
SELECT
[sysadmin_server role] = '-- SYSADMIN SERVER ROLE ASSIGN TO---',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit  (A,B,C,D)
SELECT
[Sys Admin role] = 'Login name',' Type ',' Login Status ',''
go
insert into #Login_Audit (A,B,C)
SELECT a.name as Logins, a.type_desc, case a.is_disabled
when 1 then 'Disable'
when 0 then 'Enable'
End
FROM sys.server_principals a
INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id
WHERE b.role_principal_id = 3
ORDER BY a.name
go

insert into #Login_Audit  (A,B,C,D)
SELECT
[Fixed_server role] = '-- FIXED SERVER ROLE DETAILS --',' ----- ',' ----- ',' ----- '
 go
insert into #Login_Audit  (A,B,C,D)
SELECT
[Fixed_server role] = 'ROLE name',' Members ',' Type ',''
 go

 insert into #Login_Audit (A,B,C)
  SELECT c.name as Fixed_roleName, a.name as logins ,a.type_desc
 FROM sys.server_principals a
 INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id
 INNER JOIN sys.server_principals c ON c.principal_id = b.role_principal_id
--WHERE a.principal_id > 250
 ORDER BY c.name
 go

 insert into #Login_Audit  (A,B,C,D)
 SELECT
[Fixed_database_Roles] = '-- FIXED DATABASE ROLES DETAILS --',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit  (A,B,C,D)
SELECT
[Fixed_database_Role] = 'Database Name','Role Name','Member','Type'
go
insert into #Login_Audit exec master.dbo.sp_MSforeachdb 'use [?]
SELECT db_name()as DBNAME, c.name as DB_ROLE ,a.name as Role_Member, a.type_desc
FROM sys.database_principals a
INNER JOIN sys.database_role_members b ON a.principal_id = b.member_principal_id
INNER JOIN sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name <> ''dbo''and c.is_fixed_role=1 '
go
------------ used is_fixed = 0 for non fixed database roles(need to run on each database)
 insert into #Login_Audit  (A,B,C,D)
 SELECT
 [NON_Fixed_database_Roles] = '-- NON FIXED DATABASE ROLES DETAILS --',' ----- ',' ----- ',' ----- '
 go
 insert into #Login_Audit  (A,B,C,D)
 SELECT
 [Non Fixed_database role] = 'Database Name','Role Name','Member ','Type'
  go
  insert into #Login_Audit exec master.dbo.sp_MSforeachdb 'use [?]
  SELECT db_name()as DBNAME, c.name as DB_ROLE ,a.name as Role_Member, a.type_desc
  FROM sys.database_principals a
  INNER JOIN sys.database_role_members b ON a.principal_id = b.member_principal_id
  INNER JOIN sys.database_principals c ON c.principal_id = b.role_principal_id
  WHERE a.name <> ''dbo''and c.is_fixed_role=0 '
  go

   insert into #Login_Audit  (A,B,C,D)
   SELECT
    [Server_Level_Permission] = '-- SERVER LEVEL PERMISSION DETAILS --',' ----- ',' ----- ',' ----- '
   go
   insert into #Login_Audit  (A,B,C,D)
   SELECT
   [Server permission] = 'Logins','Permission Type',' Permission_desc ','Status'
   go
   insert into #Login_Audit
   SELECT b.name,a.type,a.permission_name,a.state_desc
   FROM sys.server_permissions a
   INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_id
 --INNER JOIN sys.server_principals b ON b.principal_id = b.role_principal_id
   WHERE b.name not like '%#%'
   ORDER BY b.name
   go

   insert into #Login_Audit  (A,B,C,D)
  SELECT
  [DATABASE_Level_Permission] = '-- DATABASE LEVEL PERMISSION DETAILS ----',' ----- ',' ----- ',' ----- '
  go

  insert into #Login_Audit  (A,B,C,D)
  SELECT
  [DB permission] = 'Database Name','Login Name',' Permission ','Status'
  go

  insert into #Login_Audit
  exec master.dbo.sp_MSforeachdb 'use [?]
  SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_desc
  FROM sys.database_permissions a
  INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id
  where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and   b.name not like   ''%#%'''
  go

   insert into #Login_Audit  (A,B,C,D)
  SELECT
  [Password_ Policy_Details] = '--- PASSWORD POLICY DETAILS ----',' ----- ',' ----- ',' ----- '
   go

   insert into #Login_Audit  (A,B,C,D)
   SELECT
   [Policy] = 'Users','type',' Policy status','Password policy status'
   go

   insert into #Login_Audit
   SELECT a.name AS SQL_Server_Login,a.type_desc,
   CASE b.is_policy_checked
   WHEN 1 THEN 'Password Policy Applied'
   ELSE
   'Password Policy Not Applied'
   END AS Password_Policy_Status,
   CASE b.is_expiration_checked
   WHEN 1 THEN 'Password Expiration Check Applied'
  ELSE
 'Password Expiration Check Not Applied'
 END AS Password_Expiration_Check_Status
 FROM sys.server_principals a INNER JOIN sys.sql_logins b
 ON a.principal_id = b.principal_id
 where a.name not like '%#%'
 order by a.name
  go

  insert into #Login_Audit  (A,B,C,D)
  SELECT
  [Orphan_Login_Details] = '--- ORPHAN LOGINS ----',' ----- ',' ----- ',' ----- '
  go

  insert into #Login_Audit  (A,B,C,D)
  SELECT
   [orphan logine] = 'Logins Name','ID','',''
   go

   insert into #Login_Audit (A,B) exec sp_validatelogins
   go

   insert into #Login_Audit  (A,B,C,D)
   SELECT
   [Orphan_USERS_Details] = '--- ORPHAN USERS----',' ----- ',' ----- ',' ----- '
   go
   insert into #Login_Audit  (A,B,C,D)
    SELECT
    [orphan users] = 'User Name','','  ',''
    go
    insert into #Login_Audit (A)
    select u.name from master..syslogins l right join
    sysusers u on l.sid = u.sid
    where l.sid is null and issqlrole <> 1 and isapprole <> 1
    and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
    and u.name <> 'system_function_schema'and u.name <> 'sys')

    insert into #Login_Audit  (A,B,C,D)
    SELECT
    [Database_Owner_details] = '--- DATABASE OWENER DETAILS----',' ----- ',' ----- ','   ----- '
    go
    insert into #Login_Audit  (A,B,C,D)
    SELECT
    [DB owner] = 'Database Name','Owener name','  ',''
    go
    insert into #Login_Audit (A,B)
    select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc
     go

    select * from #Login_Audit


Try looking here!

  The TIMESTAMP data type is used for values that contain both date and
  time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to
  '2038-01-19 03:14:07' UTC.

You are exceeding the permitted ranges! Also, should activity_time be a duration of some sort? Maybe a begin_timestamp and end_timestamp might be an idea?

You should prefer TIMESTAMP to DATETIME, from here (Date and Time Type Storage Requirements), a timestamp without fractional seconds (i.e. most of them)  requires only 4 bytes (as opposed to 5, but hey!). Also, 99% of dates that we deal with on a day to day basis will be between 1970 and 2038.

This part of the manual should help with initialisations.

Following the small oversight so kindly pointed out by @ypercubeᵀᴹ I had a look at this issue - i.e. of '0000-00-00 00:00:00' being a special "zero" value.

The fruits of my efforts are available here.

DDL:

CREATE TABLE `user_login_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `event_type` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  -- `activity_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  `test_time1` TIMESTAMP NULL DEFAULT NULL,  -- WORKS
  -- `test_time2` TIMESTAMP DEFAULT '0000-00-00 00:00:00', -- FAILS
  -- `test_time3` TIMESTAMP NOT NULL DEFAULT 0,  -- FAILS
  -- `test_time35` TIMESTAMP NULL DEFAULT 0,
  `test_time4` TIMESTAMP NULL DEFAULT NULL,
  `test_time5` TIMESTAMP NOT NULL DEFAULT '1970-01-01, 00:00:01', -- FAILS with time = 00:00:00
  -- `test_dtime6` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  `test_time6` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  -- `test_time7` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
  -- 'test_time8` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  PRIMARY KEY (`id`),
  INDEX my_index (created_at) -- if the TABLE is CREATEd, the INDEX is!
) ENGINE=MyISAM;

It appears to me that there is a problem/bug with MySQL - in that it appears to confuse this special "zero" with the NULL value! It won't work with DATETIME either! I looked at the documentation again and can't appear to find why many of the failing constructs in my DDL won't work. There was a time when MySQL didn't work with multiple TIMESTAMPs - maybe this is relic of that epoch? I even tried SQLFiddle - which will build the schema but then fails - not sure why! I hope this clarifies matters unless @ypercubeᵀᴹ has a better suggestion! I found this helpful.