texts: Set variable inside cursor for to use in select statement

texts:
This is my query to create procedure, but its displaying error like :

  'DECLARE curBalRows CURSOR FOR

  select if(

What I'm missing here?

    DELIMITER $$

drop procedure if exists `getAllBalanceRowsUpdate`$$

CREATE PROCEDURE `getAllBalanceRowsUpdate`(
    IN `p_ind` INT(20),
    IN `p_scode` VARCHAR(200)
)

BEGIN

DECLARE finished INT DEFAULT 0;
DECLARE balanced DECIMAL(20,3) DEFAULT 0;
DECLARE orderId INT DEFAULT 0;

select @redeem_unit = (SELECT sum(allotted_unit) from orders where investor_id=p_ind and scheme_code=p_scode and type='R' and status in ('approved', 'awaiting'));
select @balance=0;
select @redeem_unit = round( ifnull( @redeem_unit, @redeem_unit:=0), 3 );

DECLARE curBalRows CURSOR FOR

select if( allotted_unit >= @redeem_unit, @balance:= allotted_unit - @redeem_unit, @balance:= 0 ) as balanced, if( allotted_unit >= @redeem_unit, @redeem_unit:=0, @redeem_unit:= @redeem_unit-allotted_unit ) as leftunit, order_id, allotted_unit, allotted_nav, allotted_nav*@balance as balanceAmount, datetime from orders where investor_id = p_ind and orders.scheme_code =p_scode and type in ('L', 'S') and status = 'APPROVED';

DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET finished = 1;

OPEN curBalRows;

getRows: LOOP
    FETCH curBalRows INTO balanced, orderId;
        IF finished = 1 THEN
            LEAVE getRows;
        END IF;

                UPDATE orders SET balance=balanced where order_id=orderId;

END LOOP getRows;
CLOSE curBalRows;

END $$

DELIMITER ;


texts: Find the highest graded student using Tuple Relational Calculus

texts:
Suppose I have a table of students, containing their ID and their grade:

-----------------
| id  |  grade  |
-----------------
|  1  |    83   |
|  2  |    94   |
|  3  |    92   |
|  4  |    78   |

How do I write a Tuple Relational Calculus formula that refers to the student with the highest grade?

My attempt:

Thinking in terms of SQL, I would write a query that does a cartesian product of the table with itself, take every grade that is less than some other grade, and then subtract from the original table. However, in Tuple Relational Calculus, it's not possible to build sub-tables in sub-queries, which is why I'm stuck.

However, I've attempted something in this direction:

{ <id> | Ǝ grade1 ∈ students (id, grade) ⋀ Ǝ grade2 ∈ students (id, grade2) ⋀ grade1 > grade2}

I believe this would get me the lower grades, but how do I subtract this all from the original table of students? I'm not allowed to insert this statement into another TRC query. Thanks in advance for your help!


texts: How to retrieve elements from another table in a SQL query?

texts:
Sorry for my english !!!

I get data from a table wp_posts and now I need to get data from the table wp_postmeta that match the ID captured in wp_posts table.

The data that I need to grab in the table wp_postmeta are product_img1, product_img2, product_img3 as shown in image below.

This is a view of table wp_posts.

I need to show ID, post_title, product_img1, product_img2, product_img3 and my SQL query is:

require("configs/conxao.php");
$datas = $pdo->query("SELECT p.ID, p.post_title, pm.product_img1, pm.product_img2,
                              pm.product_img3
                          FROM wp_posts p
                          JOIN wp_postmeta pm
                          ON p.ID = pm.post_id
                          WHERE p.post_type = 'wpcproduct'
                    ");
$datas->execute();

while($donnees = $data->fetch(PDO::FETCH_ASSOC)){

     How to show this data here?

}

  Can you help me to show this data for ID 103?


texts: Setting up binary replication between two PostgreSQL instances

texts:
I have two virtual machines: dbrepa (-.175) and dbrepb (-.191).
Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on dbrepa, dbrepb was made as a clone.
I need to have the databases on dbrepb be read-only copies of those on dbrepa.

And so, I set the appropriate settings of dbrepa/postgresql.conf:

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3 # Aside, I assume '3' is arbitrary

and dbrepa/pg_hba.conf (paying attention to spacing):

host   replication   all   192.168.108.191/32   trust

and dbrepb/postgresql.conf:

hot_standby = on

and dbrepb/recovery.conf (a new file):

standby_mode = 'on'
primary_conninfo = 'host=192.168.108.175'

Each database is empty, but the following command was run on both:

[root@dbrep-]# service postgresql-9.2 initdb

Now, since there are no databases, I don't need to worry if they are out of sync (since there is no data to be out of sync).
I start the master:

[root@dbrepa]# service postgresql-9.2 start
Starting postregsql-9.2 service:   [  OK  ]

and I start the standby:

[root@dbrepb]# service postgresql-9.2 start
Starting postregsql-9.2 service:   [FAILED]

Examining the log files of each:

dbrepa/data/pg_log/-.log is empty.

dbrepb/data/pg_log/-.log:

LOG:  entering standby mode
WARNING:  WAL was generated with wal_level=minimal, data may be missing
HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
FATAL:  hot standby is not possible because wal_level was not set to "hot_standby" on the master server
HINT:  Either set wal_level to "hot_standby" on the master, or turn off hot_standby here.
LOG:  startup process (PID 4320) exited with exit code 1
LOG:  aborting startup due to startup process failure

but when I double-check dbrepa/data/postgres.conf, wal_level is clearly set to hot_standby.

What else could be going wrong?


texts: Error 23 on transaction log backup, full backup works fine

texts:
I have a database with the database files on the E: drive, and logs files on the D: drive.

I'm getting the following error when trying to run a transaction log backup:

  Read on "D:\LOG\AOA_Log.ldf" failed: 23(Data error (cyclic redundancy check).)

I'm also getting a bad block message in the Windows event log.

A Full database backup works fine.

Can I change the database property to simple restore and shrink the log file?

Can I run chkdsk on the D: drive (need to dismount) without messing up the database?

The server is a VMWare guest with the data residing in a disk array.


texts: Making dynamic queries for linked server in SQL Server

texts:
I want to create a dynamic query in SQL Server which will run on linked server. I am trying to do it as follows.

USE [MYDB]
GO

DECLARE @company AS nvarchar(50);
DECLARE @id nvarchar(MAX);
DECLARE @query nvarchar(MAX);

SET @company = 'mycompany.com';
SET @query = N'SELECT @csid = id FROM OPENQUERY(LINKSERVER12,
   ''SELECT id from company where name = @comp'')';
EXECUTE sp_executesql @company_query, N'@comp nvarchar(50), @csid
nvarchar(MAX) OUTPUT', @comp = @company,@csid = @id OUTPUT

In the above script, I want to pass the value for @comp dynamically. For that I tried setting input and output variable while executing SQL with sp_executesql.

I am getting the following error

  Syntax error in SQL statement. Syntax error line 1 at or after token .[10179].

  Msg 7321, Level 16, State 2, Line 4
  An error occurred while preparing the query "SELECT id from company where name = @comp" for execution against OLE DB provider "MSDASQL" for linked server "LINKSERVER12".

The error is happening at the dynamic query

N'SELECT @csid = id FROM OPENQUERY(LINKSERVER12,
   ''SELECT id from company where name = @comp'')'

I tried replacing @comp in the SQL query with ''@comp'', ''''@comp'''' with no luck. Any help is greatly appreciated.


texts: Oracle.exe (SHAD) process keeps increasing

texts:
I have an Oracle Server 11g that we use for integration tests. For some reason, the processes count [select count(*) from v$process;] keeps increasing up to the limit, causing ORA-12514: TNS:listener does not currently know of service requested in connect descriptor error when the limit is reached. I tried increasing the processes limit to 300. But after a while the database will still reach this limit and have the same error. When I tried querying

select program, count(*) from v$process group by program order by count(*) desc;

I found that almost all of the processes are owned by Oracle.exe (SHAD)

PROGRAM                                                            COUNT(*)
---------------------------------------------------------------- ----------
ORACLE.EXE (SHAD)                                                       135
ORACLE.EXE (J001)                                                         1
ORACLE.EXE (Q001)                                                         1
ORACLE.EXE (PMON)                                                         1
ORACLE.EXE (GEN0)                                                         1
PSEUDO                                                                    1
ORACLE.EXE (LGWR)                                                         1
ORACLE.EXE (Q000)                                                         1
ORACLE.EXE (W000)                                                         1
ORACLE.EXE (DBRM)                                                         1
ORACLE.EXE (DBW0)                                                         1
ORACLE.EXE (DIAG)                                                         1
ORACLE.EXE (DIA0)                                                         1
ORACLE.EXE (MMAN)                                                         1
ORACLE.EXE (SMON)                                                         1
ORACLE.EXE (RECO)                                                         1
ORACLE.EXE (SMCO)                                                         1
ORACLE.EXE (W001)                                                         1
ORACLE.EXE (J000)                                                         1
ORACLE.EXE (PSP0)                                                         1
ORACLE.EXE (MMNL)                                                         1
ORACLE.EXE (D000)                                                         1
ORACLE.EXE (S000)                                                         1
ORACLE.EXE (CJQ0)                                                         1
ORACLE.EXE (MMON)                                                         1
ORACLE.EXE (QMNC)                                                         1
ORACLE.EXE (VKTM)                                                         1
ORACLE.EXE (CKPT)                                                         1

Any idea what's causing this process spam and how to fix it? Note, I found many users reporting the issue in the internet, see for example:

https://forums.oracle.com/forums/thread.jspa?threadID=839381
https://forums.oracle.com/forums/thread.jspa?threadID=969909
https://forums.oracle.com/forums/thread.jspa?threadID=2409737

But so far I haven't found any solution or even a sensible theory


texts:
I am developing a SaaS web application for School Management. What I need help is this:

The whole application is purposed for the current year that the School is in. However, some data and statistics should be able to be accessed from passed years.

For example: It is extremely important to keep old students contact details or grades etc. However I don't think its a good idea to keep all the old and current students on the same table, because I will have to manually use "WHERE" clauses everywhere (Grades, evaluations, comments etc.) and that might be a bit "messy".
So, another approach would be to use a different database perhaps for each year. Keep in mind i intend to use a single-tenant approach.

Any ideas on these two approaches or perhaps something else?


texts:
I am trying to replace # with numbers, but the order by is being ignored, and I was told to move the subquery usingWITH or adding a limit to the order by, I tried the latter but it didn't work, and I am not sure how to use WITH in the following query.

update items
join (select id, @num := @num + 1 as num
    from items, (select @num := 0) items
     where parent_id =  159133    order by name) items2
on items2.id = items.id
set items.description = replace(items.description, '#' COLLATE utf8_unicode_ci, cast(num as char(10)))
WHERE parent_id = 159133 and type='photo';


texts: How to execute alter database commands from DbUp scripts?

texts:
I am using DbUp to maintain and upgrade my SQL Server 2016 databases. Typical SQL scripts that I have executed so far included tasks like creating tables, creating or altering stored procedures, etc.

I am going to start using Memory Optimised Tables, and need to run ALTER DATABASE commands to create the necessary filegroups in the existing databases. I tried running a dynamic SQL script that builds and runs ALTER DATABASE commands containing the database name that the script is currently running for, but since DbUp uses transactional execution, it seems that ALTER statements are not allowed and I am getting this error:

Script block number: 2; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): ALTER DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE statement not allowed within multi-statement transaction
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Any ideas how I can run ALTER DATABASE commands via DbUp, or is there an alternative way how to handle this case?

Thanks in advance.


texts: Using GROUPing or ROLLUP rather than SUMing every column

texts:
I am currently looking for a way to achieve what this query is doing without using SUM for every column and, if possible, GROUPing or using ROLLUP or something similar.

This query gives me a total for orders across all currencies.

SELECT DATE, TYPE, SUM(orders), SUM(qty),
SUM(shipped_orders), SUM(shipped_qty), SUM(return_qty),
SUM(shipped_minus_returns_units)
    FROM daily_report
    WHERE `date` >= '2015-07-01' AND `date` <= '2015-07-29'
GROUP BY DATE ASC, TYPE ASC;

My current query looks like this:

SELECT DATE, currency, TYPE, orders, qty, shipped_orders, shipped_qty, return_qty,
shipped_minus_returns_units
    FROM daily_report
    WHERE `date` >= '2015-07-01' AND `date` <= '2015-07-29'
GROUP BY DATE ASC, TYPE ASC, currency ASC;

which returns this DB table view:

CREATE TABLE `daily_report` (
  `date` date NOT NULL,
  `currency` varchar(5) NOT NULL,
  `type` enum('customer','exchanges') NOT NULL,
  `orders` int(11) NOT NULL DEFAULT '0',
  `qty` int(11) NOT NULL DEFAULT '0',
  `value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `value_ex_ship` decimal(9,2) NOT NULL DEFAULT '0.00',
  `average_order_value` decimal(7,2) NOT NULL,
  `average_item_value` decimal(7,2) NOT NULL,
  `average_item_per_order` decimal(7,2) NOT NULL,
  `shipped_orders` int(11) NOT NULL DEFAULT '0',
  `shipped_qty` int(11) NOT NULL DEFAULT '0',
  `shipped_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `net_shipped_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `return_qty` int(11) NOT NULL,
  `return_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `returns_to_shipped` decimal(9,2) NOT NULL DEFAULT '0.00',
  `shipped_minus_returns_units` int(11) NOT NULL DEFAULT '0',
  `shipped_minus_returns_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`date`,`currency`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Is there a way to total up these columns using group rather than SUM'ing everything?


texts: Using pg_dump on a view always produces an empty file

texts:
I want to export a subset of a tables rows using pg_dump (using powershell on windows). The simplest way seems to be to create a view with an appropriate WHERE clause. However, when I run pg_dump on the view the resulting output files never has data in it (it has several rows of SET statements, but no actual data.)
I can successfully run pg_dump on tables without any problems, and creating an appropriate view isn't a problem, I can select from the view using the same account that I'm running pg_dump from so I don't think permissions are an issue. pg_dump isn't producing any errors.
I've been using this command to create the view...
CREATE OR REPLACE VIEW viewA
AS
(
  select * from tableA
  WHERE blah blah blah....
);

And this command to do the dump
$env:PGPASSWORD='own'
.\pg_dump.exe -U myuser -d mydatabase -t viewA -f mydumpfile

This works (i.e. it runs without error) but mydumpfile never contains data.
If I do something very similar, but creating a table instead....
CREATE TABLE tabB
AS
(
  select * from tableA
  WHERE blah blah blah....
);

Followed by an almost identical pg_dump on the table...
$env:PGPASSWORD='own'
.\pg_dump.exe -U myuser -d mydatabase -t tabB-f mydumpfile

Then I get a dump file with the correct data.
Is there any way of using pg_dump against a view. The postgres documentation suggests it should be possible


texts: Make a (copy of a) postgis database available to customer?

texts:
Our application uses a PostgreSQL database (with the PostGIS spatial extension). Now one of our customers has requested direct access to the database; because, as they claim, it’s their data.

We’d rather not provide direct access to the database, because a wrong query might end up killing (or temporarily disabling) the database server, resulting in downtime for our application, or even a corrupt database.

Instead, we were thinking of setting up a slave database, and replicate the master. The slave would then only be used for read-only querying, so doesn’t need to write back to the master.

What is the easiest method of doing this with Postgres, while working with PostGIS as well? Or is a daily data dump easier to accomplish?


texts: Can slowms be set on each individual database in MongoDB?

texts:
I am using db.setProfilingLevel(1, 1000) to set the profile level for each database. I see the level can be set for each database but the slowms is a global state. Is there any way to set this value for each individual database?

If I can't set it for each individual database, why there is a method db.setProfilingLevel(level, slowms) accept the second parameter as slowms. It looks like this parameter is for global value rather than database value. What is confused is this method is specific for setting a single database profile level but for some reasons it also set slowms in global setting.


texts: How to troubleshoot ORA-00333 with NOARCHIVELOG mode

texts:
I'm getting the following errors when I try to connect to database:

ORA-00333: refaire bloc 8746 d'erreur de lecture de journal compte 7562
Errors in file C:\ORACLE11G\diag\rdbms\aim51\aim51\trace\aim51_ora_10804.trc:
ORA-00333: refaire bloc 8746 d'erreur de lecture de journal compte 7562
ORA-333 signalled during: alter database open...


texts: While using Cross Apply, can I force "empty" records to appear?

texts:
I have a query that is working fantastically, except that I would like to force some records to appear. I am using SqlServer 2016, and this query will ultimately be used in a VIEW and used by SSRS 2016. If the desired behavior can be done in SSRS, or even changing to a PIVOT or some such, I am open to that option.

I will put some DDL down below to mimic the example images shown.

The data I am pulling from looks like the below:

And I am using a cross apply to make the totals appear vertically like so:

The problem is, as seen above, the records that have a state and SUI_State of 'IN', have a SUI_State of "OH". This leads to the output to not contain SUI records for "IN" because no records have an SUI_State1 of "IN"

Is there any way to force "IN Employee SUI", "IN Employer SUI", etc... to appear with values of 0?

There are many states where the State/SDI State and SUI State are different, so I cannot hard-code the logic in there.

And here is some dumbed down similar data with my current query. The values aren't important in this question, only forcing the SUI records to appear for the state, "IN'

    CREATE TABLE #EmployeeTaxes
(    Payroll int
    ,SDI_State char(2)
    ,SUI_State char(2)
    ,State char(2)
    ,SIT DECIMAl(19,2)
    ,Employee_SDI DECIMAL(19,2)
    ,Employer_SDI DECIMAL(19,2)
    ,Employee_SUI DECIMAL(19,2)
    ,Employer_SUI DECIMAL(19,2)
)

--Data in source table
INSERT INTO #EmployeeTaxes
VALUES   (4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)

--My Current Query
SELECT Payroll
      ,v.Item                       AS [Witholding]
      ,SUM(v.TaxValue)              AS Tax
      ,v.OrderByNumber              AS [OrderByNumber]
FROM #EmployeeTaxes
CROSS APPLY (
    VALUES ([STATE]   + ' SIT', SIT, [STATE] + '110')
          ,(SDI_STATE + ' Employee SDI', EMPLOYEE_SDI,SDI_STATE + '111')
          ,(SDI_STATE + ' Employer SDI', EMPLOYER_SDI,SDI_STATE + '112')
          ,(SUI_STATE + ' Employee SUI', EMPLOYEE_SUI,SUI_STATE + '113')
          ,(SUI_STATE + ' Employer SUI', EMPLOYER_SUI,SUI_STATE + '114A')
          ,(SUI_STATE + ' Total', EMPLOYEE_SUI + EMPLOYER_SUI, SUI_State + '114B')
          ,(SDI_STATE + ' Total', EMPLOYEE_SDI + EMPLOYER_SDI , SDI_State + '114B')
          ,('---------------------------------',NULL,[STATE] + '121')

) v (Item, TaxValue, OrderByNumber)
GROUP BY Payroll, OrderByNumber, v.Item
ORDER BY PAYROLL, OrderByNumber
DROP TABLE #EmployeeTaxes


texts: The SQL server Log folder is expanding because of the SQL Dump files (what to do with it ?)

texts:
The "Log" folder SIZE in the SQL server root directory (X:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log) becomes too big , ~80 GB.

When I checked it, I see that there are a lot of SQLDumpxxxx.mdmp /SQLDumpxxxx.txt files in that folder.

what to do with them ?
can I delete them and if it is good thing to do ?

10X


texts:
If one table, let's call it table_1, has a composite alternate key made up of field1 and version, is it considered denormalized data, since field1 is repeating itself in the table (multiple rows with same value for field1 but different value in version), and thus field1 shall be extracted to another table, let's  call it table_2, so table_2 will have two columns field_1 and field_1_id, and replace field1 in table_2 with field_1_id?
Considerations
I think one table is a better design, but someone is suggesting me to have two tables, the reason is the duplication of values in field1, but even with two tables, the field_1_id will still be duplicated, right?


texts: Is it safe to kill ANALYZE TABLE query on a MyISAM table?

texts:
I have a huge MyISAM table that is blocking my application. I frequently find ANALYZE TABLE queries running on that table. I guess that's because a join is being performed on the table or at least an index is being used, I'm not sure.

So I'm thinking to run a OPTIMIZE TABLE query on the table to speed things up. But there are currently multiple ANALAYZE TABLE queries locking the table, so is it safe to kill such queries ? I couldn't find enough information on mysql's documentation for that matter..

Thanks.


texts: Way around Composite Primary Key requiring Composite Foreign Key

texts:
Here is a table called Make (where name is the PK)

name|other columns
----------------------
  A | ...
  B | ...
  C | ...

And a table called Type (where make, name together is the PK and make references Make.name as FK)

make|name|other columns
------------------------
  A |SUV | ...
  B |SUV | ...
  B |UTE | ...
  C |UTE | ...

Finally a table called Model (where make, type, name together is the PK and make, type reference Type.make, Type.name as FK)

make|type|name|other columns
-----------------------
  A |SUV |CRV | ...
  A |SUV |HRV | ...
  B |UTE |DMAX| ...

It seems to me the Model.make is redundant in the Model table, but it is necessary only because the Type table uses a composite key make, name.

What is the way to get rid of this redundancy?

I believe I can add an unique id column in the Type table, so that the Model table can reference that as FK. But it seems this extra column is just another form of redundancy, as make, name can uniquely identify every row already?


texts:
I have a query that needs a plan guide, but I am having a hard time setting it up.

Query below from the procedure cache...

(@state nvarchar(14),
 @jobName nvarchar(18),
 @jobGroup nvarchar(28),
 @oldState nvarchar(6))

 UPDATE JOB_TRIGGERS
 SET TRIGGER_STATE = @state
 WHERE JOB_NAME = @jobName
 AND JOB_GROUP = @jobGroup
 AND TRIGGER_STATE = @oldState

SQL Server chooses to perform a clustered index scan vs a non clustered index seek. I am having sporadic deadlock issues with this update statement and a certain select statement on the table. I understand why SQL is choosing an clustered index scan on the table....Rows < 100 and PageCount < 25.

The table has a large of amount of activity, and since its a 3rd party product I don't have the ability to modify the query and supply an index hint. The query cost of using the non clustered index is more, but I believe it will improve concurrency based on testing....

I need to tell it to use the non clustered index below

WITH (INDEX (ix_jobname_jobgroup_triggerstate))

Help setting this up would be much appreciated..


texts:
How does one setup two identical servers for automatic failover in PostgreSQL 9.1.

OS

  Centos 5
  PostgreSQL 9.1 compiled from source
  The postgres user account exists on both machines and has a ssh passwordless key to connect to both machines.

My Current Setup :

Master server configuration:

postgresql.conf:

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 16
wal_keep_segments = 8
archive_mode = on
archive_command = 'cp "%p" /opt/pgsql91/archive/"%f"'

pg_hba.conf:

 host  replication   all   10.0.66.1/32      trust
 host  replication   all   10.0.66.2/32      trust

Standby Server

postgresql.conf and pg_hba.conf are identical to what is configured on the master server.

recovery.conf:

 standby_mode = 'on'
 primary_conninfo = 'host=10.0.66.1'
 trigger_file = '/opt/pgsql91/data/trigger.txt'

Thanks to hzRoot, I now understand how to switch the server from standby to master.

Using the following commands, I can synchronize the new slave with the new master and then get replication backup and running.

On the new master (10.0.66.2)

su - postgres
touch trigger.txt in /opt/pgsql91/data/
recovery.conf becomes recovery.done
psql -c ";SELECT pg_start_backup('backup', true)";
rsync -a -v -e ssh /opt/pgsql91/data/ 10.0.66.1:/opt/pgsql91/data/ --exclude postmaster.pid
psql -c ";SELECT pg_stop_backup()";

On the new slave (10.0.66.1)

create the recovery.conf : cp recovery.done to recovery.conf
vi recovery.conf change ip address : primary_conninfo = 'host=10.0.66.2'
start postgresql

So my questions are now :

Is this the correct way to switch roles?
Has anyone automated this process, if so what did you do?
If synchronous replication is enabled, I noticed the new master server won't commit any transactions because it is waiting for the slave to respond.  There is no slave however because the other server, the old master is down.  Is this correct or do I need to temporarily disable synchronous replication while the new slave is down?


texts: sp_BlitzFirst: Poison Wait - Why is this appearing?

texts:
When I execute sp_BlitzFirst I see a Poison Wait Detected: THREADPOOL entry. However, in the Detail column it says this:

For 0 seconds over the last 60 seconds, SQL Server was waiting on this particular bottleneck.

If SQL Server never waited on this (0 seconds over the last 60 seconds), why is this entry included in the results?


texts: Optimizer choose incorrect index for query according to rows number in mysql 5.6

texts:
I have mysql 5.6. The problematic query has the following format:

SELECT field_1, field_2
  FROM trace_ejb3
 WHERE field_1 IN (...) AND field_2 BETWEEN ... AND ...
 GROUP BY field_1;

Optimizer calculates the cost for each execution plans and eventually choosees incorrect index to use. From what I understand the main reason - number of returned rows.
However in fact I need to use another index. The difference in execution is 2 days vs 1 hours.

I have tried to play with the following options:
- innodb_stats_persistent + sample_pages
- optimizer_switch
- optimizer_search_depth
- optimizer_prune_level

However none of the about options influence the results : optimizer still choose execution plan according to the least number of returned rows.

Question: is there a way to force optimizer not take into account rows number?

P.S. I know about index hints, but I don`t consider it like a long-term solution.


texts: Strategies to import big data into Oracle

texts:
What strategies are available to import about 500 million small transactions every day from an external system?

Oracle becomes very slow with very frequent commits.

The transfer process which is reading the data from the external system can be implemented individually.

A NoSQL based solution is possible.

Update:
The datasource is a 'transaction journal' of a different DB system.


texts:
I wanna add some data into two columns in a table.

Would be something like:

INSERT INTO dm.billables_links (billable_id) VALUES ((SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Ali%')),

INSERT INTO dm.billables_links (mobiuser_id) VALUES ((SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Ali%' AND name_last LIKE 'Daon%'))

Question:

Note that the "billable_id" selected on the first INSERT must be related to the "mobiuser_id" selected on the second INSERT.

How can I do such a thing?

Table dm.billables.links columns:

billable_id, mobiuser_id

SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Ali%' AND name_last LIKE 'Daon%'

950898

SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Ali%'

13270372


texts: Versioning in parent child relationship - stale foreign keys problem

texts:
I have modeled a parent-child relationship in the following way:
Following are my entities:
Task: Holds details of the task.
Event: Holds an event of status change of a task. For example, task_1 completed.
Event_To_Child_Task: This table holds the information of what task (child_task_id) to run upon status change of parent task i.e., the occurrence of the event (event_id).
Data model:

Here, the parent task can independently be updated and a new row is created with incremented version. The Old row is marked deleted. This way we can revert back to the previous version anytime.
The problem here is that when the parent task is updated, the row in Event table defined by the child still points to an older version of the task as the foreign key is not updated.
In order to solve this problem, I have thought of removing the foreign key reference on Task in Event table. This way, child can define the Event in a freeflow manner and the parent can update its task data at any time. Event table in this case holds only the name of the task.
Is this the right approach? or Is it better to maintain the references, remove versioning and always update the data, delegating the responsibility to maintain history to a different table. The only problem I see in the second approach is that we are losing revert functionality.
What will be the effect on performance by losing the foreign key references?


texts: How to set uuid-ossp as a default extension for whole server?

texts:
I installed uuid-ossp for the default PostgreSQL database (postgres) using the following sql commands but whenever I create a new database I am forced to run the same commands once again for every new database.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
ALTER EXTENSION "uuid-ossp" SET SCHEMA public;

Is there any way to install uuid-ossp once and ensure that every new database will have uuid-ossp enabled by default?
PS. I use PostgreSQL ver. 13.1


texts: Duplicating rows based on a count column SQL Server

texts:
I have the following table:

MyTable
PlaceID NameID  Code    CodeCount
1       1       1234A   2
1       2       1234A   3
1       3       1234A   1
2...
2...
3...
3...

I want to select 1234A where PlaceID = 1, the number of times equal to the sum of the CodeCount. So, the result would be six rows like this:

MyResult
Code
1234A
1234A
1234A
1234A
1234A
1234A

Eventually, this selection will have a bunch of different codes for a single PlaceID. YES I know I'm duplicating data, in the end it's not going to be a lot and I'll explain why I'm doing this below.

First, I needed to get the sum of the counts:

DECLARE @cdcnt INT = (SELECT SUM(CodeCount) FROM MyTable WHERE PlaceID = 1 AND Code = '1234A')

Then I can use this value as a control in a while loop to select the value that many times:

DECLARE @i INT = 0;
WHILE @i < @cdcnt
BEGIN
    SELECT DISTINCT Code
    FROM MyTable
    WHERE PlaceID = 1 AND Code = '1234A'
    SET @i = @i + 1
END

The problem is I get 6 separate selections, instead of one selection with 6 rows. How do I create MyResult shown above, instead of 6 separate selections?

Why I am doing this: I want to use a random number generator to pick a Code with the frequency at which it appears in aggregate for each PlaceID (there will be other Codes added, I'm still figuring out how to do just one). I am open to suggestions for different ways of doing this, but it's the only way I could figure out how. If there's a function already built to pick from a list according to a set frequencies, I'm all ears.


texts: How to get something written to MySQL error log without restart

texts:
I want to cause the MySQL 5.5 error log to be written to at will without a service stop or start. Note that I do not care what gets written to the error log, as long as something gets written.

I tried running a bad query and setting a nonexistent system variable, but those actions do not seem to generate an entry in the error log.

How can I do this?


texts: Converting UTC time to timestamptz always adds the +01 offset in Postgres

texts:
In Postgres 9.5, I do the following to convert a timestamptz to the UTC timezone:

SELECT TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC'

this outputs correctly 2016-02-20 14:15:54.000495 as timestamp without timezone.

I would like to store this value in a timestamptz column. However, when I do the following:

SELECT (TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC')::timestamptz

I would expect to get 2016-02-20 14:15:54.000495 as timestamptz.

However, this query gives me 2016-02-20 14:15:54.000495+01 as timestamptz, which sadly doesn't even represent the original time.

Whats the reason of such behavior? How do I make this work correctly?


texts: how to user table name as variable in mysql stored procedure

texts:
I create a procedure to set auto-increment index value to 1. But there I want to use table name as a variable. I am try it but it not working correctly. I know there have a lot of question like that but those are not help me to solve my problem.

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
DECLARE var1 CHAR(50);
DECLARE done TINYINT DEFAULT FALSE;
DECLARE cursor1
 CURSOR FOR
 SELECT table_name FROM information_schema.tables where table_schema='maliban';
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cursor1;
 my_loop:
 LOOP
 FETCH cursor1 INTO var1;
  IF done THEN
   LEAVE my_loop;
  ELSE
   ALTER TABLE var1 AUTO_INCREMENT = 1;
  END IF;
 END LOOP;
END

This is the my stored procedure. I want to user var1 as a variable in bellow line

ALTER TABLE var1 AUTO_INCREMENT = 1;

I already use it as a variable but it not working. because of it get like a table name.

please help me. I am new to the stored procedure.


texts: Can you run two WSFC instances on two servers where each node is the primary for a different availability group?

texts:
I'm drawing up a proposal for upcoming infrastructure changes. This will include a production server and reports/data warehouse server, each with Always On. To keep hardware and licencing costs down, is it possible to run in a configuration of Server-A running Prod-AG Primary and Rep-AG Secondary, and Server-B running Rep-AG Primary and Prod-AG Secondary?

I presume each server would need 2x of the following WSFC instances,sql instances, AG's, listeners, DNS names/ports.

I hope this makes sense, here's a diagram of what I think it will looks like.

In the case of a fail-over on either node, the workload/business need isn't that great that running off the same server for a couple of hours would be a major issue.

I've only found a couple of mentions of a similar setup that kind of worked but no definitive information from Microsoft or anyone who's successfully ran this setup.

SQL Edition will be 2017, most likely standard, I don't think we'll be approved for Enterprise. OS will be Windows Server 2016 Core.


texts: Mysql multiline statement causes syntax error

texts:
// php, using wordpress to send a raw query string
    $output = $wpdb->query("
      INSERT INTO wp_priceGrids (gridId) VALUES (NULL); SELECT MAX(gridId) FROM wp_priceGrids;
    ");

Results in:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(gridId) FROM wp_priceGrids' at line 1]
INSERT INTO wp_priceGrids (gridId) VALUES (NULL); SELECT MAX(gridId) FROM wp_priceGrids;

Tried

Deleting / commenting the 2nd line. Works. A new row is added and
visible as I check through CLI
Adding backticks (``) around the
field name gridId. Seems not to affect anything, error is the same
Removing the hard return between statements (as you can see above).
No change

Question

Is it a syntax error? I've checked against examples and I think what I have matches their formats

owner@G700:~$ mysql --version
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper
owner@G700:~$ php --version
PHP 7.2.15-0ubuntu0.18.04.1 (cli) (built: Feb  8 2019 14:54:22) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Xdebug v2.7.0, Copyright (c) 2002-2019, by Derick Rethans
    with Zend OPcache v7.2.15-0ubuntu0.18.04.1, Copyright (c) 1999-2018, by Zend Technologies


texts: DB2 - What is the difference between online backup and AUTO_DB_BACKUP?

texts:
We would like to perform online backup.
I see that there is one parameter AUTO_DB_BACKUP.
I read this article on IBM link for AUTO_DB_BACKUP  http://www.ibm.com/developerworks/data/library/techarticle/dm-0801ganesan/

but still I cannot see the purpose of this AUTO_DB_BACKUP when online backup is ON.

Thank you


texts:
I have a database that has not been reindexed in the last 5 years.

I want a yes or no answer if databases without index maintenance and "Missing Rows" are related to each other.


texts:
I am not sure if this has been asked before, however I am tasked with designing a new reservation system for my organization

We have an older system which simply stored the booking in a table, and it worked pretty well, but it becomes quite slow when I try to determine what time slots are free (It has to get the open/close time range, and calculate whats free by taking away whats booked). Ideally, I would like a better solution for this, and I am hoping that somebody has solved the problem before.

The booking system itself will display only one day at a time, listing whats booked & whats available for that specific day.


texts:
I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query).
The queries are sent continuously. The table is INNODB, two INT columns, there is index for both columns. CPU utilization of RDS instance is about 30% during data receiving.
When I have one EC2 instance, the speed is in orders of magnitude faster than I run 15 instances simultaneously. In light of this, the 15-instances group works slower and slower until the speed becomes totally unsatisfactory.
How can I optimize performance for this process?
UPDATE
My SHOW CREATE TABLE result is the following:
CREATE TABLE `UserData` (
 `uid` int(11) NOT NULL,
 `data` int(11) NOT NULL,
 PRIMARY KEY (`uid`,`data`),
 KEY `uid` (`uid`),
 KEY `data` (`data`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I need 2 indexes because it is necessary for me to fetch data by uid and by data value.
I insert data with INSERT INTO UserData (uid, data) VALUES (1,2),(1,3),(1,10),... with 40000 (uid,data) pairs.
15 parallel instances insert ~121 000 000 rows in 2 hours, but I am sure that it can be much more faster.


texts: Why do I have RID locks if I have a clustered index?

texts:
I have configured extended_events to capture blocks on my database. Now I can see that I had blocks on one table that have been causing blocks problem. I can see that one UPDATE query can not go through and the detail says:

waitresource="RID: 9:1:6162688:0"

I understand why the UPDATE can not go through, it is because the query has to read the whole table and it blocks when it tries to read the RID. I am guessing that RID has a X lock.

My questions are:

How can I know which query is locking the RID ?
Why do I have locks at the RID level if I have a clustered index?


texts:
I have two tables in a SQL Server database with basic fulltext indexes:

CREATE FULLTEXT CATALOG table1_catalog;
CREATE FULLTEXT INDEX ON table1
 (
   name1
 )
  KEY INDEX pk_table1
  ON table1_catalog;
ALTER FULLTEXT INDEX ON table1 START UPDATE POPULATION;
CREATE FULLTEXT CATALOG table2_catalog;
CREATE FULLTEXT INDEX ON table2
 (
   name2
 )
  KEY INDEX pk_table2
  ON table2_catalog;
ALTER FULLTEXT INDEX ON table2 START UPDATE POPULATION;

The tables have approximately 100,000 entries.

The following two queries return immediately when there are no results:

SELECT * FROM table1 t1
  LEFT JOIN table2 t2 on t1.id=t2.id
  WHERE CONTAINS(t1.*, '"noresult"');
-- Result in 00:00:00

SELECT * FROM table1 t1
  LEFT JOIN table2 t2 on t1.id=t2.id
  WHERE CONTAINS(t2.*, '"noresult"');
-- Result in ~50ms

But the following takes much longer:

SELECT * FROM table1 t1
  LEFT JOIN table2 t2 on t1.id=t2.id
  WHERE CONTAINS(t1.*, '"noresult"');
  OR CONTAINS(t2.*, '"noresult"');
-- Result in ~22,000ms

The queryplans are here: http://pastebin.com/SBkgpF8X

Is there any explanation for this? Can I fix it or do I need to perform to separate queries.


texts: How can I extract the perfmon counter "Memory: Pages/Sec" using t-sql?

texts:
I've googled this and so far, no luck so turning to all you awesome gurus for help.

I'm trying to figure out if there's a way to get the perfmon counter "Memory: Pages/Sec" using t-sql as I'm trying to add it as a custom metric in our Ignite monitoring tool.  I've looked at the sys.dm_os_performance_counters DMV but didn't find it there.

Anyone know of a way doing this?  Thanks in advance :)


texts: PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call

texts:
This is my remote function:

CREATE OR REPLACE FUNCTION public._test1()
RETURNS record
LANGUAGE plpgsql
AS $function$
DECLARE
 rec record;
BEGIN
  select 1,2 into rec;
  return rec;
END $function$;

This is my local function call:

SELECT x.a, x.b
FROM dblink('conn_str', 'select public._test1();')
as x(a int ,b int);

This is the error thrown:

ERROR: remote query result rowtype does not match the specified FROM clause rowtype

Since DBLINK requires that I define a schema to place the function's return items, how do I make the call to recognize a record type being returned by the function?


texts: History of database restore completion times?

texts: I'm trying to estimate the restore time for a few 1TB+ databases on SQL 2012. Is it possible to look at completed restores from the last year and list the completion time ? It's an odd remote server on the other side of the world, all I know full restores used to run every beginning of a new month, for some reason, but can't figure out how to access the history.


texts:
In the code below, the OUTPUT Updated.* fails when executed as-is, but removing the FROM in the UPDATE and replacing it with a static value works fine. Is there a different syntax required when using OUTPUT with an UPDATE FROM?

 USE SCRATCH

 CREATE TABLE Tab1(a char(10))
 CREATE TABLE Tab2(a char(10))
 CREATE TABLE Tab3(a char(10))

 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')

 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')

 UPDATE T1
 SET T1.a = T2.a
 FROM Tab1 T1 JOIN Tab2 T2 ON T1.a = T2.a
 OUTPUT Updated.*

The error I get is

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'OUTPUT'.


texts: Inconsistent Execution Plan for Stored Procedure

texts:
We have a business-critical stored procedure that normally runs daily at 2am from a scheduled job (in an SSIS package) on the production DB server. The same procedure/package is called from a second job, 15 minutes later, from a SQL Agent job running on a different server (as an emergency failover in case anything goes awry with the first job).

The procedure is defined WITH RECOMPILE.

The procedure normally executes in about 45 seconds. Last Wednesday, and again this morning (also Wednesday, coincidence?!?), the 2am procedure took 90 minutes to execute. While it was executing, the 2:15am job ran and that execution took the usual 45 seconds.

I have the execution plans from both situations. There are some table variable processes that should include estimated row counts in the neighborhood of 200K rows. The faulty plan reports these table variables with an estimated 130 billion rows. [Side note: I have already rewritten the code to use temp tables instead of table variables, based on this discussion and will be moving it to production in the near future]

Our monitoring software (Solar Winds DPA) reports excessive CXPACKET waits for the 2am execution. This seems to indicate issues with parallelism and is likely related to the table variables being used in the procedure.

There is still user activity on the server during this time, and some scheduled jobs, but nothing that I see that would affect this procedure or its execution plan. An index maintenance job is run at 2:30am.

I understand the poorly-performing execution plan is related to the temp tables, but why would this same procedure executed 15 minutes later have such a drastically different execution plan (and why does the 2am execution run fine the rest of the week?)

Here are links to the .sqlplan files: The Bad Plan and the Good Plan.


texts: Can CyberArk Manage SQL Credentials To Synchronize Password Changes?

texts:
My organization utilizes CyberArk to force expiration & reset of administrator & service account passwords on the domain. When it comes to SQL Server-related assets, I'm hitting an impasse updating a credential used by a proxy in SQL Server Agent to execute SSIS packages.

The CyberArk admin is able to update execution creds on a scheduled task. I ran a successful test with a simple .NET console app and I figure I could run a SSIS package with dtexec in that fashion.

However, if I want to leverage SQL Server Agent jobs and keep the package execution away from the O/S layer, CyberArk needs to be able to update the password on the credential object used by the SSA proxy.

Is there a plug-in or feature that CyberArk can leverage to connect to an instance with a login that has ALTER ANY CREDENTIAL and push a new password?Alternatively, the CyberArk admin suggested calling the REST API (CyberArk AIM) to pull the new password at regular intervals (probably using a non-expiring domain account with the same SQL privs). Has anyone attempted either method?


texts: What disrupts the inherit logic order of autoincrement that leads to illogical ordering of ids in the absence of an ORDER clause?

texts:
On occasion I come across a database table on a project where when no sorting is applied (ORDER clause) that the ids are not in their inherit logical order.

1
178
3
144
2

Under the presumption that no one went out of their way and started manually changing the id column values for any reason and that the id column is set to autoincrement what would lead to this scenario and should whatever that cause/those causes should be an issue to be addressed for security or data integrity purposes?


texts: Reporting table needs to get refreshed without killing user's read connection to it, any ideas?

texts: I'm investigating a solution that will allow a reporting table to be refreshed that can potentially have users reading from it at a time that it needs to be refreshed. Has anyone implemented a way of doing something like this without needing to kill the user's connection to that table at the time of refresh?


texts: Create Language plperl - Error: could not load library plperl.dll

texts:
When I create language plperl , I get error: ERROR:  could not load library "C:/Program Files/PostgreSQL/9.1/lib/plperl.dll": The specified module could not be found.

But in my computer,  "plperl.dll" file is exist in "C:/Program Files/PostgreSQL/9.1/lib/..." folder   ( I can not post illustrative image, this forum require >= 10 reputations)

And if I select * pg_pltemplate, I get:

-[ RECORD 4 ]-+-------------------------
tmplname      | plperl
tmpltrusted   | t
tmpldbacreate | t
tmplhandler   | plperl_call_handler
tmplinline    | plperl_inline_handler
tmplvalidator | plperl_validator
tmpllibrary   | $libdir/plperl


texts: Joining multiple tables results in duplicate rows

texts:
I am getting more rows then I expect returned by my query.

I believe it has something to do with my join statements.

There are multiple tables that have different information in them.  Person holds the primary information about the person but not the address, or phone, or email.   This is because the original designer wanted the table to be able to hold multiple phone numbers and emails and addresses.

SELECT (person.FirstName + ' ' + person.LastName) as FullName
    ,ISNULL(Person.isClient, '')
    ,ISNULL(Person.UDF1, '')
    ,ISNULL(Address.City, '')
    ,ISNULL(Address.state, '')
    ,PersonAddress.Person
    ,PersonAddress.Address
    ,ISNULL(Phone.PhoneNumber, 'N/A')
    ,Email.Email
    ,Person.Website
FROM Person
    left join PersonAddress on Person.ID = PersonAddress.Person
    left join Address on PersonAddress.Address = Address.ID
    left join PersonPhone on Person.ID = PersonPhone.Person
    left join Phone on PersonPhone.Person = Phone.ID
    left join Email with (nolock) on Person.ID = Email.Person
WHERE (
        isclient = 'prospect'
        or isclient = 'client'
        )
    and Address is not null
    and name like '%Mike%'
ORDER BY isClient asc;

For this example, I get 6 rows of "Mike Worths". 3 of the copies have one email and three have another email.

For "Mike Pamstein" I get two duplicate rows with the same email.

I need the results to only contain a single unique row for each person.

I want to drop the second email.


texts:
I am new to MySQL.

I installed MySQL through dmg file downloaded from oracle in mac. I created my first connection also but now whenever I am trying to connect it again it not allowing me.

I read over internet that I need to ALTER root password. So I tried many ways of doing it but either it says UPDATE Command not found or Access denied to root@localhost...

Can someone please suggest a solution?


texts: How to update a column with foreign key?

texts:
In two tables with a foreign key as

CREATE TABLE table1
(
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE table2
(
id2 int(11) NOT NULL AUTO_INCREMENT,
id1 int(11) NOT NULL,
FOREIGN KEY(id) REFERENCES table1(id) ON DELETE CASCADE,
PRIMARY KEY(id2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

How to update the id column to synchronously affect both tables? For example, id+1000.


texts: Select only rows with at least 2 non-missing values

texts:
I'm pretty new to MySQL (used to programming in R/Stata), and getting stuck on this - I'm probably going about it the wrong way. Any pointers would be appreciated!

I have a large database (a couple million rows) and want to select records where at least two fields from a selection of variables are non-missing (not NULL). I can find lots on counting missing values by column, but not by row.

As an example, I would like to write a select statement for the table below, based only on var1-var5, that would select ID 1 and 3 as they have at least 2 non-NULL values.

ID | var1 | var2 | var3 | var4  | var5 | var6 | var7
1  | NULL | 4    | X    | NULL  | NULL | NULL | R69
2  | NULL | NULL | NULL | NULL  | 1    | X    | J10
3  | 4    | 5    | 23   | 1jan03| 2    | 22   | Z38

I was thinking creating a user-defined variable to count the number of non-missing values and delete rows where this is <2, but it doesn't seem to work.

SET @include = 0;
SELECT var1, var2, var3, var4, var5 FROM db1;
@include := @include + 1 if var1 is not null;
DELETE FROM db1 WHERE @include<2;

I'm guessing this is something I can do with a WHERE statement in the SELECT line, but I can only think of writing it so it only selects rows where everything is non-missing, not at least 2 values.


texts: What can I do to find out since which release a given DBMS supports a feature, function, etc?

texts:
It is rather easy to find links describing a feature.
In most cases there is no hint in which release the feature was introduced.

Often the last resort is to google for the feature in combination with special releases and to compare the hit counts or something similar like old lists of new features etc.


texts: Why isn't Unicode character REPLACEd in some cases?

texts:
Running this results in "test", as expected:

SELECT
    REPLACE(NCHAR(1234), NCHAR(1234), N'test');

However, running this results in "aӒa", which doesn't have "test" in it:

SELECT
    REPLACE(N'a' + NCHAR(1234) + N'a', NCHAR(1234), N'test');

I thought it might have to do with the string concatenation happening for the haystack but not the needle, but when I tried this, it still didn't "work":

SELECT
    REPLACE(N'a' + NCHAR(1234) + N'a', N'' + NCHAR(1234) + N'', N'test');

Result: "aӒa"

I suspected it might have to do with how it's interpreting the characters, so I tried specifying a binary collation... and that "fixed" the issue:

SELECT
    REPLACE(N'a' + NCHAR(1234) + N'a' COLLATE Latin1_General_100_BIN2, NCHAR(1234), N'test');

Result: "atesta".

Why?

This behavior seems to exist for some characters, but not others.

SELECT
    REPLACE(N'a' + NCHAR(23423) + N'a', NCHAR(23423), N'test');

Result: "atesta" ("works")

SELECT
    REPLACE(N'a' + NCHAR(5342) + N'a', NCHAR(5342), N'test');

Result: "aᓞa" (doesn't "work")

Why?


texts:
I can use the Management studio to connect directly to my Azure SQL database and execute this query:

select * from DB.dbo.DSeries where instrument='name1'

and it works really fast. But if I create a linked server (called "WF") in my local server that connects to Azure and try to execute the same query,

select * from WF.DB.dbo.DSeries where instrument='name1'

The query is really slow. Why could that be happening?


texts: SQL Server: can I restore full DB file backup to First LSN (i.e. when backup started)?

texts:
A full database backup was started at 15:46:00 using the following cmd:

backup database IF_BROWNS to disk = 'C:\temp\IF_STDINT_{0}.BAK' (where {0} = timestamp, e.g. 2017-05-16T15-46-00.0831634+01-00)

The IF_BROWNS database was in Full recovery mode at the time. The backup took 34 seconds to complete. There are no other backups (full, differential or log) available.

I want to restore the database to the earliest point in time possible, i.e. 15:46:00. This Microsoft article implies that that the backup will normally be restored to the database's state at the end of the backup, i.e. 15:46:34

     *Full database backups represent the database at the time the backup finished.*

I tried using the Timeline button on the restore dialog and although it gave the impression that I could restore to 15:46:00, it did not actually make any difference to the generated restore cmd, and the restored database definitely contains data changes made after 15:46:00.

I can find articles describing how to restore to a point in time or LSN if I have a log backup, but as I said before I do not have that.

Here is an image of the restore dialog, showing times and LSNs:


texts: pgAdmin Query Tool throws error when I try to run multiple statements

texts:
I'm very new to PostgreSql and still learning the ropes.

I have a really long script for setting up a new copy of my database. It creates functions, runs them, does some inserts and a bunch of other stuff. When I try to run this entire script in the pgAdminIII Query Tool, it throws an error that doesn't seem to make sense.

ERROR:  syntax error at or near ""
LINE 18: /*
         ^

This error gets thrown right after the first alter function owner statement which is after the first create function statement.

If I take each statement out and run them individually then everything runs OK. What gives?

Lines before error:

ALTER FUNCTION table_exists(text) OWNER TO postgres;

Lines at error:

/*
-- Function     : column_exists(text, text)
-- Function output  : true / false
*/

Lines after error:

CREATE FUNCTION column_exists(tablename text, columnname text)
RETURNS boolean AS $$

BEGIN
    RETURN EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = $1 AND column_name = $2);
END;

$$LANGUAGE plpgsql;


texts: How to convert timezone in MySQL using the timezone name?

texts:
I want to convert date from one timezone to another timezone in MySQL, MySQL query which I am trying is

SELECT DATE_FORMAT(CONVERT_TZ('2015-12-18 06:00:00', '+0:00', '-05:00' ),'%b %d %Y %h:%i %p') AS test
FROM appointment

which is working properly. Now the issue I am having is that my database uses the timezone name as America/New_York.

Is there any way to convert timezone using timezone name not from timezone offset?


texts:
I cannot start Oracle 11g after a reboot. Configured Using ASM. I'm New to Oracle domain.

# crsctl start has

CRS-4124: Oracle High Availability Services startup failed.
CRS-4000: Command Start failed, or completed with errors.

# ocrcheck

> Status of Oracle Cluster Registry is as follows :
>          Version                  :          3
>          Total space (kbytes)     :     262120
>          Used space (kbytes)      :        120
>          Available space (kbytes) :     262000
>          ID                       : 1063256645
>          Device/File Name         : /u01/app/oracle/product/11.2.0/grid/cdata/localhost/local.ocr

                                Device/File integrity check succeeded

                                Device/File not configured

                                Device/File not configured

                                Device/File not configured

                                Device/File not configured

     Cluster registry integrity check succeeded

     Logical corruption check succeeded


texts: Reverse Byte-Order of a postgres bytea field

texts:
I'm currently working on a table that contains hashes, stored in bytea format. Converting the hashes to hex-strings however yields the wrong order of bytes. Example:

SELECT encode(hash, 'hex') FROM mytable LIMIT 1;

Output: 1a6ee4de86143e81
Expected: 813e1486dee46e1a

Is there a way to reverse the order of bytes for all entries?


texts: mysql innodb db full text search FTS_DOC_ID column

texts:
I am adding full text search to already existing mysql tables that have primary auto incrementing key of id integer.

When I add the full text index I get a warning:

/*!50604 ALTER TABLE `phppos_additional_item_numbers` ADD FULLTEXT INDEX item_number_search (`item_number`)*/;

Warning (Code 124): InnoDB rebuilding table to add column FTS_DOC_ID

I cannot simply refactor all my code to add a column FTS_DOC_ID to remove this warning. Is it bad to just allow this warning to happen?


texts: Assignment of first group element - MySQL 4.7

texts:
I would like to assign the first element of a certain group to 0. The following tables are respectively the original and the final result.
CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
)

CREATE TABLE IF NOT EXISTS result (
   id_ INTEGER  NOT NULL,
   new_col INT NOT NULL
)

INSERT INTO t1 (id_, value1) VALUES
(1, 1),
(2, 1),
(2, 1),
(3, 1),
(3, 1),

INSERT INTO result (id_, new_col) VALUES
(1, 0),
(2, 0),
(2, 1),
(3, 0),
(3, 1)


texts:
I am attempting to setup a MySQL Cluster.

I have the folllowing setup

one management node
two data nodes
another machine on which two more nodes reside

Below is the config.ini file:

[ndbd default]

# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2    # Number of replicas

DataMemory=80M    # How much memory to allocate for data storage

IndexMemory=18M   # How much memory to allocate for index storage

                  # For DataMemory and IndexMemory, we have used the
                  # default values. Since the "world" database takes up
                  # only about 500KB, this should be more than enough for
                  # this example Cluster setup.

DataDir=/var/lib/mysql-cluster

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

LogDestination=FILE:filename=my-cluster.log

[tcp default]

[ndb_mgmd]

# Management process options:
hostname=X.X.X.26         # Hostname or IP address of MGM node

datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

# Storage Nodes
[NDBD]

hostname=X.X.X.26   # IP address of storage-node-1

DataDir= /var/lib/mysql-cluster

[NDBD]

HostName=X.X.X.73         # IP address of storage-node-2

DataDir= /var/lib/mysql-cluster

[NDBD]
hostname=X.X.X.26      # IP address of storage-node-3
DataDir= /var/lib/mysql-cluster

[NDBD]
HostName=X.X.X.73         # IP address of storage-node-4
DataDir= /var/lib/mysql-cluster

# Setup node IDs for mySQL API-servers (clients of the cluster)

[mysqld]
# SQL node options:
hostname=X.X.X.73        # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)

Below is the my.cnf file for both systems:

[mysqld]

ndbcluster # run NDB storage engine

ndb-connectstring=X.X.X.26 # location of management server

[mysql_cluster]

ndb-connectstring=X.X.X.26 # location of management server

When I try to start the cluster, it gives me following error:

Could not determine which NodeID to use for this node. Specify it with --ndb-nodeid=<nodeid> on command line

If clustering setup is done, there is no replication of data as it give me again another error: ERROR 1296 (HY000): Got error 4009 'Cluster Failure' from NDBCLUSTER

Can anyone help me determine the problem?


texts: mariadb server optimization for a db of 6GB size

texts:
I have a database I am trying to configure but I am struggling to get the settings right. I keep changing the settings but I always get the same slow performance.

the mysql database has about 50 tables
2 of the tables have about 10.000.000 rows each.
total size of the database is 6GB. Of those 6GB about 3GB are indexes (because of those 2 tables mainly where most operations occur)
This db is expected to get about 1 million rows more per month added. (there is an import procedure of new records every morning)
Other than that is mostly reading and manipulating data to present to the users.
As this is for a web app, there are only 4 clients using it.

on our new server i have a VM with 64GB of RAM and 16 cores.
on our previous server we had 16GB RAM and 8 cores.

on the previous server i had those settings only :

max_binlog_size   = 100M
binlog_do_db            = equidata
relay-log = /data/mysql/mysql-relay-bin

innodb-buffer-pool-size        = 8G
innodb_log_buffer_size         = 512M
innodb_buffer_pool_instances = 8
join_buffer_size = 2G
thread_cache_size = 50
query_cache_size = 4G
query_cache_limit = 4G

Performance is ok, but until the tables are cached is a bit slow, I believe it could be lot faster.
on the new server with the same settings I get about the same performance. using mysqltuner or the percona online advisor and following the advices, it does not get any better whatever i change.

there are many queries that are not using indexes properly, and have optimized most of them.
when the queries that run to present data are appearing to be slow, I cannot see any spikes on the new server, it is like it is lagging, no activity on cpu and ram..

whenever i use suggestions from mysqltuner I end up with the warning that mysql maximum memory usage is dangerously high etc (because it suggest me to increase the join buffers so for example : Total buffers: 10.6G global + 2.0G per thread (151 max threads)

I am stuck with this, and I ve been trying to fix it for sometime but I always leave it for later as I have huge workload programming the application, but now I am at the point where it affects the application and we got this new big server.

I am open to suggestions for settings. I understand that the queries with no indexes is a major thing, but I am looking at the best optimal configuration for my case on this instance.


texts: Attach Database is disabled in Microsoft SQL Server 2012 Management Studio

texts:
I have buy from Godadday hosting and domain, after that I have create database in godaddy and the connect to this db using my authentication.

I have .mdf, .ldf that need to attach to the connected server, I am going to Object Explorer -> Database -> Attach -> Attach database screen is opened but everything inside is disabled.

What can I do to enable attaching my local DB to the server DB?

Thank you so much for helping.

I am using SQL Server 2012 Management Studio


texts:
I have a large table that contains product IDs and product category IDs.

I want to select product IDs that are in a certain list of category IDs, AND exclude the product ID entirely if it's in another list of category IDs.

Example table:

prod_id cat_id
------- ------
1       10
1       11
2       10
2       11
2       12
3       10
3       11

So I want to see all products that have category IDs 10 and 11, BUT if they are also have category ID 12, they must be excluded.

I was thinking this would work, but it does not:

SELECT prod_id from products WHERE cat_id IN (10,11) AND NOT IN (12)

This seems like it should be really easy and common, but I'm just not getting it.


texts: For each field name in a list of fields, get the unique values

texts:
The Question:

I have a view that is a list of fields in tables:

FIELD_LIST_VW
+-------------+------------+
| TABLE_NAME  | FIELD_NAME |
+-------------+------------+
| ENG.TABLE_1 | FIELD_1    |
| ENG.TABLE_1 | FIELD_2    |
| ENG.TABLE_2 | FIELD_A    |
+-------------+------------+

The underlying tables look like this:

TABLE_1
+---------+---------+
| FIELD_1 | FIELD_2 |
+---------+---------+
| A       | X       |
| A       | Y       |
| B       | Y       |
| B       | Y       |
| B       |         |
| C       |         |
+---------+---------+

TABLE_2
+---------+
| FIELD_A |
+---------+
|       1 |
|       1 |
|       1 |
|       2 |
|       3 |
+---------+

TABLE_3, TABLE_4, etc...

I want to expand the list of fields from FIELD_LIST_VW, so that there is a row for each unique value, per field:

+-------------+------------+--------------+
| TABLE_NAME  | FIELD_NAME | UNIQUE_VALUE |
+-------------+------------+--------------+
| ENG.TABLE_1 | FIELD_1    | A            |
| ENG.TABLE_1 | FIELD_1    | B            |
| ENG.TABLE_1 | FIELD_1    | C            |
+-------------+------------+--------------+
| ENG.TABLE_1 | FIELD_2    | X            |
| ENG.TABLE_1 | FIELD_2    | Y            |
+-------------+------------+--------------+
| ENG.TABLE_2 | FIELD_A    | 1            |
| ENG.TABLE_2 | FIELD_A    | 2            |
| ENG.TABLE_2 | FIELD_A    | 3            |
+-------------+------------+--------------+

What I've tried:

I figure a good place to start would be a cross join sub-select:

SELECT
    a.table_name
    ,a.field_name
    ,b.unique_value
FROM
    eng.field_list_vw a
CROSS JOIN (
    SELECT
        'FIELD_1' AS field_name    --<-- Hardcoded
         ,field_1 AS unique_value  --<-- Hardcoded
    FROM
        eng.table_1                --<-- Hardcoded
    GROUP BY
        field_1                    --<-- Hardcoded
    ) b
WHERE a.field_name = b.field_name

But of course, the cross join sub-select is full of hardcoded table & field names. The query joins TABLE_1, FIELD_1, but not any of the other fields in FIELD_LIST_VW. This is not what I want:

+-------------+------------+--------------+
| TABLE_NAME  | FIELD_NAME | UNIQUE_VALUE |
+-------------+------------+--------------+
| ENG.TABLE_1 | FIELD_1    | A            |
| ENG.TABLE_1 | FIELD_1    | B            |
| ENG.TABLE_1 | FIELD_1    | C            |
+-------------+------------+--------------+
(rows are missing)

Instead of hardcoding, is there a way to dynamically cross join to the unique values of each field in FIELD_LIST_VW?

I do not have CREATE TYPE privileges.


texts: Filter condition not correctly applied to Clustered Columnstore index

texts:
Using the below example, the predicates are the same, however the top statement (correctly) returns 0 rows, the bottom statement returns 1 - even though the predicates do NOT match:

declare @barcode nchar(22)=N'RECB012ZUKI449M1VBJZ'
declare @tableId int = null
declare @total decimal(10, 2) = 5.17

SELECT 1
FROM
    [dbo].[transaction] WITH (INDEX([IX_Transaction_TransactionID_PaymentStatus_DeviceID_DateTime_All]))
WHERE
    Barcode = @barcode
    AND StatusID = 1
    AND TableID = @tableID
    AND @total <= Total

SELECT 1
FROM
    [dbo].[transaction]
WHERE
    Barcode = @barcode
    AND StatusID = 1
    AND TableID = @tableID
    AND @total <= Total

Why could this be happening?

Further info:

The Non Clustered Index in the top statement is NOT filtered
CheckDB returns 0 issues
Server Version: Microsoft SQL Azure (RTM) - 12.0.2000.8   Dec 19 2018 08:43:17   Copyright (C) 2018 Microsoft Corporation

Paste the Plan link:

https://www.brentozar.com/pastetheplan/?id=S1w_rU68E

Further Info:

Have ran dbcc checktable ([transaction]) with all_errormsgs, extended_logical_checks, data_purity which indicates no issues.

I can reliably reproduce the issue against this table when restoring a backup of this database.


texts: Ensuring that Oracle sequence does not violate Java Integer constraints

texts:
Take for example a standard primary key on a JPA entity (POJO), similar to what is described by the first section of Configuring a JPA Entity Primary Key in the Oracle® Containers for J2EE Enterprise JavaBeans Developer's Guide:

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="ORGANIZATION_ID_SEQ")
@SequenceGenerator(name="ORGANIZATION_ID_SEQ", sequenceName = "ORGANIZATION_ID")
@Column(name="ORGANIZATION_ID")
private Integer organizationId;

Let's assume that the data in this table has the potential to be extremely large (over 2 billion rows). The sequence ORGANIZATION_ID above, when inspected in TOAD, has a maxValue of 999999999999999 (which is much bigger than the max value for Java Integer) and Cycle set to "No".

I can change the JPA Entity to be private Long organizationId, but I'm worried about the cascading impact on this very complex application. Are there any tricks or settings I should be aware of on the Oracle side that would make these sequences play nicely with Java Integers?

Right now, the expectation is that the DBA will simply backup and truncate the tables, then reset the sequences, when they are near Java Integer MAX_SIZE.


texts: SQL to update table with records from second table (but only those with distinct value in a given column)

texts:
Using PostgreSQL 10, I have two tables (say T1 and T2) where I would like to INSERT into columns A and B of table T1 records using columns D and E of table T2 while satisfying the UNIQUE constraint of column A on table T1.

Basically, ...

Find records in table T2 where a value is present in column D of table T2 that is not present in column A of table T1.
Take at most one record for each distinct column D value identified in (1) (doesn't matter which one if multiple records exist) and insert it (along with one or more other columns from table T2) into table T1.

Table illustration:

T1:
  UID : primary key, autoassigned by database sequence
  A   : Unique constraint (distinct values from column D of Table T2)
  B   : Data field #1
  C   : Data field #2
T2:
  UID : primary key
  D   : Multiple values
  E   : Data field #1
  F   : Data field #3

Based on research thus far and searches of somewhat similar questions, I believe that I'm looking for something akin to the following:

INSERT INTO T1 ("A","B")
SELECT T2."D", T2."E"
FROM T2 LEFT INNER JOIN LATERAL (...) ON (...)
WHERE T1."UID" IS NULL;

I'm unclear how to formulate the query to perform the desired result. For example, given the following data, I'd like the query to INSERT either {w,e} or {w,g} into columns {A,B} of Table #1:

T1:                   T2:
     UID | A | B | C      UID | D | E | F
    -----+---+---+---    -----+---+---+---
      0    x   a   b      100   w   e   f
      1    y   c   d      200   w   g   h
                          300   x   a   i
                          400   y   c   j

T1: (post-transaction):
     UID | A |  B  | C
    -----+---+-----+---
      0    x    a    b
      1    y    c    d
      2    w   e|g   NULL

Thank you in advance.

Update (2018-08-06 9:40 AM EDT): Changed post-transaction value in column C of T1 for newly inserted record to NULL to avoid ambiguity.


texts:
Do you know if there is a system/product/technique which would allow to "wrap" one or more relational databases into one object-oriented virtual database (or could we call it a "multi-database object-oriented proxy")?

Let me illustrate the principle:

So, basically, the pink thing in the middle (hereafter "object proxy") would contain definitions of business entities and their mappings to data in multiple relational databases. Applications would request or insert/update/delete data from the object proxy, which would magically synchronize with underlying relational databases.

Is that possible? Does such a system exist?


texts: Specify build and probe tables to hash join?

texts:
I am observing a situation in AdventureWorks database where the hash join operator is building hash table using a larger input (296 rows) and then probing with a much smaller input (3 rows). I'm not able to understand why this reverse mapping is favoured by SQL server.

Following is the query and plan:

SELECT *
FROM HumanResources.Employee e
INNER MERGE JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
INNER HASH JOIN HumanResources.Shift s ON edh.ShiftID = s.ShiftID

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

I wonder if I can specifically instruct SQL to use HumanResources.Shift for build and the other input to probe?


texts: Creating an SSIS package that uses a stored procedure

texts:
We're trying to make an SSIS package where it'll launch a stored procedure and capture the contents in a flat file. This will have to run every night, and the new file should overwrite the existing file.

This wouldn't normally be a problem, as we just plug in the query and it runs, but this time everything was complicated enough that we chose to approach it with a stored procedure employing temporary tables. How can I go about using this in a DTS package? I tried going the normal route with the Wizard and then plugging in EXEC BlahBlah.dbo... It did not care for that:

  The Statement could not be parsed. Additional information: Invalid
  object name '#DestinyDistHS'. (Microsoft SQL Server Native Client
  10.0)

Can anyone guide me in the right direction here?

Thanks.


texts: Create Database within the same Cluster without closing active connection?

texts:
Is there  a way to clone a master database without closing an active connection? I have a single master database and multiple slave database. To clone a new database I use the following command

CREATE DATABASE slave_n WITH TEMPLATE master

This command is very nice but it is giving me trouble. I have to currently close any active transaction to the master database. This cannot happen because I am using a loop in python and the command is on the loop. E.g. of my code:

con = connect(dbname='postgres', user=user, host='localhost', password=password)
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cur = con.cursor()
for db in self.browse(ids):
    if db.created:
        continue
    dbname = db.name
    cur.execute("select pg_terminate_backend(pid) from pg_stat_activity where datname='"+master_db+"'"); ##terminate active connection
    ## select pg_terminate_backend(procpid) from pg_stat_activity where datname='db'; ###falback if psql <9.2
    cur.execute('CREATE DATABASE ' + dbname +" WITH TEMPLATE "+master_db)
    '''
    database.set_active_db(master_db)
    db_con = database.get_connection()
    db_con.execute("update report_template set created = True")
    db_con.commit()
    '''
cur.close()
con.close()

Is there a way to clone without having to delete all the connection? My first code con = Connect is also closed Resulting in unwanted errors.

Any suggestion or Help is highly appreciated.


texts: Is there any way to find out if any alteration is done to any table?

texts:
I am using SQL Server and a database has too many tables. I am preparing the list of tables which have been recently modified.

Is there any way to find out which tables are modified?

I am interested in knowing:

if any datatypes are modified to any columns,
new column is added,
any column is deleted.

Note
The answers below shows tables which has been modified but do not give enough information of what changes has been done to the tables.


texts: SUM of column by GROUP BY which is WHithin a GROUP BY

texts:
i want to display the rows GROUP BY main Reference Id and then show the amount GROUP BY type.

this my query

SELECT gl.`drcr`,gl.`amount`,gl.`name`,gl.`type`,
       gl.`narration`,gl.`main_reference`,gl.`date` "
. "FROM `general_ledger` AS gl "
. "INNER JOIN ( "
. " SELECT SUM(amount) AS total
    FROM `general_ledger` GROUP BY `type` ) AS tot "
. "WHERE gl.`date` BETWEEN '$start_date' AND '$end_date' "
. "GROUP BY gl.`main_reference`,gl.`date`,gl.`name`,
            gl.`type`,gl.`amount`,gl.`drcr`,gl.`narration`

if main reference id and type same want to add both row amount and need to display as one row like

please help me..
thanks in advance


texts: Data Pipeline load bulk tables in one time

texts:
I am using AWS Data Pipeline for copying my RDS MySQL Database to Redshift. I need to create separate pipeline for each table and each pipeline create new EC2 instance. (Process takes time).

Problem

Is there any way to load whole database table into Redshift with single Data Pipeline . so i can schedule it and sync all my data daily with single pipeline.

NOTE: I have almost 250+ tables in DB that needs to be sync with redshift db (Almost daily). Or suggest any other better way for that purpose.


texts:
I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them in set 1. Next three are 0,0,1 and I want them in set 2.

Order is important as I'm matching against a pattern table to get an overall count of the binary patterns.

I can see that Row would help but I don't know how to make row count by 3'ss.

Thank you.


texts: Innodb: Operating System Error number 5 in MySQL

texts:
I am trying to retrieve the data from MySQL ibd file but facing following error.

140601 17:00:45 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140601 17:00:45 [Note] Plugin 'FEDERATED' is disabled.
140601 17:00:45 InnoDB: The InnoDB memory heap is disabled
140601 17:00:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140601 17:00:45 InnoDB: Compressed tables use zlib 1.2.3.4
140601 17:00:45 InnoDB: Initializing buffer pool, size = 8.0M
140601 17:00:45 InnoDB: Completed initialization of buffer pool
140601 17:00:45 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140601 17:00:45  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Error: tried to read 16384 bytes at offset 136 2748317696.
InnoDB: Was only able to read -1.
140601 17:00:45  InnoDB: Operating system error number 5 in a file operation.
InnoDB: Error number 5 means 'Input/output error'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: File operation call: 'read'.
InnoDB: Cannot continue operation.

Please help me out from this issue.

Thanks


texts: sql server configuration manager says it is on a 32 bit machine and it is not. Should we change that?

texts:
when running the following script for Determining 32/64 bit in Powershell

cls
if ((gwmi win32_operatingsystem | select osarchitecture).osarchitecture -eq "64-bit")
{
    #64 bit logic here
    Write "64-bit OS"
}
else
{
    #32 bit logic here
    Write "32-bit OS"
}

I get a nice reply back:

  64-bit OS

However, when running the sql server configuration manager

I get it is running on 32 bits as you can see on the picture below.

now 2 questions:

where is sql server looking at to say it is running on a 32 bits system?

how do I change that?

ok 3rd question, is it worth changing?


texts: SQL Server full database backup without a job

texts:
I am trying to work out where I can modify a schedule for a backup job that is running. The SQL Server was setup by a team before it was handed to us.
We have a set of maintenance jobs in our SQL Server Agent which I can modify, but looking through the server logs there is another full backup that runs, backing everything up into a blob storage.
The issue is, I cannot find where this is being scheduled. There is no job that I can see in the Agent that is executing this backup and there is no maintenance plan in the management folder either.
Is there something I am missing? Or any way for me to find what is executing this backup?


texts:
maybe this is more of a wiki type question, but I'd like to learn more about the (no doubt many) paths taken to become a DBA.
What path lead you to become a DBA?


texts:
I would like to start learning about the big data technologies. I work in this area in the future. Does anyone know good books to start learning about it? Hadoop, HBase.

Beginner -
intermediate -
advanced -

Thanks in advance


texts: Is it more efficient to store 0 and 1 for a binary value or just empty and 1 in MySQL

texts:
Is it more efficient to store 0 and 1 for a binary value indicating whether a flag is up or down or just empty and then set 1 for rows where the flag is up. Using MySQL.

E.g.

Flag
0
1
1
0
0

Or

Flag
''
1
1
''
''


texts: How to reset MariaDB into a "fresh install" state?

texts:
I had InnoDB corruption and managed to start the server in read only mode and perform a fresh backup using innodb_force_recovery=5.

This way of starting the service puts the databases in read only mode, even deletion is disallowed.

Is there an official procedure to reset the whole server into a fresh installed (or at least "empty") version?

And in case there isn't, then what are the correct uninstall/reinstall steps to make sure there will be no remaining residues of data that could generate problems in the future?


texts:
Using SQL-Server 2008 R2 and Report Builder 3.0

Here is my query without the running total column:

SELECT
   CostManagement_CommitmentDetails.Id
  ,CostManagement_CommitmentDetails.LineNumber AS LineNumber
  ,CostManagement_CommitmentDetails.Description
  ,CostManagement_CommitmentDetails.CostCodeId
  ,CostManagement_CommitmentDetails.TotalCost
  ,CostManagement_CommitmentDetails.PeriodId
  ,Periods.Id AS [Periods Id]
  ,Periods.LineNumber AS [Periods LineNumber]
  ,Periods.Period
  ,Periods.FromDate
  ,Periods.ToDate
  ,Projects.ProjectName
  ,[CostManagement_Commitments].ProjectId

,'COM' as RecordType

,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.[CostCodeId], 1) AS Group1
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 1) AS Group1Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 2) AS Group2
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 2) AS Group2Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 3) AS Group3
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 3) AS Group3Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 4) AS Group4
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 4) AS Group4Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 5) AS Group5
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 5) AS Group5Description

FROM
  CostManagement_CommitmentDetails
  LEFT OUTER JOIN Periods
    ON CostManagement_CommitmentDetails.PeriodId = Periods.Id
LEFT OUTER JOIN [CostCodes]
    ON [CostManagement_CommitmentDetails].CostCodeId = [CostCodes].Id
LEFT OUTER JOIN [CostManagement_Commitments]
    ON CostManagement_Commitments.Id = [CostManagement_CommitmentDetails].CommitmentId
LEFT OUTER JOIN [Projects]
    ON [Projects].Id = [CostManagement_Commitments].ProjectId

Where (Period <= @ToPeriod) and [CostManagement_Commitments].ProjectId IN (@Projectlisting)AND 'COM' IN (@RecordTypes)

Running totals work fine if my dataset is from the start date to end date for both the Graph and Matrix.  I run into trouble when I want to show just a subset of all the dates say a 3 month window.  The Matrix works fine in keeping the running total values  from the start of the project even though those months are not displayed in the Matrix, but the Graph sets the Running totals (cumulative) to zero which displays incorrect totals.  The Matrix has all the correct totals, the Graph does not.  Category in the Graph does not have a visibility option.  Looking for a solution either Graph wise or by adding a running cumulative total column that I can then graph because of the current graph limitations for Category.

If you look at the last Graph is has 2.4 million for the cumulative total for the yellow line but it should be 5.5 million for period 07-1403

Note:

We are restricted from creating any Stored Procedures.
The Bars in the Graph are Period/Month totals only.
The Line in the Graph is the cumulative running total.

All help is greatly appreciated.


texts: How to select the most recent entry based on a field in MySQL

texts:
My table has multiple entries by individual customers, These customers have made multiple purchases over the years from any of several suppliers listed in my database. I need to run a query to select those whose most recent purchase was from supplier 'A'. If a customer purchased from supplier 'A' last month, but their most recent purchase was, say, yesterday from supplier 'B', then I don't want them included in the result. This may be a simple query, but I'm having trouble getting my head around a reliable method of running this query.

Basically, what I'm looking to generate is:

SELECT
`customer_email`,
`supplier`,
`purchase_date`
 from `customer_table`
WHERE
`supplier` = 'A' and

... "this purchase by "customer_email" is "customer_email's" most recent purchase, even though they have made multiple previous purchases from any of several 'suppliers'.

Any help in pointing me in the right direction as to completing this query would be greatly appreciated!


texts: Job fails overnight intermittently because of a windows authentication failure to log on to SQL Server. How do I troubleshoot?

texts:
The failed job is a Litespeed transaction log backup, but also happens to fulls and diffs occasionally.

This same setup works on other servers without issue, and it's only a few times overnight that the error occurs, but it does happen every night at some point:

Error in SQL Server Agent Log:

"Logging on to SQL Server 'SQLFOO' with Windows Authentication  TWideSafeCallErrorException - Timeout expired  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\slssqlmaint.exe"

Error in Litespeed log:

"Msg 49999, Level 19, State 1, Line 0 Failed to login to SQL Server".

Is there anything I can look for on the server that would give me a clue as to why there is an intermittent login error?

EDIT: More information - A scheduled query was discovered that runs across this time period for several hours, all the failures happen during this time period. The transaction log backups that do complete are taking 10 minutes compared to less than a minute to complete as they normally do.

EDIT2: No transaction log failures when the job isn't running. There is one update script that while running still causes the trxlog backup to fail with the logon authentication error. It's an update script that hits a ton of records, I guess it's time to put on my junior_dev hat.


texts: Stored Procedure Execute as Owner Close Database Connections Not Working

texts:
I created a stored procedure to allow users to Close All Db connections in the QA environment.  I am SA and created the procedure.

When I modify the SP , run it Without 'Execute as Owner', I get results.
When I add 'Execute as Owner', I do not receive any results.

Trying to understand why.

create procedure [dbo].[DatabaseConnectionClose]
    @DatabaseName varchar(255)
with execute as owner
as

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id(@DatabaseName)

select @kill as CloseConnectionScript

EXEC(@kill);


texts: A beautiful trigger issue. Flawed logic? Updating Orderheader when orderlines are complete

texts:
THe bussiness rule is as such: The orderlines must be updated with a date stamp when all the related orderlines are marked as 'Y' denoting a completed order line.

THe trigger thus far is:

CREATE OR REPLACE TRIGGER orderComplete
after update ON orderline
DECLARE
orderId NUMBER;
ordersNotDone NUMBER;
BEGIN

    SELECT COUNT(Orderline_fulfilled), order_id INTO ordersNotDone, orderId
    FROM orderHeader
    JOIN orderline ON
    orderHeader.Order_id = orderLine.Orderline_order
    WHERE Order_id = orderline_order
    AND orderline_fulfilled = 'N'
    GROUP BY order_id;

    if ordersNotDone = 0
    then
        UPDATE orderHeader
        set ORDER_COMPLETED = sysdate
        WHERE order_id = orderId;
    end if;

END;

Upon an update of the order line, an 'no data found' is returned- is this sugesting the orderId variable is not returning data? The queries do work, however, when taken out of the trigger and ran independently.


texts: Masking Certain Parts of Specific Records

texts: Is there a way in SQL Server to mask only certain records for a given column based on a primary key value? I know the new Dynamic Data Masking function automatically masks the whole field that you select. Is there a way to just do certain records?


texts: How to filter date columns in Mysql database effectively?

texts:
I have a column called created_at and its datatype is (date).
Consider the following two SQL queries.

select * from my_table where created_at < '2019-11-05'

and the other one is

 select * from my_table where created_at < date('2019-11-05')

which query is right and should I use in the future date comparisons?


texts: Get start time and end time of a trip in SQL Server


texts: How can I limit a hierarchical query to the point where there are multiple children?

texts:
This question is related to my previous question about hierarchical queries.

I have a hierarchical query in Oracle 11gR2 that returns something like this:

- Level 1
  - Level 2
    - Level 3
      - Level 4
        - Level 5
          - Level 6
      - Level 4
        - Level 5
          - Level 6
            - Level 7

I'd like to design a query to return the rows for levels 1-3, on the basis that there are two siblings at level 4. Or, to put it another way, I'd like to return all the rows in the hierarchy, starting from a given point, until I reach a level with more than one sibling. I'd then like to stop there, excluding the level with multiple siblings.

I've tried putting count functions in various places in my query, but I can't work it out. This is what I have so far:

select   *
  from
    (select   level, count (*) over (partition by level) level_count, <columns>
      from <tables>
        start with <predicate>
        connect by prior <child> = <parent>
    )
  where level_count = 1;

This works for the case there the length of each branch is the same, but returns extra rows otherwise. In my example above, I want to return only the rows for levels 1-3 (i.e. 3 rows in total) and exclude level 7 because level 4 has two siblings. I hope that makes sense!


texts: Building distinct Column from a common column from two tables

texts:
Good afternoon team,

hope all is well

I am hoping you can help me.

I have following two tables "GODS.DQ_TOBE" & "GODS.DQ_CURRENT". Within each table there is a common column called "Account_code__C". What i would like to do is build a  column called Account_code__C which houses the distinct account codes from both of these tables. i would then like to produce two other columns to tell me what table the Account_code__C was found in.

Desired outcome would look something like this

SELECT Account_code__C FROM GODS.DQ_TOBE
Select Account_code__C from GODS.DQ_CURRENT


texts:
I have master categories table like this

category_id    category_name            parent_name           parent_id
     1         animals & pet supplies       null                   0
     2         live animals            animals & pet supplies      1
     3         pet supplies            animals & pet supplies      1
     4         bird supplies           pet supplies                3
     5         clothing                     null                   0
     6         mens clothing           clothing                    5
     7         womens clothing         clothing                    5
     8         shirts                  mens clothing               6
     9         formal shirts           shirts                      8

when the user selects category i need to create new table with all categories and subcategories & sub sub categories (infinity subcategories). for example if user selects animals & pets supplies the new category table should be like this

  category_id    category_name            parent_name           parent_id
     1         animals & pet supplies       null                   0
     2         live animals            animals & pet supplies      1
     3         pet supplies            animals & pet supplies      1
     4         bird supplies           pet supplies                3

if the user selects clothing category then new category table should be like this

 category_id    category_name            parent_name           parent_id
     5         clothing                     null                   0
     6         mens clothing           clothing                    5
     7         womens clothing         clothing                    5
     8         shirts                  mens clothing               6
     9         formal shirts           shirts                      8

anybody please help me in achieving this. thanks in advance. suggestions to change in table designing also welcome to achieve this


texts:
I'm having issues using DISTINCT and COUNT in the same query. I would like to get unique items then get a total count for each month. (I've done this in two queries but I know there's a better way.)

Query:

SELECT
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date,
  count(*) Total_Sold
FROM
  `project.dataset.items`
GROUP BY
  Sell_Date
ORDER BY
  Sell_Date

Results (snippet):

Row     Sell_Date   Total_Sold
 1       2010-05        15
 2       2010-06        40
 3       2010-07        75
 4       2010-08        20

This is what I would like, however, this contains duplicate Item_Id entries.

If I replace the SELECT above with this:

SELECT
  DISTINCT Item_Id,
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date,

I get this error:

  Error: SELECT list expression references column Item_Id which is neither grouped nor aggregated at [2:12]

If I replace the GROUP BY with:

GROUP BY
      Sell_Date, Item_Id

Results (snippet):

Row     Item_Id      Sell_Date   Total_Sold
 1       992         2010-05         1
 2       118         2010-05         1
 3       855         2010-05         1
 4       846         2010-05         1
 5       989         2010-05         1
 6       505         2010-05         1
 7       997         2010-05         1
 8       983         2010-05         1
 9       122         2010-05         1
 10      601         2010-05         1
 11      845         2010-05         1

How can I get DISTINCT items then count the total for each month?


texts:
I have a table that store messages for my Chat app:
CREATE TABLE message (
    currentUserId,
    threadId,
    timestamp,
    message,
    msgType,
    msgState )

Then I used this query to load 20 message for each thread:
SELECT rowid, * FROM message WHERE currentUserId = '123' AND threadId = '456' ORDER BY timestamp LIMIT 50, 20

So which index should I create to optimize loading speed of this query?
I already try to index in each of these approaches but none of them seem to be working:

timestamp
threadId
(threadId, timestamp)


texts: Any disadvantages of "SQL_BUFFER_RESULT" for MySQL SELECT?

texts:
I've been researching the usage of SQL_BUFFER_RESULT. Mostly it is referred to as an aid to reduce table lock issues.

It seems to be a good option to use.

However I cannot seem to find any disadvantages about it. Is it an option which should be used most of the time?