Events are not "running" unless they are actually in the process of executing.

What you see in your processlist is the actual event scheduler, not the event itself.  You will always have this entry when the event scheduler is enabled.

mysql> show processlist;
+---------+-----------------+---------------------------+-----------------+---------+-------+-----------------------------+------------------+
| Id      | User            | Host                      | db              | Command | Time  | State                       | Info             |
+---------+-----------------+---------------------------+-----------------+---------+-------+-----------------------------+------------------+
|      48 | event_scheduler | localhost                 | NULL            | Daemon  |     1 | Waiting for next activation | NULL             |

Nothing shows in the processlist for scheduled events unless the actual query spawned by the event is currently running.

When that happens, this row will still be in the processlist, plus you will see a new thread that will appear while the event's query is actually running, and disappear when the event's query has finished.  The next time the event fires, it will have a new thread and a new thread ID.

To check on an event:

mysql> use the_event_db; # your database name
Database changed

mysql> show events;
+------------------+------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db               | Name                   | Definer   | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+------------------+------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| the_event_db     | scheduled_stats_update | tehuser@% | SYSTEM    | RECURRING | NULL       | 15             | MINUTE         | 2012-12-14 12:41:16 | NULL | ENABLED |         11 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------------+------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
mysql>

Note the Starts value.  In the event above, this event doesn't fire at :00, :15, :30, and :45 minutes of every hour -- it runs every 15 minutes after 2012-12-14 12:41:16... so each hour it runs at XX:11:16, and XX:26:16, XX:41:16, and XX:56:16.

Your original question shows this:

ON SCHEDULE EVERY 1 MONTH STARTS '2013-02-02 00:00:00'

This would run on the second day of every month, not the first.


What is the best practice for physical deployment of data marts? We are just starting to get into business intelligence at our company. We have some architects who are designing our data warehouse and also our data marts. They are using star schema for both designs.

The architects have stated that the design they are using for the marts is the same, that we just need "another copy" of it for whichever customer wishes to query their mart and that the data may differ per mart.

We only had one mart to start with. This was one AIX LPAR, one DB2 database instance, and one database, with one schema.

Now we have requests for three more data marts. What is the best practice? Should these be different schemas within the same database? Should each mart be a separate database within the same instance? Should we have separate databases, separate instances?

What is the best practice here from a physical deployment and maintenance perspective? Or what guidelines are used to define when it should be which option?

It sounds like you're looking at something similar to, if not identical to, a multi-tenant architecture.

In a multi-tenant architecture, all the tenants (clients, customers) share something. They might share only the server and the dbms, with each tenant getting a private database. Or they might share the database, with each tenant getting a private schema. Or they might share tables, where each row in each table carries a tenant identifier.

So there's a pretty broad spectrum of choices. Each one of these can probably be considered a best practice for some applications, and a worst practice for others.

Your architects seem to be thinking "one database per customer," also known as "shared nothing." (That actually means sharing only the dbms, and usually the server.)

  What is the best practice here from a physical deployment and
  maintenance perspective? Or what guidelines are used to define when it
  should be which option?

There isn't really a single best practice, because of the spectrum of possibilities. You want to make deployment and maintenance as easy as possible, but you also have to consider what the tenants are sharing.

Separate, private databases make some kinds of maintenance easy, and it makes other kinds of maintenance hard. For example, when tenants share nothing but the server and dbms,

adding custom columns, tables, and view for a single tenant is easy, but
adding an identical column or table for all tenants is relatively hard.

When tenants share tables

adding custom columns, tables, and views for a single tenant is hard, but
adding an identical column or table for all tenants is easy.

There are other issues. Disaster recovery is a lot easier with "shared nothing"  than with shared tables (also called "shared everything"). With "shared nothing", you can just restore a database. With "shared everything", you have to restore just a few rows in every table.  Cost of hardware can be an issue when you scale up.  Data isolation and security is easier with "shared nothing", harder with "shared everything".


Search result from two collections I'm new to MongoDB.
Lets say I have a collection of blog post, and a collection of authors. I want to search for posts e.g. by topic, and language. But I only want results by certain authors, e.g. by age span. What's the correct way to do this? Should I search posts first, then iterate the result, and make a new search based on that? Or can I make a chained search somehow?

You may want to have a look on that document http://docs.mongodb.org/manual/reference/database-references/ . In general join is not a good practice for mongodb since it does not automatically supported. You might need to nest some author attributes - if not all information - that commonly used by your find queries on the blog collection for faster lookup.


How to run psql on Mac OS X? I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available.

I get this message:

psql
-bash: psql: command not found

Do I have to install something more? Or how can I configure PostgreSQL so I can use it on my computer?

Locate the psql binary. (In a terminal, run locate psql | grep /bin, and make note of the path. (In my case, it's /opt/local/lib/postgresql90/bin/, as it was installed using MacPorts.)

Then, edit the .bash_profile file in your home folder (e.g. mate -w ~/.bash_profile assuming you've textmate), and add the needed line so it's in your path, e.g.:

export PATH=/opt/local/lib/postgresql90/bin/:$PATH

After having saved the file, read the file (. ~/.bash_profile) or open a new terminal, and type psql.


Multiple User Types - DB Design Advice I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. Some of the fields that each user has will be the same, as in:

email, password, first_name, last_name, etc.

But some of the fields for different user types will be different. For example:

User Type: Instructor
Fields unique to this type of user
----------------------------------
hourly_rate, tax_status

==================================

User Type: Student
Fields unique to this type of user
----------------------------------
instrument, monthly_charge, program

==================================

User Type: Employee
Fields unique to this type of user
----------------------------------
hourly_rate, location

This is a brief example of the types of fields that can be similar and unique between these types of users.

The possible setups I've thought of are:

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

or:

Table: `users`; contains all possible columns for all users and allow columns that could be filled for one user type but not another to be NULL

My question: Is one of these a better approach over the other or are both terrible and I should look at something else entirely?

I would advice 1st one is better approach , which is

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

Reason:-

User related data like columns in students, instructors etc tables may increase in future so it will be easy to manage with 1st approach.

and choosing the other options to create a table with too many numbers of columns which would be not good in terms of future database management and with increasing number of columns in future it will more problematic.


SQL Server : multiple databases with transaction log on one SSD array I know for HDD arrays that having multiple log files from different databases has a performance hit as it changes the IO pattern from sequential to random for the array.  So having an array per database for it's logs is a best practice.

Does anyone have any suggestions about running multiple SQL Server databases with the log files for each of those databases existing on one SSD array?

Does anyone have experience with the differences of Hitachi's FMDs vs SSDs for their HUS hardware?  I'm concerned with consolidating individual drives down to a few FMDs and not being able to sustain performance between the user databases and tempdb if all I have is disk pool of 4 FMDs for everything.

Changing from sequential to logical read / write patterns is far more harmful on rotational drives than SSDs.  Because SSDs have no read heads, latency is much lower, even on non-sequential reads and writes.  You will take a performance hit if it's not sequential, but with higher-end SSDs, you'll be able to get close to or saturate bus speeds (SAS / SATA) on the system.  You'll need to make sure that whatever you're driving your array with can sustain the I/O.  I've found CrystalDiskMark benchmarks w/ 4k @ 32QD have been accurate indicators of performance on a busy system.  All that said, if your SSD array can sustain the same IOPS across all logs with random I/O as what your rotational drives can do sequential, you'll be fine.

I don't have any experience with FMDs vs SSDs.


How to avoid DROP TABLE for a specific MySQL table I have used a Wordpress plugin that for some unknown reason is always deleting a database table (cerber_files).

The plugin's author is not giving support for this issue.

I think in order to solve this, there would be some option that would prevent DROP TABLE from being executed ONLY FOR THIS TABLE.

I've researched a lot but found no solution that worked for this issue.

Any idea?

prevent DROP TABLE from being executed ONLY FOR THIS TABLE

But dropping some another table in the same database must be available?

If so the task cannot be solved using MySQL privilege system. Because some privilege granted to or revoked from some object affects on all its subobjects. Revoking DROP on some database revokes it for all tables in this database. No separate table  privilege.

But the task can be solved using FOREIGN KEY subsystem.

Create some fake table. Set some field in this table as referenced to the table in question. And server will forbid your table drop until this fake table (or FK from it to your table) drop.


Starting MySQL with SSL option using systemd Can someone explain how to start a MySQL server with SSL options enabled using systemd? I've generated certificates and edited the my.cnf file to enable SSL but I'm not sure how to restart MySQL so that it allows SSL connections to be made with systemd.

This command did the trick before systemd but it now now longer works:

/etc/init.d/mysql start --ssl-ca=/etc/mysql/newcerts/ca.pem --ssl-cert=/etc/mysql/newcerts/server-cert.pem --ssl-key=/etc/mysql/newcerts/server-key.pem

Any help would be much appreciated.

You can the SSL variables into the my.cnf file and load on regular systemctl call.


moving to SQL Enterprise 2014 from Standard 2012 I just need your help in understanding of having SQL Server Standard 2012 and 2014 Enterprise version.

We do have CRM application on-prim. which runs based on SQL server. we do have right now 2012 standard version installed. We do have to runs 100's of reports which is based of SSRS and hitting every time to OLTP system.

We are thinking to get Enterprise 2014 which has "High Availability " or something like that option which is I guess live copy of the OLTP database.

If we do this, I believe it will be easier to run all SSRS report from copy as compared to direct OLTP DB.

Please let me know if this is viable option or there are other more practical approaches as compared to this!

Thanks.

It sounds like you are referring to Always On availability groups with a read only replica.  2012 EE as well as 2014 EE have this as an option and you can route reports to the replica without impacting the primary OLTP database.

Product Documentation


Foreign key for rows added later I'm using a queue to crawl pages and track links between them.

I have a table called page with a primary key called page_id.

I have a table called links with two columns: link_from, link_to

The queue takes a page, saves it, collects all links from it and saves those. The link_to value in some rows points to a non-existent row (temporarily, until the target pops out of the queue).

From a long-term point of view, I'd like to connect them with foreign keys (for multiple reasons, e.g. delete cascading, automatic relationship discovery from an application which uses the database).

How do you do such a thing? I can't insert the row with a foreign key reference until the row exists.

I would suggest the following:

Allow null foreign keys in the main table.
Insert rows minus their foreign keys in the import.
Add a table ImportRelationships that stores relationships during importing.
Rectify the foreign keys in the main table at the end of the import based on ImportRelationships.
Delete the rows from ImportRelationships.


ADMINISTER DATABASE TRIGGER privilege What does ADMINISTER DATABASE TRIGGER privilege actually mean?

I have the following case:

I have 2 databases: DB1 and DB2

On DB1 I have a trigger which arises after logon on database and checks if this username exists in some table. If not raises application error like "You are not permitted to log on!"

I created link on DB2, which is connecting to DB1 by some username, for example by kupa_dba.

Note that kupa_dba is not in an access list(in the table storing usernames that can connect to the database)

If kupa_dba doesn't have ADMINISTER DATABASE TRIGGER than trigger works, BUT if kupa_dba has ADMINISTER DATABASE TRIGGER  privilege there is no application error raising, trigger is ignored and DB2 can retrieve data from DB1

The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers).  It also allows you to log in regardless of errors thrown by a login trigger as a failsafe.  If you inadvertently coded your login trigger to throw an error no matter who was logging in, for example, you need to allow someone to log in to fix the trigger.

It appears that the trigger is behaving as expected in this case.  You wouldn't in reality create a database link from one database to another using a DBA account.


Display one row over another if there are two Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.

If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.

Any way to do that?

SELECT DISTINCT
  Employees.ID,
  Employees.FName,
  Employees.LName,
  Address.City,
  Address.State,
  Site.ID
from Employees
  Left Join Address on Employees.ID = Address.ID
  Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
  SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID

Example result set:

1234   John   Williams   Sacramento   CA   1
1234   John   Williams   Portland     OR   2
1111   Mary   Johnson    Sacramento   CA   1
1112   James  Stoller    Carson City  NV   2

Would like this back:

1234   John   Williams   Sacramento   CA   1
1111   Mary   Johnson    Sacramento   CA   1
1112   James  Stoller    Carson City  NV   2

Wrap your query in a common table expression and add a row number column like this

SELECT DISTINCT
  Employees.ID,
  Employees.FName,
  Employees.LName,
  Address.City,
  Address.State,
  Site.ID,
Row_Number() over(partition by Employees.ID order by Site.id) as rn

Then, select from the common table expression where rn = 1


MySql: does max_user_connection concern SUPER user? On MySql 5.7 I've setup variable max_user_connection = 35.
I have several users, their number of simultaneous connections is effectively limited.
Now I have created a brand new user with SUPER privilege. Is this user's connection is concerned by the max_user_connection variable too ?
Thanks
Edit: I know the max_user_connection documentation, but this specific point is not covered.

No, it the limit counts for every connection independent of which kind of user connects

This limits the number of simultaneous connections that can be made by any given account

See manual


How to exclude NULL values inside CONCAT MySQL? If I have this - tadd is the Address table:

CONCAT(tadd.street_number, ' ',
            tadd.street_name,', ',
            tadd.apt_number,', ',
            tadd.city,', ',
            tadd.postal_code,', ',
            tadd.country) AS 'Address'

Is there a way to exclude the apt_number if it doesn't exist?

I was thinking of:

WHERE tadd.apt_number IS NOT NULL

But it will return only those rows with apt_number, and even if something works how do I then deal with that extra comma.

If it's a duplicate please post a link in comments.

If you want to skip NULL values (but not empty strings), you can use CONCAT_WS() function:
CONCAT_WS( ', ',            -- Separator
           CONCAT_WS(' ', tadd.street_number, tadd.street_name),
           tadd.apt_number,  tadd.city,
           tadd.postal_code, tadd.country
         ) AS Address

From the docs:

CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments.  If the separator is NULL, the result is NULL.
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.


Trying to return a complete column from one table, and a count from another table I currently have two tables that I am trying to pull data from, one being Customers and one being Bills.

Customers is a static table that just lists all the customers, but the reason I need this table is because it provides the actual human-friendly customer name instead of the less helpful numeric customer ID.

Bills is a frequently updated table and I want to count the number of updates per day for each customer. So I came up with the following:

SELECT c.name, COUNT(DISTINCT b.billid) from customers c
JOIN bills b
ON b.customerid = c.customerid
WHERE b.createdate = CURDATE() -INTERVAL 1 DAY
GROUP BY b.customerid;

This works wonderfully with one problem: There are some clients that don't process on a given day. And while this is currently a small select overall (I get about 24 out of a max of 31 rows back) this data is going to be used in metrics reports and transposing the data with these missing rows could eventually become a problem.

What I want is to be able to get EVERY name from Customers showing up in the first column, and then if there is no data from Bills for that customer that day, I'd like it to show up as 0 or NULL. I've been banging my head against the wall for a while now, and I'm not sure I can actually do this or not in MySQL.

I think I was able to accomplish what you want using the following query:

SELECT c.name, coalesce(billcount, 0) FROM customers c
LEFT JOIN (
    SELECT c.name, c.customerid, COUNT(DISTINCT b.billid) as billcount from customers c
    JOIN bills b
    ON b.customerid = c.customerid
    WHERE b.createdate = CURDATE() -INTERVAL 1 DAY
    GROUP BY b.customerid
) bc
on bc.customerid = c.customerid

Essentially I just put your original query in a subquery (the indented area between the parenthesis), and left joined the columns you wanted with the results from that subquery.

This will always return a full list of customers and the number of bills they have, if any. Right now it's returning 0 for the customers that don't have any bills in the given time range. If you prefer it to return null, omit the coalesce() function and just use billcount in the column list on the first line. (Obviously you can rename this column to whatever you want.)


How to have MySQL Slave Replicate From a Master's Master We are using the Following:

MySQL 5.1.63-community-log MySQL Community Server (GPL)
OS : Red Hat Enterprise Linux Server release 5.4

We have three servers and all servers are enabled with binary logging option

Server A : Running master on it.
Server B : it is slave of Server A but also enable binary logs
Server C : it is slave of Server B.

Our goal to change master server of server c from server B to server A.

Is that possible in run time without any data corruption?
What are prerequisites for this?
How can we achieve this?

Step 01

On ServerB, run the following commands

STOP SLAVE;
SET GLOBAL innodb_max_dirty_pages_pct = 0;
FLUSH TABLES;

Step 02

On ServerC, run SHOW SLAVE STATUS\G

Repeat running SHOW SLAVE STATUS\G until Seconds_Behind_Master is 0

Then, run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Step 03

On ServerB, run SHOW SLAVE STATUS\G

For the sake of example, let say SHOW SLAVE STATUS\G looks like this:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.64.68.253
                Master_User: replusername
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.003202
        Read_Master_Log_Pos: 577991837
             Relay_Log_File: relay-bin.010449
              Relay_Log_Pos: 306229695
      Relay_Master_Log_File: mysql-bin.003202
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 577991837
            Relay_Log_Space: 306229695
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0

Please note the following:

Master_Host (10.64.68.253)
Master_User (replusername)
Relay_Master_Log_File (mysql-bin.003202)
Exec_Master_Log_Pos (577991837)

Step 04

On ServerC, run the following:

STOP SLAVE;
SET GLOBAL innodb_max_dirty_pages = 0;
FLUSH TABLES;
CHANGE MASTER TO
MASTER_HOST='10.64.68.253',
MASTER_PORT=3306,
MASTER_USER='replusername',
MASTER_PASSWORD='replpassword',
MASTER_LOG_FILE='mysql-bin.003202',
MASTER_LOG_POS=577991837;
START SLAVE;

Step 05

On ServerC, run SHOW SLAVE STATUS\G

If Seconds_Behind_Master is a Number, CONGRATULATIONS !!!

Step 06

On ServerB, run SET GLOBAL innodb_max_dirty_pages_pct = 90;

On ServerC, run SET GLOBAL innodb_max_dirty_pages_pct = 90;

Give it a Try !!!

CAVEAT

If the majority of your data is MyISAM, ignore all commands that change innodb_max_dirty_pages_pct.


what is the syntax for insert trigger? I am very new to sql. feel a little stupid to ask this question

Say I have this table Warehouse(shipperid, name , state). Then I define another table warehouse_maine (shipperid, name, state) which should only contain the warehouse located in maine. after I insert all the data for warehouse. I want to create a trigger for warehouse_maine, which insert the warehouse data only in maine state.

CREATE TRIGGER TRIGGER01 ON WAREHOUSE_MAINE
FOR INSERT
AS select * from warehouse where warehouse.state= maine

This is what I thought but it's obviously wrong. what should be the syntax after keyword AS?

What you are looking for is to actually do the INSERT into warehouse_maine after Warehouse

DELIMITER $$
DROP TRIGGER IF EXISTS Warehouse_InsertAfter $$
CREATE TRIGGER Warehouse_InsertAfter AFTER INSERT ON Warehouse
FOR EACH ROW
BEGIN
    IF NEW.state = 'maine' THEN
        INSERT IGNORE INTO warehouse_maine (shipperid, name, state)
        VALUES (NEW.shipperid, NEW.name, NEW.state);
    END IF;
END;
DELIMITER ;

This trigger will do the job. If you are doing a bulk INSERT of rows into the Warehouse table, this trigger will slow things down a bit. If you plan to do such bulk INSERTs, you are better off doing that in a single INSERT:

INSERT IGNORE INTO warehouse_maine
SELECT * FROM Warehouse WHERE state='maine';


MySQL - GROUP BY not working with multiple CASE WHEN statements I am working on a database with Kickstarter database, and I'm trying to:

Define short, medium-lengthed, and long campaigns
See how much each length of campaign raise
Convert different currencies into USD

My original code is as follows:
SELECT
CASE
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign'
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END AS 'Campaign Length',
CASE
    WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
    WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
    WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
    WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
    WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
    WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
    WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
    WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
    WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
    WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
    WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
    WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
    WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
    ELSE ROUND(SUM(campaign.pledged),2)
END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY `Campaign Length`;

I'm expecting a result of:
Campaign Length.      ---- Amount Raised
Short Campaign.       ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Long Campaign.        ---- XXXXXXXXXXXXX

Where XXXXXX is the aggregate of the pledged amount after conversion. The original code shows:
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ks_data.currency.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So I improvised by adding additional GROUP BY, after realizing I can't GROUP BY Amount Raised:
SELECT
CASE
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign'
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END AS 'Campaign Length',
CASE
    WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
    WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
    WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
    WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
    WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
    WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
    WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
    WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
    WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
    WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
    WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
    WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
    WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
    ELSE ROUND(SUM(campaign.pledged),2)
END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY `Campaign Length`,currency.id;

But the result is instead not grouping as I intended:
Campaign Length.      ---- Amount Raised
Short Campaign.       ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Long Campaign.        ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Short Campaign.       ---- XXXXXXXXXXXXX

I've tried many ways, but couldn't find a solution

A syntactical version that should work (untested due to lack of ddl and sample data) is:
SELECT
CASE
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign'
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END AS 'Campaign Length',
CASE
    WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
    WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
    WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
    WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
    WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
    WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
    WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
    WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
    WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
    WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
    WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
    WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
    WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
    ELSE ROUND(SUM(campaign.pledged),2)
END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY CASE
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign'
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END, currency.name;

I agree with @Akina that the result will be confusing when several currencies are involved. The result may end up like:
Long Campaign   42
Long Campaign   36
...

and you basically have no idea what row that represents which currency


how to uniformly select rows in sql I am new to here and please let me know if I didn't make the question clearly.

If I have a m rows in a table and I want to select n rows uniformly. In other words, the interval between first row and next row is m / n.

For example, if i have a 10 rows and I want to select 5 rows out of them, then the selected rows would be 1, 3, 5, 7, 9 because the interval is 10 / 5 = 2.

Does anyone can help?

You can use row_number and the modulo operator

Tables don't have any inherent order. You need to define an ordering expression (e.g column names) to define what "first" and "next" mean.

DECLARE @StepSize int = 2;

WITH CTE AS
(
SELECT *,
       ROW_NUMBER() OVER (ORDER BY YourOrderingColumn) AS RN
FROM YourTable
)
SELECT *
FROM CTE
WHERE RN % @StepSize = 1;


SQL Server update and database transfer In my test network there is a database created with SQL Server 2005. I have to update SQL Server 2005 to SQL Server 2008. The data base should be used with SQL Server 2008. So it is to be moved. What is the best way to transfer the database during/after SQL Server update?

If you are upgrading in place, you shouldn't need to transfer any databases; the instance will retain information on databases.

If you are installing a new named instance or installing it fresh on a different server, you have two major options:  deatch-and-attach, and backup-and-restore.

The detach-attach option is pretty quick:  detach the current database using sp_detach_db or in SSMS, right-click on the database and go to Tasks --> Detach.  Then, you can cut and paste the database files to their new location.  On the new instance, use sp_attach_db or in SSMS, right-click the Databases folder and select "Attach..." from the menu options.

The backup and restore option is pretty simple:  take a backup of the current database.  Then, copy that backup someplace where the new instance can see it, and restore from that backup.  You'll have a copy of the current database on the 2008 instance, as well as on the 2005 instance.

Here are some considerations:

If you need the 2005 database up during this time, use backup-and-restore.
The larger the database, the more likely you'd want to detach and attach, especially if this is staying on the same server and you're under a time crunch.  Taking backups and restoring from backups can take longer than shuffling bits across the WAN (or, best-case, updating a file pointer in Windows).  It also will mean more disk space in use, as backups take up some non-zero amount of space.
If you are currently using replication, you can't detach until you disable publishing (as the BOL link above indicates).
If you currently have any snapshots of the database, you can't detach until you drop all snapshots.  If you don't want to drop those snapshots, go with the backup and restore method.


Table Corruption Error I have a problem with my SQL Server database. Today I opened it and saw an error – SQL Server Table Corruption Error.

I don't know the reason for this error. I applied SQL Server Management Studio, but it couldn't completely restore my database.

Does someone know methods of repairing SQL Server databases?

Does the database open and you just can't view a single table, or are you unable to browse your database at all? Are other databases on the server affected, or just one? What's the current database status - offline, suspect, or something else? Each can mean different steps to correct or repair the issue.

Also, if you've got a backup handy, you'll want to keep it nearby in case you can't repair the corruption that caused this problem.

If you can open the database, to start you'll want to scan it with DBCC and see if anything obvious comes up:

DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS

From there, deal with the error messages or warnings that you see - it should provide enough detail that you're able to search for the specific issues (or update your question to include those details so that we can help you more directly).

SqlMag has a great series on corruption, what it is, and how to respond to it, starting here:

http://sqlmag.com/blog/sql-server-database-corruption-part-i-what-corruption

Jump ahead to section 9 if you want to see their suggestions for fixing it, but be sure you understand what you're dealing with before you take any action.


Database migration program, that can analyze source and target db, and tell diff I'm looking for a program that can help with migration of both database schema and data from a source db, to a target db.
And it has to be able to do it while target db stays live, all the time. So clearing target db, and doing a full migration, is not an option.

We usually use Navicat SQL Server for this, but I'm just wondering, if there is any alternatives out there.

It would also be nice, if the program can make a database diagram, for the SQL servers that does not support the way SSMS does this. (like azure db's)
And unfortunately SSMS isnt up to this task.

What would a working alternative be to using this program?

There are multiples alternatives out there. I would recommend these two:

Idera comparison Toolset and the Red Gate SQL Compare & Red Gata Data Compare. All of them offer full funcionality during the trial period so you can take a look and decide which one is better for you. They work in a similar way: you select source and target databases, compare and generate deployment script with the differences.
For database diagram you can also use the Red Gate Dependency tracker.

I use Redgate ones everyday and used Idera in the past so let me know if you have any issue.


How Plan Explorer Group Operators in Query Columns Tab The two Index Seek (highlighted in green and red) are for filtering, they did not group in Query Columns tab. But the two Object3 highlighted in red are grouped together instead. Here is the XML Plan.

Does anyone has idea about why PE group the two Object3 highlighted in red and leave the Object3 highlighted in green in another group. I'm sorry asking such a stupid question, because I am a newbie in SQL tuning.

Here is the "Query Columns" tab I am talking about. Plan Explorer grouped the Index Seek and Index Scan. Actually, Plan Explorer will group Index Seek/Scan and Key/RID Lookup together. In this case, I understand the purpose. It help us considering adding the lookup column as an INCLUDE column to an index.

But I don't understand the purpose behind why PE grouped the highlighted Index Seek and Index Scan together!

At first, it looks like you've modified XML, because the query does not exactly match the plan.

At second, format your query like this

SELECT o2.Column1,
       o2.Column2,
       o1.Column3,
       o1.Column4,
       o1.Column5
  FROM Object1 as o1,
       Object2 as o2,
       Object3 as o3
 WHERE o1.Column3 = o3.Column6
   AND o2.Column1 = o3.Column7
   AND o3.Column8 between ? and ?
   and o3.Column7 < ?

At third, instead of having separate indexes on Column7 and Column8 (green and red seeks) you better create one index on Column7 + Column8 (green first) and have included Column6. That will help you to avoid Red Scan.

At fourth, (optional) you can create index on o1.Column3 with included columns o1.Column3, o1.Column4, o1.Column5


How do I disable foreign key constraints in my Postgres dump file? I’m using Postgres 9.5.  I have created a dump file of INSERTs for three of my tables.  To avoid getting foreign key errors, I added the below to the top and bottom of my file (the “…” is where all the INSERTs are) …

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;
ALTER TABLE addresses DISABLE TRIGGER ALL;
ALTER TABLE table1 DISABLE TRIGGER ALL;
ALTER TABLE table2 DISABLE TRIGGER ALL;

…

ALTER TABLE addresses ENABLE TRIGGER ALL;
ALTER TABLE table1 ENABLE TRIGGER ALL;
ALTER TABLE table2 ENABLE TRIGGER ALL;

However, even when i run this file, I get errors like

ERROR:  insert or update on table "table2" violates foreign key constraint "fk_rails_ba656ceafa"
DETAIL:  Key (table1_id)=(f62c5fee-1031-4d5e-a084-9210f052a2d1) is not present in table "table1".

Why am I getting these errors despite disabling foreign keys and more importantly, how can I prevent them?  I want to INSERT all the data and then re-enable foreign keys.

I'm not sure what's happening, but you're certainly not giving us enough information. ALTER TABLE DISABLE TRIGGER ALL works fine on disabling FOREIGN KEYS.

test=# CREATE TABLE table1 ( id serial PRIMARY KEY );
CREATE TABLE

test=# CREATE TABLE table2 ( fkey int REFERENCES table1(id) );
CREATE TABLE

test=# INSERT INTO table2 VALUES (1);
ERROR:  insert or update on table "table2" violates foreign key constraint "table2_fkey_fkey"
DETAIL:  Key (fkey)=(1) is not present in table "table1".

test=# ALTER TABLE table2 DISABLE TRIGGER ALL;
ALTER TABLE

test=# INSERT INTO table2 VALUES (1);
INSERT 0 1

I would never suggest anyone DISABLE TRIGGERS though, or make them NOT VALID. They're there to ensure data integrity and disabling them for even brief periods is a recipe for disaster and an anti-pattern in my opinion.

Just pg_dumpall and trim out what you don't need.


Increase performance WITHOUT adding indexes I'm having a rather large table (350MB, 1.3M rows) and there are dozens of queries arriving at this table every second. Most queries are super-fast thanks to indexes - but some others aren't.

Obviously I'm using indexes for the most common queries on this table; but I always hesitated to add indexes for queries which are only performed every few seconds or even minutes. They are just a minority compared to the more important queries.

As far as I know, adding more indexes makes inserting new rows and updating existing data slower (and UPDATE/DELETE/INSERT queries happen very frequently for this table); but adding indexes also makes normal SELECT queries slower, since MySQL has to find the perfect index first and has to check more index possibilities.
Obviously, the slightly slower SELECT statements wouldn't be a problem; but I'm more worried about the UPDATE queries.
But on the other hand: I don't want these rare queries to take 1+ seconds, either.

Is there anything else I could do?
These performance heavy queries are creating temp tables and I thought about increasing the tmp_table_size (it's currently 16MB and I'm having 6+GB of unused RAM space). But then I read that this makes things even worse.

Join/Sort buffer sizes are already at 128/256MB
(Now that I think about it: These two variables are lower than the table size itself. That might be a problem; though I don't want to increase variables without knowing exactly what I'm doing.)

I wrote some posts about RAM disk and temp tables

Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
Jan 17, 2012 : How can I optimize my mysql setup to create my index faster?
Sep 23, 2011 : skip copying to tmp table on disk mysql

The basic idea would be to set set tmp_table_size and max_heap_table_size to the bare minimum value so that move temp tables to the RAM disk quickly.

This would only help if your queries produce temp tables and you do not wish to tune tables and indexes. However, it is imperative to tuning queries and use indexes to produce the smallest possible temp tables before tuning the tmp_table_size and max_heap_table_size options or even go the route of the RAM Disk.

Nevertheless, since you have 16GB on the server, you could set up good 2G RAM disk, configure good settings for join, sort and read buffers per connection, and sufficient RAM left for the OS.

UPDATE 2014-11-25 11:09 EST

Your last comment

  I'm wondering what's the difference between temp tables on a RAM Disk and temp tables in "memory"? I think that's the same, isn't it? Therefore it makes no difference wheter I set tmp_table_size & max_heap_table_size to a high or a low value - RAM is used in both situations. Or am I wrong here?

It makes a big difference. Why ?

If you set tmp_table_size/max_heap_table_size too high, each DB Connection will needlessly glut memory (This is in addition to other per-connection buffers : return my.conf values to sane levels (total memory footprint = 220GB)) I have seen customers set these to 100G to avoid thinking about this issue (Bad decision).
If you set tmp_table_size/max_heap_table_size high and queries need just a little more here is what has to happen:

You have tmp_table_size/max_heap_table_size set at 64M
You makes a temp table that takes up more that 64M (let's say 66M)
Temp table reaches 64M
Query suspends processing
Temp table is migrated to disk
Last 2M needed is loaded into the temp table on Disk
Query continues processing
This is counted in Created_tmp_disk_tables

HERE IS MY POINT : If you set tmp_table_size/max_heap_table_size low on purpose (let's sa the minimum value of 1K), the temp table will materialize in the RAM Disk faster because it only has to stop and transfer 1K from RAM to Disk.


Stored procedure deployment - push or pull into database? Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and any other reasons for adopting a push or pull approach.

For example:
Push approach, the stored procedure would be in the Staging database and would push data into a table in the destination database.

Pull approach, the stored procedure would be in the destination database and would pull data from the Staging database.

Both approaches would involve cross-database queries and neither option appears to be perfect, though I prefer to keep all my ETL stored procedures and supporting objects in the Staging database away from others.

What approaches are people generally implementing?

Although it's all opinion I strongly favour using pull techniques for the following reasons:

I think it's easier to keep track of the dependencies by using a purely pull-approach. In a way it's similar to the whole concept of queries on tables or queries on views on tables. It's all "pull", not "push".

Most DBMSes have better control of transactions etc in the current database vs the remote database when running code in stored procedures.
For example, in Oracle, you can't truncate a table on the other side of database-link. So you'd require a helper-package on the other side to perform these actions. Much better to have the stored procedure execute the truncate locally and pull the data to populate the table.

I think "pull" is a better fit to the requirements of a reporting data-warehouse / reporting system. These tend to run at scheduled times.
If you have an architecture that does incremental updates of the warehouse or reports, then "push" is a better fit. I don't like to mix the flows.

Talk to your admins / ops team. If I was the DBA, I would prefer to admin systems that "pull" there data from others. I think it's easier to manage the cascading effects of eg down time in this way.

Just some of the reasons and the ones I can think of right now.


listagg in order and including a sequence of separator when missing any row of a sequence Sorry for the long title but it's a little bit difficult to explain.

We have a table

| N rows | Atrib1  |    Atrib2 | Atrib3 | Atrib4 |  Final row |
---------------------------------------------------------------
| N1    |   R     |     X       | One   |  A1    |     True   |
| N1    |   D     |     Y       |       |        |     False  |
| N1    |   K     |     Z       |       |        |     False  |
| N1    |   N     |     A       |       |        |     True   |
---------------------------------------------------------------

We must aggregate all the Atribs column into a single line with ';' separator in the order marked in the table, because we are trying to do an export to a CSV file.

We have no problem with the aggregation but there is a special condition that we must follow. If a row or more rows, from sequence (R, D, K, N) for Atrib1, doesn't exist we must even include a set of ';' for each Atrib(1..4).

The query that we have already works fine if all the rows are filled but not when someone is missing.

select N rows, LISTAGG(Atrib1 ||';'|| coalesce(Atrib2,'') ||';'|| coalesce(Atrib3,'') ||';'|| coalesce(Atrib4,''), ';') WITHIN GROUP (ORDER BY Code_crit_0) "Employees", Final row
from TABLE
group by N rows
order by decode(Code_crit_0, 'R',1,'D',2,'K',3,'N',4, 5) asc;

Any idea of how to accomplished this special condition without any SQL procedure.

Thanks in advance.

We have no problem with the aggregation but there is a special
  condition that we must follow. If a row or more rows, from sequence
  (R, D, K, N) for Atrib1, doesn't exist we must even include a set of
  ';' for each Atrib(1..4).

Suppose you have the following table (called TEST).  Some of the the "nrows" - N2, N3, N4, N5 - do not have a complete set of (R,D,K,N) values.

create table test ( nrows, a1, a2, a3, a4, final )
as
select 'N1', 'R', 'X', 'One', 'A1', 'True' from dual union all
select 'N1', 'D', 'Y', null, null, 'False' from dual union all
select 'N1', 'K', 'Z', null, null, 'False' from dual union all
select 'N1', 'N', 'A', null, null, 'True'  from dual union all
-- N2: D, K, N missing
select 'N2', 'R', 'X', 'two', 'B1', 'True' from dual union all
-- N3: R, K, N missing
select 'N3', 'D', 'Y', null, null, 'False' from dual union all
-- N4: D, R, N missing
select 'N4', 'K', 'Z', null, null, 'False' from dual union all
-- N5: D, K, R missing
select 'N5', 'N', 'A', null, null, 'True'  from dual ;

select * from test ;
NROWS  A1  A2  A3    A4    FINAL
N1     R   X   One   A1    True
N1     D   Y   NULL  NULL  False
N1     K   Z   NULL  NULL  False
N1     N   A   NULL  NULL  True
N2     R   X   two   B1    True
N3     D   Y   NULL  NULL  False
N4     K   Z   NULL  NULL  False
N5     N   A   NULL  NULL  True

As a first step, you could write a query that generates the missing nrows/A1 combinations (NA1), and LEFT JOIN it to the original (O) table.

select *
from (
  select nrows, a1
  from (
     select unique a1 from test
  ) cross join (
     select unique nrows from test
  )
) NA1
left join  (
   select nrows, a1, a2, a3, a4, final from test
) O
on NA1.nrows = O.nrows and NA1.a1 = O.a1
;
-- result
NROWS  A1  NROWS  A1    A2    A3    A4    FINAL
N1     R   N1     R     X     One   A1    True
N1     D   N1     D     Y     NULL  NULL  False
N1     K   N1     K     Z     NULL  NULL  False
N1     N   N1     N     A     NULL  NULL  True
N2     R   N2     R     X     two   B1    True
N3     D   N3     D     Y     NULL  NULL  False
N4     K   N4     K     Z     NULL  NULL  False
N5     N   N5     N     A     NULL  NULL  True
N2     K   NULL   NULL  NULL  NULL  NULL  NULL
N2     N   NULL   NULL  NULL  NULL  NULL  NULL
N4     D   NULL   NULL  NULL  NULL  NULL  NULL
N3     R   NULL   NULL  NULL  NULL  NULL  NULL
N2     D   NULL   NULL  NULL  NULL  NULL  NULL
N3     N   NULL   NULL  NULL  NULL  NULL  NULL
N3     K   NULL   NULL  NULL  NULL  NULL  NULL
N4     N   NULL   NULL  NULL  NULL  NULL  NULL
N4     R   NULL   NULL  NULL  NULL  NULL  NULL
N5     D   NULL   NULL  NULL  NULL  NULL  NULL
N5     K   NULL   NULL  NULL  NULL  NULL  NULL
N5     R   NULL   NULL  NULL  NULL  NULL  NULL
-- 20 rows selected.

Then, use LISTAGG and DECODE (if CASE ... , as used in the example below, is too "bulky") and replace the NULLs with ;

select
  NA1.nrows
, NA1.a1    as "RDKN"
, listagg ( O.a1 || ';' ||
    case when O.a2 is null then '' else O.a2 end || ';' ||
    case when O.a3 is null then '' else O.a3 end || ';' ||
    case when O.a4 is null then '' else O.a4 end || ';'
  , ';' ) within group ( order by O.a1 ) "Attribs 1-4"
from (
  select nrows, a1
  from (
     select unique a1 from test
  ) cross join (
     select unique nrows from test
  )
) NA1
left join  (
   select nrows, a1, a2, a3, a4, final from test
) O
on NA1.nrows = O.nrows and NA1.a1 = O.a1
group by NA1.nrows, NA1.a1
;

Result

NROWS  RDKN  Attribs 1-4
N1     D     D;Y;;;
N1     K     K;Z;;;
N1     N     N;A;;;
N1     R     R;X;One;A1;
N2     D     ;;;;
N2     K     ;;;;
N2     N     ;;;;
N2     R     R;X;two;B1;
N3     D     D;Y;;;
N3     K     ;;;;
N3     N     ;;;;
N3     R     ;;;;
N4     D     ;;;;
N4     K     K;Z;;;
N4     N     ;;;;
N4     R     ;;;;
N5     D     ;;;;
N5     K     ;;;;
N5     N     N;A;;;
N5     R     ;;;;

20 rows selected.

Tested with Oracle 12c, and Oracle 11g. Dbfiddle here.


Data Warehouse measures without a corresponding dimension I have an operations database that contains this table:

salesQuality salesPrice salesCategory

According to my business requirements, the salesQality and salesPrice are measures.

However, salesCategory is a dimension.

Unfortunately, in the operations database, there are some rows without the salesCategory value.

What should I do in the fact table to represent these sales that don't have a category value in the category dimension?

The standard approach for handling missing dimensional data would be to enter a null value for the relevant data in the fact table.

If this approach is problematic for some reason (table constraints, business rules) , adding a dimensional value to represent 'null' would be acceptable, if less technically correct. If you choose to implement this solution, keep in mind that you will need to consider that null <> null for incremental processing.

Another possible solution would be to encourage the business (modify the application, change the data feed, consult with the outside data vendor etc.) to define a more robust salesCategory dimension- that is, define more cases for the dimension so that the number of missing values is reduced or eliminated and less holes in the data are present.


creating trigger which will insert into table from view I have view LIVE_MATCHES (home,away,comp_name) and
table MATCHES (priority ,sport,sub_list)

I need to create a trigger that inserts in TABLE from VIEW after the view is updated or inserted (doesn't matter) and to check that unique column (sub_list) in table Matches isn't already inserted. Only need to insert from view is comp_name, sport and priority are fixed variables.

 CREATE OR REPLACE TRIGGER LIVE_MATCHES_TO_MATCHES
        instead of insert ON LIVE_MATCHES
       for each row
              declare
       p_priority number:= 1;
       p_sport number:=0;
       begin
         insert into MATCHES (sub_list , priority , sport, created)
         select :old.comp_name , p_priority, p_sport,sysdate
         from dual
          where not exists (
         select 1 from MATCHES
        where sub_list = :old.comp_name);
       end;

I edited trigger so it is compiled now without errors but it does not insert into table MATCHES.

What am I doing wrong in this trigger.

SQL*Developer

SQL*Developer has a bad habit of interpreting the :new and :old identifiers of a trigger as run-time BIND variables.  Usually, a window will pop up asking for a value for the BIND variable new and/or old.

Workaround

One workaround is to edit the TRIGGER directly by going trough Connections.

A simple "recompile" works.


Optimization of complicated sums and counts in related tables I have a categories table, which have two columns, id and name.

I have a types table, which have three columns, id, category_id and points.

I have a products table, which have three columns, id, type_id and customer_id.

This means each product has a type, and each type has a category. One category has many types, one type has many products.

I am using MySQL and PHP.

From PHP, given a customer_id, I want to know how much points that customer has in each category separately (and display a table to the user, for example).

My solution:
I would first do a query to find what are all category ids, then for each category_id I would find all types inside that category (that is, all types with the given category_id); then for each type_id I would count how many products have both that type_id and the given customer_id, and I would multiply that amount for the points of the given type_id. Adding all the results for each type_id, I have in the end the total points the given customer has on that category_id. And as I said, I would have to do this for each category_id. My solution will work, but seems to me that it would be VERY slow. I am looking for advice on how to solve this problem in a better way - with less queries, or faster queries, or both. The way it is now, it can potentially do as many queries as the amount of categories.

Example:

/* TABLE: categories */

name           id
A              1
B              2
C              3

/* TABLE: types */

points         id          category_id
1000             1                1
500              2                1
200              3                3
50               4                2
20               5                3
1                6                3

/* TABLE: products */

id           type_id          customer_id
1               2                   13
2               4                   13
3               5                   13
4               6                   13
5               5                   13
6               3                   13
7               4                   13
8               5                   13
9               6                   13

If the given customer_id is 13, I want get something like the following:

500 total points in category A
100 total points in category B
262 total points in category C

1. What is a good way to do this without changing how my database is structured?

2. Is there a good way to change my database design to allow a better way to do this? (of course I have other fields in the tables that I omitted for simplicity).

Your question shows you are thinking in a procedural way, however you need to think in a "set-based" way.

The following query may help you understand this better:

SELECT p.customer_id
    , c.name
    , sum(t.points)
FROM products p
    INNER JOIN types t ON p.type_id = t.id
    INNER JOIN categories c ON t.category_id = c.id
GROUP BY p.customer_id
    , c.name;

Essentially, this should show you a list of customer_id, category name, and the total points each customer has in each category.  It accomplishes this through the use of an aggregate function (SUM) along with the GROUP BY clause.

(please take a look at my answer here for information about naming your ID columns)


What are the current best practices concerning varchar sizing in SQL Server? I'm trying to understand the best way to decide how big varchar columns should be, both from storage and performance perspectives.

Performance
From my research, it seems that varchar(max) should only be used if you really need it; that is, if the column must accommodate more than 8000 characters, one reason being the lack of indexing (though I'm a little suspicious of indexing on varchar fields in general.  I'm pretty new to DB principles though, so maybe that's unfounded) and compression (more a storage concern).  In fact, in general people seem to recommend only using what you need, when doing varchar(n)....oversizing is bad, because queries must account for the maximum possible size.  But it's also been stated that the engine will use half the indicated size as an estimate of the average actual size of the data.  This would imply that one should determine, from the data, what the average size is, double it, and use that as n.  For data with very low but non-zero variability though, this implies up to a 2x oversizing over the maximum size, which seems like a lot, but maybe it's not?  Insights would be appreciated.

Storage
After reading about how in-row vs. out-of-row storage works, and keeping in mind that actual storage is limited to actual data, it actually seems to me that the choice of n has little or no bearing on storage (besides making sure it's big enough to hold everything).  Even using varchar(max) shouldn't have any impact on storage.  Instead, a goal might be to limit the actual size of each data row to ~8000 bytes if possible.  Is that an accurate read on things?

Context
Some of our customer data fluctuates a little, so we generally make columns just a little wider than they need to be, say 15-20% bigger, for those columns. I was wondering if there were any other special considerations; for example, someone I work with told me to use 2^n - 1 sizes (I have found no evidence that's a thing though....)

I'm talking about initial table creation. A customer will tell us that they are going to start sending us a new table, and send sample data (or just the first production data set), which we look at and make a table on our end to hold the data. We want to make the table on our end to handle future imports as well as what's in the sample. But, certain rows are bound to get longer, so we pad them.

The question is how much, and are there technical guidelines?

Regardless of specific datatype, you need to be able to store whatever the application requests to be stored. You cannot specify something smaller than the max size of what will actually be saved.

You also do not need, nor want, to specify a column length larger than the maximum actual size that will be stored for a variety of reasons: query memory allocation, potentially filling up the maximum row size and not leaving any room for adding columns in the future, etc.

True, variable length string and binary columns do not have the storage implication that fixed-length datatypes (string / binary / numeric / date / etc) do (although, some of those implications can be nullified via data compression or use of the SPARSE column definition option). However, as you pointed out, even if no there is direct storage implication, there is still the performance implication of overestimating required memory for queries.

Be sensible. Use only what you need. Considerations can be made if there is a high probability that column length will need to increase in the near future, but keep in mind that it is easier to expand the size of a column than reduce the size.  Yes, some work will be involved, but since that work is merely "potential", while performance implications of over-sizing are "actual", it is often best to define columns based on what you actually need, not what you maybe-kinda-sorta think you might need in the future. Many changes that are talked about never happen, and often the changes that are required cannot be foreseen. Go with what you know.

  Instead, a goal might be to limit the actual size of each data row to ~8000 bytes if possible.

I am not exactly sure what you are getting at here. SQL Server will physically limit you to just over 8000 bytes. Using LOB types — VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, and the deprecated TEXT, NTEXT, and IMAGE types — allow for going beyond that initial page size limitation, but that is only due to placing a pointer (16 or more bytes, depending on the type, and depending on the size of the value being stored off-row when using the MAX types). The actual physical limit of the data page did not change.

Your goal should be to use the least amount of physical space to store what the app / business needs to store without breaking or truncating such that the incomplete value loses meaning or causes problems downstream. If you need to store a 12,000 character thingy, then use VARCHAR(MAX) because that is what is needed. If you are storing a phone number or postal / zip code, then it would be unwise to use VARCHAR(100), and irresponsible to use VARCHAR(MAX).

  some of our customer data fluctuates a little, so we generally make columns just a little wider than they need to be, say 15-20% bigger, for those columns. I was wondering if there were any other special considerations;

Don't all systems have at least some data that fluctuates? Any system that stores a person's name would qualify, right? There is a fairly large variance in length of names. And then you have someone like Prince go and change their name to a symbol and now you have an entirely different problem that is not length. This is just how things are.

But, to play devil's advocate for a moment: how can the "15-20% larger than what is needed" value not be the actual needed value? Let's say that there's a discussion about adding a new column, and someone suggests 50 characters, then someone else says, "well, 20% more is 60 so let's do 60 because someone might have 60." If it's true that a customer might have 60, then 60 is, and was always, the actual needed value, and 50 was wrong the whole time.

Of course, it would help if there was some indication as to the source of the data because:

if you make "URL" 1024 and someone needs 1060, then it needed to be 1060 (similarly, if you make URL VARCHAR and get complaints that it is messing up Unicode characters which are now allowed in domain names, then it needed to be NVARCHAR), but
if someone wants to add 1000 characters to a 500 character-limit comment field, then it still only needed to be 500. People can be less verbose in comments (a huge challenge for me ;-), but ProductSKU better be big enough to fit all of the customer's SKUs.

  I'm talking about initial table creation. A customer will tell us that they are going to start sending us a new table, and send sample data (or just the first production dataset), which we look at and make a table on our end to hold the data. We want to make the table on our end to handle future imports as well as what's in the sample. But, certain rows are bound to get longer, so we pad them. The question is how much, and are there technical guidelines?

You are making a lot of assumptions here. Sure some fields might get bigger. But then again, they might not. Or, some could get smaller. Some can change from being non-Unicode to being Unicode (once they realize that the world is getting smaller and one cannot assume that last names will only ever have basic ASCII / US English characters). Or, they could stop sending a field. Or they can add one or more fields in the future. Any combination of this and other things. So why focus only on VARCHAR columns? What if they are currently sending an INT value and in a year or two they reach the max value and starting sending a BIGINT? What if they have a "status" field with values 0 - 5. Are you just going to assume INT which is "padded" as it allows for growth, but should probably be TINYINT?

The only thing that you can safely predict is that trying to predict how your customers data will change will be wrong more often than it is correct. And being correct is a matter of luck / coincidence (if not luck, then just go play the lottery ;).

So the guideline is:

Don't waste time and energy on trying to answer an unanswerable question.
Instead, focus on getting as much information as possible regarding your customer's actual data, and go with that (i.e. data-driven decision making ;-).

You already have example data, great. But, please don't forget that you also have your customer's contact info: phone and/or email. Contact them! Ask them for their data specs (just like your system, the data currently in their system might have a max length of 35, but their system has it defined as VARCHAR(50), and their system will accept up to that length, in which case you should use 50). And, ask them if they have any near-term plans to change and of those datatypes (type and/or size).


Postgres Query Array Column with Array I have a postgres view "people_codes" that has a column that is an ARRAY_AGG of intergers. I am trying to query on the view with an array of integers for all rows that may have one of these codes
uuid    |  name     |     codes
(uuid)  | (varchar) |   (bigint[])
abc-def |  john     |  {5294, 5295}
bee-aaa |  jane     |  {1123, 2222}
111-a98 |  bob      |     {1933}

I can successfully query the view with only one integer for the query
SELECT * FROM people_codes WHERE 5294 = ANY (codes);

And this returns the row that I need:
abc-def   | john    |    {5294, 5295}    |

My final goal is to be able to query my view with an array, so that I can find all rows that have one or more of the given codes.
I have tried changing my value into an int, also casting it as a ::bigint[], but I get a syntax error at the brackets. Occurs no matter the brackets (, [, or {
SELECT * FROM people WHERE [5294,1933]::bigint[] = ANY (codes);

ERROR:  syntax error at or near "["
LINE 6: SELECT * FROM people WHERE [5294,1933]::bigint...

I have also tried using the @> and && comparison operators, but having trouble with the array of values again. It's trying to hint to adding an explicit type cast, which is what I thought was doing. Same thing with trying to place the search values into an ARRAY_AGG
SELECT * FROM people WHERE codes @> IN (5294,1933)::bigint[];

ERROR:  operator does not exist: bigint[] @>
LINE 8: ...ROM people WHERE codes @> IN (529...

What is a correct query to get rows that contain any elements in the query array? By searching on the Array of [5294,1933] I'm expecting to get back the rows for both john and bob

As documented in the manual there are two ways to specify an array constant
On is to use the array[...] notation:
SELECT *
FROM people
WHERE codes && array[5294,1933]::bigint[]

Alternatively use a string that uses {..}
SELECT *
FROM people
WHERE codes && '{5294,1933}'::bigint[]

I prefer the first one, as that also doesn't have problems when the data type itself requires quoting e.g. for text arrays.


Oracle 11g hunting down the deadlock, maybe foreign key? I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only some read occur before. The updates/inserts are done in a batch manner. The key, which is also used to identify the row which is to be updated, is indexed with a b+-tree. This key also acts in other tables as a foreign key. In these other tables the foreign key is allowed to be null.

The application disallows parallel transactions containing possible conflicts.

Here is the head of the trace file:

Deadlock graph:
    ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-134b0012-3201376f        65     185     X             33     392           S
TX-13495007-50f092d1        33     392     X             65     185           S

session 185: DID 0001-0012-04533045 session 392: DID 0001-0023-03401G5A
session 392: DID 0001-0023-03401G5A session 185: DID 0001-0012-04533045

Rows waited on:
Session 588: no row
Session 497: no row

Can the insert of a null value in a table referencing the key as a foreign key cause some kind of table scan (don't think so)? Can a batch insert/update maybe cause bigger branches of an index to be locked? Maybe T1 gets some left branch of the tree and T2 a right branch, and now T1 wants to to insert/update a value in the right branch, but the right branch is still locked by T2 and T" then requests something from the left side? I have got no idea if and how multi granularity locking is implemented in Oracle, but it would be nice if someone could rule this out.

Is there any possibility that Oracle allows deadlocks even if the transactions are 100% logically disjunct?

What could I be looking for? Is it some problem accessing the index?

P.S.: read committed is set.

Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys:

  There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

Locks in Oracle are managed at the row level. Concurrent disjoint transactions should not interfere with one another. Unindexed foreign keys are an exception, since it can result in a complete TABLE LOCK.

You should get the SQL in the trace file of the deadlock and that should help you
 narrow down which table / foreign key is responsible for the lock. Once you know which table is affected by the deadlock, make sure that all foreign key references to this table are properly indexed. E.G in your example T2.t1_id should be indexed if it points to T1.t1_id.

Alternatively you could use Tom Kyte's script from the above link to determine if you have any unindexed foreign key.

If you're locked on inserts, it usually means that you're trying to insert rows that have the same values for a set of columns that are UNIQUE, for example:

CREATE TABLE T1 (col1 NUMBER NOT NULL, col2 NUMBER);
ALTER TABLE T1 ADD CONSTRAINT T1_UNIQUE UNIQUE (col1, col2);

session1> INSERT INTO T1 VALUES (1, NULL);
session2> INSERT INTO T1 VALUES (2, NULL);
session2> INSERT INTO T1 VALUES (1, NULL); -- <= this will wait on session1
session1> INSERT INTO T1 VALUES (2, NULL); -- <= Deadlock !


Automysqlbackup fix error "Skipping the data of table mysql.event. Specify the --events option explicitly" I need to fix the mysqldump warning "-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.".

I found that its because of not specifying whether or not to add events table in mysqldump.

I need to know how to specify the  events option inside automysqlbackup config file.

In answer to the Server Fault question How to avoid mysqldump warning “Skipping the data of table mysql.event”:

  User Shane on a bike wrote:

    Modify or Create your ~/.my.cnf and create a mysqldump section by adding the following

[mysqldump]
events
ignore-table = mysql.events


MOVING TO ORACLE 12c I have an rman backup of 11.2.0.2. Also I have installed 12c on a separate server. Can I move my backup to 12c. if yes?
Please guide me how to do it?
Appreciate your comments and answer.

Restore the database as you would normally do it.

But at the end, instead of:

alter database open resetlogs;

Open the database with:

alter database open resetlogs upgrade;

Then upgrade the database to the new release. Instructions can be found at:

Manually Upgrading a Non-CDB Oracle Database

Note that the above documentation is for version 12.1.

Direct upgrade from 11.2.0.2 to 12.2 is not supported, you need an extra upgrade step to 11.2.0.3 or 11.2.0.4 or 12.1 before upgrading to 12.2.


PostgreSQL: How does COPY FROM behave inside a transaction? I have an 8-million-line CSV file I need to import into a table. This import will currently take place within a transaction.

Does COPY FROM create a subtransaction? Will a failure roll back the parent transaction, or leave it alone?

Does COPY FROM create a subtransaction?

No. It creates a single transaction if not run in a transaction; otherwise it will use the current transaction.

  Will a failure roll back the parent transaction, or leave it alone?

It will roll back the parent transaction.

If you want a subtransaction, start one explicitly before the COPY FROM.

There is no way to cause COPY FROM to ignore errors and continue. Any error causes the whole COPY to fail.


Using PgBarman for PostgreSQL 9.3 backup I'm setting up a backup strategy for a PostgreSQL 9.3 db. My job includes:

take db backups every day/week/month
use a Synology NAS as remote backup device

I'm thinking of using Postgres [base_backup + WAL archiving] + [rsync to NAS] for accomplishing the task. The db is a low activity one so I should force wal archiving via pg_switch_xlog or similar. One aspect of this is I would find myself with partially empty WAL files and I would like to reduce their size (I've been suggested to use pg_clearxlogtail).

Searching the Internet I found PgBarman which looks like a good aid in accomplishing the task but the documentation on the site assumes:

I have Postgres installed on the backup machine
I install PgBarman on the backup machine

I don't think I can install either of the two on the NAS (am I wrong?).

So I'm rather planning to:

install PgBarman on the actual Postgres server machine
use it to backup the db locally and manage eventual recovery
archive the base backup and WAL files to the remote NAS

The main goal of the backup is not failure recovery as the Postgres server is hosted by a cloud hosting provider which ensures availability and no data loss, but rather for human error recovery.

Does this strategy make sense? Can anyone point me to a good resource to find out how to implement it? Would there be a better solution?
Any suggestions on implementing it or adopting alternative approaches would be greatly appreciated.

(I've already been suggested streaming replication many times but I don't think I need it and moreover I wouldn't know how to set that up in a NAS)

You are looking for point in time recovery (for them awkward moments when you forget the where clause  "delete from customers")

Barman sounds like a good way to go.  Barman uses built in Postgres functionality but makes managing the base backups and WAL logs a lot easier (backup and WAL log cataloger, Point in time recovery manager, manage many servers from 1 location)

Your suggested strategy sounds fine, you can set the backup location in the barman conf to point at your NAS, you can then set the archive_command in postgresql.conf to rsync WAL's to your NAS as they are rotated.

There is documentation on their website http://docs.pgbarman.org/#introduction


HAVING ANY in Postgres I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition.

For example if I wanted to select a group where at least one row has a fold_change value greater than 4, I would expect this to work:

HAVING ANY(rnaseq.fold_change) > 4

But I get the error

  ERROR: syntax error at or near "ANY"

I suspect this is because Postgres requires the constant value to be on the left, but if I do it the other way around I get this error

4 < ANY(rnaseq.fold_change)

  ERROR: op ANY/ALL (array) requires array on right side

The only way I can get it to work is using array_agg like this:

4 < ANY(array_agg(rnaseq.fold_change))

But that syntax is fairly confusing compared to the first example and having to manually create an array for each group doesn't sound efficient at all.

Is there a proper way to do HAVING ANY in postgres?

You could use either:

HAVING MAX(rnaseq.fold_change) > 4

or the Postgres aggregate function bool_or:

HAVING bool_or( rnaseq.fold_change > 4 )


SSMS generated script failing to execute So I am in a situation where I need to import a small DB (under 1GB) from a newer version of SQL Server (I am restoring to a 2014 SQL Server from a 2016 SQL server), without a direct connection between the two. This DB is the backend for a CMS and so stores not only conventional data but html content as well.

The solution I found (I followed this guide.)was to use "Generate Scripts" in SSMS (schema+data) to script out the database, and then execute the script to recreate it on the destination server. The resulting script is about 900MB total.

The first issue I ran into was that SSMS would not execute the script as it was apparently too large for it. Sqlcmd was able to start executing the script, but then failed 10000+ lines into it. When I cut out the script at the failed point, it continued to run no problem where it left off, so I'm guessing it was out-of-memory related or something? The specific error I received was a

"Sqlcmd Error: Syntax Error at line 14769 near command ' " ' in file <path\script.sql>".

The actual line at 14769 is:

INSERT [dbo].[sf_control_properties] (
    [val],
    [validation],
    [prnt_prop_id],
    [ordinal],
    [nme],
    [last_modified],
    [language],
    [id],
    [flags],
    [description_],
    [control_id],
    [caption_],
    [app_name],
    [voa_version]
    )
VALUES (
    N'RulesGroupBackend',
    NULL,
    NULL,
    1,
    N'ControlDefinitionName',
    CAST(N'2018-03-23T12:35:50.977' AS DateTime),
    NULL, N'7cf975d2-f37d-4716-a634-0d6424921f38',
    1,
    NULL,
    N'27c93f05-386b-4fd2-8aae-d6664ee7e57a',
    NULL,
    N'Title/',
    1)

The line before (multiple line statement 14766-14768) is:

INSERT [dbo].[sf_control_properties] (
    [val],
    [validation],
    [prnt_prop_id],
    [ordinal],
    [nme],
    [last_modified],
    [language],
    [id],
    [flags],
    [description_],
    [control_id],
    [caption_],
    [app_name],
    [voa_version]
    )
VALUES (
    N'$(document).ready(function(){
       $("#content > div > section:nth-child(2) > div > div > div > div > div:nth-child(1) > h2").text("English Support");
});',
    NULL,
    N'5d056cac-51db-492c-b601-e88c82a8bc72',
    1,
    N'InlineCode',
     CAST(N'2018-11-27T20:33:59.060' AS DateTime),
     NULL,
     N'1f8ac206-d771-42b2-9991-0ca66bceff19',
     1,
     NULL,
     NULL,
     NULL,
     N'Title/',
     1
     )

I don't know what the error is referring to when it says syntax error on line 14769 near command ' " '

So in summary I'm wondering A) What may be going wrong with the script at these lines B) Whether this is an issue with sqlcmd (since the script was also failing in SSMS). To me the script appears fine, and after all it was automatically generated by SSMS so I'm confused what the problem could be. Thanks!

Try running SQLCMD with the -x argument to disable variable substation. This will avoid issues when the script includes SQLCMD variable tokens (e.g. $(stuff-here)) but are not actually SQLCMD variables.


Login failed in Linking two servers - SQL Server I want to connect 2 database which are on 2 different server.

SV1 -> SQL Authentication
SV2\SQLSERVER -> Windows Authentication

LinkedServer query

EXEC master.dbo.sp_addlinkedserver
@server = N'serverB',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'serverB',
@catalog=N'MyDatabase',
@provstr='Integrated Security=SSPI;'

EXEC sp_addlinkedsrvlogin 'CGCEL-PVQA\SQLSERVER', true

But I am getting a login error which executing SELECT query.
This is the error which I am getting:

Login failed for user 'sqladmin'.
OLE DB provider "SQLNCLI11" for linked server "SV2\SQLSERVER" returned message "Invalid connection string attribute".

I am new to this, please someone guide me.
PS:
Lets assume my login credentials for SV1 are:
Username : admin
Password : admin123

Your question is quite confused, first you have 2 servers SV1, SV2, then you link ServerB, but you add login to be used to CGCEL-PVQA\SQLSERVER server. So what does all this mean, that the local server is SV1 and you link SV2=ServerB=CGCEL-PVQA\SQLSERVER to it???

From this one:

  Lets assume my login credentials for SV1 are: Username : admin
  Password : admin123

I see that your local login is admin that is not Windows login but SQL Server login, and in this code:

EXEC sp_addlinkedsrvlogin 'CGCEL-PVQA\SQLSERVER', true

you told the server that the connection will be made using the login's security context, so what did you want to say by adding  @provstr='Integrated Security=SSPI;'?

Sorry if I misunderstood something, but from your code I can make another one that is working code but I'm not quite sure it's what you want:

EXEC master.dbo.sp_addlinkedserver
@server = N'serverB',
@srvproduct=N'blabla',
@provider=N'SQLNCLI',
@datasrc=N'CGCEL-PVQA\SQLSERVER',
@catalog=N'MyDatabase';

EXEC sp_addlinkedsrvlogin 'CGCEL-PVQA\SQLSERVER', true;

This code creates linked server with the name serverB, it points to the server CGCEL-PVQA\SQLSERVER, and all the logins will use their security context.

This one will not give you the error about

  Invalid connection string attribute

But your login admin will fail anyway.
This is because your SV2 is configured for Windows Authentication only.
This means all SQL Server logins will fail until you change it to Mixed mode and map your SQL Server logins to it.

For remote server configured with Windows Authentication only all the logins from local server can be mapped only to themselves.

So there is no way that your local login admin can logged in to the remote server configured with Windows Authentiction only


Mysql query time massively affected by limit I have this mysql query, where I am getting 250ms query time when the limit is 500.
If the limit is 1000, it's 1,5 seconds
if the limit is 1300 , it's 2,5 seconds
select hotel_code,
       ST_Distance_Sphere(
               point(longitude, latitude),
               point(-74.007227, 40.713054)
           ) * 0.000621371192 as distance
from hotel_parser_links hpl
where parser_code = 'ST'
having distance is not null
   and distance < 30

Here is the table structure, it has around 500k rows
create table hotel_parser_links
(
    id bigint unsigned auto_increment
        primary key,
    hotel_id bigint unsigned not null,
    parser_code varchar(255) not null,
    hotel_code varchar(255) not null,
    finished_parsing_at datetime null,
    started_parsing_at datetime null,
    city varchar(255) null,
    longitude decimal(11,8) null,
    latitude decimal(11,8) null,
    coordinate point not null
)
collate=utf8mb4_unicode_ci;

create spatial index hotel_parser_links_coordinate_index
    on hotel_parser_links (coordinate);

create index hotel_parser_links_hotel_code_index
    on hotel_parser_links (hotel_code);

create index hotel_parser_links_latitude_index
    on hotel_parser_links (latitude);

create index hotel_parser_links_longitude_index
    on hotel_parser_links (longitude);

create index hotel_parser_links_parser_code_index
    on hotel_parser_links (parser_code);

Here is the explain output
+----+-------------+-------+------------+------+--------------------------------------+--------------------------------------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                        | key                                  | key_len | ref   | rows   | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------------------+--------------------------------------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | hpl   | NULL       | ref  | hotel_parser_links_parser_code_index | hotel_parser_links_parser_code_index | 1022    | const | 270853 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------------------------+--------------------------------------+---------+-------+--------+----------+-------+

Would appreciate any help :)

The bigger the limit, the more of the 500K rows it will have to scan.  Hence the longer the query will take.
Yes, I see that the growth is not "linear"; there are many possible reasons for that, but let's ignore that and go for what you really want -- the query to be faster.
See the following for several ways to speed up the query.  In particular, pick the SPATIAL option.  http://mysql.rjweb.org/doc.php/find_nearest_in_mysql
Your indexes on lat and lng are not useful for this query, and tend to be not useful if you added a "bounding box" constraint in the WHERE clause.  However, INDEX(lat, lng), INDEX(lng, lat) is useful for a bounding box.
But the real problem seems to be that the Spatial test is in HAVING.  The Spatial index can be used if you move that test into the WHERE clause.  Yes, you will have to repeat the expression; too bad.
PS:  distance is not null is redundant; the test < 30 fails for NULL.
Since there is no test on lat or lng, INDEX(parser_code, lat, lng) won't help.  You could add a bounding box test; that might let that composite index be even faster than SPATIAL; that would depend on the cardinality of parser_code.


Tracking MongoDB execution times using PHP I am currently working on a project using PHP, I have done research and found that microtime() helps with tracking the time of a query in a MySQL database..

how do I do the same for MongoDB, I have a PHP interface and I have also inserted data into the MongoDB database using this but how do I show how long the time of the query took to insert into the database.

PHP uses microtime()
MongoDB uses ????

As per MongoDB BOL Analyze Query Performance usees The cursor.explain("executionStats") and the  db.collection.explain("executionStats") methods provide statistics about the performance of a query. This data output can be useful in measuring if and how a query uses an index.

db.collection.explain() provides information on the execution of other operations, such as db.collection.update().


Index by subkey in jsonb I have a table payment with a jsonb column application. It contains payments and the total amount for one or more tours. For example, a parent can pay 1000 $ for all his family, e.g. 4 children and his wife. Then one row in payment contains 6 records in the jsonb field application, describing the tour and the customer.

[
   {
    "tour_id":
      {
        "id":"d61802ff-3eec-4a72-97ca-832f51b96bf0","name":"Paris  2018"
      },
      "amount":"500.00",
      "customer_id":
          {
             "id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
             "name":"OBAMA John"
          },
     "id":"5cd8338c-15c2-48c5-b305-9e172bb33a9a"
   },
   {
    "tour_id":
      {
        "id":"a52d38d4-9bfb-4ffa-1122-e536f04b0c60","name":"London  2018"
      },
      "amount":"500.00",
      "customer_id":
          {
             "id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
             "name":"OBAMA John"
          },
     "id":"5cd8338c-15c2-48c5-b305-9e172bb33a9a"
   },
]

My id is unique in all the postgres database, I'm using UUID v4. Which is the best way to index this id? Really the name is redundant here, because I have a table for customers and tours, and I could just record the id:

[
   {
    "tour_id":"d61802ff-3eec-4a72-97ca-832f51b96bf0",
    "amount":"500.00",
    "customer_id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
    "id":"5cd8338c-15c2-48c5-b305-9e172bb33a9a"
   },
   (...more elements)

But I'd like to get advise on the best indexing strategy (or alternative solutions).

A jsonb GIN index with the jsonb_path_ops operator class would be an option.

But to just index (multiple) application #>> '{*, tour_id, id}') values per jsonb field (pseudocode, the * is illegal), I would write a tiny, IMMUTABLE SQL function to extract and cast the UUID fields to an UUID array (uuid[]) - and create an expression GIN index on that. Should be considerably smaller and hence faster:

CREATE OR REPLACE FUNCTION f_extract_tour_ids(jsonb)
  RETURNS uuid[] AS
$func$
SELECT ARRAY(SELECT (jsonb_array_elements($1) #>> '{tour_id, id}')::uuid)
$func$ LANGUAGE sql IMMUTABLE;

What was missing in my original answer: there is no GIN operator class for uuid[], yet (up to Postgres 10). See this thread on pgsql-hackers.

Update: Starting with Postgres 11, the additional module btree_gin provides the missing operator class.

You have to create it yourself (once per database):

CREATE OPERATOR CLASS _uuid_ops DEFAULT
   FOR TYPE _uuid USING gin AS
   OPERATOR 1 &&(anyarray, anyarray),
   OPERATOR 2 @>(anyarray, anyarray),
   OPERATOR 3 <@(anyarray, anyarray),
   OPERATOR 4 =(anyarray, anyarray),
   FUNCTION 1 uuid_cmp(uuid, uuid),
   FUNCTION 2 ginarrayextract(anyarray, internal, internal),
   FUNCTION 3 ginqueryarrayextract(anyarray, internal, smallint, internal, internal, internal, internal),
   FUNCTION 4 ginarrayconsistent(internal, smallint, anyarray, integer, internal, internal, internal, internal),
   STORAGE uuid;

Note (quoting the manual):

  The user who defines an operator class becomes its owner. Presently,
  the creating user must be a superuser.

See:

PostgreSQL GIN index on array of uuid

Then create the index:

CREATE INDEX payments_application_tour_ids_gin_idx ON payments
USING gin (f_extract_tour_ids(application));

Match the expression and use an indexable array operator in your queries to be applicable, like:

SELECT *
FROM   payment
WHERE  f_extract_tour_ids(application) @> '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::uuid[];

As you can see, you can search for any number of UUIDs at once with this query.

Or, if you can't (or won't) create the operator class, you can work with the text representation of the UUID values - which is considerably bigger and hence more expensive.

Adapt f_extract_tour_ids(jsonb) like this:

CREATE OR REPLACE FUNCTION f_extract_tour_ids(jsonb)
  RETURNS text[] AS
$func$
SELECT ARRAY(SELECT (jsonb_array_elements($1) #>> '{tour_id, id}'))
$func$ LANGUAGE sql IMMUTABLE;

And the query:

SELECT *
FROM   payment
WHERE  f_extract_tour_ids(application) @> '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::text[];

Be sure to match the UUID format exactly.

Related:

Index for finding an element in a JSON array
Can PostgreSQL index array columns?
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars


How can I set a random timestamp for a row based on a columns value in mysql? I have an app that I built and found an issue with it.

Essentially there are created_at and ended_at timestamp columns.

The ended_at function in my app had a bug so now I have a few hundred thousand rows that have no ended at timestamp.

I want to go through each row and based on the created_at column, generate an ended_at timestamp that's anywhere from 5 seconds to 8 hours away.

This is what I came up with, I just want to make sure it's what I want before I run it on 500k rows.

UPDATE streams SET ended_at = FROM_UNIXTIME(
        UNIX_TIMESTAMP(created_at) + FLOOR(0 + (RAND() * 28800))
    )
    WHERE ended_at IS NULL LIMIT 1;

I have tested it and it appears to function correctly and i have backups of my database, but I just want to be 100% sure.

Thanks

I like to check out a computed column as part of a read only select first just to make sure it shows the values I'd expect.

SELECT *
    ,UNIX_TIMESTAMP(created_at) + FLOOR(0 + (RAND() * 28800)) as ended_at_new
        from streams
            WHERE ended_at IS NULL

If you see the correct variation and values you were expecting to see then your update query will work fine.

For extra protection you could run the update in a transaction:

begin tran;

UPDATE streams SET ended_at = FROM_UNIXTIME(
        UNIX_TIMESTAMP(created_at) + FLOOR(0 + (RAND() * 28800))
    )
    WHERE ended_at IS NULL

select count(ended_at) where ended_at IS NULL #should see 0

select * from streams limit 0, 1000; #add better filters to correctly identify records you want to check

rollback tran;

If you are happy with what you saw, change the rollback to commit and run it again.


Default directory for MySQL databases Is it recommended to have the MySQL database location as the default one which is /varlib/mysql ? Are there any risks in having the databases in the specified location?

That's a standard suggested location in the installation layouts to-start-with and usually people tend to follow the defaults.

Just to answer it short - keeping databases under /var/lib/mysql as such is not a "risk" as such!

There are considerations though. You can always change your directory to a different partition for better manageability and to take IO advantages.
I've even seen people softlink /var/lib/mysql to a different partition to keep things simple-to-view at configuration level.


PostgreSQL vacuum duration on a big table I have a very big table in my postgreSQL (over 300 million rows) that has never been vacuumed. Yesterday I tried a vacuum and analyze (not a full one) and it took about 7 hours to complete. Will it take 7 hours if I do it every day, or will the duration become smaller due to frequent maintenance?

Because you haven't specified your PostgreSQL version it's hard to answer. VACUUM has been improved in many ways on newer versions.

Presuming you're on 9.0 or 9.1 - so you don't need a max_fsm_pages setting, you have the improved VACUUM FULL, you have the visibility map, etc - then subsequent VACUUM operations should be quite fast. However, you should not be doing them.

Turn autovaccum on. Set it to run frequently. If this table is updated a lot, set autovaccum to process it more often. Vaguely modern PostgreSQL versions work best with lots of very frequent vacuum runs.


Do I need client certs for mysql ssl replication? I'm setting up mysql replication using SSL, and have found two different guides.

The first one creates both client and server certs, while the second one only creates server certs.

I don't know enough about SSL to understand the implication of one option over the other.  Should the slave be using the client certs or the server certs?

No, you don't need client certificates if you only want to encrypt the connection.

But, if you have client certificates, the master can authenticate the slave using the much more secure public/private keys (in addition to the password).

I got pretty frustrated with the other guides, so I put together a very comprehensive step by step guide to setting up encrypted replication.  It shows both what's the minimum required to get encryption running, and how to use certs on both sides to beef up authentication.
http://mysql.wingtiplabs.com/documentation/ssl27yzv/encrypt-replication-traffic


PostgreSQL newbie - how to create objects in a database I've just finished installing PostgreSQL and pgadmin3 on Ubuntu.  Using pgadmin, I've been able to create a new user called 'test' and also a new database with 'test' as the owner.
Now I'm trying to create tables using SQL statements... but I can't seem to be able to figure out how to do that in pgAdmin.  I don't' want to create each column individually using the GUI.
Is there any way I can run a SQL statement like:

create table test
(
  id serial primary key,
  name varchar(64),
  handler varchar(16),
  desc varchar(255)
);

I figured it out. I can create an empty table using the "new table.." menu item. Then I can edit the table once created by right clicking on it and choosing "scripts".