texts: Trying to optimize a MySQL query that has sub selects

texts:
I have the following sql

SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as loginCount,
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = c.identity AND clv.type = 10) as viewCount
FROM client c
LIMIT 50;

This query takes ~45 seconds to run (and I am going to need to run this without a limit) with client having ~80,000 records and ClientLog having ~430,000 records.

Is there anything I can do to the sub selects to increase the performance of this query besides breaking it into separate query calls?


texts: How to join 3 tables together given 2 many-to-many relationships tables in between?

texts:
I have 3 main tables: Person, Publication and Conference

and 2 many-to-many link tables in between: person_publication and person_conference

I want to join all 3 main tables together (showing me all the publication and conference a person was involved in)

But so far, I am only able to query either person-to-publication or person-to-conference like this:

select person.*, publication.*
from person
left join person_publication on person_publication.person_id = person.id
left join publication on person_publication.publication_id = publication.id;

select person.*, conference.*
from person
left join person_conference on person_conference.person_id = person.id
left join conference on person_conference.conference_id = conference.id;

How can I join them all together?


texts:
Consider a scenario, when 2 Stored Procedure called inside a Stored procedure like follows.
Note:  All 3 SP’s are implemented with TRANSACTION.

CREATE PROC [dbo].[SP3]
AS
BEGIN
      BEGIN TRAN
EXEC SP1
EXEC SP2

      COMMIT TRAN
      END

GO

What happens when an error happened in SP2.

a.  Commits SP1 alone and raise error for SP2

b.  Rollback SP1 and SP2

c.  Syntax Error

d.  Commits SP1 and SP2 with Error Message

Please give the answer.

Thanks.


texts: MySQL select values based on a sub query

texts:
I have two tables , Table #1 => tbl_notification_flow has the  following information :

app_status
Booked
Notified
Missed
Defaulted

With the  app_status being the column name and Table #2 => tbl_appointment  has the following information :

id  app_status
1   Booked
3   Missed
4   Missed
5   Missed
6   Missed
7   Booked
8   Booked
9   Booked
10  Booked
11  Booked
12  Booked
13  Booked
14  Booked
15  Booked
17  Booked
18  Booked
19  Booked
20  Defaulted
21  Notified

With id and app_status being the  columns ,

I want to get a count of all the  app_status based on the  number of times they have appeared on Table #2 ,

I tried the  following query :

SELECT COUNT(tbl_appointment.id)AS total_count, tbl_appointment.`app_status`
  FROM tbl_appointment
 WHERE (tbl_appointment.`app_status`) IN
 (
   SELECT tbl_notification_flow.`notification_type` AS app_status
     FROM tbl_notification_flow
    WHERE tbl_notification_flow.notification_type != 'Other'
 )

It gets the following result :

total_count app_status
19  Booked

Please help with the  best query to get the  right results.


texts: Postgres shared_buffers and pooled processes

texts:
I have read several articles (including Postgres docs) that suggest setting the shared_buffer's config value to 25% of your total physical memory.

Using my 4 GB memory as an example... If I understand correctly, when postgres starts up, it will request ~1 GB of physical memory from the OS. It will use this for buffering purposes. Thus, when I look at a utility like "top" I see the size/RES of the postgres process to be ~1GB.

So my question is - what happens when I have POOLED connections to Postgres? i.e. A Tomcat server which keeps N number of open, idle connections to postgres? I can see there are N number of postgress processes waiting in the background for requests from Tomcat. Each process is the same size (~1Gb). They're forks of the original process, I suppose. So does that mean Postgres as a whole is taking up N*1GB of memory?? Isn't this a bad thing to make shared_buffer this large??


texts: Migrating Access Saved Import Specifications to SSIS Package

texts:
We currently receive a multi-hundred column flat file from a vendor, and the tech prior to me had created an import specification in Microsoft Access to automatically split the file into the right fields based on field length, properly formatting dates and such, etc etc.

As I'm migrating this data to SQL Server, is there any quick way to migrate the stored import specifications from Access to a SSIS package without completely recreating it? Since it's a file based on field length rather than a CSV, it's quite a process to recreate from scratch due to the file size, so figured I'd check if anyone is familiar with an automated way.


texts: Whats the best practice when using Amazon Redshift as a cache

texts: I have tested Amazon Redshift as a cache. So I save data by a key. But with the time the cache becomes slower and slower. Once I do a VACCUM on the database it get backs to the speed. But should i run VACCUM continuously in the database ?.  Or is there a better way of using redshift tables for continuous reads, writes and deletes.


texts: How can I tell if my tempdb database has a performance issue/need to add more data files?

texts:
SQL Server works in database called tempdb to handle a lot of tasks. Query operations like joins and aggregations happen there. Online index rebuilds, order by, aggregate function, triggers are also done in tempdb.

How can I measure when should I add another tempdb data file? And how many tempdb data files are required?


texts:
I'm new in PostgreSQL. I try to create table in database. In psql i write:

CREATE TABLE mail_user (
   user char(50) NOT NULL,
   domain char(50) NOT NULL,
   password char(50) NOT NULL
);

But i get error:

ERROR:  syntax error at or near "user"
LINE 2: user char(50) NOT NULL,
        ^

What's wrong? How can i fix it?

Thank you.


texts: Why all instances of PostgreSQL are not listed in stackbuikder (enterpriseDB)?

texts:
On a windows server 2012, There are two instances of PostgreSQL 11.5.

The first in running on port 5432 and was created with EnterpriseDB installer.

The second was created manualy with initdb then pg_ctl start and pg_ctl register to register as service ; it is running on port 5433.

I can connect to both instances and both services are running fine.

The point is that I want to make some upgrade tests on instance 5433 using stackbuilder.
Problem is that this instance does not appear in the list of instances, just 5432.
Is there a way to solve this ?
(I could install an instance of the latest version of PostgreSQL then restore all the databases but I prefer to work on simpler solution first.)


texts: SQL Server Agent - Running SSIS Package with Proxy

texts:
I have followed the below article to create credentials/proxy to run a SSIS job from SQL Server Agent.

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

I get this error:
Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 6).  The step failed.

Does the credential account need to be a Domain user account? I have tried using sa and another SQL Login but still no success.


texts: Showing data that is not already in the data base

texts:
I have the following problem. I want to search a query in an existing data base and that the result shows me the entries of the query that are NOT already in the database.

Minimal example:

| ID | name |
| 1 | Paul |
| 2 | Clara |

ID Query (1,3)

The result should be 3!

So I want to do something like this:

Select * from names Where ID Not in (1,3);

But well this is obviously not correct.

Furthermore, I don't want to insert an additional table with the query!
Does anyone know a solution for this problem?


texts:
I am using Oracle DBMS_DATAPUMP API to import a dumpfile containing some table data.

Is it possible to stop the job and issue COMMIT; in the same session to maybe cleanup UNDO tablespace  and then resume the import?

Regards,
Bibhuti


texts: How do I monitor bandwidth used by SQL Server on port 1433?

texts:
I have a single server running a .NET web application and a SQL Server database (2008 Standard). I'm planning to move the database onto a separate server but in order to provision the network hardware I'd like to benchmark the data throughput between the web application and the database. Can port 1433 be monitored internally? If so is there any tool native to Windows 2008 R2 that can do this, or would I need some 3rd party application like WireShark? My connection string is referencing the database using server=localhost, is it still possible to tap into 1433 to see the bandwidth used on this port?

Essentially I'm trying to determine if I need a Gbit or 100 Mbit connection between the web server and database server. Any thoughts on this would be much appreciated.

UPDATE 8th July

I hashed out the above as I realise it's largely irrelevant. For some reason I thought there would be a big cost difference between a 100 Mbit and 1 Gbit switch. Only the cheapo home user switches are 100 Mbit. As others have pointed out, Wireshark will not pickup activity between IIS and the SQL server on the same box. I'm putting in a 1Gbit managed switch for now and I'll use either Wireshark or the built in monitoring on the switch to see what's going on later. I don't think it will be anywhere close to the physical transfer limits imposed by the hardware.


texts: Subtracting an integer in my db by 1. SQL

texts:
I want to subtract the follower count of a user by 1, or increase it by but I don't want to do this:

   $sql = $conn->prepare('SELECT folllower_num FROM users WHERE user_id = ?');
   $sql->execute($user_id));
   //then grab the follower_num value, save it to a variable, and subtract 1
   //from it and then:
   $sql = $conn->prepare('UPDATE users SET follower_num = ? WHERE user_id =
   ?;');
   $sql->execute($newfollowercount, $user_id));

or something like that. To me this seems bad to do because while the value is saved into this code and then changed and put back into the database, someone else could have followed or unfollowed and then the number becomes incorrect. Is there another way I can do this?


texts: Stored Procedure With an Optional Parameter Limiting Rows Returned if Specified

texts:
How would one go about having a sproc that allows a consumer to optionally specify the number of rows returned?

If no row count is specified, then all rows returned.


texts: Reducing the size of a 500 Gb database on standard edition

texts: One of my clients has requested to find any options to reduce the size of some of the tables in this 500 GB database that are huge in size, and causes issues with read performance. My client would be migrating to SQL 2016 soon. Is there an approach or a way to have it reduced and increase the read performance as well.


texts: Is it advisable to have MySQL Master/Slave running on the same physical server, on the same mysqld, sharing the same disk?

texts:
my Use-Case :

The solution I am developing is very data oriented. I run a cron job every 3 hrs to read lots of records from a file, process them and store them in the database. I also need to generate reports, which I do by querying database.

So, I was thinking of having MySQL replication in Master and slave so that I can do all the inserts / updates on Master and use the slave just to read data to generate reports.

I read this other post on the site and it got me thinking.

Here is my concern: Both my master and slave will be sharing the same resources here as they are on same physical server.

Is it good idea to do so?


texts: Unable to deny remote access to Postgresql even if there is no entry in pg_hba.conf

texts:
I have 5 Postgres Servers, with IP addresses let's say as A,B,C,D and E.
A is the master server and remaining are the child servers which need to access A. In the postgresql.conf file of A, the listen address has been set to '*'.

Now, let's say I want B to access A remotely. For that, the pg_hba.conf file of A should contain an entry with the IP address of B (along with other necessary data), like this for example:

host all    all     IP_Address_of_B 32    md5

But, the problem which I am facing is that even if I am entering the details of any one of the child servers in the pg_hba.conf file of A, the access to A is enabled to all of the child servers. And if I remove that entry, then the access is disabled for all.

As far as I know, if the entry of B is present in the pg_hba.conf file of A, the only B must be allowed to access A, not the remaining. But, in my case, either all have the access or none.

Is their a way to enable only B (or only a selected servers, not all) to access A ?


texts: Restoring a SQL Server 2008 R2 backup on SQL Server 2005

texts:
I'm getting an error whenever I restore a testing database on SQL Server 2005 that was backed up from SQL Server 2008 R2. It seems that I cannot restore from a higher version of sql onto lower version, how can I solve this?

  The media family on device is incorrectly formed. SQL server cannot process this media family. RESTORE HEADERONLY is terminating abnormally.

I already tested to restore the same file in its own server (SQL Server 2008 R2) and it works, so it's not corrupted.

I cannot install the updated version of SQL into my older server, so how can I use my older SQL Server 2005 as a dev server but with the updated life data from a different instance?


texts: What is restarting SSRS service once a day?

texts:
My 2008 R2 SSRS service (10.50.1600.1) is consistently restarting every morning at 2:30 AM and I can't figure out why.  The RecycleTime setting in rsreportserver.config is unchanged from the 720 minute (12 hours) default.  But, the service doesn't restart twice a day, just once.  I've attempted to affect the restart time by manually restarting at a different time of day but regardless of whether that time was 12 hours after the restart or more than 12 hours, the service continues to restart at 2:30 AM.  Same result regardless of whether the service restarted or the server rebooted: 2:30 (The attempted time could possibly 2:00, if the service waits on the 30 minute MaxAppDomainUnloadTime setting).

Any thoughts on where else to look for what is causing this?


texts: Postgres pgAdmin4 and table maintenance fails

texts:
I just loaded Postgres 9.6 for a dev db and using pgAdmin4 I cannot run any table maintenance at all.  I added the path in the binary and it continues to fail.  Please help as I have no idea how to fix this one. Any ideas?

Path to the binaries

Error message I cannot get around.

On another dev box using pgAdmin3 I do not have this issue. I really do not understand what is happening.

If I use pgAdmin 3 I have no issue at all on the same machine.  Here are the screenshots on the same machine with pgAdmin3.

pgAdmin3 Settings

Results

I'm using:

Version1.1
Python Version2.7.11 (v2.7.11:6d1b6a68f775, Dec 5 2015, 12:54:16) [GCC 4.2.1 (Apple Inc. build 5666) (dot 3)]
Flask Version 0.11.1


texts: Networking (classmate like) website database model

texts:
I am working  on a project that consists in building a networking website like the Classmate web site.
I have proposed a database model based on the social media database model presented on the databaseanswers.org web site.
Does my database model match perfectly with my business rules? The rules are:

A provinces has one or many towns

A primary school belongs to a town

A high school belongs to a town

An institute belongs to a town

A university belongs to a town

A university or an institute has one or many departments

A university has one or many faculties

A university could have at least one or no institute

An institute could belong or not to a university; in the latter case it is independent

A person lives in a town located in a state

A person was born in a town located in a state

A person holds up to one user account

A user account attended a primary school or a high school or an institute or a university or an enterprise

A user account stores none or many messages

A user account sends or receives none or many messages

There are many types of messages

One user account could have a relationship (friend, colleague, classmate, ...) with another

There are many types of relationships

At registration an account could choose a primary school or a high school or an institute or a university or an enterprise

An account that has already chosen a primary school or a high school or an institute or a university or an enterprise can choose one or many more later

An account could display another account information

An account could display the list of its friends (relationship)

An account could display the list of the photos it has uploaded on the web site

An account could display the list of the photos related to the school it has attended or the enterprise it has worked in

An account could invite another account to be in relationship; the other account could accept or refuse the invitation.

An account could seek for its relationship

The administrator should be able to list all the persons' and all the users' accounts

The administrator should be able to list all the schools an account has attended

An account and the administrator could update the list of schools an account has attended


texts: SET ANSI_NULLS - Performance of Stored Procedures

texts:
Does setting ANSI_NULLS ON/OFF affect the performance of stored procedures?

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestProcedure]


texts:
I am trying to figure out the best MySQL replication topology, but I am not sure the best way to lay it out.

datacenter A | datacenter B
     ____    |     ____
    |    |   |    |    |
    | AA |   |    | BA |
    |____|   |    |____|
     ____    |     ____
    |    |   |    |    |
    | AB |   |    | BB |
    |____|   |    |____|
             |

These 4 MySQL servers are connected in pairs with AA and AB in Datacenter A, while BA and BB will be located in datacenter B. Datacenter A and Datacenter B are on different subnets to increase redundancy in the event of an ISP failure.

AA and AB will share a VIP using keepalived, heartbeat, or similar.

BA and BB will also share a different VIP using the same method.

I am trying to find the best way to use replication to ensure that all servers are kept in sync. Only one DB server will be written to at any given time (handled through HAProxy load balancer - not pictured)

I think there should be Master-Master replication between AA <-> BB as well as Master-Master between BA <-> BB.

Would it be better to use DRBD between AA <-> AB as well as DRBD between BA <-> BB?

If DRBD is used like this, how will I handle replication between datacenter A and datacenter B? I prefer something similar to master-master where a slave does not need to be manually promoted as this can take time for us to respond.

The HA aspect of this problem may be better suited on serverfault, but I am not sure.


texts: Why can my index seek estimate the right number of rows and the sort operator can't?

texts:
I've a query that uses a function on the predicate, something like this:

commentType = 'EL'
AND commentDateTime >= DATEADD(month,datediff(month,0,getdate()) - 13,0)

I have a filtered index on commentType that has 40K rows and when I run the query, the estimated number of rows for the Index Seek is very accurate (around 11K), but for the next step (sort operator) it completely ignores the statistics and just estimates the total number of rows in the filtered index.

Why is this happening? I know the basics about sargability, and I've tested just for the sake of sanity replacing the dateadd by an actual date (2014-01-01) and voila... The sort started guessing the number of rows correctly...

Why is this happening and how can I fix it? I can't pass a fixed date...


texts: Optimize queries from MySQL SP/Triggers/Functions


texts:
Inside a nested loop query plan, does sql server wait for one instance of the loop to complete before starting the next one?

I have some queries which are slow and their pattern is index scan->nestedLoop lookup, and i'm reasonably sure that the cache is cold.

Assuming I've a query plan that shows a nested loop with 10000 lookups, If i consistently have a 5ms disk access time, is there any way to work out how long it will take to read the rows (assume 5k page fetches are needed; some of the rows will be on already fetched pages, producting logical but not physical reads).


texts: Type conversion in expression may affect “CardinalityEstimate” - on a computed column?

texts:
I had a look at this question:

Type conversion in expression may affect “CardinalityEstimate” in query plan choice?

But that was related to collation and not with computed columns.

I have the following table definitions that are used in a query below that is giving me a query hint

  Type conversion in expression
  (CONVERT(varchar(10),[t].[FLTCD_FLT_DATE],112)) may affect
  "CardinalityEstimate" in query plan choice

Please note the computed column flightReference

CREATE TABLE [dbo].[repl_Transportation] (
[FLIGHT#]          INT                              NOT NULL,
[FLTCD_FLT_DATE]   DATETIME                             NULL,
[FLTCD_DEP_GATE]   CHAR(3)                              NULL,
[FLTCD_ARR_GATE]   CHAR(3)                              NULL,
[FLTCD_SEQUENCE]   CHAR(1)                              NULL,
[DIRECTION]        CHAR(1)                              NULL,
[PNR_NUMBERS]      VARCHAR(70)                          NULL,
[HK]               NUMERIC(3,0)                         NULL,
[Create_Date]      DATETIME                         NOT NULL,
[Modify_Date]      DATETIME                             NULL,
[flightReference]  AS (substring(((CONVERT([varchar](10),[FLTCD_FLT_DATE],
 (112))+[FLTCD_DEP_GATE])+[FLTCD_ARR_GATE])+[FLTCD_SEQUENCE],(3),(13))) PERSISTED,
CONSTRAINT   [PK_FLIGHT#]  PRIMARY KEY CLUSTERED    ([FLIGHT#] asc))

IF OBJECT_ID('[dbo].[repl_Transportation_Details]') IS NOT NULL
DROP TABLE [dbo].[repl_Transportation_Details]
GO
CREATE TABLE [dbo].[repl_Transportation_Details] (
[FLT_LEG_ID]     INT                              NOT NULL,
[FLIGHT#]        INT                              NOT NULL,
[LEG_NO]         TINYINT                              NULL,
[AIRLINE_CODE]   CHAR(2)                              NULL,
[AIRLINE_FLTNO]  VARCHAR(6)                           NULL,
[DEP_DATE_TIME]  DATETIME                             NULL,
[ARR_DATE_TIME]  DATETIME                             NULL,
[DEP_AIRPORT]    CHAR(3)                              NULL,
[ARR_AIRPORT]    CHAR(3)                              NULL,
[Create_Date]    DATETIME                             NULL,
[Modify_Date]    DATETIME                             NULL,
CONSTRAINT   [PK_FLT_LEG_ID]  PRIMARY KEY CLUSTERED    ([FLT_LEG_ID] asc))

When I run the following query I get a warning on the select:
The query plan is here

  Type conversion in expression
  (CONVERT(varchar(10),[t].[FLTCD_FLT_DATE],112)) may affect
  "CardinalityEstimate" in query plan choice

    SELECT  t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    MIN(td.DEP_DATE_TIME)     AS departDate,
    MAX(td.ARR_DATE_TIME)     AS arrivalDate,
    CASE WHEN
     PNR_NUMBERS IS NULL OR PNR_NUMBERS = ''
     THEN 'Paper Ticket'
     ELSE 'E-Ticket'
    END          AS ticketType,
    'US'         AS source,
    0          as sourceId,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE
 FROM        repl_Transportation      AS t
 LEFT JOIN repl_Transportation_Details    AS td ON td.FLIGHT# = t.FLIGHT#
 GROUP BY t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE

When I remove the MIN and MAX columns from the select,
there is no more any warnings, the query plan is here

    SELECT  t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    --MIN(td.DEP_DATE_TIME)     AS departDate,
    --MAX(td.ARR_DATE_TIME)     AS arrivalDate,
    CASE WHEN
     PNR_NUMBERS IS NULL OR PNR_NUMBERS = ''
     THEN 'Paper Ticket'
     ELSE 'E-Ticket'
    END          AS ticketType,
    'US'         AS source,
    0          as sourceId,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE
 FROM        repl_Transportation      AS t
 LEFT JOIN repl_Transportation_Details    AS td ON td.FLIGHT# = t.FLIGHT#
 GROUP BY t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE

Is there any way I can get rid of this warning?

Is it a persisted column, the conversion has been already done, why exactly is the query optimiser going on about it?


texts: AG listener with MultiSubnetfailover True on Named Instance

texts:
I have an AG configuration with databases on a Named instance. I have enabled
multisubnetfailover =true and registerallprovidersip =1
I just saw some blurb on the web that these settings are not supported by Named Instances.
Has anyone used these settings on a named instance and had a successful fast failover from 1 subnet to another?
Confusing details on leveraging multi subnet failover and sql server always on listeners.


texts: Pressing Ctrl+C during UPDATE query in Postgres, was the query partially run?

texts: I accidentally launched unwanted UPDATE query inside psql (9.4) and killed it with CTRL+C a few seconds later. Were there any records modified, or it's all or nothing?


texts: Some timeout in query store but get all SP:Starting and SP:Completed in profiler

texts:
I have a report SP running. The user complaint report timeout sometimes. And I do get some canceled execution plan in my query store.

But in my profiler tracing file, every SP:Starting has its SP:Completed. Am I missing any? or could you please give me some direction?

Thanks,


texts: Why does Management Studio not automatically create FK column in the parent table?

texts:
When creating a FK in SQL Server 2008 Management Studio using a single column, one has to first create a column manually then select the PF/FK relationship from a dialog box.

I am wondering why is there no option to let the software automatically create the column with the correct data type in the parent table as soon as the drag operation completes?


texts: Snapshot replication from SQL Server 2012 to SQL Server 2008R2

texts:
We are using snapshot replication as a standby to replicate prod data for a month from 2012 to 2008R2 after migration from 8R2 to 2012, just as a rollback process.

Using snapshot replication for 10 databases to replicate on old sql instance. For one of the db (2 TB) it takes almost 32 hours for distribution agent job to complete. On old sql that database is set to simple recovery model to prevent log growth and db there no needs to go through log backups. Snapshot job is scheduled to run daily 12 PM for a month or 2 to monitor for any issues on newly migrated one.

Is there a way this 32 hours can be made faster? Will changing to bulk recovery model help? Data type are blob and xml within that db ..


texts:
I have 4 tables comment,employee,mode and module(all are default NULL).
I want to add an after update trigger in my comment table when emp_id,mode_id,module_id from comment table(which are foreign key here) updated it will record into the new activity table with timestamp.
All I need to show First name(from employee table), last name(from employee table), tweet(from comment table),timestamp(current time),mode_name(from mode table) and module_name(from module table).
when I created a trigger and when i update single row in my comment table it will put 10419 records in my activity table it supposed to put just 1 record instead of 10419.
Thanks in advance.
CREATE
DEFINER=`root`@`localhost`
TRIGGER `rewardco3`.`comment_AFTER_UPDATE`
AFTER UPDATE
ON `comment`
FOR EACH ROW
BEGIN
INSERT INTO activity(
        Emp_ID,
        First_name,
        last_name,
        tweet,
        mode_name,
        module_name
        )
        select
        new.Emp_ID,
        e.First_name,
        e.last_name,
        old.tweet,
        m.mode_name,
        ml.module_name
    FROM comment c,employee e, mode m, module ml
    where new.Emp_ID=e.Emp_ID AND new.mode_id=m.mode_id AND new.module_id=ml.module_id;
END


texts: Group data by Custom Period Ranges Using a Reference Date


texts: Optimizing a large number of INSERT ... SELECT statements

texts:
I have a MySQL InnoDB database with two tables related by a foreign key. Each time a row is inserted into one it uses an INSERT ... SELECT statement from the other to get the corresponding foreign key.

I have an application that is continuously doing a large number of these INSERT ... SELECT statements to populate this database. I have read that using a multiple row insert, i.e. INSERT INTO table (col1, col2, col3) VALUES (1,2,3), (4,5,6), ..., can be significantly faster than individual inserts. Unfortunately it appears that splitting the database into separate tables prevents me from taking advantage of this? Is there a workaround or another way to improve the speed of these inserts?

EDIT:

Would any of the following be likely to help?

Combining the INSERT ... SELECT statements with a UNION.
A multiple row insert into a temporary table followed by a JOIN and
a single INSERT ... SELECT.
Splitting the inserts among multiple threads.


texts: Can I combine SELECT TOP() with the IN operator?

texts:
I was wondering if there is a way for a SELECT query to be made on each matched value found within IN.

Consider that the table below contains 100 records for each of the values included referenced by the INoperator, I'd like only 50 to be returned for each match.

SELECT TOP (50) COLUMN
FROM TABLE
WHERE COLUMN IN (1,2,3)

Is there an efficient way I can do this?

The only way I could think of is to execute the query for each value within IN which would be way too slow considering the actual query retrieves many column from various tables.


texts: Filter column by records matching a timezone in Postgres

texts: I want to check whether any rows in my column (TIMESTAMP WITH TIME ZONE) are not stored using UTC. Is there a query I can write to match values that are stored with a TZ offset of 1 or 8 or 11 or (not 0)?


texts: Returning correct timezone offset from join

texts:
Meeting table

|MeetingID | Start               | Timezone             | LocalStartTime
|13        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |
|23        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |
|3         | 2018-01-31 19:00:00 | (UTC+10:00)Hobart    |

Timezone Offset

|Timezone ID| StartDateTime    | EndDateTime      | Timezone              | OffsetMin
|1          | 2017-09-30 16:00 | 2018-03-31 16:00 | (UTC+10:00) Melbourne | 660
|2          | 2018-03-31 16:00 | 2018-10-06 16:00 | (UTC+10:00) Melbourne | 600

Hi, I have joined the above two tables in order to retrieve offsetmin, however the only foreign key i have in the meeting table is Timezone. Joining on this returns two rows for each meetingID. This is because each timezone is listed twice inside the timezone offset table, with two seperate offsetmin values based on whether it is day light savings or not.

select m.meetingid, m.start, m.timezone, m.localstarttime, t.offsetmin
from meetingtable as m
join timezone as t

Meeting Table With Offset

|MeetingID | Start               | Timezone             | LocalStartTime| OffsetMin
|13        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |               |660
|13        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |               |600

I am trying to return only the correct timezone offset based off its date, whether or not it falls into daylight savings or not (Timezone ID 1 or 2)

I am using MSSQL


texts: stored procedure to display error messages & success when a sp_refreshview is ran

texts:
How can I convert this stored procedure to display error messages when a sp_refreshview fails? I need to know which view failed. I would also like to know which "database_name.SQL_View_object_name" that were successful. The purpose of the script is to try to refresh the all the views of the specific sql databases that start with BAF3_% and BAR3_%

            SET ANSI_NULLS ON
            GO
            SET QUOTED_IDENTIFIER ON
            GO
            --SET QUOTED_IDENTIFIER ON|OFF
            --SET ANSI_NULLS ON|OFF

            ALTER PROCEDURE [dbo].[csp_Refresh_All_Views]
                @cycles AS VARCHAR(50)
                ,@databases_name AS VARCHAR(500) = NULL
            AS
            DECLARE @tab TABLE
                (
                  id INT IDENTITY(1, 1)
                         PRIMARY KEY ,
                  SQLname VARCHAR(50)
                );
            INSERT  INTO @tab
                    ( SQLname
                    )
                    SELECT  name
                    FROM    master..sysdatabases
                    --WHERE   name IN ( @databases_name );
                    WHERE   name LIKE ('BAF3%_+@cycles')
                    OR name LIKE ( 'BAR3%_+@cycles' )
                    AND name IN ( @databases_name )
                    AND NAME NOT LIKE 'X_%';
                    --WHERE   name IN ( 'BAF3_AR_201506', 'BAF3_MR_201506', 'BAF3_201506',
              --                        'BAR3_AR_201506', 'BAR3_MR_201506', 'BAR3_201506' );

            DECLARE @sql VARCHAR(8000) ,
                @i INT ,
                @count INT ,
                @sqlname VARCHAR(50);

            SET @i = 1;
            SELECT  @count = COUNT(*)
            FROM    @tab;
            WHILE @i <= @count
                BEGIN
                    SELECT  @sqlname = SQLname
                    FROM    @tab
                    WHERE   id = @i;

                    SET @sql = '
              USE ' + @sqlname + '
               DECLARE @viewName AS SYSNAME
            DECLARE listOfViews CURSOR FOR
             SELECT TOP 1000 ''[''+name+'']'' AS [name]
             FROM sysobjects
             WHERE xtype = ''V''
            ORDER BY [name]

            OPEN listOfViews

                FETCH NEXT FROM listOfViews into @viewName

                WHILE (@@FETCH_STATUS <> -1)
                 BEGIN

             BEGIN TRY
                    FETCH NEXT FROM listOfViews INTO @viewName
                     EXEC sp_refreshview @viewName

             END TRY
            GO
            BEGIN CATCH
                SELECT ERROR_NUMBER() AS ErrorNumber;
            END CATCH;
                END

            CLOSE listOfViews

            DEALLOCATE listOfViews;
            ';

            --print @sql
                    EXEC (@sql);

                    SET @i = @i + 1;
                END;


texts: View column has an 8000 character limit?

texts:
In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 characters, even though the update contains a greater number of characters.

Is there some kind of limit on the number of characters that a view can receive for an update?

Ok I'm working with the application development team on this and they think they found a way around it so they discarded the code they were using as their test case.  I'll do my best to give the general idea. The update statement looked something like:

UPDATE vorders
set [description] = '<br> This is some text about the order </br>'
    + CHAR(34) + 'This is more text' + CHAR(34) + 'and on it goes'
where orderid = 12345

We would then check the length of the characters put into the field by running the following query

SELECT LEN([description])
  FROM vorders
 WHERE orderid = 12345

What the application guys found is that if they remove the + CHAR(34) + part of the code the length would go up to 8700 characters.  With the + CHAR(34) + part of the code in it was truncating the text down to 8000 characters.  This made me suspect that it was some kind of implicit conversion; however, we found that the truncation did not occur with one or two of the + CHAR(34) + in place.  The original statment had something like 30 of them.

Any idea what might be causing this cause I'm at a loss on this one.


texts: Design for table with multiple links to other table

texts:
I am designing a database to keep track of our network equipment. My db has 2 tables:

sites & circuits

(circuit = cable) & (site = property location)

Each circuit runs between two sites, so it should relate to two sites, yes? The circuit table has the following  columns: ID_circuit (PK), circuit_number, site1, site2. I thought that I could relate my ID_site field to both site1 and site2, but I cannot. Would I be better off creating a whole other table to hold the locations of both cable ends?


texts: New query taking over 60% of running time

texts:
As part of a daily automated reporting system, we run a series of queries to check different operating parameters from our customers' assets tables.

Last Tuesday I added a new check that works, but triggered a few performance alarms that led me to analyze what might be wrong and I ended up realizing that the new check actually increased the overall checks time about 160%.

Drilling down this new check I narrowed it to actual DB query that is affecting the whole process:

select case COALESCE(max(driverid),-1) when 0 then false when -1 then null else true end
from reports.avl_historico_354676052451142
where DATE_PART('day', now() - fecha ) < 5;

And when doing an explain analyze verbose, I get:

Aggregate  (cost=75004.56..75004.58 rows=1 width=4) (actual time=544.460..544.461 rows=1 loops=1)
  Output: CASE COALESCE(max(driverid), (-1)) WHEN 0 THEN false WHEN (-1) THEN NULL::boolean ELSE true END
  ->  Seq Scan on reports.avl_historico_354676052451142  (cost=0.00..73782.38 rows=488873 width=4) (actual time=535.565..541.467 rows=9661 loops=1)
        Output: fecha, latitud, longitud, altitud, velocidad, cog, nsat, tipo, utc_hora, fix_fecha, imei, registro, input1, input2, input3, input4, hdop, adc, ignicion, adc2, power, driverid, ibutton2, ibutton3, ibutton4, trailerid, adc3, adc4, horometro, odometro, panico, bateria, bateriaint
        Filter: (date_part('day'::text, (now() - avl_historico_354676052451142.fecha)) < 5::double precision)
        Rows Removed by Filter: 1457143
Planning time: 0.078 ms
Execution time: 544.582 ms

So I'm guessing that this query might need some kind of help / improvement, but I'm completely clueless about it. So I'm asking: what improvements would you recommend for this scenario? Indexes? Query rephrasing? Others? What else should I provide for deeper understanding of the model?

Please consider that the sample table in the query has a large number in its name, which stands for a deviceId and we have around 30k tables of those which are queried regularly...it's some kind of a table partitioning model.

Update!

New results from the indexing suggested by @a_horse_with_no_name :

Aggregate  (cost=5478.72..5478.74 rows=1 width=4) (actual time=8.302..8.302 rows=1 loops=1)
  Output: CASE COALESCE(max(driverid), (-1)) WHEN 0 THEN false WHEN (-1) THEN NULL::boolean ELSE true END
  ->  Index Scan using reports_avl_historico_354676052451142_index_fecha on reports.avl_historico_354676052451142  (cost=0.43..5473.68 rows=2017 width=4) (actual time=0.026..4.644 rows=12249 loops=1)
        Output: fecha, latitud, longitud, altitud, velocidad, cog, nsat, tipo, utc_hora, fix_fecha, imei, registro, input1, input2, input3, input4, hdop, adc, ignicion, adc2, power, driverid, ibutton2, ibutton3, ibutton4, trailerid, adc3, adc4, horometro, odometro, panico, bateria, bateriaint
        Index Cond: (avl_historico_354676052451142.fecha > (('now'::cstring)::date - 5))
Planning time: 0.181 ms
Execution time: 8.332 ms

I'll time the overall execution and keep you posted.


texts: Get rows with most recent date for each different item

texts:
Let's say  this is the sample date coming from a join of 2 tables. Database is Postgres 9.6

id  product_id  invoice_id  amount       date
1    PROD1       INV01       2          01-01-2018
2    PROD2       INV02       3          01-01-2018
3    PROD1       INV01       2          05-01-2018
4    PROD1       INV03       1          05-01-2018
5    PROD2       INV02       3          08-01-2018
6    PROD2       INV04       4          08-01-2018

I want to know if it's possible in a optimized way to:

Get all the PRODx with their respective INVx which have the latest date, but per product_id. Please note that records unused from a day may be reported to a new one.
This means:

id  product_id  invoice_id  amount       date
3    PROD1       INV01       2          05-01-2018
4    PROD1       INV03       1          05-01-2018
5    PROD2       INV02       3          08-01-2018
6    PROD2       INV04       4          08-01-2018

Get daily summed amounts for each PRODx but fill the gaps with the previous ones if day does not exist.

This means:

 product_id    amount       date
   PROD1         2          01-01-2018
   PROD2         3          01-01-2018
   PROD1         2          02-01-2018
   PROD2         3          02-01-2018
   PROD1         2          03-01-2018
   PROD2         3          03-01-2018
   PROD1         2          04-01-2018
   PROD2         3          04-01-2018
   PROD1         3          05-01-2018
   PROD2         3          05-01-2018
   PROD1         3          06-01-2018
   PROD2         3          06-01-2018
   PROD1         3          07-01-2018
   PROD2         3          07-01-2018
   PROD1         3          08-01-2018
   PROD2         7          08-01-2018

A few thoughts:

For first question I could obtain the max(date) for each PRODx and the pick for each PRODx the rows that have the date=with max(date) but I was wondering if there's faster way to obtain this given a large number of recors in the database
For the second question, I could generate a series of dates for the interval needed and then use WITH rows As and do the query grouping by product_id and sum by amount and then select for each date the previous values from rows with a limit 1 but that does not sound that optimized either.

Looking forward for any input. Thank you.

Later edit:
Trying to give DISTINCT ON () a try.

If I have distinct on(product_id, invoice_id) then I don't get only the most recent ones for the most recent date. If there were invoice_ids in the the past, beside the latest date, then they will be returned
If I have distinct on (product_id) then it returns from the most recent date, but as normal, only the last rows even if in the last day I have two positions for PROD1.

Basically I need something like 'I need for the most recent date, all the product_ids and their invoice_ids while keeping in mind that a product_id can have multiple invoice_ids'

Later edit 2:

Running a query like for first question seems to be reasonably fast:

select product_id, invoice_id, amount
from mytable inner join myOtherTable on...
             inner join (select max(date) as last_date, product_id
                         from mytable
                         group by product_id) sub on mytable.date =
                         sub.last_date


texts: Storing html in mysql text fields, or include files? (html formatted articles)

texts:
I'm wondering if I should store long html formatted articles in my MySQL database, or if it would be better to store a reference to a file, and include that instead. It is not just paragraphs, but lists, tables, images (<img src="">'s), and other containers containing side notes and such.
The articles are usually somewhere between 150 000 and 250 000 characters (including spaces). But that is excluding html tags.

It works fine storing it in database. But was wondering if there was a better approach?
There is no search options on this site.


texts:
We have crm-2015 on-prem, inplace and running and causing lot of deadlocks when someone tries to update anything in batch and same time if multiple users are reading those activities.

We are planning to turn on RCSI. I already did on DEV. server and tested and didn't get any locks, but I know that's not the right and only test.

My questions to all experts here is,

Current size of DB is 40 GB, how hard is to turn that option now to ON.
Once we turn on, can we turn back off using the same procedure stopping all   the services making DB with one active transaction only and then execute ALTER DB command with set option to OFF.
I also read that some code changes requires but in existing application what to inform our developers about changing the code, or it is best to do all operations which we are performing and see if anything breaks.
last but I guess this most important once we turn on does tempdb database version store cleanup  happens by itself or do we have to turn on, if we have to from where?

Please help.

Thanks.


texts: AlwaysOn Availability Groups - Proper Quorum across WAN

texts:
I am evaluating the usage of AlwaysOn Availability groups with one node located in one data center, and another node located in a DR site. Both nodes are running in vmware on the same domain.

I've been reading about the dangers of using a DFS file share as the "file share" in a Node + File Share Majority. However, vmware's high availability guide says to use Node + File Share for any 2-node cluster. What should I be using across a WAN for that if not a DFS Share?


texts:
I want my query to show all months grouped together, regardless of the year.  For example, I would want this to be the output:

01/13/2015
01/01/2016
02/07/2015
02/01/2016

This is sample DDL - what must I alter so months are all displayed together regardless of year?

CREATE TABLE randomdate(dateofwork timestamp);

INSERT INTO randomdate VALUES('2016-01-01 00:00:00-05');
INSERT INTO randomdate VALUES('2015-01-13 00:00:00-05');
INSERT INTO randomdate VALUES('2016-02-01 00:00:00-05');
INSERT INTO randomdate VALUES('2015-02-07 00:00:00-05');

Select
date_trunc('month',randomdate)
from randomdate
GROUP BY date_trunc('month', randomdate)
ORDER BY date_trunc('month', randomdate) ASC


texts: Query not returning items in the original order

texts:
Consider the following set, which is the content of the table variable @ProductContent that was just created for the query that runs against it:

Notice how items are sorted by media_type_id, image_weight, image_id and finally image_width DESC.
If I run the following query:

SELECT -- <images>
         pcs.image_type AS [@Type]
        ,pcs.image_type_id AS [@ImageTypeId]
        ,(
            SELECT -- <imageCollection>
                 pcc.image_id AS [@Guid]
                ,pcc.image_angle AS [@Angle]
                ,pcc.image_weight AS [@Weight]
                ,(
                    SELECT -- <image>
                         pci.mime_type AS [@MimeType]
                        ,pci.content_guid AS [@Guid]
                        ,pci.url AS [@Url]
                        ,pci.media_type_id AS '@MediaTypeId'
                        ,t.media_type_description '@Description'
                        ,(
                            SELECT DISTINCT -- <attribute>
                                a.meta_attribute_id '@Id'
                                ,a.meta_attribute_name  '@Name'
                                ,v.meta_value_name AS '@Value'
                            FROM [ADS2].dbo.ads_digital_content_meta m
                            JOIN [ADS2].dbo.ads_digital_content_meta_atr_voc a ON a.meta_attribute_id = m.meta_attribute_id
                            JOIN [ADS2].dbo.ads_digital_content_meta_value_voc v ON v.meta_value_id = m.meta_value_id
                            WHERE m.content_guid = pci.content_guid
                            FOR XML PATH('Attribute'), TYPE)
                    FROM
                        @ProductContent pci

                    JOIN [ADS2].dbo.ads_digital_content_media_type t ON t.media_type_id = pci.media_type_id

                    WHERE
                        pci.image_id = pcc.image_id

                    GROUP BY pci.content_guid, pci.mime_type, pci.url, pci.media_type_id, pci.image_width, t.media_type_description

                    FOR XML PATH('Image'), TYPE)

            FROM @ProductContent pcc

            WHERE pcc.image_type_id = pcs.image_type_id
            AND pcc.product_id = pc.product_id

            GROUP BY
                    pcc.product_id
                ,pcc.image_weight
                ,pcc.image_id
                ,pcc.image_angle

            FOR XML PATH('ImageCollection'), TYPE)

    FROM @ProductContent pcs

    WHERE pcs.media_type_id = 15
    AND pcs.product_id = pc.product_id

    GROUP BY
        pcs.image_type,
        pcs.image_type_id

    FOR XML PATH('Images'), TYPE

For some reason the the images don't come out in the right order. For instance, the image with width 400 comes first, instead of the one with width 640. My question is, what part of this query is changing the order from the way it is in @ProductContent? I'd rather just sort once before inserting into @ProductContent as the order by clauses in the XML query are a big performance hit.


texts: How does Gcache save state in galera cluster work

texts:
I am wondering how does gcache save data.
Does gcache always save data for the last seqno of database or which point of seqno?


texts: Why is MySQL dump not restoring all my tables?

texts:
I have Mysql 5.5 server on a EC2 instance which have multiple sql users to perform operation , now i want to create a back up db server .

For which i have created a mysql dump using root user .
Installed Msql 5.5 on backup db server .
Restore the dump file on backup server.

The issue is in my Mysql server i have 110 tables but when i restore dump file it only create 35 tables in my backup db .
I have nearly 100 Gb data on Server and my dump file size is 32 GB when i restore the dump it also create the data nearly the size of dump file .
What is the problem with dump file ?


texts:
I used to try a replica set demo in same db folder. The replica set name is "test". Then I tried to re-build repl set with different name "repl". After i reconfig, I get error message below:

{
        "ok" : 0,
        "errmsg" : "replSetReconfig should only be run on PRIMARY, but my state is REMOVED; use the \"force\" argument to override",
        "code" : 10107,
        "codeName" : "NotMaster"
}

So I follow the errmsg, and try to run the command below:

rs.reconfig(config,{"force":true})

Now I get following error message:

{
        "ok" : 0,
        "errmsg" : "New and old configurations differ in replica set name; old was test, and new is repl",
        "code" : 103,
        "codeName" : "NewReplicaSetConfigurationIncompatible"
}

too bad! So I use the previous name "test". But I get the error again.

{
        "ok" : 0,
        "errmsg" : "New and old configurations differ in the setting of the arbiterOnly field for member 127.0.0.1:27020
; to make this change, remove then re-add the member",
        "code" : 103,
        "codeName" : "NewReplicaSetConfigurationIncompatible"
}

I just really don't understand, if every configure information has to be consistent with the previous, why do we need to reconfig it?

Someone said you can drop local database, but this operation still doesn't work.

 {
            "ok" : 0,
            "errmsg" : "Cannot drop 'local' database while replication is active",
            "code" : 20,
            "codeName" : "IllegalOperation"
    }

Oh, MGD! I just couldn't understand why it is so difficult to restart a replset in current db folder.


texts: How to properly re-run a deadlocked trasaction in TSQL

texts:
In TSQL (SQL Server 2012), is this a proper way of re-running a transaction that was killed due to deadlocked - it was chosen as the deadlock victim.

EX:  "Transaction (Process ID 118) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

__RETRY_BATTLE__:
BEGIN TRY
    begin transaction BATTLE

    ---- DO STUFF
    ---- May call other stored procs

    COMMIT transaction BATTLE

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK

    IF ERROR_NUMBER() = 1205 -- deadlock occured in this SP
        OR (ERROR_NUMBER() = 50000 AND left(ERROR_MESSAGE(), 8) = 'DEADLOCK' ) -- deadlock occured in some called SP
    BEGIN
        WAITFOR DELAY '00:00:05'
        GOTO __RETRY_BATTLE__
    END

    RAISERROR('error ya!',11,1)
    return

END CATCH


texts:
Suppose one wanted to cache simple aggregate data over a table or grouped parts of a table, one could simply make a table with the aggregated results. If the original table is only appended to, it is simple to keep the aggregate-table up to date (let's assume an aggregate like SUM()).

However, I am wondering if there is a method to keep/cache aggregate data, over different attributes from the original table.

For example.

games(username text, win bool, t timestamp);

--- attribute, grouping
aggregate_games(wins int, username text);

SELECT * FROM aggregate_games
WHERE username=<...>

But it gets more complicated when I want to keep the aggregate data for many different possible attributes. For example.

games(win bool, username text, map text, revision int, mode int, team int, server inet);

--- attribute, group-combinations
aggregate_games(wins int, username text, map text, revision int, mode int, team int, server inet);

Ultimately, what I want to be able to do is something like:

SELECT sum(wins)
WHERE username={*,{username}}
  AND map={*,{mapname}}
  AND revision={*,{revision}}
  AND mode={*,{mode}}
  AND team={*,{team}}
  AND server={*,{server}}

Is there a good way to do this without:

re-aggregating all the data
without keeping an insane unscalable number of combinations of aggregate data around

To extend it a bit further, I want to actually keep the aggregate data itself over time, so essentially my aggregate data would have a timestamp (and so, for example, it could be graphed over time).


texts:
I have a query with a CTE that uses a Spectrum table. When I try to union the CTE with itself:

WITH foo AS (
  SELECT col1
  FROM spectrum.bar
)
SELECT * FROM foo UNION ALL
SELECT * FROM foo;

I get the following error:

[XX000][500310] [Amazon](500310) Invalid operation: Relation "spectrum_foo_58abe8db83a3e" already exists Details: ----------------------------------------------- error: Relation "spectrum_foo_58abe8db83a3e" already exists code: 13 ..

Is this a known issue and/or is there any way to work around it (that doesn't involve pasting the original query for every union)?


texts: Stop on error in PL/SQL developer or Oracle SQL Developer

texts:
At this time I am using TOAD to apply SQL*Plus scripts and there is an option to stop on the first error. After it I can fix the script and apply the remaining part.

Due to license issues we can't use TOAD anymore and we have a choice between Oracle SQL Developer or PL/SQL Developer. Is it possible to set such behaviour there?

For example, Aqua Data Studio just shows all errors at the end of execution, but it doesn't stop on them. Thanks!


texts: Database design for eCommerce website with multiple products having huge specifications index

texts:
I am designing a web site for online shopping and I have a question related to that. As you know that shopping website may have different product with different statistic/specification. What is the best way to design a database? Below are the thoughts which comes to my mind

Creating different tables for different product type. Ex. Phone, Tv, Video Games, Watches, Clothes, Computer etc.

  Concern:

but let's say a to show mobile specification [just like this example] I need to have around 40-50 columns to store a mobile specification. And let's say tomorrow one new feature comes to mobile then I probably have to add one more column which will hold the value for that specific model and for every other mobile that column will be NULL.

2: Having major product tables ex. Electronics, clothing and accessories, health etc.. and a separate specification table for each table. Ex. Electronics table will hold the master details for tablets, mobile, land line etc. and Electronics_Spec will contains the columns with related specification.

  Concern:

There are many characteristic between products which does not match with others. Ex. land line phone will have a specification about wire length while as tablets won't.

  General concerns:

There are certain products which has quite less specifications. Ex: bed, toys , footwear.

Is there any other better way to design a database to hold huge specifications related to multiple products?

My intention is to save database space to the best.
I sincerely apologize for my bad English.


texts: Mysql Replication - How it works offline?

texts:
i have a mysql server on a local machine but the connection is not got for users outside the local network. I want that database to be available on another server wich can handle way more requests and is more stable than the local server.
My question is:
   What is the best way to replicate the data from the local server into the web server?
   I've readed a little about slave servers, are they made for this case?
   If i use a mysql slave server and the internet on the main server goes offline, what happens to the slave?
   If the main server makes some database changes while offline will these changes be written on the slave when the master goes online again?
   Are there any performace loss for using a slave server?

I do not want a backup server, i want a server to redirect ouside queries into it, so the main server wont be affected for huge traffic and etc.


texts: Database encryption and key management with pg_crypto

texts:
I am looking at the possibility of having to store root passwords for virtual machines in reversibly encrypted format in a database.  The reason is that local logins will sometimes be required to machines as much as they are discouraged and we need to be able to look up root passwords.  Access of course would be heavily restricted to a few admins.  These would be unused except when the vm is inaccessible over the network.  Synchronizing users and passwords in this regard poses similar problems, namely that if the network is down, how does the sudoer log in with the guaranteed most recent password?

What is the best way to plan for keys to be rotated?  Should I just use a passphrase that every admin has?  This raises some red flags in my mind.  Is there a way to securely manage keys such that admins can set their own passwords?  Maybe use public key encryption and encrypt a symmetric key with each admin's public key?

Here I am looking just for general outlines of a solution regarding key management of encrypted sensitive data.  If I can't figure out how to implement from that level I can ask more questions.


texts: ORACLE: Can I get results of "SHOW PARAMETER" into a variable or table?

texts:
When I execute SHOW PARAMETER CONTROL_MANAGEMENT_PACK_ACCESS, it gives me the value of this Oracle parameter as part of what appears to be a single row with three columns (name, type, and value). How can I get this value into a variable or table that I can use later in my script?

I'm trying to make a decision later in my script based on a few parameter values and I'm not sure how to store them in something persistent.


texts: Dynamic select column name on the basis of a flag

texts:
I have a config table where there are config values are stored as key, value pair in each row.
 And there is another table which have two columns (lets say column1 and column2). Now I have to select column1 value in select statement if value for flag1 is present and equal to 1 in config table and select column2 if value for flag1 is absent or equal to 0.

I fetched the flag value in a variable-

DECLARE @flag smallint
SELECT @flag = CASE WHEN EXISTS (SELECT 1
                                 FROM   dbo.ACCOUNT_CONFIG WITH (nolock)
                                 WHERE acct_id = #{accountId}
                                 AND key_name = 'flag1' AND key_value = 1) THEN 1
                                 ELSE 0 END

Now I have to do some thing like:

SELECT col1, col2
From   ACCOUNT_USER table
where  acct_id = #{accountId}
AND    CASE @flag1 = 1 THEN column1 = #{COLUMN_VALUE}
       ELSE column2=#{COLUMN_VALUE} END

But above statement is not working for me.


texts: XACT_ABORT ON not working as expected on SQL Server 2012?

texts:
It looks like XACT_ABORT ON is not working as expected. Here are a table and a procedure that inserts into it:

CREATE TABLE rain
    (
      rain_time DATETIME ,
      location VARCHAR(100)
    );
GO

CREATE PROCEDURE insert_rain
    @rain_time DATETIME ,
    @location VARCHAR(100)
AS
    BEGIN;
        SET XACT_ABORT ON;
        BEGIN TRANSACTION;
        PRINT 'before insert';
        INSERT  INTO rain
                ( rain_time, location )
        VALUES  ( @rain_time, @location );
        PRINT 'after insert';
        COMMIT;
    END;
GO

I've created a trigger to imitate a runtime error:

CREATE TRIGGER rain_no_insert
ON rain
FOR INSERT
AS
RAISERROR('Cannot insert', 16, 1);
GO

When I invoke the insert procedure, I do get the error, but the execution does not stop, because the last PRINT prints its 'after insert' message:

EXEC insert_rain
    @rain_time = '2015-03-30 12:34:56',
    @location = 'Wautoma, WI';

before insert
Msg 50000, Level 16, State 1, Procedure rain_no_insert, Line 5
Cannot insert

(1 row(s) affected)
after insert

Also the transaction commits:

SELECT * FROM dbo.rain;

rain_time               location
----------------------- ---------------------------------
2015-03-30 12:34:56.000 Wautoma, WI

Why my XACT_ABORT does not abort?


texts: Cannot login to SQL Server from Visual Studio, same credentials work on SSRS page

texts:
I am running into a strange issue.  I have a SQL Server login that I use for running reports on SSRS.  When I go to the SSRS page I can modify the stored credentials to use that login:

Now when I try to build that same login right into the report in Visual Studio I get the following error:

I am not sure why I can login from one place but not the other.  I am using SQL Server 2014 Standard and VS 2013.  Does anyone know what permissions setting needs to be change, or what to do to be able to deploy my report using this?


texts: What is the difference between PAGEIOLATCH_* and IO_COMPLETION?

texts:
according to this blog:

SQL Server Books online defines IO_COMPLETION as “Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_ waits”*

What do non-data page IOs mean? or we can put the question in other way: What does actually “...waiting for I/O operations to complete“
mean?


texts: Select a single row from two distinct columns

texts:
Possible Duplicate:
  How to get the MAX row

I need to fetch the "latest" row for certain conditions in my tkhist1 table. Tickets are bound to TicketNo and LocationID, and I need to select one row for DISTINCT values of both of those columns.

Multiple rows may share the same TicketNo and LocationID though, they are additionally defined by a UniqueID column. I need to select a single row for each combination of TicketNo and LocationID, SELECTING the row with the single highest UniqueID value. I do not need Unique ID in my SELECT statement.

The following query selects some of the information I need, but it does not only grab one row per UniqueID. If a row has different values for FreightPay (or any other column) it will be SELECTED as a new row.

SELECT DISTINCT
    [TicketNo],
    [LocationID],
    [FreightPay]
  FROM [Tkhist1]

I need a single row for each distinct pair of TicketNo and LocationID, selecting only information from the highest value (DESC) UniqueID


texts: Small MySQL table swap/replace taking incredibly long time

texts:
We have an import job that commits data to a staging table, and then swaps the production/staging tables using the below SQL:

RENAME TABLE user_accounts TO user_accounts_temp, user_accounts_staging TO user_accounts, user_accounts_temp TO user_accounts_staging;

Aside from writing to the staging table during the import, the tables are only read by our application (no other writes). The staging/production tables are always around same size (~40k rows). All reads to the staging/production table queries are by ID. There are no FKs in the tables.

The mystery is that the time to do the RENAME takes between 0sec-8min (usually it's 0sec). But in the latter case, it blocks reads to the table causing our production site to hang.

How can I fix/understand what the problem is?  My understanding of what I've read about the rename operation is that this should always be nearly instant, and this is the best practice for doing periodic imports.


texts: How to update mysql my.cnf without getting error?

texts:
I updated the MySQL my.conf file and and restarted my server without a problem.

As soon as I tried to access a table I received the error

mysql #1033 - Incorrect information in file:

I reverted the my.cnf file back to the original and the server is working properly.

I am looking to update some of the innodb settings:

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_file_size
innodb_log_buffer_size

I think I am seeing that I have to update the my.cnf file before creating any databases on the server, but that doesn't seem right.

Is there a way to update the my.cnf file after a database has been created on the server?

CentOS 5.6

MySQL 5.0.77

Original:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

New:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# This is my configuration!
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M

innodb_log_file_size=1G
innodb_log_buffer_size=8M

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


texts:
I am trying to use SQL transaction with my SSIS package so if any error comes changes are rollback.

I am using an "Sequence Container" in Control Flow for this and setting its "Transaction Option" to "Required".

But it fails to execute the package and gives DTS error . (Everything is working fine if i remove the Data Flow from Sequence Container).

i have done the changes on server as described here:
http://www.databasesupport.eu/categories/sql-server/1482-ssis-distributed-transactions

Could you please tell me if there is something i am missing.
Do i need to do DTS settings on both the servers from where i am importing the data and to which i am importing.

Please tell what i am missing. I am very new to SSIS.


texts: How to Upgrade Master Slave MySQL on AWS RDS?

texts:
I already have Master Slave configuration on AWS RDS. We want to upgrade 5.5 to 5.6.23. ..What should be correct sequence to Upgrade ?

Upgrade Master ( Using Modify Instance ) --> Upgrade Slave
Upgrade Master  ( Using Modify Instance ) --> Create a new Read Replica ?


texts: Database design - Members & companies represented by members

texts:
I'm struggling in database design problem and it seems I can't get it out properly.

I need some help on this. Maybe it's a dumb case but I'd like to validate or correct my scheme and then go ahead.

The app should behave like this:

a user can be part of multiple accounts and have different roles (admin or not)
an account can have one or many groups
each group can have one or many members
a member can be part of many groups
a member can be either an user or a legal entity (company)
if it's a company, then the company is represented by a user
a user can represent 0,1 or many companies in different groups

Here is my actuel scheme where I have a n-to-m relationship on Groups-User-Companies.

Here in GroupMembers, the users_id or companies _id will be filled either it's a legal entity or individual.

And the CompanyRepresentedBy table will contain the group_id, user_id and company_id

I'm not really sure of this scheme.

To have a better understanding, here is a diagram of the relationship Account-Group-Member-User

Thank you all!

PS: I can provide the MySQL Workbench file if needed


texts:
Im using 32vCPU and 120GB VM for Mysql 5.6.

it's a dedicated MySQL server.
15000 IOPS SSD disk.
Total database
size 40G

This server was working fine for a couple of months. Memory usage is never crossed 60GB.

But a few days back the mysql process killed due to OOM. After restart memory usage is fine, but its gradually increasing within 6 to hrs it'll reach the maximum limit and killed by OOM Killer.

After installing the mysql, I used percona configuration wizard to generate the my.cnf file.

Here are the important parameters:

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 5000
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 50

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 90G

The last downtime happened yesterday. I have verified that the InnoDB buffer never crossed 30GB for the whole day.

Buffer Pool usage

Memory used:

The both highlighted points are the maximum limit.

InnoDB IO

Mysql client thread activity:

Temo Objects:

Mysql Internal memory

From a shell script:

+------------------------------------------+--------------------+
|                          key_buffer_size |          32.000 MB |
|                         query_cache_size |           0.000 MB |
|                  innodb_buffer_pool_size |       92160.000 MB |
|          innodb_additional_mem_pool_size |           8.000 MB |
|                   innodb_log_buffer_size |           8.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |       92208.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           0.250 MB |
|                         read_buffer_size |           0.125 MB |
|                     read_rnd_buffer_size |           0.250 MB |
|                         join_buffer_size |           0.250 MB |
|                             thread_stack |           0.250 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |          32.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          33.156 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |               1399 |
|                          max_connections |               5000 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |      138593.594 MB |
|                              TOTAL (MAX) |      257989.250 MB |
+------------------------------------------+--------------------+

I just wanted to know that, InnoDB never crossed >30, but how mysql used all the memory?


texts: MySql column without aggregates and group by clause

texts:
Since I am coming from MSSQL world I am confused, what happening in queries where you have group by clause and other columns in select which do not go through aggregates functions.

I was shocked when MySql executed query like this

select * from myTable
group by columnThatIsNotPk

Or fiddle like this
http://sqlfiddle.com/#!9/b30bf5/1

My questions are:

Is there an explanation which data I am going to get for columns that not exists in group by clause?
Is it ok to use queries like that?
Do I lose or gain performances with queries like this?
Does MySql implements First and Last aggregates as it does JetEngine in ACCESS, and unfortunately missing feature in MSSQL engine?


texts: Excluding database objects from a DacPac database deployment?

texts:
As I understand it, a sql server DacPac (visual studio data-tier app) applies to the entire database that it's deployed in. However, for my particular database, I have some tables and stored procs that I want to always remain unchanged in one environment, regardless of the dacpac's current state.
I've been reading about partial/composite database projects, but I'm not sure if/how this can be done with DacPac, or if that is even the solution I'm really looking for. Any ideas?


texts: Running queries with SQLCMD vs. Running queries with SSMS

texts:
I've noticed something which i'm not sure about. can it be that running queries directly with SSMS is much faster than running queries with SQLCMD? I've noticed that the results of queries that ran with SSMS are written much faster to the disk.

What are the differences?

Thanks.


texts: Alternative to Temporary Table with Create View - MySQL

texts:
I have a view which uses many tables, and statements and functions. It is very slow, the main reason being that I only have read-only access to the main tables which do not have the appropriate indexes, so joins evaluate very slowly.

Following some advice I have broken the joins down using temporary tables, to ensure that every join has indexed fields on both sides. This has worked very well. If I run a select to pull the final output it runs 60x quicker than previously. However I cannot use this to drive the necessary view, as views cannot refer to temporary tables.

Are there any alternatives to temporary tables, or different approaches I could take?

Thanks


texts: MySQL Slows Down after MYD file reaches 2GB (64 Bit OS)

texts:
MySQL seems to be writing rows OK (75,000 rows/min) until when the MYD file reaches 1.8 GB, and then the insert rate drops immensly (10,000 rows/min), and 1 hour later it's even slower (6k rows /min).

The engine is MyISAM, there are no indexes, no PRIMARY key. The machine has 16GB RAM and 8 Cores.

The MySQL configuration is:

key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

At the time of the 'faster' imports, the CPU was > 100% load, and the RAM was 50% in use. Now the CPU is hardly reaching 3% load, and the RAM 1%.

The disk seems to be operating OK, hdparm says:

/dev/vda1:
 Timing cached reads:   14780 MB in  2.00 seconds = 7399.35 MB/sec
 Timing buffered disk reads: 1148 MB in  3.00 seconds = 382.36 MB/sec

And a file writing test says:

dd if=/dev/zero of=/tmp/output bs=8k count=100k; rm -f /tmp/output

102400+0 records in
102400+0 records out
838860800 bytes (839 MB) copied, 2.55773 s, 328 MB/s

I don't think it's the OS, but I'm not sure how to probe that.

In terms of MySQL idle queries, there are none, SHOW PROCESSLIST returns:

+-------+------+-----------------+--------+---------+------+-------+------------------+
| Id    | User | Host            | db     | Command | Time | State | Info             |
+-------+------+-----------------+--------+---------+------+-------+------------------+
| 18548 | .... | .........:57742 | dw_raw | Query   |    0 | init  | show processlist |
+-------+------+-----------------+--------+---------+------+-------+------------------+

Complexity of the queries: SIMPLE; the insert queries are pretty basic, EXPLAIN shows nothing unusual.

Other ways to 'attack' the problem:

Emptied the table and resumed the writing to see if the table size could be the problem: the writing is still slow, no change in writing speed.
Checked if the app that writes the insert is the bottleneck: seems to be writing fine to a file with good writing speed.

I am out of ideas at this stage, any feedback on what I could be doing next?

Here's a graph of how the CPU/Disk usage decreased:

Many thanks,
Vlad


texts: Introducing an 'age' category in a patients database

texts:
I want to do the following in my DB related to patients:

when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients
how can I translate the birthday into age category?
how to design that age category?


texts:
I am working on creating a simple exam system, in which you are going to be basically able to store students, classes, exams, questions and scores(and some other stuff that those depend on). I am also using Entity Framework code-first.
I am using inheritance for the Student and Instructor classes, which derive from the Person class. In SQL that it represented by a single People table containing a Discriminator column, as well as columns, representing each of the fields of both classes.
Here is a picture of my database diagram:

It is my first time designing a database this complicated and am really not sure if I have done everything right (specifically worried about circular references). I would really appreciate it if you could take a quick look and tell me if you see anything wrong.

Thanks a lot for the answer, mgw854.
The inconsistent naming is because of Entity Framework. I am using code first so it names the columns that it auto-generates(the foreign keys) as it pleases. If you could tell me a how to fix it, I will.
About the Student to Class relationship, it is kinda weird why EF generated it that way. It is probably because I forgot to include a collection of students inside the Class class :D
Also yes, now that I think about it, it definitely would be better if I added a Value column to each question which indicates how many points it gives you and then just use that to calculate the score on runtime.
Thanks for the help. I will fix those ASAP.
PS: I am not going to mark the question as answered, because I want to see some more suggestions, if that's ok with you :)

So I have made the changes and updated the diagram image. Looking for a way to make EF generate column names without underscores without having to write each name manually.

Just found how to fix the naming conventions in a nice way (EF custom naming convention).
Diagram updated again.


texts: Decode Base64 String Natively in SQL Server

texts:
I have a varchar column in a table in SQL Server which hold a base64-encoded text string which I would like to decode into it's plain text equivalent

Does SQL Server have any native functionality to handle this type of thing?

A sample base64 string:

cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA==

Which decodes to:

role:1 producer:12 timestamp:1468640222571000 latlng{latitude_e7:416989394 longitude_e7:-738966210} radius:19764


texts: Export a very big amount of files from a table

texts:
I'm using Oracle Database 11g Release 11.1.0.6.0 and have a table recording a very big amount of files (GIF) with IDs (ID).

  Name                                      Type
  ----------------------------------------- ----------------------------
  ID                                        VARCHAR2(50)
  GIF                                       BLOB

And currently I'm using this piece of code to export the pictures:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

This code works well, BUT the problem is, there're about 100 million rows in the table. On my computer the export speed is about only 7 pictures/second.

So, is there any method that can accelerate the exporting? Thank you!


texts: How many connections does Object Explorer use in SSMS?

texts:
I recently enabled DAC and noticed this error when first trying to connect with object explorer in SSMS:

  Cannot connect to admin:servername

  Dedicated administrator connections are not supported via SSMS as it
  establishes multiple connections by design.
  (Microsoft.SqlServer.Management.SqlStudio.Explorer)

It works fine via sqlcmd or just doing File>New>Database Engine Query.

My question is, how many default connections are made when you connect to a server in Object Explorer? I would guess if you have it configured to spawn a new query window that would be another connection? The SQL Server Agent?

I understand this answer: Is it possible to estabilish Dedicated Administrator Connection (DAC) using SSMS? but I was just curious what connections are made by default.

Thanks!


texts: Permissions for Cluster service account in SQL server

texts:
Recently i faced an issue where the SQL server 2012 being hosted in a 2 node failover cluster failed to start. And since the cluster and windows event log didn't help much, i started checking the SQL server error log, which had the below error every time i tried to start the SQL server role in failover cluster manager

  Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Could not find a login matching the name provided. [CLIENT: ]

so i went ahead created the login NT AUTHORITY\SYSTEM and also added it to the Sysadmin role. and it worked!,

Now i'm skeptical whether what i did is right or not security wise.

I've heard that creating NT AUTHORITY\SYSTEM within SQL server is not safe, If that is the case, Should i change my cluster service to a domain account?, Will this affect anything?
I added NT AUTHORITY\SYSTEM to Sysadmin role - Is this really required or can i provide only the required permission explicitly to the login


texts:
I have a personal webserver which were running under Ubuntu 14 LTS and I migrated it under 16.04 LTS a while ago.

Since I did it I have an error with MySQL server every time I'm updating packages on this server. I tried to solve the problem many time, then gave up because my MySQL server is still working but now I'm really bothered about that issue and want to find a fix.

So every time I run apt-get upgrade I get :

dpkg: error processing package mysql-server (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mysql-server-5.7
 mysql-server

So I tried everything in that post, removing all the my.cnf files and then finally remove the entire mysql folder and try reinstall the MySQL server --> Still get the error.

So I tried as described to get a verbose mode when running configure :

+ existingdatabase=1
+ touch /var/lib/mysql/debian-5.7.flag
+ '[' configure = configure ']'
+ APP_PROFILE=/etc/apparmor.d/usr.sbin.mysqld
+ '[' -f /etc/apparmor.d/usr.sbin.mysqld ']'
+ LOCAL_APP_PROFILE=/etc/apparmor.d/local/usr.sbin.mysqld
+ test -e /etc/apparmor.d/local/usr.sbin.mysqld
+ aa-status --enabled
+ apparmor_parser -r -T -W /etc/apparmor.d/usr.sbin.mysqld
+ deb-systemd-helper unmask mysql.service
+ deb-systemd-helper --quiet was-enabled mysql.service
+ deb-systemd-helper enable mysql.service
+ '[' configure = configure ']'
+ '[' -x /etc/init.d/mysql ']'
+ update-rc.d mysql defaults 19 21
+ '[' -x /etc/init.d/mysql ']'
+ invoke-rc.d mysql start
+ '[' configure = configure ']'
+ '[' 1 = 1 ']'
+ test_sysmaint_access
+ mysql --defaults-file=/etc/mysql/debian.cnf
+ result=0
+ mysql_upgrade --defaults-file=/etc/mysql/debian.cnf
Error occurred: Cannot setup server variables.
+ result=1
+ '[' 1 -ne 0 -a 1 -ne 2 ']'
+ echo 'mysql_upgrade failed with exit status 1'
mysql_upgrade failed with exit status 1
+ exit 1
dpkg: error processing package mysql-server-5.7 (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-5.7; however:
  Package mysql-server-5.7 is not configured yet.

dpkg: error processing package mysql-server (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mysql-server-5.7
 mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)

Here is just the end of the result but error seems to come from

+ mysql_upgrade --defaults-file=/etc/mysql/debian.cnf
Error occurred: Cannot setup server variables.

Also I tried to stop mysqld and restart it skipping databases:

sudo mysqld --skip-grant-tables &

and run:

sudo dpkg --configure -a

Same error. Now I'm out of ideas and if somebody an help me I will be really grateful.


texts:
My company has a huge (hundreds of millions of records) Oracle database of GIS/temporal information.  We are trying to switch to an open source alternative.

If it helps, we imagined the following requirements:

horizontal scaling (like elastic search, for example)
fully SQL compliant. We don't think we can use NoSQL, etc, because we would have to rewrite all our queries.
No license fee


texts: Is it possible to change SQL in a z/OS mainframe COBOL application so that it becomes eligible to be directed to the IBM System z Integrated Information Processor (zIIP)?


texts: Find entries, meant to only be available on specific days, being set on incorrect days

texts:
FYI: I'll be using different table names and column names to the ones actually used in the live system throughout this question, and (obviously) leaving out any tables I don't believe are relevant to the question.

So, this is a tricky one for me, and I'd like to get some help with this issue. I'm probably missing an easy solution here as I might just be a bit burnt out at the moment:

Tables and their relationship to each other:

Table "Cycle": This has "BeginDate" (type DATE), and "NumberOfWeeks" (type TINYINT). NumberOfWeeks will be either 4 or 5, and BeginDate will be some date, from which everything goes forward. For this question, let's say the BeginDate is 2017-10-23, and NumberOfWeeks is 5. This means that there's a cycle from the day set in BeginDate until 2017-11-26 (inclusive), and the next cycle then begins on 2017-11-27 (5 weeks after the initial BeginDate). These cycles continue forward like this indefinitely.

Table "Entity": This has EntityID as BIGINT Primary Key, TypeID as Foreign Key to next table Type, and EntityDate as type DATETIME.

Table "Type": This has TypeID as BIGINT Primary Key.

Table "TypeAvailable": This has TypeAvailID as BIGINT PrimaryKey, TypeID as Foreign Key to Type, Week as TINYINT, and Day as TINYINT.

Each table has other columns in them too, but none which relate to my problem at hand.

How it works:

Each person chooses Entities which are available to them on particular days. The Entity is chosen by the user, and has a particular Type. Their availability is determined by the following:

Say a cycle is 5 weeks long for a particular location. You want to offer entities of a new type (let's call it TypeA) on every day on the first week of each cycle, and on Wednesday on every third week of each cycle. TypeA would be added with, let's say, TypeID 1 to table Type.

TypeA is then also created in TypeAvailable twice. One entry, TypeAvailID 1, has Week set to 1, and Day set to 0 (zero denoting that it's available every day that week). The second entry, TypeAvailID 2, has Week set to 3, and Day set to 3.

Lets say you wanted another type, TypeB, and you wanted this type available on every Monday of every week of the cycle. It would have TypeID 2. Its corresponding TypeAvailable  (with TypeAvailID 3) has a Day set to 1, and Week set to 0 (again, denoting that it's available on every week of the cycle).

The user is looking at a particular day, and is presented with Types which are available to be picked on that day of the cycle. When they select one, it's added as an Entity, with the TypeID of the Type they chose and the EntityDate for the date they chose it on, to the Entity table.

The Problem:

At some point, junk data got introduced to the system. Specifically, some users now have Entities booked in their name on dates that the Type isn't actually set to be available.

For one example, a random user might have an Entity booked in their name for for an EntityDate which corresponds to week 2 of the cycle, on a Friday. However, the Entity is TypeA, which is only available all week on Week 1, and Wednesday on Week 3. I.e. it shouldn't be there in the Entity table, on week 2 of the cycle, on a Friday, at all.

I need to find the IDs of all Entities which are booked in for invalid dates.

I've been at this problem for a little while now and as of yet haven't cracked the problem (as mentioned, I think I'm a bit burnt out at the moment, which probably isn't helping, either). Any help?

I know that "Restore From Backup" is usually an ideal solution, but the issue is that this is a problem which has been building up for many months now, and the business only recently began to notice the issue. As such, it's not really a good option as a solution to this problem, so please don't offer that up as an answer. Thank you :)


texts: Addtional configuration when deploying Data Tier Applications?

texts: I'm starting a few new database projects and I'm attempting to create them at Data Tier Applications. There are two items I'm not able to find documentation for. I would like to set the db owner to SA and set the initial filesize and growth rate. Even if those items are outside the scope of the app, I would expect that there would some way to specify that at publish time, either in SSDT or SSMS. I can find no documentation either way. Is this the case?


texts: Recover parameters for stored procedure from system logs

texts:
I'm trying to debug an app, and I'd like to rule out database errors before tackling the code.

I know which stored procedure is responsible to handling the data I'm looking at, but there is no logging nor any traces set up on this DB. Is there a way to recover the parameters passed to that procedure, using existing system logs?

I've looked at:
dm_exec_procedure_stats and fn_dblog but they don't seem to have what I need.

I'm setting up logging moving forward.


texts: Total_worker_time over SQL 2008 linked server

texts:
I’ve come across a lengthy SP that is being recorded as generating a high amount of total_worker_time in sys.dm_exec_procedure_stats. (But I think it might be a red-herring)

The SP contains cursors and distributed updates over linked MS SQL Servers.

When a query takes place over a linked server, does the local server record Elapsed time in place of CPU time, or does the remote server return the actual CPU time to the local server?


texts: purchasing item by client - how to ensure that user has enough funds?

texts:
I have to tables:

users (id serial, coins integer not null default 0)
purchases (id serial, price integer not null, user_id)

When new row is inserted into purchases table I want to subtract amount from user.coins column and ensure that user.coins >= 0 (so user had enough founds to make a purchase).

I want to do it in AFTER INSERT trigger:

UPDATE users SET coins = coins - NEW.price WHERE id = NEW.user_id;
RETURN NULL;

My question is how to ensure that user won't spend more coins than he posses? My first idea is to just make CHECK constraint on users table like this CHECK (coins >= 0) - will that work with multiple concurrent inserts on users table?


texts:
I'm working on a query in Access 2010 and need help.

If the value is =0 I want it to show as blank,
if the value is <500 I want it to show as "B",
if the value is >500 I want it to show as "R"


texts: Optimal placement of tempdb, mdf and ldf files in SQL Server 2012 on SSD's?

texts:
I realize this is probably a very open ended question and the answers could vary, but what is the optimal placement for the tempdb, mdf and ldf files in SQL Server 2012 when talking SSD's?

Pre-new-purchase, I had an existing SSD with the SQL Server 2012 core files and tempdb installed on and had both the mdf/ldf on a 7200rpm HDD. I then bought 2 SSD's with the original intention of putting mdf on one and ldf on the other.

But, from reading into it more, separate physical disks for mdf and ldf files don't really apply when it comes to SSD's. Correct?

So, I was thinking of the following:

SSD 1 - SQL Server 2012 Core Files and Windows
SSD 2 - tempdb
SSD 3 - mdf and ldf

If it makes a difference, this will be dedicated to just one database so there won't be any contention between multiple databases.

Is my "thinking" setup good or just simply a waste (i.e. no reason to separate out tempdb) where I now have an extra SSD to make use of elsewhere?


texts: InnoDB "holes" in large table 200 Million Rows?

texts:
I have a large MySql InnoDB table with about 200 million rows.  One of the columns has some data in it that I really don't need any more, so I'm thinking about removing that column, or setting all the cells in that column to NULL.

This column contains a text value of about 300 characters, so by removing it I'll save about 300 bytes x 200 million rows = 60GB.

But, will this leave a lot of "holes" in my data?  Can MySql effectively and efficiently re-use this space without some sort of de-fragmentation?  Or does that just not matter since it's InnoDB and on an SSD drive?

Thanks!

Update:  The table has about columns total, but most of them are INT values.  This text field is by far the biggest.  Each row only takes up about 4KB of space total.


texts: Select query with custom column and value of other column

texts:
I want to have a query that have a custom column and have some value of other column

here is example of a table

table 1 : id, name, emp_id

I want to have input like this

id | name | emp_id | proccess    |
1  | John | 002    | Work of John|

The process is just costum column I want to do it in Select query..

This is what I tried:

Select id, name, emp_id, "Work of "+name as process, from table1

But its not working. I also want to add 2 or more column value in custom column.

Thanks


texts: MySQL merge two tables of different database to single database

texts:
Friends, i have database named as DB1 with a table called TB1A and another database as DB2 with  a table TB2A.
TB1A having 5 columns and TB2A  having 8 columns, All the columns available in TB1A is present in TB2A.
I want to create new table called TB3A which should be a merged result of a TB1A and TB2A. Means TB3A will have 8 columns and contains all the datas of TB1A and TB2A.
CREATE TABLE TB3A AS (

SELECT *
  FROM DB1.TB1A
  JOIN  DB2.TB2A
)

While i trying like above it is showing an error like below

Error Code: 1060
Duplicate column name 'Agreement_Id'
Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

Does any one have any idea about this. I have google it this issue but i am not able to find the solution for this ..


texts: How to migrate windows login in from one sql server to another sql server on another domain

texts: We have a SQL server that is located in domain 1(DMN1). We are migrating databases in domain 1 to our new SQL Server in domain 2(DMN2).Corporate states that there will be no trust established between each domains. So whatever logins the database has in DMN1 cannot be used in DMN2 anymore. it would take a lot of time to sort out a lot of windows logins in DMN1, get their equivalent login in DMN2 and map them to the right databases. What would be an easier way of doing it? Is it possible to just copy the data in each databases without the security part, and just rebuild the security on each database manually? Can anyone share a non time consuming way of accomplishing this?


texts: MongoDB/MySQL - auto increment vs object ID

texts:
I am in the process of evaluating MongoDB and writing some basic PHP applications around it. Typically in my PHP/MySQL applications, I would create an ID field and set it to auto-increment on each row insert. I would then use this ID to relate to/join tables together.

However in MongoDB, there is no auto-increment (unless you create function to do it, I believe), only an objectID. Because of this, i'm unsure of how to relate collections together.

Is it worth writing an auto-increment function (as explained here: http://docs.mongodb.org/manual/tutorial/create-an-auto-incrementing-field/), or is it common practice to store the ObjectID?

An example would be the following MySQL query:

SELECT * FROM cars WHERE car_type = '2'

But in MongoDB....

db.cars.find( { car_type : 507c35dd8fada716c89d0013 } );

Is the above standard practice? Am I using Mongo incorrectly by creating an auto-increment field?

Darius

EDIT: I think I am so deeply rooted in MySQL that I can't imagine an application without auto-incrementing IDs. Is it safe to store objectIDs (just as you would store a MySQL AI ID)? Is this bad for readability, since you would have to look up that objectID just to see what it relates to?