texts: Does mysql stop a search automatically after finding 1 record when searching primary key with no limit?

texts:
Having trouble finding this answer. If I search for a record by a primary key id does mysql automatically stop searching after it finds 1 record if there was no "limit" in the query?
Say a table has 1 billion records and I search for id = 1 and id is the primary auto incrementing key in the table. SELECT * FROM logs WHERE id IN(1) Will that query stop after it finds one or search the whole billion records even after finding a match?
I am asking because a model in a framework I am using that auto-builds db queries builds them like this including using "IN(id)" instead of id = # which is why the example shows it that way.
I want to use the database model a cleanly as possible but if it has a performance issue because of how it builds the query when dealing with 1 record I have to add more code to manually specify a limit. The model also builds updates the same way I assume mysql will handle the select and update on the primary key the same way (stopping after 1 found/updated or not).
Thanks.


texts: How can I design a table for quick versioning of textual data?

texts:
Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content of Each edit, update, save is to be saved as new version based on option by end-user. How should i design a table for this kind of task?
Current Implementation
id pk auto increment
content
parentid defaults to 0
version  defaults to 0

Sample records
id     content        parentid        version
1      Hello World        0              0
2      Hello Doggy        1              1
3      Hello Kitty        1              2

as you can see each record with a parent id equal to existing id will be saved in incremental version(which is actually calculated in server side code). Which is displayed in the UI like below
1.Hello World
    -Hello Doggy(Version1)
    -Hello Kitty(Version2)

where each is displayed as link so that further editing can be done. Things works like expected no problem but we want to improve on this & i think it would be best to start from the table designs.


texts: How to determine when REORG with LONGLOBDATA is required (DB2)

texts:
REORGCHK only identifies when a reorg is required for space reclamation on normal (non-LOB) data. Is there a way of determining how many pages are wasted due to LONG and LOB data that has been logically deleted and thus indicate that a reorg with LONGLOBDATA would be beneficial?

The admin table ADMINTABINFO accurately identifies the number of pages containing LOBDATA but gives no indication of how much of that data is "real" data, and how much has been logically deleted.

eg. Before REORG with LONGLOBDATA column LOB_OBJECT_P_SIZE/32 showed 35328 pages. After the reorg it showed 2176 pages. A substantial saving. But I had no way of knowing that I would save this much until actually running the reorg.


texts: Is necessary to create a new table in this context?

texts:
I have a table Template and another ProjectDescriptions. A template can have many ProjectDescriptions, so the tables are like this:
Template: id, title, etc
ProjectDescriptio: title, description, template_id, etc

In the backoffice there is a screen for the user to create new project descriptions for a specific template.
In the frontend there is a list that shows the project descriptions for a specific template. There is a combobox for the user to select the project description that he wants, the can associate more than one project description to a template. My doubt is how to store the selected project descriptions. Is necessary to create a new table?


texts: Need help to optimize this slow performance Query

texts:
Need help to refactor a query. I need to get a result from four tables and structure looks like this

Resource table

Id | Leader | Title | Author |

ResourceField Table

Id | ResourceId | Position | ResourceFieldValueId |

ResourceFieldValue Table

Id | Tag | Ind1 | Ind2 | Normalized | Subfields | SpecialTag |

ResourceHolding Table

Id | Barcode | ResourceId | LocationId | CallNumber | ResourceCategoryId | ResourceStatusId | Cost |
Relation between tables:
These tables are to hold library's resource information. Resource represents book, ResourceHolding represents individual copies of a book, ResourceFieldValue represents properties of books and ResourceField holds relation between Resource and ResourceFieldValue. So,

A resource can have multiple Resource Holding records
A resource can have multiple ResourceFieldValues in ResourceField table

Note: In ResourceFieldValue table, each property of a book/resource represents by three digit numeric value (saved as a string) in Tag column and some of the Tags are grouped into one special group. For example these tags represents ('100', '110', '700', '800', '920') Author group, Values in these tags represent ('130', '222', '242', '245', '246', '730', '830') Title of a resource/book.
Query I am having issue with:
I need to get x number of unique holdings with Author's name and Title from ResourceFieldValue.. I have Title and Author in Resource table but I can't use that.
This the query so far I have come up with but it is still slow.. I have around 40,000 holdings and 360k+ ResourceFieldValue records. With this query, right now it takes around 30 second to get results.
SELECT DISTINCT
h.ResourceId,
h.Id AS HoldingId,
h.Barcode AS [BAR],
h.Call AS [CAL],
h.ResourceCategoryId as CategoryId,
fvAUT.Normalized fAUT,
fvAUT.Subfields sf_AUT,
fvTIT.Subfields sf_TIT
FROM ResourceHolding h
OUTER APPLY (SELECT TOP 1 Subfields, Normalized FROM ResourceField rfAUT INNER JOIN ResourceFieldValue rfvAUT ON rfAUT.ResourceFieldValueId = rfvAUT.Id AND rfvAUT.Tag IN ('100', '110', '700', '800', '920') AND ResourceId = h.ResourceId)  fvAUT
OUTER APPLY (SELECT TOP 1 Subfields, Normalized FROM ResourceField rfTIT INNER JOIN ResourceFieldValue rfvTIT ON rfTIT.ResourceFieldValueId = rfvTIT.Id AND rfvTIT.Tag IN ('130', '222', '242', '245', '246', '730', '830') AND ResourceId = h.ResourceId)  fvTIT
 WHERE h.ResourceStatusId != 7
ORDER BY fAUT asc
OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY

Let me know if you need more info to understand table structure. Any help would be greatly appreciated.
Thanks


texts: Store server password in table for reading with CURSOR

texts:
I would like to connect to multiple servers using Linked Server do download table and do queries.  I want to store server IP in a table and go down the list using CURSOR to connect to each.  I would like to store the password on the table as well but I don't know if this is bad idea.  Is this ok or what is better alternative?
Is there a version of sp_addlinkedsrvlogin that doesn't need password..?


texts: Difference in duration of SP:Completed and sum(SP:StmtCompleted)

texts:
I have a stored procedure, which sometimes displays a big difference in duration for the overall execution time and the execution time for each of its statements. The procedure consist of 4 statements, each with a maximum run-time of 4ms. But some procedure has Sp:Completed duration of around 1000 ms or more.
The procedure is called very often (at least once every second, or more).
My first idea was about recompile time, but this procedure is rather simple and doesn't need to be recompiled. I run trace to find recompile events relating this procedure and can't catch any record in it.
I want to use MS SQL 2017 if it may help. Every duration which I mention here was obtained from the SQL Server Profiler.
What is the reason for the long procedure execution time?


texts: Agent shows disabled(although it is not) after migration

texts:
I am migrating our old server to a more reliable and powerful new server. SQL Server edition and build number stays the same (SQL Server 2008 R2 Standard). For this I followed the steps given in this article. This is what I have done so far in summary:

Backed up the master db of the old machine,
Noted down the system db's data and log files' paths on the old server and created them on the new one,
Stopped the SQL services on the old machine and copied the data and log files of msdb and model db's of the old machine over to the new one and placed them exactly on their original paths (drive letters are same as well),
Started SQL Server on the new machine in single user mode and restored the backup of old machine's master db.
Backed up the service master key file on the old machine and restored on the new one in order for the linked servers to work,
Changed the server name of the new machine in system tables to the old one.

Everything looks like fine except a glitch. SQL Server Agent in the SSMS shows as "Agent XPs disabled". However, I can confirm that it is not. Agent service can successfully be started and run. It is whenever the agent service is stopped that the run_value of sp_configure 'Agent XPs'changes to 0. Whenever the agent service is started, it is set automatically back to 1.

What might be causing this and does it pose a risk?
Thank you


texts: Set InnoDB as default engine and set a default collation

texts:
What should I do to set InnoDB as the default database engine?
And is possible to set a default collation when I create a schema and tables (e.g. utf8mb4)?

I don't want to have to choose these options every time I create a new database.

I am using MySQL 5.7.


texts:
I'm trying to run Adam Machanic's sp_WhoIsActive in AWS RDS for SQL Server. When I run as the designated master account, say "TheAdmin", it works.  When I run as another user, say "AlmostAdmin", whom I've made a db_owner on the target DB, say "MyDB", it fails with permission errors like so:

Msg 297, Level 16, State 1, Line 49
The user does not have permission to perform this action.
Msg 297, Level 16, State 1, Line 342
The user does not have permission to perform this action.

Note that, because you can't create any objects in the master DB in RDS, I had to create sp_WhoIsActive in the user DB MyDB, as stated.

Anybody had any luck making this work in RDS?  Thanks!

Other things I've tried:

Granted VIEW SERVER STATE to "AlmostAdmin" and added him to server-roles processadmin and setupadmin to mirror "TheAdmin".
Added "AlmostAdmin" to all other elevated DB roles in MyDB (yeah, I know, shouldn't make a difference, just grasping at straws).


texts: Why does selecting an attribute of an Oracle object require an alias?

texts:
My GIS software has an Oracle 18c object type called ST_GEOMETRY.
The object type has attributes:

The attributes of the spatial type represent the following
information:

Entity: The type of geometric feature stored in the spatial column (linestring, multilinestring, multipoint, multipolygon, point, or
polygon), the value of which is a bit mask derived from the
st_geom_util stored procedure.
Numpts: The number of points defining the geometry; for multipart geometries, this includes the separators between each part, one point
for each separator.
Minx, miny, maxx, maxy: The spatial envelope of the geometry
Area: The area of the geometry
Len: The perimeter length of the geometry
SRID: Contains the identifier for the geometry that links it to its associated spatial reference (coordinate system) record in the
ST_Spatial_References table
Points: Contains the byte stream of the point coordinates that define the geometry

I can select an object's attribute using one of two methods:

Use Oracle's TREAT() function.

select
    treat(shape as sde.st_geometry).numpts as num_points
from
    a_test_table

NUM_POINTS
----------
        37
       161

Or I can simply use an alias:

select
    a.shape.numpts as num_points
from
    a_test_table a

NUM_POINTS
----------
        37
       161

Question:
Why is it necessary to use an alias to select an attribute from an Oracle object?
If I were to remove the alias...
select
    shape.numpts as num_points
from
    a_test_table

Then I would get an error:
ORA-00904: "SHAPE"."NUMPTS": invalid identifier

Edit:
This appears to apply to the native Oracle SDO_GEOEMTRY methods too.
select
    a.shape.get_wkt()
from
    table a


texts: Can I change max_allowed_packet for a MySQL database, but not others in the same instance?

texts:
We have a MySQL server with multiple databases in the same instance. Can I change the max_allowed_packet value for just one of the database?

From looking at this, it seems SET GLOBAL max_allowed_packet=xx will affect the entire server while SET max_allowed_packet=xx will only affect the current session


texts: Review a database schema for a design approval system

texts:
I'm working on my first web application that will utilize a database. The basis for the application is a design approval system. I work for a company which mainly produces print and online marketing materials, most frequently magazines and websites. I'd like to have all communication run through the site and host all of our files there rather than work through email and various file delivery systems since most of them are too big to send through Outlook.

This is the schema as I've designed it so far (only the magazine part is included thus far):

The way I figure it should work is everyone is assigned a username and password. The roleid indicates if they're a writer, editor, graphic designer, advertiser, or customer. If they're a designer, writer or editor they'll get to see all of the currently unapproved magazines and see what needs to be done as well as make posts and download/upload files.

Advertisers will be brought directly to the page regarding their advertisement and see any posts that our writers/designers have posted as well as download and upload files.

If they're a customer (owner of the magazine) they will get to see all of the editorial as their own section as well as a section for where we are in regards to layout/final product and respond to our writers/designers as well as upload/download files.

If y'all can point me in the right direction I'd be really appreciative.

UPDATE

Updated ER diagram


texts: Recovery_model for backupset table null sqlserver

texts:
Going through https://www.sqlshack.com/understanding-database-recovery-models/
I see that there are three types of recovery models:

SIMPLE
FULL
BULK_Logged

But for the table msdb.dbo.backupset I can see the recovery_model column can be null. In what case would this take a NULL value. How to create a backup such that it falls into this case.


texts: SQL Server INNER REMOTE JOIN returns more rows than INNER JOIN

texts:
I'm trying to join a few rows from a remote view to a local table. The view has about 300 million rows, so I want to use the REMOTE query hint so that all 3 million rows don't have to be transferred to my computer.

SELECT R.Something, L.ID, L.Something
FROM [dbo].[LocalTable] L
INNER JOIN (
    SELECT TOP 100 Something, L_ID FROM [RemoteServer].[RemoteDB].[dbo].[RemoteTable]
    ) R
ON L.ID = R.L_ID

This returns 100 rows, as I expected, and takes basically no time, as I expected.

However,

SELECT R.Something, L.ID, L.Something
FROM [dbo].[LocalTable] L
INNER REMOTE JOIN (
    SELECT TOP 100 Something, L_ID FROM [RemoteServer].[RemoteDB].[dbo].[RemoteTable]
    ) R
ON L.ID = R.L_ID

starts to return thousands of rows. I quit it after a few seconds, but it was in the tens - hundreds of thousands.

How could a query hint change my result set?


texts: What are options for Bi-Directional Replication of SQL Azure Database

texts:
This questions came from a developer friend who is working with SQL Azure Database.

Scenario: There are two Azure DB servers, one in North America, USA region and another one in South East Asia region. One way replication has been setup for a read-only replica to enable a local client to get data faster. The issue is whenever the client has to write data to the database, they need to write to North America, which incurs large latency issue. As a solution, they want to setup bi-directional replication between two servers to keep them in sync and thus allowing a client to read and write from a server located in their region.

I'm more of SQL Developer than DBA, thus, I'm not familiar with different options that could be available for bi-direction in SQL Azure DB. I have told him about SQL Broker and how I saw it used for near real-time replication and with some effort it could be set up for bi-directional messages, though not sure if it can be implemented in SQL Azure DB.


texts: Modeling an entity-relationship diagram about Clients, Orders, Payments and Bills

texts:
There is a part of an entity-relationship diagram creation that troubles me, I don't know if it's ok. I tried to follow these instructions:

A client makes an order (which has products).
When the client pays, he or she gets a bill with information about the order.

My attempts

Is this one ok?

Or this one?


texts:
I have a requirement to create a query which can get the logins details which were not used in last one month.
I have tried several way like extended events and audit tracing. But i'm unable to get the list of the logins that were not being used in last one month,
Please help me with this.


texts: Is it possible to set initial index number in a table?

texts:
My System database has got a very large amount of data and some tables are really getting very big. I was looking for a way such as when the system will query on those large tables, it will ignore the historical data and will start looking from the index that I will set. Something like I have 20000 rows but I will set 15000 index as the starting point, so the query will think 15001 is the first index and will start operation.
How can I achieve this? I do not want to delete the historical data from my tables.


texts: Performance schema: 'stage/sql/Sending data'

texts:
Background

I am using MariaDB 10.0 but MySQL 5.6 answers are also of interest.

Two machines cloned from the same image, same MariaDB configuration as far as I could tell, same dataset. One machine is way slower than the other (the production machine, of course).

I have enabled performance profiling to investigate (performance_schema=1 in /etc/my.cnf), restarted.

A query on development host:

SELECT
  event_name AS Stage,
  TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM
  performance_schema.events_stages_history_long
WHERE
  NESTING_EVENT_ID=23;

Output:

+--------------------------------+----------+
| Stage                          | Duration |
+--------------------------------+----------+
| stage/sql/init                 | 0.000122 |
| stage/sql/checking permissions | 0.000005 |
| stage/sql/Opening tables       | 0.000610 |
| stage/sql/After opening tables | 0.000004 |
| stage/sql/System lock          | 0.000012 |
| stage/sql/Table lock           | 0.000002 |
| stage/sql/After opening tables | 0.000006 |
| stage/sql/init                 | 0.000042 |
| stage/sql/optimizing           | 0.000022 |
| stage/sql/statistics           | 0.000591 |
| stage/sql/preparing            | 0.000040 |
| stage/sql/executing            | 0.000003 |
| stage/sql/Sorting result       | 0.000003 |
| stage/sql/Sending data         | 0.345838 |
| stage/sql/end                  | 0.000006 |
| stage/sql/query end            | 0.000003 |
| stage/sql/closing tables       | 0.000012 |
| stage/sql/freeing items        | 0.000009 |
| stage/sql/cleaning up          | 0.000001 |
+--------------------------------+----------+
19 rows in set (0.00 sec)

Same query on production host:

SELECT
  event_name AS Stage,
  TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM
  performance_schema.events_stages_history_long
WHERE
  NESTING_EVENT_ID=7;

Output:

+--------------------------------+-----------+
| Stage                          | Duration  |
+--------------------------------+-----------+
| stage/sql/init                 |  0.000124 |
| stage/sql/checking permissions |  0.000005 |
| stage/sql/Opening tables       |  0.000024 |
| stage/sql/After opening tables |  0.000006 |
| stage/sql/System lock          |  0.000005 |
| stage/sql/Table lock           |  0.000003 |
| stage/sql/After opening tables |  0.000007 |
| stage/sql/init                 |  0.000043 |
| stage/sql/optimizing           |  0.000029 |
| stage/sql/statistics           |  0.003686 |
| stage/sql/preparing            |  0.000061 |
| stage/sql/executing            |  0.000003 |
| stage/sql/Sorting result       |  0.000004 |
| stage/sql/Sending data         | 11.537281 |
| stage/sql/end                  |  0.000010 |
| stage/sql/query end            |  0.000003 |
| stage/sql/closing tables       |  0.000016 |
| stage/sql/freeing items        |  0.000012 |
| stage/sql/logging slow query   |  0.000007 |
| stage/sql/cleaning up          |  0.000002 |
+--------------------------------+-----------+
20 rows in set (0.00 sec)

Which is over 3000% slower. The data returned by the queries is the same in both cases, only 33 rows and four columns, about 300 bytes in all.

Questions

What is the stage/sql/Sending data metric actually measuring?
What could possibly be the reason for such a slow performance?


texts: Creating a Trigger in MySQL that stores stock levels once a sale takes place

texts:
I have a basic database for a retail store with 4 tables, one of which stores Order info, one that stores Product info and one that stores Supplier info.

I  am trying to run a query that will display total sum of stock sold figure (in dollars) with a GROUP BY supplier with ROLL UP. I have a feeling that SUM should be in the code but I can't find where to put it, so far I have this:

SELECT ORDERS.OrderPrice, PRODUCT.SupplierID, SUPPLIER.SupplierName
FROM ((ORDERS
INNER JOIN PRODUCT ON ORDERS.ProductID = PRODUCT.ProductID)
INNER JOIN SUPPLIER ON PRODUCT.SupplierID = SUPPLIER.SupplierID)
GROUP BY SupplierName WITH ROLLUP;

The database can be viewed on DB Fiddle here:

https://www.db-fiddle.com/f/qS515ShyFGxgj4h6Ga6HA3/1#&togetherjs=aNgE8G0yMh

Any ideas? Thanks


texts: What is the difference between a SQL Login and a Database User in terms of security?

texts:
I am currently working with a consulting agency to set up a new production environment. They have set up a separate SQL login for each database.

However, there is no corresponding database user mapped to any of the logins, and all of the logins seem to have full access to any of the databases on the server.

In fact, each login has a check next to all the server roles.

Is this a common practice and is it safe in terms of security?


texts: How to minimize the need for recalculating index on SQL Server?

texts:
We have rather big tables (500 mils rows) where we expect frequent inserts. The tables have approx. 2 indexes each. The problem is that we are afraid that, when the index is recalculating the whole table is locked and all queries will time out (during the time of recalculating).

Is there a way how to minimize the occurrence of recalculating the index? For example let the nodes to be bigger when they are created, so more added pointers will fit in the index without the need of the rebalancing the index?

We have SQL Server 2008 R2.


texts: Profiling postgres with gprof: no data/gprof folder and 'no time accumulated'

texts:
I'm attempting to profile Postgres with gprof. A gmon.out file successfully gets created in both the data directory and in the working directory where I launched the client (pgbench) process from.

These files include the numbers of calls made to each function, but no times (It says 0.00 for all of them and warns "no time accumulated" at the top of the readable output). Furthermore, no data/gprof folder gets created (it is supposed to make a gprof directory filled with directories for each server process, and gmon.out results for each).

I have tested gprof using these instructions (http://www.thegeekstuff.com/2012/08/gprof-tutorial/) and it works fine. My installation of postgres also seems to work fine otherwise.

The postgres mailing lists have this one question on the subject: http://www.postgresql.org/message-id/DD33DDFC-3C26-4550-9E9A-FE29E017B374@gmail.com
Unfortunately this problem is not quite the same as mine; this user has output in the gprof folder and may not be compiling with gcc.

I've also tried modifying the permissions for most things, with no change. Why isn't gprof working?


texts:
I’ve planned to build MySQL master-master replication and need DR for this setup. I was  thinking for replication from any of the node as a slave.

Please help, which option is best and how to switch over back if any disasters happen?


texts: Restrict the occurrence of a value according to the respective value in another column in a PostgreSQL table

texts:
I'm using a PostgreSQL table to link taxonomic names with the respective taxon concept as follows:

create type name_status as enum ('accepted','basionym','synonym');
create table temp (
  concept_id integer not null,
  name_id integer not null,
  status name_status,
  primary key (name_id),
  unique (name_id,concept_id)
);
insert into temp values
  (101,1,'accepted'),
  (101,2,'basionym'),
  (101,3,'synonym'),
  (101,4,'synonym'),
  (102,5,'accepted'),
  (102,6,'synonym');

The following conditions have to be fulfilled by this table:

The value 'accepted' have to appear once and only once per 'concept_id'.
The value 'basionym' may not appear in each 'concept_id', but if occurring, then only once per 'concept_id'.
The value 'synonym' may occur many times or none per 'concept_id'.

Is there a way to define such constraints without the necessity of inserting a new table in the database?


texts: How to change the database mode to STANDBY?

texts:
I have a "log shipping" from server A(Database Name = main) to server B(Database Name = LS_main) .
I want to take a Snapshot Database From the secondary Database (Database Name = LS_main), but he id in STANDBY mode (Read_only) and I can't do it.

How can I do it ?

(I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode...is it right to do ?)


texts: How can I make this nested query more efficient?

texts:
I have 3 tables: Room, Conference, and Participant.  Room has many Conferences, and Conference has many Participants.  I need my query to display the fields from Room, as well as the number of associated Conferences it has, and the sum of the number of associated Participants each Conference has.  Here's a cut-down version of the SELECT query I wrote to get this info; first, I just selected the room ID:

SELECT TOP(1000)
  rm.[Id]
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

This was very fast as SQL Server was able to just pull the data from Room and pretty much ignore the subquery (see Trial 1 - Trial 4 in image below).  Then I added in the ConferenceName field from the subquery, as well as a count of the number of conferences per room:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName])
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

This slowed down the query quite a bit, by a factor of about 100 (see Trial 5 - Trial 7 in image below).  I then added in the participant count from the subquery, meaning there were 2 levels of aggregate functions being used:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

This further slowed down the query by a factor of about 4 (see Trial 8 - Trial 10 in image below).  Here's the client statistics with data on the 10 trials:

Here's the query plan of the slow query: https://www.brentozar.com/pastetheplan/?id=SJpyeec5Q

Is there a way I can make this kind of query - where I calculate an aggregate of a subquery's aggregate - more efficient?


texts: Alternative to triggers on system views for user auditing

texts:
Even writing the title I'm cringing at the idea I've tried this (but hey, I've learned something in the process).

I've got a problem with developers running migration scripts on the database and in the process overwriting stored procedures already existing in the database (DEV, thank a deity).

However, there's no way of finding out who made these changes because:

they don't admit it
SQL Server does not populate the principal_id column in the sys.objects  system view, because everyone is connecting as dbo (more info here).

So, I've tried writing a trigger on sys.objects and that didn't work (I don't know why I ever expected it to) as I'm getting a error:

  The object 'DEV_DB.sys.objects' does not exist or
  is invalid for this operation.

I've also tried creating a user for each developer so that when they do their automatic migrations they will use the user I've made them and hopefully all changes made to sys.objects by that developer will be logged as the user they logged in with.

But that didn't work either, since principal_id still wasn't populated (what I have done was create a new login / user for each Developer & DB and give the login specific server roles : public, db_datawriter, db_datareader, db_ddladmin).

Any suggestions on what else I could try as an alternative?


texts: Mongodump: Unrecognized field 'snapshot'

texts:
I am using mongodump from mongo tools to download a backup of my server. However as I run the command, the command returns "Unrecognized field 'snapshot'".

The full error is:

Failed: error reading collection: Failed to parse: { find: "data", skip: 0, snapshot: true, $readPreference: { mode: "secondaryPreferred" }, $db: "xxx" }. Unrecognized field 'snapshot'.

Mongodump --version returns:

mongodump version: built-without-version-string
git version: built-without-git-spec
Go version: go1.7.3
   os: linux
   arch: amd64
   compiler: gc
OpenSSL version: OpenSSL 1.0.2g  1 Mar 2016

I might need to downgrade my version? Otherwise unsure of where to go from here...


texts: Add a mongodb read only replica for reporting purpose

texts:
I have a MongoDB DB X sharded in 2 replica set. Now I want to add a replica (which will be only read operations) to reduce load on mongodb for reporting purpose.
How is possible to achieve this as my collections are sharded to 2 different replica sets?


texts: How do you see which database on a Server uses most resources

texts:
I have a database server with a couple of databases on it. How can I see where any resource pressure may come from?

I would like to get a table of:

Database Name
Batch Requests per second
CPU Time
Logical Read
Logical Writes


texts: Retrieving a record for the last date received and returning the value of another column in that row

texts:
I have the following query which is returning me total number of expected records :

select a.New_FirstName + ' ' + a.New_LastName as 'Account Name', a.New_MPRNNumber,MAX(b.New_readdate) as 'Last SC Read Date'
from AccountExtensionBase as a
left join New_meterreadingextensionBase as b on a.AccountId = b.new_account_meterreadingid
where New_AccountType in (5,7)
and b.New_ReadTypeCodeMM = 'SC'
and b.New_ReadReasonCodeMM = 95
group by a.New_FirstName,a.New_LastName, a.New_MPRNNumber
order by a.New_MPRNNumber

However when I add the New_ReadingValue property to the select list as follows I am returning an incorrect number of rows:

select a.New_FirstName + ' ' + a.New_LastName as 'Account Name', a.New_MPRNNumber,MAX(b.New_readdate) as 'Last SC Read Date', New_ReadingValue

I understand why this is happening in that the two readings are different but I only want to get 1 reading for the latest read date, what is the best approach for doing this? can i set up distinct on two or three of the other properties?

If I try the above then It complains that New_ReadngValue is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause


texts: MySQL: Is it best practice to check whether some column is NULL by `NOT ISNULL(column_name)`?

texts:
Given the following schema, my question is that since I have already added the NOT NULL to IsBanned, would I have to check whether it is not null using the syntax NOT ISNULL(IsBanned)? And what's the best practice to do the same if I didn't add NOT NULL?
CREATE TABLE Viewer (
    AccountID varchar(25),
    IsBanned boolean                NOT NULL
                                    DEFAULT 0,
    ...
    CHECK                           (NOT ISNULL(isBanned)),
);


texts:
I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is running slowly.

It isn't always slow but it's particularly slow at the beginning of the month when we have a large amount of data inserted into the CMLEDG table.

The plan is here https://www.brentozar.com/pastetheplan/?id=BycGicsGX


texts: Multi-dimensional range queries with single column indexes

texts:
I am reading a book that covers many database topics on a very high level, but doesn't dive deeper. It introduces multi-column indexes as a better way to query for 2 dimensional ranges. For example, this query is stated to be inefficient, if latitude and longitude each have single column indexes:

SELECT * FROM restaurants
 WHERE latitude > 51.4946
   AND latitude < 51.5079
   AND longitude > -0.1162
   AND longitude < -0.1004
;

A better way would be to use a multicolumn index using R-trees spanning over both latitude and longitude.

I don't understand why first extracting information of latitude > 51.4946 using a single columnd index and then extacting information longitude > -0.1162 AND longitude < -0.1004 using the other single column index is inefficient? Can you use only 1 index per table when performing a query or is there some other reason for inefficiency?


texts: SQL Check Constraint that requires a not null value in a column based on the value of another column

texts:
I have a table with the following two columns

OnSite BIT NOT NULL
ClientId INT NULL

I want to add a constraint that requires ClientId to be NOT NULL when OnSite is true(1).

I could not find anything on the site.

Thanks


texts:
I'm working on an extension to a legacy database application. Specifically, I need to be able to add entries to a notes system.

Using SSMS, I can see that existing notes are stored in a table that has a column for the formatted text of the note. This column is of type "image" and the contents appear to be an RTF document. E.g.:

?{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\lang2057\f0\fs20 Text needs to go here
\par }

From here, I thought it would be fairly easy to modify the text. Surely something like the following would work?

UPDATE ConstituentNotepad SET Notes = CONVERT(image,'?{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\lang2057\f0\fs20 Good text needs to go here
\par }
') WHERE NotesId = 35416

Alas no. After executing the update, the note now shows the raw RTF markup rather than the properly rendered text.

I presume I'm mangling the RTF somehow when converting / updating. Am I doing something obviously wrong? How would you approach this?


texts: mongodb service not running after changing path

texts:
I have changed the dbpath of mongodb.conf.
And after that trying to start mongodb service,when I run

sudo service mongodb start

I get back

mongodb start/running, process 7320

After that I run:

sudo service mongodb status

And getting back

mongodb stop/waiting

When I want to access mongo shell by :

 mongo
MongoDB shell version: 2.0.4
connecting to: test
Sun Oct 28 13:01:36 Error: couldn't connect to server 127.0.0.1 shell/mongo.js:84
exception: connect failed

But when I start mongod as manually by mongod --dbpath=/mnt/data/db it runs fine.
And I can access the mongo shell by mongo command.

I have correct permission for my data directory.

Any clue where I am doing wrong?


texts:
I have a list of strings in my database let say in a column

understand
understan
understa
underst
unders
under

I'm trying to find out How to delist subset strings if it is a substring of another string with sql?

So I if we pretend that this is a column of my table, the end result must be only

understand


texts:
Would it be possible to suppress all sqlcmd messages output to the cmd window?

I am running a large script and it seems to be slowed down a lot with all the Processed XXXX total records messages.

The script is over 4Gb so there is a large amount of data.


texts: Are deadlocks always a symptom of design flaws or are they an unavoidable occurrence even in well designed systems with high data writes/reads?

texts:
A complete redesign proposal is experiencing deadlocks in its prototype. I am of the opinion that deadlocks are always the symptom of design flaws. I do not want to go into the specifics of our system; I just want to know others'opinion in general situations.
If a system is experiencing deadlocks on volume testing at such an early stage in its development, should the design be reevaluated?


texts: With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order?  There is an indication through a lack of information that this is not an option, but perhaps there is a way to do this such as queuing in a different order.


texts: Does a filtered index improve searching on the opposite condition?

texts:
Does having a filtered index on a column improve queries with a WHERE clause that is the opposite of the filter condition?

Since the filtered index specifies what not to return, does it reduce the number of records to search?

Example:

CREATE TABLE MyTable
(
  ColumnA INT PRIMARY KEY CLUSTERED,
  ColumnB INT NULL
)

CREATE NONCLUSTERED INDEX IX_MyTable_Filtered_ColumnB
  ON MyTable (ColumnB)
  WHERE ColumnB IS NOT NULL

The index should improve this query:

SELECT ColumnA
FROM MyTable
WHERE ColumnB IS NOT NULL

Will it make any difference to this one?

SELECT ColumnA
FROM MyTable
WHERE ColumnB IS NULL


texts: MySQL/PHP - SELECT DISTINCT column based on another column with ORDER and LIMIT


texts: Effect of upgrading windows server 2012 data center

texts:
I have a production windows server 2012 data center server where IIS and SQL server was installed and replication was configured in SQL Server. It is infected with a malware. After cleaning it with an anti malware, I am unable to install applications (MSI installer problem).
I have tried almost every option available but no joy - I still can't install applications.

Finally I have decided to upgrade/repair the OS.

Will it affect the SQL sever replication, The IIS configuration? any Idea?


texts:
I have a Postgres (8.4.20) table that I'm supposed to add columns to, but it seems to have some problems that I have difficulties to debug:

I'm attempting what I thought was a simple ALTER TABLE... on it:

db_name=> ALTER TABLE "marking" ADD COLUMN published TIMESTAMP WITHOUT TIME ZONE;
ERROR:  canceling statement due to statement timeout

I'm puzzled, because:

Although the table has (very little) data in it, it's not a NOT NULL column
I've also tried to dump the table, delete it's contents and re-run without data in it

But it still gives the same result. Here's the current schema for the table:

db_name=> \d marking
           Table "public.marking"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 link   | character varying(32) | not null
 hash   | character varying(40) | not null
 url    | text                  | not null
 blob   | text                  | not null
Indexes:
    "marking_pkey" PRIMARY KEY, btree (hash)
    "ix_marking_link" btree (link)

Sorry for the newbie question, but there's clearly something fundamental I don't understand here and I'm having some trouble getting further.

I've tried to increase statement_timeout and work_mem, but even very large values won't help.


texts: What is the potential fallout of changing SQL Server's log on account?

texts:
I need to be able to read files from a UNC path in TSQL and am getting an access denied error.  I've read I must change the user SQL Server logs in as to a domain user, it's currently running as NT Service\MSSQLSERVER.

If I make the change, what potential issues should I watch out for, if any?


texts: Why is DistinctSort followed by TopNSort in my query plan?

texts:
I have the following definition:

CREATE TABLE [dbo].[JobItems] (
    [ItemId]            UNIQUEIDENTIFIER NOT NULL,
    [ItemState]         INT              NOT NULL,
    [ItemCreationTime]  DATETIME         NULL DEFAULT GETUTCDATE(),
    [ItemPriority]      TINYINT          NOT NULL DEFAULT(0),
    [ItemRefreshTime]   DATETIME         NULL,
    -- lots of other columns
    CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
    ON [dbo].[JobItems]([ItemId] ASC);

CREATE INDEX [GetTaskToProcessIndex]
    ON [dbo].[JobItems]([ItemState], [ItemPriority], [ItemCreationTime])

and the following query:

SELECT TOP(1) ItemId FROM JobItems
WHERE ItemState = 5 OR
   ( ( ItemState = 11 ) AND ( DATEDIFF( SECOND, ItemRefreshTime, GETUTCDATE() ) > 14 ) )
ORDER BY ItemPriority ASC, ItemCreationTime ASC

I run this query and inspect the actual execution plan and here's what's going on:

Index seek is done to retrieve items with ItemState=5.
Index seek is done to retrieve items with ItemState=11 and then for each row a separate seek is done to retrieve ItemRefreshTime and the results of two seeks are filtered using nested loops.
Sets from 1 and 2 containing ItemId, ItemCreationTime and ItemPriority are concatenated and then...
Magical DistinctSort happens with ORDER BY ItemId ASC and finally
TopNSort happens with ORDER BY ItemPriority ASC, ItemCreationTime ASC

TopNSort and DistinctSort take something like 32 percent each so I'd be happy to get rid of DistinctSort - I don't even understand its purpose.

What's this magical TopNSort which useful DistinctSort and why is it there?


texts: could not connect to server: can't connect postgres on the server to postico on my local

texts:
when I connect postgres on the server to postico on my local I get this error
could not connect to server: Operation timed out
Is the server running on host "http://159.65.65.203/" (92.242.140.2) and accepting
TCP/IP connections on port 5432?

I could not find anywhere postgresql.conf and pg_hba.conf files, so i created the files, as you see in the below image

I found this explanation but still does not solve my issue
Ubuntu does not use the standard pg_ctl for postgreSQL. Instead, it
uses pg_ctlcluster.
That in turn controls the different PostgreSQL clusters. When you do
an install of a new cluster, pg_ctlcluster is smart enough to put
postgresql.conf & pg_hba.conf into separate dirs.
So to be specific, /usr/lib/tmpfiles.d/postgresql.conf is just a tmp
file that you can ingnore, /etc/postgresql/9.4/main/postgresql.conf is
the original version for the 9.4 cluster and
/var/lib/postgresql/9.4/main/postgresql.auto.conf is the actual, live
version of the 9.4 cluster that you need to change to affect the 9.4
cluster. Likewise for the pg_hba.conf.
reference https://www.postgresql.org/message-
id/CANu8FiygR5CdeqRTiE0YM+BQk6aK+qZyw2frH_FWV27J1exong@mail.gmail.com

here how i configured postgresql.conf
port=5432
listen_addresses='*'

here how i configured pg_hba.conf
 host    all             all              0.0.0.0/0                       md5
 host    all             all              ::/0                            md5

when i run
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program
name
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN
29761/postgres
tcp6       0      0 :::22                   :::*                    LISTEN      -

Clearly 5432 is not open for all connections.
when i make an api call using postman seems to work.
I use mac currently firewall not on, i have my server on digitalocean use Ubuntu 16.04.6 (LTS) x64.
This is the path of postgres files
postgres@borroupapi:~/9.5/main$

when i run  find / -name postgresql.conf i get this
/usr/lib/tmpfiles.d/postgresql.conf

but i cant find the path as logged in postgres user


texts:
I've got a test system which is creating and dropping databases. I'm getting pretty poor performance on a Ubuntu 18.04 machine with 26gb or RAM and many CPUs and SSD storage. It takes 15 seconds to drop a database that contains 350 mostly empty tables.

This is my config at the moment:

[mysqld]
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_file_per_table = 0
innodb-flush-method=nosync
innodb-flush-log-at-trx-commit=2

Once the database is dropped and recreated the 350 tables are created again, this takes some time too.

Would really appreciate some pointers to speed it up.

Thanks


texts: Build an Oracle query to get parent and child records

texts:
Table structure as below

col1| col2 | col3|
 1  | empty|empty|
 2  |  1   | Xyz |
 3  |  1   | abc |
 4  |  2   | abc |

Note Col2 is deciding parent record for child record.

Looking to get result if we search for where col3='abc' we will get results as:

1  | empty|empty|
2  |  1   | Xyz |
3  |  1   | abc |
4  |  2   | abc |

Table definition and sample data:

CREATE TABLE ForgeRock
(
    id int,
    Dependency varchar(7),
    Prod varchar(55),
    Prod2 varchar(22)
) ;

INSERT ALL
    INTO ForgeRock (id, Dependency, Prod, Prod2)
        VALUES (1, '', '','')
    INTO ForgeRock (id, Dependency, Prod, Prod2)
        VALUES (2, '1', 'Full','101')
    INTO ForgeRock (id, Dependency, Prod, Prod2)
        VALUES (3, '1', 'Robust','101')
    INTO ForgeRock (id, Dependency, Prod, Prod2)
        VALUES (4, '2', 'Robust','102')
SELECT * FROM dual ;


texts: SSMS Activity Monitor Expensive Query start time

texts:
I keep seeing rows in "Recent Expensive Queries" for queries that have not been run for hours.

This confuses me when trying to figure what is snarling things up.

Is there a way to add start time column to view - or somehow filter old queries out?


texts:
I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly.

Suppose having the following MySQL table:

CREATE TABLE IF NOT EXISTS `spec_99_4_1_attivita` (
    `id_attivita` int(10) unsigned NOT NULL DEFAULT '0',
    `id_gruppo` int(10) unsigned NOT NULL DEFAULT '0',
    `id_valutazione` int(10) unsigned NOT NULL DEFAULT '0',
    `id_azienda` int(10) unsigned NOT NULL DEFAULT '1',
    `id_sede` int(10) unsigned NOT NULL DEFAULT '1',
    `revisione_documento` int(10) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_attivita`,`id_gruppo`,`id_valutazione`,`id_azienda`,`id_sede`,`revisione_documento`),
    CONSTRAINT `fk_id_attivita_spec_99_4_1_attivita` FOREIGN KEY (`id_attivita`, `id_azienda`, `id_sede`, `revisione_documento`) REFERENCES `gen_12_1_aree_lavoro` (`id_area_lavoro`, `id_azienda`, `id_sede`, `revisione_documento`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_id_gruppo_spec_99_4_1_attivita` FOREIGN KEY (`id_gruppo`, `id_valutazione`, `id_azienda`, `id_sede`, `revisione_documento`) REFERENCES `spec_99_4_1_gruppi` (`id_gruppo`, `id_valutazione`, `id_azienda`, `id_sede`, `revisione_documento`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

would a query like:

SELECT * FROM spec_99_4_1_attivita WHERE id_azienda = 1 AND id_sede = 2 AND revisione_documento = 3 AND id_valutazione = 4 AND id_gruppo = 5

use the indexes, or would it require a full table scan?

And, more in general, how would you determine if a query is going to use indexes or not? Could you make some general examples for both cases?


texts: Microsoft SQL Server 2008 - Catching user role changes with SQL Server Profiler?

texts: I'm trying to figure out why one of my SQL server users keeps losing sysadmin role when I uninstall a particular program. Can I use the SQL Server Profiler to track down the action that causes this to happen? If so which events do I need to check off on the Events Selection screen?


texts:
I am maintaining a legacy system that uses Sybase ASE as the database. Because the system is a legacy system, the only way to create an SQL query is string concatenation (prepared statements are not possible*).
Is the below scheme sufficient for preventing SQL injection on "string" columns:

Replace all instances of a single quote with two single quotes.
Replace all instances of a double quote with two double quotes.

Keep in mind that the \ character is not an escape character in Sybase ASE. Also keep in mind that the columns in the selection clause and the tables in the from clause are static. The only thing that is dynamic is the where clause.
*Stored procs are not possible either, because string concatenation is needed to create the SQL query that calls the stored proc.


texts:
I have a system where my TCP/IP command prompt application receive data and insert that data in [database1].[mytable].

Now I want to move that data from [database1].[mytable] to [database2].[anothertable] when any new record insert in [database1].[mytable] without losing any single data.

And there are a approx 70/100 records insert at every 1 second in [database1].[mytable]. Now I want to move that data and have to do some calculation and then insert into [database2].[anothertable].

I have tried triggered and Stored procedures as well but it take too much database memory though sometime my db hangs when there are concurrent user try to connect.

So I have read and come up with solution like SQL Service  broker and SqlDependency with C# application.

So my questions are: can I rely on SQL Service broker or SqlDependency to achieve this? Because I do not want to lost/drop my any data as this are very important data.

Any suggestions?


texts:
I have installed a few versions of Oracle on my machine. I have tried 64 bit and 32 bit versions. I have installed 11G. I need Oracle 10G 32 bit.

Using the following guide I removed all versions of Oracle from my PC:

Uninstall all Oracle components using the Oracle Universal Installer (OUI).
Delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key which contains registry entries for all Oracle products by using regedit.
Delete any references to Oracle services/components in the following registry location: HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/. Looks for key entries that starts with “Ora” which are obviously related to Oracle.
Reboot the workstation.
Delete the ORACLE_BASE directory. (i.e C:\Oracle)
Delete the C:\Program Files\Oracle directory.
Empty the temp directory.
Empty the recycle bin.

http://www.mydigitallife.info/manual-and-clean-uninstall-oracle-for-windows/

I then installed Oracle 10G 32bit.

When I go to connect in PL/SQL Developer I get:

ORA-12154: TNS:could not resolve the connect identifier specified. (http://i.stack.imgur.com/UGpwD.png)

I have set up my Oracle Home and added a value for tns_admin. My TNSNAMES.ORA file is in the directory referenced by tns_admin.

I think my Oracle is picking up the TNSNAMES.ORA file as the drop-down list for Database in populated in PL/SQL Developer.

The only options I see at the moment are:

Uninstall and try again
Re-install Windows to make sure the settings are gone.

Any help you could give would be appreciated.


texts: I am looking for a way that I can backup my 12 MongoDB shards into a single server. Disk space is not an issue but getting the backups to be on a single node would be the best. I am not sure if there is a way to run more than one MongoDB server on a single machine or if there is a way to have all 12 shards merged into a single MongoDB instance. I am running on AWS and will have the primary data in US-East but want the backups in US-West. I dont mind if there is lag on the backup itself, but trying to avoid the app servers for waiting for the backup to be written.


texts: How to restore function using PG_RESTORE

texts:
I did the backup of my database:

-bash-4.2$ pg_dump -d mydb --format=custom -f /tmp/dump
-bash-4.2$

Below checked and I saw my functions there:

-bash-4.2$ pg_restore -l /tmp/dump | grep -i addconf
233; 1255 39949 FUNCTION public addconfiguration(text) mydb
247; 1255 39950 FUNCTION public addconfiguration2(text) mydb
273; 1255 39951 FUNCTION public addconfiguration3(text) mydb

Then I tried to restore one function using the Postgres pg_restore instructions:

-bash-4.2$ pg_restore -d postgres --function=addconfiguration2(text) -v /tmp/dump
-bash: syntax error near unexpected token `('

I also tried without the parentheses and the arg, but it didn't work:

-bash-4.2$ pg_restore -d postgres --function=addconfiguration2 -v /tmp/dump
pg_restore: connecting to database for restore
pg_restore: implied data-only restore

After the attempts I verified if the function was created using \df inside psql, but without success.

How could I restore functions using pg_restore?


texts:
I have a busy MySQL database with 1.6 million records a day, I'm looking to replicate this data to another server, then run a clean up job on production database to maintain small database in production.

I thought about Master / Slave setup, but I'm not sure how can I prevent deletes from replicating to slave.

I'm sure other BD admins / developers have run into same situation, I would appreciate any suggestion / recommendation

Thank you


texts:
We used to have one database per client, and we're moving to using a single database.
I need to find a way to merge the databases without losing data, including foreign keys.
My primary keys are all integer.
What I understood is that the only way to do this is to update all primary keys and all foreign keys on all but one database (so that there's no conflict), then import them in a new db.
Is this true?
If so, the only way I found is to manually set all my foreign keys to DEFERRABLE, then manually increment all my primary keys.
Is this true? Is there a way to automate it to ALL my primary and foreign keys?


texts: Should a partial index with WHERE NOT NULL be used if queries use a JDBC prepared statement?

texts:
Suppose we create a partial index in Postgres that avoids useless NULL data:

 CREATE INDEX my_ix ON my (col1) WHERE col1 IS NOT NULL;

Should I write the SELECT  query as:

SELECT * FROM my WHERE col1 = 'abc';

or as:

SELECT * FROM my WHERE col1 = 'abc' AND col1 IS NOT NULL;

In case of JDBC connection libraries it may look like this:

SELECT * FROM my WHERE col1 = ?;

and I am not sure if Postgres can infer index applicability without:

 AND col1 IS NOT NULL

Also it is interesting if Postgres can infer transitivity, for index:

 CREATE INDEX my_ix ON my (col1) WHERE col1 > 0;

I expect I don't need to write:

SELECT * FROM my WHERE col1 > ? AND col1 > 0;

if parameters are 10, 23, etc (> 0).


texts: Use STUFF function to insert at multiple points in a string?

texts:
Is there a way that I can replace a substring in SQL server while remaining 'aware' of what I'm replacing so that I can then re-insert that content?

I am building a string of column names to pivot on, and these column names need to be aggregated on in the query.

For example, I have this list of columns as an nvarchar(max) string:

[A], [B], [C]

And I would like to transform this string to look like this:

sum([A]) [A], sum([B]) [B], sum([C]) [C],

I have just found the stuff function but I don't know how to achieve what I want yet.

But I get the feeling that I can use it to insert substrings instead of using replacing substrings.

Looking at the stuff function:

STUFF( source_string, start, length, add_string )

How do you insert into multiple locations at once?

My current solution is this:

declare @cols nvarchar(max);
declare @colsAggregate nvarchar(max);
declare @sql nvarchar(max);

select @cols = stuff((
    select distinct ',' + quotename(sCode)
    from EntityList EL
    where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'),
1, 1, '');

select @colsAggregate = stuff((
    select distinct ', max(' + quotename(sCode) + ') ' + quotename(sCode)
    from EntityList EL
    where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'),
1, 1, '');


texts: How to reclaim disk space after delete without rebuilding table?

texts:
Our PostgreSQL 9.0 Windows production server is running low on space.

In our 100GB database, we have a large table containing TOASTed binary data. We have deleted some rows and need to return the space to the O/S.

We do not have enough space to do a full rewrite of the table, and all my reading of CLUSTER, VACUUM FULL and pg_repack is that they need to rewrite the table. My google-fu has been insufficient so far to find anything else.

Some downtime is acceptable (~2 hours), however the backup/restoration is too slow for our purposes, nor am I keen on dropping the database in between steps.

QUESTION: How can I return the disk space to the O/S without doing a full table rewrite?

(This is a production server so any solutions must be reputable/recommended/supported etc).

(P.S. A separate much larger disk is available which could be used, if the table could be rebuilt on it somehow. Tablespaces?)


texts: replicate-ignore-table setup and usage for excluding table from binlog backup

texts:
I need to make an everyday backup of a mysql database that is very big on a remote server and I only want to get the difference since the last backup. I also need to exclude some tables from being backed up.
I've setup the mysql server as a master, here is the important part of my.cnf i've changed :

log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = my_database

Then on the backup server i've setup mysql my.cnf like this :

replicate-ignore-table  = my_db_name.my_table_name

To test that everything is ok, I do the following:

On Master:

$ mysql -u root -p
MariaDB [my_database]> insert into my_table (param1, param2, ...) values(1, 1, ...)
MariaDB [my_database]> exit
$ scp /var/log/mysql/mysql-bin.00000X vagrant@backupserver:/tmp/

On Slave:

$ mysqlbinlog mysql-bin.00000X | mysql -u root -p

When I check the slave database I always see the new entry in the table, but that's not the expected result....

I must miss something, any idea ?


texts: Potential risks of users connecting to SQL Server, through Excel, as sysadmin?

texts: I recently discovered that a large swath of the finance department is using Excel to connect to my SQL Server 2000 instance with an account in the sysadmin role. What are my current risks that I should immediately communicate to the powers that be?


texts:
I always search my problem with different search terms but the problem I am facing now I can't express this in search keyword. So instead I came here to ask directly from humans as machines were unable to what I exactly means.

So coming to scenario:

I have database with having table_item and table_item_linked. table_item has many items. User come and add item(s). Later other user came and link one item with different item via a form with two dropdown.

What I did so far is:

Structure of `table_item`:
- item_id (Primary)
- others..

.

Structure of `table_item_linked`:
- linked_id (Primary)
- item_id (Foreign key referencing -> item_id of table_item)
- linked_items (here I need to store ids of linked items)
- linked_by (referencing to user_id of user_table)
- linked_timestamp (timestamp)

If I have items like:
A B C D E F G H

When I link D with G

I can successfully fetch G when I am fetching D or vice versa. But problem came when I

Link H with G

So I must fetch D H while fetching G.

It is like a multiple relation.

Guys I know there must be professional way to do it. I will like to have any guidance. I can even change my database structure.

PS:
Please don't suggest to add #tag as one item is exactly similar to the other linked.


texts: How to modify MySQL 5.5 settings that were set during the installation?

texts: Especially the one that defines how MySQL is gonna use server's CPU. I'd like to boost performance. Also, what are other ways to boost MySQL performance? (Maybe some code optimization hints?) Anyway. Any information will be useful. Thanks in advance.


texts: One case statement across multiple columns

texts:
I have a query in which I am executing the same case statement across multiple columns, i.e. something like this

select
case when (cond) then
     'Column1 Cond T'
else'Column1 Cond F'
end,
case when (cond) then
     'Column2 Cond T'
else'Column2 Cond F'
end,
from table

I would like to "merge" these two case statement into one so I have something like this:

 SELECT
 CASE WHEN (cond) then
     'Column1 Cond T', 'Column2 Cond T'
ELSE 'Column1 Cond F','Column2 Cond F'
END,
FROM table

However, I am getting a syntax error when I try running that. What is the correct syntax for this type of case statement?


texts: MongoDB Indexing - How many fields should I index?

texts:
I have a Collection in my db, called Post.

Here's how it looks:

Post {
    user: <EmbeddedObject>,
    school: <EmbeddedObject>,
    hashtag: <EmbeddedObject>,
    numberOfReports: <Number>,
    viewRanking: <Number>,
    type: <String>,
    isPollOfTheDay: <Bool>,
    createdAt: <Date>,
    endsAt: <Date>
}

(Obviously it also contains other, unrelated fields)

So this Post collection, is being queried by 5 different screens in my app. School, Hashtag, All Polls, Discover & Profile. All queries are very similar to each other, but they differ.

Let's have a look at them individually:

  School

Here, I have 2 queries

I compare by school.name (equal to), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order either by createdAt or viewRanking (depends on the user)
I compare by isPollOfTheDay (equal to true) and endsAt (greater than or equal to)

  Hashtag

I compare by hashtag.id (equal to), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order either by createdAt or viewRanking (depends on the user)

  All Polls

I compare by type (equal to), isPollOfTheDay (equal to false), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order either by createdAt or viewRanking (depends on the user)

  Discover

I compare by school.name (not equal to), type (equal to), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order by createdAt

  Profile

I compare by user.id (equal to) and we sort in descending order by createdAt

These are all of my queries! I can say that they are called almost with the same frequency. My question is, should I just index all 9 fields? Are they too many to index? Should I ignore the isPollOfTheDay field, since it's a Boolean? (I've read that we shouldn't index Booleans)

EDIT: Every document occupies about 200 bytes. We currently have 25K documents and growing in a pace of ~300/day.
The only fields that can change, are viewRanking and numberOfReports where the first one will change often, whereas the second far less often!

The selectivity of each query is high (I think), since the needed documents are found mainly by their first comparison. There are about 50 different school.names & another 50 hashtag.ids.


texts: How to I automate copying 1 Agent job from Instance A to Instance B?

texts:
I have a Always On cluster of 2012 through to 2019 instances. Don't want to over complicate using it for my Customers. I would like to be in a position of the customer creating 1 job through the listener on the primary. Then have a Automated process to copy them across to the secondary and DR. Without my intervention or their creating on each instance.

What is the best way of Automate ether in SQL or Powershell (or anything else i haven't thought of). Coping 1 Agent job from Instance 1 too Instance 2 without DBA or Customer intervention?


texts:
Wikipedia says that Read Committed is prone to non-repeatable reads. However, I can simply cache the first read result in my transaction (make a snapshot) and release the DB lock to let other transactions update the read rows. Effectively, I achieve repeated reads at the cost of RC isolation and, thus, higher performance than Repeatable Read offers. Nothing bad can happen and I can feel as safe as when using the Repeatable Read isolation, right? No, I guess that holding a read lock for the data I have read helps to prevent some undesirable scenarios in banking or booking reservations, probably. Which ones?

What I am looking for is an example where my emulation of RR fails. I have demonstrated that I can make the reads repeatable simply by caching the data item on its first read access. I want an example where my emulation is not sufficient. What is bad about releasing the lock immediately after reading under this strategy?

The Wikipedia article and the name Repeatable Read seem to imply to me that that is all what we need. But I suspect that Repeatable Read, by taking the shared lock, sacrifices performance for the sake of providing something more important than simple consistency of reads, since the latter can simply be achieved with a simple Read Committed + cache (snapshot) combination. I guess the reader locks persist for the whole transaction in 2PL and we have shared/read locks as in multiple readers/single-writer for a greater purpose than just to provide repeatable reads within a single transaction and, consequently, Repeatable Read is a misnomer that hides the fact of locking, which actually sacrifices the performance for something larger than consistent reads.


texts: Database backup hangs both in SSMS and XMLA

texts:
I am using SQL Server Analysis Services 2008 R2 Service Pack 3.  My database was recently upgraded from Analysis Services 2005 and since that time I have been unable to do a backup either from SSMS or by using XMLA - the process just hangs and says it's executing.   The backup should be very quick as the database is small (estimated size is 226mb).

I have to stop the service each time I try.

I've done a validate through BIDS and the database validates ok.

It is being run on a VM server with 8gb memory.

Any suggestions would be appreciated.


texts: Why would I NOT use the SQL Server option "optimize for ad hoc workloads"?

texts:
I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

Why is there even an option to "optimize for ad hoc workloads"? Shouldn't this always be on? Whether the developers are using ad-hoc SQL or not, why would you not have this option enabled on every instance that supports it (SQL 2008+), thereby reducing cache bloat?


texts:
This question is for a report I'm writing for school. Technically speaking, what is Psycopg2? In this tutorial they refer to it both as an "adapter" and "driver"

  Psycopg2 is a DB API 2.0 compliant PostgreSQL driver that is actively
  developed. It is designed for multi-threaded applications and manages
  its own connection pool. Other interesting features of the adapter are
  that if you are using the PostgreSQL array data type, Psycopg will
  automatically convert a result using that data type to a Python list.

Since it's imported into Python, I would've guest it was a library?

What's the distinction between library, driver and adapter?


texts: How to upgrade SQL Server 2008 R2 RTM to SQL Server 2012 SP3 on the same server?

texts:
We want to upgrade SQL Server 2008 R2 to SQL Server 2012 sp3 on the same server A. The SQL Server 2008 R2 is on Windows Server 2008 R2 Standard and it has six user databases.

First we will upgrade Windows Server R2 Standard to Windows Server 2012 R2 Standard. Then we will uninstall SQL Server 2008 R2 from server A.  We will install SQL Server 2012 SP3 on Server A and restore the backup of SQL Server 2008 R2 databases. I have script out the SQL Server Agent Jobs and linked server. So I can run after the restore.

There are only four logins. I script out the login too.

My question is: do I need to restore system databases too?


texts: Database Migration failed from SQL Server 2012 to 2016

texts:
After restoring backup of SQL Server 2012 to 2016 at the 100% completion it has failed and taken the database to recovery pending.

Able to do restore withe same backup of SQL Server 2012 to 2014 its successful and then tried to do from 2104 to 2016 but same error.

Error: 928, Severity: 20, State: 1.
During upgrade, database raised exception 211, severity 23, state 109, address 00007FFA50AB1225. Use the exception number to determine the cause.
Error: 211, Severity: 23, State: 109.
Corruption in database ID 7, object ID 3 possibly due to schema or catalog inconsistency.

Have done integrity check and catalog check on source.
Also have tried to do repair allow data loss after taking to emergency mode.
Have tried to detach and attach with single file. Even tried with rebuild log.
Got the same resources as of source server.


texts: Issue importing large file into SQL server

texts:
I am trying to import a 4gb csv file into a new table in sql server using the wizard. And when I do I get the errors below. I also included the 2nd row of the file I am trying to import without the headers. Any idea on how I can fix this?

  Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data
  conversion for column ""Other Provider Identifier Issuer_2"" returned
  status value 4 and status text "Text was truncated or one or more
  characters had no match in the target code page.".  (SQL Server Import
  and Export Wizard)

  Error 0xc020902a: Data Flow Task 1: The "output column ""Other
  Provider Identifier Issuer_2"" (466)" failed because truncation
  occurred, and the truncation row disposition on "output column ""Other
  Provider Identifier Issuer_2"" (466)" specifies failure on truncation.
  A truncation error occurred on the specified object of the specified
  component.  (SQL Server Import and Export Wizard)

  Error 0xc0202092: Data Flow Task 1: An error occurred while processing
  file
  "C:\Users\test\Desktop\NPPES_Data_Dissemination_June_2013\npidata_20050523-20130609.csv"
  on data row 79.  (SQL Server Import and Export Wizard)

  Error 0xc0047038: Data Flow Task 1: SSIS Error Code
  DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source
  - npidata_20050523-20130609_csv" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called
  PrimeOutput(). The meaning of the failure code is defined by the
  component, but the error is fatal and the pipeline stopped executing.
  There may be error messages posted before this with more information
  about the failure.  (SQL Server Import and Export Wizard)


texts: What is the `TABLE example` syntax called?

texts:
In PostgreSQL, I've been using syntax like
TABLE example1
UNION ALL
TABLE example2

which I understand to be equivalent to
SELECT * FROM example1
UNION ALL
SELECT * FROM example2

But a colleague asked me questions about this, and I can't find any PostgreSQL docs on this syntax. ("TABLE" isn't a helpful search phrase.) Where can I find documentation about it?
Is it ANSI SQL standard?

EDIT: It currently apparently even confuses StackExchanges's syntax highlighting.


texts: Is it possible to build an UNDO framework for postgres?

texts:
I was thinking of a table which would automatically log all transactions made to other tables and the command to undo that modifications. So every time you issue an UNDO() command you would read the statement from the log table and execute it.

Example:

You issue a

INSERT INTO sales (id, client_id, product, value)
VALUES (4621, 231, 'Hamburguer', 500)

so the log-trigger would do

INSERT INTO log (undo) VALUES ('DELETE FROM sales WHERE id = 4621')

When you issue UNDO() the function would issue something like EVAL(SELECT undo FROM log ORDER BY id DESC LIMIT 1).

Someone experienced, please tell me if this is possible.


texts: SQL Server 2008R2 minimum RAID/Disk config

texts:
I want to take advantage of the great price/performance offered by the new Intel E5-2620 (v2) machines being offered by some dedicated server webhosts.

Unfortunately most of these E5-2620v2 dedicated server offerings are a 1CU and limited to 4 drive bays. My production SQL 2008 R2 (2GB) database is supporting an auction web application with a very high frequency of small updates by many users.

I currently have an 8 drive configuration on my SQL Server with:
 - RAID 1 array (OS & Program files)
 - RAID 1 array (SQL log files)
 - RAID10 (SQL Data & TempDB)

My question is would I creating a disk performance issue for my OLTP SQL server  if I went with:
 1) A single 4 drive RAID 10 array (600 GB SAS drives with OS and all SQL Files). Would I be better off going with a much slower & more expensive machine that supports 8 drives?

2) I found one that offers 2 internal SSDs plus 4 hostswap drives, what would be the recommended disk layout. Is it still recommend to have SQL Logs on separate disk with high frequency of small db updates?
    2 Drive SDD RAID 1: OS & Temp files & SQL Log file
    4 Drive RAID 10: SQL DATA & System DB files, TempDB

3) Is there still a significant performance difference between SATA IIIs and SAS 15K disks as some wehosts only offer SATAs?

Many thanks in advance for any advice.
arf


texts: I have Microsoft SQL Server 2014 on an Azure VM (Windows Server 2012 R2) with one database. Is it possible to configure an automatic backup to my Google Drive folder? Maybe a special tool or somethings else?


texts:
I have sy_version table which has a 2-column primary key: (mod_id, sub_mod):

CREATE TABLE `sy_version` (
`mod_id` VARCHAR(2) NOT NULL,
`sub_mod` VARCHAR(30) NOT NULL,
`version` VARCHAR(50) NULL DEFAULT NULL,
`remark` VARCHAR(50) NULL DEFAULT NULL,
`update_date` DATETIME NULL DEFAULT NULL,
`download_link` VARCHAR(50) NULL DEFAULT NULL,
`file` BLOB NULL,
PRIMARY KEY (`mod_id`, `sub_mod`)
)COLLATE='utf8_general_ci'
ENGINE=MyISAM;

And my data is

CS;sbm_sl.exe;2015.11.01;IBS Sales App;2015-11-10 11:34:13;\N;0x73002E00000000

I want to update my data using insert into on duplicate key, for the first it is working fine. But everytime I executed I get my file column become NULL, result that I want is : file becomes NULL when version where I want to set is bigger the present(original) version. And the command is like below:

INSERT INTO sbm_sys.sy_version (mod_id,sub_mod,`version`,remark,update_date,file)
values ('CS','sbm_sl.exe','2015.11.07.1','IBS Sales App','2015-11-10 11:34:13', NULL)
ON DUPLICATE KEY UPDATE `version`='2015.11.07.1', remark='IBS Sales App',
update_date='2015-11-10 11:34:13', file=if(VALUES(version)>version,null,file) ;

It doesn't work fine, file column does not become NULL but the other columns is updated, so I tried changing the part become, and it works as I hoped:

file=if(VALUES(version)>version,null,file) ==> file=if(VALUES(version)>'2015.11.01',null,file) ;

The question is how I can't get "2015.01.01" from original version value?

Thanks for advice.


texts: How to get Postgres database name at Linux terminal

texts: I need to connect to a single postgres database but I dont know the name of the database. How can I list names of available databases from the Linux terminal ?


texts: I'm confused about High Availability/Always On

texts:
is High Availability something to describe Log Shipping and Always On ( replica )?

is Always on something to describe Log Shipping and High Availability?

I'm studying to get my certification 40-762 but I need to study a lot about these topics ( since i'm bad in this part on online tests ).

But even looking at Microsoft's website I'm confused about these topics. I know something about log shipping and replica, But I would like a point to study, and looking for "always on" and "high availability" not knowing what they are is a bit hard.

So, how can I describe them?

  The AlwaysOn Availability Groups feature is a high-availability and
  disaster-recovery solution that provides an enterprise-level
  alternative to database mirroring. Introduced in SQL Server 2012,
  AlwaysOn Availability Groups maximizes the availability of a set of
  user databases for an enterprise. An availability group supports a
  failover environment for a discrete set of user databases, known as
  availability databases, that fail over together. An availability group
  supports a set of read-write primary databases and one to eight sets
  of corresponding secondary databases. Optionally, secondary databases
  can be made available for read-only access and/or some backup
  operations.

And does "availability groups" is when i have log shipping and replicas in the same server?


texts: Is hosting the back end in the cloud supposed to significantly slow performance?

texts:
I moved my back-end from a local .accdb source to Microsoft's online Access App. Ever since then, forms have been taking more than 20 times longer to load. Is this normal? Or is there something wrong with my connection?

I'm using Access 2013.


texts: Is it possible to upgrade MongoDB from 2.0 to 2.4 directly

texts:
We have a replica set with 2 members and one arbiter in production. 10gen documentation states that 2.0 to 2.2 is a binary-compatible “drop-in" upgrade and so it's 2.2 to 2.4. Is it safe to upgrade from the 2.0 version directly to the 2.4 version?

This question was posted on stackoverflow and closed as offtopic. It was suggested to me that I try to ask it here.


texts:
I have a column family that store articles.
I'll need to get those articles from the most recent to the oldest, getting them from Country, and of course the ability to limit the number of fetched articles.

I though about another ColumnFamily "ArticlesByDateAndCountry" with dynamic columns

The Key would a mix from the 2 Char country Code (ISO 3166-1), and the articles day's date
so something like : US-20150118 or FR-20141230 -- (XX-YYYYMMDD)

In those Row, the column name would be the timeuuid of the article, and the value is the article's ID.

It would probably get a thousand of articles per day for each country.

Let's say I want to show only 100 of the newer articles, I'll get the today's articles, and if it does not fill the request (too few articles), I'll check the day before that, etc...

Is that the best practice, or does someone has a better idea for this purpose ?


texts: Populate nested associations using PostgreSQL

texts:
I have three tables in my PostgreSQL database:

user
address
city

My tables have self-associations like user -[ address -[ city where -[ represents a 1:n association.

I have a simple query:

SELECT
    *,
    ST_Distance_Sphere(ST_MakePoint(-22.4149023, -47.56513940000002), "geolocation") AS distance,
    row_to_json(addr.*) as address
FROM "user" as u
LEFT JOIN
(
    SELECT *, row_to_json(cty.*) as city
    FROM "address"
    LEFT JOIN "city" ON address.city_id = city.city_id
    WHERE user_id = u.user_id
) as addr
WHERE u.user_name LIKE '%Tod%'
ORDER BY distance ASC

How can I retrieve a nested object in one query, populating all columns of all tables to obtain a result like:

  {
    "userId": 148,
    "userType": 1,
    "userName": "Jimmy's Restaurante",
    "email": "jimmys6@jimmys.com",
    "phoneNumber": "588559944",
    "password": "$2a$10$q9j2O.240kXTxa91KgocD.FPZyUC847S8B9GcJ5fNqPE9jntZuH8y",
    "oppId": "1cd1f75f99c6606061996a2e5561a86b9b043210",
    "accessToken": null,
    "deviceToken": null,
    "addressId": 66,
    "dni": null,
    "birthDate": null,
    "status": false,
    "owner": "Jeremy Hopkins",
    "description": "This is the Jeremy Hopkins Restaurant, a.k.a         Jimmy's",
    ,
    "address": {
      "addressId": 66,
      "addressLine1": "Rua Dona Alexandrina 2450",
      "addressLine2": "Piso2 Dto.4",
      "postalCode": "13566-290",
      "city": {
        "cityId": 1,
        "provinceId": 1,
        "name": "La Plata"
      }
    },
    "distance": 263.67136658372544,
    "imageUrl": "http://cdn2.colorir.com/desenhos/pintar/loja-com-toldo_2.png"
  }

My database SQL structure:

User table

CREATE TABLE public."user"
(
  user_id integer NOT NULL DEFAULT nextval('user_user_id_seq'::regclass),
  user_name character varying(255) NOT NULL,
  email character varying(255) NOT NULL,
  user_type integer,
  phone_number character varying(255),
  password character varying(255) NOT NULL,
  opp_id integer,
  birth_date date,
  dni character varying(255),
  access_token character varying(255),
  device_token character varying(255),
  status boolean,
  owner character varying(255),
  cuit character varying(255),
  description character varying(255),
  geolocation geometry(Point),
  image_url character varying(255),
  pin character varying(255),
  "createdAt" timestamp with time zone NOT NULL,
  "updatedAt" timestamp with time zone NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY (user_id),
  CONSTRAINT user_email_key UNIQUE (email),
  CONSTRAINT user_phone_number_key UNIQUE (phone_number),
  CONSTRAINT user_user_name_key UNIQUE (user_name)
)

Address table

CREATE TABLE public.address
(
  id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
  description character varying(255),
  address_line_1 character varying(255),
  address_line_2 character varying(255),
  postal_code character varying(255),
  "createdAt" timestamp with time zone NOT NULL,
  "updatedAt" timestamp with time zone NOT NULL,
  city_id integer,
  user_id integer,
  CONSTRAINT address_pkey PRIMARY KEY (id),
  CONSTRAINT address_city_id_fkey FOREIGN KEY (city_id)
      REFERENCES public.city (city_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT address_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES public."user" (user_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL
)

City table

CREATE TABLE public.city
(
  city_id integer NOT NULL DEFAULT nextval('city_city_id_seq'::regclass),
  name character varying(255),
  "createdAt" timestamp with time zone NOT NULL,
  "updatedAt" timestamp with time zone NOT NULL,
  CONSTRAINT city_pkey PRIMARY KEY (city_id)
)


texts: loading a csv file which is on local system in to Mysql DB which is on remote server

texts:
Can we directly load a CSV file ( which is on the local system) on MYSQL DB ( which is installed on the Remote server ) ?

'load data infile into table name' command can only be used for loading in local system only.


texts: Make custom aggregate function easier to use (accept more input types without creating variants)

texts:
Recently I wrote a custom aggregate function in postgres that would return a specific column for the row that matches the max/min aggregate using a different column.

While the code in itself works great it is somewhat bothersome to create custom data type for every possible input combination that I might need.

Here is the code I use

CREATE TYPE agg_tuple_text AS
(
    exists boolean,
    value numeric,
    text text
);

--------------------------------------------------------------------------------

CREATE FUNCTION valued_min(old_tuple agg_tuple_text, new_value numeric, new_text text)
    RETURNS agg_tuple_text
    LANGUAGE plpgsql
AS $$
    BEGIN
        IF (old_tuple).exists = false THEN
            RETURN (true, new_value, new_text);
        ELSIF (old_tuple).value > new_value THEN
            RETURN (true, new_value, new_text);
        ELSE
            RETURN old_tuple;
        END IF;
    END;
$$;
--------------------------------------------------------------------------------

CREATE FUNCTION unpack_agg_tuple_text(value agg_tuple_text)
    RETURNS text
    LANGUAGE plpgsql
AS $$
BEGIN
    IF (value).exists = false THEN
        RETURN NULL;
    ELSE
        RETURN (value).text;
    END IF;
END
$$;

--------------------------------------------------------------------------------

CREATE AGGREGATE valued_min(numeric, text)
(
        INITCOND = '(false, 0, null)',
        STYPE = agg_tuple_text,
        SFUNC = valued_min,
        FINALFUNC = unpack_agg_tuple_text
);

--------------------------------------------------------------------------------

-- Example
SELECT min(value) as min_value, valued_min(value, name) as min_name, max..., avg... FROM kv;
-- Output:
-- min_value | min_name           | ...
-- ----------+--------------------+----
--     11.11 | this is the lowest | ...

EDIT: My goal is drawing a min/max/avg chart for a TSDB and displaying the name of the min and max entries each.

Is there a way to achieve this without creating all of these for every possible combination? (Maybe some kind of generic parameter that are present in Java or alike)

Value column types

Various Date/Time types
Numeric types
Maybe text
(any comparable type)

data column types

anytype

It would be sufficient if I only could use it for the data value since it isn't used in any calculation inside that code. Unfortunately the anyelement type isn't allowed in custom data types.

I already considered using the json type as input, but that feels somewhat wrong, because it looses the type information (especially for date/time types).

I use Postgres 10 without extensions, but if this is possible using postgres 1x or using a special extension I'm willing to try.

I also considered joining the values, but then I get isues with performance and potential duplicates/rows that have the same value.


texts:
I have a view, which selects data from TableA (TableA will be decommissioned once we migrated it all). We are creating a new table, called TABLEC, where we'll be migrating all the data from TableA to TableC in the future.

The reason we are doing this, it's because tableA is very old and bad designed.
At the moment, tableA is being used only for old records. All new data that would be inserted into tableA is already going to tableC via application.
I cannot change any table's schema.
The view can be changed to use CTE, IF, it is at least as fast as it's now.
path is not a real path, but it's a regular expression. It will be something like: /file/account/[0-9]+ <-- after this there are some possibilites, it can be several different words (such as: attach/assigned/etc).

While we don't migrate all the data from tableA to tableC, we need the view to be compatible with both tables, as there is already new data going to tableC.

View:

CREATE OR REPLACE VIEW path_view AS
  SELECT split_part(n1.path::text, '/'::text, 18)::integer AS id,
         split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
         lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
         '00000000-1000-1000-3000-600000000000'::uuid AS guid,
         n1.bytes AS byte_count,
         n1.last_modified AS last_modified
  FROM   tablea n1
  JOIN   tableb s2
  ON     s2.path = n1.path;

-- tablea and tableb are pretty much the same; as said before, it was bad designed.

I need the view to do:

Look if the requested data is in the new table tableC
IF NOT, then go to tableA and get the data

QUESTION:

How can I do that? I tried with UNION ALL but it's very slow.

\d tableC:

Table "public.tablec"
--------------------+-----------------------------+-------------------------------------------------------------------
id            | integer                     | not null default nextval('tablec_id_seq'::regclass)
e_type        | integer                     | not null
e_id          | bigint                      |
e_variation   | character varying(16)       | not null
path          | character varying(255)      | not null
name          | character varying(255)      | not null
size          | bigint                      | not null
md5           | md5_hash                    | not null
modified_date | timestamp without time zone | default statement_timestamp()
created_date  | timestamp without time zone | default statement_timestamp()
clientid      | bigint                      | not null
f_id          | bigint                      |


texts: Getting Error: invalid UTF-8 sequence - bad continuation byte

texts:
I'm inserting into a table from an external csv file and one of the fields in that file has non-ascii characters. An example of the character is this: É.

Here is my insert statement:

INSERT INTO SCHEMA.TABLE
    SELECT * FROM
    EXTERNAL 'C:\MyCSVFolder\external_table.csv'
    USING (
        DELIMITER '|'
        Y2BASE 2000
        ENCODING 'internal'
        REMOTESOURCE 'ODBC'
        ESCAPECHAR '\'
        MAXERRORS 10
        LOGDIR 'C:\MyLogFolder'
    );

When I look through my log file, I notice that the operation gets cancelled because values in one of my fields has an É. The error is: invalid UTF-8 sequence - bad continuation byte

How can I solve this? I can't change my csv file since it is a huge file and all values should remain the same. Any help would be greatly appreciated!


texts: How Can Get Random 2 rows FROM EACH GROUP FROM JOINED TABLE


texts: Best Way to Index Multiple Effective Date Columns

texts:
I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an EndDate column. I now need to add another dimension of start and end dates, meaning that the relationship can be viewed with two different date "lenses" (queried with two dates, @Date1 and @Date2), so the schema will look something like this:

MyJoinTable:

| Id | Entity1Id | Entity2Id | StartDate1 |  EndDate1  | StartDate2 |  EndDate2  |
|----|-----------|-----------|------------|------------|------------|------------|
|  1 | A         | B         | 1753-01-01 | 2018-09-01 | 1753-01-01 | 2025-01-01 |
|  2 | A         | B         | 2018-09-01 | 2018-10-01 | 1753-01-01 | 2018-11-01 |
|  3 | A         | C         | 2018-09-01 | 2018-10-01 | 2018-11-01 | 2025-01-01 |
|  4 | A         | B         | 2018-10-01 | 2025-01-01 | 1753-01-01 | 2018-11-01 |
|  5 | A         | D         | 2018-10-01 | 2025-10-01 | 2018-11-01 | 2025-01-01 |

The queries will mostly be joins to this table, for example:

SELECT e1.Field, e2.Field
FROM Entity1 e1
INNER JOIN MyJoinTable jt ON jt.Entity1Id = e1.Id
    AND StartDate1 <= @Date1 AND EndDate1 > @Date1
    AND StartDate2 <= @Date2 and EndDate2 > @Date2
INNER JOIN Entity2 e2 ON e2.Id = jt.Entity2Id

My questions are:

What's the best way to index this join table?

Index on Entity1Id
Index on Entity2Id
Composite Index on all four date columns? (StartDate1, EndDate1, StartDate2, EndDate2)

What's the best way to constrain the DB so that I only get one relationship row returned for any @Date1, @Date2 combination?
Do you have any recommendations on a better data model?


texts: How can I find out if any cluster member is offline in mongodb cluster?

texts:
I have a mongodb cluster with 1 master machine(running mongos and config service) and 3 slave machines(used as data store).But now,I find that one of 3 machines (192.168.10.11) is down because I can neither  ping to it nor telnet to it.Of cource,only one machine is down will not lead to data loss because each replication set has three replication members which is distributed to three different machines. I select a database and run db.stats(),I cannot see anything wrong ,every shard is there.I use db.runCommand({listShards:1}),I cannot see anything wrong either,no shard is wrong.

So , my question is ,how can I find out any exception message from mongodb if any of cluster members is down or offline?


texts: Pgpool - semanage config files - which filetype should I choose?

texts:
Reffering to problem with pgpool installation :
Problem with pgpoolAdmin installation
I had a problem with non running pgpooladmin(installation files permission was wrong).
I must change file label of files: "pgpool.conf" and "pcp.conf". I checked those files with ls -Z and saw this:

[root@cvc etc]# ls -Z
-rw-rw-rw-. apache root unconfined_u:object_r:etc_t:s0   pcp.conf
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0       pcp.conf.sample
-rw-rw-rw-. apache root unconfined_u:object_r:etc_t:s0   pgpool.conf
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0   pgpool.conf.sample
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0 pgpool.conf.sample-master-slave
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0  pgpool.conf.sample-replication
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0   pgpool.conf.sample-stream
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0   pool_hba.conf.sample

Communicate from SELinux tolds:

You should change label of pgpool.conf with
semanage fcontext -a -t FILE_TYPE 'pgpool.conf',
which FILE_TYPE is one of below:
squirrelmail_spool_t,
dirsrvadmin_config_t,
afs_cache_t, httpd_t,
user_cron_spool_t,
httpd_lock_t,
dirsrv_config_t,
httpd_tmp_t,
dirsrvadmin_tmp_t,
httpd_cache_t,
httpd_tmpfs_t,
httpd_squirrelmail_t,
puppet_tmp_t,
dirsrv_var_log_t,
zarafa_var_lib_t,
dirsrv_var_run_t,
httpd_var_lib_t,
httpd_var_run_t,
httpd_nagios_rw_content_t,
passenger_tmp_t,
httpd_nutups_cgi_rw_content_t,
httpd_apcupsd_cgi_rw_content_t,
httpd_dspam_rw_content_t,
httpd_mediawiki_rw_content_t,
httpd_squid_rw_content_t,
httpd_prewikka_rw_content_t,
httpd_smokeping_cgi_rw_content_t,
passenger_var_run_t,
httpd_openshift_rw_content_t,
httpd_dirsrvadmin_rw_content_t,
httpd_w3c_validator_rw_content_t,
cluster_var_lib_t,
cluster_var_run_t,
httpd_user_rw_content_t,
httpd_awstats_rw_content_t,
root_t,
httpdcontent,
httpd_cobbler_rw_content_t,
httpd_munin_rw_content_t,
cluster_conf_t,
cluster_tmp_t,
httpd_bugzilla_rw_content_t,
httpd_cvs_rw_content_t,
httpd_git_rw_content_t,
httpd_sys_rw_content_t,
httpd_sys_rw_content_t
Next you have to execute:
restorecon -v 'pgpool.conf'

When I try to change filetype it tolds me that choosen one is wrong... which filetype should I choose?


texts: postgresql connection with pgadmin4 shows all databases on the same domain

texts: I have created 2 postgresql databases, 1 in the main domain and another one in the subdomain of the same domain. they both have different username and passwords. I have logged in with pgadmin 4 to subdomain database but  i was surprised to see that i saw both databases in the domain. is this normal or i need to create different user privilages?


texts: How to specify “partition (partx)” in “mysqldump” command?

texts:
I have to export some data from a table which is partitioned, and with some extra condition
So I use mysqldump command,and use --where"xxxxxx" options as below

mysqldump -hxxx -Pxxx -uxxx xxx rtm --tables xxx --where="xxx"> xxx

But how can I specify partition (partx) keyword in mysqldump command，since I just need the data from the specific partition.


texts: Applying normalization to a relational Database (Access & SQL Server)

texts:
This question is pretty specific but I hope it can get answered on here. There is a wide range of information about normalization but I feel what I have read so far isn't as effective as just stating my situation.

I'm having trouble discerning what NF my tables are and applying normalization principles to my model. Also whether some tables should be left in 2NF and not 3NF, if it even exists and for what reason. I'm having difficulty transitioning from creating a regular database and normalizing the whole thing.

I believe the tables I have attached are all that is needed.

I will be building this in both Access and SQL Server, with that said I will be creating a form for making bookings and billings from Access that should be able to be input into the database.

Side question: I have been asked to create a report for a profit and loss statement, while this can be done in Excel is it possible to make a table in the database?

Thank you and please feel free to ask any questions relating to the topic

I have been asked to setup and ODBC driver to link with Access.

EDIT: I have decided that the BIKE table be renamed to BIKE_HIRE and have a BIKE table with the actual details of the bike itself. Also to create the Income/Expense table, have it linked to the PAYROLL table. If anything else needs to be normalized please suggest it.

EDIT 2: Specifications: This may give further insight into the database, but I'll be working on it with the help, thanks !

The business is a small 5 room guest house home stay.
They have motorbike and car rentals and propose that only motorbike rentals be recorded in the database.
There is little staff which duties along with the manager are housekeeper, guest liaison, housekeeping, accounts.
Drivers are paid on the day and don't need to be included on the bill or database. (Details may be necessary)
Guests are given a form to fill for their details when they enter.
Proposed that payments have different rates depending on days.
Client wants to be able to record expenses and income

Based on this, is it possible that maybe some entities are better left as 2NF?

Edit3: The solution for my case was simpler than possible. I left Payment unnormalised, and attached an Expense table onto Payroll, this was all I needed to do as this was a sample .beginner project for me