What is the correct way to make an exact copy of a database with pg_dump? Basically, I'm trying to make a SQL dump that will make an exact copy of a database: user defined functions, relationships, constraints, tables, data, etc.
Is this doable with pg_dump or does psql need to be used instead?
I've been using pg_dump -U user -cOx database > pg.sql, but it isn't entirely clear to me if it captures everything.
I was looking at all of the pg_dump flags here:
https://www.postgresql.org/docs/11/app-pgdump.html
The only one I can think that might do it, because it doesn't really say explicitly, is the -s --schema-only flag, which excludes data. I'm fine with that and running a second pg_dump for the data, but I primarily want to make sure I am backing up the user defined functions, relationships, etc.

I'm not a PostgreSQL expert but I think their documentation is rather clear and I believe the command you're using is pretty much all you need (you might need to specify the password with a flag - not sure), unless you do want to do it in two steps with --schema-only and then with --data-only:

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.

pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall.

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql.

This tutorial on pg_dump and pg_dumpall should help get you going too.
Finally, best thing you can do is run the scripts after they're generated, in a test database and spot check for the different things you'd expect to get created (Table, View, Primary Key, Foreign Key, other constraints, etc) before you make any drastic changes to the original database it came from.


Using * to enforce the need to update both tables together I am a Java Programmer and definitely NOT a DBA. I am just an "accidental helper" to a DBA as we try to troubleshot an inherited VB.NET application situation. The guy that wrote this MS SQL got fired after a 12 year run! The current DBA is a bit mystified as to what he was trying to do here? A clue might be in the note on the former REMMED out code that apparently suddenly stopped working after several years?

Any ideas as to what this technique is trying to do as to "enforce the need to update both tables together"? The new DBA seems a bit mystified by what he was trying to do?

This is a copy of the Notation and the SQL

-- the following intentionally uses * to enforce the need to
-- update both tables together

/* this stopped working!!  WHY??
Insert Into MAIN
Select *
From IPR_CIP
Where IPR_CIP.CASE_NUM Not In
  (Select CASE_NUM From MAIN); */

(The is the current deployed code:)

Insert Into MAIN
Select *
From IPR_CIP
Where IPR_CIP.CASE_NUM In
   (Select IPR_CIP.CASE_NUM
    From IPR_CIP
    Left Outer Join MAIN On IPR_CIP.CASE_NUM = MAIN.CASE_NUM
    Where MAIN.CASE_NUM IS NULL);

I'm not entirely sure what he is trying to do either. This query is inserting into MAIN rows from IPR_CIP that don't already exist in MAIN. It depends on the two tables always having the same number of columns and corresponding columns having compatible data types. It would be much better to explicitly list the columns you are updating in MAIN and the columns you are selecting from IPR_CIP. The subquery is redundant and can be moved up into the parent. Here's the TSQL code I would use.

INSERT INTO MAIN (col1, col2, col3, ... , coln)
SELECT (col1, col2, col3, ..., coln)
FROM IPR_CIP
LEFT OUTER JOIN MAIN On IPR_CIP.CASE_NUM = MAIN.CASE_NUM
WHERE MAIN.CASE_NUM IS NULL


Migrating Pure PHP Site with Associated Database To New Server First, let me say I'm not a DBA or network admin, but have moved large scale CMS websites from host to host. But, I have never moved a pure PHP-coded database driven website from one host to another.

What I have done is moved the entire site including the level above 'html' with the include folder with config.php, database.php, main.php etc. I have imported database(s) and have proper credentials for access.

But, when I try to load a file from the site (ex. accounts/index.html) I just get partial code returned with no css styles or anything that controls the site.

Could someone point me in a direction to be able to start to sync this site back? Like I said, getting raw output with viewing files and can't even get CSS to load site colours and styles.

I'm a newb when it comes to this aspect of things and appreciate any guidance.

PHP is not running on your new server. Set it up correctly and be sure the database has been migrated and is working.

Try adding a simple page with:

<?php
phpinfo();
?>

To your webserver root folder and open it. If you do not get proper info but the script on the page the PHP scripts are not being processed.


Datatype for phone number: VARCHAR, INT or BIGINT? So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition:

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

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

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

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

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

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

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

How would you handle a phone number with an extension, such as "+1-000-000-0000 ext 1234" ?

Note, the "+" indicates international dialing rules should be applied; so from North America, the system automatically knows "011" in front of international calls, etc.

Also, what about phone numbers such as "1-800-DBA-HELP"?

I would typically store phone numbers as text. Having said that, it really depends how critical your phone number column is. If you are running automated dialers from that column, then you'd really want to ensure that only numbers are included, and the data represents well-formed phone numbers.

You could have separate columns for extensions, and phone numbers that have text, such as the "1-800-DBA-HELP" example I provided.


What's the maximum number of partition per table I can/should have in MySql 8? I have around 1T (1,000,000,000) records of products belong to 500,000 accounts. Each product record have the account_id and since I always query within a single account context (there are no cross-account queries) I thought of creating a partition per account to improve performance.

Is it even possible to create 500,000 list partitions in mysql8?
Will it required changing in the number of open files the process required?
If it is possible, is there a reason for me not to go this path?

The maximum number of partitions allowed in MySQL 8 for InnoDB tables is 8192 (from https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html). This includes subpartitions.

In general, partitions do not on their own improve performance, but there are a some scenarios where they can help:

Secondary indexes are local to the partition, so if you for example bulk insert data one partition at a time (or in order), it makes it more likely the secondary indexes fit into the buffer pool which can improve insert performance.
For partition pruning. This can for example mean that MySQL effectively uses two indexes for the same table without doing an index merge.
Managing logical grouped data such as moving all data that belongs to one partition to another table (for example to move it to another MySQL instance) or to delete all data in a partition.

One thing to be aware of if you have many partitions for a table is that by default each partition gets each own tablespace file (.ibd file). So if you have 8192 partitions, the table will end up using 8192 files.


What to do when I get “mysqld dead but subsys locked” in my amazon ec2 micro instance? I have an Amazon ec2 micro instance that holds a WordPress site. Since yesterday, my mysql db keep crashing saying:

[ec2-user@ip-xxx-xx-xx-xxx ~]$ sudo service mysqld status
mysqld dead but subsys locked

I tried restarting the mysqld but after every 5 mins it keeps crashing with the same message - "Error establishing a database connection"

This is what my DB log file reads -

160123 05:44:41 mysqld_safe mysqld restarted
160123  5:44:43 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.46) starting as process 7928 ...
160123  5:44:44 [Note] Plugin 'FEDERATED' is disabled.
160123  5:44:46 InnoDB: The InnoDB memory heap is disabled
160123  5:44:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160123  5:44:46 InnoDB: Compressed tables use zlib 1.2.8
160123  5:44:46 InnoDB: Using Linux native AIO
160123  5:44:46 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160123  5:44:46 InnoDB: Completed initialization of buffer pool
160123  5:44:46 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160123  5:44:46 [ERROR] Plugin 'InnoDB' init function returned error.
160123  5:44:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160123  5:44:46 [ERROR] Unknown/unsupported storage engine: InnoDB
160123  5:44:46 [ERROR] Aborting
160123  5:44:46 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
160123 05:44:46 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

The common solution that found out there when I Googled for a solution for the issue - mysqld dead but subsys locked, is to create a swapfile. I did exactly as most of the solution suggest but since I'm not well versed with UNIX, I don't know what I have done is right or not.

I have a swap space of 1MB.

[ec2-user@ip-xx-xx-xx-xxx /]$ free
             total       used       free     shared    buffers     cached
Mem:       1019452     613820     405632         76      19124      64348
-/+ buffers/cache:     530348     489104
Swap:      1048572      46604    1001968

Or, Is there any other solution for this issue. Kindly help.

Fatal error: cannot allocate memory for the buffer pool

You have only 1GB of RAM?  What is the value of innodb_buffer_pool_size; it should not be more than, say, 50M.

What changes did you make to my.cnf?  There may be other things to decrease.  (Or you could get more RAM.)

More

If you have more than just MySQL in that 1GB, it may really be impractical to have such a tiny VM.  Either split into to VMs, one for MySQL and one for app, or get get a bigger VM.


WMI error: 0x800700a4 - The @wmi_query could not be executed in the @wmi_namespace provided When trying to create a WMI alert in SQL Server, I received this error on 7 of the ~50 instances I was applying the change to:

SQLServerAgent Error: WMI error: 0x800700a4.
Msg 14511, Level 16, State 1, Procedure sp_verify_alert, Line 304 [Batch Start Line 557]
The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.

The 7 instances that got this error were all SQL 2008 R2 and SQL 2012, a mix of Developer and Standard editions. I did not get the error on SQL 2014 or 2016 instances.

I was able to solve the issue in a couple, but not all 7 of the cases. See my answer below.

Fix #1
In some cases I was able to solve the problem by restarting the WMI service "Windows Management Instrumentation", and then re-running the script. This did not help in every case however.

Fix #2
On both of the SQL 2008 R2 servers, I noticed that the WMI service ("Windows Management Instrumentation") was running as Local System, so I checked to see if it had login rights on the SQL instance. It did indeed already have a login "NT AUTHORITY\SYSTEM", with no particular rights granted to it. I gave it sysadmin temporarily, and was able to run my script successfully. I then removed sysadmin. and my WMI alert continued to work.

Fix #3
Another possible reason is the account that runs the Windows service "Windows Management Instrumentation" is disabled as a SQL login. (If you are running SQL 2012+, look for the login 'NT SERVICE\winmgmt'). (Source: Blog by 'rahmanagoro' )


Single table or multiple table design when records have a clear distinction but are queried together? Let's say I have the following types of recurring data (all types periodically come in).

Post (Title, Summary, Text, Timestamp)
Image (URL, Caption, Timestamp)
Event (StartDate, EndDate, Title, Description, Location)

As the style of the data suggests, we can split them into their own separate tables, Post, Image and Event respectively. However the majority of the queries on these data are of the "timeline" variety (Return me all posts, images and events within a given timeframe sorted by time), though there are also some queries that work on a single type alone.

Someone suggested that we combine all three into a single table and encode the unique attributes into a json field (We're using Postgres 9.3+) rather than use a UNION of the three tables.

So according to that model, we'd have

Info (Type, Timestamp, Attributes)

where Attributes is a JSON field which will have different keys based on the Type value.

Which is the better approach here?

P.S. This is a simple example for explaining the question but in reality there are closer to 9 different "types" of data and the number of unique attributes is much larger. We might need to join on one of those unique attributes as well in the future (not sure if Postgres supports this).

I won't put all of them together. Even if you query them together, it may occur that your application will be able to post multiple images per post or multiple events per post or multiple posts per event.

In this case you would be able to save space. The JSON may be a good idea, but it would be better (in view of indices) to use multiple tables. This way you can provide indices which may improve the query process.

Another idea could be to use permutation. Which means you have a global table which holds many different types with the same structure. For example:

CREATE TABLE types(
    id int,
    type nvarchar(50),
    property1 int,
    property2 nvarchar(50)
)

This way you can store a value with Type = 'Image' which holds a property1 (order number) and a property2 (a path). And additionally you can hold an event like Type = 'Event' which holds the date in property1 and the name in property2. Just as an example.


MySQL Views Authorization Questions:

Is there a way, using SQL, to identify how many views are in the database per user?
How can you authorize/deny users access to delete views created by another user?

Here is the definition of information_schema.views:

mysql> show create table information_schema.views\G
*************************** 1. row ***************************
       Table: VIEWS
Create Table: CREATE TEMPORARY TABLE `VIEWS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VIEW_DEFINITION` longtext NOT NULL,
  `CHECK_OPTION` varchar(8) NOT NULL DEFAULT '',
  `IS_UPDATABLE` varchar(3) NOT NULL DEFAULT '',
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Here is how you can count the views per database per user

SELECT COUNT(1) ViewCount,table_schema,definer
FROM information_schema.views
GROUP BY table_schema,definer;

I am not that sure about access/denial of views. In the information_schema.views table, there is field called SECURITY_TYPE (which have values DEFINER and INVOKER).

If a View has SECURITY_TYPE as INVOKER, everybody and his grandmother can SELECT from that View. My guess is that you would have to set the SECURITY_TYPE to DEFINER to restrict everyone from running a SELECT from that View. Of course any user with SUPER privilege and the definer can a SELECT on that View.

Changing the SECURITY_TYPE is a little painful.

You can mysqldump all views. Here is a post I made back on July 26, 2011 : Modify DEFINER on Many Views. @DTest had an even more concise answer using ALTER VIEW.

Edit the output file in vi or some other editor to change the SECURITY TYPE of any View you wish. Then, jus reimport the view from that text file.

Give it a Try !!!


Oracle MODEL clause giving unusual output This is my table structure:

Name         Null Type
------------ ---- -----------
PAID_MM           VARCHAR2(2)
GA                NUMBER
SA                NUMBER
MA                NUMBER
TOTAL_AMOUNT      NUMBER
AMT_VAR           NUMBER

and this is my data:

PAID_MM  GA     SA     MA     TOTAL_AMOUNT  AMT_VAR
01       98.5   48.16  161.47  308.13          0
02       116.34 53.14  115.46  284.94          0
03       288.03 25.31  136.04  449.38          0
04       138.41 118.84 148.5   405.75          0
05       75.69  190.74 51.7    318.13          0
06       451.18  0     214.84  666.02          0

I want calculate the AMT_VAR as (TOTAL_AMOUNT of current month - TOTAL_AMOUNT of previous month / TOTAL_AMOUNT of previous month) *100.

I am using Oracle MODEL clause as this:

SELECT
  PAID_MM,
  TOTAL_AMOUNT,
  TRUNC(AMT_VAR*100, 2) AS VARIAN
FROM
  AGNT_PAID_SUMM
MODEL RETURN UPDATED ROWS
DIMENSION BY (PAID_MM)
MEASURES (AMT_VAR, TOTAL_AMOUNT)
RULES
(
AMT_VAR [PAID_MM] = ((TOTAL_AMOUNT[CV(PAID_MM)] - TOTAL_AMOUNT[CV(PAID_MM)      -1])/TOTAL_AMOUNT[CV(PAID_MM) -1])
)
ORDER BY PAID_MM

and I am getting the following output:

PAID_MM  TOTAL_AMOUNT VARIAN
01       308.13       NULL
02       284.94       NULL
03       449.38       NULL
04       405.75       NULL
05       318.13       NULL
06       666.02       NULL
07       339.47       NULL
08       445.86       NULL
09       548.52       NULL
10       363.94       NULL
11       362.8        -0.31
12       409.37       12.83

This looks quite weird to me. Can somebody help me understand why it's not working for all the rows except the last two ones?

I am using Oracle 12c.


SQL Server query across several linked servers - abysmal performance There are three different databases on a three separate linked servers SQL0, SQL1 (hosting 2 dbs), SQL2 (hosting 1 db). SQL0 acts as a reporting server and will host all the databases in question (method TBD - maybe replication maybe sth else), but as the data will be old for some audit purposes (<24H) it will still be necessary to run some queries on live data.

Two setups:

Setup 1. Localhost\SQL0 - linked to SQL1 and SQL2 (each using their respective sa credentials to connect)

Setup 2. SQL1 linked to SQL2 (again, using sa credentials)

Query in question joins (for now) 8 tables across those three databases. In the future there will be another server, another database and several more joined tables in the query, ran regularly by agent and results emailed. It usually returns less than 100 rows, but is limited using TOP 10 while in development.

Why this query ran on SQL2 takes 3s or less to execute, but 105s when ran on SQL0? As long as it's just network traffic I'm willing to disregard the 30x longer execution, but if it's impacting linked servers performance I need to find the solution stat.

All servers are on same LAN, but SQL2 is in another building connected via VPN.

Everything is in SSMS, on a machine connected to same network as all others, no views, no linked servers to linked servers or other "complications"; I have access to all servers as sa.

Query plans (actual), SQL2 and SQL0 respectively:

and

The poor performance is because you are using linked server.  There is a constant difference of opinion between developers and database admins.  Database admins know linked server connections are slow.  Developers want them because they solve problems for them.  I have been both a developer and a database administrator and I remember as a developer, I pushed to get linked server added.  As a database admin for the past 20 years, I have trouble shot poor performance thousands of times to find a linked server connection is the cause.

Use transactional replication to get real time data to your report server.  This will eliminate the 24 hour delay and it will eliminate your need to use linked server.  You will also have a performance gain on your source servers because read only report queries won't be competing with live transactions that run your company.


MySQL MONTH Clause: Want to get Month Name instead of number My query is

SELECT MONTH(`hire_date`) AS month FROM `emp_tbl`

I'm getting output in Java fx

if(resultSet.next()){
int month = resultset.getInt('month');           // output is like 11

}

Then I used swtich statement to get month in string. I want output like 'November' instead of 11. I dont want to use switch statment.
Is there is any other solution to get month name as a string instead of a number?

Any other solution besides SWITCH statement?

SELECT MONTHNAME('2018-11-03');

... should give 'November'. So use MONTHNAME instead of MONTH.

Another option is the DATE_FORMAT function. This should give the same result as above:

SELECT DATE_FORMAT('2018-11-03', '%M');

Use %b instead of %M to get abbreviated month names (Jan, Feb etc).


RMAN Backup to Disk and to Cloud Parallel I'm currently working on a backup solution for my company.
One part of the solution would be to write the backup to disk and also send it to an oracle cloud archive storage for long term storage.

In the documentation I only found a way to store it in two directories on local disk via:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/%U', '/u02/%U';

Is it possible to define the first backup location to be local disk, the second in the cloud and let rman backup to those two in parallel mode?

I appreciate any help.

Thanks in advance, jan

Your local backup uses a disk channel. Cloud backup uses and SBT channel.
The same backup can not be duplexed to mixed kind of channels, see below.

Duplexing Backup Sets

  RMAN can duplex backups to either disk or tape, but cannot duplex
  backups to tape and disk simultaneously.

Even if you used an SBT channel for the local backup, that would require a media manager library that is different from the cloud SBT media manager library, which you again can not mix.

So the answer is no.

Just create some local disk backups, then backup the backupsets to the cloud.

About Multiple Copies of RMAN Backups

  Back up your files as backup sets or image copies, and then back up
  the backup sets or image copies with the RMAN BACKUP BACKUPSET or
  BACKUP COPY OF commands


MySQL - How can a deadlock b caused by the same transaction? I am experiencing a deadlock scenario, and it's possible that I'm just not reading this correctly but here are the details, my interpretation is at the bottom.

The players:

Transaction 1: An ETL that is deleting records in batches.

Transaction 2: A store front.

The offending table is as such:

CREATE TABLE items
(
  id BIGINT PRIMARY KEY,
  secondary_key BIGINT UNIQUE
)

T1 is attempting to delete records by the secondary_key, meanwhile T2 is attempting to update the secondary_key on an existing record

So T1 is doing:

DELETE FROM items where secondary_key IN (1,2,3,4,5,6);

And T2 is doing:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- This is set at the beginning of a larger transaction

UPDATE items SET secondary_key = 7 where id = 4;

The deadlock log that I the get is as follows:

Transaction 1

*** (1) TRANSACTION:
TRANSACTION 42065889, ACTIVE 25 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 62416 lock struct(s), heap size 7577808, 9631608 row lock(s)
MySQL thread id 177980, OS thread handle 47144643110656, query id 45451329  preparing
DELETE FROM items
      WHERE secondary_id IN (
        SELECT
          li.id
        FROM staging.legacy_items li
        WHERE change_operation = 'D'
        AND li.id BETWEEN 9082453 AND 29059282
      )
      AND legacy_id IS NOT NULL

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table `items` trx id 42065889 lock_mode X waiting

Transaction 2

*** (2) TRANSACTION:
TRANSACTION 42065914, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
34 lock struct(s), heap size 3520, 19 row lock(s), undo log entries 10
MySQL thread id 177524, OS thread handle 47137066059520, query id 45451721 updating
UPDATE `items` SET `items`.`secondary_id` = 29059300 WHERE `items`.`id` = 15558171

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table `items` trx id 42065914 lock mode S locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table `items` trx id 42065914 lock_mode X locks rec but not gap waiting

Result

*** WE ROLL BACK TRANSACTION (2)

SO.. to me it looks like:

Transaction 2 gets a shared lock on single record
Transaction 1 takes an exclusive lock on range of records
Transaction 2 tries to take an exclusive lock on the single record that it already has a shared lock for.

I can't understand why Transaction 2 wouldn't be able to get an exclusive lock on the record that it already has a shared lock for.

Am I interpreting this completely wrong?

Thanks!

I suggest you add code to make the product more robust.  Check for deadlocks after every statement.  When you encounter such, replay the statement(s) in the transaction.

That may be cheaper than figuring out all the possible causes of deadlocks (you have shown one, but there could be more).  Later you can worry about the extra unique key.  But leave the checks for deadlocks in place even if they never happen again.

Adding to FOR UPDATE to the SELECT.


How do I horizontally partition an oracle database table, and should I? We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment.

Create tenant specific aggregate tables, and query from those.
Horizontally partition the data based on tenant.

The first option means that for every new tenant that comes onboard, we'll need to create a new set of tables. This isn't that difficult, as a new tenant signing up comes with a few weeks notice, and a lack of reporting will be revealed early on if we forget.

Partitioning the data to me sounds like a better approach, since we're not duplicating the data. We don't have to rely on a process to transfer new data to the aggregate tables.

I'm wondering which of these options would be better, if anyone has had similar experiences before. Does partitioning the data actually help? Or would it be not so much different from leaving all the data in one 'space'?

And, with Oracle 10g, how does one horizontally partition data? If I had the following table:

TABLE Transaction(id, tenant_id, a, b, c, d)

We'll be migrating to Oracle 11g soon, so any differences in partitioning across versions would be appreciated.

(Note: I tried to use a partitioning tag, but not enough rep, if someone else could add a tag that'd be cool)

To answer your second question first: yes you should partition. Oracle's query optimizer has a feature called partition elimination, which will check the predicate for the partition and only execute the SQL on the appropriate partitions.

Partitioning also leaves all the data in one space. Conceptually, think of it as many tables of identical structure, with an implicit UNION ALL between them if you were to do a SELECT from the entire table. Except "under the hood" Oracle sorts the actual rows into the right "table" based on the criteria you specify. Any rows that come in that don't match any of the criteria, go into what's known as the "default" partition.

For what you want to do, a "range partition" might be a good approach (so you can add more tenants later), e.g.:

create table transaction (id, tenant_id, a, b, c, d)
partition by range(tenant_id)
partition p_tenant1 values less than (2) tablespace ts_tenant1
partition p_tenant2 values less than (3) tablespace ts_tenant2
partition p_tenant3 values less than (4) tablespace ts_tenant3
partition p_tenantd values less than (MAXVALUE) tablespace ts_default;

Then later

alter table transaction
add partition p_tenant4 values less than (5) tablespace ts_tenant4;

This will create something that looks and behaves just like a normal table, but actually rows where tenant_id=1 will be in a partition in tablespace ts_tenant1, and queries will ignore all other partitions. Queries across the entire table can run in parallel on each partition. If tenant_id=4 in this scenario, the row will live in ts_default unless you add the new partition as shown, but the INSERT won't be rejected because there's no partition for it!

FWIW At my site we use partitioned tables in our 40Tb DW, you don't need to worry about this approach scaling or performing, if you choose your partitioning strategy well (e.g. you could partition on tenant_id then subpartition on month perhaps), create the right indexes, and so on.


How to recover/restore corrupted Innodb data files? A while ago, my Windows 7 system on which a MySQL Server 5.5.31 was running crashed and corrupted the InnoDB database. The weekly backup that's available does not cover all the tables that were created in the meantime, therefore I would endeavor to recover as much as possible from the data. Right after the crash, I copied the whole data folder of MySQL to an external drive. I would like use this as the starting point for my rescue attempts.

In the following I'll describe the steps of my (not yet convincing) rescue attempt so and would be thankful for any comments or guidance on how to improve it:

I've now done a fresh install of MySQL Server 5.5.31 on another PC
I stop the MySQL service with "net stop MySQL" at the command prompt.
I already figured that I need to adjust the size of the innodb log file in the my.ini file as it deviates (256 MB) from the default value (19MB).
In the my.ini, I also set innodb_force_recovery=6
In the data folder of the fresh installation, I overwrite the ibdata1, iblogfile0, iblogfile1 files with those recovered from the crashed machine. I also copy the relevant database (UPDATE: and the mysql) folders into here (NOT the standard mysql, test and performance folders).
I start the MySQL service with "net start MySQL".
I go into MySQL Workbench, open my server instance, go to Data Export, basically leave the default settings, and have every table of my databases exported as an individual dump file. I also set stored procedures to be dumped. Otherwise I do not change the default settings there.
I start the dump process; it makes its way through 43 out of 195 tables. Of these 43,

some cannot be recovered yielding an error "mysqldump: Got error: 1146: Table '...whatever...' doesn't exist when doing LOCK TABLES",
but many can. I assume that when the dump does not yield any error, the table's data is non-corrupted.
Then, after the 44th, all the other table dumps fail as it is reported that the server cannot be connected to anymore:
"mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect
Operation failed with exitcode 2"
These errors then go on for all the remaining tables from the 44th to the 195th.
For the 44th table itself, the error is the following: "mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table ...table 44... at row: 57". So it seems that for this table corruption is or begins at row 57.

Now to my questions:

Why is the connection breaking down given that innodb_force_recovery is set to 6?
How to proceed? I worked out what the 44th table was where the connection was lost and can try to resume the process from the 45th table. But isn't there a better way to do it?
Once the data has been copied and the server restarts well, should I just try a dump of each table or what alternatives are there?

Thanks.

UPDATE: Additional notes for my later reference
- When re-creating stored routines backed up using SHOW CREATE PROCEDURE ... and SHOW CREATE FUNCTION ..., they must be imported using  DELIMITER //
(create procedure code of procedure 1)//
(create procedure code of procedure 2)//
DELIMITER ;

Check the MySQL error log.  It looks like trying to read table 44 is actually crashing the server.

"Lost connection to MySQL server during query" is (or should be) heart-stopping time for a MySQL DBA because it often means that whatever your query just did has actually crashed the server.

The subsequent messages seem to bear this out:

mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when
trying to connect
Operation failed with exitcode 2

A quick test of mysqldump on Windows 7 against a MySQL server that is not running returns exactly that same error and exits with %ERRORLEVEL% set to 2.  The nature of error 10061 can be found with the perror utility.

C:\>perror 10061
Win32 error code 10061: No connection could be made because the target machine actively refused it.

...so, check the MySQL error log.  What's likely happening is that you're hitting corruption severe enough that in spite of innodb_force_recovery, the MySQL Server is crashing.  I will then usually restart on its own, but this takes time so may not complete before mysqldump gets an IP connection refused on the subsequent connection attempts.  This should all show up in the error log.

  I assume that when the dump does not yield any error, the table's data is non-corrupted.

Incorrect assumption.  If the dump does not yield any error, it could also mean that that innodb_force_recovery allowed the server to retrieve some of the data.  A "successful" dump of a table only means it's not corrupted so severely that innodb_force_recovery can't survive it.

  1 (SRV_FORCE_IGNORE_CORRUPT)

  Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

  http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Again, you should see chatter from InnoDB in the MySQL error log.

Final suggestion, don't use workbench.  Use the mysql command line client to explore the server schemata and use mysqldump directly from the command line to try to restore individual schemas or individual databases.

update/additional:

  how do you recover stored procedures? I get an error because they access corrupt tables

It shouldn't be because the procedures access corrupt tables, because stored procedures and functions aren't validated against their referenced tables in the way views are (drop a table that a view references, and then SHOW CREATE VIEW doesn't work -- the same isn't the case with procs or functions)... MySQL doesn't care whether tables referenced by stored procedures and functions even exist (except at runtime)... however, unlike view definitions, which are stored in individual files, the definitions of procedures and functions are stored in the proc table in the mysql schema, which is a MyISAM table... so I would expect that you'd see error messages referencing that table and CHECK TABLE mysql.proc; and REPAIR TABLE mysql.proc; might get you going again.  Note that the version of MySQL Server that you're using for your recovery needs to be the same release series (e.g. 5.5) or the table definition of mysql.proc will be wrong... that shouldn't be the issue here, since if I read the question correctly, the crashed server and the recovery server are both 5.5.31.

If you can SELECT * FROM mysql.proc then you can retrieve the definitions that way if for some reason the SHOW CREATE PROCEDURE statement doesn't work.

  And what options for a (partial) rescue are there for the tables that yielded an error in the dumps?

It depends on the nature and scope of the errors encountered.  One option, discussed here, involves repeated INSERT ... SELECT ... LIMIT ... OFFSET, extracting blocks of rows from readable pages into a MyISAM table (because you can't write to an InnoDB table while innodb_force_recovery is on).  There's also the Percona Data Recovery Tool for InnoDB, though I have never had occasion to use it.


The following system error occurred: No mapping between account names and security IDs was done While trying to Process a cube i go this error

"Error  133 The following system error occurred:  No mapping between account names and security IDs was done.       0   0"

i know its some user which is available in the roles have been removed from active directory.

the annoying part in this is the error doesn't indicate which user has been removed from active directory, our SQL is setup as windows authentication.

and i have to go and check one by one.

is there a way to identify the user, so i remove it and continue with the processing

from the SQL management studio, connect to Analysis services and go to Roles, under the roles, the user that has been removed from active directory will appear in question marks "????????????????" , remove him and reprocess the cube


Move SQL Server 2008 database from machine A to B with no downtime I got a bit of a problem. I have a SQL Server database in box A which has approximately 60gb of data. I need to move this to a new machine but I cannot have downtime at all. Every second that goes by there are new data in the db so we cant do it at some random time in the middle of the night.

Is there any way to achieve this? I am pretty much able to do whatever I want so any suggestion will be useful.

Database Mirroring (SQL Server 2005+) would allow as little as 20 seconds of downtime.  This is likely the fastest way of moving a database from machine A to machine B.

You can setup mirroring during the day with no downtime, then cutover at the time of your choosing.  As long as the clients are programmed to retry whatever operation they are doing at the time of cutover, there will be no data loss (if you do the client correctly).


Unix timestamp vs timestamptz index performance I'm new to the world of DB admin and have a large table (2 billion rows) of cryptocurrency tick data for multiple currencies in a Postgres database. Most of my queries will involve selecting a moving time window for each currency individually - for this, I currently have an index on my "timestamp" and symbol columns.
Querying appears to be pretty slow, around 100 seconds to query the last 2 days of data for 1 symbol (~130,000 rows) (I checked with explain analyze and the composite index is being hit)
Here's the kicker - I have the timestamps stored as Unix timestamps as int8 e.g. 1611751286078083
Will converting these to timestamptz, storing these in a new column and then using that as the index be worthwhile/ speed up query? I read that it's bad practice to create a new column with data that is available in another column already.
If so, is it likely that adding this new column will break my existing (constantly running) INSERT script that only inserts on the columns currently defined?
tldr;
Is an index on timestamptz noticeably more performant than on Unix timestamps stored as int8.
Index Scan using huobi_timestamp_symbol_idx on huobi  (cost=0.58..438627.67 rows=177735 width=48) (actual time=0.041..101131.528 rows=488785 loops=1)
  Index Cond: (("timestamp" > '1611751286078083'::bigint) AND (symbol = 'YFII'::text))
  Buffers: shared hit=68973 read=219478
  I/O Timings: read=99426.796
Planning Time: 13.925 ms
Execution Time: 101179.934 ms

I have 1 index on (timestamp desc, symbol) and the primary key is on (timestamp, market) and am using Postgres 12.3 hosted on AWS.

To answer your basic question: No. timestamptz is stored as 64 bit integer quantity internally (same as int8). An index on it performs identically to one on  a bigint (int8) column - when used correctly. Related:

Ignoring time zones altogether in Rails and PostgreSQL

If in doubt, go with timestamptz. It's built for the purpose. The only argument in favor of Unix timestamps would be that you already have them and use them elsewhere.
As for the query plan added later. Consider a multicolumn index like bbaird suggested. See:

Multicolumn index and performance

You might rewrite the table physically to help this particular query. CLUSTER takes an exclusive lock. To deal with concurrent write load consider pg_rewrite or pg_squeeze. Either requires excess storage and time for a big table. See:

Configuring PostgreSQL for read performance


Simple Database Design - Many to many? - First database (newbie) This is my first database I am designing.
However I am getting confused on the many to many relationships.
I have a products table, each product can have inventories in many different warehouses. So one product can have many inventories (in different warehouses)

Table 1 -
products
PK
code | name | cost_price
Table 2 - (Many to Many Bridging table)
product_inventory
PK                                   PK
prod_code (foreign key of products.code) | inv_code (foreign key of >products.code)
Table 3 -
inventory
PK
inv_code (auto increment) | warehouse_code (FK of warehouse.code) | >inventory_count
Table 4 -
warehouse
PK
code | warehouse_name

My big questions is: is this correct? and 2: does table 3 need product.code linked to it? or is it already linked through the bridging table? (product_inventory) If it is linked, what is an example of an SQL query to input an inventory for a product, so that Table 2 populates to link it all back so later I can do a join to get the inventory for the product again.
Any tips are well appreciated!!
Does every primary key need to have a foreign key? I.E should inventory.code be a foreign key to product_inventory.inv_code? or do only doubles (not the original need to be the foreign key) if so which should be the copy (foreign key) the bridging table or the inventory table inv_code.
Thanks in advance!

Why have product_inventory at all?

inventory

prod_code (foreign key of products.code) | warehouse_code (FK of warehouse.code) | >inventory_count


Is it possible to know how many times a user connected to an Oracle database? Is it possible to know how many times a user connected to an Oracle database?

If possible where we can find this information?

If audit is enabled, you can check DBA_AUDIT_SESSION/USER_AUDIT_SESSION (More info about auditing - Audit doc). Another option is to check dba_hist_active_sess_history - you will need to write a query that counts distinct serials/sessions to get rough number of user logins assuming user did anything during the session. Also, you won't be able to go too far in the past with dba_hist_active_sess_history - it's a view based on tables populated by AWR which automatically deletes old records (as far as I remember default retention period is 1 week ).


Storing multiple tables by type vs one table I want to store a lot of rows in a table, but I will often want to select only some of them, depending of their type. So I could just add a column type to each row, but then, if I want to select only the rows of one type, I'll have to make a query like SELECT * FROM MyTable WHERE type = 1 for example. But does it mean that the DBMS will have to fetch all the table, even if only a few rows have the type that I want?
Is it better to have a table per type, so I could just SELECT * FROM MyTableType1, without having to use a WHERE?

As long as all the data have (roughly) the same attributes, and the only difference is the type, storing them in a single table is fine.
To make your query fast, create an index on type. Then two things can happen:

there are few enough rows of that type that you get an index scan (or bitmap index scan in PostgreSQL)

the type occurs often enough that PostgreSQL opts for a sequential scan

In both cases you won't be much slower than with many small tables.
The final decision might depend on the number of tables (having 10000 tables in a database is not much fun) and the type of query you have:

The following can be a static query with parameters:
SELECT ... FROM mytable WHERE type = 'type1';

while the following requires you to compose an SQL statement for every query:
SELECT ... FROM mytable_type1;

If you want to query several types at once, it is nicer to write
SELECT ... FROM mytable WHERE type IN ('type1', 'type2', 'type3');

than having to write
SELECT ... FROM mytable_type1
UNION ALL
SELECT ... FROM mytable_type2
UNION ALL
SELECT ... FROM mytable_type3;


Insert rows in a table, via a row-level trigger, activated after insertion in that same table I have to design a schema and trigger system in SQL that will save words and their synonyms.

I thought of this basic schema:

create table words (
    id int primary key auto_increment,
    word varchar(50)
);

create table synonyms(
    w1 int references words(id),
    w2 int references words(id),
    primary key (w1, w2)
);

Another requirement is to make the synonyms relation symmetric (if a is a synonym of b, then b is a synonym of a and the table synonyms should be updated accordingly).

I thought of using a trigger that looks like this:

create trigger MakeSynonymsSymmetric
after insert on synonyms
for each row
insert into synonyms
values (new.w2, new.w1);

Which of course won't work because the trigger activates itself generating an infinite loop (or at least that's my understanding).

Trying to insert into synonyms gives me this error:

  Can't update table 'synonyms' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any way around this problem by defining the schema in a different way, but always using triggers?

To perform what you are looking for based on this example:

INSERT INTO synonym_bucket VALUES (23,37);

SUGGESTION #1

DROP TABLE IF EXISTS synonym_bucket;
CREATE TABLE synonym_bucket LIKE synonyms;
ALTER TABLE synonym_bucket ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER MakeTwoSynonyms
AFTER INSERT ON synonym_bucket
FOR EACH ROW
BEGIN
    IF new.w1 <> new.w2 THEN
        INSERT IGNORE INTO synonyms VALUES (new.w1, new.w2),(new.w2, new.w1);
    END IF;
END $$
DELIMITER ;

Using synonym_bucket takes up just 2K (table definition plus trigger)

Bottom Line: For a table's before and after trigger, you cannot perform additional DML to the same table. However, you can do DML from a trigger to another table. This is why I made a BLACKHOLE table. It takes up to 2K (table definition and the trigger code). You use it to write to a separate table. In this case, synonym_bucket does the double INSERT into synonyms.

SUGGESTION #2

Do the the double INSERT yourself

INSERT IGNORE INTO synonyms VALUES (23,37),(37,23);

No trigger required

GIVE IT A TRY !!!


Table structure and query for messaging between users I'm building a PHP Laravel app and one of the features is messaging between users. A user can message other users.

This is my current schema:

I'm trying to achieve the following:-

Have a list of conversations between different users on a page with the last message between users.
The first column should be the participant's name. This is the query I'm using currently:

SELECT * FROM messages WHERE id IN (SELECT MAX(id) FROM messages WHERE sender_id = $current_user_id OR recipient_id =$current_user_id GROUP BY conversation_id)

which gives me the result in screenshot (Note: The first column is the sender's id so this is incorrect for now).
I have a col in messages table :conversation_id which I'm using to group the messages. I set this value in php while inserting message, based on the 2 participants' id, eg. 1_2. this is calculated using: "smaller_id_number"_"bigger_id_number". This makes sure that the conversation id between 2 users will always be same. Whether they sent or received the message. I think this is not the best approach so I'm open to alternative suggestions to achieve what I'm trying to where I don't have to use a redundant (as per my thoughts) column conversation_id.

On a side note. I also have a single page for conversations between 2 users where users can see all messages between them and the participant. I'm using this query for that:

SELECT * FROM messages WHERE conversation_id='1_3'

Then on php side, I check if the message was sent or received by current user by checking the sender_id. So if I get a solution for my problem above without using conversation_id, I might need to update this feature as well.

Any help is appreciated. Thanks.

P.S. I have checked similar questions but they are not answering my problem.

EDIT:
To clarify, I'm only asking one question here.

Current sample data
These are the messages. My goal is to get a list of last messages between a user and other users(highlighted in yellow) and the participant's name. I will query using the current user's id on recipient_id or sender_id

Current results with my new query:
SELECT m.*, u1.name AS sender, u2.name AS recipient
FROM messages m
JOIN users u1 ON m.sender_id=u1.id
JOIN users u2 ON m.recipient_id=u2.id
WHERE m.id IN (
SELECT MAX(id)
FROM messages
WHERE sender_id = 1 OR recipient_id =1
GROUP BY conversation_id) ORDER BY m.id DESC

I am able to get the participant name in PHP by checking if the current user's id is the sender's id. If yes then I set the participant name to recipient. If not then sender.

As you can see, I have to use php to get the participant name. My goal is to get the participant in MYSQL, if possible. And to eliminate the extra column conversation_id that I'm using if it would be possible without it because I also have to generate this first in php before inserting new message.

Expected output:

For your main question:

You should be able to use a case statement to get just the other person's name:

SELECT m.*, CASE WHEN u2.id = 1
              THEN u1.name
              ELSE u2.name
            END as participant
FROM messages m
JOIN users u1 ON m.sender_id=u1.id
JOIN users u2 ON m.recipient_id=u2.id
WHERE m.id IN (
SELECT MAX(id)
FROM messages
WHERE sender_id = 1 OR recipient_id =1
GROUP BY conversation_id) ORDER BY m.id DESC
;

If the user id from u2 is the ID of the user whose information you're retrieving, then you want the name from u1. Otherwise, you want the name from u2. This should work even if users can send messages to themselves.


What is an equivalent of EXCEPT(in PostgreSQL) in MySQL? I want to find out unmatched records of two tables in MySQL.

You can use a NOT EXISTS predicate. Assume you want to mimic:

select a.c1, a.c2 from a
except
select b.c1, b.c2 from b

This can be expressed as:

select distinct a.c1, a.c2
from a
where not exists (
    select 1 from b
    where b.c1 = a.c1
      and b.c2 = a.c2
)

Other options are to use a left join and check for null:

select distinct a.c1, a.c2
from a
left join b
    on a.c1 = b.c1
   and a.c2 = b.c2
where b.c1 is null

Note that these differ from EXCEPT if the same tuple exists in both relations and contains null. Let a = {(1,1),(2,2),(1,null)} and b = {(1,1),(1,null)}

select a.c1, a.c2 from a
except
select b.c1, b.c2 from b

(2,2)

In a sense, EXCEPT considers null to be equal to null, whereas the predicate a.c2 = b.c2 evaluates to null if either a.c2 or b.c2 is null and as a consequence NOT EXISTS evaluates to false.

select distinct a.c1, a.c2
from a
where not exists (
    select b.c1, b.c2 from b where a.c1 = b.c1 and a.c2 = b.c2
)

(2,2),(1,null)

LEFT JOIN behaves similarly to NOT EXISTS:

select distinct a.c1, a.c2
from a
left join b
    on a.c1 = b.c1 and a.c2 = b.c2
where b.c1 is null

(2,2),(1,null)


read/write/execute to pl/sql grants For a current task and future references, I am wondering how pl/sql grants (select, insert, update, delete, references, alter, index, all) are categorized from a read/write/execute or read/read-write/read-write-execute point of view? A lot of end users or non-dba folks tend to use the later language when asking for access or grant privileges where I work. Any feedback is appreciated :)

Read: READ.

Not SELECT. Forget SELECT. SELECT privilege allows users to run SELECT .. FOR UPDATE, which modifies the metadata in the database blocks (not the user data) and locks the affected rows.

READ privilege is available starting with 12c.

Write: INSERT, UPDATE, DELETE.

Execute: EXECUTE

For the rest (Object Privileges), most people do not even know about them and can happily live without them.


Select value from a (SET) list I am building a blog website with php.

I have a table with a column SET(Y,N,X,A).

And I want to categorize each value.

for example:
A post has labels: News, World, Tropical... selected from the SET of values in the database.

And now I want to get every post from the database that has "News" as label.
I've been really struggling 2 days to find an answer!!!

Ok I found the solution by myself, thanks to Rick James for giving me an enlightenment.

The Right MySql call for this, is

SELECT * WHERE labels LIKE 'N'

Just to mention:
labels is a SET('Y','N') column and I want to check, if a row has value 'Y' or 'N'
then get the row.

The SET column doesn't work with labels='N' because by default is labels='Y','N'


postgresql optimize query with 'where not in' clause I have query to select all rows from table1 that they are not in tabl2 then insert them in table2.
This query is taking log time to execute since I have millions of rows in table2.

Is there better way to optimize or rewrite this query to make it execute faster.

WITH t1 AS (
select *
from table1
where id  not in (select id table2))
insert into table2
select * from t1 ;

First you may try two things:

Do not use WITH because it's an optimization fence. As of PostgreSQL 10, the result of the CTE query will be materialized even if it would be better not to.
Use NOT EXISTS instead of NOT IN. Long explanation of why on stackoverflow: https://stackoverflow.com/questions/173041/not-in-vs-not-exists

 INSERT INTO table2
 SELECT * FROM table1
 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id=table1.id);

Also, please study and share the result of an EXPLAIN of the query or if possible EXPLAIN ANALYZE.


Using the sysadmin role with EXECUTE AS It is my understanding that I can use the EXECUTE AS OWNER clause as part of a procedure that I create to make the body of that procedure run as a different user. My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224)). This procedure is supposed to be called by an unprivileged user.

I ran the following script under the sa user:

SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin')
-- dbo  1   1

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'MyProc')
    DROP PROCEDURE MyProc

GO
CREATE PROCEDURE MyProc
WITH EXECUTE AS OWNER
AS
    SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin');
-- dbo  1   0

    DBCC TRACEON(1224)
--Msg 2571, Level 14, State 3, Procedure MyProc, Line 7
--User 'dbo' does not have permission to run DBCC TRACEON.

RETURN
GO

EXEC MyProc

Output is inline in comments. It turns out that outside of the procedure I seem to have sysadmin membership, but not inside the procedure.

The procedure is owned by the dbo user. I understand that it is not possible to grant the sysadmin role to a database user (at least the GUI doesn't offer this possibility). So I don't see how I could ever make a database user have a server role.

I also tried EXECUTE AS 'sa' which results in Cannot execute as the user 'sa', because it does not exist or you do not have permission.. The documentation states that I can only specify a user name, not a login name. So I understand why that didn't work.

How can I run my procedure with sysadmin role membership?

My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224))

You are punching a hole in your security by allowing an unprivileged user run as sysadmin role.

If you are trying to set 1224 traceflag, which disables lock escalation based on the number of locks, you can do it on table level using ALTER TABLE

e.g. Below enables lock escalation to the partition level on a partitioned table. If the table is not partitioned, lock escalation is set at the TABLE level.

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO); -- valid options are AUTO, TABLE and DISABLE

Now you can just give an unpreviledge user alter table rights.

HTH


Are there disadvantages to contained databases? SQL Server 2012 introduced the concept of "contained" databases, where everything (well, mostly everything) the database needs is contained within the database itself. This offers big advantages when moving databases between servers. I would like to know, then, if this should be my default strategy when designing a new database.

MSDN lists several disadvantages to contained databases, and the big ones are a lack of support for change tracking and replication. Are there others? If I'm not going to use these features, is there any reason not to use contained databases?

The primary purpose of contained databases is to make it easier to port your database to a new server without a lot of scaffolding around it. With that in mind, I'll treat a few potential issues that will make this migration more difficult - and most revolve around the fact that contained databases are only partially contained in SQL Server 2012 (containment is not actually enforced).

Connection strings

Connection strings to a contained database must explicitly specify the database in the connection string. You can no longer rely on the login's default database to establish a connection; if you don't specify a database, SQL Server is not going to step through all the contained databases and try to find any database where your credentials may match.

Cross-db queries

Even if you create the same user with the same password in two different contained databases on the same server, your application will not be able to perform cross-database queries. The usernames and passwords may be the same, but they're not the same user. The reason for this? If you have contained databases on a hosted server, you shouldn't be prevented from having the same contained user as someone else who happens to be using the same hosted server. When full containment arrives (likely in the version after SQL Server 2012 never), cross-database queries will absolutely be prohibited anyway. I highly, highly, highly recommend that you don't create server-level logins with the same name as contained database users, and try to avoid creating the same contained user name across contained databases. If you need to run queries that hit multiple contained databases, do so using a server-level login that has appropriate privileges (you might think this is sysadmin, but for read-only queries, this is CONNECT ANY DATABASE and SELECT ALL USER SECURABLES).

Synonyms

Most 3- and 4-part names are easy to identify, and appear in a DMV. However if you create a synonym that points to a 3- or 4-part name, these do not show up in the DMV. So if you make heavy use of synonyms, it is possible that you will miss some external dependencies, and this can cause problems at the point where you migrate the database to a different server. I complained about this issue, but it was closed as "by design" and didn't survive the migration to the new feedback system. Note that the DMV will also miss 3- and 4-part names that are constructed via dynamic SQL.

Password policy

If you have created a contained database user on a system without a password policy in place, you may find it hard to create the same user on a different system that does have a password policy in place. This is because the CREATE USER syntax does not support bypassing the password policy. I filed a bug about this problem, and it remains open (and it also didn't survive the move when Connect was retired). And it seems strange to me that on a system with a password policy in place, you can create a server-level login that easily bypasses the policy, but you can't create a database user that does so - even though this user is inherently less of a security risk.

Collation

Since we can no longer rely on the collation of tempdb, you may need to change any code that currently uses explicit collation or DATABASE_DEFAULT to use CATALOG_DEFAULT instead. See this BOL article for some potential issues.

IntelliSense

If you connect to a contained database as a contained user, SSMS will not fully support IntelliSense. You'll get basic underlining for syntax errors, but no auto-complete lists or tooltips and all the fun stuff. I filed a bug about this issue, and it remains open - and one more that did not survive the move.

SQL Server Data Tools

If you are planning to use SSDT for database development, there currently isn't full support for contained databases. Which really just means that building the project won't fail if you use some feature or syntax that breaks containment, since SSDT currently doesn't know what containment is and what might break it.

ALTER DATABASE

When running an ALTER DATABASE command from within the context of a contained database, rRather than ALTER DATABASE foo you will need to use ALTER DATABASE CURRENT - this is so that if the database is moved, renamed, etc. these commands don't need to know anything about their external context or reference.

A few others

Some things you probably shouldn't still be using but nonetheless should be mentioned in the list of things that aren't supported or are deprecated and shouldn't be used in contained databases:

numbered procedures
temporary procedures
collation changes in bound objects
change data capture
change tracking
replication

That all said, these aren't necessarily disadvantages to using contained databases, they're just issues you should be aware of and aren't all explicitly disclosed in the official documentation.

You'll also need to be sure that if a contained database is going to be migrated, or is part of an availability group or is being mirrored, that all potential destination servers have the sp_configure option contained database authentication set to 1.

You may find this blog post useful, as well as this one, even though they pre-date RTM.


Timing issues for backups during recovery Let's say we did a full database backup of the PRIMARY filegroup at 10:20 am. It finished at 10:45 am. We also took log backups every 5 minutes, hence
10:15, 10:20, 10:25, 10:30, 10:35, 10:40, 10:45,...
Then we do a full backup for secondary filegroup at 10:50am it finishes at 11:00am.

Now I try to recover a database using full filegroup and log backups. I take the full backup of PRIMARY done at 10:15, restore with norecovery. What should I restore next? The logs until 10:45 with norecovery? then the second full backup after that all the logbackups after 11:50am  till the tail log with recovery? Is it the right way or should I do something else?

Thanks!

You should first restore your PRIMARY with norecovery, then your SECONDARY with norecovery, and then all the log backups from 10:15 till tail of the log, the last one with recovery.


How do I identify the program that called a stored procedure? As part of an application database design spec, we've been asked to block execution of a set of stored procedures if they have been fired directly from SSMS / osql / sqlcmd and so on; that is, they must be permitted to run only from within the application itself.

When our team questioned if this is even possible, we were shown a demo that limited queries to specific systems (actually it let the query run, but logged to a table where it was running from). However, the procedure that did that is encrypted, so we could not find out how it was done.

How do we accomplish this?

Multiple ways to get this information:

SELECT APP_NAME();

SELECT PROGRAM_NAME();

SELECT [program_name]
  FROM sys.dm_exec_sessions
  WHERE session_id = @@SPID;

Just keep in mind that it can be spoofed in the connection string or in Management Studio's connection properties. If I connect using the following parameter, all three of the above will return foobar:


Losing customization capability with managed servers (AWS or Azure)? We're currently hosting our SQL Server instances on AWS VMs. I was recently hired as a data architect and DBA. I'd like to move our instances to either managed services using AWS RDS or Azure SQL. I was told by our current CTO the following:

After having actually managed all this for several years now, I disagree [about the benefits of moving to a Platform-as-a-Service (PaaS) model]. I actually have experience with both "cloud managed" (on both AWS and Azure) and "self managed" and I have real concerns about performance (mostly I/O) on PaaS platforms. The level of flexibility and ability to tailor storage exactly how we need is something we just can't do with the PaaS services. With the ease of use, you lose almost all customization capability.

For those of you with experience using AWS RDS or Azure SQL, do we actually lose almost all customization capability? Most of our databases are under 1TB. In fact, I think our largest is currently at 680GB. Also, as an FYI, my colleague's greatest concern seems to be the performance gap between a managed environment and an instance sitting on a VM. The ease with which you can scale resources would seem to alleviate this concern.

Also, if this isn't the appropriate forum for a question like this, I would be glad to move it, if someone can tell me a more appropriate forum.

For platform-as-a-service (PaaS) databases, you don't get to design the underlying hardware such as CPU, I/O or memory configuration.

You can only choose a service tier that gives you a general idea of the overall performance. On Azure SQL Database, the different service tiers are defined by something Microsoft calls "DTUs", a performance metric that blends disk and CPU performance, but you cannot prioritize one over the other. I suspect AWS RDS works similarly in this regard.

The reason (personal opinion) why you would choose PaaS over IaaS (installing SQL Server on a VM) is that you don't have to maintain and patch any infrastructure. Your colleague is correct in saying that this comes at a price: less freedom.

The upside with PaaS is that you can scale it up and down whenever you need, minute by minute or hour by hour.


How to keep row ordering with DISTINCT command or how to add row number to cross apply I have problem that after distinct, order direction of Variable column change.

Here is example

Data Source

DECLARE @Table TABLE
(
    FirstName NVARCHAR(255),
    LastName NVARCHAR(255),
    Variable1 NVARCHAR(255),
    Variable2 NVARCHAR(255),
    Variable3 NVARCHAR(255)
)

INSERT INTO @Table VALUES

('FirstName 1','LastName 1', 'D', 'B','A'),
('FirstName 1','LastName 1', 'D', 'C','A'),
('FirstName 2','LastName 2', 'A', 'B','C')

First select with proper ordering (order is done by Cross Apply sequence)

/* Ordering is okay, but table containts duplicates */
SELECT
    FirstName, LastName, v.VariableName, v.Value

FROM @Table
CROSS APPLY (
    VALUES
        ('Variable1',Variable1),
        ('Variable2',Variable2),
        ('Variable3',Variable3)
) AS v(VariableName, Value)

Second select is reordered by Distinct command

/* Ordering is okay, but table containts duplicates */
SELECT DISTINCT
    FirstName, LastName, v.VariableName, v.Value

FROM @Table
CROSS APPLY (
    VALUES
        ('Variable1',Variable1),
        ('Variable2',Variable2),
            ('Variable3',Variable3)
) AS v(VariableName, Value)

What I want

I need second select but with ordering of cross apply sequence. I thought about adding row number for every person but I don't know how to do it

By "cross apply sequence" I mean ordering by cross apply order in code (ordering by syntax). I don't know how to describe it better, therefore I attached an image.

Even with your first query the output order is in no way guaranteed. Unless a specific ordering is demanded via an ORDER BY clause the database is free to hand you the results in any order it see fit. For simple queries that can use an index it may look like the order is guaranteed because the output will be in the order of which ever index is used as the main seek/scan, but for anything more complex than a single table select there will be at least two ways to do it and the one the query planner chooses may change over time as the balance of data in your tables changes.

If you want a specific order, you must specify it in an ORDER BY clause.

In your this case ORDER BY FirstName, LastName, VariableName, Value seems to be what you want.

You don't mention caring if Value is in any particular order. If you don't care about that then go with ORDER BY FirstName, LastName, VariableName instead just in case the engine would need to do extra work to enforce the extra ordering detail. In you example data this would mean the rows FN1/LN1/V2/B and FN1/LN1/V2/C would come out in arbitrary order (i.e. sometimes B will be first, sometimes C will).


Find the size of each index in a MySQL table I am trying to figure out size of each index of a table.

SHOW TABLE STATUS gives "Index_length" is the summation of all the indices of the table.
However if a table has multiple indices (e.g. in an employee table) then emp_id, ename and deptno are 3 different indices for which I want sizes separately.

emp_id : xx Kb
ename  : yy Kb
deptno : zz Kb

How can I get these?

You should query the following:

INFORMATION_SCHEMA.STATISTICS
INFORMATION_SCHEMA.COLUMNS

If the table was called mydb.mytable, just run the following:

SET @PowerOfTwo = 0;
SET @GivenDB = 'mydb';
SET @GivenTB = 'mytable';
SELECT COUNT(1) INTO @MyRowCount FROM mydb.mytable;
SELECT
    index_name,SUM(column_length * @MyRowCount) indexentry_length
FROM
(
    SELECT
        index_name,column_name,
        IFNULL(character_maximum_length,
        IF(data_type='double',8,
        IF(data_type='bigint',8,
        IF(data_type='float',4,
        IF(data_type='int',4,
        IF(data_type='mediumint',3,
        IF(data_type='smallint',2,
        IF(data_type='datetime',4,
        IF(data_type='date',3,
        IF(data_type='tinyint',1,1)
        ))))))))
    ) / POWER(1024,@PowerOfTwo) column_length
FROM
(
    SELECT
        AAA.index_name,AAA.column_name,
        BBB.data_type,coalesce(AAA.sub_part,BBB.character_maximum_length) AS character_maximum_length
        FROM
        (
            SELECT table_schema,table_name,index_name,column_name,sub_part
            FROM information_schema.statistics
            WHERE table_schema = @GivenDB AND table_name = @GivenTB
        ) AAA INNER JOIN
        (
            SELECT
                table_schema,table_name,column_name,
                character_maximum_length,data_type
            FROM information_schema.columns
            WHERE table_schema = @GivenDB AND table_name = @GivenTB
        ) BBB USING (table_schema,table_name,column_name)
    ) AA
) A GROUP BY index_name;

Give it a try !!!

CAVEAT #1

Please note the first line

SET @PowerOfTwo = 0;

Here is how the setting affects the output

0 : Bytes
1 : KiloBytes
2 : MegaBytes
3 : GigaBytes
4 : TeraBytes

CAVEAT #2

This does not take BTREE overhead and fragmentation into account.

Here is another post from someone else : Find out MySQL index size for a concrete index

That answer suggests a fudge factor of 1.4 to 2.8.

Therefore, whatever answer my queries produce, multiple it by 1.4 or 2.8 to factor in BTREE nodes and possible fragmentation. You should defragment the table before running my queries.

If your table is MyISAM:

OPTIMIZE TABLE mydb.mytable;

If your table is InnoDB:

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;


How to update a yes/no field to yes when all related fields in another table are marked yes I'm new to database stuff (read: database idiot) and I am using Access. I have a table (let's call it "projects") that has a list of projects and one the fields (let's call it "actions_complete") for each of the projects is basically "are all the actions related to this project complete?" There is another table (let's call it "actions") that has broken down all the actions related to each of the projects and a field (let's call it "complete") that documents whether or not that specific action is complete.

What I want is for "actions_complete" in "projects" to change to a yes value once all of the "complete" values for a corresponding project are marked as done.

I took a database class in college but I retained very little information from then because I had never worked with databases and then I went and taught English for two years (where I forgot everything IT-related about my degree) on the other side of the world and now I'm working in a place where some basic database administration skills would be very helpful. I'm super grateful to the community here at StackExchange and I'll be very happy if anyone is willing to explain to me how I can get this done or if it's even possible. I'm sure it's much easier than I'm imagining.

In general a design like that is a bad idea, a better idea would be to start using a view, and in that view you add a column where you calculate if all subtasks are done or not. That way every time you query the view you calculate the correct value without having to rely on things like triggers to update your actions_complete every single time something happens.


updating values in one table if depending table row is affected there are two tables

structure is like:

product_table

item_id product_id count_subscriber
1          2           4
2          3           5

subscriber_table

item_id subscriber_id product_id
1         115           2
2         145           2
3         84            3

what i want to achieve is if i add/delete one item_id from subcriber_table this should update the value in count_subscriber (increment/decrement) of product_table where product_id matches.

is this possible to do this in one query? how should i achieve this in one query? right now what i am doing is first deleting the item_id in subscriber_table and than updating the count_subscriber in product_table and same for addition.

please advise.
thank you for your time.

Not sure if this will fit in your situation, but a mysql VIEW should work for you, and make things easier.

You could create a VIEW called product_table, based on a select over subscriber_table, like this:

CREATE VIEW product_table AS SELECT item_id, product_id, COUNT(*) AS
count_subscriber  FROM subscriber_table GROUP BY item_id, product_id

Using this, you will not need to update manually product_table data on every change made over subscriber_table.


Is there a way to see if a SQL 2012 Replication publication is marked for initilazation and a way to unmark it? We have a SQL (2102) Replication Database with a Realtime DB being the Publisher and a Historical Db as the Subscriber. All of the Tables/Articles have the setting of Do Not Delete. Thereby the Realtime DB is maintained with a max data of say 2 weeks but the historical DB will just keep growing. We need to add a couple of tables/articles and have seen the mini-snapshot discussed and it does work fine. However I have come to a worrisome condition. If ever a snapshot is initialized, all of the history will be lost! Bad design I suppose. But is there a way to check to see if a publication is marked for initialization and a way to clear it?

Thanks!

Microsoft has some documentation on Initializing and Reinitializing Transactional Replication.

Initialize a Subscription with a Snapshot

Initialize a Transactional Subscription Without a Snapshot

Reinitialize Subscriptions

Snapshot Options

From your concern over lost data, you might want to investigate the Snapshot Options. Note this option: Specify an alternate snapshot folder location instead of or in addition to the default snapshot folder location.


pg_basebackup: could not set compression level -1 When trying to run pg_basebackup I received the following error message:

pg_basebackup: could not set compression level -1 (null)

using the -z option. I received a similar error message when trying -Z #.

I tried the command without a compression option and received this error message:

pg_basebackup: could not create file "/dir/names/base.tar": Permission denied

which revealed that the destination directory was not writable for the user. Changing permissions or ownership solved the problem.

Unfortunately, the original error message was not as informative as it could have been.


Granting Execute Permissions on Stored Procedures, Functions and Views I am using a user which has read permission only. It basically has permission to

Connect
Execute

However, it does not have execute permissions on Functions and Stored Procedures. In part of my reports, I am calling my own functions. I would like to give my readonlyuser permission to execute SP, Functions and Views.

I came across  this code which shows how to give execute permission on Stored Procedures and Functions for a particular user.

- Grant Execute on all functions for testuser
declare @username varchar(255)
set @username = 'testuser'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE='PROCEDURE'

and for functions

declare @username varchar(255)
set @username = 'testuser'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE='FUNCTION'

Now, here it looks like this permission applied to existing SP and functions only. If you add a new function, you have to run this code again to grant permission.

Questions

If my above interpretation is not wrong, is there a way to give execute permission to a read only user so that you have permission on all functions and stored procedures, even if you create new functions?
Can I apply the same permissions on Views?
In this thread, it is said you cannot give execute permission on function which returns a table. Is that true?

I know there are a couple of questions but all of them are related. Better to be in one place.

It is true that you cannot grant EXEC permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.:

GRANT SELECT ON dbo.Table_Valued_Function TO [testuser];

So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA and much prefer to use the catalog views, which also don't need functions like OBJECTPROPERTY):

DECLARE
    @sql      NVARCHAR(MAX) = N'',
    @username VARCHAR(255)  = 'testuser';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT ' + CASE
    WHEN type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
      OR type_desc = 'VIEW'
    THEN ' SELECT ' ELSE ' EXEC ' END
    + ' ON ' + QUOTENAME(SCHEMA_NAME([schema_id]))
    + '.' + QUOTENAME(name)
    + ' TO ' + @username + ';'
 FROM sys.all_objects
WHERE is_ms_shipped = 0 AND
(
    type_desc LIKE '%PROCEDURE'
    OR type_desc LIKE '%FUNCTION'
    OR type_desc = 'VIEW'
);

PRINT @sql;
-- EXEC sp_executesql @sql;

Now you can grant EXEC on a schema, and always create these procedures in that schema (actually one of the purposes of schemas!), which @jgardner04 already suggested, however in order for this solution to apply to table-valued functions as well, you'd also have to grant SELECT. Which is okay if you're not storing any data in tables in that schema (or at least that you want to hide from them), but it will apply to any tables and views as well, which might not be your intention.

Another idea (e.g. if you can't, or don't want to, use schemas) is to write a DDL Trigger that captures the CREATE_PROCEDURE, CREATE_FUNCTION and CREATE_VIEW events, and grants permissions to a user (or a set of users, if you want to store them in a table):

CREATE TRIGGER ApplyPermissionsToAllProceduressAndFunctions -- be more creative!
    ON DATABASE FOR CREATE_PROCEDURE, CREATE_FUNCTION, CREATE_VIEW
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @sql       NVARCHAR(MAX),
        @EventData XML = EVENTDATA();

    ;WITH x ( sch, obj )
    AS
    (
        SELECT
          @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
          @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')
    )
    SELECT @sql = N'GRANT ' + CASE
      WHEN o.type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
        OR o.type_desc = 'VIEW'
      THEN ' SELECT '
      ELSE ' EXEC ' END
        + ' ON ' + QUOTENAME(x.sch)
        + '.' + QUOTENAME(x.obj)
        + ' TO testuser;' -- hard-code this, use a variable, or store in a table
    FROM x
    INNER JOIN sys.objects AS o
    ON o.[object_id] = OBJECT_ID(QUOTENAME(x.sch) + '.' + QUOTENAME(x.obj));

    EXEC sp_executesql @sql;
END
GO

The drawback I find with DDL Triggers is that you can quickly forget that they're there. So a year down the road when you decide to stop granting these permissions to all new objects, it might take a while to troubleshoot why it's still happening. At my last job we logged all actions invoked by DDL triggers to a central "event log" of sorts, and that was our go-to place for tracking down any actions that happened on the server that nobody seems to remember (and it was a DDL trigger about half the time). So you may consider adding some additional logic that will help with that.

EDIT

Adding code for schema-based, and I'll mention again that this will grant permissions on any procedures, functions and tables created in the foo schema.

CREATE SCHEMA foo;
GRANT EXEC, SELECT ON SCHEMA::foo TO testuser;

Now if you create the following procedure, testuser will be able to execute:

CREATE PROCEDURE foo.proc1
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 1;
END
GO


Error while opening SQL worksheet in SQL Developer I am facing the following issue while opening SQL worksheet in SQL developer

Popup with Error Message --> "An error occurred while opening ide.worksheet.file:/C:/users/%USERNAME%/Appdata/Local/Temp/worksheet....."

After clicking on OK I am able to continue work as usual.

I am not able to determine what is causing this problem.
I do not want this pop up coming up every time I start a new worksheet.

Any help is highly appreciated.

Regards.

I was also facing the same issue.
While running SQL Developer with Run as Administrator option, I was able to open worksheets.


if exists, update else insert, with cursors in stored procedures only returns 1 row I have 2 tables api(columns - api , api_context, api_id -->primary key) and api_request_summary(columns - api_context). i need to insert the number of times the api_context relevant to a particular api_id is repeated in the table curhittest1. both tables have multiple rows so i have used cursors to loop through the tables. i can correctly insert the values to the table but i need to check wether that api_id already exists in the curhittest1 table and if yes update and if no insert.

DELIMITER //
CREATE PROCEDURE curhit12()

BEGIN

 DECLARE done INT DEFAULT FALSE;
 DECLARE apiId, noOfHits int(11);
 Declare apiContext varchar(255);

 DECLARE cur1 CURSOR FOR Select api_id from api;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO apiId;

    IF done THEN
      LEAVE read_loop;
    END IF;

    select api_context into apiContext from api where api_id =apiId;

    SELECT COUNT(*) FROM api_request_summary WHERE api_context=apiContext into noOfHits;

 IF exists (SELECT * FROM curhittest1 WHERE apiid = apiId) THEN

   UPDATE curhittest1
                      SET noofhits=noOfHits
                      WHERE apiid = apiId;
 ELSE

  insert into curhittest1(apiid,noofhits) values (apiId,noOfHits);
 END IF;

  END LOOP;

  CLOSE cur1;

END//
DELIMITER

When i use the following code only 1 row is added to the curhittest1 table. When the exact same code is used in sql procedures without cursors it displays so i'm assuming i need to do something differently when using with cursors. How can i add all the values to the table accurately?

It worked after using INSERT ... ON DUPLICATE KEY UPDATE. Thank you @RickJames. For anyone interested here is the code

DELIMITER //
CREATE PROCEDURE curhit12()

BEGIN

 DECLARE done INT DEFAULT FALSE;
 DECLARE apiId, noOfHits int(11);
 Declare apiContext varchar(255);
 DECLARE cur1 CURSOR FOR Select api_id from api;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO apiId;

    IF done THEN
      LEAVE read_loop;
    END IF;

    select api_context into apiContext from api where api_id =apiId;

SET noOfHits = (SELECT COUNT(*) FROM api_request_summary
                WHERE api_context=apiContext);

INSERT INTO api_hits (api_id,no_of_hits) VALUES (apiId,noOfHits)
   ON DUPLICATE KEY UPDATE no_of_hits=noOfHits;

  END LOOP;

CLOSE cur1;

END//
DELIMITER


if set global transaction, how can I commit? I found that MySQL can set a global transaction, but can I commit in another session? refer: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

BEGIN or START TRANSACTION initiates a transaction.  It is ended by COMMIT or ROLLBACK in the same connection.

SET TRANSACTION provides optional attributes for the transaction; it is not often used.

If you need a long-running transaction, or something that spans multiple web page invocations, you need to do it some other way.  (This is probably a common question on this forum.)


designing multiple tables while eliminating duplication? As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating.

What I'm trying to design is [n] tables that will store subject_names, subject_id, grade_name grade_number and ofcource grade_id. But since the same subject could be taught for different grades; there will be duplication.

For example, Math will be taught for every grade which means there will (10+) columns with subject_name set to Math, but each one will have different grade_id.

The current design I have in mind(which I didn't implement yet) is creating two tables, subjects and grades and subjects table will store subject_name, subject_id and grade_id and grades table will contain grade_id, grade_name and grade_number. Unfortunately this design will contain many duplicates.

So, what's the correct way of creating such a database design?

Thanks in advance.

EDIT:
Sorry for confusion, I'm referring to grade as a year of education.

My recommendation would be to have the subject table with just subject_name and subject_id, have the grades table with just the grade_name and grade_id. Then create a third table called subject_grades that has the columns subject_id and grade_id, on this table create foreign keys to the subject.subject_id column and the grades.grades_id column. I'd highly recommend putting a primary key over both of the columns in this table or at least a unique key.

I hope this helps you.


"Unrecognized configuration parameter: qreplace_function" trying to backup a database I am new to PostgreSQL and I am trying to backup a database with the pg_dump utility.

I am using this command:

/opt/PostgresPlus/9.4AS/bin/pg_dump -U (username) -f /opt/PostgresPlus/9.4AS//pgbackup/db_name.sql dbname -p 5432

and I am encountering this error:

  pg_dump: [archiver (db)] query failed:
  ERROR: unrecognized configuration parameter "qreplace_function"

Can anyone please help me understand what I am doing wrong?

You have not used  PostgreSQL. You are using  PostgresPlus created by EnterpriseDB.

The parameter qreplace_funtion is mentioned in EnterpriseDB's document (page 21). I guess this is a typical privilege problem.

OR, if you use PostgreSQL but run PostgrePlus's pg_dump, try original PostgreSQL's pg_dump.


SQL Server 2008 - combined index + other separate index on one column Lately I've been refactoring the SQL Server indexes of the products that I have developed for my employer. One of these products is an online dashboard containing multiple user levels.

Each user (on admin level) can create projects that they can then link to users of the user level below. Each project has its own Name field and also the field AdministratorUserId to keep track of the project admin. I created a combined non-clustered unique index on these two fields, meaning that project names have to be unique per administrator.

Now here's the catch: I am noticing that I often do a select on AdministratorUserId only, and not on Name. According the indexing guidelines, and please correct me if I'm wrong, I am to create a non-clustered index on the field in order to optimize my queries.

My question being - does my existing index on Name + AdministratorUserId already improve my queries or is it advisable to create a separate index besides it? Would there be any downside to AdministratorUserId being present in two separate indexes?

The order of the columns in indexes is critical.
If your AdministratorUserId is the first column of the index, then separating the indexes will not improve anything.
The index works like an order by clause, meaning that it is sorted by the first column, then the second, then the third and so on. Because of that, an Index on AdministratorUserId is functionally equivalent to an index on AdministratorUserId and Name.  Note that it's possible in sql server to include columns to a non-clustered index that are non key columns. This will increase the storage size of the index but may improve performance of queries since it will save a lookup operation on columns that are included in the index.


Query to filter out the duplicate and pick the latest I'm trying to filter out duplicates and just get the latest of the form name based on the the higher number, no dates on the table:

Form Name
abc
abc-01
abc-01
abc-02
abc-02
abc-03
def-01
def-02
xyz

And I would like to get this result:

Form Name
abc-03
def-02
xyz

What is the query to filter that out? Any suggestions? Thank you!

The query below will probably work; the subquery extracts the highest number after the - ('Version') and the outer query concatenates them back into the original format.

SELECT CASE Version IS NULL
           WHEN 1 THEN ID
           ELSE CONCAT(ID, '-', Version)
           END
  FROM
(SELECT
  CASE LOCATE('-', name)
           WHEN 0 THEN name
           ELSE SUBSTRING(name, 1, LOCATE('-', name) - 1)
           END AS 'ID',
  MAX(CASE LOCATE('-', name)
           WHEN 0 THEN NULL
           ELSE SUBSTRING(name, LOCATE('-', name) + 1)
           END) AS 'Version'
  FROM forms
    GROUP BY CASE LOCATE('-', name)
           WHEN 0 THEN name
           ELSE SUBSTRING(name, 1, LOCATE('-', name) - 1)
           END) AS subquery

Here is an online demo.


Optimising BCP performance for BLOB data I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between 50kb and 500kb, with a small percentage in the 500kb to 1MB range. Part of the migration will involve BCPing data from the old to new database.

BCP is the preferred approach as the current/historic divide in the data permits extracting the older data in stages (during quieter periods) in advance of a final switch, minimising impact on the live system. The volume of data and availability of storage precludes an in-situ rebuild to a partition scheme.

I'm suspect there maybe some performance gains to be had by experimenting with KILOBYTES_PER_BATCH rather than ROWS_PER_BATCH, due to the BLOB content. It's suggested in the BCP documentation that SQL can optimise the operations based on this value.

What I can't find is any guidance on the nature of these optimisations or where to start my testing. In the abscence of suggestions I'll try short runs at 4/8/16/32/64mb boundaries to start.

Probably some gains to be from changing the packet size (BCP -a parameter, rather than server level setting) but I'm inclined to bump this to the maximum 65535 unless anyone has a more formulaic approach.

This is not a direct answer to your question, but there are some articles that you would benefit from reading them (in case you didn't find them first :-) ). They are about loading lots of data using bcp/bulk copy. I've read them all and I didn't find anything detailed regarding KILOBYTES_PER_BATCH, they're all using ROWS_PER_BATCH, but I'm sure you will find other useful information.

Load 1TB in less than 1 hour (from SQL CAT team) - list of advices from here (quote):

Run as many load processes as you have available CPUs.  If you have 32 CPUs, run 32 parallel loads.  If you have 8 CPUs, run 8 parallel loads.

If you have control over the creation of your input files, make them of a size that is evenly divisible by the number of load threads you want to run in parallel.  Also make sure all records belong to one partition if you want to use the switch partition strategy.

Use BULK insert instead of BCP if you are running the process on the SQL Server machine.

Use table partitioning to gain another 8-10%, but only if your input files are GUARANTEED to match your partitioning function, meaning that all records in one file must be in the same partition.

Use TABLOCK to avoid row at a time locking.

Use ROWS PER BATCH = 2500, or something near this if you are importing multiple streams into one table.

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse (from SQL CAT team) - advices (quote):

Use SIMPLE or BULK LOGGED recovery model during the initial data load.

Create the partitioned fact table with the Clustered index.

Create non-indexed staging tables for each partition, and separate source data files for populating each partition.

Populate the staging tables in parallel (Use multiple BULK INSERT, BCP or SSIS tasks)

Build a clustered index on each staging table, then create appropriate CHECK constraints.

SWITCH all partitions into the partitioned table.

Build non-clustered indexes on the partitioned table.

The Data Loading Performance Guide (from SQL CAT team)

Loading Bulk Data into a Partitioned Table - SQL Server Best Practices Article  (Technet article)

SQL Server 2000 Incremental Bulk Load Case Study (Technet article)

Lessons Learned and Findings from a Large Fast-Track POC (from SQL CAT team)

Performance Tuning Tips for SQL Server BCP (By Brad McGehee)

Performance Impact: Finding the Most Optimal Batch Size  (By Linchi Shea)

and the obvious MSDN references:

Managing Batches for Bulk Import
Managing Bulk Copy Batch Sizes
Bulk Copy Performance Considerations
Guidelines for Optimizing Bulk Import
Optimizing Bulk Import Performance

In my personal experience, I succeeded to make a fast data load using parallel load and testing with several batch sizes. I guess that only personal testing will suit you.
Hopefully you will find some good advices in the references.


Dumping data from website's Database I'm looking for ways to browse and dump the database of a particular content from the website. To be more specific i am collecting the database of the Skills designed for the device amazon alexa. Is there any way where i can access the database of all the updated skills list without browsing one by one and copying each skill to create a list? Thanks and regards, EJ

This has 0 to do with databases.

  collecting the database of the Skills

Sure, but you're really just crawling their API. Find the web portal: https://www.amazon.com/b?node=13727921011

From there you'll see b?node= in the url. I searched for node= in the api. I found this in the passage,

ProductCategoryId Identifier for a product category (or browse node).
ProductCategoryName   Name of a product category (or browse node).

Knowing that the term here is browse node. I set out googling for a newer API than the MarketPlace Web Services (Amazon MWS) which was last updated in 2011. I found the "Product Advertising API" which was last updated in 2013. BTW, this is normal for Amazon. Their docs are crap, so move fast through them.

The Product Advertising API supports a method called ItemSearch which supports a BrowseNode argument which does exactly what you want. You'll have to crawl through that because it only returns 10 results per page.

Working with Amazon is a blast. Godspeed. Cache the data with a time_crawled_idx, and time_crawled_detail. You'll only get the ASIN from that. From there, off to ItemLookup

If you're looking to spec out the project, be glad to help contact info in profile.