texts: Creating many-to-many relation on demand?

texts:
Users can join projects.

Projects can have many tasks.

Each user completes the task at their own time, they can also have some other user specific settings on the task like the priority in their todo list and whether to hide it on completion or not.

The problem I'm facing is that some of these settings must have default values e.g. priority = 0, hideOnComplete = true.

These must be available to the client for each project user at all times. How do I make that the TaskUser rows with the default values for the each user exist at all times?

Option A:

I create a TaskUser relation for every user in the project each time a new task is added to the project.
I create a new TaskUser relation for each task in the project every time a new user joins the project.

I don't think this is the way to go since you have to be reacting to these events all the time and acting in response.

Option B:

Duplicate the default fields in the Task table. I include a priority and hideOnComplete field in the Task table. The client can then know what what to display on Task setting forms even if the TaskUser relation doesn't exist.

I no longer have to react to new users or tasks. A client can request the task object and populate the settings form with the default fields even when there is no TaskUser relation. Afterwards, when the users submits his own settings the client can look into the existing TaskUser relation for the correct values.

This seems to be a much better option to me. What do you think?


texts: Optimizing query for large number of rows in mysql

texts:
I have the following database table in MySQL :

and is running the following query

SELECT `scratch_tickets`.`id` AS ticket_id, `scratch_tickets`.`created_at`, `scratch_tickets`.`prize_type`, `scratch_tickets`.`prize`, `scratch_tickets`.`is_win`, `scratch_tickets`.`game_id` FROM (`scratch_tickets`)  WHERE `scratch_tickets`.`created_at` >='2015-02-12 00:00:00' and created_at<='2015-02-18 23:59:59' AND `scratch_tickets`.`status` = 4 LIMIT 50;

Now the EXPLAIN is giving following results

The query is taking almost 35 seconds to execute and when I use this with PHP, it is getting timed out. Here I think the indexes being utilized. How can I optimize this for a better performance ?

The table already have 25708614 records.


texts:
I'm just curious. I was trying to optimize an update statement with a function

Ex

update circle set area = fnGetArea(radius) where ...<bunch of conditions>

This update went 5-10mins and when I removed the fnGetArea, it went 5-10 seconds!

How did that happen...?

I want to google it but I'm not sure what to type in.

Please enlighten me! :D

Thanks in advance


texts: Cannot add Network Service as User/Login

texts:
I want to setup ProGet on a 2016 server with SQL Express and IIS.

Both the service and IIS web runs under the Network Service user. None seem to be able to access the Database on a local SQL Express instance.

The IIS website tells me that the login WORKGROUP\PACKAGE$ (where PACKAGE is the servers name) cannot access the database:

I understand that I need to add the Network Service (which IIS uses to actually authenticate with SQL Express) as a login/user to the database.

When I tried adding the NT-Authority\Network Service as a login/user to the database, I get an error that tells me that Windows cannot find any authentication for the Network Service.

How can this be? Why can't SQL Express find the Network Service?


texts: Is it possible to store and query JSON in SQLite?

texts:
I need to store JSON objects in a SQLite database,
and then do complex queries on it.

I did a table like this:

+--------------------------------------+
|document |  property | string | number|
+--------------------------------------+
|foo      |  "title"  | "test" |       |
+--------------------------------------+
|foo      |  "id"     |        |  42   |
+--------------------------------------+
|bar      |  "id"     |        |  43   |
+--------------------------------------+

for the two objects

foo {"title": "test", "id": 42}
bar {id: 43}

But I can't do "AND" queries, like:

SELECT DISTINCT id  FROM table WHERE title = "test" AND id = 42

as you see, the part after the "WHERE" is total nonsense,
but I have no idea of how to create a query that would do what I want.

So do you think there is a better way to store my data,
or a workaround  to do an "AND" query?

And of course, the JSON can contain any property,
so I can't create a table with columns for each properties.

I am using WebSQL, which is SQLite without extensions.

I know my question is pretty specific, but could you help me?


texts: Pivoting row contents to produce one dimensional result

texts:
I have a table with a varchar column containing pipe-delimited values

For example:

Row 1 Column 1 = a|b|e|gg|foo
Row 2 Column 1 = oV|foo|do
Row 3 Column 1 = boop

How can I query this to return something like? Duplicates are ok.

Results:

column
a
b
e
gg
foo
foo
oV
do
boop

I realize this is not optimal but my hands are tied with the current schema


texts: Trigger - Add to a date using a subquery

texts:
(postgresql 9.2)
I'v 2 tables

Table 1 (extinguisher)

extinguisher_id (serial)
type_designation_extinguisher_type(text)
manufacturing_date (date)
life_date(date)

Table 2 (extinguisher_designation)

extinguihser_type(serial)
extinguisher_designation(text)
lifespan(int)

lifespan can take values like: 10 or 20 and it represents years

My objective is to do a trigger that will automatically populate table1.life_date based on the extinguisher_type using table2.

The problem is that lifespan is an int and can change.
The usual way to add year to a date is using
date + interval '10 year'

The problem is that I want a variable instead of the 10.

At this point i'v this:

CREATE OR REPLACE FUNCTION ext_life_span()

RETURNS trigger AS

$BODY$

DECLARE
new_date timestamptz;
manu_date char(10);
intervalo char(10);
BEGIN
IF (TG_OP = 'INSERT') THEN
intervalo := to_char((SELECT lifespan FROM extinguisher_type, extinguisher where type_designation = type_designation_extinguisher_type), '99999');
manu_date := to_char(New.manufacturing_date, 'YYYY-MM-DD');
new_date := (date 'manu_date' + integer 'intervalo' );
update extinguisher set life_date = new_date;
RETURN NULL;
ELSIF (TG_OP = 'UPDATE') THEN
IF new.manufacturing_date IS DISTINCT FROM old.manufacturing_date THEN
intervalo := to_char((SELECT lifespan FROM extinguisher_type, extinguisher where type_designation = type_designation_extinguisher_type), '99999');
manu_date := to_char(New.manufacturing_date, 'YYYY-MM-DD');
new_date := (date 'manu_date' + integer 'intervalo' );
update extinguisher set life_date = new_date;
END IF;
RETURN NULL;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION ext_life_span() OWNER TO postgres;

CREATE TRIGGER insert_lifespan
AFTER INSERT OR UPDATE OF manufacturing_date
ON "extinguisher"
FOR EACH ROW
EXECUTE PROCEDURE ext_life_span();

I'm getting an error: invalid input syntax for type date: "menu_date"

Am I doing the best approach? Is there an easier way to do it?


texts: Creating index on large MySQL table taking forever

texts:
My table has 981 million rows and is about 30GB large. It is six columns: ID, Ticker, Date, Time, Price, Volume. The data was in text files and using LOAD DATA INFILE was actually quite painless and only took a couple hours. However, each select statement takes 5 minutes now because of the number of rows.

I want to create an index on Ticker, Date, Time. I used the following command:

create index tickerdatetime on kibot (Ticker,Date,Time);

It is now been over eight hours and it is still working. My machine is ok (PC, 3.40GHz, quad core, 12GB RAM). Two questions:

Is there a better (ie faster) way of doing this?
Is there any way I can know what percentage is done? Am I 10% done or 80% done?


texts: Can't create schema for a db with PostgreSQL

texts:
Use psql -U postgres to login.
postgres=> CREATE DATABASE mydb;
postgres=> \c mydb;

You are now connected to database "mydb" as user "postgres".

When create schema, got
mydb=> CREATE SCHEMA main;
ERROR:  permission denied for database mydb

What will be the possible denied permission that didn't set?
Edit
When I run \l, it showed
   Name    |        Owner         | Encoding |   Collate   |    Ctype    |               Access privileges
-----------+----------------------+----------+-------------+-------------+-----------------------------------------------
 mydb      | mydb_administrator   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/mydb_administrator                     +
           |                      |          |             |             | mydb_administrator=CTc/mydb_administrator
 postgres  | postgres             | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 ...

\du got
      Role name       |                         Attributes                         |                          Member of
----------------------+------------------------------------------------------------+-------------------------------------------------------------
 mydb_administrator   |                                                            | {rds_iam}
 mydb_application     | Cannot login                                               | {}
 mydb_user            |                                                            | {rds_iam,mydb_application}
 postgres             | Create role, Create DB                                    +| {rds_superuser}
                      | Password valid until infinity                              |
 ...

All the things were executed by postgres user.


texts:
I wanted to extract some information from the server trace file that is generated after I send a connect statement to the database. I wanted to know the naming convention for the server trace file. I could figure out that the name is in the below format:

<ORACLE_SID>_ORA_<4_digit_ID>.trc

I wanted to know what is the 4_digit_id that is appended at the end of the trace. As per my research it seems to the process id of the oracle server. I queried the v$process for the pid column, however it seems to be returning multiple values and that too are not in 4 digit. It would be really appreciated if an expert can comment on this.

Is there a way to identify the spid of a failed login attempt? For instance I would like to know the spid for the process when I issue the below command:

sqlplus non_existent_user/random_password

BTW, the server trace is enabled through sqlnet.ora where trace_level_server=16 is set.


texts: Understanding Variations on Multiple Joins

texts:
I have two versions of a multiple join which produce identical results. The five tables are:
customers ← sales ← saleitems → paintings → artists

The arrows (hopefully) show the relationship between the tables.
Each table has a primary key called id, and the inner tables have a foreign key to another table called [othertable]id.
The first version is a classic join with the table listed in the above order
SELECT
    c.id, c.givenname, c.familyname,
    a.givenname, a.familyname
FROM
    customers c
        JOIN sales s ON c.id=s.customerid
            JOIN saleitems si ON s.id=si.saleid
                JOIN paintings p ON si.paintingid=p.id
                    JOIN artists a ON p.artistid=a.id

;
The second version jas all the JOIN clauses first, and the the ON clauses, in the reverse order.
SELECT
    c.id, c.givenname, c.familyname,
    a.givenname, a.familyname
FROM
    customers c
        JOIN sales s
            JOIN saleitems si
                JOIN paintings p
                    JOIN artists a
                    ON p.artistid=a.id
                ON si.paintingid=p.id
            ON s.id=si.saleid
        ON c.id=s.customerid

;
OK, so it works, but can any one explain how the second version works? Why must the order of the ON clauses be the reverse of the JOIN clauses? Is it possible to randomly order the JOIN or ON clauses?
I have tested this in Microsoft SQL as well as with PostgreSQL.


texts: Get current table name trigger was fired on

texts:
My system: Db2 11.1 on Linux.
1.Create some table:
create table admin.tab (a int not null primary key, b int) @

2.Create audit table:
create table admin.audit (table_name varchar(2000), datetime timestamp) @

3.Very very simple sample, when insert into source table admin.tab I would like to insert into admin.audit:
create trigger admin.mytrig
after insert on admin.tab
referencing new as new
for each row
begin
    insert into admin.audit (table_name, datetime)
       values ('admin.tab', current_timestamp);
end
@

4.Above trigger works fine, but is there any way in above trigger I can replace 'admin.tab' constant with current table name that was fired using trigger, like CURRENT_TABLE_TRIGGER_WAS_FIRED_ON or something?
create trigger admin.mytrig
after insert on admin.tab
referencing new as new
for each row
begin
    insert into admin.audit (table_name, datetime)
       values (CURRENT_TABLE_TRIGGER_WAS_FIRED_ON, current_timestamp);
end
@

I need this kind of solution, because I need to create hundreds of such triggers and to share as much code as possible saves a lot of time.


texts: Does mysql horizontal partitioning create new ibd files for each partition?

texts:
To my best knowledge, each table created in mysql creates an ibd file as a pointer to your table. Any alteration made to this table happens on this ibd file.
Now if Table1 of size 1GB, with its ibd file x.ibd, is horizontally partitioned equally into p1,p2,p3,p4. Do the new partitions p1,p2,p3 and p4 having equal size around 250MB each have their own ibd files? If no, how can we do it?


texts: Is the use of my lookup table efficient?

texts:
I'm trying to find the best way to architect the relationship in the scenario that:
A team can have multiple players, a player may also have multiple teams

My first thought was to use a lookup table to set the players whom belong to that team, but is there a more efficient way I could define this relationship?
I would normally provide a foreign key from the Player table to the Team table, but given a single player can be in multiple teams, I think it makes sense to create a lookup table between the two; below is a simplified skeleton of the schema I am planning:
Table: Player
-------------
p_id       PK
p_name

Table: Team
-------------
t_id       PK
t_name

Table: Roster
-------------
r_id       PK
t_id       FK
p_id       FK

I have normally been able to model my databases without the need of a look-up table, but in this use case I have a gut feeling that it makes sense to implement one.  Any advice would be appreciated.
EDIT My only concern with this implementation, is that when drawn on paper the cardinalities between Roster and Team don't make sense:
multiple teams can have one roster = no, nobody shares a roster...
multiple rosters can belong to one team = no, the team is set, players aren't substituted.


texts: Aborted restoring from back up Sql Server 2008

texts:
I started restoring from backup using (WITH REPLACE, RESTORE WITH RECOVERY)
and realized that it was a mistake, so I stopped restoring.

Now this db is unavailable and has a 'Restoring' status.

Can I expect full rollback?
What should I do?


texts:
I have 2 stored procedures (one vendor, one third party) that are constantly (monthly) stepping on each other.  They both (for some reason) acquire a schema lock when all they are doing is updating tables in the database.  As this is a vendor database, there is only one schema.  When these two calls occur at about the same time...down goes the database.  There is nothing I can do to kill these processes and everytime I have to recycle the SQL Server to get the system back up again.

What calls in T-SQL would force a Schema Lock?  I am skimming through the code to try and find the area that is the culprit, but nothing is jumping out at me.  Are there calls that would create a Schema Lock that you wouldn't expect would?

Also...should 2 different calls be able to acquire a Schema Lock at the same time?  I can say with great confidence that everytime this happens, it's when these two SP's are called within a second of each other.


texts: Could not load replication libraries | Unable to load one or more of the requested types - DBATools - Test-DbaRepLatency

texts:
I get the below message when trying to check replication latency on a couple of servers. From others, I seem to be able to run completely fine without any issues (run from, not against).
I have checked and the DLLs do appear in the correct location.
The servers which have the issues are running OS Windows Server 2016 and 2012, and those which are working are Windows Server 2019 and Windows 2010. The dbatools build version is 1.0.121.and Powershell version is 5.1.14409.1018


texts: MySQL performance degradation with heavy inserts

texts:
I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (insert/updates) start to get more and more slower. When the queries get slower, the throughput goes down and the database gets fewer inserts/updates. This causes the database to get fast again, and then we're stuck in a loop.

I can see the IOPS and CPU going up together, however, they don't go up more than 50%, which makes me imagine that I'm underusing the database and that there's some config that could be changed or that's causing it.

This can be seen/illustrated in the graph below:

I'm using the Memory Optimized Azure MySQL with 32vCPU and I have around 900 active connections and here is some important configs I have set currently:

innodb_buffer_pool_size : 80% of avail memory
io_threads (read/write) : 64
innodb_log_buffer_size: 1Gb
innodb_io_capacity: 5000
innodb_io_capacity_max: 9000
query_cache_size: 0
innodb_change_buffer_max_size: 50
table_open_cache: 40000
table_open_cache_instances: 8
innodb_lru_scan_depth: 8192
innodb_max_dirty_pages_pct: 65

Here's the INNOB Status for a high throughput period : SHOW ENGINE INNODB STATUS

I'm not sure what other configs I should be looking into to use the max of my CPU/IOPS and would love some tips on what to look into.


texts: How to set up where to store data in SQL Server

texts:
My C:\ drive has a size of 99GB and my D:\ drive has a size of 2TB

Here comes the problem: where does SQL Server store data in the database? Can I control it?

Actually, my C:\ has been full of data now..... and I don't know how to deal with it.

I want to configure SQL Server right so that I can store data on D:\

Thank you very much.


texts: Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?

texts:
Way back when Michael Jackson was still alive, I made this question:

https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause

And now I'm performance-auditing a system in which the developer used the first form, instead of an INNER JOIN. The DBMS is Oracle 10.2.0.4 on Solaris/SPARC. I could, of course, recommend the change in syntax solely because it's better form, but if it won't positively impact performance it's kind of pointless.

So, my question is: is it possible there will be a change in query execution if I ask them to change the query syntax to INNER JOIN?


texts:
I have read the following post How do you effectively model inheritance in a database? about inheritance in databases. Unfortunately I could not find anything in the PostgreSQL books from Packt.  And now I'm a bit confused if my approach is an overkill and doesn't even belong in the database or makes sense (see my considerations at the end).
First I wanted to implement the following example using Table-Per-Concrete. But then I read several times that you should only do this when you really have performance problems and you should use table per type as long as you have no performance problems.
So the following relations are given:
system_user (system_user_id, type, first_name, last_name, ...)

user (system_user_id) *Has no unique attributes but special relations accessible only for him, e.g. orders
customer (system_user_id, company_name, ...)
supporter (system_user_id, nickname, ...)

So far so good, but I was wondering how to ensure in the above example that:

The type cannot be changed.
A system_user has at most one entry in an extension.

My consideration:

A trigger that throws an error if the type is changed during an UPDATE.
One trigger for each extension, which checks before an INSERT, if an entry with this system_user_id already exists in another extension.

Does this make sense? Or am I going about it the wrong way? Is this even necessary? Should I create a separate relation for each entity directly?
Maybe I have too much of an object-oriented software developer view on it. Thanks a lot for your answers.


texts: Is there a way to add disk space to a Postgres instance hosted on an EC2 server?

texts:
I have a machine that has 512 GB disk space on which I am running a Postgres instance. The problem is that the disk is almost full and I need to increase the size of the available disk space.

I know I could backup the disk as snapshot and then use that snapshot to create a new volume with more size and attach it to the original disk. But this takes too long and I do not want to be doing it as I can not afford the downtime.

Is there a way I can bring up another machine and then somehow tell the postgres to use disk space from that server for this database?


texts: Re-adding constraint after ALTER fails on row that exists

texts:
I'm getting a failure trying to add a constraint that previously existed on a table after having dropped it to do an alter:

mcqueen=# ALTER TABLE ONLY public.mcqueen_base_imagemeta2
mcqueen-#     ADD CONSTRAINT mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b FOREIGN KEY (image_id) REFERENCES public.mcqueen_base_image(id) DEFERRABLE INITIALLY DEFERRED;
ERROR:  insert or update on table "mcqueen_base_imagemeta2" violates foreign key constraint "mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b"
DETAIL:  Key (image_id)=(5648463223) is not present in table "mcqueen_base_image".

I can select that image_id in the both referenced tables and the rows do exist.

I tried changing the PK on the row in mcqueen_base_image to a difference one and back and even deleting and re-adding the row, but neither worked. I finally deleted the row, but then failed on another. Since this table has some 7 billion rows, waiting 2 hours for each failure and deleting that row is not a workable solution.

Is there anything I can do to "repair" this issue?

UPDATE: The root issue ended up being some corruption in our backup restore process, so the test DB was just plain corrupt, so no re-indexing or even reloading of tables helped the issue.


texts: Does adherence to the DRY principle justify dynamic SQL?

texts:
Suppose I have two queries that will be frequently run against my database:

SELECT
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserID = @User

SELECT
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserName LIKE @Name + '%

Should they be in two separate stored procedures, or just a single one which creates the statements dynamically using sp_executesql?

If they are in two stored procedures, then I will need to modify both procedures if I ever want to add or remove a column in the SELECT statement. And if I use dynamic SQL then presumably I am sacrificing a small amount of performance.

Is this a case where maintainability and adherence to the DRY principle (don't repeat yourself) by using dynamic SQL would take precedence over the performance gain of a stored procedure?


texts: SELECT COUNT in same table based on current row value within a single query


texts: How to dispose of large tables with the least impact to log shipping?

texts: Have two semi large related tables (284,6876,609 rows - 156,785,176 KB and 4,2628,162 rows - 2,212,464 KB) in a log shipped db that needs to be either purged or dropped.   How can I dispose of these two tables while minimizing impact to log shipping?


texts: How to decrease RAM usage of SQL Server 2012 without restarting Service

texts:
Is there a way of decreasing RAM usage of SQL Server 2012 without restarting it?

Setting up MAX Memory usage is not an option as it's quickly building up to limit I have set and then app based on this SQL Server instance is acting up, being not responding or working very slow.


texts:
I have a huge view what consists of about 15+ million rows and about 150+ columns.
That view is made of a lot of LEFT OUTER JOIN between a master table and various related tables.

That master table contains a list of products with a unique product id, but the view is a cross product of the products and all their attributes contained in the external tables.

We have an application that needs to materialize that view applying some business logic to the dataset. What the application does is go through each product, collect the rows from that view and retrieve the attributes of each column through a SELECT DISTINCT query and then, after everything is computed, the result gets written back to a row in a new table.

Of course this is a very slow operation because we run a SELECT DISTINCT for each column, multiplied by each row of the view.

Is there any other way to manage such a huge view? I've been thinking about splitting it up by a certain number of rows or by columns, but I'm not even sure that would make any difference.
Are there best practices to work around this issue?
Thanks!


texts: 'ORA-00059: maximum number of files exceeded' on Oracle 12c

texts:
I have a test database.  Every night a script drops and recreates a number of pluggable databases that are used for our automated test runs.  Recently, I have started getting 'ORA-00059: maximum number of files exceeded' errors on the create pluggable database commands.  I must be missing something, because from what I can tell, I am no where near the maximum (and this script was working a month ago).

SQL> select value from v$parameter where name = 'db_files';

VALUE
-----------------------------------------------------------

200

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        24

SQL> create pluggable database testdb FROM DB_SEED
  2    FILE_NAME_CONVERT = ('/home/ora12c1/app/ora12c1/oradata/ora12c1/testdb_seed/', '/home/ora12c1/app/ora12c1/oradata/ora12c1/testdb/')
  3    PATH_PREFIX = '/home/ora12c1/app/ora12c1/oradata/ora12c1/testdb';
create pluggable database testdb FROM DB_SEED
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

It looks like the following resolves the issue:
Bug 18522516  12.1.0.1.4 (Jul 2014) Database Patch Set Update (PSU)

One note though, I had to bounce the database before I could create a new pluggable database.  As, I preformed all the steps that I saw in the doc:

Apply the patch using opatch apply
Startup/Open all pdbs
Run datapatch -verbose

Then I had to bounce the database to resolve my issue.


texts: How to improve Oracle Standard Edition's performance for testing?

texts:
There's a great post on StackOverflow about improving Postgres performance for testing.

https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing/9407940#9407940

However, there aren't any resources on doing the same for OracleDB. I don't have a license for Enterprise Edition, that has features like 'In-Memory' columnar storage that would almost definitely improve performance.

https://docs.oracle.com/en/database/oracle/oracle-database/19/inmem/intro-to-in-memory-column-store.html

I'm really limited in what I can try in Standard Edition. It's running in a Docker container in a CI pipeline.  I've tried putting the tablespace on a RAM disk, but that doesn't improve performance at all.  I've tried fiddling with FILESYSTEMIO_OPTION, but no performance change.
Would anyone know of some more obvious things I can do in OracleDB in a CI environment?


texts: Can I deactivate log shipping jobs without raising errors?

texts:
I've set up log shipping from Server A to Server B.  I then set it up from B to A when B was the primary.

I've reverted back to A being primary and disabled the backup, copy and restore jobs associated with B to A shipping.  However, I have a failure of the LSAlert job on both A and B.

Is there a way to suppress these error or must I remove Log Shipping from B to A while A is primary?

My goal is to leave the log shipping configuration in place for DR, but have it not raise errors stating that databases have not been sync'd.

Thanks


texts:
Quick question - if I apply same binary log multiple times via:

mysqlbinlog --start-position=xxx mysql-bin.xxxx | mysql

will the server understand and omit previously applied changes or further corrupt the database?

Also, in my backup I have the log position stated like this:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=155;

However, when I do

SHOW MASTER STATUS

this is what I get:

+ --------- + ------------- + ----------------- + --------------------- + ---------------------- +
| File      | Position      | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set      |
+ --------- + ------------- + ----------------- + --------------------- + ---------------------- +
| mysql-bin.000002 | 497824459     |                   |                       |                        |
+ --------- + ------------- + ----------------- + --------------------- + ---------------------- +
1 rows

I understand that the number from the backup should be more accurate because the backup was made in transaction, but my confusion is how comes the numbers are so different? (in a matter of 30 minutes) Or these are different numbers and I should use the 155 number for the
--start-position option?


texts: Select column to be inserted based on input column value

texts:
Assume the following two tables (and ignore the quality of the database design):

Table 1 has columns id, sequence_number, value: it contains many rows.
Table 2 has columns id, seq1, seq2, seq3, seq4, seq5: it is empty.

The goal is to fill Table 2 with Table 1's value, such that if in Table 1 one row is sequence_number = 3; value = 1564.1, then in Table 2 one should insert a row with seq3 = 1564.1. (the other columns aside from id should be empty).

How to do that in SQL? I don't want to hard code all 5 options (because in my actual use case there are over 50.), but I don't know how to condition the column name that should receive the value based on the value.


texts: I would like to migrate my Mysql databases from Ubuntu to RHEL..what is the best way to proceed? I'm on community edition. I will be moving to entreprise edition of MySql on RHEL.


texts: How to reinstall a legacy DB2 Server 7.x for Linux?

texts: We have to move our legacy database dump to a new Linux installation. I know DB2 7.x is out of support for over a decade, but is there a way to obtain a DB2 7.x Server installation media for Linux? Barring that, what other options do we have?


texts:
I'm trying to install Oracle 11g on Linux and have already configured the variables and X-host but can't able to create Central Inventory.

My Central Inventory location is /u01/app/u0 and group name is dba.

It throw an error:

  error [INS-32033] Central Inventory location is not writable.

I have already tried chmod 755.


texts: How is it possible for mysql storage engine to be NULL?

texts:
I'm just reading a book and in one of the examples I get notices:

That the engine in the information_schema.table is null, how is that possible?
Can I create tables without any engine??, is there any pro in that?


texts: SQL Server : I need to combine multiple rows into one row with different column values

texts:
I have a view

Order          Drink       Entree         Desert
--------------------------------------------------
12             Null        Preparing      Null
12             Ready       NULL           NULL
12             NULL        NULL           Waiting

I want to convert it to:

Order          Drink       Entree         Desert
---------------------------------------------------
12             Ready       Preparing      Waiting


texts: Definition of approach to store temporarily unknown data

texts:
What structure would be more reasonable to store temporarily unknown data? Initially we do not know some aspects of the data we are working with, but such aspects will be filled in at some point.
Problem description
I am developing a database for product refurbishment, in which I have to store data such as customer, model, serial number, etc. (this part is working well so far).
Said database has to handle shipment of products (incoming - outgoing). There is no problem for the outgoing part because we already have the data, the problem we are facing is with the incoming aspect, since some customers send products as a bulk or lot, and we only receive a summary of the shipment (i.e., model and quantity), other customers do send the complete information of the products.
Alternatives I have considered
So far, I have a Shipment table, and a linking table that stores the relationship between Products and Shipments, the options that I have come up with are the following:

Option A. Insert products in the Product table with NULL marks (except Model & Customer) and relate them to a Shipment; later, when we receive the products we fill in the columns that were marked as NULL. This option will allow the products to be treated as the rest and will add minimal changes to the system.

Option B. Have a separate table that stores the ProductSummaries to be received, and then when the shipment is received they are properly stored in the Product table. This option will add more tables and complexity, but I will not have to deal with NULLable columns.

Questions

Which of my two described approaches will be more suitable?

Is there any other design or structure that I can implement or follow?


texts:
I have a table like so.

ALTER TABLE clients ADD tsv tsvector;
CREATE INDEX index_clients_on_tsv
  ON clients
  USING gin (tsv);
CREATE TRIGGER clientstsvectorupdate BEFORE INSERT OR UPDATE
  ON clients FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(
    tsv, 'pg_catalog.english', first_name, middle_name, last_name, email, birthday_string
  );

But then I noticed that fuzzy searching was not working: "Chris", would not look up "Christopher". I did a little digging and found gin_trgm_ops Naturally the first thing I tried to do was update the index via

CREATE INDEX index_clients_on_tsv
  ON clients
  USING gin (tsv gin_trgm_ops);

But now I see an error relating to the fact I'm passing a tsvector to gin_trgm_ops.

  (Postgrex.Error) ERROR 42804 (datatype_mismatch): operator class "gin_trgm_ops" does not accept data type tsvector

What is the best way to address what I'm trying to implement?


texts: Looking for a better way with Reporting Db server

texts:
I have a "reporting" db server which is basically a clone of production using backup and restore in a stored proc to handle restore , user , permission etc.
Backups/restores happen 1x per day at a scheduled time.

Now they have a new db that they want on the report server and it is a pure OLTP db (right now I backup and restore it daily takes about 45 mins). The would like something a little more up-to-date just not realtime aka approx. with 2-4hours. I am in middle of converting from 2008 r2 to sqlserver 2014. 2008 r2 would give me replication or log shipping anybody have a better idea? Would really like a better idea as I am a single dba shop on call 24/7 365 and enjoy ability to sleep thru the night.
Thanks
kris


texts: Resolving a performance issue with BETWEEN join - eager spool

texts:
This query (anonymized) takes about 2 minutes to execute.

SELECT
    ly.Col1
    ,sr.Col2
    ,sr.Col3
    ,sr.Col4
INTO TempDb..TempLYT
FROM Tempdb..T1 ly
JOIN TempDb..T2 sr on sr.[DateTimeCol] BETWEEN ly.DateTimeStart and ly.DateTimeEnd
WHERE sr.Col5 = 1 OR sr.Col5 = 2

Are there some alternatives that would help with this query?

After applying the index suggested by Paul White, the query plan looks like this:


texts: mixing binlog formats between master and slave

texts:
I have a pretty much straightforward Master --> Slave replication setup in MySQL 5.6 / Innodb.

Both my Master and Slave are setup in MIXED binlog format.

Now I need my slave to act as a master to plug a Java binlog connector that will retrieve the binlog to further feed a hadoop stack.
For this to work I have enabled the log-slave-updates variable

Thing is: that java connector will better work on row based binary log.

So my question is:

Can I safely change the binlog format on my slave for ROW ?
and keeping the MIXED format on the master ?

I Couldn't find any specifics about this in MySQL docs.


texts: Concatenating two mysql queries to achieve desire results

texts:
Here I am trying to show one item per subcategory from a single mysql query.
This is my table "PRODUCTS" shown below :

Using a query I am able to show data which I want to get display in a more formatted way. I mean joining table and show data based on id. Image is shown below:

In this pic you can see multiple items are shown for a single subcategory which is table b. Three items are displayed for a single subcategory "Bag Packs", like wise 12 items are displayed for another single subcategory "Gents Wallets".

I just want to avoid these multiple items being displayed, instead want to show one item per subcategory.
Using a different query I able to get display the table which I want (shown below) :

Now the problem which I am facing is how to get his items shown by concatenting these two queries :

Query 1 :

SELECT p.pid, p.ctid, p.scid, s.name, p.type, p.pdesc
FROM products as p
    inner join b as s
where p.scid=s.scid and p.type='Gents' and p.featured='1'
order by name;

Query 2 :

SELECT *
FROM products
WHERE scid IN (SELECT DISTINCT scid
               from products)
GROUP BY scid ;

"Concatenating 2 Queries" : This part I really don't have any idea how to achieve. Please help.


texts:
I'm new to SQL Server, still struggling in understanding schema.

1.Is schema associated with login name?

Is login name the same as 'user name' under SQL Server Authentication?

3.Let's say I login as username 'ITSupport', so any table I created will be sth like ITSupport.TableName, so I don't need to prefix ITSupport to access TableName. So what's my default schema name, ITSupport or dbo


texts: Force standby de-synchronisation for script testing

texts:
Two databases are configured in a production-standby configuration with Oracle Dataguard.

I want to test a script that supposedly detects whether the dataguard observer is in the status SYNCHRONIZED or not. To do so, I am trying to manually break something to force the synchronization to momentarily fail while I test my script and then fix it.

I already tried stopping the observer but doing so will only cancel the third party status reporting ability of the observer itself which won't affect the databases at all.

A theoretical solution would be to break the network link between the two by disabling the network card. The issue is maybe in this case the observer wouldn't be able to connect to the standby and behave unexpectedly.

Please give me a heads up on where to start here !


texts: Cannot execute statement: impossible to write to binary log?

texts:
When I try to create a user story/issue in my my percona xtradb cluster I am getting below error. How to fix it?
Error creating issue:

Could not create workflow instance: root cause: while inserting:
[GenericEntity:OSWorkflowEntry][id,10009][name,Agile Simplified
Workflow for Project FF][state,0]
(SQL Exception while executing the following:INSERT INTO OS_WFENTRY (ID,
NAME,  INITIALIZED, STATE) VALUES (?, ?, ?, ?)
(Cannot execute statement: impossible to write to binary
log since BINLOG_FORMAT = STATEMENT and at least one table uses a
storage engine limited to row-based logging. InnoDB is limited to
row-logging when transaction isolation level is READ COMMITTED or READ
UNCOMMITTED.))


texts:
I'm writing an SSIS package that is supposed to import flat files into a database then move them to another folder when done.

The flat files are in a bunch of sub folders so I'm using a Foreach Loop Container to find the files. In the Foreach Loop, I have a script task that compares the first line of the file to the expected column headings format. If it matches, then it moves on to the Data Flow Task, which transforms and loads the data into the database. After the Data Flow Task, there is an Expression Task to calculate the destination file path and then a File System Task to move the file.

The thing is, it never gets past the Data Flow Task. The package transforms and imports the files fine but after the Data Flow Task, the loop moves on to the next iteration. I put a breakpoint on the OnPreExecute event of the Expression Task but it never gets there and there are no errors. Does anyone know what would cause this?


texts: Elasticsearch: Versioning a document on revisions

texts:
I currently have a document which gets revised regularly, I want to keep track of the document by keeping each old version of the document. So if document A has a summary and update date I want to, after every update, keep the previous version along with its update data.

The problem is that I'm not sure how I should do this efficiently:

...{
        Title: A
        Summary: {update_date:content, update_date:content, ...}
    }

The problem is that if I take the key as value then the automatic generated schema will take all dates as possible keys. Which is not something you want.

So my question is what's the most efficient way of tracking all revisions by date in ElasticSearch?


texts: mysql queries in 2 times (select) + 1 time (Insert) verifications handle problems for db to dont create double inserts

texts:
I want to make an 2 way select verification for all users in that kind of code version:

select * from table_A where other_user_id='B'
if (1) -> update set response_A='1'
if (0) -> select * from table_B where other_user_id='A'
  if (1) -> update set response_B='1'
  if (0) -> insert into table_A (response_A) values ('1') where other_user_id='B'

it is the reason to have less db entries and make the db queries faster/thiner, because not all users would have all information of each others in there own table. the problem what i see is i am not sure if that will creates me doubles. lets say. user A do that on same millisecounds as user B does on different computers. then the db create doubles (right?) because the db does not wait for the full query. is there an better way how to handle that? can and how can i stop for user B for that time that the db needs to make that for user A? thx and br


texts: Does MariaDB Support Materialized views?

texts:
Before I even try to experiment with MariaDB, could anyone tell me if MariaDB supports Materialized views? I can't seem to find it in their documentation.

This is one of my major issues with MySQL (the fact that it does not have materialized views out of the box). This is a major feature for me in a database, and I would like to know if MariaDB supports it.


texts: Date range query for past 24 hour in Mongo shell

texts:
I am setting a cron job to collect results from MongoDB database profiler. I'd like to collect results within a 24 hrs period. I plan to run mongo command with javascript.

Question is, in Mongo shell, how do I write a query to find a date range from 24 hrs ago? Such as:

db.system.profile.find({
    "timestamp" : {
        $lte : <current date & time>,
        $gt : <date & time 24 hrs ago>
    }
})


texts: Automated flow of creating user in Azure AD to MySQL that runs in K8s cluster in Azure

texts:
My ASP.net core application runs on Azure K8s cluster, the app uses authentication that is configured in Azure (the app is registered in Azure AD and some accounts are created there to use it).
The ASP.net core application is connected to read data from MySQL database that is running in Azure K8s as docker image.
The issue here is Azure AD accounts are not linked with MySQL database. once a user login has no relation with MySQL database uses. I like to have a an automated flow once I create an account in Azure AD to use my app the account data is automatically insert into MySQL database?
Knowing that I don't want to use Azure MySQL database, I prefer to use MySQL that is running as service in Azure K8s cluster.


texts: How can a group track database schema changes?

texts: What version control methodologies help teams of people track database schema changes?


texts: Finding entries which have no foreign key mapping in any other table

texts:
Let's say I have Table A, which has one column id, which is unique within the table. I have multiple other tables: Table B, Table C, ..., Table N. Each of these tables has a column A_id, which is a foreign key to id of Table A.

Is there an elegant way to get all rows of Table A which are not mapped to as a foreign key by any of the other tables? Ideally, I would like to see if there's a way to do this without having to type in a complicated query that involves manually querying each other dependent table, but any approach would be appreciated.


texts: Incorrect Statistics EQ_ROWS Causing Parallel Table Scans and Deadlocks

texts:
On one customer's system, one of our database's tables has about 123 million rows.  This table is a transaction table that is grouped by a batch key.  The clustered index is 4 columns: batch key, transaction version, shift number, transaction number.  One batch can have any number of transactions in it, but a query to count by batch shows the maximum number of transactions per batch is 764, and the count drops sharply from that peak.

Some of our queries that join to this transaction table are randomly getting bad query plans that choose to perform parallel index scans on the transaction table.  Many of these queries run in separate connections.  When this occurs, the parallel scans are causing constant deadlocking on this table.

The query plan shows a very large estimate for the number of rows in a batch-- about 20,000.  I already know the maximum per batch is 764, so I was puzzled where this estimate comes from.

I ran DBCC SHOW_STATISTICS ('MyTable', 'MyTableClusteredIndex').  The statistics histogram that came back looks weird, because the EQ_ROWS numbers are incorrect.  For example, here are the first 3 entries in the histogram.  The EQ_ROWS numbers are very different than the actual number of rows found with a query using the same key (shown in the right-most column):

RANGE_HI_KEY RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ActualRows
        58287          0        1                   0              1        121
        78045   664341.4 14512.91                1308       507.9888        134
        86487   569858.3 14512.91                1208       471.8449        118
        94282   574411.7  9070.57                 538       1067.721        128

Since this is a production server, I do not want to try to mess with the statistics during business hours.

We have an internal dev server that we use for performance testing.  The dev machine has 2 10-core processors on it and is using about 500 GB of memory.
On this server, the transaction table has about 1.1 billion rows.  The dev server shows a larger disparity between EQ_ROWS and the actual number of rows for each key.  For example, some EQ_ROWS numbers are over 60,000, but the actual number of rows for those keys are between 130 to 160 rows.

If I run a manual UPDATE STATISTICS MyTable on our dev server, the statistics do not change.  The EQ_ROWS are still the same incorrect numbers.

So far, I have found the same strange EQ_ROWS behavior on SQL 2014 SP 2 and SQL 2012 SP 3.

Our database servers run with the following trace flags enabled: 1222, 2312, 2453, 4199.  I verified that disabling these trace flags globally does not change the behavior when updating statistics.

I verified that the UPDATE STATISTICS MyTable is not running in parallel.  When it runs, its degree of parallelism is 1, so it does not look like parallelism is causing some kind of statistics estimation bug.

Aren't the EQ_ROWS values supposed to be the exact number of rows for that key value?  If so, why are they incorrect?  Does this look like a SQL Server bug?

(Note that I have ignored parameter sniffing, because this issue looks related to the statistics estimates.  The query plan does not look related to parameter sniffing to me except for randomly optimizing the query for one of these keys.)


texts: MySQL - Create a function from another function

texts:
I want to create a function same like my old functions.

When I create a table, I can use `AS  to other table as follows:

CREATE TABLE dummy2 AS SELECT * FROM dummy1;

Can I do the same thing with functions?


texts:
We had a mysql multi master with 3 nodes and unfortunately the replication broke. We have some set of tables which has to be in sync with other database servers, so i was looking into Percona-Toolkit for trying out the sync between these 3 servers. I could able to sync the records in the tables between 2 db servers in bidirectional way.

Is there any way to sync bidirectionally with 3 servers using pt-table-sync ? Or any steps involved in syncing three db servers?

Thanks,
Swaroop.


texts: What's the best way to ensure referential integrity on a replicated database?

texts:
Using SQL SERVER 2005, I have a couple of questions on Replication and referential integrity.

1) Does Replication handle referential integrity?

2) If I do an Insert to Parent table and then to Insert to Child table, in one transaction, on Source DB - will Replicated DB also behave in the same manner? I.e. In Replicated DB record must be present in Master table, before it is referenced in child table?


texts: Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure

texts:
While looking at several examples of pl/python and pl/pgsql,  I have seen many - but not all - using volatile cost.

ie:

CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- code
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Searching for more information about volatile cost I have discovered (at first sight) that roughly at least 90% of web examples are using volatile cost 100, and sometimes somethimes volatile cost 1. (for rows it's 1000).

As I have understood, this indication helps the query plan optimizer to decide how to set priorities in short-circuit boolean operations.

Is it premature optimization if I start to give an estimate cost or rows for each of my stored procedures ? Should I only do it when I want to optimize certain query ? Is it an art for choosing the good value of cost ?

I know about the command explain, which I have have not learnt yet. Is this command helpful for estimating cost ?


texts:
I'm consolidating multiple Linux boxes running Postgres into one box.

Given the way Postgres handles resource utilization, would one single server service handling all the databases provide a much better performance than multiple server services (each one listening to different IP:port) running on the same host?

Thanks


texts: MySQL cluster - how many hosts are needed for full HA?

texts:
I need some clarification on MySQL-Cluster (NDB) setup

I want to achieve full HA but I'm low on server budget and can't afford to rent a dozen of servers for simple solution that I'm working on.

What's the minimum amount of hosts to achieve full HA in case of one node failure?

I mean situation that cluster should continue to work with no errors when any single physical servers fails. Failed server can be any of MySQL Cluster components (management, sql or data)

I don't need extreme performance, just fully automated server failure handling on small number of servers

From the MySQL Cluster documentation I know that I need at least two full copies of data (replicas). Does one replica need to be on more than one host?

Can HA be achieved on 4 servers? 2 management nodes (kind of small VPS) + 2 physical servers each running SQL and data node?

I'm aware of MySQL master-slave, but it doesn't provide automated failover and requires application rewrite


texts: single mysql query that show a particular parent and child value

texts:
I am facing problem to show a particular parent child value and under all child value as a relationship. I have created single table tree and three column id, child and parent.

I created a tree relationship but I don't understand how to fetch a particular parent child and his child and his child value for tree. Can you help me?


texts:
I've got several Drupal sites, and all their tables are latin1 and collations are latin1_swedish_ci. Recently, I wanted to shrink my ibdata1 so I proceeded to read this answer and I dropped my tables (I used phpmyadmin and later was told this was a mistake because it messes with encoding) and all that. When I later re-imported, all my characters have lost their encoding. I was told to properly dump using the mysqldump(1) utility, then re-import with the correct charset. I thus tried:

  mysqldump -uun -ppw --all-databases --add-drop-table --all-databases -r all.sql
  mysql -uun -ppw --default-character-set=latin1 < all.sql

which didn't work. I then tried the same thing with utf8, and it didn't work either. What do I need to do?


texts: Do I need to set "ConnectionReset" parameter in the connection string when using isolation levels occasionally?

texts:
I have a MySql 5.6.44, and an dotnet application using Mysql.Data 8.0.12.

The application code has not specified any isolation level till now because the default one was enough. Suddenly we need to do a SERIALIZABLE transaction in a new feature, and only the transactions in that part of the code specify such level.

In dotnet, connections to the DB are pooled, so after the connection usage is over, the connection gets back to the pool. I have the concern, that the isolation level set for that operations remains in the connection affecting other transactions in the code which do not need such high level.

According to the component documentation, there is an optional connection string parameter that indicates if the connection state needs to be reset:

  ConnectionReset , Connection Reset Default: false

  If true, the connection state is reset when it is retrieved from the
  pool. The default value of false avoids making an additional server
  round trip when obtaining a connection, but the connection state is
  not reset.

Checking the decompiled component code, it seems that transaction levels are set with SESSION scope (MySqlConnection.cs:1238):

MySqlTransaction mySqlTransaction = new MySqlTransaction(this, iso);
MySqlCommand mySqlCommand = new MySqlCommand("", this);
mySqlCommand.CommandText = "SET SESSION TRANSACTION ISOLATION LEVEL ";
switch (iso)
{
  case System.Data.IsolationLevel.Chaos:
    this.Throw((Exception) new NotSupportedException(Resources.ChaosNotSu
    break;
  case System.Data.IsolationLevel.ReadUncommitted:
    mySqlCommand.CommandText += "READ UNCOMMITTED";
    break;
 ...

According to MySql documentation about transactions:

  With the SESSION keyword:

  The statement applies to all subsequent transactions performed within
  the current session.

So if I understand correctly, the isolation level set for one operation will affect other operations in our code that are not specifying an isolation level themselves, since the connections are being returned to the pool without reseting.

So being this the case, I should use the "ConnectionReset" parameter in our connection strings, am I right?

PS: The open source MySql connector (which we do not use) seems that is setting true by default in the ConnectionReset parameter by default, which makes total sense to me.


texts: Getting "ORA-00942: table or view does not exist" while table does exist

texts:
I'm fairly new to Oracle database. I have installed Oracle Database 11g R2 on Oracle Linux 6. I have successfully created a new database with dbca and connected to the database using:

$ sqlplus "/ as sysdba"

I successfully created a table and inserted some data and performed some selects:

SQL> CREATE TABLE Instructors (
         tid    NUMBER(7) PRIMARY KEY,
         fname  VARCHAR2(32),
         lname  VARCHAR2(32),
         tel    NUMBER(16),
         adrs   VARCHAR2(128) );

Table created.

SQL> INSERT INTO Instructors (tid, fname, lname, tel, adrs)
     VALUES (8431001, 'John', 'Smith', 654321, 'London');

1 row created.

SQL> SELECT count(*) FROM Instructors;

  COUNT(*)
----------
        1

Then I created a new user with the CONNECT privilege:

SQL> CREATE USER teacher1 IDENTIFIED BY pass1;

User created.

SQL> GRANT CONNECT TO teacher1;

Grant succeeded.

Then I created a new role with appropriate object privileges:

SQL> CREATE ROLE instructor;

Role created.

SQL> GRANT SELECT, UPDATE ON Instructors TO instructor;

Grant succeeded.

And granted the role to the user:

SQL> GRANT instructor TO teacher1;

Grant succeeded.

Next I exited sqlplus with exit; and connected as the new user to test it. I logged in successfully to the database with:

$ sqlplus teacher1

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 25 03:20:50 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password: *****

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

But when I try to select from the table it says:

SQL> SELECT * FROM Instructors;
SELECT * FROM Instructors
              *
ERROR at line 1:
ORA-00942: table or view does not exist

What I'm missing here ?!


texts: What is the last version of postgres with pgadmin 3?

texts:
For some reason I cant get used to the pgadmin 4 in web version. I found it very slow. For thing I want do fast I rather have pgadmin 3 even if that mean work with previous version of postgres.

So what was the last version of postgres with pgadmin 3?


texts: Convert subquery to join or other performance improving solution

texts:
This is direct continuation of this question, then solved with this answer.

The problem lies with the logic here:

,case when
  Cast(o.[CreationDate] as time) > '16:00:00'
     then (select top 1 [PKDate] from [calendar].[dbo].days
           where is_workday = 1 and continuous_day > da.continuous_day
           and continuous_day < da.continuous_day+7 order by PKDate asc)
     else (select top 1 [PKDate] from [calendar].[dbo].days
           where is_workday = 1 and continuous_day >= da.continuous_day
           and continuous_day < da.continuous_day+7 order by PKDate asc)
     end  OpDate

This fragment tries to find next workday date based on time part of o.[CreationDate] in [calendar].[dbo].[days].  If I could replace that subquery with a join I think it would make the results faster, which is my aim.

However the only solution I could think of in this case is to add another column to [calendar].[dbo].[days], which would hold NextWorkdayDate. I can do that, but I would like query solution, as I may not always be able to amend schema.

The dataset to be crunched has doubled in size since last time (yeah, we had a great year), the full query also grew a bit, so it's taking too long to execute (~20min).

The above bit of code is now my main problem, as all its instances and variations account for 70% of the whole query's actual cost.  Smoothing it out would be great.

This is for a Data Warehouse, so actually adding column makes a lot of sense. The Data Warehouse is getting bigger and more sophisticated so cutting down on cycles everywhere I can is good long-term strategy. Would you agree?


texts: SQL Server CDC: Can/should there be more than one variable value in the cdc_states table

texts:
Question:
Should there be multiple CDC state values (i.e., rows) in the cdc_state table when there are CDC packages running that are dependent on the result of other CDC packages? None of the tutorials, blogs, or book previews that I have read online discuss this topic.
I think the answer is yes, but I would like to get confirmation/2nd opinion on it.
Context:
I am currently on a project that is migrating data from one database to another and CDC is being used to track changes in real-time. The cdc state is being stored on a database table called cdc_state and contains the name and value columns. I am new to working with CDC. All of the tutorials that I have read only discuss CDC for a single table replication.
My project is a bit more complicated because it has the requirement of modifying the data model on-the-fly. So, the source databases data model is different from the destination databases data model. As a result, when running CDC, I am having to pull data from other destination tables because that data does not exist in the source (i.e., identity insert values). This is easy enough to handle through Lookups within the data flow.
But, this seems to introduce a race condition. If I have a CDC package that is dependent on the CDC inserts of another package (think of it as a sequencing order or parent-child relationship), then won't the CDC state value that is stored on the cdc_states table be invalid on my package when attempting to use the same cdc state value to run my package?
In other words, when the parent package runs, it will mark a new processing range in the cdc_state value, which contains the start and stop LSNs. When my downstream package runs, it will be looking at a different LSN range, rather than the range from where it left off, hence a race condition. This implies that any transaction that occurred within the processing range of the parent package, and is relevant to the child package, will be missed by the child package.
Is my understand here correct?


texts: Confused about the "Maximum Failures in the Specified Period" setting on SQL Server Cluster

texts:
The failover properties of the SQL Server role on my two-node SQL cluster are as follows:

Maximum Failures in the Specified Period: 2

Time period: 6 (hrs)

I would expect that forcing failover (either by moving the services manually or rebooting the owner node) more than twice would cause the resources to stay 'Offline', but this isn't the case. I can freely failover a seemingly infinite amount of times with no impact.

What is the purpose of that property and how do I simulate things correctly to test behaviour?


texts: SQL Server 2012 database in suspect mode after upgrade from Developer to Standard Edition

texts:
SQL Server database is in suspect mode after an upgrade from developer to standard edition 2012 version. In the log file there are two errors: 933 and error 905. Following error occurred when we tried to get the database back online.

  Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. Msg 905, Level 21, State 1, Line 1 Database 'VTSDB' cannot be started in this edition of SQL Server because it contains a partition function 'FNprocessedrawdata'. Only Enterprise edition of SQL Server supports partitioning. Msg 933, Level 21, State 1, Line 1 Database 'VTSDB' cannot be started because some of the database functionality is not available in the current edition of SQL Server.


texts:
How do I join multiple tables for aggregates with Sum and Count functions?

The query I'm trying is as below:

Select
  campaigns.id,
  campaigns.name,
  Count(landers.campaign_id) As landers_count,
  Sum(conversions.revenue) As total_revenue
From
  campaigns Left Join
  conversions
    On campaigns.id = conversions.campaign_id Left Join
  landers
    On campaigns.id = landers.campaign_id
Group By
  campaigns.id

I even tried Outer joins but no luck and  I'm getting inaccurate results.

My example tables are as below:

Campaigns table:

| id | name           |
+----+----------------+
| 1  | Facebook Ads   |
| 2  | Bing Ads       |
| 3  | Direct Mailing |
| 4  | Solo Ads       |

Landers table:

| id | name        | campaign_id |
+----+-------------+-------------+
| 1  | Lander 1    | 1           |
| 2  | Lander Two  | 2           |
| 3  | Lander 3    | 4           |
| 4  | Lander Four | 1           |

Conversions table:

| id | revenue | campaign_id | lander_id |
+----+---------+-------------+-----------+
| 1  | 25.00   | 1           | 1         |
| 2  | 12.00   | 1           | 4         |
| 3  | 19.00   | 4           | 3         |

The results I'm expecting should look like below:

| campaigns.id | campaigns.name | landers_count | total_revenue |
+--------------+----------------+---------------+---------------+
| 1            | Facebook Ads   | 2             | 37.00         |
| 2            | Bing Ads       | 1             | 00.00         |
| 3            | Direct Mailing | 0             | 00.00         |
| 4            | Solo Ads       | 1             | 19.00         |

Fiddle based on answer from @'Willem Renzema'

Fiddle


texts:
I have a query:
Select <CASE WHEN>, myfunction(param1, <CASE WHEN>)
FROM table

Both the Case When instances are the same set of conditions. So here I am clearly repeating the same conditions.
Question: Is there a way to not repeat the same Case When block efficiently?


texts: Oracle partitioning scenario with primary key absolutely required

texts: Is there any partitioning scenario in which the partitioned table is absolutely required to have primary key?


texts: Check constraint that exactly one in group has bit column set as true

texts:
Let's say you have a table of addresses like the following:

dbo.Address
(
    AddressId INT PRIMARY KEY,
    CustomerId INT,
    IsPrimary BIT,
    ...
    more typical address fields
    ...
)

I want a check constraint to enforce that there is always exactly one primary address per customer that allows me to switch primary addresses for a customer or add a new address and make that one primary.

I know I could have a constraint that not more than one is primary (allowing none to be primary) and then set all of a customer's address to not primary before inserting/updating an address to be primary. But how would I enforce that if a customer has addresses, one of them must always have IsPrimary = 1?


texts: Query Statistics in management data warehouse empty

texts:
i am using management data warehouse form SQL Server 2012.

I used it before on a 2008 instance a few years ago and everything worked good.
However this time i cannot see any Query Stastics data meaning the in built report is empty.

The Collector is configured and running.
The SQL Server agent Job is collecting and uploading and does not fail.

However still i cannot see any queries and there are plenty of them running on that server.

Does my service account has no rights? What permissions does it need?

I want to mention that for "Server Activity" and "Disk Usage" Report i have no problem and i can see proper values.


texts: Randomly failing SQL Server 2008R2 used by Access 2010 front-end via ODBC

texts:
From time to time (sometimes days apart, sometimes several times a day) users of a multi-user Access front-end, which has ODBC connections to a SQL Server back-end, see a variety of problems. Most users have Access Runtime, but a few use full Access. At most 15 users will be active at any one time, and their activity is mostly retrieve/amend/save, working on one record (and associated sub-records) at a time, usually for a minute or two.

The problems can be any of:

an Access form has #Error in all its bound fields.
an Access form has #Name? in all its bound fields.
an Access form has #Field! in all its bound fields.
front-end error handler reports "[ODBC SQL Server Driver][DBNETLIB]General network error
front-end error handler reports "[ODBC SQL Server Driver][DBNETLIB]Communication link failure

Sometimes (particularly with the first 3 types) the errors can be eliminated for a while by restarting the instance.
I'm not a DBA, but I am the closest that the company has.
Any suggestions for possible solutions, or at least some informed diagnostic tactics?
TIA.


texts: Preventing SQL Server Data Tools from Inserting Spaces in Object Names

texts:
Is there any way to prevent SQL Server Data Tools and Visual Studio from introducing spaces into object names when creating dimensions, mining structures and the like from data source views (DSVs)?

For example, if you happen to have columns named WordCount and IsList defined in your DSV, the SSDT/Visual Studio GUIs will automatically change the names to "Word Count" and "Is List." Although this may be more "end-user-friendly," it can cause a lot of problems if your goal is to tightly synchronize object names with the relational objects the DSV columns are derived from; for example, scripts that try to make some kind of statistical comparison between the relational column and data calculated upon it in a cube may fail because the object names are different. It can also cause difficulties in synchronizing report writing and documentation generation. It's cumbersome to have to manually remove the spaces that SSDT/Visual Studio introduce (as well as risky, given that these tools are notoriously temperamental when it comes to juggling metadata, leading to all kinds of obscure SSAS errors) so I'd like to shut off this "feature" permanently if possible. Thanks in advance.

Note that I did not indicate an edition of SSDT or Visual Studio because I have yet to see one that doesn't exhibit this behavior.


texts:
Why is RMAN restoring the datafiles out of ASM and how to restore a backup into ASM?

Here is one output. The tablespace triscaltb was restored in /u01/app/oracle/product/12.1.0/dbhome_1/dbs/MISSING00013

RMAN> report schema;

Report of database schema for database with db_unique_name TRISCALDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               YES     +DATA/TRISCALDB/DATAFILE/system.258.1016190249
3    600      SYSAUX               NO      +DATA/TRISCALDB/DATAFILE/sysaux.257.1016190057
4    135      UNDOTBS1             YES     +DATA/TRISCALDB/DATAFILE/undotbs1.260.1016190491
5    250      PDB$SEED:SYSTEM      NO      +DATA/TRISCALDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.267.1016191021
6    5        USERS                NO      +DATA/TRISCALDB/DATAFILE/users.259.1016190489
7    510      PDB$SEED:SYSAUX      NO      +DATA/TRISCALDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.266.1016191021
8    260      TRISCALPDB:SYSTEM    NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.272.1016192445
9    520      TRISCALPDB:SYSAUX    NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.271.1016192441
10   5        TRISCALPDB:USERS     NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.273.1016192445
11   1243     TRISCALPDB:EXAMPLE   NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.270.1016192435
13   0        TRISCALTB            NO      /u01/app/oracle/product/12.1.0/dbhome_1/dbs/MISSING00013

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       +DATA/TRISCALDB/TEMPFILE/temp.265.1016190837
2    20       PDB$SEED:TEMP        32767       +DATA/TRISCALDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012019-08-13_11-21-09-am.dbf
3    20       TRISCALPDB:TEMP      32767       +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/triscalpdb_temp012019-08-13_11-47-56-am.dbf


texts: Ensuring Only Fresh Data Gets Accessed on SQL Server 2008/2012 Replication For Reads

texts:
I am designing a scale out solution for a globally dispersed company that has data centers on all continents. Over 10,000 servers report to a SQL Server cluster in Los Angeles, and also query it to perform certain tasks.

The Justification/Reason

In order to move from clusters, the issues with vertical scaling, and to improve performance due to the global network distance between many nodes to the cluster in LA, I thought to use Transactional Replication on specific core tables to subscribers (single pub multi sub) spread out across the world as my first step.

Issue to be Resolved

If for whatever reason the data is over X minutes old (let's say 10 minutes), I want them to go to the publisher (or another defined server) and read it from there.  I would like to automatically grab data from the publisher instance if the preferred one is not available or stale.

I'm not sure what the best method is for doing this, especially if the data is 'stale'.  I know we have replication monitoring tools, but how would I use that to know if the data is stale and force the connections to another server that isn't?

The reason is I'm concerned that certain issues will take over 10 minutes to resolve, during this time the critical apps such as maintenance and automatic provisioning will get stale data and the business could be greatly impacted.

Possible Solutions & Issues With Them:

Put the Subscribers/Publisher in a 'read only' load balancer and have all queries go there.

If the database servers were all in the same data center, I would probably use a load balancer and pull servers out of it, but that will require manual intervention, and I want the system to force all reads to the publisher (or another subscriber) and bypass the subscriber they connect to.

This would not resolve the issue with stale data however, such as if replication falls behind and it takes an hour to fix.

Modify Code To Check Outstanding Replicated Queries And Adjust Accordingly

This would require code modifications on many queries and the devs won't have the cycle to do so, plus it doesn't sound like a very plausible idea.

Query Replication Status And Change Connection String Automatically If X Number Of Queries Are not Replicated

I should be able to do this in Powershell, but the majority of servers are Linux so they would need an 'app' which queries the replication status, and modifies their connection string if it is over X number of unreplicated commands.

Thank you!  I have 1 2012 environment and several 2008 environments I'd like to implement this on, with the idea that we will move them all to 2012.  Enterprise is an option.

Other Ponderings

Having a load balancer in LA that the world sends a very quick small query to, to decide which server to pick might work well actually, as it would be 1 small query and perhaps the requesting end could cache that info temporary.

It seems like I'd have to have the SQL or other DB engine that is the 'read only' pair spit back to the app 'this data is stale, go somewhere else, preferably here'


texts: Where can I get the master.dacpac file for SQL Server 2012?

texts:
I am using Visual Studio 2012 Professional with SQL Server Data Tools with a SQL Server 2012 standard edition database instance.
I have been unable to locate a master.dacpac file on either my development machine or on the server hosting the SQL Server instance.

How can I obtain this file, so I can get rid of warnings like the following:

SQL71502: Procedure: <ProcedureName> has an unresolved reference to object [sys].[objects]


texts: Database design for multiple ordered component types on a page

texts:
I am creating a page type in the database that would represent a page on a website. The problem is that each page could have multiple components such as: text, image, video, audio file, etc.

I am having trouble with wrapping my head around how that should be represented in the database. The easiest way I can think of is having a table called component as follows:

component
=========
id (PK)
page_id (FK)
component_position_on_page (the order each component appears on the page)
text
image
video
audio
bgcolor
fgcolor

Now, while that might be the easiest way it has two problems: Most of those fields would be empty because if you choose to place text in a component of a page then the other three component types (image, video, audio) will be empty. It would also not be very scalable if I want to later add other component types.

A better way might be to have a separate table for each of the component types, but then it seems more difficult to link to them and order them correctly on the page.

What is a good way to implement such a design?


texts: SSIS Package Data Flow Task RollBack on Error

texts:
I tried running a package and it failed but I noticed in the output box (in visual studio) that it managed to write 2000+ rows to a table. I was wondering if an SSIS package would roll back the transactions in my Data Flow Task if the overall package failed. I found this thread

http://www.sqlservercentral.com/Forums/Topic998096-391-1.aspx#bm998104

which states that in order for the package to rollback DFT changes:

Set the TransactionOption property of the package to "Required"
Set the TransactionOption property of the dft to "Supported"

I had both the Package and DFT Transaction Option property set to supported so I am guessing that the rows written to the table were not rolled back... Do you all agree that this is the best way to make sure table changes are rolled back in the event the package fails? Will this also rollback file System Tasks such as file moves on the local file system?


texts:
I'm having a Recovery catalog database 12c version. While I'm trying to register the target database (11g) with the catalog I get the following error:

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 14 13:03:44 2015

Copyright 1982, 2014, Oracle and/or its affiliates. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06429: TARGET database is not compatible with this version of RMAN
RMAN-06618: RMAN client and database release mismatch; indicated database release is 11.2.0.4

I have checked the compatibility matrix, it says the RMAN client and the target database has to be the same version. But I'm using Catalog as 12c database therefore the client is 12 as well. Should I use RMAN 11g client to connect to the target? If yes, How should I do that?


texts: Prepopulate database with empty / null values [mysql]

texts:
What would be the best way to pre-populate a database with 100 empty fields and then begin the Auto_Increment?

What I mean to say, I want to reserve the first 100 userfields (ie UIDs which are auto_increment) 0 - 100 for "special users" and have the AUTO_INCREMENT start at 101 (as an example).

Obviously I can't set Auto_Increment to 101 as then it will start the UID at 101. And, I don't know beforehand how many special users there will be, however 100 sounds like a reasonable number to reserve.

What kind of null data can I insert and how would you go about this?


texts: What exactly is the datatype input into a DATETIME value in MySQL

texts:
I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right format.
Actual question: I assume it's string, because when I manually input values using and INSERT, the value has to be in ' '. Is this a stupid assumption on my part?
When the output of my PLC is a string and the datatype of my column is DATETIME, what I read from a SELECT in my DB is completely wrong : '1905-07-13 00:00:00' instead of '2021-04-30 08:51:30'. However, if I write it in the DB column set to be of type VARCHAR, it's written correctly, but not usable for my next app. So if I then change the column to DATETIME, everything works great, but that's super impractical and I can be changing the datatype of my column every time I need to add to the DB.
Another assumption I'm making here is that the input datatype is what's causing this problem, but as it is now, DATE and DATETIME are not supported by the driver I'm using, so maybe it's just wishful thinking on my part that any of this will work at all.
Note: Please do migrate this if it's off topic, wasn't really sure if it was a best fit here or stack overflow...
Second Note: The only way I have found to make this work is to write into the DB as a VARCHAR and then use CONVERT(,DATETIME) in my app, but it feels weird that I can't just write a date in my DB for some reason.....


texts: InnoDB locking: what does index record mean?

texts:
From MySQL documentation

  Record Locks

  A record lock is a lock on an index record.

what does index record mean for cluster index?
what does index record mean for secondary index?
Is it a key-value pair in the B+ tree leaf node?
Is it a data struct in MySQL source code?

Reference

This article seems useful, but I just don't understand.

https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/

https://github.com/octachrome/innodb-locks


texts: Compression not available on SQL Server Standard? Options?

texts:
So as they say, everyday is a school day. Today I learned that my workplace, runs SQL Server Standard edition, where I would have assumed Enterprise was in place. Although in reality shouldn't be surprised!

For some context, we have a very large database that houses our warehouse data. As the database has grown to a large size, it's causing issues with space on the server along with some application performance. So looking at it from my perspective I suggested we archive and purge the PROD database, to house only 18 months data in the PROD environment.

Wrote my scripts and tested them and all fine. I then went to compress the tables I had deleted data from, to find error messages that compression is not available in SQL Server Standard and requires Enterprise edition.

Wondering what my next steps are here? My assumption is that even though I am deleting a lot of data, we won't actually benefit in terms of performance, and space requisition until the tables get compressed.

Shrinking is something I guess I've always shy'd away from, many articles or posts here would advise not to use it.

Wondering, what sort of options do I have here?

Is my assumption correct, in that without compressing, we won't regain space from the trimmed database?


texts: Unclear oracle problem with getting response of query

texts:
The problem is very unclear,

I have an application server, that constructs queries and sends it to Oracle to get response.

The Oracle database is installed on a high perfomance server:
Linux 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

The application server was tested on different locations inside of the intranet, including that high perfomance server, where the database is located.

Several versions of the application server (from old to newest) were tested also.

The problem:

When the application server sends a query to the database, it has to wait for about 300 seconds to get response and construct logged output.

But when I'm connecting to the database with PL/SQL Developer and running that query using that IDE, I'm getting a response in less than one second.

Is there something familiar for you, guys?

We're just cracked our heads trying to determine where the problem could be located.

If you need more information - I'll update this post immideately.


texts: Difference of two columns with possible NULL cells

texts:
I need to sort a table by the difference of two columns as

SELECT (first - second) AS dif
    FROM table1
ORDER BY dif DESC

but the two columns can be NULL. In this case, it returns the error:

BIGINT UNSIGNED value is out of range

The solution to this error is to use IFNULL(col,0), but the problem is that I need dif to be NULL if any of the columns is NULL


texts: PostgreSQL PREPARE query with IN () parameters

texts:
I'm trying to prepare a query from PHP like:

pg_prepare($con, "prep", "select * from test where tid in ($1)");

and then execute it with:

$strpar = "3,4,6,8,10";
pg_execute($con, "prep", array($strpars));

The problem is that I cannot pass a series of values built as prepare expects a fixed number of parameters. Is there any way to make the parameters dynamic?


texts: How data can be transferred from Sybase SQL Anywhere to SQL Server

texts:
One of our client shared .db IMS SQL Anywhere database backup file. Our mission is to transfer data from that backup file to SQL Server.

My questions is

Which software I need to download or it can be done through ODBC\OLEDB without installing Sybase Server?


texts: Can you help clear confusion of ORACLE Timestamp with Local Time Zone?

texts:
Below is a description of ORACLE TIMESTAMP WITH LOCAL TIME ZONE

  ...data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone.

What does data stored in the database is normalized to the database time zone means ? Normalized...how ?

If it is normalized, why isn't it returned accordingly with Server's time zone instead of the users' local session time zone... How is it "session" and not "server" ?


texts: MongoDB create user readWriteAnyDatabase but without the ability to create new DB

texts:
I want to create a single user who can read and write to any existing DB, but wouldn't be able to create new DBs. I want to give that privilege only to the dbAdmin.

I have tried to create a user with the privilege readWriteAnyDatabase, but this role grants the user the ability to create new DBs. I also tried readWrite on the admin DB, but It didn't bring me the required results.

To sum up, 2 users. 1 can read and write from any DB, and the second can create DBs. How can I manage to create those users?


texts: Connect to mysql server with one url but have multiple masters

texts:
I am looking to have one URL like master.mydb.com, but under the hood I want to be able to access databases on slave1.mydb.com or slave2.mydb.com.
The reason for this is we have a massive number of near identical databases on each server, but very different data on each database. kinda like this:
slave1.mydb.com has databases
p1
p2
p3

slave2.mydb.com has databases
p4
p5
p6

all of those databases will have the exact same tables in them, but when my server accesses the data I just want to be able to connect to the cluster, and not have to worry about figuring out where the database p4 is stored, and making a connection slave2-mydb.com. In production we will probably have something more like slave[1-20].mydb.com with


texts:
How can I programmatically insert a row into either the SQL Error or SQL Server logs on a SQL Server?

I wrote a Dot Net application to do a data import job and would like to write log entries into the SQL Server's log on a failure.

In SSMS, the log can be found Management-SQL Server Log.

I can pull the contents of the error and SQL Server log by the stored procedure xp_readerrorlog.


texts:
As I understand Functional Dependency X -> Y in R, indicates Y can be determined by X and is FD if and only if X maps to one and only one Y.

if this is true then is it true to say X is a superkey?

I believe I have misunderstood functional dependency because I see questions related to determining superkeys keys based on the list of functional dependencies.

Is my above description of FD incorrect ?


texts: Help writing a query that select the min column in a group by

texts:
I have two simple table

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  nickname varchar(35) NOT NULL,
  PRIMARY KEY (id),
);

CREATE TABLE user_service (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  service_type VARCHAR(10) NOT NULL,
  price numeric(15,2) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_service (user_id, service_type),
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

I'm trying to retrieve in ONE query a list of user that provides certain services.
The query should return the user and the lowest price of its services

Here is the query I wrote, as you can see i have wrote a sub select and I was wondering if this is a good way of doing things? Or is there a more efficient way?

SELECT
    u.id,
    u.nickname,
    (select MIN(price) from user_service where user_id=u.id order by price desc) as minPrice
    FROM
      user u
      LEFT JOIN user_service us ON us.user_id = u.id
    WHERE us.service_type IN ('TYPE1','TYP2')
   group by u.id;


texts: PostgreSQL 10 not allowing remote connection

texts:
I cant get a PostgreSQL 10 instance to allow remote connections.

The database is hosted on Ubuntu 18.04

listen_addresses has been set to both '*' and 'myactualip' that needs to connect to the database.

pg_hba.conf has had IPV4 set to

host all all 0.0.0.0/0 md5
host all all myactualip/subnet md5

I still have no connection and get the usual "is x.x.x.x allowing tcp ip connections on port 5432".

The Postgres config file is listing 5432 as the listen port, netstat -atn shows the server listening on 5432.

firewall is disabled.

getenforce is disabled

I can ping the client computer.

The client computer that needs to connect also has firewall disabled.

I restart the Postgres service after every change, I've also tried restarting the VM multiple times to no avail.


texts:
I have been using XAMPP (on Windows XP/XAMPP 1.7.4 /PHP 5.3.5) for quite a while now, and last morning I started to get some weird messages when I was developing my website. It happened yesterday and started as something I thought I could handle but apparently I couldn't. These messages were some about timeouts, some were about "can't connect" for my connection string inside my website.

I was thinking that the site might be under attack as last morning was pretty much like any other morning. I can actually start the admin (the button near the "start/stop" apache) page, but can't login to MySQL. I think root was not set, so I tried to use the documentation to reset it.

What happens now is that I just can't login into MySQL Server no matter what I do. I keep getting access denied for ''@'localhost' and 'root@localhost' if I try logging as root.

What can I do?


texts: Top Activity in OEM reports that dbms_scheduler jobs run as the job_creator user?

texts:
The top activity graph in Oracle Enterprise Manager was reporting that certain activity was being performed by certain users at odd hours of the night.  Upon researching, this activity was because the user in question was the creator of a scheduler job (even though the scheduler job was in a different schema).  This has led to some amount of confusion, so we are attempting to have the Top Activity graph in OEM report that the activity is from the owner of the scheduler job instead of the job_creator.

Test Case

sqlplus kjohnston

begin
    dbms_scheduler.create_job(job_name => 'UTILITIES.SCHEDULE_TEST', job_type => 'PLSQL_BLOCK',
    job_action => 'begin for v_id in (select * from dba_tables) loop null; end loop; end;',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=1', auto_drop => FALSE, enabled => TRUE);
end;

When UTILITIES.SCHEDULE_TEST runs via the repeat_interval, the job is reported as running as the job_creator user.

SELECT sample_time, username, sql.sql_text
  FROM v$active_session_history sess, v$sql sql, dba_users users
 WHERE sess.user_id = users.user_id
   AND sess.sql_id = sql.sql_id
   AND upper(sql.sql_text) = 'SELECT * FROM DBA_TABLES'
  ORDER BY sample_time desc;

SAMPLE_TIME         USERNAME
14-DEC-15 14.47.52  KJOHNSTON
14-DEC-15 14.46.52  KJOHNSTON

However, when the job is run on-demand, the job is reported as having been run as the job owner.

exec dbms_scheduler.run_job('UTILITIES.SCHEDULE_TEST');
/
SELECT sample_time, username, sql.sql_text
  FROM v$active_session_history sess, v$sql sql, dba_users users
 WHERE sess.user_id = users.user_id
   AND sess.sql_id = sql.sql_id
   AND upper(sql.sql_text) = 'SELECT * FROM DBA_TABLES'
  ORDER BY sample_time desc;

SAMPLE_TIME         USERNAME
14-DEC-15 14.59.19  UTILITIES

My first attempt at a solution was to wrap the create_scheduler_job in a procedure owned by the owner of the scheduler job, then run the procedure to create the scheduler job... however, the job_creator is still KJOHNSTON (which would then still look like KJOHNSTON is running the job instead of the job owner: UTILITIES):

create or replace procedure utilities.create_scheduler_job as
begin
    dbms_scheduler.create_job(job_name => 'UTILITIES.SCHEDULE_TEST', job_type => 'PLSQL_BLOCK',
    job_action => 'begin for v_id in (select * from dba_tables) loop null; end loop; end;',
    auto_drop => FALSE, enabled => TRUE);
end;
/
exec utilities.create_scheduler_job;
/
SELECT job_creator FROM dba_scheduler_jobs WHERE owner = 'UTILITIES' and job_name = 'SCHEDULE_TEST';

JOB_CREATOR
KJOHNSTON

I've considered 2 other potential solutions:

1)  Proxy in as the job owner to create the scheduler job.

2)  Create a generic SCHEDULER_JOB user that we login as in order to create the scheduler job.

Neither solution is particularly appealing to me as either solution requires more work per job than I would care to put into scheduler job creation.

Just to be clear, the jobs run without issue whether they are reported to have been run from the job_creator or the owner of the job, but this would help with the future analysis of top activity on our databases.

Has anyone else come across this issue and come up with a better/easier/more elegant solution?


texts: When is SQL Server permission check done?

texts:
When a user is to execute a complex stored procedure, inside of which there are quite a few tables / views or even another stored procedure.

My question is:

Before SQL server generates an execution plan, does it check the user's permission?

If not, generating execution plan itself is an overhead cost wasted if the user does not have the EXEC permission on this stored procedure in the first place.

If yes, it seems to me there is nothing consumable to the SQL Server engine, i.e. there is no securable identifier (like the stored procedure itself, tables inside the stored procedure etc) that the SQL Server engine can use to check against the user permission. All these securable identifier will not be generated until at least parsing is done.

So from a lifecyle perspective of executing a stored procedure, when does SQL Server permission check kick in?


texts: How to update a huge MySQL table from a text file

texts:
I have a huge MySQL table with 1.3 TB data with InnoDB engine format.

Now I have a text file containing updated values of 2.2 GB of records in the 1.3 TB of data.

How do I proceed ?

I am a newbie to this data loading part. Please guide me through this.

Here the sample data present in the .txt file.

91653   8       2       1       4       Publisher                               2       1       4
91653   8       2       2       5       Publisher                               3b      1       5


texts: What is difference between primary index, secondary index, unique index and normal index?

texts:
I work on Oracle systems where I two type of indices exists in any table:

Unique index (Based on any search key which is unique key).
Normal index (Index made on any search key which is not candidate or primary key).

But now I came to know about two new type of indices from a university documentation:

Primary index
Secondary index

How are primary and secondary indices different from each other? Are they the same as unique and normal index respectively in Oracle?

I also want to know about below two indices:

Dense index
Sparse index

Can we consider dense and sparse indices as type of primary and secondary indices?


texts:
I collect some personal information from users of my website and according to German law, the storage of this data is only allowed with consent of the user. I programmed my site in a way, that the users needs to enter the data via a form, and confirm it via a double opt-in which is send to his email address. So far everything is still compliant with the law.
But the law also requires from me, that I am able, any time, to prove that the user is really the person who made the entry.
The double opt-in should be enough, but as I have access to my database, could make entries too. So the question is, how to turn off my own writing rights without being able to get them back. (And how can I prove that I did not have write access ...)


texts: Restored database from .bkp file in SQL Server 2014 generates some strange errors

texts:
I have restored the well-known Northwind database and executed a trivial query:

Notice the strange error "Invalid object name..." although the query has been executed successfully and both tables can be seen in Object Explorer.

Is there an explanation for this?


texts: MySQL: Which join is better between left outer join and inner join

texts:
Which join is better peforming if all of them provides the same result?
For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). To get some specific results both inner join and left join gives the same result. But, I have still some doubts which is not limited to this question only.

which join is more efficient which should prefer in case of same result values ?
What are other factors which must be considered at the time of applying join?
Is there any relationship between inner join and cross join?


texts: SQL Server 2008 R2 Express Edition - generating database create script

texts: I need to script a database schema out to T-SQL for inclusion source control. The Express version of SSMS doesn't seem to let you script the entire database, just individual tables. Are there any free tools (scripts or otherwise) that can do this ?


texts:
Do you have to restart MySQL to edit changes? Or can you edit them on the fly and restart MySQL or make the changes inside MySQL?

This server runs 100s of website that are active and I don't really want to bring mysql down for a restart unless I have to.


texts: I have a MySql (5.5) database with a MS Access frontend. If two users are viewing the same record at the same time how can I make each user aware of each other? For example how would I go about displaying a label indicating that another user is also viewing the same record?