texts:
I've queried SHOW processlist as root after a server restert, and got a long list of Sleep threads:

+-----+-------------+-----------------+---------+---------+------+-------+------------------+
| Id  | User        | Host            | db      | Command | Time | State | Info             |
+-----+-------------+-----------------+---------+---------+------+-------+------------------+
| 217 | root        | localhost       | masters | Sleep   |  371 |       | NULL             |
| 251 | mastersuser | localhost:50883 | masters | Sleep   |  297 |       | NULL             |
| 252 | mastersuser | localhost:50884 | masters | Sleep   |  297 |       | NULL             |
| 253 | mastersuser | localhost:50885 | masters | Sleep   |  297 |       | NULL             |
| 254 | mastersuser | localhost:50891 | masters | Sleep   |  296 |       | NULL             |
| 261 | mastersuser | localhost:50897 | masters | Sleep   |  281 |       | NULL             |
| 260 | mastersuser | localhost:50896 | masters | Sleep   |  281 |       | NULL             |
| 262 | mastersuser | localhost:50899 | masters | Sleep   |  281 |       | NULL             |
| 263 | mastersuser | localhost:50900 | masters | Sleep   |  280 |       | NULL             |
| 272 | mastersuser | localhost:50908 | masters | Sleep   |  263 |       | NULL             |
| 273 | mastersuser | localhost:50909 | masters | Sleep   |  263 |       | NULL             |
| 274 | mastersuser | localhost:50911 | masters | Sleep   |  263 |       | NULL             |
| 275 | mastersuser | localhost:50912 | masters | Sleep   |  263 |       | NULL             |
| 287 | mastersuser | localhost:52434 | masters | Sleep   |  242 |       | NULL             |
| 288 | mastersuser | localhost:52435 | masters | Sleep   |  242 |       | NULL             |
| 289 | mastersuser | localhost:52436 | masters | Sleep   |  242 |       | NULL             |
| 290 | mastersuser | localhost:52437 | masters | Sleep   |  242 |       | NULL             |
| 296 | mastersuser | localhost:52445 | masters | Sleep   |  233 |       | NULL             |
| 298 | mastersuser | localhost:52446 | masters | Sleep   |  232 |       | NULL             |
| 299 | mastersuser | localhost:52447 | masters | Sleep   |  232 |       | NULL             |
| 300 | mastersuser | localhost:52448 | masters | Sleep   |  232 |       | NULL             |
| 301 | mastersuser | localhost:52449 | masters | Sleep   |  231 |       | NULL             |
| 308 | mastersuser | localhost:52453 | masters | Sleep   |  219 |       | NULL             |
| 315 | mastersuser | localhost:52457 | masters | Sleep   |  210 |       | NULL             |
| 316 | mastersuser | localhost:52458 | masters | Sleep   |  210 |       | NULL             |
| 317 | mastersuser | localhost:52459 | masters | Sleep   |  210 |       | NULL             |
| 319 | mastersuser | localhost:52460 | masters | Sleep   |  210 |       | NULL             |
| 330 | mastersuser | localhost:52468 | masters | Sleep   |  196 |       | NULL             |
| 331 | mastersuser | localhost:52469 | masters | Sleep   |  196 |       | NULL             |
...
| 436 | mastersuser | localhost:52547 | masters | Sleep   |   47 |       | NULL             |
| 468 | root        | localhost       | masters | Query   |    0 | NULL  | SHOW processlist |
+-----+-------------+-----------------+---------+---------+------+-------+------------------+

After a while, the sleep processes are gone.

Should I regard this as a problem?


texts: How do I design a database for different sets of measurements on different subjects?

texts:
I'm conducting a research project that consists in sampling different physical quantities on about 30 buildings, most of them single-family houses.

Some of those physical quantities will be common to all houses: indoor temperature, outdoor temperature, etc. Others, however, will be measured only on some houses. For example, we will measure the pulsed air temperature only on those buildings that are properly equipped with an air-handling unit. Such values make no sense for other buildings.

So we will have perhaps 50 different possible physical quantities. A given building will have, say, not more than 15 of these quantities.

One possible solution I'm considering is the Entity-Attribute-Value model. I would have one table for floating-point data (temperatures, heating water flow, etc) and another table for categorical data (water pump speed: high, low, off, etc).

The only concern I have is that the Wikipedia article mentions that this model is suitable for a "vast" number of possible attributes. This is hardly my case.

Any advice/recommendation/book/article would be most welcome.


texts: Generate CSV Email For Each UserID In Table

texts:
Good day all, I have a unique requirement where I need to generate a .csv file containing data for each userID.  The result set that I have returned from my select statement is

  userID    numOfSales  saleDate    meetMark
  ec12    4   2017-01-02  No
  ec12    3   2017-01-03  No
  ss33    5   2017-01-10  No

So I would want to generate 2 .csv files one for ec12 and one for ss33 and email those files to an email address.

Below is my sample DDL and query to return the above, how can I do a for each in SQL Server to generate a .csv file for each userID in the above select statement?

Create Table #bluebell
(
    userID varchar(100)
    ,numOfSales int
    ,saleDate date
    ,meetMark varchar(10)
)

Insert Into #bluebell (userID, numOfSales, saleDate, meetMark) Values
('ec12', '22', '20170101', 'Yes'),('ec12', '4', '20170102', 'No'),('ec12', '3', '20170103', 'No')
,('er11', '30', '20170101', 'Yes'), ('er11', '22', '20170103', 'Yes'), ('er11', '33', '20170109', 'Yes')
,('ss33', '40', '20170101', 'Yes'), ('ss33', '5', '20170110', 'No')

Select * FROM #bluebell where meetMark = 'No'

DROP TABLE #bluebell


texts: Oracle regexp_like square brackets inside character set

texts:
This questions is in regards to Oracle, pl/sql, and the regexp_like function.

I am trying to build a character set that will match on all typical special characters. My character set currently looks like:

pattern := '[-~`!@#$%^&*\(\)\\{}_+=|''";:,./?]+';

I would like to add the square brackets to this character set, however, whatever I try to add ']' is not working. Here is a simple example that illustrates the problem:

select
  case when regexp_like('w]ord', '[\]]+') then 'true'
  else 'false' end
from dual;

This returns false, meaning it did not match the ']' character. Curiously, I can get the '[' character to match because this returns true:

select
  case when regexp_like('w[ord', '[\[]+') then 'true'
  else 'false' end
from dual;


texts: oracle 11g Grant on dbms_lock not persisting

texts:
I am using oewizard from swingbench to create an order-entry like schema on oracle 11g. When I run the wizard I get the following errors:

ORA-01031: Cannot grant execute on dbms_lock to soe: Insufficient privilege
ORA-01031: Cannot GRANT SELECT ON SYS.V_$PARAMETER to SOE: Insufficient privilege

Now I am using the SYSTEM user as the DBA to log into the account from oewizard. Now my first question:

Does the SYSTEMuser have SYS_DBAprivilege from the beginning

I explicitly grant DBA privilege to SYSTEM from sqlplus (GRANT DBA to SYSTEM; Grant Succeeded) but while connecting via SYSTEM (the same user) through JDBC (oewizard's back end), I still get the same error. So my next question is:

When logged in from sqlplus (as SYSDBA), don't the grants assigned to a given user persist even after we exit sqlplus?
If so what would be the best way to assign GRANTs to a given user so that they persist?
Is there no way we can log in as SYSDBA via JDBC since when I tried it, it didn't allow me

Keen to hear from you


texts: How to reuse index space that was not created

texts: My database is 34Gb in size and I created a clustered index which I believe was not successfully created due to some error and I can't find the index anywhere. During running of creation of Index, i was monitoring the hard disk size and it was decreasing while the creation of index is running. It took 10 gb space. Now, after the error in creation of index, the 10 gb space did not return. How can I get back this 10 gb of space? Thank you!!


texts: Is it safe to run a Windows Failover Cluster Public and Heartbeat on a Single NIC?

texts:
I have a 2-Node Windows Failover Cluster (Windows Server 2016, SQL Server 2017) with a Quorum Disk Witness on fibre-connected shared storage.

How important is it that the heartbeat is on its own network with this Quorum setup and why?

Am I right in thinking that if the network goes down, the Quorum Disk Witness will stop the nodes trying to failover, as it will still vote that both nodes are online, even though the nodes themselves are saying the other is offline?

In this scenario I can't see what benefit there is to having the heartbeat on its own network. I completely get that teaming two NIC's gives resiliency, but separating the heartbeat (as far as I can see) doesn't offer much with this quorum setup.


texts: Validate database backup For Innodb tables

texts:
Every fortnight, I take MySQL database dump from slave and then perform following steps as backup validation.

Number of Tables in every database.
Validate Schema of tables.
Validate random records for important tables.
An approximate(as its a backup) count of records in the table (only for MyISAM)

I am able to complete the activity. However since most of the tables are in InnoDB, I am not able to validate count properly. What solution can be there as count(*) is too slow in innodb?


texts: ER Diagram | Group, Post, User relationship

texts:
I need to design an ER diagram, but I am confused in a section, more specifically how to relate the post with the groups, because I need that post contains the information of in which group I publish and that user I publish it, I have two diagrams one where the relation of post, group and user are direct, and other where the post is related by means of the table generated of the relation many to many between group and user. My question is which of these two is more recommended.
Diagram A

Diagram B


texts: SQL Server 2016 replication between Windows 10 and Windows Server 2012

texts:
I've been asked to set up SQL Server 2016 replication between a development PC running Windows 10 Home and Windows Server 2012 R2. Both run developer versions of SQL Server 2016.

I have found this tutorial: Replication Tutorials SQL Server 2016 and later

This is when I realised that the Windows 10  Home edition does not allow you to create extra users and groups, and in order to do so I would need to spend another £120 to upgrade to Windows 10 Pro.

I've never set up server replication at all, my question is:

Is it possible to setup replication between those operating systems and SQL Servers Dev Editions?

I thought I ask before spending the money just in case it is not possible or I will hit ridiculous amount of problems doing it.

I tried finding anything on that online, to no avail. The Editions and Supported Features for SQL Server 2016 do not mention SQL Server 2016 Developer Edition and the only version from that list that supports Windows 10 is SQL Server 2016 Standard.


texts: Enforce known functional dependency in a group by clause

texts:
Let's consider I have the following tables:

    CREATE TABLE users(
        id serial PRIMARY KEY,
        age integer
    )

    CREATE TABLE products(
        id serial PRIMARY KEY,
        sku character varying(255),
        user_id integer REFERENCES users
    )

So basically a product would belong to a user and a user could have many products.

I want the ability to load users with their products doing the aggregation at the database level (Maybe wrongly but I assume it would be easier and probably more efficient than running some aggregation code in my application layer: Postgres comes with nice aggregation functions, why not using them !)

So I would run something like

SELECT "users".*, json_agg("products".*) as "products"
  FROM "users" LEFT JOIN "products" ON "users"."id" = "products"."user_id"
  GROUP BY "users"."id"

All good, my driver can parse JSON structures and I have nothing to do on the application layer side.

The problem rises if I want to introduce some sort of pagination:

WITH "users" AS (SELECT * FROM "users" ORDER BY "id" LIMIT 20)
SELECT "users".*, json_agg("products".*) as "products"
FROM "users" LEFT JOIN "products" ON "users"."id" = "products"."user_id"
GROUP BY "users"."id"

I have the known error

  ERROR: column "users.age" must appear in the GROUP BY clause or be used in an aggregate function

As the "users"."id" is not considered as the primary key of the temporary table created by my subquery

I could fix it by adding every users column in the group by clause. But I find it troublesome and to be a shame as I am sure "users"."id" will define in a unique way an item of my subquery.

So, I would like to know if there is a way to tell the database engine "users"."id" is some sort of primary key for my subquery ?

If not, do you see a better way ?

EDIT: This question is quite similar (3 years old though)

Thanks


texts:
I need to install SQL Server MSDE 2000 SP4 on Windows 10.

I have other machines that run Windows 7 and 8.1 and SQL Server MSDE 2000 works fine (with merge replication configured too).

Now I testing the same scenario with Windows 10, but setup is closing unexpectedly during installation.

I know that this version is very old, but is there a workaround to install it on Windows 10?


texts: Linking Fields From 2 Different Tables (Microsoft Access)

texts:
I am trying to link 2 fields from 2 different tables on Access but I will give an example to try and make the issue clearer.

I have 1 table called "Employee Details" which contains the field 'Employee Names' and 'EmployeesID'

The 2nd table is called Schedules" which contains the field 'Employee Names' and'EmployeesID'

The Employees ID is set to a primary key for table 1 but I do not know how to link/create a relationship between the 2 tables so that in the 2nd table the primary key remains the same.

Overall I want to be able to link the 2 fields so that the ID number remains the same throughout the tables.

Please may I be advised of how to solve this issue.

Thank you.


texts: Modeling a db that has products: What are the good practices/solutions? How to treat grammar variations in the words?

texts:
The problem is:

A Company belongs to a sector.
A Company produces products.
A Sector contains products.
A Product belongs to sectors.

Three entities: Company, Sector and Product. And it's a ternary relationship, right?

The dilemma is:

Possibility 1

I follow the above modeling. But I don't know how much hassle I'll get in the implementation and in the day-to-day use of the database. I'm saying that because the user could register the product "chair", but also "chairs", but also "wood chairs", etc...

How would I treat that singular-plural-adjective-grammar-etc trouble, under that modeling?

How would I guarantee to the user that no matter how he types "chair" (singular, plural, with or without adjectives), he will get all the results that the database can provide?

Possibility 2

I do not follow the modeling. I skip the Product entity, and just make an attribute "products" in the parent entity, which is Company. That attribute would be a big string containing all the products.

Example: The company 'Wood Corporation' has the attribute "products" equal to "chair chairs woods wood metal metals ..." (space-separated values). It looks a very ugly solution and very prone to bad data input by the user.

What do you think? What are the common practices/solutions? How to treat the grammar variations?

Maybe I'm just looking too far forward (tables, implementation, use), because I'm just in the modeling phase. Yet, those concerns keep arising, I can't avoid it and it bites my neurons.


texts: Query To Return Values Where NOT All Text

texts:
I have a field that is varchar(100) - if the field is all text, I want to ignore it, if it is alpha-numeric I want to return the data.  This is sample DDL and my ISNUMERIC() excludes both values (I assume because they contain text with the numbers).

How should this be altered in order to return any value that is not just text?

Declare @Table Table (field1 varchar(100))
Insert Into @Table Values
('1ZCLJKSDFLKSJDF'),
('0301190XFS3'),
('Pink Socks Run Free'),
('White Goats Stink')

Select * from @table where ISNUMERIC(field1) >= 1


texts:
I have two tables: MAINCOLOR and SUBCOLOR with the data as:

MAINCOLOR :
MAINID  COLOR
1       RED
2       BLUE
3       GREEN

SUBCOLOR:
SUBID MAINID SUBCOLOR STATUS
1     1      PINK     0
2     1      ORANGE   1
3     2      VIOLET   1

I need a result set as:

MAINID  COLOR   SUBCOLOR
1       RED     ORANGE
2       BLUE    VIOLET
3       GREEN

Resultset should be a join of MAINCOLOR and SUBCOLOR using MAINID. Need child data having status as 1.


texts: Difference between candidate key & primary key

texts: What is the differences between candidate key and primary key?


texts: 2 tables with foreign keys to 3rd table with primary key

texts:
This might be a silly question.
But can you have the following:

1 Table with Primary Key,
2 different tables each with a foreign key referencing the first table Primary key?

I know its possible to have 2 foreign keys in the same table, but the request I received was for the the below scenario:


texts:
There is a query that our BI team use. They can not execute the query because of the error below.

  Ad hoc access to ole db provider 'MSDASQL' has been denied. You must
  access this provider through a linked server.

I granted the users to sysadmin role and they could execute the query.

But, i don't want to give sysadmin role to them. I tried to give them bulkadmin role but it did not work.

How can i do it without sysadmin role? The query is below :

create table #temp
(username varchar(100),
    id varchar(100),
    ipaddress varchar(100),
    ipaddress2 varchar(100),
    time_todate varchar(100),
    username_new varchar(80),
    portnumber varchar(100))

insert  into #temp (username , id, ipaddress, ipaddress2 , time_todate,portnumber )

 select * from OPENROWSET('MSDASQL', 'DSN=PROXY',
 'SELECT username, id,ipaddress ,ipaddress2,time_todate,portnumber
    FROM session nolock
    where portnumber in (''4589ERC-13#228:154'')' )

 select * from #temp;


texts:
I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be:

Free
Works under Linux
Simple to use and not buggy
You actually tried and confirm that works
Preferably, not written with Java or Ruby

I have tried different tools listed here, but none worked for me.

Thank you in advance.


texts: pg_dump not recognized, even though I've confirmed the binary is present

texts:
I'm trying to do a dump of a table in PostgreSQL via pgAdmin3. When I try to run the the following command:

pg_dump -t schema.table_name database_name > chosen_save_name.sql

it errors out and just points to the first character.

I've gone to File -> Options -> Binary Paths -> PG bin path and verified that the pg_dump is in there (listed as pg_dump.exe).

What am I doing wrong here?


texts:
I am using psql to query a large database. and (understandably) many queries that I type in take a long time.  I typically want to cancel these queries and so I enter ^C, and psql responds with the message

Cancel request sent

This cancel request sometimes cancels queries quickly (milliseconds), but sometimes takes very long times (>10 minutes) to cancel the query and display a prompt where I can type in new commands.  These long cancel times even happen on queries that have only been running less than a second.

Why does psql take so long to cancel some queries?  And what can I do to make psql cancel the queries faster?

Version info:

$ psql --version
psql (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)


texts: Using `SELECT` as `RETURN` Argument in Function?

texts:
Using Oracle, I attempted to write a function that demonstrates how to return the result of a SELECT ... query:

 CREATE OR REPLACE FUNCTION f(v1 number) RETURN number IS
     BEGIN
      IF TRUE THEN
         RETURN (select 42 from dual);
       ELSE
         RETURN v1;
       END IF;
     END;

But it does not compile.

How can I fix the above IF's RETURN to return 42, i.e. the result of select 42 from DUAL?


texts: PostgreSQL concurrent increment of counter

texts:
I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item is instanced I need to increment usage of the specific item.

My first implementation is:

statistics(
  id      integer NOT NULL,
  name    character varying(255) NOT NULL,
  usage   integer NOT NULL DEFAULT 0,
);

UPDATE statistics
  SET usage = usage + 1
WHERE name = '<name>';

My concerns are about performance and concurrency. The update process will be instantiated by several tens (maybe 80-120) devices and could occur several times per second, so my questions are:

1) will this method preserve concurrency? (ie if more than one devices request the update "at same time", will every request be counted?)

2) can you suggest a best way to achieve the result? I expect to have load in writing the updates, while the reads would me much more frequent. Does it exist a specific function to increment values? I'm looking at "sequence" but I'm not sure if that is the right way...

Thank you a lot in advance for any advice


texts:
I have three tables: User, Role and Project
User Table
User_ID|Email             |Name     |
-------|------------------|---------|
      1|bobtest@gmail.com |Bob      |
      2|marytest@gmail.com|Mary     |
      3|samtest@gmail.com |Sam      |

Role Table
User_Role_ID|Role_Name          |
------------|-------------------|
           1|project_admin      |
           2|project_manager    |
           3|project_leader     |
           4|project_participant|

Project Table
Project_Id|Project_Name|
----------|------------|
         1|    Project1|
         2|    Project2|
         3|    Project3|

I have created a User_Role many to many table:
User_Role Table
User_Id|User_Role_ID|
-------|------------|
      1|           1|
      1|           3|
      2|           3|
      2|           1|
      1|           2|
      3|           3|

How can I design the database to show the relationship between user, role and project with the below scenario? One participant can be assigned to multiple projects.
Scenario1: If each project must have all the same roles, one project_admin, one project_manager, one project_leader and multiple project_participant, how should I design database?
What I though is to create a Project table as:
Project_Id|Project_Name|Project_admin|Project_manager|Project_leader|
----------|------------|-------------|---------------|--------------|
         1|    Project1|            1|              2|             1|
         2|    Project2|            2|              3|             3|
         3|    Project3|            3|              4|             1|

All Project_admin, Project_manager and Project_leader will be the foreign key of User table and one participant table for the project and those users with participant role, participant_id will be the PK and Project_ID and User_ID are the FK of project table and user table.
Participant_Id|Project_Id|User_Id|
--------------|----------|-------|
             1|  Project1|      1|
             2|  Project1|      2|
             3|  Project1|      3|
             4|  Project2|      2|
             5|  Project3|      3|
             6|  Project3|      4|

I'm not sure if this is fine as if I have a new role, I need to add a new column on the project table.
Scenario2: If each project can have different roles, for example, project1 can have one project_admin and many project_participant, project2 has one project_admin, one project_leader and many project_participant and project3 has one project_admin, one project_manager and many project_participant ,how should I design database?


texts: MySQL: is it more efficient to query for duplicate row or insert(update) on duplicate?

texts:
Just curious on resource efficiency.  If I have a list of inserts that need to be accomplished repeatedly which may contain duplicates (there is no way around this, the API I have to work with sucks), is it better for me to query for the primary, and only insert if it doesn't exist (ie. 2 queries), or just insert anyway with update on duplicate syntax?

Thanks!


texts: Commands like CREATE, GRANT, DROP always return "0 rows affected"

texts:
I was considering why some special commands in mysql like create user, grant privileges, drop user when correct passed return "0 rows affected", which is not true! (rows in mysql db are affected indeed)

I know there are not standard queries, but if we do delete from user where ... on mysql database, we will see N rows affected.

so it's countable in rather easy way.
otherwise response "Query OK (0.00 sec)" will be much accurate.

or am I missing sth?
I verify above with a few mysql version 5.1-5.5.


texts: Manual recovery of dropped collection in mongodb

texts:
Some collections were accidentally dropped from mongodb. Although the collections do not exist, in the dbpath there are 6GB of dbname.0, dbname.1,... and dbname.ns

We tried to run mongod --repair but instead of recovering the files it deleted them.

Is there a way to recover these data? maybe partially?

We are using standalone mongodb.

Thanks


texts: stored procedure with multiple statements does not execute all statements when run from job

texts:
** see updates below

I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the procedure from the command line, it works as intended, so I put it in a job to run every morning. When the procedure is run from the job, no errors arise, the job completes "successfully" in a fraction of the time I would expect, but no records have been inserted into the table. The delete works, but nothing gets inserted.

In trying to figure what is happening, I added some print messages on either side of each statement, "delete started", "delete ended", etc - and all of the messages show up in the job log - so the code is stepping through, it's just not inserting any records into the table.

My first inclination was it was some kind of permissions problem, but the delete statement works fine, and I have other jobs that are configured with the same owner that insert with no problem. I double checked the "mirror" schema, and the owner listed on the job has insert, update, delete, select privileges all enabled. (Still might be a permissions problem, I haven't done much configuration on that front, insights welcome.)

The actual statement is quite long (though uncomplicated), so here's a shortened version of what it's doing with identical syntax. I actually don't think the problem is with the code, as when executed from the command line, it works fine, but here it is anyway - maybe it will prompt just the right Q/A that I need.

Any ideas as to where to look would be appreciated:

**** 2014-03-27 a few updates: it occurred to me to try removing the delete part and just have an insert, just to isolate the problem. Same result, insert didn't happen, job succeeded. So I guess it's not about having more than one statement, it's the insert that is not working, flat out.

Also tried changing owner to myself as well as another user, but those resulted in failed job due to

"Unable to determine if the owner (PRELIANT\reports) of job CRM Reporting Dataset Updates has server access"

For reference, here is the exact message from the job history message window when the job "succeeds":
Date        3/27/2014 7:33:11 AM
Log     Job History (CRM Reporting Dataset Updates)

Step ID     2
Server      ULYSSES
Job Name        CRM Reporting Dataset Updates
Step Name       tblPhoneCall Insert
Duration        00:00:54
Sql Severity    0
Sql Message ID  0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted   0

Message
Executed as user: PRELIANT\reporting. begin insert [SQLSTATE 01000] (Message 0)  finish insert [SQLSTATE 01000] (Message 0).  The step succeeded.

/* remove all records from mirror that are not 'complete' (complete records are read only and will not change) */
    delete MyDB.mirror.tblPhoneCall
    where statecode <> 1 ;

/* remove records from mirror that were deleted from CRM */
    delete tbl
    from
        MyDB.mirror.tblPhoneCall tbl left join
        AnotherDB.dbo.PhoneCall vw on vw.ActivityId = tbl.activityid
    where
        vw.ActivityId is null;

/* insert all records from CRM where there is no match on mirror table, which now includes new records, or records that were not previously completed */
insert MyDB.mirror.tblPhoneCall
(
    activityid
    , field1
    , field2
)
select
    vw.activityid
    , vw.field1
    , vw.field2
from
    AnotherDB.dbo.FilteredPhoneCall vw left join
    MyDB.mirror.tblPhoneCall tbl on tbl.activityid = vw.activityid

where
    tbl.activityid is null;


texts: Clean Code - On which side to place the key

texts:
I am currently modelling a database schema. In it, addresses are used several times, which follow a fixed structure. In our previous systems, the address relation was modelled in such a way that the address contained foreign keys, e.g. those of a user.
Reasoning: In this way, an address can only be attached to one parent. From my point of view, however, this is an absolute chaos because sometimes up to eight foreign keys exist in the address relation, whereby care must be taken that only one is filled.
Wouldn't it make much more sense to place the key on the parent, so that by means of a not-zero constraint it can be ensured that each parent has an address and the address relation is clean? The parent is also rarely/never navigated to from the address side.
Or am I violating any conventions by doing this? In such a case, doesn't cleanliness weigh more heavily than a "convention"? If desired, one could also model the previous requirements additionally using triggers?
Current approach: Constraint to ensure only one FK is set on address.

My approach: address_id as FK on parents.


texts: PostgreSQL relation size does not sum up

texts:
I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow:

=> SELECT pg_size_pretty(pg_total_relation_size('data'));
157 GB

This seemed a bit large, so I tried to sum up the data:

=> SELECT pg_size_pretty(pg_relation_size('data'));
19 GB

With the indices:

SELECT pg_size_pretty(pg_relation_size('data_pkey'));
757 MB
SELECT pg_size_pretty(pg_relation_size('data_file_end_date_idx'));
766 MB
SELECT pg_size_pretty(pg_relation_size('data_file_end_date_idx'));
766 MB
SELECT pg_size_pretty(pg_relation_size('data_merged_idx'));
854 MB
SELECT pg_size_pretty(pg_relation_size('data_owner_idx'));
794 MB
SELECT pg_size_pretty(pg_relation_size('data_session_format_idx'));
779 MB

The summation of the data and indices size is around 26 GB, but the total relation size is near 160 GB. The table was restored from dump just a while ago, and no writes were made since.

What's the explanation for this difference?
Any way to reduce wasted disk space? Will clustering help?


texts: Difference between host '*' and host '%'


texts:
RTO is 8 hours
RPO is 24 hours
Retention Period: 5 days
Based on above values I have plan to configure below backup stratgey for Production. Please suggest if there is any changes required in below backup policy.
Full Backup - Daily
Log Backup - every 30 minutes (this is just to prevent log grwoth/low disk problems during index rebuild job)
SQL DB size is approx 500-650 GB.
HA/DR solution for above DB: Configured Distributed Always-ON AG from DC1 to DC2 on top of WSFC (DC1- two DB serevrs and DC2-two DB servers)


texts: Select only those rows based on specific condition of user id


texts: MongoDB generated ObjectID for arrays is descending instead of ascending

texts:
I have the following document in a collection in my MongoDB database:

"_id":"5e2088563824e72ea5ee7095",
"image":"xxx",
"questions":[
    {
       "_id":"5e2088563824e72ea5ee70c3",
       "text":"test test test",
    },
    {
       "_id":"5e2088563824e72ea5ee70be",
       "text":"what is your age 1?",
    },
    {
      "_id":"5e2088563824e72ea5ee70b9",
      "text":"what is your gender 1?",
    },
    {
      "_id":"5e2088563824e72ea5ee70b4",
      "text":"what is your blood type 1?",
    },
    {
      "_id":"5e2088563824e72ea5ee70af",
      "text":"what is your blood type 1?",
    }
],
"__v":0

Without doing any type of sorting on the questions array, the document would always return like this which is the natural ascending order of the data entered.

What is strange is that the generated IDs are in reversed order as you can see:

key 0 => 5e2088563824e72ea5ee70c3
key 1 => 5e2088563824e72ea5ee70be
key 2 => 5e2088563824e72ea5ee70b9
key 3 => 5e2088563824e72ea5ee70b4
key 4 => 5e2088563824e72ea5ee70af

I expected the generated ID to match with the key of the array (ascending) so, it should be like:

key 0 => 5e2088563824e72ea5ee7096
key 1 => 5e2088563824e72ea5ee709b
key 2 => 5e2088563824e72ea5ee70a0
key 3 => 5e2088563824e72ea5ee70a5
key 4 => 5e2088563824e72ea5ee70aa

However, behaviour of the generated document IDs is normal like the auto-increment of MySQL (ascending). It's just the nested arrays that have the reversed order.

Is this a bug or is it the normal behavior? Why is it designed like this if this is normal?


texts: Why does my transaction log use so much space?

texts:
I have a staging table with 20 million rows that uses about 8GB of storage. After populating foreign keys and sanitizing data I need to move this data into a production table (which does not need all the source data used to key and normalize).

The production table ends up using about 6GB of storage. My choice was to use a straightforward method and simply run one large insert/select statement. When I start the transaction I am the only user in the database, and the transaction log is nearly empty.

The max transaction log size was set to 30 GB, which I thought would be adequate. Turns out the transaction needed 59GB of transaction log to complete. The production table has a clustered PK, a handful of nonclustered indexes, and some foreign key constraints that I did not bother removing.

Can anyone explain why SQL Server requires 59GB of transaction log space to copy 6 GB of data from one table to another in the same database?


texts: How to format the output of this query into year...second?

texts:
I have this query that given @secs a number of seconds, it returns a table with year,months,weeks,days,hours,minutes and seconds.

take for example:

declare @secs int
select @secs = 19475298

                                SELECT      FLOOR ( UpTime / 31207680 ) AS YEARS
                                        ,   FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680  )  * 12 ) AS MONTHS
                                        ,   FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 )  * 4.3 ) AS WEEKS
                                        ,   FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 ) AS DAYS
                                        ,   FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 ) AS HOURS
                                        ,   FLOOR( ( UpTime / 60 )   - FLOOR( UpTime / 3600 ) * 60 ) AS MINUTES
                                        ,   UpTime - FLOOR( UpTime / 60 ) * 60 AS SECONDS

                                FROM        (

                                                   select @secs as Uptime

                                             ) AS RadheX

this gives me:

how can I have all these columns in a single string with 2 digits for each column?

on the example above the result would be
00:07:01:01:09:48:18


texts: Practical way to temporarily shut down a database

texts:
What would be the most practical way to shut down a database to access for any user?

I need to practically shut down a specific database on a SQL Server for test purposes, and would like to know the most practical common solution.

Thanx


texts: Update a view with daily tables in Postgres

texts:
I have a view like this:

CREATE OR REPLACE VIEW some_table AS
select * from h20180101
union all
select * from h20180102
union all
select * from h20180103
etc...

It's a union daily tables. The thing is each day a new table is created. Is there a way in PL/PgSQL to automatically update the script daily to make a union to the table of the previous day?

Thanks!


texts: performance of multi field primary key or 'contrived' 'semi artificial' key

texts:
This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can easily be found and decisions made by whoever would like to search for them.

This question is more about performance of the keys (or lack thereof)

I work as a DB manager, and when I create my tables I attempt to use a 'natural' key for the table. Often times this comes out as being a set of 2,3, or sometimes 4 fields that act as the primary key for the given table. More often than not these fields are Varchar in nature, but short (10 or 15 characters in length at most). Personally I try to keep them shorter!

My question is this.

Imagine I have a table that holds demographic data.
The only way I can ensure that I have uniqueness in each row is to use the fields for
FirstName FamilyName DateOfBirth PlaceOfBirth

(You may wonder why I include 'place of birth', I am aware of another individual (who used to live nearby - same phone number, different dialing code) with whom I shared all these details (I assume that the PlaceOfBirth was different, but I guess I could have used MothersMaidenName ;) )

so now I have an interesting problem.

I could use a much shorter field that is created from concatenating the info in the 4 main fields
example: DateOfBirth first 2 characters of FirstName first 2 characters of FamilyName first 2 character of PlaceOfBirth

My question is this.

At what point would the concatenation of field provide a performance improvement over using the fields directly, ie how many columns.

I know from searching that most DBMS's have a 'thoretical maximum size limit' dependent on the B-Tree that is created. I am assuming that I don't hit this limit in terms of the length/size of the primary key.

My reason for considering using this type of 'contrived' key is:
The information in the concatenated column is most likely sufficient to be able to identify the record without the need to extract all the primary key fields (would this be better for performance or no different compared to using all the 4 primary key fields?)

This is obviously a fairly 'theoretical' question, but I have considered doing this concatenation on a table that end up with 4 varchar fields, and it was obvious that uniqueness would be described by using just a shortened version. Obviously there is an effort to create this field in the first place, but in others opions would this effort be worthwhile, and at what point would it become more interesting.

I have searched for this but I have never found this question asked directly, it allays comes out as a 'natural' or 'artificial' primary key discussion.

Of course if this feels like a 'natural' or 'artificial' key discussion feel free to say so. My feeling is that this 'contrived' key would proffer the advantages of a both. Has anyone used this idea in a real world solution?

Thanks in advance for your thoughts.

David

Edit.
I just found this thread

https://stackoverflow.com/questions/3735390/best-primary-key-for-storing-urls

It seems to cover similar ground, I must admit I hadn't thought of 'hashing' my columns together (mainly because they are short by nature), but I do like the idea. I guess you could do that and hash the whole row !

Edit2.

I've returned to this question just to see if there has been any change to the answers, or extra comments. I've decided to accept a response, but would like to note that I have found all the responses helpfull in the terms of the discussion.


texts:
I'm having problems when loading a csv file into MySQL 5.7 with MySQL Workbench.

Example Data

ESH13   1/2/2013    10:04:35    1451    1
ESH13   1/2/2013    10:04:35    1451.25 2
ESH13   1/2/2013    10:04:35    1451    1
ESH13   1/2/2013    10:04:35    1451    1
ESH13   1/2/2013    10:04:36    1451    1
ESH13   1/2/2013    10:04:36    1451    2

Originally I tried using this [below] method to start:

SHOW VARIABLES LIKE "secure_file_priv";

CREATE TABLE es (
    id INT NOT NULL AUTO_INCREMENT,
    ticker VARCHAR(255) NOT NULL,
    date DATE NOT NULL,
    time DECIMAL(10 , 2 ) NULL,
    price DECIMAL(10 , 2 ) NULL,
    vol DECIMAL(10 , 2 ) NULL,
    PRIMARY KEY (id)
);

LOAD DATA INFILE 'C:\Users\YCM LLC\Desktop\HFT\ES_test.csv'
INTO TABLE es_test1
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This error occurs when the above is run:

  Error Code: 1290. The MySQL server is running with the
  --secure-file-priv option so it cannot execute this statement.

I tried to modify C:\ProgramData\MySQL\MySQL Server 5.7\my.ini by blocking the statement as shown below, but I get an "Access is denied" when I try to save the changes.

# Secure File Priv.
# secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

I then tried to use of "local" keyword in the LOAD DATA INFILE statement:

    LOAD DATA local INFILE 'C:\Users\YCM LLC\Desktop\HFT\ES_test.csv'
    INTO TABLE es_test1
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

This error occurs when the above is run:

  Error Code: 2. File 'C:UsersYCM LLCDesktopHFTES_test.csv' not found
  (Errcode: 2 - No such file or directory)

I was able to get data to load after getting adjusting per the advice from [a] the person that answered my question in a comment, but I started having trouble loading time string data to a table

CREATE TABLE es (
    ticker VARCHAR(255) NOT NULL,
    xdate DATE NOT NULL,
    time Time NULL,
    price DECIMAL(10 , 2 ) NULL,
    vol DECIMAL(10 , 2 ) NULL
   );

LOAD DATA local INFILE 'C:\\Users\\YCM LLC\\Desktop\\HFT\\ES_test.csv'
INTO TABLE es
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(ticker, @var1, @var2, price, vol)
SET xdate = STR_TO_DATE(@var1, '%m/%d/%Y'),
    time = STR_TO_DATE(@var2, '%hh:%mm:%ss');

This error occurs when the above is run:

  6 row(s) affected, 6 warning(s): 1411 Incorrect datetime value:
  '10:04:35' for function str_to_date 1411 Incorrect datetime value:
  '10:04:35' for function str_to_date 1411 Incorrect datetime value:
  '10:04:35' for function str_to_date 1411 Incorrect datetime value:
  '10:04:35' for function


texts: Display Postgres server output in terminal and record to logs at same time

texts:
I'm running Postgres 9.1 (Homebrew installation on Mac OSX) and I'd like to monitor my postgres server more closely.

My question relates to logs.  I'd like to get the logs displaying in a terminal pane.  Here's what the Postgres docs say about the logs:

"On Unix-like systems, by default, the server's standard output and standard error are sent to pg_ctl's standard output (not standard error). The standard output of pg_ctl should then be redirected to a file or piped to another process such as a log rotating program like rotatelogs; otherwise postgres will write its output to the controlling terminal (from the background) and will not leave the shell's process group. On Windows, by default the server's standard output and standard error are sent to the terminal. These default behaviors can be changed by using -l to append the server's output to a log file. Use of either -l or output redirection is recommended."

So, when I get my postgres server running with the following:

pg_ctl start -D /usr/local/var/postgres

The logs display in the terminal window.  When I run:

pg_ctl start -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log

The logs go to my logfile and don't display in terminal.

In short, it would be great if anyone can tell me what command I use after I've directed logs to the file (with the second command) to make the logs also appear at the command line.  It helps when I'm developing (in Django) to watch the SQL statements get executed in real time.


texts: Find SQL text from Plan Diagram in SentryOne Plan Explorer

texts:
I had SSMS create an execution plan and opened it through the context menu in SentryOne Plan Explorer. I go to Top Operations, select one with huge cost and switch to Plan Diagram. The node corresponding to the option is scrolled into view.

I'm now wondering how I can find out to what part of my SQL statement this node relates to. To generate comma separated lists I use some calls to STUFF ... FOR XML PATH. I guess it's one of those that's slow. Hence it reports some [XML Reader].

How can I find out which one?


texts: How do you gain access to embedded MySQL database via localhost command line?

texts:
We run an application (Windows 2003 server) and would like to have database connectivity -- contacted the company and they do not have any documentation on how to do so.  A quick glance at the installation and the services running shows that the data lives in an embedded MySQL installation.  Looking at some of the installation scripts reveals a login and password to some of the database tables...but no credential that gives full permissions to create/modify users.

Ultimate goal is to:

modfiy the my.cnf file to allow remote connections, then
grant access to a user who could connection into the database
make a dblink in our primary reporting database (Oracle 11g) to this MySQL installation

I see some writeups on how to reset root:
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

but I am hesitant to do so given that I don't want to run any risk of disrupting the application that uses this MySQL installation as a data store.

So, question: Is there any reason why I couldn't use a similar  approach as in the documentation above (especially option C - connecting with skip grant) but create a new user with the permissions I need (as opposed to altering the root user, as they do above?)

If it's relevant, mysql shows a server version of 4.1.18-pro-nt.

Thanks!


texts:
I need your help again. I need to calculate the monthly growth for the trips.
The query that I have written is below:
SELECT EXTRACT(YEAR FROM DATE (stoptime)) AS Year, EXTRACT(MONTH FROM DATE (stoptime)) AS Month,
    COUNT(bikeid) AS Trips,
    ROUND(100* (COUNT(bikeid)- LAG(COUNT(bikeid),1,0) OVER (PARTITION BY 'Year' ORDER BY 'Year', 'Month'))/lag(COUNT(bikeid),1) OVER
    (PARTITION BY 'Year' ORDER BY 'Year', 'Month')) ||'%' AS growth
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE end_station_name LIKE '%University%'
GROUP BY Year, Month
ORDER BY Year DESC, Month DESC

However, the query below cannot give me the right output. The output from the query above as below:

I do not know which part of my query is wrong. Kindly help me with this.
My desired output example:

Thank you!


texts:
Sorry if this has been covered before but I couldnt find an example to my issue

Situation: Say I have a table of sales and want to display them

Step 1)

SELECT payment_id, amount, payment_date
FROM payment
WHERE payment_date >= '2018-01-01'
AND payment_date <= NOW());

Now I want to sum sales or 'amount
Step 2)

SELECT SUM(amount) as 'Total Amount'
FROM
(SELECT payment_id, amount, payment_date
FROM payment
WHERE payment_date >= '2018-01-01'
AND payment_date <= NOW()
);

Is it possible to have that 'total amount' as a new row in my first table? Ie join the list of payment details with a total amount figure at the end of the list?


texts:
In ORDBMS databases I have seen that complex types can be used:

create type name as( ...)

And I can references those types when creating a new table, like:

create table example (row_name ref(name))

How can I achieve the same in PostgreSQL?


texts:
I simulated a crash in Postgres in middle of a transaction by force closing the Postgres process, after running again the Postgres the data that was changed was recovered to last data before transaction start,thats correct behavior.
I want to see actual recovery logs that do this thing in Postgres.
I spend lots of time with log files in Postgres but didn't find anything related to recovery and it just shows this message when I force close Postgres:

unexpected EOF on client connection with an open transaction

anyone have an idea how I can get actual recovery logs in Postgres?
I searched all over the internet and did not find anything useful except WAL system that generates files that I think might have data that I want but I cannot open them? is there any way?


texts: Extended Event definition information for a stopped session

texts: I am trying to get configuration information, via T-SQL, about existing, stopped extended-event sessions.  If the session is started, then the sys.dm_xe_session_object_columns DMV gives me exactly what I'm looking for.  Is that information available somewhere for stopped sessions?


texts: What settings to use to export mysql table rows that I'll be importing to another table with data using phpmyadmin

texts:
If I'm using phpmyadmin to manage mysql database, what settings should I use to export only two rows of data that I'll be importing to another table with the same name that contains many other rows?

I used the quick option, but I'm getting an error that table already exists and nothing gets imported.

If I use the default custom option with the default settings, I get the same error message.

So, what's the best settings to use to export settings that I'm going to import to another table?


texts: SQL Server Linked Server query taking hours to complete

texts:
One of the SQL select query taking 10 minutes to pull 2 million records in local SQL server.
When i run same query in other SQL DB server using linked server it takes hours to complete. The servers are in different locations(different US states) and different domains, This is just plain select query and it doesn't contains any joins, this issue observed for all the tables not just for single table.
what might be the reason for this issue and how would i fix it.
I gone through this below similar post but seems my case is different,
Executing query against Azure SQL db using Linked server takes hours
Below is the LocalQuery Estimated Execution Plan

Below is the Remote Query Estimated Execution Plan


texts: Rebuilding of Index in LogShipping DB in SQL Server 2008 R2 Standard version

texts:
Recently I joined the new organization and I am new to Log Shipping. After joining new company I found the fragmentation size of clustered indexes approx 256 indexes more than a 80% and I got the task from my manager to rebuild all of bad fragemented indexes.

And here is the configuration status of DB size and standard version:

DB Size:- 389698.00 MB
SQL Server Version:- 2008 R2 Standard edition (online index rebuild not supported)
HA\DRConfigured:- LogShipping

Now, here I am not confident to perform a rebuilding activity because I am not that much aware about the log shipping as log shipping is configured in the same DB.

So, experts please guide or provide me step by step to complete index rebuilding activity without any issue at the downtime or in future also.

Thank you in advance for all your help.


texts: SQL Server 2008: sequence which restarts daily

texts:
I have to add a trigger which should update a column using the following format strings: <current_date>_<per_day_incremental_id>, e.g 2015-10-01_36. Ids must be incremental and gaps are allowed.

My approach is rather naive: make a table with current date and current sequence value and maintain a single record in it:

create table DailySequence
(
    date date,
    sequence int
)

insert into DailySequence values (getdate(), 1);

CREATE TRIGGER MakeHumanReadableId ON dbo.AuditMeasures
FOR INSERT
AS
    DECLARE @ret int;
    DECLARE @tempDate date;
    DECLARE @nowDate date;

    SET @nowDate = getdate();

    SELECT @ret = t.sequence, @tempDate = t.date from DailySequence as t;

    IF @nowDate = @tempDate
    BEGIN
        SET @ret = @ret + 1;

        UPDATE DailySequence
        SET sequence = @ret;
    END
    ELSE
    BEGIN
        SET @ret = 0;

        UPDATE DailySequence
        SET sequence = @ret, date = @nowDate;
    END

    UPDATE AuditMeasures
    SET [HumanReadableId] = CAST(@nowdate AS VARCHAR(10)) + '_' + CAST(@ret AS VARCHAR(10));
    FROM inserted
    INNER JOIN AuditMeasures On inserted.id = AuditMeasures.id
GO

Questions:

Are there any pitfalls for my solution? e.g code inside the trigger won't run inside a transaction thus giving incorrect values.
Am I missing a better solution?


texts: mysql - large table - index performance and query issue

texts:
I have simplified a much larger table below:
CREATE TABLE `core` (
  `id` int NOT NULL,
  `loc_country` enum('United States','Colombia','United Kingdom',
       'Australia','India','Germany','Canada','Korea','Netherlands',
       '200 more')  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `loc_city` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
  `job` enum('a','b','c','d') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `loc_country_2` (`loc_country`,`job`,`loc_city`(6))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
          ROW_FORMAT=COMPRESSED

explain format=json
    SELECT id FROM core
        WHERE id!=518601449
          AND loc_country='Mongolia'
          AND id < 518601449
          AND job='a'
        LIMIT 151\G

*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "14002.99"
    },
    "table": {
      "table_name": "core",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "loc_country_2"
      ],
      "key": "loc_country_2",
      "used_key_parts": [
        "loc_country",
        "job",
        "loc_city",
        "id"
      ],
      "key_length": "34",
      "rows_examined_per_scan": 45657,
      "rows_produced_per_join": 45657,
      "filtered": "100.00",
      "using_index_for_skip_scan": true,
      "cost_info": {
        "read_cost": "9437.29",
        "eval_cost": "4565.70",
        "prefix_cost": "14002.99",
        "data_read_per_join": "1G"
      },
      "used_columns": [
        "id",
        "loc_country",
        "job"
      ],
      "attached_condition": "((`api`.`core`.`job` = 'a') and (`api`.`core`.`loc_country` = 'Mongolia') and (`api`.`core`.`id` <> 518601449) and (`api`.`core`.`id` < 518601449))"
    }
  }
}

The query took 14 seconds to run, I need it to be done in 0.01 seconds
The biggest problem seems to be to use id < XXX and order by id, I thought that this should be "free" to use given that the id is primary key.
I need the id < and sort because I need to get a different portion out of the database with each query, if I'd not use it I would receive the same data with each query for each country+job
I can not parition the table as I have dozens of such queries using different columns, it's just one example.
I believe the compression makes a big impact, it might be responsible for most issues I have though I don't have the storage on my NVME disks to run without compression.
Would it help to add the primary key to the indexes I have? At the end ? I fear it would waste a lot of storage space.
Any ideas ?


texts: dbcc shrinkfile not reducing size of log file in sql server 2008

texts:
I am shrinking log file of SQL Server 2008 R2 database, but it is not working. I was using this code:

ALTER DATABASE [database] SET RECOVERY SIMPLE
DBCC SHRINKFILE(DATABASE_LOG, 20)
ALTER DATABASE [database] SET RECOVERY FULL

I have seen this article on stackoverflow, and tried the following code as per this post, but this also did not solve my problem.

BACKUP LOG [database] TO DISK = 'D:\database.bak' GO
ALTER DATABASE [database] SET RECOVERY SIMPLE
use [database] declare @log_File_Name varchar(200)
select @log_File_Name = name from sysfiles where filename like '%LDF'
declare @i int = FILE_IDEX ( @log_File_Name)
dbcc shrinkfile ( @i , 50)
ALTER DATABASE [database] SET RECOVERY FULL

Please help.


texts: PostgreSQL on Windows fails to start - 'could not create shared memory segment'

texts:
PostgreSQL 9.4 & 9.5 (beta) on my Windows 7 laptop fails to start. In the Windows event log I see the following error:

  2015-11-10 13:53:49 IST FATAL:  could not create shared memory segment   "Global/PostgreSQL.851401618": Permission denied

It was working before for a long time, and suddenly it stopped.

I tried to uninstall and install, without any luck.

dynamic_shared_memory_type is set to windows.


texts: I need to provide real-time, or almost real-time, data for SSRS and Tableau reporting. I don't want the production OLTP system to be negatively impacted by long running queries. Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?


texts: Aggregation, joining, and filtering with many-to-many table

texts:
Let's say i have a database of customers who buy materials that are "applicable" to random objects. For example, John buys $10 of "Material X" that is applicable to a car and a house.

Customers
+----+-------+
| ID | Name  |
+----+-------+
|  1 | John  |
|  2 | Larry |
+----+-------+

Orders
+---------+------------+-------+----------+
| OrderID | CustomerID | Sales | Material |
+---------+------------+-------+----------+
|       1 |          1 |    10 | x        |
|       2 |          1 |    15 | x        |
|       3 |          1 |     6 | y        |
|       4 |          2 |     3 | x        |
|       5 |          2 |    25 | y        |
+---------+------------+-------+----------+

My Materials table originally looked like this

+----------+-------------------------+
| Material |      Applicability      |
+----------+-------------------------+
| x        | car, house, plane, bike |
| y        | car, bike               |
+----------+-------------------------+

When I need to display what materials John buys and which objects that material is applicable to, my query is this.

Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John'
GROUP BY ID, Name, Material, Applicability

The result

+----+------+--------------+----------+-------------------------+
| ID | Name | Total Sales | Material |      Applicability      |
+----+------+--------------+----------+-------------------------+
|  1 | John |           25 | x        | car, house, plane, bike |
|  1 | John |            6 | y        | car, bike               |
+----+------+--------------+----------+-------------------------+

The comma separated values (i know it violates many rules) was convenient, because when parsing the applicability i could simply split the string by commas and then i had a list of applicability objects.

Now it's been decided to normalize the Materials table. The table relationship is many to many. A material can be applicable to many objects, and objects can contain many materials.

+----------+---------------+
| Material | Applicability |
+----------+---------------+
| x        | car           |
| x        | house         |
| x        | plane         |
| x        | bike          |
| y        | car           |
| y        | bike          |
+----------+---------------+

This normalization has disrupted my existing query, it causes the sum(sales) result to be a multiple of however many objects the material is applicable to.

Example.

+----+------+-------------+----------+---------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+-------------+----------+---------------+
|  1 | John |          25 | x        | car           |
|  1 | John |          25 | x        | house         |
|  1 | John |          25 | x        | plane         |
|  1 | John |          25 | x        | bike          |
|  1 | John |           6 | y        | car           |
|  1 | John |           6 | y        | bike          |
+----+------+-------------+----------+---------------+

Now it looks John has bought $100 of material x, when he has really only bought $25. I need to show the user John's purchase of material x, as well as x's applicability.

The main problem is when i need to find out what John buys, but also filter by applicability.

Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY ID, Name, Material, Applicability

If any material is applicable to both car and bike, then the aggregate value sum(sales) will be doubled.

How do i deal with this duplication? I'm open to getting the result set in a different format, I just want the aggregation results to be correct.


texts: Updating table basing on composite primary keys from derived table

texts:
I have two tables TableA and TableB defined as followings:

TableA has 4 columns id1 (Primary key), id2 (Primary key), code, enable.

TableB has 3 columns id (Primary key), code, loc.

TableB.code corresponds to TableA.code.

I would like to update TableA.enable for the entry with TableB.loc equals to foo in MySQL. Under general conditions this should do the work:

UPDATE `TableA`
JOIN `TableB` ON `TableA`.`code` = `TableB`.`code`
SET `TableA`.`enable` = 1
WHERE `TableB`.`loc` = 'foo'

But in MySQL Workbench I got the following error: (since TableB.loc is not a primary key)

  Error Code: 1175. You are using safe update mode and you tried to
  update a table without a WHERE that uses a KEY column.  To disable
  safe mode, toggle the option in Preferences -> SQL Editor and
  reconnect.

So I attempted to nest a query to fetch the two primary key values from TableA and update TableA:

UPDATE `TableA` SET `TableA`.`enable` = 1
WHERE EXISTS (
    SELECT * FROM (
        SELECT
            `TableA`.`id1` AS `id1`,
            `TableA`.`id2` AS `id2`
        FROM `TableA`
        LEFT JOIN `TableB`
        ON
            `TableA`.`code` = `TableB`.`code`
        WHERE
            `TableB`.`loc` = 'foo'
    ) t
    WHERE
        t.`id1` = `TableA`.`id1` AND
        t.`id2` = `TableA`.`id2`
);

But it throws out the same error, so I tried to see if I can use the primary key column in my WHERE statement.

I also tried this query:

UPDATE `TableA`
SET `TableA`.`enable` = 1
WHERE (
    (`TableA`.`id1`, `TableA`.`id2`) = (
        SELECT `dummy`.`id1`, `dummy`.`id2` FROM (
            SELECT
                `TableA`.`id1` AS `id1`,
                `TableA`.`id2` AS `id2`
            FROM `TableA`
            LEFT JOIN `TableB`
            ON
                `TableA`.`code` = `TableB`.`code`
            WHERE
                `TableB`.`loc` = 'foo'
        ) AS `dummy`
    )
);

But it also throws the error 1175.

On the other hand, I tried something like this:

UPDATE `TableA`
SET `TableA`.`enable` = 1
WHERE (
    `TableA`.`id1` = (
        SELECT `dummy`.`id1` FROM (
            SELECT
                `TableA`.`id1` AS `id1`,
                `TableA`.`id2` AS `id2`
            FROM `TableA`
            LEFT JOIN `TableB`
            ON
                `TableA`.`code` = `TableB`.`code`
            WHERE
                `TableB`.`loc` = 'foo'
        ) AS `dummy`
    ) AND
    `TableA`.`id2` = (
        SELECT `dummy`.`id2` FROM (
            SELECT
                `TableA`.`id1` AS `id1`,
                `TableA`.`id2` AS `id2`
            FROM `TableA`
            LEFT JOIN `TableB`
            ON
                `TableA`.`code` = `TableB`.`code`
            WHERE
                `TableB`.`loc` = 'foo'
        ) AS `dummy`
    )
);

It works, but by repeating the subquery for dummy twice the code would be very clumsy. It is obvious to me that I can disable the safe update mode to perform this query, but I just hope to know if there are any methods for matching the rows using the two primary keys (TableA.id1 and TableA.id2) without repeating the dummy derived table and without disabling the safe update mode?


texts: How do I add keyboard shortcuts in SSMS 2012 to run a specific query?

texts:
How do I add keyboard shortcuts in SSMS 2012 to select all the data from table?

In SSMS 2008 we could assign a shortcut in the following way:

  TOOLS-->OPTIONS-->ENVIRONMENT-->KEYBOARD--->CTRL+3-->Select * from

This doesn't appear to be possible in SSMS 2012.


texts:
Hi i had 2 servers with the same hardware and software configuration, booth servers had the same my.cnf flags.

Centos 7.4
Cpanel
Mysql 5.6
Multi PHP

I execute a task on both servers, and on one of them the task uses 90% cpu but on the other server the same task uses only 0.25%.

Some clue about that?

Thanks in advance


texts: Refresh (replicate) MySQL database from server to server

texts:
I'm new to MySQL but I have experience with other major RDBMS like Oracle and MSSQL.
I'm trying to find out what are some of the widely used methods (or best practices) when it
comes to refreshing one or more MySQL databases between two servers on a nightly basis.
For example:    refresh the "QA" server from "PROD". I would like to develop an automatated
process to handle this refresh/reload and put it on a scheduled job and have it run nightly.
The current setup is running under Linux.

I would like to get some feedbacks or suggestions from those who have done this before
or provide me with a few pointers.

Thank you


texts: Is it important to release memory back to the server?

texts: I have a SQL Server 2008 R2 instance running on a production system. I'm not a DBA; I'm more a developer, but my client is currently asking me: How important is it to release memory from SQL Server back to the server? Would it impact the performance of subsequent queries to the instance if we released memory back to the OS?


texts: How do I set and get custom database variables?

texts: Using PGAdmin III I can right-click on a database, navigate to the Variables tab, and put a variable name-value property on the database itself.  Is there a way to customize these?  I saw an application_name variable, but I'd like to have an application_version variable.


texts: List minutes instead of grouping by them

texts:
Looking how to list minutes 1 to 120 from a 2 hour interval timestamp field instead of getting each minute from both hours together.

What I want is something like this:

min      COUNT(*)
1        300
2        320
3        294
...      ...
...      ...
61       341
62       210
63       258
...      ...
...      ...
119      196
120      347

I was doing the GROUP BY but I'm only getting 60 rows collecting the result of each hour.

min      COUNT(*)
1        641
2        530
3        552
...      ...
...      ...

sorry if it's silly but I can't get the keyword to find it out ... 'group by minutes without collecting, list by minutes query mysql, order more than 60 minutes' were some searches I made.

I'm using this for now, of course I know it doesn't work but I want to initialize from something,

SELECT MIN(timestamp) AS min, COUNT(field) FROM table WHERE timestamp BETWEEN '2014-06-12 10:00:00' AND '2014-06-12 12:00:00' GROUP BY min


texts:
I have a data like this

id  timestamp       ticket_id
2   6/16/16 8:14    1466089815
10  6/27/16 7:15    1466089815
3   6/16/16 8:14    1466089816
11  6/27/16 7:15    1466089816
13  6/27/16 7:50    1466089816

I am trying to find the time taken by tiket_id. Is there a simple function which can be used to a groupby and take a time difference.

Here I am trying to find the time taken by the ticket_id.
Eg:

ticket_id    time_taken
1466089816   10.93

Appreciate the support

Best
Jagan


texts: Are there any benefits in certain ordering of columns when defining indexes

texts:
For example, if I have two indexes:

CREATE INDEX IDX_1 ON MY_TABLE_1
 (ITEM, DATE, LOCATION)
 COMPUTE STATISTICS;

CREATE INDEX IDX_2 ON MY_TABLE_1
 (DATE, LOCATION, ITEM)
 COMPUTE STATISTICS;

Would this make IDX_2 redundant? If not, how do I determine the order of declaring the columns?

Should I be tailoring indexes to regular queries?


texts: How can I tune MySQL to behave better under load

texts:
We have a typical master-slave set up. When we find ourselves under considerable load, the master grinds to a halt. The slave continues to march along all happy like.

So during a recent slow-down, I took a VERY simple query (no joins, nicely indexed, not many results) and ran it on the slave and master, with profiling turned on.

Notice how different the results of the profiling are.

Master

Slave

I fear someone might tell me: "Of course your database will perform worse when it's under heavy load." And to them, I say: "You're right!"

But I am really curious if the results from the profile would suggest anything like:

Your DB server needs more memory, or
It's writing to disk too much, or
Change this setting and everything will be fixed, or
...

EDIT 1
The following graph shows how queries slow down when we're having a problem. The gold line is average write time and the green line is average read time. As you can see writes get really slow. The average write peaks at around 500 ms. When things are running smoothly, average writes peaks are down around 2ms.


texts:
Running PostgreSQL 9.1.8 on Xubuntu 12.04, installed from the repos.

From the shell, I have executed:

createlang plperl db_name;
createlang plperlu db_name;

As the superuser running psql, I have executed:

GRANT ALL ON LANGUAGE plperl TO account_name;
GRANT ALL ON LANGUAGE plperlu TO account_name;

The pg_language table reveals:

select lanname,lanpltrusted from pg_language where lanname like 'plperl%';

"plperl";t
"plperlu";t

When I create the following function:

CREATE OR REPLACE FUNCTION get_hostname()
  RETURNS text AS
$BODY$
  use Sys::Hostname;
  return hostname;
$BODY$
  LANGUAGE plperlu IMMUTABLE
  COST 1;
ALTER FUNCTION get_hostname()
  OWNER TO account_name;

I receive the following error:

ERROR:  Unable to load Sys/Hostname.pm into plperl at line 2.
BEGIN failed--compilation aborted at line 2.
CONTEXT:  compilation of PL/Perl function "get_hostname"

Yet the following works:

CREATE OR REPLACE FUNCTION get_hostname()
  RETURNS text AS
$BODY$
  return '127.0.0.1';
$BODY$
  LANGUAGE plperlu IMMUTABLE
  COST 1;
ALTER FUNCTION get_hostname()
  OWNER TO account_name;

The following Perl script works as expected from the shell:

use Sys::Hostname;
print hostname;

I tried to run the function as an anonymous block:

DO $$
  use Sys::Hostname;
  print hostname;
$$ LANGUAGE plperlu;

This returned the same error as before, with this additional information:

ERROR: Unable to load Sys/Hostname.pm into plperl at line 3.
BEGIN failed--compilation aborted at line 3.
SQL state: 42601
Context: PL/Perl anonymous code block

From the documentation, error 42601 is a syntax error.

I see no syntax error.

Furthermore, the function loaded fine with the superuser account when I imported the database:

psql -d db_name -U account_name -W -f db-dump.sql > import.log 2> error.log

I tried to simplify the code to no avail:

CREATE OR REPLACE FUNCTION get_hostname() RETURNS text AS
$$
  use Sys::Hostname;
  return hostname;
$$
LANGUAGE plperlu;

Same error as before.

What I don't understand is why, according to the error message, PostgreSQL is trying to load the code into plperl instead of plperlu.

Any ideas?

Update #1

Note that the postgres user is a Superuser:

                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}


texts: How to get last values in SQL Server 2008

texts:
I need to make a program that calculates money for water bills. Here are table definitions that I created:

CusAdd_Table
    Id,
    Name,
    Phone,
    FamilyNo,
    City,
    SubscribeDate

Calu_Table
    NowData,
    PerviousData,
    Diff,
    MonthtBill,
    money,
    id

The Diff column is computed as NowData-PerviousData. The id column is a reference to CusAdd_Table. The Money column is calculated with some differentiation. The MonthtBill column is a dataTimepicker used for ordering.

My table structure is able to easily add new subscribers to the database. When I add a new bill for a month I insert a row into the Calu_Table table. For example, I would specify the values for the NowData,PerviousData,[NowData-PerviousData] as Diff columns when inserting data for Jan. I would need to insert additional rows to insert data for Feb and Mar.

I want to print last month's data but the code needs to work without knowing the number of subscribers upfront. I tried the below code and it did not satisfy my requirements because I want every Id with its last value.

SELECT
CusAdd.CusID AS Expr1,
CusAdd.CusName,
CusAdd.CusPhone,
CusAdd.CusFamily,
CusAdd.CusCity,
CusAdd.CusSubscribe,
Calculte_Table.Months,
Calculte_Table.NowRead,
Calculte_Table.ExRead,
Calculte_Table.Esthlak,
Calculte_Table.WhatHePay,
Calculte_Table.Dis_Money,
Calculte_Table.MonthtBill
FROM Calculte_Table
INNER JOIN CusAdd ON Calculte_Table.CusID = CusAdd.CusID
ORDER BY Calculte_Table.MonthtBill DESC;

Here is the result:


texts: Selecting most recent date when dates are stored in another table

texts:
Working in a MySQL database, I have tables set up like this:

Table song_instances has song_id (int), meeting_instance_id (int)

The meeting_instances table has columns meeting_instance_id (int), meeting_date (datetime), meeting_id (int).

Each meeting_id (referencing the meetings table) might have numerous meeting_instances, and each meeting_instance might have numerous songs associated with it.

I'm trying to get a list of all songs from song_instances that are related to a particular meeting type (meeting_id)and include the date of the most recent date that the song has been used. Because the meeting_id and the meeting_date are only referenced in the meeting_instances table, I am joining that table in. But every attempt falls short.

Example:
song_instances:

song_id     meeting_instance_id
1           1
1           2
2           2
2           3
3           1
1           5
3           5

Meeting Instances:

meeting_instance_id      meeting_id       meeting_date
1                        4                2014-09-01
2                        4                2014-09-02
3                        2                2014-09-03
5                        4                2014-09-04

I want to get back:

song_id         lastDate
1               2014-09-04
2               2014-09-03
3               2014-09-04

etc.

Here is my most recent (and closest to successful) attempt.

SELECT si.song_id, mi.lastDate FROM song_instances si LEFT JOIN
        (SELECT MAX(meeting_date) as lastDate, meeting_id, meeting_instance_id FROM meeting_instances WHERE meeting_id = 4 GROUP BY meeting_instance_id) as mi
    ON si.meeting_instance_id = mi.meeting_instance_id
    GROUP BY si.song_id ORDER BY lastDate DESC

This currently gives me the date farthest in the past rather than the most recent date.
Not sure why. Any thoughts on how to improve this query?

This is a derived table from a much larger query, that is selecting song names and topics from other tables, but this is the only part of the whole query that is not working as intended.


texts:
I'm don't know much about database management, so to enable the remote access to my postgresql db I followed some tutorials. I've updated pg_hba_conf and postgresql_conf files.
In pg_hba_conf I added the
"host   all   all   0.0.0.0/0   md5"
line  in the "# IPv4 local connections" slot.
I've been succeeded for some days, but since yesterday there's a problem happening. In the computer I'm remote accessing the server, after I input the password for postgres user, PgAdmin returns "timeout expired".

First I've checked the server properties, and discovered that the IP inserted was not correct (what seemed strange, but I've corrected anyway). For the day it did, but today the same "timeout expired" message appeared again. The remote access settings are fine in PgAdmin, but as I checked pg_hba_conf in the server computer, it came with this settings where I've updated in first time:
"# IPv4 local connections:
host    all             all             127.0.0.1/32            @authmethodhost@"
Seems that it automatically removed my updated line and also altered the authentication method (as I remember, the default was "md5" and not this @authmethodhost@). I'm really confused with that.
Below I'm copying a print from the pg_hba_conf file for you to check if there's another change that I wasn't able to perceive.


texts: Stored procedure runs fine when executed manually but fails in scheduled job

texts:
I have a stored procedure that runs fine when executed manually but fails in scheduled job. The error I receive is:

Executed as user: \USER-a. Login failed for user '\USER-a'. [SQLSTATE 28000] (Error 18456).  The step failed.

My other stored procedures runs fine when scheduled. The only stored procedures that fails are the ones that are written in this form.
SET @QUERY = 'SELECT * from abc'
EXEC(@QUERY)


texts: using tnsnames.ora and sqlplus client without installing oracle 11g server on linux client

texts:
I want to create a connection to remote Oracle server using SQLplus client and tnsnames.ora without installing Oracle 11g server on the Linux client. I used debian and bash script to generate reports from remote Oracle servers. Is this possible?

I tried installing SQLplus client but I can't find the tnsnames.ora files.

If possible, can you please give me an example of configuring SQLplus client to use tnsnames.ora files?

I have installed the "basic instant client" and the "sqlplus addition" package. I can connect normally with sqlplus user/password@host:port/servicenames. I want to create bash scripts to execute *.sql files on more than 10 instances. So, if I can just connect with sqlplus user/pw@instance It will look simple. it's is possible?

Update :
Solved by copying tnsnames.ora files to /etc.

refer to this page on post #3

Thanks to :
Wernfried Domscheit, EdStevens, Paul White


texts: How to create aggregate function, like sum, with an implicit DISTINCT?

texts:
We have an ERP system which allows aggregates to be used (eg SUM(foo)) but not with DISTINCT (eg SUM(DISTINCT foo).

Is it possible to create an aggregate function (SUM_DISTINCT), that returns the same result as as SUM(DISTINCT foo), so SUM_DISTINCT(foo) = SUM(DISTINCT foo)?


texts: AlwaysOn Error: The target database is participating in an availability group and is currently not accessible for queries

texts:
We have a 2 Node AlwaysOn Availability Group in SQL Server 2014 running on Hyper-V (VM).

Current setup:

SRV01: Role: PRIMARY      READABLE SECONDARY: NO
SRV02: Role: SECONDARY    READABLE SECONDARY: YES

We do Manual Failover to Secondary Replica (SRV02 becomes primary and SRV01 becomes secondary). During testing our Application uses the AG Listener connection (INSERT/UPDATE TRANSACTION) and we have encountered an error stating:

  "The target database XXXXXX is participating in an availability groups
  and current not accessible for queries. Either the data movement is
  suspended or availability replica is not enabled for read access. To
  allow read-only access to this and other databases in the availability
  group, enable read access to one or more secondary availability
  replicas in the group.  (Microsoft Error: 976)

Has anyone encountered the same error, and what are your solutions?


texts: sp_configure: Preserve values of 'xp_cmdshell' and 'Show Advanced Options'

texts:
I dont want to disturb the server values of xp_CMDShell & Show Advanced options. The criteria is to use SQL to run a batch file.

This is the script i am trying to solve it with:

DECLARE @advanced_options int
DECLARE @xp_cmdshell int

--CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SELECT @advanced_options = value_in_use
FROM sys.configurations
WHERE name = 'show advanced options'

SELECT @xp_cmdshell = value_in_use
FROM sys.configurations
WHERE name = 'xp_cmdshell'

select @advanced_options as advOptions, @xp_cmdshell as cmdShell

exec sp_configure 'show advanced options', 1;
Reconfigure;

EXEC sp_configure 'xp_CMDShell', 1;
Reconfigure;

DECLARE @CMDSQL VARCHAR(1000)
SET @CMDSQL = 'F:\Adress\FillDBWithMSTabFiles'--Keep in mind, path needs to be correct.
EXEC master..xp_CMDShell @CMDSQL

EXEC sp_configure 'xp_CMDShell', @xp_cmdshell;
Reconfigure;

exec sp_configure 'show advanced options', @advanced_options;
Reconfigure;

select @advanced_options as advOptions, @xp_cmdshell as cmdShell

Results in error:

Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 3, Line 8
Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.


texts: Downgrading SQL Server database and moving it to another computer

texts:
I have created database on my computer using SQL Server 2019, and now i want to move it to another computer that has SQL Server 2014 installed on it.

Do I have to download SQL Server 2014 and downgrade the database or there is another solution for it? What should I do?


texts: Start/stop postgresql with pg_ctl or service without root access on RHEL6

texts:
We plan to use postgresql on RHEL 6.

DBAs won't have root access but they will need to start/stop the databases.
They'll be able to do so with pg_ctl as postgres.

However databases should also start automatically when the server reboots and so we should use services (/etc/init.d/postgresql-9.x).

When postgres is started with service (as root) and stopped with pg_ctl as postgres, the pid file in $PGDATA is deleted but not the specific pid and lock files (respectively in /var/run and /var/lock/subsys) created by the init.d script.

It leads to an inconsistent state where service postgresql-9.x status throws"postgresql-9.x dead but pid file exists".

So what is the best practice and solution for this situation?

ask sysadmin to give sudo /etc/init.d/postgresql-9.x to the DBAs and never use pg_ctl again?
remove the specific pid and lock files from the postgresql-9.x service script?
any other idea?


texts: How to send two query into one? Is there any other alternative?

texts:
So I have this query that gets me the number of failed parts in the order. for example 94 failed. However, I want to see the total amount of parts in that order. For example, 94 failed and 200 total. How can I achieve that in one query? If it's not possible how can I combine two queries in one result table?

SELECT status, COUNT(order)
FROM result
WHERE status='failed' and order = '4373'
GROUP BY status


texts: Migrating from SQL Server to IBM Filent P8

texts:
I am Newbie to IBM Filenet P8. I was trying to migrate my sample SQL Server database to IBM Filenet the other day and was stuck right at the beginning. I was just wondering FROM WHERE I SHOULD BEGIN. I've tried searching online for resources but none of them walked me through.

Please help and on this from the basics on how to migrate a database from SQL Server to IBM Filenet P8.

Thanks.


texts:
I'm currently learning how to build a site in PHP mysql. However, I seem to fail to understand COUNT() as count and wouldn't mind some further explanation.

I get the principles of COUNT, 0 || 1, and how it returns all the values that pertain to that query. But, don't see how COUNT as count works.

Anyhow, this is how the code I'm writing goes - so we have a working example - and where I first became perplexed.

SELECT COUNT(`id`) as `count`,
       `id`
FROM `user`
WHERE `email`='$email'
AND `password`='".md5$password."'";

If anyone can explain be a great help!


texts: What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?

texts: My database is extremely large and growing at a rate of ~20m rows/day.  I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month comparisons.  Time is displayed in the result sets occasionally but never used as a criteria.  Given this, I'm thinking that I'd save considerable storage space with an index on date alone vs a combined datetime field.  I'm not sure if I would also see performance gains in my selects or if there are any disadvantages to splitting into 2 fields.


texts: Error parsing DDL for schema.table in mysql workbench

texts:
I have production dbs using Mysql 5.5.31-0ubuntu0.12.04-1 (I know its old, will be upgraded soon).

I used a too advanced version of mysql workbench which allowed me to add a comment to key. (Now reverted to WB 6.3 which should be compatible with mysql 5.5)

Now when I 'Alter table' on one of the tables I get Error parsing DDL.
When I click it shows the error is in the comment of the key (see below)

 KEY `for cron (product ready)` (`a`,`b`),
  KEY `subproducts` (`a`,`b`,`c`) COMMENT 'find products by the subproducts joiner'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The question is how can I remove the comment of the key without mysql rebuilding the table and without access to the mysql workbench alter table UI.
This is an important production server and I would like to avoid alter table which will scan all the table.
Thanks


texts: Will mysqldump command also backup all the views that are present in DB or there is a special command for it?

texts:
I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it.

Please suggest me an answer. Any help is greatly appreciated.


texts: Where to start learning the IDEF1X method of model notation?

texts:
I recently started learning about data modeling.
After seeing @MDCCL's diagrams and thoughtful approach here, I'm interested in learning more about IDEF1X. I don't want to pay $200+ for the ISO standard.
So where can I begin to learn this appraoch?


texts: How to get the customer payment count for each row in MYSQL


texts: Shortest Query to Combine Count and Unique (Oracle)

texts:
I have the following query which works for me:

select count(*)
from (
   select UNIQUE col1, col2, col3
   from my_table
   where cond1 = 'A'
   and cond2 = 'B'
   and cond3 = 'C'
)

Is it possible to write this without the nested query?


texts: Rights to grant for controlling an Availability Group

texts:
We are having the following setting:
For an ERP Test / Accept SQL Cluster we want to follow the least privilege principle to give the external engineers rights on the SQL Server. We are running SQL Server 2016 in a cluster with two nodes. Synchronous commit, readable secondary on true. The engineers are added to an AD group. We grant permissions to that AD group.
The engineers need to be able to do the following on each cluster node concerning Availability Groups:

Remove database from availability group (for restore purposes)
Add database to availability group using automatic seeding

We have granted ALTER and CONTROL permissions on the Availability Group to the AD group on both nodes using the statement:
use [master]
GO
GRANT ALTER ON AVAILABILITY GROUP::[BSIBITSQLAG13] TO [BLIJDORP\DL_BSI_BiT_Red_SQL_DB_RW_Admins]
GO
use [master]
GO
GRANT CONTROL ON AVAILABILITY GROUP::[BSIBITSQLAG13] TO [BLIJDORP\DL_BSI_BiT_Red_SQL_DB_RW_Admins]
GO

After this the removing part works, the engineers are able to. But adding the database right back using automatic seeding is not working. Using the 'Add Database' wizard the validation step only shows green checkmarks. But when finishing the wizard the following error appears:

I tried giving the Availability Group create database permission because of this blog:
USE [master]
GO
ALTER AVAILABILITY GROUP [BSIBITSQLAG13] GRANT CREATE ANY DATABASE;
GO

This script worked, but didn't resolve the error at the other user (I'm sysadmin, they are not). I guess this is where the problem lies, we have to give the AD group rights to give rights to the Availability Group (to execute the above statement). I can't find out which statement to use. Also adding the securityadmins server role to the AD group doesn't work.


texts: How to Configure PostgreSQL Hotstandby Automatic Failover

texts:
I have configured two PostgreSQL servers to have hot standby by streaming.

How can I configure it so that when the master server goes down, the slave server will become the new master automatically?


texts: MongoDB: What does '[DataFileSync] flushing mmaps took ... ms for ... files' mean?

texts:
I am building an index in MongoDB for a large collections (~300GB) and the db terminal window keeps printing:

[DataFileSync] flushing mmaps took 10920ms for 151 files

The time that it takes to flush mmaps is usually around 10 seconds and sometimes higher. I'm running MongoDB on a high performance machine so RAM availability is not an issue.

What does the data file sync message mean?

Is it an issue??

EDIT:

The real problem with the flushing routine is that MongoDB seems to freeze while doing that.

Even simple queries from a terminal window on the same host have to wait for the flushing to finish in order to complete and ~10 seconds (up to 150s) is way too long. The routine happens (at irregular intervals of 1, 2, or 3 mins) even if no operations are run on the server and there are zero connections active.

I'm running MongoDB 2.2.1 2008R2+ on Windows Server 2008 R2.


texts: Back-end with encription and user-control for MS Access2003-VBA

texts:
My client has an Access 2003 "multi-user" (<6) "database" (<3k records) with "encryption" located in a LAN folder and an easy Form I polished with VBA(DAO library). Access 2003 encryption can be broken in less than a second with very easy tools.
A decent level of encryption should be applied to the data (and maybe user control). My client needs an "easy" solution; something they can manage on their own (like they did with the access mdb file) without the help of engineers.
Some computers have access 2003, some Access 2007 (so 2007's marvellous and straight forward encryption isn't a solution). Access 2003 refuses to connect to an access 2007 database via ODBC and also stores the passwords for linked tables in plain text (and doesn't ask for them).

I have thought of the following:

Encripting individual fields. User generates a sha-512 hash that is substracted from every field to get the decrypted field. Problem: Manual editions of the Access table are common in this database.
MySQL in a computer-server, they connect to the database via ODBC. Or SQL Express (seems better with Access). Problem: Someone has to configure and manage the server periodically. It seems disproportionaly complex given the scarce amount of data and the not frequent use of them. sqlite (which also has an ODBC driver) doesn't have good encryption, it seems.
I believe a complex solution in their case would have greater security risks than a simpler one...
A mdb file inside a zip file, the decription consists of extracting the file when they need to use it. Would solve the problem but is not elegant or safe enough (if a hacker went into the remote folder when the database is opened he would gain access to all the data). The file is only opened once or twice every day.

What do you suggest? Do you have another ideas? I have thought many times and it seems I'll have to go for SQL Express 2008; I don't like this solution.
Thank you


texts: Mysqld can`t start after I changed the socket path

texts:
here is the log

131229 14:55:27 [ERROR] Can't start server : Bind on unix socket: Permission denied
131229 14:55:27 [ERROR] Do you already have another mysqld server running on socket: /tmp/mysql.sock ?

and what i had down is this in my.cnf:

[mysqld]
socket =/tmp/mysql.sock

what to put the sock file in here and tmp

drwxrwxrwt.  15 root root  4096 Dec 29 14:55 tmp

But I can start it anymore, if I remove that /tmp/mysql.sock. every think is ok.
why?
why tmp got a 777 permission but it still got this error?
thinks in advance.


texts:
Currently I am running a m3.medium SQL Server RDS(20GB storage without IOPS). CPU utilization is under 50% for most of the time(~90%). However, during peak hours(~10%) CPU utilization will hit 100% and my website become extremely slow.

DB instance details(http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html)

Instance Type   : db.m3.medium
vCPU    : 1
ECU : 3
Memory (GiB)    : 3.75
PIOPS-Optimized : -
Network Performance : Moderate

Attached with the overall metrics of my RDS

How can i reduce the chances of my RDS hitting 100% CPU utilization? Does enable IOPS will helps? Or should i upgrade it into a better instance?


texts:
While profiling my application against SQL Server 2008 running against a JDBC Application most of the time consumed were by following

COMMIT TRAN
FETCH API_CURSOR & exec sp_cursorfetch

I cannot reduce the frequency of commits in my app - so is there any setting/configuration like increasing the redo log buffers or log file size that can provide relief ?

And should I look at avoiding server side cursors as it eats up most of the time?

But since most of those statements involve blobs probably batch returning it makes more sense.

Please advice.


texts: MySQL Workbench: Unable to CREATE/UPDATE table from valid SELECT

texts:
I have an SQL SELECT statement of the form:

SELECT a.date, a.count FROM (
  SELECT d.date, x.count + y.count - z.count AS count
  FROM date_table d
  LEFT JOIN (
    SELECT date, count FROM caluculated_table_x
  ) x ON d.date = x.date
  LEFT JOIN (
    SELECT date, count caluculated_table_y
  ) y ON d.date = y.date
  LEFT JOIN (
    SELECT date, count caluculated_table_z
  ) z ON d.date = z.date
) a ON a.date = d.date;

The SELECT works just fine, taking roughly 10s to complete. However, when I try to use it in a CREATE TABLE/UPDATE/INSERT INTO statement, e.g.:

CREATE TABLE count_summary
SELECT...;

I get "Error Code: 2013. Lost connection to MySQL server during query" after 600s. Any idea what might be causing this issue or how I might diagnose the problem?


texts: Different product attributes for ecommerce store

texts:
I'm working on an ecommerce database but I'm having difficulties when working with products attributes, such as size and colors. For example, I have this table where I save each product attribute:

product_id | stock | title | value | color | add_price
    1      |   1   | Size  |   S   |  null |   null
    1      |   3   | Size  |   M   |  null |   null
    1      |   3   | Size  |   L   |  null |   2.00
    1      |   1   | Color | Blue  | #FFF  |   null
    1      |   2   | Color | Pink  | #000  |   null
...

It's ok for me to show each type of attribute, size and color. The problem is when I need to create this configuration and associate each of them. For example, What if I have 10 shirts, where 5 of them are size M and of these 5 shirts 4 are blue and 1 is pink? How should I store this in the database in order to create a proper validation on this?

Currently I don't think I'll be working with products that need more than size and color attributes.

When looking online on some stores, what I've seen is that when there is a case like this, there is different products for each color (ex.: Shirt - Black, Shirt - Pink) and then only the size attribute is choosen. I don't know if there is a way to go on this case. Any suggestions on this is welcome.

  I'm working with a mysql database and PHP on the backend

As requested in the comments, this is the tables referent to the products and products attributes.

CREATE TABLE IF NOT EXISTS `product` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `active` tinyint(1) NOT NULL DEFAULT '0',
    `stock` int(11) NOT NULL DEFAULT '0',
    `name` varchar(250) DEFAULT NULL,
    `description` longtext,
    `weight` decimal(10,2) NOT NULL,
    `package_h` decimal(10,2) NOT NULL,
    `package_l` decimal(10,2) NOT NULL,
    `package_w` decimal(10,2) NOT NULL,
    `price` decimal(10,2) DEFAULT NULL,
    `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
);

CREATE TABLE IF NOT EXISTS `product_attr` (
    `product_id` int(11) NOT NULL,
    `stock` int(11) NOT NULL DEFAULT '0',
    `type` enum('List','Color') NOT NULL DEFAULT 'List',
    `title` varchar(50) NOT NULL,
    `value` varchar(50) NOT NULL,
    `color` char(7) DEFAULT NULL,
    `add_price` decimal(10,2) NOT NULL DEFAULT '0.00',
);


texts:
Does PostgreSQL cut statements if there is a subselect in it?

We use log_min_duration_statement = 20ms to find statements which need a long time.

But unfortunately the statements are truncated:

2014-11-20 14:09:25 CET foo3_egs_p foo3_egs_p [22106] LOG:  duration: 99210.696 ms  statement: SELECT "foo3_ticket"."tickettype_id", "foo3_tickettypegroup_tickettypeen"."tickettypegroup_id", "foo3_tickettype"."ordner_id", COUNT("foo3_ticket"."id") AS "pk__count" FROM "foo3_ticket" INNER JOIN "foo3_tickettype" ON ("foo3_ticket"."tickettype_id" = "foo3_tickettype"."id") LEFT OUTER JOIN "foo3_tickettypegroup_tickettypeen" ON ("foo3_tickettype"."id" = "foo3_tickettype group_tickettypeen"."tickettype_id") WHERE ((

The line ends with WHERE ((.

That's sad, because I would like to copy the statement to debug it with EXPLAIN ANALYZE

Why does the above statement get truncated? Any way to get the whole statement?

This is a legacy environment with PostgreSQL 9.1.2


texts: Converting float4 to numeric in PostgreSQL is rounding the values

texts:
I have a table that has a column storing some coefficients which require high precision. The type of the column is set to float4 and I need to change it to numeric.

But after altering the table, it changes the values I previously had stored in that column.

Eg: 0.0199999996 becomes 0.0200.

The command I used:

ALTER TABLE IF EXISTS table_name ALTER COLUMN column_name TYPE numeric(16, 15) USING column_name::numeric;

Any ideas on how I could change the column type but keep the previous values? I am using postgresql 10.4