I have a table that's had CONSTRAINT table_pkey PRIMARY KEY (id) since setup. However, I recently found that I have around 100 duplicate rows (I ran select count(*) from (select count(*) from table group by id having count(*) > 1) as t1 as per this old stack post . However, I'm running Postgres 10.6, which should be far after that bug. Any ideas?

I need to identify which application process issues a large number of MySQL connections on a Linux system. The show processlist sql command or tools like innotop do not really help, as they only show db user and host, and i doubt that i catch all statements that way. I was thinking to use something on the network level, like tcpdump or netstat. Any suggestions?

I have seeing a strange behaviour with PostgreSQL13 pg_settings system view. I tried updating max_connections using SET command and left without a restart. But if I query from the same session or any new session the pending_restart column in pg_settings is not showing true for this parameter. However if I issue a statement like I attached in the screenshot then pending_restart column shows correctly as `true. What's this behaviour related to?

I am trying to import some relatively large databases, over 100 GB dump file. When I try to import the dump files with a command like: mysql -h localhost -u username -p dbname < backup.sql after some hours it fails and it prints last line, I cannot see upper if there is a start of error, because I am in screen and screen only shows end of output. What I get there is a dump line like:

,('daga88.bet',0,1,1,0,6,257078,-132553,1662996449,179113,598788,NULL,NULL,0,0,1712710000,NULL,1710150000,1652513002,NULL),('daga88.com',1,1,1,0,6,266021,0,1672675038,266021,266021,NULL,NULL,0,0,1672675098,0,1708690000,1672687391,NULL),('daga88.live',0,1,1,0,6,471459,0,1655308937,471459,471459,NULL,NULL,0,0,1707210000,0,1710150000,1655345529,NULL),('daga888.live',0,1,1,0,7,344485,0,1655736029,344485,344485,NULL,NULL,0,0,1707640000,0,1710150000,1655753000,NULL),('daga88vn.net',1,1,1,0,8,620701,0,1648227623,620701,620701,NULL,NULL,0,0,1697470000,0,1715620000,1652513003,NULL),('daga99.net',1,1,1,0,6,692132,0,1582125484,329203,746817,'not found',NULL,0,1683060000,1712150000,0,1713570000,1652513047,NULL),('daga999.com',1,1,1,0,7,382440,0,1630243622,382440,382440,NULL,NULL,0,0,1716600000,0,1717490000,1652513049,NULL),('daga999.net',1,1,1,0,7,630164,0,1581778443,630164,630164,NULL,NULL,0,0,1709620000,0,1714150000,1652513051,NULL),('dagaa.in',0,1,0,0,5,219833,-76305,1646767866,219833,330146,NULL,NULL,0,0,1701070000,0,1710150000,1652513053,NULL),('dagaa.mx',0,1,0,0,5,28433'.

Then I open the dump file with vi editor to look at that line and I see this:

('dagaa.mx',0,1,0,0,5,28433^@^@^@^@^@^@^@^@^@^@^@^@^@^

I don't see any error in mysql log. Maybe an error might have been printed at export time. What could that mean ? I assume my dump is corrupted ? What could cause this ? I remember I seen this before but cannot remember the cause / fix. Maybe this can be fixed by tuning some mysql values to process more data at once ? like innodb_redo_log_capacity or other parameters ? Is hard to "try" things because this happens after 10-12 hours of processing (importing .sql file).

This takes up [How to debug a script component in SSIS - Stack Overflow](https://stackoverflow.com/questions/6446619/how-to-debug-a-script-component-in-ssis) which was asked for the 2008 version and is now outdated. That is why this question needs to be revived for the versions of nowadays. I put a breakpoint in two Script Components in an SSIS Data Flow Task. Only the first Script Component gets debugged, as soon as I reach the second, the script is run to its end without stopping at any breakpoint. How can I debug the second Script Component so that the debugger stops at the breakpoints there?

This takes up [SSIS package with status running is not running - Stack Overflow](https://stackoverflow.com/questions/33889507/ssis-package-with-status-running-is-not-running) . SSIS project window turns white right at the beginning of debugging, status "Running": Right click closing cannot be done: And if I hover over the task bar icon, wait for the preview to pop up, and right click on the X of the crashed window, I can see the project again in the old window so that I can close the other window since I do not need it anymore: And you can see that the debugging has run through, everything green: And the whole run took just 3 seconds even though I waited minutes for the white window to go away: And the run is flawless, no warnings or errors in the Progress / Execution Results. This happens at every debugging. How can I get rid of this whitened project window of Visual Studio right at the beginning of running a container? PS: This is most likely not linked since I run into this too often, but who knows: at the same time, I also cannot change the size of any boxes in the Control Flow or Data Flow. The mouse cursor sometimes stays a double arrow after having tried resizing something, and even though it is a strange double arrow, I can double click into the boxes or click to rename things.

We've been having several problems the past days in our production environment, and some of them seem to boil down to database issues. Just realized we are having deadlocks (68 actually), on database "waypoint": Can anyone please provide hints on where / how to search for possible deadlock causes? select version(); => PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit Found this evidence in log: 2016-03-06 06:58:39 UTC [11807-1] waypointtx@waypoint ERROR: deadlock detected 2016-03-06 06:58:39 UTC [11807-2] waypointtx@waypoint DETAIL: Process 11807 waits for ShareLock on transaction 370917997; blocked by process 11788. Process 11788 waits for ShareLock on transaction 370917865; blocked by process 11807. Process 11807: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result Process 11788: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result 2016-03-06 06:58:39 UTC [11807-3] waypointtx@waypoint HINT: See server log for query details. 2016-03-06 06:58:39 UTC [11807-4] waypointtx@waypoint CONTEXT: while updating tuple (2836,88) in relation "instantaneo" SQL statement "UPDATE telemetria_data.instantaneo SET utc = '2016-03-06 06:58:34', registro=now(), power=0, voltaje = 0, valor ='{2147}'::double precision [] WHERE imei=354676055362536 and clase=40;" PL/pgSQL function telemetria_data.insert_tabla_instantaneo(bigint,timestamp without time zone,integer,double precision[],integer,double precision) line 13 at EXECUTE statement 2016-03-06 06:58:39 UTC [11807-5] waypointtx@waypoint STATEMENT: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result So it seems that the following part is the problem: UPDATE telemetria_data.instantaneo ....... WHERE imei=354676055362536 and clase=40; Am I right? Possible offending function: CREATE OR REPLACE FUNCTION telemetria_data.insert_tabla_instantaneo( bigint, timestamp without time zone, integer, double precision[], integer, double precision) RETURNS boolean AS $BODY$ DECLARE imei ALIAS FOR $1; utc ALIAS FOR $2; clase ALIAS FOR $3; valor ALIAS FOR $4; power ALIAS FOR $5; voltaje ALIAS FOR $6; num_rows int; BEGIN EXECUTE 'UPDATE telemetria_data.instantaneo SET utc = ''' || utc || ''', registro=now(), power='|| power ||', voltaje = '|| voltaje ||', valor =''{' || array_to_string(valor,',') ||'}''::double precision [] WHERE imei='|| imei ||' and clase=' || clase || ';'; GET DIAGNOSTICS num_rows = ROW_COUNT; IF num_rows > 0 THEN RETURN TRUE; ELSE EXECUTE 'insert into telemetria_data.enabled_units (imei,clase) values ('|| imei ||','|| clase ||');'; EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',') ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; RETURN TRUE; END IF; EXCEPTION WHEN UNIQUE_VIOLATION THEN EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',') ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; RETURN TRUE; WHEN foreign_key_violation THEN RETURN FALSE; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; Related table structure: CREATE TABLE telemetria_data.instantaneo ( imei bigint NOT NULL, utc timestamp without time zone NOT NULL, clase integer NOT NULL, valor double precision[], registro timestamp without time zone DEFAULT now(), power integer DEFAULT (-1), voltaje double precision DEFAULT 0.0, CONSTRAINT pk_telemetria_data_instantaeo PRIMARY KEY (imei, clase), CONSTRAINT fk_telemetria_data_clase_id FOREIGN KEY (clase) REFERENCES telemetria_data.clase (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE );

I have a MySQL EER diagram the I forward engineer into a database. I also use the forward engineer feature to generate insert statements for data automatically. After entering about 120 rows of data I forward engineered the database only for it to give an error:

SQL script in server
ERROR: Error 1054: Unknown column 'XX' in 'field list'
SQL Code:
        INSERT INTO mydb.Person (Person_ID, SSN, FirstName, MiddleName, LastName, Gender, Race, MaritalStatus, Military, Note1, Note2, DOB) VALUES (111, 'xxx-xx-xxxx', 'John', 'K', 'Smith', 'M', 'XX', 'XX', XX, 'Case_1', NULL, NULL)

I don't understand what the problem is; I'm using the forward engineer feature so none of the column names can be misspelled. Is this just a bug? I'm using MySQL Workbench 8.0.31 if that helps. I know it isn't the latest version, but I downgraded because I encountered crashes while querying in the new version. **Update**: J.D helpfully pointed out that the field didn't have quotes and from there I was able to find the problem. The Military field was a TinyInt and therefore wasn't being given quotation marks, but the data being inserted was a string; hence the error.

I installed SSMS 19 Preview 4 and it doesn't have a Debug Menu or Debugging option. I also learnt that SSMS V17 was the last version with that capability. Then I decided to try Azure Data Studio, but still not able to see debug option. Can someone direct me how I can debug an SP using Azure Data Studio IDE?

How to print(var) in postgres like python? Given the following code:

CREATE OR REPLACE FUNCTION custom_sum(c1 integer, c2 integer)
RETURNS integer AS 
$$
    SELECT c1 + c2;
$$ 
LANGUAGE SQL;

CREATE aggregate agg_custom_sum(integer) (
    sfunc = custom_sum,
    stype = integer,
    initcond = 0
);

DROP TABLE IF EXISTS aggcheck;
CREATE TABLE aggcheck AS 
SELECT x FROM generate_series(1, 3) AS g(x);

SELECT agg_custom_sum(x) FROM aggcheck;

How can I print the values of c1, c2 to the terminal when running? Eg (I'm aware this won't work - it's python syntax - hopefully communicates the sort of usage I'm after though):

CREATE OR REPLACE FUNCTION custom_sum(c1 integer, c2 integer)
RETURNS integer AS 
$$
    print(f"c1 = {c1}, c2 = {c2}"); 
    SELECT c1 + c2;
$$ 
LANGUAGE SQL;

If it makes much difference - I'm calling this using \i script.sql from within a psql session. If there are any links to debugging approaches more generally (eg breakpoing, try/catch, writing to a file) they would be appreciated, though not necessary to answer this question.

I have the following case statement

case %1$s::text
  when ''lookup_relation_job'' then
    case %1$s.relation
      when ''followers'' then ''get-followers''
      when ''following'' then ''get-following''
      when ''blocking'' then ''get-blocking''
      when ''muting'' then ''get-muting''
    end
  when ''manage_list_members_job'' then
    case %1$s.add
      when true then ''add-list-member''
      else ''remove-list-member''
    end
  when ''manage_relation_job'' then
    case %1$s.relation
      when ''follow'' then
        case %1$s.add
          when true then ''add-follow''
          else ''remove-follow''
        end
      when ''block'' then
        case %1$s.add
          when true then ''add-block''
          else ''remove-block''
        end
      when ''mute'' then
        case %1$s.add
          when true then ''add-mute''
          else ''remove-mute''
        end
    end
end

Here %1$s is an argument of type regclass. When I run the above with %1$s = lookup_relation_job, Postgres throws an error saying: column lookup_relation_job.add does not exist. But it shouldn't even check for the add column when the argument is lookup_relation_job according to the case logic. What's going wrong here? I suspect either there's some kind of fall-through happening here (I double checked that I've closed all case statements with end), or Postgres checks for all the conditions even if the parent condition is false (this is unlikely). Any help would be appreciated! Edit: Below is the whole relevant PL/pgSQL function

create or replace function get_jobs_to_add
  (job_table regclass, active_jobs bigint[], failed_jobs bigint[], out result bigint[])
  as $$
begin
execute format('
  select array(
    select
    distinct on (%1$s.user_id)
    %1$s.id
  from %1$s
    left join user_profile on user_profile.id = %1$s.user_id
    left join twitter_api_rate_limit on
      twitter_api_rate_limit.user_twitter_id = user_profile.twitter_id and
      twitter_api_rate_limit.endpoint = (
        case %1$s::text
          when ''lookup_relation_job'' then
            case %1$s.relation
              when ''followers'' then ''get-followers''
              when ''following'' then ''get-following''
              when ''blocking'' then ''get-blocking''
              when ''muting'' then ''get-muting''
            end
          when ''manage_list_members_job'' then
            case %1$s.add
              when true then ''add-list-member''
              else ''remove-list-member''
            end
          when ''manage_relation_job'' then
            case %1$s.relation
              when ''follow'' then
                case %1$s.add
                  when true then ''add-follow''
                  else ''remove-follow''
                end
              when ''block'' then
                case %1$s.add
                  when true then ''add-block''
                  else ''remove-block''
                end
              when ''mute'' then
                case %1$s.add
                  when true then ''add-mute''
                  else ''remove-mute''
                end
            end
        end
      )
  where %1$s.finished = false
    and %1$s.paused = false
    and %1$s.deleted = false
    and (twitter_api_rate_limit.resets_at is null or twitter_api_rate_limit.resets_at < now())
    and not (%1$s.id = any(%3$L))
    and %1$s.user_id not in (
      select user_id from %1$s where id = any(%2$L)
    )
  order by %1$s.user_id, %1$s.priority desc
);', job_table, active_jobs, failed_jobs) into result;

end
$$ language plpgsql;

We are trying to identify what is taking a SQL backup. The physical device name is just a guid which I have seen before when using a VSS type backup but I don't see any backup agent software installed on the host and there are no maintenance plans or jobs running any backups so we are kinda stumped. We are going to try and use Wireshark to see if we can determine anything. Anyone have any ideas about identifying what is running the backup? See screenshot of the backup. Thanks! 12/22/2022 04:47:55,spid60,Unknown,I/O is frozen on database msdb. No user action is required. However if I/O is not resumed promptly you could cancel the backup.

I have a maintenance plan subtask in SQL Server 2012 Enterprise that executes a stored procedure (or a SQL Server agent job). In the log file for this maintenance plan, there are a few lines for this task, such as the start- and end- date, and the SQL statement used to execute the stored procedure. Can I somehow generate text output in the stored procedure and have it included in the log? It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.

We have a group of fairly complex stored procedures that process a transaction queue on a round the clock schedule. These scripts use a number of print statements to document what is being done to aid in debugging. I want to keep this information while it is running in production (as an SQL Server Agent job) to help with troubleshooting when something goes wrong. I have looked at this site which has a fairly good method of saving the print output but it also has some downsides (output is not available until the script completes, etc). It also requires SSIS or DTS which I have no experience with. I have thought of changing all of the print statements to insert statements to a log table. The problem is that much of the work we need debug info for is under transaction control and if there is an error, all of the log information would be rolled back with the actual transaction. Does anyone have a better solution or an idea of how to work either of the solutions I have looked at above?

I have an application to debug which performs a few dozen operations against a MySQL database. These operations are performed in a single transaction. I need to know the state of the database while debugging at a specific breakpoint. I can log into the MySQL service using the common mysql CLI tool. I see that a transaction in progress:

mysql> SELECT * FROM information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 325090
                 trx_state: RUNNING
               trx_started: 2022-09-14 12:10:32
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 30
       trx_mysql_thread_id: 26
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 10
          trx_lock_structs: 26
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 43
         trx_rows_modified: 4
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.01 sec)

## Now, how can I join that transaction to query the state of the database during the transaction? As a workaround, I could add a query to the application code. However that requires me to know which query I want to run before running the application, and then to recompile the app for each query I want to run. Having an open CLI to debug would be very helpful. The database is MySQL 8.0 running on Debian.

Read it carefully, we have this query which is inserting values in the table called users. For the value member_id we are running a subquery to select from the table admin_users the id of the member. The reason why there are single quotes with +, it's because we are trying to manipulate the query. At this moment this first subquery works correctly but what happends with the second subquery? The second subquery selects the pass from the table settings, the table settings and the value pass totally exists and there is only one record, but this second query inside the INSERT INTO is not returning nothing. When the execution of the query INSERT INTO finishs, all the values are stored correctly except notes column which finally inserts 0. I don't know why but if you delete all the ''+ it works correctly the whole sql statement but in this time we can not delete ''+ because we are altering the query. I need a solution for this issue. INSERT INTO users (username,password,number,member_id,exp_date,notes) VALUES ('balvin','sjeneoeoe','3', ''+(select id from admin_users where username = 'TEST')+'', '1644622354', '' + (select pass from settings));#;'); Also i have tried modifying the second subquery like this but it didn't work. '' + (select pass from settings LIMIT 1) '' + (select pass from settings GROUP BY pass LIMIT 1) '' + (select pass from settings where id = 1 LIMIT 1) Perhaps the error it's the datatype of the column value pass in settings or the column notes in users CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) DEFAULT NULL, username varchar(255) COLLATE utf8_unicode_ci NOT NULL, password varchar(255) COLLATE utf8_unicode_ci NOT NULL, exp_date int(11) DEFAULT NULL, notes mediumtext COLLATE utf8_unicode_ci NOT NULL, number int(11) NOT NULL DEFAULT '1', PRIMARY KEY (id), KEY member_id (member_id), KEY exp_date (exp_date), KEY username (username), KEY password (password), ) ENGINE=InnoDB AUTO_INCREMENT=1702894 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE settings ( id int(11) NOT NULL, name mediumtext COLLATE utf8_unicode_ci NOT NULL, pass mediumtext COLLATE utf8_unicode_ci NOT NULL, ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I write a lot of debugging code in plpgsql along the lines: RAISE NOTICE 'var1 = %', var1; (there can be more than one variable to print out). Is it possible to avoid having to repeat the variable var1 every time by writing a PostgreSQL function (say myprint) so that SELECT myprint(var1); will generate the same output as RAISE NOTICE 'var1 = %', var1;? The problem is that I know how to get the value of var1 in the function but I don't know how to get the name var1. Is there some call-stack or context magic in plpgsql that can provide the name of the variable passed to myprint? (This is with PostgreSQL 12.1 or later)

I've been having this issue where replication was breaking on colliding primary keys. The annoying thing was these were auto increment generated. I think my issues been tracked down to http://bugs.mysql.com/bug.php?id=45677 for a couple reasons * The slaves that were breaking were using triggers * Slaves with identical schemas sans triggers were not breaking * I was able to reproduce the problem following the reproduction steps in the ticket What leaves a shadow of a doubt that's the exact bug that's biting me is the schema and application have been running for years with out issue. It started happening shortly after upgrading mysql versions. That might seem like a 'well duh' thing but it was an upgrade from 5.1.5 to 5.1.59. The ticket indicates it's been around since 5.1.35, so should have bitten me much sooner, no? If you've run into this what version were you running?

I was on mysql:8.0.15 and I updated to mysql:8.0.17 - On 8.0.15 ... and (col1, col2) in (('a', 'b')) the query return results, this is what I expect - On 8.0.17 the same query with same data return 0 results. I thought it was our application, so I downgraded back to 8.0.15 and the query is working again... I noticed ... and (col1, col2) in (('a', 'b'),('a', 'b')) is working in 8.0.17 I tried to search mysql bug list, but I can't find any information... In the changelog I see they optimized WHERE IN in 8.0.17, but it should not break my use case. > The optimizer now transforms a WHERE condition having NOT IN (subquery), NOT EXISTS (subquery), IN (subquery) IS NOT TRUE, or EXISTS (subquery) IS NOT TRUE internally into an antijoin, thus removing the subquery. This is similar to the existing IS NULL (Not exists) outer join optimization; see EXPLAIN Extra Information, for further information. In addition, the semijoin materialization can now be used for a WHERE condition having IN (subquery) IS TRUE, or EXISTS (subquery) IS TRUE, or when the IN condition belongs to a left join such as SELECT * FROM t1 LEFT JOIN t2 ON t2.x IN (SELECT * FROM t3). See Optimizing IN and EXISTS Subquery predicates with Semijoin Transformations. Also as a result of this work, MySQL is now able to simplify a co It seems too big to be a bug in mysql. Am I missing something ? Here is the full query

select t1.user_id    as user_id,
       t1.created_at as created_at,
       t1.updated_at as updated_at,
       t3.node_id    as t3node_id,
       t3.node_type  as t3node_type,
       t2.node_id    as t2node_id,
       t2.node_type  as t2node_type
from table1 as t1
         inner join table2 as t2 on t1.set_id = t2.set_id and t1.set_type = t2.set_type
         left join table2 as t3
                   on t2.node_type = 3 and t3.set_type = 5 and t3.set_id = t2.node_id
         left join table3 as ts on COALESCE(t3.node_type, t2.node_type) = ts.cache_node_type and
                                                 COALESCE(t3.node_id, t2.node_id) = ts.cache_node_id
where t1.tenant_id = '825773475713154048'
and (t1.user_id, t2.node_id) in ( ('825773502556700160', '825773839074099712'))

On 8.0.17 I get 0 results. On 8.0.15 it works. - If, on 8.0.17, I replace the last line by and (t1.user_id, t2.node_id) in ( ('825773502556700160', '825773839074099712'), ('825773502556700160', '825773839074099712')) (notice it's the same tuple, two times) I get results. - It works with this and (t1.user_id, t2.node_id) = ('825773502556700160', '825773839074099712') - I tried with a simple query SELECT * FROM test.test as t1 WHERE (t1.col1,t1.col2) IN (('a','b')); it works on 8.0.17.

I had a query that was a simple left join between two tables with

NULL

included in the where clause because I needed all of the rows of the left table shown, even if it gave null values in the right table. This worked as I had it working within my php code and my site was displaying what it needed. I haven't looked at this in over a week and went back today to see that it is now all of a sudden not working even though I haven't touched it. I have created a db fiddle here with my exact code and tables - https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=2effc82390641ce513806252700fd25c I want to show - all rows in the left table (level_quiz) and all rows in the right table (student_points) where student_no = 40204123 OR there are NULL rows Could anyone please have a look at this to see why it is not showing the extra row of the left table? (where there would be NULL values for the right table) This would be much appreciated.