texts: MySQL replication - some values are not being replicated

texts:
MySQL Replication issues

I might sound clue less here as i am :) , I am having problem with mysql replication, i.e. replication seems to be missing in some random occasion.

My structure looks like:

1) Master
   2) slave (without SSL)
   3) Slave (with SSL)

On some occasions, what goes wrong is that data are not replicated to slave.

  First my question, was if i someone is updating slaves. And i took off all
      privilege from all user other than read.
      which itself contradicts because both slave are not replicated and user's do not have permision on both slave.

Ignored DB:

binlog-ignore-db                                        = mysql
binlog-ignore-db                                        = test
replicate-ignore-db                                     = mysql
replicate-ignore-db                                     = test

Manual Test:
            I created new database, created table, new filed all was replicated to both slave. Updated works, altered works and delete works.

On some occasions when it misses out, randomly I am unable to troubleshoot or narrow down problem area.

Any suggestions?

FYI, I do not have skip-error in place.


texts: What Oracle SQL syntax do I need to find transactions that occurred during time intervals of a day for a period of a year?

texts: I am looking at a transactional-level table for which I need to extract all transactions which occurred between certain hours (between 15:00 and 23:59) of a day for the past year.  How can I do this using Oracle SQL syntax?


texts: How can I retrieve the column names of a table in mysql?

texts:
I have a table with n columns.

I want to retrieve the first 3 column names of that table.

Should be something like this:

Select column_name(3) from table_name="sample";


texts:
The syntax to define an update value for timestamp is the following:

CREATE TABLE Example1 (
    ...
    `RowVersion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ...
);

I can change the datatype from timestamp to GUID colum by:

`RowVersion` CHAR(36) NOT NULL DEFAULT 'uuid()'

But I can not write:

 `RowVersion` CHAR(36) NOT NULL DEFAULT 'uuid()' ON UPDATE 'uuid()',

How am I able to set a new uuid() when the row is updated without create a trigger?


texts: Is there any way to avoid SSMS 2012 allocating extra 1.5 GB RAM for non-standard color scheme?

texts:
SSMS 2012 allocates 1.7+ GB of RAM if the colors for Text Editor are different from default. Every few keystrokes GUI freezes for 5-10 seconds, which does not occur with standard color scheme.

When reverting to default, only 200+ MB RAM is used. Is there any workaround to still use custom colors and not face freezes due to excessive RAM utilization in SSMS?


texts:
As a reporting solution, I have a large table with ~50M records called "reports". Currently, I am using PostgreSQL with partitioning table feature day by day, (table name reports_20170101 means that all the records of 2017-01-01 stores in there).

An example query (runs 41 seconds)

SELECT to_char(date_trunc('week', rpt_datetime), 'YYYY-WW') date_week,
SUM(rpt_revenue) revenue FROM reports
WHERE rpt_datetime < ?
GROUP BY date_week

EXPLAIN, ANALYZE result:

https://gist.github.com/onesvat/be234fbcb6c4d375f9d1dd4151d69391

If the interval is small, there is no problem but when I use larger intervals, it becomes too slow (20 or more seconds).

The system has 128GB Ram, 16 threads and 4 SSD disk with raid0.

The system never deletes or updates yesterday and backward tables so there is no need the re-query older datas. Is there any database or extension that can handle these type of queries in reasonable time?

There is indexes on most columns that includes date and something, in fact, there is additional indexes like:

btree (date_trunc('week'::text, timezone('Europe/Istanbul'::text, rpt_datetime)))

also constraint :

"reports_20170101_rpt_datetime_check" CHECK (rpt_datetime >= '2017-01-01 00:00:00+00'::timestamp with time zone AND rpt_datetime < '2017-01-02 00:00:00+00'::timestamp with time zone)


texts: MySQL 5.7 JSON_MERGE combine with GROUP_CONCAT

texts:
we have table with JSON column
for example simple:

{"a": 1}
{"b": 2}

run JSON_MERGE query

select JSON_MERGE('{"a": 1}','{"b": 2}')

result correct:

{"a": 1, "b": 2}

run the GROUP_CONCAT query

select GROUP_CONCAT('\'',json_text,'\'') from t_json

result

'{"a": 1}','{"b": 2}'

but if try to combine both of them:

SELECT JSON_MERGE((select GROUP_CONCAT('\'',json_text,'\'') from t_json))

return error:

Incorrect parameter count in the call to native function 'JSON_MERGE'

any ways for merge JSON documents from group operation?


texts: Load Numerical Column with Variable Comma

texts:
I'm using sqlldr to load a file which has a numerical value as one of its fields.

The problem is that in some of the records the number has commas whereas in other, it doesn't.

So doing something like

num "to_number(:num, '999,999,999.99')",

loads the records with commas but not those without commas and doing

num "to_number(:num)",

loads only those without commas.

Is there a way to tell Oracle (sqlldr) that the comma is optional?


texts: Extract JSON array of numbers from JSON array of objects

texts:
I have a table containing a json array of objects similar to this:

id    |   record
____________________
name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...]

I would like to get a table containing a json array of only, say, the "a" values:

id    |   record
____________________
name1 | [0, 1, 2, ...]

I use PostgreSQL 11, so the latest functions are acceptable.


texts: How to view table data in the newest 6.0 MySQL Workbench

texts:
I basically fail to view data in the new Workbench version without firing queries. I recall it was clearly possible in the previous version by simply using context menu something like "view table data".

Now in 6.0 I can't see this option anymore. Though I can see "search table data" which gives me next window where I have to input some mysterial query the pattern of which I fail to recognize. (see second screenshot - Start Search button is disabled)

It is not a big problem for me to fire queries, but still this is quite annoying especially if you don't see sql on daily basis using ORM and you just want to take a quick look at the table.

Oracle update - 1, :-)

My apologies to all db evangelists.


texts:
I'm trying to work out the SQL to list the top 5 rentals for each month on MySQL or SQL Server.  The database is the Sakila Sample database and the table is rental. Here are the first several rows in my database client (Navicat Premium):

I'd like the take the month from the rental_date field.
Each film has a unique inventory_id.

So far I've got this:

SELECT
  MONTH( rental_date ) AS month,
    inventory_id,
    COUNT ( inventory_id ) AS no_of_rentals
FROM
    rental
GROUP BY
    MONTH ( rental_date ),
    inventory_id
HAVING
    COUNT ( inventory_id ) > 1
ORDER BY
    MONTH ( rental_date ),
    inventory_id;

Unfortunately, that returns all films with more than one rental per month, which is not what I want.

I'd like to see something more like:

month   inventory_id  no_of_rentals
_________________________________
April   367           5
April   98            4
(3 more rows)
May     144           5
May     32            5
(3 more rows)
etc...

Bonus points if you can get the film title!

Thanks!

Rob

UPDATE:

Thanks to Dominique Boucher for the answer.

Here's my finished query:

WITH cte AS (
    SELECT
      MONTH(rental_date) rental_month_no,
      DATENAME(month, rental_date) rental_month,
      inventory_id,
      COUNT( inventory_id ) no_of_rental,
      ROW_NUMBER( ) OVER ( PARTITION BY MONTH(rental_date) ORDER BY COUNT( inventory_id ) DESC) Rank
    FROM
      rental
    GROUP BY
      MONTH(rental_date),
      DATENAME(month, rental_date),
      inventory_id
    HAVING COUNT( inventory_id ) > 1
)
SELECT
    rental_month,
    cte.inventory_id,
    title,
    no_of_rental,
    rank
FROM
  cte
  JOIN inventory ON cte.inventory_id = inventory.inventory_id
  JOIN film ON inventory.film_id = film.film_id
WHERE
  Rank < 6
ORDER BY rental_month_no, no_of_rental DESC;

I added code to fetch the titles and limited results to films that were rented more than once in a month.

Here are the results in Navicat:


texts: Most efficient way to cascade delete in MySQL

texts:
I have several 1-to-many FK relationships in my MySQL schema, with tables as follows:

AGENCYS  1-*>  ROUTES  1-*>  TRIPS  1-*>  STOPTIMES

At present, there are roughly 1300 agencys, 16000 routes, 1734765 trips and 69,000,000 stoptimes - and we'd like to scale much larger than this.  Each FK relationship has a CASCADE deletion rule set.

Each week, I need to delete all the agencys that have a particular agency_import_source, and delete all their descendants too (i.e. routes, trips and stoptimes).

The obvious query, to a non-expert such as myself, is this:

DELETE FROM agencies
WHERE agencys.agency_import_source = "UK"

However, this is timing out after ten minutes, I imagine because of the huge number of foreign key indexes.  Each table also has multiple additional indexes that perhaps need to be re-built each time too -- I'm not an expert in MySQL unfortunately.

What is a sensible way of tackling this problem?  Ideally, this task needs to complete quickly with many, many more records than this.

FOOTNOTES:
1. The context of this question:  we store transit data from around the world (schedules, etc.)  The data is updated from various sources at different times, so I need to remove the old records related to a certain source and replace them with the newer data, while preserving records from other sources.

We're using MySQL 5.7 to get InnoDB spatial index support, although I hope this is not relevant - the problem persists in MySQL 5.6 too.


texts: Broken SELECT in Sql Server Compact 3.5 SP2?

texts:
I'm looking for a cause of a really strange case of SQL Server Compact db corruption. The case is that in a .NET Compact Framework app that we are running every once in a while we get into a situation where the following statement returns no rows

select * from order_item where id = '043740d1-c6d0-4fd4-ae84-fdb9218f8b81'

while

select * from order_item where id LIKE '043740d1-c6d0-4fd4-ae84-fdb9218f8b81'

returns one. Actually, when you select all rows from the table you can see that this row indeed exists in the db. The id column is uniqueidentifier, so it's not related to casing etc.

In any case, when I run the sdf file through Repair in SSMS, the first query starts returning the correct row.

I can only guess that the problem is related to some sort of index corruption - maybe it gets out of sync with the actual data store. Neither the app nor SSMS gives any sign of DB corruption - no exceptions, no messageboxes whatsoever.

Anyway, I can't really get to the root cause of this. I think it might have to do with modifying the db from different threads (at some points we may have 2 threads working on the db), but I thought this shouldn't be an issue.

Any idea what might cause this sort of issue?

UPDATE:
As @Catcall mentioned dropping the index fixed the issue, so it seems it's related to an out-of synch index. I think it's likely happening when the row is deleted - the index gets updated but the table row isn't for some reason.


texts:
I have read threads here and here and I get that elapsed time is the total duration of the task - and if the elapsed time is less than CPU time, the query went parallel.

After typing that, I was trying to improve a stored procedure's performance in an area in which we are experiencing some slowness.

The existing TSQL:
Paste the Plan

As you can see, we have 6 parameters that are all optional - a customer lookup where you can use a variety or just one variable to search.

When executed, this procedure goes parallel and the statistics IO and time are as follows:

My initial thought was to rewrite the WHERE clause to replace the COALESCE & ISNULL functions to simply ( @paramCustomerID IS NULL or c.id = @paramCustomerID )

And because I find CTE's more readable than a sub-query, I did change that portion of the query as well.

Here is the execution plan for the rewrite: Paste the Plan

And the results of the Statistics IO & Time:

The logical reads from the Customers table was cut by nearly a 1/3 and the CPU time was drastically cut, but the elapsed time is nearly double: new version was 1.2 seconds to .545 for the existing version.

I'm not an expert by any means and I am trying to learn, but the main differences I see is that the new version is performing a Key Lookup and the existing version is using Parallelism.

The advice or knowledge I'm hoping to gain here is which version of the stored procedure would give the best performance? And if the new version should be better, is there anything that could be done to make it run parallel so the elapsed time would be shorter?

Trying to clarify the question -

1) This maybe purely subjective and possibly frowned upon on this site, but based on the information provided; which procedure would you use to get the results to the end user the quickest? The proc with COALESCE/ISNULL functions in the WHERE clause the goes parallel or the revised procedure that has fewer logical reads but a greater elapsed time?

2) If we choose not to use dynamic SQL, what advice would you give to improve query performance for the revised procedure?

As I am typing the edits to try and clarify, I do see that Max has given some very useful information.

Just wanted to add, the Statistic Parser information was provided by this site.


texts: SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?

texts:
In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of @Id), it goes nuts and takes a minute or so. This is longer than the app timeout, so the app fails for the user.

In the "goes nuts" cases, the returned result set is correctly empty, as it is in many but not all the other cases.

Luckily this is reproducible both in the production and development environments.

The developer says removing "TOP 1" from the query, then making sure the app consumes the extra rows of the result set, clears up the performance problem.

The query planner suggests no indexes when TOP 1 is present. (in dev).

Changing the query and fixing the app is in progress.  Rollout takes a while.

My question: Is there any DBA-accessible way to tune or tweak the production SQL Server instance to overcome this problem before the app change with the new query rolls out?

SELECT TOP 1
       subscription_id
  FROM subscription AS sub
  JOIN billing_info AS bi ON bi.billing_info_id = sub.billing_info_id
  JOIN person_group AS apg ON apg.person_id = bi.person_id
  JOIN pplan ON pplan.plan_id = sub.plan_id
  JOIN product ON product.product_id = [plan].product_id
  JOIN product_attribute ON product_attribute.product_id = product.product_id
 WHERE apg.group_id = @Id
   AND apg.start_date < GETDATE()
   AND (apg.end_date IS NULL OR apg.end_date > GETDATE())
   AND (sub.end_date IS NULL OR sub.end_date > GETDATE())
   AND product_attribute.attribute_type = 'special feature'
   AND product_attribute.attribute_data = '1'
 ORDER BY sub.start_date ASC;


texts: Insert sum of the row data of each record into separate column in postgreSQL?

texts:
I have a table

   id   name     class  social  science math  Total
    2   Max      Three  85        56     85
    3   Arnold   Three  55        40     75
    4   Krish    Four   60        50     70
    5   John     Four   60        80     90

I have to find total of each student's marks and insert to column Total.

While running this query -

INSERT INTO student(Total)
SELECT (social + math + science ) as Total from student;

I am getting in this format -

   id   name     class  social  science math  Total
    2   Max      Three  85        56     85
    3   Arnold   Three  55        40     75
    4   Krish    Four   60        50     70
    5   John     Four   60        80     90
    6                                          226
    7                                          170
    8                                          180
    9                                          230

How to correct this I am not getting.


texts: Table queries locked during long insert SQL server

texts:
Everyday runs a sql job that loads data from bulk files and load them in auxiliary tables, to adapt and then copy to the destination table where applications read (and only read) the data.

I am focusing on a table 'Accounts' that is accessed constantly and contains over 7 million rows. The copy from the 'AccountsAux' (loaded from the bulk file) to 'Accounts' take about 10 minutes, and during that time any selection on this table gets locked.

The insert method looks like this:

INSERT INTO Accounts
SELECT * -- actually the 60 columns are here, included the primary key (not identity)
FROM AccountsAux

I've tried using WITH (NOLOCK) on the selections, but for what I've read the insertion escalates to a TABLOCK. I don't care getting incomplete results

What options do I have to avoid locking the selections during the insertion?


texts: Is this good/bad database design and why?

texts:
I am building a database for a small project. I don't have much experience so I am unsure, but I get the feeling I am not approaching this correctly.

I have a system which allows users to create courses, which contain sections of questions which they answer. I have separated out courses, sections and questions as separate entities, but the questions table requires foreign keys from:

The Users table to know who created it.
The Courses table to know what course it is part of.
The Sections table to know what section of the course it is in.

Question:
In terms of the Questions table requiring foreign keys from everywhere, is this layout acceptable? Is there any improvements that could be made?

Thanks for any help.


texts: how to make a particular field into seperate columns?

texts:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.

I have this table in the below format:

No | User | Value
01 | Port | Funds
01 | Vip1 | Systems
02 | Port | Bank
02 | Vip1 | Authority

This is how I want it:

No | Port  | Vip1
01 | Funds | Systems
02 | Bank  | Authority

Now the problem is, in this table, the User column has 6 other entries besides Port and Vip1. So I would want 6 columns and their respective values. I also want a query that I can use in other similar tables that have different User column entries. This is what I was trying to do without any success:

SELECT
   No,
   CASE user WHEN 'Port' THEN Value ELSE NULL END AS Port,
   CASE user WHEN 'Vip1' THEN Value ELSE NULL END AS Vip1
FROM table1

Please let me know what you think.


texts:
I have parent records in two tables, Table1 and Table2.  Some records in Table3 are children of Table1, and some are children of Table2.

I would like to build a query that I can use to populate a TreeView control with the data from Table3 displaying either under Table1 if it only has a parent in Table1 and not Table2, or Table2 if it has parent records in both Table1 and Table2.

Is it possible that if the table3's column(Child) is only related to table 1 to show it under table 1 and not under table 2, but at the same time another (Child) has a parent in table 2 (which usually is the case) it will show under table 2 as its currently doing.

How can I output that in a query for a TreeView? I am assuming that I will have to program the output in C#, with 3 FOR loops.  In the second loop I can check if the column is grandchild or Child and make that as a second row or 2nd node of the TreeView, but I am having a problem building a query in SQL.

The query below shows all Parent, then child then grandchild, which is working, but what is desired is at times child takes place of a father.

DECLARE @x AS XML
SET @x =
(
    SELECT DISTINCT
        Table1.AssetSysID
        , Table1.Asset_ID
        , Table1.FromLR
        , Table1.Asset_ID + ', ' + Table1.[Desc2] AS GarndFather
        , Table2.ACISysID
        , Table2.PAssetSysID
        , Table2.FeatureName + ', ' + Table2.[DESC] AS Father
        , Table3.ITMSysID
        , Table3.Item_ID + ',' + Table3.[DESC] AS Child
    FROM Table1
        LEFT OUTER JOIN Table2 ON Table1.AssetSysID = Table2.PAssetSysID
        LEFT OUTER JOIN Table3 ON Table1.AssetSysID = Table3.AssetSysID
                                AND Table2.ACISysID = Table3.ACISysID
    WHERE (Table1.AssetType = @AssetType)
    FOR XML AUTO,root('xml')
);

I've modified the code above to reflect some changes:

DECLARE @x AS XML
SET @x =
(
    SELECT
        Table1.AssetObjID AS "@AssetObjID"
        , Table1.Asset_ID AS "@Asset_ID"
        , Table1.FromLR AS "@FromLR"
        , Table1.AssetType + ', ' + Table1.StreetName + ', ' +  Table1.FromMunicNo   AS "@FirstRow"
        , (
            SELECT
                Table2.ACIObjID AS "@ACIObjID"
                , Table2.PAssetObjID AS "@PAssetObjID"
                , Table2.Feature_ID + ', ' + Table2.FeatureName   AS "@ChildOfFirstRow"
                , (
                    SELECT
                        Table3.ITMObjID AS "@ITMObjID"
                        , Table3.Item_ID + ',' + Table3.[DESC] AS "@GrandChildOfFirstRow"
                    FROM Table3
                    WHERE Table1.AssetObjID = Table3.AssetObjID
                        AND Table2.ACIObjID = Table3.ACIObjID
                    FOR XML PATH('Table3'), TYPE
                    )
                FROM Table2
                WHERE
                    Table1.AssetObjID = Table2.PAssetObjID
                FOR XML PATH('Table2'), TYPE
            )
        , (
            SELECT
                Table3.ITMObjID AS "@ITMObjID"
                , Table3.Item_ID + ',' + Table3.[DESC] AS "@GrandChildOfFirstRow"
            FROM Table3
            WHERE Table1.AssetObjID = Table3.AssetObjID
                AND Table2.ACIObjID <> Table3.ACIObjID
            FOR XML PATH('Table3'), TYPE
        )
        FROM Table1
        WHERE Table1.AssetType = 'xxxx'
        FOR XML PATH('Table1'), root('xml')
);


texts: mysql apply regexp_replace for whole column/multiple rows

texts:
I Have column exe_production inside users_acts table with data like `blah blah blah 10.10.2020 (any date) blah blah.
I need to order this column by date.
My steps:

Get date from string by next query:
SELECT REGEXP_REPLACE((SELECT exe_production FROM users_acts LIMIT 1), '[^[0-9]{2}[.]{1}[0-9]{2}[.]{1}[0-9]{4}]', '');

Convert all results strings to date by DATE_FORMAT statement.

Apply ORDER BY statement.

The problem is that I can't apply first query for multiple rows/values, only by LIMIT 1. How I can solve it?


texts: Mysql Workbench crashing when connecting

texts:
I have created an user i intend to give access to one db only.
But when i try connecting with him, the program crashes. When i connect with the root user, it works.

 create user 'test'@'localhost';
 GRANT ALL PRIVILEGES ON sbb2_test.* To 'test'@'localhost' IDENTIFIED BY 'test';
 FLUSH PRIVILEGES;

Then i have verified he has the permissions for the sbb2_test db only:

 SHOW GRANTS FOR 'test'@'localhost'

Output:

'GRANT USAGE ON *.* TO \'test\'@\'localhost\' IDENTIFIED BY PASSWORD \'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29\''
'GRANT ALL PRIVILEGES ON `sbb2_test`.* TO \'test\'@\'localhost\''

Then i have tried connecting with the new user:

I have entered "test" as the password, and the program crashed.
Why?
These are the last lines from the wb.log file:

15:58:40 [INF][      Workbench]: Current version given by meta info is: 6.1.7

15:58:41 [INF][      Workbench]: Exiting as another instance of WB is already running.
15:58:41 [INF][      Workbench]: Running the application

15:58:41 [INF][      WBContext]: Parsing application arguments.

15:58:41 [INF][      WBContext]:     C:\Program Files (x86)\MySQL\MySQL Workbench 6.1 CE\MySQLWorkbench.exe

15:58:46 [WRN][   WBContext UI]: Server instance  has no attached connection, deleting it

15:58:46 [WRN][   WBContext UI]: Server instance  has no attached connection, deleting it

15:59:17 [INF][     SSH tunnel]: Starting tunnel

15:59:17 [ERR][  GRTDispatcher]: exception in grt execute_task, continuing: Exception: Access denied for user 'test'@'localhost' (using password: NO)

15:59:30 [INF][          Admin]: Auto-created instance profile for connection

15:59:30 [ERR][      WBContext]: Unsupported server version: mariadb.org binary distribution 10.1.26-MariaDB


texts: SSIS 2012 metadata not refreshing on re-deploy

texts:
I have three systems: DEV, UAT, PROD, all running SQL 2012 Enterprise.  I have existing packages that hit objects on these systems.  Every time I have to make a change that affects metadata, it's not enough that I update the objects on the target system, then update the SSIS package that points to the system, and then re-deploy the package on the system.  Even though the package will have been updated prior to re-deploy to handle the new metadata, I still have to go onto each system and update the package AGAIN before it will recognize the new metadata.

This behavior is new to me.  In BIDS 2008, a refresh of metadata on one system would persist to any system it was deployed to. Now, if I change the connection manager prior to deploying it, I have to go back and re-do the metadata refresh before I can send it up.  It appears that the metadata per connection setting is retained and is not updated when the package is updated.

An example so I can make this clear: I add a column to a destination table on all three systems.  I update the package while it's pointing to DEV to reflect the change, and then deploy.  So far, so good.  Then I update the connection manager to point to UAT, and deploy the changed package there.  At this point I'd expect that there would not be a required refresh of the metadata, as the schema of the destination table in UAT matches the one in DEV, to which the package was pointed when it was last refreshed.  However, the package acts as if it was not updated, and needs its metadata refreshed while explicitly pointing to that system in order to work.

To try to address this problem, I added an expression for the ServerName property on the Connection Manager to be determined by System::MachineName, thinking that the problem was that changing the Connection Manager on the machine I'm developing on immediately triggered a check of the objects there, even with Delay Validation turned on.  No such luck; it still shows the same behavior.

I can't find much info about this online, to determine if it's expected behavior with SSDT 2012 or something I'm doing incorrectly or a genuine bug.

Anyone have any insight into this issue?


texts:
The definition in my book says:

  The set of all functional dependencies logically implied by F is the closure of F.

My question is, do we include FDs in F in F closure as well ?

If I have:

F = {A→B, B→C}
a) F+ = {A→C, A→BC, A→B, B→C}
b) F+ = {A→C, A→BC}

Which one would be correct a) or b) ?


texts: Count nested joined table records only if the table associated via bridge table have two records with given names

texts:
I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert some data

main_table

CREATE TABLE main_table
(
    id        serial PRIMARY KEY,
    is_active BOOLEAN,
    name      VARCHAR(50) UNIQUE NOT NULL
);

nested_table_1

CREATE TABLE nested_table_1
(
    id   serial PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

nested_table_2

CREATE TABLE nested_table_2
(
    id   serial PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

bridge_table_1

CREATE TABLE bridge_table_1
(
    main_table_id     INTEGER REFERENCES main_table (id),
    nested_table_1_id INTEGER REFERENCES nested_table_1 (id),
    PRIMARY KEY(main_table_id, nested_table_1_id)
);

bridge_table_2

CREATE TABLE bridge_table_2
(
    main_table_id     INTEGER REFERENCES main_table (id),
    nested_table_2_id INTEGER REFERENCES nested_table_2 (id),
    PRIMARY KEY(main_table_id, nested_table_2_id)
);

Query

SELECT COUNT(main.id)
      FILTER (where nes_2.name IN ('nested table second 1', 'nested table second 2') AND
                    nes_1.name = 'Contact')                              AS contact_compliants,
      COUNT(main.id)
      FILTER (where nes_2.name = 'Drop' AND nes_1.name = 'Dropley')      AS droplet_compliant,
      COUNT(main.id) FILTER (where nes_2.name IS NOT NULL AND
                                    nes_1.name IN ('Contact', 'Droplet')) AS total_opportunities
FROM main_table AS main
        INNER JOIN bridge_table_1 AS bt_1
                    ON bt_1.main_table_id = main.id
        INNER JOIN nested_table_1 AS nes_1
                    ON bt_1.nested_table_1_id = nes_1.id
        INNER JOIN bridge_table_2 AS bt_2
                    ON bt_2.main_table_id = main.id
        INNER JOIN nested_table_2 AS nes_2
                    ON nes_2.id = bt_2.nested_table_2_id
WHERE main.is_active = 'true';

The query above is working all great counting everything fine. Just one issue. In the first part for contact_compliants, it has IN which means either one of the names for nested will increment count. which is not my requirement. I want to count only when when both the names matches.

to be clear: How do I make this part where nes_2.name IN ('nested table second 1', 'nested table second 2') to count only when nes_2.name = 'nested table second 1' AND nes_2.name = 'nested table second 2'). I have tried adding double join so that I can compare but no success. I have also seen similar solution but I am not sure, how it will apply here with Having

Insert some data with expected output

INSERT INTO main_table (is_active, name, id) VALUES (true, 'main 1', 1);
INSERT INTO main_table (is_active, name, id) VALUES (true, 'main 2', 2);

INSERT INTO nested_table_1 (name, id) VALUES ('Contact', 1);
INSERT INTO nested_table_1 (name, id) VALUES ('Dropley', 2);

INSERT INTO nested_table_2 (name, id) VALUES ('nested table second 1', 1);
INSERT INTO nested_table_2 (name, id) VALUES ('nested table second 2', 2);
INSERT INTO nested_table_2 (name, id) VALUES ('nested table second 3', 3);
INSERT INTO nested_table_2 (name, id) VALUES ('Drop', 4);

INSERT INTO bridge_table_1 (main_table_id, nested_table_1_id) VALUES (1, 1);
INSERT INTO bridge_table_2 (main_table_id, nested_table_2_id) VALUES (1, 1);
-- Count for contact_compliants should be zero till here but its 1

After inserting all above data, expected output should be
    contact_compliants,droplet_compliant,total_opportunities
    0,0,1

But its actually
    contact_compliants,droplet_compliant,total_opportunities
    1,0,1

INSERT INTO bridge_table_2 (main_table_id, nested_table_2_id) VALUES (1, 2);
-- Similarly, Count for contact_compliants should be 1 after inserting above but its 2


texts:
I have a SQL dump file: C:\Users\Rayyan\Desktop\dump_2.sql.

I have been trying to import this for so long. I am using pgadmin 4, and since I could find nothing in that GUI to help, I tried using Windows command prompt.

I created my new database "wecudos" in pgadmin4.

Then tried the following command in windows command prompt:

  psql wecudos < C:\Users\Rayyan\Desktop\dump_2.sql

according to the specified format:

  psql dbname < dumpfile

This is not working, please help, I have also added a screenshot of the error/data I entered, thanks!


texts:
I have a table like this

id_project    id_field    value
1             1           abc
1             2           xfe
2             3           ufj
2             4           osj

And I have to export the data in a CSV-like manner

field_1    field_2
abc        xfe

I tried this

SELECT

(SELECT  value
 FROM    table
 WHERE   id_field = 1) AS field_1,

(SELECT  value
 FROM    table
 WHERE   id_field = 2) AS field_2

FROM table
WHERE id_project = 1;

but it does not work.

EDIT: thanks for all the answers, I think I figured out  after reading this comment https://stackoverflow.com/a/13065203/1822157


texts: Databse design for a part/subassembly list

texts:
We work with tracking the fabrication of large assemblies and sub-assemblies with a large flat Excel files. Usually separate spreadsheets for estimating material costs, fabrication costs, keeping track of submittals etc.

I've done some basic work in MS-Access over the years-enough to know there's probably a better way to manage this data--and have thought of developing a tool in Access.  The few times I've sat down to diagram out what the different tables would look like and how they would relate I get stumped by how to treat parts and subbassemblies.  To borrow the diagram from this question. The data could look like this:

Assembly (id:1)
    |
    |-Part 1: Rivet
    |-Part 2: Rivet
    |-Group 1: SubAssembly
    |   |
    |   |-Part 1: Rivet
    |   |-Part 2: Bolt
    |   |-Part 3: Bolt
    |   |-Group 1: SubSubAssembly
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-Group 2: SubAssembly
       |-Rivet
       |-Bolt

There could be parts or subassemblies (a collection of parts or more assemblies) at each level.  Practicalities aside, there's no limit to the number of levels.

The best I could think of (that doesn't involve hard coding some set max number of levels) is to have a flat table with a field listing the next higher level and somehow sort and organize on that.  That is similar to what appears on the drawing-a box labeled "Next higher level" that may contain another drawing number.  That seems to be counter to the point of using a relational database though.  I'm not looking to develop a central repository of all data, just a desktop tool that I can use to manage my data and if it proves to be useful, share with my co-workers.

What is the proper way to organize this data?


texts: Permissions needed to call DBMS_DATAPUMP from within package

texts:
I am attempting to write a procedure (within a package) that calls DBMS_DATAPUMP to copy an existing schema into a different schema. The code works when executed from an anonymous block, but from a procedure I get the following error.

[Error] Execution (1: 1): ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285
ORA-06512: at "UTILITY.MANAGE_SCHEMA", line 71
ORA-06512: at line 1

This error is being generated from the DBMS_DATAPUMP.OPEN call. Since it works in the anonymous block, I'm assuming this is actually a permissions issue (i.e. one or more  permissions granted via a role are required). However, I can't find any documentation about what permissions are necessary to use datapump.

This problem boils down to: which permissions need to be explicitly granted to a user/schema in order for a package in that schema to be able to call DBMS_DATAPUMP?

The owner of the package has both the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles, as well as the DBA role.

The procedure code is below.

   PROCEDURE copy_schema (p_source_schema VARCHAR2,
                          p_target_schema VARCHAR2,
                          p_asynchronous BOOLEAN := FALSE,
                          p_link_name VARCHAR2 := 'prddb') IS
      dph NUMBER;
      v_source_schema VARCHAR2 (30)
         := UPPER (DBMS_ASSERT.simple_sql_name (p_source_schema));
      v_target_schema VARCHAR2 (30)
         := UPPER (DBMS_ASSERT.simple_sql_name (p_target_schema));
      v_link_name VARCHAR2 (30)
         := UPPER (DBMS_ASSERT.qualified_sql_name (p_link_name));
      v_job_name VARCHAR2 (30) := UPPER ('IMPORT_' || p_target_schema);
      v_state VARCHAR2 (30);
   BEGIN
      DBMS_OUTPUT.put_line (
            'Starting copy: source_schema = '
         || v_source_schema
         || '; target_schema = '
         || v_target_schema
         || '; link_name = '
         || v_link_name
         || '; job_name = '
         || v_job_name);
      dph :=
         DBMS_DATAPUMP.open ('IMPORT',
                             'SCHEMA',
                             v_link_name,
                             v_job_name);
      DBMS_OUTPUT.put_line ('dph = ' || dph);
      DBMS_DATAPUMP.metadata_filter (dph,
                                     'SCHEMA_LIST',
                                     '''' || v_source_schema || '''');
      DBMS_DATAPUMP.metadata_remap (dph,
                                    'REMAP_SCHEMA',
                                    v_source_schema,
                                    v_target_schema);
      DBMS_DATAPUMP.set_parameter (dph, 'TABLE_EXISTS_ACTION', 'REPLACE');
      DBMS_DATAPUMP.create_job_view (dph, UPPER (v_job_name || '_vw'));
      DBMS_DATAPUMP.start_job (dph);

      IF NOT p_asynchronous THEN
         DBMS_DATAPUMP.wait_for_job (dph, v_state);
      END IF;
   END copy_schema;

I have found one solution to creating a procedure that calls datapump, but it solves the problem by putting the procedure in the SYSTEM schema, which I would prefer not to do.


texts: Postgres table size doubles are transaction completes

texts:
I have a table with 50 million rows and 40+ text columns. Size of the table is around 17GB. Inside a transaction, I add another integer column to the table and update all the rows with corresponding values. After the transaction is done, the size of the table doubles to about ~35GB.

VACUUM FULL ANALYZE brings the size of the table back to 17GB, but it takes about 10   hours to complete!
Here are the specs:

24GB RAM, 16 cores, 2.4GHz

postgresql.conf settings -

shared_buffers = 3GB
effective_cache_size = 15GB

All other settings are default.


texts:
I have two servers, one for app(rails) and one for db(mongo).My rails config for mongo will be like this(yaml file):

hosts:
  - xxx.xxx.xxx.xxx:57777
database: admin
username: root
password: some_password

So.. how can I configure on the mongo server including the 57777 port, password for root user?


texts:
I have to create a trigger that calculate the the quantity in stock and this is my first one on PostgreSQL.

When entering the quantity bought at a Provider (Fournisseur) or a delivery at a client, the quantity has to be updated.

I tried:

CREATE OR REPLACE TRIGGER CalculQStockProduit(String)
    WHEN UPDATE QuantiteEnStock ON Produit
         BEGIN QuantiteEnStock = QuantiteEnStock-Quantite
         OR    QuantiteEnStock = QuantiteEnStock+Quantite

I know this wrong, not enough accurate. Can you help me learning how to create an accurate and right trigger?

The related database scheme in UML is (in French, unfortunately):


texts: Lock Escalation happening while inserting few million records into a table in production

texts:
I want to insert few million records into a table in my production database.
I am not updating any existing rows.I am just doing

INSERT INTO TAB1
SELECT
COL1,COL2,COL3
FROM TAB2

This table has an Identity column as primary key and a non clustered key.

When i do this i get blocking and then it gets escalated to chain of bolocking.
I know that by default SQL Server doesn't do TABLE lock and default isolation level is READ COMMITTED.

Since i am inserting new rows into the table,i am sure that the application is not acquiring any shared lock(select while insert scenario).

I am confused as to why i am getting this blocking and then chain of block.

I am thinking of giving an hint to perform ROW LEVEL lock only or use any other lock hints.

Any suggestion.

Additional Info:

The target table into which i am inserting rows is being replicated using transactional replication.I am not sure whether it is affecting the insert process.


texts:
I'm trying to learn more about how SQL Server works with NUMA nodes as I noticed something in the output of sp_Blitz that I didn't understand:

Node: 0 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 1343

Node: 1 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 1343

Node: 2 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 1 Memory VAS Reserved GB: 0

Node: 3 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 1 Memory VAS Reserved GB: 0
The server has 32 logical processors so the numbers above make sense. We're running SQL Server 2016 SP2 Enterprise Edition
I have a conceptual understanding of NUMA from online reading and need to do much more research, however in the short term, I'd like to understand the meaning/importance of VAS Reserved, why it differs between the nodes above, and whether the difference is anything to be concerned about.
This is the query (taken from sp_Blitz):
SELECT  'Node: ' + CAST(n.node_id AS NVARCHAR(10)) + ' State: ' + node_state_desc
        + ' Online schedulers: ' + CAST(n.online_scheduler_count AS NVARCHAR(10)) + ' Offline schedulers: ' + CAST(oac.offline_schedulers AS VARCHAR(100)) + ' Processor Group: ' + CAST(n.processor_group AS NVARCHAR(10))
        + ' Memory node: ' + CAST(n.memory_node_id AS NVARCHAR(10)) + ' Memory VAS Reserved GB: ' + CAST(CAST((m.virtual_address_space_reserved_kb / 1024.0 / 1024) AS INT) AS NVARCHAR(100))
FROM sys.dm_os_nodes n
INNER JOIN sys.dm_os_memory_nodes m ON n.memory_node_id = m.memory_node_id
OUTER APPLY (SELECT
COUNT(*) AS [offline_schedulers]
FROM sys.dm_os_schedulers dos
WHERE n.node_id = dos.parent_node_id
AND dos.status = 'VISIBLE OFFLINE'
) oac

WHERE n.node_state_desc NOT LIKE '%DAC%'
ORDER BY n.node_id OPTION (RECOMPILE);

Thanks.


texts: Merge/combine two queries with different dates

texts:
I have one query that runs a SUM statement based on daterange 1, and worked fine, and another query that run same SUM statement, but on a different date range. I would like to merge/combine the two queries in one but I don't know how.

This is my first query:

SELECT
PROD.SezioneID
, PROD.Desc_Prod
, sample.products.VETTURA AS VetturaID
, truncate((SUM(PROD.mtscrap) / SUM(PROD.MtProdotti)) * 100, 2) AS Scrap_Week
, truncate((SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')) )
THEN PROD.mtscrap ELSE 0 END) /
SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')))
THEN PROD.MtProdotti ELSE 0 END) * 100 ) , 2
) AS Scrap_CH_OVER_Week
, truncate((SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')) )
THEN PROD.mtscrap ELSE 0 END) /
SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')))
THEN PROD.MtProdotti ELSE 0 END) * 100 ) , 2
) AS Scrap_ACICLO_Week
FROM flB.flB_prod AS PROD
JOIN FLB.flb_causali CAUSE
    ON PROD.SEZIONEID = CAUSE.IDSEZIONE
    AND PROD.GIORNO = DATASTARTPRG
JOIN SAMPLE.PRODUCTS
    ON (sample.products.SKU = PROD.SEZIONEID)
    AND (sample.products.LINEA = 'FLB')
WHERE giorno BETWEEN '2014-06-09' AND '2014-06-13'
GROUP BY SezioneID

The 2nd query is:

SELECT
truncate((SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')))
THEN PROD.mtscrap ELSE 0 END) /
SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')) )
THEN PROD.MtProdotti ELSE 0 END) * 100 ) , 2
) AS Scrap_ACICLO_Annual
FROM flB.flB_prod AS PROD
JOIN FLB.flb_causali CAUSE
    ON PROD.SEZIONEID = CAUSE.IDSEZIONE
    AND PROD.GIORNO = DATASTARTPRG
JOIN SAMPLE.PRODUCTS
    ON (sample.products.SKU = PROD.SEZIONEID)
    AND (sample.products.LINEA = 'FLB')
WHERE ProD.SezioneID IN
(
    SELECT SEZIONEID
    FROM FLB.flb_prod
    WHERE giorno BETWEEN '2014-06-09' AND '2014-06-13'
)
GROUP BY SezioneID

I would like to insert the calculated field Scrap_ACICLO_Annual next to field Scrap_ACICLO_week I hope I explained myself.


texts: Database Unallocated Space - Correct way to measure

texts:
I am confused over the web regrading few scripts on calculating the database size and free space within same.
Most of the scripts over the web uses sys.databases_files dmv to calculate free space like here and here
However if we see one from sp_spaceused or database properties via GUI amount of free space is different. I get the reason that it is diff as it has filter on type in (0,2,4) which excludes Log file.
But i am not able to understand which one is correct and why Log file is excluded from sp_spaceused or is it that scripts over web are not correct and showing something else?


texts: Oracle multiple databases only able to access one database

texts:
I am new to oracle. I've installed oracle 12c(required to be this specific version) in my windows pc with no problem. I created one database named LOCAL using Database Configuration Assistant. Then i created one more database named SIAKDB with same method.
I tried accessing both db using sqlplus system/12qwasZX@localhost/local for the LOCAL db, and sqlplus system/12qwasZX@localhost/siakdb for the SIAKDB db. I can connect to either db without problem. And i also imported dmp files to both db just fine.
However, problem arises when i restarted my pc. I can only connect to SIAKDB. When i try to connect to LOCAL db, i got this error
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I have checked that both database are running in Services.msc.

My tnsnames.ora file are as follows:
# tnsnames.ora Network Configuration File: D:\deployed\oracle\product\12.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

SIAKDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = siakdb)
    )
  )

LISTENER_SIAKDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = local)
    )
  )

LISTENER_LOCAL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

I've noticed that i created a database named ORCL when installing oracle, tried to access that database but got same error.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Somehow i only managed to access the latest DB i've made. How can i access the SIAKDB? And why does it works first time but not working after i restarted my PC?
Thanks
Edit : running tnsping local i got this:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = local)))
OK (20 msec)


texts: how to query for rows by specifying the range of row numbers in postgresql

texts:
I have an application where in the user can see the history of all questions he has posted. These are have to be displayed in the order newest to oldest. I am implementing this in Android.
I have two questions
1) I have not stored the time at which the question was asked. Does postgres, internally store the timestamp of when the row was inserted?

2) In a mobile device, only a few posts can be displayed at a time. So when the user scrolls, the new rows should be displayed. So in the database, is it possible to query like give me the top 10 rows, 10-20 rows and so on.


texts: Ranking each row in Postgres from 1-100 when comparing in groups

texts:
For each row in my table, I want to calculate its "expensiveness" when grouped together with other rows with same year/month and size.

Example data:

year,month,size,price
2017,1,300,1000
2017,1,300,1100

I tried a query like this:

select
*,
ntile(100) over (partition by year, month, size order by price) as percentile
from mytable;

The problem I have is that rows with the same price can get different percentiles, especially if a certain price is very common. Is there an alternate way to rank things from 1-100 that would give results that seem more consistent?

I don't mind if I need to create multiple views/queries to achieve this, e.g. perhaps I should calculate every percentile first and then my main query finds the "nearest" percentile, etc.

Also to note: sometimes the data may be sparse per group, e.g. less than 100 rows per group. Also, prices often group together so there will be even less discrete values.


texts: Three tables joined by a many-to-many relationship?

texts:
I'm working on creating a db model for the following requirements:

I have many Types that have many different Statuses.

Depending on the Type and Status, the Status can have certain SubStatuses.

So let's say I have

Type 1 with Status A, that has SubStatuses X and Y.

I can also have

Type 2 with Status A, that has Substatus Z

I created a Many-to-Many relationship between Type and Status and a Many-to-Many relationship between Status and SubStatus. Am I going about this the right way? Is there a better design?


texts: Optimizing "Removing duplicates" thread state

texts:
I'm trying to hunt down MySQL variables/settings and metrics relevant to the "Removing duplicates" thread state.

Here's a snippet from show full processlist.

Id      User    Host    db      Command Time    State   Info    Rows_sent    Rows_examined   Rows_read
123456  user    123.123.132.123:12345      database        Query   12601   Removing duplicates     INSERT INTO table_a ... select distinct from table_b ... group by field_a, field_b, field_c ..     0       0       0

All but Time has been redacted from the aforementioned output. You can see that its been running for quite some time (12601 seconds). Usually, the query runs within minutes. However, ever so often, the application will come across a set of id's (from the select) that have a larger result set (>100,000).

The MySQL documentation says ...

Removing duplicates

The query was using SELECT DISTINCT in such a way that MySQL could not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.

My question is, what is this "extra stage" and what variables and/or metrics would be applicable for improving performance?


texts: Get the marketdata based on present date and previous date


texts: Why can't I get the available free space after shrinking?

texts:
My disk drive was full. So I deleted some records. Then I tried to shrink and it indicated available free space of around 5GB as indicated in the below image.

Once I ran the shrink for data file, though it completed successfully I didn't get the 5GB space as indicated. Infact the disk size remained the same. Nothing was shrunk. For log file shrink it shows available space of 200MB and when I run a log file shrink it does free up 200MB as indicated. So why is it not working with data file shrink? What am I doing wrong? Please advice.


texts: SQL Server 2005/8 Query Optimisation Hints

texts:
I'm looking at educating a team on writing better SQL Server queries and was wondering what people's best hints were for improving performance.

For instance I once had a DBA who insisted that count(*) would perform worse than count(1) (I have no idea whether she was right or whether it's still valid against the latest query optimisers).

What simple things should I be telling the team to try and always use or avoid?  I'm ideally looking for things which (a) might make a reasonable difference and (b) are straight forward, 1 - 2 lines to state.


texts: What should be done before upgrading MySQL?

texts: I need to perform an upgrade of MySQL on my web server. There could be many users connected to the server at any time, performing queries or updates on the database, or uploading files to the server. Besides putting up an advanced notification that the system will undergo maintenance during a certain period of time, what should I do to avoid the scenario of stopping MySQL service or the server while some user operations are still being carried out, which may lead to a corruption of the file system or the database?


texts:
I'm a university student building a web application for a project with three collegues , I took the database portion.

Our site is basically a link sharing site and supports tags, what is an efficient way to store tags in Mysql database ? I thought of these ways :

1- table for tags : the relation between link and tag is many-to-many so I must break it with an intermediate table, I can't think of an SQL statements to fetch links with specific tags example : the user might search for links tagged with (php , mysql ) or (hardware-issues , gigabyte).

Also even if I did , it needs join , we're making the site global, that means the database will be very very huge some day , I don't think we can bare a join.

2-putting tags as an array in links table : I can put an array of strings, this means only one select statement(better than join I think) but manipulating the array will be hard, the link's poster might edit the tags on his/her link, I can figure out a way to deal with this but I'm not sure if this is the most efficient way , of course it's not normalized and the database will grow faster but I think on a server , speed is more important than size.

So is there a better way ?


texts:
This morning I’ve been trying to connect the PostgreSQL database on my Windows 7 Professional desktop.

The default value is ‘postgres’, but sure enough I forgot what password I used when I originally installed it.

I have googled and found a post related to resetting your password. I followed the steps, but the end result is a bit different then mentioned in the post. I used—

net user postgres postgres

to reset the password for my database but instead of a success message I am getting:

  "System error 5 has occurred. Access is denied."

system error. How do I avoid this error and reset the password?


texts: Jet OLEDB driver refuses to open a linked table where the original has a lock - any way to force this?

texts:
I have a requirement to read reference data from several access databases for an ETL job.  The data owners have produced a file with a series of linked tables that consolidates them all into one place.  However, if one of the base files is open with a lock (.ldb) file the OLEDB provider will refuse to open the linked table.

The SSIS package is set up with several data flow tasks reading from OLEDB sources.  To clarify, these are reference tables, and not necessarily being updated at the time - the .mdb file has a lock (.ldb) file.

Is it possible to configure the MS jet OLEDB driver to force-open the linked tables, ignoring the locks?

A couple of alternatives that we've looked into are:

Configure the linked tables the other way around - put the sources in the central database and link to them from the other access DBS.  The business are reluctant to do this, and we don't have the clout to force it.
Copy the base databases to a staging area and open the copies - this is our plan B, although it involves shifting about 2GB across a slow-ish WAN link and adds latency right at the start of the ETL job (pretty much everything is dependent on these reference tables).  I'd rather avoid the latency if possible.

EDIT: See my answer - the problem was due to lack of write permission on the lock file and an ambiguous error message returned by the driver.


texts: How can I dump the data which has been added recently?

texts:
On a daily basis I will download the data from mongodb and make a backup. I use the command

mongodump -h $MONGODB_SERVICE_HOST -o /opp/rot/llc/ttc -u 'admin' \
  -p $MONGODB_ADMIN_PASSWORD

So in this dump every day the total db is downloading. It is consuming so much space.

I want to know is there a way where we can download (dump) only the extra added data from the last dump we have made and add it to the main data?


texts: OraOLEDB.Oracle not showing up in Providers in Linked Server

texts:
Im trying to link the Oracle database to our SQL Server database. However OraOLEDB.Oracle is not in the Providers list on the Linked Servers. I have installed Oracle Database Client (12.1.0.2.0) for Microsoft Windows(x86) from the Oracle website, did a tnsping from the Oracle server that I am going to link to SQL Server, and setup my tnsname.ora file like this

 hx0014.ourserver.com =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 139.180.174.214)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =)
    )
  )

I restarted our SQL Server server afterwards, but OraOLEDB.Oracle still doesn't show up.

Can anyone guide me as to what's wrong?


texts: Multiple values in one table; need them to be in separate columns

texts: I'm relatively new to SQL so thank you in advance for any help you can give me. I'm writing a query that needs to produce a table that shows all students' majors, minors, and the dept. I've created multiple columns already: (maj, dept, min, dept, concentration, dept). My problem is that my table is producing multiple rows because I have not included anything in my query that allows for students who have multiple majors. So instead what is happening is that they are showing up in multiple rows. I need every student to occupy only one row. Students can have any combination of maj's, minor's, and concentrations (and multiple for each). I'm dealing with 5,000 students. What do I need to include in my syntax? Thank you.


texts:
We have a legacy application (ERP) that, because of technical issues, spawns a new OS process (.exe - ms windows clients) for each screen requested by the user. Also, each company branch (> 20) has its own schema, and the application deals using 1 connection for each. Since each user may open as many screens they deserve to, it is quite common that some users reach > 30 sessions.

That will result in opening many connections to Oracle.
In last report, we got 861 sessions for 87 users (~10 sessions/user).

My customer's DBA is saying that this will knock the database performance down, and demand heaps (really huge amounts) from the server's RAM & CPU.

Does this statement make sense?

So:

What are the side effects of raising the PROCESSES system parameter?
What is the cost of a open process (even if idle) in the server?


texts: how can I use a CASE with an alias defined befor case?

texts:
how can I use an alias like pop in a further case like in this example (Oracle database).

select
   (select sum(ccs_pop) from rap4) as pop,
case
    when pop+x=a+b+c then pop+x
end as sum1,
case
    when pop+y=d+e+f then pop+y
end as sum2
from rap4

The ideea is that I need "pop" in a complex situation and I need "pop" in many sentences and I need to use an alias or something...


texts: What procedure can be used to change system statistics in a production environment

texts:
System statistics aren't required but highly recommended by Oracle. From my own experience, it produced a huge improvement on the total user IO waits of our production environment when first implemented.

There isn't a need to change it regularly, however it is recommended to regather the system statistics when a big change occur, such as change of hardware.

When such a need occur, what procedure do you use to refresh it and ensure the results are beneficial?


texts: How to update a table from a another table

texts:
I have one table A has column (id, field_1,field_2),
and another table B has column (id,field_2)

Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. So how to implement that ? BTW I am using oracle

Thanks


texts: SQL Server Job does not execute when scheduled, but does execute manually, how do I get it to run as scheduled?

texts:
I am running SQL Server 2014 SP 1 CU 5.
The job did work in the past. The schedule is enabled.
The account it is running under is an ADS account which has sysadmin rights and has delete rights to the folders in question.
The same job, meaning the same step and only that step is in both, runs successfully on the test server.
The job deletes old backups, relying on the master.sys.xp_delete_file. Other jobs are running according to schedule.

I have re-created the job and still have the same problem.
I have changed the scheduled time to different times of the day.

Any ideas?

How do determine why the job will not run at the scheduled time given the information above?

Thanks!


texts:
I'm coming from an Oracle background and came across code to create a SQL Server view which I've never seen before.

We have a view called viewA (for example)

Within the script to generate the view I see this code

select columnA, columnB, columnC
from tableA as vw_tableB

The question I have is, what is the view really called?

Is it viewA or vw_tableB.

The other question I have is what is the rationale behind this?


texts: Update Server JDK and JRE after Oracle Database Installation and creation

texts: Will there be any impact on existing Oracle Database if we upgrade the server JDK/JRE versions in Linux?


texts: Does partitioning help if you don't query on the partition value?

texts:
Say I have an orders.Order table that is partitioned on CreatedWhen (a datetime column set to GetDate() when the row is created).

The table has over 10 million rows and the partition is quarterly (or some such regular interval).

If I run a query like:

SELECT ord.OrderId, ord.ClientId, ord.ReceptId, ord.Cost, agt.State
FROM   orders.Order ord
       join personnel.Agent agt
          on ord.AgentId = agt.AgentId
WHERE  agt.FirstName = 'Bob'
       and LastName = 'Whiely'
       and ord.Cost > 135

Am I going to see any benefit from the partition for this query?

I don't see how there could be a benefit because I am not using the partition value in my query and all of the table still needs to be searched.

Note: The results of the query will all be in the most recent partition.  But I don't see how that would help.

Second Note: The partitions are all on the same SAN drive (Sys Admins won't let it be any other way), so the disk IO will not be parallel.

Is there a better way to partition so that random queries, like the one above, will perform better?  (Maybe on the primary key?)

Third Note: The query above is made up.  I am using it to show that I have many queries that hit many random columns (which are properly indexed).


texts: galera_new_cluster hangs with galera_recovery

texts:
My cluster crashed and i cannot restart the first node with galera_new_cluster.
The nodes are distributed geographically in France with medium latency of 12ms.
It remains on /usr/bin/galera_recovery without doing anything.
This is my Galera config on all nodes (values may change in order to match node IP etc)...
#
# * Galera-related settings
#
# See the examples of server wsrep.cnf files in /usr/share/mysql
# and read more at https://mariadb.com/kb/en/galera-cluster/

[galera]
# Mandatory settings
wsrep_provider           = /usr/lib/libgalera_smm.so
wsrep_on                 = ON
wsrep_cluster_name       = "xxxxCLUSTER"
wsrep_cluster_address    = "gcomm://xxx.xxx.xxx.100,xxx.xxx.xxx.101,xxx.xxx.xxx.102"
binlog_format            = ROW
default_storage_engine   = InnoDB
#innodb_autoinc_lock_mode = 2

wsrep_sst_method         = rsync

# Allow server to accept connections on all interfaces.
bind-address = 0.0.0.0

#wsrep_sst_donor          = "DB2"

# Optional settings
wsrep_slave_threads      = 32
wsrep_node_name          = "DB0"
wsrep_node_address       = "xxx.xxx.xxx.100"

#innodb_flush_log_at_trx_commit = 0

Since i've been using Galera for years in his MariaDB 10.x version i know that safe_to_bootstrap has to be set 1 ecc...But nothing works with galera_new_cluster and i cannot figure out why.
This is a typical log from wsrep_recovery
021-03-27  2:06:14 0 [Note] /usr/sbin/mysqld (mysqld 10.5.9-MariaDB-1:10.5.9+maria~buster) starting as process 8286 ...
2021-03-27  2:06:14 0 [Note] InnoDB: Uses event mutexes
2021-03-27  2:06:14 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-03-27  2:06:14 0 [Note] InnoDB: Number of pools: 1
2021-03-27  2:06:14 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-03-27  2:06:14 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
2021-03-27  2:06:14 0 [Note] InnoDB: Using Linux native AIO
2021-03-27  2:06:14 0 [Note] InnoDB: Initializing buffer pool, total size = 1073741824, chunk size = 134217728
2021-03-27  2:06:14 0 [Note] InnoDB: Completed initialization of buffer pool
2021-03-27  2:06:14 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-03-27  2:06:14 0 [Note] InnoDB: 128 rollback segments are active.
2021-03-27  2:06:14 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-03-27  2:06:14 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-03-27  2:06:14 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-03-27  2:06:14 0 [Note] InnoDB: 10.5.9 started; log sequence number 57353507521; transaction id 2641101
2021-03-27  2:06:14 0 [Warning] InnoDB: Skipping buffer pool dump/restore during wsrep recovery.
2021-03-27  2:06:14 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-03-27  2:06:14 0 [Note] Recovering after a crash using tc.log
2021-03-27  2:06:14 0 [Note] Starting crash recovery...
2021-03-27  2:06:14 0 [Note] Crash recovery finished.
2021-03-27  2:06:14 0 [Note] Server socket created on IP: '::'.
2021-03-27  2:06:14 0 [Note] WSREP: Recovered position: 2edc32a3-8e96-11eb-9165-6281b552400d:7

Can someone help me figuring out why? I'm really desperated.
Thank you so much!


texts: Copy+Pasting currency value from SSMS 2012 to excel does not complete or calculate new value

texts:
So I'm trying to copy and paste some query results into Excel, and for some reason it seems to be not properly completing the first value.

Let me explain what I mean by this.

In Excel the cells I am copying into are formatted as Currency. Normally when you copy in value(s) or type it in and then press enter, it formats it properly with commas, dollar signs, decimals, etc, and then any formulas that use this cell are recalculated instantly.

However, when I copy in a set of values, the very first one does not get formatted, and it does not get calculated in a formula that uses that cell. The next 4 values work properly though, and if I try doing just one value at a time the problem still occurs.

At first I tried casting and converting the values to different SQL types like decimal, money, float, as I thought it was an issue with that, but it makes no difference.

In my actual query these values are SUMs of decimal fields, but here is an example query that does the same thing:

SELECT 123456.78, 9012.34, 567890.12, 345678.90, 123456.78;

Does anyone know what is causing this?

I'm pretty sure this is something to do with SSMS 2012 and not Excel, as it never happened to me in 2008. Not to mention there was another copy and paste issue I ran into earlier with SSMS 2012 involving carriage returns and line feeds that caused new rows when pasted into Excel that never happened in 2008.

Thanks


texts:
SQL Server 2014 SP2 CU10 Patch uninstall stuck at DowntimeTimingConfigAction.

It has been at the step
"Updating '[Instance Name]': DowntimeTimingConfigAction" for almost 2 hours.

What is DowntimeTimingConfigAction?
and at what point should I call it a lost cause?

I can't find much about it anywhere.


texts:
I have a pretty large table (~114 million rows) containing OS MasterMap data. This is freshly loaded data in a new table. When trying to set the primary key, I get this error:

ERROR:  could not create unique index "tbl_os_mmap_topoarea_pkey"
DETAIL:  Key (toid)=(1000000004081308) is duplicated.

Somehow, I have ended up with an exactly duplicated row. Every field is the same in these two rows. I want to delete one row, but keep the other. As there is no way to distinguish between the two, how can this be done?

I would like to do this as quickly and simply as possible. Creating temporary tables etc. is not really an option as it would take too long on a dataset of this size. Creating a new unique ID column would be quicker I guess, but also probably take some time.

After a bit of research, I have learned that all records in postgres have a hidden unique id, the ctid. Can I use this to delete one of the duplicate rows?


texts: In SQL Server Mgmt Studio, why don't triggers automatically get updated when tables get renamed?

texts: I ran into an issue last week where we renamed a bunch of tables in a SQL Server and it broke a bunch of triggers. I would have though SQL Server Mgmt Studio would have been smart enough to update those triggers but clearly not. Am I expecting too much? Is there a way to detect these issue where a trigger is referencing a table (or column as well) that no longer exists?


texts:
Why is it that different platforms (linux vs windows) does not support the same naming conventions? I'm stunned by the fact that the documentation uses file names as an excuse for this.

Yes, windows is not case sensitive for file names but one would think the developers would accommodate for this. I (personal preference) despise the under_score_convention and prefer camelCaseConvention.

Is there a way to enable the camel case sensitivity when connecting to the database, or is this something that has to be done in the default configuration?

Are there alternatives to MySQL that support the camelCaseConvention as per default on all platforms?

Edit

Ok, I feel a bit stupid now, I updated to mysql 5.5 and the camel-case convention is now supported, by lowering the entire name. It does not however preserve the camel-case, which is part of what I'm looking for.


texts: XQuery on Extended Events joining with dm_exec_query_plan not working

texts:
I have this XQuery for my extended event session which works fine but when I try joining the plan_handle to dm_exec_query_plan to get the query plan, it says

  Msg 257, Level 16, State 3, Line 1 Implicit conversion from data type
  varchar to varbinary is not allowed. Use the CONVERT function to run
  this query.

If I convert to varbinary, this is the error I receive.

  "Msg 569, Level 16, State 5, Line 1 The handle that was passed to
  dm_exec_query_plan was invalid. "

If I manually pass the handle into the DMF, it works just fine. This is how it looks like in varchar format (after adding the 0x)

  0x06000700f9971028f01033ae0300000001000000000000000000000000000000000000000000000000000000

SELECT
        q.Time
        ,q.[CPU (ms)]
        ,q.[Duration (s)]
        ,q.[SQL Statement]
        ,q.plan_handle
FROM (
SELECT
        data.value(     '(/event[@name="sql_statement_completed"]/@timestamp)[1]', 'DATETIME') AS [Time]
        ,data.value(        '(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)]
        ,CONVERT(float, data.value('(/event/data[@name="duration"]/value)[1]', 'BIGINT')) / 1000000
        AS [Duration (s)]
        ,data.value(
        '(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement]
        --,data.value('(/event/action[@name=''plan_handle'']/value)[1]', 'varchar(4000)')       AS [PlanHandle]
        ,'0x' + CAST(data.value('(/event/action[@name="plan_handle"]/value)[1]', 'varchar(4000)') AS varchar(4000)) AS plan_handle
    FROM (
        SELECT
            CONVERT(xml, event_data) AS data
        FROM sys.fn_xe_file_target_read_file
        ('H:\SQLskills\EE_ExpensiveQueries*.xel', 'H:\SQLskills\EE_ExpensiveQueries*.xem', NULL, NULL)
    ) entries
    )q
    CROSS APPLY sys.dm_exec_query_plan(plan_handle)


texts: How to find Linear interpolation without using pivot and unpivot in SQL Server 2008


texts: Schema designing for handling multiple payment gateways

texts:
This is more of a question that requires feedback.
I'm designing a database that handles multiple payment gateways. A payment gateway mostly requires a table for order details before making the payment(this is common for all PGs), and a table for transaction details, for storing response after making the payment.

Now to handle multiple payment gateways, I can either keep a single transaction table, stuffing it with all the fields available from all the payment gateways and a field that says which PG that row is from;
Or, I can create separate transaction tables for each of the PG with prefix like paypal_ or bank_ etc, each having the fields each of them need.

I'm just not sure which is the more optimum way of doing it. Also need to learn it for similar scenarios I might come across in future.


texts: One Big Database vs. Several Smaller Ones

texts:
We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g.,

Setup "A":

central_database
  app1_table1
  app1_table2
  app1_tablen
...
  appn_table1
  appn_table2
  appn_tablen

The end result being a large db with many tables.

Setup "B":

app1_db
  table1
  table2
  tablen

...

appn_db
  table1
  table2
  tablen

The end result being many databases with some tables.

All things equal (e.g., amount of data, number of app instances, etc), what are the pros and cons of going with either approach? What would be detrimental to database performance and maintenance? The application is PHP 5 based, run over Apache 2.x, and we're running MySQL 5.x.

Many thanks for your time and thoughts!


texts: SQL Server using index scan instead of seek when updating a view that has an INSTEAD OF UPDATE trigger via a cursor

texts:
I have a 3rd party application which uses a cursor that updates rows in my SQL Server 2014 table. I am unable to modify this code and have therefore devised a scheme to point the cursor to a view which has an INSTEAD OF UPDATE trigger where I can intercept and fully control the update. I am very aware that cursors are evil, but I am forced to work with them because I cannot modify the source programs.

When the UPDATE...WHERE CURRENT OF statement executes, it's performing a clustered index scan rather than a seek to locate the record that the cursor is currently pointing at. I cannot determine why the optimizer is doing a scan when it could be doing a seek. I believe that it's a combination of cursor + view + instead of update trigger that is causing the problem, because if I remove any one of these 3 variables from my tests, the index seek is used properly.

Note that it doesn't matter how many rows are in the table; the optimizer always uses a scan, according to the execution plan. I've even removed all of the logic within the trigger to further simplify the test.

Here's some simple code to recreate the issue:

CREATE TABLE [dbo].[Person](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](40) NULL,
  CONSTRAINT [PK_Person_Id] PRIMARY KEY CLUSTERED ( [Id] ASC )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
  ON [PRIMARY]
GO

CREATE VIEW [dbo].[vPerson]
AS SELECT Id, Name FROM dbo.Person
GO

CREATE TRIGGER [dbo].[InsteadOfUpdate_vPerson] ON [dbo].[vPerson]
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON
END
GO

INSERT INTO Person VALUES('John Doe');
GO

--This will use a seek
UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE Id = 1
GO

--This will not use a seek
DECLARE c CURSOR FOR
SELECT * FROM dbo.vPerson
WHERE Id = 1
FOR UPDATE

OPEN c
FETCH NEXT FROM c

UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE CURRENT OF c
CLOSE c
DEALLOCATE c
GO

Any help or suggestions on things to try would be greatly appreciated.


texts: How to sum the buy order and sell order in one row per each ticker

texts:
I have a table containing some trading information of several tickers and I would like to divide the data into sum(Amount) for buy and sell separately, grouping by each ticker.

I have one table containing the following: iOrder 1 = Buy, -1 = Sell.

Ticker | iOrder | Price | Amount
1,1,90,180
1,1,89.5,179
1,-1,91,91
2,1,5,20
2,-1, 4,16
2,-1,3,9

My Expected resulting Table:

Ticker | BuyAmount |  SellAmount
1, 359, 91
2,20, 25

Ticker 1 Buy amount 359  =two Buy order with amount equals 180 and 179 , therefore sum of the buy order for ticker 1 = 180+179 = 359

I just know how to do one table containing Ticker and BuyAmount.

 select Ticker, sum(Amount) as BuyAmount
    from AAMMNews where iOrder = 1 group by Ticker

Using union or join? Please help, experts. Thanks.


texts:
Using OpenSUSE leap 15.1
10.2.29-lp151.2.9.1-x86_64 from vendor OpenSUSE (installed)

CREATE TABLE testtab2(
    my_id2 INT AUTO_INCREMENT,
    name2 VARCHAR(10),
    comment2 VARCHAR(20),
    PRIMARY KEY (my_id2)
);

testtab.txt:

|Roger |hurray   |
|George|today    |
|Paul  |yesterday|
|John  |last year|
|Ringo |tomorrow |

LOAD DATA LOCAL INFILE 'testtab.txt' INTO TABLE testtab FIELDS TERMINATED BY '|';

works fine with linenumbers (my_id) 1-5

INSERT INTO testtab (name,comment) VALUES ('Beatles','band');

this gets linenumber (my_id) 8 - should be 6.

If testtab.txt have 8 records and using INSERT INTO 1 record this gets linenr (my_id) 16.
This happens in other tables too - one moved from 199 to 256.

Anyone who can solve the problem?


texts: SQL Server Failover Cluster Scalable by adding nodes?

texts:
In the book Pro SQL Server Always On Availability Groups They write about scalability. Here is what they say

  Windows Server 2012 (and above), failover clustering supports up to 64
  cluster nodes. This helps to easily scale out the solution by adding
  nodes to it. For example, if you have a two-node failover cluster
  hosting applications that is getting close to hitting a node-specific
  capacity limit such as a CPU or memory, it is very easy to add a third
  node and redistribute the load.

How is that possible? If you have one instance on two nodes and you need to add memory. How will another node help? You can't load balance to 3 nodes. I completely understand running two instances ( one on each node) but then adding a 3rd will provide what?


texts: Can I generate binary logs if they weren't turned on?

texts:
Ok, so here's the scenario:

Our master went down, boom! gone (Call this one DB1)
We switched our backup replica server. Ok, life is good, sort of (Call this DB2)
Binary logs were not turned on for DB2, therefore we are missing a few hours of logs
We want to bring another server up and sync with DB 2

Question: Can we generate the logs for the few hour gap? Or are they gone? I have the logs for everything up to that position and after but not during.

Followups:
Data is both InnoDb and MyIsam. The binary logs have been turned on for DB2. The issue is that they were turned on 2 hours after the server was in production. So there is two hours worth of binary logs that are missing

If i'm down voted, please tell me what I can do to improve my question.


texts: How do I change the DEFINER of a VIEW in Mysql?

texts:
When I run mysqldump, I get an error:

mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES

This makes sense because foobar is a legacy machine that no longer exists.

How do I change the definer of all my tables to 'root'@'localhost'?


texts:
So not too sure how to go about this, but ideally I want to pull out matching & non-matching info from an array of rows in a table, so I have a table with info like this:

12,2019-07-15 06:02:52,sw72,56,19,DISC,5038-fc:cf:62:88:e9:00
12,2019-07-15 06:02:52,sw72,56,20,DISC,5038-fc:cf:62:88:e9:00
12,2019-07-15 06:02:52,sw72,71,19,DISC,5047-fc:cf:62:88:e9:00
12,2019-07-15 06:02:52,sw72,71,20,DISC,5047-fc:cf:62:88:e9:00
12,2019-07-15 06:02:52,sw72,83,19,DISC,5053-fc:cf:62:88:e9:00
12,2019-07-15 06:02:52,sw72,83,20,DISC,5053-fc:cf:62:88:e9:00
12,2019-07-15 06:02:52,sw74,1,44,DISC,7001-00:25:03:b7:cc:00

13,2019-07-16 06:02:46,sw71,111,23,DISC,706f-00:25:03:b7:cc:00
13,2019-07-16 06:02:46,sw71,111,24,DISC,706f-00:25:03:b7:cc:00
13,2019-07-16 06:02:47,sw72,112,19,DISC,5070-fc:cf:62:88:e9:00
13,2019-07-16 06:02:47,sw72,60,19,DISC,503c-fc:cf:62:88:e9:00
13,2019-07-16 06:02:47,sw72,83,19,DISC,5053-fc:cf:62:88:e9:00
13,2019-07-16 06:02:47,sw72,83,20,DISC,5053-fc:cf:62:88:e9:00
13,2019-07-16 06:02:47,sw74,1,44,DISC,7001-00:25:03:b7:cc:00

What I want to do is pull out the differences based on column 1 (so select * from table where column1 = 12 or 13) then pull out the differences but ignoring the datetime in column2..
So column 3,4,5 needs checking..

i.e. rows 1 & 2 in the top set of data, do not match the 2nd set of data (but all in the same SQL table)..

then I want to also pull out the differences too..  so in the 2nd set of data I want to return the lines that don't match (in a separate query)

So I guess my question is, can i use different queries to pull out the mismatched info from either set of data, then the matching data?

perhaps i need 3 separate queries, but not too sure where to start on this one as I'm not a SQL DBA, just a basic SQL admin.

Many thanks in advance for any assistance!


texts: Should I use MongoDB or a relational database management system for this scenario?

texts:
I have a different item types, each item has different number of attributes some attributes might contain an arbitrary number of values. For example, a building which has amenities attribute, this attribute has many different possible values a user should be able to select these values for the item Building from a list. Some examples of these values are Balcony, Pet Allowed, WiFi. Another item is Car this car have different attributes like Model, Speed, Class each of them has a single possible value.
In addition to Attributes I have a different nested Categories for each item, for example a Vehicle category, is a parent of Car category which is also a parent of BMW Car category.
As you can see, the second requirement can be modeled easily for a Relational Database but the first one is very hard to model for Non-relational Database like MongoDB. My app will be used by huge number of users, and both categories and attributes are subject to Insert/Update/Delete operations also they may be involved in some reports generation process.

I think about using MongoDB as DBMS, but I'm not sure yet about this decision.


texts: Querying a view that is defined as "select * from"

texts:
I am trying to understand how columns/indexes are used when querying a view that is defined as

select * from someTable where someColumn=someValue

Will using this view always be bad, or will SQL Server take into account the actual columns selected and use appropriate indexes in a query like

select column1, column2 from theView


texts: How can I determine which tables are causing the database to grow over time?

texts: I have a database in SQL Server 2008 that is growing all the time, and I'd like to know which tables have caused the most growth over the past 6 months.


texts: How to upgrade MySQL 5.0 to 5.5 with master-master replication?

texts:
I have to update 2 hosts with SUSE Linux Enterprise 11 from SP2 to SP3 which implies an update from MySQL 5.0.94 to 5.5.31.

The release notes state:

  SLE 11 SP3 introduces the upgrade of the MySQL database to version
  5.5. This upgrade involves a change of the database format and the database needs to be converted before MySQL can run again. Therefore
  MySQL is not running directly after the upgrade.
  To migrate the MySQL
  database, run following commands as root:

  touch /var/lib/mysql/.force_upgrade

  rcmysql restart

Now the tricky part is that both hosts are configured with a master-master replication. Read access should be possible during the update, write access is not needed. How do I do this?


texts: Oracle 12cR2 - ORA-00905 When Creating View

texts:
I want to create a view as below:
CREATE OR REPLACE VIEW "USERNAME"."VIEWNAME" ("columnName", "columnName", "columnName", ...) AS
  SELECT DISTINCT table_name.column_names AS columnName, table_name.column_name AS columnName
, table_name.column_name AS columnName, table_name.column_name AS columnName, table_name.column_name AS columnName, table_name.column_name AS columnName
, table_name.column_name AS columnName, table_name.column_name  ,table_name.column_name,table_name.column_name AS columnName,table_name.column_name,table_name.column_name,table_name.column_name,table_name.column_name
,table_name.column_name,table_name.column_name,table_name.column_name, table_name.column_name,table_name.column_name,table_name.column_name
FROM USERNAME.TABLENAME TN
LEFT JOIN TABLENAME COLUMNNAME ON TABLENAME.COLUMNNAME = TABLENAME.COLUMNNAME
LEFT JOIN TABLENAME TB ON (table_name.column_name = table_name.column_name AND table_name.column_name = table_name.column_name AND table_name.column_name = table_name.column_name)
LEFT JOIN (select distinct table_name.column_name,table_name.column_name,...  from table_name tblnm left join  username.tablename tanm
            on tablename.columnname = tablename.columnname left join ... on
            (... = ... and  ... = ... and ...=...)
            where ... = 'OPEN' and
            ... IS NOT NULL and ... IS NOT NULL
            ) DD ON ...=... AND ...=... AND ...=...
WHERE ...='OPEN'
and ... IN ('somewords','somewords')  AND ... = 'ACTIVE' AND
... NOT IN ('somewords','somewords','somewords','somewords','somewords','somewords','somewords','somewords','somewords')
/

It gives ORA-00905.
I couldn't fine any clear solution.
Regards,


texts: What are the risks of changing oracle sys password?

texts:
I need to change some old databases SYS password because of security. I'm not DBA of these databases. I don't know if I will change SYS password what it will be happen.

What are the risks of changing oracle SYS password? What will be happen and how can I back to old password if I get some error?

I don't know current passwords, just I have root passwords of database machines(RAC / 2 machines).

Thank you.


texts: SQL Replication - Distribution database backlog of commands for one table

texts:
Our infrastructure is as follows:

Publisher on Live db server, Distibutor and subscriber on our mirror server.

Basically yesterday we were doing archiving on a huge table on our live server, (tblEventlog), and I was deleting rows month by time, millions of records. This caused the transaction log to fill up completely...so when we restarted server and got the space back, we now have 144225820 commands in our distribution database (on mirror)...and estimated time of 27 hours to apply and catch up.

Basically I am looking for the quickest fix to this - I know the offending table is tblEventLog. Can we drop this from the mirror and then export a copy (with the deletes applied) from Live...would this have any effect on the transactions waiting in the distribution db? Would the distributor still try and apply them even though the rows are now gone, or would it just skip them?

If so, whats the best way out of this...would rather not reinitialise as its a huge db and stresses live server.

Publisher is SQL Server 2008, Subscriber server is SQL Server 2012.


texts: Oracle: Quick way to list all database links

texts:
The title says it all, is there a way to quickly list all the current database links in oracle?

Something along the same lines as this, which lists the current user's tables:

select * from user_tables;

Thanks,
GC.


texts: How best to export from Oracle Ent Ed (EE) to Oracle Express Edition (XE)?

texts:
I am aware that Oracle Ent Ed (EE) has more functionality than Oracle Express Ed (XE).  But I would like to do a FULL export from EE to XE.  Is this even possible?  The only solution I can assume will work would be to export/import tablespaces or schemas.

A comment was made that this question was not precise so let me clarify (hopefully):

EE has features that XE does not have.  It follows that EE may have schemas that XE does not have or use.  When the import is done, will the import fail in XE because those 'extra non-XE' schemas are not recognized due to the absence of the feature in XE?  Or will XE just 'not fail, not care'?

NOTE: I was hoping to do a FULL export which is why I am concerned about the schemas being different between EE and XE.

HTH


texts: Get all dates in the current month report from three table with date wise sum and balance

texts:
I have three seperate table without any foregin key or relations each..

I need a Summary like this

Now, what and how will be mysql query.. I am very new web developer... please kindly lets me know details. pleas help me on this issues

refference links :: https://stackoverflow.com/questions/6676653/mysql-query-two-tables-get-data-in-date-wise-or-user-wise


texts: Slow query when using ORDER BY in Postgres

texts:
This query

SELECT *
FROM
  ( SELECT event_id
   FROM events_201806
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201805
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201804
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201803
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN') AS ids
NATURAL INNER JOIN events;

Returns 456 results and takes about 20 ms.

Adding ORDER BY

SELECT *
FROM
  (SELECT event_id
   FROM events_201806
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201805
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201804
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201803
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN') AS ids
NATURAL INNER JOIN events
ORDER BY event_ts DESC;

causes the query to take a very long time to complete, about 10 minutes.

Any pointers on how to fix this?


texts: How to specify column positions in insert statements generated by pg_dump?

texts:
I need to add a column to a specific position in a table. I'm doing this because I'm merging tables from two different databases:

DB1.schema1.table1 has these columns:

name_pk | date | type

while DB2.schema1.table1 has these:

name_pk | date | value | type

I need to add value in the first database so that I can dump its data and restore it to DB3 (along with DB2 data, DB3 has the same columns than DB2).

What would be the best way to solve this? I thought:

Just add the "value" column to DB1 before dumping the data. However, because PostgreSQL doesn't have INSERT ... AFTER clause, the column gets added to the end, which is not good.
Dumping the table with schema-only into schema.sql, editing the resulting file manually and adding the missing column to the right spot. It does the trick, but it requires manual work and not suitable for my script going through multiple databases.
Adding column to the end, then adding the rest columns after that and deleting the ones before it. This requires finding out the names and types of the rest of the columns.
Is there any other way of doing this easily?


texts:
I am in a situation where I need to query a database for data, but am struggling on how to properly form the query.  Essentially I need to get data from the following tables:

Table: Contact
    contact_id INT [Primary Key]
    first_name VARCHAR(50)
    last_name  VARCHAR(50)

Table: Contact_Phone
    contact_id   INT [Foreign Key/Composite Primary Key]
    phone_number VARCHAR(15) [Composite Primary Key]
    extension    VARCHAR(10), NULL

Table: Contact_Fax
    contact_id   INT [Foreign Key/Composite Primary Key]
    fax_number   VARCHAR(15) [Composite Primary Key]

Table: Contact_Email
    contact_id   INT [Foreign Key/Composite Primary Key]
    email        VARCHAR(100) [Composite Primary Key]

Now not every single contact in my Contacts table has a Phone, Fax, or Email.  Some just have a phone or fax.  Others just an email.  There is one contact I know only has a phone number.

Now my issue is, if I try and make a simple query joining all of these tables look for specific contact information, if for any one of them there is no entry (for example, my one contact which doesn't have a fax or email) then I get no data being returned at all.

So the question is, how do I put this query together so that if I am trying to look up a specific contact's information, I will still be able to see all of the data (Phone/Fax/Email) even if one of them happens to be blank?


texts:
I've been trying to figure out how I can completely backup my PostgreSQL database.
I have not seen any resource that explains how to do a complete backup.

I am aware of the pg_dump and pg_dumpall utilities.  The syntax is simple, but the details are not very clear.

It appears that pg_dump only backs "some" tables for a particular db and none of any type of configuration info. I am guessing that things like permissions and many other things are not backed up. So in the event of a total re-install of a PostgreSQL db, the dump backup would not work without significant manual configuration and setup.

pg_dumpall backs-up some more things than pg_dump but again no clear info is provided in the Postgres docs about what else is left out from the backup dump and needs to be backed-up in addition to.

I need to back up all PostgreSQL configuration info, tables, indexes, permissions, schemes, etc.  In the event of a complete catastrophe, provided that I reinstall the same version of PostgreSQL I should not
have to reconfigure anything.

I don't care for example if the indexes are re-built from scratch of if the backup already stores the indexes... stuff like this doesn't matter to me... I just want the db to be in the exact same state after the restore as it was at time of backup.

I'd appreciate if someone could list the things needed to be backed-up in order to achieve a complete backup.  Of course, I'd appreciate if you would recommend some tools along with some procedure for the various things on the list.  Please explain which items on the grand list the tool & procedure you recommend covers.


texts: Most restricting permissions for querying view with linked server

texts:
I've created view to query another server via linked server(SQL Server 2014).

I need to grant permissions to developers to use this view.
Granting only select is not enough and they got the following error:

  Access to the remote server is denied because no login-mapping exists.

What is the most restricted permission that I can grant them to be able to select from this view?

We use a SQL login to connect to linked server (be made using this security context), and I can query this view with my user. I can also query this view with my Active Directory user. I didn't try to connect to access the linked server with linked server login, but my AD user couldn't connect to linked server, if there were any issues with connecting login.

For testing purposes, I granted sysadmin to this user. It could query this view, so it is definitely a permissions issue. Prior to granting sysadmin, the user had only public, with no special permissions at the server level and only db_reader in the view's database.


texts:
I had a query that was taking way too much time despite having all the indexes I could think of.

Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to another data type because the column from table A is of type varchar while the matching column of table B is of type integer.

I changed my code to use a temporary table in which I insert the rows I need from table A and cast the column from varchar to integer.

It improved the query speed by about a 1000x!

Table zone_site:

CREATE TABLE traitements.zones_sites (
    pkid serial PRIMARY KEY,
    pkid_site integer NOT NULL,
    origine varchar(50) NOT NULL,
    origine_id varchar(255) NOT NULL,  -- can't be converted to int
    catégorie varchar(255) NOT NULL,
    horodatage timestamp NOT NULL,
    geom geometry(MultiPolygon,2154),
    précision_contour varchar(100),
    statut varchar(100),
    détails_jsonb text,
    CONSTRAINT enforce_dims_geomloc CHECK (ST_NDims(geom) = 2)
);

CREATE INDEX zones_sites_idx_pkid_site  ON traitements.zones_sites (pkid_site);
CREATE INDEX zones_sites_géométrie ON traitements.zones_sites USING GIST (geom);
CREATE INDEX zones_sites_précision_contour ON traitements.zones_sites (précision_contour);
CREATE INDEX zones_sites_idx_catégorie ON traitements.zones_sites (catégorie);
CREATE INDEX zones_sites_idx_origine ON traitements.zones_sites (origine);
CREATE INDEX zones_sites_idx_statut ON traitements.zones_sites (statut);
ALTER TABLE  traitements.zones_sites
    ADD CONSTRAINT zones_sites_references_sites_candidats FOREIGN KEY (pkid_site)
    REFERENCES traitements.sites_candidats(pkid) ON DELETE CASCADE;

Related question on dba.SE:

Join on different types

Unfortunately, the column traitements.zones_sites.origine_id cannot be of type integer because it holds identifier from several origins, some of them not being integers.

Original query:

SELECT emprises.pkid, emprises.pkid_site, emprises.origine, parcelles.idpar
FROM traitements.zones_sites AS emprises
JOIN parcelles ON parcelles.idpk::varchar = emprises.origine_id
WHERE emprises.catégorie = 'emprise_site'
AND emprises.précision_contour = 'contour_inconnu'
AND emprises.origine = 'xxxxxxxxx';

Modified code:

CREATE TEMPORARY TABLE temp_emprises (
    pkid serial PRIMARY KEY,
    pkid_site integer NOT NULL,
    origine varchar(50) NOT NULL,
    idpk_parcelle integer NOT NULL
);

INSERT INTO temp_emprises (pkid, pkid_site, origine, idpk_parcelle)
SELECT pkid, pkid_site, origine, origine_id::integer
FROM traitements.zones_sites AS emprises
WHERE emprises.catégorie = 'emprise_site'
AND emprises.précision_contour = 'contour_inconnu'
AND emprises.origine = 'xxxxxxxxx';

CREATE INDEX ON temp_emprises(idpk_parcelle);

SELECT emprises.pkid, emprises.pkid_site, emprises.origine, parcelles.idpar
FROM temp_emprises AS emprises
JOIN parcelles ON parcelles.idpk = emprises.idpk_parcelle;

Is there a better solution?